Following on from my previous blogs detailing how to setup and customise the Oracle EPM Integration Agent connection from Oracle Cloud EPM Planning to Oracle E-Business Suite (Oracle EBS), we are now ready to setup the drill-through component. Drill-through is a fantastic tool which allows users to analyse a specific data intersection in Oracle Cloud EPM Planning by opening the relevant page to view the selected account balances in Oracle EBS directly.

This blog will detail how to setup the drill-through URL for on-premises Oracle EBS. There are multiple components required to setup the URL, where the green text must be edited to define the drill query below:

http://<SERVER>:<PORT>/OA_HTML/RF.jsp?function_id=<InstallSpecific>&CALLING_PAGE=FDM_DRILLDOWN&resp_appl_id=<FromEBS>&resp_id=<FromEBS>&SOB_ID=<UserDefined>&LED_ID=<UserDefined>&fdm_per=<UserDefined>&fdm_ccid=<UserDefined>&fdm_currency=<UserDefined>&fdm_balance_type=<UserDefined>

Once defined, this URL will be referenced in the import format, to allow drill through on the loaded data intersection:

1. Server and Port

The first section <SERVER>:<PORT> is the server and port for the Oracle EBS environment. This can be found at the beginning of the EBS environment URL.

For example: server.local:8000

2. Finding the Function_ID value

The function ID indicates which page should be displayed as part of the URL. It is specific to each Oracle EBS environment and is generated at the time of install and configuration.

To retrieve the function id value, connect to your Oracle EBS environment and run the SQL query below:

SELECT FUNCTION_ID

FROM fnd_form_functions

WHERE function_name = ‘GLAADPAGE’

For example: function_id=52482

3. Resp_Appl_ID and Resp_id

These parameters indicate the application ID and responsibility ID from Oracle EBS. In our example, these are unknown so have both been set to -1. We use SSO across Oracle Cloud EPM Planning & Oracle EBS, however Oracle EBS will prompt for a login if this is not the case in your setup.

These parameters are hard coded in the URL as: &resp_appl_id=-1&resp_id=-1

4. Additional URL Parameters

The below parameters are required to allow the URL to open the correct data combination in EBS as part of the drill-through from EPM.

For each parameter, we can either hard code the options into the URL or pass references to the data management TDATASEG data columns using the $<TDATASEG_COLUMN>$ or to the import format columns using $$<IMPORT_FORMAT_COL>$$ format.

SOB_ID = Internal Set of Books ID. Set to 1 if unknown.
LED_ID = Internal Oracle EBS Ledger ID. If there is only one Ledger then this can be hardcoded, otherwise it will need to be referenced in the import format and mapped to a field or attribute in Oracle Cloud EPM Planning.
fdm_per = The Oracle EBS period name. This value must be the same as the period name in the Oracle EBS system and is case sensitive!
fdm_ccid = The Code Combination ID (CCID) for the data combination. As this changes for each data row, the CCID must be included in the import format and mapped to a field or attribute in Oracle Cloud EPM Planning.
fdm_currency = The Oracle EBS ledger currency. Again, if there is only one currency then this can be hardcoded.
fdm_balance_type = A for Actual, B for Budget (this can be hard coded)

5. Finding the correct TDATASEG column

To reference the Ledger ID & CCID in the drill URL, we have mapped the Ledger ID to dimension Source and the Code Combination ID to dimension Version in the GL import format.

Once we know which dimension is being used, we can find the TDATASEG column name in the Planning Target Application Details.

In our example, we can see that Source uses column UD11 and Version uses column UD1. Therefore, in the URL we have:

&LED_ID=$UD11$ (Ledger ID = Source = UD11)
&fdm_ccid=$UD1$ (Code Combination ID = Version = UD1)

6. Referencing Attribute Columns

For the GL Period, we used an attribute in the import format to reference the GL_Period source, which is the period in EBS format (e.g. JUL-20). This is very important as the drill-through will only work with Oracle EBS period format and is case sensitive!

We pick up the attribute for the period in the drill URL with notation &fdm_per=$ATTR1$

Piecing all these elements together, with a combination of hard coding and referencing TDATASEG table columns/attributes, we have the below parameters:

&SOB_ID=1&LED_ID=$UD11$&fdm_per=$ATTR1$&fdm_ccid=$UD1$&fdm_currency=GBP&fdm_balance_type=A

Here we have hard coded the set of books as 1, the currency as GBP and the balance type as A to indicate we’re drilling on Actuals.

7. Setting the drill URL

Once the URL is complete, it will be similar to the below:

http://server.local:8000/OA_HTML/RF.jsp?function_id=52482&CALLING_PAGE=FDM_DRILLDOWN&resp_appl_id=-1&resp_id=–1&SOB_ID=1&LED_ID=$UD11$&fdm_per=$ATTR1$&fdm_ccid=$UD1$&fdm_currency=GBP&fdm_balance_type=A

We can then assign the drill URL to the relevant GL import format. This will link the URL to data loaded using this import format.

Note: Drill-through from Oracle Smart View will open in the default browser set on your device.

Using Microsoft Internet Explorer is fully supported and works without an add-on, however to use drill-through from Smart View via Chrome or Firefox, you’ll need to install an add-on HERE.

Until next time.

Lydia

You can read many other useful Oracle Cloud EPM and NetSuite ERP blogs posted by my colleagues at Brovanture HERE