Available Inventory

Available inventory queries are typically executed for a particular production unit and represent the amount of material that is available to be consumed on that production unit.

The query below is an example of a query for number of parts that are available based on the assumption that each production event represents a single part. Since each production is an individual part, we do not need to incorporate the quantity of the production event itself.

SELECT	COUNT(e.Event_Id) 
FROM Events e
JOIN Production_Status ps ON ps.ProdStatus_Id = e.Event_Status 
WHERE	e.PU_Id = 6
        AND ps.Count_For_Inventory = 1

For production events that can have partial quantities (for example, roll of paper, batch of liquid, basket of parts, and so on), we need to incorporate the dimension of the event itself, along with any waste and consumed quantities (via genealogy). The query below is an example of a query for available production events and their respective available quantities.

SELECT	e.Event_Num, 
        e.TimeStamp,
        ISNULL(ed.Initial_Dimension_X, 0)
                  -SUM(ISNULL(ec.Dimension_X,0))
	          -SUM(ISNULL(wed.Amount,0))	AS ‘Final_Dimension_X’
FROM Events e
JOIN Production_Status ps ON ps.ProdStatus_Id = e.Event_Status 
LEFT JOIN Event_Details ed ON ed.Event_Id = e.Event_Id
LEFT JOIN Event_Components ec ON e.Event_Id = ec.Source_Event_Id
LEFT JOIN Waste_Event_Details wed ON	wed.PU_Id = e.PU_Id	-- utilizes clustered index
                                        AND wed.Event_Id = e.Event_Id
WHERE	e.PU_Id = 6
        AND ps.Count_For_Inventory = 1	-- Filters out non-inventory production events
GROUP BY	e.Event_Num,	-- Allows the summarization of wate and component dimensions                 ed.Initial_Dimension_X,
                e.TimeStamp
HAVING (ISNULL(ed.Initial_Dimension_X, 0)	-- Filters out events that have 0 available quantity
               -SUM(ISNULL(ec.Dimension_X,0))

The above query recalculates Final_Dimension_X for each of the queried production events. If Final_Dimension_X is dynamically calculated as each waste and/or component record is created the query could be simplified to the following:

SELECT	e.Event_Num, 
            e.TimeStamp, 
            ed.Final_Dimension_X
FROM Events e
JOIN Production_Status ps ON ps.ProdStatus_Id = e.Event_Status 
LEFT JOIN Event_Details ed ON ed.Event_Id = e.Event_Id
WHERE	e.PU_Id = 6
        AND ps.Count_For_Inventory = 1 
        AND ed.Final_Dimension_X > 0

However, as previously mentioned, there is no default functionality to dynamically calculate Final_Dimension_X so it would require custom calculations and/or consumption models to determine the correct number.