RMA Pending ( Awaiting Return )

 SELECT   (SELECT   ORDER_NUMBER
            FROM   OE_ORDER_HEADERS_ALL OOHA
           WHERE   OOHA.HEADER_ID = OOLA.HEADER_ID) RMA_ORDER_NUMBER,
          (SELECT   to_char(CREATION_DATE,'DD-MON-YY')
            FROM   OE_ORDER_HEADERS_ALL OOHA
           WHERE   OOHA.HEADER_ID = OOLA.HEADER_ID) RMA_CREATION_DATE,
          (SELECT   ORDER_NUMBER
            FROM   OE_ORDER_HEADERS_ALL OOHA
           WHERE   OOHA.HEADER_ID = oola.RETURN_ATTRIBUTE1) Sales_order_NUMBER,
         RETURN_REASON_CODE ,
         FLOW_STATUS_CODE,
         ORDERED_ITEM,
         (select description from mtl_system_items_b where inventory_item_id = oola.INVENTORY_ITEM_ID and ORGANIZATION_ID = oola.SHIP_FROM_ORG_ID ) item_description,
         ORDERED_QUANTITY ,
         (select PARTY_NAME from hz_parties where party_id = oola.invoice_to_org_id ) customer
  FROM   OE_ORDER_LINES_ALL OOLA
 WHERE   FLOW_STATUS_CODE = 'AWAITING_RETURN'
 and     SHIP_FROM_ORG_ID = nvl(:org_id,SHIP_FROM_ORG_ID)