This chapter describes the Oracle Transfer Pricing reports and the procedure for generating and viewing them.
This chapter covers the following topics:
Oracle Transfer Pricing (FTP) leverages fact data defined in two core business areas, EPF and FTP, to provide you with reports to analyze data from business processes in your application or to evaluate the quality of your business data. The EPF and FTP business areas reside in a standard Oracle Applications end user layer (EUL_US) and are populated through facts, joins, and lookup tables. See:
Oracle Transfer Pricing (FTP) provides you the following three types of reports:
Data Management reports: Use the data management reports to evaluate the quality of your data. See: Overview of Data Management Reports.
Audit reports: Use the Oracle Financial Services (OFS) audit reports to view the results of cash flow edits and cash flow processing. See: Overview of Oracle Financial Services Audit Reports.
Transfer Pricing reports: Use the transfer pricing reports to find out the account level match funded spread and the interest margin for each product in your portfolio or for a transfer rate stratification range.
The following table list the transfer pricing reports and their Discoverer file names.
| Report Name | Discoverer Workbook Title | Discoverer Worksheet Title |
|---|---|---|
| STD - FTP Interest Margin Account Detail | STD - FTP Interest Margin Account Detail | FTP Interest Margin Account Detail |
| STD - FTP Interest Margin (Org/Product, Summary) | STD - FTP Interest Margin (Org/Product Summary) | FTP Interest Margin (Org/Product Summary) |
| STD - FTP Margin Stratification | STD - FTP Margin Stratification |
|
You can run these reports either from the Document tab of the Oracle Transfer Pricing application, or use the Oracle Discoverer Desktop or Discoverer Administration applications themselves. See:
Oracle Business Intelligence Discoverer Desktop User's Guide and Oracle Business Intelligence Discoverer Administration Guide
This hierarchical report is based on account-level data and shows both the account level match funded spread and the interest margin for each product.
This reports lets you:
Select the hierarchy and appropriate calendar period.
Drill down through the various levels of the hierarchy.
The STD - FTP Interest Margin Account Detail report is based on the following business area folders:
EPF Currencies: This simple dimension folder is based on the FEM_CURRENCIES_VL view.
EPF Datasets: This simple dimension folder is based on the FEM_DATASETS_VL view.
EPF Ledgers: This simple dimension folder is based on the FEM_LEDGERS_VL view.
EPF All Account Tables: This is a custom folder that joins instrument tables and presents views that link them.
Line Items Dimension Hierarchy: This is a hierarchical dimension folder. Each hierarchy folder contains the hierarchy definition information and up to twenty levels of parent-child relationships. Within each level, the internal dimension member ID, member display code, and member name and description are available.
The STD - FTP Interest Margin Account Detail report makes use of the following joins to retrieve data:
EPF Currencies.Currency code -> EPF All Account Tables.Currency
EPF Datasets.Dataset code -> EPF All Account Tables.Dataset
EPF Ledgers.Ledger ID -> EPF All Account Tables.Ledger
Line Items Dimension Hierarchy.Level20 ID -> EPF All Account Tables.Line Item
The STD - FTP Interest Margin Account Detail report uses the following condition to filter data:
Ledger = Ledger and Dimension Value Set Mapping
The STD - FTP Interest Margin Account Detail report is based on the following parameters:
Ledger
Dataset
Effective Date
Currency
Hierarchy Name
Hierarchy Version
The STD - FTP Interest Margin Account Detail report has the following row:
Line Item ID (Hierarchy Levels 2 and 3)
The STD - FTP Interest Margin Account Detail report has these headings and calculations (columns). If applicable, the calculation, source, or default value for the column is provided below.
Record Count
Calculation: COUNT(1)
Average Balance
Source: Average Gross Book Balance
Calculation: SUM(Average Gross Book Balance)
Ending Balance
Source: Current Gross Book Balance
Calculation: SUM(Current Gross Book Balance)
Current Net Rate
Calculation: SUM(EPF All Account Tables.Weighted Current Net Rate)/SUM(EPF All Account Tables."Sum(Current Gross Par Balance)")
Transfer Rate
Calculation: SUM(EPF All Account Tables.Weighted Transfer Rate)/SUM(EPF All Account Tables."Sum(Current Gross Par Balance)")
% Margin
Calculation: SUM(EPF All Account Tables.Weighted Matched Spread)/SUM(EPF All Account Tables."Sum(Current Gross Par Balance)")
Interest Income/Expense
Calculation: Current Net Rate*"Sum(Average Gross Book Balance) SUM"*(30/36000)
TP Charge/Credit
Calculation: Transfer Rate*"Sum(Average Gross Book Balance) SUM"*(30/36000)
% Interest Margin
Calculation: "%Margin"*"Sum(Average Gross Book Balance) SUM"*( 30/36000 )
Related Topics
Overview of Oracle Transfer Pricing Reports
Generating and Viewing Reports
This hierarchical report is based on summarized ledger data and shows both the account-level match funded spread and the interest margin for each product.
The STD - FTP Interest Margin Account (Org/Product, Summary) report is based on the following business area folders:
EPF Currencies: This simple dimension folder is based on the FEM_CURRENCIES_VL view.
EPF Datasets: This simple dimension folder is based on the FEM_DATASETS_VL view.
EPF Ledgers: This simple dimension folder is based on the FEM_LEDGERS_VL view.
EPF Balances: This simple folder is based on the FEM_BALANCES table and contains summarized ledger data.
EPF Natural Account Dimension Hierarchy: This is a hierarchical dimension folder based on the FEM_DIS_NAT_ACCTS_HIER_VL view.
Each hierarchy folder contains the hierarchy definition information and up to twenty levels of parent-child relationships. Within each level, the internal dimension member ID, member display code, and member name and description are available.
The FEM_DIS_NAT_ACCTS_HIER_VL view is a hierarchy transformation view based on the FEM_NAT_ACCTS_HIER table. LEVEL1 to LEVEL20 columns in FEM_DIS_NAT_ACCTS_HIER_VL view represent hierarchy levels 1 through 20.
EPF Company Cost Center Organizations: This simple dimension folder is based on the FEM_CCTR_ORGS_VL view.
EPF Line Items: This simple dimension folder is based on the FEM_LN_ITEMS_VL view.
The STD - FTP Interest Margin Account Detail (Org/Product, Summary) report makes use of the following joins to retrieve data:
EPF Currencies.Currency code -> EPF Balances.Currency
EPF Datasets.Dataset code -> EPF Balances.Dataset
EPF Ledgers.Ledger Id -> EPF Balances.Ledger
EPF Line Items.Line Item Id -> EPF Balances.Line Item
EPF Company Cost Center Organizations.Company Cost Center Org Id -> EPF Balances.Company Cost Center Organization
Natural Accounts Dimension Hierarchy.Level20 Id -> EPF Balances.Natural Account
The STD - FTP Interest Margin Account Detail (Org/Product, Summary) report uses the following conditions to filter data:
EPF Balances.Financial Element IN (140,100,170,420,450)
Ledger and Company Cost Center Organization Dimension Value Set Mapping
Ledger and Natural Account Dimension Value Set Mapping
Ledger and Line Item Dimension Value Set Mapping
The STD - FTP Interest Margin Account Detail (Org/Product, Summary) report is based on the following parameters:
Ledger
Dataset
Currency
Effective Date
Company Cost Center Organization
Line Item
Hierarchy Name
Hierarchy Version
The STD – FTP Interest Margin Account (Org/Product Summary) report displays Natural Accounts included in the Natural Account hierarchy as rows (display defaults to level 3 of the hierarchy).
The STD - FTP Interest Margin Account Detail (Org/Product, Summary) report has these headings and calculations (columns). If applicable, the calculation, source, or default value for the column is provided below.
Average Balance
Calculation: SUM(DECODE(EPF Balances.Financial Element,140,EPF Balances.Period to Date Balance Entered,0))
Ending Balance
Calculation: SUM(DECODE(EPF Balances.Financial Element,100,EPF Balances.Period to Date Balance Entered,0))
Current Rate
Calculation: DECODE(SUM(DECODE(EPF Balances.Financial Element,140,EPF Balances.Period to Date Balance Entered,0)),0,0,SUM(DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0))/SUM(DECODE(EPF Balances.Financial Element,140,EPF Balances.Period to Date Balance Entered,0))*360/30)
Transfer Rate
Calculation: DECODE(SUM(DECODE(EPF Balances.Financial Element,140,EPF Balances.Period to Date Balance Entered,0)),0,0,SUM(DECODE(EPF Balances.Financial Element,170,EPF Balances.Period to Date Balance Entered,0))/SUM(DECODE(EPF Balances.Financial Element,140,EPF Balances.Period to Date Balance Entered,0)))
% Margin
Calculation: DECODE(SUM(DECODE(EPF Balances.Financial Element,140,EPF Balances.Period to Date Balance Entered,0)),0,0,SUM(DECODE(EPF Get Dimension Attribute Value('NATURAL_ACCOUNT','EXTENDED_ACCOUNT_TYPE',EPF Balances.Natural Account,Natural Accounts Dimension Hierarchy.Value set id,NULL,NULL),'ASSET',DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0)*360/30-DECODE(EPF Balances.Financial Element,170,EPF Balances.Period to Date Balance Entered,0),'EARNING ASSET',DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0)*360/30-DECODE(EPF Balances.Financial Element,170,EPF Balances.Period to Date Balance Entered,0),DECODE(EPF Balances.Financial Element,170,EPF Balances.Period to Date Balance Entered,0)-DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0)*360/30))/SUM(DECODE(EPF Balances.Financial Element,140,EPF Balances.Period to Date Balance Entered,0)))
Interest Income/Expense
Calculation: SUM(DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0))
TP Charge/Credit
Calculation: SUM(DECODE(EPF Balances.Financial Element,450,EPF Balances.Period to Date Balance Entered,0))
Interest Margin
Calculation: SUM(DECODE(EPF Get Dimension Attribute Value('NATURAL_ACCOUNT','EXTENDED_ACCOUNT_TYPE',EPF Balances.Natural Account,Natural Accounts Dimension Hierarchy.Value set id,NULL,NULL),'ASSET',DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0)-DECODE(EPF Balances.Financial Element,450,EPF Balances.Period to Date Balance Entered,0),'EARNING ASSET',DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0)-DECODE(EPF Balances.Financial Element,450,EPF Balances.Period to Date Balance Entered,0),DECODE(EPF Balances.Financial Element,450,EPF Balances.Period to Date Balance Entered,0)-DECODE(EPF Balances.Financial Element,420,EPF Balances.Period to Date Balance Entered,0)))
Related Topics
Overview of Oracle Transfer Pricing Reports
Generating and Viewing Reports
This report is based on the account level data and lets you define up to 10 transfer rate stratification ranges. The report shows both the matched spread and interest margin for each stratification range. The report comprises two Discoverer worksheets:
TP Margin Stratification: This worksheet provides a stratification on transfer rate for a single Calendar Period.
TP Margin Stratification Over Time: This worksheet provides the same information but with a stratification over time.
Both the worksheets of the STD - FTP Margin Stratification report are based on the following business area folder:
EPF Currencies: This simple dimension folder is based on the FEM_CURRENCIES_VL view.
EPF Datasets: This simple dimension folder is based on the FEM_DATASETS_VL view.
EPF Ledgers: This simple dimension folder is based on the FEM_LEDGERS_VL view.
EPF All Account Tables: This is a custom folder that joins instrument tables and presents views that link them.
EPF Company Cost Center Organizations: This simple dimension folder is based on the FEM_CCTR_ORGS_VL view.
EPF Line Items: This simple dimension folder is based on the FEM_LN_ITEMS_VL view.
EPF Natural Accounts: This simple dimension folder is based on the FEM_NAT_ACCTS_VL view.
Both the worksheets of the STD - FTP Margin Stratification report make use of the following joins to retrieve data:
EPF Currencies.Currency code -> EPF All Account Tables.Currency
EPF Datasets.Dataset code -> EPF All Account Tables.Dataset
EPF Ledgers.Ledger Id -> EPF All Account Tables.Ledger
EPF Company Cost Center Organizations.Company Cost Center Org Id -> EPF All Account Tables.Company Cost Center Organization
EPF Line Items.Line Item Id -> EPF All Account Tables.Line Item
EPF Natural Accounts.Natural Account Id -> EPF All Account Tables.Natural Account
The STD - FTP Interest Margin Account Detail (Org/Product, Summary) report uses the following conditions to filter data:
TP Margin Stratification worksheet:
Ledger and Company Cost Center Organizations Dimension Value Set Mapping
Ledger and Natural Account Dimension Value Set Mapping
Ledger and Line Items Dimension Value Set Mapping
TP Margin Stratification Over Time worksheet:
Ledger and Company Cost Center Organizations Dimension Value Set Mapping
Ledger and Natural Account Dimension Value Set Mapping
Ledger and Line Items Dimension Value Set Mapping
EPF All Account Tables (Calendar Period End Date BETWEEN :end_date_lAND:end_date_h)
The STD - FTP Margin Stratification report is based on the following parameters:
Ledger
Dataset
Currency
Company Cost Center Organization
Natural Account
Line Item
The STD - FTP Margin Stratification report has the following row:
Instrument Type Code
Calculation:
Stratification – Transfer Rate: DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T1L),:S1_T1H),EPF All Account Tables.Non weighted Transfer Rate,'01. 0 - '||:S1_T1H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T2L),:S1_T2H),EPF All Account Tables.Non weighted Transfer Rate,'02. '||:S1_T2L||' - '||:S1_T2H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T3L),:S1_T3H),EPF All Account Tables.Non weighted Transfer Rate,'03. '||:S1_T3L||' - '||:S1_T3H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T4L),:S1_T4H),EPF All Account Tables.Non weighted Transfer Rate,'04. '||:S1_T4L||' - '||:S1_T4H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T5L),:S1_T5H),EPF All Account Tables.Non weighted Transfer Rate,'05. '||:S1_T5L||' - '||:S1_T5H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T6L),:S1_T6H),EPF All Account Tables.Non weighted Transfer Rate,'06. '||:S1_T6L||' - '||:S1_T6H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T7L),:S1_T7H),EPF All Account Tables.Non weighted Transfer Rate,'07. '||:S1_T7L||' - '||:S1_T7H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T8L),:S1_T8H),EPF All Account Tables.Non weighted Transfer Rate,'08. '||:S1_T8L||' - '||:S1_T8H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T9L),:S1_T9H),EPF All Account Tables.Non weighted Transfer Rate,'09. '||:S1_T9L||' - '||:S1_T9H,DECODE(LEAST(GREATEST(EPF All Account Tables.Non weighted Transfer Rate,:S1_T10L),:S1_T10H),EPF All Account Tables.Non weighted Transfer Rate,'10. '||:S1_T10L||' - '||:S1_T10H,'Other'))))))))))
Both worksheets:
Record Count
Calculation: COUNT(1)
Average Balance
Source: Average Gross Book Balance
Calculation: Sum(Average Gross Book Balance)
Ending Balance
Source: Current Gross Book Balance
Calculation: Sum(Current Gross Book Balance)
Current Rate
Calculation: SUM(EPF All Account Tables.Weighted Current Net Rate)/SUM(EPF All Account Tables."Sum(Current Gross Par Balance)")
Transfer Rate
Calculation: SUM(EPF All Account Tables.Weighted Transfer Rate)/SUM(EPF All Account Tables."Sum(Current Gross Par Balance)")
% Margin
Calculation: SUM(EPF All Account Tables.Weighted Matched Spread)/SUM(EPF All Account Tables."Sum(Current Gross Par Balance)")
TP Margin Stratification worksheet:
Interest Income/Expense
Calculation: (Current Rate*EPF All Account Tables."Sum(Average Gross Book Balance)" )*30/36000
TP Charge/Credit
Calculation: (Transfer Rate*EPF All Account Tables."Sum(Average Gross Book Balance)" )*30/36000
Interest Margin
Calculation: ("% Margin"*EPF All Account Tables."Sum(Average Gross Book Balance)" )*30/36000
TP Margin Stratification Over Time worksheet:
End Date (header level column)
Source: Calendar Period End Date
Grand Total Rows Sum for Record Count
Related Topics