IRISO Status Query Required in Oracle EBS R12

 Item_Description, Requisition QTY Ordered, Reqisition qty Received, Intransit Qty, SO Ordered qty, SO Shipped qty, SO cancelled qty.

SELECT prha.segment1 IR_NUMBER, ooh.ORDER_NUMBER, msi.segment1 item,

      msi.description,

      prla.quantity Req_Qty_Order,

      prla.quantity_received Req_qty_Received,  

     (SELECT quantity FROM mtl_supply

      WHERE req_header_id =

        (SELECT REQUISITION_HEADER_ID FROM po_requisition_headers_all

         WHERE segment1 = &&value1

         AND creation_date = (SELECT MAX (creation_date)

                              FROM po_requisition_headers_all

                              WHERE segment1 = &&value1))

         AND supply_type_code = 'SHIPMENT') Intransit_Qty, 

      prla.quantity_delivered,

      ool.ordered_quantity SO_Qty_Ordered,

      ool.shipped_quantity SO_Qty_Shipped,

      ool.cancelled_quantity SO_Qty_Cancelled

FROM po_requisition_headers_all prha,

     po_requisition_lines_all prla,

     mtl_system_items_b msi,

     oe_order_headers_all ooh,

     oe_order_lines_all ool,

     org_organization_definitions org

WHERE  prha.requisition_header_id = prla.requisition_header_id

   AND prha.type_lookup_code = 'INTERNAL'

   AND msi.inventory_item_id = prla.item_id

   AND prla.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID

   AND ooh.header_id = ool.header_id

   AND org.ORGANIZATION_ID = ool.SHIP_FROM_ORG_ID

   AND ooh.source_document_id = prha.requisition_header_id

   AND prla.REQUISITION_LINE_ID = ool.source_document_line_id

   AND prha.authorization_status LIKE 'APPROVED'

   AND prha.segment1 = number_to_be_given;