dataexport: 28
This data as json
| id | dataQuery | dateCreated | dateLastModified | description | lastChangedUserId | name |
|---|---|---|---|---|---|---|
| 28 | SELECT l1.finished_good, l1.finished_good_description, l1.component_part_number, l1.component_description, l1.component_uom, l1.qty_per_unit, l1.make_buy, l1.qty_on_hand, l1.qty_on_order, 'Level 1' AS bom_level FROM ( SELECT b.num AS finished_good, p_fg.description AS finished_good_description, p_comp.num AS component_part_number, p_comp.description AS component_description, u_part.name AS component_uom, CASE WHEN bi.uomId = p_comp.uomId THEN bi.quantity ELSE bi.quantity * ( SELECT uc.multiply / uc.factor FROM uomconversion uc WHERE uc.fromUomId = bi.uomId AND uc.toUomId = p_comp.uomId LIMIT 1 ) END AS qty_per_unit, JSON_UNQUOTE(JSON_EXTRACT(p_comp.customFields, '$.\"42\".value')) AS make_buy, MAX(qt.QTYONHAND) AS qty_on_hand, MAX(qt.QTYONORDER) AS qty_on_order FROM bom b JOIN part p_fg ON p_fg.num = b.num JOIN bomitem bi ON bi.bomid = b.id JOIN part p_comp ON p_comp.id = bi.partid JOIN parttype pt ON pt.id = p_comp.typeid JOIN uom u_part ON u_part.id = p_comp.uomId LEFT JOIN qtyinventorytotals qt ON qt.PARTID = p_comp.id WHERE b.num IN ('06330','04777','06463','06464','06465','06476','05899', '06495','05828','05790','16645','06470','16646','06426','06355', '06414','03606','03612','03611','03839','01195','06062','AB1069', '06483','06490','06480','05930','04220','06251','04535','06500', '06446','05931','00826','AB1331','AB2357','AB1089','AB1170','05929', '00513','AB1194','AB2335','06218','06509','AB1151','00290','06167', '01181','AB1135','06204','AB1077','06347','00514','AB1125','AB1136', '06506','AB1808','AB1583','AB1757','00172','AB1398','AB1327','06473', '05825','00836','03991','04235','04233','04232','04890','00846', '06040','00011','01038','02417','05935','04562','00499','AB1977', 'AB1003','06494','AB1045','AB1579','AB1306','AB1412','AB1673', 'AB1161','AB1232','02416','AB1008','AB1131','AB1153','AB1216', 'AB1191','AB1409','00632','01106','AB1833','AB1367','AB1617', 'AB1152','AB1148','06261','06519','06520','01180','AB1234','AB1142', '06429','AB1438','03987','AB1009','AB1875','AB1192','AB1710', '06139','00505','AB1714','AB1629','AB1094','04529','00009','AB1129', '06514','06515','06516','06517','00137','AB1415','AB1193','AB1972', 'AB1119','AB1123','AB1573','05934','04460','00963','AB1292','AB1445', '00291','06166','06169','06168','06014','06522','06521','AB1205', 'AB1414','AB2375','02830','06435','AB1149','AB1266','AB1122','00509', '00157','AB1527','AB2230','00496','04528','00502','AB1206','AB1697', 'AB1018','06508','00503','02414','00292','AB1240','AB1485','AB1482', '03605','AB2098','AB1218','AB1021','00617','AB1672','AB1172', 'AB1144','AB1007') AND p_comp.activeFlag = 1 AND pt.id NOT IN (20, 21, 22, 60) AND p_comp.num != b.num AND NOT EXISTS ( SELECT 1 FROM bom b_sub WHERE b_sub.num = p_comp.num ) GROUP BY b.num, p_fg.description, p_comp.num, p_comp.description, p_comp.uomId, u_part.name, bi.uomId, bi.quantity, p_comp.customFields ) l1 UNION ALL SELECT l2.finished_good, l2.finished_good_description, l2.component_part_number, l2.component_description, l2.component_uom, l2.qty_per_unit, l2.make_buy, l2.qty_on_hand, l2.qty_on_order, 'Level 2' AS bom_level FROM ( SELECT b1.num AS finished_good, p_fg.description AS finished_good_description, p_comp2.num AS component_part_number, p_comp2.description AS component_description, u_part2.name AS component_uom, CASE WHEN bi1.uomId = p_comp1.uomId THEN bi1.quantity ELSE bi1.quantity * ( SELECT uc.multiply / uc.factor FROM uomconversion uc WHERE uc.fromUomId = bi1.uomId AND uc.toUomId = p_comp1.uomId LIMIT 1 ) END * CASE WHEN bi2.uomId = p_comp2.uomId THEN bi2.quantity ELSE bi2.quantity * ( SELECT uc.multiply / uc.factor FROM uomconversion uc WHERE uc.fromUomId = bi2.uomId AND uc.toUomId = p_comp2.uomId LIMIT 1 ) END AS qty_per_unit, JSON_UNQUOTE(JSON_EXTRACT(p_comp2.customFields, '$.\"42\".value')) AS make_buy, MAX(qt2.QTYONHAND) AS qty_on_hand, MAX(qt2.QTYONORDER) AS qty_on_order FROM bom b1 JOIN part p_fg ON p_fg.num = b1.num JOIN bomitem bi1 ON bi1.bomid = b1.id JOIN part p_comp1 ON p_comp1.id = bi1.partid JOIN parttype pt1 ON pt1.id = p_comp1.typeid JOIN bom b2 ON b2.num = p_comp1.num JOIN bomitem bi2 ON bi2.bomid = b2.id JOIN part p_comp2 ON p_comp2.id = bi2.partid JOIN parttype pt2 ON pt2.id = p_comp2.typeid JOIN uom u_part2 ON u_part2.id = p_comp2.uomId LEFT JOIN qtyinventorytotals qt2 ON qt2.PARTID = p_comp2.id WHERE b1.num IN ('06330','04777','06463','06464','06465','06476','05899', '06495','05828','05790','16645','06470','16646','06426','06355', '06414','03606','03612','03611','03839','01195','06062','AB1069', '06483','06490','06480','05930','04220','06251','04535','06500', '06446','05931','00826','AB1331','AB2357','AB1089','AB1170','05929', '00513','AB1194','AB2335','06218','06509','AB1151','00290','06167', '01181','AB1135','06204','AB1077','06347','00514','AB1125','AB1136', '06506','AB1808','AB1583','AB1757','00172','AB1398','AB1327','06473', '05825','00836','03991','04235','04233','04232','04890','00846', '06040','00011','01038','02417','05935','04562','00499','AB1977', 'AB1003','06494','AB1045','AB1579','AB1306','AB1412','AB1673', 'AB1161','AB1232','02416','AB1008','AB1131','AB1153','AB1216', 'AB1191','AB1409','00632','01106','AB1833','AB1367','AB1617', 'AB1152','AB1148','06261','06519','06520','01180','AB1234','AB1142', '06429','AB1438','03987','AB1009','AB1875','AB1192','AB1710', '06139','00505','AB1714','AB1629','AB1094','04529','00009','AB1129', '06514','06515','06516','06517','00137','AB1415','AB1193','AB1972', 'AB1119','AB1123','AB1573','05934','04460','00963','AB1292','AB1445', '00291','06166','06169','06168','06014','06522','06521','AB1205', 'AB1414','AB2375','02830','06435','AB1149','AB1266','AB1122','00509', '00157','AB1527','AB2230','00496','04528','00502','AB1206','AB1697', 'AB1018','06508','00503','02414','00292','AB1240','AB1485','AB1482', '03605','AB2098','AB1218','AB1021','00617','AB1672','AB1172', 'AB1144','AB1007') AND p_comp1.activeFlag = 1 AND p_comp2.activeFlag = 1 AND pt1.id NOT IN (20, 21, 22, 60) AND pt2.id NOT IN (20, 21, 22, 60) AND p_comp1.num != b1.num AND p_comp2.num != b2.num AND p_comp2.num != b1.num GROUP BY b1.num, p_fg.description, p_comp2.num, p_comp2.description, p_comp2.uomId, u_part2.name, bi1.uomId, bi1.quantity, bi2.uomId, bi2.quantity, p_comp2.customFields ) l2 ORDER BY finished_good, bom_level, component_part_number; | 2026-04-20 14:32:06.897000 | 2026-04-20 14:32:06.897000 | Change assembly numbers whereever b.num in is used to get data | 51 | Part_Data_Based_On_Assemblies |