Pending RMA List

select ORDER_NUMBER ,
       (select ORGANIZATION_CODE from org_organization_definitions where ORGANIZATION_ID = oola.SHIP_FROM_ORG_ID) ORGANIZATION_CODE,
        (select ORGANIZATION_name from org_organization_definitions where ORGANIZATION_ID = oola.SHIP_FROM_ORG_ID) ORGANIZATION_name,
       oola.ORDERED_QUANTITY ,
       oola.FULFILLED_QUANTITY
from oe_order_headers_all ooha,
     oe_order_lines_all oola
where ooha.header_id = oola.header_id
and ooha.org_id = oola.org_id
and ORDER_CATEGORY_CODE = 'RETURN'
and oola.FLOW_STATUS_CODE = 'AWAITING_RETURN'
and oola.OPEN_FLAG = 'Y' --
order by 2
--and order_number = 3711800202

Item Reservations Query

SELECT   (select msib.segment1 || '|' || msib.segment2
         from mtl_system_items_b msib
         where msib.inventory_item_id = moq.inventory_item_id
               and msib.organization_id = moq.organization_id ) PART_NUMBER,
         moq.organization_id,
         ((SELECT   ORGANIZATION_code
                     FROM   org_organization_definitions odd
                    WHERE   organization_name LIKE 'UET%'
                            AND odd.organization_id = moq.organization_id)) Org_code,
        mr.reserve_quantity reserved_quantity,
         moq.transaction_quantity - mr.reserve_quantity available_toreserve_qty,
        moq.transaction_quantity        
  FROM   (  SELECT   moq.inventory_item_id inventory_item_id,
                     moq.organization_id organization_id,
                     SUM (moq.primary_transaction_quantity)
                        transaction_quantity
              FROM   mtl_onhand_quantities_detail moq
             WHERE   Moq.Organization_Id = nvl(:Organization_Id,Organization_Id)
                    -- AND moq.subinventory_code = 'XXXX'
          GROUP BY   moq.inventory_item_id, moq.organization_id) moq,
         (  SELECT   SUM (primary_reservation_quantity) reserve_quantity,
                     inventory_item_id,
                     organization_id
              FROM   Mtl_Reservations
             WHERE   Organization_Id = nvl(:Organization_Id,Organization_Id)
                   -- AND subinventory_code = ' XXXX'
          GROUP BY   Organization_Id, inventory_item_id) mr
 WHERE   moq.inventory_item_id = mr.inventory_item_id
         AND Moq.Organization_Id = Mr.Organization_Id
         and Moq.Organization_Id in (SELECT   ORGANIZATION_id
                                      FROM   org_organization_definitions odd
                                      WHERE   organization_name LIKE 'UET%')

INTERORG SHIPMENT Query

SELECT  
         ms.quantity,
         ms.receipt_date,
         ms.from_organization_id from_org,
         ms.to_organization_id to_org,
         ms.unit_of_measure uom,
         rsh.shipment_num,
         ms.item_id,
         mp1.organization_code from_org_code,
         mp2.organization_code to_org_code
    FROM
         mtl_supply ms,
         rcv_shipment_headers rsh,
         mtl_parameters mp1,
         mtl_parameters mp2
   WHERE
         ms.supply_type_code = 'SHIPMENT'
     AND ms.shipment_header_id = rsh.shipment_header_id
     AND (rsh.shipment_num = :p_shipment_no OR :p_shipment_no IS NULL)
     AND ms.from_organization_id = mp1.organization_id
     AND ms.to_organization_id = mp2.organization_id
ORDER BY receipt_date, mp2.organization_code