Acumatica BQL

BQL in Acumatica 

Acumatica gives different options to query the data, before Acumatica I never heard of such language. BQL Business Query Language seems to be native to Acumatica 

Luckily, it is not too far from SQL, below is an example and its SQL equivalent:

Complex BQL 

Equivalent in SQL

     Select *  From BalancedAPDocument
            Left Join APInvoice
                On APInvoice.docType=BalancedAPDocument.docType
                    And APInvoice.refNbr=BalancedAPDocument.refNbr
            Left Join APPayment
                On APPayment.docType=BalancedAPDocument.docType
                    And APPayment.refNbr=BalancedAPDocument.refNbr
            Left Join APAdjust
                On APAdjust.adjgDocType=BalancedAPDocument.docType
      Group By BalancedAPDocument.docType,
               BalancedAPDocument.refNbr,
               BalancedAPDocument.released,
               BalancedAPDocument.prebooked,
               BalancedAPDocument.openDoc

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

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

Acumatica: Fix to undo Extend To Vendor

In Response to www.augforums.com/forums/acumatica-finance-modules/undo-vendor-extend-to-customer/

How to undo the Extend to Vendor in Acumatica?

Problem

After using the option to Extend to Vendor from Customer, one would like to reverse the option.

Unfortunately, Acumatica has not made any provision yet to reverse that action.

Pre-requisite

For this fix to work make sure that no transactions have been posted to the extended vendor.

Steps to Steps

1.Check the BAccountID of the customer:      

                                                                      

Select AcctCD,AcctName,BAccountID from BAccount Where Type='VC' AND AcctName='Name Of Customer' (or AcctCD='Customer ID')

2. Note that the vendor will be created with the same BAccountID into the Vendor table, to check this:

             

Select * from Vendor Where BaccountID= xx* (from Baccount table)

3. Script to undo the Extend to Vendor 

    a)updating the business account table as if it was just a customer account

Update BAccount set type='CU' where BAccountID=xx (being the id of the customer)

   b)Delinking the vendor from the business account

Update VENDOR set BAccountID='' where BAccountID=xx

Note:

Please note that this fix is only valid when there are no transactions posted under the extended vendor.

The same logic applies to the option to extend to a customer from a vendor.

This must be applied by someone who has extended knowledge of Acumatica.

Acumatica feature : Extend to Vendor

1#Extend to Vendor 

What is it?

It is a nice feature that you find under the customer. It is a quicker way to create a vendor from the existing customer.

Where do you find it?

This feature is found under the customer profile, Action list:

When can you use it?

When your customer is your supplier as well.

For the Techy

Run the following script in SQL Server Studio Management:

Select * from BAccount where type='VC'

Type VC means VendorCustomer.

Note

The same feature exists under the vendor profile and is called Extend to Customer.