SQL Query to get the Purchase Order(PO) FBDI Import Error details.

Here is the quick SQL Query to get the Purchase Order(PO) FBDI Import Error details for all the PO's which are errored out.

This is the sample query which can be used. Please use and modify accordingly for your convenience.

SQL Query

SELECT
POIE.BATCH_ID,
POIE.INTERFACE_TYPE,
PHI.DOCUMENT_NUM,
PLI.LINE_NUM,
PLLI.SHIPMENT_NUM,
PDI.DISTRIBUTION_NUM,
PHI.vendor_name,
phi.vendor_site_code,
phi.vendor_id,
phi.vendor_site_id,
PDI.PROJECT,
PDI.TASK,
PDI.EXPENDITURE,
pdi.PJC_EXPENDITURE_ITEM_DATE,
PDI.EXPENDITURE_ORGANIZATION,
POIE.COLUMN_NAME,
POIE.COLUMN_VALUE,
POIE.ERROR_MESSAGE,
POIE.ERROR_MESSAGE_NAME,
POIE.TABLE_NAME,
POIE.CREATED_BY,
poie.request_id
FROM
PO_INTERFACE_ERRORS POIE,
PO_HEADERS_INTERFACE PHI,
PO_LINES_INTERFACE PLI,
PO_LINE_LOCATIONS_INTERFACE PLLI,
PO_DISTRIBUTIONS_INTERFACE PDI
WHERE POIE.INTERFACE_HEADER_ID = PHI.INTERFACE_HEADER_ID(+)
AND POIE.INTERFACE_LINE_ID = PLI.INTERFACE_LINE_ID(+)
AND POIE.INTERFACE_LINE_LOCATION_ID = PLLI.INTERFACE_LINE_LOCATION_ID(+)
AND POIE.INTERFACE_DISTRIBUTION_ID = PDI.INTERFACE_DISTRIBUTION_ID(+) 

SQL Query to get the Legal entity in Oracle fusion.

Here is the SQL Query to get the Legal entity in Oracle fusion.

SQL Query

SELECT *
FROM XLE_ENTITY_PROFILES
where TRUNC (SYSDATE) BETWEEN NVL ( EFFECTIVE_FROM,TRUNC (SYSDATE) - 1)

                           AND NVL (EFFECTIVE_TO,TRUNC (SYSDATE) + 1)

Import Program to import Purchase Orders in Oracle fusion.

Here is the quick post to import the Purchase orders using the FBDI process.


1. Log in to the application and click on navigator icon and select Scheduled Process

2. Click on Schedule New Process. Search and select Load Interface File for Import Program.

3. Search and select the import process Import Orders. Upload the zipped FBDI data files.

4.  Once the program is submitted, click on done and check the status of submitted process in Process window. Make sure the status or submitted process id must be completed along with all the child process.

5.Once the Load Interface File for Import Program is completed successfully, click on Schedule New Process. Search and select Import Orders program

Key in the below parameters as follows

           Procurement BU: - Select the Procurement BU for which Orders needs to be loaded.

         Default Buyer: - Select the Default Buyer.

         Approval Action: - Keep the approval action blank

         Batch ID: - Give the Batch ID that is used in the file. 

    Once done submit the program.

    6.    Once the program is complete, download the program execution report form the process window.

Rest API to delete the UnProcessed Projects Costs.

Here is the Rest API for the Oracle fusion to delete the UnProcessed Projects Costs.
The input for this Rest API is the Transaction Reference ID from the Projects Interface table "PJC_TXN_XFACE_ALL"

Method: DELETE

Path: /fscmRestApi/resources/11.13.18.05/unprocessedProjectCosts/{UnprocessedTransactionReferenceId}

Also, Please do refer the latest Oracle documents for the same.
https://docs.oracle.com/en/cloud/saas/project-portfolio-management/19c/fapap/api-unprocessed-project-costs.html

TaxPayer ID - Supplier Profile database table to find the Tax Payer ID - Oracle fusion.

Here is the table name in the Oracle fusion cloud application, where the TaxPayer ID gets stored.

                             POZ_SUPPLIERS_PII.NCOME_TAX_ID   ( TableName. ColumnName)

VAT Code and Tax Registration Number at the Supplier Site - Oracle Fusion FBDI

VAT Code and Tax Registration Number at the Supplier Site FBDI upload.

These two columns are dummy columns and are not uploaded through the Supplier Site FBDI.

Both Vat Code and Tax Registration Number are not supported functionality via the Supplier Site import process.  Please see below the Doc ID provided

Supplier Site Import Process Not Populating The Tax Registration Number (VAT Registration Number) ( Doc ID 2034667.1 )  

Cause : "Populating the Tax Registration Number via the Supplier Site import process is currently not supported functionality."

Query to find the Supplier Site Tolerances configuration setups in Oracle fusion

Here is the query to find the Supplier Site Service Tolerance and Quantity Tolerances configuration setups in Oracle fusion.

SQL Query

SELECT *
   FROM AP_TOLERANCE_TEMPLATES

Import program to Purge the PO Receipts interface table in Oracle fusion.

Here is the import program to purge the PO receipts interface table :


"Purge Receiving Interface"

These are the steps to be followed :

1. Go to Tools -> Scheduled Process

2. Click on Schedule New Process.

3. Search for : "Purge Receiving Interface"

4. Select OK from the Search results.

5. Select and provide the appropriate input parameters value for 

    a. Purge Data Before.

    b. Status.

6. Click on Submit.

Where exclusive_payment_flag of Supplier Site is stored

Here is the table name where the exclusive_payment_flag of the supplier site is stored :

iby_external_payees_all

SQL Query to fetch the Project Contracts Billing Cycle setup details in Oracle cloud.

Here is the SQL Query to fetch the Project Contracts Billing Cycle setup details in Oracle cloud.

SELECT 'Billing Cycle' "SETUP_NAME",
            pbc.billing_cycle_name VALUE
  FROM  pjf_billing_cycles_vl pbc
 WHERE TRUNC(SYSDATE) BETWEEN NVL (start_date_active,TRUNC(SYSDATE) - 1) AND  NVL (end_date_active, TRUNC(SYSDATE) + 1);

SQL Query to fetch the Project Billing Currency Type code in Oracle cloud fusion.

Here is the SQL Query to fetch the Project Billing Currency Type codes in Oracle cloud fusion.


SELECT 'Billing Currency Type Code' "SETUP_NAME",
               flvl.lookup_code  VALUE
  FROM   fnd_lookup_values_vl flvl
 WHERE lookup_type = 'PJB_BILLING_CURRENCY_TYPE'
     AND enabled_flag = 'Y'
    AND TRUNC(SYSDATE) BETWEEN NVL (start_date_active,TRUNC(SYSDATE) - 1) AND NVL (end_date_active, TRUNC(SYSDATE) + 1) ;

SQL Query to fetch the payment terms setups in Oracle cloud fusion

Here is the SQL Query to fetch the payment terms setups in Oracle cloud.


SELECT 'PAYMENT TERM' "SETUP_NAME",
          rt.name VALUE
  FROM ra_terms rt
 WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active, TRUNC(SYSDATE) - 1)  AND NVL (end_date_active, TRUNC(SYSDATE) + 1) ;

SQL Query to get the Contract Party Role setup details in Oracle cloud fusion Contracts

Here is the SQL Query to get the Contract Party Role Setup details in Oracle cloud fusion.

SELECT  'Party Role Code' "SETUP_NAME",
                 'PARTY_ROLE_CODE'  SETUP_NAME_CODE,
                 flvl.lookup_code  VALUE
  FROM fnd_lookup_values_vl flvl
 WHERE lookup_type = 'OKC_PARTY_ROLE'
      AND enabled_flag = 'Y'
     AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active,TRUNC(SYSDATE) - 1) AND NVL (end_date_active, TRUNC(SYSDATE) + 1) ;

SQL Query to get the Organization ID details in Oracle cloud fusion.

Here is the SQL Query to get the Organization ID in Oracle cloud fusion. This query can be tweaked accordingly for the further requirements.

SELECT 'Business Unit ID' "SETUP_NAME",
                haot.name  VALUE,
                 hao.organization_id "Value ID"
FROM HR_ALL_ORGANIZATION_UNITS_F hao,
            HR_ORGANIZATION_UNITS_F_TL haot,
            HR_ORG_UNIT_CLASSIFICATIONS_F houcf
WHERE hao.organization_id = haot.organization_id
     AND hao.effective_start_date = haot.effective_start_date
    AND hao.effective_end_date = haot.effective_end_date
    AND TRUNC(SYSDATE) BETWEEN hao.effective_start_date AND hao.EFFECTIVE_END_DATE
   AND houcf.organization_id = hao.organization_id
   AND houcf.classification_code = 'BUSINESS_UNIT'
   AND haot.LANGUAGE='US';

How to create a folder in Oracle BI EE 11.

Here is the post on creating a folder in Oracle BI EE 11. I know this is very simple task, but sometimes if you are a new to this of creating a BI report, then you will spend quite sometime to locate the tool and how to create the same.

Here are the steps provided to create a folder in Oracle BI cloud.

1. Login to the Oracle cloud applications.

2. Click on the Reports and Analytics.

3. Now once on this page, navigate and click on the Browse Catalog button.

4. Oracle BI page opens up and now you can see the catalog and other stuffs. On the Left hand side, we see Folders.

5. In order to create a new folder, there is tool bar section.

6. Click on "New" tool.

7. We see the new folder icon. Click on that tool. Now it pops up the New folder window to create. Enter the desired name and click on the Ok button.

This creates new Folder successfully.

Oracle Cloud - Manage File Import Activities

Here are the navigation steps to access the "Manage File Import activities" in Oracle cloud applications.

1. Login to Oracle cloud application.

2. Click on the user, settings and action.

3. Navigate to "Setup and Maintenance"

4.  In the Search Tasks text box, type"Manage" and enter.

5. Now, you will see the list of options.

6. Click on the "Manage File Import Activities" task. You will be directed to the Manage Import Activities page, where you can submit the the individual jobs.

STATS Collection Oracle SQL Query

Here is the Sample Oracle SQL Query for the STATS collection.

select t.last_analyzed, nvl(num_rows,0) num_rows, t.* from all_tables t where last_analyzed is not null and owner in (<Oracle_Schema_name>)

order by 1 desc;

SQL Query to find the time taken in hours, minutes and seconds.

Here is the oracle sql query that can be used to find out the time taken in hours, minutes and second.

This sql query converts the date time format and calculates accordingly.

This can be modified accordingly for the requirements and can be tested.

select bb.start_time,bb.end_time,
mod(trunc( 24 * (to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_HOURS,
mod(trunc( 24 * 60 * (to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_MINS,
mod(trunc( 24 * 60 * 60 *(to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_SECONDS
from
XX_CUSTOM_BATCH bb;

SQL Query to validate Start_Date and End_Date for each consecutive records

Assume you have requirement to validate the start date and end date for each record provided and your validation or check should be that every consecutive record should have start_date + 1 from the previous record end date :

Here is the sample data :

EMP_PRICING DATA :

ID     NAME   START_DATE    END_DATE
1.      P1           1-JAN-2019        31-MAR-2019
2.      P1           1-APRIL-2019    30-JUN-2019

Here is sample SQL Query to validate this data in the sql query. Please note this can be modified accordingly to your requirements.


SQL Query.


select * from (select * from EMP_PRICING_DATA order by start_dt) a
where exists (select 1 from EMP_PRICING_DATA b
                      where b.name = a.name
                        and b.start_dt <= a.end_dt
                        and b.start_dt <> a.end_dt + 1
                        and b.end_dt >= a.start_dt
                        and b.rowid < a.rowid
                      );

How to refresh Materialized view

Here is the sample script to refresh the materialized view.


DECLARE
BEGIN
  DBMS_MVIEW.refresh('EMP_MV');    --'EMP_MV is the materialized view name.
END;