PO_RECEIPT_TXN_Final_QRY

/* Formatted on 8/12/2011 11:02:52 PM (QP5 v5.115.810.9015) */
  SELECT distinct hou.name Operating_unit,
           ood.organization_name,
           pov.vendor_id,
           pov.segment1 vendor_code,
           pov.vendor_name,
           poh.po_header_id,
           rt.SHIPMENT_HEADER_ID,
           rt.SHIPMENT_LINE_ID,
           rsh.RECEIPT_NUM L_REFERENCE,
           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
   WHERE       rt.ORGANIZATION_ID = ood.ORGANIZATION_ID
           AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_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 ood.set_of_books_id = 5
          AND TRUNC (rt.transaction_date) BETWEEN '01-Aug-2011'
                                               AND  '13-Aug-2011'
           /*AND RT.TRANSACTION_ID IN (  SELECT   MAX (TRANSACTION_ID)
                                         FROM   RCV_TRANSACTIONS
                                     GROUP BY   SHIPMENT_LINE_ID) */
ORDER BY   TO_NUMBER (rsh.RECEIPT_NUM), pol.LINE_NUM