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.
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