Custom Comparison

As part of the newly enhanced upon ICSR import Framework, a customization capability has been introduced in the Difference report building logic. As a result, a user can define their own custom logic over the OOTB difference report to control the comparison. The following flow chart depicts the functional flow for the Custom Comparison:

Since this comparison is a customizable logic, there can be several use cases.

The following is an example that describes some of the sample logics at a high level:

Note:

While defining the custom comparison logic over the esm_difference_report table, you can use the following Input parameters:

• PI_INCOMING_RPT_ID — Report ID of the Incoming File.
• PI_CASE_ID — Case ID of the case on which Follow-up is accepted.
• PI_CASE_RPT_ID — Report ID of the report generated on the current case data for comparison.
• PI_USER_ID — User ID of the current user.

For column descriptions, refer to the Table definition.

Example 5-1 User defined logic to not import any of the Reporter details in the system:

UPDATE esm_difference_report
SET imp_check = 0
WHERE (parent_element='PRIMARYSOURCE'
or dtd_element = 'PRIMARYSOURCE')
AND report_id1 = :PI_INCOMING_RPT_ID
AND user_id = :PI_USER_ID
AND REC_ACTION = 1

Example 5-2 User define logic to update the Reporter comparison to overwrite the OOTB comparison:

DECLARE
n_parentid_del VARCHAR2(1000 CHAR):='0';
n_parentid_ins VARCHAR2(1000 CHAR):='0';
n_parentid_delete VARCHAR2(1000 CHAR):='0';
30
N_CASE_ID number :=:PI_CASE_ID ;
N_USER_ID number := :PI_USER_ID;
N_RPT_ID1 NUMBER := :PI_INCOMING_RPT_ID;
N_RPT_ID2 NUMBER := :PI_CASE_RPT_ID ;
--Fetch all the Reporter that are marked to Insert as New Reporter during F-u
Cursor c1 is
select * from ESM_DIFFERENCE_REPORT where case_xref = N_CASE_ID and user_id = N_USER_ID and
PARENT_ELEMENT = 'PRIMARYSOURCE' and REC_ACTION = 3 and report_id2 = N_RPT_ID2;
BEGIN
FOR c_row in c1
LOOP
--Run the cursor on the reports that are to be inserted
if c_row.dtd_element = 'REPORTERGIVENAME' then
--This query run as custom check on the reporter recors to see if there exists a record to be deleted under the cutom condition
--If this qery fetches a record then it will Update the deletion record as and update record
select parent_pk into n_parentid_ins from ESM_DIFFERENCE_REPORT where case_xref = N_CASE_ID and
user_id = N_USER_ID and PARENT_ELEMENT = 'PRIMARYSOURCE' and REC_ACTION = 1 and report_id1 = N_RPT_ID1
and dtd_element = 'REPORTERGIVENAME';
n_parentid_del := c_row.parent_pk;
end if;
end loop;
IF n_parentid_del <> '0' Then
--Sample Update query to update the reporter record in Difference report Table
Update ESM_DIFFERENCE_REPORT EDR_INS
Set
EDR_INS.DIFF_VALUE2 = (SELECT EDR_DEL.DIFF_VALUE2 from ESM_DIFFERENCE_REPORT EDR_DEL where EDR_DEL.parent_pk = n_parentid_del AND EDR_INS.dtd_element = EDR_DEL.dtd_element AND EDR_INS.PARENT_ELEMENT = EDR_DEL.PARENT_ELEMENT),
EDR_INS.ID2 = (SELECT EDR_DEL.ID2 from ESM_DIFFERENCE_REPORT EDR_DEL where EDR_DEL.parent_pk = n_parentid_del AND EDR_INS.dtd_element = EDR_DEL.dtd_element AND EDR_INS.PARENT_ELEMENT = EDR_DEL.PARENT_ELEMENT),
EDR_INS.DECODED_VALUE2 = (SELECT EDR_DEL.DECODED_VALUE2 from ESM_DIFFERENCE_REPORT EDR_DEL where EDR_DEL.parent_pk = n_parentid_del AND EDR_INS.dtd_element = EDR_DEL.dtd_element AND EDR_INS.PARENT_ELEMENT = EDR_DEL.PARENT_ELEMENT),
EDR_INS.REC_ACTION = 2 ,
EDR_INS.IMP_CHECK = 1,
EDR_INS.DIFF_CODE1 = 0,
EDR_INS.DIFF_CODE2=0
where
EDR_INS.parent_pk = n_parentid_ins or seq_num = n_parentid_ins ;
Update ESM_DIFFERENCE_REPORT
Set REC_ACTION = 2 where seq_num = n_parentid_ins ;
31
END IF;
DELETE from ESM_DIFFERENCE_REPORT where parent_pk =n_parentid_del;
DELETE from ESM_DIFFERENCE_REPORT where seq_num =n_parentid_del;
END;

Example 5-3 Custom Comparison logic to forcefully match the Drug block by comparing the WHO Drug Codes:

DECLARE
n_parentid_del VARCHAR2(1000 CHAR):='0';
n_parentid_ins VARCHAR2(1000 CHAR):='0';
n_parentid_delete VARCHAR2(1000 CHAR):='0';
n_group_id_del number:=-1;
N_CASE_ID number := :PI_CASE_ID ;
N_USER_ID number := :PI_USER_ID;
N_RPT_ID1 NUMBER := :PI_INCOMING_RPT_ID;
N_RPT_ID2 NUMBER := :PI_CASE_RPT_ID ;
--Fetch all the DRUG's that are marked to be newly inserted by the Diffrence report Logic
Cursor c1 is
select * from ESM_DIFFERENCE_REPORT where case_xref = N_CASE_ID and user_id = N_USER_ID and
PARENT_ELEMENT = 'DRUG' and REC_ACTION = 1 and report_id1 = N_RPT_ID1;
--Fetch all the Repeater records under the Drug Node
cursor c2(groupid number) is
select * from ESM_DIFFERENCE_REPORT where data_element like 'G.k%' and parent = 1 and group1=groupid and case_xref = N_CASE_ID
and user_id = N_USER_ID and report_id1 = N_RPT_ID1;
--Fetch all the deatils each Repeater record under the Drug Node - Section Wise
cursor c3(in_parentid VARCHAR2(100), in_dtd_element VARCHAR2(100)) is
SELECT * from ESM_DIFFERENCE_REPORT EDR_DEL WHERE parent_pk = in_parentid and dtd_element = in_dtd_element;
BEGIN
FOR c_row in c1
LOOP
--Run the cursor on the Drug Record that are makred for insertion
if c_row.dtd_element = 'MEDICINALPRODUCT' then
--Execute a custom check to see if the MEDICINALPRODUCT tag contains the WHO or J Drug Code of the Product
--Then also treat it to be same product in the Difference Report
--General Guidance to be followed while updating the records
-- If the new Record matches with the already existing records : Update the record mark for Deletion
-- If the New Record Doesnot Matches with Any Existing Record : Update the New Entrant and link it to Exisitng Drug Node
-- Delete any unwanted records from Existing or the new Drug Node
BEGIN
32
select parent_pk, group1 into n_parentid_delete, n_group_id_del from ESM_DIFFERENCE_REPORT where case_xref = N_CASE_ID and
user_id = N_USER_ID and PARENT_ELEMENT = 'DRUG' and REC_ACTION = 3 and report_id2 = N_RPT_ID2
and dtd_element = 'MEDICINALPRODUCT' and dbms_lob.substr(DIFF_VALUE2) in (
SELECT ll.trade_name FROM LM_PRODUCT LP, lm_license ll, lm_lic_products llp
WHERE
lp.product_id = llp.product_id
and llp.license_id = ll.license_id
and (lp.drl_id = dbms_lob.substr(c_row.DIFF_VALUE1) or lp.drl_id_j = dbms_lob.substr(c_row.DIFF_VALUE1))
and ll.country_id = 223
and ll.deleted is null
and lp.deleted is null
and llp.deleted is null);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,3950));
n_parentid_delete := '0';
END;
dbms_output.put_line(n_parentid_delete);
dbms_output.put_line(n_group_id_del);
if n_parentid_delete <> '0' then
n_parentid_ins := c_row.parent_pk;
end if;
dbms_output.put_line(n_parentid_ins);
end if;
end loop;
IF n_parentid_delete <> '0' Then
FOR c_row2 in c2(n_group_id_del)
LOOP
if c_row2.dtd_element = 'DRUGEVENTMATRIX' then
-- For event Matrix delete the current records and bring in the new records
for c_row3 in c3(n_parentid_ins,'DRUGEVENTMATRIX' )
Loop
-- Loop into the Assessment Data and mark the appropriate falgs/data fields
End loop;
if c_row2.dtd_element = 'DOSAGEINFORMATION' then
for c_row3 in c3(n_parentid_ins,'DOSAGEINFORMATION' )
Loop
-- Loop into the Dosage Data and mark the appropriate falgs/data fields
End loop;
-- Like wise loop into all the Child elements in the ESM_DIFFERENCE_REPORT Table
Else
Update ESM_DIFFERENCE_REPORT EDR_INS
Set
33
EDR_INS.DIFF_VALUE1 = (SELECT EDR_DEL.DIFF_VALUE1 from ESM_DIFFERENCE_REPORT EDR_DEL where EDR_DEL.parent_pk = to_char(c_row2.seq_num) AND EDR_INS.dtd_element = EDR_DEL.dtd_element AND EDR_INS.PARENT_ELEMENT = c_row2.DTD_ELEMENT ),
EDR_INS.DECODED_VALUE1 = (SELECT EDR_DEL.DECODED_VALUE1 from ESM_DIFFERENCE_REPORT EDR_DEL where EDR_DEL.parent_pk = to_char(c_row2.seq_num) AND EDR_INS.dtd_element = EDR_DEL.dtd_element AND EDR_INS.PARENT_ELEMENT = c_row2.DTD_ELEMENT),
EDR_INS.REC_ACTION = 2,
EDR_INS.DIFF_CODE1 = 0,
EDR_INS.DIFF_CODE2=0
where
EDR_INS.parent = 0 and
(EDR_INS.parent_pk = to_char(c_row2.seq_num) or seq_num = c_row2.seq_num) ;
Update ESM_DIFFERENCE_REPORT
Set
REC_ACTION = 2 where seq_num = c_row2.seq_num ;
END IF;
end loop;
END IF;
-- Delete the unwanted record that was marked for insertion
update ESM_DIFFERENCE_REPORT set deleted = sysdate where parent_pk =n_parentid_ins ;
update ESM_DIFFERENCE_REPORT set deleted = sysdate where seq_num =n_parentid_ins;
END;

Note:

The following script is only for reference purpose and has not been validated against the system.