Oracle® Business Intelligence Applications New Features Guide Version 7.9.6.2 Part Number E16811-01 |
|
|
View PDF |
This chapter contains the following topics:
The ETL process in 7.9.6.2 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.
The following table shows sample records of the different balance types. All four records are from ledger 'US Ledger' with U.S. dollars (USD) as the base ledger currency. These five columns in the following table are columns in the physical data warehouse table:
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 |
Users can query these balances by using different value columns and filters. The following tables contain sample queries that you can use to view different currency balance types and their corresponding results from the sample warehouse data in the preceding table.
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 |
The ETL process in release 7.9.6.2 extracts four different currency balance types from PeopleSoft, namely base currency, translated currency, entered currency and statistical balance. These records are loaded and stored in the warehouse table W_GL_BALANCE_F. The following table shows some sample records of different balance types. The first four records are from Primary ledger 'Euro Ledger' with EUR as the base ledger currency. The next three records are from Translation ledger 'US Ledger' with USD as the base ledger currency. These five columns in the following table are columns in the physical data warehouse table:
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 | Euro Ledger (Primary ledger) | EUR | EUR | N | 390 | 50 | A Base Currency Balance record with base ledger currency of EUR |
2 | Euro Ledger
(Primary ledger) |
EUR | USD | N | 0 | 200 | An Entered Currency Balance record with entered currency in USD (EUR is the base ledger currency) |
3 | Euro Ledger
(Primary ledger) |
EUR | STAT | N | 0 | 80 | A Statistical balance amount |
4 | US Ledger
(Translation ledger) |
USD | USD | Y | 340 | 340 | A Translated Currency Balance record with translation currency of USD
Note: US Ledger is a Translation ledger |
5 | US Ledger
(Translation ledger) |
USD | EUR | N | 0 | 50 | An Entered Currency Balance record with entered currency in EUR (USD is the base ledger currency)
Note: US Ledger is a Translation ledger |
Users can query these balances by using different value columns and filters. The following table contains sample queries to show how to view different currency balance types and their corresponding results from the sample warehouse data in the preceding table.
Note:
Primary ledger 'Euro Ledger' corresponds to LEDGER_WID = 85228 and Translation ledger 'US Ledger' corresponds to LEDGER_WID = 87320 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 EUR, in Primary ledger 'Euro Ledger' | BALANCE_LOC_AMT | LEDGER_WID = 85228 | 390 |
B | To query: entered balance amount for entered currency of EUR in Primary ledger 'Euro Ledger' | BALANCE_ACCT_AMT | LEDGER_WID = 85228
AND ACCT_CURR_CODE = 'EUR' AND TRANSLATED_FLAG = 'N' |
50 |
C | To query: entered balance amount for entered currency of USD, in Primary ledger 'Euro Ledger' | BALANCE_ACCT_AMT | LEDGER_WID = 85228
AND ACCT_CURR_CODE = 'USD' AND TRANSLATED_FLAG = 'N' |
200 |
D | To query: statistical balance amount, in Primary ledger 'Euro Ledger' | BALANCE_ACCT_AMT | LEDGER_WID = 85228
AND ACCT_CURR_CODE = 'STAT' |
80 |
E | To query: translated balance account amount for translated currency of USD, in Translation ledger 'US Ledger' | BALANCE_ACCT_AMT | LEDGER_WID = 87320
AND TRANSLATED_FLAG = 'Y' |
340 |
F | To query: entered balance amount for entered currency of EUR, in Translation ledger 'US Ledger' | BALANCE_ACCT_AMT | LEDGER_WID = 87320
AND ACCT_CURR_CODE = 'EUR' AND TRANSLATED_FLAG = 'N' |
50 |
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" = 'Euro Ledger' Ledger Name = Euro Ledger Debit Local Amount = 390 |
B | View the local currency balance amounts (Debit Amount), such as in sample query B in the preceding table | Ledger
Facts – GL Balance |
Ledger Name
Debit Amount |
Apply filter on:
"Ledger Name" = 'Euro Ledger' Ledger Name = Euro Ledger Debit Amount = 50 |
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" = 'Euro Ledger' Entered Currency Code" = 'USD' Ledger Name = Euro Ledger Debit Entered Amount = 200 Entered Currency Code = USD |
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 = Euro Ledger Statistical Amount = 80 |
E | View the translated currency balance amounts (Debit Translated Amount), such as in sample query E 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" = 'USD' Ledger Name = US Ledger Debit Translated Amount = 340 Translated Currency Code = USD |
F | If we want to view the Entered currency balance amounts (Debit Entered Amount), such as in sample query F 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" = 'EUR' Ledger Name = US Ledger Debit Entered Amount = 50 Entered Currency Code = EUR |