7. Data Reconciliation
7.1 Introduction
Once the data has been migrated from the source version to the target
version, you need to reconcile the data. You can use the Upgrade Reconciliation
Tool to compare the data on the source and target versions after migration
and after running a parallel EOD. After data reconciliation, you can
generate the reconciliation reports.
This chapter discusses the method of using upgrade data reconciliation
tool. The following points are discussed in connection with reconciliation
tool in this chapter:
- Setting up a new environment
- Releasing additional units (delta release)
- Changing the source and target schema in the existing system
- Extraction and report generation
- Generating migration reconciliation report
- Generating adhoc reconciliation report
- Generating parallel run reconciliation reports
- Moving extraction data into history table
7.2 Setting Up New Environment
When reconciliation tool is setup in a fresh environment, you need
to follow the steps given below.
- Run the recon tool sources (dll, inc, spc,s ql, vw) in
the source schema and target schema.
- In the source schema, complete the following activities:
- Update the source schema name, password and SID name in the script
‘1a_db_link_src.sql’ and run the script
- Run ‘1b_tab_recon_script_gen_src.sql’
- Run ‘2a-validate_recon_scripts_src.sql’
- Run ‘2b-update_inv_scripts_src.sql’
- Run ‘1d_populate_mapping_tables_scr.sql’
- In the target schema, complete the following activities:
- Update the target schema name, password and SID name in the script
‘1a_db_link_tar.sql’ and run the script
- Run ‘1b_tab_recon_script_gen_tar.sql’
- Run ‘2a-validate_recon_scripts_tar.sql’
- Run ‘2b-update_inv_scripts_tar.sql’
- Run ‘1d_populate_mapping_tables_tar.sql’
- Check the following parameters in the ‘CVTB_PARAM
TABLE’:
| Parameter |
| Description |
| RECON_MODULE_LIST |
| Module list in Tilda separated
values. This list will be taken, if the module code has been passed as
‘ALL’ during data extraction. |
| RECON_REPORT_PATH |
| Path where the Recon reports
needs to be generated. |
| TARGET_LM_INSTALLED |
| Target LM module installed
(it can be LM or EL) |
| SOURCE_LM_INSTALLED |
| Source LM module installed
(it can be LM or EL) |
| TARGET_LOAN_INSTALLED |
| Target Loans module installed
(it can be LD or CL) |
| SOURCE_LOAN_INSTALLED |
| Source Loans module installed
(it can be LD or CL) |
| RECON_ENVIRONMENT |
| This is the environment
Name. It will be appended as part of the report file name. |
7.3 Releasing Additional Units - Delta
Release
In case DDL files are released, it needs to be applied in both source
schema and target schema.
In case the INC files are released, you need to perform the following
activities:
- Apply ‘CVTB_PARAM’ table related INCs in both
the source schema and the target schema
- Apply ‘CVTM_RECON_DYN_SCRIPTS’ table related
INCs only in the target schema
- Apply ‘CVTM_RECON_REPORTS’ table related INCs
only in the target schema
- In the source schema, complete the following activities:
- Update the source schema name, password and SID name in the script
‘1a_db_link_src.sql’ and run the script.
- Run ‘1b_tab_recon_script_gen_src.sql’
- Run ‘2a-validate_recon_scripts_src.sql’
- Run ‘2b-update_inv_scripts_src.sql’
- Follow the below steps in Target System:
- Update the target schema name, password and SID name in the script
‘1a_db_link_tar.sql’ and run the script
- Run ‘1b_tab_recon_script_gen_tar.sql’
- Run ‘2a-validate_recon_scripts_tar.sql’
- Run ‘2b-update_inv_scripts_tar.sql’
In case spc, sql, vw (cvpks_recon_extract.sql, cvpks_recon_extract.spc) are released, you need
to execute them in both the source schema and the target schema.
7.4 Changing Source and Target Schema
in Existing System
When source schema and target schema in the existing system are changed,
you need to follow the steps given below.
- In the source schema, complete the following activities:
- Update the source schema name, password and SID name in the script
‘1a_db_link_src.sql’ and run the script
- Run ‘1b_tab_recon_script_gen_src.sql’
- Run ‘2a-validate_recon_scripts_src.sql’
- Run ‘2b-update_inv_scripts_src.sql’
- Run ‘1d_populate_mapping_tables_scr.sql’
- In the target system, complete the following activities:
- Update the target schema name, password and SID name in the script
‘1a_db_link_tar.sql’ and run the script
- Run ‘1b_tab_recon_script_gen_tar.sql’
- Run ‘2a-validate_recon_scripts_tar.sql’
- Run ‘2b-update_inv_scripts_tar.sql’
- Run ‘1d_populate_mapping_tables_tar.sql’
- Check the following parameters in the ‘CVTB_PARAM
TABLE’:
- RECON_MODULE_LIST Module list in Tilda separated values.
This list will be taken, if the module code has been passed as ‘ALL’
during data extraction)
| Parameter |
| Remarks |
| RECON_REPORT_PATH |
| Path where the Recon reports
needs to be generated. |
| TARGET_LM_INSTALLED |
| Target LM module installed
(it can be LM or EL) |
| SOURCE_LM_INSTALLED |
| Source LM module installed
(it can be LM or EL) |
| TARGET_LOAN_INSTALLED
|
| Target Loans module installed
(it can be LD or CL) |
| SOURCE_LOAN_INSTALLED
|
| Source Loans module installed
(it can be LD or CL) |
| RECON_ENVIRONMENT |
| This is the environment
name. It will be appended as part of the report file name. |
7.5 Generating Reports
You can generate various reports related to reconciliation tool. This
section discusses the methods to generate the following reports:
- Migration Reconciliation Report
- Adhoc Reconciliation Report
- Parallel Run Reconciliation Report
7.5.1 Generating Migration Reconciliation
Report
Once the source data is migrated to the target version environment,
you can generate the migration reconciliation report. This is a complete
reconciliation report and covers all the entities that need to be reconciled.
For generating the migration recon report, you need to follow the steps
given below:
- Check the following details:
- Head office branch for the source schema and target schema must be
the same
- The ‘Today’ column in ‘sttm_dates’ table
should be same for all the branches in the source schema and the target
schema
- The report generation path available in ‘CSTB_PARAM TABLE’,
PARAM_NAME: ‘RECON_REPORT_PATH’.
- The recon extraction modules available in ‘CSTB_PARAM TABLE’,
PARAM_NAME: ‘RECON_MODULE_LIST’.
- In the source schema, complete the following activities:
- Run ‘1b_tab_recon_script_gen_src.sql’
- Run ‘2a-validate_recon_scripts_src.sql’
- Run ‘2b-update_inv_scripts_src.sql’
- Follow the below steps in Target System:
- Run ‘1b_tab_recon_script_gen_tar.sql’
- Run ‘2a-validate_recon_scripts_tar.sql’
- Run ‘2b-update_inv_scripts_tar.sql’
- In the source schema, complete the following activity:
- Run ‘3_recon-migrt_src.sql’ with parameter ‘BRANCH_CODE’
as head office branch. In normal cases it is CHO.
- In the target schema, complete the following activities:
- Run ‘3_recon-migrt_tar.sql’ with parameter BRANCH_CODE
as head office branch. In normal cases it is CHO.
- Run ‘5_recon-reportgen_migrt.sql’ with parameter
BRANCH_CODE as head office branch. In normal cases it is CHO.
7.5.2 Generating Adhoc Reconciliation
Report
You can generate the adhoc reconciliation report for individual entities
that you need to verify. For generating this report, you need to follow
the steps given below:
- Before you start the report generation, check the following:
- Head office branch for the source schema and the target schema are
the same.
- The ‘Today’ column in ‘sttm_dates’ table
should be the same for all the branches in source and target system.
- The report generation path available in ‘CSTB_PARAM TABLE’,
PARAM_NAME: ‘RECON_REPORT_PATH’.
- The recon extraction modules available in ‘CSTB_PARAM TABLE’,
PARAM_NAME: ‘RECON_MODULE_LIST’.
- Get the list of entities which needs to be part of the adhoc report
generation (module code, entities) and prepare the below insert statement.
insert into cvtb_recon_adhoc_entity(module_code,entity) values ('PC','PC_PERIODIC_INSTRUCTIONS');
- In the target schema, complete the following activities:
- Run insert statements prepared in the previous step
- Run commit
- In the source schema, complete the following activity:
- Run ‘4c_recon-parl_adhoc_src.sql’ with parameter
BRANCH_CODE as ‘ALL’ for all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
- Follow the below steps in Target System:
Run ‘4c_recon-parl_adhoc_tar.sql’ with Parameter
BRANCH_CODE as ‘ALL’ for all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
Run ‘6e_recon-reportgen_parl_adhoc.sql’ with parameter
BRANCH_CODE as ‘ALL’ for all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
7.5.3 Generating Parallel Run Reconciliation
Report
Once the data is migrated, you need to run EOD batch on both the source
and the target environments at the same time. You can check specific
entities and mark for parallel run. The parallel run reconciliation report
provides the details of data reconciliation after the parallel EOD batch.
For generating this report, you need to follow the steps given below:
- Check the following:
- Head office branch for the source schema and the target schema are
the same.
- The branch for which Recon is planned to be executed
- The ‘Today’ column in ‘sttm_dates’ table
should be the same for the branch in source and target system, for which
the report is generated.
- The stage during which the recon is planned to be executed. It can
be ‘MarkEOTI’ or ‘PostBOD’
- The Oracle FLEXCUBE logical stage is the same for source schema and
target schema
- The report generation path available in ‘CSTB_PARAM TABLE’,
PARAM_NAME: ‘RECON_REPORT_PATH’
- The recon extraction modules available in ‘CSTB_PARAM TABLE’,
PARAM_NAME: ‘RECON_MODULE_LIST’
- In the source schema, complete the following activities:
- Run ‘1b_tab_recon_script_gen_src.sql’
- Run ‘2a-validate_recon_scripts_src.sql’
- Run ‘2b-update_inv_scripts_src.sql’
- In the target schema, complete the following activities:
- Run ‘1b_tab_recon_script_gen_tar.sql’
- Run ‘2a-validate_recon_scripts_tar.sql’
- Run ‘2b-update_inv_scripts_tar.sql’
At this stage, you need to consider two instances of parallel run
at MarkEOTI stage and PostBOD stage .
Case A: Parallel Run at MarkEOTI Stage
- In the source schema, complete the following activity:
- Run ‘4a_recon-parl_preod_src.sql’ with parameter
BRANCH_CODE as ‘ALL’ for all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
- In the target schema, complete the following activities:
- Run ‘4a_recon-parl_preod_tar.sql’ with parameter
BRANCH_CODE as ‘ALL’ for all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
- Run ‘46a_recon-reportgen_parl_preod.sql’ with parameter
BRANCH_CODE as ‘ALL’ For all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
Case B: Parallel Run at PostBOD Stage
- In the source schema, complete the following activity:
- Run ‘4b_recon-parl_pseod_src.sql’ with parameter BRANCH_CODE
as ‘ALL’ for all branch extraction. For a specific branch,
the BRANCH_CODE parameter needs to be the specific branch itself.
- In the target schema, complete the following activities:
- Run ‘4b_recon-parl_pseod_tar.sql’ with parameter
BRANCH_CODE as ‘ALL’ for all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
- Run ‘6b_recon-reportgen_parl_pseod.sql’ with parameter
BRANCH_CODE as ‘ALL’ for all branch extraction. For a specific
branch, the BRANCH_CODE parameter needs to be the specific branch itself.
- Check the following parameters in the CVTB_PARAM TABLE:
| Parameter |
| Remarks |
| RECON_MODULE_LIST |
| Module list in Tilda separated
values. This list will be taken if the module code has been passed as
‘ALL’ during data extraction. |
| RECON_REPORT_PATH |
| Path where the Recon reports
needs to be generated. |
| TARGET_LM_INSTALLED |
| Target LM module installed
(it can be LM or EL). |
| SOURCE_LM_INSTALLED |
| Source LM module installed
(it can be LM or EL). |
| TARGET_LOAN_INSTALLED
|
| Target Loans module installed
(it can be LD or CL). |
| SOURCE_LOAN_INSTALLED
|
| Source Loan module installed
(it can be LD or CL). |
| RECON_ENVIRONMENT |
| This is the environment
name. It will be appended as part of the report file name. |
7.5.4 Moving Extraction Data into
History Table
For moving the extraction data into the history tables, you need to
follow the steps given below.
- Check the following:
- Head office branch for the source schema and the target schema are
the same.
- The ‘Today’ column in ‘sttm_dates’ table
should be the same for all the branches in source and target system.
- Collect the branch code, stage and extraction date for the extraction
data which is being moved into the history table.
- In the source schema, complete the following activity:
- Run ‘1c_move_to_history_src.sql’ with parameter
branch code, stage and extraction date which has been collected in the
previous step.
- In the target schema, complete the following activity:
- Run ‘1c_move_to_history_tar.sql’ with parameter
branch code, stage and extraction date which has been collected in step
1.