Buenas noches disculpe necesito realizar un query de Facturas que no tengan retenciones ingresadas ósea que no hayan sido canceladas , lo estaba realizando con la tabla de pagos recibidos y filtrando por tipo de operación retención pero no me funciona.
adjunto query por favor su ayuda si de pronto tienen algo similar millon gracias!!
select distinct
'Pendiente de pago',
T0.DocNum as 'Factura',
T0.NumAtCard [Numero Factura] ,
T0.DocTotal +T0.DiscSum - T0.VatSum [Base Imponible],
T0.VatSum [Impuesto],
T0.DocTotal [Total Documento],
-- (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',
T4.SumApplied 'Monto Pagada',
--T0.DocStatus as 'Estado de Factura',
T0.CardCode 'Codigo_Cliente',
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',
o2.U_LLE_RETENCION 'Aplica Retencion'
FROM OINV T0
INNER JOIN INV1 T5 on T0.DocEntry = T5.DocEntry
INNER JOIN OSLP T3 on T0.SlpCode = T3.SlpCode
LEFT JOIN ORCT T2 on T0.ReceiptNum = T2.DocNum
LEFT JOIN OCRD o2 on o2.CardCode = T0.CardCode
INNER JOIN RCT2 T4 ON T2.[DocEntry] = T4.[DocNum]
WHERE
--T0.DocDate between '20210401' and '20210430'
T0.[DocDate] >= '[%0]'
AND T0.[DocDate] <= '[%1]'
AND T4.SumApplied <> T2.DocTotal
AND T0.CANCELED = 'N'
AND o2.U_LLE_RETENCION = 'Si'
AND T0.DocSubType != 'DN'
--and T0.PaidToDate <> DocTotal
And T2.U_SYP_TIPOOPERACION IN ('C-601','C-602','C-603','C-604','C-610','C-611','C-612','C-613','C-614','C-615','C-599')
AND (T4.[InvType] = 13 or T4.[InvType] is null)
order by T0.DocDate