Query to find ISO Shipment Details in EBS R12

 /*Query to find ISO Shipment Details in EBS R12*/

SELECT (SELECT name
        FROM   hr_organization_units hou
        WHERE  hou.organization_id = ooha.org_id)
       From_Organization,
       (SELECT Max(transfer_organization_id)
        FROM   mtl_material_transactions mmt
        WHERE  mmt.transaction_type_id = 62 --Int Order Intr Ship
               AND mmt.transaction_action_id = 21 --Intransit shipment
               AND mmt.transaction_source_type_id = 8 --Internal order
               AND mmt.source_code = 'ORDER ENTRY'
               AND mmt.shipment_number = wnd.name
               AND mmt.trx_source_delivery_id = wnd.delivery_id
               AND mmt.organization_id = wnd.organization_id)
       TRANSFER_ORGANIZATION_ID,
       (SELECT pla.organization_id
        FROM   po_location_associations_all pla,
               hz_cust_acct_sites_all hcas,
               hz_cust_site_uses_all hcsua
        WHERE  pla.address_id = hcas.cust_acct_site_id
               AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
               AND ooha.ship_to_org_id = hcsua.site_use_id)
       TRANSFER_ORGANIZATION_ID2,
       ooha.order_number,
       ooha.ordered_date,
       (SELECT hp.party_name
        FROM   hz_parties hp,
               hz_cust_accounts hca
        WHERE  hp.party_id = hca.party_id
               AND hca.cust_account_id = ooha.sold_to_org_id)
       CUSTOMER_NAME,
       --wdd.DELIVERY_DETAIL_ID ,   wda.delivery_detail_id,
       wnd.delivery_id
       shipment_number,
Nvl(Nvl(oola.actual_shipment_date, oola.actual_fulfillment_date), wnd.confirm_date) shipping_date,
oola.ordered_item item,
msib.description,
oola.pricing_quantity Quantity,
oola.pricing_quantity_uom UOM,
oola.unit_selling_price,
(SELECT item_cst.item_cost item_average_cost
 FROM   cst_item_costs item_cst
 WHERE  item_cst.cost_type_id = 2 --(select cost_type.cost_type_id from cst_cost_types cost_type where cost_type.cost_type = 'Average')
        AND item_cst.inventory_item_id = msib.inventory_item_id
        AND item_cst.organization_id = msib.organization_id)
       avg_cost,
wdd.shipped_quantity,
(SELECT SUM(rcv.quantity)
 FROM   rcv_shipment_headers rsh,
        rcv_shipment_lines rsl,
        rcv_transactions rcv
 WHERE  rsh.shipment_header_id = rsl.shipment_header_id
        AND rcv.transaction_type = 'RECEIVE'
        AND rcv.shipment_line_id = rsl.shipment_line_id
        AND rcv.shipment_header_id = rsl.shipment_header_id
        AND rsl.item_id = msib.inventory_item_id
        AND rsl.from_organization_id = ooha.ship_from_org_id
        AND rsh.shipment_num = wnd.name)
       received_qty,
(SELECT rsh.receipt_num
 FROM   rcv_shipment_headers rsh
 WHERE  rsh.receipt_source_code = 'INTERNAL ORDER'
        AND rsh.shipment_num = wnd.name
        AND rsh.organization_id = ooha.ship_from_org_id)
       RECEIPT_NUM
FROM   oe_order_headers_all ooha,
       oe_order_lines_all oola,
       wsh_new_deliveries wnd,
       wsh_delivery_assignments wda,
       wsh_delivery_details wdd,
       mtl_system_items_b msib
WHERE  1 = 1
       AND oola.ordered_item = msib.segment1
       AND ooha.ship_from_org_id = msib.organization_id
       AND wdd.source_header_id = ooha.header_id
       AND wdd.source_line_id = oola.line_id
       AND ooha.header_id = oola.header_id
       AND wdd.delivery_detail_id = wda.delivery_detail_id
       AND wnd.delivery_id = wda.delivery_id
       AND ooha.order_source_id = (SELECT order_source_id
                                   FROM   oe_order_sources
                                   WHERE  name = 'Internal') --10
       --and ooha.ORDER_NUMBER='1051022248'
       AND wnd.delivery_id = 15587613--15589119
;