1 PACKAGE BODY IGS_AD_VAL_APAC AS
2 /* $Header: IGSAD37B.pls 120.1 2005/10/21 08:36:59 appldev ship $ */
3 -- Validate that admission period admission category can be duplicated.
4 FUNCTION admp_val_apac_dup(
5 p_old_adm_cal_type IN VARCHAR2 ,
6 p_old_adm_ci_sequence_number IN NUMBER ,
7 p_old_admission_cat IN VARCHAR2 ,
8 p_new_admission_cat IN VARCHAR2 ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN IS
11 gv_other_detail VARCHAR2(255);
12 BEGIN -- admp_val_apac_dup
13 -- Routine to validate that at least on IGS_AD_PRD_PS_OF_OPT can be
14 -- duplicated for the new admission category.
15 DECLARE
16 v_apcoo_course_cd IGS_AD_PRD_PS_OF_OPT.course_cd%TYPE;
17 v_apcoo_version_number IGS_AD_PRD_PS_OF_OPT.version_number%TYPE;
18 v_apcoo_acad_cal_type IGS_AD_PRD_PS_OF_OPT.acad_cal_type%TYPE;
19 v_ret_val BOOLEAN DEFAULT TRUE;
20 v_message_name varchar2(30);
21 v_apcoo_found BOOLEAN DEFAULT FALSE;
22 v_match_found BOOLEAN DEFAULT FALSE;
23 CURSOR c_apcoo IS
24 SELECT DISTINCT course_cd,
25 version_number,
26 acad_cal_type
27 FROM IGS_AD_PRD_PS_OF_OPT
28 WHERE adm_cal_type = p_old_adm_cal_type AND
29 adm_ci_sequence_number = p_old_adm_ci_sequence_number AND
30 admission_cat = p_old_admission_cat;
31 BEGIN
32 p_message_name := Null;
33 -- Check that the new admission period admission category combination has at
34 -- least one course offering option restriction that maps to the new
35 -- admission category.
36 FOR v_apcoo_rec IN c_apcoo LOOP
37 v_apcoo_found := TRUE;
38 -- For each record found, validate the course offering option of
39 -- the admission application against the admission cat.
40 IF IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_adm_cat(
41 v_apcoo_rec.course_cd,
42 v_apcoo_rec.version_number,
43 v_apcoo_rec.acad_cal_type,
44 NULL,
45 NULL,
46 NULL,
47 p_new_admission_cat,
48 v_message_name) = TRUE THEN
49 -- At least one match is found, so finish processing
50 v_match_found := TRUE;
51 EXIT;
52 END IF;
53 END LOOP;
54 IF v_apcoo_found = FALSE THEN
55 -- no records found for c_apcoo
56 p_message_name := 'IGS_AD_ADM_PERIOD_PRG_DUPL';
57 RETURN FALSE;
58 END IF;
59 IF v_match_found = TRUE THEN
60 -- existing Admission Period Course Offering Option restrictions map
61 -- to admission category
62 p_message_name := Null;
63 RETURN TRUE;
64 END IF;
65 -- The only way we reach here is if no match has been found against
66 -- admission cat rec
67 p_message_name := 'IGS_AD_ADM_PERIOD_PRG_DUPL';
68 RETURN FALSE;
69 END;
70 EXCEPTION
71 WHEN OTHERS THEN
72 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
73 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APAC.admp_val_apac_dup');
74 IGS_GE_MSG_STACK.ADD;
75 App_Exception.Raise_Exception;
76 END admp_val_apac_dup;
77
78 --
79 -- Validate admission period admission category calendar instance.
80 FUNCTION admp_val_apac_ci(
81 p_adm_cal_type IN VARCHAR2 ,
82 p_adm_ci_sequence_number IN NUMBER ,
83 p_admission_cat IN VARCHAR2 ,
84 p_start_dt OUT NOCOPY DATE ,
85 p_end_dt OUT NOCOPY DATE ,
86 p_message_name OUT NOCOPY VARCHAR2 )
87 RETURN BOOLEAN IS
88 gv_other_detail VARCHAR2(255);
89 BEGIN -- admp_val_apac_ci
90 -- Validate that the admission period admission category.
91 -- Calendar type must have calendar category of ADMISSION
92 -- Calendar instance must be ACTIVE
93 -- Calendar instance must have a superior link to one calendar instance of
94 -- category ?ACADEMIC?.
95 -- Calendar instance alternate code must not already exist for an admission
96 -- period with the same admission category linked to the same superior
97 -- academic calendar instance.
98 DECLARE
99 v_alter_found CHAR DEFAULT 'N';
100 v_alter_exist CHAR DEFAULT 'N';
101 v_sup_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE;
102 v_sup_ci_seq_no IGS_CA_INST_REL.sup_ci_sequence_number%TYPE;
103 v_message_name varchar2(30);
104 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
105 v_start_dt IGS_CA_INST.start_dt%TYPE;
106 v_end_dt IGS_CA_INST.end_dt%TYPE;
107 CURSOR c_sup (
108 p_adm_cal_type IGS_AD_PERD_AD_CAT.adm_cal_type%TYPE,
109 p_adm_ci_sequence_number
110 IGS_AD_PERD_AD_CAT.adm_ci_sequence_number%TYPE) IS
111 Select cir1.sup_cal_type,
112 cir1.sup_ci_sequence_number
113 FROM IGS_CA_INST_REL cir1,
114 IGS_CA_TYPE cat
115 WHERE cir1.sub_cal_type = p_adm_cal_type AND
116 cir1.sub_ci_sequence_number = p_adm_ci_sequence_number AND
117 cir1.sup_cal_type = cat.cal_type AND
118 cat.s_cal_cat = 'ACADEMIC';
119 CURSOR c_alter_code (
120 cp_sup_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
121 cp_sup_ci_sequence_number
122 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE) IS
123 SELECT DISTINCT ci.alternate_code,
124 cir2.sub_cal_type,
125 cir2.sub_ci_sequence_number
126 FROM IGS_CA_INST_REL cir2,
127 IGS_CA_INST ci,
128 IGS_CA_TYPE cat
129 WHERE cir2.sup_cal_type = cp_sup_cal_type AND
130 cir2.sup_ci_sequence_number = cp_sup_ci_sequence_number AND
131 (cir2.sub_cal_type <> p_adm_cal_type OR
132 cir2.sub_ci_sequence_number <> p_adm_ci_sequence_number) AND
133 cat.s_cal_cat = 'ADMISSION' AND
134 cat.cal_type = ci.cal_type AND
135 ci.cal_type = cir2.sub_cal_type AND
136 ci.sequence_number = cir2.sub_ci_sequence_number;
137 CURSOR c_apac (
138 cp_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE,
139 cp_sub_ci_sequence_number
140 IGS_CA_INST_REL.sub_ci_sequence_number%TYPE) IS
141 SELECT admission_cat
142 FROM IGS_AD_PERD_AD_CAT
143 WHERE adm_cal_type = cp_sub_cal_type AND
144 adm_ci_sequence_number = cp_sub_ci_sequence_number;
145 BEGIN
146 p_message_name := Null;
147 IF IGS_AD_VAL_APAC.admp_val_adm_ci(
148 p_adm_cal_type,
149 p_adm_ci_sequence_number,
150 v_start_dt,
151 v_end_dt,
152 v_alternate_code,
153 v_message_name) = FALSE THEN
154 p_message_name := v_message_name;
155 RETURN FALSE;
156 ELSE
157 p_start_dt:= v_start_dt;
158 p_end_dt := v_end_dt;
159 END IF;
160 OPEN c_sup(
161 p_adm_cal_type,
162 p_adm_ci_sequence_number);
163 <<c_sup_loop>>
164 LOOP
165 -- Get superior academic calendar instance
166 FETCH c_sup INTO v_sup_cal_type,
167 v_sup_ci_seq_no;
168 EXIT WHEN (c_sup%NOTFOUND);
169 -- get alternate_code, sub_cal_type and sub_ci_sequence_number
170 -- from cal_instance using superior cal_cal_type and ci_sequence_no
171 FOR v_rec_alter_code IN c_alter_code(
172 v_sup_cal_type,
173 v_sup_ci_seq_no) LOOP
174 v_alter_found := 'Y';
175 -- check for same admission category for each record found
176 FOR v_rec_apac IN c_apac(
177 v_rec_alter_code.sub_cal_type,
178 v_rec_alter_code.sub_ci_sequence_number) LOOP
179 -- Check that the alternate code of the admission period does not
180 -- already exist for another admission period linked to the same academic
181 -- period.
182 IF (v_rec_apac.admission_cat = p_admission_cat AND
183 v_rec_alter_code.alternate_code = v_alternate_code) THEN
184 v_alter_exist := 'Y';
185 EXIT c_sup_loop;
186 END IF;
187 END LOOP; -- c_apac
188 END LOOP; -- c_alter_code
189 IF (v_alter_found = 'N') THEN
190 -- must fetch twice to force it to return false and 2646 if
191 -- rowcount of c_sup found is > 1
192 FETCH c_sup INTO v_sup_cal_type,
193 v_sup_ci_seq_no;
194 EXIT c_sup_loop;
195 END IF;
196 END LOOP; -- c_sup
197 -- The admission period should be linked to ONE academic calendar
198 -- 20-OCT-2005 akadam modified this validation as per bug #4554718
199 IF (c_sup%ROWCOUNT = 0 ) THEN
200 CLOSE c_sup;
201 p_message_name := 'IGS_AD_ADMCAL_SUPLINK_ADMCAL';
202 RETURN FALSE;
203 END IF;
204 IF (v_alter_exist = 'Y') THEN
205 CLOSE c_sup;
206 p_message_name := 'IGS_AD_ADMCAL_ALTCD_ADMCAT';
207 RETURN FALSE;
208 END IF;
209 CLOSE c_sup;
210 RETURN TRUE;
211 END;
212 EXCEPTION
213 WHEN OTHERS THEN
214 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
215 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APAC.admp_val_apac_ci');
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END admp_val_apac_ci;
219
220 --
221 -- Insert admission period admission process category
222 -- Enhancement: 3132406 nsinha 9/25/2003 added new parameter p_prior_adm_ci_seq_number
223 --
224 FUNCTION admp_ins_dflt_apapc(
225 p_adm_cal_type IN VARCHAR2 ,
226 p_adm_ci_sequence_number IN NUMBER ,
227 p_admission_cat IN VARCHAR2 ,
228 p_message_name OUT NOCOPY VARCHAR2,
229 p_prior_adm_ci_seq_number IN NUMBER DEFAULT NULL
230 )
231 RETURN BOOLEAN IS
232 gv_other_detail VARCHAR2(255);
233 BEGIN -- admp_ins_dflt_apapc
234 -- Routine to insert admission period admission process categories.
235 -- This will be fired from the form when saving an admission period
236 -- for an admission category.
237 -- Enhancement: 3132406 nsinha 9/25/2003 added new parameter p_prior_adm_ci_seq_number
238 -- Added logic related to cursor c_apapc_roll.
239 DECLARE
240 CURSOR c_apapc(
241 cp_cal_type IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE,
242 cp_sequence_number IGS_AD_PRD_AD_PRC_CA.adm_ci_sequence_number%TYPE,
243 cp_admission_cat IGS_AD_PRD_AD_PRC_CA.admission_cat%TYPE) IS
244 SELECT 'x'
245 FROM IGS_AD_PRD_AD_PRC_CA
246 WHERE adm_cal_type = cp_cal_type AND
247 adm_ci_sequence_number = cp_sequence_number AND
248 admission_cat = cp_admission_cat;
249 CURSOR c_apc(
250 cp_admission_cat IGS_AD_PRD_AD_PRC_CA.admission_cat%TYPE) IS
251 SELECT s_admission_process_type
252 FROM IGS_AD_PRCS_CAT
253 WHERE admission_cat = cp_admission_cat
254 AND closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
255 v_apapc_rec c_apapc%ROWTYPE;
256 v_rowid VARCHAR2(25);
257
258 CURSOR c_apapc_roll (
259 cp_cal_type IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE,
260 cp_sequence_number IGS_AD_PRD_AD_PRC_CA.adm_ci_sequence_number%TYPE,
261 cp_admission_cat IGS_AD_PRD_AD_PRC_CA.admission_cat%TYPE) IS
262 SELECT *
263 FROM IGS_AD_PRD_AD_PRC_CA
264 WHERE adm_cal_type = cp_cal_type AND
265 adm_ci_sequence_number = cp_sequence_number AND
266 admission_cat = cp_admission_cat AND
267 NVL (closed_ind,'N') = 'N';
268 l_single_response_flag igs_ad_prd_ad_prc_ca.single_response_flag%TYPE;
269
270 BEGIN
271 p_message_name := Null;
272 -- Check that an IGS_AD_PRD_AD_PRC_CA record does not already exist
273 OPEN c_apapc(
274 p_adm_cal_type,
275 p_adm_ci_sequence_number,
276 p_admission_cat);
277 FETCH c_apapc INTO v_apapc_rec;
278 IF c_apapc%FOUND THEN
279 CLOSE c_apapc;
280 p_message_name := 'IGS_AD_CAN_DFLT_ADMPRC_TYPES';
281 RETURN FALSE;
282 END IF;
283 CLOSE c_apapc;
284
285 IF p_prior_adm_ci_seq_number IS NULL THEN
286 FOR v_apc_rec IN c_apc(p_admission_cat) LOOP
287
288 IGS_AD_PRD_AD_PRC_CA_PKG.INSERT_ROW(
289 X_ROWID => v_rowid,
290 X_ADM_CAL_TYPE => p_adm_cal_type,
291 X_ADM_CI_SEQUENCE_NUMBER => p_adm_ci_sequence_number,
292 X_ADMISSION_CAT => p_admission_cat,
293 X_S_ADMISSION_PROCESS_TYPE => v_apc_rec.s_admission_process_type,
294 X_MODE => 'R');
295
296 END LOOP;
297 ELSE -- p_prior_adm_ci_seq_number parameter IS NOT NULL
298 -- OPEN c_apapc_roll (p_adm_cal_type, p_prior_adm_ci_seq_number, p_admission_cat)
299 -- INSERT INTO IGS_AD_PRD_AD_PRC_CA_PKG, the records fetched by above cursor as follows
300 FOR v_apapc_rec IN c_apapc_roll (p_adm_cal_type, p_prior_adm_ci_seq_number, p_admission_cat) LOOP
301 --DECODE(v_apapc_rec.include_sr_in_rollover_flag,'Y', v_apapc_rec.single_response_flag ,'N')
302 IF v_apapc_rec.include_sr_in_rollover_flag = 'Y' THEN
303 l_single_response_flag := v_apapc_rec.single_response_flag;
304 ELSE
305 l_single_response_flag := 'N';
306 END IF;
307
308 IGS_AD_PRD_AD_PRC_CA_PKG.INSERT_ROW (
309 X_ROWID => v_rowid,
310 X_ADM_CAL_TYPE => p_adm_cal_type,
311 X_ADM_CI_SEQUENCE_NUMBER => p_adm_ci_sequence_number,
312 X_ADMISSION_CAT => p_admission_cat,
313 X_S_ADMISSION_PROCESS_TYPE => v_apapc_rec.s_admission_process_type,
314 X_SINGLE_RESPONSE_FLAG => l_single_response_flag,
315 X_INCLUDE_SR_IN_ROLLOVER_FLAG => v_apapc_rec.include_sr_in_rollover_flag, /*Rollover flag from Prior Admission period should be carried to new Rollover period*/
316 X_MODE => 'R');
317
318 END LOOP;
319 END IF;
320 RETURN TRUE;
321
322 END;
323 EXCEPTION
324 WHEN OTHERS THEN
325 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
326 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APAC.admp_ins_dflt_apapc');
327 IGS_GE_MSG_STACK.ADD;
328 App_Exception.Raise_Exception;
329 END admp_ins_dflt_apapc;
330
331 --
332 -- Validate admission period calendar instance
333 FUNCTION admp_val_adm_ci(
334 p_adm_cal_type IN VARCHAR2 ,
335 p_adm_ci_sequence_number IN NUMBER ,
336 p_start_dt OUT NOCOPY DATE ,
337 p_end_dt OUT NOCOPY DATE ,
338 p_alternate_code OUT NOCOPY VARCHAR2 ,
339 p_message_name OUT NOCOPY VARCHAR2 )
340 RETURN BOOLEAN IS
341 gv_other_detail VARCHAR2(255);
342 BEGIN -- admp_val_adm_ci
343 -- Validate that the admission period admission category.
344 -- Calendar type must have calendar category of ?ADMISSION?
345 -- Calendar instance must be ?ACTIVE?
346 DECLARE
347 cst_admission CONSTANT VARCHAR2(9) := 'ADMISSION';
348 cst_active CONSTANT VARCHAR2(9) := 'ACTIVE'; --removed the planned variable (cst_planned) as per bug#2722785 --rghosh
349 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
350 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
351 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
352 v_start_dt IGS_CA_INST.start_dt%TYPE;
353 v_end_dt IGS_CA_INST.end_dt%TYPE;
354 CURSOR c_s_cal_cat (
355 cp_adm_cal_type IGS_AD_PERD_AD_CAT.adm_cal_type%TYPE) IS
356 SELECT cat.s_cal_cat
357 FROM IGS_CA_TYPE cat
358 WHERE cat.cal_type = cp_adm_cal_type;
359 CURSOR c_cal_instance_cal_status (
360 cp_adm_cal_type IGS_AD_PERD_AD_CAT.adm_cal_type%TYPE,
361 cp_adm_ci_sequence_number IGS_AD_PERD_AD_CAT.adm_ci_sequence_number%TYPE)
362 IS
363 SELECT cs.s_cal_status,
364 ci.alternate_code,
365 ci.start_dt,
366 ci.end_dt
367 FROM IGS_CA_INST ci,
368 IGS_CA_STAT cs
369 WHERE ci.cal_type = cp_adm_cal_type AND
370 ci.sequence_number = cp_adm_ci_sequence_number AND
371 ci.cal_status = cs.cal_status;
372
373 BEGIN
374 p_message_name := Null;
375 OPEN c_s_cal_cat(
376 p_adm_cal_type);
377 FETCH c_s_cal_cat INTO v_s_cal_cat;
378 IF(c_s_cal_cat%FOUND) THEN
379 IF(v_s_cal_cat <> cst_admission) THEN
380 CLOSE c_s_cal_cat;
381 p_message_name := 'IGS_AD_ADMCAL_CAT_AS_ADM';
382 RETURN FALSE;
383 END IF;
384 END IF;
385 CLOSE c_s_cal_cat;
386 p_alternate_code := NULL;
387 p_start_dt := NULL;
388 p_end_dt := NULL;
389 OPEN c_cal_instance_cal_status(
390 p_adm_cal_type,
391 p_adm_ci_sequence_number);
392 FETCH c_cal_instance_cal_status INTO
393 v_s_cal_status, v_alternate_code, v_start_dt, v_end_dt;
394 IF(c_cal_instance_cal_status%FOUND) THEN
395 IF(v_s_cal_status <> cst_active) THEN --removed the planned status as per bug#2722785 --rghosh
396 CLOSE c_cal_instance_cal_status;
397 p_message_name := 'IGS_AD_ADMCAL_PLANNED_ACTIVE';
398 RETURN FALSE;
399 ELSE
400 p_alternate_code := v_alternate_code;
401 p_start_dt := v_start_dt;
402 p_end_dt := v_end_dt;
403 END IF;
404 END IF;
405 CLOSE c_cal_instance_cal_status;
406 RETURN TRUE;
407 END;
408 EXCEPTION
409 WHEN OTHERS THEN
410 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
411 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APAC.admp_val_adm_ci');
412 IGS_GE_MSG_STACK.ADD;
413 App_Exception.Raise_Exception;
414 END admp_val_adm_ci;
415 --
416 -- Validate if IGS_AD_CAT.admission_cat is closed.
417
418
419 END IGS_AD_VAL_APAC;
[Home] [Help]