OBIEE 11g Configuration for MS SQL Database

Step 1: Apply the updated/recent ODBC driver.

Considered , OBIEE version: 11.1.1.7.1 and Linux 64 bit server. 

Need to download the latest Data driver 7.1.5 from https://support.oracle.com

Patch #: 21440314

Apply the patches as per the Oracle instructions. Post to that we need to manually copy and unzip the driver to below location.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant

Step 2: System DSN Setup.

Create a System DSN to connect to MS SQL database

Step 3: OPMN.XML file Configuration.

Navigate to below location to do the change for opmn.xml file.

/u01/BIUAT/OBIEE/instances/instance1/config/OPMN/opmn

Backup copies of opmn.xml. Make edits to opmn.xml.

Search for BI components in opmn.xml. These are XML elements, <ias-component>, with the id "coreapplication_obi<...>".

In each of these BI component elements, modify and make sure that these environment variables point to recent ODBC driver.    

 $LD_LIBRARY_PATH

 $PATH

Step 4: ODBC.INI file Configuration.

Navigate to below location to do the change for odbc.ini file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup of odbc.ini file.

Need to do below changes for [ODBC] tag. TraceDll and InstallDir need to be pointed to new data driver.

Need to modify the below mentioned line for MS SQL Data driver section.

·         Tag name should be same as System DSN name which we have created.

·         Driver path should point to 7.1.5 .

·         Database

·         EnableQuotedIdentifiers should set to 1.

·         HostName. ( Along with Instance if any)

·         PortNumber

Once this changes are done , we need to copy the file to new Data driver folder as well.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant/7.1.5

Step 5: USER.SH file Configuration.

Navigate to below location to do the change for user.sh file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup the user.sh file.

In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:

·         For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.

·         For HP-UX, the library path variable is SHLIB_PATH.

·         For AIX, the library path variable is LIBPATH.

Step 6: BI-INIT.SH file Configuration.

bi-init.sh file used to source env , should point to the DataDirect 7.1.5 driver

Back up bi-init.sh, usually located under

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Make edit to bi-init.sh.

Modify the EPM_ODBC_DRIVER_DIR parameter to point it the location of the DD 7.1.5 ODBC driver library directory.

   For example: EPM_ODBC_DRIVER_DIR=$ORACLE_HOME/common/ODBC/Merant/7.1.5/lib

   Note that bi-init.sh is only needed only for running OBIEE applications directly from a shell command line.

Step 7: RPD Changes.

Create a new connection pool to connect to MS SQL database.

Disable function "NESTED_ORDERBY_SUPPORTED" in the rpd .

Step 8: Restart BI Services.

Restart all servers (AdminServer and all Managed server(s))

Enjoy and Stay Well !! :)

Enable CLOB Field in OBIEE

Today we are going to discuss how to enable CLOB field in OBIEE. Below are the steps which need to be followed.

Lets assume , ACTION_ITEM table has a column called ACTION_TAKEN which datatype is CLOB.

1. There is not CLOB datatype in OBIEE RPD. For CLOB please select LONGVARCHAR type in RPD physical layer.

 2.   Its mandatory to define a Keys for that Physical Table in RPD.

3.Open the same column in BMM layer. Select that particular column and click on Edit under “Column Source Type”.

4. Open that Column from Logical Table Source.

5. Need to use LOOKUP DENSE funcation.

Syntax : LOOKUP (DENSE  CLOB Column, PK Column)

6. Move it to Presentation layer and use the same in BI report.

Enjoy and Stay Well !! :)

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 !!