[Home] [Help]
PACKAGE BODY: APPS.IGS_DA_TRNS_IMP
Source
1 PACKAGE BODY igs_da_trns_imp AS
2 /* $Header: IGSDA12B.pls 120.19 2005/12/11 23:31:01 appldev noship $ */
3 l_msg_at_index NUMBER := 0;
4 l_return_status VARCHAR2 (1);
5 l_debug_str VARCHAR2 (1000);
6 l_msg_count NUMBER;
7 l_msg_data VARCHAR2 (2000);
8 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
9 l_label VARCHAR2 (200)
10 := 'igs.plsql.igs_da_trns_imp';
11 g_pkg_name CONSTANT VARCHAR2 (30) := 'igs_da_trns_imp';
12
13 PROCEDURE write_log (l_debug_str IN VARCHAR2, l_label IN VARCHAR2)
14 AS
15 l_prog_label VARCHAR2 (100) := 'igs.plsql.igs_da_trns_imp';
16 BEGIN
17 ecx_debug.push (l_debug_str);
18 ecx_debug.pop (l_debug_str);
19
20 IF fnd_log.test (fnd_log.level_statement, l_prog_label)
21 THEN
22 fnd_log.string_with_context (fnd_log.level_statement,
23 l_label,
24 l_debug_str,
25 NULL,
26 NULL,
27 NULL,
28 NULL,
29 NULL,
30 NULL
31 );
32 END IF;
33 END write_log;
34
35 PROCEDURE write_message (p_msg IN VARCHAR2)
36 -- this procedure will be used to debug
37 IS
38 BEGIN
39 write_log (p_msg, 'igs.plsql.igs_da_trns_imp.adv_stnd_import');
40 END write_message;
41
42 PROCEDURE notify_error (
43 p_batch_id IN igs_da_rqst.batch_id%TYPE,
44 p_person_id IN hz_parties.party_id%TYPE,
45 p_program_code IN igs_av_lgcy_unt_int.program_cd%TYPE,
46 p_msg IN VARCHAR2
47 )
48 IS
49
50 v_report_text VARCHAR2 (4000);
51 l_error_code VARCHAR2 (30) := 'REPLY_ERROR';
52 BEGIN
53 ecx_debug.push ('IGS_DA_TRNS_IMP.NOTIFY_ERROR');
54
55 IF p_msg IS NOT NULL
56 THEN
57 v_report_text :=
58 ' <HTML> <BODY> Error Report <BR> <BR> '
59 || p_msg
60 || ' '
61 || ' </BODY> </HTML> ';
62 END IF;
63
64 IF v_report_text IS NOT NULL
65 THEN
66 UPDATE igs_da_rqst
67 SET request_status = 'COMPLETE_ERROR'
68 WHERE batch_id = p_batch_id;
69
70 UPDATE igs_da_req_stdnts
71 SET report_text = v_report_text,
72 ERROR_CODE = l_error_code
73 WHERE batch_id = p_batch_id
74 AND person_id = p_person_id
75 AND program_code = p_program_code;
76 END IF;
77
78 write_message ('Calling IGS_DA_TRNS_IMP.NOTIFY_ERROR ' || p_msg);
79 igs_da_xml_pkg.process_reply_failure (p_batch_id);
80 ecx_debug.pop ('IGS_DA_TRNS_IMP.NOTIFY_ERROR');
81 EXCEPTION
82 WHEN OTHERS
83 THEN
84 write_message ('Error occurred. See log for Details' || sqlerrm);
85 END notify_error;
86
87 --start of local validation procedure
88 PROCEDURE validate_acadhis (
89 person_history_rec trans_cur_rec,
90 p_error_code OUT NOCOPY VARCHAR2,
91 p_status OUT NOCOPY VARCHAR2
92 )
93 AS
94 CURSOR c_val_inst_cd_non_uk_cur
95 IS
96 SELECT hp.ROWID row_id
97 FROM hz_parties p, igs_pe_hz_parties hp
98 WHERE hp.party_id = p.party_id
99 AND hp.inst_org_ind = 'I'
100 AND p.party_number = person_history_rec.prev_institution_code;
101
102 CURSOR c_val_inst_cd_uk_cur
103 IS
104 SELECT hp.ROWID row_id
105 FROM hz_parties p,
106 igs_pe_hz_parties hp,
107 igs_or_org_inst_type_all oit
108 WHERE hp.party_id = p.party_id
109 AND hp.inst_org_ind = 'I'
110 AND p.party_number = person_history_rec.prev_institution_code
111 AND hp.oi_institution_type = oit.institution_type(+)
112 AND oit.system_inst_type IN ('POST-SECONDARY', 'SECONDARY');
113
114 c_val_inst_cd_rec c_val_inst_cd_non_uk_cur%ROWTYPE;
115 BEGIN
116 ecx_debug.push ('IGS_DA_TRNS_IMP.VALIDATE_ACADHIS');
117 -- log header
118 c_val_inst_cd_rec.row_id := NULL;
119
120 --1. Institution Code
121 IF person_history_rec.prev_institution_code IS NOT NULL
122 THEN
123 IF fnd_profile.VALUE ('OSS_COUNTRY_CODE') <> 'GB'
124 THEN
125 OPEN c_val_inst_cd_non_uk_cur;
126 FETCH c_val_inst_cd_non_uk_cur INTO c_val_inst_cd_rec;
127 CLOSE c_val_inst_cd_non_uk_cur;
128 ELSE
129 OPEN c_val_inst_cd_uk_cur;
130 FETCH c_val_inst_cd_uk_cur INTO c_val_inst_cd_rec;
131 CLOSE c_val_inst_cd_uk_cur;
132 END IF;
133
134 IF c_val_inst_cd_rec.row_id IS NULL
135 THEN
136 p_error_code := 'E401';
137 p_status := '3';
138 RETURN;
139 END IF;
140 END IF;
141
142 --6. START_DATE
143 IF person_history_rec.start_date IS NOT NULL
144 THEN
145 IF NOT person_history_rec.start_date < SYSDATE
146 THEN
147 p_error_code := 'E405';
148 p_status := '3';
149 RETURN;
150 END IF;
151 END IF;
152
153 --7. END_DATE
154 IF person_history_rec.end_date IS NOT NULL
155 AND person_history_rec.start_date IS NOT NULL
156 THEN
157 IF NOT person_history_rec.end_date >= person_history_rec.start_date
158 THEN
159 p_error_code := 'E406';
160 p_status := '3';
161 RETURN;
162 END IF;
163 END IF;
164
165 p_error_code := NULL;
166 p_status := '1';
167 ecx_debug.pop ('IGS_DA_TRNS_IMP.VALIDATE_ACADHIS');
168 RETURN;
169 EXCEPTION
170 WHEN OTHERS
171 THEN
172 ecx_debug.pop ('IGS_DA_TRNS_IMP.VALIDATE_ACADHIS');
173 write_message('ERROR ' || sqlerrm);
174 p_error_code := 'E518';
175 p_status := '3';
176 -- log detail
177 RETURN;
178 END validate_acadhis;
179
180 --end of local validation procedure
181 ---------------------------------------------------------------------------
182 -- local procedure to insert the academic history record
183 PROCEDURE crc_pe_acad_hist (
184 person_history_rec IN OUT NOCOPY trans_cur_rec,
185 l_error_code IN OUT NOCOPY VARCHAR2
186 )
187 AS
188 l_msg_at_index NUMBER := 0;
189 l_return_status VARCHAR2 (1);
190 l_msg_count NUMBER;
191 l_msg_data VARCHAR2 (2000);
192 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
193 l_error_text VARCHAR2 (2000);
194 l_education_id NUMBER;
195 l_status VARCHAR2 (10);
196 l_object_version_number hz_education.object_version_number%TYPE
197 := NULL;
198 l_rowid VARCHAR2 (25);
199 l_prog_label VARCHAR2 (100)
200 := 'igs.plsql.igs_da_trns_imp';
201 BEGIN
202 ecx_debug.push ('IGS_DA_TRNS_IMP.CRC_PE_ACAD_HIST');
203 l_status := '1';
204 l_error_code := NULL;
205 l_error_text := NULL;
206 validate_acadhis (person_history_rec, l_error_code, l_status);
207
208 IF l_status = '1'
209 THEN
210 BEGIN
211 l_msg_at_index := igs_ge_msg_stack.count_msg;
212 SAVEPOINT before_create_hist;
213
214 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') =
215 'EXTERNAL'
216 )
217 THEN
218 write_message ('***** IGS_AD_ACAD_HISTORY_PKG.INSERT_ROW *****'
219 );
220 igs_ad_acad_history_pkg.insert_row (x_rowid => l_rowid,
221 x_attribute14 => NULL,
222 x_attribute15 => NULL,
223 x_attribute16 => NULL,
224 x_attribute17 => NULL,
225 x_attribute18 => NULL,
226 x_attribute19 => NULL,
227 x_attribute20 => NULL,
228 x_attribute13 => NULL,
229 x_attribute11 => NULL,
230 x_attribute12 => NULL,
231 x_education_id => l_education_id,
232 x_person_id => person_history_rec.person_id,
233 x_current_inst => 'N',
234 x_degree_attempted => NULL,
235 x_program_code => NULL,
236 x_degree_earned => NULL,
237 x_comments => NULL,
238 x_start_date => TO_DATE (NULL
239 ),
240 x_end_date => TO_DATE (NULL
241 ),
242 x_planned_completion_date => TO_DATE (NULL
243 ),
244 x_recalc_total_cp_attempted => NULL,
245 x_recalc_total_cp_earned => NULL,
246 x_recalc_total_unit_gp => NULL,
247 x_recalc_tot_gpa_units_attemp => NULL,
248 x_recalc_inst_gpa => NULL,
249 x_recalc_grading_scale_id => NULL,
250 x_selfrep_total_cp_attempted => NULL,
251 x_selfrep_total_cp_earned => NULL,
252 x_selfrep_total_unit_gp => NULL,
253 x_selfrep_tot_gpa_uts_attemp => NULL,
254 x_selfrep_inst_gpa => NULL,
255 x_selfrep_grading_scale_id => NULL,
256 x_selfrep_weighted_gpa => NULL,
257 x_selfrep_rank_in_class => NULL,
258 x_selfrep_weighed_rank => NULL,
259 x_type_of_school => NULL,
260 x_institution_code => person_history_rec.prev_institution_code,
261 x_attribute_category => NULL,
262 x_attribute1 => NULL,
263 x_attribute2 => NULL,
264 x_attribute3 => NULL,
265 x_attribute4 => NULL,
266 x_attribute5 => NULL,
267 x_attribute6 => NULL,
268 x_attribute7 => NULL,
269 x_attribute8 => NULL,
270 x_attribute9 => NULL,
271 x_attribute10 => NULL,
272 x_selfrep_class_size => NULL,
273 x_transcript_required => 'Y',
274 x_status => 'A',
275 x_object_version_number => l_object_version_number,
276 x_msg_data => l_msg_data,
277 x_return_status => l_return_status,
278 x_mode => 'R'
279 );
280 END IF;
281
282 person_history_rec.education_id := l_education_id;
283 ecx_debug.pop ('IGS_DA_TRNS_IMP.CRC_PE_ACAD_HIST');
284 EXCEPTION
285 WHEN OTHERS
286 THEN
287 ecx_debug.pop ('IGS_DA_TRNS_IMP.CRC_PE_ACAD_HIST');
288 write_message('ERROR ' || sqlerrm);
289 -- ROLLBACK TO before_create_hist;
290 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
291 p_return_status => l_return_status,
292 p_msg_count => l_msg_count,
293 p_msg_data => l_msg_data,
294 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab
295 );
296
297 IF l_hash_msg_name_text_type_tab (l_msg_count - 1).NAME <>
298 'ORA'
299 THEN
300 l_error_text := l_msg_data;
301 l_error_code := NULL;
302 write_log (l_msg_data,
303 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist'
304 );
305 ELSE
306 l_error_text := NULL;
307 l_error_code := 'E518';
308
309 IF fnd_log.test (fnd_log.level_exception, l_prog_label)
310 THEN
311 write_log (l_msg_data,
312 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist'
313 );
314 l_debug_str := fnd_message.get;
315 write_log (l_msg_data,
316 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist'
317 );
318 END IF;
319 END IF;
320
321 write_log (l_error_text,
322 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist'
323 );
324 RETURN;
325 END;
326
327 IF l_return_status IN ('E', 'U')
328 THEN
329 write_log (l_msg_data,
330 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist'
331 );
332 --log detail
333 ELSE
334 person_history_rec.education_id := l_education_id;
335 END IF;
336 ELSE -- validation fails
337 write_log (igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
338 l_error_code,
339 8405
340 ),
341 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist'
342 );
343 NULL;
344 END IF; -- end of ( l_error_code IS NULL ) */
345 EXCEPTION
346 WHEN OTHERS
347 THEN
348 write_message('ERROR ' || sqlerrm);
349 END crc_pe_acad_hist;
350
351 PROCEDURE prc_pe_acad_hist (acad_hist_rec IN OUT NOCOPY trans_cur_rec)
352 AS
353 CURSOR c_dup_cur
354 IS
355 SELECT ah.*
356 FROM igs_ad_acad_history_v ah
357 WHERE ( person_id = acad_hist_rec.person_id
358 AND institution_code = acad_hist_rec.prev_institution_code
359 );
360
361 dup_cur_rec c_dup_cur%ROWTYPE;
362
363 CURSOR c_edu_id
364 IS
365 SELECT h1.education_id
366 FROM hz_education h1, hz_parties h2
367 WHERE h1.party_id = acad_hist_rec.person_id
368 AND h2.party_number = acad_hist_rec.prev_institution_code
369 AND h2.party_id = h1.school_party_id
370 ORDER BY h1.creation_date DESC;
371
372 l_error_code VARCHAR2 (10);
373 l_prog_label VARCHAR2 (100);
374 BEGIN
375 ecx_debug.push ('IGS_DA_TRNS_IMP.PRC_PE_ACAD_HIST');
376 l_prog_label := 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist';
377 write_log ( 'Entered prc_pe_acad_hist prev_institution_code='
378 || acad_hist_rec.prev_institution_code
379 || ' person_id ='
380 || acad_hist_rec.person_id,
381 'igs.plsql.igs_da_trns_imp.prc_pe_acad_hist'
382 );
383 OPEN c_dup_cur;
384 FETCH c_dup_cur INTO dup_cur_rec;
385
386 IF c_dup_cur%NOTFOUND
387 THEN
388 write_log ('calling crc_pe_acad_hist ',
389 'igs.plsql.igs_da_trns_imp.prc_pe_acad_hist'
390 );
391 crc_pe_acad_hist (acad_hist_rec, l_error_code);
392 ELSE
393 write_log ('Not calling crc_pe_acad_hist ',
394 'igs.plsql.igs_da_trns_imp.prc_pe_acad_hist'
395 );
396 -- find the education id if acad hist exists
397 OPEN c_edu_id;
398 FETCH c_edu_id INTO acad_hist_rec.education_id;
399 CLOSE c_edu_id;
400
401 --If invalid education ID then error out.
402 IF acad_hist_rec.education_id IS NULL
403 THEN
404 write_log (igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
405 'E711',
406 8405
407 ),
408 'igs.plsql.igs_da_trns_imp.crc_pe_acad_hist'
409 );
410 END IF;
411 ---
412 END IF;
413
414 CLOSE c_dup_cur;
415 ecx_debug.pop ('IGS_DA_TRNS_IMP.PRC_PE_ACAD_HIST');
416 EXCEPTION
417 WHEN OTHERS
418 THEN
419 write_message('ERROR ' || sqlerrm);
420 END prc_pe_acad_hist;
421
422 PROCEDURE delete_adv_stnd_records (p_person_id IN hz_parties.party_id%TYPE)
423 AS
424 CURSOR c_edu_id
425 IS
426 SELECT education_id
427 FROM hz_education
428 WHERE party_id = p_person_id;
429
430 CURSOR c_trans (cp_education_id igs_ad_transcript.education_id%TYPE)
431 IS
432 SELECT trans_oss.ROWID, trans_oss.*
433 FROM igs_ad_transcript trans_oss
434 WHERE transcript_source IN (
435 SELECT code_id
436 FROM igs_ad_code_classes
437 WHERE CLASS = 'TRANSCRIPT_SOURCE'
438 AND closed_ind = 'N'
439 AND system_status = 'THIRD_PARTY_TRANSFER_EVAL')
440 AND education_id = cp_education_id
441 FOR UPDATE NOWAIT;
442
443 CURSOR c_trans_term (
444 p_transcript_id igs_ad_term_details.transcript_id%TYPE
445 )
446 IS
447 SELECT term_oss.ROWID, term_oss.*
448 FROM igs_ad_term_details term_oss
449 WHERE transcript_id = p_transcript_id
450 FOR UPDATE NOWAIT;
451
452 CURSOR c_term_unit (
453 p_term_details_id igs_ad_term_unitdtls.term_details_id%TYPE
454 )
455 IS
456 SELECT unit_oss.ROWID, unit_oss.*
457 FROM igs_ad_term_unitdtls unit_oss
458 WHERE term_details_id = p_term_details_id
459 FOR UPDATE NOWAIT;
460
461 CURSOR c_adv_stnd_unt (
462 p_unit_details_id igs_ad_term_unitdtls.unit_details_id%TYPE
463 )
464 IS
465 SELECT unt.ROWID, unt.*
466 FROM igs_av_stnd_unit_all unt
467 WHERE unit_details_id = p_unit_details_id
468 FOR UPDATE NOWAIT;
469
470 CURSOR c_adv_stnd
471 IS
472 SELECT adv.ROWID, adv.*
473 FROM igs_av_adv_standing_all adv
474 WHERE p_person_id = adv.person_id
475 FOR UPDATE NOWAIT;
476
477 CURSOR c_adv_unt
478 IS
479 SELECT unt.ROWID, unt.*
480 FROM igs_av_stnd_unit_all unt
481 WHERE p_person_id = unt.person_id
482 FOR UPDATE NOWAIT;
483
484 CURSOR c_adv_unt_basis(cp_AV_STND_UNIT_ID IGS_AV_STD_UNT_BASIS_ALL.AV_STND_UNIT_ID%type)
485 IS
486 SELECT unt.ROWID, unt.*
487 FROM IGS_AV_STD_UNT_BASIS_ALL unt
488 WHERE unt.AV_STND_UNIT_ID = cp_AV_STND_UNIT_ID
489 FOR UPDATE NOWAIT;
490
491 BEGIN
492 ecx_debug.push ('IGS_DA_TRNS_IMP.DELETE_ADV_STND_RECORDS');
493
494 FOR l_edu_id IN c_edu_id
495 LOOP
496 -- delete transcript information
497 FOR l_trans IN c_trans (l_edu_id.education_id)
498 LOOP
499 FOR l_trans_term IN c_trans_term (l_trans.transcript_id)
500 LOOP
501 FOR l_term_unit IN c_term_unit (l_trans_term.term_details_id)
502 LOOP
503
504 FOR l_adv_stnd_unt IN
505
506 c_adv_stnd_unt (l_term_unit.unit_details_id)
507 LOOP
508 FOR l__adv_unt_basis IN c_adv_unt_basis(l_adv_stnd_unt.AV_STND_UNIT_ID)
509 LOOP
510 IGS_AV_STD_UNT_BASIS_PKG.delete_row (l__adv_unt_basis.ROWID);
511 END LOOP;
512
513 -- delete advanced standing information
514 igs_av_stnd_unit_pkg.delete_row (l_adv_stnd_unt.ROWID);
515 END LOOP;
516
517 igs_ad_term_unitdtls_pkg.delete_row (l_term_unit.ROWID);
518 END LOOP;
519
520 igs_ad_term_details_pkg.delete_row (l_trans_term.ROWID);
521 END LOOP;
522
523 igs_ad_transcript_pkg.delete_row (l_trans.ROWID);
524 END LOOP;
525 END LOOP;
526
527 -- delete records from igs_av_adv_standing_all
528
529
530 FOR l_adv_stnd_unt IN c_adv_unt
531 LOOP
532 FOR l_adv_unt_basis IN c_adv_unt_basis(l_adv_stnd_unt.AV_STND_UNIT_ID)
533 LOOP
534 IGS_AV_STD_UNT_BASIS_PKG.delete_row (l_adv_unt_basis.ROWID);
535 END LOOP;
536 igs_av_stnd_unit_pkg.delete_row (l_adv_stnd_unt.ROWID);
537 END LOOP;
538
539 FOR l_adv_stnd IN c_adv_stnd
540 LOOP
541 BEGIN
542 igs_av_adv_standing_pkg.delete_row (l_adv_stnd.ROWID);
543 EXCEPTION
544 WHEN OTHERS
545 THEN
546 NULL;
547 END;
548 END LOOP;
549
550 ecx_debug.pop ('IGS_DA_TRNS_IMP.DELETE_ADV_STND_RECORDS');
551 EXCEPTION
552 WHEN OTHERS
553 THEN
554 write_message('ERROR ' || sqlerrm);
555 END delete_adv_stnd_records;
556
557 -- Create new Transcript
558
559 PROCEDURE create_new_transcript_details (
560 p_trans_record IN OUT NOCOPY trans_cur_rec
561 )
562 AS
563 l_rowid VARCHAR2 (25);
564 l_transcript_id igs_ad_txcpt_int.transcript_id%TYPE;
565 l_error_code VARCHAR2 (4) := NULL;
566 l_error_text VARCHAR2 (2000) := NULL;
567 override_ind VARCHAR2 (1) := 'N';
568
569 CURSOR c_source
570 IS
571 SELECT code_id
572 FROM igs_ad_code_classes
573 WHERE CLASS = 'TRANSCRIPT_SOURCE'
574 AND closed_ind = 'N'
575 AND system_status = 'THIRD_PARTY_TRANSFER_EVAL'
576 ORDER BY NVL (system_default, 'A') DESC;
577
578 l_transcript_source igs_ad_code_classes.code_id%TYPE;
579 BEGIN
580 ecx_debug.push ('IGS_DA_TRNS_IMP.CREATE_NEW_TRANSCRIPT_DETAILS');
581 l_transcript_id := NULL;
582
583 BEGIN
584 -- insert academic history record or find the education id if one already exists
585 SAVEPOINT before_create_transcript;
586 prc_pe_acad_hist (p_trans_record);
587 l_msg_at_index := igs_ge_msg_stack.count_msg;
588
589 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL'
590 )
591 THEN
592 OPEN c_source;
593 FETCH c_source INTO l_transcript_source;
594 CLOSE c_source;
595 write_message ('***** IGS_AD_TRANSCRIPT_PKG.INSERT_ROW *****');
596 igs_ad_transcript_pkg.insert_row (x_rowid => l_rowid,
597 x_quintile_rank => NULL,
598 x_percentile_rank => NULL,
599 x_transcript_id => l_transcript_id,
600 x_education_id => p_trans_record.education_id,
601 x_transcript_status => 'FINAL',
602 x_transcript_source => l_transcript_source,
603 x_date_of_receipt => TRUNC (SYSDATE
604 ),
605 x_entered_gpa => NULL,
606 x_entered_gs_id => fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE'
607 ),
608 x_conv_gpa => NULL,
609 x_conv_gs_id => fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE'
610 ),
611 x_term_type => p_trans_record.term_type,
612 x_rank_in_class => NULL,
613 x_class_size => NULL,
614 x_approximate_rank => NULL,
615 x_weighted_rank => NULL,
616 x_decile_rank => NULL,
617 x_quartile_rank => NULL,
618 x_transcript_type => 'OFFICIAL',
619 x_mode => 'R',
620 x_date_of_issue => TRUNC (SYSDATE
621 ),
622 x_override => NVL (override_ind,
623 'N'
624 ),
625 x_override_id => NULL,
626 x_override_date => NULL
627 );
628 END IF;
629
630 p_trans_record.transcript_id := l_transcript_id;
631 write_log ('igs_ad_transcript_pkg.insert_row',
632 'igs.plsql.igs_da_trns_imp.create_new_transcript_details'
633 );
634 igs_ad_wf_001.transcript_entrd_event (p_trans_record.person_id,
635 p_trans_record.education_id,
636 l_transcript_id
637 );
638 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_NEW_TRANSCRIPT_DETAILS');
639 EXCEPTION
640 WHEN OTHERS
641 THEN
642 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_NEW_TRANSCRIPT_DETAILS');
643 write_message('ERROR ' || sqlerrm);
644 -- ROLLBACK TO before_create_transcript;
645 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
646 p_return_status => l_return_status,
647 p_msg_count => l_msg_count,
648 p_msg_data => l_msg_data,
649 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab
650 );
651
652 IF l_hash_msg_name_text_type_tab (l_msg_count - 1).NAME <> 'ORA'
653 THEN
654 l_error_text := l_msg_data;
655 l_error_code := 'E322';
656 write_log (l_msg_data,
657 'igs.plsql.igs_da_trns_imp.create_new_transcript_details'
658 );
659 ELSE
660 l_error_text :=
661 igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
662 'E518',
663 8405
664 );
665 l_error_code := 'E518';
666 l_label :=
667 'igs.plsql.igs_da_trns_imp.create_new_transcript_details.exception '
668 || l_msg_data;
669 fnd_message.set_name ('IGS', 'IGS_PE_IMP_ERROR');
670 fnd_message.set_token ('INTERFACE_ID', 'Some Value');
671 fnd_message.set_token ('ERROR_CD', 'E322');
672 l_debug_str := fnd_message.get;
673 write_log (l_debug_str,
674 'igs.plsql.igs_da_trns_imp.create_new_transcript_details'
675 );
676 END IF;
677
678 write_log (l_error_text,
679 'igs.plsql.igs_da_trns_imp.create_new_transcript_details'
680 );
681 END;
682 EXCEPTION
683 WHEN OTHERS
684 THEN
685 write_message('ERROR ' || sqlerrm);
686 END create_new_transcript_details;
687
688 -- Update transcript details
689
690 PROCEDURE update_transcript_details (
691 p_trans_record IN OUT NOCOPY trans_cur_rec
692 )
693 AS
694 l_transcript_id igs_ad_txcpt_int.transcript_id%TYPE;
695 l_error_code VARCHAR2 (4) := NULL;
696 l_error_text VARCHAR2 (2000) := NULL;
697
698 CURSOR c_dup_cur
699 IS
700 SELECT trans_oss.ROWID row_id, trans_oss.*
701 FROM igs_ad_transcript trans_oss
702 WHERE ( transcript_id = p_trans_record.transcript_id
703 AND p_trans_record.transcript_id IS NOT NULL
704 )
705 OR ( p_trans_record.transcript_id IS NULL
706 AND education_id = p_trans_record.education_id
707 );
708
709 CURSOR c_source
710 IS
711 SELECT code_id
712 FROM igs_ad_code_classes
713 WHERE CLASS = 'TRANSCRIPT_SOURCE'
714 AND closed_ind = 'N'
715 AND system_status = 'THIRD_PARTY_TRANSFER_EVAL';
716
717 dup_cur_rec c_dup_cur%ROWTYPE;
718 l_source igs_ad_code_classes.code_id%TYPE;
719 BEGIN
720 ecx_debug.push ('IGS_DA_TRNS_IMP.UPDATE_TRANSCRIPT_DETAILS');
721 l_transcript_id := NULL;
722 OPEN c_dup_cur;
723 FETCH c_dup_cur INTO dup_cur_rec;
724 CLOSE c_dup_cur;
725 l_msg_at_index := igs_ge_msg_stack.count_msg;
726 SAVEPOINT before_update_transcript;
727
728 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
729 THEN
730 OPEN c_source;
731 FETCH c_source INTO l_source;
732 CLOSE c_source;
733 igs_ad_transcript_pkg.update_row (x_rowid => dup_cur_rec.row_id,
734 x_quintile_rank => dup_cur_rec.quintile_rank,
735 x_percentile_rank => dup_cur_rec.percentile_rank,
736 x_transcript_id => dup_cur_rec.transcript_id,
737 x_education_id => dup_cur_rec.education_id,
738 x_transcript_status => dup_cur_rec.transcript_status,
739 x_transcript_source => l_source,
740 x_date_of_receipt => TRUNC (SYSDATE
741 ),
742 x_entered_gpa => p_trans_record.unit_grade_points,
743 x_entered_gs_id => fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE'
744 ),
745 x_conv_gpa => dup_cur_rec.conv_gpa,
746 x_conv_gs_id => dup_cur_rec.conv_gs_id,
747 x_term_type => p_trans_record.term_type,
748 x_rank_in_class => dup_cur_rec.rank_in_class,
749 x_class_size => dup_cur_rec.class_size,
750 x_approximate_rank => dup_cur_rec.approximate_rank,
751 x_weighted_rank => dup_cur_rec.weighted_rank,
752 x_decile_rank => dup_cur_rec.decile_rank,
753 x_quartile_rank => dup_cur_rec.quartile_rank,
754 x_transcript_type => dup_cur_rec.transcript_type,
755 x_date_of_issue => dup_cur_rec.date_of_issue,
756 x_override => NULL,
757 x_override_id => NULL,
758 x_override_date => NULL
759 );
760 END IF;
761
762 p_trans_record.transcript_id := dup_cur_rec.transcript_id;
763 write_log ('Update trans details',
764 'igs.plsql.igs_da_trns_imp.update_transcript_details'
765 );
766 ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TRANSCRIPT_DETAILS');
767 EXCEPTION
768 WHEN OTHERS
769 THEN
770 ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TRANSCRIPT_DETAILS');
771 write_message('ERROR ' || sqlerrm);
772 -- ROLLBACK TO before_update_transcript;
773 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
774 p_return_status => l_return_status,
775 p_msg_count => l_msg_count,
776 p_msg_data => l_msg_data,
777 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab
778 );
779
780 IF l_hash_msg_name_text_type_tab (l_msg_count - 1).NAME <> 'ORA'
781 THEN
782 l_error_text := l_msg_data;
783 l_error_code := 'E014';
784 write_log (l_msg_data,
785 'igs.plsql.igs_da_trns_imp.update_transcript_details'
786 );
787 ELSE
788 l_error_text :=
789 igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
790 'E518',
791 8405
792 );
793 l_error_code := 'E518';
794 fnd_message.set_name ('IGS', 'IGS_PE_IMP_ERROR');
795 fnd_message.set_token ('INTERFACE_ID', 'Some Value');
796 fnd_message.set_token ('ERROR_CD', 'E014');
797 l_debug_str := fnd_message.get;
798 write_log (l_debug_str,
799 'igs.plsql.igs_da_trns_imp.update_transcript_details'
800 );
801 END IF;
802
803 write_log (l_error_text,
804 'igs.plsql.igs_da_trns_imp.update_transcript_details'
805 );
806 END update_transcript_details;
807
808 -- Update term unit details
809
810 PROCEDURE update_term_unit_details (
811 p_term_unitdtls_record IN OUT NOCOPY trans_cur_rec
812 )
813 AS
814 l_rowid VARCHAR2 (25);
815 l_var VARCHAR2 (25);
816 l_msg_at_index NUMBER := 0;
817 l_return_status VARCHAR2 (1);
818 l_msg_count NUMBER;
819 l_msg_data VARCHAR2 (2000);
820 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
821 l_error_code VARCHAR2 (4) := NULL;
822 l_error_text VARCHAR2 (2000) := NULL;
823
824 CURSOR c_dup_cur
825 IS
826 SELECT unit_oss.ROWID, unit_oss.*
827 FROM igs_ad_term_unitdtls unit_oss
828 WHERE term_details_id = p_term_unitdtls_record.term_details_id
829 AND unit = p_term_unitdtls_record.unit;
830
831 dup_cur_rec c_dup_cur%ROWTYPE;
832 BEGIN
833 ecx_debug.push ('IGS_DA_TRNS_IMP.UPDATE_TERM_UNIT_DETAILS');
834 OPEN c_dup_cur;
835 FETCH c_dup_cur INTO dup_cur_rec;
836 CLOSE c_dup_cur;
837 l_msg_at_index := igs_ge_msg_stack.count_msg;
838 SAVEPOINT before_update_unit;
839
840 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
841 THEN
842 igs_ad_term_unitdtls_pkg.update_row (x_rowid => dup_cur_rec.ROWID,
843 x_unit_details_id => dup_cur_rec.unit_details_id,
844 x_term_details_id => dup_cur_rec.term_details_id,
845 x_unit => p_term_unitdtls_record.unit,
846 x_unit_difficulty => dup_cur_rec.unit_difficulty,
847 x_unit_name => p_term_unitdtls_record.unit_name,
848 x_cp_attempted => p_term_unitdtls_record.cp_attempted,
849 x_cp_earned => p_term_unitdtls_record.cp_earned,
850 x_grade => p_term_unitdtls_record.grade,
851 x_unit_grade_points => p_term_unitdtls_record.unit_grade_points
852 );
853 END IF;
854
855 p_term_unitdtls_record.unit_details_id := dup_cur_rec.unit_details_id;
856 write_log ( 'igs_ad_term_unitdtls_pkg.update_row unit_details_id='
857 || p_term_unitdtls_record.unit_details_id,
858 'igs.plsql.igs_da_trns_imp.update_term_unit_details'
859 );
860 ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_UNIT_DETAILS');
861 EXCEPTION
862 WHEN OTHERS
863 THEN
864 ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_UNIT_DETAILS');
865 write_message('ERROR ' || sqlerrm);
866 -- ROLLBACK TO before_update_unit;
867 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
868 p_return_status => l_return_status,
869 p_msg_count => l_msg_count,
870 p_msg_data => l_msg_data,
871 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab
872 );
873
874 IF l_hash_msg_name_text_type_tab (l_msg_count - 1).NAME <> 'ORA'
875 THEN
876 l_error_text := l_msg_data;
877 l_error_code := 'E014';
878 write_log (l_msg_data,
879 'igs.plsql.igs_da_trns_imp.update_term_unit_details'
880 );
881 ELSE
882 l_error_text :=
883 igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
884 'E518',
885 8405
886 );
887 l_error_code := 'E518';
888 fnd_message.set_name ('IGS', 'IGS_PE_IMP_ERROR');
889 fnd_message.set_token ('INTERFACE_ID',
890 p_term_unitdtls_record.unit_details_id
891 );
892 fnd_message.set_token ('ERROR_CD', 'E014');
893 l_debug_str := fnd_message.get;
894 write_log (l_debug_str,
895 'igs.plsql.igs_da_trns_imp.update_term_unit_details'
896 );
897 END IF;
898
899 write_log (l_error_text,
900 'igs.plsql.igs_da_trns_imp.update_term_unit_details'
901 );
902 END update_term_unit_details;
903
904 -- Create term unit details
905
906 FUNCTION create_term_unit_details (
907 p_term_unitdtls_record IN OUT NOCOPY trans_cur_rec
908 )
909 RETURN igs_ad_term_unitdtls.unit_details_id%TYPE
910 AS
911 CURSOR c_unit_difficulty
912 IS
913 SELECT code_id
914 FROM igs_ad_code_classes
915 WHERE CLASS = 'UNIT_DIFFICULTY' AND NAME = 'STANDARD';
916
917 l_rowid VARCHAR2 (25);
918 l_var VARCHAR2 (25);
919 l_unit_details_id igs_ad_term_unitdtls.unit_details_id%TYPE;
920 l_msg_at_index NUMBER := 0;
921 l_return_status VARCHAR2 (1);
922 l_msg_count NUMBER;
923 l_msg_data VARCHAR2 (2000);
924 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
925 l_error_code VARCHAR2 (4) := NULL;
926 l_error_text VARCHAR2 (2000) := NULL;
927 l_unit_difficulty NUMBER;
928 BEGIN
929 ecx_debug.push ('IGS_DA_TRNS_IMP.CREATE_TERM_UNIT_DETAILS');
930 l_msg_at_index := igs_ge_msg_stack.count_msg;
931 SAVEPOINT before_create_unit;
932
933 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
934 THEN
935 write_log ('Before igs_ad_term_unitdtls_pkg.insert_row',
936 'igs.plsql.igs_da_trns_imp.create_term_unit_details'
937 );
938 OPEN c_unit_difficulty;
939 FETCH c_unit_difficulty INTO l_unit_difficulty;
940 CLOSE c_unit_difficulty;
941 write_message ('***** IGS_AD_TERM_UNITDTLS_PKG.INSERT_ROW *****');
942 igs_ad_term_unitdtls_pkg.insert_row (l_rowid,
943 l_unit_details_id,
944 p_term_unitdtls_record.term_details_id,
945 p_term_unitdtls_record.unit,
946 l_unit_difficulty,
947 p_term_unitdtls_record.unit_name,
948 p_term_unitdtls_record.cp_attempted,
949 p_term_unitdtls_record.cp_earned,
950 p_term_unitdtls_record.grade,
951 p_term_unitdtls_record.unit_grade_points
952 );
953 END IF;
954
955 write_log ( 'After igs_ad_term_unitdtls_pkg.insert_row l_unit_details_id='
956 || l_unit_details_id,
957 'igs.plsql.igs_da_trns_imp.create_term_unit_details'
958 );
959 p_term_unitdtls_record.unit_details_id := l_unit_details_id;
960 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_TERM_UNIT_DETAILS');
961 RETURN l_unit_details_id;
962 EXCEPTION
963 WHEN OTHERS
964 THEN
965 write_message('ERROR ' || sqlerrm);
966 -- ROLLBACK TO before_create_unit;
967 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
968 p_return_status => l_return_status,
969 p_msg_count => l_msg_count,
970 p_msg_data => l_msg_data,
971 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab
972 );
973
974 IF l_hash_msg_name_text_type_tab (l_msg_count - 1).NAME <> 'ORA'
975 THEN
976 l_error_text := l_msg_data || SQLERRM || ' ERROR';
977 l_error_code := 'E322';
978 write_log (l_msg_data || SQLERRM,
979 'igs.plsql.igs_da_trns_imp.create_term_unit_details'
980 );
981 ELSE
982 l_error_text :=
983 igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
984 'E518',
985 8405
986 );
987 l_error_code := 'E518';
988 l_label :=
989 'igs.plsql.igs_da_trns_imp.create_term_unit_details.exception '
990 || l_msg_data;
991 fnd_message.set_name ('IGS', 'IGS_PE_IMP_ERROR');
992 fnd_message.set_token ('INTERFACE_ID',
993 p_term_unitdtls_record.term_details_id
994 );
995 fnd_message.set_token ('ERROR_CD', 'E322');
996 l_debug_str := fnd_message.get;
997 write_log (l_debug_str,
998 'igs.plsql.igs_da_trns_imp.create_term_unit_details'
999 );
1000 END IF;
1001
1002 write_log (l_error_text,
1003 'igs.plsql.igs_da_trns_imp.create_term_unit_details'
1004 );
1005 END create_term_unit_details;
1006
1007 FUNCTION process_term_unit_details (
1008 p_batch_id IN igs_da_req_stdnts.batch_id%TYPE,
1009 p_person_id IN hz_parties.party_id%TYPE,
1010 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
1011 p_trans_cur_rec IN OUT NOCOPY trans_cur_rec
1012 )
1013 RETURN igs_ad_term_unitdtls.unit_details_id%TYPE
1014 AS
1015 l_unit_details_id igs_ad_term_unitdtls.unit_details_id%TYPE;
1016
1017 CURSOR c_dup_cur
1018 IS
1019 SELECT unit_oss.ROWID, unit_oss.*
1020 FROM igs_ad_term_unitdtls unit_oss
1021 WHERE term_details_id = p_trans_cur_rec.term_details_id
1022 AND unit = p_trans_cur_rec.unit
1023 AND unit_name = p_trans_cur_rec.unit_name;
1024
1025 dup_cur_rec c_dup_cur%ROWTYPE;
1026 BEGIN
1027 ecx_debug.push ('IGS_DA_TRNS_IMP.PROCESS_TERM_UNIT_DETAILS');
1028 write_log ('Entering process_term_unit_details',
1029 'igs.plsql.igs_da_trns_imp.process_term_unit_details'
1030 );
1031 OPEN c_dup_cur;
1032 FETCH c_dup_cur INTO dup_cur_rec;
1033
1034 IF c_dup_cur%NOTFOUND
1035 AND (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL'
1036 )
1037 THEN
1038 write_log ('Entering create_term_unit_details',
1039 'igs.plsql.igs_da_trns_imp.process_term_unit_details'
1040 );
1041 l_unit_details_id := create_term_unit_details (p_trans_cur_rec);
1042 ELSIF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
1043 THEN
1044 write_log ('Entering update_term_unit_details',
1045 'igs.plsql.igs_da_trns_imp.process_term_unit_details'
1046 );
1047 l_unit_details_id := dup_cur_rec.unit_details_id;
1048 update_term_unit_details (p_trans_cur_rec);
1049 ELSIF c_dup_cur%FOUND
1050 THEN
1051 l_unit_details_id := dup_cur_rec.unit_details_id;
1052 ELSE
1053 write_log ('Source Unit Not Found',
1054 'igs.plsql.igs_da_trns_imp.process_term_unit_details'
1055 );
1056 notify_error (p_batch_id,
1057 p_person_id,
1058 p_program_cd,
1059 fnd_message.get_string ('IGS',
1060 'IGS_DA_SRC_UNT_NOT_EXIST'
1061 )
1062 );
1063 END IF;
1064
1065 CLOSE c_dup_cur;
1066 write_log ('Source Unit l_unit_details_id=' || l_unit_details_id,
1067 'igs.plsql.igs_da_trns_imp.process_term_unit_details'
1068 );
1069 ecx_debug.pop ('IGS_DA_TRNS_IMP.PROCESS_TERM_UNIT_DETAILS');
1070 RETURN l_unit_details_id;
1071 EXCEPTION
1072 WHEN OTHERS
1073 THEN
1074 write_message('ERROR ' || sqlerrm);
1075 END process_term_unit_details;
1076
1077 PROCEDURE create_term_details (
1078 p_term_dtls_record IN OUT NOCOPY trans_cur_rec
1079 )
1080 AS
1081 l_rowid VARCHAR2 (25);
1082 l_term_details_id igs_ad_trmdt_int.term_details_id%TYPE;
1083 l_msg_at_index NUMBER := 0;
1084 l_return_status VARCHAR2 (1);
1085 l_msg_count NUMBER;
1086 l_msg_data VARCHAR2 (2000);
1087 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1088 l_error_code VARCHAR2 (4) := NULL;
1089 l_error_text VARCHAR2 (2000) := NULL;
1090 BEGIN
1091 ecx_debug.push ('IGS_DA_TRNS_IMP.CREATE_TERM_DETAILS');
1092 l_msg_at_index := igs_ge_msg_stack.count_msg;
1093 SAVEPOINT before_create_term;
1094
1095 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
1096 THEN
1097 write_message ( 'got p_term_dtls_record.transcript_id '
1098 || p_term_dtls_record.transcript_id
1099 );
1100 write_message ('***** IGS_AD_TERM_DETAILS_PKG.INSERT_ROW *****');
1101 igs_ad_term_details_pkg.insert_row (l_rowid,
1102 l_term_details_id,
1103 p_term_dtls_record.transcript_id,
1104 p_term_dtls_record.term,
1105 TRUNC (p_term_dtls_record.start_date
1106 ),
1107 TRUNC (p_term_dtls_record.end_date
1108 ),
1109 NULL,
1110 NULL,
1111 NULL,
1112 TO_NUMBER (NULL),
1113 --p_term_dtls_record.total_gpa_units,
1114 TO_NUMBER (NULL)
1115 --p_term_dtls_record.gpa
1116 );
1117 END IF;
1118
1119 p_term_dtls_record.term_details_id := l_term_details_id;
1120 write_log (l_term_details_id,
1121 'igs.plsql.igs_da_trns_imp.create_term_details'
1122 );
1123 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_TERM_DETAILS');
1124 -- Update Transcript Status
1125 EXCEPTION
1126 WHEN OTHERS
1127 THEN
1128 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_TERM_DETAILS');
1129 -- ROLLBACK TO before_create_term;
1130 write_message('ERROR ' || sqlerrm);
1131 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
1132 p_return_status => l_return_status,
1133 p_msg_count => l_msg_count,
1134 p_msg_data => l_msg_data,
1135 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab
1136 );
1137
1138 IF l_hash_msg_name_text_type_tab (l_msg_count - 1).NAME <> 'ORA'
1139 THEN
1140 l_error_text := l_msg_data;
1141 l_error_code := 'E322';
1142 write_log (l_error_text,
1143 'igs.plsql.igs_da_trns_imp.create_term_details'
1144 );
1145 ELSE
1146 l_error_text :=
1147 igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
1148 'E518',
1149 8405
1150 );
1151 l_error_code := 'E518';
1152 l_label :=
1153 'igs.plsql.igs_da_trns_imp.create_term_details.exception '
1154 || l_msg_data;
1155 fnd_message.set_name ('IGS', 'IGS_PE_IMP_ERROR');
1156 fnd_message.set_token ('INTERFACE_ID',
1157 p_term_dtls_record.term_details_id
1158 );
1159 fnd_message.set_token ('ERROR_CD', 'E322');
1160 l_debug_str := fnd_message.get;
1161 write_log (l_debug_str,
1162 'igs.plsql.igs_da_trns_imp.create_term_details'
1163 );
1164 END IF;
1165
1166 write_log (l_error_text,
1167 'igs.plsql.igs_da_trns_imp.create_term_details'
1168 );
1169 END create_term_details;
1170
1171 PROCEDURE update_term_details (
1172 p_term_dtls_record IN OUT NOCOPY trans_cur_rec
1173 )
1174 AS
1175 l_msg_at_index NUMBER := 0;
1176 l_return_status VARCHAR2 (1);
1177 l_msg_count NUMBER;
1178 l_msg_data VARCHAR2 (2000);
1179 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1180 l_error_code VARCHAR2 (4) := NULL;
1181 l_error_text VARCHAR2 (2000) := NULL;
1182 l_prog_label VARCHAR2 (100)
1183 := 'igs.plsql.igs_da_trns_imp.update_term_details';
1184
1185 CURSOR c_dup_cur
1186 IS
1187 SELECT term_oss.ROWID, term_oss.*
1188 FROM igs_ad_term_details term_oss
1189 WHERE transcript_id = p_term_dtls_record.transcript_id
1190 AND term = p_term_dtls_record.term
1191 AND TRUNC (start_date) = TRUNC (p_term_dtls_record.start_date)
1192 AND TRUNC (end_date) = TRUNC (p_term_dtls_record.end_date);
1193
1194 dup_cur_rec c_dup_cur%ROWTYPE;
1195 BEGIN
1196 ecx_debug.push ('IGS_DA_TRNS_IMP.UPDATE_TERM_DETAILS');
1197 OPEN c_dup_cur;
1198 FETCH c_dup_cur INTO dup_cur_rec;
1199 CLOSE c_dup_cur;
1200 l_msg_at_index := igs_ge_msg_stack.count_msg;
1201 SAVEPOINT before_update_term;
1202
1203 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
1204 THEN
1205 igs_ad_term_details_pkg.update_row (x_rowid => dup_cur_rec.ROWID,
1206 x_term_details_id => dup_cur_rec.term_details_id,
1207 x_transcript_id => dup_cur_rec.transcript_id,
1208 x_term => p_term_dtls_record.term,
1209 x_start_date => TRUNC (p_term_dtls_record.start_date
1210 ),
1211 x_end_date => TRUNC (p_term_dtls_record.end_date
1212 ),
1213 x_total_cp_attempted => dup_cur_rec.total_cp_attempted,
1214 --dup_cur_rec.total_cp_attempted,
1215 x_total_cp_earned => dup_cur_rec.total_cp_earned,
1216 --p_term_dtls_record.total_cp_earned,
1217 x_total_unit_gp => dup_cur_rec.total_unit_gp,
1218 --p_term_dtls_record.total_unit_gp,
1219 x_total_gpa_units => dup_cur_rec.total_gpa_units,
1220 --p_term_dtls_record.total_gpa_units
1221 x_gpa => dup_cur_rec.gpa
1222 );
1223 END IF;
1224
1225 p_term_dtls_record.term_details_id := dup_cur_rec.term_details_id;
1226 write_log ('igs_ad_term_details_pkg.update_row',
1227 'igs.plsql.igs_da_trns_imp.update_term_details'
1228 );
1229 ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_DETAILS');
1230 EXCEPTION
1231 WHEN OTHERS
1232 THEN
1233 ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_DETAILS');
1234 write_message('ERROR ' || sqlerrm);
1235 -- ROLLBACK TO before_update_term;
1236 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
1237 p_return_status => l_return_status,
1238 p_msg_count => l_msg_count,
1239 p_msg_data => l_msg_data,
1240 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab
1241 );
1242
1243 IF l_hash_msg_name_text_type_tab (l_msg_count - 1).NAME <> 'ORA'
1244 THEN
1245 l_error_text := l_msg_data;
1246 l_error_code := 'E014';
1247 write_log (l_msg_data,
1248 'igs.plsql.igs_da_trns_imp.update_term_details'
1249 );
1250 ELSE
1251 l_error_text :=
1252 igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE',
1253 'E518',
1254 8405
1255 );
1256 l_error_code := 'E518';
1257
1258 IF fnd_log.test (fnd_log.level_exception, l_prog_label)
1259 THEN
1260 l_label :=
1261 'igs.plsql.igs_ad_imp_024.update_term_details.exception '
1262 || l_msg_data;
1263 fnd_message.set_name ('IGS', 'IGS_PE_IMP_ERROR');
1264 fnd_message.set_token ('INTERFACE_ID',
1265 p_term_dtls_record.term_details_id
1266 );
1267 fnd_message.set_token ('ERROR_CD', 'E014');
1268 l_debug_str := fnd_message.get;
1269 write_log (l_debug_str,
1270 'igs.plsql.igs_da_trns_imp.update_term_details'
1271 );
1272 END IF;
1273 END IF;
1274
1275 write_log ('igs_da_trns_imp.update_term_details',
1276 'igs.plsql.igs_da_trns_imp.update_term_details'
1277 );
1278 END update_term_details;
1279
1280 PROCEDURE process_term_details (
1281 p_batch_id IN igs_da_req_stdnts.batch_id%TYPE,
1282 p_person_id IN hz_parties.party_id%TYPE,
1283 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
1284 p_trans_cur_rec IN OUT NOCOPY trans_cur_rec
1285 )
1286 AS
1287 CURSOR c_dup_cur
1288 IS
1289 SELECT term_oss.ROWID, term_oss.*
1290 FROM igs_ad_term_details term_oss
1291 WHERE transcript_id = p_trans_cur_rec.transcript_id
1292 AND term = p_trans_cur_rec.term
1293 AND TRUNC (start_date) = TRUNC (p_trans_cur_rec.start_date)
1294 AND TRUNC (end_date) = TRUNC (p_trans_cur_rec.end_date);
1295
1296 dup_cur_rec c_dup_cur%ROWTYPE;
1297 BEGIN
1298 ecx_debug.push ('IGS_DA_TRNS_IMP.PROCESS_TERM_DETAILS');
1299 write_log ('process_term_details',
1300 'igs.plsql.igs_da_trns_imp.process_term_details'
1301 );
1302 OPEN c_dup_cur;
1303 FETCH c_dup_cur INTO dup_cur_rec;
1304
1305 IF c_dup_cur%NOTFOUND
1306 AND NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL'
1307 THEN
1308 create_term_details (p_trans_cur_rec);
1309 ELSIF NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL'
1310 THEN
1311 p_trans_cur_rec.term_details_id := dup_cur_rec.term_details_id;
1312 update_term_details (p_trans_cur_rec);
1313 ELSIF c_dup_cur%FOUND
1314 THEN
1315 p_trans_cur_rec.term_details_id := dup_cur_rec.term_details_id;
1316 ELSE
1317 write_log ('ERROR :- Term details not found ',
1318 'igs.plsql.igs_da_trns_imp.update_term_details'
1319 );
1320 notify_error (p_batch_id,
1321 p_person_id,
1322 p_program_cd,
1323 'ERROR :- Term details not found '
1324 );
1325 END IF;
1326
1327 CLOSE c_dup_cur;
1328 write_log ('end process_term_details',
1329 'igs.plsql.igs_da_trns_imp.process_term_details'
1330 );
1331 ecx_debug.pop ('IGS_DA_TRNS_IMP.PROCESS_TERM_DETAILS');
1332 EXCEPTION
1333 WHEN OTHERS
1334 THEN
1335 write_message('ERROR ' || sqlerrm);
1336 END process_term_details;
1337
1338 PROCEDURE create_acad_hist_rec (
1339 p_batch_id IN igs_da_req_stdnts.batch_id%TYPE,
1340 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
1341 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
1342 p_person_id_code_type IN igs_pe_alt_pers_id.api_person_id%TYPE,
1343 p_term_type IN VARCHAR2,
1344 p_term IN igs_ad_term_details.term%TYPE,
1345 p_start_date IN VARCHAR2,
1346 p_end_date IN VARCHAR2,
1347 p_source_course_subject IN VARCHAR2,
1348 p_source_course_num IN VARCHAR2,
1349 p_unit_name IN igs_ad_term_unitdtls.unit_name%TYPE,
1350 p_inst_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
1351 p_inst_id_code_type IN igs_pe_alt_pers_id.api_person_id%TYPE,
1352 p_cp_attempted IN igs_ad_term_unitdtls.cp_attempted%TYPE,
1353 p_cp_earned IN igs_ad_term_unitdtls.cp_earned%TYPE,
1354 p_grade IN igs_ad_term_unitdtls.grade%TYPE,
1355 p_unit_grade_points IN igs_ad_term_unitdtls.unit_grade_points%TYPE,
1356 p_unit_details_id OUT NOCOPY igs_ad_term_unitdtls.unit_details_id%TYPE
1357 )
1358 AS
1359 l_return_status VARCHAR2 (1);
1360 l_trans_cur_rec trans_cur_rec;
1361 l_unit igs_ad_term_unitdtls.unit%TYPE;
1362
1363 CURSOR c_dup_cur
1364 IS
1365 SELECT trans_oss.ROWID, trans_oss.*
1366 FROM igs_ad_transcript trans_oss
1367 WHERE trans_oss.term_type = l_trans_cur_rec.term_type
1368 AND ( ( transcript_id = l_trans_cur_rec.transcript_id
1369 AND l_trans_cur_rec.transcript_id IS NOT NULL
1370 )
1371 OR ( l_trans_cur_rec.transcript_id IS NULL
1372 AND education_id = l_trans_cur_rec.education_id
1373 -- AND TRUNC (date_of_issue) = TRUNC (SYSDATE)
1374 )
1375 )
1376 ORDER BY last_update_date DESC;
1377
1378 CURSOR c_edu_id (
1379 cp_person_id hz_education.party_id%TYPE,
1380 p_school_party_id hz_education.school_party_id%TYPE
1381 )
1382 IS
1383 SELECT hz.ROWID, hz.*
1384 FROM hz_education hz
1385 WHERE hz.party_id = cp_person_id
1386 AND hz.school_party_id = p_school_party_id
1387 ORDER BY hz.last_update_date DESC;
1388
1389 CURSOR c_transcript_id
1390 IS
1391 SELECT transcript_id
1392 FROM igs_ad_transcript
1393 WHERE education_id = l_trans_cur_rec.education_id
1394 AND transcript_status = 'FINAL'
1395 AND transcript_source IN (
1396 SELECT code_id
1397 FROM igs_ad_code_classes
1398 WHERE CLASS = 'TRANSCRIPT_SOURCE'
1399 AND closed_ind = 'N'
1400 AND system_status = 'THIRD_PARTY_TRANSFER_EVAL')
1401 AND entered_gs_id = fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE')
1402 AND conv_gs_id = fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE')
1403 AND term_type = l_trans_cur_rec.term_type
1404 AND transcript_type = 'OFFICIAL'
1405 ORDER BY last_update_date DESC;
1406
1407 CURSOR c_edu
1408 IS
1409 SELECT hz.ROWID, hz.*
1410 FROM hz_education hz
1411 WHERE education_id = l_trans_cur_rec.education_id;
1412
1413 dup_cur_rec c_dup_cur%ROWTYPE;
1414 l_person_number hz_parties.party_number%TYPE;
1415 l_school_party_id hz_parties.party_id%TYPE;
1416 l_edu_rec c_edu%ROWTYPE;
1417 l_edu_id_rec c_edu_id%ROWTYPE;
1418 BEGIN
1419 write_message (' p_batch_id ' || p_batch_id);
1420 write_message (' p_program_cd ' || p_program_cd);
1421 write_message (' p_person_id_code ' || p_person_id_code);
1422 write_message ( ' p_person_id_code_type '
1423 || p_person_id_code_type
1424 );
1425 write_message ( ' p_term_type '
1426 || SUBSTR (p_term_type, 1, 1)
1427 );
1428 write_message (' p_term ' || p_term);
1429 write_message (' p_start_date ' || p_start_date);
1430 write_message (' p_end_date ' || p_end_date);
1431 write_message ( ' p_source_course_subject '
1432 || p_source_course_subject
1433 );
1434 write_message (' p_source_course_num ' || p_source_course_num);
1435 write_message (' p_unit_name ' || p_unit_name);
1436 write_message (' p_inst_id_code ' || p_inst_id_code);
1437 write_message (' p_inst_id_code_type ' || p_inst_id_code_type);
1438 write_message (' p_cp_attempted ' || p_cp_attempted);
1439 write_message (' p_cp_earned ' || p_cp_earned);
1440 write_message (' p_grade ' || p_grade);
1441 write_message (' p_unit_grade_points ' || p_unit_grade_points);
1442 ecx_debug.push ('IGS_DA_TRNS_IMP.CREATE_ACAD_HIST_REC');
1443 write_log ('start create_acad_hist_rec',
1444 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1445 );
1446 --initialise(trans_cur_rec);
1447 l_unit := p_source_course_subject || p_source_course_num;
1448 l_trans_cur_rec.term_type := SUBSTR (p_term_type, 1, 1);
1449 l_trans_cur_rec.term := p_term;
1450 l_trans_cur_rec.start_date :=
1451 TO_DATE (SUBSTR (RTRIM (LTRIM (p_start_date)), 1, 10), 'YYYY-MM-DD');
1452 l_trans_cur_rec.end_date :=
1453 TO_DATE (SUBSTR (RTRIM (LTRIM (p_end_date)), 1, 10), 'YYYY-MM-DD');
1454 l_trans_cur_rec.unit := l_unit;
1455 l_trans_cur_rec.unit_name := p_unit_name;
1456 l_trans_cur_rec.cp_attempted := p_cp_attempted;
1457 l_trans_cur_rec.cp_earned := p_cp_earned;
1458 l_trans_cur_rec.grade := p_grade;
1459 l_trans_cur_rec.unit_grade_points := p_unit_grade_points;
1460 -- get institution code
1461 igs_da_xml_pkg.get_person_details (RTRIM (LTRIM (p_inst_id_code)),
1462 RTRIM (LTRIM (p_inst_id_code_type)),
1463 l_school_party_id,
1464 l_trans_cur_rec.prev_institution_code
1465 );
1466 write_message ( 'Got prev_institution_code= '
1467 || l_trans_cur_rec.prev_institution_code
1468 );
1469 -- get person ID
1470 igs_da_xml_pkg.get_person_details (RTRIM (LTRIM (p_person_id_code)),
1471 RTRIM (LTRIM (p_person_id_code_type)),
1472 l_trans_cur_rec.person_id,
1473 l_person_number
1474 );
1475
1476 -- if student ID is not found
1477
1478 IF l_trans_cur_rec.person_id IS NULL
1479 THEN
1480 write_log ('ERROR Unable to validate student ID',
1481 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1482 );
1483 igs_da_xml_pkg.process_reply_failure (p_batch_id);
1484 p_unit_details_id := NULL;
1485 END IF;
1486
1487 OPEN c_edu_id (l_trans_cur_rec.person_id, l_school_party_id);
1488 FETCH c_edu_id INTO l_edu_id_rec;
1489
1490 IF c_edu_id%FOUND
1491 THEN
1492 l_trans_cur_rec.education_id := l_edu_id_rec.education_id;
1493 hz_education_pkg.update_row (x_rowid => l_edu_id_rec.ROWID,
1494 x_education_id => l_edu_id_rec.education_id,
1495 x_course_major => l_edu_id_rec.course_major,
1496 x_party_id => l_edu_id_rec.party_id,
1497 x_school_party_id => l_edu_id_rec.school_party_id,
1498 x_degree_received => l_edu_id_rec.degree_received,
1499 x_last_date_attended => l_edu_id_rec.last_date_attended,
1500 x_school_attended_name => l_edu_id_rec.school_attended_name,
1501 x_type_of_school => l_edu_id_rec.type_of_school,
1502 x_start_date_attended => l_edu_id_rec.start_date_attended,
1503 x_status => 'A',
1504 x_object_version_number => l_edu_id_rec.object_version_number,
1505 x_created_by_module => l_edu_id_rec.created_by_module,
1506 x_application_id => l_edu_id_rec.application_id
1507 );
1508 ELSE
1509 l_trans_cur_rec.education_id := NULL;
1510 END IF;
1511
1512 CLOSE c_edu_id;
1513 write_log ('Got education ID as ' || l_trans_cur_rec.education_id,
1514 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1515 );
1516 OPEN c_dup_cur;
1517 FETCH c_dup_cur INTO dup_cur_rec;
1518
1519 IF ( (l_trans_cur_rec.education_id IS NULL OR c_dup_cur%NOTFOUND)
1520 AND NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL'
1521 )
1522 THEN
1523 write_log ('Calling create_new_transcript_details',
1524 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1525 );
1526 create_new_transcript_details (l_trans_cur_rec);
1527 ELSIF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
1528 THEN
1529 write_log ( 'Calling update_transcript_details for transcript_id='
1530 || dup_cur_rec.transcript_id,
1531 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1532 );
1533 l_trans_cur_rec.transcript_id := dup_cur_rec.transcript_id;
1534 update_transcript_details (l_trans_cur_rec);
1535 ELSIF (c_dup_cur%NOTFOUND)
1536 THEN
1537 notify_error (p_batch_id,
1538 l_trans_cur_rec.person_id,
1539 p_program_cd,
1540 'Unable to find transcript information.'
1541 );
1542 END IF;
1543
1544 IF ( c_dup_cur%FOUND
1545 AND NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') <>
1546 'EXTERNAL'
1547 )
1548 THEN
1549 l_trans_cur_rec.transcript_id := dup_cur_rec.transcript_id;
1550 END IF;
1551
1552 CLOSE c_dup_cur;
1553 write_log ('Before c_transcript_id',
1554 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1555 );
1556
1557 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
1558 THEN
1559 OPEN c_transcript_id;
1560 FETCH c_transcript_id INTO l_trans_cur_rec.transcript_id;
1561 CLOSE c_transcript_id;
1562 END IF;
1563
1564 write_log ( 'After c_transcript_id transcript_id='
1565 || l_trans_cur_rec.transcript_id,
1566 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1567 );
1568 write_log ('Before process_term_details',
1569 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1570 );
1571 process_term_details (p_batch_id,
1572 l_trans_cur_rec.person_id,
1573 p_program_cd,
1574 l_trans_cur_rec
1575 );
1576 write_log ('Before process_term_unit_details',
1577 'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
1578 );
1579 p_unit_details_id :=
1580 process_term_unit_details (p_batch_id,
1581 l_trans_cur_rec.person_id,
1582 p_program_cd,
1583 l_trans_cur_rec
1584 );
1585 -- set the institution rec as inactive
1586
1587 OPEN c_edu;
1588 FETCH c_edu INTO l_edu_rec;
1589 hz_education_pkg.update_row (x_rowid => l_edu_rec.ROWID,
1590 x_education_id => l_edu_rec.education_id,
1591 x_course_major => l_edu_rec.course_major,
1592 x_party_id => l_edu_rec.party_id,
1593 x_school_party_id => l_edu_rec.school_party_id,
1594 x_degree_received => l_edu_rec.degree_received,
1595 x_last_date_attended => l_edu_rec.last_date_attended,
1596 x_school_attended_name => l_edu_rec.school_attended_name,
1597 x_type_of_school => l_edu_rec.type_of_school,
1598 x_start_date_attended => l_edu_rec.start_date_attended,
1599 x_status => 'I',
1600 x_object_version_number => l_edu_rec.object_version_number,
1601 x_created_by_module => l_edu_rec.created_by_module,
1602 x_application_id => l_edu_rec.application_id
1603 );
1604 CLOSE c_edu;
1605 -- COMMIT;
1606 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_ACAD_HIST_REC');
1607 EXCEPTION
1608 WHEN OTHERS
1609 THEN
1610 write_message('ERROR ' || sqlerrm);
1611 END create_acad_hist_rec;
1612
1613 /********************************************************************
1614 Create Advanced Standing Record
1615 ********************************************************************/
1616 FUNCTION get_adv_stnd_granting_status (
1617 p_batch_id igs_da_rqst.batch_id%TYPE
1618 )
1619 RETURN igs_av_stnd_unit_all.s_adv_stnd_granting_status%TYPE
1620 IS
1621 CURSOR c_ftr_val
1622 IS
1623 SELECT feature_value
1624 FROM igs_da_req_ftrs
1625 WHERE batch_id = p_batch_id AND feature_code = 'AUT';
1626
1627 l_automatic_grant VARCHAR2 (5);
1628 BEGIN
1629 ecx_debug.push ('IGS_DA_TRNS_IMP.GET_ADV_STND_GRANTING_STATUS');
1630
1631 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
1632 THEN
1633 RETURN 'GRANTED';
1634 ELSE
1635 -- check if autmatically grant adv stnd is checked
1636
1637 OPEN c_ftr_val;
1638 FETCH c_ftr_val INTO l_automatic_grant;
1639 CLOSE c_ftr_val;
1640
1641 IF (NVL (l_automatic_grant, 'N') = 'Y')
1642 THEN
1643 RETURN 'GRANTED';
1644 ELSE
1645 RETURN 'APPROVED';
1646 END IF;
1647 END IF;
1648
1649 ecx_debug.pop ('IGS_DA_TRNS_IMP.GET_ADV_STND_GRANTING_STATUS');
1650 EXCEPTION
1651 WHEN OTHERS
1652 THEN
1653 write_message('ERROR ' || sqlerrm);
1654 END get_adv_stnd_granting_status;
1655
1656 FUNCTION validate_parameters (
1657 p_batch_id IN igs_da_rqst.batch_id%TYPE,
1658 p_person_id IN igs_da_rqst.person_id%TYPE,
1659 p_person_number IN igs_av_lgcy_unt_int.person_number%TYPE,
1660 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
1661 p_unit_cd IN igs_av_lgcy_unt_int.unit_cd%TYPE, --- advstnd unit
1662 p_version_number IN igs_av_lgcy_unt_int.version_number%TYPE
1663 )
1664 RETURN BOOLEAN
1665 IS
1666 /*===========================================================================+
1667 | FUNCTION |
1668 | validate_parameters |
1669 | |
1670 | DESCRIPTION |
1671 | This function checks all the mandatory parameters for the |
1672 | passed record type are not null ,and adds error messages to|
1673 | the stack for all the parameters. |
1674 | |
1675 | MODIFICATION HISTORY |
1676 | jhanda 11-08-2005 Created |
1677 +===========================================================================*/
1678 l_b_return_val BOOLEAN DEFAULT TRUE;
1679 l_s_message_name VARCHAR2 (30);
1680 BEGIN
1681 ecx_debug.push ('IGS_DA_TRNS_IMP.VALIDATE_PARAMETERS');
1682 write_message ('Inside validate_parameters');
1683
1684 IF p_person_number IS NULL
1685 THEN
1686 l_s_message_name := 'IGS_EN_PER_NUM_NULL';
1687 l_b_return_val := FALSE;
1688 fnd_message.set_name ('IGS', l_s_message_name);
1689 fnd_msg_pub.ADD;
1690 notify_error (p_batch_id,
1691 p_person_id,
1692 p_program_cd,
1693 fnd_message.get_string ('IGS', 'IGS_EN_PER_NUM_NULL')
1694 );
1695 END IF;
1696
1697 IF p_program_cd IS NULL
1698 THEN
1699 l_s_message_name := 'IGS_EN_PRGM_CD_NULL';
1700 l_b_return_val := FALSE;
1701 fnd_message.set_name ('IGS', l_s_message_name);
1702 fnd_msg_pub.ADD;
1703 notify_error (p_batch_id,
1704 p_person_id,
1705 p_program_cd,
1706 fnd_message.get_string ('IGS', 'IGS_EN_PRGM_CD_NULL')
1707 );
1708 END IF;
1709
1710 IF p_unit_cd IS NULL
1711 THEN
1712 l_s_message_name := 'IGS_AV_UNIT_CD_NULL';
1713 l_b_return_val := FALSE;
1714 fnd_message.set_name ('IGS', l_s_message_name);
1715 fnd_msg_pub.ADD;
1716 notify_error (p_batch_id,
1717 p_person_id,
1718 p_program_cd,
1719 fnd_message.get_string ('IGS', 'IGS_AV_UNIT_CD_NULL')
1720 );
1721 END IF;
1722
1723 IF p_version_number IS NULL
1724 THEN
1725 l_s_message_name := 'IGS_AV_UNIT_VER_NULL';
1726 l_b_return_val := FALSE;
1727 fnd_message.set_name ('IGS', l_s_message_name);
1728 fnd_msg_pub.ADD;
1729 notify_error (p_batch_id,
1730 p_person_id,
1731 p_program_cd,
1732 fnd_message.get_string ('IGS',
1733 'IGS_DA_TGT_UNT_NOT_EXIST'
1734 )
1735 );
1736 END IF;
1737
1738 write_message ('Comming Out Of validate_parameters' || l_s_message_name);
1739 ecx_debug.pop ('IGS_DA_TRNS_IMP.VALIDATE_PARAMETERS');
1740 RETURN l_b_return_val;
1741 EXCEPTION
1742 WHEN OTHERS
1743 THEN
1744 write_message('ERROR ' || sqlerrm);
1745 END validate_parameters;
1746
1747 FUNCTION derive_unit_data (
1748 p_batch_id IN igs_da_rqst.batch_id%TYPE,
1749 p_person_number IN igs_av_lgcy_unt_int.person_number%TYPE,
1750 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
1751 p_unit_cd IN igs_av_lgcy_unt_int.unit_cd%TYPE, --- advstnd unit
1752 p_version_number IN igs_av_lgcy_unt_int.version_number%TYPE,
1753 p_institution_cd IN igs_av_lgcy_unt_int.institution_cd%TYPE,
1754 p_load_cal_alt_code IN igs_av_lgcy_unt_int.load_cal_alt_code%TYPE,
1755 p_avstnd_grade IN igs_av_lgcy_unt_int.grade%TYPE,
1756 p_achievable_credit_points IN OUT NOCOPY igs_av_lgcy_unt_int.achievable_credit_points%TYPE,
1757 p_person_id IN OUT NOCOPY igs_pe_person.person_id%TYPE,
1758 p_s_adv_stnd_type IN OUT NOCOPY igs_av_stnd_unit_all.s_adv_stnd_type%TYPE,
1759 p_cal_type IN OUT NOCOPY igs_ca_inst.cal_type%TYPE,
1760 p_seq_number IN OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
1761 p_auth_pers_id IN OUT NOCOPY igs_pe_person.person_id%TYPE,
1762 p_as_version_number IN OUT NOCOPY igs_en_stdnt_ps_att.version_number%TYPE
1763 )
1764 RETURN BOOLEAN
1765 IS
1766 /*===========================================================================+
1767 | FUNCTION |
1768 | derive_unit_data |
1769 | |
1770 | DESCRIPTION |
1771 | This function derives advanced standing unit level data |
1772 | |
1773 | MODIFICATION HISTORY |
1774 | jhanda 11-08-2005 Created |
1775 +===========================================================================*/
1776 l_n_rec_count NUMBER := 0;
1777
1778 CURSOR c_credit_points (
1779 cp_unit_cd igs_av_lgcy_unt_int.unit_cd%TYPE,
1780 cp_version_number igs_av_lgcy_unt_int.version_number%TYPE
1781 )
1782 IS
1783 SELECT NVL (achievable_credit_points,
1784 enrolled_credit_points
1785 ) credit_points
1786 FROM igs_ps_unit_ver
1787 WHERE unit_cd = cp_unit_cd AND version_number = cp_version_number;
1788
1789 l_count NUMBER := 0;
1790 l_start_dt igs_ad_term_details.start_date%TYPE;
1791 l_end_dt igs_ad_term_details.end_date%TYPE;
1792 l_return_status VARCHAR2 (1000);
1793 BEGIN
1794 ecx_debug.push ('IGS_DA_TRNS_IMP.DERIVE_UNIT_DATA');
1795 p_s_adv_stnd_type := 'UNIT'; -- initialise
1796 p_person_id := igs_ge_gen_003.get_person_id (p_person_number);
1797 write_message ('Got person ID as ' || p_person_id);
1798
1799 IF p_person_id IS NULL
1800 THEN
1801 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
1802 fnd_msg_pub.ADD;
1803 RETURN FALSE;
1804 END IF;
1805
1806 IF p_load_cal_alt_code IS NULL
1807 THEN
1808 fnd_message.set_name ('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
1809 fnd_msg_pub.ADD;
1810 notify_error (p_batch_id,
1811 p_person_id,
1812 p_program_cd,
1813 fnd_message.get_string ('IGS',
1814 'IGS_AV_INVALID_CAL_ALT_CODE'
1815 )
1816 );
1817 RETURN FALSE;
1818 END IF;
1819
1820 write_message ( 'Calling IGS_GE_GEN_003.get_calendar_instance '
1821 || p_cal_type
1822 || p_load_cal_alt_code
1823 );
1824 igs_ge_gen_003.get_calendar_instance (p_alternate_cd => p_load_cal_alt_code,
1825 p_s_cal_category => '''LOAD''',
1826 p_cal_type => p_cal_type,
1827 p_ci_sequence_number => p_seq_number,
1828 p_start_dt => l_start_dt,
1829 p_end_dt => l_end_dt,
1830 p_return_status => l_return_status
1831 );
1832 write_message ( 'Got p_cal_type as '
1833 || p_cal_type
1834 || ' and p_seq_number as'
1835 || p_seq_number
1836 );
1837
1838 -- IF 0 or more load calendars are found
1839 IF p_seq_number IS NULL OR p_cal_type IS NULL
1840 THEN
1841 fnd_message.set_name ('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
1842 fnd_msg_pub.ADD;
1843 notify_error (p_batch_id,
1844 p_person_id,
1845 p_program_cd,
1846 fnd_message.get_string ('IGS',
1847 'IGS_AV_INVALID_CAL_ALT_CODE'
1848 )
1849 );
1850 RETURN FALSE;
1851 END IF;
1852
1853 write_message ('Got p_auth_pers_id as ' || p_auth_pers_id);
1854 -- Get the program version number
1855 p_as_version_number :=
1856 igs_ge_gen_003.get_program_version (p_person_id => p_person_id,
1857 p_program_cd => p_program_cd
1858 );
1859 write_message ('Got p_as_version_number as ' || p_as_version_number);
1860
1861 -- Default p_achievable_credit_points
1862 IF p_achievable_credit_points IS NULL
1863 THEN
1864 OPEN c_credit_points (p_unit_cd, p_version_number);
1865 FETCH c_credit_points INTO p_achievable_credit_points;
1866 CLOSE c_credit_points;
1867 END IF;
1868
1869 write_message ( 'Got p_achievable_credit_points as '
1870 || p_achievable_credit_points
1871 );
1872 ecx_debug.pop ('IGS_DA_TRNS_IMP.DERIVE_UNIT_DATA');
1873 RETURN TRUE;
1874 EXCEPTION
1875 WHEN OTHERS
1876 THEN
1877 notify_error (p_batch_id,
1878 p_person_id,
1879 p_program_cd,
1880 'Error has occurred.See log for Details'
1881 );
1882
1883 write_message('ERROR ' || sqlerrm);
1884
1885 END derive_unit_data;
1886
1887 FUNCTION validate_adv_std_db_cons (
1888 p_batch_id IN igs_da_rqst.batch_id%TYPE,
1889 p_person_id IN igs_pe_person.person_id%TYPE,
1890 p_person_number IN igs_av_lgcy_unt_int.person_number%TYPE,
1891 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
1892 p_unit_cd IN igs_av_lgcy_unt_int.unit_cd%TYPE, --- advstnd unit
1893 p_version_number IN igs_av_lgcy_unt_int.version_number%TYPE,
1894 p_load_cal_alt_code IN igs_av_lgcy_unt_int.load_cal_alt_code%TYPE,
1895 p_achievable_credit_points IN igs_av_lgcy_unt_int.achievable_credit_points%TYPE
1896 )
1897 RETURN BOOLEAN
1898 IS
1899 /*===========================================================================+
1900 | FUNCTION |
1901 | validate_adv_std_db_cons |
1902 | |
1903 | DESCRIPTION |
1904 | |
1905 | MODIFICATION HISTORY |
1906 | jhanda 11-08-2005 Created |
1907 +===========================================================================*/
1908 x_return_status BOOLEAN := TRUE;
1909 BEGIN
1910 ecx_debug.push ('IGS_DA_TRNS_IMP.VALIDATE_ADV_STD_DB_CONS');
1911 x_return_status := TRUE;
1912 write_message ('Before igs_ps_ver_pkg.get_pk_for_validation ');
1913
1914 IF NOT igs_ps_ver_pkg.get_pk_for_validation (x_course_cd => p_program_cd,
1915 x_version_number => p_version_number
1916 )
1917 THEN
1918 fnd_message.set_name ('IGS', 'IGS_AV_PRG_CD_NOT_EXISTS');
1919 fnd_msg_pub.ADD;
1920 x_return_status := FALSE;
1921 notify_error (p_batch_id,
1922 p_person_id,
1923 p_program_cd,
1924 fnd_message.get_string ('IGS',
1925 'IGS_AV_PRG_CD_NOT_EXISTS'
1926 )
1927 );
1928 END IF;
1929
1930 write_message ('Inside validate_adv_std_db_cons Got x_return_status as ');
1931 ecx_debug.pop ('IGS_DA_TRNS_IMP.VALIDATE_ADV_STD_DB_CONS');
1932 RETURN x_return_status;
1933 EXCEPTION
1934 WHEN OTHERS
1935 THEN
1936 notify_error (p_batch_id,
1937 p_person_id,
1938 p_program_cd,
1939 'Error has occurred.See log for Details.'
1940 );
1941
1942 write_message('ERROR ' || sqlerrm);
1943
1944 END validate_adv_std_db_cons;
1945
1946 FUNCTION validate_adv_stnd (
1947 p_batch_id IN igs_da_rqst.batch_id%TYPE,
1948 p_person_id IN igs_pe_person.person_id%TYPE,
1949 p_person_number IN igs_av_lgcy_unt_int.person_number%TYPE,
1950 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
1951 p_unit_cd IN igs_av_lgcy_unt_int.unit_cd%TYPE, --- advstnd unit
1952 p_version_number IN igs_av_lgcy_unt_int.version_number%TYPE,
1953 p_prev_institution_code IN igs_ad_acad_history_v.institution_code%TYPE
1954 )
1955 RETURN BOOLEAN
1956 IS
1957 /*===========================================================================+
1958 | FUNCTION |
1959 | validate_adv_stnd |
1960 | |
1961 | DESCRIPTION |
1962 | |
1963 | MODIFICATION HISTORY |
1964 | jhanda 11-08-2005 Created |
1965 | swaghmar 09-11-2005 Bug# 4706134 - Modified the query for |
1966 | cursor c_validate_inst |
1967 +===========================================================================*/
1968 x_return_status BOOLEAN;
1969 BEGIN
1970 ecx_debug.push ('IGS_DA_TRNS_IMP.VALIDATE_ADV_STND');
1971 x_return_status := TRUE;
1972
1973 /*
1974 check whether person is deceased or not
1975 */
1976 DECLARE
1977 CURSOR c_ind (cp_party_id igs_pe_hz_parties.party_id%TYPE)
1978 IS
1979 SELECT deceased_ind
1980 FROM igs_pe_hz_parties
1981 WHERE party_id = cp_party_id;
1982
1983 l_ind igs_pe_hz_parties.deceased_ind%TYPE;
1984 BEGIN
1985 OPEN c_ind (p_person_id);
1986 FETCH c_ind INTO l_ind;
1987 CLOSE c_ind;
1988
1989 IF UPPER (l_ind) = 'Y'
1990 THEN
1991 fnd_message.set_name ('IGS', 'IGS_AV_PERSON_DECEASED');
1992 fnd_msg_pub.ADD;
1993 x_return_status := FALSE;
1994 notify_error (p_batch_id,
1995 p_person_id,
1996 p_program_cd,
1997 fnd_message.get_string ('IGS',
1998 'IGS_AV_PERSON_DECEASED'
1999 )
2000 );
2001 END IF;
2002
2003 write_message ('l_ind :' || l_ind);
2004 END;
2005
2006 /*
2007 check whether exemtion_inst_cd is valid or not
2008 */
2009 DECLARE
2010 CURSOR c_validate_inst (
2011 cp_exemption_institution_cd igs_ad_acad_history_v.institution_code%TYPE
2012 )
2013 IS
2014 SELECT hp.party_number tca_party_number,
2015 ihp.oss_org_unit_cd exemption_institution_cd, hp.party_name,
2016 ihp.oi_institution_status, 'INSTITUTION CODE' SOURCE, hp.created_by,
2017 hp.creation_date, hp.last_updated_by, hp.last_update_date,
2018 hp.last_update_login
2019 FROM hz_parties hp, igs_pe_hz_parties ihp
2020 WHERE hp.party_id = ihp.party_id
2021 AND ihp.inst_org_ind = 'I'
2022 AND ihp.oi_institution_status = 'ACTIVE'
2023 AND ihp.oss_org_unit_cd = cp_exemption_institution_cd;
2024
2025 l_validate_inst c_validate_inst%ROWTYPE;
2026 BEGIN
2027 OPEN c_validate_inst (p_prev_institution_code);
2028 FETCH c_validate_inst INTO l_validate_inst;
2029
2030 IF c_validate_inst%NOTFOUND
2031 THEN
2032 fnd_message.set_name ('IGS', 'IGS_AV_STND_EXMPT_INVALID');
2033 fnd_msg_pub.ADD;
2034 x_return_status := FALSE;
2035 notify_error (p_batch_id,
2036 p_person_id,
2037 p_program_cd,
2038 fnd_message.get_string ('IGS',
2039 'IGS_AV_STND_EXMPT_INVALID'
2040 )
2041 );
2042 END IF;
2043
2044 CLOSE c_validate_inst;
2045 write_message ('Verified exemption_inst_cd');
2046 END;
2047
2048 /*
2049 check whether program_cd is valid or not
2050 */
2051 DECLARE
2052 l_message_name VARCHAR2 (2000);
2053 BEGIN
2054 IF NOT igs_av_val_as.advp_val_as_crs (p_person_id => p_person_id,
2055 p_course_cd => p_program_cd,
2056 p_version_number => p_version_number,
2057 p_message_name => l_message_name
2058 )
2059 THEN
2060 fnd_message.set_name ('IGS', 'IGS_HE_EXT_SPA_DTL_NOT_FOUND');
2061 fnd_msg_pub.ADD;
2062 x_return_status := FALSE;
2063 notify_error (p_batch_id,
2064 p_person_id,
2065 p_program_cd,
2066 fnd_message.get_string ('IGS',
2067 'IGS_HE_EXT_SPA_DTL_NOT_FOUND'
2068 )
2069 );
2070 END IF;
2071 END;
2072
2073 /*
2074 validation for exemption credit points
2075 */
2076 DECLARE
2077 CURSOR c_local_inst_ind (
2078 cp_ins_cd igs_or_institution.institution_cd%TYPE
2079 )
2080 IS
2081 SELECT ins.local_institution_ind
2082 FROM igs_or_institution ins
2083 WHERE ins.institution_cd = cp_ins_cd;
2084
2085 CURSOR cur_program_exempt_totals (
2086 cp_course_cd igs_ps_ver.course_cd%TYPE,
2087 cp_version_number igs_ps_ver.version_number%TYPE,
2088 cp_local_ind VARCHAR2
2089 )
2090 IS
2091 SELECT DECODE (cp_local_ind,
2092 'N', NVL (cv.external_adv_stnd_limit, -1),
2093 NVL (cv.internal_adv_stnd_limit, -1)
2094 ) adv_stnd_limit
2095 FROM igs_ps_ver cv
2096 WHERE cv.course_cd = cp_course_cd
2097 AND cv.version_number = cp_version_number;
2098
2099 rec_cur_program_exempt_totals cur_program_exempt_totals%ROWTYPE;
2100 rec_local_inst_ind c_local_inst_ind%ROWTYPE;
2101 l_message_name fnd_new_messages.message_name%TYPE;
2102 BEGIN
2103 OPEN c_local_inst_ind (p_prev_institution_code);
2104 FETCH c_local_inst_ind INTO rec_local_inst_ind;
2105
2106 IF (c_local_inst_ind%NOTFOUND)
2107 THEN
2108 rec_local_inst_ind.local_institution_ind := 'N';
2109 END IF;
2110
2111 CLOSE c_local_inst_ind;
2112
2113 IF (rec_local_inst_ind.local_institution_ind = 'N')
2114 THEN
2115 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_EXT_LMT';
2116 ELSE
2117 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_INT_LMT';
2118 END IF;
2119 END;
2120
2121 /*
2122 check the course_attempt_status
2123 */
2124 DECLARE
2125 CURSOR c_exists (
2126 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
2127 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE
2128 )
2129 IS
2130 SELECT 'x'
2131 FROM igs_en_stdnt_ps_att
2132 WHERE person_id = cp_person_id
2133 AND course_cd = cp_course_cd
2134 AND course_attempt_status IN
2135 ('ENROLLED',
2136 'INACTIVE',
2137 'INTERMIT',
2138 'UNCONFIRM',
2139 'DISCONTIN',
2140 'COMPLETED'
2141 );
2142
2143 l_exists VARCHAR2 (1);
2144 BEGIN
2145 OPEN c_exists (p_person_id, p_program_cd);
2146 FETCH c_exists INTO l_exists;
2147
2148 IF c_exists%NOTFOUND
2149 THEN
2150 fnd_message.set_name ('IGS', 'IGS_AV_PRG_ATTMPT_INVALID');
2151 fnd_msg_pub.ADD;
2152 x_return_status := FALSE;
2153 notify_error (p_batch_id,
2154 p_person_id,
2155 p_program_cd,
2156 fnd_message.get_string ('IGS',
2157 'IGS_AV_PRG_ATTMPT_INVALID'
2158 )
2159 );
2160 END IF;
2161
2162 CLOSE c_exists;
2163 END;
2164
2165 ecx_debug.pop ('IGS_DA_TRNS_IMP.VALIDATE_ADV_STND');
2166 RETURN x_return_status;
2167 EXCEPTION
2168 WHEN OTHERS
2169 THEN
2170 notify_error (p_batch_id,
2171 p_person_id,
2172 p_program_cd,
2173 'Error has occurred.See log for Details.'
2174 );
2175
2176 write_message('ERROR ' || sqlerrm);
2177
2178 END validate_adv_stnd;
2179
2180 FUNCTION validate_std_unt_db_cons (
2181 p_batch_id IN igs_da_rqst.batch_id%TYPE,
2182 p_person_number IN igs_av_lgcy_unt_int.person_number%TYPE,
2183 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
2184 p_unit_cd IN igs_av_lgcy_unt_int.unit_cd%TYPE, --- advstnd unit
2185 p_version_number IN igs_av_lgcy_unt_int.version_number%TYPE,
2186 p_institution_cd IN igs_av_lgcy_unt_int.institution_cd%TYPE,
2187 p_person_id IN igs_pe_person.person_id%TYPE,
2188 p_auth_pers_id IN igs_pe_person.person_id%TYPE,
2189 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%TYPE,
2190 p_as_version_number IN igs_en_stdnt_ps_att.version_number%TYPE,
2191 p_prev_institution_code IN igs_ad_acad_history_v.institution_code%TYPE
2192 )
2193 RETURN BOOLEAN
2194 IS
2195 /*===========================================================================+
2196 | FUNCTION |
2197 | validate_std_unt_db_cons |
2198 | |
2199 | DESCRIPTION |
2200 | This function performs all the data integrity validation |
2201 | before entering into the table IGS_AV_STND_UNIT_ ALL and |
2202 | keeps adding error message to stack as an when it encounters.| |
2203 | MODIFICATION HISTORY |
2204 | jhanda 11-08-2005 Created |
2205 +===========================================================================*/
2206 x_return_status BOOLEAN := TRUE;
2207 l_c_tmp_msg VARCHAR2 (30);
2208 BEGIN
2209 -- Foreign Key with Table IGS_AV_ADV_STANDING_PKG
2210 ecx_debug.push ('IGS_DA_TRNS_IMP.VALIDATE_STD_UNT_DB_CONS');
2211 write_message ( 'p_person_id='
2212 || p_person_id
2213 || ' p_program_cd='
2214 || p_program_cd
2215 || ' p_as_version_number='
2216 || p_as_version_number
2217 || ' p_prev_institution_code='
2218 || p_prev_institution_code
2219 );
2220
2221 IF NOT igs_av_adv_standing_pkg.get_pk_for_validation (x_person_id => p_person_id,
2222 x_course_cd => p_program_cd,
2223 x_version_number => p_as_version_number,
2224 x_exemption_institution_cd => p_prev_institution_code
2225 )
2226 THEN
2227 fnd_message.set_name ('IGS', 'IGS_AV_NO_ADV_STND_DET_EXIST');
2228 fnd_msg_pub.ADD;
2229 x_return_status := FALSE;
2230 write_message ('validate_std_unt_db_cons IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION '
2231 );
2232 notify_error (p_batch_id,
2233 p_person_id,
2234 p_program_cd,
2235 fnd_message.get_string ('IGS',
2236 'IGS_AV_NO_ADV_STND_DET_EXIST'
2237 )
2238 );
2239 END IF;
2240
2241 write_message ('p_auth_pers_id=' || p_auth_pers_id);
2242
2243 -- Foreign Key with AUTHORIZING_PERSON_ID exists in table IGS_PE_PERSON
2244 IF p_auth_pers_id IS NULL
2245 THEN
2246 fnd_message.set_name ('IGS', 'IGS_AV_INVALID_PERS_AUTH_NUM');
2247 fnd_msg_pub.ADD;
2248 x_return_status := FALSE;
2249 write_message ('validate_std_unt_db_cons p_auth_pers_id ');
2250 notify_error (p_batch_id,
2251 p_person_id,
2252 p_program_cd,
2253 fnd_message.get_string ('IGS',
2254 'IGS_AV_INVALID_PERS_AUTH_NUM'
2255 )
2256 );
2257 END IF;
2258
2259 write_message ( 'igs_ps_unit_ver_pkg.get_pk_for_validation'
2260 || p_unit_cd
2261 || ' '
2262 || p_version_number
2263 );
2264
2265 -- Foreign Key with Table IGS_PS_UNIT_VER
2266 IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (x_unit_cd => p_unit_cd,
2267 x_version_number => p_version_number
2268 )
2269 THEN
2270 fnd_message.set_name ('IGS', 'IGS_AV_ADV_STUNT_UNIT_EXISTS');
2271 fnd_msg_pub.ADD;
2272 x_return_status := FALSE;
2273 write_message ('validate_std_unt_db_cons IGS_PS_UNIT_VER_PKG.GET_PK_FOR_VALIDATION '
2274 );
2275 notify_error (p_batch_id,
2276 p_person_id,
2277 p_program_cd,
2278 fnd_message.get_string ('IGS',
2279 'IGS_AV_ADV_STUNT_UNIT_EXISTS'
2280 )
2281 );
2282 END IF;
2283
2284 -- Check that if institution_cd is NOT NULL and unit_details_id is NULL
2285 IF p_institution_cd IS NOT NULL AND p_unit_details_id IS NULL
2286 THEN
2287 fnd_message.set_name ('IGS', 'IGS_AV_STUT_INST_UID_NOT_NULL');
2288 fnd_msg_pub.ADD;
2289 x_return_status := FALSE;
2290 write_message ('validate_std_unt_db_cons p_prev_institution_code ');
2291 notify_error (p_batch_id,
2292 p_person_id,
2293 p_program_cd,
2294 fnd_message.get_string ('IGS',
2295 'IGS_AV_STUT_INST_UID_NOT_NULL'
2296 )
2297 );
2298 END IF;
2299
2300 ecx_debug.pop ('IGS_DA_TRNS_IMP.VALIDATE_STD_UNT_DB_CONS');
2301 RETURN x_return_status;
2302 EXCEPTION
2303 WHEN OTHERS
2304 THEN
2305 notify_error (p_batch_id,
2306 p_person_id,
2307 p_program_cd,
2308 'Error has occurred.See log for Details.'
2309 );
2310
2311 write_message('ERROR ' || sqlerrm);
2312
2313 END validate_std_unt_db_cons;
2314
2315 FUNCTION validate_unit (
2316 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
2317 p_unit_cd IN igs_av_lgcy_unt_int.unit_cd%TYPE, --- advstnd unit
2318 p_version_number IN igs_av_lgcy_unt_int.version_number%TYPE,
2319 p_achievable_credit_points IN igs_av_lgcy_unt_int.achievable_credit_points%TYPE,
2320 p_person_id IN igs_pe_person.person_id%TYPE,
2321 p_auth_pers_id IN igs_pe_person.person_id%TYPE,
2322 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%TYPE,
2323 p_as_version_number IN igs_en_stdnt_ps_att.version_number%TYPE,
2324 p_batch_id IN igs_da_rqst.batch_id%TYPE,
2325 p_prev_institution_code IN igs_ad_acad_history_v.institution_code%TYPE
2326 )
2327 RETURN BOOLEAN
2328 IS
2329 /*===========================================================================+
2330 | FUNCTION |
2331 | validate_unit |
2332 | |
2333 | DESCRIPTION |
2334 | This function performs all the business validations before |
2335 | inserting a record into the table IGS_AV_STND_UNIT_ALL and|
2336 | keeps adding error message to stack as an when it encounters.|
2337 | |
2338 | MODIFICATION HISTORY |
2339 | jhanda 11-08-2005 Created |
2340 +===========================================================================*/
2341 x_return_status BOOLEAN := TRUE;
2342 l_total_exmptn_approved igs_av_adv_standing_all.total_exmptn_approved%TYPE;
2343 l_total_exmptn_granted igs_av_adv_standing_all.total_exmptn_granted%TYPE;
2344 l_total_exmptn_perc_grntd igs_av_adv_standing_all.total_exmptn_perc_grntd%TYPE;
2345 l_message_name VARCHAR2 (30);
2346 l_grant_status igs_av_stnd_unit_all.s_adv_stnd_granting_status%TYPE;
2347 BEGIN
2348 ecx_debug.push ('IGS_DA_TRNS_IMP.VALIDATE_UNIT');
2349
2350 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
2351 THEN
2352 l_grant_status := 'GRANTED';
2353 ELSE
2354 l_grant_status := get_adv_stnd_granting_status (p_batch_id);
2355 END IF;
2356
2357 IF NOT igs_av_val_asu.advp_val_as_totals (p_person_id => p_person_id,
2358 p_course_cd => p_program_cd,
2359 p_version_number => p_as_version_number,
2360 p_include_approved => TRUE,
2361 p_asu_unit_cd => p_unit_cd,
2362 p_asu_version_number => p_version_number,
2363 p_asu_advstnd_granting_status => l_grant_status,
2364 p_asul_unit_level => NULL,
2365 p_asul_exmptn_institution_cd => p_prev_institution_code,
2366 p_asul_advstnd_granting_status => l_grant_status,
2367 p_total_exmptn_approved => l_total_exmptn_approved,
2368 p_total_exmptn_granted => l_total_exmptn_granted,
2369 p_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd,
2370 p_message_name => l_message_name,
2371 p_unit_details_id => p_unit_details_id,
2372 p_tst_rslt_dtls_id => NULL
2373 )
2374 THEN
2375 fnd_message.set_name ('IGS', l_message_name);
2376 fnd_msg_pub.ADD;
2377 x_return_status := FALSE;
2378 write_message ('validate_unit IGS_AV_VAL_ASU.ADVP_VAL_AS_TOTALS ');
2379 notify_error (p_batch_id,
2380 p_person_id,
2381 p_program_cd,
2382 fnd_message.get_string ('IGS', l_message_name)
2383 );
2384 END IF;
2385
2386 -- Check for person hold
2387 IF NOT igs_en_val_encmb.enrp_val_excld_prsn (p_person_id => p_person_id,
2388 p_course_cd => p_program_cd,
2389 p_effective_dt => SYSDATE,
2390 p_message_name => l_message_name
2391 )
2392 THEN
2393 fnd_message.set_name ('IGS', l_message_name);
2394 fnd_msg_pub.ADD;
2395 x_return_status := FALSE;
2396 write_message ('validate_unit IGS_EN_VAL_ENCMB.ENRP_VAL_EXCLD_PRSN ');
2397 notify_error (p_batch_id,
2398 p_person_id,
2399 p_program_cd,
2400 fnd_message.get_string ('IGS', l_message_name)
2401 );
2402 END IF;
2403
2404 write_message ( 'igs_ad_val_acai.genp_val_staff_prsn p_auth_pers_id='
2405 || p_auth_pers_id
2406 );
2407
2408 IF NOT igs_ad_val_acai.genp_val_staff_prsn (p_person_id => p_auth_pers_id,
2409 p_message_name => l_message_name
2410 )
2411 THEN
2412 fnd_message.set_name ('IGS', 'IGS_GE_NOT_STAFF_MEMBER');
2413 fnd_msg_pub.ADD;
2414 --todo change this to false if staff validation required
2415 --x_return_status := false;
2416 write_message ('validate_unit IGS_GE_NOT_STAFF_MEMBER ');
2417 notify_error (p_batch_id,
2418 p_person_id,
2419 p_program_cd,
2420 fnd_message.get_string ('IGS',
2421 'IGS_GE_NOT_STAFF_MEMBER'
2422 )
2423 );
2424 END IF;
2425
2426 IF p_achievable_credit_points IS NULL
2427 THEN
2428 fnd_message.set_name ('IGS', 'IGS_AV_CRD_PER_CANNOT_BE_NULL');
2429 fnd_msg_pub.ADD;
2430 x_return_status := FALSE;
2431 write_message ('validate_unit IGS_AV_CRD_PER_CANNOT_BE_NULL ');
2432 write_message ('validate_unit IGS_EN_VAL_ENCMB.ENRP_VAL_EXCLD_PRSN ');
2433 notify_error (p_batch_id,
2434 p_person_id,
2435 p_program_cd,
2436 fnd_message.get_string ('IGS',
2437 'IGS_AV_CRD_PER_CANNOT_BE_NULL'
2438 )
2439 );
2440 END IF;
2441
2442 /*
2443 check the course_attempt_status
2444 */
2445 DECLARE
2446 CURSOR c_exists (
2447 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
2448 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE
2449 )
2450 IS
2451 SELECT 'x'
2452 FROM igs_en_stdnt_ps_att
2453 WHERE person_id = cp_person_id
2454 AND course_cd = cp_course_cd
2455 AND course_attempt_status IN
2456 ('ENROLLED',
2457 'INACTIVE',
2458 'INTERMIT',
2459 'UNCONFIRM',
2460 'DISCONTIN',
2461 'COMPLETED'
2462 );
2463
2464 l_exists VARCHAR2 (1);
2465 BEGIN
2466 OPEN c_exists (p_person_id, p_program_cd);
2467 FETCH c_exists INTO l_exists;
2468
2469 IF c_exists%NOTFOUND
2470 THEN
2471 fnd_message.set_name ('IGS', 'IGS_AV_PRG_ATTMPT_INVALID');
2472 fnd_msg_pub.ADD;
2473 write_message ('validate_unit IGS_AV_PRG_ATTMPT_INVALID ');
2474 x_return_status := FALSE;
2475 notify_error (p_batch_id,
2476 p_person_id,
2477 p_program_cd,
2478 fnd_message.get_string ('IGS',
2479 'IGS_AV_PRG_ATTMPT_INVALID'
2480 )
2481 );
2482 END IF;
2483
2484 CLOSE c_exists;
2485 END;
2486
2487 ecx_debug.pop ('IGS_DA_TRNS_IMP.VALIDATE_UNIT');
2488 RETURN x_return_status;
2489 EXCEPTION
2490 WHEN OTHERS
2491 THEN
2492 notify_error (p_batch_id,
2493 p_person_id,
2494 p_program_cd,
2495 'Error has occurred.See log for Details'
2496 );
2497
2498 write_message('ERROR ' || sqlerrm);
2499
2500 END validate_unit;
2501
2502 FUNCTION create_post_unit (
2503 p_person_id IN igs_pe_person.person_id%TYPE,
2504 p_course_version IN igs_ps_ver.version_number%TYPE,
2505 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%TYPE,
2506 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
2507 p_unit_cd IN igs_av_lgcy_unt_int.unit_cd%TYPE, --- advstnd unit
2508 p_version_number IN igs_av_lgcy_unt_int.version_number%TYPE,
2509 p_batch_id igs_da_rqst.batch_id%TYPE,
2510 p_prev_institution_code IN igs_ad_acad_history_v.institution_code%TYPE
2511 )
2512 RETURN BOOLEAN
2513 IS
2514 /*===========================================================================+
2515 | FUNCTION |
2516 | create_post_unit |
2517 | |
2518 | DESCRIPTION |
2519 | |
2520 | MODIFICATION HISTORY |
2521 | jhanda 11-08-2005 Created |
2522 +===========================================================================*/
2523 CURSOR c_adv_stnd
2524 IS
2525 SELECT ROWID
2526 FROM igs_av_adv_standing_all
2527 WHERE person_id = p_person_id
2528 AND course_cd = p_program_cd
2529 AND version_number = p_course_version
2530 AND exemption_institution_cd = p_prev_institution_code;
2531
2532 x_return_status BOOLEAN := TRUE;
2533 l_message VARCHAR2 (2000);
2534 l_total_exmptn_approved igs_av_adv_standing_all.total_exmptn_approved%TYPE;
2535 l_total_exmptn_granted igs_av_adv_standing_all.total_exmptn_granted%TYPE;
2536 l_total_exmptn_perc_grntd igs_av_adv_standing_all.total_exmptn_perc_grntd%TYPE;
2537 l_adv_stnd c_adv_stnd%ROWTYPE;
2538 BEGIN
2539 ecx_debug.push ('IGS_DA_TRNS_IMP.CREATE_POST_UNIT');
2540 x_return_status := TRUE;
2541 write_message ('In create_post_unit');
2542
2543 /*
2544 Validate whether the advanced standing approved / granted has not
2545 exceeded the advanced standing internal or external limits of
2546 the Program version
2547 */
2548 IF NOT igs_av_val_asu.advp_val_as_totals (p_person_id => p_person_id,
2549 p_course_cd => p_program_cd,
2550 p_version_number => p_course_version,
2551 p_include_approved => TRUE,
2552 p_asu_unit_cd => p_unit_cd,
2553 p_asu_version_number => p_version_number,
2554 p_asu_advstnd_granting_status => get_adv_stnd_granting_status (p_batch_id
2555 ),
2556 p_asul_unit_level => NULL,
2557 p_asul_exmptn_institution_cd => p_prev_institution_code,
2558 p_asul_advstnd_granting_status => get_adv_stnd_granting_status (p_batch_id
2559 ),
2560 p_total_exmptn_approved => l_total_exmptn_approved,
2561 p_total_exmptn_granted => l_total_exmptn_granted,
2562 p_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd,
2563 p_message_name => l_message,
2564 p_unit_details_id => p_unit_details_id,
2565 p_tst_rslt_dtls_id => NULL,
2566 p_asu_exmptn_institution_cd => p_prev_institution_code
2567 )
2568 THEN
2569 fnd_message.set_name ('IGS', l_message);
2570 fnd_msg_pub.ADD;
2571 x_return_status := FALSE;
2572 notify_error (p_batch_id,
2573 p_person_id,
2574 p_program_cd,
2575 fnd_message.get_string ('IGS', l_message)
2576 );
2577 ELSE -- function returns TRUE
2578 /*
2579 update IGS_AV_ADV_STANDING_ALL with above obtained values for
2580 total_exmptn_approved, total_exmptn_granted and total_exmptn_perc_grntd
2581 */
2582 OPEN c_adv_stnd;
2583 FETCH c_adv_stnd INTO l_adv_stnd;
2584 igs_av_adv_standing_pkg.update_row (x_rowid => l_adv_stnd.ROWID,
2585 x_person_id => p_person_id,
2586 x_course_cd => p_program_cd,
2587 x_version_number => p_course_version,
2588 x_total_exmptn_approved => l_total_exmptn_approved,
2589 x_total_exmptn_granted => l_total_exmptn_granted,
2590 x_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd,
2591 x_exemption_institution_cd => p_prev_institution_code,
2592 x_mode => 'R'
2593 );
2594 CLOSE c_adv_stnd;
2595 END IF;
2596
2597 write_message ('Out create_post_unit');
2598 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_POST_UNIT');
2599 RETURN x_return_status;
2600 EXCEPTION
2601 WHEN OTHERS
2602 THEN
2603 notify_error (p_batch_id,
2604 p_person_id,
2605 p_program_cd,
2606 'Error has occurred.See log for Details'
2607 );
2608 write_message('ERROR ' || sqlerrm);
2609
2610 END create_post_unit;
2611
2612 PROCEDURE create_adv_stnd_unit (
2613 p_batch_id IN igs_da_rqst.batch_id%TYPE,
2614 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%TYPE,
2615 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
2616 p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
2617 p_program_cd IN igs_av_lgcy_unt_int.program_cd%TYPE,
2618 p_load_cal_alt_code IN igs_av_lgcy_unt_int.load_cal_alt_code%TYPE,
2619 p_avstnd_grade IN igs_av_lgcy_unt_int.grade%TYPE,
2620 p_achievable_credit_points IN igs_av_lgcy_unt_int.achievable_credit_points%TYPE,
2621 p_target_course_subject IN VARCHAR2,
2622 p_target_course_num IN VARCHAR2,
2623 p_inst_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
2624 p_inst_id_code_type IN igs_pe_alt_pers_id.api_person_id%TYPE
2625 )
2626 IS
2627 /*===========================================================================+
2628 | PROCEDURE |
2629 | create_adv_stnd_unit |
2630 | |
2631 | DESCRIPTION |
2632 | Creates advanced standing unit |
2633 | |
2634 | MODIFICATION HISTORY |
2635 | jhanda 11-08-2005 Created |
2636 +===========================================================================*/
2637 l_api_name CONSTANT VARCHAR2 (30) := 'create_adv_stnd_unit';
2638 l_api_version CONSTANT NUMBER := 1.0;
2639 l_ret_status BOOLEAN;
2640 l_b_av_stnd_alt_unit_pk_exist BOOLEAN := TRUE;
2641 l_person_id igs_pe_person.person_id%TYPE;
2642 l_s_adv_stnd_type igs_av_stnd_unit_all.s_adv_stnd_type%TYPE;
2643 l_cal_type igs_ca_inst.cal_type%TYPE;
2644 l_seq_number igs_ca_inst.sequence_number%TYPE;
2645 l_auth_pers_id igs_pe_person.person_id%TYPE;
2646 l_as_version_number igs_en_stdnt_ps_att.version_number%TYPE;
2647 l_av_stnd_unit_lvl_id igs_av_stnd_unit_all.av_stnd_unit_id%TYPE;
2648 l_request_id igs_av_stnd_unit_all.request_id%TYPE;
2649 l_program_id igs_av_stnd_unit_all.program_id%TYPE;
2650 l_program_application_id igs_av_stnd_unit_all.program_application_id%TYPE;
2651 l_program_update_date igs_av_stnd_unit_all.program_update_date%TYPE;
2652 duplicate_record_exists EXCEPTION;
2653 l_granted_dt igs_av_stnd_unit_all.granted_dt%TYPE
2654 := NULL;
2655 l_unit_cd igs_av_lgcy_unt_int.unit_cd%TYPE; --- advstnd unit
2656 l_version_number igs_av_lgcy_unt_int.version_number%TYPE;
2657 l_person_number igs_av_lgcy_unt_int.person_number%TYPE;
2658 l_return_status VARCHAR2 (30);
2659 l_msg_count NUMBER;
2660 l_msg_data VARCHAR2 (200);
2661 l_prev_institution_code igs_ad_acad_history_v.institution_code%TYPE;
2662 l_achievable_credit_points igs_av_lgcy_unt_int.achievable_credit_points%TYPE;
2663 l_institution_cd igs_av_lgcy_unt_int.institution_cd%TYPE;
2664 l_dmmy_rowid ROWID;
2665
2666 CURSOR c_unit_ver
2667 IS
2668 SELECT version_number
2669 FROM igs_ps_unit_ver_all OUTER
2670 WHERE unit_cd = l_unit_cd
2671 AND unit_status = 'ACTIVE'
2672 AND version_number =
2673 (SELECT MAX (version_number)
2674 FROM igs_ps_unit_ver_all inn
2675 WHERE OUTER.unit_cd = inn.unit_cd
2676 AND inn.unit_status = 'ACTIVE');
2677
2678 CURSOR c_present_inst
2679 IS
2680 SELECT adv_stnd_basis_inst
2681 FROM igs_av_stnd_conf;
2682
2683 CURSOR c_requestor
2684 IS
2685 SELECT dr.requestor_id
2686 FROM igs_da_rqst dr, fnd_user fdu
2687 WHERE dr.batch_id = p_batch_id
2688 AND dr.requestor_id = fdu.person_party_id;
2689
2690 CURSOR c_adv_stnd_unt (
2691 cp_person_id NUMBER,
2692 cp_exemption_institution_cd VARCHAR2,
2693 cp_unit_details_id NUMBER,
2694 cp_unit_cd VARCHAR2,
2695 cp_as_course_cd VARCHAR2,
2696 cp_as_version_number NUMBER,
2697 cp_version_number NUMBER,
2698 cp_s_adv_stnd_type VARCHAR2
2699 )
2700 IS
2701 SELECT unt.ROWID, unt.*
2702 FROM igs_av_stnd_unit_all unt
2703 WHERE person_id = cp_person_id
2704 AND exemption_institution_cd = cp_exemption_institution_cd
2705 AND unit_details_id = cp_unit_details_id
2706 AND unit_cd = cp_unit_cd
2707 AND as_course_cd = cp_as_course_cd
2708 AND as_version_number = cp_as_version_number
2709 AND version_number = cp_version_number
2710 AND s_adv_stnd_type = cp_s_adv_stnd_type
2711 FOR UPDATE NOWAIT;
2712
2713
2714 CURSOR c_requestor_id
2715 IS
2716 SELECT hz_parties.party_id
2717 FROM hz_parties, fnd_user
2718 WHERE fnd_user.customer_id = hz_parties.party_id
2719 AND fnd_user.user_id = fnd_profile.VALUE ('IGS_DA_WF_ADMIN');
2720
2721
2722
2723 l_adv_stnd_unt c_adv_stnd_unt%ROWTYPE;
2724 l_grant_status igs_av_stnd_unit_all.s_adv_stnd_granting_status%TYPE;
2725 v_dummy hz_parties.party_id%TYPE;
2726 v_rowid ROWID;
2727 BEGIN
2728 write_message (' p_batch_id => ' || p_batch_id);
2729 write_message ( ' p_unit_details_id => '
2730 || p_unit_details_id
2731 );
2732 write_message ( ' p_person_id_code => '
2733 || p_person_id_code
2734 );
2735 write_message ( ' p_person_id_code_type => '
2736 || p_person_id_code_type
2737 );
2738 write_message (' p_program_cd => ' || p_program_cd);
2739 write_message ( ' p_load_cal_alt_code => '
2740 || p_load_cal_alt_code
2741 );
2742 write_message (' p_avstnd_grade => '
2743 || p_avstnd_grade
2744 );
2745 write_message ( ' p_achievable_credit_points => '
2746 || p_achievable_credit_points
2747 );
2748 write_message ( ' p_target_course_subject => '
2749 || p_target_course_subject
2750 );
2751 write_message ( ' p_target_course_num => '
2752 || p_target_course_num
2753 );
2754 write_message (' p_inst_id_code => '
2755 || p_inst_id_code
2756 );
2757 write_message ( ' p_inst_id_code_type => '
2758 || p_inst_id_code_type
2759 );
2760 ecx_debug.push ('IGS_DA_TRNS_IMP.CREATE_ADV_STND_UNIT');
2761 OPEN c_present_inst;
2762 FETCH c_present_inst INTO l_institution_cd;
2763 CLOSE c_present_inst;
2764
2765 IF l_institution_cd IS NULL
2766 THEN
2767 write_message ('ERROR The institution setup is not done in Advanced standing configuration form'
2768 );
2769 notify_error (p_batch_id,
2770 l_person_id,
2771 p_program_cd,
2772 fnd_message.get_string ('IGS', 'IGS_DA_INST_NOT_EXIST')
2773 );
2774 RETURN;
2775 END IF;
2776
2777 l_achievable_credit_points := p_achievable_credit_points;
2778 -- get the person ID
2779 igs_da_xml_pkg.get_person_details (p_person_id_code,
2780 p_person_id_code_type,
2781 l_person_id,
2782 l_person_number
2783 );
2784 -- get institution code
2785 igs_da_xml_pkg.get_person_details (RTRIM (LTRIM (p_inst_id_code)),
2786 RTRIM (LTRIM (p_inst_id_code_type)),
2787 v_dummy,
2788 l_prev_institution_code
2789 );
2790
2791 IF l_prev_institution_code IS NULL
2792 THEN
2793 write_message ('ERROR The institution ID must match either the OSS ID or an alternate institution ID as defined in the degree audit configuration.'
2794 );
2795 RETURN;
2796 END IF;
2797
2798 l_unit_cd := p_target_course_subject || p_target_course_num;
2799 OPEN c_unit_ver;
2800 FETCH c_unit_ver INTO l_version_number;
2801 CLOSE c_unit_ver;
2802 l_grant_status := get_adv_stnd_granting_status (p_batch_id);
2803
2804 IF (l_grant_status = 'GRANTED')
2805 THEN
2806 l_granted_dt := TRUNC (SYSDATE);
2807 END IF;
2808
2809 OPEN c_requestor;
2810 FETCH c_requestor INTO l_auth_pers_id;
2811 CLOSE c_requestor;
2812
2813
2814 IF l_auth_pers_id IS NULL
2815 THEN
2816
2817 OPEN c_requestor_id;
2818 FETCH c_requestor_id INTO l_auth_pers_id;
2819 CLOSE c_requestor_id;
2820 END IF;
2821
2822 IF l_auth_pers_id IS NULL
2823 THEN
2824 write_message ('ERROR The authorising person must match either the OSS ID or an alternate student ID as defined in the degree audit configuration.'
2825 );
2826 RETURN;
2827 END IF;
2828
2829 IF p_unit_details_id IS NULL
2830 THEN
2831 write_message ('ERROR Cannot add advanced standing records without a source unit being specified.'
2832 );
2833 RETURN;
2834 END IF;
2835
2836 write_message ('ENTERED create_adv_stnd_unit ');
2837 --Standard start of API savepoint
2838 SAVEPOINT create_adv_stnd_unit;
2839 fnd_msg_pub.initialize;
2840 --Initialize API return status to success.
2841 l_return_status := fnd_api.g_ret_sts_success;
2842 l_unit_cd := UPPER (l_unit_cd);
2843 l_prev_institution_code := UPPER (l_prev_institution_code);
2844
2845 IF validate_parameters (p_batch_id => p_batch_id,
2846 p_person_id => l_person_id,
2847 p_person_number => l_person_number,
2848 p_program_cd => p_program_cd,
2849 p_unit_cd => l_unit_cd,
2850 p_version_number => l_version_number
2851 )
2852 THEN
2853 write_message ('Before derive_unit_data');
2854
2855 IF derive_unit_data (p_batch_id => p_batch_id,
2856 p_person_number => l_person_number,
2857 p_program_cd => p_program_cd,
2858 p_unit_cd => l_unit_cd,
2859 p_version_number => l_version_number,
2860 p_institution_cd => l_institution_cd,
2861 p_load_cal_alt_code => p_load_cal_alt_code,
2862 p_avstnd_grade => p_avstnd_grade,
2863 p_achievable_credit_points => l_achievable_credit_points,
2864 p_person_id => l_person_id,
2865 p_s_adv_stnd_type => l_s_adv_stnd_type,
2866 p_cal_type => l_cal_type,
2867 p_seq_number => l_seq_number,
2868 p_auth_pers_id => l_auth_pers_id,
2869 p_as_version_number => l_as_version_number
2870 )
2871 THEN
2872 write_message ('*****l_unit_details_id=' || p_unit_details_id);
2873 write_message ('Before validate_adv_std_db_cons');
2874
2875 IF validate_adv_std_db_cons (p_batch_id => p_batch_id,
2876 p_person_id => l_person_id,
2877 p_person_number => l_person_number,
2878 p_program_cd => p_program_cd,
2879 p_unit_cd => l_unit_cd,
2880 p_version_number => l_version_number,
2881 p_load_cal_alt_code => p_load_cal_alt_code,
2882 p_achievable_credit_points => l_achievable_credit_points
2883 )
2884 THEN
2885 write_message ('Before validate_adv_stnd');
2886
2887 IF validate_adv_stnd (p_batch_id => p_batch_id,
2888 p_person_id => l_person_id,
2889 p_person_number => l_person_number,
2890 p_program_cd => p_program_cd,
2891 p_unit_cd => l_unit_cd,
2892 p_version_number => l_version_number,
2893 p_prev_institution_code => l_prev_institution_code
2894 )
2895 THEN
2896 write_message ('Before IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION'
2897 );
2898
2899 -- Validate that the current record is already present in the tables IGS_AV_ADV_STANDING_ALL and IGS_AV_STND_UNIT_ALL
2900 IF NOT igs_av_adv_standing_pkg.get_pk_for_validation (x_person_id => l_person_id,
2901 x_course_cd => p_program_cd,
2902 x_version_number => l_as_version_number,
2903 x_exemption_institution_cd => l_prev_institution_code
2904 )
2905 THEN
2906 write_message ('***** INSERT INTO IGS_AV_ADV_STANDING_ALL *****'
2907 );
2908
2909 igs_av_adv_standing_pkg.insert_row (x_rowid => v_rowid,
2910 x_person_id => l_person_id,
2911 x_course_cd => UPPER (p_program_cd
2912 ),
2913 x_version_number => l_as_version_number,
2914 x_total_exmptn_approved => 0,
2915 x_total_exmptn_granted => 0,
2916 x_total_exmptn_perc_grntd => 0,
2917 x_exemption_institution_cd => l_prev_institution_code,
2918 x_org_id => igs_ge_gen_003.get_org_id ()
2919 );
2920
2921 END IF; --IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION
2922
2923 write_message ('Before IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION'
2924 );
2925
2926 IF NOT igs_av_stnd_unit_pkg.get_uk_for_validation (x_person_id => l_person_id,
2927 x_exemption_institution_cd => UPPER (l_prev_institution_code
2928 ),
2929 x_unit_details_id => p_unit_details_id,
2930 x_tst_rslt_dtls_id => NULL,
2931 x_unit_cd => UPPER (l_unit_cd
2932 ),
2933 x_as_course_cd => UPPER (p_program_cd
2934 ),
2935 x_as_version_number => l_as_version_number,
2936 x_version_number => l_version_number,
2937 x_s_adv_stnd_type => l_s_adv_stnd_type
2938 )
2939 THEN
2940 write_message ('Before validate_std_unt_db_cons');
2941 write_message ( '**** l_unit_details_id='
2942 || p_unit_details_id
2943 );
2944
2945 IF validate_std_unt_db_cons (p_batch_id => p_batch_id,
2946 p_person_number => l_person_number,
2947 p_program_cd => p_program_cd,
2948 p_unit_cd => l_unit_cd,
2949 p_version_number => l_version_number,
2950 p_institution_cd => l_institution_cd,
2951 p_person_id => l_person_id,
2952 p_auth_pers_id => l_auth_pers_id,
2953 p_unit_details_id => p_unit_details_id,
2954 p_as_version_number => l_as_version_number,
2955 p_prev_institution_code => l_prev_institution_code
2956 )
2957 THEN
2958 write_message ('Before validate_unit');
2959
2960 IF validate_unit (p_program_cd => p_program_cd,
2961 p_unit_cd => l_unit_cd,
2962 p_version_number => l_version_number,
2963 p_achievable_credit_points => l_achievable_credit_points,
2964 p_person_id => l_person_id,
2965 p_auth_pers_id => l_auth_pers_id,
2966 p_unit_details_id => p_unit_details_id,
2967 p_as_version_number => l_as_version_number,
2968 p_batch_id => p_batch_id,
2969 p_prev_institution_code => l_prev_institution_code
2970 )
2971 THEN
2972 l_request_id := fnd_global.conc_request_id;
2973 l_program_id := fnd_global.conc_program_id;
2974 l_program_application_id :=
2975 fnd_global.prog_appl_id;
2976
2977 IF (l_request_id = -1)
2978 THEN
2979 l_request_id := NULL;
2980 l_program_id := NULL;
2981 l_program_application_id := NULL;
2982 l_program_update_date := NULL;
2983 ELSE
2984 l_program_update_date := SYSDATE;
2985 END IF;
2986
2987 write_message ( '***** l_av_stnd_unit_lvl_id='
2988 || l_av_stnd_unit_lvl_id
2989 );
2990 write_message ('***** INSERT INTO IGS_AV_STND_UNIT_ALL *****'
2991 );
2992 DECLARE
2993 CURSOR c_unitcd_ver
2994 IS
2995 SELECT schm.grading_schema_code,schm.grd_schm_version_number
2996 FROM igs_ps_unit_grd_schm schm , IGS_AS_GRD_SCH_GRADE grd
2997 WHERE schm.unit_version_number = l_version_number AND schm.unit_code = l_unit_cd AND grd.grade=p_avstnd_grade
2998 AND schm.grading_schema_code = grd.grading_schema_cd
2999 AND schm.grd_schm_version_number=grd.version_number;
3000
3001 rec_unitcd_ver c_unitcd_ver%ROWTYPE;
3002 BEGIN
3003 OPEN c_unitcd_ver;
3004 FETCH c_unitcd_ver into rec_unitcd_ver;
3005 BEGIN
3006 igs_av_stnd_unit_pkg.insert_row (x_rowid => l_dmmy_rowid,
3007 x_person_id => l_person_id,
3008 x_as_course_cd => UPPER (p_program_cd
3009 ),
3010 x_as_version_number => l_as_version_number,
3011 x_s_adv_stnd_type => l_s_adv_stnd_type,
3012 x_unit_cd => UPPER (l_unit_cd
3013 ),
3014 x_version_number => l_version_number,
3015 x_s_adv_stnd_granting_status => 'APPROVED',
3016 x_credit_percentage => NULL,
3017 x_s_adv_stnd_recognition_type => 'CREDIT',
3018 x_approved_dt => SYSDATE,
3019 x_authorising_person_id => l_auth_pers_id,
3020 x_crs_group_ind => 'N',
3021 x_exemption_institution_cd => UPPER (l_prev_institution_code
3022 ),
3023 x_granted_dt => TO_DATE (NULL
3024 ),
3025 x_expiry_dt => TO_DATE (NULL
3026 ),
3027 x_cancelled_dt => TO_DATE (NULL
3028 ),
3029 x_revoked_dt => TO_DATE (NULL
3030 ),
3031 x_comments => 'Advanced Standing from external source',
3032 x_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
3033 x_cal_type => l_cal_type,
3034 x_ci_sequence_number => l_seq_number,
3035 x_institution_cd => UPPER (l_prev_institution_code --l_institution_cd
3036 ),
3037 x_unit_details_id => p_unit_details_id,
3038 x_grade => p_avstnd_grade,
3039 x_achievable_credit_points => l_achievable_credit_points,
3040 x_mode => 'R',
3041 x_org_id => igs_ge_gen_003.get_org_id (),
3042 x_adv_stnd_trans => 'N',
3043 x_grading_schema_cd => rec_unitcd_ver.grading_schema_code,
3044 x_grd_sch_version_number => rec_unitcd_ver.grd_schm_version_number
3045 );
3046 EXCEPTION
3047 WHEN OTHERS THEN
3048 IF (nvl(fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'INTERNAL') THEN
3049 RETURN;
3050 END IF;
3051 END;
3052 CLOSE c_unitcd_ver;
3053 END;
3054
3055 IF l_grant_status <> 'APROVED'
3056 THEN
3057 DECLARE
3058 CURSOR c_unitcd_ver
3059 IS
3060 SELECT schm.grading_schema_code,schm.grd_schm_version_number
3061 FROM igs_ps_unit_grd_schm schm , IGS_AS_GRD_SCH_GRADE grd
3062 WHERE schm.unit_version_number = l_version_number AND schm.unit_code = l_unit_cd AND grd.grade=p_avstnd_grade
3063 AND schm.grading_schema_code = grd.grading_schema_cd
3064 AND schm.grd_schm_version_number=grd.version_number;
3065
3066 rec_unitcd_ver c_unitcd_ver%ROWTYPE;
3067 BEGIN
3068 OPEN c_unitcd_ver;
3069 FETCH c_unitcd_ver into rec_unitcd_ver;
3070 igs_av_stnd_unit_pkg.update_row (x_rowid => l_dmmy_rowid,
3071 x_person_id => l_person_id,
3072 x_as_course_cd => UPPER (p_program_cd
3073 ),
3074 x_as_version_number => l_as_version_number,
3075 x_s_adv_stnd_type => l_s_adv_stnd_type,
3076 x_unit_cd => UPPER (l_unit_cd
3077 ),
3078 x_version_number => l_version_number,
3079 x_s_adv_stnd_granting_status => l_grant_status,
3080 x_credit_percentage => TO_NUMBER (NULL
3081 ),
3082 x_s_adv_stnd_recognition_type => 'CREDIT',
3083 x_approved_dt => SYSDATE,
3084 x_authorising_person_id => l_auth_pers_id,
3085 x_crs_group_ind => 'N',
3086 x_exemption_institution_cd => UPPER (l_prev_institution_code
3087 ),
3088 x_granted_dt => l_granted_dt,
3089 x_expiry_dt => TO_DATE (NULL
3090 ),
3091 x_cancelled_dt => TO_DATE (NULL
3092 ),
3093 x_revoked_dt => TO_DATE (NULL
3094 ),
3095 x_comments => 'Advanced Standing from external source',
3096 x_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
3097 x_cal_type => l_cal_type,
3098 x_ci_sequence_number => l_seq_number,
3099 x_institution_cd => UPPER (l_prev_institution_code --l_institution_cd
3100 ),
3101 x_unit_details_id => p_unit_details_id,
3102 x_tst_rslt_dtls_id => NULL,
3103 x_grading_schema_cd => rec_unitcd_ver.grading_schema_code,
3104 x_grd_sch_version_number => rec_unitcd_ver.grd_schm_version_number,
3105 x_grade => p_avstnd_grade,
3106 x_achievable_credit_points => l_achievable_credit_points,
3107 x_mode => 'R',
3108 x_deg_aud_detail_id => NULL
3109 );
3110 CLOSE c_unitcd_ver;
3111 END;
3112 END IF;
3113
3114 write_message ( ' Inserted into IGS_AV_STND_UNIT_ALL val AV_STND_UNIT_ID ='
3115 || l_av_stnd_unit_lvl_id
3116 );
3117 ELSE -- validate_unit
3118 write_message ('Error 3');
3119 l_return_status := fnd_api.g_ret_sts_error;
3120 END IF; --validate_unit
3121 ELSE -- validate_std_unt_db_cons
3122 l_return_status := fnd_api.g_ret_sts_error;
3123 write_message ('Error 4');
3124 END IF; --validate_std_unt_db_cons
3125 ELSE
3126 write_message (' Updating igs_av_stnd_unit_all');
3127 OPEN c_adv_stnd_unt (l_person_id,
3128 l_prev_institution_code,
3129 p_unit_details_id,
3130 l_unit_cd,
3131 p_program_cd,
3132 l_as_version_number,
3133 l_version_number,
3134 l_s_adv_stnd_type
3135 );
3136 FETCH c_adv_stnd_unt INTO l_adv_stnd_unt;
3137
3138 IF c_adv_stnd_unt%FOUND
3139 THEN
3140 DECLARE
3141 CURSOR c_unitcd_ver
3142 IS
3143
3144
3145 SELECT schm.grading_schema_code,schm.grd_schm_version_number
3146 FROM igs_ps_unit_grd_schm schm , IGS_AS_GRD_SCH_GRADE grd
3147 WHERE schm.unit_version_number = l_adv_stnd_unt.version_number AND schm.unit_code = l_adv_stnd_unt.unit_cd AND grd.grade=p_avstnd_grade
3148 AND schm.grading_schema_code = grd.grading_schema_cd
3149 AND schm.grd_schm_version_number=grd.version_number;
3150
3151 rec_unitcd_ver c_unitcd_ver%ROWTYPE;
3152 BEGIN
3153 OPEN c_unitcd_ver;
3154 FETCH c_unitcd_ver into rec_unitcd_ver;
3155 igs_av_stnd_unit_pkg.update_row (x_rowid => l_adv_stnd_unt.ROWID,
3156 x_person_id => l_adv_stnd_unt.person_id,
3157 x_as_course_cd => l_adv_stnd_unt.as_course_cd,
3158 x_as_version_number => l_adv_stnd_unt.as_version_number,
3159 x_s_adv_stnd_type => l_adv_stnd_unt.s_adv_stnd_type,
3160 x_unit_cd => l_adv_stnd_unt.unit_cd,
3161 x_version_number => l_adv_stnd_unt.version_number,
3162 x_s_adv_stnd_granting_status => l_grant_status,
3163 x_credit_percentage => l_adv_stnd_unt.credit_percentage,
3164 x_s_adv_stnd_recognition_type => l_adv_stnd_unt.s_adv_stnd_recognition_type,
3165 x_approved_dt => l_adv_stnd_unt.approved_dt,
3166 x_authorising_person_id => l_auth_pers_id,
3167 x_crs_group_ind => l_adv_stnd_unt.crs_group_ind,
3168 x_exemption_institution_cd => l_adv_stnd_unt.exemption_institution_cd,
3169 x_granted_dt => l_granted_dt,
3170 x_expiry_dt => l_adv_stnd_unt.expiry_dt,
3171 x_cancelled_dt => l_adv_stnd_unt.cancelled_dt,
3172 x_revoked_dt => l_adv_stnd_unt.revoked_dt,
3173 x_comments => 'Advanced Standing from external source',
3174 x_av_stnd_unit_id => l_adv_stnd_unt.av_stnd_unit_id,
3175 x_cal_type => l_cal_type,
3176 x_ci_sequence_number => l_seq_number,
3177 x_institution_cd => l_adv_stnd_unt.institution_cd,
3178 x_unit_details_id => l_adv_stnd_unt.unit_details_id,
3179 x_tst_rslt_dtls_id => l_adv_stnd_unt.tst_rslt_dtls_id,
3180 x_grading_schema_cd => rec_unitcd_ver.grading_schema_code,
3181 x_grd_sch_version_number => rec_unitcd_ver.grd_schm_version_number,
3182 x_grade => p_avstnd_grade,
3183 x_achievable_credit_points => l_achievable_credit_points,
3184 x_deg_aud_detail_id => l_adv_stnd_unt.deg_aud_detail_id
3185 );
3186
3187 CLOSE c_unitcd_ver;
3188 END;
3189 write_message ('DONE IGS_AV_STND_UNIT_PKG.UPDATE_ROW ');
3190 END IF;
3191
3192 CLOSE c_adv_stnd_unt;
3193 END IF; --IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION
3194
3195 IF NOT create_post_unit (p_person_id => l_person_id,
3196 p_course_version => l_as_version_number,
3197 p_unit_details_id => p_unit_details_id,
3198 p_program_cd => p_program_cd,
3199 p_unit_cd => l_unit_cd,
3200 p_version_number => l_version_number,
3201 p_batch_id => p_batch_id,
3202 p_prev_institution_code => l_prev_institution_code
3203 )
3204 THEN
3205 write_message ('Error 2');
3206 l_return_status := fnd_api.g_ret_sts_error;
3207 END IF; --create_post_unit
3208 ELSE -- validate_adv_stnd
3209 l_return_status := fnd_api.g_ret_sts_error;
3210 write_message ('Error 8');
3211 END IF; --validate_adv_stnd
3212 ELSE -- validate_adv_std_db_cons
3213 l_return_status := fnd_api.g_ret_sts_error;
3214 write_message ('Error 9');
3215 END IF; --validate_adv_std_db_cons
3216 ELSE -- derive_unit_data
3217 l_return_status := fnd_api.g_ret_sts_error;
3218 write_message ('Error 10');
3219 END IF; -- derive_unit_data
3220 ELSE -- validate_parameters
3221 l_return_status := fnd_api.g_ret_sts_error;
3222 write_message ('Error 11');
3223 END IF; --validate_parameters
3224
3225 /* IF l_return_status IN (fnd_api.g_ret_sts_error, 'E', 'W')
3226 THEN
3227 write_message ('************************ Roll Back ********************');
3228 ROLLBACK TO create_adv_stnd_unit;
3229 END IF;*/
3230
3231 -- COMMIT;
3232 write_message ('************************ END ADVSTND ********************'
3233 );
3234 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_ADV_STND_UNIT');
3235 --Standard call to get message count and if count is 1, get message info.
3236 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3237 p_data => l_msg_data);
3238 EXCEPTION
3239 WHEN duplicate_record_exists
3240 THEN
3241 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_ADV_STND_UNIT');
3242 notify_error (p_batch_id,
3243 l_person_id,
3244 p_program_cd,
3245 'Error has occurred.See log for Details'
3246 );
3247
3248 write_message('ERROR ' || sqlerrm);
3249 -- ROLLBACK TO create_adv_stnd_unit;
3250 l_return_status := 'W';
3251 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3252 p_data => l_msg_data
3253 );
3254 WHEN fnd_api.g_exc_error
3255 THEN
3256 -- ROLLBACK TO create_adv_stnd_unit;
3257 notify_error (p_batch_id,
3258 l_person_id,
3259 p_program_cd,
3260 'Error has occurred.See log for Details'
3261 );
3262 l_return_status := fnd_api.g_ret_sts_error;
3263 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3264 p_data => l_msg_data
3265 );
3266 WHEN fnd_api.g_exc_unexpected_error
3267 THEN
3268 -- ROLLBACK TO create_adv_stnd_unit;
3269 l_return_status := fnd_api.g_ret_sts_unexp_error;
3270 notify_error (p_batch_id,
3271 l_person_id,
3272 p_program_cd,
3273 'Error has occurred.See log for Details'
3274 );
3275 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3276 p_data => l_msg_data
3277 );
3278 WHEN OTHERS
3279 THEN
3280 notify_error (p_batch_id,
3281 l_person_id,
3282 p_program_cd,
3283 'Error has occurred.See log for Details'
3284 );
3285 write_message('ERROR ' || sqlerrm);
3286 -- ROLLBACK TO create_adv_stnd_unit;
3287 l_return_status := fnd_api.g_ret_sts_unexp_error;
3288 fnd_message.set_name ('IGS', 'IGS_AV_UNHANDLED_ERROR');
3289 fnd_message.set_token ('ERROR', SQLERRM);
3290 fnd_msg_pub.ADD;
3291 notify_error (p_batch_id,
3292 l_person_id,
3293 p_program_cd,
3294 'Error has occurred.See log for Details'
3295 );
3296
3297 write_message('ERROR ' || sqlerrm);
3298 fnd_msg_pub.count_and_get (p_count => l_msg_count,
3299 p_data => l_msg_data
3300 );
3301 END create_adv_stnd_unit;
3302
3303 PROCEDURE create_or_get_batch_id (
3304 p_batch_id IN igs_da_rqst.batch_id%TYPE,
3305 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
3306 p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
3307 p_program_code IN igs_av_lgcy_unt_int.program_cd%TYPE,
3308 transaction_sub_type IN VARCHAR2,
3309 p_out_batch_id OUT NOCOPY igs_da_rqst.batch_id%TYPE
3310 )
3311 IS
3312 CURSOR c_template
3313 IS
3314 SELECT request_type_id
3315 FROM igs_da_cnfg_req_typ
3316 WHERE request_name = 'Transfer Evaluation External Source'
3317 AND request_type = 'TE'
3318 AND closed_ind = 'N';
3319
3320 CURSOR c_batch_id
3321 IS
3322 SELECT igs_da_batch_id_s.NEXTVAL
3323 FROM DUAL;
3324
3325 CURSOR c_requestor
3326 IS
3327 SELECT requestor_id
3328 FROM igs_da_rqst
3329 WHERE batch_id = p_batch_id;
3330
3331 CURSOR c_stdnts_batch (p_person_id hz_parties.party_id%TYPE)
3332 IS
3333 SELECT 'x'
3334 FROM igs_da_req_stdnts
3335 WHERE batch_id = p_batch_id
3336 AND person_id = p_person_id
3337 AND ERROR_CODE = 'INP';
3338
3339 CURSOR c_requestor_id
3340 IS
3341 SELECT hz_parties.party_id
3342 FROM hz_parties, fnd_user
3343 WHERE fnd_user.customer_id = hz_parties.party_id
3344 AND fnd_user.user_id = fnd_profile.VALUE ('IGS_DA_WF_ADMIN');
3345
3346 l_request_type_id igs_da_cnfg_req_typ.request_type_id%TYPE;
3347 l_batch_id igs_da_rqst.batch_id%TYPE;
3348 l_person_id hz_parties.party_id%TYPE;
3349 l_requestor_person_id hz_parties.party_id%TYPE;
3350 l_person_number hz_parties.party_number%TYPE;
3351 v_dummy VARCHAR2 (1);
3352 v_dummy_rowid ROWID;
3353 l_return_status VARCHAR2 (500);
3354 l_msg_data VARCHAR2 (2000);
3355 l_msg_count NUMBER;
3356 l_igs_da_req_stdnts_id igs_da_req_stdnts.igs_da_req_stdnts_id%TYPE;
3357 BEGIN
3358 ecx_debug.push ('IGS_DA_TRNS_IMP.CREATE_OR_GET_BATCH_ID');
3359 write_message (' p_batch_id ' || p_batch_id);
3360 write_message (' p_person_id_code ' || p_person_id_code);
3361 write_message (' p_person_id_code_type ' || p_person_id_code_type);
3362 write_message (' p_program_code ' || p_program_code);
3363 write_message (' transaction_sub_type ' || transaction_sub_type);
3364 write_message (' p_out_batch_id ' || p_out_batch_id);
3365 igs_da_xml_pkg.get_person_details (RTRIM (LTRIM (p_person_id_code)),
3366 RTRIM (LTRIM (p_person_id_code_type)),
3367 l_person_id,
3368 l_person_number
3369 );
3370
3371 IF l_person_id IS NULL
3372 THEN
3373 write_message ('ERROR The student ID must match either the OSS ID or an alternate student ID as defined in the degree audit configuration.'
3374 );
3375 notify_error (p_batch_id,
3376 l_person_id,
3377 p_program_code,
3378 fnd_message.get_string ('IGS', 'IGS_DA_STU_NOT_EXIST')
3379 );
3380 RETURN;
3381 END IF;
3382
3383 write_message ( 'create_or_get_batch_id : Got person ID as '
3384 || l_person_id
3385 );
3386
3387 IF (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') = 'EXTERNAL')
3388 THEN
3389 write_message ('create_or_get_batch_id : Source is External');
3390 OPEN c_stdnts_batch (l_person_id);
3391 FETCH c_stdnts_batch INTO v_dummy;
3392
3393 IF c_stdnts_batch%NOTFOUND
3394 THEN
3395 -- this person data has not been imported before for this xml message
3396 -- safely delete academic history and advanced standing data
3397 write_message ('create_or_get_batch_id : Deleting existing records'
3398 );
3399 delete_adv_stnd_records (l_person_id);
3400 END IF;
3401
3402 CLOSE c_stdnts_batch;
3403
3404 IF p_batch_id IS NULL
3405 AND UPPER (transaction_sub_type) = UPPER ('NoRequest')
3406 THEN
3407 -- get the request template type id
3408 OPEN c_template;
3409 FETCH c_template INTO l_request_type_id;
3410 CLOSE c_template;
3411
3412 IF l_request_type_id IS NULL
3413 THEN
3414 write_message ('Setup Template not defined');
3415 notify_error (p_batch_id,
3416 l_person_id,
3417 p_program_code,
3418 'Setup Template not defined'
3419 );
3420 END IF;
3421
3422 -- create a new batch id
3423
3424 OPEN c_batch_id;
3425 FETCH c_batch_id INTO l_batch_id;
3426 CLOSE c_batch_id;
3427 OPEN c_requestor;
3428 FETCH c_requestor INTO l_requestor_person_id;
3429 CLOSE c_requestor;
3430
3431 IF l_requestor_person_id IS NULL
3432 THEN
3433 OPEN c_requestor_id;
3434 FETCH c_requestor_id INTO l_requestor_person_id;
3435 CLOSE c_requestor_id;
3436 END IF;
3437
3438 IF l_requestor_person_id IS NULL
3439 THEN
3440 notify_error (p_batch_id,
3441 l_person_id,
3442 p_program_code,
3443 'ERROR Could not find the authorising person ID in OSS.'
3444 );
3445 END IF;
3446
3447 write_message ('create_or_get_batch_id : Creating new request');
3448 write_message ('***** INSERT INTO IGS_DA_RQST *****');
3449 igs_da_rqst_pkg.insert_row (x_rowid => v_dummy_rowid,
3450 x_batch_id => l_batch_id,
3451 x_request_type_id => l_request_type_id,
3452 x_request_mode => 'MULTI',
3453 x_program_comparison_type => 'DP',
3454 x_request_status => 'COMPLETED',
3455 x_person_id_group_id => NULL,
3456 x_person_id => NULL,
3457 x_requestor_id => l_requestor_person_id,
3458 x_student_release_ind => 'N',
3459 x_special_program => NULL,
3460 x_special_program_catalog => NULL,
3461 x_attribute_category => NULL,
3462 x_attribute1 => NULL,
3463 x_attribute2 => NULL,
3464 x_attribute3 => NULL,
3465 x_attribute4 => NULL,
3466 x_attribute5 => NULL,
3467 x_attribute6 => NULL,
3468 x_attribute7 => NULL,
3469 x_attribute8 => NULL,
3470 x_attribute9 => NULL,
3471 x_attribute10 => NULL,
3472 x_attribute11 => NULL,
3473 x_attribute12 => NULL,
3474 x_attribute13 => NULL,
3475 x_attribute14 => NULL,
3476 x_attribute15 => NULL,
3477 x_attribute16 => NULL,
3478 x_attribute17 => NULL,
3479 x_attribute18 => NULL,
3480 x_attribute19 => NULL,
3481 x_attribute20 => NULL,
3482 x_mode => 'R',
3483 x_return_status => l_return_status,
3484 x_msg_data => l_msg_data,
3485 x_msg_count => l_msg_count
3486 );
3487 p_out_batch_id := l_batch_id;
3488 ELSIF UPPER (transaction_sub_type) <> UPPER ('NoRequest')
3489 AND p_batch_id IS NULL
3490 THEN
3491 write_message ('ERROR :- Missing Batch ID');
3492 notify_error (p_batch_id,
3493 l_person_id,
3494 p_program_code,
3495 'ERROR :- Missing Batch ID.'
3496 );
3497 ELSE
3498 p_out_batch_id := p_batch_id;
3499 END IF;
3500
3501 IF UPPER (transaction_sub_type) = UPPER ('NoRequest')
3502 AND (NVL (fnd_profile.VALUE ('IGS_AV_STND_SOURCE'), 'X') =
3503 'EXTERNAL'
3504 )
3505 AND l_batch_id IS NOT NULL
3506 THEN
3507 write_message ('create_or_get_batch_id : Adding students to request'
3508 );
3509 write_message ('***** INSERT INTO IGS_DA_REQ_STDNTS *****');
3510 v_dummy_rowid := NULL;
3511 igs_da_req_stdnts_pkg.insert_row (x_rowid => v_dummy_rowid,
3512 x_batch_id => l_batch_id,
3513 x_igs_da_req_stdnts_id => l_igs_da_req_stdnts_id,
3514 x_person_id => l_person_id,
3515 x_program_code => p_program_code,
3516 x_wif_program_code => NULL,
3517 x_special_program_code => NULL,
3518 x_major_unit_set_cd => NULL,
3519 x_program_major_code => NULL,
3520 x_report_text => NULL,
3521 x_wif_id => NULL,
3522 x_mode => 'R',
3523 x_error_code => 'INP'
3524 );
3525 ELSE
3526 UPDATE igs_da_req_stdnts
3527 SET ERROR_CODE = 'INP'
3528 WHERE batch_id = p_batch_id AND person_id = l_person_id;
3529 END IF;
3530 ELSE
3531 IF p_batch_id IS NULL
3532 THEN
3533 write_message (' ERROR Batch ID missing ');
3534 ELSE
3535 p_out_batch_id := p_batch_id;
3536 END IF;
3537
3538 write_message (' New Batch ID is ' || p_out_batch_id);
3539 END IF;
3540
3541 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_OR_GET_BATCH_ID');
3542 EXCEPTION
3543 WHEN OTHERS
3544 THEN
3545 ecx_debug.pop ('IGS_DA_TRNS_IMP.CREATE_OR_GET_BATCH_ID');
3546 notify_error (p_batch_id,
3547 l_person_id,
3548 p_program_code,
3549 'Setup Template not defined'
3550 );
3551 write_message('ERROR ' || sqlerrm);
3552 END create_or_get_batch_id;
3553
3554 PROCEDURE complete_import_process (p_batch_id IN igs_da_rqst.batch_id%TYPE)
3555 AS
3556 BEGIN
3557 ecx_debug.push ('IGS_DA_TRNS_IMP.COMPLETE_IMPORT_PROCESS');
3558 write_message ( 'Entered complete_import_process p_batch_id='
3559 || p_batch_id
3560 );
3561
3562 UPDATE igs_da_req_stdnts
3563 SET ERROR_CODE = NULL
3564 WHERE batch_id = p_batch_id AND ERROR_CODE = 'INP';
3565
3566 UPDATE igs_da_req_stdnts
3567 SET report_text =
3568 ' <HTML> <BODY> Transfer Evaluation <BR> <BR> '
3569 || 'Completed Successfully'
3570 || ' '
3571 || ' </BODY> </HTML> '
3572 WHERE batch_id = p_batch_id AND report_text IS NULL;
3573
3574 igs_da_xml_pkg.update_request_status (p_batch_id);
3575 ecx_debug.pop ('IGS_DA_TRNS_IMP.COMPLETE_IMPORT_PROCESS');
3576 EXCEPTION
3577 WHEN OTHERS
3578 THEN
3579 write_message('ERROR ' || sqlerrm);
3580 END complete_import_process;
3581 END igs_da_trns_imp;