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

Reconciliation of APhistory with GL

Reference:

https://help-2020r1.acumatica.com/Help?ScreenId=ShowWiki&pageid=177d968e-53c0-3d58-e93c-b8a55936635a

Below will show you how to correct the following script.

The script:

SELECT
    APHistorySum.CompanyID,
    APHistorySum.FinPeriodID,
    APHistorySum.AccountID,
    APHistorySum.CustomerID,
    APHistorySum.SubID,
    APHistorySum.Turnover,
    GLTransactionSum.TotalAmount,
    (APHistorySum.Turnover <> GLTransactionSum.TotalAmt) AS DoesReconcile,
FROM
    (
    SELECT
        CompanyID,
        FinPeriodID,
        AccountID,
        CustomerID,
        SubID,
        Turnover = SUM(FinYtdBalance - FinBegBalance)
     FROM APHistory
     GROUP BY
        CompanyID,
        FinPeriodID,
        AccountID,
        SubID,
        CustomerID) AS APHistory
    LEFT JOIN (
        SELECT
            CompanyID,
            FinPeriodID,
            AccountID,
            ReferenceID,
            SubID,
            TotalAmount = SUM(DebitAmt - CreditAmt)
        FROM
            GLTran
        WHERE
            Module = 'AP'
        GROUP BY
            CompanyID,
            FinPeriodID,
            AccountID,
            SubID,
            ReferenceID) AS GLTransactionSum
        ON APHistorySum.CompanyID = GLTransactionSum.CompanyID
        AND APHistorySum.FinPeriodID = GLTransactionSum.FinPeriodID
        AND APHistorySum.AccountID = GLTransactionSum.AccountID
        AND APHistorySum.CustomerID = GLTransactionSum.ReferenceID -- the vendor ID is stored in this field for AP-originated GL transactions
        AND APHistorySum.SubID = GLTransactionSum.SubID

Here the error message from SQL:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '<'.
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 43
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 APHistorySum.Turnover and GLTransactionSum.TotalAmt to reconcile, the sum should be Zero. Therefore it should be changed to                                                   (APHistorySum.Turnover + GLTransactionSum.TotalAmt ).
2. There is no need for a comma (,)after DoesReconcile.
3. We are in a Vendor context, so CustomerID is incorrect, should be VendorId.
4. The APHistory alias should be APHistorysum.

The Solution

SELECT
    APHistorySum.CompanyID,
    APHistorySum.FinPeriodID,
    APHistorySum.AccountID,
    APHistorySum.VendorID,--change customerid to vendorid
    APHistorySum.SubID,
    APHistorySum.Turnover,
    GLTransactionSum.TotalAmount,
    (APHistorySum.Turnover + GLTransactionSum.TotalAmount) AS DoesReconcile--Change TotalAmt to TotalAmount and <> to +
FROM
    (
    SELECT
        CompanyID,
        FinPeriodID,
        AccountID,
        VendorID,
        SubID,
        Turnover = SUM(FinYtdBalance - FinBegBalance)
     FROM APHistory
     GROUP BY
        CompanyID,
        FinPeriodID,
        AccountID,
        SubID,
        VendorID) AS APHistorySum--first error change from APHistory to APHistorySum
    LEFT JOIN (
        SELECT
            CompanyID,
            FinPeriodID,
            AccountID,
            ReferenceID,
            SubID,
            TotalAmount = SUM(DebitAmt - CreditAmt)
        FROM
            GLTran
        WHERE
            Module = 'AP'
        GROUP BY
            CompanyID,
            FinPeriodID,
            AccountID,
            SubID,
            ReferenceID) AS GLTransactionSum
        ON APHistorySum.CompanyID = GLTransactionSum.CompanyID
        AND APHistorySum.FinPeriodID = GLTransactionSum.FinPeriodID
        AND APHistorySum.AccountID = GLTransactionSum.AccountID
        AND APHistorySum.VendorID = GLTransactionSum.ReferenceID -- the vendor ID is stored in this field for AP-originated GL transactions
        AND APHistorySum.SubID = GLTransactionSum.SubID
--We are in Vendor context so VendorID must be changed to VendorID

The result