1 PACKAGE BODY IGS_EN_GEN_014 AS
2 /* $Header: IGSEN14B.pls 120.0 2005/06/02 03:33:13 appldev noship $ */
3
4 -------------------------------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 --prchandr 08-Jan-01 Enh Bug No: 2174101, As the Part of Change in IGSEN18B
8 -- Passing NULL as parameters to ENRP_CLC_SUA_EFTSU
9 -- ENRP_CLC_EFTSU_TOTAL for Key course cd and version number
10 -- anilk 10-Nov-2003 Audit special fee build, Added p_include_audit
11 -- ckasu 22-JUL-2004 added new Functions and Procedures Specs inorder to incorporate
12 -- the logic for getting current,future load calendars information.
13 -- as a part of Bug# 3784635
14 -------------------------------------------------------------------------------------------
15
16 Function Enrs_Clc_Sua_Cp(
17 p_person_id IGS_EN_SU_ATTEMPT_ALL.person_id%TYPE ,
18 p_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
19 p_crv_version_number IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE ,
20 p_unit_cd IGS_EN_SU_ATTEMPT_ALL.unit_cd%TYPE ,
21 p_unit_version_number IGS_EN_SU_ATTEMPT_ALL.version_number%TYPE ,
22 p_teach_cal_type IGS_EN_SU_ATTEMPT_ALL.cal_type%TYPE ,
23 p_teach_sequence_number IGS_EN_SU_ATTEMPT_ALL.ci_sequence_number%TYPE ,
24 p_uoo_id IN NUMBER ,
25 p_load_cal_type IGS_CA_INST_ALL.cal_type%TYPE ,
26 p_load_sequence_number IGS_CA_INST_ALL.sequence_number%TYPE ,
27 p_override_enrolled_cp IN NUMBER ,
28 p_override_eftsu IN NUMBER ,
29 p_truncate_ind IN VARCHAR2 ,
30 p_sca_cp_total IN NUMBER,
31 -- anilk, Audit special fee build
32 p_include_audit IN VARCHAR2 DEFAULT 'N' )
33 RETURN NUMBER AS
34 BEGIN
35 DECLARE
36 v_sua_eftsu NUMBER;
37 v_sua_cp NUMBER;
38 BEGIN
39 v_sua_eftsu := IGS_EN_PRC_LOAD.enrp_clc_sua_eftsu(
40 p_person_id,
41 p_course_cd,
42 p_crv_version_number,
43 p_unit_cd,
44 p_unit_version_number,
45 p_teach_cal_type,
46 p_teach_sequence_number,
47 p_uoo_id,
48 p_load_cal_type,
49 p_load_sequence_number,
50 p_override_enrolled_cp,
51 p_override_eftsu,
52 p_truncate_ind,
53 p_sca_cp_total,
54 NULL,
55 NULL,
56 v_sua_cp,
57 p_include_audit );
58 RETURN v_sua_cp;
59 END;
60 END enrs_clc_sua_cp;
61
62 Function Enrs_Clc_Sua_Eftsu(
63 p_person_id IGS_EN_SU_ATTEMPT_ALL.person_id%TYPE ,
64 p_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
65 p_crv_version_number IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE ,
66 p_unit_cd IGS_EN_SU_ATTEMPT_ALL.unit_cd%TYPE ,
67 p_unit_version_number IGS_EN_SU_ATTEMPT_ALL.version_number%TYPE ,
68 p_teach_cal_type IGS_EN_SU_ATTEMPT_ALL.cal_type%TYPE ,
69 p_teach_sequence_number IGS_EN_SU_ATTEMPT_ALL.ci_sequence_number%TYPE ,
70 p_uoo_id IN NUMBER ,
71 p_load_cal_type IGS_CA_INST_ALL.cal_type%TYPE ,
72 p_load_sequence_number IGS_CA_INST_ALL.sequence_number%TYPE ,
73 p_override_enrolled_cp IN NUMBER ,
74 p_override_eftsu IN NUMBER ,
75 p_truncate_ind IN VARCHAR2 ,
76 p_sca_cp_total IN NUMBER ,
77 -- anilk, Audit special fee build
78 p_include_audit IN VARCHAR2 DEFAULT 'N' )
79 RETURN NUMBER AS
80 BEGIN
81 DECLARE
82 v_sua_cp NUMBER;
83 BEGIN
84 RETURN IGS_EN_PRC_LOAD.enrp_clc_sua_eftsu(
85 p_person_id,
86 p_course_cd,
87 p_crv_version_number,
88 p_unit_cd,
89 p_unit_version_number,
90 p_teach_cal_type,
91 p_teach_sequence_number,
92 p_uoo_id,
93 p_load_cal_type,
94 p_load_sequence_number,
95 p_override_enrolled_cp,
96 p_override_eftsu,
97 p_truncate_ind,
98 p_sca_cp_total,
99 NULL,
100 NULL,
101 v_sua_cp,
102 p_include_audit );
103 END;
104 END enrs_clc_sua_eftsu;
105
106 Function Enrs_Clc_Sua_Eftsut(
107 P_PERSON_ID IN NUMBER ,
108 P_COURSE_CD IN VARCHAR2 ,
109 P_CRV_VERSION_NUMBER IN NUMBER ,
110 P_UNIT_CD IN VARCHAR2 ,
111 P_UNIT_VERSION_NUMBER IN NUMBER ,
112 P_TEACH_CAL_TYPE IN VARCHAR2 ,
113 P_TEACH_SEQUENCE_NUMBER IN NUMBER ,
114 p_uoo_id IN NUMBER ,
115 p_override_enrolled_cp IN NUMBER ,
116 p_override_eftsu IN NUMBER ,
117 p_sca_cp_total IN NUMBER )
118 RETURN NUMBER AS
119 BEGIN
120 DECLARE
121 v_original_eftsu NUMBER;
122 BEGIN
123 RETURN IGS_EN_PRC_LOAD.enrp_clc_sua_eftsut(
124 p_person_id,
125 p_course_cd,
126 p_crv_version_number,
127 p_unit_cd,
128 p_unit_version_number,
129 p_teach_cal_type,
130 p_teach_sequence_number,
131 p_uoo_id,
132 p_override_enrolled_cp,
133 p_override_eftsu,
134 p_sca_cp_total,
135 v_original_eftsu);
136 END;
137 END enrs_clc_sua_eftsut;
138
139 Function Enrs_Get_Acad_Alt_Cd(
140 p_cal_type IN VARCHAR2 ,
141 p_ci_sequence_number IN NUMBER )
142 RETURN VARCHAR2 AS
143 v_acad_cal_type IGS_CA_INST.cal_type%TYPE;
144 v_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
145 v_acad_ci_start_dt IGS_CA_INST.start_dt%TYPE;
146 v_acad_ci_end_dt IGS_CA_INST.end_dt%TYPE;
147 v_message_name Varchar2(30);
148 BEGIN
149 RETURN IGS_EN_GEN_002.enrp_get_acad_alt_cd(p_cal_type,
150 p_ci_sequence_number,
151 v_acad_cal_type,
152 v_acad_ci_sequence_number,
153 v_acad_ci_start_dt,
154 v_acad_ci_end_dt,
155 v_message_name);
156 END enrs_get_acad_alt_cd;
157
158 Function Enrs_Get_Acai_Cndtnl(
159 p_adm_cndtnl_offer_status IN VARCHAR2 ,
160 p_cndtnl_off_must_be_stsfd_ind IN VARCHAR2 DEFAULT 'N')
161 RETURN VARCHAR2 AS
162 v_message_name Varchar2(30);
163 v_s_adm_cndtnl_offer_status
164 IGS_AD_CNDNL_OFRSTAT.s_adm_cndtnl_offer_status%TYPE;
165 v_other_detail VARCHAR2(255);
166 BEGIN
167 IF IGS_EN_VAL_SCA.ENRP_VAL_ACAI_CNDTNL(
168 p_adm_cndtnl_offer_status,
169 p_cndtnl_off_must_be_stsfd_ind,
170 v_s_adm_cndtnl_offer_status,
171 v_message_name) THEN
172 RETURN 'Y';
173 ELSE
174 RETURN 'N';
175 END IF;
176 /*
177 EXCEPTION
178 WHEN OTHERS THEN
179 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
180 App_Exception.Raise_Exception;
181 */
182 END enrs_get_acai_cndtnl ;
183
184 Function Enrs_Get_Sca_Comm(
185 p_person_id IN NUMBER ,
186 p_course_cd IN VARCHAR2 ,
187 p_student_confirmed_ind IN VARCHAR2 DEFAULT 'N',
188 p_effective_date IN DATE )
189 RETURN VARCHAR2 AS
190 BEGIN
191 IF IGS_EN_GEN_006.ENRP_GET_SCA_COMM(p_person_id,
192 p_course_cd,
193 p_student_confirmed_ind,
194 p_effective_date) THEN
195 RETURN 'NEW';
196 ELSE
197 RETURN 'RETURN';
198 END IF;
199 END enrs_get_sca_comm;
200
201 Function Enrs_Get_Sca_Elgbl(
202 p_person_id IN NUMBER ,
203 p_course_cd IN VARCHAR2 ,
204 p_student_comm_type IN VARCHAR2 ,
205 p_acad_cal_type IN VARCHAR2 ,
206 p_acad_ci_sequence_number IN NUMBER )
207 RETURN VARCHAR2 AS
208 v_message_name Varchar2(30);
209 v_other_detail VARCHAR2(255);
210 BEGIN
211 IF IGS_EN_GEN_006.ENRP_GET_SCA_ELGBL(
212 p_person_id,
213 p_course_cd,
214 p_student_comm_type,
215 p_acad_cal_type,
216 p_acad_ci_sequence_number,
217 'N',
218 v_message_name) THEN
219 RETURN 'TRUE';
220 ELSE
221 RETURN 'FALSE';
222 END IF;
223 /*
224 EXCEPTION
225 WHEN OTHERS THEN
226 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
227 App_Exception.Raise_Exception;
228 */
229 END enrs_get_sca_elgbl ;
230
231 Function Enrs_Get_Sca_Trnsfr(
232 p_person_id IN NUMBER ,
233 p_course_cd IN VARCHAR2 )
234 RETURN VARCHAR2 AS
235 v_message_name Varchar2(30);
236 BEGIN
237 IF IGS_EN_GEN_006.enrp_get_sca_trnsfr(
238 p_person_id,
239 p_course_cd,
240 v_message_name) = FALSE THEN
241 RETURN 'N';
242 ELSE
243 RETURN 'Y';
244 END IF;
245 END enrs_get_sca_trnsfr;
246
247 Function Enrs_Get_Within_Ci(
248 p_sup_cal_type IN VARCHAR2 ,
249 p_sup_sequence_number IN NUMBER ,
250 p_sub_cal_type IN VARCHAR2 ,
251 p_sub_sequence_number IN NUMBER ,
252 p_direct_match_ind IN VARCHAR2 )
253 RETURN VARCHAR2 AS
254 v_p_direct_match_ind BOOLEAN;
255 BEGIN
256 -- Convert char to boolean.
257 IF p_direct_match_ind = 'Y' THEN
258 v_p_direct_match_ind := TRUE;
259 ELSE
260 v_p_direct_match_ind := FALSE;
261 END IF;
262 IF IGS_EN_GEN_008.enrp_get_within_ci(p_sup_cal_type,
263 p_sup_sequence_number,
264 p_sub_cal_type,
265 p_sub_sequence_number,
266 v_p_direct_match_ind) = TRUE THEN
267 RETURN 'Y';
268 ELSE
269 RETURN 'N';
270 END IF;
271 END enrs_get_within_ci;
272
273
274 PROCEDURE get_all_cur_load_cal (
275 p_acad_cal_type IN VARCHAR2,
276 p_effective_dt IN DATE,
277 p_load_cal_table_info_str OUT NOCOPY VARCHAR2
278 ) AS
279
280 /*------------------------------------------------------------------
281 --Created by : CKASU, Oracle IDC
282 --Date created: 21-JUL-2004
283 --this was created as a part of Bug#3784635
284 --Purpose: This Procedure takes academic cal type and effective date as input and
285 --gets all the current term calendar information and returns p_load_cal_table_info_str
286 --which contains all current calendar info concatenated by '||' where current
287 --calendar info is a combination of sequencenumberand cal_type seperated by '*'.
288 --
289 --
290 --Known limitations/enhancements and/or remarks:
291 --
292 --Change History:
293 --Who When What
294
295 --------------------------------------------------------------------*/
296
297 cst_active VARCHAR2(10);
298 cst_load VARCHAR2(10);
299
300 CURSOR c_all_cur_load_cal (cp_cal_type igs_ca_inst.cal_type%TYPE,
301 cp_effective_dt DATE
302 )
303 IS
304 SELECT DISTINCT ci.cal_type,
305 ci.sequence_number,
306 ci.alternate_code,
307 ci.start_dt,
308 ci.end_dt,
309 ci.description
310 FROM igs_ca_type ct,
311 igs_ca_inst ci,
312 igs_ca_stat cs,
313 igs_ca_inst_rel cir
314 WHERE cs.s_cal_status = cst_active
315 AND ci.cal_status = cs.cal_status
316 AND ct.s_cal_cat = cst_load
317 AND ci.cal_type = ct.cal_type
318 AND CIR.SUB_CAL_TYPE = CI.CAL_TYPE
319 AND cir.sub_ci_sequence_number =ci.sequence_number
320 AND cir.sup_cal_type = cp_cal_type
321 AND p_effective_dt Between ci.start_dt AND ci.end_dt
322 ORDER BY ci.start_dt;
323
324 l_all_cur_load_cal_rec c_all_cur_load_cal%ROWTYPE;
325 l_load_cal_table_info load_cal_table_type;
326 l_next_row Number;
327
328 BEGIN
329
330 cst_active := 'ACTIVE';
331 cst_load := 'LOAD';
332
333 -- encapsulating all the current term details in l_load_cal_table_info table.
334 FOR l_all_cur_load_cal_rec IN c_all_cur_load_cal(p_acad_cal_type,p_effective_dt) LOOP
335 l_next_row := NVL(l_load_cal_table_info.LAST,0) + 1;
336 l_load_cal_table_info(l_next_row) := l_all_cur_load_cal_rec;
337 END LOOP;
338
339 p_load_cal_table_info_str := get_seqno_caltyp_from_caltable(l_load_cal_table_info);
340 --this returns the concatened string which a contains term details seperated by '||'
341 --and term details itself is represented as a combination of 'sequence_no*cal_type'
342
343 END get_all_cur_load_cal;
344
345
346 PROCEDURE get_all_future_load_cal (
347 p_acad_cal_type IN VARCHAR2,
348 p_future_ld_cal_table_info_str OUT NOCOPY VARCHAR2
349 ) AS
350
351 /*------------------------------------------------------------------
352 --Created by : CKASU, Oracle IDC
353 --Date created: 21-JUL-2004
354 --this was created as a part of Bug#3784635
355 --Purpose: This Procedure takes academic cal type as input and gets all
356 --the future term calendar information and returns p_future_ld_cal_table_info_str
357 --which contains all future calendar info concatenated by '||' where current
358 --calendar info is a combination of sequencenumberand cal_type seperated by '*'.
359 --
360 --Known limitations/enhancements and/or remarks:
361 --
362 --Change History:
363 --Who When What
364
365 --------------------------------------------------------------------*/
366
367 cst_active VARCHAR2(10);
368 cst_load VARCHAR2(10);
369
370 CURSOR c_all_fut_load_cal (cp_cal_type igs_ca_inst.cal_type%TYPE,
371 cp_cur_cal_erly_st_dt igs_ca_inst.start_dt%TYPE)
372 IS
373 SELECT DISTINCT ci.cal_type,
374 ci.sequence_number,
375 ci.alternate_code,
376 ci.start_dt,
377 ci.end_dt,
378 ci.description
379 FROM igs_ca_type ct,
380 igs_ca_inst ci,
381 igs_ca_stat cs,
382 igs_ca_inst_rel cir
383 WHERE cs.s_cal_status = cst_active
384 AND ci.cal_status = cs.cal_status
385 AND ct.s_cal_cat = cst_load
386 AND ci.cal_type = ct.cal_type
387 AND CIR.SUB_CAL_TYPE = CI.CAL_TYPE
388 AND cir.sub_ci_sequence_number =ci.sequence_number
389 AND cir.sup_cal_type = cp_cal_type
390 AND ci.start_dt > cp_cur_cal_erly_st_dt
391 ORDER BY ci.start_dt ;
392
393 l_future_ld_cal_table_info load_cal_table_type;
394 l_cur_load_cal_table_info_str VARCHAR2(2000);
395 l_cur_cal_erly_st_dt igs_ca_inst.start_dt%TYPE;
396 l_cur_load_cal_table_info load_cal_table_type;
397 l_next_row NUMBER;
398
399 BEGIN
400
401 cst_active := 'ACTIVE';
402 cst_load := 'LOAD';
403 -- getting all current term calendar details
404 get_all_cur_load_cal (p_acad_cal_type,SYSDATE,l_cur_load_cal_table_info_str);
405
406 --populates current term calendar details in to l_cur_load_cal_table_info pl/sql table
407 --where each record in table contains caltype,sequenceno,start date,end date,description
408 l_cur_load_cal_table_info := get_cal_tbl_frm_caltyp_seq_lst(l_cur_load_cal_table_info_str);
409
410 --gets the earlier start date among tha start dates of all current term calendars
411 IF l_cur_load_cal_table_info IS NOT NULL THEN
412 IF l_cur_load_cal_table_info.count > 0 THEN
413 l_cur_cal_erly_st_dt := l_cur_load_cal_table_info(1).p_load_ci_start_dt ;
414 END IF;
415 END IF;
416
417 --All the Terms calendars whose startdate is greater than earlier start are selected
418 --and those term calendars which are not part of Current calendars are populated into
419 --future term calendar l_future_ld_cal_table_info pl/sql table where each record in
420 --table contains caltype,sequenceno,start date,end date,description
421
422 FOR c_fut_load_cal_rec IN c_all_fut_load_cal( p_acad_cal_type, l_cur_cal_erly_st_dt) LOOP
423
424 IF NOT is_fut_cal_exists_as_cur_cal(l_cur_load_cal_table_info,c_fut_load_cal_rec) THEN
425 l_next_row := NVL(l_future_ld_cal_table_info.LAST,0) + 1;
426 l_future_ld_cal_table_info(l_next_row) := c_fut_load_cal_rec;
427 END IF;
428
429 END LOOP;
430
431 --get_seqno_caltyp_from_caltable takes l_future_ld_cal_table_info table as parameter and
432 --returns the concatened string which contains term details seperated by '||' and term
433 --details itself is represented as a combination of 'sequence_no*cal_type'.
434 p_future_ld_cal_table_info_str := get_seqno_caltyp_from_caltable(l_future_ld_cal_table_info);
435
436 END get_all_future_load_cal;
437
438 FUNCTION get_cal_tbl_frm_caltyp_seq_lst (
439 p_seqno_caltype_info VARCHAR2
440 ) RETURN load_cal_table_type AS
441
442 /*------------------------------------------------------------------
443 --Created by : CKASU, Oracle IDC
444 --Date created: 21-JUL-2004
445 --this was created as a part of Bug#3784635
446 --Purpose: This function takes calendar information which contains
447 --calendar info concatenated by '||' where current calendar info
448 --is a combination of sequencenumber and cal_type seperated by '*'
449 --(Example: 300*TERM SU||400*TERM SP||500*DEVRY LOAD ) as input
450 --and gets the term Calendar details from the calendar info and encapsulates
451 --the information in pl/sql table and returns the Table type as ouput
452 --pl/sql table returned contains Records as elements.Each record
453 --contains caltype,sequenceno,start date,end date,description
454 --
455 --Known limitations/enhancements and/or remarks:
456 --
457 --Change History:
458 --Who When What
459
460 --------------------------------------------------------------------*/
461
462 l_strtpoint INTEGER;
463 l_endpoint INTEGER;
464 l_cindex INTEGER;
465 l_pre_cindex INTEGER;
466 l_nth_occurence INTEGER;
467 l_seqno_caltype_info VARCHAR2(2000);
468 l_load_cal_table_info load_cal_table_type ;
469 l_seqno_and_caltype VARCHAR2(100);
470 l_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
471 l_cal_seqno IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
472 l_cal_seq_sep_index INTEGER;
473 l_next_row INTEGER;
474 CURSOR c_get_cal_inst_info(cp_caltype IGS_CA_INST.CAL_TYPE%TYPE,
475 cp_cal_seqno IGS_CA_INST.SEQUENCE_NUMBER%TYPE)
476 IS
477 SELECT ci.cal_type,
478 ci.sequence_number,
479 ci.alternate_code,
480 ci.start_dt,
481 ci.end_dt,
482 ci.description
483 FROM igs_ca_inst ci
484 WHERE ci.cal_type = cp_caltype
485 AND ci.sequence_number = cp_cal_seqno;
486
487 c_load_cal_rec c_get_cal_inst_info%ROWTYPE;
488
489 BEGIN
490
491 l_strtpoint := 0;
492 l_pre_cindex := -1;
493 l_nth_occurence := 1;
494
495 l_seqno_caltype_info := p_seqno_caltype_info;
496
497 IF l_seqno_caltype_info IS NULL THEN
498 RETURN l_load_cal_table_info;
499 END IF;
500
501 l_seqno_caltype_info := l_seqno_caltype_info || '||';
502 l_cindex := INSTR(l_seqno_caltype_info,'||',1,l_nth_occurence);
503 -- getting the poistion of first occurence of '||'
504 WHILE (l_cindex <> 0 ) LOOP
505 l_strtpoint := l_pre_cindex + 2;
506 l_endpoint := l_cindex - l_strtpoint;
507 l_pre_cindex := l_cindex;
508 l_seqno_and_caltype := substr(l_seqno_caltype_info,l_strtpoint,l_endpoint);
509 -- l_seqno_and_caltype contains sequence_number*cal_type
510 l_cal_seq_sep_index := INSTR(l_seqno_and_caltype,'*',1);
511 l_cal_seqno:= SUBSTR(l_seqno_and_caltype,1,l_cal_seq_sep_index - 1);
512 l_cal_type := SUBSTR(l_seqno_and_caltype,l_cal_seq_sep_index + 1);
513 -- l_cal_seqno ,l_cal_type contains extratcs sequence_number and
514 --cal_type from l_seqno_and_caltype
515
516 --this cursor gets the details of calendar whose sequence number and
517 --cal_type are l_cal_seqno ,l_cal_typ and populates l_load_cal_table_info
518 --table with this information.
519 OPEN c_get_cal_inst_info(l_cal_type,TO_NUMBER(l_cal_seqno));
520 FETCH c_get_cal_inst_info INTO c_load_cal_rec;
521 IF c_get_cal_inst_info%FOUND THEN
522 l_next_row := NVL(l_load_cal_table_info.LAST,0) +1;
523 l_load_cal_table_info(l_next_row) := c_load_cal_rec;
524 END IF;
525 CLOSE c_get_cal_inst_info;
526 --now increasing l_nth_occurence by 1 inorder to get index for the next
527 --occurence of '||'
528 l_nth_occurence := l_nth_occurence + 1;
529 l_cindex := INSTR(l_seqno_caltype_info,'||',1,l_nth_occurence);
530
531 END LOOP;
532 RETURN l_load_cal_table_info;
533
534 END get_cal_tbl_frm_caltyp_seq_lst;
535
536 FUNCTION get_cur_ld_cal_with_erly_st_dt (
537 p_load_cal_table_info_str IN VARCHAR2
538 ) RETURN VARCHAR2 AS
539
540 /*------------------------------------------------------------------
541 --Created by : CKASU, Oracle IDC
542 --Date created: 21-JUL-2004
543 --this was created as a part of Bug#3784635
544 --Purpose: This function takes calendar information which contains
545 --calendar info concatenated by '||' where current load calendar info
546 --is a combination of sequencenumber and cal_type seperated by '*'
547 --(Example: 300*TERM SU||400*TERM SP||500*DEVRY LOAD ) as input
548 --and gets the term Calendar details which has earlier start date
549 --
550 --
551 --Known limitations/enhancements and/or remarks:
552 --
553 --Change History:
554 --Who When What
555 ckasu 22-JUL-2004 changed the code in if case by removing DELETE Function
556 --------------------------------------------------------------------*/
557
558 l_load_cal_table_info load_cal_table_type;
559 l_load_cal_with_erly_st_dt load_cal_table_type;
560 l_load_cal_table_info_str VARCHAR2(2000);
561
562 BEGIN
563
564 --populates current term calendar details in to l_cur_load_cal_table_info pl/sql table
565 --where each record in table contains caltype,sequenceno,start date,end date,description
566
567 l_load_cal_table_info := get_cal_tbl_frm_caltyp_seq_lst(p_load_cal_table_info_str);
568
569 --this deletes all other records in table except the first record which is the
570 --current calendar with earlier startdate.
571 IF l_load_cal_table_info IS NOT NULL THEN
572 IF l_load_cal_table_info.count > 0 THEN
573 l_load_cal_with_erly_st_dt(1) := l_load_cal_table_info(1);
574 l_load_cal_table_info_str := get_seqno_caltyp_from_caltable(l_load_cal_with_erly_st_dt);
575 RETURN l_load_cal_table_info_str;
576 END IF;
577 END IF;
578 RETURN l_load_cal_table_info_str;
579
580 END get_cur_ld_cal_with_erly_st_dt;
581
582 FUNCTION get_load_eff_dt_alias
583 RETURN VARCHAR2 AS
584
585 /*------------------------------------------------------------------
586 --Created by : CKASU, Oracle IDC
587 --Date created: 21-JUL-2004
588 --this was created as a part of Bug#3784635
589 --Purpose: This function returns the load effective date alias from
590 --enrollment calendar configuration
591 --
592 --Known limitations/enhancements and/or remarks:
593 --
594 --Change History:
595 --Who When What
596
597 --------------------------------------------------------------------*/
598
599 CURSOR c_s_enr_cal_conf
600 IS
601 SELECT secc.load_effect_dt_alias
602 FROM igs_en_cal_conf secc
603 WHERE secc.s_control_num = 1;
604
605 l_load_effect_dt_alias igs_en_cal_conf.LOAD_EFFECT_DT_ALIAS%TYPE;
606
607 BEGIN
608
609 --fetch load effective date alias from enrollment calendar configuration
610 OPEN c_s_enr_cal_conf;
611 FETCH c_s_enr_cal_conf INTO l_load_effect_dt_alias;
612 IF c_s_enr_cal_conf%NOTFOUND THEN
613 CLOSE c_s_enr_cal_conf;
614 RETURN NULL;
615 END IF;
616 CLOSE c_s_enr_cal_conf;
617 RETURN l_load_effect_dt_alias;
618
619 END get_load_eff_dt_alias;
620
621 FUNCTION get_seqno_caltyp_from_caltable (
622 p_cal_table_info IN load_cal_table_type
623 ) RETURN VARCHAR2 AS
624
625 /*------------------------------------------------------------------
626 --Created by : CKASU, Oracle IDC
627 --Date created: 21-JUL-2004
628 --this was created as a part of Bug#3784635
629 --Purpose: This function takes pl/sql table as input.this table
630 --contains Records as elements.Each record contains caltype,sequenceno,
631 --start date,end date,description of load calendars.
632 --This functions prepares a String of form sequenceno*cal_type for each
633 --record in pl/sql table and concatenates these strings and returns as
634 --output.(Example : 300*TERM SU||400*TERM SP||500*DEVRY LOAD)
635 --Known limitations/enhancements and/or remarks:
636 --
637 --Change History:
638 --Who When What
639
640 --------------------------------------------------------------------*/
641
642 caltype_seq_info VARCHAR2(2000);
643 no_of_records NUMBER;
644
645 BEGIN
646
647 --this returns the concatened string which a contains term details seperated by '||'
648 --and term details itself is represented as a combination of 'sequence_no*cal_type'
649 IF p_cal_table_info IS NOT NULL THEN
650 IF p_cal_table_info.count > 0 THEN
651 no_of_records := p_cal_table_info.count;
652
653 FOR i IN p_cal_table_info.first..p_cal_table_info.last LOOP
654 IF p_cal_table_info.exists(i) THEN
655 IF i = 1 THEN
656 caltype_seq_info := caltype_seq_info||p_cal_table_info(i).P_LOAD_CI_SEQ_NUM||'*'||p_cal_table_info(i).P_LOAD_CAL_TYPE;
657 ELSE
658 caltype_seq_info := caltype_seq_info||'||'||p_cal_table_info(i).P_LOAD_CI_SEQ_NUM||'*'||p_cal_table_info(i).P_LOAD_CAL_TYPE;
659 END IF;
660 END IF;
661 END LOOP;
662
663 END IF;
664
665 END IF;
666
667 RETURN caltype_seq_info;
668
669 END get_seqno_caltyp_from_caltable;
670
671 FUNCTION is_cur_ld_cal_has_eff_dt_alias (
672 p_acad_cal_type IN VARCHAR2,
673 p_effective_dt IN DATE,
674 p_all_cur_load_cal_info_str OUT NOCOPY VARCHAR2
675 ) RETURN BOOLEAN AS
676
677 /*------------------------------------------------------------------
678 --Created by : CKASU, Oracle IDC
679 --Date created: 21-JUL-2004
680 --this was created as a part of Bug#3784635
681 --Purpose: This function checks whether all the current term calndar
682 --has effective date alias or not and returns TRUE if load effective
683 --date alias exists even for any one of current term calendars else
684 --returns FALSE
685 --Known limitations/enhancements and/or remarks:
686 --
687 --Change History:
688 --Who When What
689
690 --------------------------------------------------------------------*/
691
692 CURSOR c_dai_v (cp_cal_type igs_ca_da_inst_v.cal_type%TYPE,
693 cp_ci_sequence_number igs_ca_da_inst_v.ci_sequence_number%TYPE,
694 cp_load_effect_dt_alias igs_en_cal_conf.load_effect_dt_alias%TYPE)
695 IS
696 SELECT daiv.alias_val
697 FROM igs_ca_da_inst_v daiv
698 WHERE daiv.cal_type = cp_cal_type
699 AND daiv.ci_sequence_number = cp_ci_sequence_number
700 AND daiv.dt_alias = cp_load_effect_dt_alias;
701
702 l_all_cur_load_cal_info_str VARCHAR2(2000);
703 l_load_effect_dt_alias igs_en_cal_conf.LOAD_EFFECT_DT_ALIAS%TYPE;
704 l_all_cur_load_cal_info load_cal_table_type;
705 isloaddtaliasfound BOOLEAN;
706 BEGIN
707
708 isloaddtaliasfound := FALSE;
709 l_load_effect_dt_alias := get_load_eff_dt_alias ;
710 get_all_cur_load_cal(p_acad_cal_type,p_effective_dt,l_all_cur_load_cal_info_str);
711 l_all_cur_load_cal_info := get_cal_tbl_frm_caltyp_seq_lst(l_all_cur_load_cal_info_str);
712 p_all_cur_load_cal_info_str := l_all_cur_load_cal_info_str ;
713 -- returns TRUE when any one of current term calendars has effective load alias date
714 -- and is les than sysdate else return FALSE
715 IF l_all_cur_load_cal_info IS NOT NULL THEN
716
717 IF l_all_cur_load_cal_info.count > 0 THEN
718 FOR i IN l_all_cur_load_cal_info.first..l_all_cur_load_cal_info.last LOOP
719 IF l_all_cur_load_cal_info.exists(i) THEN
720 FOR rec_dai_v IN c_dai_v (l_all_cur_load_cal_info(i).P_LOAD_CAL_TYPE,
721 l_all_cur_load_cal_info(i).P_LOAD_CI_SEQ_NUM,
722 l_load_effect_dt_alias)
723 LOOP
724 IF (p_effective_dt >= rec_dai_v.alias_val) THEN
725 isloaddtaliasfound := TRUE;
726 RETURN isloaddtaliasfound;
727 END IF;
728 END LOOP;
729 END IF;
730 END LOOP;
731 END IF;
732 END IF;
733 RETURN isloaddtaliasfound;
734
735 END is_cur_ld_cal_has_eff_dt_alias;
736
737 FUNCTION is_fut_cal_exists_as_cur_cal (
738 p_all_cur_load_cal_info IN load_cal_table_type,
739 p_fut_load_cal_rec IN load_cal_rec_type
740 ) RETURN BOOLEAN AS
741 /*------------------------------------------------------------------
742 --Created by : CKASU, Oracle IDC
743 --Date created: 21-JUL-2004
744 --this was created as a part of Bug#3784635
745 --Purpose:This function returns TRUE when the passed future term cal
746 --is present as one of Current terms calendars else return FALSE.
747 --
748 --Known limitations/enhancements and/or remarks:
749 --
750 --Change History:
751 --Who When What
752
753 --------------------------------------------------------------------*/
754
755 cal_already_exists BOOLEAN;
756
757 BEGIN
758
759 cal_already_exists := FALSE;
760 IF p_all_cur_load_cal_info IS NOT NULL THEN
761 IF p_all_cur_load_cal_info.count > 0 THEN
762
763 --returns TRUE when the passed term calendar exists in Current term
764 --calendars else returns FALSE
765 FOR i IN p_all_cur_load_cal_info.first..p_all_cur_load_cal_info.last LOOP
766 IF p_all_cur_load_cal_info.exists(i) THEN
767 IF ( p_all_cur_load_cal_info(i).p_load_cal_type = p_fut_load_cal_rec.p_load_cal_type AND
768 p_all_cur_load_cal_info(i).p_load_ci_seq_num = p_fut_load_cal_rec.p_load_ci_seq_num AND
769 p_all_cur_load_cal_info(i).p_load_ci_alt_code = p_fut_load_cal_rec.p_load_ci_alt_code AND
770 p_all_cur_load_cal_info(i).p_load_ci_start_dt = p_fut_load_cal_rec.p_load_ci_start_dt AND
771 p_all_cur_load_cal_info(i).p_load_ci_end_dt = p_fut_load_cal_rec.p_load_ci_end_dt AND
772 p_all_cur_load_cal_info(i).p_load_cal_desc = p_fut_load_cal_rec.p_load_cal_desc
773 ) THEN
774
775 cal_already_exists := TRUE;
776 RETURN cal_already_exists;
777
778 END IF;
779 END IF;
780 END LOOP;
781 END IF;
782 END IF;
783
784 RETURN cal_already_exists;
785
786 END is_fut_cal_exists_as_cur_cal;
787
788
789 END IGS_EN_GEN_014;
[Home] [Help]