3.1.2 OFS Know Your Customer (KYC)

This section describes the KYC-related post-installation steps.

Note:

  • For new BD/KYC installation, follow these steps:
    • Import all assessment (xml) files located in $FIC_HOME/IPE/KYC, then run the DI batch.
    • Once the DI batch is completed successfully, import all assessments (xml) files located in $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation.
    • Start executing the regular batches.

If OFS KYC is installed:

  1. Navigate to $FIC_HOME/ficapp/common/FICServer/bin and execute the following executables after replacing the INFODOM placeholder:
    1. ./RTIImport.sh $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation/ OFS_KYC_RTIExport_AcceleratedRereview.xml##AMINFODOM## OFS_KYC false
    2. ./RTIImport.sh $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation/ OFS_KYC_RTIExport_ChangeInRiskModel.xml##AMINFODOM## OFS_KYC false
    3. ./RTIImport.sh $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation/ OFS_KYC_RTIExport_ModelBasedAssessment.xml ##AMINFODOM## OFS_KYCfalse
    4. ./RTIImport.sh $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation/ OFS_KYC_RTIExport_RAOR_ModelBasedAssessment.xml ##AMINFODOM##OFS_KYC false
    5. ./RTIImport.sh $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation/ OFS_KYC_RTIExport_RAOR_RuleBasedAssessment.xml##AMINFODOM## OFS_KYC false
    6. ./RTIImport.sh $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation/ OFS_KYC_RTIExport_RuleBasedAssessment.xml ##AMINFODOM## OFS_KYCfalse
  2. Perform this step only if Oracle Financial Services Compliance Studio is installed. Do not import it if Compliance Studio is not installed. Navigate to $FIC_HOME/ficapp/common/FICServer/bin and execute the following executable after replacing the INFODOM placeholder.

    ./RTIImport.sh $FIC_HOME/IPE/KYC/8.1.2.6.0/Simulation/ OFS_KYC_RTIExport_AcceleratedRereview_WithAomalyDetectionML.xml ##AMINFODOM## OFS_KYC false

  3. Execute the following utility to synchronize data in SMS tables:
    1. Go to $FIC_HOME/utility/AppPckMastSynch/bin.
    2. Open putty session and run the following commands one by one:
      1. ExportPackName=OFS_BD_PACK
      2. ./AppPckMastSynch.sh
  4. Once the above steps are done, create the following EAR/WAR file and redeploy this new WAR on the web application server. See Oracle Financial Services Behavior Detection Applications Pack Installation Guide Release 8.1.2.0.0 for steps related to deployment.
    1. JSONToTablePersistenceUtility
    2. TabletoJSONService
    3. InitiateOnboardingService
    4. GenerateCaseInputService

      Note:

      For using Websphere Server, remove all the jackson* Jars from the following folder before creating OFSAAI EAR/WAR file:

      $FIC_HOME/ficweb/webroot/externalib/WEB-INF/lib

  5. If RAOR deployment is required, navigate to aFIC_HOME/raor_processing and run ./ant.sh and deploy the RAOR war file.
  6. To add a new task in KYC daily batch:
    1. Log in as an Admin user.
    2. Navigate to the Common Tasks section, select the Rule Run Framework, and then select Process.
    3. Select IPEPopulateIP from the table list and click Edit.
    4. Click Component in the new window.
    5. Expand Transformation Rules from Available Components.
    6. Click Database Functions-Transformations and expand it.
    7. Move POPULATE_CUST_PROCESSING_IP to the right into Tasks in ROOT.
    8. In the right box, move the new task POPULATE_CUST_PROCESSING_IP after POPULATE_IP_KYC.
    9. Click OK.
    10. Click Precedence.
    11. Select POPULATE_CUST_PROCESSING_IP from the drop down.
    12. Select POPULATE_IP_KYC in the Available Precedence object and move it to the right.
    13. Click OK and Save.
  7. To add a new task in KYC daily batch:
    1. Log in as an Admin user.
    2. Navigate to the Common Tasks section, select the Rule Run Framework, and then select Process.
    3. Select IPEEndBatchProcess from the table list and click Edit.
    4. Click Component in the new window.
    5. Expand Transformation Rules from Available Components.
    6. Click Database Functions-Transformations and expand it.
    7. Move F_TRUNC_RTI_ASSMNT_TABLES to the right into Tasks in ROOT.
    8. Click OK and Save.
  8. Execute these following post installation scripts.
    1. If KYC and ECM are in the same DB, execute the following scripts:
      1. create or replace synonym FCC_PARTY_PARTY_RLSHP_LOOKUP_ECM for &&case_schema_owner..FCC_PARTY_PARTY_RLSHP_LOOKUP;
      2. create or replace synonym FCC_PARTY_PARTY_RLSHP_ECM for &&case_schema_owner..FCC_PARTY_PARTY_RLSHP;
      3. create or replace synonym FCC_PARTY_PARTY_RLSHP_EVNT_ECM for &&case_schema_owner..FCC_PARTY_PARTY_RLSHP_EVNT;
      4. create or replace synonym CM_BD_PARTY_PARTY_RLSHP_SKEY_ECM for &&case_schema_owner..CM_BD_PARTY_PARTY_RLSHP_SKEY;
      5. create or replace synonym kdd_case_party_party_rlshp_ECM for &&case_schema_owner..kdd_case_party_party_rlshp;
      6. create or replace synonym FCC_CASE_PRIORITY_ECM for &&case_schema_owner..FCC_CASE_PRIORITY;
      7. create or replace synonym KDD_CASETYPE_PARAM_CONFIG_ECM for &&case_schema_owner..KDD_CASETYPE_PARAM_CONFIG;
      8. create or replace synonym KDD_CAL_ECMfor &&case_schema_owner..KDD_CAL;
      9. create or replace synonym KDD_CASE_INVOLVED_PARTY_LINK_ECM for &&case_schema_owner..KDD_CASE_INVOLVED_PARTY_LINK;
      10. create or replace synonym KDD_CASE_INVOLVED_PARTY_DETAIL_ECM for &&case_schema_owner..KDD_CASE_INVOLVED_PARTY_DETAIL;
    2. If KYC and ECM are in different DBs, execute the following scripts:
      1. create or replace synonym FCC_PARTY_PARTY_RLSHP_LOOKUP_ECM for FCC_PARTY_PARTY_RLSHP_LOOKUP@AMDBLINK;
      2. create or replace synonym FCC_PARTY_PARTY_RLSHP_ECM for FCC_PARTY_PARTY_RLSHP@AMDBLINK;
      3. create or replace synonym FCC_PARTY_PARTY_RLSHP_EVNT_ECM for FCC_PARTY_PARTY_RLSHP_EVNT@AMDBLINK;
      4. create or replace synonym CM_BD_PARTY_PARTY_RLSHP_SKEY_ECM for CM_BD_PARTY_PARTY_RLSHP_SKEY@AMDBLINK;
      5. create or replace synonym kdd_case_party_party_rlshp_ECM for kdd_case_party_party_rlshp@AMDBLINK;
      6. create or replace synonym FCC_CASE_PRIORITY_ECM for FCC_CASE_PRIORITY@AMDBLINK;
      7. create or replace synonym KDD_CASETYPE_PARAM_CONFIG_ECM for KDD_CASETYPE_PARAM_CONFIG@AMDBLINK;
      8. create or replace synonym KDD_CAL_ECM for KDD_CAL@AMDBLINK;
      9. create or replace synonym KDD_CASE_INVOLVED_PARTY_LINK_ECM for KDD_CASE_INVOLVED_PARTY_LINK@AMDBLINK;
      10. create or replace synonym KDD_CASE_INVOLVED_PARTY_DETAIL_ECM for KDD_CASE_INVOLVED_PARTY_DETAIL@AMDBLINK;
  9. Enter the following values while executing the IPEKYCRun daily batch in the Parameters option.
    1. [MODELID]=PROD
    2. [VERSION]=0
    3. [APP_ID]=OFS_KYC
  10. To add the GathrStats_PRCSNG_TABLES task in the KYC daily batch, follow these steps:
    1. Navigate to Common Tasks > Rule Run Framework > Process as an Admin user.
    2. Select WLS_Process from the table list and click Edit.
    3. Click Component in the new window.
    4. Expand Transformation Rules from the Available Components.
    5. Click Database Functions-Transformations and expand it.
    6. Move GathrStats_PRCSNG_TABLES to the right into Tasks in ROOT.
    7. Click OK.
    8. Click Precedence.
    9. Select GathrStats_PRCSNG_TABLES from the drop down.
    10. Select UPDATE_WLS_STATUS in the Available Precedence object and move it to the right.
    11. Click OK and Save.
  11. To add the SIM_VERIFY_TABLES task in the KYC daily batch, follow these steps:
    1. Navigate to Common Tasks > Rule Run Framework > Process as an Admin user.
    2. Select IPEPREProcess from the table list and click Edit.
    3. Click Component in the new window.
    4. Expand Transformation Rules from the Available Components.
    5. Click DatabaseFunctions-Transformations and expand it.
    6. Move SIM_VERIFY_TABLES to the right into Tasks in ROOT.
    7. Move SIM_VERIFY_TABLES to the top to make it task 1.
    8. Click OK.
    9. Click Precedence.
    10. Select Customer from the dropdown.
    11. Select SIM_VERIFY_TABLESin the Available Precedence object and move it to the right.
    12. Click OK and Save.
  12. To add the PopulateCustForAccReReview task in the KYC daily batch, follow these steps:
    1. Navigate to Common Tasks > Rule Run Framework > Process as an Admin user.
    2. Select IPEPREProcess from the table list and click Edit.
    3. Click Component in the new window.
    4. Expand Transformation Rules from the Available Components.
    5. Click Database Functions-Transformations and expand it.
    6. Move PopulateCustForAccReReview to the right into Tasks in ROOT.
    7. Move PopulateCustForAccReReview before Customer.
    8. Click OK.
    9. Click Precedence.
    10. Select Customer from the dropdown.
    11. Select PopulateCustForAccReReviewin the Available Precedence object and move it to the right.
    12. Click OK and Save.
  13. To add the UpdProcessedAccReReviewCust task in the KYC daily batch, follow these steps:
    1. Navigate to Common Tasks > Rule Run Framework > Process as an Admin user.
    2. Select IPEEndBatchProcess from the table list and click Edit.
    3. Click Component in the new window.
    4. Expand Transformation Rules from the Available Components.
    5. Click Database Functions-Transformations and expand it.
    6. Move UpdProcessedAccReReviewCust to the right into Tasks in ROOT.
    7. Click OK and Save.
  14. Execute the following scripts in Compliance Studio schema:

    insert into mmg_app_menu_mapping(V_APP_ID,V_MENU_CODE) select 'KYC',V_MENU_CODE from MMG_MENU;

  15. Execute the following scripts in Behavior Detection schema:
    1. update aai_setup_props set V_PROP_VALUE = 'TRUE' where V_PROP_NAME = 'REFERRER-POLICY-ENABLED';
    2. update aai_setup_props set V_PROP_VALUE = '##Studio Server IP:Port##/ ' where V_PROP_NAME = 'ALLOWED-REFERRER-URLS';

      For example:

      update aai_setup_props set V_PROP_VALUE = 'http://10.34.110.111:8001/' where V_PROP_NAME = 'ALLOWED-REFERRER-URLS';

      Note:

      You must enter / after the port number.
    3. Update aai_validator_expression SET v_reg_expression = '[A-Za-z0- 9_.]*' WHERE V_EXP_TYPE_CODE = 'data_source_exp' AND v_reg_expression = '[A-Za-z0-9_]*';
  16. Replace the placeholders and execute following script in Atomic schema:

    ##JRSDCN_CD## -- Replace with Jurisdiction codes.

    ##LOOK_BACK_PERIOD##-- No of previous days to review the customers.

    1. Merge into APPLN_PARAMS T using:
      (SELECT 'KYC_SAR_ACTN_CTGRY' V_PARAM_IDENTIFIER, 'KYC' V_PARAM_CATEGORY,
'This parameter lists the actions on which a Risk Assessment on a
Customer has to be created. When a user performs any actions on an alert
which would result in creating a regulatory report for a customer, Risk
Assessments would be created based on the look up period which is
configurable. Note: The actions to be added here needs to be picked up
from kdd_activity_type_cd.actvy_type_cd where actvy_cat_cd = ''RR'' .
Those actions which are suffixed with R in the action code should not be
added as these are Recommendations which requires approval'
V_PARAM_DESC, 'Y' V_PARAM_VALUE, '' V_ATTRIBUTE1_CODE, 'Application -
Business' V_PARAM_CLASSIFICATION, 'CCFSAR' V_ATTRIBUTE1_VALUE, ''
V_ATTRIBUTE1_DESC, '' V_ATTRIBUTE2_CODE, '' V_ATTRIBUTE2_VALUE, ''
V_ATTRIBUTE2_DESC, '' V_ATTRIBUTE3_CODE, '' V_ATTRIBUTE3_VALUE, ''
V_ATTRIBUTE3_DESC, '' V_ATTRIBUTE4_CODE, '' V_ATTRIBUTE4_VALUE, ''
V_ATTRIBUTE4_DESC, '' V_ATTRIBUTE5_CODE, '' V_ATTRIBUTE5_VALUE, ''
V_ATTRIBUTE5_DESC, '' V_ATTRIBUTE6_CODE, '' V_ATTRIBUTE6_VALUE, ''
V_ATTRIBUTE6_DESC, '' V_ATTRIBUTE7_CODE, '' V_ATTRIBUTE7_VALUE, ''
V_ATTRIBUTE7_DESC, '' V_ATTRIBUTE8_CODE, '' V_ATTRIBUTE8_VALUE, ''
V_ATTRIBUTE8_DESC, '' V_ATTRIBUTE9_CODE, '' V_ATTRIBUTE9_VALUE, ''
V_ATTRIBUTE9_DESC, '' V_ATTRIBUTE10_CODE, '' V_ATTRIBUTE10_VALUE, ''
V_ATTRIBUTE10_DESC, '' V_ATTRIBUTE11_CODE, '' V_ATTRIBUTE11_VALUE, ''
V_ATTRIBUTE11_DESC, '' V_ATTRIBUTE12_CODE, '' V_ATTRIBUTE12_VALUE, ''
V_ATTRIBUTE12_DESC, '' V_ATTRIBUTE13_CODE, '' V_ATTRIBUTE13_VALUE, ''
V_ATTRIBUTE13_DESC, '' V_ATTRIBUTE14_CODE, '' V_ATTRIBUTE14_VALUE, ''
V_ATTRIBUTE14_DESC, '' V_ATTRIBUTE15_CODE, '' V_ATTRIBUTE15_VALUE, ''
V_ATTRIBUTE15_DESC, 'system' V_CREATED_BY, '' V_PARAM_COMMENTS, ''
D_CREATED_DATE, '' V_ATTRIBUTE1_COMMENTS, '' V_MODIFIED_BY, ''
V_ATTRIBUTE2_COMMENTS, '' D_MODIFIED_DATE, '' V_ATTRIBUTE3_COMMENTS, ''
V_AUTHORIZED_BY, '' V_ATTRIBUTE4_COMMENTS, '' D_AUTHORIZED_DATE, ''
V_ATTRIBUTE5_COMMENTS, '' F_LATEST_RECORD_IDENTIFIER, ''
V_ATTRIBUTE6_COMMENTS, '' D_PARAM_DATE, '' V_ATTRIBUTE7_COMMENTS, ''
FIC_MIS_DATE, '' V_ATTRIBUTE8_COMMENTS, '' V_ATTRIBUTE9_COMMENTS, ''
V_ATTRIBUTE10_COMMENTS, '' V_ATTRIBUTE11_COMMENTS, ''
V_ATTRIBUTE12_COMMENTS, '' V_ATTRIBUTE13_COMMENTS, ''
V_ATTRIBUTE14_COMMENTS, 'Risk Parameter' V_ATTRIBUTE15_COMMENTS,
'Regulatory Report Actions' V_PARAM_NAME, '##JRSDCN_CD##' V_JRSDCN_CD,
'' N_CNTRY_KEY FROM DUAL) S
ON ( T.V_PARAM_IDENTIFIER = S.V_PARAM_IDENTIFIER AND T.V_JRSDCN_CD =
S.V_JRSDCN_CD)
WHEN MATCHED THEN UPDATE SET T.V_PARAM_CATEGORY = S.V_PARAM_CATEGORY,
T.V_PARAM_DESC = S.V_PARAM_DESC, T.V_PARAM_VALUE = S.V_PARAM_VALUE,
T.V_ATTRIBUTE1_CODE = S.V_ATTRIBUTE1_CODE, T.V_PARAM_CLASSIFICATION =
S.V_PARAM_CLASSIFICATION, T.V_ATTRIBUTE1_VALUE = S.V_ATTRIBUTE1_VALUE,
T.V_ATTRIBUTE1_DESC = S.V_ATTRIBUTE1_DESC, T.V_ATTRIBUTE2_CODE =
S.V_ATTRIBUTE2_CODE, T.V_ATTRIBUTE2_VALUE = S.V_ATTRIBUTE2_VALUE,
T.V_ATTRIBUTE2_DESC = S.V_ATTRIBUTE2_DESC, T.V_ATTRIBUTE3_CODE =
S.V_ATTRIBUTE3_CODE, T.V_ATTRIBUTE3_VALUE = S.V_ATTRIBUTE3_VALUE,
T.V_ATTRIBUTE3_DESC = S.V_ATTRIBUTE3_DESC, T.V_ATTRIBUTE4_CODE =
S.V_ATTRIBUTE4_CODE, T.V_ATTRIBUTE4_VALUE = S.V_ATTRIBUTE4_VALUE,
T.V_ATTRIBUTE4_DESC = S.V_ATTRIBUTE4_DESC, T.V_ATTRIBUTE5_CODE =
S.V_ATTRIBUTE5_CODE, T.V_ATTRIBUTE5_VALUE = S.V_ATTRIBUTE5_VALUE,
T.V_ATTRIBUTE5_DESC = S.V_ATTRIBUTE5_DESC, T.V_ATTRIBUTE6_CODE =
S.V_ATTRIBUTE6_CODE, T.V_ATTRIBUTE6_VALUE = S.V_ATTRIBUTE6_VALUE,
T.V_ATTRIBUTE6_DESC = S.V_ATTRIBUTE6_DESC, T.V_ATTRIBUTE7_CODE =
S.V_ATTRIBUTE7_CODE, T.V_ATTRIBUTE7_VALUE = S.V_ATTRIBUTE7_VALUE,
T.V_ATTRIBUTE7_DESC = S.V_ATTRIBUTE7_DESC, T.V_ATTRIBUTE8_CODE =
S.V_ATTRIBUTE8_CODE, T.V_ATTRIBUTE8_VALUE = S.V_ATTRIBUTE8_VALUE,
T.V_ATTRIBUTE8_DESC = S.V_ATTRIBUTE8_DESC, T.V_ATTRIBUTE9_CODE =
S.V_ATTRIBUTE9_CODE, T.V_ATTRIBUTE9_VALUE = S.V_ATTRIBUTE9_VALUE,
T.V_ATTRIBUTE9_DESC = S.V_ATTRIBUTE9_DESC, T.V_ATTRIBUTE10_CODE =
S.V_ATTRIBUTE10_CODE, T.V_ATTRIBUTE10_VALUE = S.V_ATTRIBUTE10_VALUE,
T.V_ATTRIBUTE10_DESC = S.V_ATTRIBUTE10_DESC, T.V_ATTRIBUTE11_CODE =
S.V_ATTRIBUTE11_CODE, T.V_ATTRIBUTE11_VALUE = S.V_ATTRIBUTE11_VALUE,
T.V_ATTRIBUTE11_DESC = S.V_ATTRIBUTE11_DESC, T.V_ATTRIBUTE12_CODE =
S.V_ATTRIBUTE12_CODE, T.V_ATTRIBUTE12_VALUE = S.V_ATTRIBUTE12_VALUE,
T.V_ATTRIBUTE12_DESC = S.V_ATTRIBUTE12_DESC, T.V_ATTRIBUTE13_CODE =
S.V_ATTRIBUTE13_CODE, T.V_ATTRIBUTE13_VALUE = S.V_ATTRIBUTE13_VALUE,
T.V_ATTRIBUTE13_DESC = S.V_ATTRIBUTE13_DESC, T.V_ATTRIBUTE14_CODE =
S.V_ATTRIBUTE14_CODE, T.V_ATTRIBUTE14_VALUE = S.V_ATTRIBUTE14_VALUE,
T.V_ATTRIBUTE14_DESC = S.V_ATTRIBUTE14_DESC, T.V_ATTRIBUTE15_CODE =
S.V_ATTRIBUTE15_CODE, T.V_ATTRIBUTE15_VALUE = S.V_ATTRIBUTE15_VALUE,
T.V_ATTRIBUTE15_DESC = S.V_ATTRIBUTE15_DESC, T.V_CREATED_BY =
S.V_CREATED_BY, T.V_PARAM_COMMENTS = S.V_PARAM_COMMENTS,
T.D_CREATED_DATE = S.D_CREATED_DATE, T.V_ATTRIBUTE1_COMMENTS =
S.V_ATTRIBUTE1_COMMENTS, T.V_MODIFIED_BY = S.V_MODIFIED_BY,
T.V_ATTRIBUTE2_COMMENTS = S.V_ATTRIBUTE2_COMMENTS, T.D_MODIFIED_DATE =
S.D_MODIFIED_DATE, T.V_ATTRIBUTE3_COMMENTS = S.V_ATTRIBUTE3_COMMENTS,
T.V_AUTHORIZED_BY = S.V_AUTHORIZED_BY, T.V_ATTRIBUTE4_COMMENTS =
S.V_ATTRIBUTE4_COMMENTS, T.D_AUTHORIZED_DATE = S.D_AUTHORIZED_DATE,
T.V_ATTRIBUTE5_COMMENTS = S.V_ATTRIBUTE5_COMMENTS,
T.F_LATEST_RECORD_IDENTIFIER = S.F_LATEST_RECORD_IDENTIFIER,
T.V_ATTRIBUTE6_COMMENTS = S.V_ATTRIBUTE6_COMMENTS, T.D_PARAM_DATE =
S.D_PARAM_DATE, T.V_ATTRIBUTE7_COMMENTS = S.V_ATTRIBUTE7_COMMENTS,
T.FIC_MIS_DATE = S.FIC_MIS_DATE, T.V_ATTRIBUTE8_COMMENTS =
S.V_ATTRIBUTE8_COMMENTS, T.V_ATTRIBUTE9_COMMENTS =
S.V_ATTRIBUTE9_COMMENTS, T.V_ATTRIBUTE10_COMMENTS =
S.V_ATTRIBUTE10_COMMENTS, T.V_ATTRIBUTE11_COMMENTS =
S.V_ATTRIBUTE11_COMMENTS, T.V_ATTRIBUTE12_COMMENTS =
S.V_ATTRIBUTE12_COMMENTS, T.V_ATTRIBUTE13_COMMENTS =
S.V_ATTRIBUTE13_COMMENTS, T.V_ATTRIBUTE14_COMMENTS =
S.V_ATTRIBUTE14_COMMENTS, T.V_ATTRIBUTE15_COMMENTS =
S.V_ATTRIBUTE15_COMMENTS, T.V_PARAM_NAME = S.V_PARAM_NAME, T.N_CNTRY_KEY
= S.N_CNTRY_KEY
WHEN NOT MATCHED THEN INSERT
(V_PARAM_IDENTIFIER,V_PARAM_CATEGORY,V_PARAM_DESC,V_PARAM_VALUE,V_ATTRIB
UTE1_CODE,V_PARAM_CLASSIFICATION,V_ATTRIBUTE1_VALUE,V_ATTRIBUTE1_DESC,V_
ATTRIBUTE2_CODE,V_ATTRIBUTE2_VALUE,V_ATTRIBUTE2_DESC,V_ATTRIBUTE3_CODE,V
_ATTRIBUTE3_VALUE,V_ATTRIBUTE3_DESC,V_ATTRIBUTE4_CODE,V_ATTRIBUTE4_VALUE
,V_ATTRIBUTE4_DESC,V_ATTRIBUTE5_CODE,V_ATTRIBUTE5_VALUE,V_ATTRIBUTE5_DES
C,V_ATTRIBUTE6_CODE,V_ATTRIBUTE6_VALUE,V_ATTRIBUTE6_DESC,V_ATTRIBUTE7_CO
DE,V_ATTRIBUTE7_VALUE,V_ATTRIBUTE7_DESC,V_ATTRIBUTE8_CODE,V_ATTRIBUTE8_V
ALUE,V_ATTRIBUTE8_DESC,V_ATTRIBUTE9_CODE,V_ATTRIBUTE9_VALUE,V_ATTRIBUTE9
_DESC,V_ATTRIBUTE10_CODE,V_ATTRIBUTE10_VALUE,V_ATTRIBUTE10_DESC,V_ATTRIB
UTE11_CODE,V_ATTRIBUTE11_VALUE,V_ATTRIBUTE11_DESC,V_ATTRIBUTE12_CODE,V_A
TTRIBUTE12_VALUE,V_ATTRIBUTE12_DESC,V_ATTRIBUTE13_CODE,V_ATTRIBUTE13_VAL
UE,V_ATTRIBUTE13_DESC,V_ATTRIBUTE14_CODE,V_ATTRIBUTE14_VALUE,V_ATTRIBUTE
14_DESC,V_ATTRIBUTE15_CODE,V_ATTRIBUTE15_VALUE,V_ATTRIBUTE15_DESC,V_CREA
TED_BY,V_PARAM_COMMENTS,D_CREATED_DATE,V_ATTRIBUTE1_COMMENTS,V_MODIFIED_
BY,V_ATTRIBUTE2_COMMENTS,D_MODIFIED_DATE,V_ATTRIBUTE3_COMMENTS,V_AUTHORI
ZED_BY,V_ATTRIBUTE4_COMMENTS,D_AUTHORIZED_DATE,V_ATTRIBUTE5_COMMENTS,F_L
ATEST_RECORD_IDENTIFIER,V_ATTRIBUTE6_COMMENTS,D_PARAM_DATE,V_ATTRIBUTE7_
COMMENTS,FIC_MIS_DATE,V_ATTRIBUTE8_COMMENTS,V_ATTRIBUTE9_COMMENTS,V_ATTR
IBUTE10_COMMENTS,V_ATTRIBUTE11_COMMENTS,V_ATTRIBUTE12_COMMENTS,V_ATTRIBU
TE13_COMMENTS,V_ATTRIBUTE14_COMMENTS,V_ATTRIBUTE15_COMMENTS,V_PARAM_NAME
,V_JRSDCN_CD,N_CNTRY_KEY)
VALUES
(S.V_PARAM_IDENTIFIER,S.V_PARAM_CATEGORY,S.V_PARAM_DESC,S.V_PARAM_VALUE,
S.V_ATTRIBUTE1_CODE,S.V_PARAM_CLASSIFICATION,S.V_ATTRIBUTE1_VALUE,S.V_AT
TRIBUTE1_DESC,S.V_ATTRIBUTE2_CODE,S.V_ATTRIBUTE2_VALUE,S.V_ATTRIBUTE2_DE
SC,S.V_ATTRIBUTE3_CODE,S.V_ATTRIBUTE3_VALUE,S.V_ATTRIBUTE3_DESC,S.V_ATTR
IBUTE4_CODE,S.V_ATTRIBUTE4_VALUE,S.V_ATTRIBUTE4_DESC,S.V_ATTRIBUTE5_CODE
,S.V_ATTRIBUTE5_VALUE,S.V_ATTRIBUTE5_DESC,S.V_ATTRIBUTE6_CODE,S.V_ATTRIB
UTE6_VALUE,S.V_ATTRIBUTE6_DESC,S.V_ATTRIBUTE7_CODE,S.V_ATTRIBUTE7_VALUE,
S.V_ATTRIBUTE7_DESC,S.V_ATTRIBUTE8_CODE,S.V_ATTRIBUTE8_VALUE,S.V_ATTRIBU
TE8_DESC,S.V_ATTRIBUTE9_CODE,S.V_ATTRIBUTE9_VALUE,S.V_ATTRIBUTE9_DESC,S.
V_ATTRIBUTE10_CODE,S.V_ATTRIBUTE10_VALUE,S.V_ATTRIBUTE10_DESC,S.V_ATTRIB
UTE11_CODE,S.V_ATTRIBUTE11_VALUE,S.V_ATTRIBUTE11_DESC,S.V_ATTRIBUTE12_CO
DE,S.V_ATTRIBUTE12_VALUE,S.V_ATTRIBUTE12_DESC,S.V_ATTRIBUTE13_CODE,S.V_A
TTRIBUTE13_VALUE,S.V_ATTRIBUTE13_DESC,S.V_ATTRIBUTE14_CODE,S.V_ATTRIBUTE
14_VALUE,S.V_ATTRIBUTE14_DESC,S.V_ATTRIBUTE15_CODE,S.V_ATTRIBUTE15_VALUE
,S.V_ATTRIBUTE15_DESC,S.V_CREATED_BY,S.V_PARAM_COMMENTS,S.D_CREATED_DATE
,S.V_ATTRIBUTE1_COMMENTS,S.V_MODIFIED_BY,S.V_ATTRIBUTE2_COMMENTS,S.D_MOD
IFIED_DATE,S.V_ATTRIBUTE3_COMMENTS,S.V_AUTHORIZED_BY,S.V_ATTRIBUTE4_COMM
ENTS,S.D_AUTHORIZED_DATE,S.V_ATTRIBUTE5_COMMENTS,S.F_LATEST_RECORD_IDENT
IFIER,S.V_ATTRIBUTE6_COMMENTS,S.D_PARAM_DATE,S.V_ATTRIBUTE7_COMMENTS,S.F
IC_MIS_DATE,S.V_ATTRIBUTE8_COMMENTS,S.V_ATTRIBUTE9_COMMENTS,S.V_ATTRIBUT
E10_COMMENTS,S.V_ATTRIBUTE11_COMMENTS,S.V_ATTRIBUTE12_COMMENTS,S.V_ATTRIBUTE13_COMMENTS,
S.V_ATTRIBUTE14_COMMENTS,S.V_ATTRIBUTE15_COMMENTS,S.V_PARAM_NAME,S.V_JRSDCN_CD,S.N_CNTRY_KEY);
    2. Merge into APPLN_REREVIEW_PARAMS T using:
      (SELECT '1070' N_RULE_ID, '##LOOK_BACK_PERIOD##' N_LOOK_BACK_PERIOD, ''
N_PRIORITY, '' N_COUNT_OF_ALERTS, '' N_ALERT_SCORE, 'Regulatory Report
action/s on a Customer Case' V_RULE_NAME, 'Reviews a customer who has
been impacted due to a change in case status to be closed' V_RULE_DESC,
'Customer' V_RULE_FOCUS, '' N_RULE_FRQUNCY, 'AR' V_RULE_TYPE, ''
N_COUNT_OF_CHANGES, '' V_CREATED_BY, '' F_ALERT_FOCUSED_TRIGR_FLAG, ''
V_COMMENTS, '' D_CREATED_DATE, '' V_MODIFIED_BY, '' D_MODIFIED_DATE, ''
V_AUTHORIZED_BY, '' D_AUTHORIZED_DATE, '' F_LATEST_RECORD_IDENTIFIER, ''
FIC_MIS_DATE, 'Y' F_ENABLE, 'Reviews a customer who has been impacted due
to a change in case status to be closed' V_RULE_DISPY_NAME,
'##JRSDCN_CD##' V_JRSDCN_CD, '' V_CUST_TYPE_CD, '' V_STATUS, ''
V_ACTION, '' V_COMMENT, '' N_SRC_CNTRY_KEY, '0' N_RULE_SCORE, ''
V_PARAM_RULE_CODE FROM DUAL) S
ON ( T.N_RULE_ID = S.N_RULE_ID AND T.V_JRSDCN_CD = S.V_JRSDCN_CD )
WHEN MATCHED THEN UPDATE SET T.N_LOOK_BACK_PERIOD =
S.N_LOOK_BACK_PERIOD, T.N_PRIORITY = S.N_PRIORITY, T.N_COUNT_OF_ALERTS =
S.N_COUNT_OF_ALERTS, T.N_ALERT_SCORE = S.N_ALERT_SCORE, T.V_RULE_NAME =
S.V_RULE_NAME, T.V_RULE_DESC = S.V_RULE_DESC, T.V_RULE_FOCUS =
S.V_RULE_FOCUS, T.N_RULE_FRQUNCY = S.N_RULE_FRQUNCY, T.V_RULE_TYPE =
S.V_RULE_TYPE, T.N_COUNT_OF_CHANGES = S.N_COUNT_OF_CHANGES,
T.V_CREATED_BY = S.V_CREATED_BY, T.F_ALERT_FOCUSED_TRIGR_FLAG =
S.F_ALERT_FOCUSED_TRIGR_FLAG, T.V_COMMENTS = S.V_COMMENTS,
T.D_CREATED_DATE = S.D_CREATED_DATE, T.V_MODIFIED_BY = S.V_MODIFIED_BY,
T.D_MODIFIED_DATE = S.D_MODIFIED_DATE, T.V_AUTHORIZED_BY =
S.V_AUTHORIZED_BY, T.D_AUTHORIZED_DATE = S.D_AUTHORIZED_DATE,
T.F_LATEST_RECORD_IDENTIFIER = S.F_LATEST_RECORD_IDENTIFIER,
T.FIC_MIS_DATE = S.FIC_MIS_DATE, T.F_ENABLE = S.F_ENABLE,
T.V_RULE_DISPY_NAME = S.V_RULE_DISPY_NAME, T.V_CUST_TYPE_CD =
S.V_CUST_TYPE_CD, T.V_STATUS = S.V_STATUS, T.V_ACTION = S.V_ACTION,
T.V_COMMENT = S.V_COMMENT, T.N_SRC_CNTRY_KEY = S.N_SRC_CNTRY_KEY,
T.N_RULE_SCORE = S.N_RULE_SCORE, T.V_PARAM_RULE_CODE =
S.V_PARAM_RULE_CODE
WHEN NOT MATCHED THEN INSERT
(N_RULE_ID,N_LOOK_BACK_PERIOD,N_PRIORITY,N_COUNT_OF_ALERTS,N_ALERT_SCORE
,V_RULE_NAME,V_RULE_DESC,V_RULE_FOCUS,N_RULE_FRQUNCY,V_RULE_TYPE,N_COUNT
_OF_CHANGES,V_CREATED_BY,F_ALERT_FOCUSED_TRIGR_FLAG,V_COMMENTS,D_CREATED
_DATE,V_MODIFIED_BY,D_MODIFIED_DATE,V_AUTHORIZED_BY,D_AUTHORIZED_DATE,F_
LATEST_RECORD_IDENTIFIER,FIC_MIS_DATE,F_ENABLE,V_RULE_DISPY_NAME,V_JRSDC
N_CD,V_CUST_TYPE_CD,V_STATUS,V_ACTION,V_COMMENT,N_SRC_CNTRY_KEY,N_RULE_S
CORE,V_PARAM_RULE_CODE)
VALUES
(S.N_RULE_ID,S.N_LOOK_BACK_PERIOD,S.N_PRIORITY,S.N_COUNT_OF_ALERTS,S.N_A
LERT_SCORE,S.V_RULE_NAME,S.V_RULE_DESC,S.V_RULE_FOCUS,S.N_RULE_FRQUNCY,S
.V_RULE_TYPE,S.N_COUNT_OF_CHANGES,S.V_CREATED_BY,S.F_ALERT_FOCUSED_TRIGR
_FLAG,S.V_COMMENTS,S.D_CREATED_DATE,S.V_MODIFIED_BY,S.D_MODIFIED_DATE,S.V_AUTHORIZED_BY,
S.D_AUTHORIZED_DATE,S.F_LATEST_RECORD_IDENTIFIER,S.FIC_M
IS_DATE,S.F_ENABLE,S.V_RULE_DISPY_NAME,S.V_JRSDCN_CD,S.V_CUST_TYPE_CD,S.
V_STATUS,S.V_ACTION,S.V_COMMENT,S.N_SRC_CNTRY_KEY,S.N_RULE_SCORE,S.V_PARAM_RULE_CODE);