1 PACKAGE BODY igs_ad_imp_024 AS
2 /* $Header: IGSADB2B.pls 120.4 2006/04/13 05:53:45 stammine ship $ */
3 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
4 cst_rule_val_I CONSTANT VARCHAR2(1) := 'I';
5 cst_rule_val_E CONSTANT VARCHAR2(1) := 'E';
6 cst_rule_val_R CONSTANT VARCHAR2(1) := 'R';
7
8
9 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
10 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
11 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
12 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
13 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
14 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
15 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
16 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
17 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
18 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
19 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
20 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
21 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
22 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
23 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
24 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
25
26 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
27 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
28 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
29 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
30
31 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
32 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
33 cst_ec_val_NULL CONSTANT VARCHAR2(4) := NULL;
34
35 cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
36 cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
37 cst_unique_record CONSTANT NUMBER := 1;
38 l_request_id NUMBER := fnd_global.conc_request_id;
39 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes*******************/
40
41 PROCEDURE process_term_details(
42 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
43 p_rule VARCHAR2,
44 p_enable_log VARCHAR2) ;
45
46 PROCEDURE process_term_unit_details(
47 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
48 p_rule VARCHAR2,
49 p_enable_log VARCHAR2) ;
50
51 PROCEDURE prc_trscrpt(
52 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
53 p_rule VARCHAR2,
54 p_enable_log VARCHAR2) AS
55
56 l_status VARCHAR2(5);
57 l_industry VARCHAR2(5);
58 l_schema VARCHAR2(30);
59 l_return BOOLEAN;
60
61 l_msg_at_index NUMBER := 0;
62 l_return_status VARCHAR2(1);
63 l_msg_count NUMBER ;
64 l_msg_data VARCHAR2(2000);
65 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
66
67
68 CURSOR trans_cur(cp_start_int_id igs_ad_txcpt_int.interface_transcript_id%TYPE, --ARVSRINI--
69 cp_end_int_id igs_ad_txcpt_int.interface_transcript_id%TYPE) IS --ARVSRINI--
70 SELECT cst_insert dmlmode, trans.rowid, trans.*
71 FROM igs_ad_txcpt_int trans
72 WHERE interface_run_id = p_interface_run_id
73 AND trans.status = '2'
74 AND ( NOT EXISTS (SELECT 1
75 FROM IGS_AD_TRANSCRIPT trans_oss
76 WHERE education_id = trans.education_id
77 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue) )
78 OR ( p_rule = 'R' AND trans.match_ind IN ('16', '25') )
79 )
80 AND UPDATE_TRANSCRIPT_ID IS NULL
81 AND interface_transcript_id BETWEEN cp_start_int_id AND cp_end_int_id --ARVSRINI--
82 UNION ALL
83 SELECT cst_update dmlmode, trans.rowid, trans.*
84 FROM igs_ad_txcpt_int trans
85 WHERE interface_run_id = p_interface_run_id
86 AND status = '2'
87 AND ( p_rule = 'I' OR (p_rule = 'R' AND trans.match_ind = '21'))
88 AND interface_transcript_id BETWEEN cp_start_int_id AND cp_end_int_id --ARVSRINI--
89 AND ( EXISTS (SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
90 WHERE education_id = trans.education_id
91 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
92 )
93 OR UPDATE_TRANSCRIPT_ID IS NOT NULL
94 );
95
96 CURSOR c_dup_cur(trans_cur_rec trans_cur%ROWTYPE) IS
97 SELECT
98 trans_oss.rowid, trans_oss.*
99 FROM
100 IGS_AD_TRANSCRIPT trans_oss
101 WHERE ( transcript_id = trans_cur_rec.update_transcript_id
102 AND trans_cur_rec.update_transcript_id IS NOT NULL)
103 OR ( trans_cur_rec.update_transcript_id IS NULL
104 AND education_id = trans_cur_rec.education_id
105 AND TRUNC(date_of_issue) = TRUNC(trans_cur_rec.date_of_issue)
106 ) ;
107
108 l_maxint NUMBER(15);
109 l_minint NUMBER(15);
110
111
112 dup_cur_rec c_dup_cur%ROWTYPE;
113 l_prog_label VARCHAR2(100) := 'igs.plsql.igs_ad_imp_024.prc_trscrpt';
114 l_label VARCHAR2(1000) ;
115 l_debug_str VARCHAR2(1000) ;
116 l_processed_records NUMBER(5) ;
117 l_count_interface_txpt_id NUMBER;
118 l_total_records_prcessed NUMBER;
119
120
121 PROCEDURE create_new_transcript_details(p_trans_record IN OUT NOCOPY trans_cur%ROWTYPE)
122 AS
123 --------------------------------------------------------------------------
124 -- Created By : pbondugu
125 -- Date Created On : 2003/11/22
126 -- Purpose:
127 -- Know limitations, enhancements or remarks
128 -- Change History
129 -- Who When What
130 -- (reverse chronological order - newest change first)
131 --------------------------------------------------------------------------
132 l_rowid VARCHAR2(25);
133 l_var VARCHAR2(25);
134 l_validation_status NUMBER;
135 l_err_code VARCHAR2(25);
136 l_transcript_id igs_ad_txcpt_int.transcript_id%TYPE;
137 l_error_code VARCHAR2(4) := NULL;
138 l_error_text VARCHAR2(2000):= NULL;
139
140 BEGIN
141 l_transcript_id := NULL;
142 BEGIN
143 IF NVL(p_trans_record.override_ind, 'N') = 'N' THEN
144 IF p_trans_record.class_size > 0 AND p_trans_record.rank_in_class > 0 THEN
145 p_trans_record.percentile_rank := ROUND( ((p_trans_record.class_size - p_trans_record.rank_in_class)/p_trans_record.class_size)*100) ;
146 p_trans_record.decile_rank := 11 - CEIL(p_trans_record.percentile_rank/10) ;
147 p_trans_record.quartile_rank := 5 - CEIL(p_trans_record.percentile_rank/25) ;
148 p_trans_record.quintile_rank := 6 - CEIL(p_trans_record.percentile_rank/20) ;
149 ELSE
150 p_trans_record.percentile_rank := NULL;
151 p_trans_record.decile_rank := NULL;
152 p_trans_record.quartile_rank := NULL;
153 p_trans_record.quintile_rank := NULL;
154 END IF;
155 END IF;
156 l_msg_at_index := igs_ge_msg_stack.count_msg;
157 SAVEPOINT before_create_transcript;
158 igs_ad_transcript_pkg.insert_row(
159 x_rowid => l_rowid,
160 x_quintile_rank => p_trans_record.quintile_rank,
161 x_percentile_rank => p_trans_record.percentile_rank,
162 x_transcript_id => l_transcript_id,
163 x_education_id => p_trans_record.education_id,
164 x_transcript_status => p_trans_record.transcript_status,
165 x_transcript_source => p_trans_record.transcript_source,
166 x_date_of_receipt => TRUNC(p_trans_record.date_of_receipt),
167 x_entered_gpa => p_trans_record.entered_gpa,
168 x_entered_gs_id => p_trans_record.entered_gs_id,
169 x_conv_gpa => p_trans_record.conv_gpa,
170 x_conv_gs_id => p_trans_record.conv_gs_id,
171 x_term_type => p_trans_record.term_type,
172 x_rank_in_class => p_trans_record.rank_in_class,
173 x_class_size => p_trans_record.class_size,
174 x_approximate_rank => p_trans_record.approximate_rank,
175 x_weighted_rank => p_trans_record.weighted_rank,
176 x_decile_rank => p_trans_record.decile_rank,
177 x_quartile_rank => p_trans_record.quartile_rank,
178 x_transcript_type => p_trans_record.transcript_type,
179 x_mode => 'R',
180 x_date_of_issue => TRUNC(p_trans_record.date_of_issue),
181 X_OVERRIDE => NVL(p_trans_record.override_ind, 'N'),
182 X_OVERRIDE_ID => FND_GLOBAL.USER_ID,
183 X_OVERRIDE_DATE => TRUNC(SYSDATE)
184 );
185 UPDATE igs_ad_txcpt_int
186 SET status = cst_s_val_1,
187 error_code = cst_ec_val_NULL,
188 transcript_id = l_transcript_id,
189 match_ind = DECODE (
190 p_trans_record.match_ind,
191 NULL, cst_mi_val_11,
192 match_ind)
193 WHERE interface_transcript_id = p_trans_record.interface_transcript_id;
194 igs_ad_wf_001.transcript_entrd_event(p_trans_record.person_id, p_trans_record.education_id, l_transcript_id);
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 ROLLBACK TO before_create_transcript;
199 igs_ad_gen_016.extract_msg_from_stack (
200 p_msg_at_index => l_msg_at_index,
201 p_return_status => l_return_status,
202 p_msg_count => l_msg_count,
203 p_msg_data => l_msg_data,
204 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
205 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
206 l_error_text := l_msg_data;
207 l_error_code := 'E322';
208
209 IF p_enable_log = 'Y' THEN
210 igs_ad_imp_001.logerrormessage(p_trans_record.interface_transcript_id,l_msg_data,'IGS_AD_TXCPT_INT');
211 END IF;
212 ELSE
213 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405) ;
214 l_error_code := 'E518';
215 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
216
217 l_label :='igs.plsql.igs_ad_imp_024.create_new_transcript_details.exception '||l_msg_data;
218
219 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
220 fnd_message.set_token('INTERFACE_ID',p_trans_record.interface_transcript_id);
221 fnd_message.set_token('ERROR_CD','E322');
222
223 l_debug_str := fnd_message.get;
224
225 fnd_log.string_with_context( fnd_log.level_exception,
226 l_label,
227 l_debug_str, NULL,
228 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
229 END IF;
230
231 END IF;
232 UPDATE igs_ad_txcpt_int
233 SET status = cst_s_val_3,
234 error_code = l_error_code,
235 error_text = l_error_text ,
236 match_ind = DECODE (
237 p_trans_record.match_ind,
238 NULL, cst_mi_val_11,
239 match_ind)
240 WHERE interface_transcript_id = p_trans_record.interface_transcript_id;
241 END;
242 END create_new_transcript_details;
243
244 PROCEDURE update_transcript_details(p_trans_record IN OUT NOCOPY trans_cur%ROWTYPE, dup_cur_rec c_dup_cur%ROWTYPE )
245 AS
246 --------------------------------------------------------------------------
247 -- Created By : pbondugu
248 -- Date Created On : 2003/11/22
249 -- Purpose:
250 -- Know limitations, enhancements or remarks
251 -- Change History
252 -- Who When What
253 -- (reverse chronological order - newest change first)
254 --------------------------------------------------------------------------
255 l_rowid VARCHAR2(25);
256 l_var VARCHAR2(25);
257 l_validation_status NUMBER;
258 l_err_code VARCHAR2(25);
259 l_transcript_id igs_ad_txcpt_int.transcript_id%TYPE;
260 l_msg_at_index NUMBER := 0;
261 l_return_status VARCHAR2(1);
262 l_msg_count NUMBER ;
263 l_msg_data VARCHAR2(2000);
264 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
265 l_error_code VARCHAR2(4) := NULL;
266 l_error_text VARCHAR2(2000) := NULL;
267
268
269 BEGIN
270 l_transcript_id := NULL;
271 BEGIN
272 IF NVL(p_trans_record.override_ind, 'N') = 'N' THEN
273 IF p_trans_record.class_size > 0 AND p_trans_record.rank_in_class > 0 THEN
274 p_trans_record.percentile_rank := ROUND( ((p_trans_record.class_size - p_trans_record.rank_in_class)/p_trans_record.class_size)*100) ;
275 p_trans_record.decile_rank := 11 - CEIL(p_trans_record.percentile_rank/10) ;
276 p_trans_record.quartile_rank := 5 - CEIL(p_trans_record.percentile_rank/25) ;
277 p_trans_record.quintile_rank := 6 - CEIL(p_trans_record.percentile_rank/20) ;
278 ELSE
279 p_trans_record.percentile_rank := NULL;
280 p_trans_record.decile_rank := NULL;
281 p_trans_record.quartile_rank := NULL;
282 p_trans_record.quintile_rank := NULL;
283 END IF;
284 END IF;
285 l_msg_at_index := igs_ge_msg_stack.count_msg;
286 SAVEPOINT before_update_transcript;
287 igs_ad_transcript_pkg.update_row(
288 X_ROWID => dup_cur_rec.rowid,
289 X_QUINTILE_RANK =>NVL(p_trans_record.QUINTILE_RANK, dup_cur_rec.QUINTILE_RANK),
290 X_PERCENTILE_RANK => NVL(p_trans_record.PERCENTILE_RANK, dup_cur_rec.PERCENTILE_RANK),
291 X_TRANSCRIPT_ID =>dup_cur_rec.transcript_id ,
292 X_EDUCATION_ID => p_trans_record.EDUCATION_ID ,
293 X_TRANSCRIPT_STATUS=> p_trans_record.TRANSCRIPT_STATUS ,
294 X_TRANSCRIPT_SOURCE=> p_trans_record.TRANSCRIPT_SOURCE ,
295 X_DATE_OF_RECEIPT => TRUNC(NVL(p_trans_record.DATE_OF_RECEIPT, dup_cur_rec.DATE_OF_RECEIPT)),
296 X_ENTERED_GPA => NVL(p_trans_record.ENTERED_GPA, dup_cur_rec.ENTERED_GPA),
297 X_ENTERED_GS_ID => p_trans_record.ENTERED_GS_ID ,
298 X_CONV_GPA => NVL(p_trans_record.CONV_GPA, dup_cur_rec.CONV_GPA),
299 X_CONV_GS_ID => p_trans_record.CONV_GS_ID ,
300 X_TERM_TYPE => p_trans_record.TERM_TYPE ,
301 X_RANK_IN_CLASS => NVL(p_trans_record.RANK_IN_CLASS, dup_cur_rec.RANK_IN_CLASS),
302 X_CLASS_SIZE => NVL(p_trans_record.CLASS_SIZE, dup_cur_rec.CLASS_SIZE),
303 X_APPROXIMATE_RANK => NVL(p_trans_record.APPROXIMATE_RANK, dup_cur_rec.APPROXIMATE_RANK),
304 X_WEIGHTED_RANK => NVL(p_trans_record.WEIGHTED_RANK, dup_cur_rec.WEIGHTED_RANK),
305 X_DECILE_RANK => NVL(p_trans_record.DECILE_RANK, dup_cur_rec.DECILE_RANK),
306 X_QUARTILE_RANK => NVL(p_trans_record.QUARTILE_RANK, dup_cur_rec.QUARTILE_RANK),
307 X_TRANSCRIPT_TYPE => NVL(p_trans_record.TRANSCRIPT_TYPE, dup_cur_rec.TRANSCRIPT_TYPE),
308 X_DATE_OF_ISSUE => TRUNC(NVL(p_trans_record.DATE_OF_ISSUE, dup_cur_rec.DATE_OF_ISSUE)),
309 X_OVERRIDE => NVL(p_trans_record.override_ind, 'N'),
310 X_OVERRIDE_ID => FND_GLOBAL.USER_ID,
311 X_OVERRIDE_DATE => TRUNC(SYSDATE)
312
313 );
314
315 UPDATE igs_ad_txcpt_int
316 SET status = cst_s_val_1,
317 error_code = cst_ec_val_NULL,
318 transcript_id = dup_cur_rec.transcript_id,
319 match_ind = DECODE (
320 p_trans_record.match_ind,
321 NULL, cst_mi_val_18,
322 match_ind)
323 WHERE interface_transcript_id = p_trans_record.interface_transcript_id;
324
325
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 ROLLBACK TO before_update_transcript;
330 igs_ad_gen_016.extract_msg_from_stack (
331 p_msg_at_index => l_msg_at_index,
332 p_return_status => l_return_status,
333 p_msg_count => l_msg_count,
334 p_msg_data => l_msg_data,
335 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
336 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
337 l_error_text := l_msg_data;
338 l_error_code := 'E014';
339
340 IF p_enable_log = 'Y' THEN
341 igs_ad_imp_001.logerrormessage(p_trans_record.interface_transcript_id,l_msg_data,'IGS_AD_TXCPT_INT');
342 END IF;
343 ELSE
344 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
345 l_error_code := 'E518';
346 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
347
348 l_label := 'igs.plsql.igs_ad_imp_024.create_new_transcript_details.exception '||l_msg_data;
349
350 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
351 fnd_message.set_token('INTERFACE_ID',p_trans_record.interface_transcript_id);
352 fnd_message.set_token('ERROR_CD','E014');
353
354 l_debug_str := fnd_message.get;
355
356 fnd_log.string_with_context( fnd_log.level_exception,
357 l_label,
358 l_debug_str, NULL,
359 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
360 END IF;
361
362 END IF;
363 UPDATE igs_ad_txcpt_int
364 SET status = cst_s_val_3,
365 error_code = l_error_code,
366 error_text = l_error_text ,
367 match_ind = DECODE (
368 p_trans_record.match_ind,
369 NULL, cst_mi_val_18,
370 match_ind)
371 WHERE interface_transcript_id = p_trans_record.interface_transcript_id;
372 END;
373 END update_transcript_details;
374
375
376
377 BEGIN
378 --If given invalid update transcript ID then error out.
379 UPDATE IGS_AD_TXCPT_INT trans
380 SET
381 status = '3', error_code = 'E707',
382 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E707', 8405)
383 WHERE interface_run_id = p_interface_run_id
384 AND status = '2'
385 AND trans.update_transcript_id IS NOT NULL
386 AND NOT EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT
387 WHERE transcript_id = NVL(trans.update_transcript_id,transcript_id)
388 ) ;
389 COMMIT;
390
391 -- jchin - bug 4629226 Put an error in the interface table if the transcript source is external.
392
393 UPDATE IGS_AD_TXCPT_INT trans
394 SET
395 status = '3', error_code = 'E334',
396 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E334', 8405)
397 WHERE interface_run_id = p_interface_run_id
398 AND status = '2'
399 AND trans.transcript_source IS NOT NULL
400 AND EXISTS ( SELECT 1 FROM igs_ad_code_classes_v code
401 WHERE code.system_status = 'THIRD_PARTY_TRANSFER_EVAL'
402 AND code.class_type_code = 'ADM_CODE_CLASSES'
403 AND code.class = 'TRANSCRIPT_SOURCE'
404 AND code.code_id = trans.transcript_source);
405 COMMIT;
406
407 -- 1. Set STATUS to 3 for interface records with RULE = E or I and MATCH IND is not null and not '15'
408 IF p_rule IN ('E', 'I') THEN
409 UPDATE igs_ad_txcpt_int
410 SET
411 status = '3'
412 , error_code = 'E700'
413 , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
414 WHERE interface_run_id = p_interface_run_id
415 AND status = '2'
416 AND NVL (match_ind, '15') <> '15';
417 END IF;
418 COMMIT;
419
420 -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
421 IF p_rule = 'R' THEN
422 UPDATE igs_ad_txcpt_int
423 SET
424 status = '1', error_code = NULL
425 WHERE interface_run_id = p_interface_run_id
426 AND status = '2'
427 AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
428 END IF;
429 COMMIT;
430
431 -- 3. Set STATUS to 3 for interface records with multiple matching duplicate system records for RULE = I
432 IF p_rule = 'I' THEN
433 UPDATE igs_ad_txcpt_int trans
434 SET
435 status = '3'
436 , match_ind = '13'
437 WHERE interface_run_id = p_interface_run_id
438 AND status = '2'
439 AND UPDATE_TRANSCRIPT_ID IS NULL
440 AND 1 < ( SELECT COUNT(*)
441 FROM IGS_AD_TRANSCRIPT trans_oss
442 WHERE education_id = trans.education_id
443 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
444 );
445
446 END IF;
447 COMMIT;
448 -- 4. Set STATUS to 3 for interface records with multiple matching duplicate system record for RULE = R
449 -- and either MATCH IND IN (15, 21) OR IS NULL
450 IF p_rule = 'R' THEN
451 UPDATE igs_ad_txcpt_int trans
452 SET
453 status = '3'
454 , match_ind = '13'
455 WHERE interface_run_id = p_interface_run_id
456 AND status = '2'
457 AND UPDATE_TRANSCRIPT_ID IS NULL
458 AND NVL(match_ind, '15') IN ('15', '21')
459 AND 1 < ( SELECT COUNT(*)
460 FROM IGS_AD_TRANSCRIPT trans_oss
461 WHERE education_id = trans.education_id
462 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
463 );
464
465 END IF;
466 COMMIT;
467 -- 5. Set STATUS to 1 and MATCH IND to 19 for interface records with RULE = E matching OSS record(s)
468 IF p_rule = 'E' THEN
469 --If multiple exact matches are found and child records are present then error out
470 UPDATE igs_ad_txcpt_int trans
471 SET
472 status = '1'
473 , match_ind = '19'
474 , transcript_id = update_transcript_id
475 WHERE interface_run_id = p_interface_run_id
476 AND status = '2'
477 AND update_transcript_id IS NOT NULL;
478
479 UPDATE igs_ad_txcpt_int trans
480 SET
481 status = '3'
482 , match_ind = '19'
483 ,error_code = 'E708'
484 , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E708', 8405)
485 WHERE interface_run_id = p_interface_run_id
486 AND status = '2'
487 AND EXISTS( SELECT 1 FROM IGS_AD_TRMDT_INT term
488 WHERE term.interface_transcript_id = trans.interface_transcript_id
489 AND status = '2')
490 AND 1 < ( SELECT count(*) FROM igs_ad_transcript trans_oss
491 WHERE education_id = trans.education_id
492 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
493 );
494 COMMIT;
495 UPDATE igs_ad_txcpt_int trans
496 SET
497 status = '1'
498 , match_ind = '19'
499 , transcript_id = ( SELECT transcript_id FROM IGS_AD_TRANSCRIPT trans_oss
500 WHERE education_id = trans.education_id
501 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
502 AND rownum <= 1)
503 WHERE interface_run_id = p_interface_run_id
504 AND status = '2'
505 AND EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
506 WHERE education_id = trans.education_id
507 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
508 );
509 END IF;
510 COMMIT;
511
512 /**********************************************************************************
513 Create / Update the OSS record after validating successfully the interface record
514 Create
515 If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
516 RULE = R and MATCH IND = 16, 25
517 Update
518 If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
519 RULE = R and MATCH IND = 21
520
521 Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
522 This is done to have one code section for record validation, exception handling and interface table update.
523 This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
524
525 **********************************************************************************/
526
527 l_total_records_prcessed := 0;
528
529 SELECT COUNT(interface_transcript_id) INTO l_count_interface_txpt_id
530 FROM igs_ad_txcpt_int
531 WHERE interface_run_id = p_interface_run_id
532 AND status =2 ;
533 LOOP
534 EXIT WHEN l_total_records_prcessed >= l_count_interface_txpt_id;
535 SELECT
536 MIN(interface_transcript_id) , MAX(interface_transcript_id)
537 INTO l_minint , l_maxint
538 FROM igs_ad_txcpt_int
539 WHERE interface_run_id = p_interface_run_id
540 AND status =2
541 AND rownum < =100;
542
543 FOR trans_cur_rec IN trans_cur(l_minint,l_maxint) --arvsrini
544 LOOP
545
546 IF trans_cur_rec.dmlmode = cst_insert THEN
547 create_new_transcript_details(trans_cur_rec);
548 ELSIF trans_cur_rec.dmlmode = cst_update THEN
549 OPEN c_dup_cur(trans_cur_rec);
550 FETCH c_dup_cur INTO dup_cur_rec;
551 CLOSE c_dup_cur;
552 update_transcript_details(trans_cur_rec, dup_cur_rec);
553 END IF;
554 l_total_records_prcessed := l_total_records_prcessed + 1;
555
556 END LOOP;
557 COMMIT;
558
559
560
561 END LOOP;
562
563
564 /*Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching OSS record(s) in
565 ALL updateable column values, if column nullification is not allowed then the 2 DECODE should be replaced by a single NVL*/
566 IF p_rule = 'R' THEN
567 UPDATE igs_ad_txcpt_int trans
568 SET
569 status = '1'
570 , match_ind = '23'
571 WHERE interface_run_id = p_interface_run_id
572 AND status = '2'
573 AND NVL (match_ind, '15') = '15'
574 AND EXISTS ( SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
575 WHERE education_id = trans.education_id
576 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue)
577 AND transcript_type = trans.transcript_type
578 AND TRANSCRIPT_STATUS = trans.TRANSCRIPT_STATUS
579 AND TRANSCRIPT_SOURCE = trans.TRANSCRIPT_SOURCE
580 AND TRUNC(NVL(DATE_OF_RECEIPT,IGS_GE_DATE.IGSDATE('1000/01/01')))
581 = TRUNC( NVL( NVL(trans.DATE_OF_RECEIPT, DATE_OF_RECEIPT ) , IGS_GE_DATE.IGSDATE('1000/01/01')))
582 AND NVL(ENTERED_GPA,'1') = NVL(NVL(trans.ENTERED_GPA, ENTERED_GPA),'X')
583 AND NVL(ENTERED_GS_ID,-1) = NVL(NVL(trans.ENTERED_GS_ID,ENTERED_GS_ID),-1)
584 AND NVL(CONV_GPA,'X') = NVL(NVL(trans.CONV_GPA,CONV_GPA), 'X')
585 AND NVL(CONV_GS_ID,-1) = NVL(NVL(trans.CONV_GS_ID,CONV_GS_ID), -1)
586 AND NVL(TERM_TYPE,'X') = NVL(NVL(trans.TERM_TYPE,TERM_TYPE) , 'X')
587 AND NVL(RANK_IN_CLASS,-1) = NVL(NVL(trans.RANK_IN_CLASS,RANK_IN_CLASS), -1)
588 AND NVL(CLASS_SIZE,-1) = NVL(NVL(trans.CLASS_SIZE, CLASS_SIZE), -1)
589 AND NVL(APPROXIMATE_RANK,'X') = NVL(NVL(trans.APPROXIMATE_RANK, APPROXIMATE_RANK), 'X')
590 AND NVL(WEIGHTED_RANK,'X') = NVL(NVL(trans.DECILE_RANK, WEIGHTED_RANK), -1)
591 AND NVL(QUARTILE_RANK,-1) = NVL(NVL(trans.QUARTILE_RANK, QUARTILE_RANK), -1)
592 AND NVL(QUINTILE_RANK,-1) = NVL(NVL(trans.QUINTILE_RANK, QUINTILE_RANK), -1)
593 AND NVL(PERCENTILE_RANK,-1) = NVL(NVL(trans.PERCENTILE_RANK, PERCENTILE_RANK ), -1)
594 AND NVL(TRANSCRIPT_TYPE,'X') = NVL(NVL(trans.TRANSCRIPT_TYPE,TRANSCRIPT_TYPE), 'X')
595 AND NVL(DECILE_RANK, -1) = NVL(NVL(trans.DECILE_RANK,DECILE_RANK), -1)
596 AND NVL( OVERRIDE_IND, 'X' ) = NVL(NVL(trans.OVERRIDE_IND, OVERRIDE_IND), 'X' )
597 );
598 END IF;
599
600 --Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and
601 --MATCH IND <> 21, 25, ones failed above discrepancy check
602 IF p_rule = 'R' THEN
603 UPDATE igs_ad_txcpt_int trans
604 SET
605 status = '3'
606 , match_ind = '20'
607 , dup_transcript_id = trans.update_transcript_id
608 WHERE trans.interface_run_id = p_interface_run_id
609 AND status = '2'
610 AND update_transcript_id IS NOT NULL;
611 COMMIT;
612 UPDATE igs_ad_txcpt_int trans
613 SET
614 status = '3'
615 , match_ind = '20'
616 , dup_transcript_id= ( SELECT transcript_id FROM IGS_AD_TRANSCRIPT trans_oss
617 WHERE education_id = trans.education_id
618 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue))
619 WHERE interface_run_id = p_interface_run_id
620 AND status = '2'
621 AND NVL (match_ind, '15') = '15'
622 AND EXISTS (SELECT 1 FROM IGS_AD_TRANSCRIPT trans_oss
623 WHERE education_id = trans.education_id
624 AND TRUNC(date_of_issue) = TRUNC(trans.date_of_issue));
625
626 END IF;
627 COMMIT;
628
629
630 --Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
631 IF p_rule = 'R' THEN
632 UPDATE igs_ad_txcpt_int trans
633 SET
634 status = '3'
635 , error_code = 'E700'
636 , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
637 WHERE interface_run_id = p_interface_run_id
638 AND status = '2'
639 AND match_ind IS NOT NULL;
640 END IF;
641 COMMIT;
642
643 --Term and units are not defined as independent categoies
644 UPDATE igs_ad_trmdt_int a
645 SET interface_run_id = p_interface_run_id,
646 (person_id,education_id, transcript_id)
647 = (SELECT person_id,education_id, NVL(update_transcript_id, transcript_id)
648 FROM igs_ad_txcpt_int
649 WHERE interface_transcript_id = a.interface_transcript_id)
650 WHERE status IN ('1','2','4')
651 AND interface_transcript_id IN (SELECT interface_transcript_id
652 FROM igs_ad_txcpt_int
653 WHERE interface_run_id = p_interface_run_id
654 AND status IN ('1','4'));
655
656 -- If record failed only due to child record failure
657 -- then set status back to 1 and nullify error code/text
658 UPDATE igs_ad_trmdt_int
659 SET error_code = NULL,
660 error_text = NULL,
661 status = '1'
662 WHERE interface_run_id = p_interface_run_id
663 AND error_code = 'E347'
664 AND status = '4';
665
666 -- To fetch table schema name for gather statistics
667 l_return := fnd_installation.get_app_info('IGS', l_status, l_industry, l_schema);
668
669 -- Gather statistics of the table
670 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
671 tabname => 'IGS_AD_TRMDT_INT',
672 cascade => TRUE);
673
674 process_term_details (p_interface_run_id, p_rule,p_enable_log);
675
676 --Term and units are not defined as independent categoies
677 UPDATE igs_ad_tundt_int a
678 SET interface_run_id = p_interface_run_id,
679 (person_id,education_id , transcript_id, term_details_id )
680 = (SELECT person_id,education_id ,
681 transcript_id, term_details_id
682 FROM igs_ad_trmdt_int
683 WHERE interface_term_dtls_id = a.interface_term_dtls_id)
684 WHERE status IN ('1','2','4')
685 AND interface_term_dtls_id IN (SELECT interface_term_dtls_id
686 FROM igs_ad_trmdt_int
687 WHERE interface_run_id = p_interface_run_id
688 AND status IN ('1','4'));
689
690 -- Gather statistics of the table
691 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
692 tabname => 'IGS_AD_TUNDT_INT',
693 cascade => TRUE);
694
695 process_term_unit_details (p_interface_run_id, p_rule,p_enable_log);
696 END prc_trscrpt;
697
698
699
700
701
702 PROCEDURE process_term_details(
703 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
704 p_rule VARCHAR2,
705 p_enable_log VARCHAR2) AS
706
707 CURSOR term_cur (cp_start_int_id igs_ad_trmdt_int.INTERFACE_TERM_DTLS_ID%TYPE, --ARVSRINI--
708 cp_end_int_id igs_ad_trmdt_int.INTERFACE_TERM_DTLS_ID%TYPE) IS --ARVSRINI--IS
709 SELECT cst_insert dmlmode, term.rowid, term.*
710 FROM igs_ad_trmdt_int term
711 WHERE interface_run_id = p_interface_run_id
712 AND term.status = '2'
713 AND INTERFACE_TERM_DTLS_ID BETWEEN cp_start_int_id AND cp_end_int_id
714 AND (NOT EXISTS (SELECT 1 FROM IGS_AD_TERM_DETAILS term_oss
715 WHERE transcript_id = term.transcript_id
716 AND term = term.term
717 AND TRUNC(start_Date) = TRUNC(term.start_Date)
718 AND TRUNC(end_Date) = TRUNC(term.end_Date) )
719 OR ( p_rule = 'R' AND term.match_ind IN ('16', '25') )
720 )
721 UNION ALL
722 SELECT cst_update dmlmode, term.rowid, term.*
723 FROM igs_ad_trmdt_int term
724 WHERE interface_run_id = p_interface_run_id
725 AND status = '2'
726 AND INTERFACE_TERM_DTLS_ID BETWEEN cp_start_int_id AND cp_end_int_id
727 AND ( p_rule = 'I' OR (p_rule = 'R' AND term.match_ind = cst_mi_val_21))
728 AND EXISTS (SELECT 1 FROM IGS_AD_TERM_DETAILS term_oss
729 WHERE transcript_id = term.transcript_id
730 AND term = term.term
731 AND TRUNC(start_Date) = TRUNC(term.start_Date)
732 AND TRUNC(end_Date) = TRUNC(term.end_Date)
733 );
734
735 CURSOR c_dup_cur(term_cur_rec term_cur%ROWTYPE) IS
736 SELECT
737 term_oss.rowid, term_oss.*
738 FROM
739 IGS_AD_TERM_DETAILS term_oss
740 WHERE transcript_id = term_cur_rec.transcript_id
741 AND term = term_cur_rec.term
742 AND TRUNC(start_Date) = TRUNC(term_cur_rec.start_Date)
743 AND TRUNC(end_Date) = TRUNC(term_cur_rec.end_Date) ;
744
745 l_maxint NUMBER(15);
746 l_minint NUMBER(15);
747
748
749 dup_cur_rec c_dup_cur%ROWTYPE;
750 l_prog_label VARCHAR2(100) := 'igs.plsql.igs_ad_imp_024.process_term_details';
751 l_label VARCHAR2(1000) ;
752 l_debug_str VARCHAR2(1000) ;
753 l_processed_records NUMBER(5) ;
754
755 l_count_interface_trmdtls_id NUMBER;
756 l_total_records_prcessed NUMBER;
757
758 PROCEDURE create_term_details(p_term_dtls_record term_cur%ROWTYPE)
759 AS
760 --------------------------------------------------------------------------
761 -- Created By : rboddu
762 -- Date Created On : 2001/07/27
763 -- Purpose:
764 -- Know limitations, enhancements or remarks
765 -- Change History
766 -- Who When What
767 -- (reverse chronological order - newest change first)
768 --------------------------------------------------------------------------
769 l_rowid VARCHAR2(25);
770 l_var VARCHAR2(25);
771 l_term_details_id igs_ad_trmdt_int.term_details_id%TYPE;
772 l_msg_at_index NUMBER := 0;
773 l_return_status VARCHAR2(1);
774 l_msg_count NUMBER ;
775 l_msg_data VARCHAR2(2000);
776 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
777 l_error_code VARCHAR2(4) := NULL;
778 l_error_text VARCHAR2(2000):= NULL;
779 BEGIN
780 l_msg_at_index := igs_ge_msg_stack.count_msg;
781 SAVEPOINT before_create_term;
782 igs_ad_term_details_pkg.insert_row(
783 l_rowid,
784 l_term_details_id ,
785 p_term_dtls_record.TRANSCRIPT_ID ,
786 p_term_dtls_record.TERM ,
787 TRUNC(p_term_dtls_record.START_DATE) ,
788 TRUNC(p_term_dtls_record.END_DATE) ,
789 NULL,
790 NULL,
791 NULL,
792 p_term_dtls_record.TOTAL_GPA_UNITS ,
793 p_term_dtls_record.GPA
794 );
795 UPDATE igs_ad_trmdt_int
796 SET status =cst_s_val_1,
797 error_code = cst_ec_val_NULL,
798 term_details_id = l_term_details_id
799 WHERE interface_term_dtls_id = p_term_dtls_record.interface_term_dtls_id;
800
801
802 -- Update Transcript Status
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 ROLLBACK TO before_create_term;
807 igs_ad_gen_016.extract_msg_from_stack (
808 p_msg_at_index => l_msg_at_index,
809 p_return_status => l_return_status,
810 p_msg_count => l_msg_count,
811 p_msg_data => l_msg_data,
812 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
813 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
814 l_error_text := l_msg_data;
815 l_error_code := 'E322';
816
817 IF p_enable_log = 'Y' THEN
818 igs_ad_imp_001.logerrormessage(p_term_dtls_record.interface_term_dtls_id,l_msg_data,'IGS_AD_TRMDT_INT');
819 END IF;
820 ELSE
821 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
822 l_error_code := 'E518';
823 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
824
825 l_label :='igs.plsql.igs_ad_imp_024.create_term_details.exception '||l_msg_data;
826
827 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
828 fnd_message.set_token('INTERFACE_ID',p_term_dtls_record.interface_term_dtls_id);
829 fnd_message.set_token('ERROR_CD','E322');
830
831 l_debug_str := fnd_message.get;
832
833 fnd_log.string_with_context( fnd_log.level_exception,
834 l_label,
835 l_debug_str, NULL,
836 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
837 END IF;
838
839 END IF;
840 UPDATE igs_ad_trmdt_int
841 SET status = cst_s_val_3,
842 error_code = l_error_code,
843 error_text = l_error_text,
844 match_ind = DECODE (
845 p_term_dtls_record.match_ind,
846 NULL, cst_mi_val_11,
847 match_ind)
848 WHERE interface_term_dtls_id = p_term_dtls_record.interface_term_dtls_id;
849 END create_term_details;
850
851 PROCEDURE update_term_details(p_term_dtls_record term_cur%ROWTYPE, dup_cur_rec c_dup_cur%ROWTYPE )
852 AS
853 --------------------------------------------------------------------------
854 -- Created By : rboddu
855 -- Date Created On : 2001/07/27
856 -- Purpose:
857 -- Know limitations, enhancements or remarks
858 -- Change History
859 -- Who When What
860 -- (reverse chronological order - newest change first)
861 --------------------------------------------------------------------------
862 l_rowid VARCHAR2(25);
863 l_var VARCHAR2(25);
864 l_term_details_id igs_ad_trmdt_int.term_details_id%TYPE;
865 l_msg_at_index NUMBER := 0;
866 l_return_status VARCHAR2(1);
867 l_msg_count NUMBER ;
868 l_msg_data VARCHAR2(2000);
869 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
870 l_error_code VARCHAR2(4) := NULL;
871 l_error_text VARCHAR2(2000):= NULL;
872 BEGIN
873 l_msg_at_index := igs_ge_msg_stack.count_msg;
874 SAVEPOINT before_update_term;
875 igs_ad_term_details_pkg.update_row(
876 X_ROWID => dup_cur_rec.rowid ,
877 X_TERM_DETAILS_ID => dup_cur_rec.term_details_id ,
878 X_TRANSCRIPT_ID => p_term_dtls_record.transcript_id ,
879 X_TERM => p_term_dtls_record.term ,
880 X_START_DATE => TRUNC(p_term_dtls_record.start_date) ,
881 X_END_DATE => TRUNC(p_term_dtls_record.end_date) ,
882 X_TOTAL_CP_ATTEMPTED => dup_cur_rec.total_cp_attempted,
883 X_TOTAL_CP_EARNED => dup_cur_rec.total_cp_earned,
884 X_TOTAL_UNIT_GP => dup_cur_rec.total_unit_gp,
885 X_TOTAL_GPA_UNITS => NVL(p_term_dtls_record.total_gpa_units, dup_cur_rec.total_gpa_units),
886 X_GPA => NVL(p_term_dtls_record.gpa, dup_cur_rec.gpa)
887 );
888 UPDATE igs_ad_trmdt_int
889 SET status =cst_s_val_1,
890 error_code = cst_ec_val_NULL,
891 term_details_id = dup_cur_rec.term_details_id
892 WHERE interface_term_dtls_id = p_term_dtls_record.interface_term_dtls_id;
893
894 EXCEPTION
895 WHEN OTHERS THEN
896 ROLLBACK TO before_update_term;
897 igs_ad_gen_016.extract_msg_from_stack (
898 p_msg_at_index => l_msg_at_index,
899 p_return_status => l_return_status,
900 p_msg_count => l_msg_count,
901 p_msg_data => l_msg_data,
902 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
903 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
904 l_error_text := l_msg_data;
905 l_error_code := 'E014';
906
907 IF p_enable_log = 'Y' THEN
908 igs_ad_imp_001.logerrormessage( p_term_dtls_record.interface_term_dtls_id,l_msg_data,'IGS_AD_TRMDT_INT');
909 END IF;
910 ELSE
911 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
912 l_error_code := 'E518';
913 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
914
915 l_label := 'igs.plsql.igs_ad_imp_024.update_term_details.exception '||l_msg_data;
916
917 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
918 fnd_message.set_token('INTERFACE_ID',p_term_dtls_record.interface_term_dtls_id);
919 fnd_message.set_token('ERROR_CD','E014');
920
921 l_debug_str := fnd_message.get;
922
923 fnd_log.string_with_context( fnd_log.level_exception,
924 l_label,
925 l_debug_str, NULL,
926 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
927 END IF;
928
929 END IF;
930 UPDATE igs_ad_trmdt_int
931 SET status = cst_s_val_3,
932 error_code = l_error_code,
933 error_text = l_error_text,
934 match_ind = DECODE (
935 p_term_dtls_record.match_ind,
936 NULL, cst_mi_val_12,
937 match_ind)
938 WHERE interface_term_dtls_id = p_term_dtls_record.interface_term_dtls_id;
939 END update_term_details;
940
941
942
943 BEGIN
944 -- jchin Bug 4629226 Put an error in the int table if record is associated with an external transcript
945
946 UPDATE igs_ad_trmdt_int term
947 SET
948 status = '3'
949 ,error_code = 'E334'
950 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E334', 8405)
951 WHERE interface_run_id = p_interface_run_id
952 AND status = '2'
953 AND EXISTS (SELECT 1 FROM igs_ad_txcpt_int trans, igs_ad_code_classes_v code
954 WHERE trans.interface_transcript_id = term.interface_transcript_id
955 AND trans.transcript_source = code.code_id
956 AND code.class = 'TRANSCRIPT_SOURCE'
957 AND code.system_status = 'THIRD_PARTY_TRANSFER_EVAL'
958 AND code.class_type_code = 'ADM_CODE_CLASSES');
959
960 COMMIT;
961
962 --1. Set STATUS to 3 for interface records with RULE = E or I and MATCH IND is not null and not '15'
963 IF p_rule IN ('E', 'I') THEN
964 UPDATE igs_ad_trmdt_int
965 SET
966 status = '3'
967 , error_code = 'E700'
968 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
969 WHERE interface_run_id = p_interface_run_id
970 AND status = '2'
971 AND NVL (match_ind, '15') <> '15';
972 END IF;
973 COMMIT;
974
975 -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
976 IF p_rule = 'R' THEN
977 UPDATE igs_ad_trmdt_int
978 SET
979 status = '1', error_code = NULL
980 WHERE interface_run_id = p_interface_run_id
981 AND status = '2'
982 AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
983 END IF;
984 COMMIT;
985 -- 5. Set STATUS to 1 and MATCH IND to 19 for interface records with RULE = E matching OSS record(s)
986 IF p_rule = 'E' THEN
987 UPDATE igs_ad_trmdt_int term
988 SET
989 status = '3'
990 , error_code = 'E708'
991 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E708', 8405)
992 WHERE interface_run_id = p_interface_run_id
993 AND status = '2'
994 AND EXISTS ( SELECT 1 FROM igs_ad_tundt_int
995 WHERE interface_term_dtls_id = term.interface_term_dtls_id
996 AND status ='2')
997 AND 1 < ( SELECT COUNT(*) FROM IGS_AD_TERM_DETAILS term_oss
998 WHERE transcript_id = term.transcript_id
999 AND term = term.term
1000 AND TRUNC(start_Date) = TRUNC(term.start_Date)
1001 AND TRUNC(end_Date) = TRUNC(term.end_Date)
1002 );
1003
1004 UPDATE igs_ad_trmdt_int term
1005 SET
1006 status = '1'
1007 , match_ind = '19'
1008 , term_Details_id = ( SELECT term_Details_id FROM IGS_AD_TERM_DETAILS term_oss
1009 WHERE transcript_id = term.transcript_id
1010 AND term = term.term
1011 AND TRUNC(start_Date) = TRUNC(term.start_Date)
1012 AND TRUNC(end_Date) = TRUNC(term.end_Date)
1013 AND rownum <=1
1014 )
1015 WHERE interface_run_id = p_interface_run_id
1016 AND status = '2'
1017 AND EXISTS ( SELECT 1 FROM IGS_AD_TERM_DETAILS term_oss
1018 WHERE transcript_id = term.transcript_id
1019 AND term = term.term
1020 AND TRUNC(start_Date) = TRUNC(term.start_Date)
1021 AND TRUNC(end_Date) = TRUNC(term.end_Date)
1022 );
1023 END IF;
1024 COMMIT;
1025
1026 /**********************************************************************************
1027 Create / Update the OSS record after validating successfully the interface record
1028 Create
1029 If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
1030 RULE = R and MATCH IND = 16, 25
1031 Update
1032 If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
1033 RULE = R and MATCH IND = 21
1034
1035 Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
1036 This is done to have one code section for record validation, exception handling and interface table update.
1037 This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
1038
1039 **********************************************************************************/
1040
1041 l_total_records_prcessed := 0;
1042 SELECT COUNT( interface_term_dtls_id) INTO l_count_interface_trmdtls_id
1043 FROM igs_ad_trmdt_int
1044 WHERE interface_run_id = p_interface_run_id
1045 AND status =2 ;
1046
1047 LOOP
1048 EXIT WHEN l_total_records_prcessed >= l_count_interface_trmdtls_id;
1049
1050 SELECT
1051 MIN(interface_term_dtls_id) , MAX(interface_term_dtls_id)
1052 INTO l_minint , l_maxint
1053 FROM igs_ad_trmdt_int
1054 WHERE interface_run_id = p_interface_run_id
1055 AND status =2
1056 AND rownum < =100;
1057
1058 FOR term_cur_rec IN term_cur(l_minint,l_minint+99)
1059 LOOP
1060 IF term_cur_rec.dmlmode = cst_insert THEN
1061 create_term_details(term_cur_rec);
1062 ELSIF term_cur_rec.dmlmode = cst_update THEN
1063 OPEN c_dup_cur(term_cur_rec);
1064 FETCH c_dup_cur INTO dup_cur_rec;
1065 CLOSE c_dup_cur;
1066 update_term_details(term_cur_rec, dup_cur_rec);
1067 END IF;
1068 l_total_records_prcessed := l_total_records_prcessed + 1;
1069
1070 END LOOP;
1071 COMMIT;
1072
1073
1074
1075
1076
1077 END LOOP;
1078
1079
1080 /*Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching OSS record(s) in
1081 ALL updateable column values, if column nullification is not allowed then the 2 DECODE should be replaced by a single NVL*/
1082 IF p_rule = 'R' THEN
1083 UPDATE igs_ad_trmdt_int term
1084 SET
1085 status = '1'
1086 , match_ind = '23'
1087 WHERE interface_run_id = p_interface_run_id
1088 AND status = '2'
1089 AND NVL (match_ind, '15') = '15'
1090 AND EXISTS ( SELECT 1 FROM igs_ad_term_details
1091 WHERE
1092 TRANSCRIPT_ID= term.transcript_id AND
1093 TERM = term.TERM AND
1094 TRUNC(START_DATE) = TRUNC(term.START_DATE) AND
1095 TRUNC(END_DATE) = TRUNC(term.END_DATE) AND
1096 NVL(TOTAL_GPA_UNITS,-1)= NVL(term.TOTAL_GPA_UNITS,-1) AND
1097 NVL(GPA,'X')= NVL(term.GPA,'X')
1098 );
1099 END IF;
1100 COMMIT;
1101
1102 --Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and
1103 --MATCH IND <> 21, 25, ones failed above discrepancy check
1104 IF p_rule = 'R' THEN
1105 UPDATE igs_ad_trmdt_int term
1106 SET
1107 status = '3'
1108 , match_ind = '20'
1109 , dup_term_dtls_id = ( SELECT term_details_id FROM IGS_AD_TERM_DETAILS term_oss
1110 WHERE transcript_id = term.transcript_id
1111 AND term = term.term
1112 AND TRUNC(start_Date) = TRUNC(term.start_Date)
1113 AND TRUNC(end_Date) = TRUNC(term.end_Date) )
1114 WHERE interface_run_id = p_interface_run_id
1115 AND status = '2'
1116 AND NVL (match_ind, '15') = '15'
1117 AND EXISTS (SELECT 1 FROM IGS_AD_TERM_DETAILS term_oss
1118 WHERE transcript_id = term.transcript_id
1119 AND term = term.term
1120 AND TRUNC(start_Date) = TRUNC(term.start_Date)
1121 AND TRUNC(end_Date) = TRUNC(term.end_Date) );
1122
1123 END IF;
1124 COMMIT;
1125
1126
1127 --Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
1128 IF p_rule = 'R' THEN
1129 UPDATE igs_ad_trmdt_int term
1130 SET
1131 status = '3'
1132 , error_code = 'E700'
1133 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
1134 WHERE interface_run_id = p_interface_run_id
1135 AND status = '2'
1136 AND match_ind IS NOT NULL;
1137 END IF;
1138 COMMIT;
1139
1140 END process_term_details;
1141
1142
1143
1144
1145 PROCEDURE process_term_unit_details(
1146 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
1147 p_rule VARCHAR2,
1148 p_enable_log VARCHAR2) AS
1149
1150 CURSOR term_unit_cur(cp_start_int_id IGS_AD_TUNDT_INT.INTERFACE_TERM_UNITDTLS_ID%TYPE, --ARVSRINI--
1151 cp_end_int_id IGS_AD_TUNDT_INT.INTERFACE_TERM_UNITDTLS_ID%TYPE) IS --ARVSRINI-- IS
1152 SELECT cst_insert dmlmode, unit.rowid, unit.*
1153 FROM igs_ad_tundt_int unit
1154 WHERE interface_run_id = p_interface_run_id
1155 AND unit.status = '2'
1156 AND INTERFACE_TERM_UNITDTLS_ID BETWEEN cp_start_int_id AND cp_end_int_id --ARVSRINI--
1157 AND (NOT EXISTS (SELECT 1 FROM igs_ad_term_unitdtls unit_oss
1158 WHERE term_details_id = unit.term_details_id
1159 AND unit = unit.unit )
1160 OR ( p_rule = 'R' AND unit.match_ind IN ('16', '25') )
1161 )
1162 UNION ALL
1163 SELECT cst_update dmlmode, unit.rowid, unit.*
1164 FROM igs_ad_tundt_int unit
1165 WHERE interface_run_id = p_interface_run_id
1166 AND status = '2'
1167 AND INTERFACE_TERM_UNITDTLS_ID BETWEEN cp_start_int_id AND cp_end_int_id --ARVSRINI--
1168 AND ( p_rule = 'I' OR (p_rule = 'R' AND unit.match_ind = cst_mi_val_21))
1169 AND EXISTS (SELECT 1 FROM igs_ad_term_unitdtls unit_oss
1170 WHERE term_details_id = unit.term_details_id
1171 AND unit = unit.unit
1172 );
1173
1174 CURSOR c_dup_cur(term_unit_rec term_unit_cur%ROWTYPE) IS
1175 SELECT
1176 unit_oss.rowid, unit_oss.*
1177 FROM
1178 igs_ad_term_unitdtls unit_oss
1179 WHERE term_details_id = term_unit_rec.term_details_id
1180 AND unit = term_unit_rec.unit ;
1181
1182
1183
1184 l_maxint NUMBER(15);
1185 l_minint NUMBER(15);
1186
1187 dup_cur_rec c_dup_cur%ROWTYPE;
1188 l_prog_label VARCHAR2(100) := 'igs.plsql.igs_ad_imp_024.process_term_unit_details';
1189 l_label VARCHAR2(1000) ;
1190 l_debug_str VARCHAR2(1000) ;
1191 l_total_records_prcessed NUMBER;
1192 l_count_interface_unitdtls_id NUMBER;
1193
1194 PROCEDURE create_term_unit_details(p_term_unitdtls_record term_unit_cur%ROWTYPE)
1195 AS
1196 --------------------------------------------------------------------------
1197 -- Created By : rboddu
1198 -- Date Created On : 2001/07/27
1199 -- Purpose:
1200 -- Know limitations, enhancements or remarks
1201 -- Change History
1202 -- Who When What
1203 -- (reverse chronological order - newest change first)
1204 --------------------------------------------------------------------------
1205 l_rowid VARCHAR2(25);
1206 l_var VARCHAR2(25);
1207 l_unit_details_id igs_ad_tundt_int.unit_details_id%TYPE;
1208 l_msg_at_index NUMBER := 0;
1209 l_return_status VARCHAR2(1);
1210 l_msg_count NUMBER ;
1211 l_msg_data VARCHAR2(2000);
1212 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1213 l_error_code VARCHAR2(4) := NULL;
1214 l_error_text VARCHAR2(2000):= NULL;
1215 BEGIN
1216 l_msg_at_index := igs_ge_msg_stack.count_msg;
1217 SAVEPOINT before_create_unit;
1218 igs_ad_term_unitdtls_pkg.insert_row(
1219 l_rowid ,
1220 l_unit_details_id ,
1221 p_term_unitdtls_record.term_details_id ,
1222 p_term_unitdtls_record.UNIT,
1223 p_term_unitdtls_record.UNIT_DIFFICULTY ,
1224 p_term_unitdtls_record.UNIT_NAME,
1225 p_term_unitdtls_record.CP_ATTEMPTED,
1226 p_term_unitdtls_record.CP_EARNED ,
1227 p_term_unitdtls_record.GRADE,
1228 p_term_unitdtls_record.UNIT_GRADE_POINTS
1229 );
1230
1231
1232 UPDATE igs_ad_tundt_int
1233 SET status = cst_s_val_1,
1234 error_code = NULL,
1235 unit_details_id = l_unit_details_id
1236 WHERE interface_term_unitdtls_id = p_term_unitdtls_record.interface_term_unitdtls_id;
1237
1238
1239
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 ROLLBACK TO before_create_unit;
1244 igs_ad_gen_016.extract_msg_from_stack (
1245 p_msg_at_index => l_msg_at_index,
1246 p_return_status => l_return_status,
1247 p_msg_count => l_msg_count,
1248 p_msg_data => l_msg_data,
1249 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1250 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1251 l_error_text := l_msg_data;
1252 l_error_code := 'E322';
1253
1254 IF p_enable_log = 'Y' THEN
1255 igs_ad_imp_001.logerrormessage(p_term_unitdtls_record.interface_term_dtls_id,l_msg_data,'IGS_AD_TUNDT_INT');
1256 END IF;
1257 ELSE
1258 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
1259 l_error_code := 'E518';
1260 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1261
1262 l_label :='igs.plsql.igs_ad_imp_024.create_term_unit_details.exception '||l_msg_data;
1263
1264 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1265 fnd_message.set_token('INTERFACE_ID', p_term_unitdtls_record.interface_term_dtls_id);
1266 fnd_message.set_token('ERROR_CD','E322');
1267
1268 l_debug_str := fnd_message.get;
1269
1270 fnd_log.string_with_context( fnd_log.level_exception,
1271 l_label,
1272 l_debug_str, NULL,
1273 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1274 END IF;
1275
1276 END IF;
1277 UPDATE igs_ad_tundt_int
1278 SET status = cst_s_val_3,
1279 error_code = l_error_code,
1280 error_text = l_error_text,
1281 match_ind = DECODE (
1282 p_term_unitdtls_record.match_ind,
1283 NULL, cst_mi_val_11,
1284 match_ind)
1285 WHERE interface_term_unitdtls_id = p_term_unitdtls_record.interface_term_unitdtls_id;
1286 END create_term_unit_details;
1287
1288 PROCEDURE update_term_unit_details(p_term_unitdtls_record term_unit_cur%ROWTYPE, dup_cur_rec c_dup_cur%ROWTYPE )
1289 AS
1290 --------------------------------------------------------------------------
1291 -- Created By : rboddu
1292 -- Date Created On : 2001/07/27
1293 -- Purpose:
1294 -- Know limitations, enhancements or remarks
1295 -- Change History
1296 -- Who When What
1297 -- (reverse chronological order - newest change first)
1298 --------------------------------------------------------------------------
1299 l_rowid VARCHAR2(25);
1300 l_var VARCHAR2(25);
1301 l_msg_at_index NUMBER := 0;
1302 l_return_status VARCHAR2(1);
1303 l_msg_count NUMBER ;
1304 l_msg_data VARCHAR2(2000);
1305 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1306 l_error_code VARCHAR2(4) := NULL;
1307 l_error_text VARCHAR2(2000):= NULL;
1308 BEGIN
1309 l_msg_at_index := igs_ge_msg_stack.count_msg;
1310 SAVEPOINT before_update_unit;
1311 igs_ad_term_unitdtls_pkg.update_row(
1312 X_ROWID => dup_cur_rec.rowid ,
1313 X_UNIT_DETAILS_ID => dup_cur_rec.UNIT_DETAILS_ID ,
1314 X_TERM_DETAILS_ID => p_term_unitdtls_record.TERM_DETAILS_ID ,
1315 X_UNIT => p_term_unitdtls_record.UNIT ,
1316 X_UNIT_DIFFICULTY => p_term_unitdtls_record.UNIT_DIFFICULTY ,
1317 X_UNIT_NAME => p_term_unitdtls_record.UNIT_NAME ,
1318 X_CP_ATTEMPTED => NVL(p_term_unitdtls_record.CP_ATTEMPTED, dup_cur_rec.cp_attempted),
1319 X_CP_EARNED => NVL(p_term_unitdtls_record.CP_EARNED, dup_cur_rec.CP_EARNED),
1320 X_GRADE => NVL(p_term_unitdtls_record.GRADE,dup_cur_rec.GRADE),
1321 X_UNIT_GRADE_POINTS => NVL(p_term_unitdtls_record.UNIT_GRADE_POINTS, dup_cur_rec.UNIT_GRADE_POINTS)
1322 );
1323
1324 UPDATE igs_ad_tundt_int
1325 SET status =cst_s_val_1,
1326 error_code = cst_ec_val_NULL,
1327 term_details_id = dup_cur_rec.UNIT_DETAILS_ID
1328 WHERE interface_term_unitdtls_id = p_term_unitdtls_record.interface_term_unitdtls_id;
1329 EXCEPTION
1330 WHEN OTHERS THEN
1331 ROLLBACK TO before_update_unit;
1332 igs_ad_gen_016.extract_msg_from_stack (
1333 p_msg_at_index => l_msg_at_index,
1334 p_return_status => l_return_status,
1335 p_msg_count => l_msg_count,
1336 p_msg_data => l_msg_data,
1337 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1338 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1339 l_error_text := l_msg_data;
1340 l_error_code := 'E014';
1341
1342 IF p_enable_log = 'Y' THEN
1343 igs_ad_imp_001.logerrormessage( p_term_unitdtls_record.interface_term_unitdtls_id,l_msg_data,'IGS_AD_TUNDT_INT');
1344 END IF;
1345 ELSE
1346 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
1347 l_error_code := 'E518';
1348 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1349
1350 l_label := 'igs.plsql.igs_ad_imp_024.update_term_details.exception '||l_msg_data;
1351
1352 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1353 fnd_message.set_token('INTERFACE_ID',p_term_unitdtls_record.interface_term_unitdtls_id);
1354 fnd_message.set_token('ERROR_CD','E014');
1355
1356 l_debug_str := fnd_message.get;
1357
1358 fnd_log.string_with_context( fnd_log.level_exception,
1359 l_label,
1360 l_debug_str, NULL,
1361 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1362 END IF;
1363
1364 END IF;
1365 UPDATE igs_ad_tundt_int
1366 SET status = cst_s_val_3,
1367 error_code = l_error_code,
1368 error_text = l_error_text,
1369 match_ind = DECODE (
1370 p_term_unitdtls_record.match_ind,
1371 NULL, cst_mi_val_12,
1372 match_ind)
1373 WHERE interface_term_unitdtls_id = p_term_unitdtls_record.interface_term_unitdtls_id;
1374 END update_term_unit_details;
1375
1376
1377
1378 BEGIN
1379
1380 -- jchin Bug 4629226 Put an error in the int table if record is associated with an external transcript
1381
1382 UPDATE igs_ad_tundt_int unit
1383 SET
1384 status = '3'
1385 , error_code = 'E334'
1386 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E334', 8405)
1387 WHERE interface_run_id = p_interface_run_id
1388 AND status = '2'
1389 AND EXISTS (SELECT 1 FROM igs_ad_txcpt_int trans, igs_ad_code_classes_v code,
1390 igs_ad_trmdt_int term
1391 WHERE trans.interface_transcript_id = term.interface_transcript_id
1392 AND term.interface_term_dtls_id = unit.interface_term_dtls_id
1393 AND trans.transcript_source = code.code_id
1394 AND code.class = 'TRANSCRIPT_SOURCE'
1395 AND code.system_status = 'THIRD_PARTY_TRANSFER_EVAL'
1396 AND code.class_type_code = 'ADM_CODE_CLASSES');
1397
1398 COMMIT;
1399
1400 --1. Set STATUS to 3 for interface records with RULE = E or I and MATCH IND is not null and not '15'
1401 IF p_rule IN ('E', 'I') THEN
1402 UPDATE igs_ad_tundt_int
1403 SET
1404 status = '3'
1405 , error_code = 'E700'
1406 , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
1407 WHERE interface_run_id = p_interface_run_id
1408 AND status = '2'
1409 AND NVL (match_ind, '15') <> '15';
1410 END IF;
1411 COMMIT;
1412
1413 -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
1414 IF p_rule = 'R' THEN
1415 UPDATE igs_ad_tundt_int
1416 SET
1417 status = '1', error_code = NULL
1418 WHERE interface_run_id = p_interface_run_id
1419 AND status = '2'
1420 AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
1421 END IF;
1422 COMMIT;
1423
1424 -- 5. Set STATUS to 1 and MATCH IND to 19 for interface records with RULE = E matching OSS record(s)
1425 IF p_rule = 'E' THEN
1426 UPDATE igs_ad_tundt_int unit
1427 SET
1428 status = '1'
1429 , match_ind = '19'
1430 WHERE interface_run_id = p_interface_run_id
1431 AND status = '2'
1432 AND EXISTS ( SELECT 1 FROM igs_ad_term_unitdtls unit_oss
1433 WHERE term_details_id = unit.term_details_id
1434 AND unit = unit.unit
1435 );
1436 END IF;
1437 COMMIT;
1438
1439 /**********************************************************************************
1440 Create / Update the OSS record after validating successfully the interface record
1441 Create
1442 If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
1443 RULE = R and MATCH IND = 16, 25
1444 Update
1445 If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
1446 RULE = R and MATCH IND = 21
1447
1448 Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
1449 This is done to have one code section for record validation, exception handling and interface table update.
1450 This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
1451
1452 **********************************************************************************/
1453
1454 l_total_records_prcessed := 0;
1455 SELECT COUNT(interface_term_unitdtls_id) INTO l_count_interface_unitdtls_id
1456 FROM igs_ad_tundt_int
1457 WHERE interface_run_id = p_interface_run_id
1458 AND status =2 ;
1459
1460 LOOP
1461 EXIT WHEN l_total_records_prcessed >= l_count_interface_unitdtls_id;
1462
1463 SELECT
1464 MIN(interface_term_unitdtls_id) , MAX(interface_term_unitdtls_id)
1465 INTO l_minint , l_maxint
1466 FROM igs_ad_tundt_int
1467 WHERE interface_run_id = p_interface_run_id
1468 AND status =2
1469 AND rownum < =100;
1470
1471 FOR term_unit_cur_rec IN term_unit_cur(l_minint,l_minint+99) --arvsrini
1472 LOOP
1473 IF term_unit_cur_rec.dmlmode = cst_insert THEN
1474 create_term_unit_details(term_unit_cur_rec);
1475 ELSIF term_unit_cur_rec.dmlmode = cst_update THEN
1476 OPEN c_dup_cur(term_unit_cur_rec);
1477 FETCH c_dup_cur INTO dup_cur_rec;
1478 CLOSE c_dup_cur;
1479 update_term_unit_details(term_unit_cur_rec, dup_cur_rec);
1480 END IF;
1481 l_total_records_prcessed := l_total_records_prcessed + 1;
1482
1483 END LOOP;
1484 COMMIT;
1485
1486
1487
1488 END LOOP;
1489
1490 /*Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching OSS record(s) in
1491 ALL updateable column values, if column nullification is not allowed then the 2 DECODE should be replaced by a single NVL*/
1492 IF p_rule = 'R' THEN
1493 UPDATE igs_ad_tundt_int unit
1494 SET
1495 status = '1'
1496 , match_ind = '23'
1497 WHERE interface_run_id = p_interface_run_id
1498 AND status = '2'
1499 AND NVL (match_ind, '15') = '15'
1500 AND EXISTS ( SELECT 1 FROM igs_ad_term_unitdtls
1501 WHERE NVL(UNIT,'X') = NVL(unit.UNIT,'X') AND
1502 NVL(UNIT_DIFFICULTY,-1) = NVL(unit.UNIT_DIFFICULTY,-1) AND
1503 NVL(UNIT_NAME,'X') = NVL(unit.UNIT_NAME,'X') AND
1504 NVL(CP_ATTEMPTED,-1) = NVL(unit.CP_ATTEMPTED,-1) AND
1505 NVL(CP_EARNED,-1) = NVL(unit.CP_EARNED,-1) AND
1506 NVL(GRADE,'X') = NVL(unit.GRADE,'X') AND
1507 NVL(UNIT_GRADE_POINTS,-1) = NVL(unit.UNIT_GRADE_POINTS,-1)
1508 );
1509 END IF;
1510 COMMIT;
1511
1512 --Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and
1513 --MATCH IND <> 21, 25, ones failed above discrepancy check
1514 IF p_rule = 'R' THEN
1515 UPDATE igs_ad_tundt_int unit
1516 SET
1517 status = '3'
1518 , match_ind = '20'
1519 , dup_term_unitdtls_id = ( SELECT unit_details_id
1520 FROM igs_ad_term_unitdtls unit_oss
1521 WHERE term_details_id = unit.term_details_id
1522 AND unit = unit.unit )
1523 WHERE interface_run_id = p_interface_run_id
1524 AND status = '2'
1525 AND NVL (match_ind, '15') = '15'
1526 AND EXISTS (SELECT 1
1527 FROM igs_ad_term_unitdtls unit_oss
1528 WHERE term_details_id = unit.term_details_id
1529 AND unit = unit.unit );
1530
1531 END IF;
1532 COMMIT;
1533
1534
1535 --Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
1536 IF p_rule = 'R' THEN
1537 UPDATE igs_ad_tundt_int unit
1538 SET
1539 status = '3'
1540 , error_code = 'E700'
1541 , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
1542 WHERE interface_run_id = p_interface_run_id
1543 AND status = '2'
1544 AND match_ind IS NOT NULL;
1545 END IF;
1546 COMMIT;
1547
1548 END process_term_unit_details;
1549
1550
1551 END igs_ad_imp_024;
[Home] [Help]