Oracle Purchase Receipts Query

Oracle Purchase Receipts Query:


/* Formatted on 12/28/2016 8:49:32 AM (QP5 v5.126.903.23003) */
SELECT   RCV_TRANSACTIONS.TRANSACTION_ID,
         PO_HEADERS_ALL.SEGMENT1,
         RCV_TRANSACTIONS.VENDOR_ID,
         RCV_TRANSACTIONS.VENDOR_SITE_ID,
         RCV_TRANSACTIONS.CURRENCY_CONVERSION_RATE,
         PO_LINE_LOCATIONS_ALL.CREATION_DATE,
         RCV_SHIPMENT_LINES.ITEM_ID,
         PO_LINES_ALL.UNIT_PRICE,
         PO_LINES_ALL.LINE_NUM,
         RCV_TRANSACTIONS.ORGANIZATION_ID,
         PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM,
         PO_LINE_LOCATIONS_ALL.ORG_ID,
         RCV_TRANSACTIONS.DELIVER_TO_PERSON_ID,
         RCV_TRANSACTIONS.INV_TRANSACTION_ID,
         RCV_TRANSACTIONS.TRANSACTION_TYPE,
         NVL (RCV_TRANSACTIONS.LOCATION_ID,
              PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID)
            LOCATION_ID,
         RCV_TRANSACTIONS.ORGANIZATION_ID,
         RCV_TRANSACTIONS.CREATED_BY,
         RCV_TRANSACTIONS.LAST_UPDATED_BY,
         DECODE (RCV_TRANSACTIONS.TRANSACTION_TYPE,
                 'MATCH', PARENT.TRANSACTION_DATE,
                 RCV_TRANSACTIONS.TRANSACTION_DATE)
            TRANSACTION_DATE,
         RCV_TRANSACTIONS.CREATION_DATE,
         RCV_TRANSACTIONS.LAST_UPDATE_DATE,
         RCV_TRANSACTIONS.QUANTITY,
         RCV_TRANSACTIONS.UNIT_OF_MEASURE,
         RCV_TRANSACTIONS.SOURCE_DOC_UNIT_OF_MEASURE,
         CASE
            WHEN RCV_TRANSACTIONS.TRANSACTION_TYPE = 'RECEIVE'
            THEN
               RCV_SHIPMENT_LINES.QUANTITY_RECEIVED
            WHEN RCV_TRANSACTIONS.TRANSACTION_TYPE = 'MATCH'
            THEN
               RCV_SHIPMENT_LINES.QUANTITY_RECEIVED
            ELSE
               RCV_TRANSACTIONS.SOURCE_DOC_QUANTITY
         END
            SOURCE_DOC_QUANTITY,
         RCV_TRANSACTIONS.PO_UNIT_PRICE,
         RCV_TRANSACTIONS.CURRENCY_CODE,
         RCV_TRANSACTIONS.INSPECTION_STATUS_CODE,
         RCV_TRANSACTIONS.COMMENTS,
         RCV_TRANSACTIONS.REASON_ID,
         RCV_SHIPMENT_HEADERS.RECEIPT_NUM,
         RCV_SHIPMENT_LINES.LINE_NUM,
         RCV_TRANSACTIONS.CURRENCY_CONVERSION_DATE,
         PO_HEADERS_ALL.AGENT_ID,
         CONTRACT.SEGMENT1,
         RCV_TRANSACTIONS.PO_LINE_LOCATION_ID,
         RCV_TRANSACTIONS.EMPLOYEE_ID,
         PO_LINES_ALL.LAST_UPDATE_DATE,
         PO_LINE_LOCATIONS_ALL.LAST_UPDATE_DATE,
         RCV_SHIPMENT_HEADERS.LAST_UPDATE_DATE,
         PO_HEADERS_ALL.LAST_UPDATE_DATE,
         PO_LINES_ALL.LINE_TYPE_ID,
         RCV_SHIPMENT_LINES.CATEGORY_ID,
         RCV_TRANSACTIONS.AMOUNT,
         PO_LINE_LOCATIONS_ALL.PAYMENT_TYPE,
         PO_LINE_LOCATIONS_ALL.SHIPMENT_TYPE,
         CASE
            WHEN PO_HEADERS_ALL.TYPE_LOOKUP_CODE = 'BLANKET'
            THEN
               PO_HEADERS_ALL.SEGMENT1
            WHEN BLANKET_AGREEMENT.TYPE_LOOKUP_CODE = 'BLANKET'
            THEN
               BLANKET_AGREEMENT.SEGMENT1
            ELSE
               NULL
         END,
         PO_LINE_LOCATIONS_ALL.CONSIGNED_FLAG,
         '0' AS X_CUSTOM,
         NVL (PARENT.TRANSACTION_TYPE, 0) PARENT_TRANSACTION_TYPE,
         PO_LINE_LOCATIONS_ALL.DAYS_EARLY_RECEIPT_ALLOWED
            DAYS_EARLY_RECEIPT_ALLOWED,
         PO_LINE_LOCATIONS_ALL.DAYS_LATE_RECEIPT_ALLOWED
            DAYS_LATE_RECEIPT_ALLOWED,
         PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,
         PO_LINE_LOCATIONS_ALL.PROMISED_DATE,
         PO_REQUISITION_LINES_ALL.NEED_BY_DATE REQUISITION_NEED_BY_DATE,
         RCV_TRANSACTIONS.SOURCE_DOCUMENT_CODE,
         PO_RELEASES_ALL.RELEASE_NUM,
         RCV_SHIPMENT_LINES.UNIT_OF_MEASURE RCV_UOM
  FROM   PO_HEADERS_ALL,
         PO_LINES_ALL,
         PO_RELEASES_ALL,
         PO_LINE_LOCATIONS_ALL,
         RCV_TRANSACTIONS,
         RCV_SHIPMENT_LINES,
         RCV_SHIPMENT_HEADERS,
         PO_HEADERS_ALL CONTRACT,
         PO_HEADERS_ALL BLANKET_AGREEMENT,
         PO_REQUISITION_LINES_ALL,
         RCV_TRANSACTIONS PARENT
 WHERE   RCV_TRANSACTIONS.PO_LINE_LOCATION_ID =
               PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID(+)
         AND PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID =
               PO_RELEASES_ALL.PO_RELEASE_ID(+)
         AND RCV_TRANSACTIONS.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+)
         AND RCV_TRANSACTIONS.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+)
         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
               RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
               RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID
         AND RCV_TRANSACTIONS.SHIPMENT_LINE_ID =
               RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID
         AND PO_LINES_ALL.CONTRACT_ID = CONTRACT.PO_HEADER_ID(+)
         AND PO_LINES_ALL.FROM_HEADER_ID = BLANKET_AGREEMENT.PO_HEADER_ID(+)
         AND RCV_TRANSACTIONS.REQUISITION_LINE_ID =
               PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID(+)
         AND RCV_TRANSACTIONS.TRANSACTION_TYPE IN
                  ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT', 'MATCH')
         AND RCV_TRANSACTIONS.PARENT_TRANSACTION_ID =
               PARENT.TRANSACTION_ID(+)
         AND NOT (RCV_SHIPMENT_LINES.PO_HEADER_ID IS NULL
                  AND RCV_SHIPMENT_LINES.SOURCE_DOCUMENT_CODE = 'PO')