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 REG REP USFED Interface
· Organization of the Interface for User Roles
The OFS REG REP US FED 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 Release, see the Oracle Financial Services Regulatory Reporting for US Federal Reserve – Integration Pack Installation Guide Release 8.1.2.0.0.
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. So, 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 REG REP US FED 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 5: 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 6: 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 REG REP US FED 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 7: 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 8: 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 9: 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 10: 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 11: 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 12: Process Execution Summary Filter Search Result Pane
3. Scroll towards
the right and click View in the Process
Name row.
Figure 13: Process Execution Details Page
4. Select Request Report Flag to request a reported flag for the
selected run execution.
Figure 14: 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 15: 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 16: Approve Request Report Flag
3. Enter the information on the Approve Request Flag page.
Figure 17: 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 18: Override Request Report Flag
3. Enter the information in the Override Report Flag window.
Figure 19: Override Report Flag Details Window
4. Click Override to override the requested report flag.
Figure 20: Report Flag Pending for Approval
5. Click Approve Report Flag to approve the override report flag
request.
Figure 21: Figure 22: 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 23: Figure 24: 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 25: 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 26: Batch Monitor Screen
For more information on the list of RESAVE and REFRESH batches, see the MOS
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 Lombard Risk. You can view the relevant OFSAA Data on various schedules of the AgileREPORTER using this functionality.
Figure 27: Retrieve Returns Page
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 28: 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 29: AgileREPORTER Main Page
3. The schedule list is displayed on the left-hand side. Click any schedule name, for example, Schedule HC-E.
Figure 30: AgileREPORTER Page Displaying List of Schedules
4. Click any cell to drill-down.
Figure 31: AgileREPORTER Schedule Details Page
5. Figure 32 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 32: 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 33: AgileREPORTER Drill-Down Screen
7. Click the Column Selector button on the header of the second table.
Figure 34: 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 35: 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.