Oracle AP Invoice Distributions Query

Oracle AP Invoice Distributions Query

/* Formatted on 12/28/2016 8:53:03 AM (QP5 v5.126.903.23003) */
SELECT   AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,
         AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY,
         AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY,
         AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE,
         AP_INVOICES_ALL.INVOICE_ID,
         AP_INVOICES_ALL.INVOICE_NUM,
         AP_INVOICES_ALL.INVOICE_CURRENCY_CODE,
         AP_INVOICES_ALL.VENDOR_ID,
         AP_INVOICES_ALL.VENDOR_SITE_ID,
         AP_INVOICES_ALL.INVOICE_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,
         AP_INVOICES_ALL.INVOICE_RECEIVED_DATE,
         AP_INVOICES_ALL.CREATION_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,
         PO_HEADERS_ALL.SEGMENT1,
         PO_LINES_ALL.LINE_NUM,
         PO_HEADERS_ALL.CREATION_DATE,
         AP_INVOICES_ALL.LAST_UPDATE_DATE,
         PO_HEADERS_ALL.LAST_UPDATE_DATE,
         PO_LINES_ALL.LAST_UPDATE_DATE,
         PO_LINES_ALL.ITEM_DESCRIPTION,
         AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION,
         AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,
         AP_INVOICES_ALL.WFAPPROVAL_STATUS,
         AP_INVOICES_ALL.PAYMENT_STATUS_FLAG,
         AP_INVOICES_ALL.CANCELLED_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE,
         PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG,
         AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID,
         AP_INVOICES_ALL.TERMS_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE,
         AP_INVOICES_ALL.EXCHANGE_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE,
         AP_INVOICES_ALL.EXCHANGE_RATE_TYPE,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE,
         AP_INVOICES_ALL.EXCHANGE_RATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID,
         AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID,
         AP_INVOICES_ALL.SET_OF_BOOKS_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED,
         AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT,
         AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT,
         CASE
            WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL
            THEN
               PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE
            ELSE
               AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE
         END,
         AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE,
         PO_VENDORS.RECEIPT_REQUIRED_FLAG,
         PO_VENDORS.INSPECTION_REQUIRED_FLAG,
         PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG,
         PO_VENDORS.LAST_UPDATE_DATE,
         AP_INVOICES_ALL.SOURCE,
         AP_INVOICES_ALL.PROJECT_ID,
         AP_INVOICES_ALL.TASK_ID,
         AP_INVOICES_ALL.EXPENDITURE_TYPE,
         AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID,
         AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE,
         AP_INVOICES_ALL.PAYMENT_CROSS_RATE,
         AP_INVOICES_ALL.REQUESTER_ID,
         AP_INVOICE_LINES_ALL.LINE_NUMBER,
         AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE,
         AP_INVOICE_LINES_ALL.REQUESTER_ID,
         AP_INVOICE_LINES_ALL.DESCRIPTION,
         AP_INVOICES_ALL.LEGAL_ENTITY_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.DIST_MATCH_TYPE
  FROM   AP_INVOICE_DISTRIBUTIONS_ALL,
         AP_INVOICE_LINES_ALL,
         AP_INVOICES_ALL,
         PO_LINES_ALL,
         PO_HEADERS_ALL,
         PO_VENDORS,
         PO_VENDOR_SITES_ALL,
         PO_DISTRIBUTIONS_ALL
 WHERE   AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID
         AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+)
         AND AP_INVOICES_ALL.VENDOR_SITE_ID =
               PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID =
               PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID =
               PO_HEADERS_ALL.PO_HEADER_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID =
               AP_INVOICE_LINES_ALL.INVOICE_ID
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER =
               AP_INVOICE_LINES_ALL.LINE_NUMBER

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')

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(+)

Oracle Inventory Transaction Query

To get the whole Oracle Inventory transaction details , below query can be used.

/* Formatted on 10/10/2016 12:53:40 PM (QP5 v5.126.903.23003) */

SELECT   MMT.ORGANIZATION_ID,

         MMT.INVENTORY_ITEM_ID ITEM_ID,

         MMT.SUBINVENTORY_CODE SUBINVENTORY_CODE,

         MMT.TRANSACTION_DATE,

         CID.TRANSACTION_TYPE_NAME,

         CID.TRANSACTION_SOURCE_TYPE_NAME,

         MMT.PRIMARY_QUANTITY,

         CID.UNIT_COST,

         CID.BASE_TRANSACTION_VALUE,

         REFERENCE_ACCOUNT,

         DISTRIBUTION_ACCOUNT_ID,

         ENCUMBRANCE_ACCOUNT,

         SHIPMENT_NUMBER,

         MMT.LOCATOR_ID,

         MMT.TRANSACTION_UOM,

         MMT.TRANSACTION_REFERENCE,

         DECODE (

            CID.TRANSACTION_SOURCE_TYPE_NAME,

            'Move order',

            (SELECT   DISTINCT

                      CONCAT (CONCAT (REQUEST_NUMBER, '--'), DESCRIPTION)

               FROM   mtl_txn_request_headers mtrh

              WHERE   HEADER_ID = mmt.TRANSACTION_SOURCE_ID AND ROWNUM = 1),

            'Purchase order',

            (SELECT   DISTINCT CONCAT (CONCAT (segment1, '--'), COMMENTS)

               FROM   po_headers_all pha

              WHERE   po_header_id = mmt.TRANSACTION_SOURCE_ID AND ROWNUM = 1),

            'Job or Schedule',

            (SELECT   DISTINCT

                      CONCAT (CONCAT (WIP_ENTITY_NAME, '--'), DESCRIPTION)

               FROM   wip_entities

              WHERE   WIP_ENTITY_ID = mmt.TRANSACTION_SOURCE_ID

                      AND ROWNUM = 1),

            CAST (mmt.SOURCE_CODE AS VARCHAR2 (100))

         )

            TRANSACTION_DESC

  FROM   MTL_MATERIAL_TRANSACTIONS MMT, CST_INV_DISTRIBUTION_V CID

 WHERE   MMT.TRANSACTION_ID = CID.TRANSACTION_ID

         AND CID.ACCOUNTING_LINE_TYPE = 1

-- AND MMT.INVENTORY_ITEM_ID IN (41041, 42496)

--and TRANSACTION_SOURCE_TYPE_NAME='Purchase order'


Enjoy and Stay Well.

BPA Master List

Sometime users wants to have the master BPA (Blanket Purchase Agreement) list irrespective of ay release is being created or not. Below query will help to get the same.


/* Formatted on 11/6/2016 12:55:53 PM (QP5 v5.126.903.23003) */
SELECT   a.SEGMENT1 BPA_NO,
         COMMENTS BPA_DESC,
         CURRENCY_CODE,
         APPROVED_DATE,
         (SELECT   APPROVED_DATE
            FROM   PO_HEADERS_ARCHIVE_ALL
           WHERE       REVISION_NUM = 0
                   AND APPROVED_FLAG = 'Y'
                   AND po_header_id = a.po_header_id)
            PO_APPROVED_DATE_ORG,
         REVISION_NUM,
         START_DATE,
         END_DATE,
         AMOUNT_LIMIT Updated_ACV_Amount,
         (CASE
             WHEN a.ATTRIBUTE_CATEGORY = 'ACV Details' THEN a.ATTRIBUTE1
          END)
            Initial_ACV_Amount,
         a.RATE,
         (SELECT   DESCRIPTION
            FROM   fnd_lookup_values
           WHERE   a.FOB_LOOKUP_CODE = LOOKUP_CODE AND lookup_type = 'FOB')
            SHIPMENT_TERMS,
         c.SEGMENT1 SUPPLIER_NUM,
         c.VENDOR_NAME SUPPLIER_NAME,
         b.address_line1,
         b.country,
         SYSDATE
  FROM   PO_HEADERS_ALL a, ap_supplier_sites_all b, ap_suppliers c
 WHERE       a.vendor_id = b.vendor_SITE_id(+)
         AND b.vendor_id = c.vendor_id(+)
         AND TYPE_LOOKUP_CODE = 'BLANKET'


Enjoy and Stay Well !!

OBIEE 11g: Conditional Formatting on a Column based on other column is not working

Issue: Conditional Formatting on a Column based on other column is not working in OBIEE.

Resolution: Value Suppression has to be set as “Repeat”

Enjoy and stay well !!

PO Action History Query

This Query will give you Action History for Standard PO, Blanket Release & Requisition.

/* Formatted on 12/7/2016 12:39:28 PM (QP5 v5.126.903.23003) */
SELECT   OBJECT_TYPE_CODE TYPE,
         SEGMENT1,
         COMMENTS DESCriptions,
         RELEASE_NUM,
         RELEASE_DATE,
         POH.AGENT_ID BUYER_REQ_ID,
         SEQUENCE_NUM,
         ACTION_CODE,
         ACTION_DATE,
         NOTE,
         POAH.EMPLOYEE_ID ACTION_OWNER_ID,
         POH.CREATION_DATE CREATION_DATE,
         POAH.LAST_UPDATE_DATE ACTION_LAST_UPDATE_DATE
  FROM   PO_RELEASES_ALL POR, po_headers_all POH, po_action_history POAH
 WHERE       POR.PO_HEADER_ID = POH.PO_HEADER_ID
         AND POR.PO_RELEASE_ID = POAH.OBJECT_ID
         AND OBJECT_TYPE_CODE = 'RELEASE'
UNION
SELECT   OBJECT_TYPE_CODE,
         SEGMENT1,
         COMMENTS PO_DESC,
         NULL RELEASE_NUM,
         NULL RELEASE_DATE,
         POH.AGENT_ID BUYER_ID,
         SEQUENCE_NUM,
         ACTION_CODE,
         ACTION_DATE,
         NOTE,
         POAH.EMPLOYEE_ID ACTION_OWNER_ID,
         POH.CREATION_DATE PO_CREATION_DATE,
         POAH.LAST_UPDATE_DATE ACTION_LAST_UPDATE_DATE
  FROM   po_headers_all POH, po_action_history POAH
 WHERE   POH.PO_HEADER_ID = POAH.OBJECT_ID AND OBJECT_TYPE_CODE = 'PO'
UNION
SELECT   OBJECT_TYPE_CODE,
         SEGMENT1,
         DESCRIPTION,
         NULL RELEASE_NUM,
         NULL RELEASE_DATE,
         POH.PREPARER_ID,
         SEQUENCE_NUM,
         ACTION_CODE,
         ACTION_DATE,
         NOTE,
         POAH.EMPLOYEE_ID ACTION_OWNER_ID,
         POH.CREATION_DATE PO_CREATION_DATE,
         POAH.LAST_UPDATE_DATE ACTION_LAST_UPDATE_DATE
  FROM   po_requisition_headers_all POH, po_action_history POAH
 WHERE   POH.REQUISITION_HEADER_ID = POAH.OBJECT_ID
         AND OBJECT_TYPE_CODE = 'REQUISITION'



Enjoy and Stay Well !! :)

Item Master Query

/* Formatted on 11/15/2016 1:35:09 PM (QP5 v5.126.903.23003) */
SELECT   ORGANIZATION_CODE,
         ORGANIZATION_NAME,
         MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE,
         --MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY,
         MTL_SYSTEM_ITEMS_B.CREATION_DATE,
         -- MTL_SYSTEM_ITEMS_B.CREATED_BY,
         MTL_SYSTEM_ITEMS_B.DESCRIPTION,
         MTL_SYSTEM_ITEMS_TL.LONG_DESCRIPTION,
         MTL_SYSTEM_ITEMS_B.SEGMENT1,
         MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE,
         --         MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT,
         --         MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE,
         --         MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE,
         --         MTL_SYSTEM_ITEMS_B.UNIT_VOLUME,
         MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE,
         MTL_SYSTEM_ITEMS_B.ITEM_TYPE,
         MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE,
         MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE,
         MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE,
         MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG,
         MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG,
         MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG,
         MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE,
         MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG,
         MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG,
         MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE,
         MTL_SYSTEM_ITEMS_B.MIN_MINMAX_QUANTITY,
         MTL_SYSTEM_ITEMS_B.MAX_MINMAX_QUANTITY,
         MTL_SYSTEM_ITEMS_B.ATTRIBUTE1 INS_GROUP,
         MTL_SYSTEM_ITEMS_B.ATTRIBUTE2 INS_GRP_DESC,
         STOCK_ENABLED_FLAG,
         INSPECTION_REQUIRED_FLAG,
         RECEIPT_REQUIRED_FLAG,
         RCV_ROUTING_HEADERS.ROUTING_NAME,
         BUYER_ID
  FROM   MTL_SYSTEM_ITEMS_B,
         org_organization_definitions,
         RCV_ROUTING_HEADERS,
         MTL_SYSTEM_ITEMS_TL
 WHERE   org_organization_definitions.ORGANIZATION_ID =
            MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID
         AND ROUTING_HEADER_ID = RECEIVING_ROUTING_ID
         AND MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID =
               MTL_SYSTEM_ITEMS_TL.ORGANIZATION_ID
         AND MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID =
               MTL_SYSTEM_ITEMS_TL.INVENTORY_ITEM_ID

Supplier Master Query

/* Formatted on 11/27/2016 8:35:10 AM (QP5 v5.126.903.23003) */
SELECT   PO_VENDORS.SEGMENT1 SUPPLIER_NUMBER,
         PO_VENDORS.VENDOR_NAME SUPPLIER_NAME,
         PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
         PO_VENDORS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
         PO_VENDOR_SITES_ALL.ADDRESS_LINE1,
         PO_VENDOR_SITES_ALL.ADDRESS_LINE2,
         PO_VENDOR_SITES_ALL.ADDRESS_LINE3,
         PO_VENDOR_SITES_ALL.CITY,
         PO_VENDOR_SITES_ALL.STATE,
         PO_VENDOR_SITES_ALL.ZIP,
         PO_VENDOR_SITES_ALL.COUNTRY,
         PO_VENDOR_SITES_ALL.AREA_CODE,
         PO_VENDOR_SITES_ALL.PHONE,
         PO_VENDOR_SITES_ALL.FAX_AREA_CODE,
         PO_VENDOR_SITES_ALL.FAX,
         PO_VENDORS.CREATION_DATE,
         (SELECT   USER_NAME
            FROM   fnd_user
           WHERE   user_id = PO_VENDORS.CREATED_BY)
            created_By,
         PO_VENDORS.LAST_UPDATE_DATE,
         (SELECT   USER_NAME
            FROM   fnd_user
           WHERE   user_id = PO_VENDORS.LAST_UPDATED_BY)
            Last_Updated_By,
         PO_VENDORS.START_DATE_ACTIVE,
         PO_VENDORS.END_DATE_ACTIVE,
         PO_VENDOR_SITES_ALL.PAY_GROUP_LOOKUP_CODE,
         PO_VENDORS.ENABLED_FLAG,
         PO_VENDOR_SITES_ALL.attribute1 SUPPLIER_CLASSIFICATION,
         PO_VENDORS.TERMS_DATE_BASIS,
         PO_VENDORS.ATTRIBUTE1 PACER_VENDOR_NO,
         AP_TERMS_TL.NAME PAYMENT_TERMS,
         ieb.BANK_NUMBER,
         ieb.bank_name "BANK NAME",
         iebb.bank_branch_name "BANK BRANCH NAME",
         ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
         EFT_SWIFT_CODE BANK_SWIFT_CODE,
         IBAN,
         PO_VENDORS.INVOICE_CURRENCY_CODE,
         PO_VENDORS.PAYMENT_CURRENCY_CODE,
            PO_VENDOR_CONTACTS.PREFIX
         || ' '
         || PO_VENDOR_CONTACTS.FIRST_NAME
         || ' '
         || PO_VENDOR_CONTACTS.LAST_NAME
            SUPPLIER_CONTACT,
         PO_VENDOR_CONTACTS.EMAIL_ADDRESS
  FROM   PO_VENDOR_SITES_ALL,
         PO_VENDORS,
         AP_TERMS_TL,
         apps.iby_ext_bank_accounts ieba,
         apps.iby_account_owners iao,
         apps.iby_ext_banks_v ieb,
         apps.iby_ext_bank_branches_v iebb,
         PO_VENDOR_CONTACTS
 WHERE       PO_VENDOR_SITES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID
         AND PO_VENDORS.TERMS_ID = AP_TERMS_TL.TERM_ID
         AND iao.account_owner_party_id = PO_VENDORS.party_id
         AND ieba.ext_bank_account_id = iao.ext_bank_account_id
         AND ieb.bank_party_id = iebb.bank_party_id
         AND ieba.branch_id = iebb.branch_party_id
         AND ieba.bank_id = ieb.bank_party_id
         AND PO_VENDORS.ENABLED_FLAG = 'Y'
         AND PO_VENDOR_SITES_ALL.VENDOR_ID = PO_VENDOR_CONTACTS.VENDOR_ID(+)
         AND PO_VENDOR_SITES_ALL.VENDOR_SITE_ID =
               PO_VENDOR_CONTACTS.VENDOR_SITE_ID(+)