4      Regulatory Reporting Solution Data Flow

This chapter provides an understanding of the data flow. It explains what happens within data flow and how various processing aspects are integrated with the overall data flow.

Topics:

·        Data Preparation 

·        Basel Processing to US FED Results Integration

·        LLFP Processing to US FED Results Integration

·        LRM Processing to US FED Results Integration

·        Overview of OFS REG REP US FED User Interface

·        Data Schedule Mapping

·        Adjustment Feature for Template-based Reports

·        Direct Upload for Data Schedules

·        Data Schedule Migration

·        Mapping of Results to Line Items in Reporting

·        AgileREPORTER: Submission

Data Preparation

This section explains the input data preparation from OFSAA.

Topics:

·        Assumptions for Data Preparation 

·        Prerequisite Tasks for US FED Run Execution

·        US FED Run Chart

·        Reclassification of Reporting Dimensions

·        Configuring Setup Tables for Standard Set of Values

·        Backward Compatibility Support

·        Run or Execution Expectations

·        Consolidation

·        Projection Data

·        Data Flow from Sources Systems to Staging Area

·        Data Flow from Staging to Results Area

·        Data flow from Staging to Processing Area

·        Data Flow from Processing to Results Area

·        Computation of Offset and Netting Balances for Assets and Liabilities

·        Guidelines for Data Loading to Result Area Tables in Data Foundation for Regulatory Reporting Implementations

·        FSDF Entity Information

·        Fact Tables or Entities

·        Inclusion of GL Recon reconciled Accounts in Reporting

Assumptions for Data Preparation

The following are the assumptions for data preparation:

1.     REG REP is a reporting solution, which uses data from underlying fact tables directly for reporting. You are expected to prepare the load for the required data in the reporting area accordingly. Although this has a thin processing layer to reclassify regulatory dimensions and bands, all the processing measures are expected to be from respective applications and provide as required.

2.     It is integrated with the results area of the respective processing application, and any change in the underlying processing can disturb the REG REP data sourcing.

3.     Baseline and stress data must be populated with appropriate codes. Inaccurate mappings lead to inaccurate results. For details please refer to Relationship between Run and Stress.

4.     For usage of consolidation dimension (which has values like Actual, Budget, Forecast, and so on), all historical data is expected to be tagged as actual to report vintage data, as per report requirements. For projection data, for a given run and Projection Period (quarter/year), only one set of data is expected to be stored.

5.     All processing reporting requirements requiring cash flows, integration package expects bucketed cash flow as an input (meaning a time bucket for cash flow and cash flow amount is expected as input).

6.     FR 2900, FFIEC-031 RC-K, FFIEC-041 RC-K, FR Y-9C HC-K, FFIEC-031 RC-O, and FFIEC-041 RC-O reports require the averaging of the balances as of the close of business for each day for the calendar quarter or an average of the balances as of the close of business on each Wednesday during the calendar quarter. Oracle Financial Services Regulatory Reporting for US Federal Reserve – Vermeg Integration supports both the above methods.

7.     You must update V_COMPONENT_VALUE in SETUP_MASTER with the method followed at the respective financial institution:

a.     For daily averaging, populate the value ‘EVERY-DAY’.

b.     For weekly averaging, populate the value ‘EVERY-WEDNESDAY’.

You must update the FSI_CAL_MIS_DATE_MAP table with dates for which averaging is required.

The table FSI_CAL_MIS_DATE_MAP (D_CALENDAR_DATE DATE, D_MIS_DATE DATE) must be populated for Reports – FR 2900, FFIEC-031 RC-K, FFIEC-041 RC-K, FR Y-9C HC-K,
FFIEC-031 RC-O, and FFIEC-041 RC-O.
FSI_CAL_MIS_DATE_MAP is an entity used to generate the quarterly average report with two date columns: D_CALENDAR_DATE and D_MIS_DATE.

a.     D_CALENDAR_DATE holds the date details for the calendar year. This includes the holiday date.

b.     D_MIS_DATE holds the effective date to be considered for quarterly average report generation. This column is excluding the holiday date.

c.     If the calendar date falls on a holiday, then D_MIS_DATE has value (date) for the last working date or any other date value as per the client's requirement.

The above-mentioned reports are generated only if FSI_CAL_MIS_DATE_MAP is populated.

Example of data in FSI_CAL_MIS_DATE_MAP:

Table 5: FSI_CAL_MIS_DATE_MAP

When performing averaging:

a.     For each date, reporting execution is selected.

b.     A business processor holds the average function for the data selected.

Post average calculation averaged data is sent to AgileREPORTER.

8.     Addition of Setup Master Entries for Branch/FED level reporting:

To ensure retrieval at the Branch/FED level, the RUNEXESUMM view must have the relevant information. This information can be configured by changing the entries for SETUP_MASTER tables as follows:

a.     The relevant component code for the configuration in the SETUP_MASTER table is ‘BRANCH_FED_DIST_IDENTIFIER’ for the following default configuration.

b.     To enable RUNEXESUMM entries for Branch/FED District, the V_COMPONENT_VALUE must be changed to the V_ACCT_BRANCH_CODE / V_FED_RESERVE_DISTRICT value as per the DIM_GEOGRAPHY table respectively. This Branch/FED District value must be the one for which retrieval is done.

The RUNEXESUMM view now reflects the entries for the Branch/FED District for retrieval purposes.

9.     “FCT_REG_ACCOUNT_SUMMARY.F_READILY_DETER_FAIR_VALUE must be populated by a Custom Rule by User based on the availability of FCT_IFRS_ACCOUNT_SUMMARY.N_IFRS_FAIR_VALUE_LEVEL1_RCY, N_IFRS_FAIR_VALUE_LEVEL2_RCY or other logic which you deem as Appropriate.”

“The Code ‘OTHLIAB’ with description ‘Other Liabilities’ is introduced in Table DIM_REG_PRODUCT_TYPE to facilitate reporting of Other Liabilities in specific line items according to the User Requirements. There is no OOTB Rule to populate this value as the composition of this value is not mentioned explicitly in the Regulatory instructions and can vary from user to user.”

“FSI_REG_REPORTING_PARAM is used in Reporting of certain Line Items which requires specific inputs from the user, notably ASU Adoption Check for which Logic for Reporting varies based on whether ASU Accounting Standard is adopted by the Reporting Institution or not and Sanctioned Limit Threshold in Schedules like FR Y-14Q Schedule K (Supplemental) which can be different from the Regulator prescribed value for few reporters.

For example:

v_Regulator_code = 'USFED'

v_reg_reporting_param = ASU201601ADOPTION

v_reg_reporting_param_val = 'Y'

Sample values in this table are provided as part of the configuration as mentioned above and can be updated based on the user requirements.”

Prerequisite Tasks for US FED Run Execution

US FED Run (RNUS_REG_RUN) has tasks that populate data into the Run-enabled tables. Few tasks are prerequisites for US FED Run.

These tasks have data flow for non-Run-enabled tables, and hence these tasks must be executed only once per FIC_MIS_DATE irrespective of the number of Apps installed/number of Batches or Run having the same task.

Recommendations for OFSAA Apps Integration with REG REP US FED

As the prerequisite Batches/Run must be executed only once per FIC_MIS_DATE. These are expected to be a non-Run enabled task, hence re-execution causes inconsistency.

If the customer has multiple OFSAA applications that share common metadata like SCD, T2T which are of non-Run enabled in nature, then those tasks must be combined in a single Batch/Run by eliminating all duplicate tasks from all apps.

For example: ##INFODOM##_REG_US_COMMON_SCD can have overlapping Task with OFS_CAP_PACK’s ##INFODOM##_SCD. As both applications use the same SCD metadata, the task re-execution can cause inconsistency in Surrogate Keys. Hence, such tasks must be de-duped before integrating the App Runs.

The main Run can continue to be a separate Run as it has only Run-enabled flows and each Run represents the data required for each Application.

US FED RUN CHART

Oracle Financial Services Regulatory Reporting for US Federal Reserve – Lombard Risk Integration Pack provides the US FED RUN Chart listing the tasks required for the population of data for US FED Reports. This covers the following tasks:

·        Set up table population

·        Stage Dimension Load

·        Seeded Dimension Data Population

·        Common data Population

·        Common Tasks like Exchange Rate Population

·        US FED Specific Data Population and Transformation

·        Derived Entity Refresh

Download the US FED 8.1.2.0.0 RUN Chart from the MOS.

Reclassification of Reporting Dimensions

This section provides information about Reporting Dimension Tables in the Regulatory Reporting for US Federal Reserve – Lombard Risk Integration Pack (OFS REG REP US FED) application and step-by-step instructions to use this section.

This section includes the following topics:

·        Overview of Reclassification of Reporting Dimensions

·        Overview of Reclassification of Reporting Dimensions Population

·        Dimension Data Expectations through SCD

·        Overview of Mappers for Reclassification of Reporting Dimensions

·        Maintenance of Mappers for Reclassification of Reporting Dimensions

·        Loading Mapper Maintenance through Backend

·        Usage of Mapper Tables in Data Flow and Reports

Overview of Reclassification of Reporting Dimensions

There are certain Reporting Dimensions in OFS REG REP US FED, which are pre-populated with a standard set of values. These values are used by downstream applications for various reporting requirements. There are equivalent customer-specific dimension tables that are populated using a Slowly Changing Dimension (SCD) process. It is required to reclassify these user-specific values to standard / regulatory specific values as the reporting expects these standard sets of values. The reclassification is done using out-of-the-box Mapper Definitions under the Mapper Maintenance screen.

Overview of Reclassification of Standard Dimensions Population

These are the out of the box User Specific dimensions to Standard Dimensions reclassification available in OFS REG REP US FED:

Table 6: Standard Dimension Reclassification

Overview of Reclassification of Regulatory Dimensions Population

These are the out of the box User Specific dimensions to Regulatory Dimensions reclassification available in OFS REG REP US FED:

Table 7: Regulatory Dimension Reclassification

Dimension Data Expectations through SCD

By default, all standard dimensions are pre-populated with seeded data. It is mandatory to have data in user-specific dimensions and then maintain the reclassifications. Therefore, you must execute the SCDs and then map the reclassification codes under Mapper Maintenance.FCT

Mappers for Reclassification of Standard Dimensions

These are out of the box mappers that are available in OFS REG REP US FED for the standard dimension reclassifications:

·        MAP_PROD_CODE_STD_PROD_TYPE: Mapper for Product Code to Standard Product Code

·        MAP_PARTY_TYP_STD_PARTY_TYP: Mapper for Party Type Code to Standard Party Type Code

·        MAP_CRDLN_TYP_STD_CRDLN_TYP: Mapper for Credit Line Type to Standard Credit Line Type

·        MAP_DIM_IRC_STD_IRC: Mapper for Interest Rate Code to Standard Interest Rate Code

·        MAP_DIM_LOB_STD_LOB: Mapper for Line of Business Code to Standard Line of Business Code

·        MAP_BAL_CAT_STD_BAL_CAT: Mapper for Balance Category to Standard Balance Category

·        MAP_CRDLN_PUR_STD_CRDLN_PUR: Mapper for Credit Line Purpose to Standard Credit Line Purpose

·        MAP_MITG_TYP_STD_MITGN_TYP: Mapper for Mitigant Type to Standard Mitigant Type

·        MAP_CREDIT_SCR_MDL_REG_MDL: Mapper for Credit Score Model To Reg Credit Score Model

·        MAP_DIM_GL_ACCT_STD_GL_TYPE: Mapper for General Ledger Account to Standard General Ledger Account Type

·        MAP_GL_CODE_REP_LINE: Mapper for GL Code to Repline Code

·        MAP_RECVR_TYP_STD_RECVR_TYP: Mapper for Common Recovery Type to Standard Recovery Type

·        MAP_VEHCL_TYP_STD_VEHCL_TYP: Mapper for Vehicle Type to Standard Vehicle Type

·        MAP_WRTOFF_STD_WRTOFF_REASN: Mapper for Write Off Reasons to Standard Write Off Reasons

Mappers for Reclassification of Regulatory Dimensions

These are out of the box mappers that are available in OFS REG REP US FED for the regulatory dimension reclassifications:

·        MPFD_ACCT_REG_ACCT_PURPOSE: Reg US Mapper for Regulatory Account Purpose

·        MPFD_ACCT_REG_ACCT_STATUS: Reg US Mapper for Regulatory Account Status

·        MPFD_APLCN_REG_APLCN_STATUS: Reg US Mapper for Regulatory Application Status

·        MPFD_ACCT_REG_CREDIT_STATUS: Reg US Mapper for Regulatory Credit Status

·        MPFD_DOC_TYPE_REG_DOC_TYPE: Reg US Mapper for Regulatory Document Type

·        MPFD_ACC_INDSTR_REG_INDSTRY: Reg US Mapper for Regulatory Industry

·        MPFD_ACCT_REG_LOAN_PURPOSE: Reg US Mapper for Regulatory Loan Purpose

·        MPFD_PROP_REG_PROPERTY_TYPE: Reg US Mapper for Regulatory Property Type

·        MPFD_SEC_POOL_REG_SEC_POOL: Reg US Mapper for Regulatory Sec Pool Type

·        MPFD_UNDERLYNG_REG_UND_TYPE: Reg US Mapper for Regulatory Underlying Type

·        MPFD_ORGUNT_LOB_STD_SEC_LOB: Reg US Mapper for Std Secondary Line of Business

Maintenance of Mappers for Reclassification of Standard Dimensions

The mapper can be maintained under OFSAAI.

1.     After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Federal Reserve, select Administration, and then select Map Maintenance.

Figure 36: Map Maintenance page

2.     For illustration, we have selected Mapper for Mitigant Type to Standard Mitigant Type. Click Mapper Maintenance.

Figure 37: Mapper for Mitigant Type to Standard Mitigant Type

3.     The OFS REG REP US FED maps OTH and MSG out-of-the-box for this mapper. The remaining mappings can be maintained by the user according to user-specific values.

Figure 38: Map Maintenance Search page

Prerequisites for Mapper Maintenance

1.     After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Federal Reserve, select Administration, and then select Save Metadata. Load all the required user-specific dimensions using SCD.

2.     To Resave these hierarchies, select these hierarchies and click Save.

§       HCMDF001 - Hier - Map Common Product

§       HCMDF002 - Hier - Map Common Standard Product Type

§       HCMDF003 - Hier - Map Common Party Type

§       HCMDF004 - Hier - Map Common Standard Party Type

§       HCMDF005 - Hier - Map Common Interest Rate Curve

§       HCMDF006 - Hier - Map Common Standard Interest Rate Curve

§       HCMDF007 - Hier - Map Common Line of Business

§       HCMDF008 - Hier - Map Common Standard Line of Business

§       HCMDF009 - Hier - Map Common Credit Line Type

§       HCMDF010 - Hier - Map Common Standard Credit Line Type

§       HCMDF011 - Hier - Map Common Credit Line Purpose

§       HCMDF012 - Hier - Map Common Standard Credit Line Purpose

§       HCMDF013 - Hier - Map Common Mitigant Type

§       HCMDF014 - Hier - Map Common Standard Mitigant Type

§       HCMDF015 - Hier - Map Common Balance Category

§       HCMDF016 - Hier - Map Common Standard Balance Category

§       HCMDF017 - Hier - Map Common General Ledger Code

§       HCMDF018 - Hier - Map Common Standard General Ledger Type

§       HCMDF019 - Hier - Map Common Vehicle Type

§       HCMDF020 - Hier - Map Common Standard Vehicle Type 

§       HCMDF021 - Hier - Map Common Write Off Reasons

§       HCMDF022 - Hier - Map Common Standard Write Off Reasons

§       HCMDF023 - Hier - Map Common Recovery Type

§       HCMDF024 - Hier - Map Common Standard Recovery Type

§       HRLMP001 - HIR - RLMP Industry Codes

§       HRLMP002 - HIR - RLMP Regulatory Industry Codes

§       HRLMP003 - HIR - RLMP Application Status

§       HRLMP004 - HIR - RLMP Regulatory Application Status

§       HRLMP005 - HIR - RLMP Document Type

§       HRLMP006 - HIR - RLMP Regulatory Document Type

§       HRLMP007 - HIR - RLMP Account Status

§       HRLMP008 - HIR - RLMP Regulatory Account Status

§       HRLMP009 - HIR - RLMP Regulatory Account Purpose

§       HRLMP010 - HIR - RLMP Organization Unit Code

§       HRLMP011 - HIR - RLMP Line of Business Code

§       HRLMP012 - HIR - RLMP Std Secondary Line of Business

§       HRLMP013 - HIR - RLMP Underlying Type

§       HRLMP014 - HIR - RLMP Regulatory Underlying Type

§       HRLMP501 - HIR - RLMP Property Type

§       HRLMP502 - HIR - RLMP Regulatory Property Type

§       HRLMP503 - HIR - RLMP Account Purpose

§       HRLMP504 - HIR - RLMP Regulatory Loan Purpose

§       HRLMP505 - HIR - RLMP Account Status Code

§       HRLMP506 - HIR - RLMP Regulatory Credit Status

§       HRLMP507 - HIR - RLMP Sec Pool Type

§       HRLMP508 - HIR - RLMP Regulatory Sec Pool Type

Figure 39: Metadata Resave page

Possible Mapping Combinations

One Standard Dimension table in the source can be mapped only to one Standard Dimension table. One to Many or Many to Many mapping leads to an error in T2T as the records are duplicated. From the illustration, the possible combinations for Mitigant Type to Standard Mitigant Type Mapping are One to One and Many to One Mapping.

·        One to One Mapping: You can map one Mitigant Type data model to one Standard Mitigant Type data model using the Mapper Maintenance screen. Here, you must select one value in the Mitigant Type data model and one value in the Standard Mitigant Type Data Model.

·        Many to One Mapping: You can map many values in the Mitigant Type Data Model to one value in the Standard Mitigant Type Data Model using the Mapper Maintenance Screen.

To conduct One to One or Many to One mapping:

1.     After logging into the OFSAAI Applications Page, navigate to Regulatory Reporting for US Federal Reserve, select Administration, and then select Map Maintenance.

Figure 40: Map Maintenance Page

2.     Click Create new Map icon to create a new map or select an existing Map. For illustration, Mapper for Party Type Code to Standard Party Type Code value is selected. Click the Mapper Maintenance icon.

3.     The Mapper Maintenance window opens (in this illustration, the Map - Mapper for Party Type Code to Standard Party Type Code Window opens). To conduct One to One or Many to One mapping, in the Member Combinations section, click Add.

Figure 41: Map Maintenance Add Page

4.     The Add Mappings pop-up window opens. In this illustration:

§       To map One to One, select one value in the Hier - Map Common Mitigant Type Data Model and one value in the Hier - Map Common Standard Mitigant Type Data Model, and click Go. Repeat this step for each One to One data model mapping, and then click Save.

In this illustration, MSG - Missing is mapped to AFC - Auto Financing Company.

Figure 42: One to One Mapping Page

·        To map Many to One, select multiple (two in this illustration) values in the Hier - Map Common Mitigant Type Data Model and one value in the Hier - Map Common Standard Mitigant Type Data Model, and then click Go. Click Save.

In this illustration, MSG-Missing and OTH-Others are mapped to the AFC-Auto Financing Company.

Figure 43: One to Many Mapping Windows

5.     An acknowledgment is displayed: Confirm Save? To confirm and save data, click Yes. In the Mapper Maintenance Window, in the Mapped combinations and the Mapped Member's Sections, you can see the newly conducted mapping.

Loading Mapper Maintenance through Backend

Load each Physical table in Atomic Schema with V_MAP_ID as mentioned against each mapper,

V_MEMBER_1 => Customer Specific Value Dimension's Member Code, V_MEMBER_2 => Standard Dimension’s Member Code.

This is the list of Mapper Physical Tables and required details:

Table 8: Mapper Physical Tables

Usage of Mapper Tables in Data Flow and Reports

The mapper maintenance output is always physically stored in the underlying tables. These tables are registered in OFSAA as an object. Therefore, these tables can be used, without any restrictions, in any of the metadata that requires reclassification. OFS REG REP US FED Data Flows (T2Ts and Rules) make use of this information to populate the Standard Dimension Surrogate Keys of Results area tables.

Configuring Setup Tables for Standard Set of Values

The following are the setup configurations that are required to be done before executing the US FED Regulatory Reporting Run.

SETUP_MASTER Table

The SETUP_MASTER table in an Atomic Schema must be modified with the required values for US FED.

Table 9: Setup Master

FSI_REGREPORTING_PARAM

The FSI_REGREPORTING_PARAM table in an Atomic Schema must be modified with the required values for US FED as a one-time activity.

The V_REG_REPORTING_PARAM_VAL must be updated to B for Branch and D for Fed District, based on the report submitted for Branch or Fed District entities.

The default value of this parameter is O.

Table 10: FSI_REGREPORTING_PARAM

FSI_PARTY_STD_PARTY_MAP

In the US FED Regulatory Reporting, there is a reporting requirement for certain Party which is a Regulatory Standard. As the Party Dimension is an SCD table and the values of Party Identifier Code (V_PARTY_ID) can change from bank to bank, the FSI_PARTY_STD_PARTY_MAP is used for mapping the bank-specific V_PARTY_ID to Regulatory-specific V_STD_PARTY_CODE. Here, you must modify the V_PARTY_ID column according to the bank-specific V_PARTY_ID of the corresponding Party, which is stored in the Party Dimension (DIM_PARTY).

The following are the STD Party Codes used in the US FED Regulatory Reporting.

Table 11: Standard Party Codes

FSI_REG_MORT_INSURER

In the US FED Regulatory Reporting, there is a reporting requirement for certain Mortgage Issuers which are considered to be Regulatory Standard. As Party Dimension is an SCD table and the values of Party Identifier Code (V_PARTY_ID) can change bank to bank, the FSI_REG_MORT_INSURER table is used for mapping the bank-specific V_PARTY_ID to Regulatory-specific V_REG_MORT_ISSUER_CD. Here, you must modify the V_PARTY_ID column according to the bank-specific V_PARTY_ID of the corresponding Party, which is stored in Party Dimension (DIM_PARTY).

The following are the Regulatory Specific Issuer Codes that are getting used in US FED Regulatory Reporting.

Table 12 Issuer Codes

Backward Compatibility Support

The changes in the seeded dimension values can impact the sourcing in the Staging layer as the values expected in the reporting condition can mismatch with the existing source data. To support the old values along with the new configurations, you can use the Backward Compatibility Data Transformation batch for every MIS Date along with the regular Run Chart executions.

The batch which is packaged out-of-the-box is <INFODOM>_UPDATE_BACKWARD_COMP. This must be executed after every Stage data load.

1.     Entity Type Changes

During the past releases, there were changes in the Entity Type Dimension values for supporting the changes in reporting conditions. To continue to source old values and use a new configuration, you can use the batch which updates the Stage Org Structure Master Table Entity Type column with the reporting requirement using the old sourcing values.

2.     Subordinated Debt

There were changes in the Subordinated Debt sourcing requirement for supporting the changes in reporting conditions. Earlier, the Instrument Type was used to identify the subordinated debt products, whereas now a flag is used in the Stage Borrowings table. The batch can be used to update the flag using the instrument type and continue to source the values in the instrument type.

3.     Counter Party CVA Table

There were changes in the reporting conditions for the Counter Party CVA. Earlier, the report was retrieved from the Customer Summary table, which is moved to the Counter Party CVA (Basel Processing Output) table. Now, there is a new T2T (T2T_FCT_CP_CVA_DETAILS_MIGRATION) introduced to support the backward compatibility that can be added to the Run after the Task for T2T_FCT_REG_ACCOUNT_SUMMARY. This T2T is not included in out-of-the-box Run but can be added to the Run at the customer site to load the table.

Run or Execution Expectations

Run refers to execution. It is assumed that at different periods, different combinations of parameters, and different data require different executions. From a reporting perspective, as required by regulators, the RRDF application requires data for the following executions:

1.     Current Data / Execution

a.     Reporting month-end data

b.     Projection Data

2.     Historical (trend/vintage) Data

a.     Yearly

b.     Quarterly

3.     Stressed Data

Consolidation

Consolidation is handled as part of the Financial Services Data Foundation (FSDF). Consolidation in FSDF refers to the elimination of intracompany transactions, that is, any kind of transactions between two parties or entities which are part of the reporting organizational hierarchy for a given execution. When there is only one legal entity involved in an execution it is called SOLO Entity vs the earlier one as CONSOLIDATED Entity.

It is expected that in the staging area, the customer loads the data from the source system and then uses consolidation logic to arrive at the consolidated output for results.

·        The scope of consolidation is about a list of Entities that participate in consolidation.

·        Legal Entity Structure is looked at through ORGANIZATION STRUCTURE DIMENSION.  This stores a parent-child relationship. This is stored only once.

·        While moving the data, Legal Entity can move related entities to the processing/reporting area.

·        The legal structure being finalized once, this structure only stores one parent-child relationship.

Figure 44: Consolidation workflow

·        Transaction/exposure between SUB 1 and SUB 2 should be eliminated while reporting for Parent A.

·        Transaction/exposure between SUB 1 and SUB 3 should not be eliminated while reporting for Parent A.

·        It is a customer for banking products and issuer for traded securities which are considered for the intracompany elimination.

Consider the following example:

Table 13: Consolidation

As shown in the preceding table, Account 1 is moved to the FSI INTRA COMPANY ACCOUNT and Account Summary tables. Run Enabled tables contain records specific to the selected legal entity and consolidation type.

Consolidation is also linked to multiple hierarchies banking organizations have. Multiple hierarchies refer to the different grouping of group entities under different parents for the given regulatory requirements.

Refer to the following representation where FR Y-9C and FR-2052A are two regulatory reporting requirements.

Figure 45: Consolidation with Multiple Hierarchies

Consolidation percentage refers to the percentage of asset or liability of a Child Entity that is brought under the Parent Heading. Except for Joint ventures and similar organization structures, Child Entities are moved under the Parent or they are not. This means the consolidation percentage is either 100% or 0%. For proportionate consolidation (Joint venture is an example for this), a given Child is moved under two Parents with all assets and liabilities divided as per the Joint Venture Agreement. Currently, in FSDF 804, proportionate consolidation is not handled.

The hierarchy structure is thus the primary input to the consolidation process. Depending on whether you have multiple hierarchies or not, there are two data flows.

Consolidation with Multiple Organization Structure Hierarchy:

·        You load Organization Structure Hierarchy to the STAGE ORG STRUCTURE MASTER table, which is moved to the ORG STRUCTURE DIMENSION using the SCD component.

·        Execution-specific organization structure hierarchies along with parent and child entity codes are populated in the STAGE LEGAL ENTITY HIERARCHY INTERFACE table, which is moved to the LEGAL ENTITY HIERARCHIES DIMENSION using the SCD component.

·        Execution-specific Consolidation percentage is loaded in the STAGE ENTITY CONSOLIDATION PERCENTAGE table, where the child entity code, parent entity code, and the consolidation percentage are populated. This is moved to the FACT ENTITY CONSOLIDATION PERCENTAGE table using Table-to-Table transformation. In FSDF 804 release, this feature is not supported yet.

The STAGE LEGAL ENTITY HIERARCHY is used for the Consolidation process and not the one from ORGANIZATION STRUCTURE DIMENSION.

Figure 46: Consolidation with Multiple Organization Structure Hierarchy

If you do not have Multiple Hierarchies, STAGE LEGAL ENTITY HIERARCHY which is used for the Consolidation process can be populated from ORG STRUCTURE DIMENSION instead of the STAGE LEGAL ENTITY HIERARCHY.

Figure 47: Consolidation without Multiple Organization Structure Hierarchy

A Solo Run does not require any type of consolidation of the elimination of accounts with other entities.

Additional Data Preparations to handle Consolidation

The entity FCT_REG_RUN_LEGAL_ENTITY_MAP is used once you select REPORTING ENTITY from AgileREPORTER. This table is populated as part of the USFED Run Execution.

Table 14: Reporting Entity in AgileREPORTER

For the solo run, only one reporting entity is expected to be included whereas a consolidated run includes all entities involved in execution. This entity provides flexibility to select one REPORTING ENTITY in AgileREPORTER and select relevant data for the particular execution based on if it is consolidated or solo.

Relationship between Run and Stress

The REG REP application for example in FRY 14 Annual, picks up reporting data based on the Reporting Run that populates the underlying Fact Table(s). Reporting Run is a flag, which must be marked as 'Y' in a DIM_RUN table so that, the OBIEE reporting layer selects a particular run execution.

In this application, a Run comprises:

Baseline Run: The Bank Holding Company (BHC) may have multiple runs. The run used for reporting is marked with a Reporting Flag = Y. This is the Baseline run for a given reporting date. It is referred to as a Baseline because the values that it represents are not stressed and the BHC may use these base values for stressing them according to various scenarios. A history of such runs accumulated over some time provides historical runs. For more information on updating the reporting flag, refer to section Updating Reporting Flag.

NOTE:   

For retrieving multiple Runs in AgileREPORTER for the same date, you must refresh the Derived Entities for each Run separately by enabling and disabling the Reporting Flag in a sequence.

Stress Run: Stress runs hold data, which are stressed by a certain percentage/basis point over the Baseline figures. The BHC expects these figures to reflect the business/risk position under predetermined business scenarios/economic conditions.

Identification of Baseline and Stress Run occurs from STRESS DIMENSION.

In this application, the required stress runs are tagged to a Baseline Run. If the BHC performs several Stress Runs, the Relevant Runs which are intended for reporting are identified and tagged with a reporting Baseline Run using the V_RUN_ID in the DIM_RUN.

DIM RUN stores n_run_skey / v_execution_id, which are execution specific for every Run definition which is v_run_id. Therefore, the Run definition can remain constant over some time and different executions provide different outputs due to underlying data changes.

DIM_STRESS conveys the stress definition. Additionally, it links the original run Definition (v_run_id) and Stressed run ID (v_stressed_run_id). You must refer to the DIM_RUN table to get the expected run execution of these runs definitions about a particular date / n_mis_date_skey.

The same fact table stores both the Baseline data and the Stressed data, uniquely identified through Scenario Codes (and Run Skeys).

Refer to the Business Metadata.xls present in the installer package for details on different Fact tables used for related reports.

Projection Data

The following points provide information on the Projection Data:

1.     The baseline Run also populates Projected Date Data.

2.     This application requires projected data at two levels - Quarterly and Annual.

3.     The DIM_CONSOLIDATION table is used to identify the projections. It contains the codes for projected quarters and years as required by the templates.

4.     In the Fact tables, projection data is referred to with the respective Consolidation Codes (scenario code for FCT_MGMT_REPORTING). BHC must populate the data accordingly.

5.     In the following example, FQ1 means Financial Quarter 1, FY1 means Financial Year 1, and so on.

Table 15: Projection Data Example 1

NOTE:   

For Movement measures data is not carried from one reporting period to another. For example, Profit or Loss. Where General ledger balances such as loan outstanding are carried forward from one year to another, profit and loss are period specific.

Therefore, unlike the End of Period (EoP) balance, movement values for quarter actuals must be derived for reporting. For historical data, net sales for quarter 3 is the difference between the sales figure as of the end of quarters 2 and 3. You need not provide this difference as a download. Movement data for actual is identified through different runs and respective values are summed up.

Only those records, whose corresponding runs fall between the fiscal month start date and end date of the reporting quarter are selected for summation. Each Run has an associated date and runs can be performed daily. Assuming that runs are performed daily in a given quarter (90 days), REG REP sums up data points across all 90 days to arrive at a quarter-end movement figure.

Table 16: Projection Data Example 2

However, when the projection of net sales for quarter 2 next year is to be performed, no derivation is required. Projections data for the said quarter can be directly downloaded in the respective Fact table(s) for reporting.

Data Flow from Source Systems to Staging Area

The staging area is populated with data from various data sources, such as GL data, Account data, Customer data, Trading data, Currency data, and Master data. See the Data Integration Hub (DIH) User Guide in OHC Documentation Library for details. DIH enables to load of the data from the source systems to the OFSAA staging tables, through logical interfaces, known as Application Data Interfaces (ADI). DIH provides a set of User Interfaces (UI), which is used to define and maintain External Data Descriptor (EDD), Application Data Interfaces, and map the EDDs and ADIs through connectors.

Data Flow from Staging to Results Area

This section details the pass-through data, transformed data, and classification.

Topics:

·        Pass-Through Data 

·        Derived or Transformed Data and Reclassifications 

·        Reclassified to Regulatory Classifications 

Pass-Through Data

Pass through data refers to the static data that is pre-processed and flows to the results area directly. The Common Staging Area (CSA) model represents the data entry point into the FSDF. CSA provides a simplified, unified data sourcing area for inputs required by analytical applications and engines. It consists of over 400 tables and nearly 9000 columns organized into distinct subjects.

The staging area is a physical data model, which is deployed using the Analytical Application Infrastructure, which manages it. The design of the staging area data model is to allow efficient data loading for analytics. It thus has crucial differences from a general-purpose repository of operational or transactional data across a bank.

The staging area acts as the single source of data and contains unified data requirements for various banking areas such as Loans and Losses, Off-balance Sheet products, Securities, Derivatives, Capital Data, Management Ledger, and General Ledger. A common example of this category includes various monetary amounts, dates, and so on.

Derived or Transformed Data and Reclassifications

OFSDF Interface with Lombard Risk for OFS REG REP US FED requires specific hierarchies and data to be transformed and reclassified to regulator-specific values.

Table 17: Data Transformation Example

For example, data from banks has attributes such as issuer type and bank instrument type. However, these values are bank-specific and must be converted or reclassified to a regulatory specific set of values such as DIM REG INSTR CLASSIFICATION as mentioned above.

Reporting derived entities use these reclassified dimensions. Some of the reclassifications are performed in the respective application area.

For example, DIM BASEL PRODUCT TYPE. This reclassification is performed in Basel application processing and is available for reporting directly.

Other transformations include various bands such as delinquency band, loan purpose, and so on.

Reclassified to Regulatory Classifications

After transformation, the regulatory data is reclassified as follows.

Table 18: Data Reclassification Example 1

Table 19: Data Reclassification Example 2

The sample reclassifications performed to transform the existing hierarchies to regulatory specific hierarchies are:

·        Regulatory Product Classification

·        Regulatory Instrument Classification

·        Regulatory Deposit Classification

·        Trading Account Book Type Classification

·        Claim Amount Population for Country Risk

·        Immediate Counterparty Classification for Country Risk

·        Claim Sector Reclassification for Country Risk

·        Risk Sector Reclassification for Country Risk

·        Cross Border Claim Reclassification for Country Risk

·        Guarantee Amount Population for Country Risk

The additional transformations that are performed are:

·        Remaining Time to Maturity Band

·        Next Repricing Date Band

·        Regulatory Delinquency Band

See Business Metadata for details of these reclassifications.

Data Flow from Staging to Processing Area

The staging area of the FSDF serves as a container for analytical processing from sourcing to consumption. Such processing is usually delivered in the form of discrete units called analytical applications, spanning different analytical use cases ranging from Finance to Risk to Compliance.

These applications include custom-built computational engines and numerical libraries and can execute processes on the data that range from simple aggregations to complex, multi-step stochastic processes such as Monte-Carlo simulation.

Hence, analytical applications place varying demands on the data infrastructure in terms of volumes and speed and hence place different demands on the data architecture. In practice, the normalized (3NF) design favored for enterprise data warehouses often fails to be efficient or performant when it comes to analytical processing across a wide range of use cases.

Therefore, the OFSDF recognizes the need for distinct application-specific working stores, separate from the staging and reporting area. For example, the OFSAA Asset and Liability Management (ALM) application have a distinct set of ALM-specific tables, as does the Market Risk solution.

NOTE:   

The structure of these processing area stores is decided by the actual analytical application and engine used. The OFSAA Suite of applications is organized this way, with each application managing a specific set of tables or schemas within the processing area.

The processing area tables or schemas are not part of the OFSDF. This is because OFSDF is intended to be an open platform. Other analytical applications and engines can equally provision data out of OFSDF by mapping their input requirements appropriately to the OFSDF Staging Area Model.

Data Flow from Processing to Results Area

This step is similar to Data Flow from Staging to Results Area. It involves either pass through data from processing to results or loading directly to results. This is mostly due to processing measures such as Fair Value, Risk-Weighted Assets, and Averages as prescribed by the Regulator, and so on.

Computation of Offset and Netting Balances for Assets and Liabilities

The computation of Offset and Netting Balances for Assets and Liabilities are as follows:

·        Loan and Deposit Offset Computation: For the reporting of balances for Loans and Deposits, the offsetting of the Loan and the Hypothecated Deposit Balances are done.

§       If the Loan Balance is greater than the Hypothecated Balance, then the Net Balance is reported as Loan Balance.

§       If the Hypothecated Balance is greater than the Loan Balance, then the net balance is reported as Deposit Balance.

For an Offset Deposit Account associated with Multiple Loan Accounts, the Loan Accounts are ranked based on the balance with the lowest balance specified as the Top rank and which is first netted.

·        Asset Liability Netting using Netting Agreement: Asset and Liability Balances with depository institutions should be reported after netting the balances of accounts part of a netting agreement. Deposit Balances part of a netting agreement is netted, post the offsetting with loan balances if applicable.

·        Fiduciary Account Netting for Derivatives and Overdrafts: Overdrafts and Derivatives Contracts are netted as part of the Reporting of Fiduciary Contracts in the schedule RC-T. Fiduciary balances are netting against the Overdraft and Derivative Balances of the Party of the Parent Account of the Fiduciary Contracts.

Guidelines for Data Loading to Result Area Tables in Data Foundation for Regulatory Reporting Implementations

Regulatory reports make use of data available across several fact tables in the OFSAA data foundation model and these result tables are either loaded from the raw data sourced from source systems via out-of-the-box T2Ts or processed data output from various OFSAA applications.

For example, Fact LRM Account Summary (FCT_LRM_ACCOUNT_SUMMARY) which stores the liquidity risk-related attributes and metrics computed by OFSAA LRM application, Fact Loan Loss Forecasting and Provision Account Summary (FCT_LLFP_ACCOUNT_SUMMARY) which stores the attributes and measures computed by OFSAA LLFP application. However, there can be several implementations use cases in the regulatory reporting space where the customer cannot have licensed any of the OFSAA applications and hence must put additional custom effort to design an ETL process to load the required data elements into the respective fact tables referenced by the report. The following section highlights some of the guidelines that the customer can consider when designing a data flow for such a use case.

·        Consistent Usage of Run Identifier

Most of the fact tables used in regulatory reporting are run enabled and have a composite primary key inclusive of run identifier that enables the same snapshot of data to be loaded multiple times into the target fact table for any given Execution Date. All the out of the box processes that impact data used in regulatory reports are executed as part of an integrated run to ensure that run identifier is consistent across fact tables. Since the reporting is done on an integrated schema, the customs data flow design must keep this integrity intact. This essentially means that the custom ETL Processes designed to load the data directly into the fact tables must be able to leverage the run identifier generated by the run engine during execution. Run Identifier information is available in the DIM_RUN table.

·        Correct Dimensional Lookup Configuration

Dimensional identifiers are typically part of referential integrity constraints with the fact table so the custom ETL processes must ensure that lookups retrieve a valid surrogate key for a given value of the business key. The intermediate staging structure must ensure all the business keys are persisted correctly and the lookup condition is designed on the correct Dimension Table.

For example, FCT_LRM_ACCOUNT_SUMMARY.n_asset_level_skey refers DIM_ASSET_LEVEL.n_asset_level_skey. The business key (v_asset_level_code) must be sourced and persisted to ensure correct values are populated in the target column, that is, FCT_LRM_ACCOUNT_SUMMARY.n_asset_level_skey.

·        Data Loading Guidelines for handling Negative or Credit Balances

·        To handle Negative Balances in Regulatory Reporting, there are two primary sources of the negative balances:

a.     Natural asset negative balances from the system of records

b.     Adjustment entries or Plug entries.

The reporting requirement is to show the genuine asset negative balances as liabilities where adjustment entries should be aggregated to the same heading assets or liabilities as they are loaded. USFED uses the General Ledger type from the General Ledger Account dimension. Primarily following two General Ledger Type codes are used for this purpose.

a.     ASSET

b.     LIABILITY

General Ledger is available in every contract or product processor table as General Ledger code. Following products are considered for the treatment of negative balances:

a.     Loans and Cards

i.       Loans are reported under the Assets category in the Balance Sheet. There are cases when a customer makes an excess payment towards the loan account which makes the end of the period account balance becoming credit balance or negative balance.

ii.     When excess payment is made, then the account does not fall under the Asset category, but it becomes a liability for the financial institution and must be reported as non-interest-bearing demand deposits in respective line items.

iii.   To avoid reporting the excess payment as assets, you must assign a General Ledger code to the given account with V_GL_TYPE_CODE = 'LIAB'.

iv.   When for any loan regulatory reclassification assigned with GL code having V_GL_TYPE_CODE = 'LIAB', it excludes the reporting for all asset line items and is added to Liability in respective line items.

v.     Accounts created for Adjustment or Plug entries must have General Ledger code having V_GL_TYPE_CODE = 'AST'. This adds up to the same asset line item resulting in the addition or reduction of the overall reporting amount for a given line item based on the sign of the end of the period balance.

vi.   Accounts created for Adjustment or Plug entries for excess payments must have General Ledger code having V_GL_TYPE_CODE = 'LIAB'. This adds up to the same Liability line item resulting in the addition or reduction of the overall reporting amount for a given line item based on the sign of the end of the period balance.

Table 20: Data Loading to Result Area Tables

HC-C Line Item 6. a: Credit Cards

HC-E Line Item 1. a: Non-Interest-Bearing Balances

HC-H Line Item 1: Earning Assets

Impact of Negative Balances on Derivative GL Reconciliation Scenarios

Derivatives (Trading Assets / Trading Liabilities / All Other Assets / All Other Liabilities)

1.     Derivatives are not expected to have genuine negative notional amounts or end-of-period balances as in the case of loans or cards. The fair value of a derivative can be loaded as a Positive or Negative value as available.

2.     The application runs a rule called a Trading Account Type dimension which checks for GL code having V_GL_TYPE_CODE. If GL type is ASSET, it is shown under Trading Assets / All Other Assets. If GL type is 'LIAB', it is shown under Trading Liabilities or All Other Liabilities.

Currently, this feature is enabled for FR Y-11 / FR 2314 / FR 2052A Reports only. Other reports to uptake this feature in subsequent releases.

Table 21: Impact of Negative Balances on Derivative GL Reconciliation Scenarios

From the OFSAA technical infrastructure standpoint, the mentioned options are available to the customer to design and implement the custom ETL process explained above. OFSAA strongly recommends the following options to maintain consistency in terms of data lineage in the Metadata browser as the configured metadata can be made available in the meta-model through MDB publish:

1.     Data Integration Hub (DIH) Connectors

2.     Data Mapping (T2T) option in Application Infrastructure

3.     Data File Mapping (F2T) option in Application Infrastructure

Topics:

§       DIH Connectors 

§       Data Mapping (T2T) 

§       Data File Mapping (Flat File to RDBMS Target - F2T) 

DIH Connectors

If you have a licensed DIH to source the data from the external systems into OFSAA, a DIH connector is the recommended approach to load the data into results. The Source data could either reside in a relational structure or a file structure. The mappings maintained in DIH are logical and they abstract the physical references including the Dimensional lookups seamlessly without the need for any additional join or configuration.

See the Data Integration Hub (DIH) User Guide, for more information about loading the data into a result area table.

Figure 48: DIH Connectors

Data Mapping (T2T)

Data Mapping refers to the process of retrieving unstructured data from data sources for further data processing, storage, or migration. This feature is commonly known as the RDBMS source to RDBMS target (T2T) framework in the OFSAA world and can be leveraged when source data is available in the Oracle database. Dimensional lookups must be handled via the T2T’s join condition and expressions. See the OFSAAI User Guide for more details on configuring a T2T.

Data File Mapping (Flat File to RDBMS Target - F2T)

If the source data is available in file structures, the OFSAA F2T component can be used to bring the data into the OFSAA ecosystem. As lookups cannot be configured in an F2T, this component must be used in conjunction with the T2T component, that is, data is first loaded from the file to an interim staging structure using the F2T component followed by data load to the target result area table using the T2T component.  This is the least recommended approach as there is a need for interim table structure in the data model and involves multiple data hops that add to the overhead.

See the OFSAAI User Guide, for more details on configuring an F2T.

FSDF Entity Information

The FSDF entity information is given in the Dimension Tables and Data Elements documents available on the MOS page.

OFS Regulatory Reporting for US Federal Reserve - Dimension Tables <release version>

OFS Regulatory Reporting for US Federal Reserve - Data Elements <release version>

Fact Tables or Entities

For all tables with data flow type tagged as a Processing, it is recommended that you map data directly to the result area if the processing application is not part of the OFSAA product suite. For example, Basel computations, RWA Numbers, and Capital Ratio are taken from the processing area which is populated by OFSAA or other Basel applications.

For processed tables, you can look for the following options:

·        OFSAA Data Integration Hub (DIH) product

·        Flat File

·        Table-to-Table Transformation with the source being processing application

The list of processing output tables is available in the OFS Regulatory Reporting for US Federal Reserve - Data Elements <release version> document on the MOS page.

Inclusion of GL Recon Reconciled Accounts in Reporting

By default, the Regulatory Reporting expects reconciliation data in the staging area for all the reports. For OFS Data Management (OFSDM) pack (OFS General Ledger Reconciliation Application (GL Recon)) installed in the same Infodom as Regulatory Reporting is installed, the results area tables will have accounts with account numbers (having prefixes defined in REVELEUS_PARAMETER_MASTER.V_PARAM_VALUE column for the REVELEUS_PARAMETER_MASTER.V_PARAM_CODE = 'ADJUSTMENT_EXP_PREFIX' used in GL Recon application).

Report-specific treatment for such accounts is handled in the Regulatory Reporting application for cases like several accounts that must be reported.

For example FR Y-14Q Retail (A1 to A10) and FR Y-14M

Basel Processing to US FED Results Integration

This chapter provides information about Basel Processing to US FED Results Integration in the Oracle Financial Services Data Foundation application and step-by-step instructions to use this section.

This chapter includes the following topics:

·        Overview of Basel Processing to US FED Results Integration Tables

·        Overview of Basel Processing to US FED Results Integration

·        Executing the BASEL Processing to US FED Results Integration T2Ts

·        Checking the Execution Status

·        BASEL Processing to US FED Results Integration Results T2Ts

Overview of Basel Processing to US FED Results Integration Tables

As part of Basel processing to US FED results in integration, US FED tables are loaded from Basel Processing tables using Table to Table (T2T) component of Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) framework. Following are the Results Tables that store integrated results:

·        FCT_FORECAST_REG_CAP_SUMMARY

·        FCT_MITIGANT_REG_CAPITAL

·        FCT_MR_CAPITAL_SUMMARY

·        FCT_MR_VAR_PORTFOLIO_SUMMARY

·        FCT_MR_VAR_SUMMARY

·        FCT_REG_ACCT_MITIGANT_MAPPING

·        FCT_REG_CAP_PLCD_COLL_SUMMARY

·        FCT_REG_CAP_POOL_SUMMARY

·        FCT_REG_CP_CAPITAL_SUMMARY

·        FCT_REG_LE_CAPITAL_SUMMARY

·        FCT_REG_OR_CAPITAL_SUMMARY

·        FCT_REG_POOL_MITIGANT_MAP

·        FCT_REG_CAP_ACCOUNT_SUMMARY

As part of Basel processing results to US FED integration, US FED is packaging the aforementioned T2Ts. These are optional T2Ts that are deployed only when OFS_CAP_ADQ_PACK is installed.

Overview of Basel Processing to US FED Results Integration

Table-to-Table seeded definitions are provided for loading data into the target tables:

Table 22: Table to Table Seeded Definitions

Executing the BASEL Processing to US FED Results Integration T2Ts

For Basel - US FED integration, you must have US FED and Basel installed on the same INFODOM. Also, you must ensure that US FED and Basel are running the same version.

There are two ways to integrate Basel and US FED:

1.     Creating Integrated Run at Implementation Site: During implementation, you can merge the tasks of both BASEL and US FED and create an integrated Run to execute each time. The processes inside Run should be ordered as Basel first, then US FED, and finally the Basel - US FED Integration process. In this Run, the Basel processing area and the US FED results in area tables must have the same Run SKEY across all tables.

For BASEL - US FED Integration Run, please use the US FED Run Management screen as the Request Report Flag, Override Report Flag, and Approve Report Flag options are not available in the Basel Run Management Screen to enable the Reporting Flag.

2.     Using approved Basel Run Execution ID in US FED Run: In this case, you can use the out-of-the-box Basel Run as-is for execution. After the execution, if the values are correct, you can execute the out-of-the-box US FED Run by selecting the required Basel Run SKEY from the Run Management screen. In this case, the Basel processing area has one RUN SKEY and for the same data, US FED has a different RUN SKEY in US FED results area tables, where the data is getting reported. Sample report generation is as follows:

a.     Log in to Oracle Financial Services Analytical Applications interface with your credentials.

b.     Navigate to Regulatory Reporting for US Federal Reserve, select Process Modelling Framework, and then select Process Modeller.

c.     Select a Run and click Execute Run.

d.     The Run Details and Run Execution Parameters window is displayed.

e.     Enter the Run Name and Run Execution Description. The Basel Run Execution Identifier and FIC MIS Date is auto-populated from the Basel Run report used.

f.       Click OK.

Resave Hierarchy HFSDF004 (US FED - Basel Run Execution Identifier for Run) after Basel execution for getting values in this Basel Run Execution Identifier.

Figure 49:  Basel Metadata Resave Screen

3.     Select only one Basel Run from the Available Hierarchies for the execution and click Save. The Run Management Summary window is displayed.

Checking the Execution Status

The status of execution can be monitored using the Batch Monitor screen.

For more comprehensive coverage of configuration and execution of a batch, see OFS Analytical  Applications Infrastructure User Guide.

The status messages in Batch Monitor are:

·        N - Not Started

·        O - On Going

·        F - Failure

·        S - Success

The execution log can be accessed on the application server in the following directory ftpshare/logs/<Run_Date>/FSDFINFO/LOAD DATA. The file name has the Batch Execution ID. Following are the error log tables in the Atomic Schema:

·        FCT_FORECAST_REG_CAP_SUMMARY$

·        FCT_MITIGANT_REG_CAPITAL$

·        FCT_MR_CAPITAL_SUMMARY$

·        FCT_MR_VAR_PORTFOLIO_SUMMARY$

·        FCT_MR_VAR_SUMMARY$

·        FCT_REG_ACCT_MITIGANT_MAPPING$

·        FCT_REG_CAP_PLCD_COLL_SUMMARY$

·        FCT_REG_CAP_POOL_SUMMARY$

·        FCT_REG_CP_CAPITAL_SUMMARY$

·        FCT_REG_LE_CAPITAL_SUMMARY$

·        FCT_REG_OR_CAPITAL_SUMMARY$

·        FCT_REG_POOL_MITIGANT_MAP$

·        FCT_REG_CAP_ACCOUNT_SUMMARY$

BASEL Processing to US FED Results Integration Results T2Ts

T2T definitions can be retrieved as an excel document for reference from the metadata browser of the Unified Metadata Manager (UMM) component of OFSAAI.

LLFP Processing to US FED Results Integration

This chapter provides information about US FED Processing to US FED Results Integration in the Oracle Financial Services Data Foundation Application and step-by-step instructions to use this section.

This chapter includes the following topics:

·        Overview of LLFP Processing to US FED Results Integration Tables

·        Overview of LLFP Processing to US FED Results Integration

·        Executing the LLFP Processing to US FED Results Integration T2Ts

·        Checking the Execution Status

·        LLFP Processing to US FED Results Integration Results T2Ts

Overview of LLFP Processing to US FED Results Integration Tables

As part of LLFP processing to FSDF results integration, US FED Tables are loaded from LLFP Processing Tables using Table to Table (T2T) component of Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) Framework. Following are the Results Tables that store integrated results:

·        FCT_LLFP_ACCOUNT_SUMMARY

As part of LLFP processing results to FSDF integration, FSDF is packaging the aforementioned T2Ts. These are optional T2Ts that are deployed only when OFS_IFRS_PACK is installed.

Overview of LLFP Processing to US FED Results Integration

Table-to-Table seeded definitions are provided for loading data into the Target Tables.

Table 23: Table to Table Seeded Definitions

Executing the LLFP Processing to US FED Results Integration T2Ts

For LLFP - US FED integration, you must have US FED and LLFP installed on the same INFODOM. There are two ways to integrate LLFP and US FED:

1.     Creating Integrated Run at Implementation Site: During implementation, you can merge the tasks of both LLFP and US FED and create an integrated Run to execute each time. The processes inside Run should be ordered as LLFP first, then US FED, and finally the LLFP - US FED Integration process. In this Run, the LLFP processing area and the FSDF results area tables must have the same Run SKEY across all tables.

For LLFP - US FED Integration Run, please use the FSDF Run Management screen as the Request Report Flag, Override Report Flag, and Approve Report Flag options are not available in the LLFP Run Management Screen to enable the Reporting Flag.

2.     Using approved LLFP Run Execution ID in US FED Run: In this case, you can use the out-of-the-box LLFP Run as-is for execution. After the execution, if the values are correct, you can execute the out-of-the-box US FED Run by selecting the required LLFP Run SKEY from the Run Management screen. In this case, the LLFP processing area has one RUN SKEY and for the same data, US FED has a different RUN SKEY in US FED results area tables, where the data is getting reported. Sample report generation is as follows:

a.     Log in to Oracle Financial Services Analytical Applications Interface with your credentials.

b.     Navigate to Regulatory Reporting for US Federal Reserve, select Process Modelling Framework, and then select Process Modeller.

c.     Select a Run and click Execute Run.

d.     The Run Details and Run Execution Parameters Window is displayed.

e.     Enter the Run Name and Run Execution Description. The LLFP Run Execution Identifier and FIC MIS Date is auto-populated from the LLFP Run Report used.

f.       Click Ok.

Resave Hierarchy HFSDF007 (US FED - LLFP Run Execution Identifier for Run) after LLFP execution for getting values in this LLFP Run Execution Identifier.

Table 24: Metadata Resave Hierarchy for USFED-LLFP Run Execution Page

3.     Select only one LLFP Run from the Available Hierarchies for the execution and click Save. The Run Management Summary window is displayed.

Checking the Execution Status

The status of execution can be monitored using the Batch Monitor screen.

For more comprehensive coverage of configuration and execution of a batch, see OFS Analytical  Applications Infrastructure User Guide.

The status messages in Batch Monitor are:

·        N - Not Started

·        O - On Going

·        F - Failure

·        S - Success

The execution log can be accessed on the Application Server in the following directory ftpshare/logs/<Run_Date>/FSDFINFO/LOAD DATA. The file name has the Batch Execution ID. Following is the error log table in the Atomic Schema:

·        FCT_LLFP_ACCOUNT_SUMMARY$

LLFP Processing to US FED Results Integration Results T2Ts

T2T definitions can be retrieved as an Excel document for reference from the Metadata Browser of the Unified Metadata Manager (UMM) component of OFSAAI.

LRM Processing to US FED Results Integration

This section provides information about LRM Processing to US FED Results Integration in the Oracle Financial Services Data Foundation application and step-by-step instructions to use this section.

Topics:

·        Overview of LRM Processing to US FED Results Integration Tables

·        Overview of LRM Processing to US FED Results Integration

·        Executing the LRM Processing to US FED Results Integration T2Ts

·        Checking the Execution Status

·        LRM Processing to US FED Results Integration Results T2Ts

Overview of LRM Processing to US FED Results Integration Tables

As part of LRM processing to US FED results in integration, US FED tables are loaded from LRM Processing tables using Table to Table (T2T) component of Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) framework. Following are the Results Tables that store integrated results:

·        FCT_LRM_ACCOUNT_SUMMARY

As part of LRM processing results to US FED integration, US FED is packaging the aforementioned T2Ts. These are optional T2Ts that are deployed only when OFS_TR_PACK is installed.

Overview of LRM Processing to US FED Results Integration

Table-to-Table seeded definitions are provided for loading data into the target tables.

Table 25: Table to Table Seeded Definitions

Executing the LRM Processing to US FED Results Integration T2Ts

For LRM - US FED integration, you must have US FED and LRM installed on the same INFODOM. There are two ways to integrate LRM and US FED:

1.     Creating Integrated Run at Implementation Site: During implementation, you can merge the tasks of both LRM and US FED and create an integrated Run to execute each time. The processes inside Run should be ordered as LRM first, then US FED, and finally the LRM - US FED Integration process. In this Run, the LRM processing area and the FSDF results in area tables must have the same Run SKEY across all tables.

For LRM - US FED Integration Run, please use the US FED Run Management screen as the Request Report Flag, Override Report Flag, and Approve Report Flag options are not available in the LRM Run Management Screen to enable the Reporting Flag.

2.     Using approved LRM Run Execution ID in US FED Run: In this case, you can use the out-of-the-box LRM Run as-is for execution. After the execution, if the values are correct, you can execute the out-of-the-box US FED Run by selecting the required LRM Run SKEY from the Run Management screen. In this case, the LRM processing area has one RUN SKEY and for the same data, US FED has a different RUN SKEY in FSDF results area tables, where the data is getting reported. Sample report generation is as follows:

a.     Log in to Oracle Financial Services Analytical Applications interface with your credentials.

b.     Navigate to Regulatory Reporting for US Federal Reserve, select Process Modelling Framework, and then select Process Modeller.

c.     Select a Run and click Execute Run.

d.     The Run Details and Run Execution Parameters window is displayed.

e.     Enter the Run Name and Run Execution Description. The LRM Run Execution Identifier and FIC MIS Date is auto-populated from the LRM Run report used.

f.       Click Ok.

Resave Hierarchy HFSDF006 (US FED - LRM Run Execution Identifier for Run) after LRM execution for getting values in this LRM Run Execution Identifier.

3.     Select only one LRM Run from the Available Hierarchies for the execution and click Save. The Run Management Summary window is displayed.

Checking the Execution Status

The status of execution can be monitored using the Batch Monitor screen.

For more comprehensive coverage of configuration and execution of a batch, see OFS Analytical  Applications Infrastructure User Guide.

The status messages in Batch Monitor are:

·        N - Not Started

·        O - On Going

·        F - Failure

·        S - Success

The execution log can be accessed on the application server in the following directory ftpshare/logs/<Run_Date>/FSDFINFO/LOAD DATA. The file name has the Batch Execution ID. Following is the error log table in the Atomic Schema:

·        FCT_LRM_ACCOUNT_SUMMARY$

LRM Processing to US FED Results Integration Results T2Ts

T2T definitions can be retrieved as an excel document for reference from the Metadata Browser of the Unified Metadata Manager (UMM) component of OFSAAI.

Overview of OFS REG REP User Interface

This section provides details to log in to the OFSAA Application, View Report Summary, View Schedule Summary, View Cells, and Map Data Schedules. It includes:

·        Logging in to OFS REG REP UI

·        Viewing Report Summary

·        Viewing Schedule Summary 

·        Viewing Data Elements

·        Viewing Cell Summary

Logging in to OFS REG REP UI

After the applications are installed and configured, to access the OFS REG REP UI you must log in to the OFSAAI Environment using the OFSAAI Login Page.

NOTE:   

The built-in security system ensures that you are permitted to access the window and actions based on the authorization only.

To access the OFS REG REP UI, follow these steps:

1.     Enter the OFSAAI URL in your browser. The OFSAAI Login Page is displayed.

Figure 50: OFSAAI Log In

2.     Select the desired language from the Language drop-down list.

3.     Enter your User ID and Password. When you log into OFSAAI, the OFSAA Applications page is displayed.

Figure 51: OFSAA Applications Screen

4.     Select Financial Services Data Foundation. The FSDF landing page is displayed.

Figure 52: Financial Services Data Foundation Landing Page

5.     Or select the Regulatory Reporting for US Federal Reserve. The Regulatory Reporting for Reporting for US Federal Reserve landing page is displayed.

Figure 53: Regulatory Reporting for US Federal Reserve Page

Viewing Report Summary

The Report Summary data comes pre-seeded based on the applications that are installed. The Report Summary enables you to view all the configured reports for the jurisdiction.

After logging into the OFS REG REP UI, navigate to Regulatory Reporting Metadata and select Reports to view the Reports Summary window.

Figure 54: Report Summary Screen

NOTE:   

You can view the summary of all the configured reports in the Tile view  or List view.

The Search Bar helps you to find the required information from the database. You can enter the nearest matching keywords to search and filter the results by entering information in the search box. You can search for a Report using either the name or description.

Figure 55: Report Summary Search Bar

The Paging option at the bottom right corner allows you to see more reports than the ones currently displayed on the window.

Figure 56: Report Summary Paging Option

Report Information

Each tile or list on the Report Summary window corresponds to one report. For each report, you can view the report code, report description, number of schedules within the report, the number of configured non-derived cells, and count of utilized derived entities.

For example, the CRSA report in the tile or list view is displayed as follows:

Figure 57: Report in Tile View

Figure 58: Report in List View

Select the Report Code to navigate to the Schedule Summary window.

Figure 59: Report Information

Viewing Schedule Summary

The Schedule Summary window provides the component schedules for the corresponding report. Select the Report Code in the Report Summary window to navigate to the Schedule Summary window (as shown in Figure 66).

For example, the Schedule Summary window for the FR Y-9C report is displayed as follows.

Figure 60: Schedule Summary Screen

NOTE:   

You can view the summary of all the configured reports in the Tile view  or List view.

The Search Bar helps you to find the required information from the database. You can enter the nearest matching keywords to search and filter the results by entering information in the search box. You can search for a Schedule using either the name or description.

The Paging option (Figure 61) at the bottom right corner allows you to see more reports than the ones currently displayed on the window.

NOTE:   

Select the  icon on the top right corner to return to the Report Summary window.

Schedule Information

Each tile or list on the Schedule Summary window corresponds to one schedule under the report. For each schedule, you can view the schedule code and the description, the number of configured non-derived cells for the schedule, and the count of utilized derived entities.

For example, the Schedule Page3 tile is displayed as follows. Select the Schedule Code to navigate to the Cell Information window.

Figure 61: Schedule Information

Viewing Data Elements

Each tile or list on the Report Summary window corresponds to one report. For each report, you can view the report code, report description, number of schedules within the report, the number of configured non-derived cells, and count of utilized derived entities.

Figure 62: Report Information

1.     Click the chain icon on the right top corner to display the data elements for the respective item. The data elements view option is available at the report schedule and cell level.

Figure 63: Data Elements Screen

2.     Select Filter to apply filters on the selected data. The filter pane allows filtering data on specific columns.

Figure 64: Filters

3.     Select Apply Filter to apply the required filters on the selected data.

4.     Select Clear Filter to clear the applied filters and display all records for the component.

5.     Select Export to CSV to export the data displayed in the window.

Viewing Cell Summary

The Cell Summary window provides the non-derived cells or MDRMs configured as a part of the solution for the corresponding schedule under a report. Select the Schedule Code in the Schedule Summary window to navigate to the Cell Summary window (as shown in Figure 65).

For example, the Cells for Schedule Page3 summary window under the CRSA report is displayed as follows.

Figure 65: Cell Summary Window

NOTE:   

You can view the summary of all the configured reports in the Tile view  or List view.

The Search Bar helps you to find the required information from the database. You can enter the nearest matching keywords to search and filter the results by entering information in the search box. You can search for a Cell using either the name or description.

The Paging option (Figure 65) at the bottom right corner allows you to see more reports than the ones currently displayed on the window.

NOTE:   

Select the  icon on the top right corner to return to the Report Summary window.

Topics:

·        Cell Information 

·        Derived Entity 

·        Measure 

·        Filters 

Cell Information

Each tile or list on the Cell Summary window corresponds to one cell or MDRM under the schedule. For each cell, you can view the MDRM name, count of utilized derived entities, count of utilized OFSAA hierarchies, and measures for that cell.

For example, the cell CRSAR040C180 tile is displayed as follows. Select the cell or MDRM Code to navigate to the Cell Information window.

Figure 66: Cell Information

The Cell Information window is displayed as follows.

Figure 67: Cell Information Window

Each section in the Cell Information window displays the relevant OFSAA Metadata and filters used for the cell.

Derived Entity

This displays the name of the OFSAA Materialized View or View that contributes to the Cell.

Measure

This displays the name of the OFSAA Measure that is reported for the particular Cell.

Filters

The Filter conditions are as follows:

1.     All filters that are applied to the cell are displayed under the filter section. It displays all the applied filters as their OFSAA description.

2.     On selection, the filter is marked by a  sign on the top left corner of the selected filter.

3.     The section that follows displays the entity or table on top of which the filter is based and the OFSAA Level Description for the selected filter.

4.     All filter values that apply to the particular MDRM are available as a ribbon. Each filter value is in a separate box.

For example, in the previous case for MDRM CRSAR040C180, the applied filters are Consolidation Code and Reporting Line Code. Currently, the Consolidation Code filter is selected and the required filter values for the same are 100.

In the case of multiple values, the filters are displayed as follows with an arrow mark.

Figure 68: Multiple Filter Values

The filters in case of not in condition are highlighted in red are displayed as follows.

Figure 69: Not in Condition Filters

Data Schedule Mapping

Data Schedule-based reports utilize wrapper views to report data. For Adjustments & for addition on newer granularity not provided by OFSAA solutions for data schedule-based reports, this feature allows mapping new derived entity columns to the corresponding wrapper view columns. The topics in this section are taken as an example and organized as follows:

·        Prerequisites

·        Navigating to Mapping Window

·        Mapping Window

·        Adding Derived Entity 

·        Mapping Procedure

·        Saving Mapping Configuration

Prerequisites

The prerequisites for Data Schedule Mapping are as follows:

·        All Derived Entities and the Wrapper Views should be resaved through resave batch pages and by the execution of scripts packaged as Postscripts with installer respectively.

·        Execute the batch <INFODOM>_DS_POP_UNION_METADATA_USFED available in the batch execution page post the step above.

Navigating to Mapping Window

Select the Navigation Menu  in the Regulatory Reporting for US FED home page to navigate to the Report Summary window. Navigate to the data schedule-based report for which mappings are to be done.

For example: to map schedules under the FR Y-14 report, select the FRY-14Q report.

Figure 70: Report Information

Select the report code (Figure 71) to navigate to the schedules. All schedules under the report are available in this window.

Figure 71: Schedules Information

Schedules for which mapping feature is available can be clearly distinguished by the Edit  icon available in the schedule tile. Schedules for which the feature is not available do not have the edit icon present in the corresponding tile.

Select Edit  to navigate to the mapping window.

Mapping Window

The Mapping window displays the wrapper view utilized for the data schedule and the contributing OFSAA derived entities to the wrapper view. The window also displays the line items of the data schedule-based report along with the internal derived entity columns mapped to it.

Figure 72: Mapping Window

Mapping Window Components

The Mapping window components are as follows.

·        Schedule Name

The Schedule Name is displayed on the top left corner of the window.

·        Wrapper View

The Wrapper view utilized for the schedule is mentioned with square brackets in the top pane along with the schedule name.

·        Contributing Derived Entities

The left section of the report lists down the OFSAA derived Entities that contribute to the Wrapper View. The list contains derived entities that are by default provided by the OFSAA solution and the ones added by the user.

Figure 73: Derived Entities

·        Mapping Table

The mapping table shows all contributing components to the line item of the data schedule. The columns of the mapping table are in Table 26.

Table 26: Mapping Table Components

Adding Derived Entity

To add a new derived entity, follow these steps:

1.     Select Add Derived Entity.

Figure 74: Add Derived Entity

2.     This lists the available Derived Entities that are present in the current Infodom. The Derived Entities can be searched by either code or name in the search box.

Figure 75: Derived Entities List

3.     Select the desired Derived Entity that must be added for adjustments and click Add.

Figure 76: Selected Derived Entity

NOTE:   

The same Derived Entity cannot be added twice for Data Schedule Mapping.

4.     On adding the new Derived Entity, the Mapping Window is displayed as follows.

Figure 77: Mapping Window with New Derived Entity

5.     Derived entities added through the above method can be distinguished from OFSAA based derived entities through a Remove   present at the end of the derived entity tab. This mark enables the removal of the derived entity. Derived Entities that are from the OFSAA provided granularities do not have the Remove  mark and thus mapping for such derived entities cannot be removed or modified from this window.

Mapping Procedure

The Mapping Window for any added derived entity allows mapping columns of the derived entity to the line item of the Data Schedule.

For example, the mapping window for Derived Entity DE - Securities AFS OCI by Portfolio [DEREG008] is displayed as follows.

Figure 78: Data Schedule Mapping Window

The mapping of the line item to the derived entity column can be modified by double-clicking on the respective row in the Metadata column of the Mapping Table.

When the row is clicked, all the columns of the Derived Entity are listed and can be selected to map that to the corresponding line item listed under the Column Name Column of the table. If no mapping is required, then select the No Mapping Needed option.

Example for Derived Entity DE - Securities AFS OCI by Portfolio [DEREG008] is displayed as follows.

Figure 79: Metadata Mapping Window

NOTE:   

Ensure that the data type of the selected metadata matches the Column Data Type.

Saving Mapping Configuration

After the mapping is complete as described in earlier sections, select Save Configuration at the bottom of the window to save the configuration. The following message is displayed after the configuration is saved.

Figure 80: Saving Mapping Configuration Page

Click Close.

Adjustment Feature for Template-based Reports

The adjustments feature is a new enhancement to adjust the differing values of the report systems. The Adjustments Derived Entity derives its values from the Adjustments Fact table (FCT_REG_REPORT_ADJUSTMENTS) that specifies the adjustment value and the seeded table (DIM_REG_REPORT_CELL) that specifies the Cell ID or MDRM Code and the Report Code to which the MDRM belongs. This ensures that there can be direct adjustments made to MDRM(s) such that the values from both the derived entities are traceable and efficiently reported.

Topics:

·        Implementing the Adjustment Feature

Implementing the Adjustment Feature

To implement the Adjustment Feature, identify the Cell ID for the report and the line item where adjustment must be implemented.

For example FRY-9C Report

Schedule: HC-C

Line Item: 1.b Loans Secured by Real Estate / Secured by farmland

Cell ID: BHDM1420

NOTE:   

The Adjustment feature works only for fixed table cells (Open Y cells are not supported).

The report currently displays a Total value = 12,490,492 for the identified cell as shown in the following figure.

Figure 81: Adjustment Feature

Now, the requirement is to adjust this amount to 12,500,492,000.00

Topics:

·        Populating Base Tables

·        Refreshing Adjustment Derived Entity

·        Lombard Verification 

Populating Base Tables

FCT_REG_REPORT_ADJUSTMENTS: This table must be populated with the requisite Adjustment Amount and other related columns.

For example:

N_ADJUSTED_AMT = 10000000

The corresponding N_CELL_SKEY value must be picked from DIM_REG_REPORT_CELL for the respective CELL_ID. The DIM_REG_REPORT_CELL table is pre-seeded with cell IDs for reports supported for this feature.

The following columns must also be updated accordingly:

1.     N_ENTITY_SKEY

2.     N_RUN_SKEY

3.     N_MIS_DATE_SKEY

Refreshing Adjustment Derived Entity

Execute the resave batch for Adjustments (<<INFODOM>>_REG_ADJUSTMENT_RESAVE), to save the Adjustment derived entity - DEADJ001.

This ensures that the adjustment amount is reflected in the adjustment-derived entity DEADJ001.

Lombard Verification

Post adjustments, the retrieved report should reflect the amount that is coming from the sourced systems and the adjusted amount.

The retrieved report should reflect the amount after adjustments as shown in the following figure.

 (12,490,492,000.00 +10000000) = 12,500,492,000.00

Figure 82: Lombard Adjustment Verification

NOTE:   

The Adjustment amount can be negative to achieve a subtracted amount.

Direct Upload for Data Schedules

This product feature allows line items for data schedule-based reports to be directly mapped to data sourced from various systems that are not captured through OFSAA regular granularities (for example, Portfolio granularity). The Direct Upload option involves using wrapper views and shadow-derived entities for managing data from regular granularities and non-OFSAA granularities to be exposed together to the Lombard Agile Reporter.

Figure 83: Data Schedules Direct Upload

Topics:

·        Setting up Shadow Derived Entity

·        Defining Shadow Derived Entity

·        Mapping Data Schedule

·        Executing View Creation Batch

Setting up Shadow Derived Entity

The initial step to enable a data schedule involves setting up a shadow-derived entity that holds data from sources that are not provided by OFSAA regular granularities.

Defining Shadow Derived Entity

The shadow-derived entity and all the underlying objects which include the Datasets, Hierarchies, Measures, and Business Processors must be defined from the OFSAA UI page under the Financial Services Data Foundation, select Unified Analytical Metadata, and then select Business Metadata.

See OFS Analytical Applications Infrastructure User Guide for more details.

NOTE:   

For populating the shadow derived entity cases where a new table is introduced which are not already a part of the OFSAA data model, ensure that the following conditions are met:

1.      The primary key of the shadow table is the same as the granularity of the data required for the data schedule.

2.      Data is expected to be mutually exclusive between OFSAA results and the shadow table.

3.      Customer to load data into shadow tables through ETL or DIH.

4.      Run Identifier, MIS Date, and Entity Identifier must be mandatory attributes and part of the primary key.

5.      This table can be created by extending the OFSAA data model followed by executing the source model generation to enable table visibility in the OFSAA framework.

Mapping Data Schedule

Mapping of the shadow-derived entity to the line items can be achieved by using the user interface described in Section 4.6: Data Schedule Mapping.

Executing View Creation Batch

Post mapping columns for direct upload through the steps mentioned in the previous section, the view must be recreated in the database to reflect the shadow-derived entity as a part of its definition.

This can be achieved by executing <<##INFODOM##_DS_RESAVE_UNION_VIEW_USFED>> batch from the batch execution page to save the view definition.

The resave batch is a sample batch for view resaves which can be utilized for the concerned view by replacing the sample view name with the desired view name under the batch maintenance page. After the changes are saved, the batch can be executed from the batch execution page.

This should modify the view definition to include the new shadow-derived entity given all metadata mapped through the page has the same data type as the parent metadata.

NOTE:   

If the metadata type required for the line item and as identified by the wrapper view does not match that of the shadow-derived entity, the view recreation fails. The errors are logged in the ERR_LOG_UNION_VIEW_PARSER table in the atomic schema.

Verifying the Configuration

After the batch is successfully executed, use any SQL tool to verify that the view is dependent on the derived entity added to the configuration. This can be verified from the USER_DEPENDENCIES table by using the following query.

Select REFERENCED_NAME from User Dependencies Where NAME='<<VIEWNAME>>'

where the VIEWNAME specifies the wrapper view for which mapping was done.

Data Schedule Migration

This section details the migration of Data Schedule mapping across environments.

Topics:

·        Prerequisites 

·        Assumptions 

·        Steps for Source Environment 

·        Steps for Destination Environment 

Prerequisites

The following tables must be backed up in the source and target environments before the migration is performed:

·        FSI_DS_CHILD_COL_MAP

·        FSI_DS_INT_CHILD_INFO

·        FSI_DS_SEEDED_VW_INFO

·        FSI_DS_VW_CHILD_MAP

·        FSI_DS_VW_COL_INFO

·        FSI_DS_VW_COL_MAP

User-defined Derived Entity (Entities) created for data schedule mapping must be migrated through the Object Migration feature of OFSAA (OFS Advanced Analytical Applications Infrastructure Application Pack 8.0.8.0.0 User Guide).

Assumptions

The assumptions considered before the migration is performed are as follows:

·        OFSAA objects (for example, determine derived entities) required for the data schedule mapping are present in the destination environment.

·        Migration overwrites already existing configuration in the destination schema with the one from the source schema.

·        The migration steps stated in the following sections for Data Schedule Mapping are performed for one view at a time.

Steps for Source Environment

Execute the following script files to migrate in the Source Environment:

1.     VW_FSI_DE_MIGRATION_UNION_DE.sql 

2.     FSI_DE_MIGRATION_UNION.sql 

3.     FSI_DE_MIGRATION_UNION_INSERT.sql (bypassing the union view name and jurisdiction code in the same sequence)

NOTE:   

Information for the parameters to be passed in the step above for a particular schedule and report can be obtained from FSI_DS_REPORT_VIEW_MAP.

4.     Generate insert scripts from the FSI_DE_MIGRATION_UNION table (say FSI_DE_MIGRATION_UNION_SOURCE_EXPORT.sql) in the source environment, which can be used to populate the same table in the destination environment.

Steps for Destination Environment

Execute the following script files to migrate in the Destination Environment:

1.     VW_FSI_DE_MIGRATION_UNION_DE.sql

2.     FSI_DE_MIGRATION_UNION.sql 

3.     FSI_DE_MIGRATION_UNION_SOURCE_EXPORT.sql (the insert script generated from the source schema)

4.     MIGRATION_POPULATION_TABLES.sql (by replacing parameters P_JURISDICTION and P_UNION_VIEW with the Jurisdiction Code and Union View Name respectively).

Mapping of Results to Reporting Requirements of Vermeg

Figure 84 explains the flow of data between OFSAA and AgileREPORTER.

Figure 84: Data Flow between OFSAA and AgileREPORTER

OFSAA provides the data to AgileREPORTER in the form of derived entities. The derived entity is an existing OFSAA higher-order metadata object and can be physicalized as a materialized view in the database. Derived entities store aggregated data from base fact entities specified in the dataset and have the necessary dimensions and measures.

Dimensional and measure combination stored within the derived entity is mapped to cells within the report. This mapping is maintained within the Dimensional Mapping template.

Decision Process within AgileREPORTER reads the derived entities and dimension mapping information to derive the data for reporting. Derived entities are created based on measures, hierarchies, and datasets.

Figure 85: Decision Process in AgileREPORTER

Some cells in the schedule can be derived as per the logic provided by the regulator. Derivation can be an expression built using values from other cells. Examples of derivation are ratio, node-level rollup, a direct reference to cells in other schedules within the report. These derivations are performed within the AgileREPORTER. OFSAA provides data only for the cells that are not derived.

NOTE:   

Metadata for data transformation is available as part of the data warehouse configuration pack provided ready-to-use or preconfigured from OFSAA. You need not perform any mapping for the reports. However, this information can be useful for maintenance or extensions when a ready-to-use pack is not available.

AgileREPORTER: Submission

The AgileREPORTER is a web-based regulatory reporting tool provided by Lombard Risk. It provides necessary features to address e-filing workflow, validation, and submission process and supports reports (called forms or returns) for various jurisdictions. AgileREPORTER provides a reliable and efficient infrastructure to compile, generate, and submit regulatory reports.