1 PACKAGE BODY IGF_AP_GEN_001 AS
2 /* $Header: IGFAP44B.pls 120.1 2005/11/07 01:53:12 appldev ship $ */
3
4 --Function to get Program Attempt Start Date
5 FUNCTION get_prog_att_start_dt(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
6 RETURN DATE
7 AS
8 /*
9 || Created By : rasahoo
10 || Created On : 26-AUG-2003
11 || Purpose : Function to get Program Attempt Start Date
12 || Known limitations, enhancements or remarks :
13 || Change History :
14 || Who When What
15 || (reverse chronological order - newest change first)
16 || sjalasut Dec 03, 2003 modified the cursor c_comm_date with the new
17 || procedure that gets the key program based on the
18 || award year. this key program will be used in calculating
19 || the course commencement date
20 */
21 CURSOR c_comm_date(cp_person_id hz_parties.party_id%TYPE, cp_course_cd igs_ps_ver_all.course_cd%TYPE,
22 cp_version_number igs_ps_ver_all.version_number%TYPE)IS
23 SELECT commencement_dt
24 FROM igs_en_stdnt_ps_att_all
25 WHERE course_cd = cp_course_cd
26 AND version_number = cp_version_number
27 AND person_id = cp_person_id;
28 l_comm_date c_comm_date%ROWTYPE;
29 l_person_id hz_parties.party_id%TYPE;
30 x_course_cd igs_ps_ver_all.course_cd%TYPE;
31 x_version_number igs_ps_ver_all.version_number%TYPE;
32 BEGIN
33 -- call igf_gr_gen.get_person_id to get the Person ID for the Base ID
34 l_person_id :=igf_gr_gen.get_person_id(cp_base_id);
35 -- get the key program from the get_key_program api
36 get_key_program(cp_base_id, x_course_cd, x_version_number);
37 -- get the course commencement date from the spa table. this date can be null !!!
38 OPEN c_comm_date(l_person_id, x_course_cd, x_version_number);
39 FETCH c_comm_date INTO l_comm_date;
40 CLOSE c_comm_date;
41 RETURN l_comm_date.commencement_dt;
42 EXCEPTION WHEN OTHERS THEN
43 RETURN NULL;
44 END get_prog_att_start_dt;
45
46
47 --Function for Anticipated Completion Date
48 FUNCTION get_anticip_compl_date(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
49 RETURN DATE
50 AS
51 /*
52 || Created By : rasahoo
53 || Created On : 26-AUG-2003
54 || Purpose : Function for Anticipated Completion Date
55 || Known limitations, enhancements or remarks :
56 || Change History :
57 || Who When What
58 || (reverse chronological order - newest change first)
59 */
60 -- Get the details of the Key Program
61 CURSOR key_program_dtl_cur(cp_person_id hz_parties.party_id%TYPE, cp_course_cd igs_ps_ver_all.course_cd%TYPE,
62 cp_version_number igs_ps_ver_all.version_number%TYPE) IS
63 SELECT course_cd, version_number, cal_type, location_cd, attendance_mode, attendance_type
64 FROM igs_en_stdnt_ps_att_all
65 WHERE course_cd = cp_course_cd
66 AND version_number = cp_version_number
67 AND person_id = cp_person_id;
68
69 l_key_program key_program_dtl_cur%ROWTYPE;
70
71 -- Get the calendar details passing the values obtained from above query
72 CURSOR adm_appl_cur(cp_person_id igs_ad_appl.person_id%TYPE,
73 cp_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,
74 cp_crv_version_number igs_ad_ps_appl_inst.crv_version_number%TYPE ,
75 cp_location_cd igs_ad_ps_appl_inst.location_cd%TYPE,
76 cp_attendance_mode igs_ad_ps_appl_inst.attendance_mode%TYPE,
77 cp_attendance_type igs_ad_ps_appl_inst.attendance_type%TYPE )
78 IS
79 SELECT adm.acad_cal_type, adm.adm_cal_type adm_cal_type, adm.adm_ci_sequence_number adm_ci_sequence_number,
80 acai.expected_completion_yr,acai.expected_completion_perd
81 FROM igs_ad_appl adm, igs_ad_ps_appl_inst acai
82 WHERE adm.person_id = acai.person_id
83 AND adm.admission_appl_number = acai.admission_appl_number
84 AND adm.person_id = cp_PERSON_ID
85 AND acai.course_cd = cp_COURSE_CD
86 AND acai.crv_version_number = cp_CRV_VERSION_NUMBER
87 AND acai.location_cd = cp_LOCATION_CD
88 AND acai.attendance_mode = cp_ATTENDANCE_MODE
89 AND acai.attendance_type = cp_ATTENDANCE_TYPE;
90 l_adm_appl adm_appl_cur%ROWTYPE;
91 l_person_id VARCHAR2(20);
92 lv_course_start_dt DATE;
93 l_completion_dt DATE;
94 cp_term_enr_dtl_rec igs_en_spa_terms%ROWTYPE;
95 x_course_cd igs_ps_ver_all.course_cd%TYPE;
96 x_version_number igs_ps_ver_all.version_number%TYPE;
97
98 CURSOR c_spa( cp_person_id hz_parties.party_id%TYPE,
99 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
100 cp_version_number igs_en_stdnt_ps_att.version_number%TYPE
101 )IS
102 SELECT commencement_dt
103 FROM igs_en_stdnt_ps_att
104 WHERE person_id = cp_person_id
105 AND COURSE_CD = cp_course_cd
106 AND VERSION_NUMBER = cp_version_number;
107
108
109
110 BEGIN
111 -- Call IGF_GR_GEN.GET_PERSON_ID to get the Person ID for the Base ID
112 l_person_id :=igf_gr_gen.get_person_id(cp_base_id);
113
114 -- get the key program from the get_key_program api
115 get_key_program(cp_base_id, x_course_cd, x_version_number);
116
117 OPEN key_program_dtl_cur(l_person_id,x_course_cd, x_version_number);
118 FETCH key_program_dtl_cur INTO l_key_program; CLOSE key_program_dtl_cur;
119
120 OPEN adm_appl_cur(l_person_id ,l_key_program.course_cd,l_key_program.version_number,l_key_program.location_cd,
121 l_key_program.attendance_mode,l_key_program.attendance_type);
122 FETCH adm_appl_cur INTO l_adm_appl;
123 CLOSE adm_appl_cur;
124
125 OPEN c_spa (l_person_id, x_course_cd, x_version_number);
126 FETCH c_spa INTO lv_course_start_dt;
127 CLOSE c_spa;
128
129 -- If there is no enrollment rec for the student use Admissions data to get the start date.
130 IF lv_course_start_dt IS NULL THEN -- Enrollment Start Date Check
131 lv_course_start_dt := igs_ad_gen_005.admp_get_crv_strt_dt(l_adm_appl.adm_cal_type, l_adm_appl.adm_ci_sequence_number);
132 END IF; -- End Enrollment Start Date Check
133 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN -- Log Level Check
134 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_anticip_compl_date.debug',
135 '| start date ' || to_char(lv_course_start_dt, 'mm/dd/yyyy')||
136 '| l_person_id ' || l_person_id ||
137 '| x_course_cd ' || x_course_cd ||
138 '| l_key_program.course_cd ' || l_key_program.course_cd ||
139 '| l_key_program.version_number ' || l_key_program.version_number ||
140 '| acad_cal_type ' || l_adm_appl.acad_cal_type ||
141 '| l_key_program.cal_type ' || l_key_program.cal_type ||
142 '| l_key_program.attendance_type ' || l_key_program.attendance_type ||
143 '| lv_course_start_dt ' || lv_course_start_dt ||
144 '| l_adm_appl.expected_completion_yr ' || l_adm_appl.expected_completion_yr ||
145 '| l_adm_appl.expected_completion_perd ' || l_adm_appl.expected_completion_perd ||
146 '| l_completion_dt ' || l_completion_dt ||
147 '| l_key_program.attendance_mode ' || l_key_program.attendance_mode ||
148 '| l_key_program.location_cd ' || l_key_program.location_cd ||
149 '| l_key_program.attendance_type - ' || l_key_program.attendance_type
150 );
151 END IF; -- End Log Level Check
152
153
154 igs_ad_gen_004.admp_get_crv_comp_dt(l_key_program.course_cd,
155 l_key_program.version_number,
156 NVL(l_key_program.cal_type, l_adm_appl.acad_cal_type),
157 l_key_program.attendance_type,
158 lv_course_start_dt,
159 l_adm_appl.expected_completion_yr,
160 l_adm_appl.expected_completion_perd,
161 l_completion_dt,
162 l_key_program.attendance_mode,
163 l_key_program.location_cd
164 );
165 RETURN l_completion_dt;
166 EXCEPTION WHEN OTHERS THEN
167 RETURN NULL;
168 END get_anticip_compl_date;
169
170
171 --Function to get Class Standing
172 FUNCTION get_class_standing(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
173 RETURN VARCHAR2
174 AS
175 /*
176 || Created By : rasahoo
177 || Created On : 26-AUG-2003
178 || Purpose : Function to get Class Standing
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184 l_person_id hz_parties.party_id%TYPE;
185 lv_class_standing igs_pr_class_std.class_standing%TYPE;
186 x_key_program_course_cd igs_ps_ver_all.course_cd%TYPE;
187 x_version_number igs_ps_ver_all.version_number%TYPE;
188 BEGIN
189 l_person_id :=igf_gr_gen.get_person_id(cp_base_id);
190 -- get the key program from the get_key_program api
191 get_key_program(cp_base_id, x_key_program_course_cd, x_version_number);
192 lv_class_standing := igs_pr_get_class_std.get_class_standing(l_person_id,
193 x_key_program_course_cd,
197 NULL);
194 'N',
195 NULL,
196 NULL,
198 RETURN lv_class_standing;
199 EXCEPTION WHEN OTHERS THEN
200 RETURN NULL;
201 END get_class_standing;
202
203 --Function to get Program Type
204 FUNCTION get_enrl_program_type(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
205 RETURN VARCHAR2
206 AS
207 /*
208 || Created By : rasahoo
209 || Created On : 26-AUG-2003
210 || Purpose : Function to get Program Type
211 || Known limitations, enhancements or remarks :
212 || Change History :
213 || Who When What
214 || (reverse chronological order - newest change first)
215 */
216 -- Get the Program Type
217 CURSOR Program_Type_Cur(cp_course_cd VARCHAR2,
218 cp_version_number NUMBER)IS
219 SELECT course_type enrl_program_type
220 FROM igs_ps_ver
221 WHERE course_cd = cp_course_cd
222 AND version_number = cp_version_number;
223 l_Program_Type Program_Type_Cur%ROWTYPE;
224 l_person_id hz_parties.party_id%TYPE;
225 x_key_program_course_cd igs_ps_ver_all.course_cd%TYPE;
226 x_version_number igs_ps_ver_all.version_number%TYPE;
227 BEGIN
228 l_person_id :=igf_gr_gen.get_person_id(cp_base_id);
229 -- get the key program from the get_key_program api
230 get_key_program(cp_base_id, x_key_program_course_cd, x_version_number);
231 -- Get the Program Type
232 OPEN Program_Type_Cur(x_key_program_course_cd,x_version_number);
233 FETCH Program_Type_Cur INTO l_Program_Type;
234 CLOSE Program_Type_Cur;
235 RETURN l_Program_Type.enrl_program_type;
236 EXCEPTION WHEN OTHERS THEN
237 RETURN NULL;
238 END get_enrl_program_type;
239
240 --Function to get Key Program
241 PROCEDURE get_key_program(cp_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
242 cp_course_cd OUT NOCOPY VARCHAR2,
243 cp_version_number OUT NOCOPY NUMBER)AS
244 /*
245 || Created By : rasahoo
246 || Created On : 26-AUG-2003
247 || Purpose : Function to get Key Program
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || (reverse chronological order - newest change first)
252 || sjalasut Dec 03, 2003 removed the cursor key_program_cur
253 || and replaced with the get_term_enrlmnt_dtl to get the
254 || key program
255 */
256 cp_term_enr_dtl_rec igs_en_spa_terms%ROWTYPE;
257 BEGIN
258 -- call get_term_enrlmnt_dtl so that based on the base_id it calculates the term and
259 -- gets the key program from that term (if any) otherwise returns the key program from the spa
260 get_term_enrlmnt_dtl(cp_base_id,cp_term_enr_dtl_rec);
261 cp_course_cd := cp_term_enr_dtl_rec.program_cd;
262 cp_version_number := cp_term_enr_dtl_rec.program_version;
263 EXCEPTION WHEN OTHERS THEN
264 cp_course_cd := NULL;
265 cp_version_number := NULL;
266 END get_key_program;
267
268 --Procedure to get the applicable enrollment term details.
269 PROCEDURE get_term_enrlmnt_dtl(cp_fa_base_id IN IGF_AP_FA_BASE_REC_ALL.BASE_ID%TYPE,
270 cp_term_enr_dtl_rec OUT NOCOPY IGS_EN_SPA_TERMS%ROWTYPE) IS
271 /*
272 || Created By : sjalasut
273 || Created On : 03 Dec 2003
274 || Purpose : Function to get applicable enrollment details
275 || - For a past award year, the last term (subordinate) is considered for processing
276 || - For the current award year, the current term (based on sysdate) is considered
277 || - For a future Award Year, the first term under that Award Year is considered
278 ||
279 || Known limitations, enhancements or remarks :
280 || Change History :
281 || Who When What
282 || (reverse chronological order - newest change first)
283 */
284 CURSOR c_fa_base_rec(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)IS
285 SELECT fa.ci_cal_type, fa.ci_sequence_number, fa.person_id, ci.start_dt, ci.end_dt
286 FROM igf_ap_fa_base_rec_all fa, igs_ca_inst ci
287 WHERE fa.BASE_ID = cp_base_id and
288 fa.ci_cal_type = ci.cal_type and
289 fa.ci_sequence_number = ci.sequence_number;
290 l_base_rec C_FA_BASE_REC%ROWTYPE;
291
292 CURSOR c_ld_calendars_end (cp_aw_cal_type igs_ca_inst.cal_type%TYPE,
293 cp_aw_seq_no igs_ca_inst.sequence_number%TYPE) IS
294 SELECT ci.cal_type enrl_load_cal_type,
295 ci.sequence_number enrl_load_seq_num ,
296 ci.alternate_code terms,
297 TRUNC(NVL(get_enr_eff_dt_alias_val(ci.cal_type,ci.sequence_number),ci.start_dt)) enrolled_start_dt,
298 TRUNC(ci.end_dt) enrolled_end_dt
299 FROM
300 igs_ca_inst ci,
301 igs_ca_type cty
302 WHERE cty.s_cal_cat = 'LOAD'
303 AND cty.cal_type = ci.cal_type
304 AND (ci.cal_type, ci.sequence_number)IN
305 (SELECT sup_cal_type,
306 sup_ci_sequence_number
307 FROM igs_ca_inst_rel
308 WHERE sub_cal_type = cp_aw_cal_type
309 AND sub_ci_sequence_number = cp_aw_seq_no
310 UNION
311 SELECT sub_cal_type,
312 sub_ci_sequence_number
313 FROM igs_ca_inst_rel
314 WHERE sup_cal_type = cp_aw_cal_type
315 AND sup_ci_sequence_number = cp_aw_seq_no
316 )
317 ORDER BY enrolled_end_dt DESC;
318
319 CURSOR c_ld_calendars_start(cp_aw_cal_type igs_ca_inst.cal_type%TYPE,
320 cp_aw_seq_no igs_ca_inst.sequence_number%TYPE)IS
321 SELECT ci.cal_type enrl_load_cal_type,
322 ci.sequence_number enrl_load_seq_num ,
323 ci.alternate_code terms,
324 TRUNC(NVL(get_enr_eff_dt_alias_val(ci.cal_type,ci.sequence_number),ci.start_dt)) enrolled_start_dt,
325 TRUNC(ci.end_dt) enrolled_end_dt
326 FROM
327 igs_ca_inst ci,
328 igs_ca_type cty
329 WHERE cty.s_cal_cat = 'LOAD'
330 AND cty.cal_type = ci.cal_type
331 AND (ci.cal_type, ci.sequence_number)IN
332 (SELECT sup_cal_type,
333 sup_ci_sequence_number
334 FROM igs_ca_inst_rel
335 WHERE sub_cal_type = cp_aw_cal_type
336 AND sub_ci_sequence_number = cp_aw_seq_no
337 UNION
338 SELECT sub_cal_type,
339 sub_ci_sequence_number
340 FROM igs_ca_inst_rel
341 WHERE sup_cal_type = cp_aw_cal_type
342 AND sup_ci_sequence_number = cp_aw_seq_no
343 )
344 ORDER BY enrolled_start_dt;
345
346 CURSOR c_spa_terms( cp_person_id hz_parties.party_id%TYPE, cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_no igs_ca_inst.sequence_number%TYPE)IS
347 SELECT spa.*
348 FROM IGS_EN_SPA_TERMS spa,
349 (SELECT su.person_id,
350 su.course_cd,
351 su.version_number,
352 tl.load_cal_type,
353 tl.load_ci_sequence_number
354 FROM igs_en_su_attempt su,
355 igs_ca_teach_to_load_v tl
356 WHERE su.cal_type = tl.teach_cal_type
357 AND su.ci_sequence_number = tl.teach_ci_sequence_number) unit_attempt
358 WHERE spa.person_id = cp_person_id
359 AND spa.term_cal_type = cp_cal_type
360 AND spa.term_sequence_number = cp_seq_no
361 AND spa.key_program_flag = 'Y'
362 AND unit_attempt.person_id = spa.person_id
363 AND unit_attempt.course_cd = spa.program_cd
364 AND unit_attempt.version_number = spa.program_version
365 AND unit_attempt.load_cal_type = spa.term_cal_type
366 AND unit_attempt.load_ci_sequence_number = spa.term_sequence_number;
367 l_term_rec c_ld_calendars_start%ROWTYPE;
368
369 CURSOR c_spa( cp_person_id hz_parties.party_id%TYPE)IS
370 SELECT *
371 FROM igs_en_stdnt_ps_att
372 WHERE person_id = cp_person_id
373 AND key_program = 'Y';
374 l_spa_rec C_SPA%ROWTYPE;
375
376 l_person_id igs_pe_person_base_v.person_id%TYPE;
377
378 BEGIN
379 -- For the person in the award year loop. is this loop required ?
380 FOR l_base_rec IN C_FA_BASE_REC(cp_fa_base_id) LOOP
381 IF TRUNC(SYSDATE) BETWEEN TRUNC(l_base_rec.start_dt) AND TRUNC(l_base_rec.End_dt) THEN
382 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
386 -- the award year is current award year. get the current term based on sysdate and look into the term details
383 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
384 'sysdate between base record start date '||l_base_rec.start_dt||' and base record end date '|| l_base_rec.End_dt);
385 END IF;
387 FOR l_term_rec in c_ld_calendars_start(l_base_rec.ci_cal_type, l_base_rec.ci_sequence_number) LOOP
388 IF TRUNC(SYSDATE) BETWEEN l_term_rec.enrolled_start_dt AND l_term_rec.enrolled_end_dt THEN
389 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
390 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
391 'sysdate between term start date '||l_term_rec.enrolled_start_dt||' and term end date '|| l_term_rec.enrolled_end_dt);
392 END IF;
393 FOR x_term_enr_dtl_rec IN c_spa_terms(l_base_rec.person_id,l_term_rec.enrl_load_cal_type,l_term_rec.enrl_load_seq_num)LOOP
394 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
395 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
396 'term record for person id '||l_base_rec.person_id||' enr load cal type '|| l_term_rec.enrl_load_cal_type || 'load seq number '||l_term_rec.enrl_load_seq_num);
397 END IF;
398 cp_term_enr_dtl_rec := x_term_enr_dtl_rec;
399 RETURN;
400 END LOOP;
401 END IF;
402 END LOOP;
403
404 -- if the end date of the award year is past. i.e. < sysdate then process the term cals desc
405 ELSIF TRUNC(l_base_rec.End_dt) < TRUNC(sysdate) THEN
406 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
407 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
408 'sysdate past base record end date '||l_base_rec.start_dt||' and base record end date '|| l_base_rec.End_dt);
409 END IF;
410
411 FOR l_term_rec in c_ld_calendars_end(l_base_rec.ci_cal_type, l_base_rec.ci_sequence_number) LOOP
412 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
413 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
414 ' term start date for past awd year'||l_term_rec.enrolled_start_dt||' and term end date for past awd year'|| l_term_rec.enrolled_end_dt);
415 END IF;
416
417 FOR x_term_enr_dtl_rec IN c_spa_terms(l_base_rec.person_id,l_term_rec.enrl_load_cal_type,l_term_rec.enrl_load_seq_num) LOOP
418 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
419 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
420 'past term record for person id '||l_base_rec.person_id||' enr load cal type '|| l_term_rec.enrl_load_cal_type || 'load seq number '||l_term_rec.enrl_load_seq_num);
421 END IF;
422 cp_term_enr_dtl_rec := x_term_enr_dtl_rec;
423 RETURN;
424 END LOOP;
425 END LOOP;
426 -- if the award year is a future award year then process the term cals asc
427 ELSIF TRUNC(l_base_rec.start_dt) > TRUNC(sysdate) THEN
428 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
429 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
430 'sysdate before base record end date '||l_base_rec.start_dt||' and base record end date '|| l_base_rec.End_dt);
431 END IF;
432 FOR l_term_rec in c_ld_calendars_start(l_base_rec.ci_cal_type, l_base_rec.ci_sequence_number) LOOP
433 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
434 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
435 'future term record for person id '||l_base_rec.person_id||' enr load cal type '|| l_term_rec.enrl_load_cal_type || 'load seq number '||l_term_rec.enrl_load_seq_num);
436 END IF;
437 FOR x_term_enr_dtl_rec IN c_spa_terms(l_base_rec.person_id,l_term_rec.enrl_load_cal_type,l_term_rec.enrl_load_seq_num) LOOP
438 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
439 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
440 'future term record for person id '||l_base_rec.person_id||' enr load cal type '|| l_term_rec.enrl_load_cal_type || 'load seq number '||l_term_rec.enrl_load_seq_num);
441 END IF;
442 cp_term_enr_dtl_rec := x_term_enr_dtl_rec;
443 RETURN;
444 END LOOP;
445 END LOOP;
446 END IF;
447 -- the control reached here. so no term records for the load calendars. peep into the prog att table directly and return the values
448 l_person_id := igf_gr_Gen.get_person_id(cp_fa_base_id);
449 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
450 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_gen_001.get_term_dtl.debug',
451 'no term record details available for '||cp_fa_base_id);
452 END IF;
453 OPEN c_spa (l_person_id);
454 FETCH c_spa INTO l_spa_rec;
455 IF c_spa%FOUND THEN
456 cp_term_enr_dtl_rec.person_id := l_spa_rec.person_id;
457 cp_term_enr_dtl_rec.program_cd := l_spa_rec.course_cd;
458 cp_term_enr_dtl_rec.program_version := l_spa_rec.version_number;
459 cp_term_enr_dtl_rec.acad_cal_type := l_spa_rec.cal_type;
460 cp_term_enr_dtl_rec.term_cal_type := NULL;
461 cp_term_enr_dtl_rec.term_sequence_number := NULL;
462 cp_term_enr_dtl_rec.key_program_flag := l_spa_rec.key_program;
463 cp_term_enr_dtl_rec.location_cd := l_spa_rec.location_cd;
464 cp_term_enr_dtl_rec.attendance_mode := l_spa_rec.attendance_mode;
468 cp_term_enr_dtl_rec.class_standing_id := l_spa_rec.igs_pr_class_std_id;
465 cp_term_enr_dtl_rec.attendance_type := l_spa_rec.attendance_type;
466 cp_term_enr_dtl_rec.fee_cat := l_spa_rec.fee_cat;
467 cp_term_enr_dtl_rec.coo_id := l_spa_rec.coo_id;
469 CLOSE c_spa;
470 RETURN;
471 END IF;
472 CLOSE c_spa;
473 END LOOP;
474 END get_term_enrlmnt_dtl;
475
476 --Function to get the end date alias value from the Date alias instances table
477 FUNCTION get_enr_eff_dt_alias_val (cp_cal_type IN igs_Ca_inst.cal_type%TYPE,
478 cp_sequence_number IN igs_ca_inst.sequence_number%TYPE)RETURN DATE IS
479 CURSOR c_min_date IS
480 SELECT TRUNC(MIN(daiv.alias_val)) enrolled_start_dt
481 FROM igs_ca_da_inst_v daiv,
482 igs_en_cal_conf secc
483 WHERE
484 secc.s_control_num = 1 AND
485 daiv.cal_type = cp_cal_type AND
486 daiv.ci_sequence_number = cp_sequence_number AND
487 daiv.dt_alias = secc.load_effect_dt_alias;
488 c_min_date_rec c_min_date%ROWTYPE;
489 BEGIN
490 OPEN c_min_date; FETCH c_min_date INTO c_min_date_rec; CLOSE c_min_date;
491 RETURN c_min_date_rec.enrolled_start_dt;
492 END get_enr_eff_dt_alias_val;
493
494 PROCEDURE get_context_data_for_term(
495 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
496 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
497 p_ld_sequence_number IN igs_ca_inst.sequence_number%TYPE,
498 p_program_cd OUT NOCOPY igs_ps_ver_all.course_cd%TYPE,
499 p_version_num OUT NOCOPY igs_ps_ver_all.version_number%TYPE,
500 p_program_type OUT NOCOPY igs_ps_ver_all.course_type%TYPE,
501 p_org_unit OUT NOCOPY igs_ps_ver_all.responsible_org_unit_cd%TYPE
502 ) IS
503 ------------------------------------------------------------------
504 --Created by : ssanyal
505 --Date created: 29-Oct-2004
506 --
507 --Purpose:
508 --
509 --
510 --Known limitations/enhancements and/or remarks:
511 --
512 --Change History:
513 --Who When What
514 --veramach 29-Oct-2004 Plugged in logic to check profile value before using anticipated values
515 -------------------------------------------------------------------
516
517 CURSOR c_fa_base_rec(
518 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
519 ) IS
520 SELECT fa.person_id
521 FROM igf_ap_fa_base_rec_all fa
522 WHERE fa.base_id = cp_base_id ;
523 l_fa_base_rec c_fa_base_rec%ROWTYPE;
524
525 CURSOR c_spa_terms(
526 cp_person_id hz_parties.party_id%TYPE,
527 cp_cal_type igs_ca_inst.cal_type%TYPE,
528 cp_seq_no igs_ca_inst.sequence_number%TYPE
529 ) IS
530 SELECT *
531 FROM igs_en_spa_terms
532 WHERE person_id = cp_person_id
533 AND term_cal_type = cp_cal_type
534 AND term_sequence_number = cp_seq_no
535 AND key_program_flag = 'Y';
536 l_spa_terms c_spa_terms%ROWTYPE;
537
538 CURSOR c_prog_dtls(
539 cp_program_cd igs_ps_ver_all.course_cd%TYPE,
540 cp_version_num igs_ps_ver_all.version_number%TYPE
541 )IS
542 SELECT *
543 FROM igs_ps_ver_all
544 WHERE course_cd = cp_program_cd
545 AND version_number = NVL(cp_version_num,version_number);
546 l_prog_dtls c_prog_dtls%ROWTYPE;
547
548 CURSOR c_anticipated_val(
549 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
550 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
551 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
552 ) IS
553 SELECT *
554 FROM igf_ap_fa_ant_data
555 WHERE base_id = cp_base_id
556 AND ld_cal_type = cp_ld_cal_type
557 AND ld_sequence_number = cp_ld_sequence_number;
558 l_anticipated_val c_anticipated_val%ROWTYPE;
559
560 BEGIN
561 --Get the Person ID from Base Record
562 OPEN c_fa_base_rec(p_base_id);
563 FETCH c_fa_base_rec INTO l_fa_base_rec;
564 IF c_fa_base_rec%NOTFOUND THEN
565 CLOSE c_fa_base_rec;
566 RETURN; --- Return Back base record not present
567 ELSE
568 CLOSE c_fa_base_rec;
569 END IF;
570
571 -- Get the Details from the Term Record
572 OPEN c_spa_terms(l_fa_base_rec.person_id,p_ld_cal_type,p_ld_sequence_number);
573 FETCH c_spa_terms INTO l_spa_terms;
574 IF c_spa_terms%FOUND THEN
575 CLOSE c_spa_terms;
576 ELSE
577 -- Term Record Does Not exist check in Antiicpated Values
578 --anticipated values can be used only if the profile is set
579 IF igf_aw_coa_gen.canUseAnticipVal THEN
580 OPEN c_anticipated_val(p_base_id,p_ld_cal_type,p_ld_sequence_number);
581 FETCH c_anticipated_val INTO l_anticipated_val;
582 IF c_anticipated_val%FOUND THEN
583 CLOSE c_anticipated_val;
584 IF l_anticipated_val.program_cd IS NOT NULL THEN
585 -- Since the Program Coe is Present in Anticipated Values use it , derive the other contexts from the Program Version setup
586 l_spa_terms.program_cd := l_anticipated_val.program_cd;
587 ELSE
591 p_program_type := l_anticipated_val.program_type;
588 -- Since the Program Code is not present in anticipated valeus get the Org unit and the Program type if present
589 p_program_cd := NULL;
590 p_version_num := NULL;
592 p_org_unit := l_anticipated_val.org_unit_cd;
593 RETURN; -- Return Back
594 END IF;
595 ELSE
596 CLOSE c_anticipated_val;
597 p_program_cd := NULL;
598 p_version_num := NULL;
599 p_program_type := NULL;
600 p_org_unit := NULL;
601 RETURN; -- Return Back with all null values
602 END IF;
603 ELSE
604 --cannot use anticipated values
605 p_program_cd := NULL;
606 p_version_num := NULL;
607 p_program_type := NULL;
608 p_org_unit := NULL;
609 RETURN; -- Return Back with all null values
610 END IF;
611 END IF;
612
613 OPEN c_prog_dtls(l_spa_terms.program_cd,l_spa_terms.program_version);
614 FETCH c_prog_dtls INTO l_prog_dtls;
615 IF c_prog_dtls%FOUND THEN
616 CLOSE c_prog_dtls;
617 p_program_cd := l_prog_dtls.course_cd;
618 p_version_num := l_prog_dtls.version_number;
619 p_program_type := l_prog_dtls.course_type;
620 p_org_unit := l_prog_dtls.responsible_org_unit_cd;
621 RETURN; --Return Back
622 ELSE
623 CLOSE c_prog_dtls;
624 p_program_cd := l_spa_terms.program_cd;
625 p_version_num := l_spa_terms.program_version;
626 RETURN; -- Return Back no program Setup found;
627 END IF;
628 END get_context_data_for_term;
629
630
631 PROCEDURE get_term_dates(
632 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
633 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
634 p_ld_sequence_number IN igs_ca_inst.sequence_number%TYPE,
635 p_ld_start_date OUT NOCOPY DATE,
636 p_ld_end_date OUT NOCOPY DATE
637 ) IS
638 ------------------------------------------------------------------
639 --Created by : ssanyal
640 --Date created: 29-Oct-2004
641 --
642 --Purpose:
643 --
644 --
645 --Known limitations/enhancements and/or remarks:
646 --
647 --Change History:
648 --Who When What
649 --veramach 01-Nov-2004 Fixed issues with parameter order
650 --veramach 19-Nov-2004 Fixed issues with passing program version
651 -------------------------------------------------------------------
652 lc_program_cd igs_ps_ver_all.course_cd%TYPE;
653 lc_version_num igs_ps_ver_all.version_number%TYPE;
654 lc_program_type igs_ps_ver_all.course_type%TYPE;
655 lc_org_unit igs_ps_ver_all.responsible_org_unit_cd%TYPE;
656
657 BEGIN
658 get_context_data_for_term(
659 p_base_id,
660 p_ld_cal_type,
661 p_ld_sequence_number,
662 lc_program_cd,
663 lc_version_num,
664 lc_program_type,
665 lc_org_unit
666 );
667
668 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
669 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_gen_001.get_term_dates.debug','get_context_data_for_term -> p_ld_cal_type/p_ld_sequence_number/lc_org_unit/lc_program_type/lc_program_cd/lc_version_num ->'
670 || '<>' || p_ld_cal_type || '<>' || p_ld_sequence_number || '<>' || lc_org_unit || '<>'
671 || lc_program_type || '<>' || lc_program_cd || '<>' || lc_version_num);
672 END IF;
673
674 p_ld_start_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
675 'FIRST_DAY_TERM',
676 p_ld_cal_type,
677 p_ld_sequence_number,
678 lc_org_unit,
679 lc_program_type,
680 lc_program_cd || '/' || lc_version_num
681 );
682
683 p_ld_end_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
684 'LAST_DAY_TERM',
685 p_ld_cal_type,
686 p_ld_sequence_number,
687 lc_program_type,
688 lc_org_unit,
689 lc_program_cd || '/' || lc_version_num
690 );
691
692 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
693 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_gen_001.get_term_dates.debug','prms to igs_ca_compute_da_val_pkg.cal_da_elt_val() -> FIRST_DAY_TERM<>'
694 || '<>' || p_ld_cal_type || '<>' || p_ld_sequence_number || '<>' || lc_org_unit || '<>' || lc_program_type || '<>'
695 || lc_program_cd || '/' || lc_version_num);
696 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_gen_001.get_term_dates.debug','ret val of igs_ca_compute_da_val_pkg.cal_da_elt_val() - p_ld_start_date -> '
697 || TO_CHAR(p_ld_start_date));
698 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_gen_001.get_term_dates.debug','prms to igs_ca_compute_da_val_pkg.cal_da_elt_val() -> LAST_DAY_TERM<>'
702 || TO_CHAR(p_ld_end_date));
699 || '<>' || p_ld_cal_type || '<>' || p_ld_sequence_number || '<>' || lc_org_unit || '<>' || lc_program_type || '<>'
700 || lc_program_cd || '/' || lc_version_num);
701 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_gen_001.get_term_dates.debug','ret val of igs_ca_compute_da_val_pkg.cal_da_elt_val() - p_ld_end_date ->'
703 END IF;
704
705 END get_term_dates;
706
707
708 FUNCTION get_date_alias_val(
709 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
710 p_cal_type IN igs_ca_inst.cal_type%TYPE,
711 p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
712 p_date_alias IN igs_ca_da_inst.dt_alias%TYPE
713 ) RETURN DATE IS
714 ------------------------------------------------------------------
715 --Created by : ssanyal
716 --Date created: 29-Oct-2004
717 --
718 --Purpose:
719 --
720 --
721 --Known limitations/enhancements and/or remarks:
722 --
723 --Change History:
724 --Who When What
725 --veramach 19-Nov-2004 Fixed issues with passing program version
726 -------------------------------------------------------------------
727 CURSOR cur_get_dt(
728 cp_dt_alias igs_ca_da_inst.dt_alias%TYPE,
729 cp_cal_type igs_ca_da_inst.cal_type%TYPE,
730 cp_ci_sequence_number igs_ca_da_inst.ci_sequence_number%TYPE
731 )IS
732 SELECT dainst.*
733 FROM igs_ca_da_inst dainst
734 WHERE dainst.dt_alias = cp_dt_alias
735 AND dainst.cal_type = cp_cal_type
736 AND dainst.ci_sequence_number = cp_ci_sequence_number;
737 lc_dt_inst cur_get_dt%ROWTYPE;
738
739 lc_derived_val DATE;
740 lc_program_cd igs_ps_ver_all.course_cd%TYPE;
741 lc_version_num igs_ps_ver_all.version_number%TYPE;
742 lc_program_type igs_ps_ver_all.course_type%TYPE;
743 lc_org_unit igs_ps_ver_all.responsible_org_unit_cd%TYPE;
744
745 BEGIN
746 get_context_data_for_term(
747 p_base_id,
748 p_cal_type,
749 p_sequence_number,
750 lc_program_cd,
751 lc_version_num,
752 lc_program_type,
753 lc_org_unit
754 );
755 -- Get the Date Instance for the give data alias with respect to the Calendar Type and Sequence Number
756 OPEN cur_get_dt(p_date_alias,p_cal_type,p_sequence_number) ;
757 FETCH cur_get_dt INTO lc_dt_inst;
758
759 IF cur_get_dt%FOUND THEN
760 CLOSE cur_get_dt;
761 lc_derived_val := igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val(
762 p_date_alias,
763 lc_dt_inst.sequence_number,
764 p_cal_type,
765 p_sequence_number,
766 lc_org_unit,
767 lc_program_type,
768 lc_program_cd || '/' || lc_version_num
769 );
770 -- If the calendar API returns the Derived value for the given calendar Instance
771 -- then use this else use obsolute value defined at Calendar Level
772 IF lc_derived_val IS NOT NULL THEN
773 RETURN lc_derived_val;
774 ELSE
775 RETURN lc_dt_inst.absolute_val;
776 END IF;
777 ELSE
778 CLOSE cur_get_dt;
779 RETURN NULL;
780 END IF;
781 END get_date_alias_val;
782
783
784 END IGF_AP_GEN_001;
[Home] [Help]