Oracle Purchase Requisition Query

Oracle Purchase Requisition Query :


/* Formatted on 12/28/2016 8:46:36 AM (QP5 v5.126.903.23003) */
SELECT   PO_REQUISITION_LINES_ALL.TO_PERSON_ID,
         PO_REQUISITION_LINES_ALL.VENDOR_SITE_ID,
         PO_REQUISITION_LINES_ALL.ITEM_ID,
         PO_REQUISITION_HEADERS_ALL.AUTHORIZATION_STATUS,
         PO_REQUISITION_LINES_ALL.CLOSED_CODE,
         PO_REQUISITION_HEADERS_ALL.TYPE_LOOKUP_CODE,
         PO_REQUISITION_LINES_ALL.DELIVER_TO_LOCATION_ID,
         PO_REQUISITION_LINES_ALL.ORG_ID,
         PO_REQUISITION_LINES_ALL.CREATED_BY,
         PO_REQUISITION_LINES_ALL.LAST_UPDATED_BY,
         PO_LINE_LOCATIONS_ALL.CREATION_DATE,
         NVL (
            PO_LINE_LOCATIONS_ALL.PROMISED_DATE,
            NVL (PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,
                 PO_REQUISITION_LINES_ALL.NEED_BY_DATE)
         ),
         PO_REQUISITION_LINES_ALL.CREATION_DATE,
         PO_REQUISITION_LINES_ALL.LAST_UPDATE_DATE,
         PO_REQUISITION_LINES_ALL.QUANTITY,
         NVL (PO_REQUISITION_LINES_ALL.CURRENCY_UNIT_PRICE,
              PO_REQUISITION_LINES_ALL.UNIT_PRICE),
         PO_REQUISITION_LINES_ALL.UNIT_MEAS_LOOKUP_CODE,
         NVL (PO_REQUISITION_LINES_ALL.CURRENCY_CODE, GSB.CURRENCY_CODE)
            AS PR_DOC_CURR,
         PO_REQUISITION_LINES_ALL.RATE,
         PO_REQUISITION_HEADERS_ALL.SEGMENT1,
         PO_REQUISITION_LINES_ALL.LINE_NUM,
         PO_REQUISITION_LINES_ALL.ITEM_DESCRIPTION,
         PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID,
         PO_REQUISITION_LINES_ALL.DESTINATION_ORGANIZATION_ID,
         PR_DATE.LAST_SUBMIT_DATE AS SUBMIT_DATE,
         NVL (PO_REQUISITION_HEADERS_ALL.APPROVED_DATE,
              PR_DATE.LAST_APPROVED_DATE)
            APPROVAL_DATE,
         PO_REQUISITION_LINES_ALL.VENDOR_ID,
         PO_REQUISITION_LINES_ALL.CANCEL_FLAG,
         PO_REQUISITION_HEADERS_ALL.LAST_UPDATE_DATE,
         PO_LINE_LOCATIONS_ALL.LAST_UPDATE_DATE,
         PO_REQUISITION_LINES_ALL.LINE_TYPE_ID,
         PO_REQUISITION_LINES_ALL.CATEGORY_ID,
         PO_REQUISITION_LINES_ALL.MODIFIED_BY_AGENT_FLAG,
         PO_REQUISITION_LINES_ALL.MATCHING_BASIS,
         PO_REQUISITION_LINES_ALL.AMOUNT,
         '0' AS X_CUSTOM,
         PO_REQUISITION_LINES_ALL.ON_RFQ_FLAG,
         PO_REQUISITION_LINES_ALL.AUCTION_HEADER_ID,
         PO_REQUISITION_LINES_ALL.REQS_IN_POOL_FLAG,
         PO_REQUISITION_LINES_ALL.AT_SOURCING_FLAG,
         PO_REQUISITION_LINES_ALL.URGENT_FLAG,
         PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID,
         PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID,
         PO_LINE_LOCATIONS_ALL.APPROVED_FLAG,
         PO_LINE_LOCATIONS_ALL.RECEIPT_REQUIRED_FLAG,
         PO_LINE_LOCATIONS_ALL.MATCHING_BASIS,
         PO_LINE_LOCATIONS_ALL.INSPECTION_REQUIRED_FLAG,
         PO_LINE_LOCATIONS_ALL.SHIPMENT_CLOSED_DATE,
         PO_LINE_LOCATIONS_ALL.CLOSED_FOR_RECEIVING_DATE,
         PO_LINE_LOCATIONS_ALL.CLOSED_FOR_INVOICE_DATE,
         PO_LINE_LOCATIONS_ALL.CONSIGNED_FLAG,
         PO_LINE_LOCATIONS_ALL.VMI_FLAG,
         PO_DOC_STYLE_HEADERS.PROGRESS_PAYMENT_FLAG,
         PO_REQUISITION_LINES_ALL.QUANTITY_CANCELLED,
         DECODE (PO_LINE_LOCATIONS_ALL.APPROVED_FLAG,
                 'Y', PO_LINE_LOCATIONS_ALL.APPROVED_DATE,
                 NULL)
            PO_APPROVED_ON_DT,
         DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
                 NULL, PO_HEADERS_ALL.SUBMIT_DATE,
                 PO_RELEASES_ALL.SUBMIT_DATE)
            PO_SUMBIT_ON_DT,
         DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
                 NULL, PO_HEADERS_ALL.DOCUMENT_CREATION_METHOD,
                 PO_RELEASES_ALL.DOCUMENT_CREATION_METHOD)
            PO_CREATION_METHOD_ID,
         DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
                 NULL, PO_HEADERS_ALL.REVISION_NUM,
                 PO_RELEASES_ALL.REVISION_NUM)
            PO_REVISIONS,
         PO_HEADERS_ALL.RATE_TYPE
  FROM   PO_REQUISITION_LINES_ALL,
         PO_REQUISITION_HEADERS_ALL,
         PO_LINE_LOCATIONS_ALL,
         (  SELECT   PO_ACTION_HISTORY.OBJECT_ID,
                     MAX(CASE
                            WHEN PO_ACTION_HISTORY.ACTION_CODE = 'APPROVE'
                            THEN
                               PO_ACTION_HISTORY.LAST_UPDATE_DATE
                         END)
                        LAST_APPROVED_DATE,
                     MIN(CASE
                            WHEN PO_ACTION_HISTORY.ACTION_CODE = 'SUBMIT'
                            THEN
                               PO_ACTION_HISTORY.LAST_UPDATE_DATE
                            WHEN PO_ACTION_HISTORY.ACTION_CODE = 'IMPORT'
                            THEN
                               PO_ACTION_HISTORY.LAST_UPDATE_DATE
                         END)
                        LAST_SUBMIT_DATE
              FROM   PO_ACTION_HISTORY
             WHERE   PO_ACTION_HISTORY.OBJECT_TYPE_CODE = 'REQUISITION'
                     AND PO_ACTION_HISTORY.ACTION_CODE IN
                              ('APPROVE', 'SUBMIT', 'IMPORT')
          GROUP BY   OBJECT_ID) PR_DATE,
         FINANCIALS_SYSTEM_PARAMS_ALL FSP,
         GL_SETS_OF_BOOKS GSB,
         PO_DOC_STYLE_HEADERS,
         PO_HEADERS_ALL,
         PO_RELEASES_ALL
 WHERE   PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID =
            PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
         AND PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID =
               PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID(+)
         AND PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID =
               PR_DATE.OBJECT_ID(+)
         AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
         AND NVL (FSP.ORG_ID, -99) =
               NVL (PO_REQUISITION_LINES_ALL.ORG_ID, -99)
         --(PO_REQUISITION_LINES_ALL.CREATION_DATE > TO_DATE('$$INITIAL_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))
         AND PO_LINE_LOCATIONS_ALL.PO_HEADER_ID =
               PO_HEADERS_ALL.PO_HEADER_ID(+)
         AND PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID =
               PO_RELEASES_ALL.PO_RELEASE_ID(+)
         AND PO_HEADERS_ALL.STYLE_ID = PO_DOC_STYLE_HEADERS.STYLE_ID(+)