Showing posts with label Help script fix for acumatica. Show all posts
Showing posts with label Help script fix for acumatica. Show all posts

Monday, March 30, 2020

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


Reconciliation of AR history with GL

Reference:

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



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

Reconciliation of APhistory with GL

Reference:

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


Norming Asset Management - Reducing Method Formula

Sage 300 Fixed Asset -Norming Asset Management formula for fixed reducing balance method The fixed reducing balance method in normin...