Dynamics Community Forum Thread Details

Hi,

I want to display a qty value which is in transit using the following conditions.

sum of PurchLine.PurchQty, where PurchTable.StoreId == thisPurchTable.StoreId, PurchTable.FulfillmentStatus == ‘Placed’, PurchTable.PurchStatus = ‘open order’, PurchLine.ItemId == thisPurchLine.ItemId and PurchTable.dataAreaId == thisdataAreaId, SalesTable.DocumentStatus = (‘packing slip’ or ‘invoice’ enums), where PurchTable/PurchLine record joins via intercompany

Here, my code works fine till it is linked within the Purchline and PurchTable, but as soon as I link to salestable I get output as 0

I am using following code to display the quantity in transit:

display InventQty LSH_InTransit()
{
    PurchTable              purchTableLoc;
    PurchLine               purchLineLoc;
    PurchQty purchQty;
    SalesTable salesTable;
    SalesLine salesLine;
    SalesStatus salesStatus;
    LSH_InterCompanyStatus intercompanyStatus;
    InventTrans inventTrans;
   
    InventQty               ret = 0;
    {

   while select sum(PurchQty) from purchLineLoc
        where 
        purchLineLoc.PurchStatus == PurchStatus::Backorder &&
        purchLineLoc.ItemId == this.ItemId &&
        purchLineLoc.dataAreaId == this.dataAreaId
        join purchTableLoc
        where purchTableLoc.purchid == purchLineLoc.PurchId
        && purchTableLoc.FulfillmentStatus == LSHFulfillmentStatus::Placed
       && purchTableLoc.PurchStatus == PurchStatus::Backorder
       && purchTableLoc.DocumentStatus == DocumentStatus::PurchaseOrder
       
        join salesTable
               where
salestable.intercompanypurchid == purchtableloc.purchid &&
        salesTable.DocumentStatus == DocumentStatus::PackingSlip ||
        salesTable.DocumentStatus == DocumentStatus::Invoice
       

               ret = purchLineLoc.PurchQty; 
    }

    return ret;

}

Can someone let me know if I joined the salestable incorrect. It is an intercompany salestable that I am joining with and the condition on the salesid is Documentstatus on the intercompany salestable is either packing slip or invoice and salestable.intercompanypurchid is equal to purchtable.purchid

Community site session details

Show Intercompany sales order value using X++

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates