on 07-27-2011 6:33 PM
Buenas tardes,
Tengo la necesidad de una consulta para obtener los movimientos hechos por concepto de DIOT, con los siguientes datos.
Nombre Proveedor
RFC
Base
IVA
IVA Ret
Total
Alguna sugerencia? Son el iva de 3 cuentas contables en especifico.
Saludos.
Buen día, les proporciona una Query que realice, aun se puede modificar para mejorar posteriormente les actualizo la consulta y agradezco comentarios y/o aportaciones saludos.
DECLARE @INI VARCHAR(10)='2019-08-01', @FIN VARCHAR(10)='2019-08-31',@CTA VARCHAR(30)='2150.0011.0001.0004',@Subtotal NUMERIC(19,4),@Iva NUMERIC(19,4),@Total NUMERIC(19,4),@CDML NUMERIC(19,4),@Divi NUMERIC(19,4)
CREATE TABLE #General
(No int IDENTITY(1,1),Fecha DATE,TransId int,BaseRef int,
ContraAct varchar(50),LineMemo varchar(80),Segment_0 varchar(80),ML numeric(12,2),
CardCode VARCHAR(20),RFC varchar(20),Name VARCHAR(80),Divi numeric(19,2),CardCode2 VARCHAR(20),RFC2 varchar(20),Name2 VARCHAR(80))
INSERT INTO #General
-----------------------------------------------------------------------------------------------
SELECT T0.RefDate,T0.TransId,T0.BaseRef,T0.ContraAct,T0.LineMemo,T1.Segment_0,CAST((T0.Debit+(- T0.Credit)) AS NUMERIC(11,2)) AS 'C/D (ML)',
(SELECT TX.ShortName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ContraAct=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE '[PC]%'
)CardCode,
(SELECT T2.LicTradNum
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE '[PC]%'
)RFC,
(SELECT T2.CardName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE '[PC]%'
)Name,
CAST(((T0.Debit+(- T0.Credit))/.16) AS NUMERIC(11,2)) AS Divi
,
---------------------------Clave proveedor------------------------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardCode2,
--------------------RFC-------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.LicTradNum
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.LicTradNum
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)RFC2
,
----------------Nombre Proveedor------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.CardName
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.CardName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardName2
------------------------------Termino de seleccion e inicio de condicion------------------------------------------
FROM JDT1 T0
FULL OUTER JOIN OACT T1 ON T1.AcctCode=T0.Account
FULL OUTER JOIN OCRD T2 ON T0.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=T0.TransId
WHERE T0.RefDate between @INI and @FIN
AND T1.Segment_0=@CTA
----------Condicion de clave de proveedor no vacias-----------------------------
AND (SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)IS NOT NULL
ORDER BY T0.RefDate asc,T0.TransId
----------------------------------------------------------
-----------------Consulta 2-------------------------------
----------------------------------------------------------
CREATE TABLE #General2
(No int IDENTITY(1,1),Fecha DATE,TransId int,BaseRef int,
ContraAct varchar(50),LineMemo varchar(80),Segment_0 varchar(80),ML numeric(12,2),
CardCode VARCHAR(20),RFC varchar(20),Name VARCHAR(80),Divi numeric(19,2),CardCode2 VARCHAR(20),RFC2 varchar(20),Name2 VARCHAR(80))
INSERT INTO #General2
SELECT T0.RefDate,T0.TransId,T0.BaseRef,T0.ContraAct,T0.LineMemo,T1.Segment_0,CAST((T0.Debit+(- T0.Credit)) AS NUMERIC(11,2)) AS 'C/D (ML)',
(SELECT TX.ShortName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ContraAct=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE '[PC]%'
)CardCode,
(SELECT T2.LicTradNum
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE '[PC]%'
)RFC,
(SELECT T2.CardName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE '[PC]%'
)Name,
CAST(((T0.Debit+(- T0.Credit))/.16) AS NUMERIC(11,2)) AS Divi
,
---------------------------Clave proveedor------------------------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardCode2,
--------------------RFC-------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.LicTradNum
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.LicTradNum
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)RFC2
,
----------------Nombre Proveedor------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.CardName
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.CardName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardName2
------------------------------Termino de seleccion e inicio de condicion------------------------------------------
FROM JDT1 T0
FULL OUTER JOIN OACT T1 ON T1.AcctCode=T0.Account
FULL OUTER JOIN OCRD T2 ON T0.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=T0.TransId
WHERE T0.RefDate between @INI and @FIN
AND T1.Segment_0=@CTA
----------Condicion de clace de proveedor vacias-----------------------------
AND (SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
--,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE '[P]%' AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)IS NULL
ORDER BY T0.RefDate asc,T0.TransId
-------------Tabla de union de las dos consultas-----------------------------------------------------------------------------------------------
CREATE TABLE #General3 (No int IDENTITY(1,1),RFC varchar(50),Clave VARCHAR(20),Nombre varchar(80),CDML numeric(12,2),
Divi numeric(12,2),Subtotal numeric(12,4),Iva numeric(12,4),Total numeric(12,4))
INSERT INTO #General3
SELECT DISTINCT RFC2,CardCode2,Name2,NULL 'C/D (ML)',NULL Divi,
CAST(SUM(ML/.16)OVER (PARTITION BY RFC2) AS NUMERIC(12,4))Subtotal,
CAST((SUM(ML/.16) OVER (PARTITION BY RFC2)*.16)AS NUMERIC(12,4))Iva,
CAST((SUM(ML/.16)OVER (PARTITION BY RFC2)+(SUM(ML/.16) OVER (PARTITION BY RFC2)*.16))AS NUMERIC(12,4))Total
FROM #General
UNION
SELECT RFC2,CardCode2,LineMemo,ML,Divi,NULL,NULL,NULL FROM #General2
ORDER BY RFC2 ASC
----------Para totales-----------
SET @Subtotal=(SELECT SUM(Subtotal) FROM #General3)
SET @Iva=(SELECT SUM(Iva) FROM #General3)
SET @Total=(SELECT SUM(Total) FROM #General3)
SET @CDML=(SELECT SUM(ML) FROM #General2)
SET @Divi=(SELECT SUM(Divi) FROM #General2)
-----------Muestra consulta----------------
SELECT * FROM #General3
UNION
SELECT 0,'Total','////////','//////////////////////////////////',@CDML,@Divi,@Subtotal,@Iva,@Total
DROP TABLE #General
DROP TABLE #General2
DROP TABLE #General3
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola josue, supongo eres ABAP, cierto?
Estoy buscando información para este reporte, pero no me quedo claro tu query o mas bien no le entendí. Si tienes información de configuración por parte de tu funcional te agradecería mucho que me puedas compartir algo.
te dejo mi correo anac.gonzalez.9@gmail.com
Saludos cordiales.
Estamos casi finalizando 2016 y este tema sigue siendo gris para muchos, para mí, alguien tiene información actualizada.
Llegué a escuchar que lo estaban sacando de SAP Business One y otros dicen que no viene en el estándar.
Saludos, gracias por la actualización de antemano.
EG
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola, el reporte de Diot se basa en facturas efectivamente pagadas en un periodo. En las consultas que ustedes ponen anteriormente, estan trayendo info unicamente de faccturas y no de pagos. Alguien tiene algo que si incluya los pagos?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
prueben con esto
SELECT T0.[Name] [Impuesto], T2.[LicTradNum][RFC], T2.[DocTotal] [Monto total],
T2.[VatSum] [Impuesto Total], T2.[CardName] [Nombre del Socio de negocios],
T2.[DocDate], T2.[DocNum] [Documento Fatura] , T3.[DocNum] [Documento Pago]
FROM OSTC T0 INNER JOIN PCH4 T1 ON T0.Code = T1.StcCode
INNER JOIN OPCH T2 ON T1.DocEntry = T2.DocEntry
INNER JOIN OVPM T3 ON T3.DocNum = T2.ReceiptNum
WHERE T3.[DocDate] >=[%0] AND T3.[DocDate] <=[%1]
ORDER BY T2.[DocNum]
si de hecho ya lo actualice
SELECT T0.[Name] [Impuesto], T2.[LicTradNum][RFC],T4.[Price] [Sub-Total],T4.[Currency] [Moneda], T3.[TrsfrSumFC] [Monto total],T3.[TrsfrSum] [Monto Total MX],T2.[VatSum] [Impuesto Total],T2.[WTSum] [Retencion], T2.[CardName] [Nombre del Socio de negocios],
T2.[DocDate] [Fecha de Contabilidad Factura],T3.[DocDate] [Fecha de Contabilidad Pago],
T2.[DocNum] [Documento Fatura] , T3.[DocNum] [Documento Pago] FROM OSTC T0 INNER JOIN PCH1 T4 ON T0.Code = T4.TaxCode/*PCH4 T1 ON T0.Code = T1.StcCode INNER JOIN OPCH T2 ON T1.DocEntry = T2.DocEntry*/ INNER JOIN OPCH T2 ON T4.DocEntry = T2.DocEntry
INNER JOIN OVPM T3 ON T3.DocNum = T2.ReceiptNum
WHERE T3.[DocDate] >=[%0] AND T3.[DocDate] <=[%1]
ORDER BY T2.[DocNum]
la parte de las retención,también los datos de las cantidades ya que con el anterior tomaba las cantidades de la factura y no del pago entonces presentaba fallas en el tipo de cambio. si se manejan USD. los demás puntos que mencionan no los tome en cuenta por que mis usuarios no me comentaron esos pero esta bien que me comentes para ingresarlos en la consulta.
Hola, aun así no es lo que tus usuarios pidan, es lo que la ley marca, te faltan los tipos de proveedores, tipos de operacion y documentos propios de la empresa
Al hacerlo así estarias declarando solamente parcialmente. Debes considerar
Para que sea un reporte de DIOT le faltan muchos escenarios legales, no del gusto del usuario.
Saludos
Alguien a validado esto? estaria interesante que comentaran! gracias
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Quintaro, a que te refieres con DIOT?
Puedes detallar mas...
Slds
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Qué tal Floyola
Es un reporte de IVa de operaciones de terceros que se entrega en México mensualmente, sin embargo, no hay uno que te SAP automáticamente algo asi, aplica únicamente para proveedores
IVA ACREDITABLE SIN IVA POR ACREDITAR RFC MONTO TOTAL MONTO IVA NOMBRE DEL SOCIO E NEGOCIOS FECHA CONTABILIZACION # DOCUMENTO
IVA Compras
IVA Importaciones
IVA Gastos
Quintaro, prueba este query
SELECT T0.StcCode, SUM(T0.BaseSum+T0.TaxSum)'Total', SUM(TaxSum)'Impuesto', T1.CardName, T1.DocDate, T1.DocNum, T1.DocEntry
FROM PCH4 T0
INNER JOIN OPCH T1 ON T1.DocEntry=T0.DocEntry
WHERE T1.DocDate >='[%0]' AND T1.DocDate <='[%1]'
GROUP BY T0.StcCode, T1.CardName, T1.DocDate, T1.DocNum, T1.DocEntry
ORDER BY T0.StcCode, T1.DocEntry
Slds
Prueba con
SELECT T0.[Name] [Impuesto], T2.[LicTradNum][RFC], T2.[DocTotal] [Monto total],
T2.[VatSum] [Impuesto Total], T2.[CardName] [Nombre del Socio de negocios],
T2.[DocDate], T2.[DocNum] FROM OSTC T0 INNER JOIN PCH4 T1 ON
T0.Code = T1.StcCode INNER JOIN OPCH T2 ON T1.DocEntry = T2.DocEntry
WHERE T2.[DocDate] >=[%0] AND T2.[DocDate] <=[%1]
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.