query to link incoming payments with deposit

[Former Member]

hi everyone

i am looking to develop a query that can link incoming payments with the respective deposit. does anyone have any such query? can you help?

thanks

[former_member583013 · Accepted solution]

Please use the following. It shows both check deposits and CC deposits

SELECT 'CHECK DEPOSIT' [Deposit Type], T0.DEPOSID [Deposit No.], T0.DEPOSDATE [Deposit Date], T0.LOCTOTAL [Desposit Total],

T2.RCPTNUM [Incoming Payment No.]

FROM ODPS T0 INNER JOIN DPS1 T1 ON T0.DEPOSNUM = T1.DEPOSITID

INNER JOIN OCHH T2 ON T2.CHECKKEY = T1.CHECKKEY

WHERE T0.DEPOSTYPE = 'K'

UNION ALL

SELECT 'CREDIT CARD DEPOSIT', T0.DEPOSID, T0.DEPOSDATE, T0.LOCTOTAL, T1.RCTABS

FROM ODPS T0 INNER JOIN OCRH T1 ON T0.DEPOSNUM = T1.DEPNUM

WHERE T0.DEPOSTYPE = 'V'

Best wishes

Suda

[Former Member · Accepted solution]

thanks suda...you rule!