Get documents about "RRT_Users_Guide_5.3P1
Shared by: yvtong
-
Stats
- views:
- 44
- posted:
- 4/29/2010
- language:
- English
- pages:
- 38
Document Sample
User's Guide
Fermat RRT 5.3 (5.3P1)
Last revised date : 06/06/2007
Contents
Chapter 01 Using Fermat Regulatory Reporting Tool.......................................................................................................4
Introduction ................................................................................................................................................................................5
A. Fermat RRT Purpose .........................................................................................................................................................5
B. Input Data ..........................................................................................................................................................................5
C. Document Version and Contents .......................................................................................................................................5
D. Main Concepts...................................................................................................................................................................5
1. Report .............................................................................................................................................................................5
2. Framework......................................................................................................................................................................6
3. Non-Aggregated vs Aggregated Data.............................................................................................................................6
4. Input Data vs Import Data ..............................................................................................................................................6
5. Adjustment Deal.............................................................................................................................................................6
I. Preparing the Process.............................................................................................................................................................7
A. Pre-Requisites: Ms Excel Version .....................................................................................................................................7
B. Importing the Setup ...........................................................................................................................................................7
C. Defining Obligor Grades and LGD Grades .......................................................................................................................7
II. Report Generation Process Overview....................................................................................................................................9
A. Section Purpose .................................................................................................................................................................9
B. Report Generation Process Overview ................................................................................................................................9
III. Importing Data from an External System ...........................................................................................................................11
A. Introduction .....................................................................................................................................................................11
B. Importing Non-Aggregated Data .....................................................................................................................................11
C. Importing Aggregated Data .............................................................................................................................................11
IV. Generating Reports.............................................................................................................................................................12
A. Introduction .....................................................................................................................................................................12
B. Updating Obligors Grades’ Scales and LGD Scales........................................................................................................12
C. Defining the Processed Data Scope .................................................................................................................................13
D. Generating Reports from the User Interface ....................................................................................................................13
1. Defining the Report(s) to Generate...............................................................................................................................13
2. Generating Reports .......................................................................................................................................................13
E. Generating Reports on a Task Server...............................................................................................................................14
1. Basic Process in Server Mode ......................................................................................................................................14
2. Advanced Process in Server Mode ...............................................................................................................................14
F. Generated Reports ............................................................................................................................................................15
1. Generated Reports’ Location........................................................................................................................................15
2. Generated Reports’ Name.............................................................................................................................................16
V. Validating Reports ...............................................................................................................................................................17
A. Audit ................................................................................................................................................................................17
1. Audit Step1...................................................................................................................................................................17
2. Audit Step2...................................................................................................................................................................18
B. Additional Information ....................................................................................................................................................21
C. Adjustment Deals.............................................................................................................................................................22
VI. Underlying Objects (for Advanced Users)..........................................................................................................................23
A. Section Purpose ...............................................................................................................................................................23
B. Underlying Input Data Model ..........................................................................................................................................23
1. Non-Aggregated Data...................................................................................................................................................23
2. Aggregated Data ...........................................................................................................................................................24
C. Underlying Tables and Views..........................................................................................................................................24
Chapter 02 Appendix 1: Detailed Import Data Model......................................................................................................26
Introduction ..............................................................................................................................................................................27
RRT_Users_Guide_5.3P1.doc 2/38
5.3P1#m
I. Credit Risk.............................................................................................................................................................................28
A. Rows Creation: General Rule ..........................................................................................................................................28
B. Rows Creation: Specific Cases ........................................................................................................................................29
1. Other Portion Types......................................................................................................................................................29
2. Additional Rows...........................................................................................................................................................29
C. Foreign Keys....................................................................................................................................................................30
II. Market Risk ..........................................................................................................................................................................31
A. MKR_IMPORT Table .....................................................................................................................................................31
1. General Rules for Data Import in MKR_IMPORT ......................................................................................................31
2. Primary Key..................................................................................................................................................................31
B. Focus on some Fields of MKR_IMPORT .......................................................................................................................31
1. Position_type = 6..........................................................................................................................................................31
2. Non Delta risk...............................................................................................................................................................32
3. Expression of Amounts ................................................................................................................................................32
4. Position_reference Field ...............................................................................................................................................32
C. MKR_IM_IMPORT Table ..............................................................................................................................................33
1. General Rules for Data Import in MKR_IM_IMPORT................................................................................................33
2. Primary Key..................................................................................................................................................................33
III. Operational Risk.................................................................................................................................................................34
A. OPR_IMPORT Table ......................................................................................................................................................34
1. General Rules for Data Import in OPR_IMPORT........................................................................................................34
2. Primary Key..................................................................................................................................................................34
B. OPR_IM_IMPORT Table................................................................................................................................................34
1. General Rules for Data Import in OPR_IM_IMPORT.................................................................................................34
2. Primary Key..................................................................................................................................................................35
IV. Own Funds and Capital Items ............................................................................................................................................36
A. General Rules for Data Import in OWN_FUND_IMPORT ............................................................................................36
B. Primary Key.....................................................................................................................................................................36
Chapter 03 Appendix 2: Framework Example..................................................................................................................37
COREP CRD Framework example ..........................................................................................................................................38
RRT_Users_Guide_5.3P1.doc 3/38
5.3P1#m
Chapter 01 Using Fermat Regulatory Reporting
Tool
RRT_Users_Guide_5.3P1.doc 4/38
5.3P1#m
Introduction
A. Fermat RRT Purpose
In addition to CAD-Basel II regulatory requirements, regulatory reports are asked by national
supervisors. The templates for these reports are defined per supervisor and must be fed by the bank
with the results of credit risk, market risk and operational risk calculations.
Fermat RRT module (Regulatory Reporting Tool) has been developed to fulfill this need. Excel
publication format is available.
Furthermore, Fermat RRT module enables you to audit reports’ contents, and if needed, to performed
some adjustments.
B. Input Data
In order to feed the RRT engine with regulatory risk data, you can use Fermat RRT module with
Fermat CAD-BII calculation engine or independently from it. In this latter case, any other calculation
engine equivalent to Fermat must be plugged to Fermat RRT.
C. Document Version and Contents
This document is valid for Fermat 5.3P1 version only (i.e. 5.3.04.08 and next 5.3.04 versions).
The first chapter of this document describes in turn:
The actions and checks to perform before using Fermat RRT
An overview of the report generation process
How to import external data
The procedures to follow in order to generate reports (from graphical user interface or using a
task server)
How reports can be checked (audit feature) and the actions that can be performed within
reports in order to complete them (adjustments or additional information).
Some RRT underlying objects that could be needed by advanced users.
Furthermore, complete information on the import data model as well as a framework (see the
framework definition in the following paragraph) example can be found in the Appendix 1 and
Appendix 2 chapters.
D. Main Concepts
This document, as well as the user interface, is based on a specific vocabulary that needs to be known
by users.
1. Report
A report is an Excel file based on a regulatory template.
RRT_Users_Guide_5.3P1.doc 5/38
5.3P1#m
2. Framework
A framework is a set of empty reports (templates) provided by a supervisor. For example, the COREP
framework is provided by the European (CRD) supervisor.
3. Non-Aggregated vs Aggregated Data
Non-aggregated data correspond to individual records representing each portion of an identified
exposure covered by an identified credit risk mitigant (exposures’ portions). For example, the CAD-
Basel II results generated by Fermat CAD-BII - CRCRC correspond to non-aggregated data. Non-
aggregated data is never visible in the reports.
Aggregated data correspond to data that is ready to be displayed in the reports.
4. Input Data vs Import Data
In this document, the “input data” term refers to all data needed by the RRT to perform the report
generation (CAD-Basel II results generated by Fermat CAD-BII - CRCRC and import data).
The “import data” term refers to data imported from an external system. An adjustment deal is also
considered as import data because it is stored in the same underlying table (*_IMPORT tables, see the
Underlying Objects (for Advanced Users) section).
5. Adjustment Deal
An adjustment deal is a record imported manually by users after the report generation and before
sending the reports to the supervisors, in order to apply corrective adjustments to the amounts
displayed in the reports. In Fermat, an adjustment deal is considered as import data because it is stored
in the same underlying table (*_IMPORT tables, see the Underlying Objects (for Advanced Users)
section).
Note: Adjustment deals are managed when working with Excel outputs only.
RRT_Users_Guide_5.3P1.doc 6/38
5.3P1#m
I. Preparing the Process
A. Pre-Requisites: Ms Excel Version
If you want to publish the reports in Excel format, Microsoft Excel 2003 must be installed.
When the process is run in client mode, Excel must be installed on the client machine.
When the process is run in server mode, Excel must be installed on the server. When the server
has been installed as a Windows service, you have to link it to a user account (Windows user).
This can be done in the Connexion tab of the Properties window.
Note: All Excel parameters and customizations must have been set before using Fermat.
B. Importing the Setup
The setup (i.e. the reports templates issued by the national regulations, their mapping into Fermat…) is
provided by Fermat.
The setup is delivered as DMP files. These DMP files should not be modified.
The DMP files must be loaded into your database. Then, the datasets generated in the database must be
imported on a reference context. Please refer to the Building_Fermat_Reference_Context document to
get the related procedure and complete information on DMP files and datasets.
The DMP files to be loaded are:
(if you want to perform reporting for Capital Adequacy and Credit Risk)
FERMAT_REP_BII_CR_[Regulation Code]_[Fermat Version][Dataset Version].dmp
(if you want to perform reporting for Capital Adequacy, Credit Risk, Market Risk and
Operational Risk)
FERMAT_REP_BII_ALL_[Regulation Code]_[Fermat Version][Dataset Version].dmp
The [Fermat Version] corresponds to the production release. For Fermat 5.3P1 release, 5.3.P1 and
5.3.PX are valid.
C. Defining Obligor Grades and LGD Grades
Among other things, the regulatory reports classify and sum up the exposures according to the PD and
LGD linked to the exposure (PD being designated as “obligor grade” in the RRT module).
In order to enable such classification, the creation of an obligor grades’ and LGD scales is required.
To create an obligor grades’ scale, follow the steps below:
1. Open the FDW > Counterparties and netting > Ratings > Rating agencies datawindow and
create, if it does not exist, an agency for reporting, with Agency type = ‘INTERNAL’ and
Agency code = ‘REPORTING’.
2. Update
RRT_Users_Guide_5.3P1.doc 7/38
5.3P1#m
3. In the FDW > Counterparties and netting > Ratings > PD Ratings datawindow, select the
agency for Reporting in the Rating agency combo-box.
4. Create the ratings associated to this agency. The Rating column must be filled with ‘AAA’,
‘AA’, ‘A’, 'BBB'… values. Up to 30 values can be defined.
5. For each rating, input a value in the Lower PD bound and Upper PD bound columns.
6. Update.
To create an LGD scale, follow the steps below:
1. Open the FDW > Counterparties and netting > Ratings > Rating agencies datawindow and
create, if it does not exist, an agency for reporting, with Agency type = ‘INTERNAL’ and
Agency code = ‘REPORTING’.
2. Update
3. In the FDW > Counterparties and netting > Ratings > LGD Ratings datawindow, select the
agency for Reporting in the Rating agency combo-box.
4. Create the ratings associated to this agency. The Rating column must be filled with ‘AAA’,
‘AA’, ‘A’, 'BBB'… values. Up to 30 values can be defined.
5. For each rating, input a value in the Lower LGD bound and Upper LGD bound columns.
6. Update.
RRT_Users_Guide_5.3P1.doc 8/38
5.3P1#m
II. Report Generation Process Overview
A. Section Purpose
Understanding the process structure may be useful because partial processes are enabled. For example,
when working on imported aggregated data only, the publication step only needed.
B. Report Generation Process Overview
The following picture gives an overview of the report generation process.
Note: Advanced users will find the underlying tables and views for the different steps of the process in
the Underlying Objects (for Advanced Users) section.
RRT_Users_Guide_5.3P1.doc 9/38
5.3P1#m
Report Generation Process Overview
Non-aggregated
results imported CAD-BII Results
from external
system
Step0: data loading
Step0: data loading
Non-aggregated input data
Filter on Data
STEP 1:
Dimensions A
mapping
Aggregated A
Data mapped towards
results imported dimensions
from external
system
STEP 2:
A
Aggregation
Step0: data loading
A
Aggregated data
Filter on Data
STEP 3: A
Publication
Excel Reports
Legend
A Internal information that may be needed by advanced users only (except
“aggregated data” and “Step3: Publication”, which can be used when importing
aggregated data.
Grey shading RRT engine perimeter
Outputs
Data state
Bordered information
RRT_Users_Guide_5.3P1.doc 10/38
5.3P1#m
III. Importing Data from an External System
A. Introduction
This section describes the actions to perform in order to import regulatory requirements computed with
an external tool.
Fermat RRT module enables you to import:
Non-aggregated data
Aggregated data: in this case, the imported data has to be aggregated according to aggregation
axes defined by Fermat.
Note: When regulatory requirements have been computed with Fermat CAD-BII CRCRC, data is
already stored in the database. You do not need to perform further actions.
B. Importing Non-Aggregated Data
In order to import non-aggregated data, you must import it in the *_IMPORT tables.
Please refer to the Underlying Objects (for Advanced Users) section to get the list of *_IMPORT
tables.
Please refer to the DMM to know how the *_IMPORT tables’ fields must be filled.
Please refer to the Appendix 1 chapter to get the general rules for filling the *_IMPORT tables.
A user interface (RRT >Import Data Model > Import detailed results) enables you to access the
*_IMPORT tables.
C. Importing Aggregated Data
Aggregated data must be imported in the REP_TEMPLATE_IMPORT table. Once this table has been
filled, you can access it in the user interface (RRT >Import Data Model > Import aggregated
results).
The REP_TEMPLATE_IMPORT table must be filled as follows:
The REP_TEMPLATE_IMPORT.report_name and
REP_TEMPLATE_IMPORT.framework_name fields are mandatory as they enable to identify
in which report the imported row will be used.
The REP_TEMPLATE_IMPORT.fact1, fact2, … fields must be filled with the values
corresponding to the REP_FACT.id field.
The REP_TEMPLATE_IMPORT.dim1, dim2, … fields must be filled with the dimensions
used in each report. The dimensions values to enter can be retrieved in the
REP_DIMENSION_SR.dim1, REP_DIMENSION_SR.dim2 field…
RRT_Users_Guide_5.3P1.doc 11/38
5.3P1#m
IV. Generating Reports
A. Introduction
Before launching the report generation, two preliminary steps are required:
1. Ensuring that obligor grades and LGDs scales are up to date.
This step is described in the first paragraph of this section.
2. Setting up the data to be processed (imported data and/or CAD-BII results generated by Fermat
CAD-BII – CRCRC, Credit Risk only related data or all risk types related data…).
Such setup is designated as the definition of the processed data and is described in the second
paragraph of this section.
Then, the report generation can be launched two ways:
From a dedicated graphical user interface (RRT > Reporting Process > Parameters)
Using a task server
Notes:
Advanced users can use the RRT > Reporting Setup window (using the right-click popup
menu) to launch the process steps separately.
When launching the reporting process after a CAD-Basel II calculation, you must ensure that
the method selected for the calculation process corresponds to the expected reports.
Statistics are launched automatically as soon as underlying data is refreshed (see the Defining
the Processed Data Scope paragraph).
B. Updating Obligors Grades’ Scales and LGD Scales
Access path: REP > Reporting Process > External entry point
If obligor grades’ scales or LGD scales have been modified in the database, you must ensure that
Fermat RRT will used updated data.
In order to get updated obligor grades:
1. Enter the REP > Reporting Process > External entry point window
2. Create a new row
3. In the Name column, select 'BEFORE_REPORTING_PROCESS'
4. In the Module Name column, select 'PACK_B2_COMMON'
5. In the Procedure Name column, select 'RELOAD_RATING_TAB'
6. The Call order column can be used when several procedures are linked to the
BEFORE_REPORTING_PROCESS entry point. Else it must be set to '1'.
7. Update
In order to get updated LGD grades, the same procedure as above must be executed, except that
‘RELOAD_LGD_RATING’ must be selected in the Procedure Name column.
RRT_Users_Guide_5.3P1.doc 12/38
5.3P1#m
C. Defining the Processed Data Scope
There are two actions to perform in order to select the data to be processed and to filter it:
1. Select the data to be loaded by the RRT engine. Two options are available:
o If you want to load all data (data related to Credit Risk, to Market Risk…), select the
Refresh underlying data check-box (RRT > Reporting Process > Parameters).
In this case, Fermat loads all data linked to the modules installed.
o (only available when using the task server) If you do not want to load all data (for example
Credit Risk and Market Risk modules are installed but you want to generate reports for
Credit Risk only), you must call one or several procedures via the entry point functionality
(RRT > Reporting Process > External Entry Point).
In the Name column, select ‘BEFORE_REPORTING_PROCESS’. In the Module Name
column, select ‘PACK_REP_LOADING’. In the Procedure Name column, select one of
the following procedures:
To load both imported data and CAD-
To load imported data only
Basel II results
load_t_cdr_from_v_cdr_import load_t_cdr_from_v_cdr_rep
load_t_mkr_from_v_mkr_import load_t_mkr_from_v_mkr_rep
load_t_ofund_from_v_ofund_import load_t_ofund_from_v_ofund_rep
load_t_opr_from_v_opr_import load_t_opr_from_v_ofund_rep
load_t_secu_from_v_secu_import load_t_secu_from_v_secu_rep
2. (Not required when working on Fermat CAD-BII results only). Filter the data to load according
to the process you want to launch (for example a process for the A supervisor, in the B
reporting currency…). This can be done by selecting a value in the in the Company,
Currency, GAAP and Supervisor fields (RRT > Reporting Process > Parameters).
D. Generating Reports from the User Interface
1. Defining the Report(s) to Generate
Access path: RRT > Reporting Process > Parameters
Fermat enables you to generate either all framework reports or a single report.
If you want to generate all framework reports:
1. Select the framework in the Framework field.
2. Do not fill the Report field.
If you want to generate a single report:
1. Select the framework in the Framework field.
2. Select the report in the Report field.
2. Generating Reports
Access path: RRT > Reporting Process > Parameters
In order to generate reports:
RRT_Users_Guide_5.3P1.doc 13/38
5.3P1#m
1. Ensure that the Adjustment run check-box is clear. Please refer to the Validating Reports
section to get details on adjustment runs.
2. Select the ‘Dimension+Aggregation+Publication’ value in the Calculation step field. When
working on imported aggregated data only, the ‘Publication’ value must be selected.
3. Select the Run reporting process command in the right-click popup menu
Note: The ‘Dimension’ and ‘Dimension+Aggregation’ values can be selected by advanced users that
want to analyze intermediate tables.
E. Generating Reports on a Task Server
In order to generate reports in server mode, a task server can be used. Please check that a server has
been installed and is running.
This paragraph does not aim at describing the task server feature; it rather focuses on the reporting
process task and on the requirements linked to this process.
Complete information on the task server is available in ADM Process Management.
Two modes are available:
The basic mode. This case is described in the Basic Process in Server Mode paragraph.
The advanced mode, which corresponds to a partial process, and which is addressed to
confirmed users. This case is described in the Advanced Process in Server Mode paragraph.
1. Basic Process in Server Mode
In order to set up a basic process from the task server:
1. In the ADM > Task manager > Model Set tab, create, if it does not exist, a model set named
‘REP_PROCESS’.
2. In the ADM > Task manager > Model tab, create a model for the ‘REP_PROCESS’ model
set. Select the ‘Process reporting’ task in the Task Name column and enter the following string
of values in the Task parameters/command column: -1;-1;-1;-1;0;-1;-1.
3. In the RRT > Reporting Process > Parameters tab, select the reports to generate. See the
corresponding sub-paragraph in the Generating Reports from the User Interface paragraph.
2. Advanced Process in Server Mode
Access path: ADM > Task manager > Model
In order to set up a partial process from the task server:
1. In the ADM > Task manager > Model Set tab, create, if it does not exist, a model set named
‘REP_PROCESS’.
2. In the ADM > Task manager > Model tab, create a model for the ‘REP_PROCESS’ model
set. Select the ‘Process reporting’ task in the Task Name column.
RRT_Users_Guide_5.3P1.doc 14/38
5.3P1#m
3. Define the parameters for this task in the Task parameters/command column. The parameters
are described in the following table.
Parameter Parameter value Notes
P1: Framework Value available in the REP_FRAMEWORK.id
ID field
P2: Report ID Value available in the REP_REPORT.id field
P3: Template ID INTERNAL: must be set to ‘-1’
P4: Aggregation
INTERNAL: must be set to ‘-1’
set ID
‘0’ --> Run from RRT > Reporting Process >
Parameters tab (see the Basic Process in Server
Mode paragraph)
‘1’ --> Dimension calculation for framework
‘2’ --> Dimension calculation for report
It is possible to sum
‘4’ --> Dimension calculation for template
up the IDs. For
(INTERNAL, not to be used)
example, in order to
‘256’ --> Aggregation for framework
launch the dimension
‘512’ --> Aggregation for report
calculation,
P5: Step ID ‘1024’ --> Aggregation for template (INTERNAL,
aggregation and
not to be used)
publication for a
‘2048’ --> Aggregation for aggregation set
framework, you can
(INTERNAL, not to be used)
enter ‘65793’ (1 +
‘65536’ --> Publication for framework
256 + 65536)
‘131072’ --> Publication for report
‘16777216’ --> Audit for report, step1
(INTERNAL, not to be used)
‘33554432’ --> Audit for report, step2
(INTERNAL, not to be used)
P6: Publication
‘E’ --> Excel
mode
‘A’ --> Adjustment
P7: Run Type
‘F’ --> Full
F. Generated Reports
At the end of the process, one or several reports are generated (one if the Report field has been filled
in the RRT > Reporting Process > Parameters tab, else all reports of the selected framework).
1. Generated Reports’ Location
The report location depends on user settings.
By default, reports are stored in Fermat database (DB_FILES table) and are available in the RRT >
Generated reports browser entry. You can open them directly by double-clicking the files.
If you want to modify this setting, you must use the Save generated reports (ADM > System
Administration > Users params tab) parameter. The values for this parameter are: ‘Fermat database’
(default setting), ‘On hard drive’ or ‘On both’.
RRT_Users_Guide_5.3P1.doc 15/38
5.3P1#m
Reports generated on hard drive are saved by default on 'C: / Temp.'. If you want to modify the save
location, you must use the Reporting Directory parameter (ADM > System Administration >
Machine parameters tab). Select the save location by clicking the browse button.
2. Generated Reports’ Name
The generated reports’ name is as follows:
[Framework]-[Reporting _date]-[Workspace]-[Report_name]-[Timestamp]
RRT_Users_Guide_5.3P1.doc 16/38
5.3P1#m
V. Validating Reports
A. Audit
Important: You can not launch two audits on the same context.
After the generation of reports, an audit process can be launched manually on a report.
In other terms, the audit feature enables you to know how an Excel cell is filled: the users have to build
a cube (based on input tables), that enable them to know where data come from (which dealbook,
which entity type…has contributed to fill the cell).
The audit process is always separated from the reporting process, as it is based on results stored in the
database at the end of the reporting process.
Audit is processed in two steps:
Step1: selection of the cell to audit
Step2: definition of aggregation axis according to which the cube(s) will be based.
Note: Please refer to Fermat Thick Client Interface and User Tools to get complete information on
Fermat cubes.
1. Audit Step1
To select the cell to audit, follow the steps below:
1. In the RRT > Additional infos and audit window, select the EXCEL AUDIT sub-entry
under the report to audit, as shown in the picture below:
The following fields are displayed in the window right pane:
RRT_Users_Guide_5.3P1.doc 17/38
5.3P1#m
2. Select the Excel sheet to audit in the Sheet Name field.
3. Input the coordinates of the cell to be audited in the Column and Row fields.
4. Right-click and select Generate audit on the current cell.
5. When no data corresponds to the specified cell (the cell is empty), the audit fails.
Otherwise, the Audit -Step2 window opens:
6. See the following paragraph to get details on step2 processing.
2. Audit Step2
At the end of the audit step1, the Audit -Step2 window opens:
RRT_Users_Guide_5.3P1.doc 18/38
5.3P1#m
In this window, the fields in the Main audit parameters area (Sheet Name, Column Cell and Row
Cell) are automatically filled with the identifiers of the cell (defined during audit step1). These fields
are not editable.
In order to launch the audit step2:
1. Fill the Underlying table field. The underlying tables that can be selected are the ones on
which the report is based (for example the T_MKR table is the Market Risk related underlying
table).
2. The cube is displayed in definition mode (only when the Always open the cube in the
definition mode check-box is selected in the ADM > System Administration > Users
params window) when selecting Generate audit on the underlying table in the right-click
popup menu.
RRT_Users_Guide_5.3P1.doc 19/38
5.3P1#m
3. Select rows and values fields.
The cube is displayed:
RRT_Users_Guide_5.3P1.doc 20/38
5.3P1#m
4. Double-click the cell you want to focus your audit on.
A new cube (one level smaller perimeter) opens in definition mode, enabling you to audit cells
always more granular.
Notes:
The Sum of imported data field is automatically filled with the amount (for the cell) that
comes from the REP_TEMPLATE_IMPORT table. This amount is given for information: as
the audit can only be performed for non-aggregated input data (*_IMPORT and T_* tables),
you may find a total value (in the Total cell of the cube) that does not correspond to the
audited cell. In this case, the value retrieved in the Sum of imported data field constitutes the
difference between both amounts.
Select Generate audit on imported table in the right-click popup menu in order to display the
REP_TEMPLATE_IMPORT records that contribute to the cell filling.
The Adjustment value field is automatically filled with the amount (for the cell) that comes
from an additional information management.
B. Additional Information
The additional information feature enables you to fill an empty cell with content, to replace the cell
content or to add a value to the cell content.
To launch the additional information feature:
1. In the RRT > Additional infos and audit window, select the ADDITIONAL INFOS sub-
entry under a report, as shown in the picture below:
2. In the window right pane, select the Excel sheet on which information will be added in the
Excel Sheet column.
3. Input the coordinates of the cell to be adjusted in the Column and Row columns.
4. Select the way Fermat would display information in the Data type column. Possible values are
'Amount', 'Date', 'Percentage' and 'Text'.
Important: When selecting the ‘Text’ value, the additional information input in the
Adjustment column will replace the original cell contents. It must be used carefully.
RRT_Users_Guide_5.3P1.doc 21/38
5.3P1#m
5. Input information in the Adjustment column.
6. In the Reporting Process > Parameters tab, select the ‘Publication’ value.
7. Select the Run reporting process command in the right-click popup menu.
Notes:
Adjusted cells are displayed in red when the Red color for cells filled with additional infos
check-box is selected in the ADM > System Administration > Users params tab. This
enables you to see at a glance the information that has been added after the first report
generation.
The Row Owner column is an internal column that enables the application to distinguish
Fermat delivered setup (when available) and custom one. It should not be modified.
C. Adjustment Deals
When some cells contain errors, it is possible to import an adjustment deal in the import tables
(*_IMPORT tables). The deal is identified as an adjustment deal thanks to the flag_adjustment field
that must be set at 'True' in these tables. Please refer to the Underlying Objects (for Advanced Users)
section to get the list of import tables.
Once the adjustment deal has been imported, the relevant report must be re-generated.
The procedures to follow are the same as the ones given in the Generating Reports section, except that
the Adjustment run check-box must be selected in the RRT > Reporting Process > Parameters tab.
RRT_Users_Guide_5.3P1.doc 22/38
5.3P1#m
VI. Underlying Objects (for Advanced Users)
A. Section Purpose
This section aims at providing the users with technical objects (mainly the underlying tables and
views). Such information may be useful:
If you want to check intermediate results
When using the audit feature
In order to understand the meaning of the procedures that you might need to call.
B. Underlying Input Data Model
1. Non-Aggregated Data
The following table details the data model used as input for a reporting process (for non-aggregated
data).
Type of Input views/tables Input views/tables generated by the RRT
reporting module
CAD-BII External Import Views Tables
Calculation
Results
V_* *_IMPORT V_*_REP T_*
Reporting V_CDR CDR_IMPORT V_CDR_REP T_CDR
for Credit
risk
Reporting V_MKR MKR_IMPORT (or V_MKR_REP T_MKR
for Market MKR_IM_IMPORT)
risk
Reporting V_OPR OPR_IMPORT (or V_OPR_REP T_OPR
for OPR_IM_IMPORT)
Operational
risk
Reporting V_OWN_FUN OWN_FUND_IMPO V_OWN_FUND_REP T_OWN_FUND
for own D RT
funds
allocation
Reporting V_SECURITIZ SECURITIZATION_ V_SECURITIZATION T_SECURITIZATI
for ATION IMPORT _REP ON
securitization
Notes:
RRT_Users_Guide_5.3P1.doc 23/38
5.3P1#m
The MKR_IM_IMPORT and OPR_IM_IMPORT tables can be used for internal model.
The way the V_CDR, V_MKR (…) views are populated is detailed in dedicated Excel files in
the CAD-BII documentation.
The way the CDR_IMPORT, MKR_IMPORT (…) tables must be filled is documented in the
DMM.
Both V_* views and *_IMPORT tables have the same structure, except that the *_IMPORT
(…) tables contain additional fields used for data filtering (supervisor_code, company_code,
ccy_code, gaap_code) and an additional field enabling the adjustment run feature
(flag_adjustment).
2. Aggregated Data
The REP_TEMPLATE_IMPORT table is used.
Please refer to the Importing Data from an External System section and to the Appendix 1 for details
on the way data must be imported in this table.
The DMM also contains information for each field.
C. Underlying Tables and Views
The following schema corresponds to the one given in the Report Generation Process Overview
section, but the underlying tables and views are provided.
Credit risk example is taken (usage of the V_CDR, CDR_IMPORT, T_CDR tables and views). The
same structure exists for market risk, operational risk, own funds and securitization. Please refer to the
Underlying Input Data Model section and to the Appendix 1 chapter for details on such structures.
RRT_Users_Guide_5.3P1.doc 24/38
5.3P1#m
Report Generation Process Overview
CDR_IMPORT V_CDR
Step0: data loading
Step0: data loading
(V_CDR_REP)
T CDR
Filter on Data
STEP 1:
Dimensions A
mapping
A
REP_TEMPLATE_ REP_DIMENSION_SR
IMPORT
STEP 2:
A
Aggregation
Step0: data loading
A
(REP_TEMPLATE_SR)
Filter on Data
V_REP_TEMPLATE_SR
STEP 3: A
Publication
REP_REPORT_SR
Legend
A Internal information that may be needed by advanced users only (except
“aggregated data” and “Step3: Publication”, which can be used when importing
aggregated data.
Grey shading RRT engine perimeter
Outputs
Data storage location
Bordered information
RRT_Users_Guide_5.3P1.doc 25/38
5.3P1#m
Chapter 02 Appendix 1: Detailed Import Data Model
RRT_Users_Guide_5.3P1.doc 26/38
5.3P1#m
Introduction
The reporting engine can be used independently from the Basel II calculation engine.
In this case, calculated data must be directly imported in Fermat, in specific tables, following some
rules and constraints explained in parts below:
Import table for credit risk: CDR_IMPORT
Import tables for market risk: MKR_IMPORT and MKR_IM_IMPORT
Import tables for operational risk: OPR_IMPORT and OPR_IM_IMPORT
Import table for calculation of own funds and capital items: OWN_FUND_IMPORT
Import table for securitization: SECURITIZATION_IMPORT (not documented in current
version)
The description of the fields constituting these tables, with the constraints and lists of possible values,
if any, is available in Fermat Data Model Manual (DMM).
IMPORT STRATEGY
Results have to be computed and imported for HOME supervisor.
Results shall already take consolidation effects (exclusion of intercos for example) into
consideration.
Granularity of import:
o Option 1: It is possible to import detailed results (in the CDR_IMPORT,
MKR_IMPORT, MKR_IM_IMPORT, OPR_IMPORT, OPR_IM_IMPORT,
OWN_FUND_IMPORT or SECURITIZATION_IMPORT tables). This is suited, for
example, for subsidiaries using Fermat (or equivalent product). In this case, full audit is
available.
o Option 2: It is possible to import aggregated results per report (in the
REP_TEMPLATE_IMPORT table). In this case, audit possibility is limited but it is
suited for small subsidiaries which provide to the holding the equivalent of the Excel
report file. Please refer to the Importing Data from an External System section in
chapter 1 for more details on this practice.
RRT_Users_Guide_5.3P1.doc 27/38
5.3P1#m
I. Credit Risk
Credit risk exposures are imported from external systems in the CDR_IMPORT table.
This section aims at describing how exposures must be recorded in this table and at listing the tables
that must be previously filled (because of foreign keys).
The contents and the format of the data to be imported in the CDR_IMPORT table are documented in
the Data Model Manual (DMM).
A. Rows Creation: General Rule
Important: In the following paragraphs, the “guaranteed” term refers to the application of a CRM (a
guarantee or a credit derivative) with substitution effect (PD/RW substitution method).
The general rule is as follows: a transaction must be decomposed into different exposure portions
(collateralized or not, guaranteed or not, both collateralized and guaranteed, on- or off-balance). Then,
one row must be created in the CDR_IMPORT table for each exposure portion.
This means that at least one row must be created for the unsecured portion of a transaction.
Regarding the guaranteed portions, as many rows as guarantees must be created. For example, a
transaction secured by two guarantees must be reflected in the CDR_IMPORT table by three rows: one
for the unsecured portion and two for the two guaranteed portions.
Furthermore, when an exposure is entirely covered, a row must still be created (where EAD = 0).
In the CDR_IMPORT table, the fields with the "_new" suffix are used for the guaranteed portion(s). In
the other cases, the value for these fields is the same as the one of the "_original" suffixed fields. The
fields with the "_original" suffix correspond to the value before guarantee application.
Let’s take the following example: a committed facility on which a drawdown has already been made is
partially collateralized and guaranteed. Following the decomposition of the transaction through the tree
we can deduce the number of rows that must be imported in the CDR_IMPORT table.
RRT_Users_Guide_5.3P1.doc 28/38
5.3P1#m
B. Rows Creation: Specific Cases
1. Other Portion Types
Next to the portion types described above, an exposure can be decomposed according to the following
properties:
Securitized/Not securitized
Link to several business lines
2. Additional Rows
The special cases for which additional rows must be created are:
Short put options on equity or on bond and long futures on equity or on bond can generate a
risk on the underlying security. In this case, one additional row must be created to reflect such
risk.
Repo transactions involving a securities portfolio (as many rows as underlying securities must
be created).
RRT_Users_Guide_5.3P1.doc 29/38
5.3P1#m
Leasing transactions generate, next to the counterparty credit risk, a residual value risk. One
additional row must be created in the CDR_IMPORT table to reflect such risk.
Purchased receivables can generate a dilution risk. One additional row must be created to
reflect such risk.
When handling repo netting, a row corresponding to the add-on exposure must be created.
When selling credit derivatives, one row must be created for the risk towards the credit
derivative buyer and one other row for the risk towards the obligation name.
For commitments on equities, a row must be created for the off-balance exposure.
C. Foreign Keys
Fermat has created foreign keys for some fields of the CDR_IMPORT table. This means that you must
ensure that the tables listed below are correctly filled.
Fermat Tables Remarks
REP_ASSET_CLASS
REP_ENTITY_TYPE These tables are provided by Fermat in the
REP_REGULATORY_PRODUCT FERMAT_REP_BII* datasets.
REP_SUPERVISOR
CURRENCY These tables are provided by Fermat in the FERMAT_FDW
COUNTRY dataset.
GL_GAAP
These tables are filled with the bank’s values (free).
COMPANIES
RRT_Users_Guide_5.3P1.doc 30/38
5.3P1#m
II. Market Risk
Market risk exposures are imported in the MKR_IMPORT and MKR_IM_IMPORT tables from
external systems. These data will be used in reporting engine for market risk reporting.
Please refer to the Data Model Manual to have the precise definition of each field in market risk import
tables.
For more details on market risk, refer to the CAD-BII Market Risk documentation.
Important: This paragraph gives an example of the MKR_IMPORT table filling rules for the COREP.
It may not be applicable for other report framework.
A. MKR_IMPORT Table
1. General Rules for Data Import in MKR_IMPORT
Basically, the MKR_IMPORT table contains one row per aggregated position.
The value contained by some field of MKR_IMPORT table depends on the rows of another Fermat
table (FDW referential). In this case, the necessary table is indicated in the definition of the field, in
the Data Model Manual. These tables are listed below:
CURRENCY
GL_GAAP
SUPERVISOR
2. Primary Key
The primary key of the MKR_IMPORT table is constituted of the following fields:
position_reference
risk_type
underlying_risk
method
matched
B. Focus on some Fields of MKR_IMPORT
1. Position_type = 6
The rows with Position_type=6 enable the application to fill the columns ‘Net Positions subject to
capital Charge’ and Capital Requirements. In the MKR SA FX it is the ‘Long’,’ Short’ and ‘Matched’
(of ‘POSITIONS SUBJECT TO CAPITAL CHARGE’ column).
To fill these columns, here is how the MKR_IMPORT table must be filled:
• Exposure is the amount that will fill the ‘Net Positions subject to capital Charge’ (except in the
case of MKR SA FX see below)
• Rw corresponds to the risk capital charge (in %)
RRT_Users_Guide_5.3P1.doc 31/38
5.3P1#m
• Rgc corresponds to capital requirement
In the COREP report, here are the rows that will be filled from position_type=6 lines of the
MKR_IMPORT table:
• For MKR SA TDI report: all the “fillable” rows except the two first ones (TRADED DEBT
INSTRUMENT , and General Risk Maturity Based approach) that are deduced from an
aggregation of the rows below.
• FOR MKR SA EQU report: all the “fillable” rows except the two first ones (TRADED DEBT
INSTRUMENT , and General Risk Maturity Based approach) that are deduced from an
aggregation of the rows below.
• For MKR SA FX report: the matched columns are filled for the exposure column. The currency
category allows knowing of which line of the Corep report will be filled. The Long, Short
columns are also filled from the exposure amount (for currency categories 4 and 5).
2. Non Delta risk
To fill the non delta risk columns in the COREP report, here are the columns that always have to be
filled in the MKR_IMPORT table (some other columns like ‘national_market for MKR SA EQU
report are mandatory for just one kind of risk):
• Risk_type : ‘NDR’
• Underlying_risk: depends of the kind of risk
• Rgc that will be the total capital requirement
• Gamma_risk and Vega_risk that will be the individual gamma and vega risk. Individual means
that the gamma_risk will be seen, even if the contribution to the gamma risk is 0. If for
instance, for interest rate risk, a position has a negative gamma risk and belongs to a maturity
zone for which the aggregated gamma risk is positive. We know that in this case the aggregated
gamma risk will be 0 and thus the contribution of the individual position is 0, but in the gamma
risk field it won’t be 0.
3. Expression of Amounts
The amounts are expressed in reporting currency. The currency detail field is important to be filled for
interest rate risk (the results are displayed by currency) and forex risk.
4. Position_reference Field
This has generally to be filled: positions are to be put individually in MKR_IMPORT table in order to
allow the audit. Nevertheless in some cases it will not be useful: when aggregated amounts are in
MKR_IMPORT table especially (for position_type = 6). The rule is the following: when audit is
possible from other lines in MKR_IMPORT table, the position_reference is of no use for position_type
= 6. But if it is not the case, then the position_reference is mandatory.
For instance: for MKR SA TDI report, audit is possible with lines corresponding to position_type=2,
so there is no need to fill the position_reference column for position_type=6.
RRT_Users_Guide_5.3P1.doc 32/38
5.3P1#m
On the other hand for non delta risk, there are only the “position_type = 6” lines so in this case the
position_reference column is mandatory.
C. MKR_IM_IMPORT Table
1. General Rules for Data Import in MKR_IM_IMPORT
MKR_IM_IMPORT table contains the results of market risk calculation, from external systems, with
Internal Model approach. This table is specifically dedicated to fill in the following reports: MKR IM
and MKR IM Details.
The value contained by some field of MKR_IMPORT table depends on the lines of another Fermat
table, from the FDW referential. In this case, the necessary table is indicated in the definition of the
field, in the Data Model Manual. These tables are listed below:
CURRENCY
GL_GAAP
SUPERVISOR
2. Primary Key
The primary key of MKR_IM_IMPORT is constituted of the following fields:
position_reference
eq_specific_risk_calculation
dbt_spec_risk_calculation
overshooting_pl_type
confidence_level
holding_period
day
effective_pl_code
mkr_im_amount
mkr_im_risk_type
RRT_Users_Guide_5.3P1.doc 33/38
5.3P1#m
III. Operational Risk
The OPR_IMPORT and OPR_IM_IMPORT tables enable you to input the data that will be used in
Operational risk reporting process.
Please refer to the Data Model Manual to have the precise definition of each field in market risk import
tables.
A. OPR_IMPORT Table
1. General Rules for Data Import in OPR_IMPORT
One row corresponds to the gross income (and other indicators) of a business line which has been
recorded for a given year.
The value contained by some field of the OPR_IMPORT table depends on the rows of another Fermat
table (from the CAD-Basel II or FDW referential). In this case, the necessary table is indicated in the
definition of the field, in the Data Model Manual. These tables are listed below:
BIS_BUSINESS_LINE
BUSINESS_LINE
CURRENCY
GL_GAAP
SUPERVISOR
2. Primary Key
The primary key of the OPR_IMPORT table is constituted of the following fields:
company_code
supervisor_code
business_line_code
bis_business_line_code
method
B. OPR_IM_IMPORT Table
1. General Rules for Data Import in OPR_IM_IMPORT
One row corresponds to the gross income (and other indicators) of a business line which has been
recorded for a given year, with an internal model approach.
The value contained by some field of OPR_IMPORT table depends on the rows of another Fermat
table (from the CAD-Basel II or FDW referential). In this case, the necessary table is indicated in the
definition of the field, in the Data Model Manual. These tables are listed below:
BIS_BUSINESS_LINE
BUSINESS_LINE
CURRENCY
RRT_Users_Guide_5.3P1.doc 34/38
5.3P1#m
GL_GAAP
SUPERVISOR
2. Primary Key
The primary key of the OPR_IM_IMPORT table is constituted of the following fields:
event_reference
company_code
supervisor_code
business_line_code
bis_business_line_code
RRT_Users_Guide_5.3P1.doc 35/38
5.3P1#m
IV. Own Funds and Capital Items
Own funds have to be imported in the OWN_FUND_IMPORT table
A. General Rules for Data Import in OWN_FUND_IMPORT
This table must contain one row per capital item with the corresponding amount linked to the item.
The value contained by some field of OWN_FUND_IMPORT table depends on the rows of another
Fermat table (from the CAD-Basel II or FDW referential). In this case, the necessary table is indicated
in the definition of the field, in the Data Model Manual. These tables are listed below:
CURRENCY
BIS_CAPITAL_ITEM
GL_GAAP
SUPERVISOR
B. Primary Key
The primary key of the OWN_FUND_IMPORT table is constituted of the following fields:
company_code
supervisor_code
capital_item
booking_company
RRT_Users_Guide_5.3P1.doc 36/38
5.3P1#m
Chapter 03 Appendix 2: Framework Example
RRT_Users_Guide_5.3P1.doc 37/38
5.3P1#m
COREP CRD Framework example
Dimension used as tab
Report type Template Name Template Description
break if any
CR SA Credit, Counterparty credit and delivery SA Exposure Class/ IRB
risks: Standardized approach to capital Exposure Class
requirements
CR IRB Credit, Counterparty credit and delivery IRB Exposure Class
risks: IRB approach to capital Own estimates of LGD
requirements and/or conversion factors
(FOU/ADV)
COREP Credit Equity – IRB approaches to capital
CR EQU IRB
Risk requirements
CR SEC SA Securitizations - Standardized approach to Securitization type
capital requirements
CR SEC IRB Securitizations - IRB approach to capital Securitization type
requirements
CR SEC Details Detailed information on securitizations by
originators and sponsors
CR TB SETT Settlement risk in the trading book
MKR SA TDI Standardized approaches for position risks Currency
in traded debt instruments
MKR SA EQU Standardized approaches for position risks National market
in equities
COREP Market
MKR SA FX Standardized approaches for foreign
Risk
exchange risk
MKR SA COM Standardized approaches for commodities Commodity
MKR IM Internal models
MKR IM Details Internal models details
OPR Operational Risk
COREP OPR Details Gross losses by business lines and event
Operational types in the last year
Risk OPR LOSS Major operational risk losses recorded in
Details the last year or which are still open
Capital GROUP Information on affiliates
Adequacy Solvency Details
CA Total own funds for solvency purposes.
Capital requirements, Solvency ratio
RRT_Users_Guide_5.3P1.doc 38/38
5.3P1#m
Related docs
Other docs by yvtong
Web_Design_Tourism-MASTER_OF_ARTS_TOURISM_MANAGEMENT_NI_TermEnd_Examination_June_
Views: 1 | Downloads: 0
Web_Design_Tourism-Select_Italy_Press_Release__Italy_Travel_and_Vacation_Packages_
Views: 2 | Downloads: 0
Web_Design_Tourism-LEHIGH_UNIVERSITY_STUDY_ABROAD_OFFICE_PC_ROSSIN_COLLEGE_OF_
Views: 1 | Downloads: 0
Web_Design_Tourism-interior_design__University_of_Johannesburg_Rethink_Education_
Views: 1 | Downloads: 0
