Reporte de Facturas pagadas

SAP Business One
1

Buen día Estimados

Estoy tratando de construir un reporte de facturas pagadas y no pagadas por fecha, pero no se como incluir las facturas que ya se pagaron, desde los pagos recibidos puedo ver todas las facturas pagadas por el cliente pero no se como incluir eso en mi query. Me pueden echar una mano?

Captura

select distinct
T0.DocNum as 'Factura',    
(T0.DocTotal - T0.VatSum) as 'Total Facturado', 
T0.IsIns 'Es Factura en Reserva',
T0.DocDate as 'Fecha de Factura',
T2.DocNum as 'N° de Pago',
T2.DocDate as 'Fecha de Pago',
T2.JrnlMemo as 'Comentarios Pagos',
T6.DocNum as 'Factura Pagada',
T6.SumApplied 'Monto Pagada',
T0.DocStatus as 'Estado de Factura',
T0.CardName as 'Cliente',
T3.SlpName as 'Vendedor',
T4.CreditSum as 'Pago por Tarjeta',
Case T4.creditcard
when '1' then 'Visa'
when '2' then 'Master Card'
when '3' then 'American Express'
when '4' then 'Clave'
when '5' then 'Bono'
end as 'Tarjeta',
T2.CashSum as 'Pago en Efectivo',
T2.CashAcct as 'Cuenta Efectivo',
T2.TrsfrSum as 'Pago por Transferencia',
T2.TrsfrAcct as 'Cuenta Transferencia',
T2.TrsfrDate as 'Fecha de Transferncia',
T2.TrsfrRef as 'Referencia de Transferencia',
T2.CheckSum as 'Pago por Cheque',
T2.CheckAcct as 'Cuenta Cheque'

FROM OINV T0 
INNER JOIN INV1 T5 on T0.DocEntry = T5.DocEntry
INNER JOIN OSLP T3 on T0.SlpCode = T3.SlpCode
LEFT OUTER JOIN ORCT T2 on T0.ReceiptNum = T2.DocNum
LEFT OUTER JOIN RCT3 T4 on T2.DocEntry = T4.DocNum
LEFT OUTER JOIN RCT2 T6 ON T2.DocEntry = T6.DocNum


WHERE T0.DocDate between '[%0]' and '[%1]' AND T0.CANCELED = 'N' and T5.TargetType <> 14
order by T0.DocDate
1 me gusta
2

Hola…

Comprendo que este query solo muestra las facturas pagadas y quieres unir las pendientes de pago, ¿cierto?

De ser así, tal vez lo más fácil sería crear un select y unirlo con un Union o Union All:

select distinct
'Pagada',
T0.DocNum as 'Factura',    
(T0.DocTotal - T0.VatSum) as 'Total Facturado', 
T0.IsIns 'Es Factura en Reserva',
T0.DocDate as 'Fecha de Factura',
T2.DocNum as 'N° de Pago',
T2.DocDate as 'Fecha de Pago',
T2.JrnlMemo as 'Comentarios Pagos',
T6.DocNum as 'Factura Pagada',
T6.SumApplied 'Monto Pagada',
T0.DocStatus as 'Estado de Factura',
T0.CardName as 'Cliente',
T3.SlpName as 'Vendedor',
T4.CreditSum as 'Pago por Tarjeta',
Case T4.creditcard
when '1' then 'Visa'
when '2' then 'Master Card'
when '3' then 'American Express'
when '4' then 'Clave'
when '5' then 'Bono'
end as 'Tarjeta',
T2.CashSum as 'Pago en Efectivo',
T2.CashAcct as 'Cuenta Efectivo',
T2.TrsfrSum as 'Pago por Transferencia',
T2.TrsfrAcct as 'Cuenta Transferencia',
T2.TrsfrDate as 'Fecha de Transferncia',
T2.TrsfrRef as 'Referencia de Transferencia',
T2.CheckSum as 'Pago por Cheque',
T2.CheckAcct as 'Cuenta Cheque'

FROM OINV T0 
INNER JOIN INV1 T5 on T0.DocEntry = T5.DocEntry
INNER JOIN OSLP T3 on T0.SlpCode = T3.SlpCode
LEFT OUTER JOIN ORCT T2 on T0.ReceiptNum = T2.DocNum
LEFT OUTER JOIN RCT3 T4 on T2.DocEntry = T4.DocNum
LEFT OUTER JOIN RCT2 T6 ON T2.DocEntry = T6.DocNum


WHERE T0.DocDate between '20190101' and '20191231' AND T0.CANCELED = 'N' and T5.TargetType <> 14


Union All

select distinct
'Pendiente de pago',
T0.DocNum as 'Factura',    
(T0.DocTotal - T0.VatSum) as 'Total Facturado', 
T0.IsIns 'Es Factura en Reserva',
T0.DocDate as 'Fecha de Factura',
0,
'' as 'Fecha de Pago',
'' as 'Comentarios Pagos',
'' as 'Factura Pagada',
T0.PaidToDate 'Monto Pagada',
T0.DocStatus as 'Estado de Factura',
T0.CardName as 'Cliente',
T3.SlpName as 'Vendedor',
0 as 'Pago por Tarjeta',
'' as 'Tarjeta',
0 as 'Pago en Efectivo',
'' as 'Cuenta Efectivo',
0 as 'Pago por Transferencia',
'' as 'Cuenta Transferencia',
'' as 'Fecha de Transferncia',
'' as 'Referencia de Transferencia',
0 as 'Pago por Cheque',
'' as 'Cuenta Cheque'

FROM OINV T0 
INNER JOIN INV1 T5 on T0.DocEntry = T5.DocEntry
INNER JOIN OSLP T3 on T0.SlpCode = T3.SlpCode



WHERE T0.DocDate between '20190101' and '20191231' AND T0.CANCELED = 'N' and T0.PaidToDate <> DocTotal
order by T0.DocDate
1 me gusta
3

Ejecute el query y me trae la información que necesito

Gracias por la ayuda no lo había pensado de esa forma pero me funciona y me da la información que se requiere.

1 me gusta