About Oracle EBS Currency Balance Types
The ETL process extracts four different currency balance types from Oracle EBS, namely base currency, translated currency, entered currency, and statistical balance. These records are loaded and stored in W_GL_BALANCE_F table in the Oracle Data Warehouse.
-
LOC_CURR_CODE
-
ACCT_CURR_CODE
-
TRANSLATED_FLAG
-
BALANCE_LOC_AMT
-
BALANCE_ACCT_AMT
Line | Ledger Name | LOC_CURR_CODE | ACCT_CURR_CODE | TRANSLATED_FLAG | BALANCE_LOC_AMT | BALANCE_ACCT_AMT | Balance Type Description |
---|---|---|---|---|---|---|---|
1 | US Ledger | USD | Null | N | 200 | 0 |
A Base Currency Balance record with base ledger currency of USD. |
2 | US Ledger | USD | GBP | Y | 0 | 100 |
A Translated Currency Balance record with translation currency of GBP (British pounds). USD is the base ledger currency. |
3 | US Ledger | USD | JPY | N | 0 | 10000 |
An Entered Currency Balance record with entered currency in JPY (USD is the base ledger currency). |
4 | US Ledger | USD | STAT | N | 0 | 150 |
A Statistical balance amount. |
Note:
Ledger 'US Ledger' corresponds to LEDGER_WID = 85228 in the following sample scenarios.Sample | Desired Queries | Physical Table Value Column | Physical Query Filters | Result for Value Column |
---|---|---|---|---|
A |
To query: balance amount for base currency of USD, in ledger 'US Ledger' |
BALANCE_LOC_AMT |
LEDGER_WID = 85228 |
200 |
B |
To query: translated balance amount for translated currency of GBP, in ledger 'US Ledger' |
BALANCE_ACCT_AMT |
LEDGER_WID 85228 AND TRANSLATED_FLAG = 'Y' AND ACCT_CURR_CODE = 'GBP' |
100 |
C |
To query: entered balance amount for entered currency of JPY, in ledger 'US Ledger' |
BALANCE_ACCT_AMT |
LEDGER_WID = 85228 AND ACCT_CURR_CODE = 'JPY' AND TRANSLATED_FLAG = 'N' |
10000 |
D |
To query: statistical balance amounts, in ledger 'US Ledger' |
BALANCE_ACCT_AMT |
LEDGER_WID = 85228 AND ACCT_CURR_CODE = 'STAT' |
150 |
Oracle BI Answers users can create reports on currency balance types by selecting the desired facts from the Financials - GL Balance Sheet presentation table, as shown in the following table.
Sample Scenario | Desired Report | Presentation Table | Presentation Column | Result in Report |
---|---|---|---|---|
A |
View the local currency balance amounts (Debit Local Amount), such as in sample query A in the preceding table |
Ledger Facts – GL Balance |
Ledger name Debit Local Amount |
Apply filter on: "Ledger Name" = 'US Ledger' Ledger Name = US Ledger Debit Local Amount = 200 |
B |
View the translated currency balance amounts (Debit Translated Amount), such as in sample query B in the preceding table |
Ledger Facts – GL Balance GL Balance Details |
Ledger Name Debit Translated Amount Translated Currency Code |
Apply filters on: "Ledger Name" = 'US Ledger' "Translated Currency Code" = 'GBP' Ledger Name = US Leger Debit Translated Amount = 100 Translated Currency Code = GBP |
C |
View the entered currency balance amounts (Debit Entered Amount), such as in sample query C in the preceding table |
Ledger Facts – GL Balance GL Balance Details |
Ledger Name Debit Entered Amount Entered Currency Code |
Apply filters on: "Ledger Name" = 'US Ledger' "Entered Currency Code" = 'JPY' Ledger Name = US Leger Debit Entered Amount = 10000 Entered Currency Code = JPY |
D |
View the statistical amount (Statistical Amount), such as in sample query D in the preceding table |
Ledger Facts - Balance Sheet Statement |
Ledger Name Statistical Amount |
Ledger Name = US Leger Statistical Amount = 150 |