Production Event Product

Normally, the product a production event is associated with is the product change record that corresponds to the event’s (described in more detail in section 10.01.01). However, a production event can also be individually allocated to a particular product. The event specific product is stored in the Applied_Product field in the Events table. For example, a line is running Grade A. The operators make product change to Grade A. However, quality tests reveal that one of the production events has failed the specifications for Grade A but meet the specifications for Grade B. Rather than rejecting the event or creating a waste record, the operator can choose to apply the Grade B product to the particular event in question.

To find the product associated with an event the query should include a reference to the Applied_Product for the event. For example,

SELECT	e.Event_Num, 
        e.TimeStamp, 
        p.Prod_Code
FROM Events e
      INNER JOIN Production_Starts ps ON e.PU_Id = ps.PU_Id
                                         AND e.TimeStamp > ps.Start_Time 
                                         AND (	e.TimeStamp <= ps.End_Time
                                                 OR ps.End_Time IS NULL)
 
-- NOTE: the following includes a COALESCE() function so the join will reference the Applied_Product field if it exists, otherwise it will reference the normal product
         INNER JOIN Products p ON p.Prod_Id = coalesce(e.Applied_Product, ps.Prod_Id) 
WHERE	         e.PU_Id = @PUId
                 AND e.TimeStamp > @ReportStartTime 
                 AND e.TimeStamp <= @ReportEndTime