How To Enable Custom DFFs in OTBI Subject Areas in Oracle Fusion Cloud

So, you have defined a bunch of DFFs but wondering why they don't appear in the corresponding subject areas when you build an OTBI Analysis ?

Well, let's see how you can enable them for BI analysis purposes and use in OTBI. 

Below are the steps to enable a DFF for BI / OTBI Analysis/Reporting

- Navigate to Setup and Maintenance

 

- Change Functional area to the desired one.e.g. Financials

 

- Search for something like Manage%<module>%Flexfields

e.g. Manage%Receivables%Flex%

 

- Open the Task

Enter the desired Flexfield Code (e.g. RA_CUSTOMER_TRX%)

Click Search

- Select the desired DFF and click Edit

- Select the Segment and click Edit

 

- Scroll all the way down and Enable the option ‘BI Enabled’

- Save and Close

Now, navigate to Scheduled Processes

Schedule New Process – 

Import Oracle Fusion Data Extensions for Transactional Business Intelligence

Run it for desired functional area (e.g. Financial Reporting)

 

Let the program finish. (It may take some time to complete).

If Process completes in Warning -

As per Doc ID 1616574.1, even if the process ends in a Warning, the Flexfield import will complete therefore the functionality of the job is not adversely affected.

Once process is finished -

- Navigate to Catalog and open the desired subject area

- You should see the DFF fields are now available in the subject area –

 

Read More

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