How to solve Nonexistent table issue while extracting BICC VO Query in Oracle Fusion Cloud

In my previous post, I explained how to retrieve database query for BICC VO in Oracle Fusion Cloud

Sometimes this method won't work as not all VOs are designed the same way.

There's a chance that you'll get below error:

ERROR
-----------------------
. [nQSError: 27047] Nonexistent table

This is expected behavior as the old VO's are available in the RPD repository (metadata of the BI Server) and hence SELECT_PHYSICAL works fine.

The VO which used above is trying to access in this case is a BICC VO and these BICC PVO names end with the string (.ExtractPVO). These BICC VO's are not part of RPD and directly based on ADF. So SELECT_PHYSICAL doesn't work.

So to overcome this error, you need to use the syntax as below and you should get the results without any error:

select * from
EXTERNAL('ADF','"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."Connection Pool"').'<VO Name>' as t
FETCH FIRST 100 ROWS ONLY

e.g.

select * from
EXTERNAL('ADF','"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."Connection Pool"').'FscmTopModelAM.FinExtractAM.ArBiccExtractAM.ReceivableActivityExtractPVO' as t
FETCH FIRST 100 ROWS ONLY

Read More

How To Retrieve Query for BICC VO in Oracle Fusion Cloud

So, you are wondering how to retrieve the actual database query behind any VO in BI Cloud Connector (BICC) ? Here's how we can do it.

- Log in to BI Analytics as user with BI Administration privilege.

Navigation path: BI domain → Analytics → Administration

- On Administration page, navigate to Issue SQL option

- Enter physical SQL as below:

select_physical * from

"<VO Name>"

WHERE 1<> 1

e.g. 

select_physical * from

"FscmTopModelAM.FinApInvTransactionsAM.InvoiceHeaderPVO"

WHERE 1<> 1

- Click Issue SQL

- Click View Log option and download the log file

- In the log file, search for string "query to database". It should looks like this -

- Copy the complete query and format it. It will look like this -

- Now, this is the true underlying query issued at database level.

Read More

How to access Descriptive Flexfields in Oracle Fusion Pages

In Oracle EBS, Descriptive Flexfields, if configured, can be accessed from Oracle forms by locating [ ] field that presents a modal window containing all configured attributes along with available contexts. 

However, in Oracle Fusion/ERP Cloud, we don't have this field. By default, the pages may not show DFF section at all but there's a very simple way to hide/unhide them.

Below example shows us a Journal entry page that' doesn't show any DFF sections for Journal header -

Now, let's say we want to find the all Descriptive Flexfields available on this page. 

To do this, navigate to Settings and Actions menu located at top right corner under your account name.

In the dropdown menu, you will see Highlight Flexfields section.

Click on this option and your page will reload and you'll be able to see Descriptive Flexfield section -

Read More

How to import Lookup Types and Codes using File Based Loader

So, we want to create Lookup Types and Upload Lookup Values but as we have a huge data volume at hand, we want to use some kind of bulk upload method. Let's see how this can be achieved.

- We need to create the import files inclusive of all mandatory and optional columns to import the lookup data. We should use the pipe ‘|’ as delimiter and save it with the .csv file extension.

- Create separate files for Lookup Types and Lookup Codes

- Sample Lookup Type File -

- Sample Lookup Codes File -

- Now, let's upload the files to UCM server

- Navigate to Tools section and click the File Import and Export

- Click the Upload icon in the Search Results section

- Select ‘setup/functionalSetupManger/import’ from the Account drop-down list

- Browse and select the import files

- Click Save and Close

- Search result should look something like this

- Go to Setup and Maintenance

- Search for the 'Manage Standard Lookups' task or the 'Manage Common Lookups' task

- Click Actions and select Import

- Select the Account where you imported the files 'setup/functionalSetupManger/import'

- Provide full name of the files to be imported, including the .csv extension

- Click Upload

- We should see the progress of upload like below

- We can download the log file and it shuold show the summary of the import life below

- Now, we can search of our newly created Lookup Type and it should show us all the details including all the imported Lookup Codes

Read More

How to automate File-Based Data Import (FBDI) Process in Oracle Fusion

 Let's see how we can automate File-Based Data Import process in Oracle fusion. In ERP Cloud, We have 'Load interface Files for Import' ESS job to manually import one-off data files to Oracle Fusion. As this is a manual process, we need to select the data file name which we have uploaded in Oracle Fusionbut what if we want to automate this import process in Fusion ?


Assume, a third party heterogeneous system creates your order data and has new files to be importes in Fusion on periodical basis. Naturally, in such scenarios we would want to automate the data import process.


To achieve this, Oracle Fusion has provided another scheduled process 'Load Multiple Interface Files for Import' in which you don't need to baby-sit and perform the manual load process.

This is how it works -

- FBDI data files would need to be uploaded to the Oracle's Universal Content Management (UCM) server at the - scm/supplyOrder/import - endpoint

- The ESS Process - Load Multiple Interface Files for Import - should be scheduled to execute on a periodic bases. The process accepts three parameters -

1. Import Process - select - Import Orders

2. File Prefix

3. Number of Requests - the number of files to be processed - XXX

- The ESS process will then import order for the latest files which have been uploaded. The latest XXX files matching the file prefix will be loaded.

- This ESS job can be scheduled using the Schedule tab provided under Advanced options -

Read More