[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_027
Source
1 PACKAGE BODY IGS_AD_IMP_027 AS
2 /* $Header: IGSADC7B.pls 115.13 2003/12/09 11:57:43 akadam noship $ */
3 /*******************************************************************************
4 Created by : Ramesh Rengarajan
5 Date created: 21 APR 2003
6
7 Purpose:
8 To Import Legacy Data
9
10 Known limitations/enhancements and/or remarks:
11
12 Change History: (who, when, what: )
13 Who When What
14 pathipat 17-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
15 Modified igs_ad_app_req_pkg TBH calls in prc_appl_fees() - added new parameters
16 **********************************************************************************/
17 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
18 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
19 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
20 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
21
22 cst_et_val_E322 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
23 cst_et_val_E686 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E686', 8405);
24 cst_et_val_E689 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E689', 8405);
25 cst_et_val_E709 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E709', 8405);
26 cst_et_val_E710 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E710', 8405);
27
28 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
29 cst_ec_val_E686 CONSTANT VARCHAR2(4) := 'E686';
30 cst_ec_val_E689 CONSTANT VARCHAR2(4) := 'E689';
31 cst_ec_val_E709 CONSTANT VARCHAR2(4) := 'E709';
32 cst_ec_val_E710 CONSTANT VARCHAR2(4) := 'E710';
33
34 PROCEDURE prc_appl_hist ( p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
35 p_enable_log VARCHAR2,
36 p_rule VARCHAR2) AS
37
38 /*************************************
39 || Created By :Praveen Bondugula
40 || Date Created By :24-apr-2003
41 || Purpose : To import Qualification details
42 || Know limitations, enhancements or remarks
43 || Change History
44 || Who When What
45 ||
46 */
47
48
49
50 CURSOR c_appl_hist_cur IS
51 SELECT *
52 FROM igs_ad_apphist_int
53 WHERE interface_run_id = p_interface_run_id
54 AND status = '2'
55 ORDER BY person_id,admission_appl_number;
56
57 CURSOR c_appl_dtls_cur(cp_person_id igs_ad_appl.person_id%TYPE,
58 cp_adm_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
59 SELECT
60 acad_cal_type,
61 acad_ci_sequence_number,
62 adm_cal_type,
63 adm_ci_sequence_number,
64 admission_cat,
65 s_admission_process_type
66 FROM
67 igs_ad_appl
68 WHERE
69 person_id = cp_person_id AND
70 admission_appl_number = cp_adm_appl_number;
71
72 appl_dtls_rec c_appl_dtls_cur%ROWTYPE;
73
74 l_status VARCHAR2(1);
75
76 l_person_id igs_ad_interface.person_id%TYPE;
77 l_admission_appl_number igs_ad_apphist_int.admission_appl_number%TYPE;
78
79 l_person_id_errored igs_ad_interface.person_id%TYPE;
80 l_adm_appl_num_errored igs_ad_apphist_int.admission_appl_number%TYPE;
81
82 l_prog_label VARCHAR2(100);
83 l_label VARCHAR2(100);
84 l_debug_str VARCHAR2(2000);
85 l_request_id NUMBER;
86 l_error_code igs_ad_notes_int.error_code%TYPE;
87 l_records_processed NUMBER := 0;
88 l_rowid VARCHAR2(30);
89
90 l_msg_index NUMBER := 0;
91 l_error_text VARCHAR2(2000);
92 l_return_status VARCHAR2(1);
93 l_msg_count NUMBER ;
94 l_msg_data VARCHAR2(2000);
95 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
96
97
98 PROCEDURE validate_appl_hist(p_appl_hist_rec c_appl_hist_cur%ROWTYPE,
99 p_status OUT NOCOPY igs_ad_interface.status%TYPE,
100 p_error_code OUT NOCOPY igs_ad_interface.error_code%TYPE) IS
101
102 BEGIN
103 --Validate HIST_START_DT
104 IF p_appl_hist_rec.hist_start_dt > sysdate THEN
105 p_error_code := 'E645';
106 p_status := '3';
107 RETURN;
108 END IF;
109
110 --Validate HIST_END_DT
111 IF (p_appl_hist_rec.hist_end_dt > sysdate) OR (p_appl_hist_rec.hist_end_dt < p_appl_hist_rec.hist_start_dt) THEN
112 p_error_code := 'E646';
113 p_status := '3';
114 RETURN;
115 END IF;
116
117 -- Validate adm_appl_status
118 IF p_appl_hist_rec.adm_appl_status IS NOT NULL THEN
119 IF IGS_AD_GEN_007.ADMP_GET_SAAS(p_appl_hist_rec.adm_appl_status) = 'COMPLETED' THEN
120 p_error_code := 'E679';
121 p_status := '3';
122 RETURN;
123 END IF;
124 END IF;
125
126 IF p_appl_hist_rec.appl_dt IS NOT NULL THEN
127 -- Validate APPL_DT
128 IF p_appl_hist_rec.appl_dt > SYSDATE THEN
129 p_error_code := 'E649';
130 p_status := '3';
131 RETURN;
132 END IF;
133 END IF;
134
135 p_status := 1;
136 p_error_code := NULL;
137 RETURN;
138 END validate_appl_hist;
139
140 BEGIN
141 l_prog_label := 'igs.plsql.igs_ad_imp_027.prc_appl_hist';
142 l_label := 'igs.plsql.igs_ad_imp_027.prc_appl_hist.';
143
144 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
145 IF (l_request_id IS NULL) THEN
146 l_request_id := fnd_global.conc_request_id;
147 END IF;
148
149 l_label := 'igs.plsql.igs_ad_imp_027.prc_appl_hist.begin';
150 l_debug_str := 'igs_ad_imp_027.prc_appl_hist';
151
152 fnd_log.string_with_context( fnd_log.level_procedure,
153 l_label,
154 l_debug_str, NULL,
155 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
156 END IF;
157
158 -- Error out all the interface records, if the corrospending person already having application history details.
159 UPDATE igs_ad_apphist_int in_rec
160 SET error_code = cst_ec_val_E686
161 ,error_text = cst_et_val_E686
162 , status ='3'
163 WHERE STATUS = '2'
164 AND interface_run_id = p_interface_run_id
165 AND EXISTS ( SELECT 1
166 FROM igs_ad_appl_hist
167 WHERE person_id = in_rec.person_id
168 AND admission_appl_number = in_rec.admission_appl_number);
169
170 COMMIT;
171
172 UPDATE igs_ad_apphist_int in_rec
173 SET error_code = cst_ec_val_E709
174 ,error_text = cst_et_val_E709
175 , status ='3'
176 WHERE STATUS = '2'
177 AND interface_run_id = p_interface_run_id
178 AND NOT EXISTS ( SELECT 1
179 FROM igs_ad_appl
180 WHERE person_id = in_rec.person_id
181 AND admission_appl_number = in_rec.admission_appl_number);
182
183 COMMIT;
184
185 l_person_id := NULL;
186 l_admission_appl_number := NULL;
187
188 l_person_id_errored := NULL;
189 l_adm_appl_num_errored := NULL;
190
191 FOR appl_hist_rec IN c_appl_hist_cur
192 LOOP
193 l_msg_index := igs_ge_msg_stack.count_msg;
194
195 IF appl_hist_rec.person_id <> NVL(l_person_id_errored, -1)
196 AND appl_hist_rec.admission_appl_number <> NVL(l_adm_appl_num_errored, -1) THEN
197
198 DECLARE
199 invalid_record exception;
200 BEGIN
201
202 IF appl_hist_rec.person_id <> NVL(l_person_id, appl_hist_rec.person_id)
203 OR appl_hist_rec.admission_appl_number <> NVL(l_admission_appl_number, appl_hist_rec.admission_appl_number) THEN
204 COMMIT;
205 END IF;
206 l_person_id := appl_hist_rec.person_id;
207 l_admission_appl_number := appl_hist_rec.admission_appl_number;
208
209 l_error_Code := NULL;
210 validate_appl_hist(appl_hist_rec,l_status,l_error_code);
211
212 IF(l_status ='3' ) THEN
213 RAISE invalid_record;
214 END IF;
215
216 OPEN c_appl_dtls_cur(appl_hist_rec.person_id, appl_hist_rec.admission_appl_number);
217 FETCH c_appl_dtls_cur INTO appl_dtls_rec;
218 CLOSE c_appl_dtls_cur;
219
220 igs_ad_appl_hist_pkg.insert_row (
221 x_rowid => l_rowid,
222 x_org_id => NULL,
223 x_person_id => appl_hist_rec.person_id,
224 x_admission_appl_number => appl_hist_rec.admission_appl_number,
225 x_hist_start_dt => appl_hist_rec.hist_start_dt,
226 x_hist_end_dt => appl_hist_rec.hist_end_dt,
227 x_hist_who => fnd_global.user_id,
228 x_appl_dt => TRUNC(appl_hist_rec.appl_dt),
229 x_acad_cal_type => appl_dtls_rec.acad_cal_type,
230 x_acad_ci_sequence_number => appl_dtls_rec.acad_ci_sequence_number,
231 x_adm_cal_type => appl_dtls_rec.adm_cal_type,
232 x_adm_ci_sequence_number => appl_dtls_rec.adm_ci_sequence_number,
233 x_admission_cat => appl_dtls_rec.admission_cat,
234 x_s_admission_process_type => appl_dtls_rec.s_admission_process_type,
235 x_adm_appl_status => appl_hist_rec.adm_appl_status,
236 x_adm_fee_status => appl_hist_rec.adm_fee_status,
237 x_tac_appl_ind => appl_hist_rec.tac_appl_ind,
238 x_mode => 'R');
239
240 UPDATE igs_ad_apphist_int
241 SET status =cst_s_val_1
242 WHERE interface_apphist_id = appl_hist_rec.interface_apphist_id;
243 -- If the all qulaificaton records with same person_id are processed , then commit;
244 --If person_id changes from previous one then it means that records with previous person_id are processed
245 EXCEPTION
246 WHEN invalid_record THEN
247 ROLLBACK ;
248 l_person_id_errored := appl_hist_rec.person_id ;
249 l_adm_appl_num_errored := appl_hist_rec.admission_appl_number;
250
251 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
252 UPDATE igs_ad_apphist_int
253 SET status = cst_s_val_3
254 , error_code = l_error_code
255 , error_text = l_error_text
256 WHERE interface_apphist_id = appl_hist_rec.interface_apphist_id;
257
258 IF p_enable_log = 'Y' THEN
259 igs_ad_imp_001.logerrormessage(appl_hist_rec.interface_apphist_id,l_error_code,'IGS_AD_APPHIST_INT');
260 END IF;
261 l_error_code := 'E688';
262 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E688', 8405);
263
264 UPDATE igs_ad_apphist_int
265 SET status = cst_s_val_3
266 , error_code = l_error_code
267 , error_text = l_error_text
268 WHERE person_id = appl_hist_rec.person_id
269 AND status =cst_s_val_2
270 AND admission_appl_number = appl_hist_rec.admission_appl_number
271 AND interface_apphist_id <> appl_hist_rec.interface_apphist_id;
272
273 WHEN OTHERS THEN
274 ROLLBACK ;
275 l_error_code := 'E322';
276 igs_ad_gen_016.extract_msg_from_stack (
277 p_msg_at_index => l_msg_index,
278 p_return_status => l_return_status,
279 p_msg_count => l_msg_count,
280 p_msg_data => l_msg_data,
281 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
282 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
283 l_error_text := l_msg_data;
284 IF p_enable_log = 'Y' THEN
285 igs_ad_imp_001.logerrormessage(appl_hist_rec.interface_apphist_id,l_msg_data,'IGS_AD_APPHIST_INT');
286 END IF;
287 ELSE
288 l_error_code := 'E518';
289 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
290 l_label := 'igs.plsql.igs_ad_imp_027.prc_appl_hist.exception '||l_msg_data;
291 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
292 fnd_message.set_token('INTERFACE_ID',appl_hist_rec.interface_apphist_id);
293 fnd_message.set_token('ERROR_CD','E322');
294 l_debug_str := fnd_message.get;
295 fnd_log.string_with_context( fnd_log.level_exception,
296 l_label,
297 l_debug_str, NULL,
298 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
299 END IF;
300
301 END IF;
302 l_person_id_errored := appl_hist_rec.person_id ;
303 l_adm_appl_num_errored := appl_hist_rec.admission_appl_number;
304
305 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
306
307 UPDATE igs_ad_apphist_int
308 SET status = cst_s_val_3
309 , error_code = l_error_code
310 , error_text = l_error_text
311 WHERE interface_apphist_id = appl_hist_rec.interface_apphist_id;
312
313 l_error_code := 'E688';
314
315 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
316
317 UPDATE igs_ad_apphist_int
318 SET status = cst_s_val_3
319 , error_code = l_error_code
320 , error_text = l_error_text
321 WHERE person_id = appl_hist_rec.person_id
322 AND status =cst_s_val_2
323 AND admission_appl_number = appl_hist_rec.admission_appl_number
324 AND interface_apphist_id <> appl_hist_rec.interface_apphist_id;
325
326
327 END;
328 END IF;
329 END LOOP;
330 COMMIT;
331
332 END prc_appl_hist;
333
334 PROCEDURE prc_appl_inst_hist (p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
335 p_enable_log VARCHAR2,
336 p_rule VARCHAR2) AS
337
338 /*************************************
339 || Created By :Praveen Bondugula
340 || Date Created By :24-apr-2003
341 || Purpose : To import Qualification details
342 || Know limitations, enhancements or remarks
343 || Change History
344 || Who When What
345 ||
346 */
347
348 CURSOR c_applinst_dup_hist_cur(cp_person_id igs_ad_appl.person_id%TYPE,
349 cp_adm_appl_number igs_ad_appl.admission_appl_number%TYPE,
350 cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
351 cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE,
352 cp_hist_start_dt igs_ad_appl_hist.hist_start_dt%TYPE) IS
353 SELECT
354 'X'
355 FROM igs_ad_ps_aplinsthst
356 WHERE person_id = cp_person_id
357 AND admission_appl_number = cp_adm_appl_number
358 AND nominated_course_cd = cp_nominated_course_cd
359 AND sequence_number = cp_sequence_number
360 AND hist_start_dt = cp_hist_start_dt;
361
362 c_applinst_dup_hist_rec c_applinst_dup_hist_cur%ROWTYPE;
363
364 CURSOR c_applinst_dtls_cur(cp_person_id igs_ad_appl.person_id%TYPE,
365 cp_adm_appl_number igs_ad_appl.admission_appl_number%TYPE,
366 cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
367 cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
368 SELECT
369 apl.acad_cal_type,
370 apl.acad_ci_sequence_number,
371 aplinst.adm_cal_type,
372 aplinst.adm_ci_sequence_number,
373 apl.admission_cat,
374 apl.s_admission_process_type,
375 aplinst.course_cd,
376 aplinst.crv_version_number,
377 aplinst.late_adm_fee_status,
378 aplinst.correspondence_cat
379 FROM
380 igs_ad_appl apl,
381 igs_ad_ps_appl_inst aplinst
382 WHERE
383 apl.person_id = aplinst.person_id
384 AND apl.admission_appl_number = aplinst.admission_appl_number
385 AND apl.person_id = cp_person_id
386 AND apl.admission_appl_number = cp_adm_appl_number
387 AND aplinst.nominated_course_cd = cp_nominated_course_cd
388 AND aplinst.sequence_number = cp_sequence_number;
389
390 applinst_dtls_rec c_applinst_dtls_cur%ROWTYPE;
391
392 CURSOR c_applinst_hist_cur IS
393 SELECT *
394 FROM igs_ad_insthist_int
395 WHERE interface_run_id = p_interface_run_id
396 AND status = '2'
397 ORDER BY person_id,admission_appl_number,nominated_course_cd,sequence_number;
398
399 l_prog_label VARCHAR2(100);
400 l_label VARCHAR2(100);
401 l_debug_str VARCHAR2(2000);
402 l_request_id NUMBER;
403 l_error_code igs_ad_notes_int.error_code%TYPE;
404 l_records_processed NUMBER := 0;
405 l_status VARCHAR2(1);
406 l_rowid VARCHAR2(30);
407 l_exit VARCHAR2(1);
408
409 l_msg_index NUMBER := 0;
410 l_error_text VARCHAR2(2000);
411 l_return_status VARCHAR2(1);
412 l_msg_count NUMBER ;
413 l_msg_data VARCHAR2(2000);
414 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
415
416 l_person_id igs_ad_interface.person_id%TYPE;
417 l_admission_appl_number igs_ad_insthist_int.admission_appl_number%TYPE;
418 l_nominated_course_cd igs_ad_insthist_int.nominated_course_cd%TYPE;
419 l_sequence_number igs_ad_insthist_int.sequence_number%TYPE;
420
421 l_person_id_errored igs_ad_interface.person_id%TYPE;
422 l_adm_appl_num_errored igs_ad_insthist_int.admission_appl_number%TYPE;
423 l_nominated_course_cd_errored igs_ad_insthist_int.nominated_course_cd%TYPE;
424 l_sequence_number_errored igs_ad_insthist_int.sequence_number%TYPE;
425 l_exist VARCHAR2(1);
426
427
428 PROCEDURE validate_applinst_hist(p_applinst_hist_rec c_applinst_hist_cur%ROWTYPE,
429 p_status OUT NOCOPY igs_ad_interface.status%TYPE,
430 p_error_code OUT NOCOPY igs_ad_interface.error_code%TYPE)
431 IS
432 l_var VARCHAR2(1);
433
434 CURSOR c_validate_auth_id IS -- should be here
435 SELECT
436 'X'
437 FROM
438 hz_parties
439 WHERE
440 party_id = p_applinst_hist_rec.adm_otcm_stat_auth_per_number;
441
442 BEGIN
443 --Validate HIST_START_DT
444 IF p_applinst_hist_rec.hist_start_dt > sysdate THEN
445 p_error_code := 'E645';
446 p_status := '3';
447 RETURN;
448 END IF;
449
450 --Validate HIST_END_DT
451 IF (p_applinst_hist_rec.hist_end_dt > sysdate) OR (p_applinst_hist_rec.hist_end_dt < p_applinst_hist_rec.hist_start_dt) THEN
452 p_error_code := 'E646';
453 p_status := '3';
454 RETURN;
455 END IF;
456
457 IF p_applinst_hist_rec.adm_otcm_stat_auth_per_number IS NOT NULL THEN
458 OPEN c_validate_auth_id;
459 FETCH c_validate_auth_id INTO l_var;
460 IF c_validate_auth_id%NOTFOUND THEN
461 p_status := '3';
462 p_error_code := 'E655';
463 CLOSE c_validate_auth_id;
464 RETURN;
465 ELSE
466 IF NVL(igs_en_gen_003.get_staff_ind(p_applinst_hist_rec.adm_otcm_stat_auth_per_number),'N') = 'N' THEN
467 p_status := '3';
468 p_error_code := 'E655';
469 CLOSE c_validate_auth_id;
470 RETURN;
471 END IF;
472 END IF;
473 CLOSE c_validate_auth_id;
474 END IF;
475
476 p_status := '1';
477 p_error_code := NULL;
478
479 END validate_applinst_hist;
480
481 BEGIN
482 l_prog_label := 'igs.plsql.igs_ad_imp_027.prc_appl_inst_hist';
483 l_label := 'igs.plsql.igs_ad_imp_027.prc_appl_inst_hist.';
484
485 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
486 IF (l_request_id IS NULL) THEN
487 l_request_id := fnd_global.conc_request_id;
488 END IF;
489 l_label := 'igs.plsql.igs_ad_imp_027.prc_appl_inst_hist.begin';
490 l_debug_str := 'igs_ad_imp_027.prc_appl_inst_hist';
491 fnd_log.string_with_context( fnd_log.level_procedure,
492 l_label,
493 l_debug_str, NULL,
494 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
495 END IF;
496
497 -- Error out all the interface records, if the corrospending person already
498 -- having application instance history details.
499 UPDATE igs_ad_insthist_int in_rec
500 SET error_code = cst_ec_val_E689
501 , status ='3'
502 , error_text = cst_et_val_E689
503 WHERE STATUS = '2'
504 AND interface_run_id = p_interface_run_id
505 AND EXISTS ( SELECT 1 FROM igs_ad_ps_aplinsthst
506 WHERE person_id = in_rec.person_id
507 AND admission_appl_number = in_rec.admission_appl_number
508 AND nominated_course_cd = in_rec.nominated_course_cd
509 AND sequence_number = in_rec.sequence_number );
510
511 COMMIT;
512
513 UPDATE igs_ad_insthist_int in_rec
514 SET error_code = cst_ec_val_E710
515 , status ='3'
516 , error_text = cst_et_val_E710
517 WHERE STATUS = '2'
518 AND interface_run_id = p_interface_run_id
519 AND NOT EXISTS ( SELECT 1
520 FROM igs_ad_ps_appl_inst
521 WHERE person_id = in_rec.person_id
522 AND admission_appl_number = in_rec.admission_appl_number
523 AND nominated_course_cd = in_rec.nominated_course_cd
524 AND sequence_number = in_rec.sequence_number );
525
526 COMMIT;
527
528 l_person_id := NULL;
529 l_admission_appl_number := NULL;
530 l_nominated_course_cd := NULL;
531 l_sequence_number := NULL;
532
533 l_person_id_errored := NULL;
534 l_adm_appl_num_errored := NULL;
535 l_nominated_course_cd_errored := NULL;
536 l_sequence_number_errored := NULL;
537
538 FOR applinst_hist_rec IN c_applinst_hist_cur
539 LOOP
540 l_msg_index := igs_ge_msg_stack.count_msg;
541
542 IF applinst_hist_rec.person_id <> NVL(l_person_id_errored, -1)
543 AND applinst_hist_rec.admission_appl_number <> NVL(l_adm_appl_num_errored, -1)
544 AND applinst_hist_rec.nominated_course_cd <> NVL(l_nominated_course_cd_errored, '~')
545 AND applinst_hist_rec.sequence_number <> NVL(l_sequence_number_errored, -1) THEN
546
547 DECLARE
548 invalid_record exception;
549 BEGIN
550 IF applinst_hist_rec.person_id <> NVL(l_person_id, applinst_hist_rec.person_id)
551 OR applinst_hist_rec.admission_appl_number <> NVL(l_admission_appl_number, applinst_hist_rec.admission_appl_number)
552 OR applinst_hist_rec.nominated_course_cd <> NVL(l_nominated_course_cd, applinst_hist_rec.nominated_course_cd)
553 OR applinst_hist_rec.sequence_number <> NVL(l_sequence_number, applinst_hist_rec.sequence_number) THEN
554 COMMIT;
555 END IF;
556 l_person_id := applinst_hist_rec.person_id;
557 l_admission_appl_number := applinst_hist_rec.admission_appl_number;
558 l_nominated_course_cd := applinst_hist_rec.nominated_course_cd;
559 l_sequence_number := applinst_hist_rec.sequence_number;
560
561 l_exit := 'N';
562
563 LOOP
564 EXIT WHEN l_exit = 'Y';
565 -- Check if the record is already existing
566 OPEN c_applinst_dup_hist_cur(applinst_hist_rec.person_id,
567 applinst_hist_rec.admission_appl_number,
568 applinst_hist_rec.nominated_course_cd,
569 applinst_hist_rec.sequence_number,
570 applinst_hist_rec.hist_start_dt
571 );
572 FETCH c_applinst_dup_hist_cur INTO c_applinst_dup_hist_rec;
573
574 IF c_applinst_dup_hist_cur%FOUND THEN
575 -- Same record is found in the History table
576 -- so we need to increment the current histroy start date with
577 -- one second
578 applinst_hist_rec.hist_start_dt := applinst_hist_rec.hist_start_dt + (1/(24*60*60));
579 l_exit := 'N';
580 ELSE
581 l_exit := 'Y';
582 END IF;
583 CLOSE c_applinst_dup_hist_cur;
584 END LOOP;
585 l_error_Code := NULL;
586 validate_applinst_hist(applinst_hist_rec,l_status,l_error_code);
587 IF(l_status ='3' ) THEN
588 RAISE invalid_record;
589 END IF;
590 OPEN c_applinst_dtls_cur(applinst_hist_rec.person_id,
591 applinst_hist_rec.admission_appl_number,
592 applinst_hist_rec.nominated_course_cd,
593 applinst_hist_rec.sequence_number);
594 FETCH c_applinst_dtls_cur INTO applinst_dtls_rec;
595 CLOSE c_applinst_dtls_cur;
596 igs_ad_ps_aplinsthst_pkg.insert_row(
597 x_rowid => l_rowid,
598 x_org_id => null ,
599 x_person_id => applinst_hist_rec.person_id,
600 x_admission_appl_number => applinst_hist_rec.admission_appl_number,
601 x_nominated_course_cd => applinst_hist_rec.nominated_course_cd,
602 x_sequence_number => applinst_hist_rec.sequence_number,
603 x_hist_start_dt => applinst_hist_rec.hist_start_dt,
604 x_applicant_acptnce_cndtn => applinst_hist_rec.applicant_acptnce_cndtn,
605 x_cndtnl_offer_cndtn => applinst_hist_rec.cndtnl_offer_cndtn,
606 x_hist_end_dt => applinst_hist_rec.hist_end_dt,
607 x_hist_who => fnd_global.user_id,
608 x_hist_offer_round_number => null,
609 x_adm_cal_type => applinst_dtls_rec.adm_cal_type,
610 x_adm_ci_sequence_number => applinst_dtls_rec.adm_ci_sequence_number,
611 x_course_cd => applinst_dtls_rec.course_cd,
612 x_crv_version_number => applinst_dtls_rec.crv_version_number,
613 x_location_cd => applinst_hist_rec.location_cd,
614 x_attendance_mode => applinst_hist_rec.attendance_mode,
615 x_attendance_type => applinst_hist_rec.attendance_type,
616 x_unit_set_cd => applinst_hist_rec.unit_set_cd,
617 x_us_version_number => applinst_hist_rec.us_version_number,
618 x_preference_number => applinst_hist_rec.preference_number,
619 x_adm_doc_status => applinst_hist_rec.adm_doc_status,
620 x_adm_entry_qual_status => applinst_hist_rec.adm_entry_qual_status ,
621 x_late_adm_fee_status => applinst_dtls_rec.late_adm_fee_status,
622 x_adm_outcome_status => applinst_hist_rec.adm_outcome_status ,
623 x_adm_otcm_status_auth_per_id => applinst_hist_rec.adm_otcm_stat_auth_per_number,
624 x_adm_outcome_status_auth_dt => TRUNC(applinst_hist_rec.adm_outcome_status_auth_dt),
625 x_adm_outcome_status_reason => applinst_hist_rec.adm_outcome_status_reason ,
626 x_offer_dt => TRUNC(applinst_hist_rec.offer_dt) ,
627 x_offer_response_dt => TRUNC(applinst_hist_rec.offer_resp_date) ,
628 x_prpsd_commencement_dt => TRUNC(applinst_hist_rec.prpsd_commencement_dt) ,
629 x_adm_cndtnl_offer_status => applinst_hist_rec.adm_cndtnl_offer_status ,
630 x_cndtnl_offer_satisfied_dt => TRUNC(applinst_hist_rec.cndtnl_offer_satisfied_dt) ,
631 x_cndtnl_ofr_must_be_stsfd_ind => applinst_hist_rec.cndtnl_offer_must_be_stsfd_ind,
632 x_adm_offer_resp_status => applinst_hist_rec.adm_offer_resp_status ,
633 x_actual_response_dt => TRUNC(applinst_hist_rec.actual_response_dt) ,
634 x_adm_offer_dfrmnt_status => applinst_hist_rec.adm_offer_dfrmnt_status ,
635 x_deferred_adm_cal_type => NULL ,
636 x_deferred_adm_ci_sequence_num => NULL,
637 x_deferred_tracking_id => applinst_hist_rec.deferred_tracking_id ,
638 x_ass_rank => applinst_hist_rec.ass_rank,
639 x_secondary_ass_rank => applinst_hist_rec.secondary_ass_rank,
640 x_intrntnl_accept_advice_num => applinst_hist_rec.intrntnl_acceptance_advice_num,
641 x_ass_tracking_id => applinst_hist_rec.ass_tracking_id,
642 x_fee_cat => applinst_hist_rec.fee_cat,
643 x_hecs_payment_option => applinst_hist_rec.hecs_payment_option,
644 x_expected_completion_yr => applinst_hist_rec.expected_completion_yr,
645 x_expected_completion_perd => applinst_hist_rec.expected_completion_perd,
646 x_correspondence_cat => applinst_dtls_rec.correspondence_cat,
647 x_enrolment_cat => applinst_hist_rec.enrolment_cat,
648 x_funding_source => applinst_hist_rec.funding_source,
649 x_mode => 'R');
650
651 UPDATE igs_ad_insthist_int
652 SET status =cst_s_val_1
653 WHERE interface_insthist_id = applinst_hist_rec.interface_insthist_id;
654 -- If the all qulaificaton records with same person_id are processed , then commit;
655 --If person_id changes from previous one then it means that records with previous person_id are processed
656 EXCEPTION
657 WHEN invalid_record THEN
658 ROLLBACK ;
659 l_person_id_errored := applinst_hist_rec.person_id ;
660 l_adm_appl_num_errored := applinst_hist_rec.admission_appl_number;
661 l_nominated_course_cd_errored := applinst_hist_rec.nominated_course_cd;
662 l_sequence_number_errored := applinst_hist_rec.sequence_number;
663
664 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
665
666 UPDATE igs_ad_insthist_int
667 SET status = cst_s_val_3
668 , error_code = l_error_code
669 , error_text = l_error_text
670 WHERE interface_insthist_id = applinst_hist_rec.interface_insthist_id;
671
672 IF p_enable_log = 'Y' THEN
673 igs_ad_imp_001.logerrormessage(applinst_hist_rec.interface_insthist_id,l_error_code,'IGS_AD_INSTHIST_INT');
674 END IF;
675
676 l_error_code := 'E691';
677 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
678
679 UPDATE igs_ad_insthist_int
680 SET status = cst_s_val_3
681 , error_code = l_error_code
682 , error_text = l_error_text
683 WHERE person_id = applinst_hist_rec.person_id
684 AND status =cst_s_val_2
685 AND admission_appl_number = applinst_hist_rec.admission_appl_number
686 AND nominated_course_cd = applinst_hist_rec.nominated_course_cd
687 AND sequence_number = applinst_hist_rec.sequence_number
688 AND interface_insthist_id <> applinst_hist_rec.interface_insthist_id;
689
690 WHEN OTHERS THEN
691 ROLLBACK ;
692 l_error_code := 'E322';
693 igs_ad_gen_016.extract_msg_from_stack (
694 p_msg_at_index => l_msg_index,
695 p_return_status => l_return_status,
696 p_msg_count => l_msg_count,
697 p_msg_data => l_msg_data,
698 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
699
700 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
701
702 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
703 l_error_text := l_msg_data;
704 IF p_enable_log = 'Y' THEN
705 igs_ad_imp_001.logerrormessage(applinst_hist_rec.interface_insthist_id,l_msg_data,'IGS_AD_INSTHIST_INT');
706 END IF;
707 ELSE
708 l_error_code := 'E518';
709 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
710 l_label := 'igs.plsql.igs_ad_imp_027.prc_appl_inst_hist.exception '||l_msg_data;
711 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
712 fnd_message.set_token('INTERFACE_ID',applinst_hist_rec.interface_insthist_id);
713 fnd_message.set_token('ERROR_CD','E322');
714 l_debug_str := fnd_message.get;
715 fnd_log.string_with_context( fnd_log.level_exception,
716 l_label,
717 l_debug_str, NULL,
718 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
719 END IF;
720 END IF;
721 l_person_id_errored := applinst_hist_rec.person_id ;
722 l_adm_appl_num_errored := applinst_hist_rec.admission_appl_number;
723 l_nominated_course_cd_errored := applinst_hist_rec.nominated_course_cd;
724 l_sequence_number_errored := applinst_hist_rec.sequence_number;
725
726 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
727
728 UPDATE igs_ad_insthist_int
729 SET status = cst_s_val_3
730 , error_code = l_error_code
731 , error_text = l_error_text
732 WHERE interface_insthist_id = applinst_hist_rec.interface_insthist_id;
733
734 l_error_code := 'E691';
735
736 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
737
738 UPDATE igs_ad_insthist_int
739 SET status = cst_s_val_3
740 , error_code = l_error_code
741 , error_text = l_error_text
742 WHERE person_id = applinst_hist_rec.person_id
743 AND status =cst_s_val_2
744 AND admission_appl_number = applinst_hist_rec.admission_appl_number
745 AND nominated_course_cd = applinst_hist_rec.nominated_course_cd
746 AND sequence_number = applinst_hist_rec.sequence_number
747 AND interface_insthist_id <> applinst_hist_rec.interface_insthist_id;
748 END;
749 END IF;
750 END LOOP;
751 COMMIT;
752 END prc_appl_inst_hist;
753
754 END IGS_AD_IMP_027;