- Extensibility Guide
- Case Series Extensibility
- Create New Merge Option
Create New Merge Option
The following are the steps to create a new merge option:
- Connect to Oracle Argus Insight Schema with APR_MART (Mart user).
- 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
- 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.
- Declaration for package
specification
Parent topic: Case Series Extensibility