Query to get transactions of inventory items with available onhand quantity

SELECT   recs.doc_type, recs.item_number, recs.doc_no, recs.po_number,
         recs.organization_id, recs.inventory_item_id, recs.primary_quantity,
         recs.transaction_date
    FROM (
/* receipt transactions*/
          SELECT ‘2-REC’ doc_type, msi.segment1 item_number,
                 rsh.receipt_num doc_no, poh.segment1 po_number,
                 mmt.organization_id, mmt.inventory_item_id,
                 TO_NUMBER (NVL (mmt.primary_quantity, 0)) primary_quantity,
                 TRUNC (rt.transaction_date) transaction_date
            FROM inv.mtl_material_transactions mmt,
                 inv.mtl_system_items_b msi,
                 po.rcv_transactions rt,
                 po.rcv_shipment_headers rsh,
                 po.po_headers_all poh
           WHERE mmt.transaction_type_id IN (,,,) –Enter your transaction type ids
             AND mmt.inventory_item_id = msi.inventory_item_id
             AND mmt.organization_id = msi.organization_id
             AND mmt.primary_quantity > 0
             AND mmt.rcv_transaction_id = rt.transaction_id
             AND rt.shipment_header_id = rsh.shipment_header_id
             AND rt.po_header_id = poh.po_header_id
          UNION ALL
/*production transactions*/
          SELECT ‘1-PROD’ doc_type, msi.segment1 item_number,
                 we.wip_entity_name doc_no, ” po_number, mmt.organization_id,
                 mmt.inventory_item_id,
                 NVL (mmt.primary_quantity, 0) primary_quantity,
                 TRUNC (mmt.transaction_date) transaction_date
            FROM inv.mtl_material_transactions mmt,
                 inv.mtl_system_items_b msi,
                 wip.wip_discrete_jobs jobs,
                 wip.wip_entities we
           WHERE mmt.transaction_type_id IN (,,,) –Enter your transaction type ids
             AND mmt.inventory_item_id = msi.inventory_item_id
             AND mmt.organization_id = msi.organization_id
             AND mmt.transaction_source_id = jobs.wip_entity_id
             AND mmt.inventory_item_id = jobs.primary_item_id
             AND mmt.organization_id = jobs.organization_id
             AND jobs.wip_entity_id = we.wip_entity_id
             AND jobs.organization_id = we.organization_id
             AND mmt.primary_quantity > 0
/*adjustment transactions*/
          UNION ALL
          SELECT ‘3-ADJ’ doc_type, msi.segment1 item_number,
                 TO_CHAR (mmt.transaction_id) doc_no, ” po_number,
                 mmt.organization_id, mmt.inventory_item_id,
                 TO_NUMBER (NVL (mmt.primary_quantity, 0)) primary_quantity,
                 TRUNC (mmt.transaction_date) transaction_date
            FROM inv.mtl_material_transactions mmt,
                 inv.mtl_system_items_b msi
           WHERE mmt.transaction_type_id IN (,,,) –Enter your transaction type ids
             AND mmt.inventory_item_id = msi.inventory_item_id
             AND mmt.organization_id = msi.organization_id
             AND mmt.primary_quantity > 0) recs
   WHERE (SELECT SUM (motv.on_hand)
            FROM apps.mtl_onhand_total_v motv
           WHERE recs.inventory_item_id = motv.inventory_item_id
             AND recs.organization_id = motv.organization_id) > 0
     AND recs.organization_id = :p_org_id
     AND recs.inventory_item_id = :p_item_id