Excess PO quantity reduction across multiple PO's

[JeffVTR]

Hi Gurus

I have excess purchase orders in our SAP system by site.

I've calculated the total excess PO qty of material by site and now I'm trying to reduce the excess material quantity across multiple purchase order lines by the total amount I've calculated. 

I've included the last column to represent the expected outcome. 

Your thoughts? I've tried searching the forums but not really found what I need. 

[ShankerV]

Hi @JeffVTR 

Can you please explain the logic to be used to derive the last column shared in the screenshot.

Like if Column1 is same, then if we have excess then -1. Like the business logic.

[JeffVTR]

Hi @ShankerV 

Material = SKU of the product, this is vague in the example.

The total qty in "Excess PO QTY (Site)" needs to consume the "PO quantity" until the "Excess PO QTY (site)" is Zero. This consumption should only occur respective to "Material" and "Site". 

In the example, Material '1' at site 'A' has 3 x Purchase Orders (100001, 100002, and 100003) all with a qty of 3 with total "Excess PO QTY" of -1. This results in the first PO '100001' has 1 removed and equals 2. The other PO quantities remain the same as PO '100001' is not fully consumed.

In the example, Material '2' at site 'A' has 2 x Purchase Orders (100004 and 100005) all with a qty of 1 with total "Excess PO QTY" of -2. This results in PO '100004' being reduced to 0, and because -1 is left, PO '100005' is also reduced by 1 resulting in 0. This scenario would keep occurring until the total of "Excess PO QTY" = 0.

This is essentially a multi row formula that would reduce the running total of "Excess PO QTY" to 0 for the "Purchase Order" quantities grouped by "Site" and "Material"

I hope this helps