Data Management Reports

This chapter describes the reports you use to analyze the quality of your data.

This chapter covers the following topics:

• Overview of Data Management Reports
• GL Instrument Reports
• Stratification Reports

Overview of Data Management Reports

Use the data management reports to evaluate the quality of your data. There are two sets of data management reports.

• GL Instrument Reports: Provides information on balances in the instrument account tables. See: GL Instrument Reports.

• Stratification Reports: Lets you verify the accuracy of various types of account level data after the data is imported into your system. See: Stratification Reports.

The data management reports leverage data defined in the EPF business area, which refers to objects in the FEM schema. This business area resides in a standard Oracle Applications end user layer (EUL_US) and is populated through facts, joins, and lookup tables. See: Core EPF Business Area and its Components.

Related Topics

Overview of the Reports

GL Instrument Reports

This table lists the GL instrument reports and their Discoverer file names.

Data Management: GL Instrument Reports

Report Name	Discoverer Workbook Title	Discoverer Worksheet Title
STD – DATA Instrument to GL Reconciliation	STD - DATA Instrument to GL Reconciliation	Instrument to GL Reconciliation Ending Balances Instrument to GL Reconciliation
STD – GL Instrument Upload	GL Instrument Upload Report	STD - Data GL Instrument Upload Internal worksheet

Related Topics

Overview of Data Management Reports

STD – DATA Instrument to GL Reconciliation

Use the STD – DATA Instrument to GL Reconciliation report to compare account table balances to the equivalent GL account balances. The report highlights large variance between the two balances to inform you of this data quality issue, and creates "plug" records that you can insert into your account data to correct the variance.

Note: The GL balance is assumed to be correct, so the account balance should match it as closely as possible.

This report is based on a custom folder, EPF Reconciliation, that creates a union between all relevant FEM account tables together with the GL account balances from the FEM_BALANCES table. The report takes a snapshot of the GL data in FEM_BALANCES, which is not necessarily what is currently in your GL data source, for example Oracle General Ledger.

The STD – DATA Instrument to GL Reconciliation contains two worksheets.

• Instrument to GL Reconciliation: Ending Balances worksheet: Shows the results of the reconciliation in terms of variance amount and percentage. Any variance greater than 2% is highlighted in red to help you focus your attention on the relevant balances.

• Instrument to GL Reconciliation worksheet: Creates "plug" account table records to make up for the variance, by assigning the variance amount to the balance fields, for example Current Gross Par Balance. Other account record attributes are defined with default values.

  Tip: You can export this worksheet into either the Excel or ASCII format, and load the data into account tables with SQL Loader.

Generally, you run this report after loading data for a new calendar period into the account and FEM_BALANCES tables. You can also resubmit the Instrument to GL Reconciliation: Ending Balances worksheet after loading the plug records into account tables, to confirm that the load was successful and that the data reconciles.

Business Area Folders

The STD – DATA Instrument to GL Reconciliation report is based on these folders.

• Both worksheets:

  • EPF Company Cost Center Organizations: This simple dimension folder is based on the FEM_CCTR_ORGS_VL view.

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

• Instrument to GL Reconciliation: Ending Balances worksheet:

  • Natural Accounts Dimension Hierarchy: This simple hierarchy folder is based on the FEM_DIS_NAT_ACCTS_HIER_VL view.

  • EPF Reconciliation: This custom account folder union's folder is based on the FEM_RECONCILIATION table and creates a union between all relevant FEM account tables together with the GL account balances from FEM_BALANCES.

• Instrument to GL Reconciliation worksheet:

  • EPF Calendar Periods: This simple dimension folder is based on the FEM_CAL_PERIODS_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.

Joins

The STD – DATA Instrument to GL Reconciliation report uses these joins to retrieve data.

• Both worksheets:

  • EPF Currencies.Currency code -> EPF Reconciliation.Currency

  • EPF Datasets.Dataset code -> EPF Reconciliation.Dataset

  • EPF Ledgers.Ledger Id -> EPF Reconciliation.Ledger

  • EPF Company Cost Center Organizations.Company Cost Center Org Id -> EPF Reconciliation.Company Cost Center Organization

• Instrument to GL Reconciliation: Ending Balances worksheet:

  Natural Accounts Dimension Hierarchy.Level20 Id -> EPF Reconciliation.Natural Account

• Instrument to GL Reconciliation worksheet:

  • EPF Line Items.Line Item Id -> EPF Reconciliation.Line Item

  • EPF Natural Accounts.Natural Account Id -> EPF Reconciliation.Natural Account

  • EPF Calendar Periods.Cal Period Id -> EPF Reconciliation.Calendar Period

Conditions

These conditions apply to the STD – DATA Instrument to GL Reconciliation report.

• Both worksheets:

  Ledger and Company Cost Center Organization Dimension Value Set Mapping

• Instrument to GL Reconciliation: Ending Balances worksheet:

  Ledger and Natural Account Hierarchy Dimension Value Set Mapping

• Instrument to GL Reconciliation worksheet:

  • Ledger and Line Item Dimension Value Set Mapping

  • Ledger and Natural Account Dimension Value Set Mapping

Page Items

The STD – DATA Instrument to GL Reconciliation report is based on these parameters.

• Both worksheets:

  • Dataset

  • Ledger

• Instrument to GL Reconciliation: Ending Balances worksheet:

  • Effective Date (Uses Calendar Period End Date)

  • Currency

  • Company Cost Center Organization

  • Hierarchy Name

  • Hierarchy Version

• Instrument to GL Reconciliation worksheet:

  Calendar Period

Rows

The Instrument to GL Reconciliation: Ending Balances worksheet displays Natural Accounts included in the Natural Account hierarchy as rows (display defaults to level 20 of the hierarchy). The Instrument to GL Reconciliation worksheet has no rows.

Report Headings and Calculations (Columns)

The STD – DATA Instrument to GL Reconciliation report has these columns. If applicable to the column, also provided below is the:

• Calculation: The formula used to calculate the column value.

• Source: The direct source of the column value, for example another column in the worksheet, meaning that the value for one column is equal to another column's value.

• Default: The actual value that defaults into the column.

Note: You can also view or even change the calculation, source, or default when you access calculation details in the Discoverer UI for specific fields.

• Instrument to GL Reconciliation: Ending Balances worksheet:

  • Instrument Balance

  • GL Balance

  • Variance

    Calculation: SUM(EPF Reconciliation.GL Balance-EPF Reconciliation.Instrument Balance)

  • % Variance

    Calculation: DECODE(Lsbal SUM,0,-1,SUM(EPF Reconciliation.GL Balance-EPF Reconciliation.Instrument Balance)/Lsbal SUM)

• Instrument to GL Reconciliation worksheet:

  • Company Cost Center Organization

  • Currency

  • Natural Account

  • Line Item

  • Current Gross Par Balance

    Calculation: EPF Reconciliation.GL Balance-EPF Reconciliation.Instrument Balance

  • Current Book Balance

    Source: Current Gross Par Balance column

  • Accrual Basis Code

    Default Value: ACT/ACT

  • Adjustable Type Code

    Default Value: 0

  • Amortization Term

    Default Value: 1

  • Amortization Term Multiplier

    Default Value: M

  • Amortization Type Code

    Default Value: 700

  • Compound Basis Code

    Default Value: 160

  • Current Net Rate

    Default Value: 5

  • Current Payment

    Default Value: 0

  • Interest Rate Code

    Default Value: 0

  • Interest Type

    Default Value: 1

  • Issue Date

    Source: EPF Reconciliation.Calendar Period End Date

  • Last Payment Date

    Source: EPF Reconciliation.Calendar Period End Date

  • Last Repricing Date

    Source: EPF Reconciliation.Calendar Period End Date

  • Margin

    Default Value: 0

  • Maturity Date

    Calculation: ADD_MONTHS(EPF Reconciliation.Calendar Period End Date,Original Term to Maturity)

  • Net Margin Code

    Default Value: 0

  • Next Payment Date

    Calculation: ADD_MONTHS(EPF Reconciliation.Calendar Period End Date,Original Term to Maturity)

  • Next Repricing Date

    Calculation: ADD_MONTHS(EPF Reconciliation.Calendar Period End Date,Original Term to Maturity)

  • Original Term to Maturity

    Default Value: 1

  • Original Term Multiplier

    Default Value: M

  • Origination Date

    Source: Issue Date column

  • Original Gross Book Balance

    Source: Current Gross Par Balance column

  • Original Gross Par Balance

    Source: Current Gross Par Balance column

  • Original Rate

    Source: Current Net Rate column

  • Payment Frequency

    Default Value: 1

  • Payment Frequency Multiplier

    Default Value: M

  • Remaining Number of Payments

    Default Value: 1

  • Repricing Frequency

    Default Value: 0

  • Repricing Frequency Multiplier

    Default Value: M

Related Topics

GL Instrument Reports

STD – DATA GL Instrument Upload

Use the STD – DATA GL Instrument Upload report to identify GL balances that do not exist in any of the FEM account tables. The report pulls in balances by natural account and company cost center organization, and assigns default attributes to these balances so that you can load the balances as instrument records into account tables.

The STD – DATA GL Instrument Upload report contains two worksheets.

• STD – DATA GL Instrument Upload worksheet: Displays the results of this report.

  Tip: You can export this worksheet into either the Excel or ASCII format, and load the data into account tables with SQL Loader.

• An internal worksheet which creates a subquery that contains a list of the natural accounts to include in the report. This list includes any natural account ID that exists in the FEM_BALANCES table but not in any of the instrument tables. See: Relationship between EPF Data Entities and its presentation in the Architecture and Using a Worksheet.

Tip: To support processing of the Transfer Pricing system, you can use this report to generate account table records for balances that otherwise only exist in the FEM_BALANCES table. Run this report after loading data for a new calendar period into the account and FEM_BALANCES tables, but before running the transfer pricing process.

Business Area Folders

The STD – DATA GL Instrument Upload report is based on these folders.

STD – DATA GL Instrument Upload worksheet:

• EPF Balances: This simple ledger balance folder is based on the FEM_BALANCES table.

• EPF Calendar Periods: This simple dimension folder is based on the FEM_CAL_PERIODS_VL view.

• EPF Company Cost Center Organizations: This simple dimension folder is based on the FEM_CCTR_ORGS_VL view.

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

Joins

The STD – DATA GL Instrument Upload report uses these joins to retrieve data.

STD – DATA GL Instrument Upload worksheet:

• EPF Currencies.Currency code -> EPF Balances.Currency

• EPF Datasets.Dataset code -> EPF Balances.Dataset

• EPF Ledgers.Ledger Id -> EPF Balances.Ledger

• EPF Company Cost Center Organizations.Company Cost Center Org Id -> EPF Balances.Company Cost Center Organization

• EPF Line Items.Line Item Id -> EPF Balances.Line Item

• EPF Calendar Periods.Cal Period Id -> EPF Balances.Calendar Period

• EPF Natural Accounts.Natural Account Id -> EPF Balances.Natural Account

Conditions

These conditions apply to the STD – DATA GL Instrument Upload report.

STD – DATA GL Instrument Upload worksheet:

• EPF Balances.Financial Element IN (100)

• Natural Account NOT IN (Natural Accounts in All Accounts Table)

• Ledger and Company Cost Center Organization Dimension Value Set Mapping

• Ledger and Line Item Dimension Value Set Mapping

• Ledger and Natural Account Dimension Value Set Mapping

Page Items

The STD – DATA GL Instrument Upload report is based on these parameters.

STD – DATA GL Instrument Upload worksheet:

• Calendar Period

• Dataset

• Ledger

Rows

The STD – DATA GL Instrument Upload report has no rows because it has the table layout.

Report Headings and Calculations (Columns)

The STD – DATA GL Instrument Upload report has these columns. If applicable to the column, also provided below is the:

• Calculation: The formula used to calculate the column value.

• Source: The direct source of the column value, for example another column in the worksheet, meaning that the value for one column is equal to another column's value.

• Default: The actual value that defaults into the column.

Note: You can also view or even change the calculation, source, or default when you access calculation details in the Discoverer UI for specific fields.

STD – DATA GL Instrument Upload worksheet:

• Company Cost Center Organization

• Currency

• Natural Account

• Line Item

• Current Gross Book Balance

  Source: EPF Balances.Period to Date Balance Entered

• Current Gross Par Balance

  Source: EPF Balances.Period to Date Balance Entered

• Accrual Basis Code

  Default Value: ACT/ACT

• Adjustable Type Code

  Default Value: 0

• Amortization Term

  Default Value: 1

• Amortization Term Multiplier

  Default Value: M

• Amortization Type Code

  Default Value: 700

• Compound Basis Code

  Default Value: 160

• Current Net Rate

  Default Value: 8

• Current Payment

  Default Value: 0

• Interest Rate Code

  Default Value: 0

• Interest Type

  Default Value: 1

• Issue Date

  Source: EPF Balances.Calendar Period End Date

• Last Payment Date

  Source: EPF Balances.Calendar Period End Date

• Last Repricing Date

  Source: EPF Balances.Calendar Period End Date

• Margin

  Default Value: 0

• Maturity Date

  Calculation: ADD_MONTHS(EPF Balances.Calendar Period End Date,Original Term to Maturity)

• Net Margin Code

  Default Value: 0

• Next Payment Date

  Calculation: ADD_MONTHS(EPF Balances.Calendar Period End Date,Original Term to Maturity)

• Next Repricing Date

  Calculation: ADD_MONTHS(EPF Balances.Calendar Period End Date,Original Term to Maturity)

• Original Term to Maturity

  Default Value: 1

• Original Term Multiplier

  Default Value: M

• Origination Date

  Source: Issue Date column

• Original Book Balance

  Source: Current Gross Par Balance column

• Original Par Balance

  Source: Current Gross Par Balance column

• Original Rate

  Source: Current Net Rate column

• Payment Frequency

  Default Value: 1

• Payment Frequency Multiplier

  Default Value: M

• Remaining Number of Payments

  Default Value: 1

• Repricing Frequency

  Default Value: 0

• Repricing Frequency Multiplier

  Default Value: M

Related Topics

GL Instrument Reports

Stratification Reports

Use the stratification reports to analyze and validate the quality of your account level data after it is loaded. You can identify trends and outliers in the displayed key data attributes from the FEM account tables. For example, you can check for negative or zero values where you do not expect them, such as in payment frequency.

Tip: You can resubmit these reports after processing the cash flow edits or performing other data correction activities, to confirm the results of those activities and verify that the data is clean and ready for processing.

There are nine stratification reports, and they all have similar worksheets and report elements. Each report focuses on specific account data, for example mortgages, savings, or term deposits. When you run the report, you specify through parameters the ranges or tiers that the report results are based on. For example, you determine date ranges by which values in the Origination Date worksheet are grouped.

This table lists the stratification reports and their Discoverer file names. Each report has multiple worksheets with similar titles. For all reports, the worksheet-specific terms in the Discoverer worksheet title are:

1. Book Balance

2. Current Net Rate

3. Origination Date

4. Maturity Date

5. Payment Frequency

6. Amort Type / Adjust Type / Repricing

7. Line Item

8. Natural Account

Important: Not every report has all eight worksheets. The exceptions are noted in the Discoverer Worksheet Title column of this table.

Data Management: Stratification Reports

Report Name	Discoverer Workbook Title	Discoverer Worksheet Title
STD – DATA Stratification MORTGAGES	STD – DATA Stratification MORTGAGES	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Mortgages
STD – DATA Stratification COMMERCIAL LOANS	STD – DATA Stratification COMMERCIAL LOANS	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Commercial Loans
STD – DATA Stratification CONSUMER LOANS	STD – DATA Stratification CONSUMER LOANS	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Consumer Loans
STD – DATA Stratification INVESTMENTS	STD – DATA Stratification INVESTMENTS	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Investments
STD – DATA Stratification CREDIT CARDS	STD – DATA Stratification CREDIT CARDS	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Credit Cards Note: This report does not have these worksheets: Payment Frequency Amort Type / Adjust Type / Repricing
STD – DATA Stratification CHECKING	STD – DATA Stratification CHECKING	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Checking Note: This report does not have these worksheets: Payment Frequency Amort Type / Adjust Type / Repricing
STD – DATA Stratification SAVINGS	STD – DATA Stratification SAVINGS	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Savings Note: This report does not have the Payment Frequency worksheet.
STD – DATA Stratification TERM DEPOSITS	STD – DATA Stratification TERM DEPOSITS	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Term Deposits
STD – DATA Stratification WHOLESALE FUNDING	STD – DATA Stratification WHOLESALE FUNDING	Stratification Report User Specified Tiers: <Worksheet-Specific Term> Wholesale Funding

Related Topics

Stratification Reports Common Concepts

Overview of Data Management Reports

Stratification Reports Common Concepts

The following concepts are common to all the stratification reports. For any applicable worksheet, the report-specific terms are as shown in this table. For example, all worksheets are based in part on the EPF <Report-Specific Term> business area folder. For the STD – DATA Stratification MORTGAGES report, that would be the EPF Mortages folder.

Report Name	Report-Specific Term
STD – DATA Stratification MORTGAGES	Mortgages
STD – DATA Stratification COMMERCIAL LOANS	Commercial Loans
STD – DATA Stratification CONSUMER LOANS	Consumer Loans
STD – DATA Stratification INVESTMENTS	Investments
STD – DATA Stratification CREDIT CARDS	Credit Cards
STD – DATA Stratification CHECKING	Checking Accounts
STD – DATA Stratification SAVINGS	Savings
STD – DATA Stratification TERM DEPOSITS	Term Deposits
STD – DATA Stratification WHOLESALE FUNDING	Wholesale Funding

Note: Not all reports have every described worksheet. See: Stratification Reports.

Business Area Folders

The stratification reports are based on these folders.

• All worksheets:

  • 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 <Report-Specific Term>: This simple account folder is based on the FEM_<Report-Specific Term> table. For example, the EPF Mortgages folder is based on the FEM_MORTGAGES table.

• Book Balance, Current Net Rate, Origination Date, Maturity Date, and Payment Frequency worksheets:

  EPF Line Items: This simple dimension folder is based on the FEM_LN_ITEMS_VL view.

• Amort Type / Adjust Type / Repricing and Line Item worksheets:

  Line Items Dimension Hierarchy: This simple hierarchy folder is based on the FEM_DIS_LN_ITEMS_HIER_VL view.

• Natural Account worksheet:

  Natural Accounts Dimension Hierarchy: This simple hierarchy folder is based on the FEM_DIS_NAT_ACCTS_HIER_VL view.

Joins

The stratification reports use these joins to retrieve data:

• All worksheets:

  • EPF Currencies.Currency code -> EPF <Report-Specific Term>.Currency

  • EPF Datasets.Dataset code -> EPF <Report-Specific Term>.Dataset

  • EPF Ledgers.Ledger Id -> EPF <Report-Specific Term>.Ledger

• Book Balance, Current Net Rate, Origination Date, Maturity Date, and Payment Frequency worksheets:

  EPF Line Items.Line Item Id -> EPF <Report-Specific Term>.Line Item

• Amort Type / Adjust Type / Repricing and Line Item worksheets:

  Line Items Dimension Hierarchy.Level20 Id -> EPF <Report-Specific Term>.Line Item

• Natural Account worksheet:

  Natural Accounts Dimension Hierarchy.Level20 Id -> EPF <Report-Specific Term>.Natural Account

Conditions

These conditions apply to the stratification reports.

• Book Balance, Current Net Rate, Origination Date, Maturity Date, and Payment Frequency worksheets:

  Line Item Dimension

• Amort Type / Adjust Type / Repricing and Line Item worksheets:

  Line Item Dimension Hierarchy

• Natural Account worksheet:

  Natural Account Dimension Hierarchy

Page Items

The stratification reports are based on these parameters.

• All worksheets:

  • Calendar Period End Date

  • Ledger Name

  • Dataset Name

  • Currency Name

• Book Balance, Current Net Rate, Origination Date, Maturity Date, and Payment Frequency worksheets:

  Line Item Name

• Amort Type / Adjust Type / Repricing, Line Item, and Natural Account worksheets:

  • Object Name

  • Object Definition Name

Rows

The stratification reports have these rows. If applicable, the calculation for the row is provided below.

• Book Balance worksheet:

  Book Balance

  Calculation:

  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T1L),:S1_T1H),EPF <Report-Specific Term>.Current Gross Book Balance,'01.  '||:S1_T1L||' - '||:S1_T1H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T2L),:S1_T2H),EPF <Report-Specific Term>.Current Gross Book Balance,'02.  '||:S1_T2L||' - '||:S1_T2H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T3L),:S1_T3H),EPF <Report-Specific Term>.Current Gross Book Balance,'03.  '||:S1_T3L||' - '||:S1_T3H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T4L),:S1_T4H),EPF <Report-Specific Term>.Current Gross Book Balance,'04.  '||:S1_T4L||' - '||:S1_T4H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T5L),:S1_T5H),EPF <Report-Specific Term>.Current Gross Book Balance,'05.  '||:S1_T5L||' - '||:S1_T5H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T6L),:S1_T6H),EPF <Report-Specific Term>.Current Gross Book Balance,'06.  '||:S1_T6L||' - '||:S1_T6H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T7L),:S1_T7H),EPF <Report-Specific Term>.Current Gross Book Balance,'07.  '||:S1_T7L||' - '||:S1_T7H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T8L),:S1_T8H),EPF <Report-Specific Term>.Current Gross Book Balance,'08.  '||:S1_T8L||' - '||:S1_T8H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T9L),:S1_T9H),EPF <Report-Specific Term>.Current Gross Book Balance,'09.  '||:S1_T9L||' - '||:S1_T9H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Current Gross Book Balance,:S1_T10L),:S1_T10H),EPF <Report-Specific Term>.Current Gross Book Balance,'10.  '||:S1_T10L||' - '||:S1_T10H,'Other'))))))))))

• Current Net Rate worksheet:

  Cur Net Rate

  Calculation:

       DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T1L),:S2_T1H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'01.  '||:S2_T1L||' - '||:S2_T1H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T2L),:S2_T2H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'02.  '||:S2_T2L||' - '||:S2_T2H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T3L),:S2_T3H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'03.  '||:S2_T3L||' - '||:S2_T3H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T4L),:S2_T4H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'04.  '||:S2_T4L||' - '||:S2_T4H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T5L),:S2_T5H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'05.  '||:S2_T5L||' - '||:S2_T5H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T6L),:S2_T6H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'06.  '||:S2_T6L||' - '||:S2_T6H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T7L),:S2_T7H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'07.  '||:S2_T7L||' - '||:S2_T7H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T8L),:S2_T8H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'08.  '||:S2_T8L||' - '||:S2_T8H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T9L),:S2_T9H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'09.  '||:S2_T9L||' - '||:S2_T9H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>."Non-weighted Current Net Rate",:S2_T10L),:S2_T10H),EPF <Report-Specific Term>."Non-weighted Current Net Rate",'10.  '||:S2_T10L||' - '||:S2_T10H,'Other'))))))))))

• Origination Date worksheet:

  Origination Date

  Calculation:

  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T1L),:S3_T1H),EPF <Report-Specific Term>.Origination Date,'01.  '||:S3_T1L||' - '||:S3_T1H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T2L),:S3_T2H),EPF <Report-Specific Term>.Origination Date,'02.  '||:S3_T2L||' - '||:S3_T2H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T3L),:S3_T3H),EPF <Report-Specific Term>.Origination Date,'03.  '||:S3_T3L||' - '||:S3_T3H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T4L),:S3_T4H),EPF <Report-Specific Term>.Origination Date,'04.  '||:S3_T4L||' - '||:S3_T4H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T5L),:S3_T5H),EPF <Report-Specific Term>.Origination Date,'05.  '||:S3_T5L||' - '||:S3_T5H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T6L),:S3_T6H),EPF <Report-Specific Term>.Origination Date,'06.  '||:S3_T6L||' - '||:S3_T6H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T7L),:S3_T7H),EPF <Report-Specific Term>.Origination Date,'07.  '||:S3_T7L||' - '||:S3_T7H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T8L),:S3_T8H),EPF <Report-Specific Term>.Origination Date,'08.  '||:S3_T8L||' - '||:S3_T8H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T9L),:S3_T9H),EPF <Report-Specific Term>.Origination Date,'09.  '||:S3_T9L||' - '||:S3_T9H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Origination Date,:S3_T10L),:S3_T10H),EPF <Report-Specific Term>.Origination Date,'10.  '||:S3_T10L||' - '||:S3_T10H,'Other'))))))))))

• Maturity Date worksheet:

  Maturity Date

  Calculation:

   DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T1L),:S4_T1H),EPF <Report-Specific Term>.Maturity Date,'01.  '||:S4_T1L||' - '||:S4_T1H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T2L),:S4_T2H),EPF <Report-Specific Term>.Maturity Date,'02.  '||:S4_T2L||' - '||:S4_T2H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T3L),:S4_T3H),EPF <Report-Specific Term>.Maturity Date,'03.  '||:S4_T3L||' - '||:S4_T3H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T4L),:S4_T4H),EPF <Report-Specific Term>.Maturity Date,'04.  '||:S4_T4L||' - '||:S4_T4H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T5L),:S4_T5H),EPF <Report-Specific Term>.Maturity Date,'05.  '||:S4_T5L||' - '||:S4_T5H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T6L),:S4_T6H),EPF <Report-Specific Term>.Maturity Date,'06.  '||:S4_T6L||' - '||:S4_T6H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T7L),:S4_T7H),EPF <Report-Specific Term>.Maturity Date,'07.  '||:S4_T7L||' - '||:S4_T7H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T8L),:S4_T8H),EPF <Report-Specific Term>.Maturity Date,'08.  '||:S4_T8L||' - '||:S4_T8H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T9L),:S4_T9H),EPF <Report-Specific Term>.Maturity Date,'09.  '||:S4_T9L||' - '||:S4_T9H,
  DECODE(LEAST(GREATEST(EPF <Report-Specific Term>.Maturity Date,:S4_T10L),:S4_T10H),EPF <Report-Specific Term>.Maturity Date,'10.  '||:S4_T10L||' - '||:S4_T10H,'Other'))))))))))

• Payment Frequency worksheet:

  • Payment Mult

  • Payment Term

  • Remaining Payments

• Amort Type / Adjust Type / Repricing worksheet:

  • Line Item (Level20)

  • Amrt Type Code

  • Adj Type Code

  • Reprice Mult

  • Reprice Freq

• Line Item worksheet:

  Line Item Hierarchy (Via join, default display to level 20)

• Natural Account worksheet:

  Natural Account Hierarchy (Via join, default display to level 20)

Report Headings and Calculations (Columns)

These headings and calculations are common to the stratification reports. If applicable, the source for the column is provided below.

• All worksheets:

  • Current Book Balance

    Source: Current Gross Book Balance SUM (a seeded calculated item in the EPF core business area)

  • Record Count

• Book Balance worksheet:

  Current Par Balance

  Source: Current Gross Par Balance SUM (a seeded calculated item in the EPF core business area)

• Current Net Rate worksheet:

  Current Net Rate

Totals

All worksheets in the stratification reports have a total for every column.

Related Topics

Stratification Reports