Reporte de desglose de pagos

[Lsanders]

Buenas tardes Estimados

Tengo un query que me es de utilidad para los desgloses de pagos, quisiera agregarle las notas de credito pero cuando lo hago solo me sale las facturas, les dejo el query para ver si me pueden echar una mano.

SELECT T0.DocDate, T0.DocNum, T0.CardCode, T0.CardName,(T0.DocTotal-T0.VatSum) as 'Total Facturado',

isnull((select (x.DocTotal)FROM OINV x  

where x.U_Mpago='1'  and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum and t0.cardcode=x.cardcode ),0.0)AS 'Efectivo',

isnull((select (x.DocTotal) FROM OINV x  

where x.U_Mpago='2'   and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum  and t0.cardcode=x.cardcode),0.0)AS 'Cheque', 


isnull((select (x.DocTotal) FROM OINV x  

where x.U_Mpago='3'   and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum  and t0.cardcode=x.cardcode),0.0)AS 'Visa',

isnull((select (x.DocTotal) FROM OINV x  

where x.U_Mpago='4'   and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum  and t0.cardcode=x.cardcode),0.0)AS 'Amex'
,

isnull((select (x.DocTotal) FROM OINV x  

where x.U_Mpago='5'   and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum  and t0.cardcode=x.cardcode),0.0)AS 'MasterCard',

isnull((select (x.DocTotal) FROM OINV x  

where x.U_Mpago='6'   and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum  and t0.cardcode=x.cardcode),0.0)AS 'Clave',


isnull((select( x.DocTotal) FROM OINV x  

where x.U_Mpago='8'   and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum  and t0.cardcode=x.cardcode),0.0)AS 'Transferencia'
,

isnull((select (x.DocTotal) FROM OINV x  

where x.U_Mpago='7'   and x.DocDate >='[%0]' and x.DocDate <= '[%1]' and x.docnum=t0.docnum  and t0.cardcode=x.cardcode),0.0)AS 'Credito'

 FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 

INNER JOIN OCTG T2 ON T0.GroupNum = T2.GroupNum 

WHERE T0.DocDate >='[%0]' and T0.DocDate <= '[%1]'  

GROUP BY T0.DocDate, T0.DocNum, T0.CardCode, T0.CardName,T0.DiscSum,T0.TotalExpns,T0.VatSum, T0.DocTotal,T2.PymntGroup

ORDER BY T0.DocDate, T0.DocNum

[Willy_Caldero]

En tu query no veo referencia a las tablas de Notas de crédito, ORIN y RIN1.

Saludos,

[OscarGerardo]

Usa este codigo:

SELECT T2.ItemCode, T2.Dscription, T2.DocEntry, T2.BaseRef,
CASE 
WHEN T1.ObjType = 13 THEN 'AR Invoice'
WHEN T1.ObjType = 14 THEN 'AR Cred Memo' 
ELSE 'RESEARCH'
END AS 'Document Type',
T1.DocDate, T2.AcctCode, T1.U_FolioNum,
T2.Quantity, T2.Price, T1.CardName, T1.CardCode, T1.NumAtCard, T2.LineTotal

FROM  OINV T1 INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry
INNER JOIN OCRD T3 ON T1.CardCode = T3.CardCode

WHERE T1.DocDate between {?Fecha1} AND {?Fecha2}

UNION ALL

SELECT T5.ItemCode, T5.Dscription, T5.DocEntry, T5.BaseRef, 
CASE 
WHEN T4.ObjType = 13 THEN 'AR Invoice'
WHEN T4.ObjType = 14 THEN 'AR Cred Memo' 
ELSE 'RESEARCH'
END AS 'Document Type',
T4.DocDate, T5.AcctCode, T4.U_FolioNum,
T5.Quantity, T5.Price, T4.CardName, T4.CardCode, T4.NumAtCard, T5.LineTotal

FROM ORIN T4 INNER JOIN RIN1 T5 ON T4.DocEntry = T5.DocEntry
INNER JOIN OCRD T6 ON T4.CardCode = T6.CardCode

WHERE T4.DocDate between {?Fecha1} AND {?Fecha2}

con ese código te debe de andar, lo que si es cambiarle las fechas por unas fechas reales yo las uso así por que son parámetros del Crystal Reports.