This chapter describes the core business areas of the Oracle Financial Services reporting architecture.
This chapter covers the following topics:
The core EPF business area is called EPF – Core Business Area and can be accessed by the system administrator with the help of the Discoverer Administration tool. The business area consists of:
Simple folders representing metadata information, data tables, dimension members.
Custom folders representing views that link multiple data tables.
Calculated items and registered PL/SQL functions.
Details of the EPF business area folders are documented in the following tables.
The following table describes the Ledger Balances folders.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FEM_BALANCES | Simple Folder | EPF Balances |
The following table describes the Account folders.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FEM_CHECKING | Simple Folder | EPF Checking Accounts |
| FEM_CONSUMER_LOANS | Simple Folder | EPF Consumer Loans |
| FEM_CREDIT_LINES | Simple Folder | EPF Credit Lines |
| FEM_COMMERCIAL_LOANS | Simple Folder | EPF Commercial Loans |
| FEM_INVESTMENTS | Simple Folder | EPF Investments |
| FEM_LEASES | Simple Folder | EPF Leases |
| FEM_MORTGAGES | Simple Folder | EPF Mortgages |
| FEM_MORTGAGE_BACK_SEC | Simple Folder | EPF Mortgage-Backed Securities |
| FEM_RETIREMENT_ACCOUNTS | Simple Folder | EPF Retirement Accounts |
| FEM_SAVINGS | Simple Folder | EPF Savings |
| FEM_TERM_DEPOSITS | Simple Folder | EPF Term Deposits |
| FEM_WHOLESALE_FUNDING | Simple Folder | EPF Wholesale Funding |
| FEM_CREDIT_CARDS | Simple Folder | EPF Credit Cards |
| FEM_OFF_BALANCE_SHEET | Simple Folder | EPF Off Balance Sheet |
The following table describes the Other Account folders.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FEM_MERCHANT_CARDS | Simple Folder | EPF Merchant Cards |
| FEM_OTHER_SERVICES | Simple Folder | EPF Other Services |
| FEM_TRUSTS | Simple Folder | EPF Trusts |
The following table describes the Account Folder Union's folders.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FEM_ALL_ACCOUNT_TABLES | Custom Folder | EPF All Account Tables |
| FEM_RECONCILIATION | Custom Folder | EPF Reconciliation |
The following table describes the Customer Profitability folders.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FEM_CUSTOMER_PROFIT | Simple Folder | EPF Customer Profit |
| FEM_CUSTOMER_DETAIL | Simple Folder | EPF Customer Detail |
| FEM_SECONDARY_OWNERS | Simple Folder | EPF Secondary Owners |
| FEM_REGION_INFO | Simple Folder | EPF Region Information |
The following table describes the Dimension folders.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FEM_DIMENSIONS_VL | Simple Folder | EPF Dimensions |
| FEM_HIERARCHIES | Simple Folder | EPF Hierarchies |
| FEM_OBJECT_CATALOG_VL | Simple Folder | EPF Object Catalog |
| FEM_OBJECT_DEFINITION_VL | Simple Folder | EPF Object Definition |
| FEM_CCTR_ORGS_VL | Simple Folder | EPF Company Cost Center Organizations |
| FEM_PRODUCTS_VL | Simple Folder | EPF Products |
| FEM_LN_ITEMS_VL | Simple Folder | EPF Line Items |
| FEM_NAT_ACCTS_VL | Simple Folder | EPF Natural Accounts |
| FEM_CAL_PERIODS_VL | Simple Folder | EPF Calendar Periods |
| FEM_CHANNELS_VL | Simple Folder | EPF Channels |
| FEM_CUSTOMERS_VL | Simple Folder | EPF Customers |
| FEM_ENTITIES_VL | Simple Folder | EPF Entities |
| FEM_LEDGERS_VL | Simple Folder | EPF Ledgers |
| FEM_TASKS_VL | Simple Folder | EPF Tasks |
| FEM_PROJECTS_VL | Simple Folder | EPF Projects |
| FEM_GEOGRAPHY_VL | Simple Folder | EPF Geography |
| FEM_MKT_SEGMNTS_VL | Simple Folder | EPF Market Segments |
| FEM_USER_DIM1_VL | Simple Folder | EPF User Dimension01 |
| FEM_USER_DIM2_VL | Simple Folder | EPF User Dimension02 |
| FEM_USER_DIM3_VL | Simple Folder | EPF User Dimension03 |
| FEM_USER_DIM4_VL | Simple Folder | EPF User Dimension04 |
| FEM_USER_DIM5_VL | Simple Folder | EPF User Dimension05 |
| FEM_USER_DIM6_VL | Simple Folder | EPF User Dimension06 |
| FEM_USER_DIM7_VL | Simple Folder | EPF User Dimension07 |
| FEM_USER_DIM8_VL | Simple Folder | EPF User Dimension08 |
| FEM_USER_DIM9_VL | Simple Folder | EPF User Dimension09 |
| FEM_USER_DIM10_VL | Simple Folder | EPF User Dimension10 |
| FEM_ACCRUAL_BASIS_VL | Simple Folder | EPF Accrual Basis |
| FEM_BRANCHES_VL | Simple Folder | EPF Branches |
| FEM_CURRENCIES_VL | Simple Folder | EPF Currencies |
| FEM_DATASETS_VL | Simple Folder | EPF Datasets |
| FEM_OVRDRFT_PRT_VL | Simple Folder | EPF Overdraft Protections |
| FEM_OUTINFO_SRC_VL | Simple Folder | EPF Outside Information Sources |
| FEM_PRODUCT_TYPES_VL | Simple Folder | EPF Product Types |
| FEM_RSN_CLOSED_VL | Simple Folder | EPF Reason Closed |
| FEM_SERV_SRC_VL | Simple Folder | EPF Service Sources |
| FEM_SIC_VL | Simple Folder | EPF Standard Industrial Classification |
| FEM_SOURCE_SYSTEMS_VL | Simple Folder | EPF Source Systems |
| FEM_ACCDH_INSCO_VL | Simple Folder | EPF Accident Health Insurance Company |
| FEM_APAYINST_TYPES_VL | Simple Folder | EPF Autopay Instrument Types |
| FEM_COLLATERAL_VL | Simple Folder | EPF Collateral |
| FEM_CREDIT_STATUS_VL | Simple Folder | EPF Credit Status |
| FEM_CRDLF_INSCO_VL | Simple Folder | EPF Credit Life Insurance Company |
| FEM_DIR_INDIRECT_VL | Simple Folder | EPF Direct Indirect |
| FEM_EXIST_BORWR_VL | Simple Folder | EPF Existing Borrowers |
| FEM_PLEDG_STAT_VL | Simple Folder | EPF Pledged Status |
| FEM_PAYMNT_TYPES_VL | Simple Folder | EPF Payment Types |
| FEM_PURPOSES_VL | Simple Folder | EPF Purposes |
| FEM_SCHOOLS_VL | Simple Folder | EPF Schools |
| FEM_STUDSCHL_YR_VL | Simple Folder | EPF Student Year In School |
| FEM_ANNUAL_FEES_VL | Simple Folder | EPF Annual Fees |
| FEM_APP_ANALYSTS_VL | Simple Folder | EPF Application Analysts |
| FEM_APPOVR_RSN_VL | Simple Folder | EPF Application Override Reasons |
| FEM_CHGOFF_RSN_VL | Simple Folder | EPF Charge off Reasons |
| FEM_PAYMNT_HIST_VL | Simple Folder | EPF Payment History |
| FEM_PAY_AHEAD_VL | Simple Folder | EPF Pay Ahead |
| FEM_FIN_ELEMS_VL | Simple Folder | EPF Financial Elements |
| FEM_COMMIT_TYPES_VL | Simple Folder | EPF Commitment Types |
| FEM_CREDIT_RATINGS_VL | Simple Folder | EPF Credit Ratings |
| FEM_FIDAGRMNTS_VL | Simple Folder | EPF Fiduciary Agreements |
| FEM_HELD_SALE_VL | Simple Folder | EPF Held for Sale |
| FEM_INTDIV_OPTN_VL | Simple Folder | EPF Interest Dividends Option |
| FEM_INVEST_TYPES_VL | Simple Folder | EPF Investor Types |
| FEM_JOINT_AGRMNTS_VL | Simple Folder | EPF Joint Agreements |
| FEM_MRGN_AGRMNTS_VL | Simple Folder | EPF Margin Agreements |
| FEM_LIEN_POS_VL | Simple Folder | EPF Lien Position |
| FEM_OCCUPANCIES_VL | Simple Folder | EPF Occupancies |
| FEM_AGENCIES_VL | Simple Folder | EPF Agencies |
| FEM_PROPTYPES_VL | Simple Folder | EPF Property Types |
| FEM_CMO_TRANCHE_VL | Simple Folder | EPF Collateralized Mortgage Obligation Tranche |
| FEM_DISBACCT_TYPES_VL | Simple Folder | EPF Disbursement Account Types |
| FEM_DISBMTHDS_VL | Simple Folder | EPF Disbursement Methods |
| FEM_FUNDING_TYPES_VL | Simple Folder | EPF Funding Types |
| FEM_FUNDSTATUS_VL | Simple Folder | EPF Funding Status |
| FEM_INTPAYMTHDS_VL | Simple Folder | EPF Interest Payment Methods |
| FEM_PLANS_VL | Simple Folder | EPF Plans |
| FEM_DRDPACCT_TYPES_VL | Simple Folder | EPF Direct Deposit Account Types |
| FEM_ROLLOVER_FAC_VL | Simple Folder | EPF Rollover Facilities |
| FEM_PUT_CALLS_VL | Simple Folder | EPF Put Calls |
| FEM_AGENCY_BNKS_VL | Simple Folder | EPF Agent Banks |
| FEM_SERV_AGENTS_VL | Simple Folder | EPF Servicing Agents |
| FEM_SOLICIT_SRC_VL | Simple Folder | EPF Solicitation Source |
| FEM_CORPAGRMNTS_VL | Simple Folder | EPF Corporate Agreements |
| FEM_DATA_AGGS_VL | Simple Folder | EPF Data Aggregation Types |
| FEM_AGE_SEG_CD_VL | Simple Folder | EPF Age Segment Codes |
| FEM_BUS_TYPE_CD_VL | Simple Folder | EPF Business Type Codes |
| FEM_CARRIER_RTE_VL | Simple Folder | EPF Carrier Route |
| FEM_CENSUS_BLK_VL | Simple Folder | EPF Census Block |
| FEM_CENSUS_CNTY_VL | Simple Folder | EPF Census County |
| FEM_CENSUS_STT_VL | Simple Folder | EPF Census State |
| FEM_CENSUS_TRC_VL | Simple Folder | EPF Census Tract |
| FEM_CRED_BUR_CD_VL | Simple Folder | EPF Credit Bureau Codes |
| FEM_CRED_CRD_CD_VL | Simple Folder | EPF Credit Card Codes |
| FEM_EDUC_LEVELS_VL | Simple Folder | EPF Education Levels |
| FEM_INC_CLAS_CD_VL | Simple Folder | EPF Income Class Codes |
| FEM_INC_SRC_CD_VL | Simple Folder | EPF Income Source Codes |
| FEM_LOB_CD_VL | Simple Folder | EPF Line of Business Codes |
| FEM_MARITAL_STS_VL | Simple Folder | EPF Marital Status |
| FEM_OCCUPATIONS_VL | Simple Folder | EPF Occupations |
| FEM_RES_TYPE_CD_VL | Simple Folder | EPF Residence Type Codes |
| FEM_TELEMRKT_CD_VL | Simple Folder | EPF Telemarket Codes |
| FEM_VEH_TYPE_CD_VL | Simple Folder | EPF Vehicle Type Codes |
| FEM_ACTIVITIES | Simple Folder | EPF Activities |
| FEM_COST_OBJECTS | Simple Folder | EPF Cost Objects |
| FEM_CURRTYPES_VL | Simple Folder | EPF Currency Types |
| FEM_ISSUERS_VL | Simple Folder | EPF Issuers |
| FEM_LIQ_CLASSES_VL | Simple Folder | EPF Liquidity Classes |
| FEM_ADVICE_TYPES_VL | Simple Folder | EPF Advice Types |
| FEM_AUTHDEV_TYPES_VL | Simple Folder | EPF Authorization Device Types |
| FEM_BILLMETHDS_VL | Simple Folder | EPF Billing Methods |
| FEM_DEPOSIT_TYPES_VL | Simple Folder | EPF Deposit Types |
| FEM_MERCH_CHAINS_VL | Simple Folder | EPF Merchant Chains |
| FEM_MERCH_CLASSES_VL | Simple Folder | EPF Merchant Classes |
| FEM_OWNRSHP_TYPES_VL | Simple Folder | EPF Ownership Types |
| FEM_SACCTSRV_TYPES_VL | Simple Folder | EPF Settlement Account Service Types |
| FEM_EXCEPTIONS_VL | Simple Folder | EPF Exceptions |
| FEM_PRNT_SERVCES_VL | Simple Folder | EPF Parent Services |
| FEM_SERV_OPTN_VL | Simple Folder | EPF Service Options |
| FEM_RELATION_TYPES_VL | Simple Folder | EPF Relationship Types |
| FEM_BUS_REL_VL | Simple Folder | EPF Business Relationships |
| FEM_ACCT_OWNSHP_VL | Simple Folder | EPF Account Ownership |
| FEM_GOV_ID_NUMS_VL | Simple Folder | EPF Government Id Numbers |
The following table describes the Hierarchy folders.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FEM_DIS_LDGR_DIM_VS_MAP | Simple Folder | EPF Ledger and Dimension Value Set Mapping |
| FEM_DIS_CCTR_ORGS_HIER_VL | Simple Folder | Company Cost Center Organizations Dimension Hierarchy |
| FEM_DIS_NAT_ACCTS_HIER_VL | Simple Folder | Natural Accounts Dimension Hierarchy |
| FEM_DIS_LN_ITEMS_HIER_VL | Simple Folder | Line Items Dimension Hierarchy |
| FEM_DIS_CAL_PER_HIER_VL | Simple Folder | Calendar Periods Dimension Hierarchy |
| FEM_DIS_CHANNELS_HIER_VL | Simple Folder | Channels Dimension Hierarchy |
| FEM_DIS_COMPANIES_HIER_VL | Simple Folder | Companies Dimension Hierarchy |
| FEM_DIS_CST_CNTRS_HIER_VL | Simple Folder | Cost Centers Dimension Hierarchy |
| FEM_DIS_CUSTOMERS_HIER_VL | Simple Folder | Customers Dimension Hierarchy |
| FEM_DIS_ENTITIES_HIER_VL | Simple Folder | Entities Dimension Hierarchy |
| FEM_DIS_GEOGRAPHY_HIER_VL | Simple Folder | Geography Dimension Hierarchy |
| FEM_DIS_PRD_TYPES_HIER_VL | Simple Folder | Product Types Dimension Hierarchy |
| FEM_DIS_PRODUCTS_HIER_VL | Simple Folder | Products Dimension Hierarchy |
| FEM_DIS_PROJECTS_HIER_VL | Simple Folder | Projects Dimension Hierarchy |
| FEM_DIS_SIC_HIER_VL | Simple Folder | Standard Industrial Classification Dimension Hierarchy |
| FEM_DIS_TASKS_HIER_VL | Simple Folder | Tasks Dimension Hierarchy |
| FEM_DIS_USR_DIM1_HIER_VL | Simple Folder | User Dimension01 Dimension Hierarchy |
| FEM_DIS_USR_DIM2_HIER_VL | Simple Folder | User Dimension02 Dimension Hierarchy |
| FEM_DIS_USR_DIM3_HIER_VL | Simple Folder | User Dimension03 Dimension Hierarchy |
| FEM_DIS_USR_DIM4_HIER_VL | Simple Folder | User Dimension04 Dimension Hierarchy |
| FEM_DIS_USR_DIM5_HIER_VL | Simple Folder | User Dimension05 Dimension Hierarchy |
| FEM_DIS_USR_DIM6_HIER_VL | Simple Folder | User Dimension06 Dimension Hierarchy |
| FEM_DIS_USR_DIM7_HIER_VL | Simple Folder | User Dimension07 Dimension Hierarchy |
| FEM_DIS_USR_DIM8_HIER_VL | Simple Folder | User Dimension08 Dimension Hierarchy |
| FEM_DIS_USR_DIM9_HIER_VL | Simple Folder | User Dimension09 Dimension Hierarchy |
| FEM_DIS_USR_DIM10_HIER_VL | Simple Folder | User Dimension10 Dimension Hierarchy |
Related Topics
Each data folder corresponds to an EPF fact table and contains items that represent columns in the table. For example, the following table displays the list of items in the EPF Balances folder.
| Name | Type | Identifier | Database Column |
|---|---|---|---|
| Dataset | Number | DATASET_CODE | DATASET_CODE |
| Calendar Period | Number | CAL_PERIOD_ID | CAL_PERIOD_ID |
| Creation Row Sequence | Number | CREATION_ROW_SEQUENCE | CREATION_ROW_SEQUENCE |
| Source System | Number | SOURCE_SYSTEM_CODE | SOURCE_SYSTEM_CODE |
| Ledger | Number | LEDGER_ID | LEDGER_ID |
| Company Cost Center Organization | Number | COMPANY_COST_CENTER_ORG_ID | COMPANY_COST_CENTER_ORG_ID |
| Currency | Varchar | CURRENCY_CODE | CURRENCY_CODE |
| Currency Type Code | Varchar | CURRENCY_TYPE_CODE | CURRENCY_TYPE_CODE |
| Activity | Number | ACTIVITY_ID | ACTIVITY_ID |
| Cost Object | Number | COST_OBJECT_ID | COST_OBJECT_ID |
| Financial Element | Number | FINANCIAL_ELEM_ID | FINANCIAL_ELEM_ID |
| Product | Number | PRODUCT_ID | PRODUCT_ID |
| Natural Account | Number | NATURAL_ACCOUNT_ID | NATURAL_ACCOUNT_ID |
| Channel | Number | CHANNEL_ID | CHANNEL_ID |
| Line Item | Number | LINE_ITEM_ID | LINE_ITEM_ID |
| Project | Number | PROJECT_ID | PROJECT_ID |
| Customer | Number | CUSTOMER_ID | CUSTOMER_ID |
| Entity | Number | ENTITY_ID | ENTITY_ID |
| Intercompany | Number | INTERCOMPANY_ID | INTERCOMPANY_ID |
| Task | Number | TASK_ID | TASK_ID |
| User Dimension01 | Number | USER_DIM1_ID | USER_DIM1_ID |
| User Dimension02 | Number | USER_DIM2_ID | USER_DIM2_ID |
| User Dimension03 | Number | USER_DIM3_ID | USER_DIM3_ID |
| User Dimension04 | Number | USER_DIM4_ID | USER_DIM4_ID |
| User Dimension05 | Number | USER_DIM5_ID | USER_DIM5_ID |
| User Dimension06 | Number | USER_DIM6_ID | USER_DIM6_ID |
| User Dimension07 | Number | USER_DIM7_ID | USER_DIM7_ID |
| User Dimension08 | Number | USER_DIM8_ID | USER_DIM8_ID |
| User Dimension09 | Number | USER_DIM9_ID | USER_DIM9_ID |
| User Dimension10 | Number | USER_DIM10_ID | USER_DIM10_ID |
| Created By Request | Number | CREATED_BY_REQUEST_ID | CREATED_BY_REQUEST_ID |
| Created By Object | Number | CREATED_BY_OBJECT_ID | CREATED_BY_OBJECT_ID |
| Last Updated By Request | Number | LAST_UPDATED_BY_REQUEST_ID | LAST_UPDATED_BY_REQUEST_ID |
| Last Updated By Object | Number | LAST_UPDATED_BY_OBJECT_ID | LAST_UPDATED_BY_OBJECT_ID |
| Period to Date Balance Entered | Number | XTD_BALANCE_E | XTD_BALANCE_E |
| Period to Date Balance Functional | Number | XTD_BALANCE_F | XTD_BALANCE_F |
| Year to Date Balance Entered | Number | YTD_BALANCE_E | YTD_BALANCE_E |
| Year to Date Balance Functional | Number | YTD_BALANCE_F | YTD_BALANCE_F |
| Quarter to Date Balance Entered | Number | QTD_BALANCE_E | QTD_BALANCE_E |
| Quarter to Date Balance Functional | Number | QTD_BALANCE_F | QTD_BALANCE_F |
| Period to Date Debit Balance Entered | Number | PTD_DEBIT_BALANCE_E | PTD_DEBIT_BALANCE_E |
| Period to Date Credit Balance Entered | Number | PTD_CREDIT_BALANCE_E | PTD_CREDIT_BALANCE_E |
| Year to Date Credit Balance Entered | Number | YTD_DEBIT_BALANCE_E | YTD_DEBIT_BALANCE_E |
| Year to Date Debit Balance Entered | Number | YTD_CREDIT_BALANCE_E | YTD_CREDIT_BALANCE_E |
You can use the Discoverer Administrator tool to examine the details of each data folder.
Each data folder contains items that represent the internal dimension member IDs. To create report on this data, joins are created between these items and the corresponding dimension member folders. The table below shows all the joins within the EPF Balances folder. Join relationships for other data folders are similar.
| Name | Master Folder | Description | Join Relationship |
|---|---|---|---|
| Calendar Periods Dimension Hierarchy -> EPF Balances | Calendar Periods Dimension Hierarchy | Join between Calendar Periods Dimension Hierarchy.Level20 id and EPF Balances.Calendar Period | One to Many (1:n) |
| Channels Dimension Hierarchy -> EPF Balances | Channels Dimension Hierarchy | Join between Channels Dimension Hierarchy.Level20 id and EPF Balances.Channel | One to Many (1:n) |
| Company Cost Center Organizations Dimension Hierarchy -> EPF Balances | Company Cost Center Organizations Dimension Hierarchy | Join between Company Cost Center Organizations Dimension Hierarchy.Level20 Id and EPF Balances.Company Cost Center Organization | One to Many (1:n) |
| Customers Dimension Hierarchy -> EPF Balances | Customers Dimension Hierarchy | Join between Customers Dimension Hierarchy.Level20 id and EPF Balances.Customer | One to Many (1:n) |
| EFP Channels -> EPF Balances | EFP Channels | Join between EPF Channels.Channel Id and EPF Balances.Channel | One to Many (1:n) |
| Entities Dimension Hierarchy -> EPF Balances | Entities Dimension Hierarchy | Join between Entities Dimension Hiearchy.Level20 id and EPF Balances.Entity | One to Many (1:n) |
| EPF Activities -> EPF Balances | EPF Activities | Join between EPF Activities.Activity Id and EPF Balances.Activity | One to Many (1:n) |
| EPF Calendar Periods -> EPF Balances | EPF Calendar Periods | Join between EPF Calendar Periods.Cal Period Id and EPF Balances.Calendar Period | One to Many (1:n) |
| EPF Company Cost Center Organizations -> EPF Balances | EPF Company Cost Center Organizations | Join between EPF Company Cost Center Organizations.Company Cost Center Org Id and EPF Balances.Company Cost Center Organization | One to Many (1:n) |
| EPF Company Cost Center Organizations -> EPF Balances (Intercompany) | EPF Company Cost Center Organizations | Join between EPF Company Cost Center Organizations.Company Cost Center Org Id and EPF Balances.Intercompany | One to Many (1:n) |
| EPF Cost Objects -> EPF Balances | EPF Cost Objects | Join between EPF Cost Objects.Cost object id and EPF Balances.Cost Object | One to Many (1:n) |
| EPF Currencies -> EPF Balances | EPF Currencies | Join between EPF Currencies.Currency code and EPF Balances.Currency | One to Many (1:n) |
| EPF Currency Types -> EPF Balances | EPF Currency Types | Join between EPF Currency Types.Currency type code and EPF Balances.Currency Type Code | One to Many (1:n) |
| EPF Customers -> EPF Balances | EPF Customers | Join between EPF Customers.Customer Id and EPF Balances.Customer | One to Many (1:n) |
| EPF Datasets -> EPF Balances | EPF Datasets | Join between EPF Datasets.Dataset code and EPF Balances.Dataset | One to Many (1:n) |
| EPF Entities -> EPF Balances | EPF Entities | Join between EPF Entities.Entity Id and EPF Balances.Entity | One to Many (1:n) |
| EPF Financial Elements -> EPF Balances | EPF Financial Elements | Join between EPF Financial Elements.Financial elem id and EPF Balances.Financial Element | One to Many (1:n) |
| EPF Ledgers -> EPF Balances | EPF Ledgers | Join between EPF Ledgers.Ledger Id and EPF Balances.Ledger | One to Many (1:n) |
| EPF Line Items -> EPF Balances | EPF Line Items | Join between EPF Line Items.Line Item Id and EPF Balances.Line Item | One to Many (1:n) |
| EPF Natural Accounts -> EPF Balances | EPF Natural Accounts | Join between EPF Natural Accounts.Natural Account Id and EPF Balances.Natural Account | One to Many (1:n) |
| EPF Products -> EPF Balances | EPF Products | Join between EPF Products.Product Id and EPF Balances.Product | One to Many (1:n) |
| EPF Projects -> EPF Balances | EPF Projects | Join between EPF Projects.Project Id and EPF Balances.Project | One to Many (1:n) |
| EPF Source Systems -> EPF Balances | EPF Source Systems | Join between EPF Source Systems.Source system code and EPF Balances.Source System | One to Many (1:n) |
| EPF Tasks -> EPF Balances | EPF Tasks | Join between EPF Tasks.Task Id and EPF Balances.Task | One to Many (1:n) |
| EPF User Dimension01 -> EPF Balances | EPF User Dimension01 | Join between EPF User Dimension01.User Dim1 Id and EPF Balances.User Dimension01 | One to Many (1:n) |
| EPF User Dimension02 -> EPF Balances | EPF User Dimension02 | Join between EPF User Dimension02 .User Dim2 Id and EPF Balances.User Dimension02 | One to Many (1:n) |
| EPF User Dimension03 -> EPF Balances | EPF User Dimension03 | Join between EPF User Dimension03.User Dim3 Id and EPF Balances.User Dimension03 | One to Many (1:n) |
| EPF User Dimension04 -> EPF Balances | EPF User Dimension04 | Join between EPF User Dimension04.User Dim4 Id and EPF Balances.User Dimension04 | One to Many (1:n) |
| EPF User Dimension05 -> EPF Balances | EPF User Dimension05 | Join between EPF User Dimension05.User Dim5 Id and EPF Balances.User Dimension05 | One to Many (1:n) |
| EPF User Dimension06 -> EPF Balances | EPF User Dimension06 | Join between EPF User Dimension06.User Dim6 Id and EPF Balances.User Dimension06 | One to Many (1:n) |
| EPF User Dimension07 -> EPF Balances | EPF User Dimension07 | Join between EPF User Dimension07.User Dim7 Id and EPF Balances.User Dimension07 | One to Many (1:n) |
| EPF User Dimension08 -> EPF Balances | EPF User Dimension08 | Join between EPF User Dimension08.User Dim8 Id and EPF Balances.User Dimension08 | One to Many (1:n) |
| EPF User Dimension09 -> EPF Balances | EPF User Dimension09 | Join between EPF User Dimension09.User Dim9 Id and EPF Balances.User Dimension09 | One to Many (1:n) |
| EPF User Dimension10 -> EPF Balances | EPF User Dimension10 | Join between EPF User Dimension10.User Dim10 Id and EPF Balances.User Dimension10 | One to Many (1:n) |
| Line Items Dimension Hierarchy -> EPF Balances | Line Items Dimension Hierarchy | Join between Line Items Dimension Hierarchy.Level20 id and EPF Balances.Line Item | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Balances | Natural Accounts Dimension Hierarchy | Join between Natural Accounts Dimension Hierarchy.Level20 id and EPF Balances.Natural Account | One to Many (1:n) |
| Products Dimension Hierarchy -> EPF Balances | Products Dimension Hierarchy | Join between Products Dimension Hierarchy.Level20 id and EPF Balances.Product | One to Many (1:n) |
| Projects Dimension Hierarchy -> EPF Balances | Projects Dimension Hierarchy | Join between Projects Dimension Hierarchy.Level20 id and EPF Balances.Project | One to Many (1:n) |
| Tasks Dimension Hierarchy -> EPF Balances | Tasks Dimension Hierarchy | Join between Tasks Dimension Hierarchy.Level20 id and EPF Balances.Task | One to Many (1:n) |
| User Dimension01 Dimension Hierarchy -> EPF Balances | User Dimension01 Dimension Hierarchy | Join between User Dimension01 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension01 | One to Many (1:n) |
| User Dimension02 Dimension Hierarchy -> EPF Balances | User Dimension02 Dimension Hierarchy | Join between User Dimension02 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension02 | One to Many (1:n) |
| User Dimension03 Dimension Hierarchy -> EPF Balances | User Dimension03 Dimension Hierarchy | Join between User Dimension03 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension03 | One to Many (1:n) |
| User Dimension04 Dimension Hierarchy -> EPF Balances | User Dimension04 Dimension Hierarchy | Join between User Dimension04 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension04 | One to Many (1:n) |
| User Dimension05 Dimension Hierarchy -> EPF Balances | User Dimension05 Dimension Hierarchy | Join between User Dimension05 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension05 | One to Many (1:n) |
| User Dimension06 Dimension Hierarchy -> EPF Balances | User Dimension06 Dimension Hierarchy | Join between User Dimension06 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension06 | One to Many (1:n) |
| User Dimension07 Dimension Hierarchy -> EPF Balances | User Dimension07 Dimension Hierarchy | Join between User Dimension07 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension07 | One to Many (1:n) |
| User Dimension08 Dimension Hierarchy -> EPF Balances | User Dimension08 Dimension Hierarchy | Join between User Dimension08 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension08 | One to Many (1:n) |
| User Dimension09 Dimension Hierarchy -> EPF Balances | User Dimension09 Dimension Hierarchy | Join between User Dimension09 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension09 | One to Many (1:n) |
| User Dimension10 Dimension Hierarchy -> EPF Balances | User Dimension10 Dimension Hierarchy | Join between User Dimension10 Dimension Hierarchy.Level20 id and EPF Balances.User Dimension10 | One to Many (1:n) |
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, member name and description are available. The table below shows all the items in the Natural Accounts Dimension Hierarchy folder. All other hierarchy folders have identical structure.
| Name | Type | Identifier | Database Column |
|---|---|---|---|
| Row id | Varchar | ROW_ID | ROW_ID |
| Object id | Number | OBJECT_ID | OBJECT_ID |
| Object definition id | Number | OBJECT_DEFINITION_ID | OBJECT_DEFINITION_ID |
| Value set id | Number | VALUE_SET_ID | VALUE_SET_ID |
| Natural Account ID (Level1) | Number | LEVEL1_ID | LEVEL1_ID |
| Natural Account ID (Level2) | Number | LEVEL2_ID | LEVEL2_ID |
| Natural Account ID (Level3) | Number | LEVEL3_ID | LEVEL3_ID |
| Natural Account ID (Level4) | Number | LEVEL4_ID | LEVEL4_ID |
| Natural Account ID (Level5) | Number | LEVEL5_ID | LEVEL5_ID |
| Natural Account ID (Level6) | Number | LEVEL6_ID | LEVEL6_ID |
| Natural Account ID (Level7) | Number | LEVEL7_ID | LEVEL7_ID |
| Natural Account ID (Level8) | Number | LEVEL8_ID | LEVEL8_ID |
| Natural Account ID (Level9) | Number | LEVEL9_ID | LEVEL9_ID |
| Natural Account ID (Level10) | Number | LEVEL10_ID | LEVEL10_ID |
| Natural Account ID (Level11) | Number | LEVEL11_ID | LEVEL11_ID |
| Natural Account ID (Level12) | Number | LEVEL12_ID | LEVEL12_ID |
| Natural Account ID (Level13) | Number | LEVEL13_ID | LEVEL13_ID |
| Natural Account ID (Level14) | Number | LEVEL14_ID | LEVEL14_ID |
| Natural Account ID (Level15) | Number | LEVEL15_ID | LEVEL15_ID |
| Natural Account ID (Level16) | Number | LEVEL16_ID | LEVEL16_ID |
| Natural Account ID (Level17) | Number | LEVEL17_ID | LEVEL17_ID |
| Natural Account ID (Level18) | Number | LEVEL18_ID | LEVEL18_ID |
| Natural Account ID (Level19) | Number | LEVEL19_ID | LEVEL19_ID |
| Natural Account ID (Level20) | Number | LEVEL20_ID | LEVEL20_ID |
| Natural Account Display Code (Level1) | Varchar | LEVEL1_DISPLAY_CODE | LEVEL1_DISPLAY_CODE |
| Natural Account Display Code (Level2) | Varchar | LEVEL2_DISPLAY_CODE | LEVEL2_DISPLAY_CODE |
| Natural Account Display Code (Level3) | Varchar | LEVEL3_DISPLAY_CODE | LEVEL3_DISPLAY_CODE |
| Natural Account Display Code (Level4) | Varchar | LEVEL4_DISPLAY_CODE | LEVEL4_DISPLAY_CODE |
| Natural Account Display Code (Level5) | Varchar | LEVEL5_DISPLAY_CODE | LEVEL5_DISPLAY_CODE |
| Natural Account Display Code (Level6) | Varchar | LEVEL6_DISPLAY_CODE | LEVEL6_DISPLAY_CODE |
| Natural Account Display Code (Level7) | Varchar | LEVEL7_DISPLAY_CODE | LEVEL7_DISPLAY_CODE |
| Natural Account Display Code (Level8) | Varchar | LEVEL8_DISPLAY_CODE | LEVEL8_DISPLAY_CODE |
| Natural Account Display Code (Level9) | Varchar | LEVEL9_DISPLAY_CODE | LEVEL9_DISPLAY_CODE |
| Natural Account Display Code (Level10) | Varchar | LEVEL10_DISPLAY_CODE | LEVEL10_DISPLAY_CODE |
| Natural Account Display Code (Level11) | Varchar | LEVEL11_DISPLAY_CODE | LEVEL11_DISPLAY_CODE |
| Natural Account Display Code (Level12) | Varchar | LEVEL12_DISPLAY_CODE | LEVEL12_DISPLAY_CODE |
| Natural Account Display Code (Level13) | Varchar | LEVEL13_DISPLAY_CODE | LEVEL13_DISPLAY_CODE |
| Natural Account Display Code (Level14) | Varchar | LEVEL14_DISPLAY_CODE | LEVEL14_DISPLAY_CODE |
| Natural Account Display Code (Level15) | Varchar | LEVEL15_DISPLAY_CODE | LEVEL15_DISPLAY_CODE |
| Natural Account Display Code (Level16) | Varchar | LEVEL16_DISPLAY_CODE | LEVEL16_DISPLAY_CODE |
| Natural Account Display Code (Level17) | Varchar | LEVEL17_DISPLAY_CODE | LEVEL17_DISPLAY_CODE |
| Natural Account Display Code (Level18) | Varchar | LEVEL18_DISPLAY_CODE | LEVEL18_DISPLAY_CODE |
| Natural Account Display Code (Level19) | Varchar | LEVEL19_DISPLAY_CODE | LEVEL19_DISPLAY_CODE |
| Natural Account Display Code (Level20) | Varchar | LEVEL20_DISPLAY_CODE | LEVEL20_DISPLAY_CODE |
| Natural Account Display Order Number (Level1) | Number | LEVEL1_DISPLAY_ORDER_NUM | LEVEL1_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level2) | Number | LEVEL2_DISPLAY_ORDER_NUM | LEVEL2_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level3) | Number | LEVEL3_DISPLAY_ORDER_NUM | LEVEL3_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level4) | Number | LEVEL4_DISPLAY_ORDER_NUM | LEVEL4_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level5) | Number | LEVEL5_DISPLAY_ORDER_NUM | LEVEL5_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level6) | Number | LEVEL6_DISPLAY_ORDER_NUM | LEVEL6_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level7) | Number | LEVEL7_DISPLAY_ORDER_NUM | LEVEL7_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level8) | Number | LEVEL8_DISPLAY_ORDER_NUM | LEVEL8_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level9) | Number | LEVEL9_DISPLAY_ORDER_NUM | LEVEL9_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level10) | Number | LEVEL10_DISPLAY_ORDER_NUM | LEVEL10_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level11) | Number | LEVEL11_DISPLAY_ORDER_NUM | LEVEL11_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level12) | Number | LEVEL12_DISPLAY_ORDER_NUM | LEVEL12_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level13) | Number | LEVEL13_DISPLAY_ORDER_NUM | LEVEL13_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level14) | Number | LEVEL14_DISPLAY_ORDER_NUM | LEVEL14_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level15) | Number | LEVEL15_DISPLAY_ORDER_NUM | LEVEL15_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level16) | Number | LEVEL16_DISPLAY_ORDER_NUM | LEVEL16_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level17) | Number | LEVEL17_DISPLAY_ORDER_NUM | LEVEL17_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level18) | Number | LEVEL18_DISPLAY_ORDER_NUM | LEVEL18_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level19) | Number | LEVEL19_DISPLAY_ORDER_NUM | LEVEL19_DISPLAY_ORDER_NUM |
| Natural Account Display Order Number (Level20) | Number | LEVEL20_DISPLAY_ORDER_NUM | LEVEL20_DISPLAY_ORDER_NUM |
| Creation date | Date | CREATION_DATE | CREATION_DATE |
| Created by | Number | CREATED_BY | CREATED_BY |
| Last updated by | Number | LAST_UPDATED_BY | LAST_UPDATED_BY |
| Last update date | Date | LAST_UPDATE_DATE | LAST_UPDATE_DATE |
| Last update login | Number | LAST_UPDATE_LOGIN | LAST_UPDATE_LOGIN |
| Object name | Varchar | OBJECT_NAME | OBJECT_NAME |
| Object definition name | Varchar | OBJECT_DEFINITION_NAME | OBJECT_DEFINITION_NAME |
| Natural Account (Level1) | Varchar | LEVEL1_NAME | LEVEL1_NAME |
| Natural Account (Level2) | Varchar | LEVEL2_NAME | LEVEL2_NAME |
| Natural Account (Level3) | Varchar | LEVEL3_NAME | LEVEL3_NAME |
| Natural Account (Level4) | Varchar | LEVEL4_NAME | LEVEL4_NAME |
| Natural Account (Level5) | Varchar | LEVEL5_NAME | LEVEL5_NAME |
| Natural Account (Level6) | Varchar | LEVEL6_NAME | LEVEL6_NAME |
| Natural Account (Level7) | Varchar | LEVEL7_NAME | LEVEL7_NAME |
| Natural Account (Level8) | Varchar | LEVEL8_NAME | LEVEL8_NAME |
| Natural Account (Level9) | Varchar | LEVEL9_NAME | LEVEL9_NAME |
| Natural Account (Level10) | Varchar | LEVEL10_NAME | LEVEL10_NAME |
| Natural Account (Level11) | Varchar | LEVEL11_NAME | LEVEL11_NAME |
| Natural Account (Level12) | Varchar | LEVEL12_NAME | LEVEL12_NAME |
| Natural Account (Level13) | Varchar | LEVEL13_NAME | LEVEL13_NAME |
| Natural Account (Level14) | Varchar | LEVEL14_NAME | LEVEL14_NAME |
| Natural Account (Level15) | Varchar | LEVEL15_NAME | LEVEL15_NAME |
| Natural Account (Level16) | Varchar | LEVEL16_NAME | LEVEL16_NAME |
| Natural Account (Level17) | Varchar | LEVEL17_NAME | LEVEL17_NAME |
| Natural Account (Level18) | Varchar | LEVEL18_NAME | LEVEL18_NAME |
| Natural Account (Level19) | Varchar | LEVEL19_NAME | LEVEL19_NAME |
| Natural Account (Level20) | Varchar | LEVEL20_NAME | LEVEL20_NAME |
| Natural Account Description (Level1) | Varchar | LEVEL1_DESCRIPTION | LEVEL1_DESCRIPTION |
| Natural Account Description (Level2) | Varchar | LEVEL2_DESCRIPTION | LEVEL2_DESCRIPTION |
| Natural Account Description (Level3) | Varchar | LEVEL3_DESCRIPTION | LEVEL3_DESCRIPTION |
| Natural Account Description (Level4) | Varchar | LEVEL4_DESCRIPTION | LEVEL4_DESCRIPTION |
| Natural Account Description (Level5) | Varchar | LEVEL5_DESCRIPTION | LEVEL5_DESCRIPTION |
| Natural Account Description (Level6) | Varchar | LEVEL6_DESCRIPTION | LEVEL6_DESCRIPTION |
| Natural Account Description (Level7) | Varchar | LEVEL7_DESCRIPTION | LEVEL7_DESCRIPTION |
| Natural Account Description (Level8) | Varchar | LEVEL8_DESCRIPTION | LEVEL8_DESCRIPTION |
| Natural Account Description (Level9) | Varchar | LEVEL9_DESCRIPTION | LEVEL9_DESCRIPTION |
| Natural Account Description (Level10) | Varchar | LEVEL10_DESCRIPTION | LEVEL10_DESCRIPTION |
| Natural Account Description (Level11) | Varchar | LEVEL11_DESCRIPTION | LEVEL11_DESCRIPTION |
| Natural Account Description (Level12) | Varchar | LEVEL12_DESCRIPTION | LEVEL12_DESCRIPTION |
| Natural Account Description (Level13) | Varchar | LEVEL13_DESCRIPTION | LEVEL13_DESCRIPTION |
| Natural Account Description (Level14) | Varchar | LEVEL14_DESCRIPTION | LEVEL14_DESCRIPTION |
| Natural Account Description (Level15) | Varchar | LEVEL15_DESCRIPTION | LEVEL15_DESCRIPTION |
| Natural Account Description (Level16) | Varchar | LEVEL16_DESCRIPTION | LEVEL16_DESCRIPTION |
| Natural Account Description (Level17) | Varchar | LEVEL17_DESCRIPTION | LEVEL17_DESCRIPTION |
| Natural Account Description (Level18) | Varchar | LEVEL18_DESCRIPTION | LEVEL18_DESCRIPTION |
| Natural Account Description (Level19) | Varchar | LEVEL19_DESCRIPTION | LEVEL19_DESCRIPTION |
| Natural Account Description (Level20) | Varchar | LEVEL20_DESCRIPTION | LEVEL20_DESCRIPTION |
The following table shows the joins in the Natural Accounts Dimension Hierarchy folder.
| Name | Detail Folder | Description | Join Relationship |
|---|---|---|---|
| Natural Accounts Dimension Hierarchy -> EPF Reconciliation | EPF Reconciliation | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Reconciliation.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Retirement Accounts | EPF Retirement Accounts | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Retirement Accounts.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Savings | EPF Savings | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Savings.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Term Deposits | EPF Term Deposits | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Term Deposits.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Trusts | EPF Trusts | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Trusts.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Wholesale Funding | EPF Wholesale Funding | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Wholesale Funding.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Balances | EPF Balances | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Balances.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF All Account Tables | EPF All Account Tables | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF All Account Tables.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Checking Accounts | EPF Checking Accounts | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Checking Accounts.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Commercial Loans | EPF Commercial Loans | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Commercial Loans.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Consumer Loans | EPF Consumer Loans | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Consumer Loans.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Credit Cards | EPF Credit Cards | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Credit Cards.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Credit Lines | EPF Credit Lines | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Credit Lines.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Customer Profit | EPF Customer Profit | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Customer Profit.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Investments | EPF Investments | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Investments.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Leases | EPF Leases | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Leases.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Merchant Cards | EPF Merchant Cards | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Merchant Cards.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Mortgage-Backed Securities | EPF Mortgage-Backed Securities | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Mortgage-Backed Securities.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Mortgages | EPF Mortgages | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Mortgages.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Off Balance Sheet | EPF Off Balance Sheet | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Off Balance Sheet.Natural Account | One to Many (1:n) |
| Natural Accounts Dimension Hierarchy -> EPF Other Services | EPF Other Services | Join between Natural Accounts Dimension Hierarchy.Level20 ID and EPF Other Services.Natural Account | One to Many (1:n) |
Other hierarchy folders contains similar joins to various data folders.
Calculated items are created for weighted average rate calculations on the instrument folders. The following tables show a list of instrument folders (arranged in alphabetical order) and their items that are used for weighted average calculation.
| EPF Checking Accounts | EPF Commercial Loans | EPF Consumer Loans | EPF Credit Cards | EPF Credit Lines | EPF Investments | EPF Leases | |
|---|---|---|---|---|---|---|---|
| Current Gross Rate | X | X | X | X | X | X | X |
| Current Loan to Value Rate | |||||||
| Current Net Rate | X | X | X | X | X | X | X |
| Current Gross Yield | X | X | X | X | X | X | X |
| Current Option Adjusted Spread | X | X | X | X | X | X | X |
| Current Static Spread | X | X | X | X | X | X | X |
| Distance from Lifetime Cap | X | ||||||
| Historic Option Adjusted Spread | X | X | X | X | X | X | X |
| Historic Static Spread | X | X | X | X | X | X | X |
| Margin (spread over index) | X | X | X | X | X | X | X |
| Margin Gross | X | X | X | X | X | X | X |
| Margin Transfer Rate | X | X | X | X | X | X | X |
| Matched Spread | X | X | X | X | X | X | X |
| Negative Amortization Limit | X | X | X | X | X | X | |
| Original Loan to Value Rate | X | X | X | X | |||
| Original Rate | X | X | X | X | X | X | X |
| Percent Sold | X | X | X | X | X | X | X |
| Payment Decrease Cycle | X | X | X | X | X | X | |
| Payment Decrease Life | X | X | X | X | X | X | |
| Rate Cap Life | X | X | X | X | X | X | |
| Rate Decrease Cycle | X | X | X | X | X | ||
| Rate Decrease Year | X | X | X | X | X | X | X |
| Rate Floor Life | X | X | X | X | X | X | X |
| Rate Increase Year | X | X | X | X | X | X | X |
| Rate Increase Cycle | X | X | X | X | X | ||
| Tax Exempt Percent | X | X | X | X | X | X | X |
| Transfer Rate | X | X | X | X | X | X | X |
| Remaining Term Transfer Rate | X | X | X | X | X | X | X |
| EPF Mortgage Backed Securities | EPF Mortgages | EPF Savings | EPF Term Deposits | EPF Wholesale Funding | |
|---|---|---|---|---|---|
| Current Gross Rate | X | X | X | X | X |
| Current Loan to Value Rate | X | ||||
| Current Net Rate | X | X | X | X | X |
| Current Gross Yield | X | X | X | X | X |
| Current Option Adjusted Spread | X | X | X | X | X |
| Current Static Spread | X | X | X | X | X |
| Distance from Lifetime Cap | X | X | X | ||
| Historic Option Adjusted Spread | X | X | X | X | X |
| Historic Static Spread | X | X | X | X | X |
| Margin (spread over index) | X | X | X | X | X |
| Margin Gross | X | X | X | X | X |
| Margin Transfer Rate | X | X | X | X | X |
| Matched Spread | X | X | X | X | X |
| Negative Amortization Limit | X | X | X | X | X |
| Original Loan to Value Rate | X | ||||
| Original Rate | X | X | X | X | X |
| Percent Sold | X | X | X | X | X |
| Payment Decrease Cycle | X | X | X | X | X |
| Payment Decrease Life | X | X | X | X | X |
| Rate Cap Life | X | X | X | X | X |
| Rate Decrease Cycle | X | X | X | X | X |
| Rate Decrease Year | X | X | X | X | X |
| Rate Floor Life | X | X | X | X | X |
| Rate Increase Year | X | X | X | X | X |
| Rate Increase Cycle | X | X | X | X | X |
| Tax Exempt Percent | X | X | X | X | X |
| Transfer Rate | X | X | X | X | X |
| Remaining Term Transfer Rate | X | X | X | X | X |
You can create additional items as follows:
Hide rate items and rename with “Non-weighted” prefix. For example, change the item name “Current Gross Rate” to "Non-weighted Current Gross Rate”.
Create calculated items with the prefix “Weighted” and with the following formula and hide them.
Formula:
“Non-weighted <rate item> ” x “Current Gross Par Balance”
Item Name: “Weighted Current Gross Rate”
Formula:
“Non-weighted Current Gross Rate” x “Current Gross Par Balance”
Create calculated items with rate item names.
Formula:
DECODE(SUM(Current Gross Par Balance),0,0,( SUM(“Weighted<rate item> ”)/SUM(Current Gross Par Balance) ))
Item Name: “Current Gross Rate”
Formula:
DECODE(SUM(Current Gross Par Balance),0,0,( SUM(Weighted Current Gross Rate)/SUM(Current Gross Par Balance) ))
The following tables list registered PL/SQL functions in the EPF Business area.
The following table describes the fem_dis_utl_pkg.get_converted_amount PL/SQL function.
| Function Name | PL/SQL | Discoverer Internal Function Name |
|---|---|---|
| EPF Get Converted Amount | fem_dis_utl_pkg.get_converted_amount | FEM_GET_CONVERTED_AMOUNT |
This function is a wrapper function that calls the fem_dis_utl_pkg.get_exchange_rate procedure to convert currency values from one currency to another using the GL currency conversion API.
FUNCTION Get_Converted_Amount(
p_from_currency IN VARCHAR2,
p_to_currency IN VARCHAR2,
p_cal_period_id IN NUMBER,
p_from_value IN NUMBER,
)RETURN number;
The following table describes the fem_dis_utl_pkg.visual_trace_url PL/SQL function.
| Function Name | PL/SQL | Discoverer Internal Function Name |
|---|---|---|
| EPF Visual Trace URL | fem_dis_utl_pkg.visual_trace_url | FEM_VISUAL_TRACE_URL |
This function returns a URL to the Oracle application page.
FUNCTION Visual_Trace_URL( p_function_name IN VARCHAR2, p_other_params IN VARCHAR2 DEFAULT NULL, )RETURN varchar2;
The following table describes the fem_dis_utl_pkg.get_dim_attribute_value PL/SQL function.
| Function Name | PL/SQL | Discoverer Internal Function Name |
|---|---|---|
| EPF Get Dimension Attribute Value | fem_dis_utl_pkg.get_dim_attribute_value | FEM_GET_DIM_ATTRIBUTE_VALUE |
This function returns a Get Dimension Attribute Value Wrapper over the FEM API.
FUNCTION GET_DIM_ATTRIBUTE_VALUE( p_dimension_varchar_label IN VARCHAR2, p_attribute_varchar_label IN VARCHAR2, p_member_id IN NUMBER, p_value_set_id IN NUMBER, p_attr_version_display_code IN NUMBER, p_return_attr_assign_mbr_id IN NUMBER, )RETURN number;
The following table describes the fem_dis_utl_pkg.get_relative_cal_period_name PL/SQL function.
| Function Name | PL/SQL | Discoverer Internal Function Name |
|---|---|---|
| EPF Get Relative Calendar Period Name | fem_dis_utl_pkg.get_relative_cal_period_name | FEM_GET_RELATIVE_CAL_PERIOD_NAME |
This function returns a Get Relative Calendar Period Name for a given base period and offset.
FUNCTION BASE_CAL_PERIOD_ID( p_base_cal_period_id IN NUMBER, p_offset IN NUMBER, )RETURN varchar2;
The core FTP business area, called FTP - Core Business Area, resides in the standard Oracle Applications end user layer (EUL_US) along with the EPF - Core Business Area. The core FTP business area contains folders for Oracle Transfer Pricing (FTP) specific tables. The following table describes the names and types of the folders based on the source tables included in the FTP - Core Business Area.
| Source Table | Folder Type | Folder Name |
|---|---|---|
| FTP_CF_CORRECTIONS | Simple Folder | FTP Cf Corrections |
| FTP_PROCESS_CASH_FLOW | Simple Folder | FTP Process Cash Flows |
| FTP_TRANSFER_PRICE_RULE | Simple Folder | FTP Transfer Price Rule |
Related Topics
Core EPF Business Area and its Components