12 Validation or Edit Checks for Data Schedules

This chapter explains the validation or edit checks for various data schedules supported within the Regulatory Reporting application.

Overview of Edit Check Process

As per regulatory references, edit checks are used during regulatory report submission to verify and improve overall data quality and communicate key structural features of the collection. "DATA COLLECTED" for the Regulator is "DATA SUBMITTED" for a reporting entity.

For template reports, edit checks are exclusively handled in VERMEG (Lombard Risk) AgileREPORTER and are not covered in the OFSAA application.

Configuration Steps

Perform the following configurations to validate / edit check for the data schedules before the Edit Check execution:

Source Model Generation

1. After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Federal Reserve application.

Figure 207: Regulatory Reporting Us Federal Reserve page

Description of Regulatory Reporting Us Federal Reserve page follows This illustration shows the navigation to the Data Sources starting from Regulatory Reporting US Federal Reserve, then selecting Data Management Framework, then selecting Data Sources.

2. Navigate to Data Management Framework and select Data Sources. A new window is displayed as follows.

Figure 208: Data Sources page

Description of the Data Sources page follows This illustration shows the Data Sources Summary page.

3. In the Summary pane, select PROCESSING and click the Edit icon. A new edit pane is displayed.

Figure 209: Data Sources Edit page

Description of the Data Sources Edit page follows This illustration shows the list of Data Sources which can be edited by selecting a Data Source code and clicking the Edit button.

4. Select Catalog and enter the required details.

Figure 210: Generate Model Catalog Details page

Description of the Generate Model Catalog Details page follows This illustration shows the Data Sources Generate Model catalog fields such as Starts With, Contain, Contain and Ends With in the REG REP US FED application..

5. Click Save to complete the configuration.

SETUP_MASTER Table

The SETUP_MASTER table must be updated with the top-most parent entity for the Bank that is used for consolidation with the following SQL statement:

UPDATE SETUP_MASTER

SET V_COMPONENT_VALUE = <Topmost Parent Entity Code>

WHERE V_COMPONENT_CODE = '2052A_CONS_ENTITY_CODE';

Execution Steps

Perform the following batch run to complete the Edit Check execution:

FSDFINFO_USFED_EDIT_CHECK_FR_2052A batch.

How to Execute the Batches?

Perform the following steps to complete the Edit Check Batch execution:

1. Log in to OFSAA application GUI.

2. Navigate to Regulatory Reporting for US Federal Reserve, select Process and Operations, select Operations, and then select Batch Execution. The Batch Execution window is displayed as follows.

Figure 211: Batch Execution page

Description of the Batch Execution page follows This illustration shows the navigation to the Batch Execution page starting with Regulatory Reporting for US Federal Reserve, then selecting Process and Operations, then selecting Operations, and then selecting Batch Execution.

3. Enter the edit check name in Batch ID Like and click Search. The Batch ID is displayed in the Batch Details pane.

Figure 212: Batch Details page

Description of Batch Details page follows This illustration shows the batch details such as Batch ID and Batch Description.

4. Select the Batch ID, click the Date icon to choose the batch execution run date, and click Execute Batch.

Logs and Status

For Batch log, navigate to Regulatory Reporting for US Federal Reserve, select Process and Operations, select Operations, and then select Batch Monitor to check the status of the batch.

The Edit Check log is classified into two types:

1. Summary Table

The FSI_EDIT_CHECK_SUMMARY table stores the summary of the edit check executions for all the OFSAA implementations of edit checks. The summary table attributes and descriptions are as follows.

Table 45: FSI Edit Check Summary Table

Attribute Name	Attribute Description
V_BATCH_ID	This is the ID provided by the batch execution.
N_EDIT_CHECK_SKEY	This is the surrogate key (SKey) of the edit check from the FSI_EDIT_CHECK_MASTER table.
V_DQ_CHECK_ID	This is the ID from the DQ_CHECK_MASTER table populated for the Data Quality Check-based edit checks.
RUN_STATUS	The following are the values for RUN_STATUS: F – Failed E – Error I – Information W – Warning P – Pass Null – Data Quality makes no entry is for RUN_STATUS if there is no data being processed.
FAILED_ROWS	The number of rows for the RUN_STATUS.
FIC_MIS_DATE	Date of the Batch execution.
ENTITY	Data Transformation edit checks populate the individual entity names of the checks.

Edit Check does not make an entry for either ENTITY or DQ_CHECK_ID if it is aggregated validations performed across multiple FR-2052A report data schedules.

2. Detail Table

The following table shows the mapping for each Edit Check and its Details Table.

Table 46: Detail Table

Edit Check No.	Edit Check Description	Edit Check Type	Details Table
2	Internal Transactions Reported on Consolidated Reporting Entity	Data Quality	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER
3	Internal Transactions Reported Without Internal Counterparty	Data Quality	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER
4	Lendable Value in Excess of Market Value	Data Quality	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER
5	Third-Party Reporting Entity Exposures versus Consolidated	Data Transformation	FSI_EDIT_CHECK_5_LOG
6	Symmetry of Intercompany Transactions	Data Transformation	FSI_EDIT_CHECK_6_LOG
7	Large Haircuts on Secured Transactions	Data Quality	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER
9	Missing Required Products by Entity Type	Data Transformation	FSI_RUN_PROD_BY_ENT_TYP_LOG
10	Improper Intra-entity Consolidation	Data Quality	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER
12	Invalid or Missing Counterparty Field	Data Quality	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER
13	Missing or Not Applicable [Collateral Class] Field	Data Quality	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER
14	Large Other Product or Counterparty Balance	Data Transformation	FSI_EDIT_CHECK_SUMMARY
15	FRY-14MD2	Control Total Check	AAI_DQ_CTC_RESULT_DETAIL AAI_DQ_CTC_RESULT_SUMMARY FSI_EDIT_CHECK_SUMMARY
16	FRY-14MD2	Specific Check	DQ_RESULT_SUMM_MASTER DQ_RESULT_DETL_MASTER

The Data Transformation Details Tables with the attributes and descriptions are as follows.

3. FSI_EDIT_CHECK_5_LOG

This table stores the result of the comparison between aggregation of maturity value, collateral value, lendable value, and market value of the top-most parent entity with its child entities.

Table 47: FSI Edit Check 5 Log

Attribute Name	Attribute Description
D_FIC_MIS_DATE	FIC MIS DATE of the batch provided during execution
N_MATURITY_STATUS	Maturity status has two values: 0 – Maturity values of the parent not matching child entities 1 – Maturity Values of the parent matching child entities
N_COLLATERAL_STATUS	Collateral status has two values: 0 – the Collateral value of the parent not matching the child entities 1 – the Collateral value of parent matching the child entities
N_LENDABLE_STATUS	Lendable status has two values: 0 – Lendable value of the parent not matching the lendable value of the child entities 1 – Lendable values of the parent matching the lendable values of child entities
N_MARKET_STATUS	Market status has two values: 0 – Market value of the parent not matching child entities 1 – Market value of parent matching child entities
V_BATCH_ID	The batch ID of the batch being executed

4. FSI_EDIT_CHECK_6_LOG

This table stores the result of the comparison between the maturity outflow amount versus the maturity inflow amount.

Table 48: FSI Edit Check 6 Log

Attribute Name	Attribute Description
V_INTERNAL_COUNTERPARTY	Internal Counterpart value of the Inflow / Outflow
D_FIC_MIS_DATE	FIC MIS DATE of the batch provided during execution
N_ED_STATUS	ED status has two values: 0 – Maturity value sum of inflow not matching outflow 1 – Maturity value sum of inflow matching outflow
V_BATCH_ID	The batch ID of the batch being executed
V_REPORTING_ENTITY	Legal Entity Name / Internal Counterparty of the views

5. FSI_RUN_PROD_BY_ENT_TYP_LOG

This table stores the availability status of PIDs for the reporting entity’s entity type.

Table 49: PID Reporting Entity Type

Attribute Name	Attribute Description
RUN_SKEY	RUN SKEY is the run from the views
FIC_MIS_DATE	FIC MIS Date of the batch being executed
ENTITY_TYPE	Entity Type of the Reporting Entity
PID	PID of the record from view
STATUS_FLAG	Status values have two flags: 1 – PID is present for that entity type of Reporting Entity 0 – PID missing for that entity type of Reporting Entity
BATCH_ID	The batch ID of the batch being executed

The status of validation/edit checks are stored in the following SQL statement:

SELECT T1.FIC_MIS_DATE, T2.V_ED_CHK_ID, T2.V_ED_CHK_NAME, T2.V_ED_CHK_DESC,

NVL(T1.V_DQ_CHECK_ID, T1.ENTITY)

ENTITY, T1.FAILED_ROWS, T1.RUN_STATUS

FROM

FSI_EDIT_CHECK_SUMMARY T1,

FSI_EDIT_CHECK_MASTER T2

WHERE T1.N_EDIT_CHECK_SKEY = T2.N_EDIT_CHECK_SKEY

AND T1.V_BATCH_ID = <Batch ID>

FR 2052A Post-Submission Validation Checks

This section outlines the automated validation applied to each FR 2052A submission to verify and improve overall data quality and communicate key structural features of the collection. These checks represent the early foundation of a validation framework for the FR 2052A report and are refined and expanded upon as the collection progresses. OFS Regulatory Reporting performs the following checks either through Data Quality or Design.

Validation Check	Performed in: Regulatory Reporting / Lombard Risk AgileREPORTER / Processing	Approach: Design / Data Quality / Data Transformation
Internal Transactions Reported on Consolidated Reporting Entity	Regulatory Reporting	Data Quality
Internal Transactions Reported Without Internal Counterparty	Regulatory Reporting	Data Quality
Lendable Value in Excess of Market Value	Regulatory Reporting	Data Quality
Third-Party Reporting Entity Exposures versus Consolidated	Regulatory Reporting	Data Transformation
Symmetry of Intercompany Transactions	Regulatory Reporting	Data Transformation
Large Haircuts on Secured Transactions	Regulatory Reporting	Data Quality
Mismatched Currency Reporting	Regulatory Reporting	Design (this is handled as part of OFS Regulatory Reporting Model design)
Missing Required Products by Entity Type	Regulatory Reporting	Data Transformation
Improper Intra-entity Consolidation	Regulatory Reporting	Data Quality
Invalid or Missing Counterparty Field	Regulatory Reporting	Data Quality
Missing or Not Applicable (Collateral Class) Field	Regulatory Reporting	Data Quality
Large Other Product or Counterparty Balance	Regulatory Reporting	Data Transformation
Weekend Maturities (in respective source system)	Processing	–

FR Y-14MD2 Post-Submission Validation Checks

This section outlines the different checks performed for FR Y-14MD2 to verify and improve overall data quality. The total number of DQ checks available in the release for FR Y-14MD2 submission is 143. To get the count (Integrity Check), "Control Total Check" is configured, and for others "Specific Check". All checks are part of DQ GROUP "FRY_14M_D2_GROUP".

Table 50: Post Submission Validation Checks

Validation Check	Performed in: Regulatory Reporting / Lombard Risk AgileREPORTER / Processing	Approach: Design / Data Quality / Data Transformation
Count of portfolios with negative values	Regulatory Reporting	Data Quality
Count of portfolios less than/greater than the reference value	Regulatory Reporting	Data Quality
Count of portfolios not equal to the reference value	Regulatory Reporting	Data Quality
Field with Null Value	Regulatory Reporting	Data Quality
Field format N12.4.	Regulatory Reporting	Data Quality
Bank ID is not in the format N10.	Regulatory Reporting	Data Quality
Credit Card Type/Credit Card Lending Type has value other than 1-4	Regulatory Reporting	Data Quality
Period ID should be the last day of the reporting period of the data set. This field must be always a past date and must be different from Period Id in the previous month’s dataset.	Regulatory Reporting	Design (this is handled as part of OFSAA DQ check as we are passing mis_date while executing the DQ)