Buenas tardes
con en el siguiente código de query desarrollamos hasta el fin del 2018 un informe de pagos recibidos con el valor aplicado a cada factura para el año 2019 hay un consecutivo que no aparece en el informe
agradezco su colaboración y asesoría para el desarrollo de esta gestión.
/*SELECT FROM [dbo].[OVPM] T10*/
declare @fecha_inic as datetime
/* WHERE */
set @fecha_inic = /* T10.DocDate */ '[%0]'
/*SELECT FROM [dbo].[OVPM] T11*/
declare @fecha_fin as datetime
/* WHERE */
set @fecha_fin = /* T11.DocDate */ '[%1]'
SELECT TB.[Pagos Recibidos.],TB.[Nº Documento], TB.[Nº Factura], TB.[Código SN], TB.[Fecha Documento], TB.[Fecha Factura], TB.[Fecha Ven. Fact], TB.Serie, TB.[Pago Aplicado], TB.Estado, tb.Cheque_Pos, T4.SlpName as Asesor FROM (
SELECT
'Pagos Recibidos.' AS'Pagos Recibidos.',
T0.[DocNum] 'Nº Documento',
-- T1.[baseAbs],
T2.[DocNum] 'Nº Factura',
T0.[CardCode] 'Código SN',
T0.[TaxDate] 'Fecha Documento' ,
T2.[DocDate] 'Fecha Factura',
T2.[DocDueDate] 'Fecha Ven. Fact' ,
(SELECT T8.SeriesName FROM NNM1 T8 WHERE T8.Series= T0.Series) AS Serie,
case
when T1.[SumApplied] is null then t0.DocTotal else T1.[SumApplied] end as 'Pago Aplicado',
case
when T0.[TaxDate]=T2.[DocDate] then 'Contado'
when T2.[DocNum] is NULL and t0.DocNum not in (SELECT distinct T1.[SrcObjAbs] FROM [dbo].[ITR1] T1 INNER JOIN OITR T2 ON T1.[ReconNum] = T2.[ReconNum] ) then 'Pago Anticipado'
when T2.[DocNum] is NULL and t0.DocNum in (SELECT distinct T1.[SrcObjAbs] FROM [dbo].[ITR1] T1 INNER JOIN OITR T2 ON T1.[ReconNum] = T2.[ReconNum] ) then 'Pago Ant. Rec'
when T2.[isIns] = 'Y' THEN 'Contado'
when MONTH (T0.[TaxDate])=MONTH (T2.[DocDate]) then 'Credito del Mes'
ELSE 'Credito'
END AS Estado,
'' as Cheque_Pos,
case
when t2.SlpCode is null then T3.SlpCode else t2.SlpCode end 'Emp de Ventas'
FROM ORCT T0
LEFT JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
LEFT JOIN OINV T2 ON T1.[baseAbs] = T2.[DocEntry]
LEFT JOIN OCRD T3 ON T0.CardCode = T3.CardCode
WHERE T0.[DocDate] >= @fecha_inic AND T0.[DocDate] <= @fecha_fin AND T0.Canceled='N' and
CheckAcct NOT IN (11050513,11050523,11050532)
union
SELECT
'Pagos Recibidos.' AS'Pagos Recibidos.',
T0.[DocNum] 'Nº Documento',
-- T1.[baseAbs],
T2.[DocNum] 'Nº Factura',
T0.[CardCode] 'Código SN',
T0.[TaxDate] 'Fecha Documento' ,
T2.[DocDate] 'Fecha Factura',
T2.[DocDueDate] 'Fecha Ven. Fact' ,
(SELECT T8.SeriesName FROM NNM1 T8 WHERE T8.Series= T0.Series) AS Serie,
case
when T1.[SumApplied] is null then t0.DocTotal else T1.[SumApplied] end as 'Pago Aplicado',
'' AS Estado,
case
when T0.[TaxDate]=T2.[DocDate] then 'Contado'
when T2.[DocNum] is NULL and t0.DocNum not in (SELECT distinct T1.[SrcObjAbs] FROM [dbo].[ITR1] T1 INNER JOIN OITR T2 ON T1.[ReconNum] = T2.[ReconNum] ) then 'Pago Anticipado'
when T2.[DocNum] is NULL and t0.DocNum in (SELECT distinct T1.[SrcObjAbs] FROM [dbo].[ITR1] T1 INNER JOIN OITR T2 ON T1.[ReconNum] = T2.[ReconNum] ) then 'Pago Ant. Rec'
when T2.[isIns] = 'Y' THEN 'Contado'
when MONTH (T0.[TaxDate])=MONTH (T2.[DocDate]) then 'Credito del Mes'
ELSE 'Credito'
END AS Cheque_Pos,
case
when t2.SlpCode is null then T3.SlpCode else t2.SlpCode end 'Emp de Ventas'
FROM ORCT T0
LEFT JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
LEFT JOIN OINV T2 ON T1.[baseAbs] = T2.[DocEntry]
LEFT JOIN OCRD T3 ON T0.CardCode = T3.CardCode
WHERE T0.[DocDate] >= @fecha_inic AND T0.[DocDate] <= @fecha_fin AND T0.Canceled='N' and
CheckAcct IN (11050513,11050523,11050532)
) TB
LEFT JOIN OSLP T4 ON TB.[Emp de Ventas]=T4.SlpCode
ORDER BY TB.[Nº Documento] ASC
