The Business requirements
My conglomerate has operations in 14 other counties span over APAC, EMEA and Latin America while headquarter is based out of Washington DC USA. We have configured separate primary ledger for each country that has a different currency than USD while balances are translated through the respective USD reporting ledgers.
my client does not want to opt in for secondary ledger’s subledger conversation for some implication related to the unrealized gain and loss.As per US GAAP's ASC 830 (FAS 52) and foreign currency translation for balance sheet the balances are translated using period end conversion rate while P&L balances are translated using the average rate.
My client would like to create an OTBI analysis across subject areas to report on the GL USD & translated balances and their respective subledger details e.g. supplier, invoice and POET.
· The translated balances do not provide the capability to drill down to subledger therefore we can not show all journals in USD and forex amounts combined with the SLA information.
The conversion process is broken into three parts:
1- Upload the average rates through statistical journal with the following information.
a dedicated stat GL account in our example is account #777777
and the journal line description is foreign currency code e.g. GBP, EUR or JYP.
This journal should be uploaded once every period name.
2- Use OBIEE’s function FILITER(Expr).
Without this function the report GL balances report will look like the below figure where STAT and actual journals will show on separate rows.
3- Use CASE WHEN function to base your condition on each row’s currency and period.
4- The below figure to show you the result on the last two columns if the currency is GBP or EUR
We will later combine the last two columns on one column and call it average rate.
Providing overview of the business operations’ proximity. Allowing the financial controlled to get a ballpark oversight on what is happening across operations with the journal and subledger information.
if you have any questions please get in touch