iMES Data Sets for Shift Handover

Four iMES data sets reach Shift Handover. Each Kafka message carries its queryId and tableName so the consumer can tell them apart.

Data sets

Data set	`queryId`	`tableName`	Parameters	Work centers
Order Details	`mfg-order-details`	`t_Mfg_Order_Output`	None (top 5 by status, then planned start)	B4, C7, PM02, PM04 (fixed list)
Machine Time	`machine-time`	`v_Mfg_Machine_Time_All`	`burdenCenter`, `lookbackHours`	For example PM02, B4
In Loads	`material-load`	`v_Material_In_Load`	`workCenterCode`, `mmmFacilityCode`, `lookbackHours` (or `analyticalFamilyCode`)	For example PM04, or family B4
Waste	`material-waste`	`v_Material_Waste`	Same as In Loads	Same as In Loads

Columns returned

The column aliases are the field names in each message row and the columns offered for IMES widgets.

Data set	Columns
Order Details	Order Number, Order Status, Desc, Product \| Process Spec, Order Qty, Produced Qty, In Load Qty, Waste Qty, Hrs Remaining, Planned Start
Machine Time	Order Number, MMM Number, Order Status, Start Time, End Time, Description, Actual Hours. End Time is NULL for an open entry, and its Actual Hours is measured against now.
In Loads	Order Number, MMM Number, Description, Created, Load ID, QC Status, Qty, UofM
Waste	Order Number, MMM Number, Created, Waste Code, Waste Desc, Qty, UofM

SQL

MySQL. Bound parameters are shown as ?, in the order listed in each graph's parameterOrder.

Machine Time, parameters burdenCenter, lookbackHours:

SELECT
  Order_Nbr AS "Order Number", mmm_id_nbr AS "MMM Number", Status AS "Order Status",
  start_datetime AS "Start Time", end_datetime AS "End Time", Action_Desc AS "Description",
  CASE WHEN end_datetime IS NULL
    THEN ROUND(TIMESTAMPDIFF(SECOND, start_datetime, NOW()) / 3600, 1)
    ELSE ROUND(Actual_Hrs, 1) END AS "Actual Hours"
FROM v_Mfg_Machine_Time_All
WHERE Burden_Center = ?
  AND COALESCE(end_datetime, NOW()) >= NOW() - INTERVAL ? HOUR

In Loads, parameters workCenterCode, mmmFacilityCode, lookbackHours:

SELECT m.Mfg_Order_Nbr AS "Order Number", im.MMM_Id_Nbr AS "MMM Number", im.Item_Desc AS "Description",
  mil.create_datetime AS "Created", mil.load_id AS "Load ID", mil.qc_status_desc AS "QC Status",
  mil.material_in_load_qty AS "Qty", mil.reporting_uom AS "UofM"
FROM v_Material_In_Load mil
JOIN t_Material m ON m.Material_Id = mil.material_id
JOIN t_Item_Master im ON im.MMM_Id_Nbr = m.MMM_Id_Nbr
WHERE m.Work_Center_Code = ? AND im.MMM_Facility_Code = ?
  AND mil.create_datetime >= NOW() - INTERVAL ? HOUR
-- family variant (B4/C7): WHERE im.Analytical_Family_Code = ? (parameters: analyticalFamilyCode, lookbackHours)

Waste (partial description; full SQL not supplied): the same joins and filters as In Loads, from v_Material_Waste mw, selecting mw.mfg_order_nbr, mw.mmm_id_nbr, m.Row_Insert_Datetime, mw.waste_cause_code, mw.waste_cause_desc, mw.waste_qty and mw.reporting_uom, filtered on m.Row_Insert_Datetime >= NOW() - INTERVAL ? HOUR.

Order Details (partial description; full SQL not supplied): the SQL is built for each work center, and the work-center value is looked up from a fixed allow-list (B4, C7, PM02, PM04), never taken from input. Hours remaining is derived as follows:

Work center	Derived from
B4	Product spec Width property, fixed line speed
C7	Product spec Width and process variable LINE_SPEED_SP
PM02, PM04	Process variable ROTOFORMER SPEED

Tables and views the read-only account needs

t_Mfg_Order_Output, t_Mfg_Order_Operation, t_Item_Master, t_Material, t_Product_Specification, t_Property_Specification, t_Property, t_Process_Specification, t_Process_Variable_Spec, t_Process_Variable_Name, t_Proc_Spec_Item_Master_Xref, v_Material_In_Load, v_Material_Waste, v_Mfg_Machine_Time_All