DAC Reference : AR DACs| Balance and History Tables ( Acumatica 2020 r1) Script

Reconciliation of AR history with GL

Reference:

https://help-2020r1.acumatica.com/Help?ScreenId=ShowWiki&pageid=546521ef-49a4-198f-36b4-5073cefa16b6 

In my journey to learn a new ERP, I found a challenge when running the above script on my Acumatica test database.

The script:

SELECT
    ARHistorySum.CompanyID,
    ARHistorySum.FinPeriodID,
    ARHistorySum.AccountID,
    ARHistorySum.CustomerID,
    ARHistorySum.SubID,
    ARHistorySum.Turnover,
    GLTransactionSum.TotalAmount,
    (ARHistorySum.Turnover - GLTransactionSum.TotalAmt) AS DoesReconcile, 
FROM
    (
    SELECT
        CompanyID,
        FinPeriodID,
        AccountID,
        CustomerID,
        SubID,
        Turnover = SUM(FinYtdBalance - FinBegBalance)
     FROM ARHistory
     GROUP BY
        CompanyID,
        FinPeriodID,
        AccountID,
        SubID,
        CustomerID) AS ARHistorySum
    LEFT JOIN (
        SELECT
            CompanyID,
            FinPeriodID,
            AccountID,
            ReferenceID,
            SubID,
            TotalAmount = SUM(DebitAmt - CreditAmt)
        FROM
            GLTran
        WHERE
            Module = 'AR'
        GROUP BY
            CompanyID,
            FinPeriodID,
            AccountID,
            SubID,
            ReferenceID) AS GLTransactionSum
        ON ARHistorySum.CompanyID = GLTransactionSum.CompanyID
        AND ARHistorySum.FinPeriodID = GLTransactionSum.FinPeriodID
        AND ARHistorySum.AccountID = GLTransactionSum.AccountID
        AND ARHistorySum.CustomerID = GLTransactionSum.ReferenceID 
        AND ARHistorySum.SubID = GLTransactionSum.SubID

Here the error message from SQL:

Msg 102, Level 15, State 1, Line 10

Incorrect syntax near '<'.

Msg 156, Level 15, State 1, Line 26

Incorrect syntax near the keyword 'AS'.

Msg 156, Level 15, State 1, Line 44

Incorrect syntax near the keyword 'AS'.

What's wrong with the script?

I will highlight in blue the element of the script which generates the error.

1. for ARHistorySum.Turnover and GLTransactionSum.TotalAmt to reconcile , the difference should be Zero. Therefore it should be changed to                                                   (ARHistorySum.Turnover - GLTransactionSum.TotalAmt )
2. There is no need for a comma (,)after DoesReconcile

The Solution

SELECT

    ARHistorySum.CompanyID,

    ARHistorySum.FinPeriodID,

    ARHistorySum.AccountID,

    ARHistorySum.CustomerID,

    ARHistorySum.SubID,

    ARHistorySum.Turnover,

    GLTransactionSum.TotalAmount,

    (ARHistorySum.Turnover - GLTransactionSum.TotalAmount) AS DoesReconcile

FROM

    (

    SELECT

        CompanyID,

        FinPeriodID,

        AccountID,

        CustomerID,

        SubID,

        Turnover = SUM(FinYtdBalance - FinBegBalance)

     FROM ARHistory

     GROUP BY

        CompanyID,

        FinPeriodID,

        AccountID,

        SubID,

        CustomerID) AS ARHistorySum

    LEFT JOIN (

        SELECT

            CompanyID,

            FinPeriodID,

            AccountID,

            ReferenceID,

            SubID,

            TotalAmount = SUM(DebitAmt - CreditAmt)

        FROM

            GLTran

        WHERE

            Module = 'AR'

        GROUP BY

            CompanyID,

            FinPeriodID,

            AccountID,

            SubID,

            ReferenceID) AS GLTransactionSum

        ON ARHistorySum.CompanyID = GLTransactionSum.CompanyID

        AND ARHistorySum.FinPeriodID = GLTransactionSum.FinPeriodID

        AND ARHistorySum.AccountID = GLTransactionSum.AccountID

        AND ARHistorySum.CustomerID = GLTransactionSum.ReferenceID -- the customer ID is stored in this field for AR-originated GL transactions

        AND ARHistorySum.SubID = GLTransactionSum.SubID

                

The result