Reporte pagos recibidos

[Former Member]

hola tengo una pregunta estoy haciendo un reporte  de pagos recibidos  a travez de un query el query es el siguiente

SELECT

T0.[DocNum],

T0.DocDate as 'Fecha Pago',

T0.CardName as 'Nombre del Cliente',

T1.[SumApplied],

T0.[DocTotal],

FROM ORCT T0 

INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum

WHERE T0.[DocDate] >= '2012/01/01' and  T0.[DocDate] <= '2012/12/31'

and T1.InvType = '13'

la pregunta es como puedo agregar por linea que factura se esta aplicado cada pago ya estuve checando y en la tabla de rct2 no esta la informacion

y viendo en la pantalla de pagos recibidos  veo que es una variable de sistema  a mi se me habia ocurrido traerlo de oinv pero no encuentro la union correcta para traer el dato correcto

espero sus comentarios

Saludos

[former_member850139]

SELECT T0.DocDate,CASE WHEN U_BXP_TIMBRAPARC='T' THEN 'TERMINADA' WHEN U_BXP_TIMBRAPARC='E' THEN 'ERROR' WHEN U_BXP_TIMBRAPARC='N' THEN 'NO TIMBRAR' END ESTATUS,

T0.U_BXP_TIMBRADOMSJS, T0.DocNum,T0.U_BXP_UUID,T0.CardCode,T0.CardName,T1.DocNum,T1.NumAtCard,T1.CardName
FROM ORCT T0, OINV T1 , RCT2 T3
where T0.DocDate >='20230301' AND T0.DocEntry = T3.DocNum AND T3.DocEntry = T1.DocEntry AND T0.U_BXP_TIMBRAPARC='T'
ORDER BY t0.DocDate DESC

[former_member203638]

Prueba Este, si puedes pasalo a crystal y queda re bien.

SELECT Distinct T1.DocDate 'F.Pago',T1.DocNum 'No.Pago',T9.SeriesName 'Giro',
Factura = Case 
When T3.InvType = '13' Then T4.FolioNum 
When T3.InvType = '14' Then T11.FolioNum 
When T3.InvType = '203' Then T10.FolioNum 
When T3.InvType = '30' Then T12.TransId
End,
'Fecha Fact' = Case 
When T3.InvType = '13' Then T4.DocDate 
When T3.InvType = '14' Then T11.DocDate 
When T3.InvType = '203' Then T10.DocDate 
When T3.InvType = '30' Then T12.RefDate
End,
T1.CardCode as "Codigo", T1.CardName as "Cliente", T2.SlpName, Pago=case 
When T1.CashSum   > '0' Then 'Efectivo' 
When T1.CheckSum  > '0' Then ('Cheque:')+cast(T6.CheckNum as varchar) + (', Banco:' + T8.BankName)
When T1.CreditSum > '0' Then ('TC,' + T7.CrTypeName)
When T1.TrsfrSum  > '0' Then 'TransFerencia' 
End,
RefP=case 
When T1.CashSum   > '0' Then 'Efectivo' 
When T1.CheckSum  > '0' Then 'Cheque'
When T1.CreditSum > '0' Then 'TC'
When T1.TrsfrSum  > '0' Then 'TransFerencia' 
End,
Total = Case 
When T3.InvType = '13' Then T3.SumApplied
When T3.InvType = '14' Then (T3.SumApplied) * -1
When T3.InvType = '203' Then T3.SumApplied
When T3.InvType = '30' Then T3.SumApplied
End,
T1.CashSum,T1.CheckSum,T1.CreditSum,T1.TrsfrSum,T1.Canceled,T1.DocEntry,T3.InvType
FROM dbo.OCRD T0
INNER JOIN  dbo.ORCT T1 ON T0.CardCode   = T1.CardCode
INNER JOIN  dbo.RCT2 T3 ON T3.DocNum     = T1.DocNum
INNER JOIN  dbo.OSLP T2 ON T0.SlpCode    = T2.SlpCode
left JOIN  dbo.OINV T4 ON T4.DocEntry   = T3.DocENtry 
LEFT  JOIN  dbo.RCT3 T5 ON T1.DocEntry   = T5.DOcNum
LEFT  JOIN  dbo.RCT1 T6 ON T1.DocEntry   = T6.DocNum
LEFT  JOIN  dbo.OCRP T7 ON T5.CrTypeCode = T7.CrTypeCode
LEFT  JOIN  dbo.ODSC T8 ON T6.BankCode   = T8.BankCode
INNER JOIN  dbo.NNM1 T9 ON T1.Series     = T9.Series
LEFT  JOIN  dbo.ODPI T10 ON T3.DocEntry  = T10.DocEntry
LEFT JOIN dbo.ORIN T11 ON T3.DocENtry = T11.DocEntry
Inner Join OJDT T12 ON T3.DocTransId = T12.TransId
WHERE  T1.Canceled = 'N' and T1.DocDate Between [%1] and [%2] Order By T1.DocNum 

Saludos

Alessandro

[former_member188471]

verifica si te sirve esto y comentas.

SELECT T1.CardCode, T1.CardName, T0.DocNum as 'AR Invoice Number', T1.DocDate as 'Payment Date', T2.DocNum as 'Incoming Payment Number ', T2.DueDate as 'Check Due Date' , T2.CheckNum as 'Check Number', T2.CheckSum as 'Amount'

FROM OINV T0 INNER JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry INNER JOIN RCT1 T2 ON T1.DocNum = T2.DocNum