This chapter provides an understanding of the prerequisites, general, and data preparation assumptions, and logging into the application. It includes:
· Accessing the OFSDF Interface or OFS AGILE RP USFED Interface
· Organization of the Interface for User Roles
The OFS AGILE RP USFED application allows you to perform the following activities:
· Manage Data Loading and Transformation from various source systems to staging, processing, and results.
· Manage relevant OFSAA metadata for regulatory reporting purposes. This includes creating, modifying, and viewing the metadata used in reporting.
· View the report metadata for mapping.
· Drill down from AgileREPORTER to OFSAA results area.
For prerequisites and detailed instructions on installing this Interim Release, see the Oracle Financial Services Regulatory Reporting for US Federal Reserve Installation Guide.
OFSDF interface with Vermeg for US FED is a Reporting Application and it does not perform any risk or stress calculations. Following listed are the assumptions for the application:
· Textual and other related portions of reports like personal details, contact details, Yes / No choices must be updated on Report Portal directly and FSDF does not have a placeholder for it.
· Data provided is post reconciliation to ensure that the accuracy of data being reported (non-prescribed by regulators) is performed in OFSAA using various components – General Ledger (GL) reconciliation.
· Validity checks such as edit checks, cross-validation checks, and so on prescribed by the regulator are performed within the AgileREPORTER.
· All monetary amounts are expected to be positive in number, except valuation outputs which can be positive or negative. There are few exceptions like Excess Payments Scenarios in Loans/Cards where Balance loaded can be in Negative Signage. Rules are constructed assuming the negative sign of valuation amounts wherever applicable.
· The application populates a few specific Dimension Tables, known as seeded / sample tables as part of the Installation Script. Since they are used in the Metadata, changes in data values have an impact on overall functioning.
· All Percentage Data are expected in decimal format meaning 9% must be provided as 9 and not 0.09.
· For data provided As of Date, such as the last day of the quarter of the reporting year: Quarterly and Year to Date (YTD) report for the given date display the same value for those measures which are of as of the date in nature. For example, the Annual and Quarterly Balance Sheet and BASEL report generated as of 31-MAR show the same values for all measures such as Account Balance, where the reporting year is from April 01 to March 31.
· In FR-2052A, for PIDs I.O.9 and 0.0.22, there is no OOTB rule provided by OFSAA to identify these PIDs. The accounts which must be reported under PIDs are purely Reporter’s Discretion. Therefore, a Custom Rule can be built to report these PIDs.
· Reporting currency identification in FR-2052A must be done by populating setup_master table, in which V_COMPONENT_CODE = 'ENTITY_REPORTING_CD' that is defaulted to ‘N’, must be changed to ‘Y’ if the Reporting entity has greater than $700 billion in total consolidated assets and greater than $10 trillion in assets under custody.
· Data Load for FR Y-14M Report must include all the loans closed from the previous month.
· In FR-2052A, few Processing Dimension tables like DIM_ASSET_LEVEL, DIM_RESULT_BUCKET are used. These tables contain values other than the ones required by the Vermeg Field Structures template provided by Vermeg as they are consumed by the Processing Application, too. For example, the Seeded Script of DIM_ASSET_LEVEL has node values not to be considered for FR-2052A. Only the following values must be considered for FR-2052A Execution from the DIM_ASSET_LEVEL table.
Table 4: DIM_ASSET_LEVEL
V_ASSET_LEVEL_CODE |
|||
|---|---|---|---|
A-0 |
E-4 |
L-10 |
S-1-Q |
A-0-Q |
G-1 |
L-11 |
S-2 |
A-1 |
G-1-Q |
L-2 |
S-2-Q |
A-1-Q |
G-2 |
L-3 |
S-3 |
A-2 |
G-2-Q |
L-4 |
S-3-Q |
A-2-Q |
G-3 |
L-5 |
S-4 |
A-3 |
G-3-Q |
L-6 |
S-4-Q |
A-3-Q |
G-4 |
L-7 |
S-5 |
A-4 |
IG-1 |
L-8 |
S-5-Q |
A-4-Q |
IG-1-Q |
L-9 |
S-6 |
A-5 |
IG-2 |
N-1 |
S-6-Q |
A-5-Q |
IG-2-Q |
N-2 |
S-7 |
C-1 |
IG-3 |
N-3 |
S-7-Q |
E-1 |
IG-4 |
N-4 |
S-8 |
E-1-Q |
IG-5 |
N-5 |
Y-1 |
E-2 |
IG-6 |
N-6 |
Y-2 |
E-2-Q |
IG-7 |
N-7 |
Y-3 |
E-3 |
L-1 |
S-1 |
Z-1 |
· For FR-2052A for DIM_RESULT_BUCKET, values under v_bucket_type = ‘FRY2052A’ should be considered, the rest of the values can be ignored as they are consumed by the Processing Application.
· For FR-2900, Deposit Data is expected to be provided on a net or reciprocal basis in applicable cases as per regulatory instructions.
· For FR-2900, the regulatory template needs to update the CEN Code 1, 2, 3, or Blank for each branch. The definition is as follows:
§ The CEN Code identifies estimated deposit totals, consolidated offices, or locations that do not accept deposits. Complete this item only if applicable by entering 1 for estimated deposits, 2 for deposits consolidated with a different location (applicable for limited-service locations only), or 3 for a non-deposit accepting location. If you are reporting actual deposits for a location, the CEN Code should be left blank.
§ This CEN Code must be populated manually by the client as FSDF provides only accurate deposits. There is no mechanism to identify the use case of estimated deposits and hence CEN Code 1 must be entered manually. FSDF runs consolidation for an Entity and it does not identify a location for consolidation. Hence, consolidation with different locations must be updated manually. If deposits are available in FSDF, the location is expected to be deposit accepting. Hence, the non-deposit accepting location must be populated manually.
§ Adjustment Entries Expectation for FR-2900: FR-2900 Data Expectation for Account / GL granularity is daily. The reporting happens on Monday where the Derived Entity picks one week prior, that is, Tuesday of Last Week to current Monday (Reporting date). But the adjustment Entries for this report are expected to be populated only on Reporting Date (that is, Monday) for all the Cell IDs (MDRM Codes). Each Cell ID represents each Regulator Specific MDRM Code and Weekday (that is, MON, TUE, and so on).
· For FR Y15-B, an effective notional amount in respect of sold credit derivatives is expected to be populated in the FCT_NET_EXPOSURES table.
· Payment data is expected to be loaded as per the trade date or as per the settlement date basis if it remains consistent between periods.
· Data in the STG_CAP_INSTR_POSITIONS table is expected as incremental load and not as a complete snapshot at a point in time.
· For FR Y15- schedules about FBO, it is expected that the reporting entity will provide the data for the combined US operations of an FBO excluding section 2 (h) (2) of the Bank Holding Company Act.
In an integrated environment, where you have multiple applications installed on the same domain or infrastructure, OFSAAI allows you to see the other licensed applications through the UI.
To view the OFSAA product license details, follow these steps:
1. Login to the OFSAA application as a System Administrator.
Figure 2: OFSAA Administration Page
2. Select the Manage OFSAA Product Licenses option. The Manage OFSAA Application Pack Licenses page is displayed.
Figure 3: Manage OFSAA Application Pack Licenses Page
3. Under Installed Application Packs section, select the OFS_RRRS_PACK option.
Figure 4: Products In The Application Pack Window
You can now view the list of licensed products associated with RRRS pack.
After the application is installed and configured, to access the OFS AGILE RP USFED application you must log in to the OFSAAI environment using the OFSAAI login page.
To access the application, follow these steps:
1. Enter the OFSAAI URL in your browser. The OFSAAI login page is displayed.
Figure 2: 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 OFSAAI Applications page is displayed.
Figure 3: OFSAAI Applications Page
4. Select the Financial Services Data Foundation option to navigate to the FSDF application or select the Regulatory Reporting for US Federal Reserve to navigate to the OFS AGILE RP USFED application.
This section explains the various features used by an analyst. It describes the organization of the user interface and provides step-by-step instructions for navigating through the application to carry out these activities.
To access the Process Execution Summary, the following roles must be assigned to the user:
1. Modify Run Parameters
2. Approve Reporting Flag
3. Override Reporting Flag
4. Request Reporting Flag
5. Run Reporting Flag
6. View Run Details
Data Analysts are expected to perform the following activities:
1. Executing Batch to Refresh Derived Entities
2. Drill down from AgileREPORTER to OFSDF
Regulatory Report Analysts are expected to perform the following activities:
1. Drill down from AgileREPORTER to OFSDF
2. Using Metadata Browser to check schedule-wise metadata
3. Using Metadata Browser to check metadata usage across schedules
Topics:
· Reporting Flag for Run through Process Execution Summary
· Executing Batch to Resave Derived Entities
· Retrieving the Returns from AgileREPORTER
· Report Verification – Drill down from AgileREPORTER to OFSAA Results Area
This section provides information on the Runs that apply to USFED. The Process Execution Summary is launched after the Runs are executed from the Processing Modelling Framework.
Various applications provide the data for regulatory reporting. You must mark specific executions for regulatory reporting as the final run.
1. After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Federal Reserve, select Process and Operations, and then select Process Execution Summary.
Figure 4: Process Execution Summary Screen
2. Scroll towards the right and click Filter, select the Run Pipeline from the available pipeline selection list. Click OK.
Figure 5: Pipeline Selection Screen
3. After the Run execution, the Process Execution Summary is generated in the list format as illustrated in the following steps. The summary page displays the Process Names for which the Run Parameters are generated.
4. Scroll towards
the right and click View 
in the Process
Name row.
Figure 6: Process Execution Summary View Screen
You can view the detailed definition of a Run in a read-only mode. The Process Execution Details page displays the execution details for the selected Execution Key with the color band displaying the status of each Execution Key.
Figure 7: Process Execution Details Screen
The execution keys and the corresponding execution details are as follows:
· Process Description: The USFED Regulatory Run appears as the process description when the user executes the Regulatory Run.
· MIS Date: The extraction date is displayed in this field.
· Start Time: It displays the Execution Date and the Execution Time when the Execution Run starts.
· End Time: It displays the End Execution Date and Execution Time.
· Process Execution Key: Unique identifiers are assigned to each Process Execution.
· Approval Status: It displays the Approval status of the Execution as Completed, Failed, or Ongoing.
· Process Monitor: This helps to show the run definition as defined in the process modeling framework. There are four icons in the Process Monitor as follows:
§ PMF Launch: Click
View 
to view the
Process flow associated with the selected run.
§ Request
Report Flag: To request for a Reporting Run, select an Execution ID in
the Process Execution Summary page and click the Request Report Flag 
.
A dialog box appears for you to input your comments. Click Submit and
the status of this Run is displayed in the Reporting Flag section. Only
a successful execution can be requested for reporting. For the selected
Run and Execution date, there can be only one reporting flag.
§ Approve
Report Flag: After submitting the Reporting Run in the earlier section,
the Approve Report Flag 
is
enabled. When you click the Approve Report Flag, a dialog box is displayed
with User Comments and Approver Comments. The Approver can update the
comments in the Approver Comments field and then click Approve or Reject.
§ Override
Report Flag: Any reporting execution can be overwritten with another execution.
Select a successfully triggered batch on the Process Execution Summary
page. The Override Report Flag 
is
enabled if the execution is already marked as a Reporting Flag. You can
override the execution by updating your comments. This must be approved
by the approver and the procedure is similar to the procedure detailed
in the Approve Report Flag for a Run section.
To request, approve, and override a flag for the process execution, perform the following steps:
1. After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Federal Reserve, select Process and Operations, and then select Process Execution Summary.
Figure 8: Process Execution Summary Page
2. Scroll towards the right and click Filter, select the Run Pipeline from the available pipeline selection list. Click OK.
Figure 9: Process Execution Summary Filter Search Result Pane
3. Scroll towards
the right and click View in the
Process Name row.
Figure 10: Process Execution Details Page
4. Select Request
Report Flag to
request a reported flag for the selected run execution.
Figure 11: Request Report Flag Window
5. Enter information in the Comments field and click Submit. The request report flag for a run is saved successfully.
Figure 12: Request Report Flag Save Page
To approve the report flag, perform the following steps:
1. Navigate to the Process Execution Summary page and select the process name for which the report flag must be approved.
2. Click Approve to
approve the request.
Figure 13: Approve Request Report Flag
3. Enter the information on the Approve Request Flag page.
Figure 14: Approve Request Report Flag Window
4. Click Approve to approve the requested report flag.
To override the report flag for a successful run, perform the following steps:
1. Navigate to the Process Execution Summary page and select the process name for which the report flag must be overridden.
2. Click Override Report Flag to
override the report flag.
Figure 15: Override Request Report Flag
3. Enter the information in the Override Report Flag window.
Figure 16: Override Report Flag Details Window
4. Click Override to override the requested report flag.
Figure 17: Report Flag Pending for Approval
5. Click Approve Report Flag to
approve the override report flag request.
Figure 18: Figure 19: Approve Override Report Flag Window
6. Enter the information in the Approver Comments field and click Approve and the report flag is overridden successfully.
Figure 20: Figure 21: Overridden Report Flag
To execute the batch to resave derived entities, follow these steps:
1. Navigate to Operations, and then select Batch Execution.
2. Select the batch <<INFODOM>>_RESAVE_DE_<<REPORT NAME>> to resave all the reporting DEs used in that <<REPORT NAME>>.
Figure 22: Batch Maintenance Screen
3. Monitor the status of the batch using the Batch Monitor link (Navigate to Regulatory Reporting for US Federal Reserve, select Operations, and then select Batch Monitor.)
Figure 23: Batch Monitor Screen
For more information on the list of RESAVE and REFRESH batches, see the MOS
AgileREPORTER Application allows you to Create, View, Edit, Submit, and Export the regulatory returns. To enable this, you must set up the Calculation Engine and bind it to a regulator’s Configuration Package in the AgileREPORTER Application.
NOTE:
Ensure that a Data Warehouse Configuration Package is uploaded to the AgileREPORTER before setting up and binding.
This section allows you to add a Calculation Engine in the AgileREPORTER Application.
1. Log in to the AgileREPORTER.
Figure 24: AgileREPORTER Login Page
2. Enter the
login credentials and click Sign in.
The AgileREPORTER Main Page is displayed.
Figure 25: AgileREPORTER Main Page
3. Navigate to Settings, select Administration, and then select Calculation Engine.
Figure 26: Calculation Engine Selection Page
The Add Calculation Engine Page is displayed.
Figure 27: Add Calculation Engine Page
4. Click Add 
button.
The Calculation Engine Details
Page is displayed.
Figure 28: Calculation Engine Details Page
5. Select the Data Warehouse Option from the available list.
Figure 29: Data Warehouse Selection Page
6. Select the OFSAA USFED Option from the drop-down list and click Save. The Calculation Engine Added Page is displayed.
Figure 30: Calculation Engine Added page
NOTE:
Click the Edit 
icon,
to edit a Calculation Engine and click the Remove 
icon,
to remove a Calculation Engine from the AgileREPORTER Application.
This section allows you to bind a Calculation Engine to a regulator’s Configuration Package in the AgileREPORTER Application.
1. Log in to the AgileREPORTER.
Figure 31: AgileREPORTER Login Page
2. Enter the login credentials and click Sign in. The AgileREPORTER Main Page is displayed.
Figure 32: AgileREPORTER Main Page
3. Navigate to Settings, select Administration, and then select Config Package Binding.
Figure 33: Config Package Binding Selection Page
The Add Config Package Binding Page is displayed.
Figure 34: Add Config Package Binding Page
4. Click Add button.
The Config Package Binding Details
Page is displayed.
Figure 35: Config Package Binding Details Page
5. Select OFSAA USFED Calculation Engine from the drop-down list and click Save. The Config Package Binding Added Page is displayed.
Figure 36: Config Package Binding Added Page
The Retrieve Return functionality in AgileREPORTER fetches data from OFSAA derived entities and embeds them on AgileREPORTER templates. This runs the decision table process in Vermeg Reporting portal. You can view the relevant OFSAA data on various schedules of the AgileREPORTER using this functionality.
Figure 37: Retrieve Returns Page
This chapter provides an understanding of the maintenance process for the regulatory templates.
Changes to the regulatory template are one of the most common and continuous activities. The following steps help to assess the impact (You can replace the measure, dimension for existing data warehousing configuration pack using the following process):
1. Choosing different execution as a final. After report verification, if the requirement is to change the execution, then you must visit the Process Execution Summary section. After making these changes you must refresh Derived Entities. Then AgileREPORTER also needs to retrieve returns so that revised data is reflected on AgileREPORTER.
2. If Executing Batch to resave Derived Entities is not working, you can look for Batch Operation Log files. For file path, OFS Analytical Applications Infrastructure Installation and Configuration Guide.
3. To apply a revised patch, refer to the ReadMe file for instructions to be followed.
4. To update the revised data warehouse configuration pack, perform the following instructions.
a. Navigate to Settings, select Administration, and then select Data Warehouse Integration.
Figure 38: Data Warehouse Integration
b. Click Add to add a contextual button.
c. Enter details of the contextual button.
Name: The text needs to be displayed in the contextual button.
URL Pattern: Replace <<OFSAA_HOST>>, <<OFSAA_PORT>> and <<OFSAA_CONTEXT>> with host, port and web context of the environment where OFSAA is installed. Replace <<OFSAA_HOST>> with the name of information domain.
http://<<OFSAA_HOST>>:<<OFSAA_PORT>>/<<OFSAA_CONTEXT>>/ drilldown/USFED/${formCode}/${cellId}/${formVersion}/${referenceDate}/${run}/${entityCode}
Example:
i. Use http or https depending on the protocol configured for OFSAA.
ii. Select an icon.
d. Click Add to save the details.
Figure 39: Adding Contextual Button
5. After the data ware configuration pack is updated, the Lombard Configuration pack must reflect this.
NOTE: Note
See AgileREPORTER user documentation for details.
Drill-down functionality enables you to view the accounts included in the aggregation. Following these steps to drill down from AgileREPORTER to OFSAA:
1. Log in to the AgileREPORTER.
Figure 40: AgileREPORTER Login Page
2. You can view the list of reports on the main page. Click any report name in the Returns column, for example, FR Y-9C.
Figure 41: AgileREPORTER Main Page
3. The schedule list is displayed on the left-hand side. Click any schedule name, for example, Schedule HC-E.
Figure 42: AgileREPORTER Page Displaying List of Schedules
4. Click any cell to drill down.
Figure 43: AgileREPORTER Schedule Details Page
5. Figure 44 displays drill-down for the first cell in Column A. The OFSAA icon is displayed. It provides information about the amounts against different MDRM codes here. In the figure, the first MDRM code – BHCB 2210 indicates the amount of deposit held by the bank that is of a non-interest-bearing variant. Click the cell, and the OFSAA icon, to view how this cell was populated from OFSAA results. You are redirected to the OFSAA drill-down page.
Figure 44: AgileREPORTER Drill Down the page
This cell is populated from the derived entity (DE) mentioned in the grid header DE – Deposit Liabilities – Schedule HC-E. The value in the derived entity grid 136,880,013 must match that of the cell in the report. The derived entity is an aggregate built on top of the OFSAA results model to serve regulatory template requirements.
The DE is built using dimensions, measures, and business processors. The dimensions that participate in determining the cell value are displayed with data. Click the derived entity link in the grid header.
6. Click the OFSAA icon, to view how this cell was populated (provides information about the amounts reported in a cell) from OFSAA results. You are redirected to the OFSAA Drill down the page.
Figure 45: AgileREPORTER Drill-Down Screen
7. Click the Column Selector button on the header of the second table.
Figure 46: Drill Down Attribute Selector
NOTE:
Select the required Data Source, from the Available
Attributes list and click Move
. You can press the
Ctrl key and click Move for multiple
selections to map all the listed Data Sources to the application.
Select the required Data Source, from the Selected
Attributes list and click Remove
to remove the
mapped Data Source from the application.
8. Expand Dataset and select the Attribute to be shown in the Drill Down. Click Apply.
Figure 47: Drill Down Columns
For better drill down results, read the following hints:
1. Generic SQL Hints for the second drill-down:
The SQL hint configured by you in the table SETUP_MASTER is applied to the second drill-down query for all cell IDs. This hint must be generic and not specific to any table.
The hint returned from the output of this query is applied to the drill-down query:
select v_component_value from setup_master where v_component_code='DRILLDOWN_GENERIC_HINT'
For seeding v_component_value as ‘DEFAULT’, you can modify: v_component_code='DRILLDOWN_GENERIC_HINT'
For example:
These are some of the sample hints which the user can seed:
/*+PARALLEL(4)*/
/*+ALL_ROWS*/
/*+FIRST_ROWS(n)*/
2. Dataset specific SQL Hints for the second drill-down:
Additionally, you can also seed dataset-specific hints for the second drill down. v_component_code in the SETUP_MASTER table should be seeded using this naming convention: DRILLDOWN_<DATSET_CODE>_HINT
For example DRILLDOWN_DS1234_HINT
If both DRILLDOWN_GENERIC_HINT and DRILLDOWN_<DATSET_CODE>_HINT are seeded by the user, then the DRILLDOWN_<DATSET_CODE>_HINT takes precedence for that cell ID / Dataset combination.
3. You cannot drill down further for non-aggregate Derived Entities. For such DEs, a hyperlink for BP / Measure columns is unavailable in the first drill down.
It can check if DE is non-aggregate by firing the query below:
select v_element_value from metadata_element_master where v_metadata_Code = '<Derived Entity code>' and n_metadata_version = 0 and v_element_code ='AGGREGATIONREQUIRED'
If v_element_value = ‘N’, then the DE is non-aggregate.