PROBLEM: We needed a report that will show, based on WO number all the parts that are needed and the leg they are on. Hunting parts on a scheduling window is hard, especially if the same part is on different legs!
SOLUTION: Use the following SQL. Drop it into a view after editing out the last line and use in Synoptix.
/*AUTH:MMKING
DATE:20160922
STATUS:KNOWN GOOD
PURPOSE:Given a WO# at end of sql, SHOW ME THE PARTS AND THE LEGS.
NOTES:
*/
select a.workorder_base_id AS "WORK_ORDER",
a.workorder_lot_id AS "LOT",
a.workorder_split_id AS "SPLIT",
a.workorder_sub_id AS "SUB",
a.operation_seq_no AS "SEQ" ,
a.part_id AS "PART",
b.stock_um AS "UOM",
a.qty_per AS "QTY_PER",
b.description AS "PART_DESCRIPTION",
c.resource_id AS "RESOURCE",
cast(substring(coalesce(d.bits,' '),1,7) as char) AS "PACKET OR STEP",
coalesce(d.bits,' ') AS "PACKET OR STEP DETAIL"
from requirement a,
part b,
operation c
left outer join operation_binary d
on d.workorder_base_id = c.workorder_base_id
and d.workorder_lot_id = c.workorder_lot_id
and d.workorder_split_id = c.workorder_split_id
and d.workorder_sub_id = c.workorder_sub_id
and d.sequence_no = c.sequence_no
where a.part_id = b.id
and a.workorder_base_id = c.workorder_base_id
and a.workorder_lot_id = c.workorder_lot_id
and a.workorder_split_id = c.workorder_split_id
and a.workorder_sub_id = c.workorder_sub_id
and a.operation_seq_no = c.sequence_no
and a.workorder_base_id = 'WO_XYZ123'
