web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Vendor Aging query Acr...
Microsoft Dynamics AX (Archived)

Vendor Aging query Across Companies

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I am using below query to combine - vendor aging query Across companies but not a success yet.

public void ProcessReport()
{
    VendTrans       vendtrans, vendTransTmp, vendTransBalance;
    VendSettlement  vendsettlement;
    VendTable       vendtable;
    VendTransOpen   vendTransOpen;
    DirPartyTable   dirPartyTable;
    Amount          amountTrans, amountSettle;
    AccountNum      vendAcc;
    Notes           companies;
    VendAccount     vendAccount;
    boolean         vendexists;
    container       conCompanies= ['B01', 'B12'];
    TransDate       fromDate= 01\07\2016;
    TransDate       toDate= 30\9\2016;
    while select crossCompany : conCompanies
    DataAreaId, RecId, AccountNum, AmountMST, ExchAdjustment, settleAmountMST from vendtrans
        order by vendtrans.AccountNum asc
            where vendtrans.TransDate >= fromDate 
            && vendtrans.TransDate <= toDate
        join party from vendtable
            where vendtable.AccountNum == vendtrans.AccountNum
        join Name from dirPartyTable
            where dirPartyTable.RecId == vendtable.Party
    {
        if(!vendAcc)
        {
         select settleamountMST from vendsettlement
            where vendsettlement.TransRecId == vendtrans.RecId
                && vendsettlement.TransDate >= todate
               && vendsettlement.dataAreaId == vendtrans.dataAreaId ;
            
         apAgingTmp.APAgingAmount += vendsettlement.settleamountmst + vendtrans.remainAmountMST();
         apAgingTmp.AccountNum = vendtrans.AccountNum;
         apAgingTmp.Name     = dirPartyTable.Name;
         apAgingTmp.CompanyId  = vendtrans.dataAreaId;
         vendacc = vendtrans.AccountNum;
            
        }
    else
        {
         select settleamountMST from vendsettlement
            where vendsettlement.TransRecId == vendtrans.RecId
                && vendsettlement.TransDate >= todate
               && vendsettlement.dataAreaId == vendtrans.dataAreaId ;
         if(vendAcc && (vendAcc != vendTrans.AccountNum))
            {
       
        select sum(AmountMST) from vendTransBalance group by vendTransBalance.AccountNum
            where vendTransBalance.AccountNum == vendAcc
                && vendTransBalance.TransDate < fromDate;
       
    apAgingTmp.APAgingAmount +=  vendTransBalance.AmountMST;
                
                apAgingTmp.APAgingAmount += vendTransTmp.AmountMST;
                if(apAgingTmp.APAgingAmount != 0.00)
                apAgingTmp.insert();
                vendacc = vendTrans.AccountNum;
                apAgingTmp.APAgingAmount = 0.00;
            }
        apAgingTmp.APAgingAmount += vendsettlement.settleamountmst + vendtrans.remainAmountMST();
        apAgingTmp.AccountNum = vendtrans.AccountNum;
        apAgingTmp.Name     = dirPartyTable.Name;
        apAgingTmp.CompanyId  = vendtrans.dataAreaId;
   }
   }
select sum(AmountMST) from vendTransBalance group by vendTransBalance.AccountNum
        where vendTransBalance.AccountNum == vendAcc
            && vendTransBalance.TransDate < fromDate;
   
    apAgingTmp.APAgingAmount +=  vendTransBalance.AmountMST;
    if(apAgingTmp.APAgingAmount != 0.00)
    apAgingTmp.insert();
}

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    RE: Vendor Aging query Across Companies

    Hi, 

    Using standard query is a good idea. 

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,941 Super User 2025 Season 2 on at
    RE: Vendor Aging query Across Companies

    I asked my questions as I don't know what the result of the query is and what you are trying to achieve. I'm not able to read the coding and then know what you are trying to achieve. There are some variables used which are not declared within this function. You have to explain in detail if you need detailed help.

    Anyway... I would also go for Ievgen's approach. To prevent re-inventing the wheel, you can have a look at the standard coding.

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Vendor Aging query Across Companies

    On high level you can go take code from standard report and wrap it into crosscompany loop, it should be quite eazy. After that you will have 2 datasources and just combine them.

  • Community Member Profile Picture
    Community Member on at
    RE: Vendor Aging query Across Companies

    Hi,

    I have a requirement - combine vendor aging and accrued purchases reports- show balances of each by vendor "Across Companies" and display as report. 

    I have managed to develop Accrued purchases query across countries. The above query I am trying for is vendor Aging. 

    Appreciate for any alternative ideas. 

    Thanks,

    Suzi 

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Vendor Aging query Across Companies

    Hi suzi12,

    Why are you trying to build your own aging report instead of using standard one ? As you can see it's a bit more complicated than your solution and there is a reason behind it.

  • Community Member Profile Picture
    Community Member on at
    RE: Vendor Aging query Across Companies

    Hi,

    To my understanding Vendor aging report - is list of vendor balances. 

    I built my query in that way to calculate - vendtrans AmountMST within the date intervals. and any settlements after Todate - can be added back this amount. Can you correct me if I am wrong with the above thought of Vendor Aging report.

    The part where I am stuck with is Do I have to consider balance of Vendtrans before from date and add it to the above calculation. When I included this logic I still unable to match up the amount to Aging report.

    Help me with some ideas.

    Thanks,

    Srujana  

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,941 Super User 2025 Season 2 on at
    RE: Vendor Aging query Across Companies

    Hi Suzi12,

    What is the current result and what needs to be improved? You haven't defined what is currently wrong in your question. Please elaborate. The forum volunteers are not clear-sighted. What is "not a success yet"?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 2

#1
Guy Terry Profile Picture

Guy Terry 2 Moderator

#1
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans