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.

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