This chapter describes the reports you use to analyze the quality of your data.
This chapter covers the following topics:
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
This table lists the GL instrument reports and their Discoverer file names.
Report Name | Discoverer Workbook Title | Discoverer Worksheet Title |
---|---|---|
STD – DATA Instrument to GL Reconciliation | STD - DATA Instrument to GL Reconciliation |
|
STD – GL Instrument Upload | GL Instrument Upload Report |
|
Related Topics
Overview of Data Management Reports
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.
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.
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
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
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
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.
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
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.
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.
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
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
The STD – DATA GL Instrument Upload report is based on these parameters.
STD – DATA GL Instrument Upload worksheet:
Calendar Period
Dataset
Ledger
The STD – DATA GL Instrument Upload report has no rows because it has the table layout.
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
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:
Book Balance
Current Net Rate
Origination Date
Maturity Date
Payment Frequency
Amort Type / Adjust Type / Repricing
Line Item
Natural Account
Important: Not every report has all eight worksheets. The exceptions are noted in the Discoverer Worksheet Title column of this table.
Related Topics
Stratification Reports Common Concepts
Overview of Data Management Reports
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.
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.
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
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
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
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)
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
All worksheets in the stratification reports have a total for every column.
Related Topics