Skip to Content
avatar image
Justice Lawer Doku

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

Oct 18, 2016 at 12:22 PM | 457 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

Regards

Justice

SAP Business One
SAP Business One | sap business one
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Oct 19, 2016 at 07:00 AM

    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

    Add comment
    10|10000 characters needed characters exceeded