1 PACKAGE BODY IGS_EN_SPA_TERMS_API AS
2 /* $Header: IGSENB1B.pls 120.14 2005/11/28 02:26:46 appldev noship $ */
3
4 CURSOR c_term_exists(cp_person_id IGS_PE_PERSON.person_id%TYPE,
5 cp_program_cd IGS_PS_VER.course_cd%TYPE,
6 cp_program_version IGS_PS_VER.version_number%TYPE,
7 cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
8 cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
9 SELECT spat.term_record_id
10 FROM IGS_EN_SPA_TERMS spat, igs_ps_ver cv1
11 WHERE spat.person_id = cp_person_id
12 AND spat.term_cal_type = cp_term_cal_type
13 AND spat.term_sequence_number = cp_term_sequence_number
14 AND cv1.course_cd = spat.program_cd
15 AND cv1.version_number = spat.program_version
16 AND
17 ( (
18 NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y'
19 AND cv1.course_type = (SELECT cv2.course_type
20 FROM IGS_PS_VER cv2
21 WHERE cv2.course_cd = cp_program_cd
22 AND cv2.version_number = cp_program_version)
23 )
24 OR
25 ( NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N'
26 AND spat.program_cd = cp_program_cd
27 )
28 );
29
30 PROCEDURE set_spa_term_cal_type(p_spa_term_cal_type IN VARCHAR2)
31 AS
32 BEGIN
33 g_spa_term_cal_type := p_spa_term_cal_type;
34 END;
35
36 PROCEDURE set_spa_term_sequence_number(p_spa_term_sequence_number IN NUMBER)
37 AS
38 BEGIN
39 g_spa_term_sequence_number := p_spa_term_sequence_number;
40 END;
41
42 PROCEDURE validate_term_rec(p_term_rec EN_SPAT_REC_TYPE%TYPE) AS
43
44 -- Check if the term calendar is subordinate to the acad cal type
45 -- as defined in its term record
46 cursor c_term_acad IS
47 SELECT 'x'
48 FROM igs_ca_inst_rel
49 WHERE sub_cal_type = p_term_rec.term_cal_type
50 AND sub_ci_sequence_number = p_term_rec.term_sequence_number
51 AND sup_cal_type = p_term_rec.acad_cal_type;
52
53 -- Check if the program offering option with the values specified
54 -- in term record exists
55 cursor c_coo_valid IS
56 SELECT 'x'
57 FROM igs_ps_ofr_opt
58 WHERE coo_id = p_term_rec.coo_id
59 AND location_cd = p_term_rec.location_cd
60 AND version_number = p_term_rec.program_version
61 AND attendance_type = p_term_rec.attendance_type
62 AND attendance_mode = p_term_rec.attendance_mode
63 AND cal_type = p_term_rec.acad_cal_type
64 AND course_cd = p_term_rec.program_cd;
65
66 l_dummy VARCHAR2(1);
67 BEGIN
68 OPEN c_term_acad;
69 FETCH c_term_acad INTO l_dummy;
70 IF (c_term_acad%NOTFOUND) THEN
71 CLOSE c_term_acad;
72
73 FND_MESSAGE.SET_NAME('IGS','IGS_EN_TERM_VALID_FAILED');
74 IGS_GE_MSG_STACK.ADD;
75 APP_EXCEPTION.RAISE_EXCEPTION;
76 END IF;
77 CLOSE c_term_acad;
78 OPEN c_coo_valid;
79 FETCH c_coo_valid INTO l_dummy;
80 IF (c_coo_valid%NOTFOUND) THEN
81 CLOSE c_coo_valid;
82 FND_MESSAGE.SET_NAME('IGS','IGS_EN_TERM_VALID_FAILED');
83 IGS_GE_MSG_STACK.ADD;
84 APP_EXCEPTION.RAISE_EXCEPTION;
85 END IF;
86 CLOSE c_coo_valid;
87
88 END validate_term_rec;
89
90
91 FUNCTION find_key_effective_for(p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
92 p_term_cal_type IN igs_ca_inst.cal_type%TYPE,
93 p_term_sequence_number IN igs_ca_inst.sequence_number%TYPE) RETURN VARCHAR2
94 AS
95 -- Check if key term record exists for the passed in term calendar
96 cursor c_key_record_exists IS
97 select program_cd from igs_en_spa_terms where person_id = p_person_id
98 and key_program_flag = 'Y'
99 and term_cal_type = p_term_cal_type
100 and term_sequence_number = p_term_sequence_number;
101
102 -- Check the oldest key term record in terms table
103 CURSOR c_oldest_term IS
104 SELECT program_cd, acad_cal_type
105 FROM igs_en_spa_terms spat, igs_ca_inst ca1
106 WHERE person_id = p_person_id
107 AND spat.term_cal_type = ca1.cal_type
108 AND spat.term_sequence_number = ca1.sequence_number
109 AND spat.key_program_flag = 'Y'
110 ORDER BY ca1.start_dt ASC;
111
112 -- Check the key term record before this term
113 CURSOR c_key_from_prev_term IS
114 select program_cd, acad_cal_type from igs_en_spa_terms spat, igs_ca_inst ca1, igs_ca_inst ca2
115 where key_program_flag = 'Y'
116 and person_id = p_person_id
117 and ca1.cal_type = term_cal_type
118 and ca1.sequence_number = term_sequence_number
119 and ca2.cal_type = p_term_cal_type
120 and ca2.sequence_number = p_term_sequence_number
121 and ca1.start_dt < ca2.start_dt order by ca1.start_dt desc;
122
123 -- Key program as in student program attempt and its academic calendar
124 CURSOR c_key_from_spa IS
125 select course_cd, cal_type from igs_en_stdnt_ps_att where person_id = p_person_id and key_program ='Y';
126
127 -- Check if the academic calendar for the key determined is same as that of passed in term.
128 CURSOR c_key_in_same_acad(cp_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE) IS
129 SELECT 'x'
130 FROM igs_ca_inst_rel
131 WHERE sub_cal_type = p_term_cal_type
132 and sub_ci_sequence_number = p_term_sequence_number
133 and sup_cal_type = cp_acad_cal_type;
134
135 l_program_cd IGS_PS_VER.course_cd%TYPE;
136 l_dummy VARCHAR2(1);
137 l_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
138 BEGIN
139 OPEN c_key_record_exists;
140 FETCH c_key_record_exists INTO l_program_cd;
141 IF (c_key_record_exists%FOUND) THEN
142 CLOSE c_key_record_exists;
143 RETURN l_program_cd;
144 END IF;
145 CLOSE c_key_record_exists;
146 OPEN c_key_from_prev_term;
147 FETCH c_key_from_prev_term INTO l_program_cd, l_acad_cal_type;
148 IF (c_key_from_prev_term%NOTFOUND) THEN
149 OPEN c_oldest_term;
150 FETCH c_oldest_term INTO l_program_cd, l_acad_cal_type;
151 IF c_oldest_term%NOTFOUND THEN
152 OPEN c_key_from_spa;
153 FETCH c_key_from_spa INTO l_program_cd, l_acad_cal_type;
154 CLOSE c_key_from_spa;
155 END IF;
156 CLOSE c_oldest_term;
157 END IF;
158 CLOSE c_key_from_prev_term;
159 OPEN c_key_in_same_acad(l_acad_cal_type);
160 FETCH c_key_in_same_acad INTO l_dummy;
161 IF c_key_in_same_acad%NOTFOUND THEN
162 return NULL;
163 ELSE
164 return l_program_cd;
165 END IF;
166 END find_key_effective_for;
167
168 PROCEDURE get_effective_attribute_values(p_person_id IN NUMBER,
169 p_program_cd IN VARCHAR2,
170 p_term_cal_type IN VARCHAR2,
171 p_term_sequence_number IN NUMBER, p_term_rec OUT NOCOPY EN_SPAT_REC_TYPE%TYPE) AS
172
173 -- ## Get the term details information for the effective term
174 CURSOR c_term IS
175 SELECT * from igs_en_spa_terms
176 WHERE person_id = p_person_id
177 AND program_cd = p_program_cd
178 AND term_cal_type = p_term_cal_type
179 AND term_sequence_number = p_term_sequence_number;
180
181 -- ## get the term record values for a term which is previous to the effective term
182 CURSOR c_prev_term IS
183 SELECT
184 SPAT.*
185 FROM
186 IGS_EN_SPA_TERMS SPAT,
187 IGS_CA_INST CI1,
188 IGS_CA_INST CI2
189 WHERE
190 SPAT.PERSON_ID = p_person_id AND
191 spat.program_cd = p_program_cd AND
192 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
193 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
194 CI1.CAL_TYPE = p_term_cal_type AND
195 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
196 CI1.START_DT > CI2.START_DT AND
197 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
198 FROM IGS_CA_INST_REL
199 WHERE SUB_CAL_TYPE = P_TERM_CAL_TYPE
200 AND SUB_CI_SEQUENCE_NUMBER = P_TERM_SEQUENCE_NUMBER)
201 ORDER BY CI2.START_DT DESC;
202
203 -- ## get the SPA values for the passed in program attempt
204 CURSOR c_spa IS
205 SELECT person_id,
206 course_cd,
207 version_number,
208 cal_type,
209 key_program,
210 location_cd,
211 attendance_mode,
212 attendance_type,
213 fee_cat,
214 coo_id,
215 IGS_PR_CLASS_STD_ID
216 FROM igs_en_stdnt_ps_att
217 WHERE person_id = p_person_id
218 AND course_cd = p_program_cd;
219
220 l_program_cd IGS_PS_VER.COURSE_CD%TYPE;
221
222 BEGIN
223 --## 1. If term record exists for the effective term then get the attribute details
224 --## from it, set in EN_SPAT_REC_TYPE and exit
225 --## 2. Term record not found for effective term, hence move on to a term record
226 --## which is immediately in the past and get the attribute information and
227 --## set it in EN_SPAT_REC_TYPE and exit
228 --## 3. If no term record is found for the program attempt then get the attribute
229 --## information from the SPA.
230
231 OPEN c_term;
232 FETCH c_term INTO p_term_rec;
233 IF (c_term%FOUND) THEN
234 CLOSE c_term;
235 p_term_rec.person_id := p_person_id;
236 p_term_rec.program_cd := p_program_cd;
237 p_term_rec.term_cal_type := p_term_cal_type;
238 p_term_rec.term_sequence_number := p_term_sequence_number;
239 RETURN;
240 ELSE
241 CLOSE c_term;
242 END IF;
243
244
245 OPEN c_prev_term;
246 FETCH c_prev_term INTO p_term_rec;
247 IF (c_prev_term%FOUND) THEN
248 CLOSE c_prev_term;
249 p_term_rec.person_id := p_person_id;
250 p_term_rec.program_cd := p_program_cd;
251 p_term_rec.term_cal_type := p_term_cal_type;
252 p_term_rec.term_sequence_number := p_term_sequence_number;
253 ELSE
254 CLOSE c_prev_term;
255 OPEN c_spa;
256 FETCH c_spa INTO p_term_rec.person_id,
257 p_term_rec.program_cd,
258 p_term_rec.program_version,
259 p_term_Rec.acad_cal_type,
260 p_term_rec.key_program_flag,
261 p_term_rec.location_cd,
262 p_term_rec.attendance_mode,
263 p_term_rec.attendance_type,
264 p_term_rec.fee_cat,
265 p_term_rec.coo_id,
266 p_term_rec.class_standing_id;
267
268 p_term_rec.person_id := p_person_id;
269 p_term_rec.program_cd := p_program_cd;
270 p_term_rec.term_cal_type := p_term_cal_type;
271 p_term_rec.term_sequence_number := p_term_sequence_number;
272 CLOSE c_spa;
273 END IF;
274 l_program_cd := find_key_effective_for(
275 p_person_id => p_person_id,
276 p_term_cal_type => p_term_cal_type,
277 p_term_sequence_number => p_term_sequence_number);
278 IF l_program_cd = p_program_cd THEN
279 p_term_rec.key_program_flag := 'Y';
280 ELSE
281 p_term_rec.key_program_flag := 'N';
282 END IF;
283
284 END get_effective_attribute_values;
285
286 PROCEDURE set_param_attributes
287 (
288 p_person_id IN NUMBER,
289 p_program_cd IN VARCHAR2,
290 p_term_cal_type IN VARCHAR2,
291 p_term_sequence_number IN NUMBER,
292 p_coo_id IN NUMBER,
293 p_key_program_flag IN VARCHAR2,
294 p_fee_cat IN VARCHAR2,
295 p_class_standing_id IN NUMBER,
296 p_plan_sht_status IN VARCHAR2,
297 p_old_term_rec IN EN_SPAT_REC_TYPE%TYPE,
298 p_new_term_rec IN OUT NOCOPY EN_SPAT_REC_TYPE%TYPE,
299 p_program_changed IN BOOLEAN
300 ) AS
301 /* p_old_term_rec is input record
302 p_new_term_rec is the output record
303 - Set the changed attributes in p_new_term_rec
304 - All attributes which are not changing, use the values in p_old_term_rec
305 */
306 CURSOR c_prg_attributes IS
307 SELECT version_number,
308 location_cd,
309 attendance_type,
310 attendance_mode,
311 cal_type
312 FROM igs_ps_ofr_opt
313 WHERE coo_id = p_coo_id;
314 BEGIN
315
316 p_new_term_rec := p_old_term_rec;
317 IF p_program_changed THEN
318 p_new_term_rec := NULL;
319 get_effective_attribute_values (
320 p_person_id => p_person_id,
321 p_program_cd => p_program_cd,
322 p_term_cal_type => p_term_cal_type,
323 p_term_sequence_number => p_term_sequence_number,
324 p_term_rec => p_new_term_rec);
325
326 p_new_term_rec.term_cal_type := p_term_cal_type;
327 p_new_term_rec.term_sequence_number := p_term_sequence_number;
328 END IF;
329
330 IF (p_coo_id <> -1) THEN
331 p_new_term_rec.coo_id := p_coo_id;
332 OPEN c_prg_attributes;
333 FETCH c_prg_attributes INTO p_new_term_rec.program_version,
334 p_new_term_rec.location_cd,
335 p_new_term_rec.attendance_type,
336 p_new_term_rec.attendance_mode,
337 p_new_term_rec.acad_cal_type;
338 CLOSE c_prg_attributes;
339
340 END IF;
341 IF (p_key_program_flag <> FND_API.G_MISS_CHAR) THEN
342 p_new_term_rec.key_program_flag := p_key_program_flag;
343 END IF;
344 IF (p_fee_cat = FND_API.G_MISS_CHAR) THEN
345 null;
346 ELSE
347 p_new_term_rec.fee_cat := p_fee_cat;
348 END IF;
349 IF (p_class_standing_id = -1) THEN
350 NULL;
351 ELSE
352 p_new_term_rec.class_standing_id := p_class_standing_id;
353 END IF;
354 IF (p_plan_sht_status = FND_API.G_MISS_CHAR) THEN
355 null;
356 ELSE
357 p_new_term_rec.plan_sht_status := p_plan_sht_status;
358 END IF;
359
360 END set_param_attributes;
361
362
363 PROCEDURE find_and_create_key_record(p_person_id IN NUMBER, p_term_cal_type IN VARCHAR2,p_term_sequence_number IN NUMBER) AS
364 l_key_program VARCHAR2(100);
365 l_message_name varchar2(2000);
366 BEGIN
367 l_key_program := find_key_effective_for(p_person_id, p_term_cal_type, p_term_sequence_number);
368 IF l_key_program IS NOT NULL THEN
369 create_update_term_rec( -- can it be check_and_create
370 p_person_id => p_person_id,
371 p_program_cd => l_key_program,
372 p_term_cal_type => p_term_cal_type,
373 p_term_sequence_number => p_term_sequence_number,
374 p_ripple_frwrd => FALSE,
375 p_update_rec => FALSE,
376 p_message_name => l_message_name);
377 END IF;
378 END;
379 PROCEDURE check_term_exists(
380 p_person_id IN NUMBER,
381 p_program_cd IN VARCHAR2,
382 p_program_version IN NUMBER,
383 p_term_cal_type IN VARCHAR2,
384 p_term_sequence_number IN NUMBER,
385 p_insert_rec OUT NOCOPY BOOLEAN,
386 p_term_record_id OUT NOCOPY NUMBER) AS
387 /* -----------------------------------------------------------------------
388 Created By : Susmitha Tutta
389 Date Created By : 16-Mar-2004
390 Purpose : Checks whether a term record exists and returns
391 term_record_id and p_insert_rec = FALSE if exists.
392 If term doesn't exist returns p_insert_rec = TRUE.
393
394 Change History
395 Who When What
396 ----------------------------------------------------------------------*/
397
398 vc_career_model_enabled VARCHAR2(1);
399 CURSOR c_term_rec_exists(cp_person_id IGS_PE_PERSON.person_id%TYPE,
400 cp_program_cd IGS_PS_VER.course_cd%TYPE,
401 cp_program_version IGS_PS_VER.version_number%TYPE,
402 cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
406 WHERE spat.person_id = cp_person_id
403 cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
404 SELECT spat.term_record_id
405 FROM IGS_EN_SPA_TERMS spat, igs_ps_ver cv1
407 AND spat.term_cal_type = cp_term_cal_type
408 AND spat.term_sequence_number = cp_term_sequence_number
409 AND cv1.course_cd = spat.program_cd
410 AND cv1.version_number = spat.program_version
411 AND
412 ( (
413 NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y'
414 AND cv1.course_type = (SELECT cv2.course_type
415 FROM IGS_PS_VER cv2
416 WHERE cv2.course_cd = cp_program_cd
417 AND cv2.version_number = cp_program_version)
418 )
419 OR
420 ( NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N'
421 AND spat.program_cd = cp_program_cd
422 )
423 );
424
425 vr_term_rec c_term_rec_exists%ROWTYPE;
426 BEGIN
427
428 p_insert_rec := TRUE;
429
430 OPEN c_term_rec_exists (p_person_id,
431 p_program_cd,
432 p_program_version,
433 p_term_cal_type,
434 p_term_sequence_number) ;
435 -- fetch term record details
436 FETCH c_term_rec_exists INTO vr_term_rec;
437 -- if term record details are found for passed parameters
438 -- new term record need not be created
439 IF c_term_rec_exists%FOUND THEN
440 -- set flag to indicate that no insert be allowed
441 p_insert_rec := FALSE;
442 -- retrieve rowid for the term record to be updated
443 p_term_record_id := vr_term_rec.term_record_id;
444 END IF;
445 CLOSE c_term_rec_exists;
446
447 END check_term_exists;
448
449 PROCEDURE check_and_create(
450 p_term_rec IN EN_SPAT_REC_TYPE%TYPE,
451 p_update_rec IN BOOLEAN DEFAULT FALSE,
452 p_program_changed IN BOOLEAN DEFAULT FALSE
453 ) AS
454 vc_row_id VARCHAR2(25);
455
456 -- flag to indicate whether term record details should be inserted or not
457 -- defailt TRUE
458 v_insert_rec BOOLEAN;
459
460 l_rowid VARCHAR2(25);
461 l_term_record_id IGS_EN_SPA_TERMS.TERM_RECORD_ID%TYPE;
462
463
464 -- cursor to fetch rowid for given term record details
465 -- used in updating an existing term record
466 Cursor cur_spat (cp_term_record_id IN NUMBER) IS
467 SELECT spat.rowid, spat.program_cd, spat.acad_cal_type
468 FROM IGS_EN_SPA_TERMS spat
469 WHERE spat.term_record_id = cp_term_record_id;
470
471 vc_cur_spat_rec cur_spat%ROWTYPE;
472 CURSOR c_check_planning_sheet (p_person_id IGS_EN_PLAN_UNITS.PERSON_ID%TYPE,
473 p_course_cd IGS_EN_PLAN_UNITS.COURSE_CD%TYPE,
474 p_term_cal_type IGS_EN_PLAN_UNITS.TERM_CAL_TYPE%TYPE,
475 p_term_ci_sequence IGS_EN_PLAN_UNITS.TERM_CI_SEQUENCE_NUMBER%TYPE
476 ) IS
477 SELECT person_id
478 FROM IGS_EN_PLAN_UNITS
479 WHERE person_id = p_person_id
480 AND course_cd = p_course_cd
481 AND term_cal_type = p_term_cal_type
482 AND term_ci_sequence_number = p_term_ci_sequence
483 AND cart_error_flag = 'N';
484
485 v_planning_sheet_rec c_check_planning_sheet%ROWTYPE;
486 l_plan_sht_status IGS_EN_SPA_TERMS.PLAN_SHT_STATUS%TYPE;
487 BEGIN
488
489 OPEN c_term_exists (p_term_rec.person_id,p_term_rec.program_cd,
490 p_term_rec.program_version,p_term_rec.term_cal_type,
491 p_term_rec.term_sequence_number);
492 FETCH c_term_exists INTO l_term_record_id;
493 IF (c_term_exists%NOTFOUND) THEN
494 v_insert_rec := TRUE;
495 ELSE
496 v_insert_rec := FALSE;
497 END IF;
498 CLOSE c_term_exists;
499
500 IF v_insert_rec=TRUE THEN
501
502
503 -- call table handler to insert new term record details
504 l_term_record_id := NULL;
505 l_rowid := NULL;
506
507 IGS_EN_SPA_TERMS_PKG.insert_row(
508 x_rowid => l_rowid,
509 x_term_record_id => l_term_record_id,
510 x_person_id => p_term_rec.person_id,
511 x_program_cd => p_term_rec.program_cd,
512 x_program_version => p_term_rec.program_version,
513 x_acad_cal_type => p_term_rec.acad_cal_type,
514 x_term_cal_type => p_term_rec.term_cal_type,
515 x_term_sequence_number => p_term_rec.term_sequence_number,
516 x_key_program_flag => p_term_rec.key_program_flag,
517 x_location_cd => p_term_rec.location_cd,
518 x_attendance_mode => p_term_rec.attendance_mode,
519 x_attendance_type => p_term_rec.attendance_type,
520 x_fee_cat => p_term_rec.fee_cat,
521 x_coo_id => p_term_rec.coo_id,
522 x_class_standing_id => p_term_rec.class_standing_id,
523 x_attribute_category => null,
524 x_attribute1 => null,
528 x_attribute5 => null,
525 x_attribute2 => null,
526 x_attribute3 => null,
527 x_attribute4 => null,
529 x_attribute6 => null,
530 x_attribute7 => null,
531 x_attribute8 => null,
532 x_attribute9 => null,
533 x_attribute10 => null,
534 x_attribute11 => null,
535 x_attribute12 => null,
536 x_attribute13 => null,
537 x_attribute14 => null,
538 x_attribute15 => null,
539 x_attribute16 => null,
540 x_attribute17 => null,
541 x_attribute18 => null,
542 x_attribute19 => null,
543 x_attribute20 => null,
544 x_mode => 'R',
545 x_plan_sht_status => NVL(p_term_rec.plan_sht_status, 'NONE')
546 );
547
548
549 -- if update flag is set to TRUE
550 ELSIF p_update_rec=TRUE THEN
551
552 -- in career mode, check if the primary program is changing,
553 -- in case the primary program is chaning then change the program code
554 -- and other related parameters as well in the term records.
555 -- If the primary program is not changing in the current updated
556 -- then do not ripple forward the changes to other programs in the
557 -- same career.
558 -- After the program transfer build, the only place from where
559 -- the primary program can be switched in the program transfer
560 -- page. Hence we check if the call to the term API was initialized
561 -- from the page/program transfer API. in that case the program
562 -- code would be rippled forward otherwise it wont.
563 -- To identify if the call has be initialized from the program transfer
564 -- the logic would use a global variable.
565
566 OPEN cur_spat(l_term_record_id);
567 FETCH cur_spat INTO vc_cur_spat_rec ;
568 CLOSE cur_spat;
569
570 l_plan_sht_status := p_term_rec.plan_sht_status;
571 IF l_plan_sht_status IS NULL OR l_plan_sht_status = FND_API.G_MISS_CHAR THEN
572 l_plan_sht_status := 'NONE';
573 END IF;
574 IF (p_program_changed OR p_term_rec.acad_cal_type <> vc_cur_spat_rec.acad_cal_type ) THEN
575 --check for planning sheet exist.
576 OPEN c_check_planning_sheet(p_term_rec.person_id, p_term_rec.program_cd,
577 p_term_rec.term_cal_type,p_term_rec.term_sequence_number);
578 FETCH c_check_planning_sheet INTO v_planning_sheet_rec;
579 IF c_check_planning_sheet%FOUND THEN
580 l_plan_sht_status := 'SKIP';
581 END IF;
582 CLOSE c_check_planning_sheet;
583 END IF;
584
585
586 -- if rowid for term record was found
587 -- term record details exist and will be updated
588 -- call table handler to update term record details
589
590
591 IGS_EN_SPA_TERMS_PKG.update_row(
592 x_rowid => vc_cur_spat_rec.rowid,
593 x_term_record_id => p_term_rec.term_record_id,
594 x_person_id => p_term_rec.person_id,
595 x_program_cd => p_term_rec.program_cd,
596 x_program_version => p_term_rec.program_version,
597 x_acad_cal_type => p_term_rec.acad_cal_type,
598 x_term_cal_type => p_term_rec.term_cal_type,
599 x_term_sequence_number => p_term_rec.term_sequence_number,
600 x_key_program_flag => p_term_rec.key_program_flag,
601 x_location_cd => p_term_rec.location_cd,
602 x_attendance_mode => p_term_rec.attendance_mode,
603 x_attendance_type => p_term_rec.attendance_type,
604 x_fee_cat => p_term_rec.fee_cat,
605 x_coo_id => p_term_rec.coo_id,
606 x_class_standing_id => p_term_rec.class_standing_id,
607 x_attribute_category => p_term_rec.attribute_category,
608 x_attribute1 => p_term_rec.attribute1,
609 x_attribute2 => p_term_rec.attribute2,
610 x_attribute3 => p_term_rec.attribute3,
611 x_attribute4 => p_term_rec.attribute4,
612 x_attribute5 => p_term_rec.attribute5,
613 x_attribute6 => p_term_rec.attribute6,
614 x_attribute7 => p_term_rec.attribute7,
615 x_attribute8 => p_term_rec.attribute8,
616 x_attribute9 => p_term_rec.attribute9,
617 x_attribute10 => p_term_rec.attribute10,
618 x_attribute11 => p_term_rec.attribute11,
619 x_attribute12 => p_term_rec.attribute12,
620 x_attribute13 => p_term_rec.attribute13,
621 x_attribute14 => p_term_rec.attribute14,
622 x_attribute15 => p_term_rec.attribute15,
623 x_attribute16 => p_term_rec.attribute16,
624 x_attribute17 => p_term_rec.attribute17,
625 x_attribute18 => p_term_rec.attribute18,
626 x_attribute19 => p_term_rec.attribute19,
627 x_attribute20 => p_term_rec.attribute20,
628 x_mode => 'R',
629 x_plan_sht_status => l_plan_sht_status
630 );
631
632 END IF;
633
634 END check_and_create;
635
636
637
638 PROCEDURE ripple_frwd
639 (
640 p_person_id IN NUMBER,
644 p_coo_id IN NUMBER,
641 p_program_cd IN VARCHAR2,
642 p_term_cal_type IN VARCHAR2,
643 p_term_sequence_number IN NUMBER,
645 p_fee_cat IN VARCHAR2,
646 p_class_standing_id IN NUMBER,
647 p_term_rec IN EN_SPAT_REC_TYPE%TYPE,
648 p_program_changed IN BOOLEAN
649 ) AS
650 --## PROCEDURE DESCRIPTION:
651 --## the attribute values for the effective term are mirrored onto the
652 --## the future term records (that exist in the term records table and
653 --## are in future to the effective term).
654
655 -- cursor to fetch term records occuring in future
656 CURSOR c_future_terms (cp_chk_othr_prms VARCHAR2,
657 cp_program_version NUMBER,
658 cp_acad_cal_type igs_ca_inst.cal_type%TYPE) IS
659 SELECT spat.*
660 FROM IGS_EN_SPA_TERMS spat,
661 IGS_CA_INST_REL cr,
662 IGS_CA_INST_REL cr2,
663 IGS_CA_INST ci,
664 IGS_PS_VER cv
665 WHERE cr.sup_cal_type = cp_acad_cal_type
666 AND cr.sub_cal_type = p_term_cal_type
667 AND cr.sub_ci_sequence_number = p_term_sequence_number
668 AND cr.sup_cal_type = cr2.sup_cal_type
669 AND cr2.sub_cal_type = spat.term_cal_type
670 AND cr2.sub_ci_sequence_number = spat.term_sequence_number
671 AND spat.person_id = p_person_id
672 AND ci.cal_type = cr2.sub_cal_type
673 AND ci.sequence_number = cr2.sub_ci_sequence_number
674 AND exists (SELECT 'x'
675 FROM IGS_CA_INST cii
676 WHERE cal_type = p_term_cal_type
677 AND sequence_number = p_term_sequence_number
678 AND ci.start_dt >= cii.start_dt)
679 AND ci.sequence_number <> p_term_sequence_number
680 AND cv.course_cd = spat.program_cd
681 AND cv.version_number = spat.program_version
682 AND (
683 (
684 cp_chk_othr_prms = 'Y' AND
685 cv.course_type = (SELECT course_type
686 FROM IGS_PS_VER cv2
687 WHERE cv2.course_cd = p_program_cd
688 AND cv2.version_number = cp_program_version)
689 )
690 OR
691 ( cp_chk_othr_prms <> 'Y' AND spat.program_cd = p_program_cd));
692
693
694 vc_career_model_enabled VARCHAR2(1);
695 vd_start_dt IGS_CA_INST.START_DT%TYPE;
696 v_program_changed BOOLEAN;
697 v_check_othr_prgms VARCHAR2(1);
698 v_changed_term_rec EN_SPAT_REC_TYPE%TYPE;
699 BEGIN
700
701 -- check if Career Model is enabled for the System and if called from program transfer
702 IF (p_program_changed AND NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N') = 'Y' ) THEN
703 v_check_othr_prgms := 'Y';
704 ELSE
705 v_check_othr_prgms := 'N';
706 END IF;
707 -- Select all the term records for the passed in person id,
708 -- term calendar and academic calendar either in the same program (in program mode)
709 -- or in the same career (in the career mode)
710 FOR vr_future_term_rec
711 IN c_future_terms
712 (v_check_othr_prgms,
713 p_term_rec.program_version,
714 p_term_rec.acad_cal_type) LOOP
715
716 IF (NOT p_program_changed AND vr_future_term_rec.program_cd <> p_term_rec.program_cd ) THEN
717 null;
718 ELSE
719
720 set_param_attributes(
721 p_person_id => p_person_id,
722 p_program_cd => p_program_cd,
723 p_term_cal_type => vr_future_term_rec.term_cal_type,
724 p_term_sequence_number => vr_future_term_rec.term_sequence_number,
725 p_coo_id => p_coo_id,
726 p_key_program_flag => FND_API.G_MISS_CHAR,
727 p_fee_cat => p_fee_cat,
728 p_class_standing_id => p_class_standing_id,
729 p_plan_sht_status => FND_API.G_MISS_CHAR,
730 p_old_term_rec => vr_future_term_rec,
731 p_new_term_rec => v_changed_term_rec,
732 p_program_changed => p_program_changed);
733
734 CHECK_AND_CREATE(
735 p_term_rec => v_changed_term_rec,
736 p_update_rec => TRUE,
737 p_program_changed => p_program_changed);
738 END IF;
739
740 END LOOP;
741
742 END ripple_frwd;
743
744
745 PROCEDURE backward_gap_fill
746 (
747 p_term_rec IN EN_SPAT_REC_TYPE%TYPE
748 ) AS
749 TYPE t_ref_cur IS REF CURSOR;
750 c_backward_gap_exists t_ref_cur;
751
752 v_backward_gap_exists_stmt VARCHAR2 (4000);
753
754 v_context_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
755 v_context_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
756 v_next_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
757 v_next_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
758
759 v_person_id IGS_EN_SPA_TERMS.PERSON_ID%TYPE;
760 v_program_cd IGS_EN_SPA_TERMS.PROGRAM_CD%TYPE;
761 v_program_version IGS_EN_SPA_TERMS.PROGRAM_VERSION%TYPE;
765 v_location_cd IGS_EN_SPA_TERMS.LOCATION_CD%TYPE;
762 v_coo_id IGS_EN_SPA_TERMS.COO_ID%TYPE;
763 v_acad_cal_type IGS_EN_SPA_TERMS.ACAD_CAL_TYPE%TYPE;
764 v_key_program_flag IGS_EN_SPA_TERMS.KEY_PROGRAM_FLAG%TYPE;
766 v_attendance_mode IGS_EN_SPA_TERMS.ATTENDANCE_MODE%TYPE;
767 v_attendance_type IGS_EN_SPA_TERMS.ATTENDANCE_TYPE%TYPE;
768 v_fee_cat IGS_EN_SPA_TERMS.FEE_CAT%TYPE;
769 v_class_standing_id IGS_EN_SPA_TERMS.CLASS_STANDING_ID%TYPE;
770
771 -- cursor to fetch backward term gaps
772 CURSOR c_backward_gap(cp_context_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
773 cp_context_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
774 cp_next_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
775 cp_next_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE)
776 IS
777 SELECT ci.cal_type, ci.sequence_number, ci.start_dt,
778 ci.alternate_code, ci.description
779 FROM igs_ca_inst ci,
780 igs_ca_inst endterm,
781 igs_ca_inst beginterm,
782 igs_ca_type ct,
783 igs_ca_stat cs,
784 igs_ca_inst_rel cr,
785 igs_ca_inst_rel cr2,
786 igs_ca_type ct2
787 WHERE ci.cal_type = cr.sub_cal_type
788 AND ci.sequence_number = cr.sub_ci_sequence_number
789 AND cr.sup_cal_type = ct2.cal_type
790 AND ct2.s_cal_cat = 'ACADEMIC'
791 AND cr.sup_cal_type = cr2.sup_cal_type
792 AND beginterm.cal_type = cr2.sub_cal_type
793 AND beginterm.sequence_number = cr2.sub_ci_sequence_number
794 AND ci.start_dt >= beginterm.start_dt
795 AND ci.start_dt < endterm.start_dt
796 AND endterm.cal_type = cp_next_cal_type
797 AND endterm.sequence_number = cp_next_sequence_number
798 AND beginterm.cal_type = cp_context_cal_type
799 AND beginterm.sequence_number = cp_context_sequence_number
800 AND ci.sequence_number <> endterm.sequence_number
801 AND ci.sequence_number <> beginterm.sequence_number
802 AND ct.cal_type = ci.cal_type
803 AND ct.s_cal_cat = 'LOAD'
804 AND cs.cal_status = ci.cal_status
805 AND cs.s_cal_status = 'ACTIVE'
806 ORDER BY ci.start_dt ASC;
807
808 -- checks if a term record exists in the same term for the given person
809 -- and academic calendar
810 v_term_rec EN_SPAT_REC_TYPE%TYPE;
811 vc_career_model_enabled varchar2(1);
812 BEGIN
813 -- fetch if Career Model is enabled for the System or not
814 vc_career_model_enabled := nvl(fnd_profile.value('CAREER_MODEL_ENABLED'),'N');
815 v_term_rec := p_term_rec;
816 -- if Career Model is enabled
817 -- if Career Model is enabled
818 IF vc_career_model_enabled = 'Y' THEN
819
820 -- set query statement to fetch any backward gap terms
821 v_backward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
822 CI2.SEQUENCE_NUMBER,
823 CI.CAL_TYPE,
824 CI.SEQUENCE_NUMBER,
825 SPT.PERSON_ID,
826 SPT.PROGRAM_CD,
827 SPT.PROGRAM_VERSION,
828 SPT.COO_ID,
829 SPT.ACAD_CAL_TYPE,
830 SPT.KEY_PROGRAM_FLAG,
831 SPT.LOCATION_CD,
832 SPT.ATTENDANCE_MODE,
833 SPT.ATTENDANCE_TYPE,
834 SPT.FEE_CAT,
835 SPT.CLASS_STANDING_ID
836 FROM IGS_EN_SPA_TERMS SPT,
837 IGS_PS_VER CV,
838 IGS_CA_INST CI,
839 IGS_CA_INST CI2
840 WHERE SPT.PERSON_ID = :1
841 AND SPT.PROGRAM_CD = CV.COURSE_CD
842 AND SPT.PROGRAM_VERSION = CV.VERSION_NUMBER
843 AND CV.COURSE_TYPE IN (SELECT CV2.COURSE_TYPE
844 FROM IGS_PS_VER CV2
845 WHERE CV2.COURSE_CD = :2
846 AND CV2.VERSION_NUMBER = :3)
847 AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
848 AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
849 AND CI.CAL_TYPE = :4
850 AND CI.SEQUENCE_NUMBER = :5
851 AND SPT.ACAD_CAL_TYPE = :6
852 AND CI2.START_DT < CI.START_DT
853 AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
854 ORDER BY CI2.START_DT DESC';
855
856 -- open cursor to fetch backward gap terms
857 OPEN c_backward_gap_exists FOR v_backward_gap_exists_stmt
861 p_term_rec.term_cal_type,
858 USING p_term_rec.person_id,
859 p_term_rec.program_cd,
860 p_term_rec.program_version,
862 p_term_rec.term_sequence_number,
863 p_term_rec.acad_cal_type;
864
865 ELSE -- if career model is not enabled
866 -- set query statement to fetch any backward gap terms
867 v_backward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
868 CI2.SEQUENCE_NUMBER,
869 CI.CAL_TYPE,
870 CI.SEQUENCE_NUMBER,
871 SPT.PERSON_ID,
872 SPT.PROGRAM_CD,
873 SPT.PROGRAM_VERSION,
874 SPT.COO_ID,
875 SPT.ACAD_CAL_TYPE,
876 SPT.KEY_PROGRAM_FLAG,
877 SPT.LOCATION_CD,
878 SPT.ATTENDANCE_MODE,
879 SPT.ATTENDANCE_TYPE,
880 SPT.FEE_CAT,
881 SPT.CLASS_STANDING_ID
882 FROM IGS_EN_SPA_TERMS SPT,
883 IGS_CA_INST CI,
884 IGS_CA_INST CI2
885 WHERE SPT.PERSON_ID = :1
886 AND SPT.PROGRAM_CD = :2
887 AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
888 AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
889 AND CI.CAL_TYPE = :3
890 AND CI.SEQUENCE_NUMBER = :4
891 AND SPT.ACAD_CAL_TYPE = :6
892 AND CI2.START_DT < CI.START_DT
893 AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
894 ORDER BY CI2.START_DT DESC';
895 -- open cursor to fetch backward gap terms
896 OPEN c_backward_gap_exists FOR v_backward_gap_exists_stmt
897 USING p_term_rec.person_id,
898 p_term_rec.program_cd,
899 p_term_rec.term_cal_type,
900 p_term_rec.term_sequence_number,
901 p_term_rec.acad_cal_type;
902
903 END IF;
904
905 --fetch backward gap terms
906 FETCH c_backward_gap_exists INTO v_context_cal_type,
907 v_context_sequence_number,
908 v_next_cal_type,
909 v_next_sequence_number,
910 v_term_rec.person_id,
911 v_term_rec.program_cd,
912 v_term_rec.program_version,
913 v_term_rec.coo_id,
914 v_term_rec.acad_cal_type,
915 v_term_rec.key_program_flag,
916 v_term_rec.location_cd,
917 v_term_rec.attendance_mode,
918 v_term_rec.attendance_type,
919 v_term_rec.fee_cat,
920 v_term_rec.class_standing_id;
921
922 -- if no backward term gaps exist
923 IF c_backward_gap_exists%NOTFOUND THEN
924 CLOSE c_backward_gap_exists;
925 RETURN;
926
927 -- if backward term gaps exist
928 ELSE
929 CLOSE c_backward_gap_exists;
930 END IF;
931
932 -- fetch backward gap term records
933 FOR vr_backward_gap_rec IN c_backward_gap(v_context_cal_type,
934 v_context_sequence_number,
935 v_next_cal_type,
936 v_next_sequence_number) LOOP
937 v_term_rec.term_cal_type := vr_backward_gap_rec.cal_type;
938 v_term_rec.term_sequence_number := vr_backward_gap_rec.sequence_number;
939 v_term_rec.plan_sht_status := 'NONE';
940 IF (v_term_rec.program_cd = find_key_effective_for(v_term_rec.person_id,
941 vr_backward_gap_rec.cal_type,
942 vr_backward_gap_rec.sequence_number)) THEN
943
944 v_term_rec.key_program_flag := 'Y';
945 ELSE
946
947 v_term_rec.key_program_flag := 'N';
948 END IF;
949
950 check_and_create(
951 p_term_rec => v_term_rec,
952 p_update_rec => FALSE);
953
954 END LOOP;
955
956 END backward_gap_fill;
957
958 PROCEDURE forward_gap_fill
959 (
960 p_term_rec IN EN_SPAT_REC_TYPE%TYPE
961 )
962 AS
963 /* -----------------------------------------------------------------------
964 Created By : rvangala
965 Date Created By : 18-Nov-2003
966 Purpose : Checks if any term future or forward gaps exists,
967 and updates them if necessary.
968
969 Change History
970 Who When What
971 stutta 31-Dec-2004 Modified c_forward_gap to pickup only records whose
975 ----------------------------------------------------------------------*/
972 start date is < the next calendar( not <= next calendar)
973 This is to avoid the same calendar being consider a
974 future and past calendar.
976 TYPE t_ref_cur IS REF CURSOR;
977 c_forward_gap_exists t_ref_cur;
978
979 v_forward_gap_exists_stmt VARCHAR2(4000);
980
981 vc_curr_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
982 vn_curr_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
983 vc_next_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
984 vn_next_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
985
986 vn_person_id IGS_EN_SPA_TERMS.PERSON_ID%TYPE;
987 vc_program_cd IGS_EN_SPA_TERMS.PROGRAM_CD%TYPE;
988 vn_program_version IGS_EN_SPA_TERMS.PROGRAM_VERSION%TYPE;
989 vn_coo_id IGS_EN_SPA_TERMS.COO_ID%TYPE;
990 vc_acad_cal_type IGS_EN_SPA_TERMS.ACAD_CAL_TYPE%TYPE;
991 vc_key_program_flag IGS_EN_SPA_TERMS.KEY_PROGRAM_FLAG%TYPE;
992 vc_location_cd IGS_EN_SPA_TERMS.LOCATION_CD%TYPE;
993 vc_attendance_mode IGS_EN_SPA_TERMS.ATTENDANCE_MODE%TYPE;
994 vc_attendance_type IGS_EN_SPA_TERMS.ATTENDANCE_TYPE%TYPE;
995 vc_fee_cat IGS_EN_SPA_TERMS.FEE_CAT%TYPE;
996 vc_class_standing_id IGS_EN_SPA_TERMS.CLASS_STANDING_ID%TYPE;
997
998 CURSOR c_forward_gap (cp_next_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
999 cp_next_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
1000 cp_curr_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1001 cp_curr_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE)
1002 IS
1003 SELECT ci.cal_type, ci.sequence_number,
1004 ci.start_dt, ci.alternate_code, ci.description
1005 FROM IGS_CA_INST ci,
1006 IGS_CA_INST ci2,
1007 IGS_CA_INST ci3,
1008 IGS_CA_TYPE ct,
1009 IGS_CA_STAT cs,
1010 IGS_CA_INST_REL cr,
1011 IGS_CA_INST_REL cr2,
1012 IGS_CA_TYPE ct2
1013 WHERE ci.cal_type = cr.sub_cal_type
1014 AND ci.sequence_number = cr.sub_ci_sequence_number
1015 AND cr.sup_cal_type = ct2.cal_type
1016 AND ct2.s_cal_cat = 'ACADEMIC'
1017 AND cr.sup_cal_type = cr2.sup_cal_type
1018 AND ci2.cal_type = cr2.sub_cal_type
1019 AND ci2.sequence_number = cr2.sub_ci_sequence_number
1020 AND ci.start_dt < ci3.start_dt
1021 AND ci.start_dt >= ci2.start_dt
1022 AND ci2.cal_type = cp_curr_cal_type
1023 AND ci2.sequence_number = cp_curr_sequence_number
1024 AND ci3.cal_type = cp_next_cal_type
1025 AND ci3.sequence_number = cp_next_sequence_number
1026 AND ci.sequence_number <> ci2.sequence_number
1027 AND ci.sequence_number <> ci3.sequence_number
1028 AND ct.cal_type = ci.cal_type
1029 AND ct.s_cal_cat = 'LOAD'
1030 AND cs.cal_status = ci.cal_status
1031 AND cs.s_cal_status = 'ACTIVE'
1032 ORDER BY ci.start_dt ASC;
1033
1034 CURSOR c_other_recs ( cp_term_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1035 cp_term_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
1036 cp_program_cd IGS_PS_VER.COURSE_CD%TYPE) IS
1037 SELECT *
1038 FROM IGS_EN_SPA_TERMS
1039 WHERE person_id = p_term_rec.person_id
1040 AND program_cd <> cp_program_cd
1041 AND term_cal_type = cp_term_cal_type
1042 AND term_sequence_number = cp_term_sequence_number
1043 AND acad_cal_type = p_term_rec.acad_cal_type
1044 AND key_program_flag = 'Y';
1045
1046 vc_career_model_enabled VARCHAR2(1);
1047 v_term_rec EN_SPAT_REC_TYPE%TYPE;
1048
1049 BEGIN
1050
1051 -- fetch if Career Model is enabled for the System or not
1052 vc_career_model_enabled := NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N');
1053
1054 -- if Career Model is enabled
1055 IF vc_career_model_enabled = 'Y' THEN
1056
1057 -- set query statement to fetch term record
1058 v_forward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
1059 CI2.SEQUENCE_NUMBER,
1060 CI.CAL_TYPE,
1061 CI.SEQUENCE_NUMBER
1062 FROM IGS_EN_SPA_TERMS SPT,
1063 IGS_PS_VER CV,
1064 IGS_CA_INST CI,
1065 IGS_CA_INST CI2
1066 WHERE SPT.PERSON_ID = :1
1067 AND SPT.PROGRAM_CD = CV.COURSE_CD
1068 AND SPT.PROGRAM_VERSION = CV.VERSION_NUMBER
1069 AND CV.COURSE_TYPE IN (SELECT CV2.COURSE_TYPE
1070 FROM IGS_PS_VER CV2
1071 WHERE CV2.COURSE_CD = :2
1072 AND CV2.VERSION_NUMBER = :3)
1073 AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
1074 AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
1075 AND CI.CAL_TYPE = :4
1079 AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
1076 AND CI.SEQUENCE_NUMBER = :5
1077 AND SPT.ACAD_CAL_TYPE = :6
1078 AND CI2.START_DT >= CI.START_DT
1080 ORDER BY CI2.START_DT ASC';
1081
1082 -- if Career Model is not enabled
1083 ELSE
1084 -- set query statement to fetch term record
1085 v_forward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
1086 CI2.SEQUENCE_NUMBER,
1087 CI.CAL_TYPE,
1088 CI.SEQUENCE_NUMBER
1089 FROM IGS_EN_SPA_TERMS SPT,
1090 IGS_CA_INST CI,
1091 IGS_CA_INST CI2
1092 WHERE SPT.PERSON_ID = :1
1093 AND SPT.PROGRAM_CD = :2
1094 AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
1095 AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
1096 AND CI.CAL_TYPE = :3
1097 AND CI.SEQUENCE_NUMBER = :4
1098 AND SPT.ACAD_CAL_TYPE = :5
1099 AND CI2.START_DT >= CI.START_DT
1100 AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
1101 ORDER BY CI2.START_DT ASC';
1102 END IF;
1103
1104 IF vc_career_model_enabled = 'Y' THEN
1105 -- fetch term record details
1106 OPEN c_forward_gap_exists FOR v_forward_gap_exists_stmt
1107 USING p_term_rec.person_id,
1108 p_term_rec.program_cd,
1109 p_term_rec.program_version,
1110 p_term_rec.term_cal_type,
1111 p_term_rec.term_sequence_number,
1112 p_term_rec.acad_cal_type;
1113 ELSE
1114 OPEN c_forward_gap_exists FOR v_forward_gap_exists_stmt
1115 USING p_term_rec.person_id,
1116 p_term_rec.program_cd,
1117 p_term_rec.term_cal_type,
1118 p_term_rec.term_sequence_number,
1119 p_term_rec.acad_cal_type;
1120 END IF;
1121 FETCH c_forward_gap_exists INTO vc_next_cal_type,
1122 vn_next_sequence_number,
1123 vc_curr_cal_type,
1124 vn_curr_sequence_number;
1125
1126 -- if term record was not found
1127 IF c_forward_gap_exists%NOTFOUND THEN
1128 CLOSE c_forward_gap_exists;
1129 RETURN;
1130
1131 -- if term record was found
1132 ELSE
1133 CLOSE c_forward_gap_exists;
1134 END IF;
1135
1136 v_term_rec := p_term_rec;
1137 -- fetch forward calendars
1138 FOR vr_forward_gap_rec IN c_forward_gap(vc_next_cal_type,
1139 vn_next_sequence_number,
1140 vc_curr_cal_type,
1141 vn_curr_sequence_number) LOOP
1142 v_term_rec.term_cal_type :=vr_forward_gap_rec.cal_type;
1143 v_term_rec.term_sequence_number :=vr_forward_gap_rec.sequence_number;
1144 v_term_rec.plan_sht_status := 'NONE';
1145
1146 IF (v_term_rec.program_cd = find_key_effective_for(v_term_rec.person_id,
1147 vc_curr_cal_type,
1148 vn_curr_sequence_number)) THEN
1149 v_term_rec.key_program_flag := 'Y';
1150 ELSE
1151 v_term_rec.key_program_flag := 'N';
1152 END IF;
1153
1154 -- create term record for future term
1155 check_and_create(
1156 p_term_rec => v_term_rec,
1157 p_update_rec => TRUE);
1158
1159 END LOOP;
1160
1161 END forward_gap_fill;
1162
1163
1164
1165
1166
1167 PROCEDURE create_update_term_rec(
1168 p_person_id IN NUMBER ,
1169 p_program_cd IN VARCHAR2,
1170 p_term_cal_type IN VARCHAR2,
1171 p_term_sequence_NUMBER IN NUMBER,
1172 p_ripple_frwrd IN boolean,
1173 p_update_rec IN BOOLEAN,
1174 p_message_name OUT NOCOPY VARCHAR2,
1175 p_coo_id IN NUMBER DEFAULT -1,
1176 p_key_program_flag IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1177 p_fee_cat IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1178 p_class_standing_id IN NUMBER DEFAULT -1,
1179 p_plan_sht_status IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1180 p_program_changed IN BOOLEAN DEFAULT FALSE
1181 )
1182 AS
1183 cursor c_valid_term IS
1184 SELECT 'x' FROM IGS_CA_INST ca, IGS_CA_TYPE ct
1185 WHERE ca.cal_type = p_term_cal_type
1186 AND ca.sequence_number = p_term_sequence_number
1187 AND ca.cal_type = ct.cal_type
1188 and ct.s_cal_cat = 'LOAD';
1189
1190
1191 l_dummy VARCHAR2(1);
1192 l_term_id IGS_EN_SPA_TERMS.term_record_id%TYPE;
1193 l_insert_rec BOOLEAN;
1194 new_term_rec EN_SPAT_REC_TYPE%TYPE;
1195 old_term_rec EN_SPAT_REC_TYPE%TYPE;
1196 l_username VARCHAR2(20);
1197
1198
1199 BEGIN
1200
1201 -- ## Checking whether term instance is a valid value.
1202 IF (p_term_cal_type IS NULL OR p_term_sequence_number IS NULL) THEN
1203
1204 p_message_name := 'IGS_EN_INVALID_LOAD_CAL' ;
1205 FND_MESSAGE.SET_NAME('IGS',p_message_name);
1209 ELSE
1206 IGS_GE_MSG_STACK.ADD;
1207 APP_EXCEPTION.RAISE_EXCEPTION;
1208
1210 OPEN c_valid_term;
1211 FETCH c_valid_term INTO l_dummy;
1212 IF (c_valid_term%NOTFOUND) THEN
1213 p_message_name := 'IGS_EN_INVALID_LOAD_CAL' ;
1214 FND_MESSAGE.SET_NAME('IGS',p_message_name);
1215 IGS_GE_MSG_STACK.ADD;
1216 APP_EXCEPTION.RAISE_EXCEPTION;
1217 END IF;
1218 END IF;
1219
1220
1221 -- Get the attribute values which are effective in the passed in term.
1222 -- It returns a record variable as out paramter which contains are effective attribute values.
1223 get_effective_attribute_values(p_person_id => p_person_id,
1224 p_program_cd => p_program_cd,
1225 p_term_cal_type => p_term_cal_type,
1226 p_term_sequence_number => p_term_sequence_number,
1227 p_term_rec => old_term_rec);
1228
1229
1230 set_param_attributes(
1231 p_person_id => p_person_id,
1232 p_program_cd => p_program_cd,
1233 p_term_cal_type => p_term_cal_type,
1234 p_term_sequence_number => p_term_sequence_number,
1235 p_coo_id => p_coo_id,
1236 p_key_program_flag => p_key_program_flag,
1237 p_fee_cat => p_fee_cat,
1238 p_class_standing_id => p_class_standing_id,
1239 p_plan_sht_status => p_plan_sht_status,
1240 p_old_term_rec => old_term_rec,
1241 p_new_term_rec => new_term_rec,
1242 p_program_changed => p_program_changed);
1243
1244 validate_term_rec(p_term_rec => new_term_rec);
1245 IF (p_ripple_frwrd) THEN
1246 ripple_frwd(
1247 p_person_id => p_person_id,
1248 p_program_cd => p_program_cd,
1249 p_coo_id =>p_coo_id ,
1250 p_term_cal_type => p_term_cal_type,
1251 p_term_sequence_number => p_term_sequence_number,
1252 p_fee_cat => p_fee_cat,
1253 p_class_standing_id => p_class_standing_id,
1254 p_term_rec => old_term_rec,
1255 p_program_changed => p_program_changed);
1256 END IF;
1257
1258 OPEN c_term_exists (old_term_rec.person_id,old_term_rec.program_cd,
1259 old_term_rec.program_version,old_term_rec.term_cal_type,
1260 old_term_rec.term_sequence_number);
1261 FETCH c_term_exists INTO l_term_id;
1262 IF (c_term_exists%NOTFOUND) THEN
1263 l_insert_rec := TRUE;
1264 ELSE
1265 l_insert_rec := FALSE;
1266 END IF;
1267 CLOSE c_term_exists;
1268 -- All attributes whose values have been passed in for the call have changed.
1269 -- Hence set those attributes which have a value passed to create_update_term_Rec .
1270
1271
1272 IF l_insert_rec THEN
1273
1274 backward_gap_fill(new_term_rec);
1275
1276 check_and_create(p_term_rec => new_term_rec,
1277 p_update_rec => TRUE,
1278 p_program_changed => p_program_changed);
1279
1280 forward_gap_fill(new_term_rec);
1281 ELSE
1282 -- If the record already exists then no gaps need not be filled.
1283 -- Only update the term record with the changed values
1284
1285 check_and_create(p_term_rec => new_term_rec,
1286 p_update_rec => TRUE,
1287 p_program_changed => p_program_changed);
1288 END IF;
1289 IF (p_key_program_flag = 'Y') THEN
1290
1291 change_key_program_to(p_person_id,
1292 p_program_cd,
1293 p_term_cal_type,
1294 p_term_sequence_number, new_term_rec);
1295 END IF;
1296
1297
1298
1299 END create_update_term_rec;
1300
1301
1302 PROCEDURE change_key_program_to(
1303 p_person_id IN NUMBER,
1304 p_program_cd IN VARCHAR2,
1305 p_term_cal_type IN VARCHAR2,
1306 p_term_sequence_NUMBER IN NUMBER,
1307 p_term_rec IN EN_SPAT_REC_TYPE%TYPE) AS
1308
1309 CURSOR c_future_key_terms IS
1310 SELECT spat.rowid, spat.*
1311 FROM igs_en_spa_terms spat, igs_ca_inst ca1, igs_ca_inst ca2
1312 WHERE ca1.cal_type = spat.term_cal_type
1313 AND ca1.sequence_number = spat.term_sequence_number
1314 AND ca2.cal_type = p_term_cal_type
1315 AND ca2.sequence_number = p_term_sequence_number
1316 AND ca1.start_dt >= ca2.start_dt
1317 and spat.person_id = p_person_id
1318 AND spat.key_program_flag = 'Y';
1319
1320 CURSOR c_latest_term_in_acad IS -- check if the latest term in acad is for this program
1321 SELECT spat.rowid, spat.program_cd, term_cal_type, term_sequence_number
1322 FROM igs_en_spa_terms spat, igs_ca_inst ca, igs_ca_inst_rel cir
1323 WHERE spat.person_id = p_person_id
1324 AND ca.cal_type = spat.term_cal_type
1328 AND cir.sup_cal_type = spat.acad_cal_type
1325 AND ca.sequence_number = spat.term_sequence_number
1326 AND cir.sub_cal_type = p_term_cal_type
1327 AND cir.sub_ci_sequence_number = p_term_sequence_number
1329 ORDER BY ca.start_dt DESC;
1330
1331 CURSOR c_dest_term IS
1332 SELECT spat.rowid, spat.term_record_id
1333 FROM igs_en_spa_terms spat
1334 WHERE person_id = p_person_id
1335 AND program_cd = p_program_cd
1336 AND term_cal_type = p_term_cal_type
1337 AND term_sequence_number = p_term_sequence_number;
1338
1339 CURSOR c_dest_fut_terms IS
1340 SELECT spat.rowid,spat.*
1341 FROM IGS_EN_SPA_TERMS spat,
1342 IGS_CA_INST_REL cr,
1343 IGS_CA_INST_REL cr2,
1344 IGS_CA_INST ci,
1345 IGS_PS_VER cv
1346 WHERE cr.sub_cal_type = p_term_cal_type
1347 AND cr.sub_ci_sequence_number = p_term_sequence_number
1348 AND cr.sup_cal_type = cr2.sup_cal_type
1349 AND cr2.sub_cal_type = spat.term_cal_type
1350 AND cr2.sub_ci_sequence_number = spat.term_sequence_number
1351 AND spat.person_id = p_person_id
1352 AND ci.cal_type = cr2.sub_cal_type
1353 AND ci.sequence_number = cr2.sub_ci_sequence_number
1354 AND ci.start_dt >=
1355 (SELECT start_dt
1356 FROM IGS_CA_INST
1357 WHERE cal_type = cr.sub_cal_type
1358 AND sequence_number = cr.sub_ci_sequence_number)
1359 AND ci.sequence_number <> p_term_sequence_number
1360 AND cv.course_cd = spat.program_cd
1361 AND cv.version_number = spat.program_version
1362 AND spat.program_cd = p_program_cd;
1363
1364 l_term_cal IGS_CA_INST.CAL_TYPE%TYPE;
1365 l_term_seq IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
1366 l_message_name VARCHAR2(2000);
1367 l_term_rec EN_SPAT_REC_TYPE%TYPE;
1368 l_rowid ROWID;
1369 l_term_id IGS_EN_SPA_TERMS.term_record_id%TYPE;
1370 l_program_cd IGS_PS_VER.COURSE_CD%TYPE;
1371
1372 BEGIN
1373 l_term_rec := p_term_rec;
1374 -- Reset all future key term records as Non-Key
1375 FOR rec_future_key_terms IN c_future_key_terms LOOP
1376
1377 IGS_EN_SPA_TERMS_PKG.update_row(
1378 x_rowid => rec_future_key_terms.rowid,
1379 x_term_record_id => rec_future_key_terms.term_record_id,
1380 x_person_id => rec_future_key_terms.person_id,
1381 x_program_cd => rec_future_key_terms.program_cd,
1382 x_program_version => rec_future_key_terms.program_version,
1383 x_acad_cal_type => rec_future_key_terms.acad_cal_type,
1384 x_term_cal_type => rec_future_key_terms.term_cal_type,
1385 x_term_sequence_number => rec_future_key_terms.term_sequence_number,
1386 x_key_program_flag => 'N',
1387 x_location_cd => rec_future_key_terms.location_cd,
1388 x_attendance_mode => rec_future_key_terms.attendance_mode,
1389 x_attendance_type => rec_future_key_terms.attendance_type,
1390 x_fee_cat => rec_future_key_terms.fee_cat,
1391 x_coo_id => rec_future_key_terms.coo_id,
1392 x_class_standing_id => rec_future_key_terms.class_standing_id,
1393 x_attribute_category => rec_future_key_terms.attribute_category,
1394 x_attribute1 => rec_future_key_terms.attribute1,
1395 x_attribute2 => rec_future_key_terms.attribute2,
1396 x_attribute3 => rec_future_key_terms.attribute3,
1397 x_attribute4 => rec_future_key_terms.attribute4,
1398 x_attribute5 => rec_future_key_terms.attribute5,
1399 x_attribute6 => rec_future_key_terms.attribute6,
1400 x_attribute7 => rec_future_key_terms.attribute7,
1401 x_attribute8 => rec_future_key_terms.attribute8,
1402 x_attribute9 => rec_future_key_terms.attribute9,
1403 x_attribute10 => rec_future_key_terms.attribute10,
1404 x_attribute11 => rec_future_key_terms.attribute11,
1405 x_attribute12 => rec_future_key_terms.attribute12,
1406 x_attribute13 => rec_future_key_terms.attribute13,
1407 x_attribute14 => rec_future_key_terms.attribute14,
1408 x_attribute15 => rec_future_key_terms.attribute15,
1409 x_attribute16 => rec_future_key_terms.attribute16,
1410 x_attribute17 => rec_future_key_terms.attribute17,
1411 x_attribute18 => rec_future_key_terms.attribute18,
1412 x_attribute19 => rec_future_key_terms.attribute19,
1413 x_attribute20 => rec_future_key_terms.attribute20,
1414 x_mode => 'R',
1415 x_plan_sht_status => rec_future_key_terms.plan_sht_status
1416 );
1417 END LOOP;
1418 OPEN c_dest_term;
1419 FETCH c_dest_term INTO l_rowid, l_term_id;
1420 CLOSE c_dest_term;
1421 -- Update the destination term record as Key
1422 IGS_EN_SPA_TERMS_PKG.update_row(
1423 x_rowid => l_rowid,
1424 x_term_record_id => l_term_id,
1425 x_person_id => l_term_rec.person_id,
1426 x_program_cd => l_term_rec.program_cd,
1427 x_program_version => l_term_rec.program_version,
1428 x_acad_cal_type => l_term_rec.acad_cal_type,
1429 x_term_cal_type => l_term_rec.term_cal_type,
1430 x_term_sequence_number => l_term_rec.term_sequence_number,
1431 x_key_program_flag => 'Y',
1432 x_location_cd => l_term_rec.location_cd,
1433 x_attendance_mode => l_term_rec.attendance_mode,
1434 x_attendance_type => l_term_rec.attendance_type,
1435 x_fee_cat => l_term_rec.fee_cat,
1436 x_coo_id => l_term_rec.coo_id,
1437 x_class_standing_id => l_term_rec.class_standing_id,
1438 x_attribute_category => l_term_rec.attribute_category,
1439 x_attribute1 => l_term_rec.attribute1,
1440 x_attribute2 => l_term_rec.attribute2,
1441 x_attribute3 => l_term_rec.attribute3,
1442 x_attribute4 => l_term_rec.attribute4,
1443 x_attribute5 => l_term_rec.attribute5,
1444 x_attribute6 => l_term_rec.attribute6,
1445 x_attribute7 => l_term_rec.attribute7,
1446 x_attribute8 => l_term_rec.attribute8,
1447 x_attribute9 => l_term_rec.attribute9,
1448 x_attribute10 => l_term_rec.attribute10,
1449 x_attribute11 => l_term_rec.attribute11,
1450 x_attribute12 => l_term_rec.attribute12,
1451 x_attribute13 => l_term_rec.attribute13,
1452 x_attribute14 => l_term_rec.attribute14,
1453 x_attribute15 => l_term_rec.attribute15,
1454 x_attribute16 => l_term_rec.attribute16,
1455 x_attribute17 => l_term_rec.attribute17,
1456 x_attribute18 => l_term_rec.attribute18,
1457 x_attribute19 => l_term_rec.attribute19,
1458 x_attribute20 => l_term_rec.attribute20,
1459 x_mode => 'R',
1460 x_plan_sht_status => NVL(l_term_rec.plan_sht_status,'NONE')
1461 );
1462 FOR rec_dest_fut_terms IN c_dest_fut_terms LOOP
1463
1464 -- Set all destination future terms as Key terms.
1465
1466 IGS_EN_SPA_TERMS_PKG.update_row(
1467 x_rowid => rec_dest_fut_terms.rowid,
1468 x_term_record_id => rec_dest_fut_terms.term_record_id,
1469 x_person_id => rec_dest_fut_terms.person_id,
1470 x_program_cd => rec_dest_fut_terms.program_cd,
1471 x_program_version => rec_dest_fut_terms.program_version,
1472 x_acad_cal_type => rec_dest_fut_terms.acad_cal_type,
1473 x_term_cal_type => rec_dest_fut_terms.term_cal_type,
1474 x_term_sequence_number => rec_dest_fut_terms.term_sequence_number,
1475 x_key_program_flag => 'Y',
1476 x_location_cd => rec_dest_fut_terms.location_cd,
1477 x_attendance_mode => rec_dest_fut_terms.attendance_mode,
1478 x_attendance_type => rec_dest_fut_terms.attendance_type,
1479 x_fee_cat => rec_dest_fut_terms.fee_cat,
1480 x_coo_id => rec_dest_fut_terms.coo_id,
1481 x_class_standing_id => rec_dest_fut_terms.class_standing_id,
1482 x_attribute_category => rec_dest_fut_terms.attribute_category,
1483 x_attribute1 => rec_dest_fut_terms.attribute1,
1484 x_attribute2 => rec_dest_fut_terms.attribute2,
1485 x_attribute3 => rec_dest_fut_terms.attribute3,
1486 x_attribute4 => rec_dest_fut_terms.attribute4,
1487 x_attribute5 => rec_dest_fut_terms.attribute5,
1488 x_attribute6 => rec_dest_fut_terms.attribute6,
1489 x_attribute7 => rec_dest_fut_terms.attribute7,
1490 x_attribute8 => rec_dest_fut_terms.attribute8,
1491 x_attribute9 => rec_dest_fut_terms.attribute9,
1492 x_attribute10 => rec_dest_fut_terms.attribute10,
1493 x_attribute11 => rec_dest_fut_terms.attribute11,
1494 x_attribute12 => rec_dest_fut_terms.attribute12,
1495 x_attribute13 => rec_dest_fut_terms.attribute13,
1496 x_attribute14 => rec_dest_fut_terms.attribute14,
1497 x_attribute15 => rec_dest_fut_terms.attribute15,
1498 x_attribute16 => rec_dest_fut_terms.attribute16,
1499 x_attribute17 => rec_dest_fut_terms.attribute17,
1500 x_attribute18 => rec_dest_fut_terms.attribute18,
1501 x_attribute19 => rec_dest_fut_terms.attribute19,
1502 x_attribute20 => rec_dest_fut_terms.attribute20,
1503 x_mode => 'R',
1504 x_plan_sht_status => rec_dest_fut_terms.plan_sht_status
1505 );
1506 END LOOP;
1507
1508
1509
1510 END change_key_program_to;
1511
1512 FUNCTION get_spat_fee_cat(
1513 p_person_id IN NUMBER,
1514 p_program_cd IN VARCHAR2,
1515 p_term_cal_type IN VARCHAR2,
1516 p_term_sequence_NUMBER IN NUMBER
1517 ) RETURN VARCHAR2
1518 AS
1519 -- ## Get the term details information for the effective term
1520 CURSOR c_term IS
1521 SELECT fee_cat from igs_en_spa_terms
1522 WHERE person_id = p_person_id
1523 AND program_cd = p_program_cd
1524 AND term_cal_type = p_term_cal_type
1525 AND term_sequence_number = p_term_sequence_number;
1526
1527 -- ## get the term record values for a term which is previous to the effective term
1528 CURSOR c_prev_term IS
1529 SELECT
1530 SPAT.fee_cat
1531 FROM
1532 IGS_EN_SPA_TERMS SPAT,
1533 IGS_CA_INST CI1,
1534 IGS_CA_INST CI2
1535 WHERE
1536 SPAT.PERSON_ID = p_person_id AND
1537 spat.program_cd = p_program_cd AND
1538 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
1539 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
1540 CI1.CAL_TYPE = p_term_cal_type AND
1541 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
1542 CI1.START_DT > CI2.START_DT AND
1543 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
1544 FROM IGS_CA_INST_REL
1545 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
1546 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
1547 ORDER BY CI2.START_DT DESC;
1548
1549 -- ## get the SPA values for the passed in program attempt
1550 CURSOR c_spa IS
1551 SELECT fee_cat
1552 FROM igs_en_stdnt_ps_att
1553 WHERE person_id = p_person_id
1554 AND course_cd = p_program_cd;
1555
1556 l_fee_cat igs_en_spa_terms.fee_cat%TYPE;
1557
1558 BEGIN
1559 --## 1. If term record exists for the effective term then get the attribute details
1560 --## from it, set in EN_SPAT_REC_TYPE and exit
1561 --## 2. Term record not found for effective term, hence move on to a term record
1562 --## which is immediately in the past and get the attribute information and
1563 --## set it in EN_SPAT_REC_TYPE and exit
1564 --## 3. If no term record is found for the program attempt then get the attribute
1565 --## information from the SPA.
1566
1567 OPEN c_term;
1568 FETCH c_term INTO l_fee_cat;
1569 IF (c_term%FOUND) THEN
1570 CLOSE c_term;
1571
1572 RETURN l_fee_cat;
1573 END IF;
1574 CLOSE c_term;
1575
1576 OPEN c_prev_term;
1577 FETCH c_prev_term INTO l_fee_cat;
1578 IF (c_prev_term%FOUND) THEN
1579 CLOSE c_prev_term;
1580 RETURN l_fee_cat;
1581 ELSE
1582 CLOSE c_prev_term;
1583 OPEN c_spa;
1584 FETCH c_spa INTO l_fee_cat;
1585 END IF;
1586 return l_fee_cat;
1587 END get_spat_fee_cat;
1588
1589 FUNCTION get_spat_class_standing(
1590 p_person_id IN NUMBER,
1591 p_program_cd IN VARCHAR2,
1592 p_term_cal_type IN VARCHAR2,
1593 p_term_sequence_NUMBER IN NUMBER
1594 ) RETURN NUMBER
1595 AS
1596
1597 -- ## Get the term details information for the effective term
1598 CURSOR c_term IS
1599 SELECT class_standing_id from igs_en_spa_terms
1600 WHERE person_id = p_person_id
1601 AND program_cd = p_program_cd
1602 AND term_cal_type = p_term_cal_type
1603 AND term_sequence_number = p_term_sequence_number;
1604
1605 -- ## get the term record values for a term which is previous to the effective term
1606 CURSOR c_prev_term IS
1607 SELECT
1608 SPAT.class_standing_id
1609 FROM
1610 IGS_EN_SPA_TERMS SPAT,
1611 IGS_CA_INST CI1,
1612 IGS_CA_INST CI2
1613 WHERE
1614 SPAT.PERSON_ID = p_person_id AND
1615 spat.program_cd = p_program_cd AND
1616 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
1617 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
1618 CI1.CAL_TYPE = p_term_cal_type AND
1619 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
1620 CI1.START_DT > CI2.START_DT AND
1621 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
1622 FROM IGS_CA_INST_REL
1623 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
1624 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
1625 ORDER BY CI2.START_DT DESC;
1626
1627 -- ## get the SPA values for the passed in program attempt
1628 CURSOR c_spa IS
1629 SELECT IGS_PR_CLASS_STD_ID
1630 FROM igs_en_stdnt_ps_att
1631 WHERE person_id = p_person_id
1632 AND course_cd = p_program_cd;
1633
1634 l_class_standing igs_en_spa_terms.class_standing_id%TYPE;
1635
1636 BEGIN
1637 --## 1. If term record exists for the effective term then get the attribute details
1638 --## from it, set in EN_SPAT_REC_TYPE and exit
1639 --## 2. Term record not found for effective term, hence move on to a term record
1640 --## which is immediately in the past and get the attribute information and
1641 --## set it in EN_SPAT_REC_TYPE and exit
1642 --## 3. If no term record is found for the program attempt then get the attribute
1643 --## information from the SPA.
1644
1645 OPEN c_term;
1646 FETCH c_term INTO l_class_standing;
1647 IF (c_term%FOUND) THEN
1648 CLOSE c_term;
1649
1650 RETURN l_class_standing;
1651 END IF;
1652 CLOSE c_term;
1653
1654 OPEN c_prev_term;
1655 FETCH c_prev_term INTO l_class_standing;
1656 IF (c_prev_term%FOUND) THEN
1657 CLOSE c_prev_term;
1658 RETURN l_class_standing;
1659 ELSE
1660 CLOSE c_prev_term;
1661 OPEN c_spa;
1662 FETCH c_spa INTO l_class_standing;
1663 END IF;
1664 return l_class_standing;
1665 END get_spat_class_standing;
1666
1667 FUNCTION get_spat_coo_id(
1668 p_person_id IN NUMBER,
1669 p_program_cd IN VARCHAR2,
1670 p_term_cal_type IN VARCHAR2,
1671 p_term_sequence_NUMBER IN NUMBER
1672 ) RETURN NUMBER
1673 AS
1674
1675 -- ## Get the term details information for the effective term
1676 CURSOR c_term IS
1677 SELECT coo_id from igs_en_spa_terms
1678 WHERE person_id = p_person_id
1679 AND program_cd = p_program_cd
1680 AND term_cal_type = p_term_cal_type
1681 AND term_sequence_number = p_term_sequence_number;
1682
1683 -- ## get the term record values for a term which is previous to the effective term
1684 CURSOR c_prev_term IS
1685 SELECT
1686 SPAT.coo_id
1687 FROM
1688 IGS_EN_SPA_TERMS SPAT,
1689 IGS_CA_INST CI1,
1690 IGS_CA_INST CI2
1691 WHERE
1692 SPAT.PERSON_ID = p_person_id AND
1693 spat.program_cd = p_program_cd AND
1694 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
1695 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
1696 CI1.CAL_TYPE = p_term_cal_type AND
1697 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
1698 CI1.START_DT > CI2.START_DT AND
1699 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
1700 FROM IGS_CA_INST_REL
1701 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
1702 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
1703 ORDER BY CI2.START_DT DESC;
1704
1705 -- ## get the SPA values for the passed in program attempt
1706 CURSOR c_spa IS
1707 SELECT coo_id
1708 FROM igs_en_stdnt_ps_att
1709 WHERE person_id = p_person_id
1710 AND course_cd = p_program_cd;
1711
1712 l_coo_id igs_en_spa_terms.coo_id%TYPE;
1713
1714 BEGIN
1715 --## 1. If term record exists for the effective term then get the attribute details
1716 --## from it, set in EN_SPAT_REC_TYPE and exit
1717 --## 2. Term record not found for effective term, hence move on to a term record
1718 --## which is immediately in the past and get the attribute information and
1719 --## set it in EN_SPAT_REC_TYPE and exit
1720 --## 3. If no term record is found for the program attempt then get the attribute
1721 --## information from the SPA.
1722
1723 OPEN c_term;
1724 FETCH c_term INTO l_coo_id;
1725 IF (c_term%FOUND) THEN
1726 CLOSE c_term;
1727
1728 RETURN l_coo_id;
1729 END IF;
1730 CLOSE c_term;
1731
1732 OPEN c_prev_term;
1733 FETCH c_prev_term INTO l_coo_id;
1734 IF (c_prev_term%FOUND) THEN
1735 CLOSE c_prev_term;
1736 RETURN l_coo_id;
1737 ELSE
1738 CLOSE c_prev_term;
1739 OPEN c_spa;
1740 FETCH c_spa INTO l_coo_id;
1741 END IF;
1742 return l_coo_id;
1743 END get_spat_coo_id;
1744
1745 FUNCTION get_spat_att_type (
1746 p_person_id IN NUMBER,
1747 p_program_cd IN VARCHAR2,
1748 p_term_cal_type IN VARCHAR2,
1749 p_term_sequence_NUMBER IN NUMBER
1750 ) RETURN VARCHAR2
1751 AS
1752
1753 -- ## Get the term details information for the effective term
1754 CURSOR c_term IS
1755 SELECT attendance_type from igs_en_spa_terms
1756 WHERE person_id = p_person_id
1757 AND program_cd = p_program_cd
1758 AND term_cal_type = p_term_cal_type
1759 AND term_sequence_number = p_term_sequence_number;
1760
1761 -- ## get the term record values for a term which is previous to the effective term
1762 CURSOR c_prev_term IS
1763 SELECT
1764 SPAT.attendance_type
1765 FROM
1766 IGS_EN_SPA_TERMS SPAT,
1767 IGS_CA_INST CI1,
1768 IGS_CA_INST CI2
1769 WHERE
1770 SPAT.PERSON_ID = p_person_id AND
1771 spat.program_cd = p_program_cd AND
1772 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
1773 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
1774 CI1.CAL_TYPE = p_term_cal_type AND
1775 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
1776 CI1.START_DT > CI2.START_DT AND
1777 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
1778 FROM IGS_CA_INST_REL
1779 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
1780 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
1781 ORDER BY CI2.START_DT DESC;
1782
1783 -- ## get the SPA values for the passed in program attempt
1784 CURSOR c_spa IS
1785 SELECT attendance_type
1786 FROM igs_en_stdnt_ps_att
1787 WHERE person_id = p_person_id
1788 AND course_cd = p_program_cd;
1789
1790 l_attendance_type igs_en_spa_terms.attendance_type%TYPE;
1791
1792 BEGIN
1793 --## 1. If term record exists for the effective term then get the attribute details
1794 --## from it, set in EN_SPAT_REC_TYPE and exit
1795 --## 2. Term record not found for effective term, hence move on to a term record
1796 --## which is immediately in the past and get the attribute information and
1797 --## set it in EN_SPAT_REC_TYPE and exit
1798 --## 3. If no term record is found for the program attempt then get the attribute
1799 --## information from the SPA.
1800
1801 OPEN c_term;
1802 FETCH c_term INTO l_attendance_type;
1803 IF (c_term%FOUND) THEN
1804 CLOSE c_term;
1805
1806 RETURN l_attendance_type;
1807 END IF;
1808 CLOSE c_term;
1809
1810 OPEN c_prev_term;
1811 FETCH c_prev_term INTO l_attendance_type;
1812 IF (c_prev_term%FOUND) THEN
1813 CLOSE c_prev_term;
1814 RETURN l_attendance_type;
1815 ELSE
1816 CLOSE c_prev_term;
1817 OPEN c_spa;
1818 FETCH c_spa INTO l_attendance_type;
1819 END IF;
1820 return l_attendance_type;
1821 END get_spat_att_type;
1822
1823 FUNCTION get_spat_att_mode(
1824 p_person_id IN NUMBER,
1825 p_program_cd IN VARCHAR2,
1826 p_term_cal_type IN VARCHAR2,
1827 p_term_sequence_NUMBER IN NUMBER
1828 ) RETURN VARCHAR2
1829 AS
1830
1831 -- ## Get the term details information for the effective term
1832 CURSOR c_term IS
1833 SELECT attendance_mode from igs_en_spa_terms
1834 WHERE person_id = p_person_id
1835 AND program_cd = p_program_cd
1836 AND term_cal_type = p_term_cal_type
1837 AND term_sequence_number = p_term_sequence_number;
1838
1839 -- ## get the term record values for a term which is previous to the effective term
1840 CURSOR c_prev_term IS
1841 SELECT
1842 SPAT.attendance_mode
1843 FROM
1844 IGS_EN_SPA_TERMS SPAT,
1845 IGS_CA_INST CI1,
1846 IGS_CA_INST CI2
1847 WHERE
1848 SPAT.PERSON_ID = p_person_id AND
1849 spat.program_cd = p_program_cd AND
1850 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
1851 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
1852 CI1.CAL_TYPE = p_term_cal_type AND
1853 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
1854 CI1.START_DT > CI2.START_DT AND
1855 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
1856 FROM IGS_CA_INST_REL
1857 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
1858 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
1859 ORDER BY CI2.START_DT DESC;
1860
1861 -- ## get the SPA values for the passed in program attempt
1862 CURSOR c_spa IS
1863 SELECT attendance_mode
1864 FROM igs_en_stdnt_ps_att
1865 WHERE person_id = p_person_id
1866 AND course_cd = p_program_cd;
1867
1868 l_attendance_mode igs_en_spa_terms.attendance_mode%TYPE;
1869
1870 BEGIN
1871 --## 1. If term record exists for the effective term then get the attribute details
1872 --## from it, set in EN_SPAT_REC_TYPE and exit
1873 --## 2. Term record not found for effective term, hence move on to a term record
1874 --## which is immediately in the past and get the attribute information and
1875 --## set it in EN_SPAT_REC_TYPE and exit
1876 --## 3. If no term record is found for the program attempt then get the attribute
1877 --## information from the SPA.
1878
1879 OPEN c_term;
1880 FETCH c_term INTO l_attendance_mode;
1881 IF (c_term%FOUND) THEN
1882 CLOSE c_term;
1883
1884 RETURN l_attendance_mode;
1888 OPEN c_prev_term;
1885 END IF;
1886 CLOSE c_term;
1887
1889 FETCH c_prev_term INTO l_attendance_mode;
1890 IF (c_prev_term%FOUND) THEN
1891 CLOSE c_prev_term;
1892 RETURN l_attendance_mode;
1893 ELSE
1894 CLOSE c_prev_term;
1895 OPEN c_spa;
1896 FETCH c_spa INTO l_attendance_mode;
1897 END IF;
1898 return l_attendance_mode;
1899 END get_spat_att_mode;
1900
1901 FUNCTION get_spat_location(
1902 p_person_id IN NUMBER,
1903 p_program_cd IN VARCHAR2,
1904 p_term_cal_type IN VARCHAR2,
1905 p_term_sequence_NUMBER IN NUMBER
1906 ) RETURN VARCHAR2
1907 AS
1908
1909 -- ## Get the term details information for the effective term
1910 CURSOR c_term IS
1911 SELECT location_cd from igs_en_spa_terms
1912 WHERE person_id = p_person_id
1913 AND program_cd = p_program_cd
1914 AND term_cal_type = p_term_cal_type
1915 AND term_sequence_number = p_term_sequence_number;
1916
1917 -- ## get the term record values for a term which is previous to the effective term
1918 CURSOR c_prev_term IS
1919 SELECT
1920 SPAT.location_cd
1921 FROM
1922 IGS_EN_SPA_TERMS SPAT,
1923 IGS_CA_INST CI1,
1924 IGS_CA_INST CI2
1925 WHERE
1926 SPAT.PERSON_ID = p_person_id AND
1927 spat.program_cd = p_program_cd AND
1928 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
1929 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
1930 CI1.CAL_TYPE = p_term_cal_type AND
1931 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
1932 CI1.START_DT > CI2.START_DT AND
1933 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
1934 FROM IGS_CA_INST_REL
1935 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
1936 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
1937 ORDER BY CI2.START_DT DESC;
1938
1939 -- ## get the SPA values for the passed in program attempt
1940 CURSOR c_spa IS
1941 SELECT location_cd
1942 FROM igs_en_stdnt_ps_att
1943 WHERE person_id = p_person_id
1944 AND course_cd = p_program_cd;
1945
1946 l_location_cd igs_en_spa_terms.location_cd%TYPE;
1947
1948 BEGIN
1949 --## 1. If term record exists for the effective term then get the attribute details
1950 --## from it, set in EN_SPAT_REC_TYPE and exit
1951 --## 2. Term record not found for effective term, hence move on to a term record
1952 --## which is immediately in the past and get the attribute information and
1953 --## set it in EN_SPAT_REC_TYPE and exit
1954 --## 3. If no term record is found for the program attempt then get the attribute
1955 --## information from the SPA.
1956
1957 OPEN c_term;
1958 FETCH c_term INTO l_location_cd;
1959 IF (c_term%FOUND) THEN
1960 CLOSE c_term;
1961
1962 RETURN l_location_cd;
1963 END IF;
1964 CLOSE c_term;
1965
1966 OPEN c_prev_term;
1967 FETCH c_prev_term INTO l_location_cd;
1968 IF (c_prev_term%FOUND) THEN
1969 CLOSE c_prev_term;
1970 RETURN l_location_cd;
1971 ELSE
1972 CLOSE c_prev_term;
1973 OPEN c_spa;
1974 FETCH c_spa INTO l_location_cd;
1975 END IF;
1976 return l_location_cd;
1977 END get_spat_location;
1978
1979 FUNCTION get_spat_program_version(
1980 p_person_id IN NUMBER,
1981 p_program_cd IN VARCHAR2,
1982 p_term_cal_type IN VARCHAR2,
1983 p_term_sequence_NUMBER IN NUMBER
1984 ) RETURN NUMBER
1985 AS
1986
1987 -- ## Get the term details information for the effective term
1988 CURSOR c_term IS
1989 SELECT program_version from igs_en_spa_terms
1990 WHERE person_id = p_person_id
1991 AND program_cd = p_program_cd
1992 AND term_cal_type = p_term_cal_type
1993 AND term_sequence_number = p_term_sequence_number;
1994
1995 -- ## get the term record values for a term which is previous to the effective term
1996 CURSOR c_prev_term IS
1997 SELECT
1998 SPAT.program_version
1999 FROM
2000 IGS_EN_SPA_TERMS SPAT,
2001 IGS_CA_INST CI1,
2002 IGS_CA_INST CI2
2003 WHERE
2004 SPAT.PERSON_ID = p_person_id AND
2005 spat.program_cd = p_program_cd AND
2006 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
2007 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
2008 CI1.CAL_TYPE = p_term_cal_type AND
2009 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
2010 CI1.START_DT > CI2.START_DT AND
2011 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
2012 FROM IGS_CA_INST_REL
2013 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
2014 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
2015 ORDER BY CI2.START_DT DESC;
2016
2017 -- ## get the SPA values for the passed in program attempt
2018 CURSOR c_spa IS
2019 SELECT version_number
2020 FROM igs_en_stdnt_ps_att
2021 WHERE person_id = p_person_id
2022 AND course_cd = p_program_cd;
2023
2027 --## 1. If term record exists for the effective term then get the attribute details
2024 l_program_version igs_en_spa_terms.program_version%TYPE;
2025
2026 BEGIN
2028 --## from it, set in EN_SPAT_REC_TYPE and exit
2029 --## 2. Term record not found for effective term, hence move on to a term record
2030 --## which is immediately in the past and get the attribute information and
2031 --## set it in EN_SPAT_REC_TYPE and exit
2032 --## 3. If no term record is found for the program attempt then get the attribute
2033 --## information from the SPA.
2034
2035 OPEN c_term;
2036 FETCH c_term INTO l_program_version;
2037 IF (c_term%FOUND) THEN
2038 CLOSE c_term;
2039
2040 RETURN l_program_version;
2041 END IF;
2042 CLOSE c_term;
2043
2044 OPEN c_prev_term;
2045 FETCH c_prev_term INTO l_program_version;
2046 IF (c_prev_term%FOUND) THEN
2047 CLOSE c_prev_term;
2048 RETURN l_program_version;
2049 ELSE
2050 CLOSE c_prev_term;
2051 OPEN c_spa;
2052 FETCH c_spa INTO l_program_version;
2053 END IF;
2054 return l_program_version;
2055 END get_spat_program_version;
2056
2057 PROCEDURE delete_terms_for_program(
2058 p_person_id IN NUMBER,
2059 p_program_cd IN VARCHAR2) AS
2060 l_term_cal_type IGS_CA_INST.cal_type%TYPE;
2061 l_term_sequence_number IGS_CA_INST.sequence_number%TYPE;
2062 l_acad_cal_type IGS_CA_INST.cal_type%TYPE;
2063 l_acad_ci_seq_num IGS_CA_INST.sequence_number%TYPE;
2064 l_load_ci_alt_code IGS_CA_INST.alternate_code%TYPE;
2065 l_load_ci_start_dt DATE;
2066 l_load_ci_end_dt DATE;
2067 l_message_name VARCHAR2(200);
2068 CURSOR c_future_terms(cp_term_cal_type IGS_CA_INST.cal_type%TYPE, cp_term_seq_num IGS_CA_INST.sequence_number%TYPE) IS
2069 select spat.rowid, spat.person_id, spat.program_cd, spat.term_cal_type,spat.term_sequence_number, spat.fee_cat
2070 from igs_en_spa_terms spat, igs_ca_inst c1, igs_ca_inst c2
2071 where person_id = p_person_id
2072 and program_cd = p_program_cd
2073 and term_cal_type = c1.cal_type
2074 and term_sequence_number = c1.sequence_number
2075 and cp_term_cal_type = c2.cal_type
2076 and cp_term_seq_num = c2.sequence_number
2077 and c1.start_dt >= c2.start_dt
2078 for update nowait;
2079 l_fee_assessed VARCHAR2(1);
2080 l_message VARCHAR2(2000);
2081 BEGIN
2082 igs_en_gen_015.enrp_get_eff_load_ci(p_person_id, p_program_cd, SYSDATE,
2083 l_acad_cal_type,
2084 l_acad_ci_seq_num,
2085 l_term_cal_type,
2086 l_term_sequence_number,
2087 l_load_ci_alt_code,
2088 l_load_ci_start_dt,
2089 l_load_ci_end_dt,
2090 l_message_name);
2091
2092 for rec_future_terms IN c_future_terms(l_term_cal_type, l_term_sequence_number)
2093 LOOP
2094
2095 -- key/pirmary will never be unconfirmed if there is an alternate program that can
2096 -- be made key/primary.
2097 -- If this is the only primary key program then its unconfirmed and SPA key program is set as 'N'
2098 -- Hence there is no need to find an alternate key primary delete_row(rowid);
2099 -- If fee is assessed for a term, then donot delete such term record.
2100 igs_fi_gen_008.chk_spa_rec_exists(
2101 p_n_person_id => rec_future_terms.person_id,
2102 p_v_course_cd => rec_future_terms.program_cd,
2103 p_v_load_cal_type=> rec_future_terms.term_cal_type,
2104 p_n_load_ci_seq => rec_future_terms.term_sequence_number,
2105 p_v_fee_cat => rec_future_terms.fee_cat,
2106 p_v_status => l_fee_assessed,
2107 p_v_message => l_message);
2108 IF l_fee_assessed = 'N' THEN
2109 --
2110 igs_en_spa_terms_pkg.delete_row(rec_future_terms.rowid);
2111 END IF;
2112
2113
2114 END LOOP;
2115
2116 END;
2117 FUNCTION get_spat_key_prog_flag(
2118 p_person_id IN NUMBER,
2119 p_program_cd IN VARCHAR2,
2120 p_term_cal_type IN VARCHAR2,
2121 p_term_sequence_NUMBER IN NUMBER
2122 ) RETURN VARCHAR2 AS
2123 l_key_program IGS_PS_VER.COURSE_CD%TYPE;
2124 BEGIN
2125 l_key_program := find_key_effective_for(p_person_id,p_term_cal_type, p_term_sequence_NUMBER);
2126 if (p_program_cd = l_key_program) THEN
2127 RETURN 'Y';
2128 ELSE
2129 RETURN 'N';
2130 END IF;
2131 END get_spat_key_prog_flag;
2132
2133
2134 FUNCTION get_miss_char RETURN VARCHAR2 AS
2135 BEGIN
2136 RETURN FND_API.G_MISS_CHAR;
2137 END;
2138 FUNCTION get_spat_att_type_desc (
2139 p_person_id IN NUMBER,
2140 p_program_cd IN VARCHAR2,
2141 p_term_cal_type IN VARCHAR2,
2142 p_term_sequence_NUMBER IN NUMBER
2143 ) RETURN VARCHAR2 AS
2144 l_att_type igs_en_atd_type.attendance_type%TYPE;
2145 l_att_desc igs_en_atd_type.description%TYPE;
2146 CURSOR c_att_desc (cp_att_type igs_en_atd_type.attendance_type%TYPE) IS
2147 SELECT description
2148 FROM igs_en_atd_type
2149 WHERE attendance_type = cp_att_type;
2150
2151 BEGIN
2155 p_term_cal_type,
2152 l_att_type := get_spat_att_type(
2153 p_person_id,
2154 p_program_cd,
2156 p_term_sequence_NUMBER);
2157 OPEN c_att_desc(l_att_type);
2158 FETCH c_att_desc INTO l_att_desc;
2159 CLOSE c_att_desc;
2160 RETURN l_att_desc;
2161 END;
2162
2163 FUNCTION get_spat_att_mode_desc (
2164 p_person_id IN NUMBER,
2165 p_program_cd IN VARCHAR2,
2166 p_term_cal_type IN VARCHAR2,
2167 p_term_sequence_NUMBER IN NUMBER
2168 ) RETURN VARCHAR2 AS
2169 l_att_mode igs_en_atd_mode.attendance_mode%TYPE;
2170 l_att_desc igs_en_atd_mode.description%TYPE;
2171 CURSOR c_att_desc (cp_att_mode igs_en_atd_mode.attendance_mode%TYPE) IS
2172 SELECT description
2173 FROM igs_en_atd_mode
2174 WHERE attendance_mode = cp_att_mode;
2175
2176 BEGIN
2177 l_att_mode := get_spat_att_mode(
2178 p_person_id,
2179 p_program_cd,
2180 p_term_cal_type,
2181 p_term_sequence_NUMBER);
2182 OPEN c_att_desc(l_att_mode);
2183 FETCH c_att_desc INTO l_att_desc;
2184 CLOSE c_att_desc;
2185 RETURN l_att_desc;
2186 END;
2187
2188 FUNCTION get_spat_location_desc (
2189 p_person_id IN NUMBER,
2190 p_program_cd IN VARCHAR2,
2191 p_term_cal_type IN VARCHAR2,
2192 p_term_sequence_NUMBER IN NUMBER
2193 ) RETURN VARCHAR2 AS
2194 l_loc igs_ad_location.location_cd%TYPE;
2195 l_loc_desc igs_ad_location.description%TYPE;
2196 CURSOR c_loc_desc (cp_loc igs_ad_location.location_cd%TYPE) IS
2197 SELECT description
2198 FROM igs_ad_location
2199 WHERE location_cd = cp_loc;
2200
2201 BEGIN
2202 l_loc := get_spat_location(
2203 p_person_id,
2204 p_program_cd,
2205 p_term_cal_type,
2206 p_term_sequence_NUMBER);
2207 OPEN c_loc_desc(l_loc);
2208 FETCH c_loc_desc INTO l_loc_desc;
2209 CLOSE c_loc_desc;
2210 RETURN l_loc_desc;
2211 END;
2212
2213
2214 FUNCTION get_spat_primary_prg(
2215 p_person_id IN NUMBER,
2216 p_program_cd IN VARCHAR2,
2217 p_term_cal_type IN VARCHAR2,
2218 p_term_sequence_NUMBER IN NUMBER
2219 ) RETURN VARCHAR2
2220 AS
2221 /* -----------------------------------------------------------------------
2222 Created By : rvangala
2223 Date Created By : 18-Nov-2003
2224 Purpose : Check whether the given program is a primary or
2225 secondary program in the given term.
2226 Returns PRIMARY, SECONDARY OR NULL
2227
2228 Change History
2229 Who When What
2230 stutta 31-Dec-2004 Added cursor cur_c3prev to pick up previous term
2231 records for the career, if no record is found for
2232 the term passed in as parameter.
2233 ----------------------------------------------------------------------*/
2234
2235 -- cursor to check whether program is a term record
2236 CURSOR cur_c1 IS
2237 SELECT 'x'
2238 FROM igs_en_spa_terms spat
2239 WHERE
2240 spat.person_id = p_person_id AND
2241 spat.program_cd = p_program_cd AND
2242 spat.term_cal_type = p_term_cal_type AND
2243 spat.term_sequence_number = p_term_sequence_number;
2244
2245 -- cursor to retrieve course_type for given program and person
2246 CURSOR cur_c2 IS
2247 SELECT ps.course_type
2248 FROM igs_ps_ver ps,
2249 igs_en_stdnt_ps_att spa
2250 WHERE
2251 spa.course_cd = p_program_cd AND
2252 spa.person_id = p_person_id AND
2253 spa.course_cd = ps.course_cd AND
2254 spa.version_number = ps.version_number;
2255
2256 -- cursor to check whether term record exists for some other program
2257 -- for the student in same career
2258 CURSOR cur_c3(p_program_type IN VARCHAR2) IS
2259 SELECT 'x'
2260 FROM igs_en_spa_terms spat
2261 WHERE spat.person_id = p_person_id AND
2262 p_program_type = (SELECT course_type FROM igs_ps_ver
2263 WHERE course_cd = spat.program_cd
2264 AND version_number = spat.program_version)
2265 AND spat.term_cal_type = p_term_cal_type
2266 AND spat.term_sequence_number = p_term_sequence_number;
2267
2268 CURSOR ci_start_dt (cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2269 cp_ci_Sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
2270 SELECT start_dt
2271 FROM IGS_CA_INST
2272 WHERE cal_type = cp_cal_type
2273 AND sequence_number = cp_ci_Sequence_number;
2274
2275 CURSOR cur_c3prev (cp_person_id igs_en_spa_terms.person_id%TYPE,
2276 cp_program_type igs_ps_ver.course_type%TYPE,
2277 cp_start_dt IGS_CA_INST.START_DT%TYPE) IS
2278 SELECT
2279 SPAT.PROGRAM_CD
2280 FROM
2281 IGS_EN_SPA_TERMS SPAT,
2282 IGS_CA_INST CI2
2283 WHERE
2284 SPAT.PERSON_ID = cp_person_id AND
2285 cp_program_type = (SELECT course_type
2286 FROM igs_ps_ver cv
2287 WHERE spat.program_cd = cv.course_cd
2288 AND spat.program_version = cv.version_number) AND
2289 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
2290 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
2291 CI2.START_DT < cp_start_dt
2292 ORDER BY CI2.START_DT DESC;
2293
2294 -- cursor to check whether given program for person exists in
2295 -- program attempt table
2296 CURSOR cur_c4 IS
2297 SELECT primary_program_type
2298 FROM igs_en_stdnt_ps_att
2299 WHERE
2300 person_id = p_person_id AND
2301 course_cd = p_program_cd;
2302
2303
2304 l_primary_prg IGS_EN_STDNT_PS_ATT.PRIMARY_PROGRAM_TYPE%TYPE;
2305 l_profile VARCHAR2(1);
2306 l_program_type IGS_PS_VER.COURSE_TYPE%TYPE;
2307 l_check VARCHAR2(1);
2308 l_program_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE;
2309 l_start_dt IGS_CA_INST.START_DT%TYPE;
2310
2311 BEGIN
2312
2313 -- check whether Career profile is set or not
2314 l_profile :=NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N');
2315
2316 -- if system is in Career model
2317 IF l_profile='Y' THEN
2318 OPEN cur_c1;
2319 FETCH cur_c1 INTO l_primary_prg;
2320
2321 -- check term record exists for given program,
2322 -- if record exists then it is the Primary program
2323 IF cur_c1%FOUND THEN
2324 l_primary_prg:='PRIMARY';
2325
2326 -- if term record for program does not exist
2327 ELSE
2328 -- retrieve course_type for program
2329 OPEN cur_c2;
2330 FETCH cur_c2 INTO l_program_type;
2331 CLOSE cur_c2;
2332
2333 -- and check whether term record exists for some other program for
2334 -- student in same career
2335 OPEN cur_c3(l_program_type);
2336 FETCH cur_c3 INTO l_check;
2337
2338 -- if term record exists for some other program for student in same career
2339 -- return SECONDARY
2340 IF cur_c3%FOUND THEN
2341 l_primary_prg := 'SECONDARY';
2342
2343 ELSE
2344 -- check if any previous term record exists for this career.
2345 OPEN ci_start_dt(p_term_cal_type, p_term_sequence_number);
2346 FETCH ci_start_dt INTO l_start_dt;
2347 CLOSE ci_start_dt;
2348 OPEN cur_c3prev(p_person_id, l_program_type, l_start_dt);
2349 FETCH cur_c3prev INTO l_program_cd;
2350 IF cur_c3prev%FOUND THEN -- if a previous term record is found for this career.
2351 IF l_program_cd = p_program_cd THEN
2352 -- This program is primary is the previous term
2353 l_primary_prg := 'PRIMARY';
2354 ELSE
2355 -- some other program is primary in previous term for the career
2356 l_primary_prg := 'SECONDARY';
2357 END IF;
2358 ELSE
2359 -- check whether given program for person exists in
2360 -- program attempt table
2361 OPEN cur_c4;
2362 FETCH cur_c4 INTO l_primary_prg;
2363
2364 -- if given program for person exists in program attempt table
2365 -- return primary_program_type value obtained
2366 IF cur_c4%FOUND THEN
2367 l_primary_prg:=l_primary_prg;
2368
2369 -- if given program for person does not exist in program attempt table
2370 -- return null
2371 ELSE
2372 l_primary_prg :=null;
2373 END IF;
2374
2375 CLOSE cur_c4;
2376 END IF;
2377 CLOSE cur_c3prev;
2378 END IF;
2379
2380 CLOSE cur_c3;
2381
2382 END IF;
2383
2384 CLOSE cur_c1;
2385 END IF;
2386
2387 RETURN l_primary_prg;
2388 END get_spat_primary_prg;
2389
2390
2391 PROCEDURE validate_terms(
2392 p_person_id IN NUMBER
2393 )
2394 AS
2395 /* -----------------------------------------------------------------------
2396 Created By : rvangala
2397 Date Created By : 18-Nov-2003
2398 Purpose : Validates number of terms containing key program
2399 for given person id
2400 Change History
2401 Who When What
2402
2403 ----------------------------------------------------------------------*/
2404
2405 -- cursor to fetch distinct term records for given person id
2406 CURSOR c_distinct_terms IS
2407 SELECT DISTINCT term_cal_type, term_sequence_number
2408 FROM IGS_EN_SPA_TERMS
2409 WHERE person_id = p_person_id;
2410
2411 -- cursor to fetch number of terms containing key program for given
2412 -- person id, term cal type and sequence number
2413 CURSOR c_count_key (cp_term_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2414 cp_term_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE)
2415 IS
2416 SELECT COUNT(key_program_flag)
2417 FROM IGS_EN_SPA_TERMS
2418 WHERE person_id = p_person_id
2419 AND term_cal_type = cp_term_cal_type
2420 AND term_sequence_number = cp_term_sequence_number
2421 AND key_program_flag = 'Y';
2422
2423 vn_key_count NUMBER(1);
2424
2425 BEGIN
2426 -- loop through the distinct term records for given person id
2427 FOR vr_distinct_terms_rec IN c_distinct_terms LOOP
2428
2429 vn_key_count := 0;
2430
2431 -- fetch number of terms containing key program
2432 OPEN c_count_key(vr_distinct_terms_rec.term_cal_type,
2433 vr_distinct_terms_rec.term_sequence_number);
2434 FETCH c_count_key INTO vn_key_count;
2435 CLOSE c_count_key;
2436
2437 -- if number of terms containing key program is greater than 1
2438 IF vn_key_count > 1 THEN
2439 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MORE_KEY_IN_TERM');
2440 app_exception.raise_exception;
2441 END IF;
2442
2443 END LOOP;
2444
2445 END validate_terms;
2446
2447 FUNCTION get_curr_term(
2448 p_cal_type IN VARCHAR2
2449 ) RETURN VARCHAR2
2450 AS
2451 /* -----------------------------------------------------------------------
2452 Created By : rvangala
2453 Date Created By : 18-Nov-2003
2454 Purpose : Returns the sequence number for the current term, for
2455 the given academic calendar
2456
2457 Change History
2458 rvangala 17-Feb-2004 Added formatting to the return value from the
2459 function, Bug #3441941
2460
2461 ----------------------------------------------------------------------*/
2462
2463 --local variables
2464 l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
2465 l_load_ci_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
2466 l_load_ci_alt_code IGS_CA_INST.ALTERNATE_CODE%TYPE;
2467 l_load_ci_start_dt DATE;
2468 l_load_ci_end_dt DATE;
2469 l_message_name VARCHAR2(80);
2470
2471 l_curr_term VARCHAR2(100);
2472
2473 BEGIN
2474
2475 -- call package igs_en_gen_015 to retrieve sequence number for current term
2476 -- for given academic calendar
2477 igs_en_gen_015.get_curr_acad_term_cal(
2478 p_acad_cal_type => p_cal_type,
2479 p_effective_dt => SYSDATE,
2480 p_load_cal_type => l_load_cal_type,
2481 p_load_ci_seq_num => l_load_ci_seq_num,
2482 p_load_ci_alt_code => l_load_ci_alt_code,
2483 p_load_ci_start_dt => l_load_ci_start_dt,
2484 p_load_ci_end_dt => l_load_ci_end_dt,
2485 p_message_name => l_message_name);
2486
2487 -- concatenate term calendar sequence number and term calendar type
2488 l_curr_term :=RPAD(l_load_ci_seq_num,6,' ') || l_load_cal_type;
2489
2490 RETURN l_curr_term;
2491
2492 END get_curr_term;
2493
2494
2495 FUNCTION get_prev_term(
2496 p_cal_type IN VARCHAR2
2497 ) RETURN VARCHAR2
2498 AS
2499 /* -----------------------------------------------------------------------
2500 Created By : rvangala
2501 Date Created By : 18-Nov-2003
2502 Purpose : Returns the sequence number for the immediate previous
2503 term for the current term, for the given academic
2504 calendar
2505
2506 Change History
2507 rvangala 17-Feb-2004 Added formatting in select clause to pick
2508 results from cur_c1, Bug #3441941
2509
2510 ----------------------------------------------------------------------*/
2511
2512 -- cursor to fetch immediate previous term for current term, for the
2513 -- given academic calendar
2514 CURSOR cur_c1(p_cur_term_cal IN VARCHAR2,
2515 p_cur_term_seq_num IN NUMBER) IS
2516 SELECT RPAD(ci2.sequence_number,6,' ') || ci2.cal_Type
2517 FROM igs_ca_inst ci2,
2518 igs_ca_inst_rel cir,
2519 igs_ca_type ct,
2523 ci2.cal_type = cir.sub_cal_type AND
2520 igs_ca_inst ci1,
2521 igs_ca_stat cs
2522 WHERE
2524 ci2.sequence_number = cir.sub_ci_sequence_number AND
2525 cir.sup_cal_type = p_cal_type AND
2526 ci2.cal_type = ct.cal_type AND
2527 ct.s_cal_cat = 'LOAD' AND
2528 cs.cal_status = ci1.cal_status AND
2529 cs.s_cal_status = 'ACTIVE' AND
2530 ci1.cal_type = p_cur_term_cal AND
2531 ci1.sequence_number = p_cur_term_seq_num AND
2532 ci2.start_dt < ci1.start_dt
2533 ORDER BY ci2.start_dt DESC;
2534
2535 --local variables
2536 l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
2537 l_load_ci_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
2538 l_load_ci_alt_code IGS_CA_INST.ALTERNATE_CODE%TYPE;
2539 l_load_ci_start_dt DATE;
2540 l_load_ci_end_dt DATE;
2541 l_message_name VARCHAR2(80);
2542
2543 l_prev_term VARCHAR2(100);
2544 BEGIN
2545 -- call package igs_en_gen_015 to retrieve sequence number for current term
2546 -- for given academic calendar
2547 igs_en_gen_015.get_curr_acad_term_cal(
2548 p_acad_cal_type => p_cal_type,
2549 p_effective_dt => SYSDATE,
2550 p_load_cal_type => l_load_cal_type,
2551 p_load_ci_seq_num => l_load_ci_seq_num,
2552 p_load_ci_alt_code => l_load_ci_alt_code,
2553 p_load_ci_start_dt => l_load_ci_start_dt,
2554 p_load_ci_end_dt => l_load_ci_end_dt,
2555 p_message_name => l_message_name);
2556
2557 -- fetch immediate previous term for the current term
2558 OPEN cur_c1(l_load_cal_type,l_load_ci_seq_num);
2559 FETCH cur_c1 INTO l_prev_term;
2560 CLOSE cur_c1;
2561
2562 RETURN l_prev_term;
2563 END get_prev_term;
2564
2565
2566 FUNCTION get_next_term(
2567 p_cal_type IN VARCHAR2
2568 ) RETURN VARCHAR2
2569 AS
2570 /* -----------------------------------------------------------------------
2571 Created By : rvangala
2572 Date Created By : 18-Nov-2003
2573 Purpose : Returns the sequence number for the immediate next term
2574 for the current term, for the given academic calendar
2575
2576 Change History
2577 rvangala 17-Feb-2004 Added formatting in select clause to pick
2578 results from cur_c1, Bug #3441941
2579
2580 ----------------------------------------------------------------------*/
2581
2582 -- cursor to fetch immediate next term for current term, for the
2583 -- given academic calendar
2584 CURSOR cur_c1(p_cur_term_cal IN VARCHAR2,
2585 p_cur_term_seq_num IN NUMBER) IS
2586 SELECT RPAD(ci2.sequence_number,6,' ') || ci2.cal_type
2587 FROM igs_ca_inst ci2,
2588 igs_ca_inst_rel cir,
2589 igs_ca_type ct,
2590 igs_ca_inst ci1,
2591 igs_ca_stat cs
2592 WHERE
2593 ci2.cal_type = cir.sub_cal_type AND
2594 ci2.sequence_number = cir.sub_ci_sequence_number AND
2595 cir.sup_cal_type = p_cal_type AND
2596 ci2.cal_type = ct.cal_type AND
2597 ct.s_cal_cat = 'LOAD' AND
2598 cs.cal_status = ci1.cal_status AND
2599 cs.s_cal_status = 'ACTIVE' AND
2600 ci1.cal_type = p_cur_term_cal AND
2601 ci1.sequence_number = p_cur_term_seq_num AND
2602 ci2.start_dt > ci1.start_dt
2603 ORDER BY ci2.start_dt;
2604
2605 --local variables
2606 l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
2607 l_load_ci_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
2608 l_load_ci_alt_code IGS_CA_INST.ALTERNATE_CODE%TYPE;
2609 l_load_ci_start_dt DATE;
2610 l_load_ci_end_dt DATE;
2611 l_message_name VARCHAR2(80);
2612
2613 l_next_term VARCHAR2(100);
2614 BEGIN
2615 -- call package igs_en_gen_015 to retrieve sequence number for current term
2616 -- for given academic calendar
2617 igs_en_gen_015.get_curr_acad_term_cal(
2618 p_acad_cal_type => p_cal_type,
2619 p_effective_dt => SYSDATE,
2620 p_load_cal_type => l_load_cal_type,
2621 p_load_ci_seq_num => l_load_ci_seq_num,
2622 p_load_ci_alt_code => l_load_ci_alt_code,
2623 p_load_ci_start_dt => l_load_ci_start_dt,
2624 p_load_ci_end_dt => l_load_ci_end_dt,
2625 p_message_name => l_message_name);
2626
2627 -- fetch immediate next term for the current term
2628 OPEN cur_c1(l_load_cal_type,l_load_ci_seq_num);
2629 FETCH cur_c1 INTO l_next_term;
2630 CLOSE cur_c1;
2631
2632 RETURN l_next_term;
2633 END get_next_term;
2634
2635
2636 FUNCTION get_spat_acad_cal_type(
2637 p_person_id IN NUMBER,
2638 p_program_cd IN VARCHAR2,
2639 p_term_cal_type IN VARCHAR2,
2640 p_term_sequence_NUMBER IN NUMBER
2641 ) RETURN VARCHAR2 AS
2642 -- ## Get the term details information for the effective term
2643 CURSOR c_term IS
2644 SELECT acad_cal_type from igs_en_spa_terms
2645 WHERE person_id = p_person_id
2646 AND program_cd = p_program_cd
2647 AND term_cal_type = p_term_cal_type
2648 AND term_sequence_number = p_term_sequence_number;
2649
2650 -- ## get the term record values for a term which is previous to the effective term
2651 CURSOR c_prev_term IS
2652 SELECT
2653 SPAT.acad_cal_type
2654 FROM
2655 IGS_EN_SPA_TERMS SPAT,
2656 IGS_CA_INST CI1,
2657 IGS_CA_INST CI2
2658 WHERE
2659 SPAT.PERSON_ID = p_person_id AND
2660 spat.program_cd = p_program_cd AND
2661 SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
2662 SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
2663 CI1.CAL_TYPE = p_term_cal_type AND
2664 CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
2665 CI1.START_DT > CI2.START_DT AND
2666 SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
2667 FROM IGS_CA_INST_REL
2668 WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
2669 AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
2670 ORDER BY CI2.START_DT DESC;
2671
2672 -- ## get the SPA values for the passed in program attempt
2673 CURSOR c_spa IS
2674 SELECT cal_type
2675 FROM igs_en_stdnt_ps_att
2676 WHERE person_id = p_person_id
2677 AND course_cd = p_program_cd;
2678
2679 l_acad_cal_type igs_en_spa_terms.acad_cal_type%TYPE;
2680
2681 BEGIN
2682 --## 1. If term record exists for the effective term then get the attribute details
2683 --## from it, set in EN_SPAT_REC_TYPE and exit
2684 --## 2. Term record not found for effective term, hence move on to a term record
2685 --## which is immediately in the past and get the attribute information and
2686 --## set it in EN_SPAT_REC_TYPE and exit
2687 --## 3. If no term record is found for the program attempt then get the attribute
2688 --## information from the SPA.
2689
2690 OPEN c_term;
2691 FETCH c_term INTO l_acad_cal_type;
2692 IF (c_term%FOUND) THEN
2693 CLOSE c_term;
2694
2695 RETURN l_acad_cal_type;
2696 END IF;
2697 CLOSE c_term;
2698
2699 OPEN c_prev_term;
2700 FETCH c_prev_term INTO l_acad_cal_type;
2701 IF (c_prev_term%FOUND) THEN
2702 CLOSE c_prev_term;
2703 RETURN l_acad_cal_type;
2704 ELSE
2705 CLOSE c_prev_term;
2706 OPEN c_spa;
2707 FETCH c_spa INTO l_acad_cal_type;
2708 CLOSE c_spa;
2709 END IF;
2710 RETURN l_acad_cal_type;
2711 END get_spat_acad_cal_type;
2712
2713
2714 END IGS_EN_SPA_TERMS_API;
[Home] [Help]