Skip Headers
Oracle® Business Intelligence Applications New Features Guide
Version 7.9.6.2
Part Number E16811-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

5 Support for Currency Balance Types in Oracle Financial Analytics for Oracle EBS and Oracle's PeopleSoft

This chapter contains the following topics:

5.1 Oracle EBS Currency Balance Types

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:

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

5.2 PeopleSoft Currency Balance Types

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:

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

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us