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) Share

Report

Posted on by Anshul Thakur

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.

I have the same question (0)

All responses (4)

Answers (1)

Sort by

André Arnaud de Cal... 304,553 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Anshul Thakur 50 on at

Like (0)

Report
Copy link

Link copied!

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);

}

}

}

}

}

}

Was this reply helpful? Yes No
Martin Dráb 239,392 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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:

Create a view that will return item IDs used in multiple companies

Use a cross-company query grouped by ItemId

Use count() aggregation function on RecId

Use the having clause to return only ItemId with count(RecId) > 0

Create a query joining PurchLine with the view.

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.

Was this reply helpful? Yes No
Verified answer

Rustem Galiamov 8,072 on at

Like (0)

Report
Copy link

Link copied!
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); } }

Was this reply helpful? Yes No