[Home] [Help]
76: Known limitations,enhancements,remarks:
77: Change History
78: Who When What
79: ******************************************************************/
80: CURSOR c_igs_ad_test_int (cp_lower_bound igs_ad_test_int.interface_test_id%TYPE,cp_higher_bound igs_ad_test_int.interface_test_id%TYPE) IS
81: SELECT cst_insert dmlmode, rowid, in_rec.*
82: FROM igs_ad_test_int in_rec
83: WHERE interface_run_id = p_interface_run_id
84: AND status = cst_s_val_2
78: Who When What
79: ******************************************************************/
80: CURSOR c_igs_ad_test_int (cp_lower_bound igs_ad_test_int.interface_test_id%TYPE,cp_higher_bound igs_ad_test_int.interface_test_id%TYPE) IS
81: SELECT cst_insert dmlmode, rowid, in_rec.*
82: FROM igs_ad_test_int in_rec
83: WHERE interface_run_id = p_interface_run_id
84: AND status = cst_s_val_2
85: AND interface_test_id between cp_lower_bound and cp_higher_bound
86: AND (( NVL(match_ind,'15') = '15'
92: OR ( p_rule = cst_rule_val_R
93: AND match_ind IN (cst_mi_val_16, cst_mi_val_25)))
94: UNION ALL
95: SELECT cst_update dmlmode, rowid, in_rec.*
96: FROM igs_ad_test_int in_rec
97: WHERE interface_run_id = p_interface_run_id
98: AND status = cst_s_val_2
99: AND interface_test_id between cp_lower_bound and cp_higher_bound
100: AND ( (p_rule = cst_rule_val_I)
106: AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date))
107:
108: UNION ALL
109: SELECT cst_dsp dmlmode, rowid, in_rec.*
110: FROM igs_ad_test_int in_rec
111: WHERE interface_run_id = p_interface_run_id
112: AND status = cst_s_val_1
113: AND interface_test_id between cp_lower_bound and cp_higher_bound
114: AND NVL(match_ind,cst_mi_val_15) in ( cst_mi_val_15,cst_mi_val_23)
142: FROM igs_ad_tst_rslt_dtls mn_rec
143: WHERE mn_rec.test_results_id = in_rec.test_results_id
144: AND mn_rec.test_segment_id = in_rec.test_segment_id);
145:
146: CURSOR c_null_hdlg_tst_rsl_cur(cp_tst_rsl_cur c_igs_ad_test_int%ROWTYPE) IS
147: SELECT ROWID, ar.*
148: FROM igs_ad_test_results ar
149: WHERE ar.person_id = cp_tst_rsl_cur.person_id
150: AND ar.admission_test_type = cp_tst_rsl_cur.admission_test_type
189: l_label VARCHAR2(100);
190: l_debug_str VARCHAR2(2000);
191: l_request_id NUMBER;
192:
193: l_min_tst_interface_id igs_ad_test_int.interface_test_id%TYPE;
194: l_max_tst_interface_id igs_ad_test_int.interface_test_id%TYPE;
195:
196: l_count_interface_testint_id NUMBER;
197: l_total_records_prcessed NUMBER;
190: l_debug_str VARCHAR2(2000);
191: l_request_id NUMBER;
192:
193: l_min_tst_interface_id igs_ad_test_int.interface_test_id%TYPE;
194: l_max_tst_interface_id igs_ad_test_int.interface_test_id%TYPE;
195:
196: l_count_interface_testint_id NUMBER;
197: l_total_records_prcessed NUMBER;
198:
467:
468:
469: -- Check for current/past dated test result without test segments
470:
471: UPDATE igs_ad_test_int tst_int
472: SET status = cst_s_val_3
473: ,error_code = cst_ec_val_E705
474: ,error_text = cst_et_val_E705
475: WHERE tst_int.interface_run_id = p_interface_run_id
493:
494: l_total_records_prcessed := 0;
495:
496: SELECT COUNT(interface_test_id) INTO l_count_interface_testint_id
497: FROM IGS_AD_TEST_INT testint
498: WHERE interface_run_id = p_interface_run_id
499: AND (status = cst_s_val_2 OR
500: (status = cst_s_val_1 AND NVL(match_ind,cst_mi_val_15) in ( cst_mi_val_15,cst_mi_val_23)
501: AND EXISTS (SELECT 1 FROM IGS_AD_TEST_SEGS_INT testsegsint
511:
512: SELECT
513: MIN(interface_test_id) , MAX(interface_test_id)
514: INTO l_min_tst_interface_id , l_max_tst_interface_id
515: FROM IGS_AD_TEST_INT testint
516: WHERE interface_run_id = p_interface_run_id
517: AND (status = cst_s_val_2 OR
518: (status = cst_s_val_1 AND NVL(match_ind,cst_mi_val_15) in ( cst_mi_val_15,cst_mi_val_23)
519: AND EXISTS (SELECT 1 FROM IGS_AD_TEST_SEGS_INT testsegsint
524: )
525: AND rownum <= 100;
526:
527:
528: FOR test_rec IN c_igs_ad_test_int(l_min_tst_interface_id,l_max_tst_interface_id)
529: LOOP
530:
531: BEGIN
532: l_error_text :='A';
535:
536: l_success := TRUE;
537: -- Check for future dated test result with test segments
538:
539: UPDATE igs_ad_test_int
540: SET status = cst_s_val_3
541: ,error_code = cst_ec_val_E577
542: ,error_text = cst_et_val_E577
543: WHERE interface_test_id = test_rec.interface_test_id
817:
818: END IF;
819:
820: IF l_success THEN
821: UPDATE igs_ad_test_int
822: SET
823: status = cst_s_val_1
824: , match_ind = DECODE(match_ind,
825: NULL, DECODE (test_rec.dmlmode,
848: ROLLBACK TO test_results_sp;
849: l_error_code :='E347';
850: l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
851:
852: UPDATE igs_ad_test_int
853: SET status = DECODE (test_rec.dmlmode,cst_dsp,cst_s_val_4,
854: cst_s_val_3)
855: ,error_code = l_error_code
856: ,error_text = l_error_text
858: match_ind)
859: WHERE interface_test_id = test_rec.interface_test_id;
860:
861: IF p_enable_log = 'Y' THEN
862: igs_ad_imp_001.logerrormessage(test_rec.interface_test_id,l_error_code,'IGS_AD_TEST_INT');
863: END IF;
864: l_error_code := NULL;
865:
866: l_records_processed := l_records_processed + 1;
891:
892: IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
893: l_error_text := l_msg_data;
894: IF p_enable_log = 'Y' THEN
895: igs_ad_imp_001.logerrormessage(test_rec.interface_test_id,l_msg_data,'IGS_AD_TEST_INT');
896: END IF;
897: ELSE
898: IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
899:
910: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
911: END IF;
912: END IF;
913:
914: UPDATE igs_ad_test_int
915: SET
916: status = cst_s_val_3
917: , match_ind = DECODE (test_rec.dmlmode,
918: cst_update, DECODE (match_ind,
969: l_prog_label VARCHAR2(100);
970: l_label VARCHAR2(100);
971: l_debug_str VARCHAR2(2000);
972: l_request_id NUMBER;
973: l_error_code igs_ad_test_int.error_code%TYPE;
974:
975: BEGIN
976:
977: l_prog_label := 'igs.plsql.igs_ad_imp_016.prc_tst_rslts';
990: END IF;
991:
992: -- Set STATUS to 3 for interface records with RULE = E or I and MATCH IND
993: IF p_rule IN ('E','I') THEN
994: UPDATE igs_ad_test_int
995: SET
996: status = cst_s_val_3
997: , error_code = cst_ec_val_E700
998: , error_text = cst_et_val_E700
1004:
1005: -- Set STATUS to 1 for interface records with RULE = R and
1006: -- MATCH IND = 17,18,19,22,23,24,27
1007: IF p_rule IN ('R') THEN
1008: UPDATE igs_ad_test_int
1009: SET
1010: status = cst_s_val_1
1011: WHERE interface_run_id = p_interface_run_id
1012: AND status = cst_s_val_2
1017:
1018: -- Set STATUS to 1 and MATCH IND to 19 for interface records with RULE =
1019: -- E matching OSS record(s)
1020: IF p_rule IN ('E') THEN
1021: UPDATE igs_ad_test_int in_rec
1022: SET
1023: status = cst_s_val_1
1024: , match_ind = cst_mi_val_19
1025: WHERE interface_run_id = p_interface_run_id
1035: -- Set STATUS to 3 for interface records with matching duplicate system
1036: -- record for RULE = I and either MATCH IND is 15 OR IS NULL (will
1037: -- require incase of data corruption, do we need in import process)
1038: IF p_rule IN ('I') THEN
1039: UPDATE igs_ad_test_int in_rec
1040: SET
1041: status = cst_s_val_3
1042: , match_ind = cst_mi_val_13
1043: , error_code = cst_ec_val_E678
1056: -- Set STATUS to 3 for interface records with matching duplicate system
1057: -- record for RULE = R and either MATCH IND IN (15, 21) OR IS NULL (will
1058: -- require incase of data corruption, do we need in import process)
1059: IF p_rule IN ('R') THEN
1060: UPDATE igs_ad_test_int in_rec
1061: SET
1062: status = cst_s_val_3
1063: , match_ind = cst_mi_val_13
1064: , error_code = cst_ec_val_E678
1072: AND mn_rec.admission_test_type = in_rec.admission_test_type
1073: AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date));
1074: COMMIT;
1075:
1076: UPDATE igs_ad_test_int in_rec
1077: SET test_results_id = (SELECT test_results_id
1078: FROM igs_ad_test_results mn_rec
1079: WHERE mn_rec.person_id = in_rec.person_id
1080: AND mn_rec.admission_test_type = in_rec.admission_test_type
1120: -- allowed then the 2 DECODE should be replaced by a single NVL
1121: -- Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and MATCH IND
1122: -- <> 21, 25, ones failed discrepancy check
1123: IF p_rule IN ('R') THEN
1124: UPDATE igs_ad_test_int in_rec
1125: SET
1126: status = cst_s_val_3
1127: , match_ind = cst_mi_val_20
1128: ,(dup_test_results_id)
1144: END IF;
1145:
1146: -- Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
1147: IF p_rule IN ('R') THEN
1148: UPDATE igs_ad_test_int
1149: SET
1150: status = cst_s_val_3
1151: , error_code = cst_ec_val_E700
1152: , error_text = cst_et_val_E700