Total open quantity on Production orders for an item by warehouse

Hi ​@bruno71 

Include INSiteStatus and AMProdItem in the GI to get warehouse-wise open production quantities.

Add a Left Join using:
INSiteStatus.InventoryID = AMProdItem.InventoryID
INSiteStatus.SiteID = AMProdItem.SiteID

​@bruno71 

Is it the component item or finished good stock for the production orders you are looking for?

Hi ​@bruno71 
 

To get the total quantity on open Production Orders by Item and Warehouse in your GI, you need to join the Manufacturing production order header table.

Use DAC:

AMProdItem
 

This table contains:
InventoryID, SiteID (Warehouse), QtytoProd, QtyComplete, Status

Join conditions:

InventoryItem.InventoryID = AMProdItem.InventoryID
AND
INSite.SiteID = AMProdItem.SiteID

In Conditions tab, filter only open production orders.

To get open quantity, create a calculated field:

QtytoProd - QtyComplete

Apply Sum aggregation on this field and group by:

InventoryID, SiteID

This will give total open production quantity by item and warehouse.

Hi ​@bruno71,
If your GI already shows Qty On Hand by warehouse (using INSiteStatus), then:

Add AMProdItem as a Left Join to pull open production quantities.

Join on:

• INSiteStatus.InventoryID = AMProdItem.InventoryID

• INSiteStatus.SiteID = AMProdItem.SiteID

Then:

• Filter AMProdItem.StatusID NOT IN (Closed, Completed, Cancelled)

• Use AMProdItem.QtyRemaining

• Aggregate QtyRemaining as SUM

• Group by InventoryID and SiteID

Using QtyRemaining ensures the total reflects the true open production quantity per item per warehouse.

I created a separate GI based on AMProdItem in order to summarize the total quantities by Warehouse and Item (see code below).  Then I referenced that GI in my original one and did a LEFT join on InventoryID and SiteID.

Everything works fine, except for the items that have no matching record in the AMProdItem GI.  Instead of showing a value of 0 or blank or null, it shows a value of 1.0.  

I tried adding a formula like 

=IsNull([PFTOpenProductionTotals.AMProdItem_qtyRemaining],0)

but it gives me an error about using a virtual field in a conditional expression.  How can I fix this?
 

FROM AMProdItem
WHERE AMProdItem.StatusID = 'R'
  OR AMProdItem.StatusID = 'I'
GROUP BY
  AMProdItem.SiteID,
  AMProdItem.InventoryID
SELECT
  AMProdItem.SiteID AS 'SiteID',
  AMProdItem.InventoryID AS 'InventoryID',
  SUM(AMProdItem.QtytoProd) AS 'QtyToProd',
  SUM(AMProdItem.QtyComplete) AS 'QtyComplete',
  SUM(AMProdItem.QtyRemaining) AS 'QtyRemaining'

Hi ​@bruno71,
Join AMProdItem directly into your main GI and perform the SUM(QtyRemaining) aggregation there instead of referencing a summarized GI. Once aggregation happens inside the same GI, null values will behave correctly and the 1.0 issue will disappear.

Got it...thanks!  The parent GI is already doing several things, so I was trying to simplify it by separating out the aggregated totals.  Otherwise, I’ll have to figure out how to add the Group By for the other columns and calculated values I’m using.

You could have just used the same DAC (INSiteStatus) you used for the QtyOnHand.  There are 2 columns to sum QtyProductionSupplyPrepared (orders that have a status of planned or on hold) or QtyProductionSupply (released or on process).  

Thanks!  I’ll check this out.