Wednesday, March 27, 2013

Using secondary currency and exchange adjustments

If you are using secondary currency (also called reporting currency) in Dynamics AX you might not be aware that the ledger exchange adjustment program does not update AmountMSTSecond values in Dynamics AX 2009.

One reason to use the secondary currency in a company is to report on both your primary currency and your secondary currency.

Say you have a company in Canada that does transactions in both US Dollars and Canadian Dollars. If your master currency is set to USD your ledger transactions will have the USD equivalent in the AmountMST field and the amount in the transaction currency in the AmountCur field. For transactions that are in CAD then will have both the USD and CAD values, but for transactions in USD both fields will be in USD. Setting your secondary currency in the company to CAD means that all transactions will have the USD amount in AmountMST and the CAD amount in AmountMSTSecond.

Currency Code Master currency amount Transactional currency amount Secondary/Reporting currency amount
USD $100 USD $100 USD $98 CAD
CAD $100 USD $98 CAD $98 CAD





The problem arises when you do an exchange adjustment. Normally the Ledger exchange adjustment program will make sure the USD amount (AmountMST) is accurate based on the given exchange rate. Any profit or loss is posted to another GL account. What about the transactions that are already in USD and their equivalent CAD amount? Since they are not adjusted the amount in the AmountMSTSecond field remains at the rate from when it was posted. If you then try to look at account totals in AmountMST vs AmountMSTSecond you will notice that even after an exchange adjustment all values are not at the new rate.

A fix for this is to update the LedgerExchAdj class. The postDiff() method is the method that determines what value needs to be posted. The highlighted code below posts the secondary currency adjustment.

private boolean postDiff(LedgerVoucher  _ledgerVoucher,
                         LedgerTable    _ledgerTable,
                         KeySum         _curSum)
{
    Integer             curTrans;

    LedgerPostingType   posting;
    LedgerAccount       regAccount;

    boolean             foundDiff;
    CurrencyCode        currencyCode;
    Dimension           dimension;

    AmountMST           amountMSTBefore;
    AmountMST           amountMSTNow;
    AmountMST           amountMSTDiff;
    AmountMSTSecondary  amountMSTSecondaryBefore;
    AmountMSTSecondary  amountMSTSecondary;

    AmountMSTSecondary  amountMSTSecondaryNow;
    AmountMSTSecondary  amountMSTSecondaryDiff;

    AmountCur           amountCur;
    EUROTriangulation   triangulation;
    CurrencyCode        secondaryCurrencyCode  = CompanyInfo::find().SecondaryCurrencyCode;
    EUROTriangulation   secondaryTriangulation = Currency::unknownNoYes2Noyes(UnknownNoYes::Unknown, secondaryCurrencyCode, toDate);
    CurrencyExchHelper  exchHelper;
    ;


    for (curTrans=1; curTrans <=_curSum.numOfTrans(); curTrans++)
    {
        [currencyCode,
         dimension]     = _curSum.index2Key(curTrans);

        [amountMSTBefore,
         amountCur,
         amountMSTSecondaryBefore] = _curSum.index2Data(curTrans);

         triangulation = Currency::unknownNoYes2Noyes(UnknownNoYes::Unknown, currencyCode, toDate);
        amountMSTNow= Currency::mstAmount(amountCur,
                                          currencyCode,
                                          toDate,
                                          Currency::noYes2UnknownNoYes(triangulation),
                                          0,
                                          0,
                                          true,
                                          governmentExchRate);

        if (amountMSTNow != amountMSTBefore)
        {
            amountMSTDiff = amountMSTNow - amountMSTBefore;

            exchHelper = CurrencyExchHelper::newExchDate(curext(), currencyCode, toDate);
            exchHelper.parmIsTriangulated(Currency::noYes2UnknownNoYes(secondaryTriangulation));
            exchHelper.parmIsGovernmentExchRate(governmentExchRate);
            amountMSTSecondary = exchHelper.calculateAmountMstToSecondary(amountMSTDiff, true);

            if ((_ledgerTable.AccountPlType <= LedgerAccountType::AccountCost     && amountMSTDiff <= 0) ||
                (_ledgerTable.AccountPlType >= LedgerAccountType::AccountStatus   && amountMSTDiff >  0))
            {
                posting    = LedgerPostingType::ExchRateGain;
                regAccount = Currency::accountNonrealProfit(currencyCode);
            }
            else
            {
                posting    = LedgerPostingType::ExchRateLoss;
                regAccount = Currency::accountNonrealLoss(currencyCode);
            }
            _ledgerVoucher.addTrans(
                LedgerVoucherTransObject::newTransExchAdj(
                    posting,
                    _ledgerVoucher.lastVoucher(),
                    _ledgerVoucher.lastTransDate(),
                   _ledgerTable.AccountNum,
                    dimension,
                    currencyCode,
                    amountMSTDiff,
                    amountMSTSecondary,
                    NoYes::No,
                    triangulation));

            _ledgerVoucher.addTrans(
                LedgerVoucherTransObject::newTransExchAdj(
                    posting,
                    _ledgerVoucher.lastVoucher(),
                    (TaxParameters::find().UnrealizedTax == NoYes::Yes ? postingDate :
                                                                         _ledgerVoucher.lastTransDate()),
                    regAccount,
                    dimension,
                    currencyCode,
                   -amountMSTDiff,
                   -amountMSTSecondary,
                    NoYes::No,
                    triangulation));

            foundDiff = true;
        }
        //Currency in same as company currency,

        //need to post an adjustment to the secondary currency amount only
        else
        {
            amountMSTSecondaryNow = Currency::amountMST2MSTSecond(amountMSTNow, toDate);

            if (amountMSTSecondaryNow != amountMSTSecondaryBefore)
            {
                amountMSTSecondaryDiff = amountMSTSecondaryNow - amountMSTSecondaryBefore;

                if ((_ledgerTable.AccountPlType <= LedgerAccountType::AccountCost    
                       &&  amountMSTSecondaryDiff <= 0) ||
                    (_ledgerTable.AccountPlType >= LedgerAccountType::AccountStatus  

                       && amountMSTSecondaryDiff >  0))
                {
                    posting    = LedgerPostingType::ExchRateGain;
                    regAccount = Currency::accountNonrealProfit(currencyCode);
                }
                else
                {
                    posting    = LedgerPostingType::ExchRateLoss;
                    regAccount = Currency::accountNonrealLoss(currencyCode);
                }

                _ledgerVoucher.addTrans(
                    LedgerVoucherTransObject::newTransExchAdj(
                        posting,
                        _ledgerVoucher.lastVoucher(),
                        _ledgerVoucher.lastTransDate(),
                       _ledgerTable.AccountNum,
                        dimension,
                        currencyCode,
                        0,
                        amountMSTSecondaryDiff,
                        NoYes::No,
                        triangulation));

                _ledgerVoucher.addTrans(
                    LedgerVoucherTransObject::newTransExchAdj(
                        posting,
                        _ledgerVoucher.lastVoucher(),
                        (TaxParameters::find().UnrealizedTax == NoYes::Yes ? postingDate :
                                                           _ledgerVoucher.lastTransDate()),
                        regAccount,
                        dimension,
                        currencyCode,
                       -0,
                       -amountMSTSecondaryDiff,
                        NoYes::No,
                        triangulation));

                foundDiff = true;
            }
        }

    }
    return foundDiff;
}

2 comments:

  1. Comparing this to the code in AX2012 it looks like this has been updated so that the accounting and reporting currencies are both adjusted independently.

    The method LedgerExchAdj.calculateAndPostAdjustments() has a code comment to this effect.

    ReplyDelete
  2. Is there any way to handle for the AR and AP exchange adjustment process as well for the secondary currency.

    ReplyDelete