[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_INS_ADMPERD
Source
1 PACKAGE BODY IGS_AD_INS_ADMPERD AS
2 /* $Header: IGSAD14B.pls 115.11 2003/09/29 07:45:51 nsinha ship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 |
8 | DESCRIPTION
9 | PL/SQL body for package: IGS_AD_INS_ADMPERD
10 |
11 | NOTES
12 |
13 | HISTORY
14 | Navin.Sinha 9/26/2003 Enhancement: 3132406 ENFORCE SINGLE RESPONSE TO OFFER
15 | Addded call to igs_ad_val_apac.admp_ins_dflt_apapc in FUNCTION admp_ins_adm_ci_roll.
16 |
17 +=======================================================================*/
18
19 --
20 -- Insert admission period details as part of a rollover process.
21 PROCEDURE admp_ins_acadci_roll(
22 errbuf OUT NOCOPY VARCHAR2,
23 retcode OUT NOCOPY NUMBER,
24 p_academic_period IN VARCHAR2,
25 p_admission_cat IN VARCHAR2,
26 p_org_id IN NUMBER )
27 IS
28 p_acad_cal_type IGS_CA_INST.cal_type%type;
29 p_acad_ci_sequence_number IGS_CA_INST.sequence_number%type;
30 BEGIN -- admp_ins_acadci_roll
31 -- Routine to rollover all admission period details from the old
32 -- academic period to a new academic period. This can be restricted
33 -- to an admission category
34 -- block for parameter validation/splitting of parameters
35 igs_ge_gen_003.set_org_id(p_org_id);
36 retcode := 0;
37
38 begin
39 p_acad_cal_type := ltrim(rtrim(substr(p_academic_period,102,10)));
40 p_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM(substr(p_academic_period,113,8));
41 end;
42
43 -- end of block for parameter validation/splitting of parameters
44
45 DECLARE
46 cst_admission CONSTANT VARCHAR2(9) := 'ADMISSION';
47 cst_error CONSTANT VARCHAR2(1) := 'E';
48 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
49 v_message_name VARCHAR2(30);
50 v_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
51 v_return_type VARCHAR2(1);
52 v_s_log_type VARCHAR2(8);
53 CURSOR c_ci (
54 cp_acad_cal_type IGS_CA_INST.cal_type%TYPE,
55 cp_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
56 SELECT ci.alternate_code
57 FROM IGS_CA_INST ci
58 WHERE ci.cal_type = cp_acad_cal_type AND
59 ci.sequence_number = cp_acad_ci_sequence_number;
60 CURSOR c_cir (
61 cp_acad_cal_type IGS_CA_INST.cal_type%TYPE,
62 cp_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
63 SELECT ci.cal_type,
64 ci.sequence_number,
65 ci.prior_ci_sequence_number
66 FROM IGS_CA_INST_REL cir,
67 IGS_CA_INST ci,
68 IGS_CA_TYPE cat
69 WHERE cat.cal_type = ci.cal_type AND
70 cat.s_cal_cat = cst_admission AND
71 cir.sup_cal_type = cp_acad_cal_type AND
72 cir.sup_ci_sequence_number = cp_acad_ci_sequence_number AND
73 cir.sub_cal_type = ci.cal_type AND
74 cir.sub_ci_sequence_number = ci.sequence_number;
75 BEGIN
76 -- Initialise log type
77 v_s_log_type := 'ADM-ROLL';
78 -- Get alternate code for the academic period
79 OPEN c_ci(
80 p_acad_cal_type,
81 p_acad_ci_sequence_number);
82 FETCH c_ci INTO v_alternate_code;
83 IF(v_alternate_code IS NULL) THEN
84 v_alternate_code := p_acad_cal_type;
85 END IF;
86 CLOSE c_ci;
87 -- Insert log for exception recording
88 IGS_GE_GEN_003.GENP_INS_LOG(
89 v_s_log_type,
90 v_alternate_code,
91 v_creation_dt);
92 FOR v_cir_rec IN c_cir(
93 p_acad_cal_type,
94 p_acad_ci_sequence_number) LOOP
95 IF v_cir_rec.prior_ci_sequence_number IS NOT NULL THEN
96 IF(IGS_AD_INS_ADMPERD.admp_ins_adm_ci_roll(
97 v_alternate_code,
98 v_cir_rec.cal_type,
99 v_cir_rec.prior_ci_sequence_number,
100 v_cir_rec.sequence_number,
101 p_admission_cat,
102 v_s_log_type,
103 v_creation_dt,
104 v_message_name,
105 v_return_type) = FALSE) then
106 IF(v_return_type = cst_error) THEN
107 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
108 v_s_log_type,
109 v_creation_dt,
110 'CI,'||v_cir_rec.cal_type ||','||
111 v_cir_rec.sequence_number||','||
112 FND_NUMBER.NUMBER_TO_CANONICAL(v_cir_rec.prior_ci_sequence_number),
113 v_message_name,
114 NULL);
115 END IF;
116 END IF;
117 END IF;
118 END LOOP;
119 COMMIT;
120 RETURN;
121 END;
122 EXCEPTION
123 WHEN OTHERS THEN
124 retcode := 2;
125 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
126 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
127 END admp_ins_acadci_roll;
128 --
129 -- Insert admission period course offering option.
130 FUNCTION admp_ins_apapc_apcoo(
131 p_acad_alternate_code IN VARCHAR2 ,
132 p_adm_cal_type IN VARCHAR2 ,
133 p_old_adm_ci_sequence_number IN NUMBER ,
134 p_old_admission_cat IN VARCHAR2 ,
135 p_new_adm_ci_sequence_number IN NUMBER ,
136 p_new_admission_cat IN VARCHAR2 ,
137 p_rollover_ind IN VARCHAR2 DEFAULT 'N',
138 p_s_log_type IN OUT NOCOPY VARCHAR2 ,
139 p_creation_dt IN OUT NOCOPY DATE ,
140 p_message_name OUT NOCOPY VARCHAR2 ,
141 p_return_type OUT NOCOPY VARCHAR2 )
142 RETURN BOOLEAN IS
143 BEGIN -- admp_ins_apapc_apcoo
144 -- Routine to insert all details from the one Admission Period Admission
145 -- Category
146 -- to another. This process will be called by the Admission calendar rollover
147 -- process, and from the form for duplicating admission period admission
148 -- categories.
149 -- NOTE: This function will be called after the admission period admission
150 -- category
151 -- has been inserted. The database trigger associated with the admission period
152 -- admission category also in inserts adm_period_admission process categories.
153 DECLARE
154 cst_error CONSTANT VARCHAR2(1) := 'E';
155 cst_warn CONSTANT VARCHAR2(1) := 'W';
156 v_message_name VARCHAR2(30);
157 v_apcoo_one_not_insert BOOLEAN DEFAULT FALSE;
158 v_apcoo_one_insert BOOLEAN DEFAULT FALSE;
159 v_apcoo_one_apcoo BOOLEAN DEFAULT FALSE;
160 CURSOR C_IGS_ADPRD_NUM_S IS
161 SELECT IGS_AD_PRD_PS_OF_OPT_SEQ_NUM_S.NEXTVAL FROM DUAL;
162 CURSOR c_apapc IS
163 SELECT s_admission_process_type
164 FROM IGS_AD_PRD_AD_PRC_CA
165 WHERE adm_cal_type = p_adm_cal_type AND
166 adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
167 admission_cat = p_new_admission_cat AND
168 closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
169 v_apapc_rec c_apapc%ROWTYPE;
170 CURSOR c_apapc_check (
171 cp_s_adm_process_type IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%
172 TYPE) IS
173 SELECT 'x'
174 FROM IGS_AD_PRD_AD_PRC_CA
175 WHERE adm_cal_type = p_adm_cal_type AND
176 adm_ci_sequence_number = p_old_adm_ci_sequence_number AND
177 admission_cat = p_old_admission_cat AND
178 s_admission_process_type = cp_s_adm_process_type AND
179 closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
180 v_apapc_check_exist VARCHAR2(1);
181 CURSOR c_aa_child (
182 cp_s_adm_process_type IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%
183 TYPE) IS
184 SELECT 'x'
185 FROM IGS_AD_APPL
186 WHERE adm_cal_type = p_adm_cal_type AND
187 adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
188 admission_cat = p_new_admission_cat AND
189 s_admission_process_type = cp_s_adm_process_type;
190 v_aa_child_exist VARCHAR2(1);
191 CURSOR c_apcood_child (
192 cp_s_adm_process_type IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%
193 TYPE) IS
194 SELECT 'x'
195 FROM IGS_AD_PECRS_OFOP_DT
196 WHERE adm_cal_type = p_adm_cal_type AND
197 adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
198 admission_cat = p_new_admission_cat AND
199 s_admission_process_type = cp_s_adm_process_type;
200 v_apcood_child_exist VARCHAR2(1);
201 CURSOR c_apcoo(
202 cp_s_admission_process_type
203 IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%TYPE) IS
204 SELECT adm_cal_type,
205 adm_ci_sequence_number,
206 admission_cat,
207 s_admission_process_type,
208 course_cd,
209 version_number,
210 acad_cal_type,
211 sequence_number,
212 location_cd,
213 attendance_mode,
214 attendance_type,
215 rollover_inclusion_ind
216 FROM IGS_AD_PRD_PS_OF_OPT
217 WHERE adm_cal_type = p_adm_cal_type AND
218 adm_ci_sequence_number = p_old_adm_ci_sequence_number AND
219 admission_cat = p_old_admission_cat AND
220 s_admission_process_type = cp_s_admission_process_type;
221 v_apcoo_rec c_apcoo%ROWTYPE;
222 CURSOR c_val_entry_point(
223 cp_course_cd IGS_AD_PRD_PS_OF_OPT.course_cd%TYPE,
224 cp_version_number IGS_AD_PRD_PS_OF_OPT.version_number%TYPE,
225 cp_acad_cal_type IGS_AD_PRD_PS_OF_OPT.acad_cal_type%TYPE) IS
226 SELECT 'x'
227 FROM IGS_PS_OFR_PAT cop,
228 IGS_CA_INST_REL cir
229 WHERE cir.sub_cal_type = p_adm_cal_type AND
230 cir.sub_ci_sequence_number = p_new_adm_ci_sequence_number AND
231 cop.course_cd = cp_course_cd AND
232 cop.version_number = cp_version_number AND
233 cop.cal_type = cp_acad_cal_type AND
234 cop.offered_ind = 'Y' AND
235 cop.entry_point_ind = 'Y' AND
236 cir.sup_cal_type = cop.cal_type AND
237 cir.sup_ci_sequence_number = cop.ci_sequence_number;
238 v_val_ep c_val_entry_point%ROWTYPE;
239
240 CURSOR Cur_IGS_AD_PRD_AD_PRC_CA (p_adm_cal_type IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE,
241 p_new_adm_ci_sequence_number IGS_AD_PRD_AD_PRC_CA.adm_ci_sequence_number%TYPE,
242 p_new_admission_cat IGS_AD_PRD_AD_PRC_CA.admission_cat%TYPE,
243 p_s_admission_process_type IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%TYPE
244 )IS
245 SELECT rowid, APAPC.*
246 FROM IGS_AD_PRD_AD_PRC_CA APAPC
247 WHERE adm_cal_type = p_adm_cal_type AND
248 adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
249 admission_cat = p_new_admission_cat AND
250 s_admission_process_type = p_s_admission_process_type;
251
252 lv_rowid VARCHAR2(25);
253 lv_nextval Number;
254 PROCEDURE admpl_ins_insert_log(
255 p_adm_ci_sequence_number
256 IGS_AD_PRD_PS_OF_OPT.adm_ci_sequence_number%TYPE,
257 p_admission_cat IGS_AD_PRD_PS_OF_OPT.admission_cat%TYPE,
258 p_s_admission_process_type
259 IGS_AD_PRD_PS_OF_OPT.s_admission_process_type%TYPE,
260 p_course_cd IGS_AD_PRD_PS_OF_OPT.course_cd%TYPE,
261 p_version_number IGS_AD_PRD_PS_OF_OPT.version_number%TYPE,
262 p_acad_cal_type IGS_AD_PRD_PS_OF_OPT.acad_cal_type%TYPE,
263 p_sequence_number IGS_AD_PRD_PS_OF_OPT.sequence_number%TYPE,
264 p_message_name VARCHAR2)
265 IS
266 BEGIN -- admpl_ins_insert_log
267 DECLARE
268 v_key IGS_GE_S_LOG.key%TYPE;
269 v_adm_calendar VARCHAR2(35);
270 v_s_log_type IGS_GE_S_LOG.s_log_type%TYPE;
271 v_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
272
273 CURSOR c_start_end_dt IS
274 SELECT start_dt,
275 end_dt
276 FROM IGS_CA_INST
277 WHERE cal_type = p_adm_cal_type AND
278 sequence_number = p_new_adm_ci_sequence_number;
279 v_dt_rec c_start_end_dt%ROWTYPE;
280 BEGIN
281 IF (p_s_log_type IS NULL) THEN
282 -- Get start and end dates for the admission period
283 v_s_log_type := 'ADM-ROLL';
284 OPEN c_start_end_dt;
285 FETCH c_start_end_dt INTO v_dt_rec;
286 IF (c_start_end_dt%NOTFOUND) THEN
287 v_adm_calendar := p_adm_cal_type;
288 ELSE
289 v_adm_calendar := p_adm_cal_type || '(' ||
290 TO_CHAR(v_dt_rec.start_dt, 'DD/MM/YYYY') || ' - ' ||
291 TO_CHAR(v_dt_rec.end_dt, 'DD/MM/YYYY') || ')';
292 END IF;
293 CLOSE c_start_end_dt;
294 v_key :=RPAD(p_acad_alternate_code,10) || ' ' ||
295 RPAD(v_adm_calendar,35) || ' ' ||
296 p_new_admission_cat;
297 IGS_GE_GEN_003.GENP_INS_LOG(
298 v_s_log_type,
299 v_key,
300 v_creation_dt);
301 p_s_log_type := v_s_log_type;
302 p_creation_dt := v_creation_dt;
303 END IF;
304 v_key := 'APCOO,' || p_adm_cal_type ||','||
305 FND_NUMBER.NUMBER_TO_CANONICAL(p_new_adm_ci_sequence_number) ||','|| p_new_admission_cat ||','||
306 FND_NUMBER.NUMBER_TO_CANONICAL(p_adm_ci_sequence_number) ||','|| p_admission_cat ||','||
307 p_s_admission_process_type ||','|| p_course_cd ||','||
308 FND_NUMBER.NUMBER_TO_CANONICAL(p_version_number) ||','|| p_acad_cal_type ||','||
309 FND_NUMBER.NUMBER_TO_CANONICAL(p_sequence_number);
310 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
311 p_s_log_type,
312 p_creation_dt,
313 v_key,
314 v_message_name,
315 NULL);
316 END;
317 END admpl_ins_insert_log;
318
319 BEGIN
320 -- Select IGS_AD_PRD_AD_PRC_CA to duplicate IGS_PS_COURSE offering
321 -- option restrictions.
322 OPEN c_apapc;
323 LOOP
324 FETCH c_apapc INTO v_apapc_rec;
325 EXIT WHEN c_apapc%NOTFOUND;
326 -- For each record, remove apapc if no longer required or inserted via
327 -- the table trigger.
328 OPEN c_apapc_check(
329 v_apapc_rec.s_admission_process_type);
330 FETCH c_apapc_check INTO v_apapc_check_exist;
331 IF c_apapc_check%NOTFOUND THEN
332 CLOSE c_apapc_check;
333 -- Delete this record, if no child records exist.
334 OPEN c_aa_child(
335 v_apapc_rec.s_admission_process_type);
336 FETCH c_aa_child INTO v_aa_child_exist;
337 IF c_aa_child%NOTFOUND THEN
338 CLOSE c_aa_child;
339 OPEN c_apcood_child(
340 v_apapc_rec.s_admission_process_type);
341 FETCH c_apcood_child INTO v_apcood_child_exist;
342 IF c_apcood_child%NOTFOUND THEN
343 CLOSE c_apcood_child;
344
345 FOR Rec_IGS_AD_PRD_AD_PRC_CA IN Cur_IGS_AD_PRD_AD_PRC_CA(p_adm_cal_type, p_new_adm_ci_sequence_number, p_new_admission_cat, v_apapc_rec.s_admission_process_type) LOOP
346 IGS_AD_PRD_AD_PRC_CA_Pkg.Delete_Row (
347 Rec_IGS_AD_PRD_AD_PRC_CA.RowId
348 );
349 END LOOP;
350 ELSE
351 CLOSE c_apcood_child;
352 -- If record found, process next record(APAPC)
353 END IF;
354 ELSE
355 CLOSE c_aa_child;
356 -- If record found, process next record(APAPC)
357 END IF;
358 ELSE -- c_apapc_check not found
359 CLOSE c_apapc_check;
360 -- For each record, select the Admission Period COURSE Offering Option
361 -- Restrictions for the process type.
362 OPEN c_apcoo(
363 v_apapc_rec.s_admission_process_type);
364 LOOP
365 FETCH c_apcoo INTO v_apcoo_rec;
366 EXIT WHEN c_apcoo%NOTFOUND;
367 IF (p_rollover_ind = 'Y' AND
368 v_apcoo_rec.rollover_inclusion_ind = 'N') THEN
369 -- Continue processing next IGS_AD_PRD_PS_OF_OPT.
370 NULL;
371 ELSE
372 -- Set variable to indicate one admission period COURSE offering option
373 -- found.
374 v_apcoo_one_apcoo := TRUE;
375 IF (IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_crv(
376 v_apcoo_rec.course_cd,
377 v_apcoo_rec.version_number,
378 v_apcoo_rec.s_admission_process_type,
379 'N', --(this is not offer processing)
380 v_message_name) = FALSE) THEN
381 -- Validate the IGS_PS_COURSE version.
382 -- If FALSE, set variable to indicate at least
383 -- one apcoo could not be inserted
384 v_apcoo_one_not_insert := TRUE;
385 -- Insert log **
386 admpl_ins_insert_log(
387 v_apcoo_rec.adm_ci_sequence_number,
388 v_apcoo_rec.admission_cat,
389 v_apcoo_rec.s_admission_process_type,
390 v_apcoo_rec.course_cd,
391 v_apcoo_rec.version_number,
392 v_apcoo_rec.acad_cal_type,
393 v_apcoo_rec.sequence_number,
394 v_message_name);
395 -- Continue processing COURSE offering option
396 -- restrictions.
397 ELSIF (IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_adm_cat(
398 v_apcoo_rec.course_cd,
399 v_apcoo_rec.version_number,
400 v_apcoo_rec.acad_cal_type,
401 v_apcoo_rec.location_cd,
402 v_apcoo_rec.attendance_mode,
403 v_apcoo_rec.attendance_type,
404 p_new_admission_cat,
405 v_message_name) = FALSE) THEN
406 -- Validate admission category mapping.
407 -- If FALSE, set variable to indicate at least
408 -- one apcoo could not be inserted.
409 v_apcoo_one_not_insert := TRUE;
410 --Insert log **
411 admpl_ins_insert_log(
412 v_apcoo_rec.adm_ci_sequence_number,
413 v_apcoo_rec.admission_cat,
414 v_apcoo_rec.s_admission_process_type,
415 v_apcoo_rec.course_cd,
416 v_apcoo_rec.version_number,
417 v_apcoo_rec.acad_cal_type,
418 v_apcoo_rec.sequence_number,
419 v_message_name);
420 ELSE
421 -- Validate entry point.
422 OPEN c_val_entry_point(
423 v_apcoo_rec.course_cd,
424 v_apcoo_rec.version_number,
425 v_apcoo_rec.acad_cal_type);
426 FETCH c_val_entry_point INTO v_val_ep;
427 IF (c_val_entry_point%NOTFOUND) THEN
428 CLOSE c_val_entry_point;
429 v_message_name := 'IGS_AD_PRGOFOP_NO_PRGOFOP';
430 -- Set variable to indicate at least one
431 -- apcoo could not be inserted.
432 v_apcoo_one_not_insert := TRUE;
433 -- Insert log **
434 admpl_ins_insert_log(
435 v_apcoo_rec.adm_ci_sequence_number,
436 v_apcoo_rec.admission_cat,
437 v_apcoo_rec.s_admission_process_type,
438 v_apcoo_rec.course_cd,
439 v_apcoo_rec.version_number,
440 v_apcoo_rec.acad_cal_type,
441 v_apcoo_rec.sequence_number,
442 v_message_name);
443 ELSE
444 CLOSE c_val_entry_point;
445 OPEN C_IGS_ADPRD_NUM_S;
446 FETCH C_IGS_ADPRD_NUM_S INTO lv_nextval;
447 IF C_IGS_ADPRD_NUM_S%NOTFOUND THEN
448 RAISE NO_DATA_FOUND;
449 END IF;
450 CLOSE C_IGS_ADPRD_NUM_S;
451
452 IGS_AD_PRD_PS_OF_OPT_Pkg.Insert_Row (
453 X_Mode => 'R',
454 X_RowId => lv_rowid,
455 X_Adm_Cal_Type => p_adm_cal_type,
456 X_Adm_Ci_Sequence_Number => p_new_adm_ci_sequence_number,
457 X_Admission_Cat => p_new_admission_cat,
458 X_S_Admission_Process_Type => v_apcoo_rec.s_admission_process_type,
459 X_Course_Cd => v_apcoo_rec.course_cd,
460 X_Version_Number => v_apcoo_rec.version_number,
461 X_Acad_Cal_Type => v_apcoo_rec.acad_cal_type,
462 X_Sequence_Number => lv_nextval,
463 X_Location_Cd => v_apcoo_rec.location_cd,
464 X_Attendance_Mode => v_apcoo_rec.attendance_mode,
465 X_Attendance_Type => v_apcoo_rec.attendance_type,
466 X_Rollover_Inclusion_Ind => 'Y'
467 );
468
469
470 -- Set variable to indicate at least one
471 -- apcoo has been inserted
472 v_apcoo_one_insert := TRUE;
473 END IF; -- c_val_entry_point%NOTFOUND
474 END IF; -- if validate COURSE version return false
475 END IF; --if p_rollver_ind = 'N' and rollover_inclusion_ind = 'N'
476 END LOOP; -- apcoo
477 CLOSE c_apcoo;
478 END IF; -- IF c_apapc_check%NOTFOUND
479 END LOOP; -- apapc
480 IF (c_apapc%ROWCOUNT = 0) THEN
481 CLOSE c_apapc;
482 p_message_name := 'IGS_AD_NO_ADM_PERIOD_ADM_DUPL';
483 p_return_type := cst_warn;
484 RETURN FALSE;
485 END IF;
486 CLOSE c_apapc;
487 IF (v_apcoo_one_insert = FALSE AND
488 v_apcoo_one_apcoo = TRUE) THEN
489 p_message_name := 'IGS_AD_ADMPRD_NOT_DUPLICATED';
490 p_return_type := cst_warn;
491 RETURN FALSE;
492 END IF;
493 IF (v_apcoo_one_not_insert = TRUE AND
494 v_apcoo_one_apcoo = TRUE) THEN
495 p_message_name := 'IGS_AD_ONE_ADMPERIOD_PRG_OFOP';
496 p_return_type := cst_warn;
497 RETURN FALSE;
498 END IF;
499 p_message_name := null;
500 RETURN TRUE;
501 END;
502 EXCEPTION
503 WHEN OTHERS THEN
504 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
505 Fnd_Message.Set_Token('NAME','IGS_AD_INS_ADMPERD.admp_ins_apapc_apcoo');
506 IGS_GE_MSG_STACK.ADD;
507 App_Exception.Raise_Exception;
508 END admp_ins_apapc_apcoo;
509 --
510 -- Insert admission period details as part of a rollover process.
511 FUNCTION admp_ins_adm_ci_roll(
512 p_acad_alternate_code IN VARCHAR2 ,
513 p_adm_cal_type IN VARCHAR2 ,
514 p_old_adm_ci_sequence_number IN NUMBER ,
515 p_new_adm_ci_sequence_number IN NUMBER ,
516 p_admission_cat IN VARCHAR2 ,
517 p_s_log_type IN OUT NOCOPY VARCHAR2 ,
518 p_creation_dt IN OUT NOCOPY DATE ,
519 p_message_name OUT NOCOPY VARCHAR2 ,
520 p_return_type OUT NOCOPY VARCHAR2 )
521 RETURN BOOLEAN IS
522 BEGIN --admp_ins_adm_ci_roll
523 --Routine to rollover all details from the old admission period to the new.
524 DECLARE
525 cst_error CONSTANT VARCHAR2(1) := 'E';
526 cst_warn CONSTANT VARCHAR2(1) := 'W';
527 v_ci_start_dt IGS_CA_INST.start_dt%TYPE;
528 v_ci_end_dt IGS_CA_INST.end_dt%TYPE;
529 v_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
530 v_message_name VARCHAR2(30);
531 v_s_log_type IGS_GE_S_LOG.s_log_type%TYPE;
532 v_adm_calendar VARCHAR2(50);
533 v_apac1_rec_exists BOOLEAN;
534 v_acat_not_ins BOOLEAN;
535 v_acat_det_not_ins BOOLEAN;
536 v_acat_ins BOOLEAN;
537 v_apcoo_exist VARCHAR2(1);
538 v_apac2_exist VARCHAR2(1);
539 --parameters required by call to IGS_AD_INS_ADMPERD.admp_val_adm_ci as OUT NOCOPY
540 -- parameters they are not used for any other purpose
541 v_start_dt IGS_CA_INST.start_dt%TYPE;
542 v_end_dt IGS_CA_INST.end_dt%TYPE;
543 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
544 CURSOR c_apcoo IS
545 SELECT 'X'
546 FROM IGS_AD_PRD_PS_OF_OPT apcoo
547 WHERE apcoo.adm_cal_type = p_adm_cal_type AND
548 apcoo.adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
549 (p_admission_cat IS NULL OR
550 apcoo.admission_cat = p_admission_cat);
551 CURSOR c_ci IS
552 SELECT ci.start_dt,
553 ci.end_dt
554 FROM IGS_CA_INST ci
555 WHERE ci.cal_type = p_adm_cal_type AND
556 ci.sequence_number = p_new_adm_ci_sequence_number;
557 CURSOR c_apac1 IS
558 SELECT apac.adm_cal_type,
559 apac.admission_cat,
560 apac.adm_ci_sequence_number
561 FROM IGS_AD_PERD_AD_CAT apac
562 WHERE apac.adm_cal_type = p_adm_cal_type AND
563 apac.adm_ci_sequence_number = p_old_adm_ci_sequence_number;
564 CURSOR c_apac2(
565 cp_apac1_admission_cat IGS_AD_PERD_AD_CAT.admission_cat%TYPE) IS
566 SELECT 'X'
567 FROM IGS_AD_PERD_AD_CAT apac
568 WHERE apac.adm_cal_type = p_adm_cal_type AND
569 apac.adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
570 apac.admission_cat = cp_apac1_admission_cat;
571 lv_rowid VARCHAR2(25);
572 BEGIN
573 --Initialise variables
574 p_message_name := null;
575 v_apac1_rec_exists := FALSE;
576 v_acat_not_ins := FALSE;
577 v_acat_det_not_ins := FALSE;
578 v_acat_ins := FALSE;
579 --Validate Parameters
580 IF (p_adm_cal_type IS NULL OR
581 p_old_adm_ci_sequence_number IS NULL OR
582 p_new_adm_ci_sequence_number IS NULL) THEN
583 p_message_name := 'IGS_AD_ADMPRD_CALROLL_ROLLED';
584 p_return_type := cst_error;
585 RETURN FALSE;
586 END IF;
587 --Validate that the calendar instance is PLANNED or ACTIVE
588 IF (IGS_AD_VAL_APAC.admp_val_adm_ci(
589 p_adm_cal_type,
590 p_new_adm_ci_sequence_number,
591 v_start_dt,
592 v_end_dt,
593 v_alternate_code,
594 v_message_name) = FALSE) THEN
595 p_message_name := v_message_name;
596 p_return_type := cst_error;
597 RETURN FALSE;
598 END IF;
599 --Check that one IGS_AD_PRD_PS_OF_OPT does not already exist
600 OPEN c_apcoo;
601 FETCH c_apcoo INTO v_apcoo_exist;
602 IF (c_apcoo%FOUND) THEN
603 --Admission perd COURSE offering option restrictions
604 --already exist for this adm period
605 p_message_name := 'IGS_AD_ADMPERIOD_OFOP_EXIST';
606 p_return_type := cst_error;
607 CLOSE c_apcoo;
608 RETURN FALSE;
609 END IF;
610 CLOSE c_apcoo;
611 --Insert log if it doesn't already exist
612 IF (p_s_log_type IS NULL) THEN
613 v_s_log_type := 'ADM-ROLL';
614 --Get start and end dates for the admission period
615 OPEN c_ci;
616 FETCH c_ci INTO v_ci_start_dt,
617 v_ci_end_dt;
618 IF (c_ci%NOTFOUND) THEN
619 v_adm_calendar := p_adm_cal_type;
620 ELSE
621 v_adm_calendar :=
622 p_adm_cal_type||'('||TO_CHAR(v_ci_start_dt,'DD/MM/YYYY')||'-'||
623 TO_CHAR(v_ci_end_dt,'DD/MM/YYYY')||')';
624 END IF;
625 CLOSE c_ci;
626 --Insert an entry into the system log
627 IGS_GE_GEN_003.GENP_INS_LOG(
628 v_s_log_type,
629 (RPAD(p_acad_alternate_code,10)||' '||RPAD(v_adm_calendar,35)),
630 v_creation_dt);
631 ELSE
632 v_s_log_type := p_s_log_type;
633 v_creation_dt := p_creation_dt;
634 END IF;
635 FOR v_apac1_rec IN c_apac1 LOOP
636 v_apac1_rec_exists := TRUE;
637 IF (p_admission_cat IS NULL OR
638 (p_admission_cat IS NOT NULL AND
639 p_admission_cat = v_apac1_rec.admission_cat)) THEN
640 --Check that the IGS_AD_PERD_AD_CAT does not already exist
641 OPEN c_apac2(
642 v_apac1_rec.admission_cat);
643 FETCH c_apac2 INTO v_apac2_exist;
644 IF (c_apac2%NOTFOUND) THEN
645 --Check that admission category is not closed
646 IF (IGS_AD_VAL_ACCT.admp_val_ac_closed(
647 v_apac1_rec.admission_cat,
648 v_message_name) = FALSE) THEN
649 --Set variable to indicate that at least one admission category
650 --could not be inserted
651 v_acat_not_ins := TRUE;
652 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
653 v_s_log_type,
654 v_creation_dt,
655 ('APAC' ||','||p_adm_cal_type||','||p_new_adm_ci_sequence_number||','
656 ||v_apac1_rec.adm_ci_sequence_number||','||v_apac1_rec.admission_cat),
657 v_message_name,
658 NULL);
659 ELSE
660 --Insert admission period admission category
661 --IGS_GE_NOTE: this will also insert admission period admission process
662 --Categories via the database trigger
663
664 IGS_AD_PERD_AD_CAT_Pkg.Insert_Row (
665 X_Mode => 'R',
666 X_RowId => lv_rowid,
667 X_Adm_Cal_Type => v_apac1_rec.adm_cal_type,
668 X_Adm_Ci_Sequence_Number => p_new_adm_ci_sequence_number,
669 X_Admission_Cat => v_apac1_rec.admission_cat,
670 X_Ci_Start_Dt => NULL,
671 X_Ci_End_Dt => NULL
672 );
673
674 -- Navin.Sinha 9/26/2003 Enhancement: 3132406 ENFORCE SINGLE RESPONSE TO OFFER
675 -- Addded following call to igs_ad_val_apac.admp_ins_dflt_apapc.
676 IF igs_ad_val_apac.admp_ins_dflt_apapc (
677 v_apac1_rec.adm_cal_type,
678 p_new_adm_ci_sequence_number,
679 v_apac1_rec.admission_cat,
680 v_message_name,
681 p_old_adm_ci_sequence_number -- p_ prior_adm_ci_seq_number is NULL if not Rollover
682 ) = FALSE THEN
683 Fnd_Message.Set_Name ('IGS', v_message_name);
684 IGS_GE_MSG_STACK.ADD;
685 APP_EXCEPTION.RAISE_EXCEPTION;
686 END IF;
687
688 v_acat_ins := TRUE;
689 END IF;
690 END IF;
691 CLOSE c_apac2;
692 v_acat_ins := TRUE;
693 --Insert admission period COURSE offering options
694 IF (IGS_AD_INS_ADMPERD.admp_ins_apapc_apcoo(
695 p_acad_alternate_code,
696 p_adm_cal_type,
697 p_old_adm_ci_sequence_number,
698 v_apac1_rec.admission_cat,
699 p_new_adm_ci_sequence_number,
700 v_apac1_rec.admission_cat,
701 'Y', --indicates this is called from the rollover process
702 v_s_log_type,
703 v_creation_dt,
704 v_message_name,
705 p_return_type) = FALSE) THEN
706 v_acat_det_not_ins := TRUE;
707 END IF;
708 END IF;
709 END LOOP;
710 IF (NOT v_apac1_rec_exists) THEN --there are no adm perd adm categories to roll
711 p_message_name := 'IGS_AD_NO_ADMPRD_ADMCAT_ROLL';
712 p_return_type := cst_warn;
713 RETURN FALSE;
714 END IF;
715 IF (NOT v_acat_ins) THEN --No adm categories could be rolled
716 p_message_name := 'IGS_AD_NONE_ADMCAT_ROLLED';
717 p_return_type := cst_warn;
718 RETURN FALSE;
719 END IF;
720 IF (v_acat_not_ins) THEN --At least adm category could not be rolled
721 p_message_name := 'IGS_AD_ONE_ADMPRD_NOT_ROLLED';
722 p_return_type := cst_warn;
723 RETURN FALSE;
724 END IF;
725 IF (v_acat_det_not_ins) THEN --At least one adm category details not rolled
726 p_message_name := 'IGS_AD_ADMPRD_NOT_RELATED_ROL';
727 p_return_type := cst_warn;
728 RETURN FALSE;
729 END IF;
730 --All adm categories rolled without error
731 RETURN TRUE;
732 EXCEPTION
733 WHEN OTHERS THEN
734 IF (c_apcoo%ISOPEN) THEN
735 CLOSE c_apcoo;
736 END IF;
737 IF (c_ci%ISOPEN) THEN
738 CLOSE c_ci;
739 END IF;
740 IF (c_apac1%ISOPEN) THEN
741 CLOSE c_apac1;
742 END IF;
743 IF (c_apac2%ISOPEN) THEN
744 CLOSE c_apac2;
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_AD_INS_ADMPERD.admp_ins_adm_ci_roll');
752 IGS_GE_MSG_STACK.ADD;
753 App_Exception.Raise_Exception;
754 END admp_ins_adm_ci_roll;
755 --
756 -- Validate if IGS_AD_CAT.admission_cat is closed.
757
758 --
759 -- Validate admission period calendar instance
760
761
762 END IGS_AD_INS_ADMPERD;