Oracle EBS - SQL Query to get Open Intransit Shipment Transaction Details

 SELECT

    xx.supply_source_id,

    msib.segment1        "Item",

    mp.organization_code,

    xx.po_num            intransit_po_num,

    xx.shipment_num      intransit_shipment_num,

    xx.line_num,

    xx.quantity_shipped,

    xx.quantity_received,

    msib.organization_id,

    msib.purchasing_item_flag,

    msib.shippable_item_flag,

    msib.inventory_item_flag,

    lot_control_code,

    decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

           5, 'At Receipt', 6,

           'At Issue')   "Serial Control",

    mc.segment1          "PPL",

    mc.segment2          "SPL",

    mc.segment3          "ITEM CATEGORY"

FROM

    mtl_system_items_b   msib,

    mtl_item_categories  mic,

    mtl_categories       mc,

    mtl_parameters       mp,

    (

        SELECT

            ms.supply_source_id,

            pla.item_id,

            ms.to_organization_id,

            pha.segment1 po_num,

            rcv.shipment_num,

            rsl.line_num,

            rsl.quantity_shipped,

            rsl.quantity_received

        FROM

            mtl_supply             ms,

            po_headers_all         pha,

            po_lines_all           pla,

            po_line_locations_all  plla,

            rcv_shipment_headers   rcv,

            rcv_shipment_lines     rsl

        WHERE

                ms.destination_type_code = 'INVENTORY'

            AND ms.supply_type_code = 'SHIPMENT'

            AND ms.po_header_id = pha.po_header_id

            AND pha.po_header_id = pla.po_header_id

            AND ms.po_line_id = pla.po_line_id

            AND plla.po_header_id = pha.po_header_id

            AND plla.po_line_id = pla.po_line_id

            AND plla.line_location_id = ms.po_line_location_id

            AND ms.to_organization_id = plla.ship_to_organization_id

            AND ms.shipment_header_id = rcv.shipment_header_id

            AND rcv.shipment_header_id = rsl.shipment_header_id

            AND pla.item_id = rsl.item_id

            AND ms.to_organization_id IN ( 85, 97 )

            

--AND ms.PO_HEADER_id=23372233

--AND ms.PO_LINE_ID=23856916 

    )                    xx

WHERE

        msib.inventory_item_id = mic.inventory_item_id

    AND msib.organization_id = mic.organization_id

    AND msib.organization_id = mp.organization_id

    AND mic.category_id = mc.category_id

    AND mic.category_set_id = 1

    AND mc.structure_id = 101

    AND msib.enabled_flag = 'Y'

    AND msib.lot_control_code = 1

    AND nvl(msib.end_date_active, sysdate) >= sysdate

    AND mc.disable_date IS NULL

    AND mp.organization_code IN ('MAS') -- Organization Code

    AND msib.item_type='P'

    AND xx.item_id = msib.inventory_item_id --item id link

    AND xx.to_organization_id = msib.organization_id --org id link 

ORDER BY

    mp.organization_code,

    xx.po_num,

    mc.segment1