Skip to Content
Oct 18, 2016 at 12:22 PM

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

1204 Views Last edit Oct 18, 2016 at 05:09 PM 2 rev

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