Global Currencies in Oracle BIA – The Sequel

I have blogged about Global Currencies in Oracle BIA recently. Global Currencies are used to report in different Currencies like USD, GBP, EUR. If you use the Global Currencies to report the same Currencies (eg. EUR) against different Exchange Rate (Types) you might end up with a challenge, because the Out-of-the-Box Oracle BIA ETL expects different Currency Codes.

If you take eg. the; ‘MPLT_CURCY_CONVERSION_RATES_ToGlobalCurrenciesOnly’-Mapplet, you will notice the;  ‘DOC_TO_GLOBAL2_EXCH_RATE_OUT’-Port. This Port is based on the; ‘DOC_TO_GLOBAL2_EXCH_RATE_VAR’-Port.

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES_ToGlobalCurrenciesOnly - DOC_TO_GLOBAL2_EXCH_RATE_VAR

If you further analyze the expression in the; ‘DOC_TO_GLOBAL2_EXCH_RATE_VAR’-Port, you can see that it’s made up of a few different checks.

DOC_TO_GLOBAL2_EXCH_RATE_VAR

1. If GLOBAL2_CURR_CODE = NULL then populate GLOBAL2_EXCH_RATE = NULL

2. If previous condition(s) not satisfied, then; If GLOBAL2_CURR_CODE = GLOBAL1_CURR_CODE then populate GLOBAL2_EXCH_RATE = GLOBAL1_EXCH_RATE

3. If previous condition(s) not satisfied, then; If GLOBAL2_CURR_CODE = DOC_CURR_CODE, then populate GLOBAL2_EXCH_RATE = 1.0

4. If previous condition(s) not satisfied, then; If DOC_CURR_CODE = ‘STAT’, then populate GLOBAL2_EXCH_RATE = 1.0

5. If previous condition(s) not satisfied, then; If GLOBAL2_CURR_CODE = LOC_CURR_CODE, then populate GLOBAL2_EXCH_RATE = DOC_TO_LOC_EXCHANGE_RATE_VAR else lookup on W_EXCH_RATE_G on the condition (DOC_CURR_CODE, GLOBAL2_CURR_CODE, EXCH_DT, GLOBAL2_RATE_TYPE_VAR, DATASOURCE_NUM_ID). The output of this Lookup is used to populate the; GLOBAL2_EXCH_RATE.

In the case of the same Global Currencies (eg. EUR) the execution of the above expression will stop at condition 2 and populate the GLOBAL2_EXCH_RATE-column with the value of GLOBAL1_EXCH_RATE. Although this behavior seems logical from an Oracle BIA perspective, it might not be satisfying as you cannot compare GLOBAL1_EXCH_RATE with GLOBAL2_EXCH_RATE.

In this case you will end up with a customization on the; the; ‘MPLT_CURCY_CONVERSION_RATES_ToGlobalCurrenciesOnly’-Mapplet. The easiest way is to copy the logic in the; DOC_TO_GLOBAL1_EXCH_RATE_VAR’-Port and apply it to the DOC_TO_GLOBAL2_EXCH_RATE_VAR’-Port. Of course you have to replace the references to; ‘GLOBAL1’ with; ‘GLOBAL2’.

DOC_TO_GLOBAL1_EXCH_RATE_VAR

 

Subledger Accounting (Oracle eBS R12) for Oracle BI Applications

I am currently reading the following book; ‘Oracle Business Intelligence Applications: Deliver Value Through Rapid Implementations’. As far as I know it’s the first and only book on Oracle BI Applications. In Chapter 5: Financial Analytics there is a paragraph about analyzing on Subledger Accounting (SLA) Transactions. This functionality is not in the Out-of-the-Box version of Oracle BIA 7.9.6.3, but Oracle provides a patch to make SLA Transaction analyzing possible.

If you want more details about this patch, have look on My Oracle Support. Search for; Patch 13697336 – PROVISION FOR LOADING SUB-LEDGER SLA ENTRIES FOR EXCHANGE GAIN/LOSS (Patch)

It could be of added value to my current project, so I will check it out. More on this later.

Validating the Group Account Mapping in Oracle BIA 7963

Configuring Oracle BIA is one of the crucial steps when implementing Oracle BIA. If you omit this step or you perform this step incorrectly, you won’t see the correct figures in your Oracle BIA Dashboards. One of the major steps is the Group Account Mapping.

For Oracle eBS installations, you probably won’t (at least not here in the Netherlands) use the Group Accounts in e.g. the Balance Sheet. It’s more likely that custom GL hierarchies are used. Nevertheless it is essential/crucial that the Group Account Mapping is performed correctly. Oracle BIA will use this Group Account Mapping to decide e.g. whether a record should go an AR-Fact table or to AP.

As a Technical OBIA Implementation Consultant I would advice you to consult a Functional eBS Implementation Consultant to perform the Group Account Mapping. If you still want to have a look at it yourself, you could have a look at the Account Segment Hierarchy in Oracle BIA, to see whether you can map Parents of the Accounts to Group Accounts. This might give you some more insight in the ranges you will need.

In the end, if you want to check whether the Group Account Mapping is correctly executed, you can use the Oracle BIA out-of-the-Box Financial Dashboard. Navigate to the ‘Financials – General Ledger’-Dashboard. Select either the ‘GL Balance’- or the ‘Trial Balance’-page. Filter on Group Account Number; values ‘NULL’ & ‘OTHERS’. If an Account isn’t mapped to a Group Account, the Oracle BIA ETL automatically maps the Account to ‘OTHERS’. Check whether the analysis returns Accounts and still map these Accounts.