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 norming AM requires to write a formula. I have struggled to find the right one and hope this will help someone else.

If there is no salvage value for the asset, this formula should work:

AMASST.BKNETVAL * BRT /100

However, if there is a salvage value then:

(AMASST.BKNETVAL -BSV)* BRT /100

Notes:

AMASST.BKNETVAL = Master Date Net Value

BSV= Salvage Value

BRT=Depreciation Rate

If this solution does not work for you or you find a discrepancy, feel free to comment.

How to create a GL journal import File for Odoo 13

How to create a GL journal import File for Odoo 13

The following fields are a must for a successful import of GL transaction within a journal:

1. Date
2. Partner
3. Reference
4. Journal Items/Label
5. Journal Items
6. Journal Items/Credit
7. Journal Items/Debit

From Accounting> Journal Entries, click on the IMPORT file icon then LOAD FILE.

Once, the load file screen identifies the location of the template and Load it.

TEST to make sure there is no matching error and IMPORT.

It is very important to leave the highlighted box as below:

Here the result:

When opening the journal:

The Excel template: 

I hope this will help you!!!

Specific valuation method in Acumatica with landed cost (Serial Item case)

Specific valuation method in Acumatica with landed cost (Serial Item case)

The Specific valuation method in Acumatica is used to value serialized and lot items. Refer here for the definition.
I once had an issue with another ERP where the landed cost was split equally to all existing serial numbers. And the customer was not happy at all, that would have been the solution for the customer.

Scenario

• Bring in 2 serial items at the value of 1000 each, then later on another 1 with a landed cost of 200 (for each Serial Item).
• The items master file shows as below: SN001 with FIFO and SN002 with Specific valuation method

The Lot/Serial Number report display the difference between FIFO vs Specific:

Observation
What you will notice is that the FIFO redistribute equally the landed cost toward all existing serial item number. This behavior has been observed with different ERP, but what needs to be emphasized is that the principle of FIFO is respected on the basis that it is a financial transaction regardless of which serial number is selected when doing a sale.
However, the Specific method respects the allocation of landed cost and fix it to the relevant SN number. This is why it is the best option when dealing with the serial items and wanting to keep track of cost to designated lot/serial items.
The Demo

Then we continue to explore Acumatica ERP.

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.