Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Get only those Purchas...
Finance | Project Operations, Human Resources, ...
Answered

Get only those Purchase orders in which the Item in any of the lines of the Purchase Order is released in more than 1 Company

(0) ShareShare
ReportReport
Posted on by Anshul Thakur 50

Hi, I have been stuck at this requirement that I am working on. I want to get only those purchase orders in which the item mentioned in the Purchase order lines is released in more than 1 companies. for eg, I have 2 products, P1 and P2. There are 2 Purchase orders PO1 for P1 and PO2 for P2. P1 is released only in 1 company, C1,whereas, P2 is released in C1 as well as C2. So I want to make a runnable class in which I want the Purchase order where the item is released in more than 1 company.

  • Verified answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Get only those Purchase orders in which the Item in any of the lines of the Purchase Order is released in more than 1 Company

    Hi, you can try to use InventItemGroupItem table. The rows in that table are created when product released.

    It should be something like this:

    static void Job316(Args _args)
{
    PurchLine           purchLine;
    InventItemGroupItem inventItemGroupItem;
    
    while select purchLine
    exists join inventItemGroupItem
        where inventItemGroupItem.ItemId         == purchLine.ItemId
           && inventItemGroupItem.ItemDataAreaId != purchLine.dataAreaId
    {
        info(purchLine.PurchId);
    }
}

  • Martin Dráb Profile Picture
    Martin Dráb 231,923 Most Valuable Professional on at
    RE: Get only those Purchase orders in which the Item in any of the lines of the Purchase Order is released in more than 1 Company

    Your code is very inefficient - for example, you fetch all PurchTable and PurchLine records from the database, despite the fact that you're interested in just a fraction of them.

    I would consider the following design:

    1. Create a view that will return item IDs used in multiple companies
      1. Use a cross-company query grouped by ItemId
      2. Use count() aggregation function on RecId
      3. Use the having clause to return only ItemId with count(RecId) > 0
    2. Create a query joining PurchLine with the view.
      1. Group by PurchId

    Then you'll make a single DB query to fetch all the data at once.

    By the way, please use Insert > Insert code (in the rich formatting view) to paste source code.

  • Anshul Thakur Profile Picture
    Anshul Thakur 50 on at
    RE: Get only those Purchase orders in which the Item in any of the lines of the Purchase Order is released in more than 1 Company

    Hi Andre,

    Thanks for the start. I have used the cross company query for inventtable to check the Items that are released into multiple companies. Now I just want to find the Purchase orders in which these items are used. The requirement is that the client wants a list of purchase orders with items that are released in more than a single company. Here is the code that I have written:

    class ATRunnableClass

    {

       public static void main(Args args)

       {

           PurchLine purchLine;

           PurchTable purchTable;

           InventTable inventtable;

           int countCompany=0;

           while select PurchId from purchTable

           {

               while select ItemId from purchLine where purchLine.PurchId == purchTable.PurchId

               {

                   while select crosscompany ItemId from inventtable where purchLine.ItemId == inventtable.ItemId

                   {

                       countCompany++;

                       if(countCompany > 1)

                       {

                           info(purchTable.PurchId);

                       }

                   }

               }

           }

       }

    }

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 293,245 Super User 2025 Season 1 on at
    RE: Get only those Purchase orders in which the Item in any of the lines of the Purchase Order is released in more than 1 Company

    Hi Anshul,

    Can you explain the purpose? What exact business requirement do you have to solve here? What process is involved? What do you want to execute in such runnable class?

    As a start: There is a global products table and a table for released products. You can use a cross company query to see if a product has been released in multiple legal entities. Then the next step would be probably also a cross company query on the purchase order lines where this product is used.

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

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,245 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,923 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans