[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_OSS_INTR_DTLS
Source
1 PACKAGE BODY igf_ap_oss_intr_dtls AS
2 /* $Header: IGFAP20B.pls 115.30 2003/12/11 06:06:21 sjalasut ship $ */
3
4 --Added the last 6 parameters as per the FACCR001 DLD Disbursement Build Jul 2002
5 --Bug No:2145941 Disbursement Build Jul 2002
6
7 PROCEDURE get_details (p_person_id IN igs_ad_appl_all.person_id%TYPE,
8 p_awd_cal_type IN igs_ca_inst_all.cal_type%TYPE,
9 p_awd_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
10 lv_oss_dtl_rec IN OUT NOCOPY oss_dtl_cur
11 )
12 --
13 -- this procedure which will be called from other packages / plds
14 -- the parameters passed to this procedure are
15 -- person_id IN
16 -- awd_cal_type IN
17 -- awd_seq_num IN
18 -- ref cursor variable OUT NOCOPY
19 -- all the packages and pls which will be calling this package procedure
20 -- must decalre a record type variable as defined in the spec.
21 --
22 IS
23
24 -- Cursor to get Responsible Org Unit code
25 --
26 CURSOR resp_org_unit_cur(p_course_cd igs_ps_ver_all.course_cd%TYPE,
27 p_ver_num igs_ps_ver_all.version_number%TYPE)
28 IS
29 SELECT responsible_org_unit_cd org_unit_code
30 FROM igs_ps_ver pver
31 WHERE pver.course_cd = p_course_cd
32 AND pver.version_number = p_ver_num;
33
34 org_unit_rec resp_org_unit_cur%ROWTYPE;
35 lv_adm_org_unit_cd igs_ps_ver_all.responsible_org_unit_cd%TYPE;
36 lv_enrl_org_unit_cd igs_ps_ver_all.responsible_org_unit_cd%TYPE;
37 --
38 -- Cursor to get "Ability to Benefit" test check
39 -- Below query will return a record, if the Student has taken this test.
40 --
41 CURSOR check_atb_cur(p_person_id igs_pe_typ_instances_all.person_id%TYPE)
42 IS
43 SELECT
44 admission_test_type adm_test_type,
45 test_date adm_test_date
46 FROM
47 igs_ad_test_results
48 WHERE person_id = p_person_id
49 AND admission_test_type = 'ATB';
50 -- Note : Though the Admission Test Type is a user defined code, the Implementation Guide
51 -- would indicate the School to set up this as "ATB".If the Student has taken up this test,
52 -- then Return "Y". Else "N"
53
54 atb_rec check_atb_cur%ROWTYPE;
55 lv_atb VARCHAR2(30) DEFAULT 'N';
56
57
58 -- These set of variables are returned from igf_ap_oss_integr.get_adm_appl_details()
59 -- [
60 lv_ad_appl_row_id ROWID; -- Retains RowId for IGS_AD_APPL
61 lv_ad_prog_row_id ROWID; -- Retains RowId for IGS_AD_PS_APPL_INST
62 lv_adm_appl_number igs_ad_appl_all.admission_appl_number%TYPE;
63 lv_course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE;
64 lv_crv_version_number igs_ad_ps_appl_inst_all.crv_version_number%TYPE;
65 lv_adm_offer_resp_stat igs_ad_ps_appl_inst_all.adm_offer_resp_status%TYPE;
66 lv_adm_outcome_stat igs_ad_ps_appl_inst_all.adm_outcome_status%TYPE;
67 lv_adm_appl_status igs_ad_appl_all.adm_appl_status%TYPE;
68 lv_multiple VARCHAR2(10); -- Hold 'Y' / 'N'
69 -- ]
70
71 -- These variables are needed to get enrol_credit_pt details
72 -- [
73 lv_req_cp igs_ps_ver_all.credit_points_required%TYPE;
74 lv_passed_cp NUMBER(15,2);
75 -- this is enrl_total_cp
76 lv_adv_stnd_cp NUMBER(15,2);
77 -- this is enrl_cuml_trans_cp
78 lv_enrl_stud_cp NUMBER(15,2);
79 -- this is cur_enrl_credit_points
80 lv_cp_remain NUMBER;
81 -- ]
82 --
83
84 -- variables to get anticipated completion date
85 -- [
86 lv_course_start_dt DATE;
87 lv_expected_completion_yr igs_ad_ps_appl_inst_all.expected_completion_yr%TYPE;
88 lv_expected_completion_perd igs_ad_ps_appl_inst_all.expected_completion_perd%TYPE;
89 lv_completion_dt DATE;
90 -- ]
91
92 -- variables to get Cummulative and Current GPA
93 -- [
94 lv_gpa_cum NUMBER;
95 lv_gpa_curr NUMBER;
96 lv_gpa_cp NUMBER;
97 lv_gpa_qp NUMBER;
98 lv_ret_stat VARCHAR2(100);
99 lv_msg_count NUMBER;
100 lv_msg_data VARCHAR2(2000);
101 lv_earned_cp NUMBER;
102 lv_attempted_Cp NUMBER;
103 lv_inst_attd VARCHAR2(30);
104 lv_credit_pts NUMBER;
105 lv_fte NUMBER;
106 --]
107 -- [
108 -- start of declaration of variables for get_enrol_details
109
110 --
111 -- Cursor to get details enrolment details from igs_en_stdnt_ps_att
112 --
113
114 --Added the last two AND conditions as per the FACCR004 in Disbursement Build Jul 2002
115 --to differentiate between records having primary program as 'Y' and 'A' or 'E'
116 --Bug Id:2154941
117 CURSOR enrl_std_cur( p_person_id igs_en_stdnt_ps_att_all.person_id%TYPE) IS
118 SELECT course_cd, adm_admission_appl_number, version_number, commencement_dt, course_attempt_status, attendance_mode, location_cd
119 FROM igs_en_stdnt_ps_att
120 WHERE person_id = p_person_id
121 AND key_program = 'Y' ;
122
123
124 --Added as per the FACCR004 in Disbursement Build Jul 2002
125 --Cursor to check if there are more than 1 program attempt for a person.
126 --
127 CURSOR cur_get_no_of_prg_attempts IS
128 SELECT COUNT(person_id)
129 FROM igs_en_stdnt_ps_att
130 WHERE person_id = p_person_id;
131
132 --Added the cursor to resolve bug 2296776
133
134 CURSOR get_adm_cnt(cp_course_cd igs_ps_ver.course_cd%TYPE) IS
135 SELECT COUNT(*) cnt FROM igs_ad_ps_appl_inst
136 WHERE person_id=p_person_id AND
137 course_cd = cp_course_cd;
138
139 l_adm_cnt_rec get_adm_cnt%ROWTYPE;
140
141
142 CURSOR get_adm_dtls(cp_course_cd igs_ps_ver.course_cd%TYPE) IS
143 SELECT adm.course_type adm_course_type,adps.course_cd adm_course_cd,
144 adps.admission_appl_number adm_appl_number
145 FROM igs_ad_ps_appl_inst adps,igs_en_stdnt_ps_att enps,
146 igs_ps_ver adm,igs_ps_ver enrl
147 WHERE adps.person_id=p_person_id AND adps.person_id= enps.person_id
148 AND enps.course_cd=cp_course_cd AND enps.course_cd=enrl.course_cd
149 AND adps.course_cd = adm.course_cd AND adm.course_type=enrl.course_type;
150
151 l_adm_rec get_adm_dtls%ROWTYPE;
152
153 --
154 -- Note : For testing purposes WHERE clause will be taken off if oss does not come up with the new
155 -- table structure to include primary_program column
156 --
157 enrl_std_rec enrl_std_cur%ROWTYPE;
158 enrl_std_mult_rec enrl_std_cur%ROWTYPE;
159
160 --
161 -- This cursor will also determine if there are more than one records for a person in
162 -- igs_en_stdnt_ps_att.If there are more than one records then
163 -- multiple_prog_d = 'Y' else 'N'
164 --
165 lv_mult_prog_d VARCHAR2(10) DEFAULT 'N';
166
167 --
168 -- Cursor to get enrollment cal_type
169 --
170
171 CURSOR enrl_dtl_cur(p_acad_cal_type VARCHAR2,
172 p_acad_seq_num NUMBER)
173 IS
174 SELECT
175 ci.cal_type enrl_load_cal_type,
176 ci.sequence_number enrl_load_seq_num ,
177 ci.alternate_code enrolled_term
178 FROM
179 igs_ca_inst ci,
180 igs_ca_type cty
181 WHERE cty.s_cal_cat = 'LOAD'
182 AND cty.cal_type = ci.cal_type
183 AND (ci.cal_type, ci.sequence_number)
184 IN
185 (
186 SELECT sup_cal_type,
187 sup_ci_sequence_number
188 FROM igs_ca_inst_rel
189 WHERE sub_cal_type = p_acad_cal_type
190 AND sub_ci_sequence_number = p_acad_seq_num
191 UNION
192 SELECT sub_cal_type,
193 sub_ci_sequence_number
194 FROM igs_ca_inst_rel
195 WHERE sup_cal_type = p_acad_cal_type
196 AND sup_ci_sequence_number = p_acad_seq_num
197 )
198 AND
199 SYSDATE BETWEEN ci.start_dt AND ci.end_dt
200 ORDER BY ci.start_dt;
201
202 enrl_dtl_rec enrl_dtl_cur%ROWTYPE;
203 lv_message VARCHAR2(1000);
204
205 -- Variables to store acad cal details
206 lv_acad_cal_type igs_ca_inst_all.cal_type%TYPE;
207 lv_acad_seq_num igs_ca_inst_all.sequence_number%TYPE;
208
209 --
210 -- Cursor to get enrlloment details
211 --
212 CURSOR en_std_cur (p_course_cd igs_ps_ver_all.course_type%TYPE,
213 p_version_number igs_ps_ver_all.version_number%TYPE)
214 IS
215 SELECT
216 course_type enrl_program_type
217 FROM igs_ps_ver
218 WHERE
219 course_cd = p_course_cd
220 AND
221 version_number = p_version_number;
222
223 en_std_rec en_std_cur%ROWTYPE;
224
225 -- The above COURSE_CD and VERSION_NUMBER are the Program details from Enrollment Module i.e.
226 -- IGS_EN_STDNT_PS_ATT.COURSE_CD IGS_EN_STDNT_PS_ATT.VERSION_NUMBER
227 --
228
229 -- end of declaration of variables for get_enrl_details
230 -- ]
231
232
233 --
234 -- Cursor to get Admission Application Record details
235 --
236 CURSOR adm_appl_cur(lv_ad_appl_row_id ROWID)
237 IS
238 SELECT adm.adm_cal_type, adm.adm_ci_sequence_number,adm.adm_appl_status, adm.appl_dt,adm.admission_cat, adm.
239 adm_fee_status,adm.spcl_grp_1, adm.spcl_grp_2, adm_st.s_adm_appl_status appl_status,
240 adm_ct.s_admission_process_type process_type, adm_ft.s_adm_fee_status fee_status
241 FROM IGS_AD_APPL adm,
242 IGS_AD_APPL_STAT adm_st,
243 IGS_AD_PRCS_CAT adm_ct,
244 IGS_AD_FEE_STAT adm_ft
245 WHERE adm.rowid = lv_ad_appl_row_id
246 AND adm.adm_appl_status = adm_st.adm_appl_status
247 AND adm.admission_cat = adm_ct.admission_cat
248 AND adm.adm_fee_status = adm_ft.adm_fee_status;
249
250 adm_appl_rec adm_appl_cur%ROWTYPE;
251
252 --
253 -- Cursor to get Admission Program Record details
254 --
255 CURSOR appl_prog_cur(lv_ad_prg_row_id ROWID)
256 IS
257 SELECT prg.attendance_type, prg.attendance_mode, prg.course_cd , prg.crv_version_number, prg.adm_outcome_status, prg.decision_date, prg.adm_offer_resp_status,
258 prg.actual_response_dt,prg.entry_level,prg.academic_index, prg.unit_set_cd, prg.us_version_number, prg_ou.s_adm_outcome_status ou_status,
259 prg_of.s_adm_offer_resp_status offer_status, prg.location_cd
260 FROM igs_ad_ps_appl_inst prg,
261 igs_ad_ou_stat prg_ou,
262 igs_ad_ofr_resp_stat prg_of
263 WHERE prg.rowid = lv_ad_prg_row_id
264 AND prg.adm_outcome_status = prg_ou.adm_outcome_status
265 AND prg.adm_offer_resp_status = prg_of.adm_offer_resp_status;
266
267 appl_prog_rec appl_prog_cur%ROWTYPE;
268
269 --
270 -- Cursor to get special program description
271 --
272
273 --
274 -- variables to store class_standing and pred_class_standing
275 --
276
277 lv_pred_class_standing VARCHAR2(60) DEFAULT NULL;
278 lv_class_standing igs_pr_css_class_std_v.class_standing%TYPE DEFAULT NULL;
279
280 --Added this cursor as per the FACCR001 DLD Disbursement Build Jul 2002
281 --
282 --Cursor to get the Atheltic Details Count
283 --
284 CURSOR cur_get_athletic_count IS
285 SELECT COUNT(person_id)
286 FROM igs_pe_athletic_prg_v
287 WHERE person_id=p_person_id;
288
289 l_athletic_cnt NUMBER;
290 l_athletic_m VARCHAR2(1);
291
292 --Added the cursor to resolve bug 2296776
293
294 CURSOR get_uset_dtls(cp_person_id igf_ap_fa_base_rec.person_id%TYPE,
295 cp_course_cd igs_ps_ver.course_cd%TYPE) IS
296 SELECT susa.unit_set_cd,susa.course_cd,susa.us_version_number
297 FROM igs_as_su_setatmpt susa,
298 igs_en_unit_set us ,
299 igs_en_unit_set_cat usc,
300 igs_en_stdnt_ps_att_all ps
301 WHERE susa.person_id = cp_person_id
302 AND ps.person_id = susa.person_id
303 AND susa.course_cd = ps.course_cd
304 AND ps.key_program = 'Y'
305 AND susa.course_cd = cp_course_cd
306 AND susa.PRIMARY_SET_IND = 'Y'
307 AND susa.selection_dt IS NOT NULL
308 AND susa.end_dt IS NULL
309 AND susa.rqrmnts_complete_dt IS NULL
310 AND susa.unit_set_cd = us.unit_set_cd
311 AND us.unit_set_cat = usc.unit_set_cat;
312
313 l_uset_rec get_uset_dtls%ROWTYPE;
314
315 CURSOR cur_fa_load_awd (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
316 IS
317 SELECT sub_cal_type,
318 sub_ci_sequence_number
319 FROM igs_ca_inst_rel,
320 igf_ap_fa_base_rec_all base
321 WHERE sup_cal_type = base.ci_cal_type
322 AND sup_ci_sequence_number = base.ci_sequence_number
323 AND base.base_id = p_base_id;
324
325 -- Cursor to get the attendance type for the maximum enrollment load
326 CURSOR c_att_type(l_cal_type igs_ca_inst_all.cal_type%TYPE) IS
327 SELECT attendance_type FROM igs_en_atd_type_load
328 WHERE cal_type = l_cal_type
329 AND lower_enr_load_range = (SELECT MAX(lower_enr_load_range)
330 FROM igs_en_atd_type_load
331 WHERE cal_type = l_cal_type);
332 l_full_att c_att_type%ROWTYPE;
333
334 CURSOR c_get_base_id (cp_person_id igf_ap_fa_base_rec.person_id%TYPE,
335 cp_ci_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
336 cp_ci_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE)
337 IS
338 SELECT base_id
339 FROM igf_ap_fa_base_rec_all
340 WHERE person_id = cp_person_id
341 AND ci_cal_type = cp_ci_cal_type
342 AND ci_sequence_number = cp_ci_sequence_number;
343
344 base_id_rec c_get_base_id%ROWTYPE;
345
346 lv_inst_attd_t VARCHAR2(30);
347 lv_load_cal_type VARCHAR2(20);
348 lv_load_ci_seq_num NUMBER;
349 lv_load_ci_alt_code VARCHAR2(20);
350 lv_load_ci_start_dt DATE;
351 lv_load_ci_end_dt DATE;
352 l_last_load_inst_attd VARCHAR2(10) := NULL;
353 BEGIN
354 -- Get the details which are independent of award year
355
356 OPEN enrl_std_cur(p_person_id);
357 -- this is the fetch to get the program details based on whether it is for Primary Program or Admission or Enrollment.
358 FETCH enrl_std_cur INTO enrl_std_rec;
359 CLOSE enrl_std_cur;
360
361 OPEN get_adm_cnt(enrl_std_rec.course_cd);
362 FETCH get_adm_cnt INTO l_adm_cnt_rec;
363 CLOSE get_adm_cnt;
364 IF NVL(l_adm_cnt_rec.cnt,0) >= 1 THEN
365 --lv_adm_appl_number :=enrl_std_rec.adm_admission_appl_number;
366 lv_course_cd :=enrl_std_rec.course_cd;
367 --lv_crv_version_number :=enrl_std_rec.version_number;
368 ELSE
369 OPEN get_adm_dtls(enrl_std_rec.course_cd);
370 FETCH get_adm_dtls INTO l_adm_rec;
371 CLOSE get_adm_dtls;
372 lv_adm_appl_number := l_adm_rec.adm_appl_number;
373 lv_course_cd := l_adm_rec.adm_course_cd;
374 lv_crv_version_number :=enrl_std_rec.version_number;
375 END IF;
376
377
378
379
380
381 --
382 -- Call igf_ap_oss_integr.gbefore call to IGFAP20B.pls admissions
383 -- set_adm_appl_details to retreive
384 -- the Selected Admission Application record [IGS_AD_APPL]
385 -- and the Selected Admission Program detail record [IGS_AD_PS_APPL_INST]
386 --
387
388 BEGIN
389
390 igf_ap_oss_integr.get_adm_appl_details(
391 p_person_id,
392 p_awd_cal_type,
393 p_awd_seq_num,
394 lv_ad_appl_row_id,
395 lv_ad_prog_row_id,
396 lv_adm_appl_number,
397 lv_course_cd,
398 lv_crv_version_number,
399 lv_adm_offer_resp_stat,
400 lv_adm_outcome_stat,
401 lv_adm_appl_status,
402 lv_multiple);
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 NULL;
407 END;
408
409 OPEN resp_org_unit_cur(lv_course_cd,
410 lv_crv_version_number);
411 FETCH resp_org_unit_cur INTO org_unit_rec;
412 CLOSE resp_org_unit_cur;
413
414 lv_adm_org_unit_cd := org_unit_rec.org_unit_code;
415
416 OPEN check_atb_cur(p_person_id);
417 FETCH check_atb_cur INTO atb_rec;
418
419 IF check_atb_cur%NOTFOUND THEN
420 CLOSE check_atb_cur;
421 ELSIF check_atb_cur%FOUND THEN
422 lv_atb := 'Y';
423 CLOSE check_atb_cur;
424 END IF;
425
426 -- Get admission related fields (IGS_AD_APPL)
427
428 OPEN adm_appl_cur(lv_ad_appl_row_id);
429 FETCH adm_appl_cur INTO adm_appl_rec;
430 CLOSE adm_appl_cur;
431
432 -- Get Program related fields (IGS_AD_PS_APPL_INST)
433
434 OPEN appl_prog_cur(lv_ad_prog_row_id);
435 FETCH appl_prog_cur INTO appl_prog_rec;
436 CLOSE appl_prog_cur;
437
438
439
440
441 -- Get enrol related fields (IGS_EN_STDNT_PS_ATT )
442
443 OPEN resp_org_unit_cur(enrl_std_rec.course_cd,
444 enrl_std_rec.version_number);
445 FETCH resp_org_unit_cur INTO org_unit_rec;
446 CLOSE resp_org_unit_cur;
447
448 lv_enrl_org_unit_cd := org_unit_rec.org_unit_code;
449
450 --
451 -- get acad cal from igs_en_gen_015 package
452 -- do not let any exceptions from this call
453
454 BEGIN
455
456
457 igs_en_gen_015.enrp_get_eff_load_ci (
458 p_person_id,
459 enrl_std_rec.course_cd,
460 SYSDATE,
461 lv_acad_cal_type, -- OUT NOCOPY parameter
462 lv_acad_seq_num, -- OUT NOCOPY parameter
463 lv_load_cal_type, -- OUT NOCOPY parameter
464 lv_load_ci_seq_num, -- OUT NOCOPY Parameter
465 lv_load_ci_alt_code, -- OUT NOCOPY Parameter
466 lv_load_ci_start_dt, -- OUT NOCOPY Parameter
467 lv_load_ci_end_dt, -- OUT NOCOPY Parameter
468 lv_message -- OUT NOCOPY Parameter
469 );
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 lv_acad_cal_type := NULL;
474 lv_acad_seq_num := NULL;
475 lv_message := NULL;
476 END;
477
478
479 -- get enrol_cal_type / seq_num / alt_code
480
481 OPEN enrl_dtl_cur(lv_acad_cal_type,lv_acad_seq_num);
482 FETCH enrl_dtl_cur INTO enrl_dtl_rec;
483 CLOSE enrl_dtl_cur;
484
485
486
487
488 OPEN en_std_cur(enrl_std_rec.course_cd,
489 enrl_std_rec.version_number);
490 FETCH en_std_cur INTO en_std_rec;
491 CLOSE en_std_cur;
492
493
494 -- do not let any exceptions from this call
495
496 BEGIN
497
498 igs_in_gen_001.inqp_get_prg_cp( p_person_id,
499 enrl_std_rec.course_cd,
500 enrl_std_rec.version_number,
501 lv_req_cp,
502 lv_passed_cp, -- this is enrl_total_cp
503 lv_adv_stnd_cp, -- this is enrl_cuml_trans_cp
504 lv_enrl_stud_cp, -- this is cur_enrl_credit_points
505 lv_cp_remain);
506 EXCEPTION
507 WHEN OTHERS THEN
508 lv_req_cp := NULL;
509 lv_passed_cp := NULL;
510 lv_adv_stnd_cp := NULL;
511 lv_enrl_stud_cp := NULL;
512 lv_cp_remain := NULL;
513
514 END;
515
516 BEGIN
517
518 igs_pr_cp_gpa.get_gpa_stats(
519 p_person_id,
520 enrl_std_rec.course_cd,
521 NULL,
522 enrl_dtl_rec.enrl_load_cal_type,
523 enrl_dtl_rec.enrl_load_seq_num,
524 NULL,--'FIN_AID', commenting FIN_AID as this was part of FISAP impl
525 'Y',
526 lv_gpa_cum,
527 lv_gpa_cp,
528 lv_gpa_qp,
529 FND_API.G_TRUE,
530 lv_ret_stat,
531 lv_msg_count,
532 lv_msg_data);
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 lv_gpa_cum := NULL;
537 lv_gpa_cp := NULL;
538 lv_gpa_qp := NULL;
539 lv_ret_stat := NULL;
540 lv_msg_count := NULL;
541 lv_msg_data := NULL;
542 END;
543
544 BEGIN
545
546 igs_pr_cp_gpa.get_gpa_stats( --Calculation of current GPA
547 p_person_id,
548 enrl_std_rec.course_cd,
549 NULL,
550 enrl_dtl_rec.enrl_load_cal_type,
551 enrl_dtl_rec.enrl_load_seq_num,
552 NULL,--'FIN_AID', commenting FIN_AID as this was part of FISAP impl
553 'N',
554 lv_gpa_curr,
555 lv_gpa_cp,
556 lv_gpa_qp,
557 FND_API.G_TRUE,
558 lv_ret_stat,
559 lv_msg_count,
560 lv_msg_data);
561 EXCEPTION
562 WHEN OTHERS THEN
563 lv_gpa_curr := NULL;
564 lv_gpa_cp := NULL;
565 lv_gpa_qp := NULL;
566 lv_ret_stat := NULL;
567 lv_msg_count := NULL;
568 lv_msg_data := NULL;
569 END;
570
571 BEGIN -- Achieved Credit Points
572
573 IGS_PR_CP_GPA.get_cp_stats(
574 p_person_id => p_person_id ,
575 p_course_cd => enrl_std_rec.course_cd,
576 p_stat_type => NULL,
577 p_load_cal_type => enrl_dtl_rec.enrl_load_cal_type,
578 p_load_ci_sequence_number => enrl_dtl_rec.enrl_load_seq_num,
579 p_system_stat => NULL, -- 'FIN_AID', commenting FIN_AID as this was part of FISAP impl
580 p_cumulative_ind => 'Y',
581 p_earned_cp => lv_earned_cp,
582 p_attempted_cp => lv_attempted_cp,
583 p_init_msg_list => FND_API.G_TRUE,
584 p_return_status => lv_ret_stat,
585 p_msg_count => lv_msg_count,
586 p_msg_data => lv_msg_data );
587 EXCEPTION
588 WHEN OTHERS THEN
589 lv_earned_cp := NULL;
590 lv_attempted_cp := NULL;
591 lv_ret_stat := NULL;
592 lv_msg_count := NULL;
593 lv_msg_data := NULL;
594 END;
595
596 BEGIN -- Derived Institutional attendance type
597 -- rasahoo replaced igs_en_prc_load.enrp_get_inst_latt
598 -- as part of Obsoletion og FA base record
599
600 OPEN c_get_base_id(p_person_id,p_awd_cal_type ,p_awd_seq_num);
601 FETCH c_get_base_id INTO base_id_rec;
602 CLOSE c_get_base_id;
603
604 FOR cur_fa_load_awd_rec IN cur_fa_load_awd(base_id_rec.base_id)
605 LOOP
606 igs_en_prc_load.enrp_get_inst_latt (p_person_id,
607 cur_fa_load_awd_rec.sub_cal_type,
608 cur_fa_load_awd_rec.sub_ci_sequence_number,
609 lv_inst_attd,
610 lv_credit_pts,
611 lv_fte);
612
613
614 OPEN c_att_type(cur_fa_load_awd_rec.sub_cal_type);
615 FETCH c_att_type INTO l_full_att;
616 CLOSE c_att_type;
617
618 lv_inst_attd := NVL(lv_inst_attd,l_full_att.attendance_type);
619
620 IF(NVL(l_last_load_inst_attd,lv_inst_attd) <> lv_inst_attd) THEN
621 lv_inst_attd := NULL;
622 EXIT;
623 END IF;
624 l_last_load_inst_attd := lv_inst_attd ;
625 END LOOP;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 lv_inst_attd := NULL;
630 lv_credit_pts := NULL;
631 lv_fte := NULL;
632 END;
633
634 -- Bug 3254448 Added this call to get the current enrolled credit points based on the current term.
635 BEGIN
636 igs_en_prc_load.enrp_get_inst_latt (p_person_id,
637 enrl_dtl_rec.enrl_load_cal_type,
638 enrl_dtl_rec.enrl_load_seq_num,
639 lv_inst_attd_t,
640 lv_credit_pts,
641 lv_fte);
642 EXCEPTION
643 WHEN OTHERS THEN
644 lv_inst_attd_t := NULL;
645 lv_credit_pts := NULL;
646 lv_fte := NULL;
647 END;
648
649 OPEN get_uset_dtls(p_person_id,enrl_std_rec.course_cd);
650 FETCH get_uset_dtls INTO l_uset_rec;
651 CLOSE get_uset_dtls;
652 --
653 -- This is end of get_enrl_details
654 --
655 -- do not let any exceptions from this call
656
657 BEGIN
658
659
660 lv_course_start_dt := igs_ad_gen_005.admp_get_crv_strt_dt(adm_appl_rec.adm_cal_type,
661 adm_appl_rec.adm_ci_sequence_number);
662
663 -- get the anticipated completion date (v_completion_dt) using the below function.
664 igs_ad_gen_004.admp_get_crv_comp_dt (lv_course_cd,
665 lv_crv_version_number,
666 lv_acad_cal_type,
667 appl_prog_rec.attendance_type,
668 lv_course_start_dt,
669 lv_expected_completion_yr,
670 lv_expected_completion_perd,
671 lv_completion_dt,
672 appl_prog_rec.attendance_mode,
673 appl_prog_rec.location_cd
674 );
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 lv_expected_completion_yr := NULL;
679 lv_expected_completion_perd := NULL;
680 lv_completion_dt := NULL;
681
682 END;
683
684
685 --
686 -- we use oss function to get class standing and predictive class standing
687 --
688
689 -- do not let any exceptions from this call
690
691 BEGIN
692
693 lv_class_standing := igs_pr_get_class_std.get_class_standing(
694 p_person_id,
695 enrl_std_rec.course_cd,
696 'N',
697 NULL,
698 NULL,
699 NULL);
700
701 lv_pred_class_standing := igs_pr_get_class_std.get_class_standing(
702 p_person_id,
703 enrl_std_rec.course_cd,
704 'Y',
705 NULL,
706 NULL,
707 NULL);
708
709
710
711 EXCEPTION
712 WHEN OTHERS THEN NULL;
713
714 END;
715
716
717 --Get the ahtletic count for the person id
718 OPEN cur_get_athletic_count;
719 FETCH cur_get_athletic_count INTO l_athletic_cnt;
720 CLOSE cur_get_athletic_count;
721
722 --Added this check as per the FACCR001 DLD Bug No:-2154941
723
724 IF NVL(l_athletic_cnt,0) > 1 THEN
725 l_athletic_m := 'Y';
726 ELSE
727 l_athletic_m := 'N';
728 END IF;
729
730 -- Select all the values into REF cursor
731
732 OPEN lv_oss_dtl_rec FOR
733 SELECT
734 adm_appl_rec.adm_appl_status,
735 adm_appl_rec.appl_status,
736 adm_appl_rec.appl_dt,
737 lv_class_standing, -- class_standing
738 lv_credit_pts,
739 adm_appl_rec.admission_cat,
740 adm_appl_rec.process_type,
741 appl_prog_rec.course_cd || ' ' || appl_prog_rec.crv_version_number,
742 appl_prog_rec.course_cd,
743 appl_prog_rec.crv_version_number,
744 appl_prog_rec.attendance_mode,
745 appl_prog_rec.attendance_type,
746 appl_prog_rec.adm_outcome_status,
747 appl_prog_rec.ou_status,
748 appl_prog_rec.decision_date,
749 appl_prog_rec.adm_offer_resp_status,
750 appl_prog_rec.offer_status,
751 appl_prog_rec.actual_response_dt,
752 adm_appl_rec.adm_fee_status,
753 adm_appl_rec.fee_status,
754 adm_appl_rec.spcl_grp_1, -- sp_program_1
755 adm_appl_rec.spcl_grp_2, -- sp_program_2
756 appl_prog_rec.entry_level, -- entry_level
757 lv_completion_dt,
758 appl_prog_rec.academic_index,
759 lv_adm_org_unit_cd,
760 appl_prog_rec.unit_set_cd || ' ' || appl_prog_rec.us_version_number,
761 appl_prog_rec.unit_set_cd,
762 appl_prog_rec.us_version_number,
763 enrl_std_rec.commencement_dt,
764 NULL, -- transfered
765 lv_multiple,
766 lv_atb,
767 enrl_dtl_rec.enrolled_term,
768 enrl_dtl_rec.enrl_load_cal_type,
769 enrl_dtl_rec.enrl_load_seq_num,
770 NULL, -- sap_evaluation_date
771 NULL, -- sap_selected_flag
772 lv_mult_prog_d,
773 enrl_std_rec.course_cd || ' ' || enrl_std_rec.version_number,
774 enrl_std_rec.course_cd,
775 enrl_std_rec.version_number,
776 en_std_rec.enrl_program_type,
777 l_uset_rec.unit_set_cd, -- enrl_unit_set
778 l_uset_rec.course_cd, -- enrl_uset_course_cd
779 l_uset_rec.us_version_number, -- enrl_uset_ver_num
780 enrl_std_rec.course_attempt_status,
781 lv_inst_attd,
782 lv_gpa_curr, -- current_gpa
783 lv_gpa_cum, -- cumulative_gpa
784 lv_earned_cp, -- acheived_cr_pts
785 lv_pred_class_standing, -- pred_class_standing
786 lv_enrl_org_unit_cd,
787 enrl_std_rec.attendance_mode,
788 enrl_std_rec.location_cd,
789 lv_passed_cp,
790 lv_adv_stnd_cp + lv_passed_cp, -- enrl_cuml_cp= enrl_cuml_trans_cp+ enrl_total_cp
791 lv_adv_stnd_cp,
792 lv_adm_appl_number
793 FROM DUAL;
794
795
796 EXCEPTION
797
798 WHEN OTHERS THEN
799
800 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
801 fnd_message.set_token('NAME','igf_ap_oss_intr_dtls.get_details');
802 igs_ge_msg_stack.add;
803
804 END get_details;
805
806 END igf_ap_oss_intr_dtls;