Drill-through from EPM Planning to Oracle EBS

Following on from my previous blogs detailing how to setup and customise the EPM Integration Agent connection from EPM Planning to 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 EPM by opening the relevant page to view the selected account balances in EBS directly.

This blog will detail how to setup the drill-through URL for on-premise Oracle E-Business Suite.

There are multiple components required to setup the URL, where the bold 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 EBS environment and is generated at the time of install and configuration.

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

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 EPM & EBS, however 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 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 EPM.
  • fdm_per = The EBS period name.  This value must be the same as the period name in the 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 EPM.
  • fdm_currency = The EBS ledger currency. Again, if 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 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 Smart View will open in the default browser set on your device.

Using 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: https://docs.oracle.com/applications/smartview/810/SVICF/browser_add_on_requirement_100xeeb0c08a.htm#SVICF-GUID-4269A432-78F5-46DC-A02A-90301044DDA6

 

Comments

Post a Comment

Popular posts from this blog

Loading multi-period row data files using Data Management

Image
The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.10), however it is something that most people seem to forget is possible in Data Management. This really is something worth remembering as it eliminates the need to either split the data files into one file per month or try to pivot the month into the columns. In order to set this up, let’s firstly look at how to add the Period and Year columns to the import format. Add the Period and Year columns using the Add -> Source Period Row dropdown, and input: The Source Column name – e.g. PERIOD The Field Number (file column) – e.g. 3 Any required expressions for that field  As these are Period/Year dimensions, the mappings are not held in the usual Data Load Mappings area. Instead they are configured in the Period Mappings , under the Source Mapping tab. Select the Source System as File to add a new mapping calendar for loading the data ...
Read more

Executing Smart View Retrievals using VBA

Image
When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections. Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves. This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide .  System Requirements ...
Read more

Loading Actuals from Fusion ERP Cloud to PBCS

Image
If you’re looking to set up a data load of Actuals from ERP to PBCS and have heard this is an “Out of the box” integration, then good news! Not only is the OOTB connector included within PBCS Data Management but the integration is simple to setup and to schedule using EPM Automate. This blog will detail how to setup the ERP to PBCS connector in Data Management and some of the common errors found when running the data load. The first thing to know about this integration is that the data source is actually the ASO Essbase cube which sits underneath the Fusion ERP tables, holding the period Actuals values. This ERP Essbase cube is created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system. This cube must be created before the integration can be set up correctly and can be accessed directly via Smart View for reconciliations. Set up the Source System As this is an Oracle built connector, it’s as simple as providing a name for the source system ...
Read more