[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_016
Source
1 PACKAGE BODY IGS_AD_IMP_016 AS
2 /* $Header: IGSAD94B.pls 120.4 2006/08/02 13:10:03 pbondugu ship $ */
3 /******************************************************************
4 Created By:
5 Date Created By:
6 Purpose:
7 Known limitations,enhancements,remarks:
8 Change History
9 Who When What
10 ******************************************************************/
11
12 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
13 cst_rule_val_I CONSTANT VARCHAR2(1) := 'I';
14 cst_rule_val_E CONSTANT VARCHAR2(1) := 'E';
15 cst_rule_val_R CONSTANT VARCHAR2(1) := 'R';
16
17 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
18 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
19 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
20 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
21 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
22 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
23 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
24 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
25 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
26 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
27 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
28 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
29 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
30 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
31 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
32 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
33
34 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
35 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
36 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
37 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
38
39
40 cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
41 cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
42 cst_dsp CONSTANT VARCHAR2(10) := 'DSPCHECK';
43
44 cst_unique_record CONSTANT NUMBER := 1;
45
46 cst_et_val_E700 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405);
47 cst_et_val_E701 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
48 cst_et_val_E678 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
49 cst_et_val_E347 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405);
50 cst_et_val_E577 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E577', 8405);
51 cst_et_val_E705 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E705', 8405);
52 cst_et_val_E322 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
53 cst_et_val_E014 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E014', 8405);
54
55
56 cst_ec_val_E322 VARCHAR2(4) := 'E322';
57 cst_ec_val_E014 VARCHAR2(4) := 'E014';
58 cst_ec_val_E700 VARCHAR2(4) := 'E700';
59 cst_ec_val_E701 VARCHAR2(4) := 'E701';
60 cst_ec_val_E347 VARCHAR2(4) := 'E347';
61 cst_ec_val_E678 VARCHAR2(4) := 'E678';
62 cst_ec_val_E577 VARCHAR2(4) := 'E577';
63 cst_ec_val_E705 VARCHAR2(4) := 'E705';
64
65
66 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
67
68 PROCEDURE crt_upd_tst_rslts(p_interface_run_id NUMBER,
69 p_rule VARCHAR2,
70 p_enable_log VARCHAR2)
71 AS
72 /******************************************************************
73 Created By:
74 Date Created By:
75 Purpose:
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
85 AND interface_test_id between cp_lower_bound and cp_higher_bound
86 AND (( NVL(match_ind,'15') = '15'
87 AND NOT EXISTS (SELECT 1
88 FROM igs_ad_test_results mn_rec
89 WHERE mn_rec.person_id = in_rec.person_id
90 AND mn_rec.admission_test_type = in_rec.admission_test_type
91 AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date)))
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)
101 OR (p_rule = cst_rule_val_R AND match_ind = cst_mi_val_21))
102 AND EXISTS (SELECT 1
103 FROM igs_ad_test_results mn_rec
104 WHERE mn_rec.person_id = in_rec.person_id
105 AND mn_rec.admission_test_type = in_rec.admission_test_type
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)
115 AND EXISTS (SELECT 1 FROM IGS_AD_TEST_SEGS_INT testsegsint
116 WHERE testsegsint.status = cst_s_val_2
117 AND in_rec.INTERFACE_TEST_ID = testsegsint.interface_test_id
118 );
119
120
121
122 CURSOR c_igs_ad_test_segs_int(cp_interface_test_id igs_ad_test_segs_int.interface_test_id%TYPE) IS
123 SELECT cst_insert dmlmode, rowid, in_rec.*
124 FROM igs_ad_test_segs_int in_rec
125 WHERE status = cst_s_val_2
126 AND interface_test_id = cp_interface_test_id
127 AND (( NVL(match_ind,'15') = '15'
128 AND NOT EXISTS (SELECT 1
129 FROM igs_ad_tst_rslt_dtls mn_rec
130 WHERE mn_rec.test_results_id = in_rec.test_results_id
131 AND mn_rec.test_segment_id = in_rec.test_segment_id))
132 OR ( p_rule = cst_rule_val_R
133 AND match_ind IN (cst_mi_val_16, cst_mi_val_25)))
134 UNION ALL
135 SELECT cst_update dmlmode, rowid, in_rec.*
136 FROM igs_ad_test_segs_int in_rec
137 WHERE status = cst_s_val_2
138 AND interface_test_id = cp_interface_test_id
139 AND ( (p_rule = cst_rule_val_I)
140 OR (p_rule = cst_rule_val_R AND match_ind = cst_mi_val_21))
141 AND EXISTS (SELECT 1
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
151 AND TRUNC(ar.test_date) = TRUNC(cp_tst_rsl_cur.test_date);
152
153 c_null_hdlg_test_rec c_null_hdlg_tst_rsl_cur%ROWTYPE;
154
155 CURSOR c_null_hdlg_tst_dtls_cur(cp_test_segment_id igs_ad_test_segs_int.test_segment_id%TYPE,
156 cp_test_results_id igs_ad_tst_rslt_dtls.test_results_id%TYPE) IS
157 SELECT ROWID, ar.*
158 FROM igs_ad_tst_rslt_dtls ar
159 WHERE test_results_id = cp_test_results_id
160 AND test_segment_id = cp_test_segment_id;
161
162 c_null_hdlg_tst_dtls_rec c_null_hdlg_tst_dtls_cur%ROWTYPE;
163
164 CURSOR c_test_type_cur ( x_admission_test_type igs_ad_test_type.admission_test_type%TYPE ) IS
165 SELECT score_type
166 FROM igs_ad_test_type
167 WHERE admission_test_type = x_admission_test_type ;
168
169
170 l_count_seg NUMBER(15);
171 l_success BOOLEAN := TRUE;
172
173 l_score_type igs_ad_test_type.score_type%TYPE;
174 l_status VARCHAR2(1);
175 l_error_code VARCHAR2(30);
176 l_error_text VARCHAR2(2000);
177 l_msg_at_index NUMBER := 0;
178 l_return_status VARCHAR2(1);
179 l_msg_count NUMBER ;
180 l_msg_data VARCHAR2(2000);
181 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
182
183 l_records_processed NUMBER;
184 l_rowid VARCHAR2(30);
185 l_tst_rslt_dtls_id igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%TYPE;
186 l_test_results_id igs_ad_test_results.test_results_id%TYPE;
187
188 l_prog_label VARCHAR2(100);
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;
198
199 test_seg_failed EXCEPTION;
200
201 PROCEDURE upd_tst_dtls_atm_bef ( p_interface_test_id igs_ad_test_segs_int.interface_test_id%TYPE,
202 p_test_results_id igs_ad_tst_rslt_dtls.test_results_id%TYPE,
203 p_success IN OUT NOCOPY BOOLEAN) AS
204 PRAGMA AUTONOMOUS_TRANSACTION;
205 BEGIN
206 IF p_rule IN ('E','I') AND p_success THEN
207 UPDATE igs_ad_test_segs_int
208 SET
209 status = cst_s_val_3
210 , error_code = cst_ec_val_E700
211 , error_text = cst_et_val_E700
212 WHERE status = '2'
213 AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15
214 AND interface_test_id = p_interface_test_id;
215 IF SQL%ROWCOUNT > 0 THEN
216 p_success := FALSE;
217 END IF;
218 COMMIT;
219 END IF;
220
221 -- Set STATUS to 1 for interface records with RULE = R and
222 -- MATCH IND = 17,18,19,22,23,24,27
223 IF p_rule IN ('R') AND p_success THEN
224 UPDATE igs_ad_test_segs_int
225 SET
226 status = cst_s_val_1
227 WHERE status = '2'
228 AND match_ind IN (cst_mi_val_17, cst_mi_val_18, cst_mi_val_19,
229 cst_mi_val_22, cst_mi_val_23, cst_mi_val_24, cst_mi_val_27)
230 AND interface_test_id = p_interface_test_id;
231 COMMIT;
232 END IF;
233
234 -- Set STATUS to 1 and MATCH IND to 19 for interface records with RULE =
235 -- E matching OSS record(s)
236 IF p_rule IN ('E') AND p_success THEN
237 UPDATE igs_ad_test_segs_int in_rec
238 SET
239 status = cst_s_val_1
240 , match_ind = cst_mi_val_19
241 WHERE status = '2'
242 AND interface_test_id = p_interface_test_id
243 AND EXISTS ( SELECT 1
244 FROM igs_ad_tst_rslt_dtls mn_rec
245 WHERE mn_rec.test_results_id = p_test_results_id
246 AND mn_rec.test_segment_id = in_rec.test_segment_id);
247 COMMIT;
248 END IF;
249
250 END upd_tst_dtls_atm_bef;
251
252 PROCEDURE upd_tst_dtls_atm_s3 ( p_interface_test_id igs_ad_test_segs_int.interface_test_id%TYPE,
253 p_success IN OUT NOCOPY BOOLEAN) AS
254 PRAGMA AUTONOMOUS_TRANSACTION;
255 BEGIN
256 IF p_rule IN ('E','I') AND p_success THEN
257 UPDATE igs_ad_test_segs_int
258 SET
259 status = cst_s_val_3
260 , error_code = cst_ec_val_E700
261 , error_text = cst_et_val_E700
262 WHERE status = '2'
263 AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15
264 AND interface_test_id = p_interface_test_id;
265 IF SQL%ROWCOUNT > 0 THEN
266 p_success := FALSE;
267 END IF;
268 COMMIT;
269 END IF;
270
271 IF p_rule IN ('R') AND p_success THEN
272 UPDATE igs_ad_test_segs_int
273 SET
274 status = cst_s_val_3
275 , error_code = cst_ec_val_E700
276 , error_text = cst_et_val_E700
277 WHERE status = '2'
278 AND NVL (match_ind, cst_mi_val_15) NOT IN (cst_mi_val_15,cst_mi_val_16,cst_mi_val_25)
279 AND interface_test_id = p_interface_test_id;
280 IF SQL%ROWCOUNT > 0 THEN
281 p_success := FALSE;
282 END IF;
283 COMMIT;
284 END IF;
285 END upd_tst_dtls_atm_s3;
286
287
288 PROCEDURE upd_tst_dtls_atm_s1 ( p_rowid VARCHAR2,
289 p_mode VARCHAR2) AS
290 PRAGMA AUTONOMOUS_TRANSACTION;
291 BEGIN
292 UPDATE igs_ad_test_segs_int
293 SET
294 status = cst_s_val_1
295 , match_ind = DECODE(match_ind,
296 NULL, DECODE (p_mode,
297 cst_update, cst_mi_val_18,
298 cst_insert, cst_mi_val_11)
299 ,match_ind)
300 WHERE rowid = p_rowid;
301 COMMIT;
302 END upd_tst_dtls_atm_s1;
303
304
305 PROCEDURE upd_tst_dtls_atm_aft ( p_interface_test_id igs_ad_test_segs_int.interface_test_id%TYPE,
306 p_mode VARCHAR2,
307 p_success IN OUT NOCOPY BOOLEAN) AS
308 PRAGMA AUTONOMOUS_TRANSACTION;
309 BEGIN
310 IF (p_mode = cst_update OR p_mode = cst_dsp ) AND p_success THEN
311 IF p_rule IN ('R') THEN
312 UPDATE igs_ad_test_segs_int in_rec
313 SET
314 status = cst_s_val_1
315 , match_ind = cst_mi_val_23
316 WHERE status = cst_s_val_2
317 AND interface_test_id = p_interface_test_id
318 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
319 AND EXISTS (
320 SELECT 1
321 FROM igs_ad_tst_rslt_dtls mn_rec
322 WHERE mn_rec.test_results_id = in_rec.test_results_id
323 AND mn_rec.test_segment_id = in_rec.test_segment_id
324 AND NVL(mn_rec.percentile,-99) = NVL(in_rec.percentile,NVL(mn_rec.percentile,-99))
325 AND NVL(mn_rec.national_percentile,-99) = NVL(in_rec.national_percentile,NVL(mn_rec.national_percentile,-99))
326 AND NVL(mn_rec.state_percentile,-99) = NVL(in_rec.state_percentile,NVL(mn_rec.state_percentile,-99))
330 AND NVL(mn_rec.irregularity_code_id,-99) = NVL(in_rec.irregularity_code,NVL(mn_rec.irregularity_code_id,-99))
327 AND NVL(mn_rec.percentile_year_rank,-99) = NVL(in_rec.percentile_year_rank,NVL(mn_rec.percentile_year_rank,-99))
328 AND NVL(mn_rec.score_band_upper,-99) = NVL(in_rec.score_band_upper,NVL(mn_rec.score_band_upper,-99))
329 AND NVL(mn_rec.score_band_lower,-99) = NVL(in_rec.score_band_lower,NVL(mn_rec.score_band_lower,-99))
331 AND NVL(mn_rec.test_score,-99) = NVL(in_rec.test_score,NVL(mn_rec.test_score,-99))
332 AND NVL(mn_rec.attribute_category,-99) = NVL(in_rec.attribute_category,NVL(mn_rec.attribute_category,-99))
333 AND NVL(mn_rec.attribute1,-99) = NVL(in_rec.attribute1,NVL(mn_rec.attribute1,-99))
334 AND NVL(mn_rec.attribute2,-99) = NVL(in_rec.attribute2,NVL(mn_rec.attribute2,-99))
335 AND NVL(mn_rec.attribute3,-99) = NVL(in_rec.attribute3,NVL(mn_rec.attribute3,-99))
336 AND NVL(mn_rec.attribute4,-99) = NVL(in_rec.attribute4,NVL(mn_rec.attribute4,-99))
337 AND NVL(mn_rec.attribute5,-99) = NVL(in_rec.attribute5,NVL(mn_rec.attribute5,-99))
338 AND NVL(mn_rec.attribute6,-99) = NVL(in_rec.attribute6,NVL(mn_rec.attribute6,-99))
339 AND NVL(mn_rec.attribute7,-99) = NVL(in_rec.attribute7,NVL(mn_rec.attribute7,-99))
340 AND NVL(mn_rec.attribute8,-99) = NVL(in_rec.attribute8,NVL(mn_rec.attribute8,-99))
341 AND NVL(mn_rec.attribute9,-99) = NVL(in_rec.attribute9,NVL(mn_rec.attribute9,-99))
342 AND NVL(mn_rec.attribute10,-99) = NVL(in_rec.attribute10,NVL(mn_rec.attribute10,-99))
343 AND NVL(mn_rec.attribute11,-99) = NVL(in_rec.attribute11,NVL(mn_rec.attribute11,-99))
344 AND NVL(mn_rec.attribute12,-99) = NVL(in_rec.attribute12,NVL(mn_rec.attribute12,-99))
345 AND NVL(mn_rec.attribute13,-99) = NVL(in_rec.attribute13,NVL(mn_rec.attribute13,-99))
346 AND NVL(mn_rec.attribute14,-99) = NVL(in_rec.attribute14,NVL(mn_rec.attribute14,-99))
347 AND NVL(mn_rec.attribute15,-99) = NVL(in_rec.attribute15,NVL(mn_rec.attribute15,-99))
348 AND NVL(mn_rec.attribute16,-99) = NVL(in_rec.attribute16,NVL(mn_rec.attribute16,-99))
349 AND NVL(mn_rec.attribute17,-99) = NVL(in_rec.attribute17,NVL(mn_rec.attribute17,-99))
350 AND NVL(mn_rec.attribute18,-99) = NVL(in_rec.attribute18,NVL(mn_rec.attribute18,-99))
351 AND NVL(mn_rec.attribute19,-99) = NVL(in_rec.attribute19,NVL(mn_rec.attribute19,-99))
352 AND NVL(mn_rec.attribute20,-99) = NVL(in_rec.attribute20,NVL(mn_rec.attribute20,-99))
353 );
354 COMMIT;
355 END IF;
356
357 -- Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and MATCH IND
358 -- <> 21, 25, ones failed discrepancy check
359 IF p_rule IN ('R') AND p_success THEN
360 UPDATE igs_ad_test_segs_int in_rec
361 SET
362 status = cst_s_val_3
363 , match_ind = cst_mi_val_20
364 WHERE status = cst_s_val_2
365 AND interface_test_id = p_interface_test_id
366 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
367 AND EXISTS ( SELECT rowid
368 FROM igs_ad_tst_rslt_dtls mn_rec
369 WHERE mn_rec.test_results_id = in_rec.test_results_id
370 AND mn_rec.test_segment_id = in_rec.test_segment_id);
371 IF SQL%ROWCOUNT > 0 THEN
372 p_success := FALSE;
373 END IF;
374 COMMIT;
375 END IF;
376 END IF;
377
378 -- Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
379 IF p_rule IN ('R') AND p_success THEN
380 UPDATE igs_ad_test_segs_int
381 SET
382 status = cst_s_val_3
383 , error_code = cst_ec_val_E700
384 , error_text = cst_et_val_E700
385 WHERE interface_test_id = p_interface_test_id
386 AND status = cst_s_val_2
387 AND match_ind IS NOT NULL;
388 IF SQL%ROWCOUNT > 0 THEN
389 p_success := FALSE;
390 END IF;
391 COMMIT;
392 END IF;
393 END upd_tst_dtls_atm_aft;
394
395
396 PROCEDURE upd_tst_dtls_atm_exp ( p_interface_test_id igs_ad_test_segs_int.interface_test_id%TYPE,
397 p_rowid VARCHAR2,
398 p_mode VARCHAR2,
399 p_error_code VARCHAR2,
400 p_error_text VARCHAR2) AS
401 PRAGMA AUTONOMOUS_TRANSACTION;
402 BEGIN
403
404 UPDATE igs_ad_test_segs_int
405 SET
406 status = cst_s_val_3
407 , match_ind = DECODE(match_ind,
408 NULL, DECODE (p_mode,
409 cst_update, cst_mi_val_18,
410 cst_insert, cst_mi_val_11)
411 ,match_ind)
412 , error_code = p_error_code
416 AND rowid = p_rowid;
413 , error_text = p_error_text
414 WHERE status IN ('1','2')
415 AND interface_test_id = p_interface_test_id
417
418 UPDATE igs_ad_test_segs_int
419 SET
420 status = cst_s_val_3
421 , match_ind = DECODE(match_ind,
422 NULL, DECODE (p_mode,
423 cst_update, cst_mi_val_18,
424 cst_insert, cst_mi_val_11)
425 ,match_ind)
426 , error_code = DECODE (p_mode, cst_update, cst_ec_val_E014,
427 cst_insert, cst_ec_val_E322)
428 , error_text = DECODE (p_mode, cst_update, cst_et_val_E014,
429 cst_insert, cst_et_val_E322)
430 WHERE status IN ('1','2')
431 AND interface_test_id = p_interface_test_id
432 AND rowid <> p_rowid;
433
434 COMMIT;
435 END upd_tst_dtls_atm_exp;
436
437 PROCEDURE upd_tst_dtls_atm_s2 ( p_interface_test_id igs_ad_test_segs_int.interface_test_id%TYPE,
438 p_test_results_id igs_ad_test_segs_int.test_results_id%TYPE) AS
439 PRAGMA AUTONOMOUS_TRANSACTION;
440 BEGIN
441
442 UPDATE igs_ad_test_segs_int
443 SET test_results_id = p_test_results_id
444 WHERE status = '2'
445 AND interface_test_id = p_interface_test_id;
446
447 COMMIT;
448 END upd_tst_dtls_atm_s2;
449
450
451 BEGIN
452
453 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
454
455
456 l_label := 'igs.plsql.igs_ad_imp_016.prc_tst_rslts.crt_upd_tst_rslts';
457 l_debug_str := 'Interface Run ID' || p_interface_run_id;
458
459 fnd_log.string_with_context( fnd_log.level_procedure,
460 l_label,
461 l_debug_str, NULL,
462 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
463 END IF;
464
465 l_records_processed := 0;
466 l_total_records_prcessed := 0;
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
476 AND tst_int.status IN (cst_s_val_2,cst_s_val_1)
477 AND TRUNC(tst_int.test_date) <= TRUNC(SYSDATE)
478 AND NOT EXISTS (SELECT 1
479 FROM igs_ad_test_segs_int tst_seg_int
480 WHERE tst_seg_int.interface_test_id = tst_int.interface_test_id
481 AND status = '2'
482 UNION
483 SELECT 1
484 FROM igs_ad_tst_rslt_dtls a, igs_ad_test_results b
485 WHERE person_id = tst_int.person_id
486 AND admission_test_type = tst_int.admission_test_type
487 AND TRUNC(test_date) = tst_int.test_date
488 AND a.test_results_id = b.test_results_id);
489
490 IF SQL%ROWCOUNT > 0 THEN
491 COMMIT;
492 END IF;
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
502 WHERE testsegsint.status = cst_s_val_2
503 AND testint.INTERFACE_TEST_ID = testsegsint.interface_test_id
504 )
505 )
506 );
507
508
509 LOOP
510 EXIT WHEN l_total_records_prcessed >= l_count_interface_testint_id;
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
520 WHERE testsegsint.status = cst_s_val_2
521 AND testint.INTERFACE_TEST_ID = testsegsint.interface_test_id
522 )
523 )
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
533 SAVEPOINT test_results_sp;
530
531 BEGIN
532 l_error_text :='A';
534 l_msg_at_index := igs_ge_msg_stack.count_msg;
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
544 AND TRUNC(test_date) > TRUNC(SYSDATE)
545 AND EXISTS (SELECT 1
546 FROM igs_ad_test_segs_int
547 WHERE interface_test_id = test_rec.interface_test_id
548 AND status = '2');
549 IF SQL%ROWCOUNT > 0 THEN
550 COMMIT;
551 ELSE
552 l_error_text := NULL;
553 END IF;
554
555
556
557 IF l_error_text IS NULL THEN
558 l_score_type := NULL ;
559
560 OPEN c_test_type_cur( test_rec.admission_test_type );
561 FETCH c_test_type_cur INTO l_score_type ;
562 CLOSE c_test_type_cur;
563
564 IF test_rec.dmlmode = cst_insert THEN
565 l_rowid := NULL;
566 l_test_results_id := NULL;
567
568 Igs_Ad_Test_Results_Pkg.Insert_Row (
569 x_rowid => l_rowid,
570 x_test_results_id => l_test_results_id,
571 x_person_id => test_rec.person_id,
572 x_admission_test_type => test_rec.admission_test_type,
573 x_test_date => test_rec.test_date,
574 x_score_report_date => test_rec.score_report_date,
575 x_edu_level_id => test_rec.edu_level_id,
576 x_score_type => l_score_type,
577 x_score_source_id => test_rec.score_source_id,
578 x_non_standard_admin => NVL(test_rec.non_standard_admin,'N'),
579 x_comp_test_score => NULL,
580 x_special_code => test_rec.special_code,
581 x_registration_number => test_rec.registration_number,
582 x_grade_id => test_rec.grade_id,
583 x_attribute_category => test_rec.attribute_category,
584 x_attribute1 => test_rec.attribute1,
585 x_attribute2 => test_rec.attribute2,
586 x_attribute3 => test_rec.attribute3,
587 x_attribute4 => test_rec.attribute4,
588 x_attribute5 => test_rec.attribute5,
589 x_attribute6 => test_rec.attribute6,
590 x_attribute7 => test_rec.attribute7,
591 x_attribute8 => test_rec.attribute8,
592 x_attribute9 => test_rec.attribute9,
593 x_attribute10 => test_rec.attribute10,
594 x_attribute11 => test_rec.attribute11,
595 x_attribute12 => test_rec.attribute12,
596 x_attribute13 => test_rec.attribute13,
600 x_attribute17 => test_rec.attribute17,
597 x_attribute14 => test_rec.attribute14,
598 x_attribute15 => test_rec.attribute15,
599 x_attribute16 => test_rec.attribute16,
601 x_attribute18 => test_rec.attribute18,
602 x_attribute19 => test_rec.attribute19,
603 x_attribute20 => test_rec.attribute20,
604 x_active_ind => test_rec.active_ind,
605 x_mode => 'R'
606 );
607
608 ELSIF test_rec.dmlmode = cst_update THEN
609
610 OPEN c_null_hdlg_tst_rsl_cur(test_rec);
611 FETCH c_null_hdlg_tst_rsl_cur INTO c_null_hdlg_test_rec;
612 CLOSE c_null_hdlg_tst_rsl_cur;
613
614 l_test_results_id := c_null_hdlg_test_rec.test_results_id;
615
616
617 Igs_Ad_Test_Results_Pkg.update_row(
618 x_rowid => c_null_hdlg_test_rec.ROWID,
619 x_test_results_id => c_null_hdlg_test_rec.test_results_id,
620 x_person_id => NVL(test_rec.person_id,c_null_hdlg_test_rec.person_id),
621 x_admission_test_type => NVL(test_rec.admission_test_type,c_null_hdlg_test_rec.admission_test_type),
622 x_test_date => NVL(test_rec.test_date,c_null_hdlg_test_rec.test_date),
623 x_score_report_date => NVL(test_rec.score_report_date,c_null_hdlg_test_rec.score_report_date),
624 x_edu_level_id => NVL(test_rec.edu_level_id,c_null_hdlg_test_rec.edu_level_id),
625 x_score_type => NVL(l_score_type, c_null_hdlg_test_rec.score_type) ,
626 x_score_source_id => NVL(test_rec.score_source_id,c_null_hdlg_test_rec.score_source_id),
627 x_non_standard_admin => NVL(test_rec.non_standard_admin,c_null_hdlg_test_rec.non_standard_admin),
628 x_comp_test_score => c_null_hdlg_test_rec.comp_test_score,
629 x_special_code => NVL(test_rec.special_code,c_null_hdlg_test_rec.special_code),
630 x_registration_number => NVL(test_rec.registration_number,c_null_hdlg_test_rec.registration_number),
631 x_grade_id => NVL(test_rec.grade_id,c_null_hdlg_test_rec.grade_id),
632 x_attribute_category => NVL(test_rec.attribute_category,c_null_hdlg_test_rec.attribute_category),
633 x_attribute1 => NVL(test_rec.attribute1,c_null_hdlg_test_rec.attribute1),
634 x_attribute2 => NVL(test_rec.attribute2,c_null_hdlg_test_rec.attribute2),
635 x_attribute3 => NVL(test_rec.attribute3,c_null_hdlg_test_rec.attribute3),
636 x_attribute4 => NVL(test_rec.attribute4,c_null_hdlg_test_rec.attribute4),
637 x_attribute5 => NVL(test_rec.attribute5,c_null_hdlg_test_rec.attribute5),
638 x_attribute6 => NVL(test_rec.attribute6,c_null_hdlg_test_rec.attribute6),
639 x_attribute7 => NVL(test_rec.attribute7,c_null_hdlg_test_rec.attribute7),
640 x_attribute8 => NVL(test_rec.attribute8,c_null_hdlg_test_rec.attribute8),
641 x_attribute9 => NVL(test_rec.attribute9,c_null_hdlg_test_rec.attribute9),
642 x_attribute10 => NVL(test_rec.attribute10,c_null_hdlg_test_rec.attribute10),
643 x_attribute11 => NVL(test_rec.attribute11,c_null_hdlg_test_rec.attribute11),
644 x_attribute12 => NVL(test_rec.attribute12,c_null_hdlg_test_rec.attribute12),
645 x_attribute13 => NVL(test_rec.attribute13,c_null_hdlg_test_rec.attribute13),
646 x_attribute14 => NVL(test_rec.attribute14,c_null_hdlg_test_rec.attribute14),
647 x_attribute15 => NVL(test_rec.attribute15,c_null_hdlg_test_rec.attribute15),
648 x_attribute16 => NVL(test_rec.attribute16,c_null_hdlg_test_rec.attribute16),
649 x_attribute17 => NVL(test_rec.attribute17,c_null_hdlg_test_rec.attribute17),
653 x_active_ind => NVL(test_rec.active_ind, c_null_hdlg_test_rec.active_ind),
650 x_attribute18 => NVL(test_rec.attribute18,c_null_hdlg_test_rec.attribute18),
651 x_attribute19 => NVL(test_rec.attribute19,c_null_hdlg_test_rec.attribute19),
652 x_attribute20 => NVL(test_rec.attribute20,c_null_hdlg_test_rec.attribute20),
654 x_mode => 'R'
655 );
656 ELSIF test_rec.dmlmode = cst_dsp THEN
657 l_test_results_id := test_rec.test_results_id;
658 END IF;
659
660 BEGIN -- Test Result Details
661
662 upd_tst_dtls_atm_s2(test_rec.interface_test_id,l_test_results_id);
663
664 IF test_rec.dmlmode = cst_insert THEN
665 l_success := TRUE;
666 upd_tst_dtls_atm_s3(test_rec.interface_test_id,l_success);
667 ELSIF test_rec.dmlmode = cst_update OR test_rec.dmlmode = cst_dsp THEN
668 l_success := TRUE;
669 -- Autonomous Transaction to update error status for Test Details interface table;
670 upd_tst_dtls_atm_bef(test_rec.interface_test_id,l_test_results_id,l_success);
671 END IF;
672
673
674 IF l_success THEN
675 FOR test_dtls_rec IN c_igs_ad_test_segs_int(test_rec.interface_test_id)
676 LOOP
677 BEGIN
678 l_msg_at_index := igs_ge_msg_stack.count_msg;
679
680 IF test_dtls_rec.dmlmode = cst_insert THEN
681 l_rowid := NULL;
682 Igs_Ad_Tst_Rslt_Dtls_Pkg.Insert_Row (
683 x_rowId => l_rowid,
684 x_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
685 x_test_results_id => l_test_results_id,
686 x_test_segment_id => test_dtls_rec.test_segment_id,
687 x_test_score => test_dtls_rec.test_score,
688 x_percentile => test_dtls_rec.percentile,
689 x_national_percentile => test_dtls_rec.national_percentile,
690 x_state_percentile => test_dtls_rec.state_percentile,
691 x_percentile_year_rank => test_dtls_rec.percentile_year_rank,
692 x_score_band_lower => test_dtls_rec.score_band_lower,
693 x_score_band_upper => test_dtls_rec.score_band_upper,
694 x_irregularity_code_id => test_dtls_rec.irregularity_code,
695 x_attribute_category => test_dtls_rec.attribute_category,
696 x_attribute1 => test_dtls_rec.attribute1,
697 x_attribute2 => test_dtls_rec.attribute2,
698 x_attribute3 => test_dtls_rec.attribute3,
699 x_attribute4 => test_dtls_rec.attribute4,
700 x_attribute5 => test_dtls_rec.attribute5,
701 x_attribute6 => test_dtls_rec.attribute6,
702 x_attribute7 => test_dtls_rec.attribute7,
703 x_attribute8 => test_dtls_rec.attribute8,
704 x_attribute9 => test_dtls_rec.attribute9,
705 x_attribute10 => test_dtls_rec.attribute10,
706 x_attribute11 => test_dtls_rec.attribute11,
710 x_attribute15 => test_dtls_rec.attribute15,
707 x_attribute12 => test_dtls_rec.attribute12,
708 x_attribute13 => test_dtls_rec.attribute13,
709 x_attribute14 => test_dtls_rec.attribute14,
711 x_attribute16 => test_dtls_rec.attribute16,
712 x_attribute17 => test_dtls_rec.attribute17,
713 x_attribute18 => test_dtls_rec.attribute18,
714 x_attribute19 => test_dtls_rec.attribute19,
715 x_attribute20 => test_dtls_rec.attribute20,
716 x_mode => 'R'
717 );
718
719 ELSIF test_dtls_rec.dmlmode = cst_update THEN
720 OPEN c_null_hdlg_tst_dtls_cur(test_dtls_rec.test_segment_id,test_dtls_rec.test_results_id);
721 FETCH c_null_hdlg_tst_dtls_cur INTO c_null_hdlg_tst_dtls_rec;
722 CLOSE c_null_hdlg_tst_dtls_cur;
723
724 Igs_Ad_Tst_Rslt_Dtls_Pkg.Update_Row (
725 x_rowid => c_null_hdlg_tst_dtls_rec.rowid,
726 x_tst_rslt_dtls_id => c_null_hdlg_tst_dtls_rec.tst_rslt_dtls_id,
727 x_test_results_id => c_null_hdlg_tst_dtls_rec.test_results_id ,
728 x_test_segment_id => NVL( test_dtls_rec.test_segment_id, c_null_hdlg_tst_dtls_rec.test_segment_id ),
729 x_test_score => NVL( test_dtls_rec.test_score, c_null_hdlg_tst_dtls_rec.test_score),
730 x_percentile => NVL( test_dtls_rec.percentile, c_null_hdlg_tst_dtls_rec.percentile),
731 x_national_percentile => NVL( test_dtls_rec.national_percentile, c_null_hdlg_tst_dtls_rec.national_percentile),
732 x_state_percentile => NVL( test_dtls_rec.state_percentile, c_null_hdlg_tst_dtls_rec.state_percentile),
733 x_percentile_year_rank => NVL( test_dtls_rec.percentile_year_rank, c_null_hdlg_tst_dtls_rec.percentile_year_rank),
734 x_score_band_lower => NVL( test_dtls_rec.score_band_lower, c_null_hdlg_tst_dtls_rec.score_band_lower),
735 x_score_band_upper => NVL( test_dtls_rec.score_band_upper, c_null_hdlg_tst_dtls_rec.score_band_upper),
736 x_irregularity_code_id => NVL( test_dtls_rec.irregularity_code, c_null_hdlg_tst_dtls_rec.irregularity_code_id),
737 x_attribute_category => NVL( test_dtls_rec.attribute_category, c_null_hdlg_tst_dtls_rec.attribute_category),
738 x_attribute1 => NVL( test_dtls_rec.attribute1, c_null_hdlg_tst_dtls_rec.attribute1),
739 x_attribute2 => NVL( test_dtls_rec.attribute2, c_null_hdlg_tst_dtls_rec.attribute2),
740 x_attribute3 => NVL( test_dtls_rec.attribute3, c_null_hdlg_tst_dtls_rec.attribute3),
741 x_attribute4 => NVL( test_dtls_rec.attribute4, c_null_hdlg_tst_dtls_rec.attribute4),
742 x_attribute5 => NVL( test_dtls_rec.attribute5, c_null_hdlg_tst_dtls_rec.attribute5),
743 x_attribute6 => NVL( test_dtls_rec.attribute6, c_null_hdlg_tst_dtls_rec.attribute6),
744 x_attribute7 => NVL( test_dtls_rec.attribute7, c_null_hdlg_tst_dtls_rec.attribute7),
745 x_attribute8 => NVL( test_dtls_rec.attribute8, c_null_hdlg_tst_dtls_rec.attribute8),
746 x_attribute9 => NVL( test_dtls_rec.attribute9, c_null_hdlg_tst_dtls_rec.attribute9),
747 x_attribute10 => NVL( test_dtls_rec.attribute10, c_null_hdlg_tst_dtls_rec.attribute10),
751 x_attribute14 => NVL( test_dtls_rec.attribute14, c_null_hdlg_tst_dtls_rec.attribute14),
748 x_attribute11 => NVL( test_dtls_rec.attribute11, c_null_hdlg_tst_dtls_rec.attribute11),
749 x_attribute12 => NVL( test_dtls_rec.attribute12, c_null_hdlg_tst_dtls_rec.attribute12),
750 x_attribute13 => NVL( test_dtls_rec.attribute13, c_null_hdlg_tst_dtls_rec.attribute13),
752 x_attribute15 => NVL( test_dtls_rec.attribute15, c_null_hdlg_tst_dtls_rec.attribute15),
753 x_attribute16 => NVL( test_dtls_rec.attribute16, c_null_hdlg_tst_dtls_rec.attribute16),
754 x_attribute17 => NVL( test_dtls_rec.attribute17, c_null_hdlg_tst_dtls_rec.attribute17),
755 x_attribute18 => NVL( test_dtls_rec.attribute18, c_null_hdlg_tst_dtls_rec.attribute18),
756 x_attribute19 => NVL( test_dtls_rec.attribute19, c_null_hdlg_tst_dtls_rec.attribute19),
757 x_attribute20 => NVL( test_dtls_rec.attribute20, c_null_hdlg_tst_dtls_rec.attribute20),
758 x_mode => 'R'
759 );
760 END IF;
761
762 upd_tst_dtls_atm_s1(test_dtls_rec.rowid,test_dtls_rec.dmlmode);
763 EXCEPTION
764 WHEN OTHERS THEN
765 l_msg_data := SQLERRM;
766 l_status := '3';
767 l_success := FALSE;
768
769 IF test_dtls_rec.dmlmode = cst_insert THEN
770 l_error_code := 'E322'; -- Insertion Failed
771 ELSIF test_dtls_rec.dmlmode = cst_update THEN
772 l_error_code := 'E014'; -- Update Failed
773 END IF;
774
775 igs_ad_gen_016.extract_msg_from_stack (
776 p_msg_at_index => l_msg_at_index,
777 p_return_status => l_return_status,
778 p_msg_count => l_msg_count,
779 p_msg_data => l_msg_data,
780 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
781 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
782
783 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
784 IF p_enable_log = 'Y' THEN
785 igs_ad_imp_001.logerrormessage(test_dtls_rec.interface_testsegs_id,l_msg_data,'IGS_AD_TEST_SEGS_INT');
786 END IF;
787 ELSE
788 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
789
790 l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.crt_upd_tst_rslts.for_loop_test_dtls.execption'||l_error_code;
791
792 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
793 fnd_message.set_token('INTERFACE_ID',test_dtls_rec.interface_testsegs_id);
794 fnd_message.set_token('ERROR_CD',l_error_code);
795
796 l_debug_str := fnd_message.get;
797 fnd_log.string_with_context( fnd_log.level_exception,
798 l_label,
799 l_debug_str, NULL,
800 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
801 END IF;
802 END IF;
803
804 -- Autonomous Transaction to update error status for Test Details interface table;
805 upd_tst_dtls_atm_exp(test_rec.interface_test_id, test_dtls_rec.rowid
806 ,test_dtls_rec.dmlmode,l_error_code,l_error_text);
807 EXIT;
808
809 END;
810 END LOOP;
811
812 -- Autonomous Transaction to update error status for Test Details interface table;
813 upd_tst_dtls_atm_aft(test_rec.interface_test_id,test_rec.dmlmode,l_success);
814 END IF;
815
816 END; -- Test Result Details
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,
826 cst_update, cst_mi_val_18,
827 cst_insert, cst_mi_val_11,
828 cst_dsp, cst_mi_val_23)
829 ,match_ind)
830 , test_results_id = l_test_results_id
831 WHERE rowid = test_rec.rowid
832 AND status = '2';
833 ELSE
834
835 RAISE test_seg_failed;
836 END IF;
837
838 l_records_processed := l_records_processed + 1;
839 l_total_records_prcessed := l_total_records_prcessed +1;
840
841 IF l_records_processed = 100 THEN
842 COMMIT;
843 l_records_processed := 0;
844 END IF;
845
846 EXCEPTION
847 WHEN test_seg_failed THEN
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
857 ,match_ind = DECODE (test_rec.dmlmode,cst_dsp,cst_mi_val_23,
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;
867 l_total_records_prcessed := l_total_records_prcessed +1;
868 IF l_records_processed = 100 THEN
869 COMMIT;
870 l_records_processed := 0;
871 END IF;
872
873 WHEN OTHERS THEN
874 ROLLBACK TO test_results_sp;
875 l_msg_data := SQLERRM;
876 l_status := '3';
877
878 IF test_rec.dmlmode = cst_insert THEN
879 l_error_code := 'E322'; -- Insertion Failed
880 ELSIF test_rec.dmlmode = cst_update THEN
881 l_error_code := 'E014'; -- Update Failed
882 END IF;
883
884 igs_ad_gen_016.extract_msg_from_stack (
888 p_msg_data => l_msg_data,
885 p_msg_at_index => l_msg_at_index,
886 p_return_status => l_return_status,
887 p_msg_count => l_msg_count,
889 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
890 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
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
900 l_label := 'igs.plsql.igs_ad_imp_016.prc_tst_rslts.crt_upd_tst_rslts.for_loop.execption'||l_error_code;
901
902 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
903 fnd_message.set_token('INTERFACE_ID',test_rec.interface_test_id);
904 fnd_message.set_token('ERROR_CD',l_error_code);
905
906 l_debug_str := fnd_message.get;
907 fnd_log.string_with_context( fnd_log.level_exception,
908 l_label,
909 l_debug_str, NULL,
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,
919 NULL, cst_mi_val_12,
920 match_ind),
921 cst_insert, DECODE (p_rule,
922 cst_rule_val_R, DECODE (match_ind,
923 NULL, cst_mi_val_11,
924 match_ind),
925 cst_mi_val_11),
926 cst_dsp,cst_mi_val_23 )
927 , error_code = l_error_code
928 , error_text = l_error_text
929 WHERE rowid = test_rec.rowid;
930
931 l_records_processed := l_records_processed + 1;
932 l_total_records_prcessed := l_total_records_prcessed +1;
933
934 IF l_records_processed = 100 THEN
935 COMMIT;
936 l_records_processed := 0;
937 END IF;
938
939 END;
940
941 IF l_records_processed = 100 THEN
942 COMMIT;
943 l_records_processed := 0;
944 END IF;
945
946 END LOOP; --Test Results Loop;
947
948 COMMIT;
949
950 END LOOP; -- Cursor Break Up LOOP;
951
952 COMMIT;
953
954 END crt_upd_tst_rslts; -- End of local procedure crt_upd_tst_rslts.
955
956 -- begin of main process prc_tst_rslts
957 PROCEDURE prc_tst_rslts(p_interface_run_id IN NUMBER,
958 p_enable_log IN VARCHAR2,
959 p_rule IN VARCHAR2 )
960 AS
961 /******************************************************************
962 Created By:
963 Date Created By:
964 Purpose:
965 Known limitations,enhancements,remarks:
966 Change History
967 Who When What
968 ******************************************************************/
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';
978 l_label := 'igs.plsql.igs_ad_imp_016.prc_tst_rslts.';
979 l_request_id := fnd_global.conc_request_id;
980
981 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
982
983 l_label := 'igs.plsql.igs_ad_imp_016.prc_tst_rslts.begin';
984 l_debug_str := 'igs_ad_imp_016.prc_tst_rslts';
985
986 fnd_log.string_with_context( fnd_log.level_procedure,
987 l_label,
988 l_debug_str, NULL,
989 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
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
999 WHERE interface_run_id = p_interface_run_id
1000 AND status = cst_s_val_2
1001 AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15;
1002 COMMIT;
1003 END IF;
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
1013 AND match_ind IN (cst_mi_val_17, cst_mi_val_18, cst_mi_val_19,
1014 cst_mi_val_22, cst_mi_val_23, cst_mi_val_24, cst_mi_val_27);
1015 COMMIT;
1016 END IF;
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
1027 AND EXISTS ( SELECT 1
1024 , match_ind = cst_mi_val_19
1025 WHERE interface_run_id = p_interface_run_id
1026 AND status = cst_s_val_2
1028 FROM igs_ad_test_results mn_rec
1029 WHERE mn_rec.person_id = in_rec.person_id
1030 AND mn_rec.admission_test_type = in_rec.admission_test_type
1031 AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date));
1032 COMMIT;
1033 END IF;
1034
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
1044 , error_text = cst_et_val_E678
1045 WHERE interface_run_id = p_interface_run_id
1046 AND status = cst_s_val_2
1047 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
1048 AND 1 < ( SELECT COUNT (*)
1049 FROM igs_ad_test_results mn_rec
1050 WHERE mn_rec.person_id = in_rec.person_id
1051 AND mn_rec.admission_test_type = in_rec.admission_test_type
1052 AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date));
1053 COMMIT;
1054 END IF;
1055
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
1065 , error_text = cst_et_val_E678
1066 WHERE interface_run_id = p_interface_run_id
1067 AND status = cst_s_val_2
1068 AND NVL (match_ind, cst_mi_val_15) IN (cst_mi_val_15, cst_mi_val_21)
1069 AND 1 < ( SELECT COUNT (*)
1070 FROM igs_ad_test_results mn_rec
1071 WHERE mn_rec.person_id = in_rec.person_id
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
1081 AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date))
1082 ,status = cst_s_val_1
1083 ,match_ind = cst_mi_val_23
1084 WHERE interface_run_id = p_interface_run_id
1085 AND status = cst_s_val_2
1086 AND NVL (match_ind, cst_mi_val_15) IN (cst_mi_val_15)
1087 AND EXISTS (
1088 SELECT 1
1089 FROM igs_ad_test_results mn_rec
1090 WHERE NVL(mn_rec.person_id,-99) = NVL(in_rec.person_id, NVL(mn_rec.person_id,-99) )
1091 AND NVL(mn_rec.admission_test_type, '~') = NVL(in_rec.admission_test_type, NVL(mn_rec.admission_test_type, '~'))
1092 AND TRUNC(NVL(mn_rec.test_date, SYSDATE)) = TRUNC(NVL(in_rec.test_date, NVL(mn_rec.test_date, SYSDATE)))
1093 AND TRUNC(NVL(mn_rec.score_report_date, SYSDATE)) = TRUNC(NVL(in_rec.score_report_date, NVL(mn_rec.score_report_date, SYSDATE)))
1094 AND NVL(mn_rec.edu_level_id, -99) = NVL(in_rec.edu_level_id, NVL(mn_rec.edu_level_id, -99))
1095 AND NVL(mn_rec.score_type, '~') = NVL(in_rec.score_type, NVL(mn_rec.score_type, '~'))
1096 AND NVL(mn_rec.score_source_id, -99) = NVL(in_rec.score_source_id, NVL(mn_rec.score_source_id, -99))
1097 AND NVL(mn_rec.non_standard_admin, '~') = NVL(in_rec.non_standard_admin, NVL(mn_rec.non_standard_admin, '~'))
1098 AND NVL(mn_rec.special_code, '~') = NVL(in_rec.special_code, NVL(mn_rec.special_code, '~'))
1099 AND NVL(mn_rec.grade_id, -99) = NVL(in_rec.grade_id, NVL(mn_rec.grade_id, -99))
1100 );
1101 COMMIT;
1102 END IF;
1103
1104 -- Create / Update the OSS record after validating successfully the interface record
1105 -- Create
1106 -- If RULE E/I/R (match indicator will be 15 or NULL by now no need to check) and
1107 -- matching system record not found OR RULE = R and MATCH IND = 16, 25
1108 -- Update
1109 -- If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
1110 -- RULE = R and MATCH IND = 21
1111
1112 -- Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying
1113 -- the DML operation. This is done to have one code section for record validation, exception
1114 -- handling and interface table update. This avoids call to separate PLSQL blocks, tuning
1115 -- performance on stack maintenance during the process.
1116 crt_upd_tst_rslts(p_interface_run_id, p_rule,p_enable_log);
1117
1118 -- Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching
1119 -- OSS record(s) in ALL updateable column values, if column nullification is not
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)
1129 = (SELECT mn_rec.test_results_id
1130 FROM igs_ad_test_results mn_rec
1131 WHERE mn_rec.person_id = in_rec.person_id
1132 AND mn_rec.admission_test_type = in_rec.admission_test_type
1133 AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date)
1134 AND ROWNUM = 1)
1135 WHERE interface_run_id = p_interface_run_id
1136 AND status = cst_s_val_2
1137 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
1138 AND EXISTS ( SELECT rowid
1139 FROM igs_ad_test_results mn_rec
1140 WHERE mn_rec.person_id = in_rec.person_id
1141 AND mn_rec.admission_test_type = in_rec.admission_test_type
1142 AND TRUNC(mn_rec.test_date) = TRUNC(in_rec.test_date));
1143 COMMIT;
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
1153 WHERE interface_run_id = p_interface_run_id
1154 AND status = cst_s_val_2
1155 AND match_ind IS NOT NULL;
1156 COMMIT;
1157 END IF;
1158
1159 END prc_tst_rslts;
1160
1161 END igs_ad_imp_016;