Create New Merge Option

The following are the steps to create a new merge option:

1. Connect to Oracle Argus Insight Schema with APR_MART (Mart user).
2. Make new entry in cfg_merge_type_master, and enter all the following mandatory fields:
   • TYPE_ID = 4 (next available number)
   • Display_Name = '<New Option Name>'
   • Target_Function = Name of function that contains the complete logic of the merge operation for Intersect, Union, and Minus.

     This function must be of public type.

     For example: F_MERGE_NEW_OPTION

     Make sure the Target_Function is accessible from the schema AM_APP_OWNER.

   • Enabled = 1
3. Create new Target_Function for Merge in the package pkg_sm_case_series by using the following template:
   • Declaration for package specification

     FUNCTION F_MERGE_NEW_OPTION (
         pi_merge_seriesid IN NUMBER,
         pi_left_seriesid  IN NUMBER,
         pi_right_seriesid IN NUMBER,
         pi_merge_type     IN NUMBER,
         pi_user_id        IN NUMBER)
         RETURN VARCHAR2 ;
     

   • Function for package body

     FUNCTION F_MERGE_NEW_OPTION (
         pi_merge_seriesid IN NUMBER,
         pi_left_seriesid  IN NUMBER,
         pi_right_seriesid IN NUMBER,
         pi_merge_type     IN NUMBER,
         pi_user_id        IN NUMBER)
         RETURN VARCHAR2 IS
         ln_set_env_var NUMBER;
         PRAGMA AUTONOMOUS_TRANSACTION;
       BEGIN
         
     ---------------------------------------------------------------------------
         -- for minus, choose security information from left case series --
         -- study_unblind_ok code broken formula values(for union choose max value and for intersection choose least value) --
         --                                         --
         -- null    null       20             20    --
               -- 1       1,2,3,4    10+code_broken 10,11,12,13,14    --                                                               
             -- 0       1,2,3,5    code_broken 0,1,2,3,4                  --                                                             ---------------------------------------------------------------------------
         -- SET USER SECURITY    ln_set_env_var := pkg_sm_data_security.f_set_env_var (pi_user_id);    IF pi_merge_type = 1 THEN   --UNION
           INSERT INTO case_detail
                       (enterprise_id, seriesid, case_num, case_id, study_
     unblind_ok, code_broken, effective_start_date)
             SELECT distinct enterprise_id, pi_merge_seriesid, case_num, case_
     id, study_unblind_ok, code_broken, effective_start_date  
                        FROM ( <your Selection Logic> );
         ELSIF pi_merge_type = 2 THEN   -- INTERSECT
           INSERT INTO case_detail                
                              (enterprise_id, 
     seriesid, case_num, case_id, study_unblind_ok, code_broken, effective_start_date
            SELECT DISTINCT enterprise_id, pi_merge_seriesid, case_num, case_id, 
     study_unblind_ok, code_broken, effective_start_date  
                        FROM ( <your Selection Logic> );
         ELSIF pi_merge_type = 3 THEN   --MINUS
           INSERT INTO case_detail
                       (enterprise_id, seriesid, case_num, case_id, study_unblind_ok, code_broken, effective_start_date)
             SELECT DISTINCT enterprise_id, pi_merge_seriesid seriesid, case_num, case_id, study_unblind_ok, code_broken, effective_start_date 
                        FROM ( <your Selection Logic> ); 
         END IF;
         COMMIT;
         RETURN 1;
       EXCEPTION
         WHEN OTHERS THEN
           ROLLBACK;
           RETURN SUBSTR (SQLERRM, 1, 3999);
       END F_MERGE_NEW_OPTION;
     

   • To create <selection logic> refer to the following existing functions:
     • f_merge_curr_data
     • f_merge_latest_rev
     • f_merge_all_rev

   Table 2-1 Template Details

   Parameter	Description
   pi_merge_seriesid	Defines the output case series ID.
   pi_left_seriesid	Defines the left side selected case series.
   pi_right_seriesid	Defines the right side selected case series.
   pi_merge_type	Defines the flag to contain operation type. 1—Union 2—Intersect 3—Minus
   pi_user_id	Defines the User ID of the logged-in application user.

   Note:

   • Always use v_case_series to fetch complete data from pi_left_seriesid and pi_right_seriesid. For example:

     Select  enterprise_id, seriesid, case_num, case_id, study_unblind_ok, code_broken, effective_start_date from v_case_detail where seriesid = pi_left_seriesid 
     minus
     Select  enterprise_id, seriesid, case_num, case_id, study_unblind_ok, code_broken, effective_start_date from v_case_detail where seriesid = pi_right_seriesid
     

   • Make sure all the required parameters are available in the Target_Function, and in the same order as that of the template.