Pagos de facturas de venta según fecha del pago

Javierx2 · 3 Noviembre, 2017 22:41

Hola todos, estoy un poco complicado y finalmente me decido a consultar el problema con ustedes.
Necesito crear un informe con las facturas de ventas (cerradas) nacionales, extranjeras de anticipo, notas de credito asociadas (y no) con sus respectivos pagos . Hasta ahi voy bien … tomando en cuenta muchos casos “especiales”… el problema es que una factura tiene 3 o mas pagos en diferentes folios , me explico, factura 1900xx pago 9030 (ene)9031(feb)9032(mar) , cuando se produce el ultimo pago la factura aparece en el informe pero cada pago se va al mes que corresponde y necesito que aparezca todo en el ultimo mes…

la consulta que realice es la siguiente:

SELECT    
datepart(yyyy,case when T2.[DocDueDate]>= CASE WHEN T2.[DocDate] IS NULL THEN T5.[DocDate] ELSE T2.[DocDate] END then T2.[DocDueDate]  else CASE WHEN T2.[DocDate] IS NULL THEN T5.[DocDate]ELSE T2.[DocDate] END end ) as 'AÑO',
datepart(mm,case when T2.[DocDueDate]>= CASE WHEN T2.[DocDate] IS NULL THEN T5.[DocDate] ELSE T2.[DocDate] END then T2.[DocDueDate]  else CASE WHEN T2.[DocDate] IS NULL THEN T5.[DocDate]ELSE T2.[DocDate] END end ) AS 'MES',     
T6.[SlpName]AS 'VENDEDOR',    
T0.[CardCode] AS 'RUT',       
T0.[CardName]AS 'CLIENTE',         
T0.[DocNum] AS 'N°DOC',       
T0.[FolioNum] AS 'N°FOLIO',  
T0.[DocStatus],         
T0.[DocDate]AS 'FECHA DOC',       
T0.[DocCur]AS 'MONEDA',    
CASE WHEN T0.[VatSum]=0 THEN T0.[DocTotalFC] ELSE T0.[DocTotal]- T0.[VatSum] END 'NETO FACT' ,   
T0.[VatSum] AS 'IVA FACT',  
CASE WHEN T0.[VatSum]=0 THEN T0.[DocTotalFC] ELSE T0.[DocTotal] END 'TOTAL FACT' ,
CASE WHEN T2.[DocNum] IS NULL THEN T5.[DocNum] ELSE T2.[DocNum] END 'N°PAGO',
case when T2.[DocDueDate]>= CASE WHEN T2.[DocDate] IS NULL THEN T5.[DocDate] ELSE T2.[DocDate] END then T2.[DocDueDate]  else CASE WHEN T2.[DocDate] IS NULL THEN T5.[DocDate] ELSE T2.[DocDate] END end 'FECHA PAGO',
CASE  WHEN T3.[vatAppldSy] = '0' THEN T3.[AppliedSys] when T3.[AppliedSys]is null then T5.[PaidSys]-T5.[VatPaidSys] else T3.[AppliedSys]-T3.[vatAppldSy] END 'NETO PAGO' ,     
CASE WHEN T3.[vatAppldSy] IS NULL THEN T5.[VatPaidSys] ELSE T3.[vatAppldSy] END 'IVA',         
CASE WHEN T3.[AppliedSys]IS NULL THEN T5.[PaidSys] ELSE T3.[AppliedSys] END  'TOTAL PAGO'              

FROM     
OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN  (SELECT T0.[DocEntry] AS 'DC',MIN( T0.[LineNum]) AS 'LINE' FROM INV1 T0 GROUP BY T0.[DocEntry])LINE  ON DC=T1.[DocEntry] AND LINE=T1.[LineNum] 
LEFT JOIN RCT2 T3 ON T3.[DocEntry]=T0.[DocEntry] AND T3.[DocTransId]= T0.[TransId]
LEFT JOIN ORCT T2 ON T2.[DocEntry] = T3.[DocNum]   
LEFT JOIN RIN1 T4 ON T4.BaseEntry = T1.DocEntry AND T4.BaseLine = T1.LineNum  
LEFT JOIN ORIN T5  ON T4.DocEntry = T5.DocEntry      
LEFT JOIN OSLP T6 ON T0.[SlpCode] = T6.[SlpCode] 
WHERE     T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015' and T0.[DocStatus]='c'

Acá consulto las facturas cerradas, si están pagadas aparece el pago, si no la nota de crédito , a esto se le suma la misma consulta por la ODPI (anticipo) y otra por las notas de credito (ORIN) con uniones (omitido). y los primeros 2 campos me sirven para consultar por año y mes de pago.

Quedo atento a sus comentarios, gracias de antemano

oscarezh · 3 Enero, 2018 15:02

Hol@ @Javierx2:

Buen día y feliz año para usted, quisiera saber si ya resolvió esto? cómo lo hizo? sino coméntanos para ver de que forma lo podemos ayudar.

Salud@s

Javierx2 · 5 Enero, 2018 15:06

Hola Oscar, feliz año …

corrigiendo algunos detalles sobre la marcha , la consulta ya esta funcionando bastante bien, hay un monton de cambios y hasta se hizo mas larga . Aprendi algunas cosas en el camino ya que soy autodidacta , asi que cualquier correccion o sugerencia para este novato es bienvenida.

SELECT    
datepart(yyyy,case when T7.DTE>= CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END then T7.DTE  else CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END end ) as 'AÑO',
datepart(mm,case when T7.DTE>= CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END then T7.DTE  else CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END end ) AS 'MES',     
T6.[SlpName]AS 'VENDEDOR',
T0.[DocStatus],    
T0.[CardCode] AS 'RUT',       
T0.[CardName]AS 'CLIENTE',         
T0.[DocNum] AS 'N°DOC',       
T0.[FolioNum] AS 'N°FOLIO',          
T0.[DocDate]AS 'FECHA DOC',       
T0.[DocCur]AS 'MONEDA',    
CASE WHEN T0.[VatSum]=0 AND T0.[DocCur]<>'CLP' THEN CASE WHEN T0.[TotalExpFC] >'0' THEN T0.[DocTotalFC]-T0.[TotalExpFC] ELSE T0.[DocTotalFC] END ELSE (T0.[DocTotal]-T0.[TotalExpns]) - T0.[VatSum] END 'NETO FACT' ,   
T0.[VatSum] AS 'IVA FACT', 
CASE WHEN T0.[VatSum]=0 AND T0.[DocCur]<>'CLP' THEN CASE WHEN T0.[TotalExpFC] >'0' THEN T0.[DocTotalFC]-T0.[TotalExpFC] ELSE T0.[DocTotalFC] END ELSE T0.[DocTotal]-T0.[TotalExpns] END 'TOTAL FACT' ,
CASE T0.[Indicator] 
WHEN 'XR' THEN 'NC_EX'
WHEN 'XP' THEN 'F_EX'
WHEN 'XN' THEN 'NC_NA'
WHEN 'XF' THEN 'F_NA'
WHEN 'XD' THEN 'ND_NA'
WHEN 'FN' THEN 'FV_NA'
WHEN 'FE' THEN 'FV_EX'
WHEN 'XT' THEN 'ND_EX'
ELSE T0.[Indicator] END 'IND', 
T8.PAGO,         
case when T7.DTE>= CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END then T7.DTE  else CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END end 'FECHA PAGO',     
T8.NETOPAGO,
T8.IVA,
T8.TOTALPAGO,
T0.[U_CV_Extras] AS 'EXTRA'

FROM     
OINV T0  
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN  (SELECT T0.[DocEntry] AS 'DC',MIN( T0.[LineNum]) AS 'LINE' FROM INV1 T0 GROUP BY T0.[DocEntry])LINE     ON      DC=T1.[DocEntry] AND LINE=T1.[LineNum] 
LEFT JOIN OSLP T6 ON T0.[SlpCode] = T6.[SlpCode] 

 
LEFT JOIN 
( SELECT   
T0.[DocNum] AS 'DOC', 
MAX(T2.[DocNum]) AS 'MAX' ,
MAX(T3.[DocDueDate])AS 'DTE'
,MAX(T3.[DocDate])AS 'DTEX'   

FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
LEFT JOIN RCT2 T2 ON T2.[DocEntry]=T0.[DocEntry] AND T2.[DocTransId]= T0.[TransId] 
LEFT JOIN ORCT T3 ON T3.[DocEntry] = T2.[DocNum] 
WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015' and T0.[DocStatus]='c' GROUP BY T0.[DocNum]   )   T7  ON DOC=T0.[DocNum]

LEFT JOIN 
(  SELECT T0.[DocNum] AS 'DC', 
T2.[DocNum] AS 'PAGO', 
T2.[DocDate] AS  'FECHAPAGO',
(T1.[AppliedSys]- T1.[ExpAppld])- T1.[vatAppldSy] AS 'NETOPAGO', 
T1.[vatAppldSy] AS 'IVA',  
T1.[AppliedSys]- T1.[ExpAppld] AS 'TOTALPAGO' 

FROM OINV T0 
LEFT JOIN RCT2 T1 ON T1.[DocEntry]=T0.[DocEntry] AND T1.[DocTransId]= T0.[TransId] 
INNER JOIN ORCT T2 ON T1.[DocNum] = T2.[DocEntry] 
where   T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015' and T0.[DocStatus]='c' AND T0.[U_EstadoCV] IS NULL  and (T2.[CANCELED] = 'N' OR T2.[CANCELED] IS NULL) 

UNION ALL

SELECT T0.[DocNum] AS 'DC', 
MAX(T3.[DocNum] )AS 'PAGO', 
T3.[DocDate] AS 'FECHAPAGO',
(T3.[DocTotal] -T3.[TotalExpns] ) -T3.[VatSum] AS 'NETOPAGO',
T3.[VatSum] AS 'IVA', 
T3.[DocTotal] -T3.[TotalExpns] AS 'TOTALPAGO' 

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
LEFT JOIN RIN1 T2 ON T2.BaseEntry = T1.DocEntry 
INNER JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] 
WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015'  AND T0.[Indicator]<>'AA' 
GROUP BY T0.[DocNum],T3.[DocDate] ,T3.[DocTotal] ,T3.[TotalExpns] ,T3.[VatSum]  )     T8          ON T8.DC = T0.[DocNum] AND T1.LineNum  =T1.LineNum  

WHERE     T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015' and T0.[DocStatus]='c' AND T0.[U_EstadoCV] IS NULL  AND T0.[Indicator]<>'AA'

UNION ALL

SELECT    
datepart(yyyy,case when T7.DTE>= CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END then T7.DTE  else CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END end ) as 'AÑO',
datepart(mm,case when T7.DTE>= CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END then T7.DTE  else CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END end ) AS 'MES',     
T6.[SlpName]AS 'VENDEDOR',
T0.[DocStatus],    
T0.[CardCode] AS 'RUT',       
T0.[CardName]AS 'CLIENTE',         
T0.[DocNum] AS 'N°DOC',       
T0.[FolioNum] AS 'N°FOLIO',          
T0.[DocDate]AS 'FECHA DOC',       
T0.[DocCur]AS 'MONEDA',    
CASE WHEN T0.[VatSum]=0 AND T0.[DocCur]<>'CLP' THEN CASE WHEN T0.[TotalExpFC] >'0' THEN T0.[DocTotalFC]-T0.[TotalExpFC] ELSE T0.[DocTotalFC] END ELSE (T0.[DocTotal]-T0.[TotalExpns]) - T0.[VatSum] END 'NETO FACT' ,   
T0.[VatSum] AS 'IVA FACT', 
CASE WHEN T0.[VatSum]=0 AND T0.[DocCur]<>'CLP' THEN CASE WHEN T0.[TotalExpFC] >'0' THEN T0.[DocTotalFC]-T0.[TotalExpFC] ELSE T0.[DocTotalFC] END ELSE T0.[DocTotal]-T0.[TotalExpns] END 'TOTAL FACT' ,
CASE T0.[Indicator] 
WHEN 'XR' THEN 'NC_EX'
WHEN 'XP' THEN 'F_EX'
WHEN 'XN' THEN 'NC_NA'
WHEN 'XF' THEN 'F_NA'
WHEN 'XD' THEN 'ND_NA'
WHEN 'FN' THEN 'FV_NA'
WHEN 'FE' THEN 'FV_EX'
WHEN 'XT' THEN 'ND_EX'
ELSE T0.[Indicator] END 'IND', 
T8.PAGO,         
case when T7.DTE>= CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END then T7.DTE  else CASE WHEN T7.DTEX IS NULL THEN T8.FECHAPAGO ELSE T7.DTEX END end 'FECHA PAGO',     
T8.NETOPAGO,
T8.IVA,
T8.TOTALPAGO,
T0.[U_CV_Extras] AS 'EXTRA'

FROM     
ODPI T0  
INNER JOIN DPI1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN  (SELECT T0.[DocEntry] AS 'DC',MIN( T0.[LineNum]) AS 'LINE' FROM INV1 T0 GROUP BY T0.[DocEntry])LINE     ON      DC=T1.[DocEntry] AND LINE=T1.[LineNum] 
LEFT JOIN OSLP T6 ON T0.[SlpCode] = T6.[SlpCode] 
 
LEFT JOIN 
( SELECT   
T0.[DocNum] AS 'DOC', 
MAX(T2.[DocNum]) AS 'MAX' ,
MAX(T3.[DocDueDate])AS 'DTE'
,MAX(T3.[DocDate])AS 'DTEX'   

FROM ODPI T0  INNER JOIN DPI1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
LEFT JOIN RCT2 T2 ON T2.[DocEntry]=T0.[DocEntry] AND T2.[DocTransId]= T0.[TransId] 
LEFT JOIN ORCT T3 ON T3.[DocEntry] = T2.[DocNum] 
WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015' and T0.[DocStatus]='c' GROUP BY T0.[DocNum]   )   T7  ON DOC=T0.[DocNum]

LEFT JOIN 
(  SELECT T0.[DocNum] AS 'DC', 
T2.[DocNum] AS 'PAGO', 
T2.[DocDate] AS  'FECHAPAGO',
(T1.[AppliedSys]- T1.[ExpAppld])- T1.[vatAppldSy] AS 'NETOPAGO', 
T1.[vatAppldSy] AS 'IVA',  
T1.[AppliedSys]- T1.[ExpAppld] AS 'TOTALPAGO' 

FROM ODPI T0 
LEFT JOIN RCT2 T1 ON T1.[DocEntry]=T0.[DocEntry] AND T1.[DocTransId]= T0.[TransId] 
INNER JOIN ORCT T2 ON T1.[DocNum] = T2.[DocEntry] 
where   T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015' and T0.[DocStatus]='c' AND T0.[U_EstadoCV] IS NULL  and (T2.[CANCELED] = 'N' OR T2.[CANCELED] IS NULL) 

UNION ALL

SELECT T0.[DocNum] AS 'DC', 
MAX(T3.[DocNum] )AS 'PAGO', 
T3.[DocDate] AS 'FECHAPAGO',
(T3.[DocTotal] -T3.[TotalExpns] ) -T3.[VatSum] AS 'NETOPAGO',
T3.[VatSum] AS 'IVA', 
T3.[DocTotal] -T3.[TotalExpns] AS 'TOTALPAGO' 

FROM ODPI T0 INNER JOIN DPI1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
LEFT JOIN RIN1 T2 ON T2.BaseEntry = T1.DocEntry 
INNER JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] 
WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015'  AND T0.[Indicator]<>'AA' 
GROUP BY T0.[DocNum],T3.[DocDate] ,T3.[DocTotal] ,T3.[TotalExpns] ,T3.[VatSum]  )  T8 ON T8.DC = T0.[DocNum] AND T1.LineNum  =T1.LineNum  

WHERE     T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015' and T0.[DocStatus]='c' AND T0.[U_EstadoCV] IS NULL  AND T0.[Indicator]<>'AA'


UNION ALL

SELECT    
datepart(yyyy,t0.DocDate) AS 'AÑO',      
DATEPART(MM,t0.DocDate)AS 'MES'     ,     
T1.[SlpName],
T0.[DocStatus],       
T0.[CardCode],       
T0.[CardName],        
T0.[DocNum],      
T0.[FolioNum],          
T0.[DocDate],         
T0.[DocCur],    
CASE WHEN T0.[VatSum]=0 THEN CASE WHEN T0.[TotalExpns]>'0' THEN (T0.[DocTotal]-T0.[TotalExpns])*-1 ELSE T0.[DocTotalFC]*-1 END ELSE (T0.[DocTotal] - T0.[VatSum])*-1 END 'NETO DOC',   
T0.[VatSum]*-1 AS 'IVA', 
CASE WHEN T0.[VatSum]=0 THEN CASE WHEN T0.[TotalExpns]>'0' THEN (T0.[DocTotal]-T0.[TotalExpns])*-1 ELSE T0.[DocTotalFC]*-1 END  ELSE T0.[DocTotal]*-1 END 'TOTAL DOC' ,        
CASE T0.[Indicator] 
WHEN 'XR' THEN 'NC_EX'
WHEN 'XP' THEN 'F_EX'
WHEN 'XN' THEN 'NC_NA'
WHEN 'XF' THEN 'F_NA'
WHEN 'XD' THEN 'ND_NA'
WHEN 'FN' THEN 'FV_NA'
WHEN 'FE' THEN 'FV_EX'
WHEN 'XT' THEN 'ND_EX'
ELSE T0.[Indicator] END 'IND',          
T0.[ReceiptNum],      
T0.[DocDate],    
CASE WHEN T0.[VatSum]=0 THEN (T0.[DocTotal]-T0.[TotalExpns])*-1 ELSE (T0.[DocTotal] - T0.[VatSum])*-1 END 'NETO PAGO',   
T0.[VatSum]*-1 AS 'IVA',  
CASE WHEN T0.[VatSum]=0 THEN  (T0.[DocTotal]-T0.[TotalExpns])*-1  ELSE T0.[DocTotal]*-1 END 'TOTAL PAGO',
T0.[U_CV_Extras]

FROM       
ORIN T0      
LEFT JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]                

WHERE   
T0.[CANCELED] = 'N' AND T0.[DocDate] >= '01/01/2015'  AND T0.[Indicator]<>'AA'

ORDER BY T0.[CardName] ASC , T0.[DocNum] ASC

saludos!!

oscarezh · 5 Enero, 2018 15:34

Hol@ @Javierx2:

Es verdad que se extendió un poco más Bueno pero al parecer ya lo tienes resuelto Quizás los eruditos del SQL te podrán dar algunos consejos

De igual forma recuerda dejar tu respuesta como solución, puede servir de ayuda a alguien.

Salud@s

system · 17 Enero, 2018 12:33

Este tema se cerró automáticamente 7 días después del último post. No se permiten nuevas respuestas.