1 PACKAGE BODY IGS_CA_VAL_QLITY AS
2 /* $Header: IGSCA14B.pls 120.4 2005/09/28 06:22:43 appldev ship $ */
3
4 ------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 --skpandey 27-SEP-2005 Bug: 4036104
8 -- Description: Modified the cursor c_chk_enr_ci definition to select only those enrolment
9 -- calendars (output from cursor c_chk_acad_ci) which have subordinates of any type
10 -- Description: Modified the cursor c_chk_acad_ci to select only those records which are of
11 -- Enrollment type and are subordinate of Academic cal instance.
12 -- c_chk_tch_ci_aus changed to use get_within_ci = 'Y' rather than N.
13 --npalanis 16-JAN-2003 Bug : 2739139
14 -- Check for all Academic Term (Load) period should have one
15 -- superior fee periods is removed
16 -- npalanis 23-DEC-2002 Bug : 2694794
17 -- new cursor added in CALP_VAL_ADM_CI to check that only one load calendar instance is attached
18 -- as a subordinate calendar under an admission calendar instance.
19 -- npalanis 16-dec-2002 Bug:2697221 . check is added for calculating load apportionment for teaching calendars which are active
20 --smadathi 11-sep-2002 Bug 2086177. Modified the procedure CALP_VAL_LOAD_CI, CALP_VAL_ADM_CI.Added
21 -- procedure calp_val_award_ci
22 --sarakshi 19-Aug-2002 Bug#2518938,modified the cursor c_chk_acad_fee_ci removed the cartesian join in the not exists clause
23 -- as functionally it was redundant, in the process resolved the bug also
24 --vchappid 11-Jun-2002 Bug#2384110, Progression Calendar is a mandatory superior calendar for the Teaching Calendar
25 -- implemented this constraint in the procedure calp_val_teach_ci
26 --schodava 17-Apr-2002 Bug #2279265
27 -- Modified procedure CALP_VAL_LOAD_CI
28 -- nsidana 7/30/2004 Bug : 3736551 : Added check to verify that load calenders do not overlap within a same academic calender.
29 ------------------------------------------------------------------
30
31 -- forward declaration of the procedure
32 PROCEDURE calp_val_award_ci( p_c_acad_cal_type IN igs_ca_inst_all.cal_type%TYPE,
33 p_n_acad_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
34 p_c_s_log_type IN VARCHAR2 ,
35 p_d_log_creation_dt IN DATE
36 );
37
38 PROCEDURE CHK_ONE_PER_CAL(p_acad_cal_type IN VARCHAR2,
39 p_acad_sequence_number IN NUMBER,
40 p_cal_cat IN VARCHAR2,
41 p_s_log_type IN VARCHAR2,
42 p_log_creation_dt IN DATE)
43 AS
44
45 -- Picks with a cal cat
46 CURSOR get_all_sda(cp_cal_cat VARCHAR2) IS
47 SELECT DISTINCT sys_date_type,date_alias
48 FROM
49 (SELECT sys_date_type,date_alias
50 FROM igs_ca_Da_configs
51 WHERE (res_cal_cat1 = cp_cal_cat OR
52 res_cal_cat2 = cp_cal_cat) AND
53 one_per_cal_flag = 'Y'
54 UNION ALL
55 SELECT a.sys_date_type,b.date_alias
56 FROM igs_ca_Da_ovd_vals b,igs_ca_Da_configs a
57 WHERE a.sys_date_type = b.sys_date_type
58 AND a.one_per_cal_flag = 'Y'
59 AND ( a.res_cal_cat1 =cp_cal_cat OR
60 a.res_cal_cat2 =cp_cal_cat)
61 );
62
63 -- Picks without a cal cat
64 CURSOR get_all_sda_with_no_cal_cat IS
65 SELECT distinct sys_date_type,date_alias
66 FROM
67 (SELECT sys_date_type,date_alias
68 FROM igs_ca_Da_configs
69 WHERE one_per_cal_flag = 'Y'
70 UNION ALL
71 SELECT a.sys_date_type,b.date_alias
72 FROM igs_ca_Da_ovd_vals b,
73 igs_ca_Da_configs a
74 WHERE a.sys_date_type = b.sys_date_type
75 AND a.one_per_cal_flag = 'Y'
76 );
77
78 CURSOR get_cal_desc(cp_cal_type VARCHAR2, cp_seq_num NUMBER)
79 IS
80 SELECT description
81 FROM igs_ca_inst
82 WHERE cal_type = cp_cal_type AND
83 sequence_number = cp_seq_num;
84
85 -- Count the instances of the DA in the CI.
86 CURSOR chk_one_per_cal(cp_dt_alias VARCHAR2, cp_cal_type VARCHAR2,cp_seq_num NUMBER)
87 IS
88 SELECT count(*)
89 FROM igs_ca_da_inst
90 WHERE dt_alias = cp_dt_alias AND
91 cal_type = cp_cal_type AND
92 ci_sequence_number = cp_seq_num;
93
94 l_cal_desc VARCHAR2(80);
95 l_count NUMBER;
96
97 BEGIN
98
99 OPEN get_cal_desc(p_acad_cal_type,p_acad_sequence_number);
100 FETCH get_cal_desc INTO l_cal_desc;
101 CLOSE get_cal_desc;
102
103 IF (p_cal_cat = 'ALL' OR p_cal_cat = 'DATES')
104 THEN
105 -- Open cursor without any restriction on the cal category and run the loop.
106 FOR get_all_sda_rec IN get_all_sda_with_no_cal_cat
107 LOOP
108 l_count := 0;
109 OPEN chk_one_per_cal(get_all_sda_rec.date_alias,p_acad_cal_type,p_acad_sequence_number);
110 FETCH chk_one_per_cal INTO l_count;
111 CLOSE chk_one_per_cal;
112 IF (l_count > 1)
113 THEN
114 -- Log an entry in the LOG table.
115 fnd_message.Set_Name('IGS','IGS_CA_GR_ONE_DAI_CI');
116 fnd_message.set_token('DA',get_all_sda_rec.date_alias);
117 fnd_message.set_token('CAL_DESC',l_cal_desc);
118 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (p_s_log_type,
119 p_log_creation_dt,
120 'DATES' || ',' || p_acad_cal_type || ',' ||TO_CHAR (p_acad_sequence_number),
121 NULL,
122 fnd_message.get);
123 END IF;
124 END LOOP;
125 ELSE
126 -- Open cursor with the restrictions on the cal category and run the loop.
127 FOR get_all_sda_rec IN get_all_sda(p_cal_cat)
128 LOOP
129 l_count := 0;
130 OPEN chk_one_per_cal(get_all_sda_rec.date_alias,p_acad_cal_type,p_acad_sequence_number);
131 FETCH chk_one_per_cal INTO l_count;
132 CLOSE chk_one_per_cal;
133 IF (l_count > 1)
134 THEN
135 -- Log an entry in the LOG table.
136 fnd_message.Set_Name('IGS','IGS_CA_GR_ONE_DAI_CI');
137 fnd_message.set_token('DA',get_all_sda_rec.date_alias);
138 fnd_message.set_token('CAL_DESC',l_cal_desc);
139 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (p_s_log_type,
140 p_log_creation_dt,
141 p_cal_cat || ',' || p_acad_cal_type || ',' ||TO_CHAR (p_acad_sequence_number),
142 NULL,
143 fnd_message.get);
144 END IF;
145 END LOOP;
146 END IF;
147
148 END chk_one_per_cal;
149
150 -- To validate research calendar instance (part of the quality check)
151 PROCEDURE CALP_VAL_RESEARCH_CI(
152 p_acad_cal_type IN VARCHAR2 ,
153 p_acad_sequence_number IN NUMBER ,
154 p_s_log_type IN VARCHAR2 ,
155 p_log_creation_dt IN DATE )
156 AS
157 ------------------------------------------------------------------
158 --Change History:
159 --Who When What
160 --
161 --sarakshi 13-Jul-2004 Bug#3729462, Added predicate DELETE_FLAG='N' to the cursor c_uop_uv .
162 --smadathi 16-sep-2002 Bug 2086177. Included getching of Planned calendars
163 -- along with the Active ones
164 ------------------------------------------------------------------
165 lv_param_values VARCHAR2(1080);
166 gv_other_detail VARCHAR2(255);
167 BEGIN -- calp_val_research_ci
168 -- Quality check calendar structures related to research teaching calendars.
169 -- The checks include:
170 -- 1. Should only be specified against the teaching periods.
171 -- 2. Should only be specified against teaching periods with a single census
172 -- date.
173 -- 3. A teaching period should only have a single start/end date.
174 -- 4. If a teaching period has a start/end date then it must have the
175 -- corresponding end/start date.
176 -- 5. All teaching periods with 'research' units should have these dates
177 -- (warning!).
178 -- 6. Effective periods should not overlap.
179 -- 7. Effective start date should be before effective end date.
180 -- 8. Effective periods should not have gaps in days between periods (or
181 -- between academic periods.
182 -- 9. Multiple teaching periods which have effective periods defined (ie.
183 -- research calendars) cannot be linked to the same load calendar within
184 -- an academic period.
185 -- EFTSU Load Determination
186 -- * Percentages specified in calendar instance relationships between
187 -- academic and load calendars should total 100%.
188 DECLARE
189
190 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
191 cst_planned CONSTANT VARCHAR2(10) := 'PLANNED';
192 cst_teaching CONSTANT VARCHAR2(10) := 'TEACHING';
193 cst_load CONSTANT VARCHAR2(10) := 'LOAD';
194 v_effective_strt_dt_alias IGS_RE_S_RES_CAL_CON.effective_strt_dt_alias%TYPE;
195 v_effective_end_dt_alias IGS_RE_S_RES_CAL_CON.effective_end_dt_alias%TYPE;
196 v_last_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
197 v_last_alias_type IGS_RE_S_RES_CAL_CON.effective_strt_dt_alias%TYPE;
198 v_last_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
199 v_last_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE;
200 v_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE;
201 v_out_of_order BOOLEAN;
202 v_strt_counter NUMBER;
203 v_end_counter NUMBER;
204 v_total_perc NUMBER;
205 v_count NUMBER;
206 v_dummy VARCHAR2(1);
207 CURSOR c_srcc IS
208 SELECT srcc.effective_strt_dt_alias,
209 srcc.effective_end_dt_alias
210 FROM IGS_RE_S_RES_CAL_CON srcc
211 WHERE s_control_num = 1;
212 CURSOR c_dai_cat (
213 cp_effective_strt_dt_alias IGS_RE_S_RES_CAL_CON.effective_strt_dt_alias%TYPE,
214 cp_effective_end_dt_alias IGS_RE_S_RES_CAL_CON.effective_end_dt_alias%TYPE) IS
215 SELECT dai.CAL_TYPE,
216 cat.S_CAL_CAT
217 FROM IGS_CA_DA_INST dai,
218 IGS_CA_TYPE cat
219 WHERE dai.DT_ALIAS IN (
220 cp_effective_strt_dt_alias,
221 cp_effective_end_dt_alias) AND
222 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
223 p_acad_cal_type,
224 p_acad_sequence_number,
225 dai.CAL_TYPE,
226 dai.ci_sequence_number, 'N') = 'Y' AND
227 cat.CAL_TYPE = dai.CAL_TYPE AND
228 cat.S_CAL_CAT <> cst_teaching;
229 CURSOR c_ci_cs_cat IS
230 SELECT ci.CAL_TYPE,
231 ci.sequence_number
232 FROM IGS_CA_INST ci,
233 IGS_CA_STAT cs,
234 IGS_CA_TYPE cat
235 WHERE IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
236 p_acad_cal_type,
237 p_acad_sequence_number,
238 ci.CAL_TYPE,
239 ci.sequence_number, 'Y') = 'Y' AND
240 cs.CAL_STATUS = ci.CAL_STATUS AND
241 cs.s_cal_status IN (cst_active,cst_planned) AND
242 cat.CAL_TYPE = ci.CAL_TYPE AND
243 cat.S_CAL_CAT = cst_teaching;
244 CURSOR c_dai (
245 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
246 cp_sequence_number IGS_CA_INST.sequence_number%TYPE,
247 cp_effective_strt_dt_alias IGS_RE_S_RES_CAL_CON.effective_strt_dt_alias%TYPE,
248 cp_effective_end_dt_alias IGS_RE_S_RES_CAL_CON.effective_end_dt_alias%TYPE) IS
249 SELECT NVL (
250 dai.absolute_val,
251 IGS_CA_GEN_001.CALP_GET_ALIAS_VAL (
252 dai.DT_ALIAS,
253 dai.sequence_number,
254 dai.CAL_TYPE,
255 dai.ci_sequence_number)) AS alias_val,
256 dai.DT_ALIAS
257 FROM IGS_CA_DA_INST dai
258 WHERE dai.CAL_TYPE = cp_cal_type AND
259 dai.ci_sequence_number = cp_sequence_number AND
260 dai.DT_ALIAS IN (
261 cp_effective_strt_dt_alias,
262 cp_effective_end_dt_alias)
263 ORDER BY alias_val ASC;
264 CURSOR c_dai_sgcc (
265 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
266 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
267 SELECT count(*)
268 FROM IGS_GE_S_GEN_CAL_CON sgcc,
269 IGS_CA_DA_INST dai
270 WHERE dai.CAL_TYPE = cp_cal_type AND
271 dai.ci_sequence_number = cp_sequence_number AND
272 dai.DT_ALIAS = sgcc.census_dt_alias AND
273 sgcc.s_control_num = 1;
274 CURSOR c_uop_uv (
275 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
276 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
277 SELECT uop.unit_cd
278 FROM IGS_PS_UNIT_OFR_PAT uop,
279 IGS_PS_UNIT_VER uv
280 WHERE uop.CAL_TYPE = cp_cal_type AND
281 uop.ci_sequence_number = cp_sequence_number AND
282 uv.unit_cd = uop.unit_cd AND
283 uv.version_number = uop.version_number AND
284 uv.research_unit_ind = 'Y' AND
285 uop.delete_flag = 'N';
286 CURSOR c_dai_ci_cs_cat (
287 cp_effective_strt_dt_alias IGS_RE_S_RES_CAL_CON.effective_strt_dt_alias%TYPE,
288 cp_effective_end_dt_alias IGS_RE_S_RES_CAL_CON.effective_end_dt_alias%TYPE) IS
289 SELECT NVL (
290 dai.absolute_val,
291 IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
292 dai.DT_ALIAS,
293 dai.sequence_number,
294 dai.CAL_TYPE,
295 dai.ci_sequence_number)) AS alias_val,
296 dai.DT_ALIAS,
297 dai.CAL_TYPE,
298 dai.ci_sequence_number
299 FROM IGS_CA_DA_INST dai,
300 IGS_CA_INST ci,
301 IGS_CA_STAT cs,
302 IGS_CA_TYPE cat
303 WHERE dai.DT_ALIAS IN (
304 cp_effective_strt_dt_alias,
305 cp_effective_end_dt_alias) AND
309 cs.s_cal_status IN (cst_active,cst_planned) AND
306 ci.CAL_TYPE = dai.CAL_TYPE AND
307 ci.sequence_number = dai.ci_sequence_number AND
308 cs.CAL_STATUS = ci.CAL_STATUS AND
310 cat.CAL_TYPE = ci.CAL_TYPE AND
311 cat.S_CAL_CAT = cst_teaching
312 ORDER BY ci.start_dt ASC,
313 ci.end_dt ASC,
314 DECODE (
315 dai.DT_ALIAS, cp_effective_strt_dt_alias, 1,
316 cp_effective_end_dt_alias, 2);
317 CURSOR c_cir_cat_ci_cs IS
318 SELECT ci.CAL_TYPE,
319 ci.sequence_number
320 FROM IGS_CA_INST_REL cir,
321 IGS_CA_TYPE cat,
322 IGS_CA_INST ci,
323 IGS_CA_STAT cs
324 WHERE cir.sup_cal_type = p_acad_cal_type AND
325 cir.sup_ci_sequence_number = p_acad_sequence_number AND
326 cat.CAL_TYPE = cir.sub_cal_type AND
327 cat.S_CAL_CAT = cst_load AND
328 ci.CAL_TYPE = cir.sub_cal_type AND
329 ci.sequence_number = cir.sub_ci_sequence_number AND
330 cs.CAL_STATUS = ci.CAL_STATUS AND
331 cs.s_cal_status IN (cst_active,cst_planned);
332 CURSOR c_dla_cir_ci_cs (
333 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
334 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
335 SELECT 'X'
336 FROM IGS_ST_DFT_LOAD_APPO dla,
337 IGS_CA_INST_REL cir,
338 IGS_CA_INST ci,
339 IGS_CA_STAT cs
340 WHERE dla.CAL_TYPE = cp_cal_type AND
341 dla.ci_sequence_number = cp_sequence_number AND
342 cir.sup_cal_type = p_acad_cal_type AND
343 cir.sup_ci_sequence_number = p_acad_sequence_number AND
344 cir.sub_cal_type = dla.teach_cal_type AND
345 ci.CAL_TYPE = cir.sub_cal_type AND
346 ci.sequence_number = cir.sub_ci_sequence_number AND
347 cs.CAL_STATUS = ci.CAL_STATUS AND
348 cs.s_cal_status IN (cst_active,cst_planned) AND
349 EXISTS (
350 SELECT 'X'
351 FROM IGS_RE_S_RES_CAL_CON srcc,
352 IGS_CA_DA_INST dai
353 WHERE srcc.s_control_num = 1 AND
354 dai.CAL_TYPE = ci.CAL_TYPE AND
355 dai.ci_sequence_number = ci.sequence_number AND
356 dai.DT_ALIAS IN (
357 srcc.effective_strt_dt_alias,
358 srcc.effective_end_dt_alias));
359 CURSOR c_cir_ci_cs_cat IS
360 SELECT SUM(NVL(cir.load_research_percentage, 0))
361 FROM IGS_CA_INST_REL cir,
362 IGS_CA_INST ci,
363 IGS_CA_STAT cs,
364 IGS_CA_TYPE cat
365 WHERE cir.sup_cal_type = p_acad_cal_type AND
366 cir.sup_ci_sequence_number = p_acad_sequence_number AND
367 ci.CAL_TYPE = cir.sub_cal_type AND
368 ci.sequence_number = cir.sub_ci_sequence_number AND
369 cs.CAL_STATUS = ci.CAL_STATUS AND
370 cs.s_cal_status IN (cst_active,cst_planned) AND
371 cat.CAL_TYPE = ci.CAL_TYPE AND
372 cat.S_CAL_CAT = cst_load;
373 BEGIN
374 -- Load the research calendar configuration
375 OPEN c_srcc;
376 FETCH c_srcc INTO
377 v_effective_strt_dt_alias,
378 v_effective_end_dt_alias;
379 IF c_srcc%NOTFOUND OR
380 v_effective_strt_dt_alias IS NULL OR
381 v_effective_end_dt_alias IS NULL THEN
382 CLOSE c_srcc;
383
384
385 --- added by syam
386 Fnd_Message.Set_Name('IGS', 'IGS_CA_NO_RES_EFF_DT');
387 --- added by syam
388
389 --- added by syam
390 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
391 p_s_log_type,
392 p_log_creation_dt,
393 'DATES' || ',' ||
394 p_acad_cal_type || ',' ||
395 TO_CHAR(p_acad_sequence_number),
396 NULL,
400
397 fnd_message.get);
398 --- added by syam
399
401
402 RETURN;
403 END IF;
404
405 CLOSE c_srcc;
406 -- Check whether research effective dates are linked to calendars of types
407 -- other than TEACHING
408 FOR v_dai_cat_rec IN c_dai_cat (
409 v_effective_strt_dt_alias,
410 v_effective_end_dt_alias) LOOP
411
412 --- added by syam
413 Fnd_Message.Set_Name('IGS', 'IGS_CA_RES_EFF_IN_CAL');
414 fnd_message.set_token('TOKEN1',v_dai_cat_rec.CAL_TYPE || ',' ||v_dai_cat_rec.S_CAL_CAT);
415
416 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
417 p_s_log_type,
418 p_log_creation_dt,
419 'DATES' || ',' ||
420 p_acad_cal_type || ',' ||
421 TO_CHAR(p_acad_sequence_number),
422 NULL,
423 fnd_message.get);
424 --- added by syam
425
426
427
428 END LOOP;
429 -- Perform date alias checks within teaching calendars in the academic year
430 FOR v_ci_cs_cat_rec IN c_ci_cs_cat LOOP
431 v_last_alias_val := NULL;
432 v_last_alias_type := NULL;
433 v_out_of_order := FALSE;
434 v_strt_counter := 0;
435 v_end_counter := 0;
436
437
438 -- Check one per cal for SDAs.
439 CHK_ONE_PER_CAL(v_ci_cs_cat_rec.cal_type, -- will check for fee cal instance.
440 v_ci_cs_cat_rec.sequence_number,
441 'DATES',
442 p_s_log_type,
443 p_log_creation_dt);
444
445 FOR v_dai_rec IN c_dai (
446 v_ci_cs_cat_rec.CAL_TYPE,
447 v_ci_cs_cat_rec.sequence_number,
448 v_effective_strt_dt_alias,
449 v_effective_end_dt_alias) LOOP
450 IF v_last_alias_val IS NOT NULL THEN
451 -- If the last date was not a start date, or the current date is not
452 -- an end date then the order is incorrect.
453 IF v_last_alias_type <> v_effective_strt_dt_alias OR
454 v_dai_rec.DT_ALIAS <> v_effective_end_dt_alias THEN
455 v_out_of_order := TRUE;
456 END IF;
457 END IF;
458 -- Increment counters for number of start/end dates
459 IF v_dai_rec.DT_ALIAS = v_effective_strt_dt_alias THEN
460 v_strt_counter := v_strt_counter + 1;
461 ELSE
462 v_end_counter := v_end_counter + 1;
463 END IF;
464 v_last_alias_val := v_dai_rec.alias_val;
465 v_last_alias_type := v_dai_rec.DT_ALIAS;
466 END LOOP;
467 IF v_strt_counter > 1 OR
468 v_end_counter > 1 OR
469 (v_strt_counter = 0 AND
470 v_end_counter = 1) OR
471 (v_end_counter = 0 AND
472 v_strt_counter = 1) THEN
473
474 --- added by syam
475 Fnd_Message.Set_Name('IGS', 'IGS_CA_EFF_START_END');
476
477 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
478 p_s_log_type,
479 p_log_creation_dt,
480 'RESEARCH' || ',' ||
481 v_ci_cs_cat_rec.CAL_TYPE || ',' ||
482 TO_CHAR(v_ci_cs_cat_rec.sequence_number),
483 NULL,
484 fnd_message.get);
485 --- added by syam
486
487
488 ELSE
489 IF v_out_of_order THEN
490
491 --- added by syam
492 Fnd_Message.Set_Name('IGS', 'IGS_CA_EFF_DATES_OUT_OF_ORDER');
493
494 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
495 p_s_log_type,
496 p_log_creation_dt,
497 'RESEARCH' || ',' ||
498 v_ci_cs_cat_rec.CAL_TYPE || ',' ||
499 TO_CHAR(v_ci_cs_cat_rec.sequence_number),
500 NULL,
501 fnd_message.get);
502
503 --- added by syam
504
505
506 END IF;
507 END IF;
508 -- If valid research dates perform further checks
509 IF v_strt_counter = 1 AND
513 v_ci_cs_cat_rec.sequence_number);
510 v_end_counter = 1 THEN
511 OPEN c_dai_sgcc (
512 v_ci_cs_cat_rec.CAL_TYPE,
514 FETCH c_dai_sgcc INTO v_count;
515 CLOSE c_dai_sgcc;
516 IF v_count > 1 THEN
517
518 --- added by syam
519 Fnd_Message.Set_Name('IGS', 'IGS_CA_TPERIOD_SINGLE_CENS');
520
521 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
522 p_s_log_type,
523 p_log_creation_dt,
524 'RESEARCH' || ',' ||
525 v_ci_cs_cat_rec.CAL_TYPE || ',' ||
526 TO_CHAR(v_ci_cs_cat_rec.sequence_number),
527 NULL,
528 fnd_message.get);
529 --- added by syam
530
531 END IF;
532 END IF;
533 -- If not a research teaching period, warn against research units being
534 -- offered within it
535 IF v_strt_counter = 0 AND
536 v_end_counter = 0 THEN
537 OPEN c_uop_uv (
538 v_ci_cs_cat_rec.CAL_TYPE,
539 v_ci_cs_cat_rec.sequence_number);
540 FETCH c_uop_uv INTO v_unit_cd;
541 IF c_uop_uv%FOUND THEN
542 CLOSE c_uop_uv;
543 --- added by syam
544 Fnd_Message.Set_Name('IGS', 'IGS_CA_NORMAL_TP_STD_EFTSU_USE');
545 fnd_message.set_token('TOKEN1',v_unit_cd);
546
547 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
548 p_s_log_type,
549 p_log_creation_dt,
550 'RESEARCH' || ',' ||
551 v_ci_cs_cat_rec.CAL_TYPE || ',' ||
552 TO_CHAR(v_ci_cs_cat_rec.sequence_number),
553 NULL,
554 fnd_message.get);
555
556 --- added by syam
557
558 ELSE
559 CLOSE c_uop_uv;
560 END IF;
561 END IF;
562 END LOOP;
563 -- Check for gaps or overlaps in effective research dates ; only report on
564 -- logical periods which overlap the context academic period
565 v_last_alias_val := NULL;
566 v_last_alias_type := NULL;
567 v_last_cal_type := NULL;
568 v_last_ci_sequence_number := NULL;
569 FOR v_dai_ci_cs_cat_rec IN c_dai_ci_cs_cat (
570 v_effective_strt_dt_alias,
571 v_effective_end_dt_alias) LOOP
572
573
574 -- Check one per cal for SDAs.
575 CHK_ONE_PER_CAL(v_dai_ci_cs_cat_rec.cal_type, -- will check for fee cal instance.
576 v_dai_ci_cs_cat_rec.ci_sequence_number,
577 'RESEARCH',
578 p_s_log_type,
579 p_log_creation_dt);
580
581 IF c_dai_ci_cs_cat%ROWCOUNT > 1 AND
582 v_dai_ci_cs_cat_rec.DT_ALIAS = v_effective_strt_dt_alias THEN
583 IF v_dai_ci_cs_cat_rec.alias_val <= v_last_alias_val THEN
584 IF IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
585 p_acad_cal_type,
586 p_acad_sequence_number,
587 v_dai_ci_cs_cat_rec.CAL_TYPE,
588 v_dai_ci_cs_cat_rec.ci_sequence_number,
589 'Y') = 'Y' OR
590 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
591 p_acad_cal_type,
592 p_acad_sequence_number,
593 v_last_cal_type,
594 v_last_ci_sequence_number,
595 'Y') = 'Y' THEN
596
597
598
599
600 --- added by syam
601 Fnd_Message.Set_Name('IGS', 'IGS_CA_RES_EFF_DATES_OVERLAP');
602 fnd_message.set_token('TOKEN1',v_dai_ci_cs_cat_rec.DT_ALIAS||':'|| IGS_GE_DATE.IGSCHAR(v_dai_ci_cs_cat_rec.alias_val)|| ', '||v_last_alias_type||':'||IGS_GE_DATE.IGSCHAR(v_last_alias_val));
603 --- added by syam
604
605
606 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
607 p_s_log_type,
608 p_log_creation_dt,
612 NULL,
609 'RESEARCH' || ',' ||
610 p_acad_cal_type || ',' ||
611 TO_CHAR(p_acad_sequence_number),
613 fnd_message.get);
614 --- added by syam
615
616
617
618
619 END IF;
620 ELSIF v_dai_ci_cs_cat_rec.alias_val > (v_last_alias_val + 1) THEN
621 IF IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
622 p_acad_cal_type,
623 p_acad_sequence_number,
624 v_dai_ci_cs_cat_rec.CAL_TYPE,
625 v_dai_ci_cs_cat_rec.ci_sequence_number,
626 'Y') = 'Y' OR
627 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
628 p_acad_cal_type,
629 p_acad_sequence_number,
630 v_last_cal_type,
631 v_last_ci_sequence_number,
632 'Y') = 'Y' THEN
633
634 --- added by syam
635 Fnd_Message.Set_Name('IGS', 'IGS_CA_GAP_BTW_EFF_PERIODS');
636 fnd_message.set_token('TOKEN1',v_dai_ci_cs_cat_rec.DT_ALIAS||':'||IGS_GE_DATE.IGSCHAR(v_dai_ci_cs_cat_rec.alias_val)||', '||v_last_alias_type||':'||IGS_GE_DATE.IGSCHAR(v_last_alias_val));
637
638 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
639 p_s_log_type,
640 p_log_creation_dt,
641 'RESEARCH' || ',' ||
642 p_acad_cal_type || ',' ||
643 TO_CHAR(p_acad_sequence_number),
644 NULL,
645 fnd_message.get);
646 --- added by syam
647
648
649
650 END IF;
651 END IF;
652 END IF;
653 v_last_alias_val := v_dai_ci_cs_cat_rec.alias_val;
654 v_last_alias_type := v_dai_ci_cs_cat_rec.DT_ALIAS;
655 v_last_cal_type := v_dai_ci_cs_cat_rec.CAL_TYPE;
656 v_last_ci_sequence_number := v_dai_ci_cs_cat_rec.ci_sequence_number;
657 END LOOP;
658 -- Check that the research teaching periods are linked to only a single load
659 -- calendar within the academic calendar
660 FOR v_cir_cat_ci_cs_rec IN c_cir_cat_ci_cs LOOP
661
662 -- Check one per cal for SDAs.
663 CHK_ONE_PER_CAL(v_cir_cat_ci_cs_rec.cal_type, -- will check for fee cal instance.
664 v_cir_cat_ci_cs_rec.sequence_number,
665 'RESEARCH',
666 p_s_log_type,
667 p_log_creation_dt);
668
669 FOR v_dla_cir_ci_cs_rec IN c_dla_cir_ci_cs (
670 v_cir_cat_ci_cs_rec.CAL_TYPE,
671 v_cir_cat_ci_cs_rec.sequence_number) LOOP
672 IF c_dla_cir_ci_cs%ROWCOUNT > 1 THEN
673
674 --- added by syam
675 Fnd_Message.Set_Name('IGS','IGS_CA_MULT_TP_SINGLE_CAL');
676 fnd_message.set_token('TOKEN1',v_cir_cat_ci_cs_rec.CAL_TYPE);
677
678 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
679 p_s_log_type,
680 p_log_creation_dt,
681 'RESEARCH' || ',' ||
682 p_acad_cal_type || ',' ||
683 TO_CHAR(p_acad_sequence_number),
684 NULL,
685 fnd_message.get);
686 ---added by syam
687
688
689
690 EXIT;
691 END IF;
692 END LOOP;
693 END LOOP;
694 -- Check that the research percentage adds up to 100% for the academic period
695 OPEN c_cir_ci_cs_cat;
696 FETCH c_cir_ci_cs_cat INTO v_total_perc;
697 CLOSE c_cir_ci_cs_cat;
698 IF v_total_perc <> 100 THEN
699
700 --- added by syam
701 Fnd_Message.Set_Name('IGS','IGS_CA_RES_PERCENT_NOT_100');
702
703 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY (
704 p_s_log_type,
705 p_log_creation_dt,
706 'RESEARCH' || ',' ||
707 p_acad_cal_type || ',' ||
711 --- added by syam
708 TO_CHAR(p_acad_sequence_number),
709 NULL,
710 fnd_message.get);
712
713 END IF;
714 EXCEPTION
715 WHEN OTHERS THEN
716 IF c_srcc%ISOPEN THEN
717 CLOSE c_srcc;
718 END IF;
719 IF c_dai_cat%ISOPEN THEN
720 CLOSE c_dai_cat;
721 END IF;
722 IF c_ci_cs_cat%ISOPEN THEN
723 CLOSE c_ci_cs_cat;
724 END IF;
725 IF c_dai%ISOPEN THEN
726 CLOSE c_dai;
727 END IF;
728 IF c_dai_sgcc%ISOPEN THEN
729 CLOSE c_dai_sgcc;
730 END IF;
731 IF c_uop_uv%ISOPEN THEN
732 CLOSE c_uop_uv;
733 END IF;
734 IF c_dai_ci_cs_cat%ISOPEN THEN
735 CLOSE c_dai_ci_cs_cat;
736 END IF;
737 IF c_cir_cat_ci_cs%ISOPEN THEN
738 CLOSE c_cir_cat_ci_cs;
739 END IF;
740 IF c_dla_cir_ci_cs%ISOPEN THEN
741 CLOSE c_dla_cir_ci_cs;
742 END IF;
743 IF c_cir_ci_cs_cat%ISOPEN THEN
744 CLOSE c_cir_ci_cs_cat;
745 END IF;
746 App_Exception.Raise_Exception;
747 END;
748 EXCEPTION
749 WHEN OTHERS THEN
750 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
751 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_QLITY.calp_val_research_ci');
752 IGS_GE_MSG_STACK.ADD;
753 lv_param_values:=p_acad_cal_type||','||(to_char(p_acad_sequence_number));
754 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
755 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
756 IGS_GE_MSG_STACK.ADD;
757 lv_param_values:=p_s_log_type ||','||(to_char(p_log_creation_dt));
758 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
759 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
760 IGS_GE_MSG_STACK.ADD;
761 App_Exception.Raise_Exception;
762 END calp_val_research_ci;
763 --
764 -- To quality check admission calendar instances
765 PROCEDURE CALP_VAL_ADM_CI(
766 p_acad_cal_type IN VARCHAR2 ,
767 p_acad_sequence_number IN NUMBER ,
768 p_s_log_type IN VARCHAR2 ,
769 p_log_creation_dt IN DATE )
770 AS
771 ------------------------------------------------------------------
772 --
773 --Change History:
774 --Who When What
775 --smadathi 11-Sep-2002 Bug 2086177. Added new cursor c_cir_5 and
776 -- associated logic to Check that all admission periods have
777 -- at least one direct subordinate Load calendar instance.
778 --skpandey 25-AUG-2005 BUG 4036104
779 -- Added a condition in where clause of cursor c_cict to select only those records which are of Admission type and are subordinate of Academic cal instance.
780 -------------------------------------------------------------------
781 lv_param_values VARCHAR2(1080);
782 gv_other_detail VARCHAR2(255);
783 BEGIN -- calp_val_adm_ci
784 -- Validate that the admission calendar instance has links to the
785 -- appropriate calendars.
786 -- This routine will the called as part of the calendar quality check.
787 DECLARE
788
789 cst_admission CONSTANT VARCHAR2(9) := 'ADMISSION';
790 cst_admission2 CONSTANT VARCHAR2(10) := 'ADMISSION,';
791 cst_academic CONSTANT VARCHAR2(8) := 'ACADEMIC';
792 cst_teaching CONSTANT VARCHAR2(8) := 'TEACHING';
793 cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
794 cst_planned CONSTANT VARCHAR2(7) := 'PLANNED';
795 cst_enrolment CONSTANT VARCHAR2(9) := 'ENROLMENT';
796 cst_load CONSTANT VARCHAR2(10) := 'LOAD';
797 v_aal_rec_found BOOLEAN := FALSE;
798 v_cir_1_rec_found BOOLEAN := FALSE;
799 v_cir_4_rec_found BOOLEAN := FALSE;
800 v_daiv_1_rec_found BOOLEAN := FALSE;
801 v_daiv_2_rec_found BOOLEAN := FALSE;
802 v_daiv_3_rec_found BOOLEAN := FALSE;
803 v_cir2_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE;
804 v_cir2_sub_ci_sequence_number IGS_CA_INST_REL.sub_ci_sequence_number%TYPE;
805 v_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
806 v_sequence_number IGS_CA_INST.sequence_number%TYPE;
807 v_dummy VARCHAR2(1);
808 v_count NUMBER;
809 CURSOR c_cict IS
810 SELECT ci.CAL_TYPE,
811 ci.sequence_number
812 FROM IGS_CA_INST ci,
813 IGS_CA_STAT cs,
814 IGS_CA_TYPE cat
818 ci.CAL_TYPE,
815 WHERE IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
816 p_acad_cal_type,
817 p_acad_sequence_number,
819 ci.sequence_number, 'N') = 'Y' AND
820 cat.S_CAL_CAT = cst_admission AND
821 cs.s_cal_status IN (cst_active, cst_planned) AND
822 ci.CAL_TYPE = cat.CAL_TYPE AND
823 ci.CAL_STATUS = cs.CAL_STATUS;
824 CURSOR c_cir_1 (
825 cp_sub_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
826 cp_sub_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
827 SELECT cir.sup_cal_type,
828 cir.sup_ci_sequence_number
829 FROM IGS_CA_INST_REL cir,
830 IGS_CA_TYPE cat
831 WHERE cir.sub_cal_type = cp_sub_cal_type AND
832 cir.sub_ci_sequence_number = cp_sub_sequence_number AND
833 cat.S_CAL_CAT = cst_academic AND
834 cir.sup_cal_type = cat.CAL_TYPE;
835 CURSOR c_cat_cir2 (
836 cp_sub_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
837 cp_sub_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
838 SELECT cir2.sub_cal_type,
839 cir2.sub_ci_sequence_number
840 FROM IGS_CA_INST_REL cir2,
841 IGS_CA_TYPE cat
842 WHERE cir2.sup_cal_type = cp_sub_cal_type AND
843 cir2.sup_ci_sequence_number = cp_sub_sequence_number AND
844 cat.S_CAL_CAT = cst_enrolment AND
845 cir2.sub_cal_type = cat.CAL_TYPE;
846 CURSOR c_cir_2 (
847 cp_sub_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
848 cp_sub_sequence_number IGS_CA_INST.sequence_number%TYPE,
849 cp_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
850 cp_acad_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
851 SELECT 'x'
852 FROM IGS_CA_INST_REL cir
853 WHERE cir.sub_cal_type = cp_sub_cal_type AND
854 cir.sub_ci_sequence_number = cp_sub_sequence_number AND
855 cir.sup_cal_type = cp_acad_cal_type AND
856 cir.sup_ci_sequence_number = cp_acad_sequence_number;
857 CURSOR c_cir_3 (
858 cp_sub_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
859 cp_sub_sequence_number IGS_CA_INST.sequence_number%TYPE,
860 cp_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
861 cp_acad_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
862 SELECT 'x'
863 FROM IGS_CA_INST_REL cir
864 WHERE cir.sub_cal_type = cp_sub_cal_type AND
865 cir.sub_ci_sequence_number = cp_sub_sequence_number AND
866 cir.sup_cal_type = cp_acad_cal_type AND
867 cir.sup_ci_sequence_number <> cp_acad_sequence_number;
868 CURSOR c_cir_4 (
869 cp_sub_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
870 cp_sub_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
871 SELECT cir.sup_cal_type,
872 cir.sup_ci_sequence_number
873 FROM IGS_CA_INST_REL cir,
874 IGS_CA_TYPE cat
875 WHERE cir.sub_cal_type = cp_sub_cal_type AND
876 cir.sub_ci_sequence_number = cp_sub_sequence_number AND
877 cat.S_CAL_CAT = cst_teaching AND
878 cir.sup_cal_type = cat.CAL_TYPE;
879 CURSOR c_aal (
880 cp_adm_cal_type IGS_AD_PERD_AD_CAT.adm_cal_type%TYPE,
881 cp_adm_ci_sequence_number
882 IGS_AD_PERD_AD_CAT.adm_ci_sequence_number%TYPE) IS
883 SELECT 'x'
884 FROM IGS_AD_PERD_AD_CAT apac
885 WHERE apac.adm_cal_type = cp_adm_cal_type AND
886 apac.adm_ci_sequence_number = cp_adm_ci_sequence_number;
887 CURSOR c_daiv_1 (
888 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
889 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
890 SELECT 'x'
891 FROM IGS_CA_DA_INST_V daiv,
892 IGS_AD_CAL_CONF sacc
893 WHERE daiv.CAL_TYPE = cp_cal_type AND
894 daiv.ci_sequence_number = cp_ci_sequence_number AND
895 daiv.DT_ALIAS = sacc.initialise_adm_perd_dt_alias AND
896 sacc.s_control_num = 1;
897 CURSOR c_daiv_2 (
898 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
899 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
903 WHERE daiv.CAL_TYPE = cp_cal_type AND
900 SELECT 'x'
901 FROM IGS_CA_DA_INST_V daiv,
902 IGS_AD_CAL_CONF sacc
904 daiv.ci_sequence_number = cp_ci_sequence_number AND
905 daiv.DT_ALIAS = sacc.adm_appl_encmb_chk_dt_alias AND
906 sacc.s_control_num = 1;
907 CURSOR c_daiv_3 (
908 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
909 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
910 SELECT 'x'
911 FROM IGS_CA_DA_INST_V daiv,
912 IGS_AD_CAL_CONF sacc
913 WHERE daiv.CAL_TYPE = cp_cal_type AND
914 daiv.ci_sequence_number = cp_ci_sequence_number AND
915 daiv.DT_ALIAS = sacc.adm_appl_course_strt_dt_alias AND
916 sacc.s_control_num = 1;
917 CURSOR c_daiv_4 (
918 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
919 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
920 SELECT COUNT(*)
921 FROM IGS_CA_DA_INST_V daiv,
922 IGS_AD_CAL_CONF sacc
923 WHERE daiv.CAL_TYPE = cp_cal_type AND
924 daiv.ci_sequence_number = cp_ci_sequence_number AND
925 daiv.DT_ALIAS = sacc.adm_appl_due_dt_alias AND
926 sacc.s_control_num = 1 AND
927 NOT EXISTS (
928 SELECT 'x'
929 FROM IGS_AD_PECRS_OFOP_DT apcood
930 WHERE apcood.adm_cal_type = cp_cal_type AND
931 apcood.adm_ci_sequence_number = cp_ci_sequence_number AND
932 apcood.DT_ALIAS = daiv.CAL_TYPE AND
933 apcood.dai_sequence_number = daiv.sequence_number);
934 CURSOR c_daiv_5 (
935 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
936 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
937 SELECT COUNT(*)
938 FROM IGS_CA_DA_INST_V daiv,
939 IGS_AD_CAL_CONF sacc
940 WHERE daiv.CAL_TYPE = cp_cal_type AND
941 daiv.ci_sequence_number = cp_ci_sequence_number AND
942 daiv.DT_ALIAS = sacc.adm_appl_final_dt_alias AND
943 sacc.s_control_num = 1 AND
944 NOT EXISTS (
945 SELECT 'x'
946 FROM IGS_AD_PECRS_OFOP_DT apcood
947 WHERE apcood.adm_cal_type = cp_cal_type AND
948 apcood.adm_ci_sequence_number = cp_ci_sequence_number AND
949 apcood.DT_ALIAS = daiv.CAL_TYPE AND
950 apcood.dai_sequence_number = daiv.sequence_number);
951 CURSOR c_daiv_6 (
952 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
953 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
954 SELECT COUNT(*)
955 FROM IGS_CA_DA_INST_V daiv,
956 IGS_AD_CAL_CONF sacc
957 WHERE daiv.CAL_TYPE = cp_cal_type AND
958 daiv.ci_sequence_number = cp_ci_sequence_number AND
959 daiv.DT_ALIAS = sacc.adm_appl_offer_resp_dt_alias AND
960 sacc.s_control_num = 1 AND
961 NOT EXISTS (
962 SELECT 'x'
963 FROM IGS_AD_PECRS_OFOP_DT apcood
964 WHERE apcood.adm_cal_type = cp_cal_type AND
965 apcood.adm_ci_sequence_number = cp_ci_sequence_number AND
966 apcood.DT_ALIAS = daiv.CAL_TYPE AND
967 apcood.dai_sequence_number = daiv.sequence_number);
968
969 -- Cursor to Check that all admission periods have
970 -- at least one direct subordinate Load calendar instance
971
972 CURSOR c_cir_5 ( cp_c_sup_cal_type igs_ca_inst.cal_type%TYPE,
973 cp_n_sup_sequence_number igs_ca_inst.sequence_number%TYPE
974 ) IS
975 SELECT cir5.sub_cal_type,
976 cir5.sub_ci_sequence_number
977 FROM igs_ca_inst_rel cir5,
978 igs_ca_type cat
979 WHERE cir5.sup_cal_type = cp_c_sup_cal_type
980 AND cir5.sup_ci_sequence_number = cp_n_sup_sequence_number
981 AND cat.s_cal_cat = cst_load
982 AND cir5.sub_cal_type = cat.cal_type;
983
984 -- Bug : 2694794
985 -- cursor to check that only one load calendar instance is attached
989 p_sequence_number igs_ca_inst_all.sequence_number%TYPE ) IS
986 -- as a subordinate calendar under an admission calendar instance
987
988 CURSOR ad_rel_load(p_cal_type igs_ca_inst_all.cal_type%TYPE,
990 SELECT count(car.sub_ci_sequence_number)
991 FROM igs_ca_inst_rel car,
992 igs_ca_inst_all ca,
993 igs_ca_type cat
994 WHERE
995 car.SUP_CI_SEQUENCE_NUMBER= p_sequence_number AND
996 car.SUP_CAL_type = p_cal_type AND
997 ca.cal_type = car.sub_cal_type AND
998 ca.sequence_number = car.sub_ci_sequence_number AND
999 ca.cal_type = cat.cal_type AND
1000 cat.s_cal_cat = 'LOAD' ;
1001
1002 rec_c_cir_5 c_cir_5%ROWTYPE;
1003 BEGIN
1004 -- Select admission periods
1005 FOR v_cict_rec IN c_cict LOOP
1006 -- Check that all admission period have at least one direct superior
1007 -- academic period.
1008 v_cal_type := v_cict_rec.CAL_TYPE;
1009 v_sequence_number := v_cict_rec.sequence_number;
1010 -- Check one per cal for SDAs.
1011 CHK_ONE_PER_CAL(v_cal_type, -- will check for acad cal instance.
1012 v_sequence_number,
1013 cst_admission2,
1014 p_s_log_type,
1015 p_log_creation_dt);
1016
1017 FOR v_cir_1_rec IN c_cir_1(
1018 v_cal_type,
1019 v_sequence_number) LOOP
1020 v_cir_1_rec_found := TRUE;
1021 -- Only check admission period for the academic period parameter
1022 OPEN c_cir_2(
1023 v_cal_type,
1024 v_sequence_number,
1025 p_acad_cal_type,
1026 p_acad_sequence_number);
1027 FETCH c_cir_2 INTO v_dummy;
1028 IF(c_cir_2%FOUND) THEN
1029 CLOSE c_cir_2;
1030 -- Identify if admission period is linked to another academic period
1031 -- of the same academic calendar type
1032 FOR v_cir_3_rec IN c_cir_3(
1033 v_cal_type,
1034 v_sequence_number,
1035 p_acad_cal_type,
1036 p_acad_sequence_number) LOOP
1037
1038
1039 --- added by syam
1040 Fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_ONLY_1_ACADCAL');
1041
1042 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1043 p_s_log_type,
1044 p_log_creation_dt,
1045 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1046 NULL,
1047 fnd_message.get);
1048
1049
1050 END LOOP;
1051 --Check that all admission periods have at least one direct
1052 --subordinate enrolment period
1053 OPEN c_cat_cir2(
1054 v_cal_type,
1055 v_sequence_number);
1056 FETCH c_cat_cir2 INTO v_cir2_sub_cal_type,
1057 v_cir2_sub_ci_sequence_number;
1058 IF c_cat_cir2%NOTFOUND THEN
1059 CLOSE c_cat_cir2;
1060
1061 --- added by syam
1062 Fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_HAVE_1_ENROLCAL');
1063
1064 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1065 p_s_log_type,
1066 p_log_creation_dt,
1067 cst_admission2|| v_cal_type ||',' || TO_CHAR(v_sequence_number),
1068 NULL,
1069 fnd_message.get);
1070 --- added by syam
1071
1072
1073 ELSE
1074 CLOSE c_cat_cir2;
1075 END IF;
1076
1077 --Check that all admission periods have at least one
1078 --subordinate Academic Term (Load) period
1079 OPEN c_cir_5 ( cp_c_sup_cal_type => v_cal_type,
1080 cp_n_sup_sequence_number => v_sequence_number
1081 );
1082 FETCH c_cir_5 INTO rec_c_cir_5;
1083 IF c_cir_5%NOTFOUND THEN
1084 FND_MESSAGE.SET_NAME('IGS','IGS_CA_ADMCAL_HAVE_1_LOAD');
1085 igs_ge_gen_003.genp_ins_log_entry( p_s_log_type,
1086 p_log_creation_dt,
1090 );
1087 cst_admission2|| v_cal_type ||',' || TO_CHAR(v_sequence_number),
1088 NULL,
1089 fnd_message.get
1091 END IF;
1092 CLOSE c_cir_5;
1093
1094 -- Bug : 2694794
1095 -- check that only one load calendar instance is attached under an admission calendar
1096 -- as subordinate calendar
1097 OPEN ad_rel_load(v_cal_type,v_sequence_number);
1098 FETCH ad_rel_load INTO v_count;
1099 IF v_count > 1 THEN
1100 FND_MESSAGE.SET_NAME('IGS','IGS_CA_ADM_CAL_SUB_LOAD');
1101 igs_ge_gen_003.genp_ins_log_entry( p_s_log_type,
1102 p_log_creation_dt,
1103 cst_admission2|| v_cal_type ||',' || TO_CHAR(v_sequence_number),
1104 NULL,
1105 fnd_message.get
1106 );
1107 END IF;
1108 CLOSE ad_rel_load;
1109
1110
1111 -- Check that the admission period in the academic period has at least one
1112 -- direct superior teaching period
1113 FOR v_cir_4_rec IN c_cir_4(
1114 v_cal_type,
1115 v_sequence_number) LOOP
1116 v_cir_4_rec_found := TRUE;
1117 END LOOP;
1118 IF(v_cir_4_rec_found = FALSE) THEN
1119
1120 --- added by syam
1121 Fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_HAVE_1_TEACHCAL');
1122 --- added by syam
1123
1124 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1125 p_s_log_type,
1126 p_log_creation_dt,
1127 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1128 NULL,
1129 fnd_message.get);
1130 --- added by syam
1131
1132 ELSE
1133 v_cir_4_rec_found := FALSE;
1134 END IF;
1135 -- Check that the admission period is defined by an admission category
1136 FOR v_aal_rec IN c_aal(
1137 v_cal_type,
1138 v_sequence_number) LOOP
1139 v_aal_rec_found := TRUE;
1140 END LOOP;
1141 IF(v_aal_rec_found = FALSE) THEN
1142
1143 --- added by syam
1144 Fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_DEFINE_1_ADMCAT');
1145
1146 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1147 p_s_log_type,
1148 p_log_creation_dt,
1149 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1150 NULL,
1151 fnd_message.get);
1152 --- added by syam
1153
1154
1155 ELSE
1156 v_aal_rec_found := FALSE;
1157 END IF;
1158 -- Validate that important dates exist in admission period
1159 FOR v_daiv_1_rec IN c_daiv_1(
1160 v_cal_type,
1161 v_sequence_number) LOOP
1162 v_daiv_1_rec_found := TRUE;
1163 END LOOP;
1164 IF(v_daiv_1_rec_found = FALSE) THEN
1165
1166 --- added by syam
1167 fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_NO_INIT_DATE');
1168
1169 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1170 p_s_log_type,
1171 p_log_creation_dt,
1172 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1173 NULL,
1174 fnd_message.get);
1175 --- added by syam
1176
1177 ELSE
1178 v_daiv_1_rec_found := FALSE;
1179 END IF;
1180 FOR v_daiv_2_rec IN c_daiv_2(
1184 END LOOP;
1181 v_cal_type,
1182 v_sequence_number) LOOP
1183 v_daiv_2_rec_found := TRUE;
1185 IF(v_daiv_2_rec_found = FALSE) THEN
1186
1187
1188 --- added by syam
1189 fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_NO_ENCUMB_DATE');
1190
1191 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1192 p_s_log_type,
1193 p_log_creation_dt,
1194 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1195 NULL,
1196 fnd_message.get);
1197
1198
1199 ELSE
1200 v_daiv_2_rec_found := FALSE;
1201 END IF;
1202 FOR v_daiv_3_rec IN c_daiv_3(
1203 v_cal_type,
1204 v_sequence_number) LOOP
1205 v_daiv_3_rec_found := TRUE;
1206 END LOOP;
1207 IF(v_daiv_3_rec_found = FALSE) THEN
1208
1209
1210 --- added by syam
1211 fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_NO_PSCOURSE_DATE');
1212 --- added by syam
1213
1214 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1215 p_s_log_type,
1216 p_log_creation_dt,
1217 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1218 NULL,
1219 fnd_message.get);
1220
1221 ELSE
1222 v_daiv_3_rec_found := FALSE;
1223 END IF;
1224 -- Check that for date aliases that can be attached to admission period
1225 -- IGS_PS_COURSE
1226 -- offering option override, only one date value in the admission must
1227 -- not be linked to IGS_AD_PECRS_OFOP_DT
1228 OPEN c_daiv_4(
1229 v_cal_type,
1230 v_sequence_number);
1231 FETCH c_daiv_4 INTO v_count;
1232 CLOSE c_daiv_4;
1233 IF(v_count > 1) THEN
1234
1235
1236 --- added by syam
1237 fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_THAN_1_DUEDATE');
1238
1239 --- added by syam
1240
1241 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1242 p_s_log_type,
1243 p_log_creation_dt,
1244 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1245 NULL,
1246 fnd_message.get);
1247
1248
1249 END IF;
1250 OPEN c_daiv_5(
1251 v_cal_type,
1252 v_sequence_number);
1253 FETCH c_daiv_5 INTO v_count;
1254 CLOSE c_daiv_5;
1255 IF(v_count > 1) THEN
1256
1257
1258 --- added by syam
1259 fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_THAN_1_APPLDATE');
1260 --- added by syam
1261
1262 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1263 p_s_log_type,
1264 p_log_creation_dt,
1265 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1266 NULL,
1267 fnd_Message.get);
1268
1269 END IF;
1270 OPEN c_daiv_6(
1271 v_cal_type,
1272 v_sequence_number);
1273 FETCH c_daiv_6 INTO v_count;
1274 CLOSE c_daiv_6;
1275 IF(v_count > 1) THEN
1276
1277 --- added by syam
1278 fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_THAN_1_OFRDATE');
1279
1280 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1281 p_s_log_type,
1285 fnd_message.get);
1282 p_log_creation_dt,
1283 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1284 NULL,
1286 --- added by syam
1287
1288 END IF;
1289 ELSE
1290 CLOSE c_cir_2;
1291 END IF; -- (c_cir_2%FOUND)
1292 END LOOP; -- c_cir_1
1293 IF(v_cir_1_rec_found = FALSE) THEN
1294
1295 --- added by syam
1296 fnd_Message.Set_Name('IGS','IGS_CA_ADMCAL_HAVE_1_ACADCAL');
1297
1298 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1299 p_s_log_type,
1300 p_log_creation_dt,
1301 cst_admission2 || v_cal_type || ',' || TO_CHAR(v_sequence_number),
1302 NULL,
1303 fnd_message.get);
1304
1305 --- added by syam
1306
1307 ELSE
1308 v_cir_1_rec_found := FALSE;
1309 END IF;
1310 END LOOP; -- c_cict
1311 RETURN;
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 IF c_cict%ISOPEN THEN
1315 CLOSE c_cict;
1316 END IF;
1317 IF c_cir_1%ISOPEN THEN
1318 CLOSE c_cir_1;
1319 END IF;
1320 IF c_cat_cir2%ISOPEN THEN
1321 CLOSE c_cat_cir2;
1322 END IF;
1323 IF c_cir_2%ISOPEN THEN
1324 CLOSE c_cir_2;
1325 END IF;
1326 IF c_cir_3%ISOPEN THEN
1327 CLOSE c_cir_3;
1328 END IF;
1329 IF c_cir_4%ISOPEN THEN
1330 CLOSE c_cir_4;
1331 END IF;
1332 IF c_aal%ISOPEN THEN
1333 CLOSE c_aal;
1334 END IF;
1335 IF c_daiv_1%ISOPEN THEN
1336 CLOSE c_daiv_1;
1337 END IF;
1338 IF c_daiv_2%ISOPEN THEN
1339 CLOSE c_daiv_2;
1340 END IF;
1341 IF c_daiv_3%ISOPEN THEN
1342 CLOSE c_daiv_3;
1343 END IF;
1344 IF c_daiv_4%ISOPEN THEN
1345 CLOSE c_daiv_4;
1346 END IF;
1347 IF c_daiv_5%ISOPEN THEN
1348 CLOSE c_daiv_5;
1349 END IF;
1350 IF c_daiv_6%ISOPEN THEN
1351 CLOSE c_daiv_6;
1352 END IF;
1353 IF c_cir_5%ISOPEN THEN
1354 CLOSE c_cir_5;
1355 END IF;
1356 App_Exception.Raise_Exception;
1357 END;
1358 EXCEPTION
1359 WHEN OTHERS THEN
1360 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1361 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_QLITY.calp_val_adm_ci');
1362 IGS_GE_MSG_STACK.ADD;
1363 lv_param_values:=p_acad_cal_type||','||(to_char(p_acad_sequence_number));
1364 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1365 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
1366 IGS_GE_MSG_STACK.ADD;
1367 lv_param_values:=p_s_log_type||','||(to_char(p_log_creation_dt));
1368 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1369 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
1370 IGS_GE_MSG_STACK.ADD;
1371 App_Exception.Raise_Exception;
1372 END calp_val_adm_ci;
1373
1374 --
1375 -- To quality check calendar data structures
1376 -- Bug # 2279265
1377 -- Update to Calendar Quality Check report
1378 -- This function is called from the report IGSCAS01
1379 FUNCTION CALP_VAL_QUAL_CHK(
1380 p_acad_cal_type IN VARCHAR2 ,
1381 p_acad_sequence_number IN NUMBER ,
1382 p_calendar_category IN VARCHAR2 )
1383 RETURN DATE AS
1384 lv_param_values VARCHAR2(1080);
1385 gv_other_detail VARCHAR2(255);
1386 BEGIN
1387 DECLARE
1388
1389 cst_all CONSTANT VARCHAR2(3) := 'ALL';
1390 cst_load CONSTANT VARCHAR2(4) := 'LOAD';
1391 cst_dates CONSTANT VARCHAR2(5) := 'DATES';
1392 cst_teaching CONSTANT VARCHAR2(8) := 'TEACHING';
1393 cst_enrolment CONSTANT VARCHAR2(9) := 'ENROLMENT';
1394 cst_admission CONSTANT VARCHAR2(9) := 'ADMISSION';
1395 cst_research CONSTANT VARCHAR2(8) := 'RESEARCH';
1396 cst_calquality CONSTANT VARCHAR2(10) := 'CALQUALITY';
1397 v_creation_dt DATE;
1398 BEGIN -- calp_val_qual_chk
1399 -- Calendar quality check module
1400 -- This routine is triggered from a Report and calls the required dbase
1401 -- routines to perform a quality check of the calendar structures
1402 IGS_GE_GEN_003.GENP_INS_LOG(cst_calquality,
1406 -- Call the procedure to check if the DA used in SDAs have more than 1 instance in the Calendar for which the report is run.
1403 p_acad_cal_type || ',' || TO_CHAR(p_acad_sequence_number),
1404 v_creation_dt);
1405 IGS_CA_VAL_QLITY.g_cal_cat := p_calendar_category;
1407 CHK_ONE_PER_CAL(p_acad_cal_type,p_acad_sequence_number,'ALL',cst_calquality,v_creation_dt); -- will check for ACAD cal instance.
1408 -- Process TEACHING calendars
1409 IF p_calendar_category = cst_all OR
1410 p_calendar_category = cst_teaching THEN
1411 IGS_CA_VAL_QLITY.calp_val_teach_ci(
1412 p_acad_cal_type,
1413 p_acad_sequence_number,
1414 cst_calquality,
1415 v_creation_dt);
1416 END IF;
1417 -- Process LOAD calendars
1418 IF p_calendar_category = cst_all OR
1419 p_calendar_category = cst_load THEN
1420 IGS_CA_VAL_QLITY.calp_val_load_ci(
1421 p_acad_cal_type,
1422 p_acad_sequence_number,
1423 cst_calquality,
1424 v_creation_dt);
1425 END IF;
1426 -- Process ENROLMENT calendars
1427 IF p_calendar_category = cst_all OR
1428 p_calendar_category = cst_enrolment THEN
1429 IGS_CA_VAL_QLITY.calp_val_enrol_ci(
1430 p_acad_cal_type,
1431 p_acad_sequence_number,
1432 cst_calquality,
1433 v_creation_dt);
1434 END IF;
1435 -- Process ADMISSION calendars
1436 IF p_calendar_category = cst_all OR
1437 p_calendar_category = cst_admission THEN
1438 IGS_CA_VAL_QLITY.calp_val_adm_ci(
1439 p_acad_cal_type,
1440 p_acad_sequence_number,
1441 cst_calquality,
1442 v_creation_dt);
1443 END IF;
1444 -- Process DATES calendars
1445 IF p_calendar_category = cst_all OR
1446 p_calendar_category = cst_dates THEN
1447 IGS_CA_VAL_QLITY.calp_val_dates_ci(
1448 p_acad_cal_type,
1449 p_acad_sequence_number,
1450 cst_calquality,
1451 v_creation_dt);
1452 END IF;
1453
1454 IF p_calendar_category = cst_all OR
1455 p_calendar_category = cst_research THEN
1456 IGS_CA_VAL_QLITY.calp_val_research_ci(
1457 p_acad_cal_type,
1458 p_acad_sequence_number,
1459 cst_calquality,
1460 v_creation_dt);
1461 END IF;
1462
1463 -- Process Award calendars
1464 -- The Award calendars selected should be ones that overlap with
1465 -- the Academic Calendar selected in the CM parameters.
1466
1467 IF p_calendar_category = cst_all THEN
1468 calp_val_award_ci( p_c_acad_cal_type => p_acad_cal_type,
1469 p_n_acad_sequence_number => p_acad_sequence_number,
1470 p_c_s_log_type => cst_calquality ,
1471 p_d_log_creation_dt => v_creation_dt
1472 );
1473
1474
1475 END IF;
1476
1477
1478 COMMIT;
1479 RETURN v_creation_dt;
1480 END;
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1484 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_QLITY.calp_val_qual_chk');
1485 IGS_GE_MSG_STACK.ADD;
1486 lv_param_values:= p_acad_cal_type||','||(to_char(p_acad_sequence_number)) ;
1487 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1488 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
1489 IGS_GE_MSG_STACK.ADD;
1490 lv_param_values:= p_calendar_category ;
1491 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1492 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
1493 IGS_GE_MSG_STACK.ADD;
1494 App_Exception.Raise_Exception;
1495 END calp_val_qual_chk;
1496 --
1497 -- To quality check system control dates within calendar instances.
1498 PROCEDURE CALP_VAL_DATES_CI(
1499 p_acad_cal_type IN VARCHAR2 ,
1500 p_acad_sequence_number IN NUMBER ,
1501 p_s_log_type IN VARCHAR2 ,
1502 p_log_creation_dt IN DATE )
1503 AS
1504 lv_param_values VARCHAR2(1080);
1505 cst_load constant IGS_CA_TYPE.S_CAL_CAT%TYPE := 'LOAD';
1506 cst_academic constant IGS_CA_TYPE.S_CAL_CAT%TYPE := 'ACADEMIC';
1507 cst_teaching constant IGS_CA_TYPE.S_CAL_CAT%TYPE := 'TEACHING';
1508 cst_enrolment constant IGS_CA_TYPE.S_CAL_CAT%TYPE := 'ENROLMENT';
1509 gv_other_detail VARCHAR2(255);
1510 BEGIN
1511 DECLARE
1512 CURSOR c_chk_dt_aliases IS
1513 SELECT load_effect_dt_alias,
1514 commencement_dt_alias,
1518 effect_enr_strt_dt_alias
1515 commence_cutoff_dt_alias,
1516 enr_form_due_dt_alias,
1517 enr_pckg_prod_dt_alias,
1519 FROM IGS_EN_CAL_CONF
1520 WHERE s_control_num = 1;
1521 CURSOR c_chk_da_exists (
1522 cp_dt_alias IGS_EN_CAL_CONF.load_effect_dt_alias%TYPE,
1523 cp_s_cal_cat IGS_CA_TYPE.S_CAL_CAT%TYPE ) IS
1524 SELECT 'x'
1525 FROM IGS_CA_DA_INST_V daiv,
1526 IGS_CA_TYPE ct
1527 WHERE daiv.DT_ALIAS = cp_dt_alias AND
1528 daiv.CAL_TYPE = ct.CAL_TYPE AND
1529 ct.S_CAL_CAT <> cp_s_cal_cat;
1530 CURSOR c_chk_da_exists1 (
1531 cp_dt_alias IGS_EN_CAL_CONF.load_effect_dt_alias%TYPE,
1532 cp_s_cal_cat IGS_CA_TYPE.S_CAL_CAT%TYPE ,
1533 cp_s_cal_cat1 IGS_CA_TYPE.S_CAL_CAT%TYPE) IS
1534 SELECT 'x'
1535 FROM IGS_CA_DA_INST_V daiv,
1536 IGS_CA_TYPE ct
1537 WHERE daiv.DT_ALIAS = cp_dt_alias AND
1538 daiv.CAL_TYPE = ct.CAL_TYPE AND
1539 ct.S_CAL_CAT <> cp_s_cal_cat AND
1540 ct.S_CAL_CAT <> cp_s_cal_cat1;
1541 v_load_effect_dt_alias IGS_EN_CAL_CONF.load_effect_dt_alias%TYPE;
1542 v_commencement_dt_alias IGS_EN_CAL_CONF.commencement_dt_alias%TYPE;
1543 v_commence_cutoff_dt_alias IGS_EN_CAL_CONF.commence_cutoff_dt_alias%TYPE;
1544 v_enr_form_due_dt_alias IGS_EN_CAL_CONF.enr_form_due_dt_alias%TYPE;
1545 v_enr_pckg_prod_dt_alias IGS_EN_CAL_CONF.enr_pckg_prod_dt_alias%TYPE;
1546 v_effect_enr_strt_dt_alias IGS_EN_CAL_CONF.effect_enr_strt_dt_alias%TYPE;
1547 v_check_dt_alias_flag CHAR;
1548
1549 v_test_flag BOOLEAN := FALSE;
1550 BEGIN
1551 -- Check optional enrolment date aliases from the IGS_EN_CAL_CONF table
1552 OPEN c_chk_dt_aliases;
1553 FETCH c_chk_dt_aliases INTO
1554 v_load_effect_dt_alias,
1555 v_commencement_dt_alias,
1556 v_commence_cutoff_dt_alias,
1557 v_enr_form_due_dt_alias,
1558 v_enr_pckg_prod_dt_alias,
1559 v_effect_enr_strt_dt_alias;
1560 CLOSE c_chk_dt_aliases;
1561 OPEN c_chk_da_exists( v_load_effect_dt_alias, cst_load );
1562 FETCH c_chk_da_exists INTO v_check_dt_alias_flag;
1563 IF c_chk_da_exists%FOUND THEN
1564
1565 --- added by syam
1566 fnd_Message.Set_Name('IGS','IGS_CA_LODEFFDT_WITHIN_CALINS');
1567 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1568 p_s_log_type,
1569 p_log_creation_dt,
1570 'DATES' || ',' || p_acad_cal_type || ',' || TO_CHAR(p_acad_sequence_number),
1571 NULL,
1572 fnd_message.get);
1573
1574 --- added by syam
1575 END IF;
1576 CLOSE c_chk_da_exists;
1577 IF v_commencement_dt_alias IS NOT NULL THEN
1578 OPEN c_chk_da_exists( v_commencement_dt_alias, cst_academic );
1579 FETCH c_chk_da_exists INTO v_check_dt_alias_flag;
1580 IF c_chk_da_exists%FOUND THEN
1581
1582
1583 --- added by syam
1584 fnd_Message.Set_Name('IGS','IGS_CA_COMMDT_WITHIN_CALINS');
1585 --- added by syam
1586
1587 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1588 p_s_log_type,
1589 p_log_creation_dt,
1590 'DATES' || ',' || p_acad_cal_type || ',' || TO_CHAR(p_acad_sequence_number),
1591 NULL,
1592 fnd_message.get);
1593
1594 END IF;
1595 CLOSE c_chk_da_exists;
1596 END IF;
1597 IF v_commence_cutoff_dt_alias IS NOT NULL THEN
1598 OPEN c_chk_da_exists( v_commence_cutoff_dt_alias, cst_teaching );
1599 FETCH c_chk_da_exists INTO v_check_dt_alias_flag;
1600 IF c_chk_da_exists%FOUND THEN
1601
1602 --- added by syam
1603 fnd_Message.Set_Name('IGS','IGS_CA_COMCUTOF_WITHIN_CALINS');
1604 --- added by syam
1605 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1606 p_s_log_type,
1607 p_log_creation_dt,
1608 'DATES' || ',' || p_acad_cal_type || ',' || TO_CHAR(p_acad_sequence_number),
1609 NULL,
1610 fnd_message.get);
1611
1612 END IF;
1613 CLOSE c_chk_da_exists;
1614 END IF;
1615 IF v_enr_form_due_dt_alias IS NOT NULL THEN
1616 OPEN c_chk_da_exists1( v_enr_form_due_dt_alias, cst_academic, cst_enrolment );
1620 fnd_Message.Set_Name('IGS','IGS_CA_ENRDUEDT_WITHIN_CALINS');
1617 FETCH c_chk_da_exists1 INTO v_check_dt_alias_flag;
1618 IF c_chk_da_exists1%FOUND THEN
1619 --- added by syam
1621 --- added by syam
1622
1623 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1624 p_s_log_type,
1625 p_log_creation_dt,
1626 'DATES' || ',' || p_acad_cal_type || ',' || TO_CHAR(p_acad_sequence_number),
1627 NULL,
1628 fnd_message.get);
1629
1630
1631 END IF;
1632 CLOSE c_chk_da_exists1;
1633 END IF;
1634 IF v_enr_pckg_prod_dt_alias IS NOT NULL THEN
1635 OPEN c_chk_da_exists1( v_enr_pckg_prod_dt_alias, cst_academic,
1636 cst_enrolment);
1637 FETCH c_chk_da_exists1 INTO v_check_dt_alias_flag;
1638 IF c_chk_da_exists1%FOUND THEN
1639
1640 --- added by syam
1641 fnd_Message.Set_Name('IGS','IGS_CA_ENRPKGDT_WITHIN_CALINS');
1642 --- added by syam
1643
1644
1645 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1646 p_s_log_type,
1647 p_log_creation_dt,
1648 'DATES' || ',' || p_acad_cal_type || ',' || TO_CHAR(p_acad_sequence_number),
1649 NULL,
1650 fnd_message.get);
1651
1652
1653 END IF;
1654 CLOSE c_chk_da_exists1;
1655 END IF;
1656 END;
1657 EXCEPTION
1658 WHEN OTHERS THEN
1659 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1660 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_QLITY.calp_val_dates_ci');
1661 IGS_GE_MSG_STACK.ADD;
1662 lv_param_values:= p_acad_cal_type||','||(to_char(p_acad_sequence_number));
1663 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1664 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
1665 IGS_GE_MSG_STACK.ADD;
1666 lv_param_values:= p_s_log_type ||','||(to_char(p_log_creation_dt));
1667 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1668 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
1669 IGS_GE_MSG_STACK.ADD;
1670 App_Exception.Raise_Exception;
1671 END calp_val_dates_ci;
1672 --
1673 -- To quality check enrolment calendar instances
1674 PROCEDURE CALP_VAL_ENROL_CI(
1675 p_acad_cal_type IN VARCHAR2 ,
1676 p_acad_sequence_number IN NUMBER ,
1677 p_s_log_type IN VARCHAR2 ,
1678 p_log_creation_dt IN DATE )
1679 AS
1680 ------------------------------------------------------------------
1681 --Change History:
1682 --Who When What
1683 --smadathi 16-sep-2002 Bug 2086177. Included Fetching of Planned calendars
1684 -- along with the Active ones
1685 --skpandey 23-SEP-2005 BUG 4036104
1686 -- Description: Modified the cursor c_chk_acad_ci to select only those records which are of Enrollment type and are subordinate of Academic cal instance.
1687 --skpandey 27-SEP-2005 Bug: 4036104
1688 -- Description: Modified the cursor c_chk_enr_ci definition to select only those enrolment calendars (from cursor c_chk_acad_ci) which have subordinates of any type
1689 ------------------------------------------------------------------
1690 gv_other_detail VARCHAR2(255);
1691
1692 BEGIN
1693 DECLARE
1694 cst_academic CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'ACADEMIC';
1695 cst_admission CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'ADMISSION';
1696 cst_enrolment CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'ENROLMENT';
1697 cst_active CONSTANT IGS_CA_STAT.s_cal_status%TYPE := 'ACTIVE';
1698 cst_planned CONSTANT IGS_CA_STAT.s_cal_status%TYPE := 'PLANNED';
1699 CURSOR c_chk_acad_ci IS
1700 SELECT ci.cal_type, ci.sequence_number, ct.S_CAL_CAT
1701 FROM IGS_CA_INST ci,
1702 IGS_CA_TYPE ct,
1703 IGS_CA_STAT cs
1704 WHERE IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
1705 p_acad_cal_type,
1706 p_acad_sequence_number,
1707 ci.CAL_TYPE,
1708 ci.sequence_number, 'N') = 'Y' AND
1709 cs.s_cal_status IN (cst_active,cst_planned) AND
1710 ct.S_CAL_CAT = cst_enrolment AND
1711 cs.CAL_STATUS = ci.CAL_STATUS AND
1712 ct.CAL_TYPE = ci.CAL_TYPE;
1713
1714 CURSOR c_chk_enr_ci (cp_cal_type IGS_CA_INST.cal_type%type, cp_sequence_number IGS_CA_INST.sequence_number%type) IS
1715 SELECT cir.sup_cal_type, cir.sup_ci_sequence_number, ct2.S_CAL_CAT
1716 FROM IGS_CA_INST ci,
1717 IGS_CA_TYPE ct,
1718 IGS_CA_TYPE ct2,
1719 IGS_CA_STAT cs,
1720 IGS_CA_INST_REL cir
1724 cs.CAL_STATUS = ci.CAL_STATUS AND
1721 WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
1722 ct.S_CAL_CAT = cst_enrolment AND
1723 ci.CAL_TYPE = ct.CAL_TYPE AND
1725 cir.sup_cal_type = cp_cal_type AND
1726 cir.sup_ci_sequence_number = cp_sequence_number AND
1727 ct2.CAL_TYPE = cir.sub_cal_type;
1728 BEGIN
1729 --- Check that enrolment calendars are only linked to academic superior
1730 --- calendar instances.
1731 FOR v_chk_acad_ci IN c_chk_acad_ci LOOP
1732
1733 --------
1734 --- added by syam
1735 fnd_Message.Set_Name('IGS','IGS_CA_ENRCAL_ONLY_ACADMCAL');
1736 fnd_message.set_token('TOKEN1',v_chk_acad_ci.S_CAL_CAT);
1737
1738
1739 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY( p_s_log_type,
1740 p_log_creation_dt,
1741 'ENROLMENT,' || v_chk_acad_ci.cal_type || ',' ||
1742 TO_CHAR(v_chk_acad_ci.sequence_number),
1743 NULL,
1744 fnd_message.get);
1745 --- added by syam
1746
1747 --- Select enrolment calendars which have subordinates of any type
1748 -- skpandey changed for loop position, earlier it was an independent loop
1749 FOR v_chk_enr_ci IN c_chk_enr_ci(v_chk_acad_ci.cal_type, v_chk_acad_ci.sequence_number) LOOP
1750
1751 --- added by syam
1752 fnd_Message.Set_Name('IGS','IGS_CA_ENRCAL_NO_SUBORCAL');
1753 fnd_message.set_token('TOKEN1',v_chk_enr_ci.S_CAL_CAT);
1754 --- added by syam
1755
1756
1757 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1758 p_s_log_type,
1759 p_log_creation_dt,
1760 'ENROLMENT,' || v_chk_enr_ci.sup_cal_type || ',' ||
1761 TO_CHAR(v_chk_enr_ci.sup_ci_sequence_number),
1762 NULL,
1763 fnd_message.get);
1764
1765 END LOOP;
1766
1767 END LOOP;
1768
1769 END;
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1773 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_QLITY.calp_val_enrol_ci');
1774 IGS_GE_MSG_STACK.ADD;
1775 App_Exception.Raise_Exception;
1776 END calp_val_enrol_ci;
1777 --
1778 -- To quality check load calendar instances
1779 PROCEDURE CALP_VAL_LOAD_CI(
1780 p_acad_cal_type IN VARCHAR2 ,
1781 p_acad_sequence_number IN NUMBER ,
1782 p_s_log_type IN VARCHAR2 ,
1783 p_log_creation_dt IN DATE )
1784 AS
1785 ------------------------------------------------------------------
1786 --Change History:
1787 --Who When What
1788 --smadathi 11-Sep-2002 Bug 2086177. Modified code logic to define
1789 -- Award and Admission calendar categories as
1790 -- superior to the 'LOAD' category.
1791 -- Included Fetching of Planned calendars
1792 -- along with the Active ones.
1793 -- A mandatory relationship between award,fee and Load
1794 -- calendars established
1795 --schodava 17-Apr-2002 Bug #2279265
1796 -- Added 'FEE' and 'PROGRESS' calendar categories
1797 -- to the list of calendar categories
1798 -- allowed to be superior to the 'LOAD' category
1799 -- nsidana 7/30/2004 Bug : 3736551 : Added check to verify that load calenders
1800 -- do not overlap within a same academic calender.
1801 --
1802 ------------------------------------------------------------------
1803
1804 lv_param_values VARCHAR2(1080);
1805 gv_other_detail VARCHAR2(255);
1806 BEGIN
1807 DECLARE
1808
1809 v_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE;
1810 v_cal_type IGS_EN_ATD_TYPE_LOAD.CAL_TYPE%TYPE;
1811 v_key VARCHAR2(50);
1812 v_warning_msg VARCHAR2(2000);
1813 v_last_upper IGS_EN_ATD_TYPE_LOAD.upper_enr_load_range%TYPE;
1814 v_count NUMBER;
1815
1816 -- 1. Select load calendars which have superior
1817 -- relationships to parents other than academic,fee,award,admission and progress
1818 CURSOR c_ci_cat_cs IS
1819 SELECT ci.CAL_TYPE,
1820 ci.sequence_number
1821 FROM IGS_CA_INST ci,
1822 IGS_CA_TYPE cat,
1823 IGS_CA_STAT cs
1824 WHERE cs.s_cal_status IN ('ACTIVE','PLANNED') AND
1825 cat.S_CAL_CAT = 'LOAD' AND
1826 cat.CAL_TYPE = ci.CAL_TYPE AND
1827 cs.CAL_STATUS = ci.CAL_STATUS;
1828 CURSOR c_cir_ci_cat (
1829 cp_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE,
1833 FROM IGS_CA_INST_REL cir,
1830 cp_sub_ci_sequence_number
1831 IGS_CA_INST_REL.sub_ci_sequence_number%TYPE) IS
1832 SELECT cat.S_CAL_CAT
1834 IGS_CA_INST ci,
1835 IGS_CA_TYPE cat
1836 WHERE cir.sub_cal_type = cp_sub_cal_type AND
1837 cir.sub_ci_sequence_number = cp_sub_ci_sequence_number AND
1838 ci.CAL_TYPE = cir.sup_cal_type AND
1839 ci.sequence_number = cir.sup_ci_sequence_number AND
1840 cat.CAL_TYPE = ci.CAL_TYPE;
1841 -- 2. Loop through all load calendars in the specified
1842 -- academic year and check for valid links
1843 CURSOR c_load_calendars IS
1844 SELECT ci.CAL_TYPE,
1845 ci.sequence_number
1846 FROM IGS_CA_INST ci,
1847 IGS_CA_TYPE cat,
1848 IGS_CA_STAT cs
1849 WHERE cs.s_cal_status IN ('ACTIVE','PLANNED') AND
1850 cat.S_CAL_CAT = 'LOAD' AND
1851 cs.CAL_STATUS = ci.CAL_STATUS AND
1852 cat.CAL_TYPE = ci.CAL_TYPE AND
1853 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
1854 p_acad_cal_type,
1855 p_acad_sequence_number,
1856 ci.CAL_TYPE,
1857 ci.sequence_number,
1858 'N') = 'Y';
1859 -- 2.1 Check for overlapping load calenders.
1860
1861 CURSOR c_chk_overlap(cp_cal_type igs_ca_inst.cal_type%TYPE,cp_seq_num igs_ca_inst.sequence_number%TYPE)
1862 IS
1863 SELECT ci.alternate_code,ci.description,ci.start_dt,ci.end_dt
1864 FROM IGS_CA_INST ci,
1865 IGS_CA_INST ci2,
1866 IGS_CA_TYPE cat,
1867 IGS_CA_STAT cs,
1868 IGS_CA_INST_REL car
1869 WHERE
1870 ci2.cal_type = cp_cal_type AND
1871 ci2.sequence_number = cp_seq_num AND
1872 cs.s_cal_status IN ('ACTIVE','PLANNED') AND
1873 cat.S_CAL_CAT = 'LOAD' AND
1874 cs.CAL_STATUS = ci.CAL_STATUS AND
1875 cat.CAL_TYPE = ci.CAL_TYPE AND
1876 car.SUP_CAL_TYPE = p_acad_cal_type AND
1877 car.SUP_CI_SEQUENCE_NUMBER = p_acad_sequence_number AND
1878 car.SUB_CAL_TYPE = ci.CAL_TYPE AND
1879 car.SUB_CI_SEQUENCE_NUMBER = ci.sequence_number AND
1880 (ci.rowid <> ci2.rowid) AND
1881 (
1882 (ci2.start_dt BETWEEN ci.start_dt AND ci.end_dt) OR
1883 (ci2.end_dt BETWEEN ci.start_dt AND ci.end_dt) OR
1884 (ci2.start_dt <= ci.start_dt AND ci2.end_dt >= ci.end_dt)
1885 );
1886
1887 -- 3. Select load calendars which have subordinates of any type
1888 CURSOR c_cir (
1889 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1890 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1891 SELECT cir.sub_cal_type
1892 FROM IGS_CA_INST_REL cir
1893 WHERE cir.sup_cal_type = cp_cal_type AND
1894 cir.sup_ci_sequence_number = cp_sequence_number;
1895 -- 4. Check that the load calendar is not linked to
1896 -- other academic calendar instances.
1897 CURSOR c_cir2 (
1898 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1899 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1900 SELECT cir.sub_cal_type
1901 FROM IGS_CA_INST_REL cir,
1902 IGS_CA_TYPE cat
1903 WHERE cir.sub_cal_type = cp_cal_type AND
1904 cir.sub_ci_sequence_number = cp_sequence_number AND
1905 (cir.sup_cal_type <> p_acad_cal_type OR
1906 cir.sup_ci_sequence_number <> p_acad_sequence_number) AND
1907 cat.CAL_TYPE = cir.sup_cal_type AND
1908 cat.S_CAL_CAT = 'ACADEMIC';
1909 -- 5. Check that one and only one 'load effect date' alias
1910 -- instance exists within the calendar instance.
1911 CURSOR c_secc_dai (
1912 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1913 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1914 SELECT count(*)
1915 FROM IGS_EN_CAL_CONF secc,
1916 IGS_CA_DA_INST dai
1917 WHERE secc.s_control_num = 1 AND
1918 dai.CAL_TYPE = cp_cal_type AND
1919 dai.ci_sequence_number = cp_sequence_number AND
1920 dai.DT_ALIAS = secc.load_effect_dt_alias;
1921 -- 6. Checks related to default load apportion entries
1922 CURSOR c_dla (
1923 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1924 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1925 SELECT dla.teach_cal_type,
1926 dla.second_percentage
1927 FROM IGS_ST_DFT_LOAD_APPO dla
1928 WHERE dla.CAL_TYPE = cp_cal_type AND
1929 dla.ci_sequence_number = cp_sequence_number;
1930 -- 7. Validate that teaching period is linked to the same academic period.
1931 CURSOR c_ci_cs (
1932 cp_teach_cal_type IGS_ST_DFT_LOAD_APPO.teach_cal_type%TYPE) IS
1936 WHERE CAL_TYPE = cp_teach_cal_type AND
1933 SELECT count(*)
1934 FROM IGS_CA_INST ci,
1935 IGS_CA_STAT cs
1937 cs.s_cal_status IN ('ACTIVE','PLANNED') AND
1938 ci.CAL_STATUS = cs.CAL_STATUS AND
1939 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
1940 p_acad_cal_type,
1941 p_acad_sequence_number,
1942 ci.CAL_TYPE,
1943 ci.sequence_number,
1944 'N') = 'Y';
1945 -- 8. Validate that attendance type load records exist for all load calendars
1946 CURSOR c_att IS
1947 SELECT att.ATTENDANCE_TYPE
1948 FROM IGS_EN_ATD_TYPE att
1949 WHERE att.closed_ind = 'N' AND
1950 ((att.lower_enr_load_range IS NOT NULL AND
1951 att.lower_enr_load_range <> 0) OR
1952 (att.upper_enr_load_range IS NOT NULL AND
1953 att.upper_enr_load_range <> 0));
1954 CURSOR c_atl (
1955 cp_attendance_type IGS_EN_ATD_TYPE.ATTENDANCE_TYPE%TYPE,
1956 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE) IS
1957 SELECT atl.CAL_TYPE -- not used
1958 FROM IGS_EN_ATD_TYPE_LOAD atl
1959 WHERE atl.ATTENDANCE_TYPE = cp_attendance_type AND
1960 atl.CAL_TYPE = cp_cal_type;
1961 -- 9. Validate that the attendance type load records for all
1962 -- attendance types cover the entire range of EFTSU. (ie. there are no gaps)
1963 CURSOR c_atl_att (
1964 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE) IS
1965 SELECT atl.lower_enr_load_range,
1966 atl.upper_enr_load_range
1967 FROM IGS_EN_ATD_TYPE_LOAD atl,
1968 IGS_EN_ATD_TYPE att
1969 WHERE atl.CAL_TYPE = cp_cal_type AND
1970 att.closed_ind = 'N' AND
1971 att.ATTENDANCE_TYPE = atl.ATTENDANCE_TYPE
1972 ORDER BY att.lower_enr_load_range;
1973
1974 -- Cursor to Check that award calendar should have
1975 -- at least one direct subordinate Load calendar instance
1976
1977 CURSOR c_cir_6 ( cp_c_sub_cal_type igs_ca_inst.cal_type%TYPE,
1978 cp_n_sub_sequence_number igs_ca_inst.sequence_number%TYPE
1979 ) IS
1980 SELECT cir6.sup_cal_type,
1981 cir6.sup_ci_sequence_number
1982 FROM igs_ca_inst_rel cir6,
1983 igs_ca_type cat
1984 WHERE cir6.sub_cal_type = cp_c_sub_cal_type
1985 AND cir6.sub_ci_sequence_number = cp_n_sub_sequence_number
1986 AND cat.S_CAL_CAT = 'AWARD'
1987 AND cir6.sup_cal_type = cat.cal_type;
1988
1989 rec_c_cir_6 c_cir_6%ROWTYPE;
1990
1991 CURSOR c_cir_9 ( cp_c_sub_cal_type igs_ca_inst.cal_type%TYPE,
1992 cp_n_sub_sequence_number igs_ca_inst.sequence_number%TYPE
1993 ) IS
1994 SELECT cir9.sup_cal_type,
1995 cir9.sup_ci_sequence_number
1996 FROM igs_ca_inst_rel cir9,
1997 igs_ca_type cat
1998 WHERE cir9.sub_cal_type = cp_c_sub_cal_type
1999 AND cir9.sub_ci_sequence_number = cp_n_sub_sequence_number
2000 AND cat.S_CAL_CAT = 'ADMISSION'
2001 AND cir9.sup_cal_type = cat.cal_type;
2002
2003 rec_c_cir_9 c_cir_9%ROWTYPE;
2004 l_overlap c_chk_overlap%ROWTYPE;
2005
2006 BEGIN
2007 -- Quality check of calendar data structures related to Load Calendars.
2008 -- Checks Include:
2009 -- 1. Must always be linked to a single academic calendar instance
2010 -- 2. Bug #2279265 : Modified the comment
2011 -- Should only be a subordinate to academic, Fee and Progress calendars
2012 -- 3. Has no logical subordinate calendar categories
2013 -- 4. Must have one load effect date alias instance
2014 -- 5. Default load apportion should only link to teaching period
2015 -- calendar types which are offered in the related academic calendar
2016 -- 6. Attendance Type Load structure should exist for all derivable
2017 -- attendance types covering the total applicable load
2018 -----------------------------------------------------------
2019 -- Select load calendars which have superior relationships
2020 -- to parents other than academic, fee ,progress, award and Admission
2021 FOR v_ci_cat_cs_rec IN c_ci_cat_cs LOOP
2022 FOR v_cir_ci_cat_rec IN c_cir_ci_cat(
2023 v_ci_cat_cs_rec.CAL_TYPE,
2024 v_ci_cat_cs_rec.sequence_number) LOOP
2025 IF (v_cir_ci_cat_rec.S_CAL_CAT NOT IN ('ACADEMIC','FEE','PROGRESS','ADMISSION','AWARD')) THEN
2026 -- Raise Warning
2027 v_key := 'LOAD,' ||
2028 v_ci_cat_cs_rec.CAL_TYPE || ',' ||
2029 v_ci_cat_cs_rec.sequence_number;
2030 --- added by syam
2034 --- added by syam
2031 fnd_Message.Set_Name('IGS','IGS_CA_LODCAL_ONLY_ACADMCAL');
2032 fnd_message.set_token('TOKEN1',v_cir_ci_cat_rec.S_CAL_CAT);
2033 v_warning_msg := fnd_message.get;
2035
2036
2037 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2038 p_s_log_type,
2039 p_log_creation_dt,
2040 v_key,
2041 NULL,
2042 v_warning_msg);
2043 EXIT;
2044 END IF;
2045 END LOOP;
2046 END LOOP;
2047 -- Loop through all load calendars in the specified academic year and
2048 -- check for valid links.
2049 FOR v_load_calendars IN c_load_calendars LOOP
2050
2051 -- Check one per cal for SDAs.
2052 CHK_ONE_PER_CAL(v_load_calendars.cal_type, -- will check for load cal instance.
2053 v_load_calendars.sequence_number,
2054 'LOAD',
2055 p_s_log_type,
2056 p_log_creation_dt);
2057
2058 -- Check if the load calender overlaps with another one in the system. Check if there are any overlapping calenders, if yes, run a loop and record all of them in the LOG table.
2059
2060 l_overlap.alternate_code := null;
2061
2062 FOR l_overlap IN c_chk_overlap(v_load_calendars.cal_type,v_load_calendars.sequence_number)
2063 LOOP
2064
2065 v_key := 'LOAD,' || v_load_calendars.CAL_TYPE || ',' || v_load_calendars.sequence_number || ',' || l_overlap.alternate_code ; -- key for the log table.
2066
2067 fnd_Message.Set_Name('IGS','IGS_CA_OVERLAP_LOAD_CAL'); -- new message for this check.
2068 fnd_message.set_token('TOKEN1',l_overlap.alternate_code);
2069 fnd_message.set_token('TOKEN2',l_overlap.description);
2070 fnd_message.set_token('TOKEN3',l_overlap.start_dt);
2071 fnd_message.set_token('TOKEN4',l_overlap.end_dt);
2072 v_warning_msg := fnd_message.get;
2073
2074 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(p_s_log_type,
2075 p_log_creation_dt,
2076 v_key,
2077 NULL,
2078 v_warning_msg);
2079 END LOOP;
2080
2081 IF (c_chk_overlap%ISOPEN)
2082 THEN
2083 CLOSE c_chk_overlap;
2084 END IF;
2085
2086 -- Check that the load calendar is not linked to another other
2087 -- academic calendar instances.
2088 OPEN c_cir(
2089 v_load_calendars.CAL_TYPE,
2090 v_load_calendars.sequence_number);
2091 FETCH c_cir INTO v_sub_cal_type;
2092 IF (c_cir%FOUND) THEN
2093 -- Raise warning
2094 v_key := 'LOAD,' ||
2095 v_load_calendars.CAL_TYPE || ',' ||
2096 v_load_calendars.sequence_number;
2097
2098
2099 --- added by syam
2100 fnd_Message.Set_Name('IGS','IGS_CA_LODCAL_NO_SUBORCAL');
2101 fnd_message.set_token('TOKEN1',v_sub_cal_type);
2102 v_warning_msg := fnd_message.get;
2103 --- added by syam
2104
2105
2106 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2107 p_s_log_type,
2108 p_log_creation_dt,
2109 v_key,
2110 NULL,
2111 v_warning_msg);
2112 END IF;
2113 CLOSE c_cir;
2114 -- Check that the load calendar is not linked to
2115 -- other academic calendar instance.
2116 OPEN c_cir2(
2117 v_load_calendars.CAL_TYPE,
2118 v_load_calendars.sequence_number);
2119 FETCH c_cir2 INTO v_sub_cal_type;
2120 IF (c_cir2%FOUND) THEN
2121 -- Raise warning
2122 v_key := 'LOAD,' ||
2123 v_load_calendars.CAL_TYPE || ',' ||
2124 v_load_calendars.sequence_number;
2125
2126 --- added by syam
2127 fnd_Message.Set_Name('IGS','IGS_CA_LODCAL_SUB_TO_1_CALINS');
2128 v_warning_msg := fnd_message.get;
2129 --- added by syam
2130
2131 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2132 p_s_log_type,
2133 p_log_creation_dt,
2134 v_key,
2135 NULL,
2136 v_warning_msg);
2137
2138 END IF;
2139 CLOSE c_cir2;
2140
2141 /**This section covers validating the mandatory relation between admission, award , fee and Academic Term (Load) period **/
2142
2143 --Check that all Academic Term (Load) period have at least one
2144 --superior award periods
2148 FETCH c_cir_6 INTO rec_c_cir_6;
2145 OPEN c_cir_6 ( cp_c_sub_cal_type => v_load_calendars.cal_type,
2146 cp_n_sub_sequence_number => v_load_calendars.sequence_number
2147 );
2149 IF c_cir_6%NOTFOUND THEN
2150 FND_MESSAGE.SET_NAME('IGS','IGS_CA_LOAD_HAVE_1_AWDCAL');
2151 igs_ge_gen_003.genp_ins_log_entry( p_s_log_type,
2152 p_log_creation_dt,
2153 'LOAD,'|| v_load_calendars.cal_type ||',' ||v_load_calendars.sequence_number,
2154 NULL,
2155 fnd_message.get
2156 );
2157 END IF;
2158 CLOSE c_cir_6;
2159
2160 --Check that all Academic Term (Load) period have at least one
2161 --superior admission periods
2162 OPEN c_cir_9 ( cp_c_sub_cal_type => v_load_calendars.cal_type,
2163 cp_n_sub_sequence_number => v_load_calendars.sequence_number
2164 );
2165 FETCH c_cir_9 INTO rec_c_cir_9;
2166 IF c_cir_9%NOTFOUND THEN
2167 FND_MESSAGE.SET_NAME('IGS','IGS_CA_LOAD_HAVE_1_ADMCAL');
2168 igs_ge_gen_003.genp_ins_log_entry( p_s_log_type,
2169 p_log_creation_dt,
2170 'LOAD,'|| v_load_calendars.cal_type ||',' ||v_load_calendars.sequence_number,
2171 NULL,
2172 fnd_message.get
2173 );
2174 END IF;
2175 CLOSE c_cir_9;
2176
2177 /**End of this section **/
2178
2179 -- Check that one and only one load effect date alias
2180 -- instance exists within the calendar instance
2181 OPEN c_secc_dai(
2182 v_load_calendars.CAL_TYPE,
2183 v_load_calendars.sequence_number);
2184 FETCH c_secc_dai INTO v_count;
2185 CLOSE c_secc_dai;
2186 IF (v_count <> 1) THEN
2187 -- Raise warning
2188 v_key := 'LOAD,' ||
2189 v_load_calendars.CAL_TYPE || ',' ||
2190 v_load_calendars.sequence_number;
2191
2192 --- added by syam
2193 fnd_Message.Set_Name('IGS','IGS_CA_LDCLINS_1_EFFDTALIAS');
2194 v_warning_msg := fnd_message.get;
2195 --- added by syam
2196
2197 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2198 p_s_log_type,
2199 p_log_creation_dt,
2200 v_key,
2201 NULL,
2202 v_warning_msg);
2203 END IF;
2204 -- Checks related to default load apportion entries
2205 FOR v_dla_rec IN c_dla(
2206 v_load_calendars.CAL_TYPE,
2207 v_load_calendars.sequence_number) LOOP
2208 -- Validate that teaching period is linked to the same academic period.
2209 OPEN c_ci_cs(v_dla_rec.teach_cal_type);
2210 FETCH c_ci_cs INTO v_count;
2211 CLOSE c_ci_cs;
2212 IF (v_count = 0) THEN
2213 -- Raise warning
2214 v_key := 'LOAD,' ||
2215 v_load_calendars.CAL_TYPE || ',' ||
2216 v_load_calendars.sequence_number;
2217
2218
2219 --- added by syam
2220 fnd_Message.Set_Name('IGS','IGS_CA_CALTYPE_NO_INSACAD');
2221 fnd_message.set_token('TOKEN1',v_dla_rec.teach_cal_type);
2222 v_warning_msg := fnd_message.get;
2223 --- added by syam
2224
2225 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2226 p_s_log_type,
2227 p_log_creation_dt,
2228 v_key,
2229 NULL,
2230 v_warning_msg);
2231
2232
2233 END IF;
2234 -- If the second percentage is set, check
2235 -- that there is need for it
2236 IF (v_dla_rec.second_percentage IS NOT NULL AND
2237 v_count = 1) THEN
2238 -- Raise warning
2239 v_key := 'LOAD,' ||
2240 v_load_calendars.CAL_TYPE || ',' ||
2241 v_load_calendars.sequence_number;
2242
2243
2244 --- added by syam
2245 fnd_Message.Set_Name('IGS','IGS_CA_TEACHCAL_1_INSACAD');
2246 fnd_message.set_token('TOKEN1',v_dla_rec.teach_cal_type);
2247 v_warning_msg := fnd_message.get;
2248 --- added by syam
2249
2253 v_key,
2250 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2251 p_s_log_type,
2252 p_log_creation_dt,
2254 NULL,
2255 v_warning_msg);
2256 END IF;
2257 END LOOP; -- (dlft_load_apportion)
2258 -- Validate that the attendance type load records exist
2259 -- for all load calendars
2260 FOR v_att_rec IN c_att LOOP
2261 OPEN c_atl(
2262 v_att_rec.ATTENDANCE_TYPE,
2263 v_load_calendars.CAL_TYPE);
2264 FETCH c_atl INTO v_cal_type;
2265 IF (c_atl%NOTFOUND) THEN
2266 -- Raise warning
2267 v_key := 'LOAD,' ||
2268 v_load_calendars.CAL_TYPE || ',' ||
2269 v_load_calendars.sequence_number;
2270
2271 --- added by syam
2272 fnd_Message.Set_Name('IGS','IGS_CA_ATTYPE_NO_LODDTL');
2273 fnd_message.set_token('TOKEN1',v_att_rec.ATTENDANCE_TYPE);
2274 v_warning_msg := fnd_message.get;
2275 --- added by syam
2276
2277 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2278 p_s_log_type,
2279 p_log_creation_dt,
2280 v_key,
2281 NULL,
2282 v_warning_msg);
2283 END IF;
2284 CLOSE c_atl;
2285 END LOOP;
2286 -- Validate that the attendance type load records for all
2287 -- attendance types cover the entire of EFTSU.
2288 -- ie. There are no gaps
2289 v_last_upper := 0.00;
2290 FOR v_atl_att_rec IN c_atl_att(v_load_calendars.CAL_TYPE) LOOP
2291 IF ((v_atl_att_rec.lower_enr_load_range - v_last_upper) > .001) THEN
2292 -- Raise warning
2293 v_key := 'LOAD,' ||
2294 v_load_calendars.CAL_TYPE || ',' ||
2295 v_load_calendars.sequence_number;
2296
2297
2298 --- added by syam
2299 fnd_Message.Set_Name('IGS','IGS_CA_GAP_BW_ATTYPE_LODRNGES');
2300 v_warning_msg := fnd_message.get;
2301 --- added by syam
2302
2303
2304 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2305 p_s_log_type,
2306 p_log_creation_dt,
2307 v_key,
2308 NULL,
2309 v_warning_msg);
2310
2311 -- exit the v_atl_att_rec loop because we only want to
2312 -- insert the one record, even if more gaps exists.
2313 EXIT;
2314 END IF;
2315 v_last_upper := v_atl_att_rec.upper_enr_load_range;
2316 END LOOP; -- (IGS_EN_ATD_TYPE_LOAD)
2317 END LOOP; -- (IGS_CA_INST)
2318 END;
2319 EXCEPTION
2320 WHEN OTHERS THEN
2321 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2322 FND_MESSAGE.SET_TOKEN('NAME','IGA_CA_VAL_QLITY.calp_val_load_ci');
2323 IGS_GE_MSG_STACK.ADD;
2324 lv_param_values:= p_acad_cal_type ||','||(to_char(p_acad_sequence_number))||','||p_s_log_type||','||(to_char(p_log_creation_dt));
2325 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
2326 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
2327 IGS_GE_MSG_STACK.ADD;
2328 App_Exception.Raise_Exception;
2329 END calp_val_load_ci;
2330 --
2331 -- To quality check teaching calendar instances
2332 PROCEDURE CALP_VAL_TEACH_CI(
2333 p_acad_cal_type IN VARCHAR2 ,
2334 p_acad_sequence_number IN NUMBER ,
2335 p_s_log_type IN VARCHAR2 ,
2336 p_log_creation_dt IN DATE )
2337 AS
2338 ------------------------------------------------------------------
2339 --Change History:
2340 --Who When What
2341 --smadathi 16-sep-2002 Bug 2086177. Included Fetching of Planned calendars
2342 -- along with the Active ones
2343 ------------------------------------------------------------------
2344 lv_param_values VARCHAR2(1080);
2345 gv_other_detail VARCHAR2(255);
2346 BEGIN -- The checks include:
2347 -- 1. Must always be linked to at least one academic calendar
2348 -- 2. Should only have superiors of Academic or Fee calendars.
2349 -- 3. Should not have any subordinate calendars other than ADMISSION,
2350 -- and should be linked to at least ONE admission calendar.
2354 -- 6. Administrative IGS_PS_UNIT status load structure must be defined for
2351 -- 4. The total apportionment to load calendars must equal 100%
2352 -- 5. Second percentage field is only required when linked to more
2353 -- than one instance of a load calendar type.
2355 -- all applicable load calendars for any administrative IGS_PS_UNIT
2356 -- statuses which can be used in the teaching period.
2357 DECLARE
2358
2359 cst_academic CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'ACADEMIC';
2360 cst_fee CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'FEE';
2361 cst_exam CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'EXAM';
2362 cst_assessment CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'ASSESSMENT';
2363 cst_teaching CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'TEACHING';
2364 cst_progress CONSTANT igs_ca_type.s_cal_cat%TYPE := 'PROGRESS';
2365 cst_active CONSTANT IGS_CA_STAT.s_cal_status%TYPE := 'ACTIVE';
2366 cst_planned CONSTANT IGS_CA_STAT.s_cal_status%TYPE := 'PLANNED';
2367 cst_admission CONSTANT IGS_CA_STAT.s_cal_status%TYPE := 'ADMISSION';
2368
2369 v_exists_flag CHAR;
2370 v_dummy VARCHAR2(10);
2371 v_message_name VARCHAR2(30);
2372 v_total_percentage IGS_ST_DFT_LOAD_APPO.percentage%TYPE;
2373 v_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
2374 v_acad_sequence_number IGS_CA_INST.sequence_number%TYPE;
2375 v_acad_start_dt IGS_CA_INST.start_dt%TYPE;
2376 v_acad_end_dt IGS_CA_INST.end_dt%TYPE;
2377 v_p_acad_start_dt IGS_CA_INST.start_dt%TYPE;
2378 v_p_acad_end_dt IGS_CA_INST.end_dt%TYPE;
2379 v_chk_tch_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE;
2380 v_sub_not_admission BOOLEAN := FALSE;
2381 v_chk_tch_sub_rec_found BOOLEAN := FALSE;
2382 CURSOR c_acad_dates (
2383 cp_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2384 cp_acad_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
2385 SELECT start_dt,
2386 end_dt
2387 FROM IGS_CA_INST
2388 WHERE CAL_TYPE = cp_acad_cal_type AND
2389 sequence_number = cp_acad_sequence_number;
2390 CURSOR c_chk_acad_fee_ci IS
2391 SELECT cir.sub_cal_type, cir.sub_ci_sequence_number, ct1.S_CAL_CAT
2392 FROM IGS_CA_INST ci,
2393 IGS_CA_TYPE ct1,
2394 IGS_CA_TYPE ct2,
2395 IGS_CA_STAT cs,
2396 IGS_CA_INST_REL cir
2397 WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
2398 ct2.S_CAL_CAT = cst_teaching AND
2399 cs.CAL_STATUS = ci.CAL_STATUS AND
2400 cir.sub_cal_type = ci.CAL_TYPE AND
2401 cir.sub_ci_sequence_number = ci.sequence_number AND
2402 cir.sup_cal_type = ct1.CAL_TYPE AND
2403 ct2.CAL_TYPE = ci.CAL_TYPE AND
2404 ct1.S_CAL_CAT NOT IN ( cst_academic ) AND
2405 (IGS_EN_GEN_014.ENRS_GET_WITHIN_CI( p_acad_cal_type,
2406 p_acad_sequence_number,
2407 ci.CAL_TYPE,
2408 ci.sequence_number,
2409 'Y') = 'Y' OR
2410 NOT EXISTS (
2411 SELECT 'x'
2412 FROM IGS_CA_INST_REL cir,
2413 IGS_CA_INST ci,
2414 IGS_CA_TYPE cat
2415 WHERE cir.sub_cal_type = ci.CAL_TYPE AND
2416 cir.sub_ci_sequence_number = ci.sequence_number AND
2417 cir.sup_cal_type = ci.CAL_TYPE AND
2418 cir.sup_ci_sequence_number = ci.sequence_number AND
2419 ci.CAL_TYPE = cat.CAL_TYPE AND
2420 cat.S_CAL_CAT = 'ACADEMIC' ));
2421
2422
2423
2424 CURSOR c_chk_teach_ci IS
2425 SELECT ci.CAL_TYPE,
2426 ci.sequence_number,
2427 cs.s_cal_status -- Bug:2697221 cal status selected to check whether
2428 -- the status is planned or active for calculating load apportionment
2429 FROM IGS_CA_INST ci,
2430 IGS_CA_TYPE ct,
2431 IGS_CA_STAT cs
2432 WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
2433 cs.CAL_STATUS = ci.CAL_STATUS AND
2434 ct.S_CAL_CAT = cst_teaching AND
2435 ct.CAL_TYPE = ci.CAL_TYPE AND
2436 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2437 p_acad_cal_type,
2438 p_acad_sequence_number,
2439 ci.CAL_TYPE,
2440 ci.sequence_number,
2441 'Y' ) = 'Y';
2442 CURSOR c_chk_tch_sub (
2446 cat2.S_CAL_CAT
2443 cp_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
2444 cp_sequence_number IGS_CA_INST_REL.sup_ci_sequence_number%TYPE ) IS
2445 SELECT cir.sub_cal_type,
2447 FROM IGS_CA_INST_REL cir,
2448 IGS_CA_TYPE cat1,
2449 IGS_CA_TYPE cat2
2450 WHERE cir.sup_cal_type = cat1.CAL_TYPE AND
2451 cir.sub_cal_type = cat2.CAL_TYPE AND
2452 cir.sup_cal_type = cp_cal_type AND
2453 cir.sup_ci_sequence_number = cp_sequence_number;
2454 CURSOR c_chk_census_dt (
2455 cp_cal_type IGS_CA_DA_INST_V.CAL_TYPE%TYPE,
2456 cp_sequence_number IGS_CA_DA_INST_V.sequence_number%TYPE,
2457 cp_acad_start_dt DATE,
2458 cp_acad_end_dt DATE ) IS
2459 SELECT 'x'
2460 FROM dual
2461 WHERE EXISTS (
2462 SELECT *
2463 FROM IGS_GE_S_GEN_CAL_CON sgcc,
2464 IGS_CA_DA_INST_V daiv
2465 WHERE sgcc.s_control_num = 1 AND
2466 daiv.CAL_TYPE = cp_cal_type AND
2467 daiv.ci_sequence_number = cp_sequence_number AND
2468 daiv.DT_ALIAS = sgcc.census_dt_alias AND
2469 daiv.alias_val BETWEEN cp_acad_start_dt AND cp_acad_end_dt);
2470 CURSOR c_chk_acad_pct (
2471 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2472 cp_sequence_number IGS_CA_INST.sequence_number%TYPE ) IS
2473 SELECT ci.*
2474 FROM IGS_CA_INST ci,
2475 IGS_CA_TYPE ct,
2476 IGS_CA_STAT cs
2477 WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
2478 ct.S_CAL_CAT = cst_academic AND
2479 ci.CAL_TYPE = ct.CAL_TYPE AND
2480 cs.CAL_STATUS = ci.CAL_STATUS AND
2481 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2482 ci.CAL_TYPE,
2483 ci.sequence_number,
2484 cp_cal_type,
2485 cp_sequence_number,
2486 'Y' ) = 'Y';
2487 CURSOR c_chk_dla (
2488 cp_teach_cal_type IGS_ST_DFT_LOAD_APPO.teach_cal_type%TYPE,
2489 cp_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2490 cp_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
2491 SELECT dla.*
2492 FROM IGS_ST_DFT_LOAD_APPO dla
2493 WHERE teach_cal_type = cp_teach_cal_type AND
2494 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2495 cp_acad_cal_type,
2496 cp_acad_ci_sequence_number,
2497 dla.CAL_TYPE,
2498 dla.ci_sequence_number,
2499 'Y' ) = 'Y';
2500 CURSOR c_chk_tch_ci_aus IS
2501 SELECT ci.CAL_TYPE,
2502 ci.sequence_number
2503 FROM IGS_CA_INST ci,
2504 IGS_CA_TYPE ct,
2505 IGS_CA_STAT cs
2506 WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
2507 ci.CAL_STATUS = cs.CAL_STATUS AND
2508 ct.S_CAL_CAT = cst_teaching AND
2509 ct.CAL_TYPE = ci.CAL_TYPE AND
2510 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2511 p_acad_cal_type,
2512 p_acad_sequence_number,
2513 ci.CAL_TYPE,
2514 ci.sequence_number,
2515 'Y') = 'Y'; --'N'; ssawhney. this should never be N.
2516 CURSOR c_chk_aus (
2517 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2518 cp_sequence_number IGS_CA_INST.sequence_number%TYPE ) IS
2519 SELECT DISTINCT ADMINISTRATIVE_UNIT_STATUS
2520 FROM IGS_CA_DA_INST_V daiv,
2521 IGS_PS_UNIT_DISC_CRT uddc
2522 WHERE daiv.CAL_TYPE = cp_cal_type AND
2523 ci_sequence_number = cp_sequence_number AND
2524 daiv.DT_ALIAS = uddc.unit_discont_dt_alias AND
2525 uddc.delete_ind = 'N';
2526 CURSOR c_chk_load_ci (
2527 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE ) IS
2528 SELECT ci.CAL_TYPE,
2529 ci.sequence_number
2530 FROM IGS_ST_DFT_LOAD_APPO dla,
2531 IGS_CA_INST ci
2532 WHERE teach_cal_type = cp_cal_type AND
2533 ci.CAL_TYPE = dla.CAL_TYPE AND
2534 ci.sequence_number = dla.ci_sequence_number AND
2535 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2536 p_acad_cal_type,
2540 'N') = 'Y';
2537 p_acad_sequence_number,
2538 ci.CAL_TYPE,
2539 ci.sequence_number,
2541 CURSOR c_chk_ausl (
2542 cp_uddc_aus IGS_PS_UNIT_DISC_CRT.ADMINISTRATIVE_UNIT_STATUS%TYPE,
2543 cp_load_ci_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2544 cp_load_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
2545 cp_teach_ci_cal_type IGS_CA_INST.CAL_TYPE%TYPE ) IS
2546 SELECT 'x'
2547 FROM dual
2548 WHERE EXISTS (
2549 SELECT *
2550 FROM IGS_AD_ADM_UT_STT_LD
2551 WHERE ADMINISTRATIVE_UNIT_STATUS = cp_uddc_aus AND
2552 CAL_TYPE = cp_load_ci_cal_type AND
2553 ci_sequence_number = cp_load_ci_sequence_number AND
2554 teach_cal_type = cp_teach_ci_cal_type);
2555 BEGIN
2556 -- Select parameter calendar start date/end date into variables for later use.
2557 OPEN c_acad_dates (
2558 p_acad_cal_type,
2559 p_acad_sequence_number);
2560 FETCH c_acad_dates INTO v_p_acad_start_dt,
2561 v_p_acad_end_dt;
2562 IF c_acad_dates%NOTFOUND THEN
2563 CLOSE c_acad_dates;
2564 RETURN;
2565 END IF;
2566 CLOSE c_acad_dates;
2567 -- Check for teaching calendar instances which have superior calendars of a
2568 -- category other than ACADEMIC or FEE.
2569 -- Only consider calendars which are children of the academic calendar
2570 -- parameter or which don't have any superior academic calendar.
2571 -- Bug 2384110, Progression Calendar is a mandatory superior calendar for the Teaching Calendar
2572 FOR v_chk_acad_fee_ci_rec IN c_chk_acad_fee_ci LOOP
2573 IF v_chk_acad_fee_ci_rec.s_cal_cat NOT IN (cst_academic, cst_exam,
2574 cst_assessment, cst_progress) THEN
2575
2576 --- added by syam
2577 fnd_Message.set_name('IGS','IGS_CA_TEACHCAL_SUP_ACEXASCAL');
2578 fnd_message.set_token('TOKEN1',v_chk_acad_fee_ci_rec.s_cal_cat);
2579 --- added by syam
2580
2581
2582 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2583 p_s_log_type,
2584 p_log_creation_dt,
2585 'TEACHING,' || v_chk_acad_fee_ci_rec.sub_cal_type || ',' ||
2586 TO_CHAR(v_chk_acad_fee_ci_rec.sub_ci_sequence_number),
2587 NULL,
2588 fnd_message.get);
2589
2590 END IF;
2591
2592 -- Check one per cal for SDAs.
2593 CHK_ONE_PER_CAL(v_chk_acad_fee_ci_rec.sub_cal_type, -- will check for fee cal instance.
2594 v_chk_acad_fee_ci_rec.sub_ci_sequence_number,
2595 'TEACHING',
2596 p_s_log_type,
2597 p_log_creation_dt);
2598
2599 END LOOP;
2600 --- Loop through all teaching calendar instances related to the academic period
2601 FOR v_chk_teach_ci_rec IN c_chk_teach_ci LOOP
2602
2603 -- Check one per cal for SDAs.
2604 CHK_ONE_PER_CAL(v_chk_teach_ci_rec.CAL_TYPE, -- will check for teach cal instance.
2605 v_chk_teach_ci_rec.sequence_number,
2606 'TEACHING',
2607 p_s_log_type,
2608 p_log_creation_dt);
2609
2610 -- Select teaching calendars which have subordinates of any type
2611 FOR v_chk_tch_sub_rec IN c_chk_tch_sub(
2612 v_chk_teach_ci_rec.CAL_TYPE,
2613 v_chk_teach_ci_rec.sequence_number ) LOOP
2614 v_chk_tch_sub_rec_found := TRUE;
2615 v_chk_tch_sub_cal_type := v_chk_tch_sub_rec.sub_cal_type;
2616 IF v_chk_tch_sub_rec.S_CAL_CAT <> cst_admission THEN
2617 v_sub_not_admission := TRUE;
2618 EXIT;
2619 END IF;
2620 END LOOP; -- c_chk_tch_sub
2621 IF v_sub_not_admission = TRUE THEN
2622
2623 --- added by syam
2624 fnd_Message.Set_Name('IGS','IGS_CA_TEACHCAL_SUB_ADMCAL');
2625 fnd_message.set_token('TOKEN1',v_chk_tch_sub_cal_type);
2626 --- added by syam
2627
2628 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2629 p_s_log_type,
2630 p_log_creation_dt,
2631 'TEACHING,' || v_chk_teach_ci_rec.CAL_TYPE || ',' ||
2632 TO_CHAR(v_chk_teach_ci_rec.sequence_number),
2633 NULL,
2634 fnd_message.get);
2635
2636 END IF;
2637 IF v_chk_tch_sub_rec_found = FALSE THEN
2638
2639 fnd_message.set_name('IGS','IGS_CA_TEACHCAL_HAVE_1_ADM');
2640 fnd_message.set_token('CAL_TYPE',v_chk_tch_sub_cal_type);
2641
2642 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2643 p_s_log_type,
2644 p_log_creation_dt,
2645 'TEACHING,' || v_chk_teach_ci_rec.CAL_TYPE || ',' ||
2646 TO_CHAR(v_chk_teach_ci_rec.sequence_number),
2647 NULL,
2648 fnd_message.get);
2649 END IF;
2650 v_total_percentage := 0;
2651 --- Check that the calendar instance has at least 1 census date.
2652 OPEN c_chk_census_dt(
2653 v_chk_teach_ci_rec.CAL_TYPE,
2654 v_chk_teach_ci_rec.sequence_number,
2655 v_p_acad_start_dt,
2656 v_p_acad_end_dt );
2657 FETCH c_chk_census_dt INTO v_exists_flag;
2658 IF c_chk_census_dt%NOTFOUND THEN
2659
2660 --- added by syam
2661 fnd_Message.Set_Name('IGS','IGS_CA_TEACHCAL_1_CENSUSALIAS');
2662 --- added by syam
2663
2664
2665 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2666 p_s_log_type,
2667 p_log_creation_dt,
2668 'TEACHING,' || v_chk_teach_ci_rec.CAL_TYPE || ',' ||
2669 TO_CHAR(v_chk_teach_ci_rec.sequence_number),
2670 NULL,
2674 CLOSE c_chk_census_dt;
2671 fnd_message.get);
2672
2673 END IF;
2675 --- Loop through all apportionments that the teaching period has to load
2676 --- calendars and sum the percentages; they must equal 100.
2677 IF v_chk_teach_ci_rec.s_cal_status = cst_active THEN -- Bug:2697221 check added to avoid
2678 -- calculating load apportionment for planned calendar
2679 FOR v_chk_acad_pct_rec IN c_chk_acad_pct(
2680 v_chk_teach_ci_rec.CAL_TYPE,
2681 v_chk_teach_ci_rec.sequence_number ) LOOP
2682
2683 CHK_ONE_PER_CAL(v_chk_acad_pct_rec.cal_type, -- will check for apportionments that the teaching period has to "acad"
2684 v_chk_acad_pct_rec.sequence_number,
2685 'TEACHING',
2686 p_s_log_type,
2687 p_log_creation_dt);
2688
2689 FOR v_chk_dla_rec IN c_chk_dla(
2690 v_chk_teach_ci_rec.CAL_TYPE,
2691 v_chk_acad_pct_rec.CAL_TYPE,
2692 v_chk_acad_pct_rec.sequence_number) LOOP
2693 -- Total the percentages from all load calendars instances within academic
2694 -- years to which the teaching period is related.
2695 -- Select all of the load calendars within the academic year that match
2696 -- the default load apportionment record.
2697 IF IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2698 v_chk_acad_pct_rec.CAL_TYPE,
2699 v_chk_acad_pct_rec.sequence_number,
2700 v_chk_dla_rec.CAL_TYPE,
2701 v_chk_dla_rec.ci_sequence_number,
2702 'N' ) = 'Y' THEN
2703 IF v_chk_dla_rec.second_percentage IS NOT NULL THEN
2704 v_dummy := IGS_EN_GEN_002.ENRP_GET_ACAD_ALT_CD(
2705 v_chk_teach_ci_rec.CAL_TYPE,
2706 v_chk_teach_ci_rec.sequence_number,
2707 v_acad_cal_type,
2708 v_acad_sequence_number,
2709 v_acad_start_dt,
2710 v_acad_end_dt,
2711 v_message_name);
2712 IF v_acad_cal_type <> v_chk_acad_pct_rec.CAL_TYPE OR
2713 v_acad_sequence_number <> v_chk_acad_pct_rec.sequence_number THEN
2714 v_total_percentage := v_total_percentage +
2715 v_chk_dla_rec.second_percentage;
2716 ELSE
2717 v_total_percentage := v_total_percentage + v_chk_dla_rec.percentage;
2718 END IF;
2719 ELSE
2720 v_total_percentage := v_total_percentage + v_chk_dla_rec.percentage;
2721 END IF;
2722 END IF; -- IGS_EN_GEN_014.ENRS_GET_WITHIN_CI
2723 END LOOP; --- c_chk_dla
2724 END LOOP; --- c_chk_acad_pct
2725 IF v_total_percentage <> 100 THEN
2726
2727 --- added by syam
2728 fnd_Message.Set_Name('IGS','IGS_CA_TOTLODAPPORTION_NOT_100');
2729 --- added by syam
2730 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2731 p_s_log_type,
2732 p_log_creation_dt,
2733 'TEACHING,' || v_chk_teach_ci_rec.CAL_TYPE || ',' ||
2734 TO_CHAR(v_chk_teach_ci_rec.sequence_number),
2735 NULL,
2736 fnd_message.get);
2737
2738 END IF;
2739 END IF; ---- Bug:2697221 end of check added to avoid calculating load apportionment for planned calendar
2740 END LOOP; -- c_chk_teach_ci
2741 -- Validate that the administrative IGS_PS_UNIT statuses are correctly linked to all
2742 -- appropriate load calendar instances.
2743 FOR v_chk_tch_ci_aus_rec IN c_chk_tch_ci_aus LOOP
2744
2745 --SIMRAN get all teaching period under the passed acad cal.
2746 --- Select the administrative IGS_PS_UNIT statuses for all date aliases within the
2747 --- teaching calendar which are discontinuation date criteria aliases.
2748 FOR v_chk_aus_rec IN c_chk_aus(
2749 v_chk_tch_ci_aus_rec.CAL_TYPE,
2750 v_chk_tch_ci_aus_rec.sequence_number ) LOOP
2751 --- Select all load calendars to which the teaching calendar is linked
2752 --- within the parameter academic period.
2753 -- SIMRAN Change this to use TEACH_TO_LOAD_V
2754 FOR v_chk_load_ci_rec IN c_chk_load_ci(
2755 v_chk_tch_ci_aus_rec.CAL_TYPE ) LOOP
2756 OPEN c_chk_ausl(
2757 v_chk_aus_rec.ADMINISTRATIVE_UNIT_STATUS,
2758 v_chk_load_ci_rec.CAL_TYPE,
2762 IF c_chk_ausl%NOTFOUND THEN
2759 v_chk_load_ci_rec.sequence_number,
2760 v_chk_tch_ci_aus_rec.CAL_TYPE );
2761 FETCH c_chk_ausl INTO v_exists_flag;
2763
2764 --- added by syam
2765 fnd_Message.Set_Name('IGS','IGS_CA_ADMIN_STATUS_LNK_LODCAL');
2766 fnd_message.set_token('TOKEN1',v_chk_aus_rec.ADMINISTRATIVE_UNIT_STATUS);
2767 --- added by syam
2768
2769
2770 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2771 p_s_log_type,
2772 p_log_creation_dt,
2773 'TEACHING,' || v_chk_tch_ci_aus_rec.CAL_TYPE || ',' ||
2774 TO_CHAR(v_chk_tch_ci_aus_rec.sequence_number),
2775 NULL,
2776 fnd_message.get);
2777
2778
2779 END IF;
2780 CLOSE c_chk_ausl;
2781 END LOOP; -- c_chk_load_ci
2782 END LOOP; -- c_chk_aus
2783 END LOOP; -- c_chk_tch_ci_aus
2784 EXCEPTION
2785 WHEN OTHERS THEN
2786 IF c_acad_dates%ISOPEN THEN
2787 CLOSE c_acad_dates;
2788 END IF;
2789 IF c_chk_acad_fee_ci%ISOPEN THEN
2790 CLOSE c_chk_acad_fee_ci;
2791 END IF;
2792 IF c_chk_teach_ci%ISOPEN THEN
2793 CLOSE c_chk_teach_ci;
2794 END IF;
2795 IF c_chk_tch_sub%ISOPEN THEN
2796 CLOSE c_chk_tch_sub;
2797 END IF;
2798 IF c_chk_census_dt%ISOPEN THEN
2799 CLOSE c_chk_census_dt;
2800 END IF;
2801 IF c_chk_acad_pct%ISOPEN THEN
2802 CLOSE c_chk_acad_pct;
2803 END IF;
2804 IF c_chk_dla%ISOPEN THEN
2805 CLOSE c_chk_dla;
2806 END IF;
2807 IF c_chk_tch_ci_aus%ISOPEN THEN
2808 CLOSE c_chk_tch_ci_aus;
2809 END IF;
2810 IF c_chk_aus%ISOPEN THEN
2811 CLOSE c_chk_aus;
2812 END IF;
2813 IF c_chk_load_ci%ISOPEN THEN
2814 CLOSE c_chk_load_ci;
2815 END IF;
2816 IF c_chk_ausl%ISOPEN THEN
2817 CLOSE c_chk_ausl;
2818 END IF;
2819 App_Exception.Raise_Exception;
2820 END;
2821 EXCEPTION
2822 WHEN OTHERS THEN
2823 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2824 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_QLITY.calp_val_teach_ci');
2825 IGS_GE_MSG_STACK.ADD;
2826 lv_param_values:= p_acad_cal_type||','||(to_char(p_acad_sequence_number))||','||p_s_log_type||','||(to_char(p_log_creation_dt));
2827 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
2828 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
2829 IGS_GE_MSG_STACK.ADD;
2830 App_Exception.Raise_Exception;
2831 END calp_val_teach_ci;
2832
2833 PROCEDURE calp_val_award_ci( p_c_acad_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2834 p_n_acad_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2835 p_c_s_log_type IN VARCHAR2 ,
2836 p_d_log_creation_dt IN DATE
2837 ) IS
2838 ------------------------------------------------------------------
2839 --Created by : Sanil Madathil, Oracle IDC
2840 --Date created: 17 Sep 2002
2841 --
2842 --Purpose: This procedure is private to this package body .
2843 -- The Award calendars selected should be ones that overlap with
2844 -- the Academic Calendar selected in the CM parameters.
2845 --
2846 --
2847 --Known limitations/enhancements and/or remarks:
2848 --
2849 --Change History:
2850 --Who When What
2851 -------------------------------------------------------------------
2852
2853 CURSOR c_ca_inst_acad IS
2854 SELECT ci.cal_type,
2855 ci.sequence_number,
2856 ci.start_dt,
2857 ci.end_dt
2858 FROM igs_ca_inst ci
2859 WHERE ci.cal_type = p_c_acad_cal_type
2860 AND ci.sequence_number = p_n_acad_sequence_number;
2861
2862 rec_c_ca_inst_acad c_ca_inst_acad%ROWTYPE;
2863
2864 CURSOR c_ca_inst_awd (p_d_start_dt igs_ca_inst.start_dt%TYPE,
2865 p_d_end_dt igs_ca_inst.end_dt%TYPE
2866 ) IS
2867 SELECT ci.cal_type,
2868 ci.sequence_number
2869 FROM igs_ca_inst ci,
2870 igs_ca_stat cs,
2871 igs_ca_type cat
2872 WHERE cat.s_cal_cat = 'AWARD'
2873 AND cs.s_cal_status IN ('ACTIVE','PLANNED')
2874 AND ci.cal_type = cat.cal_type
2875 AND ci.cal_status = cs.cal_status
2876 AND (TRUNC(ci.start_dt) >= TRUNC(p_d_start_dt) OR
2877 TRUNC(ci.end_dt) <= TRUNC(p_d_end_dt)) ;
2878
2879 rec_c_ca_inst_awd c_ca_inst_awd%ROWTYPE;
2880
2881 -- Cursor to Check that all Award periods have
2882 -- at least one direct subordinate Load calendar instance
2883
2884 CURSOR c_cir_8 ( cp_c_sup_cal_type igs_ca_inst.cal_type%TYPE,
2885 cp_n_sup_sequence_number igs_ca_inst.sequence_number%TYPE
2886 ) IS
2887 SELECT cir8.sub_cal_type,
2888 cir8.sub_ci_sequence_number
2889 FROM igs_ca_inst_rel cir8,
2890 igs_ca_type cat
2891 WHERE cir8.sup_cal_type = cp_c_sup_cal_type
2892 AND cir8.sup_ci_sequence_number = cp_n_sup_sequence_number
2893 AND cat.s_cal_cat = 'LOAD'
2894 AND cir8.sub_cal_type = cat.cal_type;
2895
2896 rec_c_cir_8 c_cir_8%ROWTYPE;
2897
2898
2899 BEGIN
2900 FOR rec_c_ca_inst_acad IN c_ca_inst_acad
2901 LOOP
2902 FOR rec_c_ca_inst_awd IN c_ca_inst_awd (rec_c_ca_inst_acad.start_dt,
2903 rec_c_ca_inst_acad.end_dt
2904 )
2905 LOOP
2906
2907 -- Check one per cal for SDAs.
2908 CHK_ONE_PER_CAL(rec_c_ca_inst_awd.cal_type, -- will check for fee cal instance.
2909 rec_c_ca_inst_awd.sequence_number,
2910 'LOAD',
2911 p_c_s_log_type,
2912 p_d_log_creation_dt);
2913
2914 --Check that all award periods have at least one
2915 --subordinate Academic Term (Load) period
2916 OPEN c_cir_8 ( cp_c_sup_cal_type => rec_c_ca_inst_awd.cal_type,
2917 cp_n_sup_sequence_number => rec_c_ca_inst_awd.sequence_number
2918 );
2919 FETCH c_cir_8 INTO rec_c_cir_8;
2920 IF c_cir_8%NOTFOUND THEN
2921 FND_MESSAGE.SET_NAME('IGS','IGS_CA_AWDCAL_HAVE_1_LOAD');
2922 igs_ge_gen_003.genp_ins_log_entry( p_c_s_log_type,
2923 p_d_log_creation_dt,
2924 'LOAD,'|| rec_c_ca_inst_awd.cal_type ||',' ||rec_c_ca_inst_awd.sequence_number,
2925 NULL,
2926 fnd_message.get
2927 );
2928 END IF;
2929 CLOSE c_cir_8;
2930 END LOOP;
2931 END LOOP;
2932
2933 END calp_val_award_ci;
2934
2935 END IGS_CA_VAL_QLITY;
[Home] [Help]