Go to primary content

Oracle® Argus Insight Extensibility Guide Release 8.1 E68411-01 |
| Contents | Index

Previous | Next |

# 3 Case Series Extensibility

You can extend the feature of merging the case series by customizing new operations or creating new merge options.

Argus Mart, by default, provides the following merge options:

- Current Data

- Latest revision

- All revisions

## 3.1 Creating New Merge Option

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

1. Connect to 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 3-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.