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