Friday, December 30, 2016

Get Trial Balance report by Query Ax 2012

static void CheckGLEntry(Args _args)
{
    GeneralJournalEntry                 generalJournalEntry;
    GeneralJournalAccountEntry          generalJournalAccountEntry;
    FiscalCalendarPeriod                fiscalCalendarPeriod;
    TransDate             _startDate = mkDate(31,12,2015);
    TransDate             _endDate = mkDate(31,12,2016);
    Fcc_BIData          bidata;
    DimensionAttributeValueCombination  dimValueCombi;
    MainAccount     mainAccount;
    MainAccountCategory mainAccountCategory;
    DimensionAttributeLevelValueAllView dimAllviewchk;
    //Find Dimension value - function
    Description255 getDimensionValue(RecId  _recId,Name _DimensionName)
    {
        DimensionAttributeLevelValueAllView dimAllview;
        DimensionAttribute          dimAttr;
        ;
        dimAllview.clear();
        select firstOnly dimAllview
            where dimAllview.ValueCombinationRecId == _recId
                join dimAttr
                    where dimAttr.RecId == dimAllview.DimensionAttribute
                            && dimAttr.Name == _DimensionName;
        return dimAllview.DisplayValue;
    }
    //Get Voucher type
    Fcc_BIType  GetBIType(Voucher   _Voucher,Name   _LE)
    {
        Fcc_BIType  Fcc_BIType;
        ;
        changeCompany(_LE)
        {
            if(_Voucher && CustTrans::findByVoucher(_Voucher))
            {
                return Fcc_BIType::Cust;
            }
            else if(_Voucher && VendTrans::findByVoucher(_Voucher))
            {
                return Fcc_BIType::Vend;
            }
            else
            {
                return Fcc_BIType::Ledger;
            }
        }
    }
    //Get Account num Cust/Vend
    AccountNum  GetAccountNumBI(Voucher   _Voucher,Name _LE)
    {
        ;
        changeCompany(_LE)
        {
            if(_Voucher && CustTrans::findByVoucher(_Voucher))
            {
                return CustTrans::findByVoucher(_Voucher).AccountNum;
            }
            else if(_Voucher && VendTrans::findByVoucher(_Voucher))
            {
                return VendTrans::findByVoucher(_Voucher).AccountNum;
            }
            else
            {
                return "";
            }
        }
    }
    //End
    ;
    delete_from bidata;
    while select generalJournalAccountEntry
            //where generalJournalAccountEntry.LedgerAccount like  mainAccount.MainAccountId //For other than expenses and sales Account category
        join generalJournalEntry  order by AccountingDate Asc
            where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
                    && generalJournalEntry.AccountingDate >= _startDate
                        && generalJournalEntry.AccountingDate <= _endDate
                            && generalJournalEntry.SubledgerVoucherDataAreaId != ""
                            //&& generalJournalEntry.Ledger == Ledger::current()
        join dimAllviewchk
            where dimAllviewchk.ValueCombinationRecId == generalJournalAccountEntry.LedgerDimension
                    && dimAllviewchk.DimensionAttribute == DimensionAttribute::findByName("MainAccount",false).RecId
            join mainAccount
                where  mainAccount.MainAccountId == dimAllviewchk.DisplayValue
                            && mainAccount.MainAccountId != "9999999"
                    join mainAccountCategory
                        where mainAccountCategory.AccountCategoryRef == mainAccount.AccountCategoryRef
                                && (mainAccountCategory.AccountCategory == "INCOME" ||
                                    mainAccountCategory.AccountCategory == "MTCOGS" ||
                                    mainAccountCategory.AccountCategory == "SRCOGS" ||
                                    mainAccountCategory.AccountCategory == "EMPEXP" ||
                                    mainAccountCategory.AccountCategory == "RENTEX" ||
                                    mainAccountCategory.AccountCategory == "OTHEXP" ||
                                    mainAccountCategory.AccountCategory == "DEPEXP"
                                    )
    {
        bidata.clear();
        select dimValueCombi
            where dimValueCombi.DisplayValue == generalJournalAccountEntry.LedgerAccount;
        bidata.MainAccountNum = getDimensionValue(dimValueCombi.RecId,"MainAccount");
        bidata.MainAccountName = MainAccount::findByMainAccountId(bidata.MainAccountNum).Name;
        bidata.AccountCatgeory = MainAccountCategory::findAccountCategoryRef(MainAccount::findByMainAccountId(bidata.MainAccountNum).AccountCategoryRef).AccountCategory;
        bidata.Opening = 0;//num2str(_amount,8,3,0,0,)
        if(generalJournalAccountEntry.IsCredit == NoYes::Yes)
        {
            bidata.Credit = generalJournalAccountEntry.AccountingCurrencyAmount *-1; //For summary AmountCredit
            bidata.Debit = 0; //For summary Amount Debit
        }
        else
        {
            bidata.Credit = 0;
            bidata.Debit = generalJournalAccountEntry.AccountingCurrencyAmount; //For summary Amount Debit
        }
        bidata.Closing = bidata.Debit - bidata.Credit; //For summary endingbalacne
        bidata.TransDate = generalJournalEntry.AccountingDate;
        bidata.month = mthOfYr(generalJournalEntry.AccountingDate);
        bidata.Year = year(generalJournalEntry.AccountingDate);
        bidata.BU = getDimensionValue(dimValueCombi.RecId,"BusinessUnit");
        bidata.Dept = getDimensionValue(dimValueCombi.RecId,"Department");
        bidata.Project = getDimensionValue(dimValueCombi.RecId,"Project");
        bidata.Worker = getDimensionValue(dimValueCombi.RecId,"Worker");
        bidata.LegalEntity = getDimensionValue(dimValueCombi.RecId,"LegalEntity");
        bidata.DateAreaVoucher  = curext();
        bidata.SubledgerVoucher = generalJournalEntry.SubledgerVoucher;
        bidata.Fcc_BIType = GetBIType(generalJournalEntry.SubledgerVoucher,curext());
        bidata.AccountNum = GetAccountNumBI(generalJournalEntry.SubledgerVoucher,curext());
        bidata.insert();
    }
    info("Data got refreshed");
}

No comments:

Post a Comment

Convert Call stack to readable format in D365FO X++

//Input --container _xppCallStack = xSession::xppCallStack();  Public static str POL_formatXppCallStack(container _xppCallStack, int _skipFr...