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.

1. Run the recon tool sources (dll, inc, spc,s ql, vw) in the source schema and target schema.
2. 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’

3. 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’

4. 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:

1. Apply ‘CVTB_PARAM’ table related INCs in both the source schema and the target schema
2. Apply ‘CVTM_RECON_DYN_SCRIPTS’ table related INCs only in the target schema
3. Apply ‘CVTM_RECON_REPORTS’ table related INCs only in the target schema
4. 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’

5. 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.

1. 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’

2. 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’

3. Check the following parameters in the ‘CVTB_PARAM TABLE’:
4. 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:

1. 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’.

2. 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’

3. 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’

4. 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.

5. 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:

1. 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');

2. In the target schema, complete the following activities:

• Run insert statements prepared in the previous step
• Run commit

3. 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.

4. 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:

1. 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’

2. 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’

3. 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

4. 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.

5. 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

6. 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.

7. 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.

8. 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.

1. 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.

2. 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.

3. 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.