Oracle Quantitative Management and Reporting for Solvency II Administration (Implementation) Guide

 

Release 2.2.0.0.100

Purpose. 2

Introduction. 2

QMR Overview.. 2

Installing QMR. 2

Financial Close Suite Version. 2

HFM Format 3

Installation Guidelines. 3

Implementing QMR for FCM.. 6

General Considerations. 6

Load Procedure. 6

Implementing QMR for FDM.. 7

General Considerations. 7

Load Procedure. 7

Implementing QMR for HFM.. 21

General Considerations. 21

Calendar/Profile. 21

Metadata. 22

Member Lists. 43

Rules. 44

Data Entry Forms. 60

Financial Reports. 62

Working with Data. 65

HFM Files for FDM.. 67

Glossary. 70

General Terms. 70

Technical Terms. 71


 

Purpose

This guide is designed for administrators implementing the Oracle Insurance Quantitative Management and Reporting for Solvency II (QMR) application.

This guide assumes that administrators are already familiar with the structure and usage of the Hyperion Financial Close Suite products: Financial Close Management (FCM), Financial Data Quality Management (FDM), Hyperion Financial Management (HFM) and Financial Reports (FR). This guide focuses on how to implement the QMR application. Please refer to the relevant sections of the product Admin and User Guides for further information related to the Financial Close Suite products.

Introduction

QMR Overview

The Oracle Insurance Quantitative Management and Reporting for Solvency II (QMR) is designed to provide a starting point for a new implementation of Hyperion Financial Management (HFM).

The current version of the QMR application is designed to produce validated Quantitative Reporting Template reports as required by the European Insurance and Occupational Pensions Authority (EIOPA) for both group and solo reporting. It provides the capability for users across the organization to load or enter the required data and generate Solo reports. Data required to be reported at Group is consolidated and reported accordingly.

The current version of the QMR application is based on two sets of requirements. All schedules required for “Interim” / “Preparatory” reporting are based on the September 2013 “EIOPA Final Report on Public Consultation No. 13/010” document (EIOPA_13_415_Final_Report_on_CP10.pdf). Additional schedules not defined in the CP 13/010 document are based on the July 2012 Quantitative Reporting Template (QRT) consultation pack. 

Installing QMR

Financial Close Suite Version

The application has been developed and tested on Financial Close Suite 11.1.2.1, 11.1.2.2 and 11.1.2.3. Other versions of the Close Suite are not supported.

The following or a more recent FCM Patch Set Update (PSU) must be applied:

-      FCM 11.1.2.1: PSU: 11.1.2.1.103

The following or a more recent FDM PSU may be applied (but is not mandatory):

-      FDM 11.1.2.1: PSU: 11.1.2.1.503 (patch 16582335, 32-bit version for all environments)

-      FDM 11.1.2.2: PSU: 11.1.2.2.301

-      PSU 301 resolves the issue of Validation reports not opening on completion of the data load. Reports can still otherwise be opened manually if PSU 301 is not installed.

The following or a more recent Financial Reporting PSU must be applied:

-      FR 11.1.2.1: PSU: 11.1.2.1.138

-      FR 11.1.2.2: PSU: 11.1.2.2.315

-      FR 11.1.2.3: PSU: 11.1.2.3.500 plus PSE 18467894

The following or a more recent HFM PSU must be applied:

-      HFM 11.1.2.1: PSU: 11.1.2.1.602

-      HFM 11.1.2.2: PSU: 11.1.2.1.304

-      HFM 11.1.2.3: PSU: 11.1.2.3.500

HFM Format

The current version of QMR is built using the HFM “Classic” files and not using EPMA or Calc Manager.

Installation Guidelines

Components

The QMR application consists of a number of files:

Application Files

·        HFM Calendar/Profile

o   Two files with descriptions in English only (EN), one for HFM 11.1.2.1, another for 11.1.2.2 and 11.1.2.3

o   Two files with descriptions in English, German, Spanish, French, Dutch (EN, DE, ES, FR, NL) 

·        HFM Metadata (XML format)

o   Two files with descriptions in English only (EN), one for HFM 11.1.2.1, another for 11.1.2.2 and 11.1.2.3

o   Two files with descriptions in English, German, Spanish, French, Dutch (EN, DE, ES, FR, NL) 

·        HFM Member Lists

·        HFM Rules

·        HFM Data Entry Forms

·        Financial Reports

·        FCM Task Lists

·        FDM Import formats, adaptors and reports in a single XML file

o   one file is provided for each different version of FCS (11.1.2.1 / 11.1.2.2 / 11.1.2.3)

o   one file is provided for use with an Oracle database and another for a Microsoft SQL database

·        Data

o   HFM EIOPA defined correlation and scalar factors

o   HFM Sample data

o   FDM Sample data

·        Localization

o   Metadata description localization files for use with the Metadata Localization Utility translating QMR descriptions from EN to DE, ES, FR, NL

o   The HFM Localization Utility that can be used with the localization files is provided as part of an HFM installation. For implementations earlier than 11.1.2.1.600 or 11.1.2.2.301, patch number 14361678 is available for download from the Oracle Support web site.

·        Documentation

o   QMR Administration (Implementation) Guide

o   QMR Administrator / User Guide

o   QMR Readme

A security file for HFM is not provided. It is anticipated that security will be established differently for each client using QMR and therefore no pre-built standard is deemed to be practical.

When implementing the HFM files provided in the QMR application, the application administrator may wish to modify existing metadata descriptions, add metadata members, or modify web data entry forms or reports. It is recommended however that existing metadata labels not be changed or existing rules / web data entry forms / financial reports may no longer work as expected. The notable exceptions are the Entity dimension which is expected to be completely customized for each client, and the Data Source members which can be removed, changed or added to (with the exception of the [None], PrecisionAdj and TotalInputData members which must be retained).

Implementing QMR 2.2.0.0.100 or upgrading from QMR 2.1.0 / 2.1.1 / 2.2.0

The functionality provided with QMR 2.2.0 / 2.2.0.0.100 has been updated for Interim Reporting requirements (CP 13/010) for all schedules listed in the document. All other schedules remain as specified in the EIOPA July 2012 QRT Consultation Packs. Many of the files used to install QMR 2.2.0 have therefore been updated from 2.1.1 with the exception of some Data Entry Forms and Financial Reports. Additional changes have been made to some files in QMR 2.2.0.0.100.

QMR 2.2.0 / 2.2.0.0.100 incorporates:

·        Support for FCS 11.1.2.1, 11.1.2.2 and 11.1.2.3:

o   HFM Profile (Calendar) and Metadata files for 11.1.2.3 are identical to the 11.1.2.2 files – for an 11.1.2.3 implementation, use the files labeled 11.1.2.2

o   FDM XML files are specific to the version of FCS installed – use the files labeled for the specific version and database type required

Note that QMR 2.2.0.0.100 requires files from both the QMR 2.2.0 application and the QMR 2.2.0.0.100 update.

To implement a new QMR 2.2.0.0.100 application:

1.    Load the 2.2.0 FCM and 2.2.0.0.100 FDM files as described in detail later in this guide.

2.    Modify the 2.2.0 HFM Profile (Calendar) file if required and create the application.

3.    Modify the 2.2.0.0.100 HFM Metadata file. As a minimum, replace the sample entity dimension with client-specific entities and structures. Modify other dimensions as required. See further details on what can and cannot be modified later in this guide. Load the modified metadata file.

4.    Load the 2.2.0.0.100 Member Lists

5.    Load the 2.2.0.0.100 Rules files.

6.    Load the 2.2.0 Data Entry Forms, either individually or from the LCM file provided.

7.    Load the 2.2.0.0.100 forms, overwriting any 2.2.0 forms of the same name.

8.    Load the 2.2.0 Financial Reports, either individually or from the ZIP file provided.

9.    Load the 2.2.0.0.100 Financial reports, overwriting any 2.2.0 reports of the same name

To upgrade an existing 2.1.0 / 2.1.1 application to 2.2.0.0.100, the following steps are suggested:

1.    Backup the HFM application.

2.    Ensure that a copy of the data loaded is retained or extracted.

3.    Merge any client-specific changes to Metadata, Rules and Member Lists files into the 2.2.0.0.100 files

4.    Load the QMR 2.2.0.0.100 HFM Metadata file using REPLACE mode. This will remove any members no longer required. Then use the Data Management “Clear Invalid Records” option to clear the orphaned data related to the deleted members. Load additional data required (re-load BS-C1 and BS-C1D data for example, with the Custom2 dimension re-mapped).

5.    Alternatively, when loading the metadata, select the “Clear all metadata before loading” option and then reload all data.

6.    Update the data feed / source data files for changes in mapping and new data–points required.

7.    Load the 2.2.0.0.100 HFM Member Lists file.

8.    Load the 2.2.0.0.100 HFM Rules file.

9.    Load the 2.2.0 Data Entry Forms, either individually or from the LCM file provided.

10. Load the 2.2.0.0.100 forms, overwriting any 2.2.0 forms of the same name.

11. Load the 2.2.0 Financial Reports, either individually or from the ZIP file provided.

12. Load the 2.2.0.0.100 reports, overwriting any 2.2.0 reports of the same name.

To upgrade an existing 2.2.0 application to 2.2.0.0.100, the following steps are suggested:

1.    Backup the HFM application.

2.    Ensure that a copy of the data loaded is retained or extracted.

3.    Merge any client-specific changes to Metadata, Rules and Member Lists files into the 2.2.0.0.100 files

4.    Load the QMR 2.2.0.0.100 HFM Metadata file using REPLACE mode. This will remove any members no longer required. Then use the Data Management “Clear Invalid Records” option to clear any orphaned data related to the deleted members. Load additional data required (Turnover, Premiums Written if required)

5.    Alternatively, when loading the metadata, select the “Clear all metadata before loading” option and then reload all data.

6.    Update the data feed / source data files for changes new data–points required.

7.    Load the 2.2.0.0.100 HFM Member Lists file.

8.    Load the 2.2.0.0.100 HFM Rules file.

9.    Load the 2.2.0.0.100 forms, overwriting any 2.2.0 forms of the same name.

10. Load the 2.2.0.0.100 reports, overwriting any 2.2.0 reports of the same name.


 

Implementing QMR for FCM

General Considerations

QMR provides a guideline for standard best practice to support QRT reporting. These practices / procedures are documented as tasks in Financial Close Management (FCM). There are over 80 tasks which are created as part of the application and can be further extended and modified to meet specific customer requirements.

Load Procedure

The QMR application provides best practices tasks in English (EN) plus German (DE), Spanish (ES), French (FR) and Dutch (NL). These are provided in separate csv files, one for each language. Three fields Name, Description and Instructions are translated into the language provided.

EN:    QMR_2.0.0_FCM_EN.csv

DE:    QMR_2.0.0_FCM_DE.csv

ES:     QMR_2.0.0_FCM_ES.csv

FR:     QMR_2.0.0_FCM_FR.csv

NL:     QMR_2.0.0_FCM_NL.csv

·        Open the QMR_2.0.0_FCM_<language code>.csv file in Excel or a text editor.

·        Update the following fields:

o   Owner

o   Approver

o   Reviewer

These fields have been documented to default to the admin user and can be changed to a specific user if required.

·        Create an FCM Template. Ensure that the minimum start day is -4 and the minimum end day is 14.

·        Click on the Template in the Template Management dialog.

·        Select Import Tasks.

·        Browse to the CSV file that contains all the tasks. Click OK.

All necessary tasks should be uploaded. Modify the tasks if required using the FCM web interface.

Once the tasks are finalized, they can be executed for a particular period.

·        Select the template in the Manage Template dialog.

·        Click Create Schedule.

·        Select period and map days to actual dates.

·        Click OK and then start schedule.

To upload a translated CSV file, ensure that the browser language is set to English (EN) before importing the alternative language file. After the file is imported, the browser language can be reset to the required language.


 

Implementing QMR for FDM

General Considerations

FDM is used for loading, validating and reporting the Assets schedules (except D3), Technical Provisions schedule F3, F3A and F3B, IGT and RC schedules. These schedules are reported for group and solo entities.

Load Procedure

Once the QMR application is set up in FDM, the following 15 QRT schedules can be imported, validated and reported:

·        QMR-Assets-D1 – List of Assets

·        QMR-Assets-D1 – List of Assets (EXTRACT)

·        QMR-Assets-D1S - Structured products Data - Portfolio list

·        QMR-Assets-D2O - Derivatives data – open positions

·        QMR-Assets-D2O - Derivatives data – open positions (EXTRACT)

·        QMR-Assets-D2T - Derivatives data - historical derivatives trades

·        QMR-Assets-D4 - Investment funds (look-through approach)

·        QMR-Assets-D5 - Securities lending and repos

·        QMR-Assets-D6 - Assets held as collateral

·        QMR-TP-F3 - Life obligations analysis

·        QMR-TP-F3A - Only for Variable Annuities - Description of guarantees

·        QMR-TP-F3B - Only for Variable Annuities - Hedging of guarantees

·        QMR-RC - Risk Concentration

·        QMR-IGT1 - Equity type transactions, debt & asset transfer

·        QMR-IGT2 - Derivatives

·        QMR-IGT3 - Internal Reinsurance

·        QMR-IGT4 - Cost sharing, contingent liabilities, off BS items and other IGT

Prerequisites

·        Ensure that an FDM application has been created for QMR. All of the following actions will be performed against the created FDM application.

·        Ensure that the FDM Adaptor to load data to HFM has been created and configured for the FDM application (as detailed in the relevant Target Adaptor ReadMe document). This step must be completed BEFORE the QMR XML file is loaded to ensure that a QMR-specific adaptor does not become the system adaptor.

·        All QMR-specific locations will support import into FDM for validation of the load file contents and for QRT schedule reporting. Note that the validation is completed as part of the file import process and does not require that the FDM “Validate” step be completed. If the Validate step is carried out by the user, an error will occur.

·        Note that the Assets D1 data will be loaded to FDM QMR-specific location only for reporting. This Assets D1 data will also need to be loaded to HFM in summary for validation against data reported for the Balance Sheet.

Setup - Import, Validate and Report QRTs

·        Open and log in to FDM Workbench.

o   EPM -> FDM -> Workbench -> Workbench Client

·        Import QMR FDM artifacts.

o   Select File -> Import… and select the QMR FDM xml file from the QMR application zip file:

§  Select the appropriate file for the version of FDM installed (11.1.2.1, 11.1.2.2 or 11.1.2.3) and the database on which FDM has been installed (MS SQL Server or Oracle).

·        This would create all the necessary QMR custom adaptors, reports, import formats and validation scripts.

·        Open and log in to FDM Web client.

o   EPM -> FDM -> Web Server Components -> Web logon

·        Create QRT locations for import and reporting

o   For each QRT schedule (total of 15), create one location (e.g. QMR-Assets-D1, QMR-RC). Multiple entities can be loaded to single location. This is required for reporting across entities.

o   For each location, specify the following:

§  Child of ControlsReview and select type Data Load.

§  Specify name (e.g. QMR-Assets-D1).

§  Select Load Type -> Bulk Insert

§  Bulk Insert is recommended over SQL Insert for performance reasons. Please refer to the FDM Administration documentation for the necessary steps to setup the Bulk Insert option, or to support article: https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1078450.1

§  Select Target Adaptor, pick the correct custom adaptor that matches the correct QRT (e.g. QMR-Assets-D1).

§  Select the Workflow Behavior tab and the correct Import Format (e.g. QMR-Assets-D1).

·        Set up Periods per QMR adaptor

o   Make sure periods are set up for the Global adaptor.

o   Open the MetaData -> Control Tables menu.

o   For each QMR adaptor in the right drop down.

§  Set up Target (M) and Year Target.

o   Update Grid.

·        Set up Categories per QMR adaptor.

o   Make sure categories are set up for the Global adaptor.

o   Change the left drop down to Category.

o   For each QMR adaptor in the right drop down.

§  Set up Target category (e.g. Actual).

o   Update Grid.

Consolidation setup in FDM

·        All Asset and Technical Provision QRT’s in FDM perform consolidation logic (aggregation, translation, and elimination).

·        For consolidation FDM needs the HFM entity hierarchy, default entity currencies and exchange rates. In order to load this information to FDM, an ability to create the appropriate files is provided to the administrator of the QMR application in HFM. For further details, please refer to the HFM Files for FDM on page 67.

·        Once these files are created, the administrator should then make them available on the FDM environment for consumption.

·        First, the entity hierarchy and default entity currencies entries are setup by running script: webQMR_Setup_Entities

·        Before running the webQMR_SetupEntities script, ensure that the QMREntityDetails.txt and QMREntityHierarchy.txt files have been created either manually or from HFM

·        Copy QMREntityDetails.txt and QMREntityHierarchy.txt under the FDM installation -> Inbox directory.

·        To run webQMR_Setup_Entities

o   EPM -> FDM -> Workbench -> Workbench Client

o   Click Tools -> Script Editor -> Custom -> Web

o   Select webQMR_Setup_Entities and click Run.

·        Second, to load currency exchange rates, an adaptor and import format named QMR-EXCH-RATES should be used. First create and setup location for the adaptor and load the exchanges rates file generated from HFM (e.g. QMRExchangeRates_Actual_2012_QA.txt)

·        Successful run of webQMR_Setup_Entities script and loading of exchange rates data is necessary for all Asset and Technical Provision QRT’s in FDM.

Asset reference and lookup table

·        Many of the assets and risk concentration QRT’s collect similar Assets data that could be referred to via a lookup table.  By using the assets reference table the user can simplify the loads of various QRT’s by eliminating many of the referenced fields (e.g. Security Title, Issuer Name, External Rating, etc.)

·        QMR_Assets_Ref is an adaptor that is created for load of the Assets reference/lookup table.

·        Before loading assets reference data make sure to setup a location as described earlier in this document.

·        NOTE: A user does not have to take advantage of the Assets reference and lookup table. They can continue to load all asset common reference data as part of the QRT data loads. The Asset reference and lookup table is provided for convenience and to provide common data integrity.

·        The following data elements are included in the Assets reference table

o   IDCode

o   IDCodeType

o   Security Title

o   Issuer Name

o   Issuer Sector

o   Issuer Group

o   Issuer Country

o   Country of Custody

o   Currency

o   CIC

o   External Rating

o   Rating Agency

o   Maturity Date

o   Issuer Code

o   Issuer Type

o   Issuer Group Code

o   Issuer Group Code Type

·        How does the reference and lookup work?

o   Asset IDCode is used as a key to match between Asset reference table and the QRT’s.

o   During a load a user can choose to load only the IDCode and omit the reference data for an asset (e.g. Security Title, Issuer Name, etc.) and all omitted data elements will be looked up from the Assets reference table when running the QRT reports. However if the reference data element exists in the load file then the value from the Asset reference table will be ignored.

Setup - Load QRT information to HFM

·        Open and log in to the FDM Web client.

o   EPM -> FDM -> Web Server Components -> Web logon

·        Create a location for push to HFM.

o   Assets QRT accounts/entities/balances need to be mapped and imported into HFM for all relevant assets schedules that need to be validated against the balance sheet in HFM.

o   Create a location for push to HFM

§  Child of ControlsReview and select type Data Load.

§  Specify name (e.g. QMR-Assets-HFM).

§  Select Load Type -> Bulk Insert.

§  Select Target Adaptor, pick the correct custom adaptor that matches the FDM version (e.g. FM11X-G5-E, FM11X-G6-A, FM11X-G6-B, FM11X-G6-C).

§  Make sure the Data Value is set to <Entity Currency>. If you can’t browse for the data value then the HFM adaptor for FDM is set up incorrectly.

§  Select the Workflow Behavior tab and select the correct Import Format (e.g. QMR-Assets-D1-HFM; note that this format is for all assets schedules that need to be imported to HFM).

·        Map Rules

o   Create Map rules that Map source data to the target members for each dimension.

o   Go to Activities -> Maps.

o   Make sure the location is set to be the location that pushes into HFM.

o   For each dimension (Entity, Account, ICP, C1..C4):

§  Select Type as Like.

§  Name the map e.g. EntityMap.

§  Enter Rule Definition as *.

§  Enter Target as *.

§  Click Update Grid.

§  Set up Category and Period target per QRT schedule.

o   Open the MetaData -> Control Tables menu.

o   For each QMR location in the right drop down.

§  Map the Period [Target Per (M)] & Year Target for the period you want to load.

§  Update Grid.

§  Change the left drop down to Category.

§  Map the Category to the HFM Scenario you would like to load.

§  Update Grid.

Customizing Validation Scripts

Upon load of the QRT files, appropriate validations are performed to ensure correct data is stored in QMR. These validations are performed by validation scripts. QMR for FDM has prebuilt over 40 validation scripts.

Example of a validation script could be as simple as checking whether the imported data is a valid date, or checking against a valid list of currencies or checking validity of an ISIN code. All validation scripts for QMR are prefixed with “QMR_”:

General validation functions:

Each of the following validation functions are applied to the schedule entries as required and defined in the EIOPA QRT Excel files and Log files.

Function                                         Logic

·        QMR_Amount:NZP                       Any valid number or no entry

·        QMR_Boolean                              Closed list:

"Y", "N"

·        QMR_Country                              ISO 3166 Country Code

·        QMR_Country_SupraNational        ISO 3166 Country Code + SupraNational (EU, XA) issuers

·        QMR_Currency                             ISO 4217 Currency Code

·        QMR_Date                                   ISO 8601 date format (YYYY-MM-DD)

For perpetual securities, use “9999-12-31”

·        QMR_IDCodeType                        Closed list:

"ISIN", "CUSIP", "Company Specific"

·        QMR_IDCode                               ISIN / CUSIP check digit logic

·        QMR_Numeric                              is numeric

·        QMR_Rate                                   is numeric from 0 and 100

·        QMR_RatingAgency                      Closed list:

"MOODY'S", "FITCH", “S&P”, “OTHER”

·        QMR_TypeOfCode                        Closed list:

"LEI", "PRE-LEI"

·        QMR_ICP                                     Ensures ICP and Entity aren’t the same

Schedule-specific validation function:

Function                                         Logic

·        QMR_Assets_Accounts                  Closed list:

"ASSETS_PPEOWNUSE"

"ASSETS_PROPERTY"

“ASSETS_PARTICIPATIONSSUBSIDIARIES”

“ASSETS_PARTICIPATIONSSIGNIFICANT”

"ASSETS_BONDSGOVERNMENT"

"ASSETS_BONDSCORPORATE"

"ASSETS_STRUCTUREDNOTES"

”ASSETS_COLLATERALIZEDSECURITIES"

"ASSETS_EQUITIESLISTED"

"ASSETS_EQUITIESUNLISTED"

"ASSETS_INVFUNDSEQUITIES"

"ASSETS_INVFUNDSDEBT"

"ASSETS_INVFUNDSMONEYMARKET"

"ASSETS_INVFUNDSASSETALLOC"

"ASSETS_INVFUNDSPROPERTY"

"ASSETS_INVFUNDSALTERNATIVE"

"ASSETS_INVFUNDSPRIVATEEQUITY"

"ASSETS_INVFUNDSINFRASTRUCTURE"

"ASSETS_INVFUNDSOTHER"

"ASSETS_DERIVATIVES"

"ASSETS_DEPOSITSEXCLCASHEQUIV"

"ASSETS_HELDFORFUNDS"

"ASSETS_DEPOSITSTOCEDANTS"

"ASSETS_LOANSMORTGAGESINDIVIDUAL"

"ASSETS_LOANSMORTGAGES"

"ASSETS_LOANSONPOLICIES"

"ASSETS_CASHANDEQUIVALENTS"

"ASSETS_DERIVATIVESLIAB"

·        QMR_Assets_BuyerSeller              Closed list:

"BUYER", "SELLER", "LENDER", "BORROWER"

·        QMR_Assets_CallablePuttable       Closed list:

"C", "P", "B"

·        QMR_Assets_CapitalProtection      Closed list:

"Y", "N", "P"

·        QMR_Assets_CIC                         Four character code: <CC><XX>

Where <CC> is a valid ISO country code or “XL” or “XT”

And <XX> is from “11” and “99” or a closed list:

"A1", "A2", "A3", "A5", "A7", "A8", "A9"

"B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9"

"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9"

"D1", "D2", "D3", "D5", "D7", "D8", "D9"

"E1", "E2", "E7", "E8", "E9"

"F1", "F2", "F3", "F4", "F9"

·        QMR_Assets_CollateralType          One character from “1” to “9” or “A” to “F”

·        QMR_Assets_CurrencyLocalForeign          Closed list:

"L", "F"

·        QMR_AssetsD1_MaturityDate        ISO 8601 date format (YYYY-MM-DD)

For perpetual securities, use “9999-12-31”

Only required when the related CIC code (third character) is 1, 2, 5, 6, 8  

·        QMR_Assets_GeoZone                  Closed list:

"EEA", "OECD", "ROW"

·        QMR_Assets_LongShort                Closed list:

"L", "S", "FX-FX", "FL-FX", "FL-FL", "FX-FL"

·        QMR_Assets_LookThrough            Closed list:

"S", "O", "M"

·        QMR_Assets_Participation             Closed list:

"N", "YNGNS", "YNGS", "YGS", "YGNS"

·        QMR_Assets_Pledged                   Closed list:

"CP", "CR", "CB", "R"

·        QMR_Assets_Portfolio                   Closed list:

"L", "NL", "G", "SF", "RF"

·        QMR_AssetsD2O_Portfolio             Closed list:

"L", "NL", "G", "I", "U"

·        QMR_Assets_ProductType             Closed list:

"CLN", "CMS", "CDS", "CDOP", "ABS", "MBS", "CMBS", "CDO", "CLO", "CMO", ”IRLN", "ELN", "FXCLN", "HLN", "MLN", "ILN", "OTHER"

·        QMR_Assets_SecurityRepoLending          Closed list:

"R", "L"

·        QMR_Assets_UnderlyingCategory  Closed list:

"1", "2", "3L", "3NL", "L"

·        QMR_Assets_UnderlyingSecurity   Closed list:

"EF", "CU", "IR", "CO", "IN", "O", "M"

·        QMR_Assets_UnwindTrigger          Closed list:

"B", "F", "R", "O"

·        QMR_Assets_UseDerivative           Closed list:

"MI", "EPM", "MA"

·        QMR_Assets_ValuationMethodSII  Closed list:

"QMP", "QMPS", “AVM”, “AEM, “IEM”, “MKTMK", "MKTMD"

·        QMR_Assets_RefMaturityDate       if 3rd digit of CIC code is 1, 2, 5, 6, 8 maturity date must exist

·        QMR_IGT1_TransactionType         Closed list:

"EQUITY TYPE - OTHERS", "OTHER ASSET TRANSFER - PROPERTIES", "OTHER ASSET TRANSFER - OTHERS", "BONDS/DEBT - UNCOLLATERALIZED", "BONDS/DEBT - COLLATERALIZED", "EQUITY TYPE - DIVIDENDS", "EQUITY TYPE - SHARES/PARTICIPATIONS"

·        QMR_IGT2_TransactionType         Closed list:

"CONTINGENT LIABILITIES", "DERIVATIVES - FUTURES", "DERIVATIVES - FORWARDS",  "DERIVATIVES - OPTIONS", "DERIVATIVES - OTHERS", "G'TEES - CREDIT PROTECTION", "G'TEES - OTHERS", "SWAPS - CREDIT DEFAULT", "SWAPS - INTEREST RATE", "SWAPS - CURRENCY", "SWAPS - OTHERS"

·        QMR_IGT2_UseDerivatives            Closed list:

"EFFICIENT PORTFOLIO MANAGEMENT", "MICRO HEDGE", "MACRO HEDGE", "OTHER"          

·        QMR_IGT3_ReinsuranceTreaty      Closed list:

"FINANCIAL", "QUOTA SHARE", "SURPLUS/STOP LOSS", "XL - AGGREGATE", "XL - CATASTROPHE", "OTHERS"

·        QMR_IGT4_TransactionType         Closed list:

"CONTINGENT LIABILITIES", "OFF BALANCE SHEET ITEMS", "INTERNAL COST SHARING", "OTHERS"

·        QMR_RC_Impact                          Closed list:

"ASSETS", "LIABILITY", "OFF BALANCE SHEET (CONTINGENT ASSET)", "OFF BALANCE SHEET (CONTINGENT LIABILITY)"

·        QMR_TP_F3B_Hedged                  Closed list:

"Y", "N", "P", "NS"

·        QMR_TP_F3B_StrategyType          Closed list:

"D", "N", "S", "A"

·        QMR_TP_F3_LOB                         Closed list:

"INSURANCE WITH PROFIT PARTICIPATION", "INDEX LINKED AND UNIT-LINKED INSURANCE", "OTHER LIFE INSURANCE", "ANNUITIES STEMMING FROM NON-LIFE CONTRACTS", "ACCEPTED REINSURANCE", "HEALTH INSURANCE", "HEALTH REINSURANCE"

·        QMR_TP_F3_PremiumType           Closed list:

"R", "S", "NS", "NF", "O"

·        QMR_TP_F3_ProdClassification      Closed list:

"PS", "PJ", "PC", "PP", "PH", “PO”, "US", "UJ", "UC", "UP", "UH", “UO”, "OS", "OJ", "OC", "OP", "OH", “OO”, "AS", "AJ", "AC", "AP", "AH", “AO”, "HS", "HJ", "HC", "HP", "HH", “HO”

·        QMR_RatingAgency                      Closed list:

"MOODY’S”, "FITCH”, "S&P”, "OTHERS”,

To customize any of these validations or add to the valid list of values, follow these steps:

·        Open the FDM workbench and select the Scripts tab.

·        Edit the appropriate script and save.

Any script that validates against a list of values has an array of these values at the top of the script. e.g. QMR_IGT1_TransType.

Dim transTypeList

transTypeList = Array("EQUITY TYPE - OTHERS", _

"OTHER ASSET TRANSFER - PROPERTIES", _

"OTHER ASSET TRANSFER - OTHERS", _

"BONDS/DEBT - UNCOLLATERALIZED", _

"BONDS/DEBT - COLLATERALIZED", _

"EQUITY TYPE - DIVIDENDS", _

"EQUITY TYPE - SHARES/PARTICIPATIONS" _

)

To add/remove the value, simply edit the array. Enter the value in UPPER case as the UPPER case imported value is compared to the list of values in the array.

The following tables list the validation that is applied to each column of the source data file:

Assets

 

Col

Assets Field Name

Validation

D1

D1S

D2O

D2T

D4

D5

D6

 

1

Entity

 

2

Account

QMR_Asset_Accounts

X

X

X

X

X

X

X

 

3

AccruedInterest

QMR_Numeric

X

 

3

AccruedInterest

QMR_Rate

X

 

4

AcquisitionCost

QMR_Numeric

X

 

5

AttachmentPoint

QMR_Rate

X

 

6

BuyerSeller

QMR_Assets_BuyerSellerLender

X

 

7

CallablePutable

QMR_Assets_CallablePuttable

X

 

8

CapitalProtection

QMR_Assets_CapitalProtection

X

 

9

CIC

QMR_Assets_CIC

X

X

X

X

 

10

CICCategory

QMR_Assets_CollateralType

X

 

11

Collateral

QMR_Numeric

X

 

12

CollateralAssetType

QMR_Assets_CollateralType

X

 

13

CollateralDebtor

 

14

CollateralDebtorGroup

 

15

CollateralType

QMR_Assets_CollateralType

X

X

 

16

ContractName

 

17

CounterpartyGroup

 

18

CounterpartyID

 

19

ContractDimension

QMR_Numeric

X

X

 

20

Currency

QMR_Currency

X

X

X

X

 

21

CurrLocalForeign

QMR_Assets_CurrencyLocalForeign

X

 

22

CustodyCountry

QMR_Country

X

X

 

23

Delta

QMR_Numeric

X

 

24

DerivativeUse

QMR_Assets_UseDerivative

X

X

 

25

DetachmentPoint

QMR_Rate

X

 

26

Duration

QMR_Numeric

X

X

 

27

ExposuresWeight

QMR_Rate

 

28

ExternalRating

 

29

FarLegAmt

QMR_Numeric

X

 

30

FixedAnnualReturn(numeric)

QMR_Rate

X

 

31

FundNumber

 

32

GeoZone

QMR_Assets_GeoZone

X

 

33

IDCode

QMR_IDCode

X

X

X

X

X

X

 

34

IDCodeType

QMR_IDCodeType

X

X

X

X

X

X

 

35

IssuerCountry

QMR_Country_SupraNational

X

X

 

36

IssuerGroup

X

X

 

37

IssuerName

X

X

 

38

IssuerSector

 

39

IsUnitIndexLinked

QMR_Boolean

X

X

X

X

 

40

LongShort

QMR_Assets_LongShort

X

X

 

41

LookThrough

QMR_Assets_LookThrough

 

42

LossGivenDefault

QMR_Rate

X

 

43

MaturityDate

QMR_Date or QMR_AssetsD1_MaturityDate

X

X

X

X

X

 

44

MaximumLoss

QMR_Numeric

X

X

 

45

NearLegAmt

QMR_Numeric

X

 

46

NotionalAmt

QMR_Numeric

X

X

 

47

NumberOfContracts

QMR_Numeric

X

X

 

48

Participation

QMR_Assets_Participation

X

 

49

Pledged

QMR_Assets_Pledged

X

 

50

Portfolio

QMR_Assets_Portfolio

X

X

X

X

 

51

PremiumPaidRecd

QMR_Numeric

X

X

 

52

PrepaymentStructured

QMR_Boolean

X

 

53

ProductType

QMR_Assets_ProductType

X

 

54

ProfitLoss

QMR_Numeric

X

 

55

Quantity

QMR_Numeric

X

X

 

56

RatingAgency

QMR_RatingAgency

X

X

X

 

57

RepoLending

QMR_Assets_SecurityRepoLending

X

 

58

RiskFactors

 

59

Security

 

60

StartDate

QMR_Date

X

 

61

SwapDelCurrency

QMR_Currency

X

X

 

62

SwapIn

QMR_Numeric

X

X

 

63

SwapOut

QMR_Numeric

X

X

 

64

SwapRecdCurrency

QMR_Currency

X

X

 

65

SyntheticStructured

QMR_Boolean

X

 

66

TotalSIIAmount

QMR_Amount:NZP

X

X

X

X

X

X

X

 

67

TradeDate

QMR_Date

X

X

 

68

TriggerValue

 

69

UnderlyingAssetLiability

 

70

UnderlyingCIC

QMR_Assets_UnderlyingCategory

X

 

71

UnderlyingSecurity

QMR_Assets_UnderlyingSecurity

X

 

72

UnitSIIPrice

QMR_Numeric

X

X

 

73

UnwindTrigger

QMR_Assets_UnwindTrigger

X

X

 

74

ValuationMethodSII

QMR_Assets_ValuationMethodSII

X

X

X

 

75

VariableAnnualReturn

QMR_Rate

X

 

76

Dividends

QMR_Numeric

 

77

Interest

QMR_Numeric

 

78

Rent

QMR_Numeric

 

79

GainsLosses (numeric)

QMR_Numeric

 

80

ICP

 

X

X

X

X

X

X

X

 

81

IssuerCode

 

X

 

 

 

 

 

 

 

82

IssuerCodeType

QMR_ TypeOfCode

X

 

 

 

 

 

 

 

83

IssuerGroupCode

 

X

 

 

 

 

 

 

 

84

IssuerGroupCodeType

QMR_ TypeOfCode

X

 

 

 

 

 

 

 

85

CounterPartyCode

 

 

 

X

 

 

 

 

 

86

CounterPartyCodeType

QMR_ TypeOfCode

 

 

X

 

 

 

 

 

87

CounterPartyGroupCode

 

 

 

X

 

 

 

 

 

88

CounterPartyGroupCodeType

QMR_ TypeOfCode

 

 

X

 

 

 

 

Technical Provisions

 

Col

TP Field Name

Validation

TP-F3

TP-F3A

TP-F3B

 

1

Entity

 

2

ProdDenom

 

3

LOB

QMR_TP_LOB

X

 

4

HRG

QMR_Numeric

X

 

5

HRGCommon

QMR_Boolean

X

 

6

RFF

 

7

Commercialized

QMR_Boolean

X

 

8

ProductType

 

9

ProdClassID

QMR_TP_F3_ProdClassification

X

 

10

PremiumType

QMR_TP_F3_PremiumType

X

 

11

Country

QMR_Country

X

 

12

NumContract

QMR_Numeric

X

 

13

NewContract

QMR_Numeric

X

 

14

PremiumAmt

QMR_Amount:NZP

X

X

X

 

15

BestEst

QMR_Numeric

X

 

16

CapRisk

QMR_Numeric

X

 

17

SurrVal

QMR_Numeric

X

 

18

AnnGuarRate

 

19

TheoriticalClaims

QMR_Numeric

X

 

20

FinInstrUse

QMR_Boolean

X

 

21

GeneralDescription

 

22

CoverTerm

 

23

GMDBPresent

QMR_Boolean

X

 

24

GMDBLevel

QMR_Numeric

X

 

25

GMDBDesc

 

26

GMABPresent

QMR_Boolean

X

 

27

GMABLevel

QMR_Numeric

X

 

28

GMABDesc

 

29

GMIBPresent

QMR_Boolean

X

 

30

GMIBLevel

QMR_Numeric

X

 

31

GMIBDesc

 

32

GMWBPresent

QMR_Boolean

X

 

33

GMWBLevel

QMR_Numeric

X

 

34

GMWBDesc

 

35

StrategyType

QMR_TP_F3B_StrategyType

X

 

36

HedgedDelta

QMR_TP_F3B_Hedged

X

 

37

HedgedRho

QMR_TP_F3B_Hedged

X

 

38

HedgedGamma

QMR_TP_F3B_Hedged

X

 

39

HedgedVega

QMR_TP_F3B_Hedged

X

 

40

HedgedFX

QMR_TP_F3B_Hedged

X

 

41

HedgedOther

QMR_TP_F3B_Hedged

X

 

42

ECResultWOHedge

QMR_Numeric

X

 

43

ECResultHedge

QMR_Numeric

X

 

44

ICP

 

X

X

X

 

45

IDCode

 

X

 

 

Risk Concentration

 

Col

RC Field Name

Validation

RC

 

1

Entity

 

2

CounterPartyName

 

3

ExposureNature

 

4

ExposureCountry

QMR_Country

X

 

5

IDCode

QMR_IDCode

X

 

6

IDCodeType

QMR_IDCodeType

X

 

7

ExternalRating

 

8

RatingAgency

 

9

IssuerSector

 

10

ExposedEntity

 

11

ExposedEntityCode

 

12

Amount

QMR_Amount:NZP

X

 

13

Currency

QMR_Currency

X

 

14

MaturityValidityDate

QMR_Date

X

 

15

MaximumExposure

QMR_Numeric

X

 

16

MaximumReinsurerLiab

QMR_Numeric

X

 

17

CededTP

QMR_Numeric

X

 

18

Impact

QMR_RC_Impact

X

Intra-Group Transfers

 

Col

IGT Field Name

Validation

IGT1

IGT2

IGT3

IGT4

 

1

Entity

 

2

Transferee

 

3

TransfereeCode

 

4

Transferor

 

5

TransferorCode

 

6

IDCode

QMR_IDCode

X

X

 

7

IDCodeType

QMR_IDCodeType

X

X

 

8

Threshold

QMR_Numeric

X

 

9

TransactionType

QMR_IGT1_TransactionType

X

 

9

TransactionType

QMR_IGT2_TransactionType

X

 

9

TransactionType

QMR_IGT4_TransactionType

X

 

10

TransactionDate

QMR_Date

X

X

X

 

11

MaturityDate

QMR_Date

X

X

 

12

Currency

QMR_Currency

X

X

X

X

 

13

TransactionAmount

QMR_Numeric

X

 

14

Amount

QMR_Amount:NZP

X

X

X

X

 

15

TransactionInPeriod

QMR_Numeric

X

 

16

TopUpsInPeriod

QMR_Numeric

X

 

17

BalanceOfContract

QMR_Numeric

X

 

18

CouponRate

QMR_Rate

X

 

19

ReferenceDocs

 

20

ValueAtTransDate

QMR_Numeric

X

 

21

ValueAtReportDate

QMR_Numeric

X

 

22

UseOfDerivatives

QMR_IGT2_UseDerivatives

X

 

23

UnderlyingAssetLiability

 

24

ProtectedCounterPartyName

 

25

DeliveredInterestRate

 

26

ReceivedInterestRate

QMR_Rate

X

 

27

DeliveredCurrency

QMR_Currency

X

 

28

ReceivedCurrency

QMR_Currency

X

 

29

ValidityStartDate

QMR_Date

X

 

30

ValidityExpiryDate

QMR_Date

X

 

31

ReinsuranceType

QMR_IGT3_ReinsuranceTreaty

X

 

32

MaximumRecoverable

QMR_Numeric

X

 

33

ReinsuranceClaimsPaid

QMR_Numeric

X

 

34

ReinsuranceResult

QMR_Numeric

 

35

ContractStartDate

QMR_Date

X

 

36

ContractExpiryDate

QMR_Date

X

 

37

TriggerEvent

 

38

MaxContingentLiabInSIIBS

QMR_Numeric

X

 

39

MaxContingentLiabNotInSIIBS

QMR_Numeric

X

Assets Reference

 

Col

RC Field Name

Validation

Ref

 

1

IDCode

QMR_IDCode

X

 

2

IDCodeType

X

 

3

Security

X

 

4

IssuerName

X

 

5

IssuerSector

X

 

6

IssuerGroup

X

 

7

IssuerCountry

QMR_Country

X

 

8

CustodyCountry

QMR_ Country

X

 

9

Currency

QMR_Currency

X

 

10

CIC

QMR_Assets_CIC

X

 

11

MaturityDate

QMR_Date

X

 

12

ExternalRating

X

 

13

RatingAgency

QMR_RatingAgency

X

 

14

IssuerCode

 

X

 

15

IssuerType

 

X

 

16

IssuerGroupCode

 

X

 

17

IssuerGroupCodeType

 

X

Exchange Rates

 

Col

RC Field Name

Validation

Ref

 

1

Currency_From

QMR_Currency

X

 

2

Currency_To

QMR_Currency

X

 

3

AvgRate

QMR_Numeric

X

 

4

ClosingRate (Amount)

QMR_Numeric

X

Editing and customizing reports

QMR reports in FDM provide QRT reports in five languages (English, German, French, Dutch and Spanish). To edit the design (e.g. add new data columns) of any of these reports, follow these steps:

·        Open the FDM workbench.

·        Select the Reports tab.

·        Click on the Report in the QMR folder for the language.

·        Right click Design Report.

To create reports for a new language (e.g. Swedish) follow these steps:

·        Select the English QMR report group.

·        Right click and select Properties.

·        Write down the settings for each field.

·        Close the properties form.

·        Right click the language you want to create a group for and select Add Report Group.

·        Enter the same information as the QMR group but leave the language selection alone.

·        Go back to the English QMR report group, right click the report you want and choose Copy Report.

·        Go back to the new group and select Paste Report.

·        Then just right click the new report and design the report.


 

Implementing QMR for HFM

General Considerations

The QMR HFM components have been created and configured to provide reports for all QRT schedules except those retained in FDM. There are however dimensions that must be modified to client requirements and others that can be customized if required.

Calendar/Profile

The Calendar/Profile file defines several of the application dimensions (Year, Period Frequency) and also defines the languages used for descriptions. The application administrator may wish to modify this file before creating the application. Changes to the application profile elements cannot be made after the application is created, a new application must be created.

Languages

The QMR metadata is provided with descriptions in English (EN) and in a second file, with localization translations to German (DE), Spanish (ES), French (FR) and Dutch (NL). The languages provided are defined in the Calendar / Profile. Additional descriptions can be added to the application by defining additional languages in the Calendar/Profile file before creating the application. It is strongly recommended that the English descriptions not be removed from the application.

If additional languages are added to the application, the descriptions in those languages for all dimension members will need to be added to the Calendar / Profile file (frequency and period descriptions) and Metadata file (all metadata dimensions). The HFM Metadata Localization Utility can be used to insert additional language descriptions to the metadata file based on a translation file, either provided with the application (currently DE, ES, FR and NL) or created by the administrator.

The HFM Metadata Localization Utility is provided as part of an HFM installation. For implementations earlier than 11.1.2.1.600 or 11.1.2.2.301, the Metadata Localization utility patch (number 14361678) is available for download from the Oracle Support web site. Further details describing the use of the utility are available in the utility documentation.

Frequency

The QMR standard quarterly file defines two frequencies.

·        QTD    quarter-to-date

·        YTD    year-to-date

The frequency labels and descriptions can be changed as required. If the labels are changed then the default frequencies applied to the Scenario members in metadata must also be changed.

Years

The QMR standard file defines the range of years as 2010 to 2025. There are no descriptions for years. The year range can be changed as required.

Periods

The QMR standard quarterly file defines five base time periods within a year.

·        Quarters        labeled Q1Q4, QA

·        Year              labeled [Year]

QA (Annual) is included as a period due to the separate reporting requirements for Q4 quarterly reporting and annual reporting.

All labels and descriptions can be changed before loading the file to create a new application except for [Year] which is a required system member. It is recommended that the quarterly labels not be changed although the descriptions can be modified.

Metadata

The metadata file defines the application settings and the remaining dimensions not defined in the Calendar / Profile file. These dimensions are: Currency (Value), Scenario, Entity, Account, Custom1, Custom2, Custom3 and Custom4.

Application Settings

Application Currency

The Application Currency is set to EUR. This currency is used as the common currency when triangulation of exchange rates is required. All exchange rates should be entered in terms of the Application Currency. This Application Currency entry can be changed to any valid currency.

Default Rates

The Default Rates for Balance Accounts (Assets, Liabilities) and the Default Rates for Flows (Revenue, Expenses) must match accounts of type CurrencyRate. Accounts OpeningRate, AverageRate and ClosingRate are provided.

PVA For Balance / Flow Accounts

Default translations can be calculated on either a periodic basis (PVA) or on a period-end basis (VAL). All QMR reporting is currently on a Year-to-date basis. It is recommended that the period-end basis be used for translations. For further details, please refer to the HFM Administrator’s Guide.

Validation Account

A validation account has not been set. The validation account can be used to prevent "locking" of data or "promotion" of process units should Process Management be implemented. For further details please refer to the HFM Administrator’s Guide.

Consolidation Methods

The consolidation rules provide full consolidation. Minority interest, Equity elimination and Proportional consolidation can be selected on an entity-by-entity basis by selecting the appropriate Method. Methods can be selected from the Ownership Management screen or loaded via a data file. Calculating ownership methods from Shares Outstanding / Shares Owned is not supported due to the different methods that may be required for the same ownership percentage. For further details please refer to the HFM Administrator’s Guide.

Currency / Value Dimension

The QMR metadata file defines most world currencies using the three-character ISO 4217 currency codes. HFM default translation will be processed based on the default currencies of the entities.

NOTE: It is recommended that only those currencies that are currently required and those that will be required in the foreseeable future are retained in the currency dimension of the metadata file used to create the application. Retaining currency codes not required may cause performance degradation. Note that the currency dimension is used only for the currency of the entities. Currency codes have also been created in the Custom3 and Custom4 dimensions for recording the currency of assets etc. All currencies can be retained in these dimensions without performance degradation.

The modification of entities and the required currency for each should be completed before finalizing the currencies to be retained. Note that by default the currencies will be displayed in the sequence in which they were created. If any currencies are to be deleted then all entities must be checked to ensure that a deleted currency is not used as a default currency.

The currencies defined, in addition to a series of predefined system members, are used to create the Value dimension.

User Defined Fields

The Scenario, Entity, Account and Custom1 to Custom4 dimensions all have three User Defined (UD) attribute fields for each member.

The QMR application uses these UD fields for various purposes. UD Field entries are used to direct HFM rules to perform certain actions on specific points-of-view, to tag certain members which share a common functional property or to provide some other member-specific information.

GetUDEntry Function

In order to make the best use of the UD fields, the QMR Member Lists and Rules files use a function (GetUDEntry) that allows each UD field to contain more than one reference (note that this function was written for starter kit applications and is not a generic HFM function).

For example, a UD field might contain the following string:

Keyword1:Entry1|Keyword2:Entry2|Keyword3:Entry3

This user defined entry consists of three separate elements, separated by a “pipe” / vertical line symbol (“|”). Within each element, there is an identifying “keyword” followed by the entry. So in the example shown above, there are three entries:

Keyword: Keyword1    Entry: Entry1

Keyword: Keyword2    Entry: Entry2

Keyword: Keyword3    Entry: Entry3

The GetUDEntry function can be used to access entries in a specific UD field (UD1, UD2, UD3) or in any UD field (ALL). For the Account dimension only, retrieving an entry from ALL fields will also retrieve an entry from the XBRLTags field.

EPMA WARNING: The pipe symbol used to separate multiple UD Field entries is not compatible with a migration from a Classic application to an EPMA application. If a Classic QMR application is to be migrated to EPMA, the pipe symbol must be replaced by an alternative symbol. Two preparation steps are required:

  1. Modify the classic XML metadata file by searching for and replacing | with ^ in a text file editor. Then re-load metadata to the Classic application (Merge mode is sufficient).
  2. Modify both the Member Lists and Rules files. Search for FieldDelimiter = "|" and replace with FieldDelimiter = "^". Re-load the Member Lists and Rules files.

The application can now be migrated successfully.