SQL Query to get the Supplier Basic info in Oracle fusion.

Here is the SQL Query to get the Supplier Basic information such as Supplier Name, Supplier Site, Vendor Site ID.

Use this query, modify by adding additional columns as required and extract the data.

SQL Query

SELECT psv.vendor_name        supplierName
      ,pssam.vendor_site_code supplierSite
      ,hps.party_site_id      HZ_Party_Site_ID
      ,pssam.vendor_site_id   vendor_site_id
  FROM poz_suppliers_v psv,
      poz_supplier_sites_all_m pssam,
      hz_party_sites HPS
WHERE pssam.vendor_id=psv.vendor_id
  AND hps.party_site_id = pssam.party_site_id

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.