SAP Business one Sales Invoice, Incoming payment and Internal reconciliation join

[millicentdark]

Hi Expert,

i want a query that will fuse invoice made,receipt paid on that invoice and receipt paid on account and then reconciled .

A join from these three areas to produce the below columns will be appreciated;

Invoice Number, Receipt Number,CardCode,CardName, Invoice Amountt, Paid amount, Balance, Reconciled Amount,.

Below is a query i wrote but the result has a lot of duplicates. If anyone can work on it to remove duplicates .

SELECT T0.CardCode, T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal], T2.[DocNum] 'Incoming Pay No.', T2.[DocDate] 'Incoming Pay Date', T0.[PaidToDate] 'Total Paid', T0.DocTotal-T0.[PaidToDate] 'Balance Amt' ,T3.ReconNum AS 'Reconciliation Number', t3.ReconDate as 'Reconciliation Date' FROM OINV T0 INNER JOIN RCT2 T1 ON T0.DocEntry=T1.baseabs INNER JOIN ORCT T2 ON T1.[DocNum] = T2.[DocEntry] left JOIN OITR T3 ON T2.DocNum=T3.InitObjAbs --left JOIN ITR1 T4 ON T3.ReconNum=T4.ReconNum ORDER BY T2.DocNum

Regards

Justice

[Former Member]

Hello Justice,

Please try belowed query and let me know if any change you still require:

SELECT T0.DocNum as 'Invoice Number' ,T0.[DocDate],T2.DocNum as 'Receipt Np',T0.CardCode, T0.[CardName], T0.[DocTotal], T2.[DocDate] 'Receipt Date',
T0.[PaidToDate] 'Total Paid', T0.DocTotal-T0.[PaidToDate] 'Balance Amt',T3.ReconNum AS 'Reconciliation Number', t3.ReconDate as 'Reconciliation Date',
t3.Total as 'Reconciled Amount'
FROM OINV T0
INNER JOIN RCT2 T1 ON T0.DocEntry=T1.baseabs
INNER JOIN ORCT T2 ON T1.[DocNum] = T2.[DocEntry]
left JOIN OITR T3 ON T2.DocNum=T3.InitObjAbs and T3.InitObjTyp=24
--left JOIN ITR1 T4 ON T3.ReconNum=T4.ReconNum ORDER BY T2.DocNum

Thanks and Regards,

Jaydeep Galiya