R12_PO_RECEIPT_TXN_Qry

/* Formatted on 24-09-2011 11:33:58 (QP5 v5.115.810.9015) */
  SELECT   DISTINCT
           hou.name Operating_unit,
           hou.organization_id,
           ood.organization_name,
           pov.segment1 vendor_code,
           pov.vendor_name,
           rsh.RECEIPT_NUM RECEIPT_NO,
           rsl.LINE_NUM RECEIPT_LINE_NO,
           rsh.SHIPMENT_NUM L_SHIPMENT_NUM,
           rt.TRANSACTION_TYPE L_TRANSACTION_TYPE,
           rt.TRANSACTION_DATE L_TRANSACTION_DATE,
           poh.SEGMENT1 L_PO_NUMBER,
           pol.LINE_NUM L_PO_LINE_NUM,
           rt.QUANTITY L_QUANTITY,
           rt.UNIT_OF_MEASURE L_UOM,
           (SELECT   segment1
              FROM   mtl_system_items_b
             WHERE   INVENTORY_ITEM_ID = pol.item_id
                     AND organization_id = ood.organization_id)
           L_ITEM_NUM,
           rt.SUBINVENTORY L_SUBINVENTORY,
           rt.DESTINATION_TYPE_CODE L_DESTINATION_TYPE,
           pol.ITEM_DESCRIPTION,
           loc.LOCATION_CODE L_SHIP_TO_LOCATION,
           rsh.SHIPMENT_NUM L_SHIPMENT_NUM
    FROM   rcv_transactions rt,
           org_organization_definitions ood,
           RCV_SHIPMENT_HEADERS RSH,
           po_headers_all poh,
           po_lines_all pol,
           HR_LOCATIONS_ALL_TL loc,
           po_line_locations_all poll,
           hr_operating_units hou,
           po_vendors pov,
           rcv_shipment_lines rsl
   WHERE       rt.ORGANIZATION_ID = ood.ORGANIZATION_ID
           AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
           AND rsh.SHIPMENT_HEADER_ID = RSl.SHIPMENT_HEADER_ID
           AND rsl.SHIPMENT_LINE_ID = rt.SHIPMENT_LINE_ID
           AND rt.PO_HEADER_ID = poh.PO_HEADER_ID
           AND poh.po_header_id = pol.po_header_id
           AND rt.po_line_id = pol.po_line_id
           AND poll.PO_HEADER_ID = poh.PO_HEADER_ID
           AND poll.po_line_id = pol.po_line_id
           AND poll.SHIP_TO_LOCATION_ID = loc.LOCATION_ID
           AND hou.organization_id = ood.operating_unit
           AND pov.vendor_id = poh.vendor_id
           and rsl.TO_ORGANIZATION_ID=ood.ORGANIZATION_ID
           AND ood.set_of_books_id = 5
           AND RT.TRANSACTION_TYPE = 'DELIVER'
           /*AND TRUNC (rt.transaction_date) BETWEEN '01-Apr-2011'
                                               AND  '13-Aug-2011' */
           /*AND RT.TRANSACTION_ID IN (  SELECT   MAX (TRANSACTION_ID)
                                         FROM   RCV_TRANSACTIONS
                                     GROUP BY   SHIPMENT_LINE_ID) */
           AND LENGTH (rsh.RECEIPT_NUM) = 9
ORDER BY   TO_NUMBER (rsh.RECEIPT_NUM),rsl.LINE_NUM, pol.LINE_NUM