1 PACKAGE BODY IGS_AD_IMP_013 AS
2 /* $Header: IGSAD91B.pls 120.1 2006/04/13 05:52:25 stammine noship $ */
3 /*Change History
4 || Who When What
5 || ssaleem 13_OCT_2003 Bug : 3130316
6 || Logging is modified to include logging mechanism
7 || pkpatel 18-MAY-2003 Bug 2853521
8 || Removed the procedure prc_address_usages since it was not getting used anywhere
9 */
10
11
12 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
13 -- Added the local package variables as part of Import process enhancements
14 cst_rule_val_I CONSTANT VARCHAR2(1) := 'I';
15 cst_rule_val_E CONSTANT VARCHAR2(1) := 'E';
16 cst_rule_val_R CONSTANT VARCHAR2(1) := 'R';
17
18
19 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
20 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
21 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
22 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
23 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
24 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
25 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
26 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
27 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
28 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
29 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
30 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
31 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
32 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
33 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
34 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
35
36 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
37 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
38 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
39 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
40
41 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
42 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
43 cst_ec_val_NULL CONSTANT VARCHAR2(4) := NULL;
44
45 cst_insert CONSTANT VARCHAR2(20) := 'INSERT';
46 cst_update CONSTANT VARCHAR2(20) := 'UPDATE';
47 cst_first_row CONSTANT VARCHAR2(20) := 'FIRST ROW';
48 cst_partial_update CONSTANT VARCHAR2(20) := 'PARTIAL UPDATE';
49 cst_unique_record CONSTANT NUMBER := 1;
50 l_request_id CONSTANT NUMBER := fnd_global.conc_request_id;
51 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes*******************/
52
53 PROCEDURE prc_pe_type(
54 p_source_type_id IN NUMBER,
55 p_batch_id IN NUMBER
56 )
57 AS
58 /*
59 || Created By : [email protected]
60 || Created On : 06-Jul-2001
61 || Purpose : This procedure is for importing person type Information.
62 || DLD: Import Person Type. Enh Bug# 2853521.
63 || Known limitations, enhancements or remarks :
64 || Change History :
65 || Who When What
66 */
67
68 l_rule VARCHAR2(1);
69 l_staff_person_type_code igs_pe_person_types.person_type_code%TYPE;
70 l_faculty_person_type_code igs_pe_person_types.person_type_code%TYPE;
71 l_system_type igs_pe_person_types.system_type%TYPE;
72 l_error_code VARCHAR2(30);
73 l_discp_exists VARCHAR2(1);
74 l_processed_records NUMBER(5) := 0;
75 l_var VARCHAR2(1);
76 l_default_date DATE ;
77 l_hr_installed VARCHAR2(1);
78
79 l_prog_label VARCHAR2(100);
80 l_label VARCHAR2(100);
81 l_debug_str VARCHAR2(2000);
82 l_enable_log VARCHAR2(1);
83 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
84 -- Cursor to select all the pending records.
85 CURSOR per_type_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86 SELECT pty.*, i.person_id
87 FROM igs_pe_type_int pty,
88 igs_ad_interface_all i
89 WHERE pty.interface_id = i.interface_id AND
90 pty.status = '2' AND
91 pty.interface_run_id = cp_interface_run_id AND
92 i.interface_run_id = cp_interface_run_id;
93
94 -- Cursor to check for duplicate record
95 CURSOR dup_per_type_cur(cp_person_id igs_ad_interface_all.person_id%TYPE,
96 cp_person_type_code igs_pe_type_int.person_type_code%TYPE,
97 cp_start_date igs_pe_type_int.start_date%TYPE) IS
98 SELECT rowid,type_instance_id, end_date,emplmnt_category_code
99 FROM igs_pe_typ_instances_all
100 WHERE person_id = cp_person_id AND
101 UPPER(person_type_code) = UPPER(cp_person_type_code) AND
102 TRUNC(start_date) = TRUNC(cp_start_date);
103
104 dup_per_type_rec dup_per_type_cur%ROWTYPE;
105
106
107 -- Cursor to check HR Mapping
108 CURSOR hr_map_cur(cp_system_type igs_pe_person_types.system_type%TYPE) IS
109 SELECT person_type_code
110 FROM igs_pe_per_type_map_v
111 WHERE system_type = cp_system_type;
112
113 -- Cursor to check whether any Staff/Faculty records are present
114 CURSOR type_exist_cur(cp_person_type_code igs_pe_person_types.person_type_code%TYPE,
115 cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
116 SELECT 'X'
117 FROM igs_pe_type_int pty
118 WHERE pty.status = '2' AND
119 pty.person_type_code = cp_person_type_code AND
120 pty.interface_run_id = cp_interface_run_id;
121
122
123 -- Private procedure to create person type
124 PROCEDURE create_person_type(
125 p_person_type_rec IN per_type_cur%ROWTYPE,
126 p_system_type IN igs_pe_person_types.system_type%TYPE,
127 p_default_date IN DATE
128 )
129 AS
130 lv_rowid ROWID;
131 l_ended_by fnd_user.user_name%TYPE;
132 l_end_method VARCHAR2(30);
133 l_type_instance_id igs_pe_typ_instances_all.type_instance_id%TYPE;
134 l_message_name VARCHAR2(30);
135 l_app VARCHAR2(50);
136 l_error_code VARCHAR2(30);
137 l_exists VARCHAR2(1);
138
139 CURSOR user_name_cur(cp_user_id fnd_user.user_id%TYPE) IS
140 SELECT user_name
141 FROM fnd_user
142 WHERE user_id = cp_user_id;
143
144 CURSOR date_overlap(cp_person_id igs_ad_interface.person_id%TYPE,
145 cp_person_type_code igs_pe_type_int.person_type_code%TYPE,
146 cp_start_date igs_pe_type_int.start_date%TYPE,
147 cp_end_date igs_pe_type_int.end_date%TYPE,
148 cp_default_date DATE) IS
149 SELECT 'Y'
150 FROM igs_pe_typ_instances_all
151 WHERE person_id = cp_person_id AND
152 person_type_code = cp_person_type_code AND
153 ( NVL(cp_end_date,cp_default_date) BETWEEN start_date AND NVL(end_date,cp_default_date)
154 OR cp_start_date BETWEEN start_date AND NVL(end_date,cp_default_date)
155 OR ( cp_start_date < start_date AND
156 NVL(end_date,cp_default_date) < NVL(cp_end_date,cp_default_date)));
157
158 CURSOR emp_cat_status(cp_person_id igs_ad_interface.person_id%TYPE,
159 cp_start_date igs_pe_type_int.start_date%TYPE,
160 cp_end_date igs_pe_type_int.end_date%TYPE,
161 cp_default_date DATE) IS
162 SELECT NULL
163 FROM igs_pe_typ_instances_all typ,igs_pe_person_types sys
164 WHERE typ.person_id = cp_person_id AND
165 sys.person_type_code = typ.person_type_code AND
166 sys.system_type IN ('FACULTY','STAFF') AND
167 ( NVL(cp_end_date,cp_default_date) BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
168 OR cp_start_date BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
169 OR ( cp_start_date < typ.start_date AND
170 NVL(typ.end_date,cp_default_date) < NVL(cp_end_date,cp_default_date))) AND
171 typ.emplmnt_category_code IS NOT NULL;
172
173 BEGIN
174
175 -- Overlap check need not be done for 'USER_DEFINED' system type
176 IF p_system_type <> 'USER_DEFINED' THEN
177 OPEN date_overlap(p_person_type_rec.person_id,
178 p_person_type_rec.person_type_code,
179 p_person_type_rec.start_date,
180 p_person_type_rec.end_date,
181 p_default_date);
182 FETCH date_overlap INTO l_exists;
183 IF date_overlap%FOUND THEN
184 CLOSE date_overlap;
185 UPDATE igs_pe_type_int
186 SET status = '3',
187 error_code = 'E295'
188 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
189 IF l_enable_log = 'Y' THEN
190 igs_ad_imp_001.logerrormessage(p_person_type_rec.interface_person_type_id,'E295');
191 END IF;
192 RETURN;
193 END IF;
194 CLOSE date_overlap;
195 END IF;
196
197 IF p_person_type_rec.emplmnt_category_code IS NOT NULL THEN
198 IF p_system_type IN ('FACULTY','STAFF') THEN
199 OPEN emp_cat_status(p_person_type_rec.person_id,
200 p_person_type_rec.start_date,
201 p_person_type_rec.end_date,
202 p_default_date);
203 FETCH emp_cat_status INTO l_exists;
204 IF emp_cat_status%FOUND THEN
205 CLOSE emp_cat_status;
206
207 UPDATE igs_pe_type_int
208 SET status = '3',
209 error_code = 'E585'
210 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
211
212 IF l_enable_log = 'Y' THEN
213 igs_ad_imp_001.logerrormessage(p_person_type_rec.interface_person_type_id,'E585');
214 END IF;
215 RETURN;
216 END IF;
217 CLOSE emp_cat_status;
218 END IF;
219 END IF;
220
221 IF p_person_type_rec.end_date IS NOT NULL THEN
222
223 l_ended_by := fnd_global.user_id;
224 l_end_method := 'END_IMPORT';
225 ELSE
226 l_ended_by := NULL;
227 l_end_method := NULL;
228 END IF;
229
230 igs_pe_typ_instances_pkg.insert_row
231 (
232 x_rowid => lv_rowid,
233 x_person_id => p_person_type_rec.person_id,
234 x_course_cd => null,
235 x_type_instance_id => l_type_instance_id,
236 x_person_type_code => p_person_type_rec.person_type_code,
237 x_cc_version_number => null,
238 x_funnel_status => null,
239 x_admission_appl_number => null,
240 x_nominated_course_cd => null,
241 x_ncc_version_number => null,
242 x_sequence_number => null,
243 x_start_date => p_person_type_rec.start_date,
244 x_end_date => p_person_type_rec.end_date,
245 x_create_method => 'CREATE_IMPORT',
246 x_ended_by => l_ended_by,
247 x_end_method => l_end_method,
248 x_org_id => null,
249 x_emplmnt_category_code => p_person_type_rec.emplmnt_category_code
250 );
251
252 UPDATE igs_pe_type_int
253 SET status = '1'
254 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
259
260 IF l_message_name IN ('IGS_AD_PROSPCT_XST_NO_EVAL','IGS_AD_EVAL_XST_NO_PROSPCT') THEN
261 l_error_code := 'E294';
262 ELSE
263 l_error_code := 'E322';
264
265 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
266
267
268 l_label := 'igs.plsql.igs_ad_imp_013.prc_pe_type.exception';
269
270 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
271 fnd_message.set_token('INTERFACE_ID',p_person_type_rec.interface_person_type_id);
272 fnd_message.set_token('ERROR_CD',l_error_code);
273
274 l_debug_str := fnd_message.get || ' ' || SQLERRM;
275
276 fnd_log.string_with_context( fnd_log.level_exception,
277 l_label,
278 l_debug_str, NULL,
279 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
280 END IF;
281 END IF;
282
283 IF l_enable_log = 'Y' THEN
284 igs_ad_imp_001.logerrormessage(p_person_type_rec.interface_person_type_id,l_error_code);
285 END IF;
286
287
288 UPDATE igs_pe_type_int
289 SET status = '3',
290 error_code = l_error_code
291 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
292
293 END create_person_type;
294
295 -- Private procedure to update person type
296 PROCEDURE update_person_type(p_person_type_rec IN per_type_cur%ROWTYPE,
297 p_type_instance_id IN igs_pe_typ_instances_all.type_instance_id%TYPE,
298 p_rowid IN ROWID,
299 p_end_date IN igs_pe_typ_instances_all.end_date%TYPE,
300 p_emplmnt_category_code IN igs_pe_typ_instances_all.emplmnt_category_code%TYPE,
301 p_system_type IN igs_pe_person_types.system_type%TYPE,
302 p_default_date IN DATE)
303 AS
304
305 l_ended_by fnd_user.user_id%TYPE;
306 l_end_method VARCHAR2(30);
307 l_type_instance_id igs_pe_typ_instances_all.type_instance_id%TYPE;
308 l_message_name VARCHAR2(30);
309 l_app VARCHAR2(50);
310 l_error_code VARCHAR2(30);
311 l_exists VARCHAR2(1);
312
313 CURSOR user_name_cur(cp_user_id fnd_user.user_id%TYPE) IS
314 SELECT user_name
315 FROM fnd_user
316 WHERE user_id = cp_user_id;
317
318 CURSOR date_overlap(cp_person_id igs_ad_interface.person_id%TYPE,
319 cp_person_type_code igs_pe_type_int.person_type_code%TYPE,
320 cp_start_date igs_pe_type_int.start_date%TYPE,
321 cp_end_date igs_pe_type_int.end_date%TYPE,
322 cp_default_date DATE) IS
323 SELECT 'Y'
324 FROM igs_pe_typ_instances_all
325 WHERE person_id = cp_person_id AND
326 person_type_code = cp_person_type_code AND
327 start_date <> cp_start_date AND
328 ( NVL(cp_end_date,cp_default_date) BETWEEN start_date AND NVL(end_date,cp_default_date)
329 OR cp_start_date BETWEEN start_date AND NVL(end_date,cp_default_date)
330 OR ( cp_start_date < start_date AND
331 NVL(end_date,cp_default_date) < NVL(cp_end_date,cp_default_date)));
332
333 CURSOR emp_cat_status(cp_person_id igs_ad_interface.person_id%TYPE,
334 cp_start_date igs_pe_type_int.start_date%TYPE,
335 cp_end_date igs_pe_type_int.end_date%TYPE,
336 cp_default_date DATE) IS
337 SELECT null FROM igs_pe_typ_instances_all typ,igs_pe_person_types sys
338 WHERE
339 typ.person_id = cp_person_id AND
340 sys.person_type_code = typ.person_type_code AND
341 sys.system_type in ('FACULTY','STAFF') AND
342 p_rowid <> typ.rowid AND
343 ( NVL(cp_end_date,cp_default_date) BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
344 OR cp_start_date BETWEEN typ.start_date AND NVL(typ.end_date,cp_default_date)
345 OR ( cp_start_date < typ.start_date AND
346 NVL(typ.end_date,cp_default_date) < NVL(cp_end_date,cp_default_date))) AND
347 typ.emplmnt_category_code IS NOT NULL;
348
349 BEGIN
350
351 -- Update only if end date is given and its different from what present in the OSS
352
353 IF p_system_type <> 'USER_DEFINED' THEN
354 OPEN date_overlap(p_person_type_rec.person_id,
355 p_person_type_rec.person_type_code,
356 p_person_type_rec.start_date,
357 p_person_type_rec.end_date,
358 p_default_date);
359 FETCH date_overlap INTO l_exists;
360 IF date_overlap%FOUND THEN
361 CLOSE date_overlap;
362
363 UPDATE igs_pe_type_int
364 SET status = '3',
365 error_code = 'E295'
366 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
367
368 IF l_enable_log = 'Y' THEN
369 igs_ad_imp_001.logerrormessage(p_person_type_rec.interface_person_type_id,'E295');
370 END IF;
371
372 RETURN;
373 END IF;
374 CLOSE date_overlap;
375 END IF;
376
377 IF p_person_type_rec.emplmnt_category_code IS NOT NULL THEN
378 IF p_system_type IN ('FACULTY','STAFF') THEN
379 OPEN emp_cat_status(p_person_type_rec.person_id,
380 p_person_type_rec.start_date,
381 p_person_type_rec.end_date,
382 p_default_date);
383 FETCH emp_cat_status INTO l_exists;
384 IF emp_cat_status%FOUND THEN
385 CLOSE emp_cat_status;
386
387 UPDATE igs_pe_type_int
388 SET status = '3',
389 error_code = 'E585'
390 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
391
392 IF l_enable_log = 'Y' THEN
393 igs_ad_imp_001.logerrormessage(p_person_type_rec.interface_person_type_id,'E585');
394 END IF;
395
396 RETURN;
397 END IF;
398 CLOSE emp_cat_status;
399 END IF;
400 END IF;
401
402 IF p_person_type_rec.end_date IS NOT NULL AND
403 (NVL(p_end_date,p_default_date) <> p_person_type_rec.end_date) THEN
404 l_end_method := 'END_IMPORT';
405 l_ended_by := fnd_global.user_id;
406 END IF;
407
408 igs_pe_typ_instances_pkg.update_row
409 (
410 x_rowid => p_rowid,
411 x_person_id => p_person_type_rec.person_id,
412 x_course_cd => null,
413 x_type_instance_id => p_type_instance_id,
414 x_person_type_code => p_person_type_rec.person_type_code,
415 x_cc_version_number => null,
416 x_funnel_status => null,
417 x_admission_appl_number => null,
418 x_nominated_course_cd => null,
419 x_ncc_version_number => null,
420 x_sequence_number => null,
421 x_start_date => p_person_type_rec.start_date,
422 x_end_date => nvl(p_person_type_rec.end_date,p_end_date),
423 x_create_method => 'CREATE_IMPORT',
424 x_ended_by => l_ended_by,
425 x_end_method => l_end_method,
426 x_emplmnt_category_code => nvl(p_person_type_rec.emplmnt_category_code,p_emplmnt_category_code)
427 );
428
429 -- END IF;
430
431 UPDATE igs_pe_type_int
432 SET status = '1',
433 match_ind = '18'
434 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
439
440 IF l_message_name IN ('IGS_AD_PROSPCT_XST_NO_EVAL','IGS_AD_EVAL_XST_NO_PROSPCT') THEN
441 l_error_code := 'E294';
442
443 ELSE
444 l_error_code := 'E014';
445
446 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
447
448
449 l_label := 'igs.plsql.igs_ad_imp_013.update_person_type.exception1';
450
451 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
452 fnd_message.set_token('INTERFACE_ID',p_person_type_rec.interface_person_type_id);
453 fnd_message.set_token('ERROR_CD',l_error_code);
454
455 l_debug_str := fnd_message.get || ' ' || SQLERRM;
456
457 fnd_log.string_with_context( fnd_log.level_exception,
458 l_label,
459 l_debug_str, NULL,
460 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
461 END IF;
462 END IF;
463
464 IF l_enable_log = 'Y' THEN
465 igs_ad_imp_001.logerrormessage(p_person_type_rec.interface_person_type_id,l_error_code);
466 END IF;
467
468 UPDATE igs_pe_type_int
469 SET status = '3',
470 error_code = l_error_code
471 WHERE interface_person_type_id = p_person_type_rec.interface_person_type_id;
472
473 END update_person_type;
474
475 PROCEDURE validate_record(p_person_type_rec IN per_type_cur%ROWTYPE,
476 p_system_type OUT NOCOPY VARCHAR2,
477 p_error_code OUT NOCOPY VARCHAR2,
478 p_hr_installed IN VARCHAR2
479 )
480 IS
481 l_birth_date igs_pe_person_base_v.birth_date%TYPE;
482
483 CURSOR system_type_cur(cp_person_type_code igs_pe_type_int.person_type_code%TYPE)
484 IS
485 SELECT system_type
486 FROM igs_pe_person_types
487 WHERE person_type_code = cp_person_type_code
488 AND closed_ind = 'N';
489
490 CURSOR birth_date_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE)
491 IS
492 SELECT birth_date
493 FROM igs_pe_person_base_v
494 WHERE person_id = cp_person_id;
495
496 CURSOR chk_lkup_code(l_lookup_code igs_lookup_values.lookup_code%TYPE,
497 l_lookup_type igs_lookup_values.lookup_type%TYPE,
498 l_enabled_flag VARCHAR2)IS
499 SELECT NULL FROM IGS_LOOKUP_VALUES
500 WHERE lookup_type = l_lookup_type AND
501 lookup_code = l_lookup_code AND
502 enabled_flag = l_enabled_flag;
503
504 l_var VARCHAR2(1);
505
506 BEGIN
507
508 -- person type code should be defined in OSS and active.
509 OPEN system_type_cur(p_person_type_rec.person_type_code);
510 FETCH system_type_cur INTO p_system_type;
511 IF system_type_cur%NOTFOUND THEN
512 p_error_code := 'E291';
513 CLOSE system_type_cur;
514 RETURN;
515 END IF;
516 CLOSE system_type_cur;
517
518 -- person type code should not be a system defined one.
519 IF p_system_type NOT IN('ADVISOR','EVALUATOR','EXTERNAL_CONTACT','FACULTY','STAFF','USER_DEFINED','INTERVIEWER') THEN
520 p_error_code := 'E292';
521 RETURN;
522 END IF;
523
524 -- Validation for Employment Category
525 IF p_person_type_rec.emplmnt_category_code IS NOT NULL THEN
526
527 -- check to see whether HR is installed and used
528 IF P_HR_INSTALLED = 'Y' THEN
529 p_error_code := 'E298';
530 RETURN;
531 END IF;
532
533 --Validation to check that the person type imported is STAFF of FACULTY if the employment category is not null
534 IF p_system_type NOT IN('FACULTY','STAFF') THEN
535 p_error_code := 'E299';
536 RETURN;
537 END IF;
538
539 -- <nsidana 9/23/2003 Commenting this code to validate the lookup as now we'll call the function to validate the code.>
540
541 /* -- validation to check the employment category lookupcode
542 OPEN chk_lkup_code(p_person_type_rec.emplmnt_category_code,'PE_EMP_CATEGORIES','Y');
543 FETCH chk_lkup_code INTO l_var;
544 IF chk_lkup_code%NOTFOUND THEN
545 p_error_code := 'E297';
546 CLOSE chk_lkup_code;
547 RETURN;
548 END IF;
549 CLOSE chk_lkup_code;
550 */
551
552 -- Make a call to the function which checks for valid lookup type / code combination.
553
554 IF NOT(igs_pe_pers_imp_001.validate_lookup_type_code('PE_EMP_CATEGORIES',p_person_type_rec.emplmnt_category_code,8405))
555 THEN
556 p_error_code := 'E297';
557 RETURN;
558 END IF;
559 END IF;
560
561 -- person type start date must not be a futire date
562 IF p_person_type_rec.start_date > TRUNC(SYSDATE) THEN
563 p_error_code := 'E296';
564 RETURN;
565 END IF;
566
567 OPEN birth_date_cur(p_person_type_rec.person_id);
568 FETCH birth_date_cur INTO l_birth_date;
569 CLOSE birth_date_cur;
570
571 -- person type start date must not be less than birth date of the person
572 IF l_birth_date IS NOT NULL THEN
573 IF p_person_type_rec.start_date < l_birth_date THEN
574 p_error_code := 'E222';
575 RETURN;
576 END IF;
577 END IF;
578
579 -- person type end date must not be less than start date
580 IF p_person_type_rec.end_date IS NOT NULL THEN
581 IF p_person_type_rec.start_date > p_person_type_rec.end_date THEN
582 p_error_code := 'E208';
583 RETURN;
584 END IF;
585 END IF;
586
587 END validate_record;
588
589 BEGIN
590 l_default_date := igs_ge_date.igsdate('9999/01/01');
591
592 l_prog_label := 'igs.plsql.igs_ad_imp_013.prc_pe_type';
593 l_label := 'igs.plsql.igs_ad_imp_013.prc_pe_type.';
594 l_enable_log := igs_ad_imp_001.g_enable_log;
595 l_interface_run_id:=igs_ad_imp_001.g_interface_run_id; -- fetching the interface run ID from the AD imp process.
596 -- Every child records needs to be updated with this value.
597
598 -- If HRMS is installed and HR mapping is done then update all the staff/faculty records as invalid.
599 IF igs_en_gen_001.check_hrms_installed = 'Y' THEN
600 OPEN hr_map_cur('STAFF');
601 FETCH hr_map_cur INTO l_staff_person_type_code;
602 CLOSE hr_map_cur;
603
604 IF l_staff_person_type_code IS NOT NULL THEN
605
606 OPEN type_exist_cur(l_staff_person_type_code,l_interface_run_id);
607 FETCH type_exist_cur INTO l_var;
608
609 IF type_exist_cur%FOUND THEN
610 UPDATE igs_pe_type_int pti
611 SET pti.status = '3',
612 pti.error_code = 'E293'
613 WHERE person_type_code = l_staff_person_type_code AND
614 status = '2' AND
615 pti.interface_run_id = l_interface_run_id;
616
617 IF l_enable_log = 'Y' THEN
618 igs_ad_imp_001.logerrormessage(l_staff_person_type_code,'E293');
619 END IF;
620 END IF;
621
622 CLOSE type_exist_cur;
623
624 END IF;
625
626
627 OPEN hr_map_cur('FACULTY');
628 FETCH hr_map_cur INTO l_faculty_person_type_code;
629 CLOSE hr_map_cur;
630
631 IF l_faculty_person_type_code IS NOT NULL THEN
632 OPEN type_exist_cur(l_faculty_person_type_code,l_interface_run_id);
633 FETCH type_exist_cur INTO l_var;
634
635 IF type_exist_cur%FOUND THEN
636
637 UPDATE igs_pe_type_int pti
638 SET pti.status = '3',
639 pti.error_code = 'E293'
640 WHERE person_type_code = l_faculty_person_type_code AND
641 status = '2' AND
642 interface_run_id = l_interface_run_id;
643
644 IF l_enable_log = 'Y' THEN
645 igs_ad_imp_001.logerrormessage(l_faculty_person_type_code,'E293');
646 END IF;
647 END IF;
648 CLOSE type_exist_cur;
649 END IF;
650 END IF;
651
652 l_hr_installed := IGS_PE_GEN_002.GET_HR_INSTALLED;
653
654 -- <nsidana 9/24/2003 Import process enhancements>
655 -- Fetching the discrepency rule before the loop.
656
657 l_rule := igs_ad_imp_001.find_source_cat_rule(p_source_type_id, 'PERSON_TYPE');
658
659
660 -- 1. If the rule is E or I, and the match ind column is not null, update all the records to status 3 as they are invalids.
661
662 IF ((l_rule='E') OR (l_rule='I')) THEN
663 UPDATE igs_pe_type_int pti
664 SET status = '3',
665 error_code = 'E695'
666 WHERE pti.status = '2' AND
667 pti.interface_run_id = l_interface_run_id AND
668 pti.match_ind IS NOT NULL;
669 END IF;
670
671 -- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
672
673 IF (l_rule = 'E') THEN
674 UPDATE igs_pe_type_int pti
675 SET status = '1',
676 match_ind = '19'
677 WHERE pti.status = '2' AND
678 pti.interface_run_id = l_interface_run_id AND
679 pti.match_ind IS NULL AND
680 EXISTS (SELECT 1
681 FROM igs_pe_typ_instances_all pi,
682 igs_ad_interface_all ai
683 WHERE pti.interface_id = ai.interface_id AND
684 ai.interface_run_id = l_interface_run_id AND
685 ai.person_id = pi.person_id AND
686 UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
687 TRUNC(pti.start_date) = TRUNC(pi.start_date));
688 END IF;
689
690 -- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
691
692 IF (l_rule='R') THEN
693 UPDATE igs_pe_type_int pti
694 SET status = '1'
695 WHERE pti.status = '2' AND
696 pti.interface_run_id = l_interface_run_id AND
697 pti.match_ind IN ('18','19','22','23');
698 END IF;
699
700
701 -- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
702
703 IF (l_rule = 'R') THEN
704 UPDATE igs_pe_type_int pti
705 SET status = '3',
706 error_code = 'E695'
707 WHERE pti.status = '2' AND
708 pti.interface_run_id = l_interface_run_id AND
709 (pti.match_ind IS NOT NULL AND pti.match_ind NOT IN ('21','25'));
710 END IF;
711
712
713 -- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
714
715 IF (l_rule ='R') THEN
716 UPDATE igs_pe_type_int pti
717 SET status = '1',
718 match_ind = '23'
719 WHERE pti.status = '2' AND
720 pti.interface_run_id = l_interface_run_id AND
721 pti.match_ind IS NULL AND
722 EXISTS
723 (SELECT 1
724 FROM igs_pe_typ_instances_all pi,
725 igs_ad_interface_all ai
726 WHERE pti.interface_id = ai.interface_id AND
727 ai.interface_run_id = l_interface_run_id AND
728 ai.person_id = pi.person_id AND
729 NVL(UPPER(pti.emplmnt_category_code),'*!*') = NVL(UPPER(pi.emplmnt_category_code),'*!*') AND
730 UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
731 TRUNC(pti.start_date)= TRUNC(pi.start_date) AND
732 ((pti.end_date IS NULL AND pi.end_date IS NULL)
733 OR (TRUNC(pti.end_date) = TRUNC(pi.end_date)) ));
734 END IF;
735
736 -- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
737 -- and value from the OSS table.
738
739 IF (l_rule ='R') THEN
740 UPDATE igs_pe_type_int pti
741 SET status='3',
742 match_ind='20',
743 dup_type_instance_id=(SELECT pi.type_instance_id
744 FROM igs_pe_typ_instances_all pi,
745 igs_ad_interface_all ai
746 WHERE pti.interface_id = ai.interface_id AND
747 ai.interface_run_id = l_interface_run_id AND
748 ai.person_id = pi.person_id AND
749 UPPER(pti.person_type_code)=UPPER(pi.person_type_code) AND
750 TRUNC(pti.start_date)=TRUNC(pi.start_date))
751 WHERE pti.status='2' AND
752 pti.interface_run_id = l_interface_run_id AND
753 pti.match_ind IS NULL AND
754 EXISTS
755 (SELECT 1
756 FROM igs_pe_typ_instances_all pi,
757 igs_ad_interface_all ai
758 WHERE pti.interface_id=ai.interface_id AND
759 ai.interface_run_id = l_interface_run_id AND
760 ai.person_id = pi.person_id AND
761 UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
762 TRUNC(pti.start_date) = TRUNC(pi.start_date));
763 END IF;
764
765 -- process the rest of the records.
766
767 FOR per_type_rec IN per_type_cur(l_interface_run_id)
768 LOOP
769 l_processed_records := l_processed_records + 1;
770
771 per_type_rec.start_date := TRUNC(per_type_rec.start_date);
772 per_type_rec.end_date := TRUNC(per_type_rec.end_date);
773 per_type_rec.emplmnt_category_code := UPPER(per_type_rec.emplmnt_category_code);
774
775 l_error_code := NULL;
776
777 validate_record(per_type_rec,
778 l_system_type,
779 l_error_code,
780 l_hr_installed);
781
782 -- All validation passed.
783 IF l_error_code IS NULL THEN
784
785 dup_per_type_rec.type_instance_id := NULL;
786 dup_per_type_rec.end_date := NULL;
787
788 -- Check for duplicate record.
789 OPEN dup_per_type_cur(per_type_rec.person_id,
790 per_type_rec.person_type_code,
791 per_type_rec.start_date);
792 FETCH dup_per_type_cur INTO dup_per_type_rec;
793 CLOSE dup_per_type_cur;
794
795 -- Duplicate record. Process as per the rule defined.
796 IF dup_per_type_rec.type_instance_id IS NOT NULL THEN
797 IF l_rule = 'I' THEN
798 update_person_type(per_type_rec,
799 dup_per_type_rec.type_instance_id,
800 dup_per_type_rec.rowid,
801 dup_per_type_rec.end_date,
802 dup_per_type_rec.emplmnt_category_code,
803 l_system_type,
804 l_default_date);
805
806 ELSIF l_rule = 'R' THEN
807 IF per_type_rec.match_ind = '21' THEN
808 update_person_type(per_type_rec,
809 dup_per_type_rec.type_instance_id,
810 dup_per_type_rec.rowid,
811 dup_per_type_rec.end_date,
812 dup_per_type_rec.emplmnt_category_code,
813 l_system_type,
814 l_default_date);
815 END IF;
816 END IF;
817 ELSE
818 -- not found in OSS. Create a new one.
819 create_person_type(per_type_rec,
820 l_system_type,
821 l_default_date);
822
823 END IF;
824 ELSE
825 -- Validation failed. Update with proper error code.
826
827 UPDATE igs_pe_type_int
828 SET status = '3',
829 error_code = l_error_code
830 WHERE interface_person_type_id = per_type_rec.interface_person_type_id;
831
832 IF l_enable_log = 'Y' THEN
833 igs_ad_imp_001.logerrormessage(per_type_rec.interface_person_type_id,l_error_code);
834 END IF;
835 END IF;
836
837 -- Commit for every 100 records..
838 IF l_processed_records >= 100 THEN
839 COMMIT;
840 END IF;
841 END LOOP;
842
843 -- Commit at the end if the staff/faculty records are processed or l_processed_records < 100
844 COMMIT;
845 END prc_pe_type;
846
847 PROCEDURE prc_address_usages (
848 p_source_type_id IN NUMBER,
849 p_batch_id IN NUMBER )
850 IS
851 /*
852 || Created By : [email protected]
853 || Created On : 06-Jul-2001
854 || Purpose : Stubbed the procedure since its not being used any where.
855 || Known limitations, enhancements or remarks :
856 || Change History :
857 || Who When What
858 */
859 BEGIN
860 NULL;
861 END prc_address_usages;
862
863
864
865 PROCEDURE PRC_PE_ACAD_HIST (
866 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
867 p_rule VARCHAR2,
868 p_enable_log VARCHAR2
869 ) AS
870
871 -- Added to overcome snapshot-old error {Rollback segment Error }
872
873 l_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
874 l_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE;
875 l_count_interface_acadhis_id NUMBER;
876 l_total_records_prcessed NUMBER;
877 CURSOR acad_hist(cp_min_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE,
878 cp_max_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE)
879 IS
880 -- Institution does not match so creating
881 SELECT cst_insert dmlmode, rowid, a.*
882 FROM IGS_AD_ACADHIS_INT_ALL a
883 WHERE a.interface_run_id = p_interface_run_id
884 AND a.status = '2'
885 AND ( NOT EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
886 WHERE h1.party_id = a.person_id
887 AND h2.party_number = a.institution_code
888 AND h2.party_id = h1.school_party_id )
889 OR ( p_rule = 'R' AND a.match_ind IN ('16', '25') )
890 )
891 AND UPDATE_EDUCATION_ID IS NULL
892 AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id
893
894 --Exact match
895 UNION ALL
896 SELECT cst_update dmlmode, rowid, a.*
897 FROM IGS_AD_ACADHIS_INT_ALL a
898 WHERE a.interface_run_id = p_interface_run_id
899 AND a.status = '2'
900 AND ( p_rule = 'I' OR (p_rule = 'R' AND a.match_ind = cst_mi_val_21))
901 AND ( EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
902 WHERE h1.party_id = a.person_id
903 AND h2.party_number = a.institution_code
904 AND h2.party_id = h1.school_party_id
905 AND TRUNC(NVL(h1.start_date_attended,
906 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
907 TRUNC(NVL(a.start_date,
908 TO_DATE('01-01-0001','DD-MM-YYYY')))
909 AND TRUNC(NVL(h1.last_date_attended,
910 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
911 TRUNC(NVL(a.end_date,
912 TO_DATE('01-01-0001','DD-MM-YYYY')))
913 )
914 OR UPDATE_EDUCATION_ID IS NOT NULL
915 )
916 AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id
917 --First record update
918 -- ( matching instituion code but dates do not match and no partial match
919 -- ( both start date and end date for all OSS matching records is NULL))
920 UNION ALL
921 SELECT cst_first_row dmlmode, rowid, a.*
922 FROM IGS_AD_ACADHIS_INT_ALL a
923 WHERE a.interface_run_id = p_interface_run_id
924 AND a.status = '2'
925 AND UPDATE_EDUCATION_ID IS NULL
926 AND NVL(a.start_date,a.end_date) IS NOT NULL
927 AND EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
928 WHERE h1.party_id = a.person_id
929 AND h2.party_id = h1.school_party_id
930 AND h2.party_number = a.institution_code
931 AND h1.start_date_attended IS NULL
932 AND h1.last_date_attended IS NULL
933 )
934 AND NOT EXISTS ( SELECT 1 FROM hz_Education h1, hz_parties h2
935 WHERE h1.party_id = a.person_id
936 AND h2.party_number = a.institution_code
937 AND h2.party_id = h1.school_party_id
938 AND NVL(h1.start_date_attended,
939 h1.last_date_attended) IS NOT NULL
940 )
941 AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id
942 -- Partial match finds single record, hence update if discrepancy rule is 'I'/'R-21' - per bug 3417941
943 UNION ALL
944 SELECT cst_partial_update dmlmode, rowid, a.*
945 FROM IGS_AD_ACADHIS_INT_ALL a
946 WHERE a.interface_run_id = p_interface_run_id
947 AND a.status = '2'
948 AND UPDATE_EDUCATION_ID IS NULL
949 AND (p_rule = 'I' OR (p_rule = 'R' AND a.match_ind = cst_mi_val_21))
950 AND 1 = (SELECT count(*) FROM hz_Education h1, hz_parties h2
951 WHERE h1.party_id = a.person_id
952 AND h2.party_id = h1.school_party_id
953 AND h2.party_number = a.institution_code
954 AND NVL(h1.start_date_attended,h1.last_date_attended) IS NOT NULL
955 AND (TRUNC(NVL(h1.start_date_attended,
956 TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
957 TRUNC(NVL(a.start_date,
958 TO_DATE('01-01-0001','DD-MM-YYYY')))
959 OR TRUNC(NVL(h1.last_date_attended,
960 TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
961 TRUNC(NVL(a.end_date,
962 TO_DATE('01-01-0001','DD-MM-YYYY'))))
963 )
964 AND NOT EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
965 WHERE h1.party_id = a.person_id
966 AND h2.party_number = a.institution_code
967 AND h2.party_id = h1.school_party_id
968 AND TRUNC(NVL(h1.start_date_attended,
969 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
970 TRUNC(NVL(a.start_date,
971 TO_DATE('01-01-0001','DD-MM-YYYY')))
972 AND TRUNC(NVL(h1.last_date_attended,
973 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
974 TRUNC(NVL(a.end_date,
975 TO_DATE('01-01-0001','DD-MM-YYYY')))
976 )
977 AND UPDATE_EDUCATION_ID IS NULL
978 AND a.interface_acadhis_id BETWEEN cp_min_interface_acadhis_id AND cp_max_interface_acadhis_id;
979
980 CURSOR c_dup_cur (acad_hist_rec acad_hist%ROWTYPE ) IS
981 SELECT ah.*
982 FROM igs_ad_acad_history_v ah
983 WHERE
984 ( acad_hist_rec.update_education_id IS NULL
985 AND person_id = acad_hist_rec.person_id
986 AND institution_code = acad_hist_rec.institution_code
987 AND TRUNC(NVL(start_date,
988 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
989 TRUNC(NVL(acad_hist_rec.start_date,
990 TO_DATE('01-01-0001','DD-MM-YYYY')))
991 AND TRUNC(NVL(end_date,
992 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
993 TRUNC(NVL(acad_hist_rec.end_date,
994 TO_DATE('01-01-0001','DD-MM-YYYY')))
995 )
996 OR (acad_hist_rec.update_education_id IS NOT NULL
997 AND ah.education_id = acad_hist_rec.update_education_id
998 );
999 dup_cur_rec c_dup_cur %ROWTYPE;
1000 --This cursor will opened in case of first record updates.
1001 CURSOR c_dup_cur_first (acad_hist_rec acad_hist%ROWTYPE ) IS
1002 SELECT ah.*
1003 FROM igs_ad_acad_history_v ah
1004 WHERE person_id = acad_hist_rec.person_id
1005 AND institution_code = acad_hist_rec.institution_code
1006 AND CREATION_DATE =
1007 (SELECT MIN(he.creation_date) FROM hz_education he, hz_parties hz
1008 WHERE he.party_id = acad_hist_rec.person_id
1009 AND hz.party_id = he.school_party_id
1010 AND hz.party_number = acad_hist_rec.institution_code );
1011
1012
1013 CURSOR c_dup_cur_partial (acad_hist_rec acad_hist%ROWTYPE ) IS
1014 SELECT ah.*
1015 FROM igs_ad_acad_history_v ah
1016 WHERE person_id = acad_hist_rec.person_id
1017 AND institution_code = acad_hist_rec.institution_code;
1018
1019
1020 person_history_rec acad_hist%ROWTYPE;
1021 l_rowid VARCHAR2(25);
1022 l_error_code VARCHAR2(10);
1023 l_records_processed NUMBER := 0;
1024 l_prog_label VARCHAR2(100) ;
1025 l_processed_records NUMBER(5) := 0;
1026 l_label VARCHAR2(100);
1027 l_debug_str VARCHAR2(2000);
1028
1029 --start of local validation procedure
1030 PROCEDURE Validate_ACADHIS(
1031 PERSON_HISTORY_REC IN acad_hist%ROWTYPE,
1032 p_error_code OUT NOCOPY VARCHAR2,
1033 p_status OUT NOCOPY VARCHAR2
1034 ) AS
1035 l_var VARCHAR2(1);
1036
1037 CURSOR c_val_inst_cd_non_uk_cur IS
1038 SELECT hp.rowid row_id
1039 FROM
1040 hz_parties p,
1041 igs_pe_hz_parties hp
1042 WHERE hp.party_id = p.party_id
1043 AND hp.inst_org_ind = 'I'
1044 AND p.party_number = person_history_rec.institution_code;
1045
1046 CURSOR c_val_inst_cd_uk_cur IS
1047 SELECT HP.rowid row_id
1048 FROM HZ_PARTIES P,
1049 IGS_PE_HZ_PARTIES HP,
1050 IGS_OR_ORG_INST_TYPE_ALL OIT
1051 WHERE HP.PARTY_ID = P.PARTY_ID
1052 AND HP.INST_ORG_IND = 'I'
1053 AND p.party_number = person_history_rec.institution_code
1054 AND HP.OI_INSTITUTION_TYPE = OIT.INSTITUTION_TYPE (+)
1055 AND OIT.SYSTEM_INST_TYPE IN ('POST-SECONDARY','SECONDARY');
1056
1057
1058 c_val_inst_cd_rec c_val_inst_cd_non_uk_cur%ROWTYPE;
1059
1060 BEGIN
1061 -- log header
1062
1063 c_val_inst_cd_rec.row_id := NULL;
1064 --1. Institution Code
1065 IF PERSON_HISTORY_REC.INSTITUTION_CODE IS NOT NULL THEN
1066 IF FND_PROFILE.VALUE('OSS_COUNTRY_CODE') <> 'GB' THEN
1067 OPEN c_val_inst_cd_non_uk_cur;
1068 FETCH c_val_inst_cd_non_uk_cur INTO c_val_inst_cd_rec;
1069 CLOSE c_val_inst_cd_non_uk_cur;
1070 ELSE
1071 OPEN c_val_inst_cd_uk_cur;
1072 FETCH c_val_inst_cd_uk_cur INTO c_val_inst_cd_rec;
1073 CLOSE c_val_inst_cd_uk_cur;
1074 END IF;
1075 IF c_val_inst_cd_rec.row_id IS NULL THEN
1076 p_error_code := 'E401';
1077 p_status := '3';
1078 RETURN;
1079 END IF;
1080 END IF;
1081
1082 --4. PROGRAM_CODE
1083
1084 --5. VERSION_NUMBER
1085
1086 --6. START_DATE
1087 IF PERSON_HISTORY_REC.START_DATE IS NOT NULL THEN
1088 IF NOT PERSON_HISTORY_REC.START_DATE < SYSDATE THEN
1089 p_error_code := 'E405';
1090 p_status := '3';
1091 RETURN;
1092 END IF;
1093 END IF;
1094
1095 --7. END_DATE
1096 IF PERSON_HISTORY_REC.END_DATE IS NOT NULL
1097 AND PERSON_HISTORY_REC.START_DATE IS NOT NULL THEN
1098 IF NOT PERSON_HISTORY_REC.END_DATE >= PERSON_HISTORY_REC.START_DATE THEN
1099 p_error_code := 'E406';
1100 p_status := '3';
1101 RETURN;
1102
1103 END IF;
1104 END IF;
1105
1106 --8. PLANNED_COMPLETION_DATE
1107 IF PERSON_HISTORY_REC.PLANNED_COMPLETION_DATE IS NOT NULL THEN
1108 IF NOT PERSON_HISTORY_REC.PLANNED_COMPLETION_DATE >= PERSON_HISTORY_REC.START_DATE THEN
1109 p_error_code := 'E408';
1110 p_status := '3';
1111 RETURN;
1112 END IF;
1113 END IF;
1114
1115 --9. SELFREP_TOTAL_CP_ATTEMPTED
1116 IF PERSON_HISTORY_REC.SELFREP_TOTAL_CP_ATTEMPTED IS NOT NULL THEN
1117 IF NOT PERSON_HISTORY_REC.SELFREP_TOTAL_CP_ATTEMPTED >= 0 THEN
1118 p_error_code := 'E409';
1119 p_status := '3';
1120 RETURN;
1121 END IF;
1122 END IF;
1123
1124 --10. SELFREP_TOTAL_CP_EARNED
1125 IF PERSON_HISTORY_REC.SELFREP_TOTAL_CP_EARNED IS NOT NULL THEN
1126 IF NOT PERSON_HISTORY_REC.SELFREP_TOTAL_CP_EARNED >= 0 THEN
1127 p_error_code := 'E410';
1128 p_status := '3';
1129 RETURN;
1130 END IF;
1131 END IF;
1132
1133 --11. SELFREP_TOTAL_GP_UNITS_ATTEMP
1134 IF PERSON_HISTORY_REC.SELFREP_TOTAL_GP_UNITS_ATTEMP IS NOT NULL THEN
1135 IF NOT PERSON_HISTORY_REC.SELFREP_TOTAL_GP_UNITS_ATTEMP >= 0 THEN
1136 p_error_code := 'E411';
1137 p_status := '3';
1138 RETURN;
1139 END IF;
1140 END IF;
1141
1142 --12. SELFREP_INST_GPA
1143 IF PERSON_HISTORY_REC.SELFREP_INST_GPA IS NOT NULL THEN
1144 IF NOT PERSON_HISTORY_REC.SELFREP_INST_GPA >= 0 THEN
1145 p_error_code := 'E412';
1146 p_status := '3';
1147 RETURN;
1148 END IF;
1149 END IF;
1150
1151 --13. SELFREP_GRADING_SCALE_ID
1152 IF PERSON_HISTORY_REC.SELFREP_GRADING_SCALE_ID IS NOT NULL THEN
1153 IF NOT PERSON_HISTORY_REC.SELFREP_GRADING_SCALE_ID > 0 THEN
1154 p_error_code := 'E413';
1155 p_status := '3';
1156 RETURN;
1157 END IF;
1158 END IF;
1159
1160 --14. SELFREP_WEIGHTED_GPA
1161 IF PERSON_HISTORY_REC.SELFREP_WEIGHTED_GPA IS NOT NULL THEN
1162 IF NOT PERSON_HISTORY_REC.SELFREP_WEIGHTED_GPA IN('Y','N') THEN
1163 p_error_code := 'E414';
1164 p_status := '3';
1165 RETURN;
1166 END IF;
1167 END IF;
1168
1169 --15. SELFREP_RANK_IN_CLASS
1170 IF PERSON_HISTORY_REC.SELFREP_RANK_IN_CLASS IS NOT NULL THEN
1171 IF NOT PERSON_HISTORY_REC.SELFREP_RANK_IN_CLASS > 0 THEN
1172 p_error_code := 'E415';
1173 p_status := '3';
1174 RETURN;
1175 END IF;
1176 END IF;
1177
1178 --16. SELFREP_WEIGHTED_RANK
1179 IF PERSON_HISTORY_REC.SELFREP_WEIGHTED_RANK IS NOT NULL THEN
1180 IF NOT PERSON_HISTORY_REC.SELFREP_WEIGHTED_RANK IN('Y','N') THEN
1181 p_error_code := 'E416';
1182 p_status := '3';
1183 RETURN;
1184 END IF;
1185 END IF;
1186
1187 /*-------------------------------------------------------------------------
1188 The code from this point onwards was written as part of the
1189 ID prospective applicant part 2 of 1.
1190 --------------------------------------------------------------------------*/
1191 --17. SELFREP_CLASS_SIZE
1192 IF PERSON_HISTORY_REC.CLASS_SIZE IS NOT NULL THEN
1193 IF NOT PERSON_HISTORY_REC.CLASS_SIZE > 0 THEN
1194 p_error_code := 'E417';
1195 p_status := '3';
1196 RETURN;
1197 END IF;
1198 END IF;
1199 /*-------------------------------------------------------------------------
1200 The code upto this point was written as part of the ID prospective
1201 applicant part 2 of 1. The starting point is mentioned above.
1202 --------------------------------------------------------------------------*/
1203 --
1204 -- Added the call to validate the descriptive flexfield columns as a part of Admissions 1.8 DLD // kamohan
1205 --
1206 -- 18. DESCRIPTIVE FLEX FIELDS
1207 IF NOT Igs_Ad_Imp_018.validate_desc_flex (
1208 p_attribute_category => PERSON_HISTORY_REC.attribute_category,
1209 p_attribute1 => PERSON_HISTORY_REC.attribute1,
1210 p_attribute2 => PERSON_HISTORY_REC.attribute2,
1211 p_attribute3 => PERSON_HISTORY_REC.attribute3,
1212 p_attribute4 => PERSON_HISTORY_REC.attribute4,
1213 p_attribute5 => PERSON_HISTORY_REC.attribute5,
1214 p_attribute6 => PERSON_HISTORY_REC.attribute6,
1215 p_attribute7 => PERSON_HISTORY_REC.attribute7,
1216 p_attribute8 => PERSON_HISTORY_REC.attribute8,
1217 p_attribute9 => PERSON_HISTORY_REC.attribute9,
1218 p_attribute10 => PERSON_HISTORY_REC.attribute10,
1219 p_attribute11 => PERSON_HISTORY_REC.attribute11,
1220 p_attribute12 => PERSON_HISTORY_REC.attribute12,
1221 p_attribute13 => PERSON_HISTORY_REC.attribute13,
1222 p_attribute14 => PERSON_HISTORY_REC.attribute14,
1223 p_attribute15 => PERSON_HISTORY_REC.attribute15,
1224 p_attribute16 => PERSON_HISTORY_REC.attribute16,
1225 p_attribute17 => PERSON_HISTORY_REC.attribute17,
1226 p_attribute18 => PERSON_HISTORY_REC.attribute18,
1227 p_attribute19 => PERSON_HISTORY_REC.attribute19,
1228 p_attribute20 => PERSON_HISTORY_REC.attribute20,
1229 p_desc_flex_name => 'IGS_AD_ACAD_HISTORY_FLEX'
1230 )
1231 THEN
1232 p_error_code := 'E418';
1233 p_status := '3';
1234 RETURN;
1235 END IF;
1236
1237 --19. Transcript REquired
1238 IF PERSON_HISTORY_REC.TRANSCRIPT_REQUIRED IS NOT NULL THEN
1239 IF NOT PERSON_HISTORY_REC.TRANSCRIPT_REQUIRED IN ('Y', 'N') THEN
1240 p_error_code := 'E419';
1241 p_status := '3';
1242 RETURN;
1243 END IF;
1244 END IF;
1245
1246 p_error_code := NULL;
1247 p_status := '1';
1248
1249 RETURN ;
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 p_error_code := 'E518';
1253 p_status := '3';
1254 -- log detail
1255 RETURN;
1256 END Validate_ACADHIS;
1257 --end of local validation procedure
1258 ---------------------------------------------------------------------------
1259 -- local procedure to insert the academic history record
1260
1261 PROCEDURE crc_pe_acad_hist(
1262 PERSON_HISTORY_REC IN acad_hist%ROWTYPE
1263 ) AS
1264 l_msg_at_index NUMBER := 0;
1265 l_return_status VARCHAR2(1);
1266 l_msg_count NUMBER ;
1267 l_msg_data VARCHAR2(2000);
1268 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1269 l_error_text VARCHAR2(2000);
1270 l_education_id NUMBER;
1271 l_status VARCHAR2(10);
1272 l_object_version_number hz_education.object_version_number%TYPE := NULL;
1273 BEGIN
1274 l_status := '1';
1275 l_error_code := NULL;
1276 l_error_text := NULL;
1277 validate_acadhis(person_history_rec,l_error_code, l_status );
1278 IF l_Status = '1' THEN
1279 -- Bug no 2452444
1280 -- If any exception occurs during insert
1281 -- catch the error and display it in the log file and update the error code to E322 and status to 3
1282 BEGIN
1283 l_msg_at_index := igs_ge_msg_stack.count_msg;
1284 SAVEPOINT before_create_hist;
1285 Igs_Ad_Acad_History_Pkg.Insert_Row (
1286 x_rowid => l_RowId,
1287 x_attribute14 => PERSON_HISTORY_REC.attribute14,
1288 x_attribute15 => PERSON_HISTORY_REC.attribute15,
1289 x_attribute16 => PERSON_HISTORY_REC.attribute16,
1290 x_attribute17 => PERSON_HISTORY_REC.attribute17,
1291 x_attribute18 => PERSON_HISTORY_REC.attribute18,
1292 x_attribute19 => PERSON_HISTORY_REC.attribute19,
1293 x_attribute20 => PERSON_HISTORY_REC.attribute20,
1294 x_attribute13 => PERSON_HISTORY_REC.attribute13,
1295 x_attribute11 => PERSON_HISTORY_REC.attribute11,
1296 x_attribute12 => PERSON_HISTORY_REC.attribute12,
1297 x_education_id => l_education_id,
1298 x_person_id => PERSON_HISTORY_REC.Person_Id,
1299 x_current_inst => PERSON_HISTORY_REC.current_inst,
1300 x_degree_attempted => PERSON_HISTORY_REC.degree_attempted,
1301 x_program_code => PERSON_HISTORY_REC.Program_Code,
1302 x_degree_earned => PERSON_HISTORY_REC.degree_earned,
1303 x_comments => PERSON_HISTORY_REC.Comments,
1304 x_start_date => TRUNC(PERSON_HISTORY_REC.Start_Date),
1305 x_end_date => TRUNC(PERSON_HISTORY_REC.End_Date),
1306 x_planned_completion_date => TRUNC(person_history_rec.planned_completion_date),
1307 x_recalc_total_cp_attempted => NULL,
1308 x_recalc_total_cp_earned => NULL,
1309 x_recalc_total_unit_gp => NULL,
1310 x_recalc_tot_gpa_units_attemp => NULL,--recalc_tot_gpa_units_attemp,
1311 x_recalc_inst_gpa => NULL, --recalc_inst_gpa,
1312 x_recalc_grading_scale_id => NULL,
1313 x_selfrep_total_cp_attempted => PERSON_HISTORY_REC.selfrep_total_cp_attempted,
1314 x_selfrep_total_cp_earned => PERSON_HISTORY_REC.selfrep_total_cp_earned,
1315 x_selfrep_total_unit_gp => NULL, --selfrep_total_unit_gp,
1316 x_selfrep_tot_gpa_uts_attemp => NULL,
1317 x_selfrep_inst_gpa => PERSON_HISTORY_REC.selfrep_inst_gpa,
1318 x_selfrep_grading_scale_id => PERSON_HISTORY_REC.selfrep_grading_scale_id,
1319 x_selfrep_weighted_gpa => PERSON_HISTORY_REC.selfrep_weighted_gpa,
1320 x_selfrep_rank_in_class => PERSON_HISTORY_REC.selfrep_rank_in_class,
1321 x_selfrep_weighed_rank => PERSON_HISTORY_REC.selfrep_weighted_rank,
1322 x_type_of_school => PERSON_HISTORY_REC.type_of_school,
1323 x_institution_code => PERSON_HISTORY_REC.institution_code,
1324 x_attribute_category => PERSON_HISTORY_REC.attribute_category,
1325 x_attribute1 => PERSON_HISTORY_REC.attribute1,
1326 x_attribute2 => PERSON_HISTORY_REC.attribute2,
1327 x_attribute3 => PERSON_HISTORY_REC.attribute3,
1328 x_attribute4 => PERSON_HISTORY_REC.attribute4,
1329 x_attribute5 => PERSON_HISTORY_REC.attribute5,
1330 x_attribute6 => PERSON_HISTORY_REC.attribute6,
1331 x_attribute7 => PERSON_HISTORY_REC.attribute7,
1332 x_attribute8 => PERSON_HISTORY_REC.attribute8,
1333 x_attribute9 => PERSON_HISTORY_REC.attribute9,
1334 x_attribute10 => PERSON_HISTORY_REC.attribute10,
1335 -- Added Class Size As part of the ID Prospective Applicant part 2 of 1
1336 x_selfrep_class_size => PERSON_HISTORY_REC.class_size,
1337 -- Added Transcript Required as a part of DLD_ADSR_IMPORT_TEST_RESULTS
1338 x_transcript_required => NVL(PERSON_HISTORY_REC.transcript_required,'Y'),
1339 x_object_version_number => l_object_version_number,
1340 x_msg_data => l_msg_data,
1341 x_return_status => l_return_status,
1342 x_mode => 'R');
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 ROLLBACK TO before_create_hist;
1346 igs_ad_gen_016.extract_msg_from_stack (
1347 p_msg_at_index => l_msg_at_index,
1348 p_return_status => l_return_status,
1349 p_msg_count => l_msg_count,
1350 p_msg_data => l_msg_data,
1351 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1352 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1353 l_error_text := l_msg_data;
1354 l_error_Code := NULL;
1355
1356 IF p_enable_log = 'Y' THEN
1357 igs_ad_imp_001.logerrormessage(person_history_rec.interface_acadhis_id,l_msg_data,'IGS_AD_ACAD_HIS_INT');
1358 END IF;
1359 ELSE
1360 l_error_text := NULL;
1361 l_error_Code := 'E518';
1362 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1363
1364 l_label := 'igs.plsql.igs_ad_imp_028.crc_pe_acad_hist.exception '||l_msg_data;
1365
1366 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1367 fnd_message.set_token('INTERFACE_ID',person_history_rec.interface_acadhis_id);
1368 fnd_message.set_token('ERROR_CD','E322');
1369
1370 l_debug_str := fnd_message.get;
1371
1372 fnd_log.string_with_context( fnd_log.level_exception,
1373 l_label,
1374 l_debug_str, NULL,
1375 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1376 END IF;
1377
1378 END IF;
1379
1380
1381 UPDATE
1382 IGS_AD_ACADHIS_INT_ALL
1383 SET
1384 error_code = l_error_Code,
1385 error_text = l_error_text,
1386 status = cst_s_val_3,
1387 match_ind = DECODE (
1388 person_history_rec.match_ind,
1389 NULL, cst_mi_val_11,
1390 match_ind)
1391 WHERE
1392 INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1393 RETURN;
1394 END;
1395
1396 IF l_return_status IN ('E','U') THEN
1397 UPDATE IGS_AD_ACADHIS_INT_ALL
1398 SET error_code = 'E322',
1399 error_text = l_msg_data,
1400 status = '3',
1401 match_ind = DECODE (
1402 person_history_rec.match_ind,
1403 NULL, cst_mi_val_11,
1404 match_ind)
1405 WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1406
1407 --log detail
1408 ELSE
1409 -- BUG 2385289 BY RRENGARA ON 24-MAY-2002
1410 -- updated education_id after successful insert
1411 UPDATE IGS_AD_ACADHIS_INT_ALL
1412 SET status = cst_s_val_1,
1413 error_code = cst_ec_val_NULL,
1414 education_id = l_education_id
1415 WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1416 END IF;
1417 ELSE -- validation fails
1418 UPDATE IGS_AD_ACADHIS_INT_ALL
1419 SET error_code = l_error_code,
1420 error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405),
1421 status = l_status,
1422 match_ind = DECODE (
1423 person_history_rec.match_ind,
1424 NULL, cst_mi_val_11,
1425 match_ind)
1426 WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1427 IF p_enable_log = 'Y' THEN
1428 igs_ad_imp_001.logerrormessage(person_history_rec.interface_acadhis_id,l_error_code,'IGS_AD_ACAD_HIS_INT');
1429 END IF;
1430 END IF; -- end of ( l_error_code IS NULL )
1431 END crc_pe_acad_hist;
1432
1433
1434 PROCEDURE upd_pe_acad_hist (
1435 PERSON_HISTORY_REC IN acad_hist%ROWTYPE,
1436 c_null_hdlg_acad_hist_cur_rec c_dup_cur%ROWTYPE) AS
1437
1438 l_msg_at_index NUMBER := 0;
1439 l_return_status VARCHAR2(1);
1440 l_msg_count NUMBER ;
1441 l_msg_data VARCHAR2(2000);
1442 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1443 l_error_text VARCHAR2(2000);
1444 l_education_id NUMBER;
1445 l_status VARCHAR2(10);
1446 l_object_version_number hz_education.object_version_number%TYPE;
1447 NO_MATCH_RECORD_FOUND EXCEPTION;
1448 BEGIN
1449 l_object_version_number := c_null_hdlg_acad_hist_cur_rec.object_version_number;
1450
1451 validate_acadhis(person_history_rec,l_error_code, l_status );
1452 IF l_error_code IS NULL THEN
1453 BEGIN
1454 SAVEPOINT before_update_hist;
1455 l_msg_at_index := igs_ge_msg_stack.count_msg;
1456 Igs_Ad_Acad_History_Pkg.update_row (
1457 x_rowid => c_null_hdlg_acad_hist_cur_rec.row_id,
1458 x_attribute14 => c_null_hdlg_acad_hist_cur_rec.attribute14,
1459 x_attribute15 => c_null_hdlg_acad_hist_cur_rec.attribute15,
1460 x_attribute16 => c_null_hdlg_acad_hist_cur_rec.attribute16,
1461 x_attribute17 => c_null_hdlg_acad_hist_cur_rec.attribute17,
1462 x_attribute18 => c_null_hdlg_acad_hist_cur_rec.attribute18,
1463 x_attribute19 => c_null_hdlg_acad_hist_cur_rec.attribute19,
1464 x_attribute20 => c_null_hdlg_acad_hist_cur_rec.attribute20,
1465 x_attribute13 => c_null_hdlg_acad_hist_cur_rec.attribute13,
1466 x_attribute11 => c_null_hdlg_acad_hist_cur_rec.attribute11,
1467 x_attribute12 => c_null_hdlg_acad_hist_cur_rec.attribute12,
1468 x_education_id => c_null_hdlg_acad_hist_cur_rec.Education_Id,
1469 x_person_id => NVL(PERSON_HISTORY_REC.Person_Id,c_null_hdlg_acad_hist_cur_rec.person_id),
1470 x_current_inst => NVL(PERSON_HISTORY_REC.current_inst,c_null_hdlg_acad_hist_cur_rec.current_inst),
1471 x_degree_attempted => NVL(PERSON_HISTORY_REC.degree_attempted,c_null_hdlg_acad_hist_cur_rec.degree_attempted),
1472 x_program_code => NVL(PERSON_HISTORY_REC.Program_Code,c_null_hdlg_acad_hist_cur_rec.Program_Code),
1473 x_degree_earned => NVL(PERSON_HISTORY_REC.degree_earned,c_null_hdlg_acad_hist_cur_rec.degree_earned),
1474 x_comments => NVL(PERSON_HISTORY_REC.Comments,c_null_hdlg_acad_hist_cur_rec.Comments),
1475 x_start_date => TRUNC(NVL(PERSON_HISTORY_REC.Start_Date,c_null_hdlg_acad_hist_cur_rec.Start_Date)),
1476 x_end_date => TRUNC(NVL(PERSON_HISTORY_REC.End_Date,c_null_hdlg_acad_hist_cur_rec.End_Date)),
1477 x_planned_completion_date => NVL(person_history_rec.planned_completion_date,c_null_hdlg_acad_hist_cur_rec.planned_completion_date),
1478 x_recalc_total_cp_attempted => c_null_hdlg_acad_hist_cur_rec.recalc_total_cp_attempted,
1479 x_recalc_total_cp_earned => c_null_hdlg_acad_hist_cur_rec.recalc_total_cp_earned,
1480 x_recalc_total_unit_gp => c_null_hdlg_acad_hist_cur_rec.recalc_total_unit_gp,
1481 x_recalc_tot_gpa_units_attemp => c_null_hdlg_acad_hist_cur_rec.recalc_total_gpa_units_attemp,
1482 x_recalc_inst_gpa => c_null_hdlg_acad_hist_cur_rec.recalc_inst_gpa,
1483 x_recalc_grading_scale_id => c_null_hdlg_acad_hist_cur_rec.recalc_grading_scale_id,
1484 x_selfrep_total_cp_attempted => NVL(PERSON_HISTORY_REC.selfrep_total_cp_attempted,c_null_hdlg_acad_hist_cur_rec.selfrep_total_cp_attempted),
1485 x_selfrep_total_cp_earned => NVL(PERSON_HISTORY_REC.selfrep_total_cp_earned,c_null_hdlg_acad_hist_cur_rec.selfrep_total_cp_earned),
1486 x_selfrep_total_unit_gp => c_null_hdlg_acad_hist_cur_rec.selfrep_total_unit_gp,
1487 x_selfrep_tot_gpa_uts_attemp => NVL(person_history_rec.selfrep_total_gp_units_attemp,c_null_hdlg_acad_hist_cur_rec.selfrep_total_gpa_units_attemp),
1488 x_selfrep_inst_gpa => NVL(PERSON_HISTORY_REC.selfrep_inst_gpa,c_null_hdlg_acad_hist_cur_rec.selfrep_inst_gpa),
1489 x_selfrep_grading_scale_id => NVL(PERSON_HISTORY_REC.selfrep_grading_scale_id,c_null_hdlg_acad_hist_cur_rec.selfrep_grading_scale_id),
1490 x_selfrep_weighted_gpa => NVL(PERSON_HISTORY_REC.selfrep_weighted_gpa,c_null_hdlg_acad_hist_cur_rec.selfrep_weighted_gpa),
1491 x_selfrep_rank_in_class => NVL(PERSON_HISTORY_REC.selfrep_rank_in_class,c_null_hdlg_acad_hist_cur_rec.selfrep_rank_in_class),
1492 x_selfrep_weighed_rank => NVL(PERSON_HISTORY_REC.selfrep_weighted_rank,c_null_hdlg_acad_hist_cur_rec.selfrep_weighed_rank),
1493 x_type_of_school => NVL(PERSON_HISTORY_REC.type_of_school,c_null_hdlg_acad_hist_cur_rec.type_of_school),
1494 x_institution_code => NVL(PERSON_HISTORY_REC.institution_code,c_null_hdlg_acad_hist_cur_rec.institution_code),
1495 x_attribute_category => c_null_hdlg_acad_hist_cur_rec.attribute_category,
1496 x_attribute1 => c_null_hdlg_acad_hist_cur_rec.attribute1,
1497 x_attribute2 => c_null_hdlg_acad_hist_cur_rec.attribute2,
1498 x_attribute3 => c_null_hdlg_acad_hist_cur_rec.attribute3,
1499 x_attribute4 => c_null_hdlg_acad_hist_cur_rec.attribute4,
1500 x_attribute5 => c_null_hdlg_acad_hist_cur_rec.attribute5,
1501 x_attribute6 => c_null_hdlg_acad_hist_cur_rec.attribute6,
1502 x_attribute7 => c_null_hdlg_acad_hist_cur_rec.attribute7,
1503 x_attribute8 => c_null_hdlg_acad_hist_cur_rec.attribute8,
1504 x_attribute9 => c_null_hdlg_acad_hist_cur_rec.attribute9,
1505 x_attribute10 => c_null_hdlg_acad_hist_cur_rec.attribute10,
1506 -- Added Class Size As part of the ID Prospective Applicant part 2 of 1
1507 x_selfrep_class_size => NVL(PERSON_HISTORY_REC.class_size,c_null_hdlg_acad_hist_cur_rec.SELFREP_CLASS_SIZE),
1508 -- Added Transcript Required as a part of the DLD_ADRS_IMPORT_TEST_RESULTS DLD
1509 x_transcript_required => NVL(PERSON_HISTORY_REC.transcript_required,c_null_hdlg_acad_hist_cur_rec.transcript_required),
1510 x_msg_data => l_msg_data,
1511 x_return_status => l_return_status,
1512 x_object_version_number => l_object_version_number,
1513 x_mode => 'R');
1514 EXCEPTION
1515 WHEN OTHERS THEN
1516 ROLLBACK TO before_update_hist;
1517 igs_ad_gen_016.extract_msg_from_stack (
1518 p_msg_at_index => l_msg_at_index,
1519 p_return_status => l_return_status,
1520 p_msg_count => l_msg_count,
1521 p_msg_data => l_msg_data,
1522 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1523 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1524 l_error_text := l_msg_data;
1525 l_error_Code := 'E014';
1526
1527 IF p_enable_log = 'Y' THEN
1528 igs_ad_imp_001.logerrormessage(person_history_rec.interface_acadhis_id,l_msg_data,'IGS_AD_ACAD_HIS_INT');
1529 END IF;
1530 ELSE
1531 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
1532 l_error_Code := 'E518';
1533 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1534
1535 l_label := 'igs.plsql.igs_ad_imp_028.crc_pe_acad_hist.exception '||l_msg_data;
1536
1537 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1538 fnd_message.set_token('INTERFACE_ID',person_history_rec.interface_acadhis_id);
1539 fnd_message.set_token('ERROR_CD','E322');
1540
1541 l_debug_str := fnd_message.get;
1542
1543 fnd_log.string_with_context( fnd_log.level_exception,
1544 l_label,
1545 l_debug_str, NULL,
1546 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1547 END IF;
1548
1549 END IF;
1550
1551
1552
1553 UPDATE
1554 IGS_AD_ACADHIS_INT_ALL
1555 SET
1556 error_code = l_error_Code,
1557 error_text =l_error_text,
1558 status = '3',
1559 match_ind = DECODE (
1560 person_history_rec.match_ind,
1561 NULL, cst_mi_val_12,
1562 match_ind)
1563 WHERE
1564 INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1565 RETURN;
1566 END;
1567
1568
1569 IF l_return_status IN ('E','U') THEN
1570 UPDATE IGS_AD_ACADHIS_INT_ALL
1571 SET error_code = 'E014',
1572 status = '3',
1573 error_text = l_msg_data,
1574 match_ind = DECODE (
1575 person_history_rec.match_ind,
1576 NULL, cst_mi_val_12,
1577 match_ind)
1578 WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1579 --log detail
1580 ELSE
1581 -- BUG 2385289 BY RRENGARA ON 24-MAY-2002
1582 -- updated education_id after successful update
1583 UPDATE IGS_AD_ACADHIS_INT_ALL
1584 SET match_ind = decode ( person_history_rec.dmlmode,
1585 cst_partial_update, cst_mi_val_12,
1586 decode ( person_history_rec.match_ind ,
1587 NULL, cst_mi_val_18,
1588 person_history_rec.match_ind)),
1589 status = cst_s_val_1,
1590 education_id = c_null_hdlg_acad_hist_cur_rec.Education_Id
1591 WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1592 END IF;
1593 ELSE
1594 UPDATE IGS_AD_ACADHIS_INT_ALL
1595 SET status = cst_s_val_3,
1596 error_code = l_error_code,
1597 error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405),
1598 match_ind = DECODE (
1599 person_history_rec.match_ind,
1600 NULL, cst_mi_val_12,
1601 match_ind),
1602 education_id = c_null_hdlg_acad_hist_cur_rec.Education_Id
1603 WHERE INTERFACE_ACADHIS_ID = PERSON_HISTORY_REC.INTERFACE_ACADHIS_ID;
1604 IF p_enable_log = 'Y' THEN
1605 igs_ad_imp_001.logerrormessage(person_history_rec.interface_acadhis_id,l_error_code,'IGS_AD_ACAD_HIS_INT');
1606 END IF;
1607 END IF; --validation fails
1608
1609 END upd_pe_acad_hist;
1610
1611
1612 -- end of local procedure
1613
1614 BEGIN
1615
1616 l_prog_label := 'igs.plsql.igs_ad_imp_013.prc_pe_acad_hist';
1617
1618 --If given invalid update education ID then error out.
1619 UPDATE IGS_AD_ACADHIS_INT_ALL acad
1620 SET
1621 status = '3', error_code = 'E711',
1622 error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E711', 8405)
1623 WHERE update_education_id IS NOT NULL
1624 AND NOT EXISTS ( SELECT 1 FROM HZ_EDUCATION
1625 WHERE party_id = acad.person_id
1626 AND education_id = NVL(acad.update_education_id ,education_id)
1627 ) ;
1628 COMMIT;
1629
1630 IF p_rule IN ('E', 'I') THEN
1631 UPDATE IGS_AD_ACADHIS_INT_ALL
1632 SET
1633 status = '3'
1634 , error_code = 'E700'
1635 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
1636 WHERE interface_run_id = p_interface_run_id
1637 AND status = '2'
1638 AND NVL (match_ind, '15') <> '15';
1639 END IF;
1640 COMMIT;
1641
1642 -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
1643 IF p_rule = 'R' THEN
1644 UPDATE IGS_AD_ACADHIS_INT_ALL
1645 SET
1646 status = '1', error_code = NULL
1647 WHERE interface_run_id = p_interface_run_id
1648 AND status = '2'
1649 AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
1650 END IF;
1651 COMMIT;
1652
1653 -- 3. Set STATUS to 3 for interface records with multiple matching duplicate system records for RULE = I
1654 IF p_rule = 'I' THEN
1655 UPDATE IGS_AD_ACADHIS_INT_ALL a
1656 SET
1657 status = '3'
1658 , match_ind = '13'
1659 WHERE interface_run_id = p_interface_run_id
1660 AND status = '2'
1661 AND UPDATE_EDUCATION_ID IS NULL
1662 AND 1 < ( SELECT COUNT (*)
1663 FROM hz_Education h1, hz_parties h2
1664 WHERE h1.party_id = a.person_id
1665 AND h2.party_number = a.institution_code
1666 AND h2.party_id = h1.school_party_id
1667 AND TRUNC(NVL(h1.start_date_attended,
1668 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1669 TRUNC(NVL(a.start_date,
1670 TO_DATE('01-01-0001','DD-MM-YYYY')))
1671 AND TRUNC(NVL(h1.last_date_attended,
1672 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1673 TRUNC(NVL(a.end_date,
1674 TO_DATE('01-01-0001','DD-MM-YYYY')))
1675 );
1676
1677 END IF;
1678 COMMIT;
1679 -- 4. Set STATUS to 3 for interface records with multiple matching duplicate system record for RULE = R
1680 -- and either MATCH IND IN (15, 21) OR IS NULL
1681 IF p_rule = 'R' THEN
1682 UPDATE IGS_AD_ACADHIS_INT_ALL a
1683 SET
1684 status = '3'
1685 , match_ind = '13'
1686 WHERE interface_run_id = p_interface_run_id
1687 AND status = '2'
1688 AND UPDATE_EDUCATION_ID IS NULL
1689 AND NVL(match_ind, '15') IN ('15', '21')
1690 AND 1 < ( SELECT COUNT (*)
1691 FROM hz_Education h1, hz_parties h2
1692 WHERE h1.party_id = a.person_id
1693 AND h2.party_number = a.institution_code
1694 AND h2.party_id = h1.school_party_id
1695 AND TRUNC(NVL(h1.start_date_attended,
1696 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1697 TRUNC(NVL(a.start_date,
1698 TO_DATE('01-01-0001','DD-MM-YYYY')))
1699 AND TRUNC(NVL(h1.last_date_attended,
1700 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1701 TRUNC(NVL(a.end_date,
1702 TO_DATE('01-01-0001','DD-MM-YYYY')))
1703 );
1704
1705 END IF;
1706 COMMIT;
1707 -- 5. Set STATUS to 1 and MATCH IND to 19 for interface records with RULE = E matching OSS record(s)
1708 IF p_rule = 'E' THEN
1709 UPDATE IGS_AD_ACADHIS_INT_ALL a
1710 SET
1711 status = '1'
1712 , match_ind = '19'
1713 , education_id = update_education_id
1714 WHERE update_education_id IS NOT NULL;
1715 COMMIT;
1716
1717 UPDATE IGS_AD_ACADHIS_INT_ALL a
1718 SET
1719 status = '3'
1720 , match_ind = '19'
1721 ,error_code = 'E708'
1722 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E708', 8405)
1723 WHERE interface_run_id = p_interface_run_id
1724 AND status = '2'
1725 AND 1 < (SELECT count(*) FROM hz_Education h1, hz_parties h2
1726 WHERE h1.party_id = a.person_id
1727 AND h2.party_number = a.institution_code
1728 AND h2.party_id = h1.school_party_id
1729 AND TRUNC(NVL(h1.start_date_attended,
1730 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1731 TRUNC(NVL(a.start_date,
1732 TO_DATE('01-01-0001','DD-MM-YYYY')))
1733 AND TRUNC(NVL(h1.last_date_attended,
1734 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1735 TRUNC(NVL(a.end_date,
1736 TO_DATE('01-01-0001','DD-MM-YYYY')))
1737 )
1738 AND EXISTS (SELECT 1 FROM igs_ad_txcpt_int
1739 WHERE interface_acadhis_id = a.interface_acadhis_id
1740 AND status = '2');
1741 COMMIT;
1742
1743 UPDATE IGS_AD_ACADHIS_INT_ALL a
1744 SET
1745 status = '1'
1746 , match_ind = '19'
1747 , education_id =
1748 ( SELECT h1.education_id FROM hz_Education h1, hz_parties h2
1749 WHERE h1.party_id = a.person_id
1750 AND h2.party_number = a.institution_code
1751 AND h2.party_id = h1.school_party_id
1752 AND TRUNC(NVL(h1.start_date_attended,
1753 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1754 TRUNC(NVL(a.start_date,
1755 TO_DATE('01-01-0001','DD-MM-YYYY')))
1756 AND TRUNC(NVL(h1.last_date_attended,
1757 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1758 TRUNC(NVL(a.end_date,
1759 TO_DATE('01-01-0001','DD-MM-YYYY')))
1760 AND rownum <= 1 )
1761 WHERE interface_run_id = p_interface_run_id
1762 AND status = '2'
1763 AND EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
1764 WHERE h1.party_id = a.person_id
1765 AND h2.party_number = a.institution_code
1766 AND h2.party_id = h1.school_party_id
1767 AND TRUNC(NVL(h1.start_date_attended,
1768 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1769 TRUNC(NVL(a.start_date,
1770 TO_DATE('01-01-0001','DD-MM-YYYY')))
1771 AND TRUNC(NVL(h1.last_date_attended,
1772 TO_DATE('01-01-0001','DD-MM-YYYY'))) =
1773 TRUNC(NVL(a.end_date,
1774 TO_DATE('01-01-0001','DD-MM-YYYY')))
1775 );
1776
1777 -- Partial match finds single record, hence success if discrepancy rule is 'E' - per bug 3417941
1778 UPDATE IGS_AD_ACADHIS_INT_ALL a
1779 SET
1780 status = '1'
1781 , match_ind = '19'
1782 , education_id =
1783 ( SELECT h1.education_id FROM hz_Education h1, hz_parties h2
1784 WHERE h1.party_id = a.person_id
1785 AND h2.party_number = a.institution_code
1786 AND h2.party_id = h1.school_party_id
1787 AND NVL(h1.start_date_attended,h1.last_date_attended) IS NOT NULL
1788 AND (TRUNC(NVL(h1.start_date_attended,
1789 TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
1790 TRUNC(NVL(a.start_date,
1791 TO_DATE('01-01-0001','DD-MM-YYYY')))
1792 OR TRUNC(NVL(h1.last_date_attended,
1793 TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
1794 TRUNC(NVL(a.end_date,
1795 TO_DATE('01-01-0001','DD-MM-YYYY'))))
1796 )
1797 WHERE interface_run_id = p_interface_run_id
1798 AND status = '2'
1799 AND 1 = (SELECT count(*) FROM hz_Education h1, hz_parties h2
1800 WHERE h1.party_id = a.person_id
1801 AND h2.party_number = a.institution_code
1802 AND h2.party_id = h1.school_party_id
1803 AND NVL(h1.start_date_attended,h1.last_date_attended) IS NOT NULL
1804 AND (TRUNC(NVL(h1.start_date_attended,
1805 TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
1806 TRUNC(NVL(a.start_date,
1807 TO_DATE('01-01-0001','DD-MM-YYYY')))
1808 OR TRUNC(NVL(h1.last_date_attended,
1809 TO_DATE('01-01-0001','DD-MM-YYYY'))) <>
1810 TRUNC(NVL(a.end_date,
1811 TO_DATE('01-01-0001','DD-MM-YYYY'))))
1812 );
1813 END IF;
1814 COMMIT;
1815
1816
1817 /**********************************************************************************
1818 6. Create / Update the OSS record after validating successfully the interface record
1819 Create
1820 If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
1821 RULE = R and MATCH IND = 16, 25
1822 Update
1823 If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
1824 RULE = R and MATCH IND = 21
1825 UPdate
1826 If all the partilly matched OSS records have both start date and end date NULL THEN
1827 update First OSS record which partilaly matched.
1828
1829 Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
1830 This is done to have one code section for record validation, exception handling and interface table update.
1831 This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
1832
1833 **********************************************************************************/
1834
1835 SELECT COUNT(interface_acadhis_id)
1836 INTO l_count_interface_acadhis_id
1837 FROM IGS_AD_ACADHIS_INT_ALL
1838 WHERE interface_run_id = p_interface_run_id
1839 AND status =2 ;
1840
1841 l_total_records_prcessed := 0;
1842
1843 LOOP
1844 EXIT WHEN l_total_records_prcessed >= l_count_interface_acadhis_id;
1845
1846 SELECT
1847 MIN(interface_acadhis_id) , MAX(interface_acadhis_id)
1848 INTO l_min_interface_acadhis_id , l_max_interface_acadhis_id
1849 FROM IGS_AD_ACADHIS_INT_ALL
1850 WHERE interface_run_id = p_interface_run_id
1851 AND status =2
1852 AND rownum < =100;
1853
1854
1855 FOR acad_hist_rec IN acad_hist (l_min_interface_acadhis_id, l_max_interface_acadhis_id)
1856 LOOP
1857
1858 IF acad_hist_rec.dmlmode = cst_insert THEN
1859 crc_pe_acad_hist(acad_hist_rec);
1860 ELSIF acad_hist_rec.dmlmode = cst_update THEN
1861 dup_cur_rec.education_id := NULL;
1862 OPEN c_dup_cur(acad_hist_rec);
1863 FETCH c_dup_cur INTO dup_cur_rec;
1864 CLOSE c_dup_cur;
1865 upd_pe_acad_hist(acad_hist_rec, dup_cur_rec);
1866 ELSIF acad_hist_rec.dmlmode = cst_first_row THEN
1867 OPEN c_dup_cur_first(acad_hist_rec);
1868 FETCH c_dup_cur_first INTO dup_cur_rec;
1869 CLOSE c_dup_cur_first;
1870 upd_pe_acad_hist(acad_hist_rec, dup_cur_rec);
1871 ELSIF acad_hist_rec.dmlmode = cst_partial_update THEN
1872 OPEN c_dup_cur_partial(acad_hist_rec);
1873 FETCH c_dup_cur_partial INTO dup_cur_rec;
1874 CLOSE c_dup_cur_partial;
1875 upd_pe_acad_hist(acad_hist_rec, dup_cur_rec);
1876 END IF;
1877 l_total_records_prcessed := l_total_records_prcessed + 1;
1878
1879 END LOOP; -- End for loop
1880 COMMIT;
1881
1882 END LOOP; -- End While loop
1883
1884
1885
1886 -- 7. Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching OSS record(s)
1887 -- in ALL updateable column values
1888
1889 IF p_rule = 'R' THEN
1890 UPDATE IGS_AD_ACADHIS_INT_ALL acad
1891 SET
1892 status = '1'
1893 , match_ind = '23'
1894 WHERE interface_run_id = p_interface_run_id
1895 AND status = '2'
1896 AND NVL (match_ind, '15') = '15'
1897 AND EXISTS (SELECT 1 FROM igs_ad_acad_history_v WHERE
1898 person_id = acad.person_id
1899 AND NVL(current_inst, 'X') = NVL(NVL(acad.current_inst, current_inst), 'X')
1900 AND STATUS = acad.status
1901 AND NVL(degree_attempted, 'X') = NVL(NVL(acad.degree_attempted , degree_attempted ), 'X')
1902 AND NVL(program_code, 'X') = NVL(NVL(acad.program_code, program_code), 'X')
1903 AND NVL(degree_earned, 'X') = NVL(NVL(acad.degree_earned, degree_earned ), 'X')
1904 AND NVL(comments, 'X') = NVL(NVL(acad.comments,comments), 'X')
1905 AND NVL(to_char(start_date,'DDMMYYYY'), '01011900') = NVL(NVL(to_char(acad.start_date,'DDMMYYYY'), to_char(start_date,'DDMMYYYY')),'01011900')
1906 AND NVL(to_char(end_date,'DDMMYYYY'), '01011900') = NVL(NVL(to_char(acad.end_date,'DDMMYYYY'), to_char(end_date,'DDMMYYYY') ), '01011900')
1907 AND NVL(to_char(planned_completion_date,'DDMMYYYY'), '01011900') =
1908 NVL(NVL(to_char(acad.planned_completion_date, 'DDMMYYYY'), to_char(planned_completion_date,'DDMMYYYY') ), '01011900')
1909 AND NVL(selfrep_total_cp_attempted, -1) = NVL(NVL(acad.selfrep_total_cp_attempted, selfrep_total_cp_attempted), -1)
1910 AND NVL(selfrep_total_cp_earned, -1) = NVL(NVL(acad.selfrep_total_cp_earned, selfrep_total_cp_earned), -1)
1911 AND NVL(SELFREP_TOTAL_GPA_UNITS_ATTEMP, -1) = NVL(NVL(acad.SELFREP_TOTAL_GP_UNITS_ATTEMP, SELFREP_TOTAL_GP_UNITS_ATTEMP), -1)
1912 AND NVL(selfrep_inst_gpa, 'X') = NVL(NVL(acad.selfrep_inst_gpa, selfrep_inst_gpa), 'X')
1913 AND NVL(selfrep_grading_scale_id, -1) = NVL(NVL(acad.selfrep_grading_scale_id,selfrep_grading_scale_id), -1)
1914 AND NVL(selfrep_weighted_gpa, 'X') = NVL(NVL(acad.selfrep_weighted_gpa, selfrep_weighted_gpa), 'X')
1915 AND NVL(selfrep_rank_in_class, -1) = NVL(NVL(acad.selfrep_rank_in_class, selfrep_rank_in_class), -1)
1916 AND NVL(selfrep_weighed_rank, 'X') = NVL(NVL(acad.selfrep_weighted_rank, selfrep_weighted_rank), 'X')
1917 AND NVL(type_of_school, 'X') = NVL(NVL(acad.type_of_school, type_of_school), 'X')
1918 AND NVL(ATTRIBUTE_CATEGORY, 'X') = NVL( NVL(acad.ATTRIBUTE_CATEGORY,ATTRIBUTE_CATEGORY), 'X')
1919 AND NVL(ATTRIBUTE1, 'X') = NVL(NVL(acad.ATTRIBUTE1, ATTRIBUTE1), 'X')
1920 AND NVL(ATTRIBUTE2, 'X') = NVL(NVL(acad.ATTRIBUTE2, ATTRIBUTE2),'X')
1921 AND NVL(ATTRIBUTE3, 'X') = NVL(NVL(acad.ATTRIBUTE3,ATTRIBUTE3), 'X')
1922 AND NVL(ATTRIBUTE4, 'X') = NVL(NVL(acad.ATTRIBUTE4,ATTRIBUTE4), 'X')
1923 AND NVL(ATTRIBUTE5, 'X') = NVL(NVL(acad.ATTRIBUTE5,ATTRIBUTE5), 'X')
1924 AND NVL(ATTRIBUTE6, 'X') = NVL(NVL(acad.ATTRIBUTE6,ATTRIBUTE6), 'X')
1925 AND NVL(ATTRIBUTE7, 'X') = NVL(NVL(acad.ATTRIBUTE7, ATTRIBUTE7),'X')
1926 AND NVL(ATTRIBUTE8, 'X') = NVL(NVL(acad.ATTRIBUTE8, ATTRIBUTE8),'X')
1927 AND NVL(ATTRIBUTE9, 'X') = NVL(NVL(acad.ATTRIBUTE9, ATTRIBUTE9),'X')
1928 AND NVL(ATTRIBUTE10, 'X') = NVL(NVL(acad.ATTRIBUTE10, ATTRIBUTE10),'X')
1929 AND NVL(ATTRIBUTE11, 'X') = NVL(NVL(acad.ATTRIBUTE11, ATTRIBUTE11),'X')
1930 AND NVL(ATTRIBUTE12, 'X') = NVL(NVL(acad.ATTRIBUTE12,ATTRIBUTE12), 'X')
1931 AND NVL(ATTRIBUTE13, 'X') = NVL(NVL(acad.ATTRIBUTE13, ATTRIBUTE13),'X')
1932 AND NVL(ATTRIBUTE14, 'X') = NVL(NVL(acad.ATTRIBUTE14, ATTRIBUTE14),'X')
1933 AND NVL(ATTRIBUTE15, 'X') = NVL(NVL(acad.ATTRIBUTE15, ATTRIBUTE15),'X')
1934 AND NVL(ATTRIBUTE16, 'X') = NVL(NVL(acad.ATTRIBUTE16,ATTRIBUTE16), 'X')
1935 AND NVL(ATTRIBUTE17, 'X') = NVL(NVL(acad.ATTRIBUTE17,ATTRIBUTE17), 'X')
1936 AND NVL(ATTRIBUTE18, 'X') = NVL(NVL(acad.ATTRIBUTE18,ATTRIBUTE18), 'X')
1937 AND NVL(ATTRIBUTE19, 'X') = NVL(NVL(acad.ATTRIBUTE19,ATTRIBUTE19), 'X')
1938 AND NVL(ATTRIBUTE20, 'X') = NVL(NVL(acad.ATTRIBUTE20,ATTRIBUTE20), 'X')
1939 -- Added Class Size As part of the ID Prospective Applicant part 2 of 1
1940 AND NVL(selfrep_class_size,-1) = NVL(NVL(acad.class_size, class_size),-1)
1941 );
1942 END IF;
1943 COMMIT;
1944 -- Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and MATCH IND <> 21, 25, ones failed discrepancy check
1945
1946 IF p_rule = 'R' THEN
1947 UPDATE IGS_AD_ACADHIS_INT_ALL acad
1948 SET
1949 status = '3'
1950 , match_ind = '20'
1951 , dup_acad_history_id = ( SELECT hz_acad_hist_id FROM igs_Ad_Hz_Acad_Hist
1952 WHERE education_id = acad.update_education_id
1953 )
1954 WHERE interface_run_id = p_interface_run_id
1955 AND status = '2'
1956 AND NVL (match_ind, '15') = '15'
1957 AND update_Education_id IS NOT NULL;
1958
1959 COMMIT;
1960
1961 UPDATE IGS_AD_ACADHIS_INT_ALL acad
1962 SET
1963 status = '3'
1964 , match_ind = '20'
1965 , dup_acad_history_id = ( SELECT hz_acad_hist_id FROM igs_Ad_Hz_Acad_Hist
1966 WHERE education_id =
1967 (SELECT education_id FROM hz_Education h1, hz_parties h2
1968 WHERE h1.party_id = acad.person_id
1969 AND h2.party_number = acad.institution_code
1970 AND h2.party_id = h1.school_party_id
1971 AND NVL(h1.start_date_attended,
1972 TO_DATE('01-01-0001','DD-MM-YYYY')) =
1973 NVL(acad.start_date,
1974 TO_DATE('01-01-0001','DD-MM-YYYY'))
1975 AND NVL(h1.last_date_attended,
1976 TO_DATE('01-01-0001','DD-MM-YYYY')) =
1977 NVL(acad.end_date,
1978 TO_DATE('01-01-0001','DD-MM-YYYY'))
1979 )
1980 )
1981
1982
1983
1984 WHERE interface_run_id = p_interface_run_id
1985 AND status = '2'
1986 AND NVL (match_ind, '15') = '15'
1987 AND ( EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
1988 WHERE h1.party_id = acad.person_id
1989 AND h2.party_number = acad.institution_code
1990 AND h2.party_id = h1.school_party_id
1991 AND NVL(h1.start_date_attended,
1992 TO_DATE('01-01-0001','DD-MM-YYYY')) =
1993 NVL(acad.start_date,
1994 TO_DATE('01-01-0001','DD-MM-YYYY'))
1995 AND NVL(h1.last_date_attended,
1996 TO_DATE('01-01-0001','DD-MM-YYYY')) =
1997 NVL(acad.end_date,
1998 TO_DATE('01-01-0001','DD-MM-YYYY'))
1999 )
2000 );
2001 END IF;
2002 COMMIT;
2003 -- Multiple Partial (do not need to compare dates as date are already compared
2004 -- and only partial matching records are in status '2'
2005 UPDATE IGS_AD_ACADHIS_INT_ALL acad
2006 SET
2007 status = '3'
2008 , match_ind = '14'
2009 WHERE interface_run_id = p_interface_run_id
2010 AND status = '2'
2011 AND 1< ( SELECT COUNT(*) FROM hz_Education h1, hz_parties h2
2012 WHERE h1.party_id = acad.person_id
2013 AND h2.party_number = acad.institution_code
2014 AND h2.party_id = h1.school_party_id
2015 );
2016 COMMIT;
2017
2018 -- Partial match finds single record, hence '20,3' for discrepancy rule 'R' - per bug 3417941
2019 UPDATE IGS_AD_ACADHIS_INT_ALL acad
2020 SET
2021 status = '3'
2022 , match_ind = '20'
2023 WHERE interface_run_id = p_interface_run_id
2024 AND status = '2'
2025 AND EXISTS ( SELECT 1 FROM hz_Education h1, hz_parties h2
2026 WHERE h1.party_id = acad.person_id
2027 AND h2.party_number = acad.institution_code
2028 AND h2.party_id = h1.school_party_id
2029 );
2030 COMMIT;
2031
2032 --Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
2033 IF p_rule = 'R' THEN
2034 UPDATE IGS_AD_ACADHIS_INT_ALL acad
2035 SET
2036 status = '3'
2037 , error_code = 'E700'
2038 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
2039 WHERE interface_run_id = p_interface_run_id
2040 AND status = '2'
2041 AND match_ind IS NOT NULL;
2042 END IF;
2043 COMMIT;
2044
2045
2046 END PRC_PE_ACAD_HIST;
2047
2048
2049
2050 PROCEDURE prc_pe_cred_details (
2051 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
2052 p_rule VARCHAR2,
2053 p_enable_log VARCHAR2
2054 ) AS
2055
2056 /***********************************************
2057 || Created By :Praveen Bondugula
2058 || Date Created By :24-apr-2003
2059 || Purpose : Import person credentials
2060 || Known limitations, enhancements or remarks
2061 || Change History
2062 || Who When What
2063 ||
2064 **********************************************/
2065
2066
2067
2068 CURSOR c_pe_cr_cur IS
2069 SELECT cst_insert dmlmode, cred.rowid, ad.person_id, cred.*
2070 FROM IGS_AD_INTERFACE_ALL ad , IGS_PE_CRED_INT cred
2071 WHERE cred.interface_run_id = p_interface_run_id
2072 AND ad.status IN ('1', '4')
2073 AND cred.interface_id = ad.interface_id
2074 AND cred.status = '2'
2075 AND ( NOT EXISTS (SELECT 1 FROM IGS_PE_CREDENTIALS
2076 WHERE person_id = ad.person_id
2077 AND credential_type_id = cred.credential_type_id
2078 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2079 TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) )
2080 OR ( p_rule = 'R' AND cred.match_ind IN ('16', '25') )
2081 )
2082 UNION ALL
2083 SELECT cst_update dmlmode, cred.rowid, ad.person_id, cred.*
2084 FROM IGS_AD_INTERFACE_ALL ad , IGS_PE_CRED_INT cred
2085 WHERE cred.interface_run_id = p_interface_run_id
2086 AND ad.status IN ('1', '4')
2087 AND cred.interface_id = ad.interface_id
2088 AND cred.status = '2'
2089 AND ( p_rule = 'I' OR (p_rule = 'R' AND cred.match_ind = cst_mi_val_21))
2090 AND EXISTS (SELECT 1 FROM IGS_PE_CREDENTIALS
2091 WHERE person_id = ad.person_id
2092 AND credential_type_id = cred.credential_type_id
2093 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2094 TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
2095 );
2096
2097 CURSOR c_dup_cur(cp_pe_cr_rec c_pe_cr_cur%ROWTYPE) IS
2098 SELECT
2099 pcreds.rowid, pcreds.*
2100 FROM
2101 igs_pe_credentials pcreds
2102 WHERE
2103 person_id = cp_pe_cr_rec.person_id
2104 AND credential_type_id = cp_pe_cr_rec.credential_type_id
2105 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) = TRUNC(NVL(cp_pe_cr_rec.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')));
2106
2107
2108 /*************************************************
2109 END Cursor Declarations
2110 *************************************************/
2111
2112
2113 l_processed_records NUMBER(5) := 0;
2114 dup_cur_rec c_dup_cur%ROWTYPE;
2115 l_prog_label VARCHAR2(100) ;
2116 l_label VARCHAR2(150) ;
2117 l_debug_str VARCHAR2(150) ;
2118 l_error_text VARCHAR2(2000) := NULL;
2119
2120 l_msg_at_index NUMBER := 0;
2121 l_return_status VARCHAR2(1);
2122 l_msg_count NUMBER ;
2123 l_msg_data VARCHAR2(2000);
2124 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
2125
2126
2127 /*************************************************
2128 Local Procedures
2129 *************************************************/
2130 PROCEDURE validate_pe_cred(cp_pe_cr_rec c_pe_cr_cur%ROWTYPE,
2131 p_status OUT NOCOPY varchar2,
2132 p_error_code OUT NOCOPY varchar2) AS
2133 /***********************************************
2134 || Created By :Praveen Bondugula
2135 || Date Created By :24-apr-2003
2136 || Purpose : Validates the credentials columns
2137 || Known limitations, enhancements or remarks
2138 || Change History
2139 || Who When What
2140 ||
2141 **********************************************/
2142 CURSOR c_credential_type_id (cp_pe_cr_rec c_pe_cr_cur%ROWTYPE) IS
2143 SELECT
2144 'X'
2145 FROM
2146 igs_ad_cred_types
2147 WHERE
2148 credential_type_id = cp_pe_cr_rec.credential_type_id
2149 AND closed_ind = 'N';
2150
2151 CURSOR c_rating(cp_pe_cr_rec c_pe_cr_cur%ROWTYPE)IS
2152 SELECT
2153 'X'
2154 FROM
2155 igs_lookup_values
2156 WHERE lookup_type = 'PE_CRE_RATING' AND
2157 lookup_code = cp_pe_cr_rec.rating_code AND
2158 enabled_flag = 'Y';
2159
2160 CURSOR c_reviewer_id (cp_pe_cr_rec c_pe_cr_cur%ROWTYPE) IS
2161 SELECT
2162 'X'
2163 FROM
2164 hz_parties
2165 WHERE
2166 party_id = cp_pe_cr_rec.reviewer_id;
2167
2168 credential_type_id_rec c_credential_type_id%ROWTYPE;
2169 rating_rec c_rating%ROWTYPE;
2170 BEGIN
2171 /*************Validate credential_type_id************************/
2172 OPEN c_credential_type_id( cp_pe_cr_rec);
2173 FETCH c_credential_type_id INTO credential_type_id_rec;
2174 IF c_credential_type_id%NOTFOUND THEN
2175 p_status :='3';
2176 p_error_code := 'E635';
2177 CLOSE c_credential_type_id;
2178 RETURN;
2179 END IF;
2180 CLOSE c_credential_type_id;
2181
2182 /*************Validate DATE_RECEIVED************************/
2183 IF (cp_pe_cr_rec.date_received IS NOT NULL AND (cp_pe_cr_rec.date_received > SYSDATE)) THEN
2184 p_status :='3';
2185 p_error_code := 'E636';
2186 RETURN;
2187 END IF;
2188
2189 /*************Validate rating************************/
2190 IF cp_pe_cr_rec.rating_code IS NOT NULL THEN
2191 OPEN c_rating( cp_pe_cr_rec);
2192 FETCH c_rating INTO rating_rec;
2193 IF c_rating%NOTFOUND THEN
2194 p_status :='3';
2195 p_error_code := 'E637';
2196 CLOSE c_rating;
2197 RETURN;
2198 END IF;
2199 CLOSE c_rating;
2200 END IF;
2201
2202 /*************Validate REVIEWER_ID****************/
2203 IF cp_pe_cr_rec.reviewer_id IS NOT NULL THEN
2204 IF (IGS_EN_GEN_003.Get_Staff_Ind(cp_pe_cr_rec.reviewer_id)='N') THEN
2205 p_status :='3';
2206 p_error_code := 'E638';
2207 RETURN;
2208 END IF;
2209 END IF;
2210
2211
2212 p_status :='1';
2213 p_error_code := NULL;
2214
2215 END validate_pe_cred;
2216
2217
2218 PROCEDURE update_pe_cred(cp_pe_cr_rec c_pe_cr_cur%ROWTYPE, cp_dup_cur_rec c_dup_cur%ROWTYPE) AS
2219 /***********************************************
2220 || Created By :Praveen Bondugula
2221 || Date Created By :24-apr-2003
2222 || Purpose : update person credentials in the existing record
2223 || Known limitations, enhancements or remarks
2224 || Change History
2225 || Who When What
2226 ||
2227 **********************************************/
2228 l_status VARCHAR2(1);
2229 l_error_code VARCHAR2(30);
2230 BEGIN
2231 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2232 l_label := 'igs.plsql.igs_ad_imp_028.update_pe_cred.begin';
2233 l_debug_str := 'igs_ad_imp_028.update_pe_cred';
2234
2235 fnd_log.string_with_context( fnd_log.level_procedure,
2236 l_label,
2237 l_debug_str, NULL,
2238 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2239 END IF;
2240
2241 validate_pe_cred(
2242 cp_pe_cr_rec => cp_pe_cr_rec,
2243 p_status => l_status,
2244 p_error_code =>l_error_code
2245 );
2246 IF l_status ='1' THEN
2247 igs_pe_credentials_pkg.update_row(
2248 x_rowid => dup_cur_rec.rowid,
2249 x_credential_id => dup_cur_rec.credential_id,
2250 x_person_id => cp_dup_cur_rec.person_id,
2251 x_credential_type_id => cp_dup_cur_rec.credential_type_id,
2252 x_date_received => TRUNC (NVL( cp_pe_cr_rec.date_received, cp_dup_cur_rec.date_received)),
2253 x_reviewer_id => NVL(cp_pe_cr_rec.reviewer_id, cp_dup_cur_rec.reviewer_id),
2254 x_reviewer_notes => NVL(cp_pe_cr_rec.reviewer_notes, cp_dup_cur_rec.reviewer_notes),
2255 x_recommender_name => NVL( cp_pe_cr_rec.recommender_name, cp_dup_cur_rec.recommender_name),
2256 x_recommender_title => NVL( cp_pe_cr_rec.recommender_title, cp_dup_cur_rec.recommender_title),
2257 x_recommender_organization=> NVL( cp_pe_cr_rec.recommender_organization, cp_dup_cur_rec.recommender_organization),
2258 x_mode => 'R',
2259 x_rating_code => NVL( cp_pe_cr_rec.rating_code,cp_dup_cur_rec.rating_code)
2260 );
2261
2262 UPDATE igs_pe_cred_int
2263 SET status = cst_s_val_1, error_code = cst_ec_val_NULL, match_ind = cst_mi_val_18
2264 WHERE interface_cred_id = cp_pe_cr_rec.interface_cred_id;
2265 ELSE
2266
2267 UPDATE igs_pe_cred_int
2268 SET
2269 status = cst_s_val_3
2270 , match_ind = DECODE (
2271 cp_pe_cr_rec.match_ind,
2272 NULL, cst_mi_val_12,
2273 match_ind)
2274 , error_code = l_error_code
2275 WHERE rowid = cp_pe_cr_rec.rowid ;
2276 -- Here it is assumed that validate_pe_cred procedure can only return status values '1' or '3'
2277 IF p_enable_log = 'Y' THEN
2278 igs_ad_imp_001.logerrormessage(cp_pe_cr_rec.interface_cred_id,l_error_code,'IGS_PE_CRED_INT');
2279 END IF;
2280
2281 END IF;
2282
2283 EXCEPTION
2284 WHEN OTHERS THEN
2285 l_status := '3';
2286 l_error_code := 'E014';
2287
2288 l_msg_at_index := igs_ge_msg_stack.count_msg;
2289 igs_ad_gen_016.extract_msg_from_stack (
2290 p_msg_at_index => l_msg_at_index,
2291 p_return_status => l_return_status,
2292 p_msg_count => l_msg_count,
2293 p_msg_data => l_msg_data,
2294 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2295 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
2296 l_error_text := l_msg_data;
2297 IF p_enable_log = 'Y' THEN
2298 igs_ad_imp_001.logerrormessage(cp_pe_cr_rec.interface_cred_id,l_msg_data,'IGS_PE_CRED_INT');
2299 END IF;
2300 ELSE
2301 l_error_text := NULL;
2302 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2303
2304 l_label := 'igs.plsql.igs_ad_imp_028.update_pe_cred.exception '|| l_msg_data;
2305
2306 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2307 fnd_message.set_token('INTERFACE_ID',cp_pe_cr_rec.interface_cred_id);
2308 fnd_message.set_token('ERROR_CD','E014');
2309
2310 l_debug_str := fnd_message.get;
2311
2312 fnd_log.string_with_context( fnd_log.level_exception,
2313 l_label,
2314 l_debug_str, NULL,
2315 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2316 END IF;
2317
2318 END IF;
2319
2320 UPDATE igs_pe_cred_int
2321 SET
2322 status = cst_s_val_3
2323 , match_ind = DECODE (
2324 cp_pe_cr_rec.match_ind,
2325 NULL, cst_mi_val_12,
2326 match_ind)
2327 , error_code = l_error_code
2328 ,error_text = l_error_text
2329 WHERE rowid = cp_pe_cr_rec.rowid ;
2330
2331
2332
2333 END update_pe_cred;
2334
2335
2336 PROCEDURE insert_pe_cred(cp_pe_cr_rec c_pe_cr_cur%ROWTYPE) AS
2337 /***********************************************
2338 || Created By :Praveen Bondugula
2339 || Date Created By :24-apr-2003
2340 || Purpose : Inserts the credentials into the OSS table.
2341 || Known limitations, enhancements or remarks
2342 || Change History
2343 || Who When What
2344 ||
2345 **********************************************/
2346 l_status VARCHAR2(1);
2347 l_error_code VARCHAR2(30);
2348 l_rowid VARCHAR2(25);
2349 l_credential_id igs_pe_credentials.credential_id%TYPE;
2350
2351 BEGIN
2352 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2353 l_label := 'igs.plsql.igs_ad_imp_028.insert_pe_cred.begin';
2354 l_debug_str := 'igs_ad_imp_028.insert_pe_cred';
2355
2356 fnd_log.string_with_context( fnd_log.level_procedure,
2357 l_label,
2358 l_debug_str, NULL,
2359 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2360 END IF;
2361 validate_pe_cred(
2362 cp_pe_cr_rec => cp_pe_cr_rec,
2363 p_status => l_status,
2364 p_error_code =>l_error_code
2365 );
2366 IF l_status ='1' THEN
2367 l_rowid := NULL;
2368 l_credential_id := NULL;
2369 igs_pe_credentials_pkg.insert_row(
2370 x_rowid => l_rowid,
2371 x_credential_id => l_credential_id,
2372 x_person_id => cp_pe_cr_rec.person_id,
2373 x_credential_type_id => cp_pe_cr_rec.credential_type_id,
2374 x_date_received => TRUNC (cp_pe_cr_rec.date_received),
2375 x_reviewer_id => cp_pe_cr_rec.reviewer_id,
2376 x_reviewer_notes => cp_pe_cr_rec.reviewer_notes,
2377 x_recommender_name => cp_pe_cr_rec.recommender_name,
2378 x_recommender_title => cp_pe_cr_rec.recommender_title,
2379 x_recommender_organization=> cp_pe_cr_rec.recommender_organization,
2380 x_mode => 'R',
2381 x_rating_code => cp_pe_cr_rec.rating_code);
2382
2383 UPDATE igs_pe_cred_int
2384 SET status = cst_s_val_1,
2385 error_code = cst_ec_val_NULL,
2386 match_ind = cst_mi_val_11
2387 WHERE interface_cred_id = cp_pe_cr_rec.interface_cred_id;
2388 ELSE
2389 UPDATE igs_pe_cred_int
2390 SET
2391 status = cst_s_val_3
2392 , match_ind = DECODE (
2393 cp_pe_cr_rec.match_ind,
2394 NULL, cst_mi_val_11,
2395 match_ind)
2396 , error_code = l_error_code
2397 WHERE rowid = cp_pe_cr_rec.rowid ;
2398
2399 -- Here it is assumed that validate_pe_cred procedure can only return status values '1' or '3'
2400 IF p_enable_log = 'Y' THEN
2401 igs_ad_imp_001.logerrormessage(cp_pe_cr_rec.interface_cred_id,'E322','IGS_PE_CRED_INT');
2402 END IF;
2403
2404 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2405
2406 l_label := 'igs.plsql.igs_ad_imp_028.insert_pe_cred.exception'||l_msg_data;
2407
2408 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2409 fnd_message.set_token('INTERFACE_ID',cp_pe_cr_rec.interface_cred_id);
2410 fnd_message.set_token('ERROR_CD',l_error_code);
2411
2412 l_debug_str := fnd_message.get;
2413
2414 fnd_log.string_with_context( fnd_log.level_exception,
2415 l_label,
2416 l_debug_str, NULL,
2417 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2418 END IF;
2419 END IF;
2420
2421 EXCEPTION
2422 WHEN OTHERS THEN
2423 l_status := '3';
2424 l_error_code := 'E322';
2425 l_msg_at_index := igs_ge_msg_stack.count_msg;
2426 igs_ad_gen_016.extract_msg_from_stack (
2427 p_msg_at_index => l_msg_at_index,
2428 p_return_status => l_return_status,
2429 p_msg_count => l_msg_count,
2430 p_msg_data => l_msg_data,
2431 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2432 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
2433 l_error_text := l_msg_data;
2434 IF p_enable_log = 'Y' THEN
2435 igs_ad_imp_001.logerrormessage(cp_pe_cr_rec.interface_cred_id,l_msg_data,'IGS_PE_CRED_INT');
2436 END IF;
2437 ELSE
2438 l_error_text := NULL;
2439 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2440
2441 l_label := 'igs.plsql.igs_ad_imp_028.update_pe_cred.exception '||'E322';
2442
2443 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2444 fnd_message.set_token('INTERFACE_ID',cp_pe_cr_rec.interface_cred_id);
2445 fnd_message.set_token('ERROR_CD','E322');
2446
2447 l_debug_str := fnd_message.get;
2448
2449 fnd_log.string_with_context( fnd_log.level_exception,
2450 l_label,
2451 l_debug_str, NULL,
2452 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2453 END IF;
2454
2455 END IF;
2456
2457 UPDATE igs_pe_cred_int
2458 SET
2459 status = cst_s_val_3
2460 , match_ind = DECODE (
2461 cp_pe_cr_rec.match_ind,
2462 NULL, cst_mi_val_11,
2463 match_ind)
2464 , error_code = l_error_code
2465 ,error_text = l_error_text
2466 WHERE rowid = cp_pe_cr_rec.rowid ;
2467
2468
2469 END insert_pe_cred;
2470
2471
2472
2473 BEGIN
2474 l_prog_label := 'igs.plsql.igs_ad_imp_028.prc_pe_cred_details';
2475 l_label := 'igs.plsql.igs_ad_imp_008.prc_pe_cred_details.';
2476 l_debug_str := 'igs.plsql.igs_ad_imp_008.prc_pe_cred_details.';
2477
2478 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2479 l_label := 'igs.plsql.igs_ad_imp_028.prc_pe_cred_details.begin';
2480 l_debug_str := 'igs_ad_imp_028.prc_pe_cred_details';
2481
2482 fnd_log.string_with_context( fnd_log.level_procedure,
2483 l_label,
2484 l_debug_str, NULL,
2485 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2486 END IF;
2487 -- 1. Set STATUS to 3 for interface records with RULE = E or I and MATCH IND is not null and not '15'
2488 IF p_rule IN ('E', 'I') THEN
2489 UPDATE IGS_PE_CRED_INT
2490 SET
2491 status = '3'
2492 , error_code = 'E700'
2493 WHERE interface_run_id = p_interface_run_id
2494 AND status = '2'
2495 AND NVL (match_ind, '15') <> '15';
2496 END IF;
2497 COMMIT;
2498
2499 -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
2500 IF p_rule = 'R' THEN
2501 UPDATE igs_pe_cred_int
2502 SET
2503 status = '1', error_code = NULL
2504 WHERE interface_run_id = p_interface_run_id
2505 AND status = '2'
2506 AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
2507 END IF;
2508 COMMIT;
2509
2510 -- 3. Set STATUS to 3 for interface records with multiple matching duplicate system records for RULE = I
2511 IF p_rule = 'I' THEN
2512 UPDATE igs_pe_cred_int cred
2513 SET
2514 status = '3'
2515 , match_ind = '13'
2516 WHERE interface_run_id = p_interface_run_id
2517 AND status = '2'
2518 AND 1 < ( SELECT COUNT(*)
2519 FROM igs_pe_credentials cred_oss
2520 WHERE person_id = (SELECT person_id FROM igs_ad_interface_all
2521 WHERE interface_id = cred.interface_id)
2522 AND credential_type_id = cred.credential_type_id
2523 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2524 TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
2525 );
2526
2527 END IF;
2528 COMMIT;
2529 -- 4. Set STATUS to 3 for interface records with multiple matching duplicate system record for RULE = R
2530 -- and either MATCH IND IN (15, 21) OR IS NULL
2531 IF p_rule = 'R' THEN
2532 UPDATE igs_pe_cred_int cred
2533 SET
2534 status = '3'
2535 , match_ind = '13'
2536 WHERE interface_run_id = p_interface_run_id
2537 AND status = '2'
2538 AND NVL(match_ind, '15') IN ('15', '21')
2539 AND 1 < ( SELECT COUNT(*)
2540 FROM igs_pe_credentials cred_oss
2541 WHERE person_id = (SELECT person_id FROM igs_ad_interface_all
2542 WHERE interface_id = cred.interface_id)
2543 AND credential_type_id = cred.credential_type_id
2544 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2545 TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
2546 );
2547
2548 END IF;
2549 COMMIT;
2550 -- 3. Set STATUS to 1 and MATCH IND to 19 for interface records with RULE = E matching OSS record(s)
2551 IF p_rule = 'E' THEN
2552 UPDATE IGS_PE_CRED_INT cred
2553 SET
2554 status = '1'
2555 , match_ind = '19'
2556 WHERE interface_run_id = p_interface_run_id
2557 AND status = '2'
2558 AND EXISTS ( SELECT 1 FROM IGS_PE_CREDENTIALS
2559 WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2560 WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
2561 AND credential_type_id = cred.credential_type_id
2562 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2563 TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
2564 );
2565 END IF;
2566 COMMIT;
2567
2568 /**********************************************************************************
2569 Create / Update the OSS record after validating successfully the interface record
2570 Create
2571 If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
2572 RULE = R and MATCH IND = 16, 25
2573 Update
2574 If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
2575 RULE = R and MATCH IND = 21
2576
2577 Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
2578 This is done to have one code section for record validation, exception handling and interface table update.
2579 This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
2580
2581 **********************************************************************************/
2582 FOR pe_cr_cur_rec IN c_pe_cr_cur
2583 LOOP
2584 IF pe_cr_cur_rec.dmlmode = cst_insert THEN
2585 insert_pe_cred(pe_cr_cur_rec);
2586 ELSIF pe_cr_cur_rec.dmlmode = cst_update THEN
2587 OPEN c_dup_cur(pe_cr_cur_rec);
2588 FETCH c_dup_cur INTO dup_cur_rec;
2589 CLOSE c_dup_cur;
2590 update_pe_cred(pe_cr_cur_rec, dup_cur_rec);
2591 END IF;
2592 l_processed_records := l_processed_records + 1;
2593 IF l_processed_records = 100 THEN
2594 COMMIT;
2595 l_processed_records := 0;
2596 END IF;
2597
2598 END LOOP;
2599 IF l_processed_records < 100 AND l_processed_records > 0 THEN
2600 COMMIT;
2601 END IF;
2602
2603 /*Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching OSS record(s) in
2604 ALL updateable column values, if column nullification is not allowed then the 2 DECODE should be replaced by a single NVL*/
2605 IF p_rule = 'R' THEN
2606 UPDATE IGS_PE_CRED_INT cred
2607 SET
2608 status = '1'
2609 , match_ind = '23'
2610 WHERE interface_run_id = p_interface_run_id
2611 AND status = '2'
2612 AND NVL (match_ind, '15') = '15'
2613 AND EXISTS ( SELECT 'x'
2614 FROM
2615 igs_pe_credentials
2616 WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2617 WHERE interface_id = cred.interface_id
2618 AND interface_run_id = p_interface_run_id)
2619 AND credential_type_id = cred.credential_type_id
2620 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2621 TRUNC(NVL(cred.date_received, NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))))
2622 AND NVL(RATING_CODE, '-1') = NVL(cred.rating_code, NVL(RATING_CODE, '-1'))
2623 AND NVL(REVIEWER_ID, -1) = NVL(cred.reviewer_id, NVL(REVIEWER_ID, -1))
2624 AND NVL(REVIEWER_NOTES, '-1') = NVL(cred.reviewer_notes, NVL(REVIEWER_NOTES, '-1'))
2625 AND NVL(RECOMMENDER_NAME, '-1') = NVL(cred.recommender_name, NVL(RECOMMENDER_NAME, '-1'))
2626 AND NVL(RECOMMENDER_TITLE , '-1') = NVL(cred.recommender_title, NVL(RECOMMENDER_TITLE , '-1'))
2627 AND NVL(recommender_organization, '-1') = NVL(cred.recommender_organization,NVL(recommender_organization, '-1'))
2628 );
2629 END IF;
2630 COMMIT;
2631
2632 --Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and
2633 --MATCH IND <> 21, 25, ones failed above discrepancy check
2634 IF p_rule = 'R' THEN
2635 UPDATE IGS_PE_CRED_INT cred
2636 SET
2637 status = '3'
2638 , match_ind = '20'
2639 , dup_credential_id= (SELECT credential_id FROM igs_pe_credentials
2640 WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2641 WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
2642 AND credential_type_id = cred.credential_type_id
2643 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2644 TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))))
2645 WHERE interface_run_id = p_interface_run_id
2646 AND status = '2'
2647 AND NVL (match_ind, '15') = '15'
2648 AND EXISTS (SELECT credential_id FROM igs_pe_credentials
2649 WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2650 WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
2651 AND credential_type_id = cred.credential_type_id
2652 AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2653 TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))));
2654
2655 END IF;
2656 COMMIT;
2657
2658
2659
2660 --Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
2661 IF p_rule = 'R' THEN
2662 UPDATE IGS_PE_CRED_INT cred
2663 SET
2664 status = '3'
2665 , error_code = 'E700'
2666 WHERE interface_run_id = p_interface_run_id
2667 AND status = '2'
2668 AND match_ind IS NOT NULL;
2669 END IF;
2670 COMMIT;
2671
2672
2673 END prc_pe_cred_details;
2674
2675
2676 END IGS_AD_IMP_013;
[Home] [Help]