Pagos de facturas de venta según fecha del pago

sql
Etiquetas: #<Tag:0x00007fb8db32ffa0>

#1

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


#2

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 :raised_hand_with_fingers_splayed:t5:


#3

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!!


#4

Hol@ @Javierx2:

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

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

Salud@s :raised_hand_with_fingers_splayed:t5:


#5

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