Oracle EBS SQL Query Generator for Items and WIP Jobs

SELECT msib.inventory_item_id,
       msib.segment1 AS item_number,
       msib.description,
       msib.primary_uom_code,
       msib.planning_make_buy_code,
       msib.inventory_item_status_code
FROM   mtl_system_items_b msib
WHERE  msib.organization_id = 207
AND    msib.segment1 LIKE 'PUMP%'
ORDER BY msib.segment1;

SELECT we.wip_entity_name AS job_name,
       msib.segment1 AS assembly,
       wdj.status_type,
       wdj.class_code,
       wdj.start_quantity,
       wdj.quantity_completed,
       wdj.quantity_scrapped,
       wdj.scheduled_start_date,
       wdj.scheduled_completion_date
FROM   wip_discrete_jobs wdj
JOIN   wip_entities we
       ON we.wip_entity_id = wdj.wip_entity_id
      AND we.organization_id = wdj.organization_id
JOIN   mtl_system_items_b msib
       ON msib.inventory_item_id = wdj.primary_item_id
      AND msib.organization_id = wdj.organization_id
WHERE  wdj.organization_id = 207
AND    wdj.status_type = 3
AND    msib.segment1 LIKE 'PUMP%'
AND    wdj.scheduled_start_date BETWEEN TO_DATE('2026-10-01', 'YYYY-MM-DD')
                                    AND TO_DATE('2026-12-31', 'YYYY-MM-DD')
ORDER BY wdj.scheduled_start_date;

SELECT msib.segment1 AS item_number,
       moqd.subinventory_code,
       SUM(moqd.transaction_quantity) AS on_hand_qty
FROM   mtl_onhand_quantities_detail moqd
JOIN   mtl_system_items_b msib
       ON msib.inventory_item_id = moqd.inventory_item_id
      AND msib.organization_id = moqd.organization_id
WHERE  moqd.organization_id = 207
AND    moqd.subinventory_code = 'STORES'
AND    msib.segment1 LIKE 'PUMP%'
GROUP BY msib.segment1, moqd.subinventory_code
ORDER BY msib.segment1;