[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_SS_APPL_UPD_PAGE
Source
1 PACKAGE BODY igs_ad_ss_appl_upd_page AS
2 /* $Header: IGSADC5B.pls 115.5 2003/10/30 13:18:27 rghosh noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_appl_perstat%ROWTYPE;
6 new_references igs_ad_appl_perstat%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_appl_perstat_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_admission_appl_number IN NUMBER ,
14 x_persl_stat_type IN VARCHAR2 ,
15 x_date_received IN DATE ,
16 x_creation_date IN DATE ,
17 x_created_by IN NUMBER ,
18 x_last_update_date IN DATE ,
19 x_last_updated_by IN NUMBER ,
20 x_last_update_login IN NUMBER
21 ) AS
22 /*
23 || Created By : tray
24 || Created On : 22-Oct-2002
25 || Purpose : Initialises the Old and New references for the columns of the table.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || (reverse chronological order - newest change first)
30 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM igs_ad_appl_perstat
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 OPEN cur_old_ref_values;
44 FETCH cur_old_ref_values INTO old_references;
45 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46 CLOSE cur_old_ref_values;
47 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48 igs_ge_msg_stack.add;
49 app_exception.raise_exception;
50 RETURN;
51 END IF;
52 CLOSE cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.appl_perstat_id := x_appl_perstat_id;
56 new_references.person_id := x_person_id;
57 new_references.admission_appl_number := x_admission_appl_number;
58 new_references.persl_stat_type := x_persl_stat_type;
59 new_references.date_received := x_date_received;
60
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END set_column_values;
74
75
76 PROCEDURE check_parent_existance AS
77 /*
78 || Created By : tray
79 || Created On : 22-OCT-2002
80 || Purpose : Checks for the existance of Parent records.
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 */
86 BEGIN
87
88 IF (((old_references.persl_stat_type = new_references.persl_stat_type)) OR
89 ((new_references.persl_stat_type IS NULL))) THEN
90 NULL;
91 ELSIF NOT igs_ad_per_stm_typ_pkg.get_pk_for_validation (
92 new_references.persl_stat_type ,
93 'N'
94 ) THEN
95 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
96 igs_ge_msg_stack.add;
97 app_exception.raise_exception;
98 END IF;
99
100 END check_parent_existance;
101
102 FUNCTION get_pk_for_validation (
103 x_appl_perstat_id IN NUMBER,
104 x_person_id IN NUMBER,
105 x_admission_appl_number IN NUMBER
106 ) RETURN BOOLEAN AS
107 /*
108 || Created By : tray
109 || Created On : 22-OCT-2002
110 || Purpose : Validates the Primary Key of the table.
111 || Known limitations, enhancements or remarks :
112 || Change History :
113 || Who When What
114 || (reverse chronological order - newest change first)
115 */
116 CURSOR cur_rowid IS
117 SELECT rowid
118 FROM igs_ad_appl_perstat
119 WHERE appl_perstat_id = x_appl_perstat_id AND
120 person_id = x_person_id AND
121 admission_appl_number = x_admission_appl_number
122 FOR UPDATE NOWAIT;
123
124 lv_rowid cur_rowid%RowType;
125
126 BEGIN
127
128 OPEN cur_rowid;
129 FETCH cur_rowid INTO lv_rowid;
130 IF (cur_rowid%FOUND) THEN
131 CLOSE cur_rowid;
132 RETURN(TRUE);
133 ELSE
134 CLOSE cur_rowid;
135 RETURN(FALSE);
136 END IF;
137
138 END get_pk_for_validation;
139
140
141 PROCEDURE before_dml (
142 p_action IN VARCHAR2,
143 x_rowid IN VARCHAR2 ,
144 x_appl_perstat_id IN NUMBER ,
145 x_person_id IN NUMBER ,
146 x_admission_appl_number IN NUMBER ,
147 x_persl_stat_type IN VARCHAR2 ,
148 x_date_received IN DATE ,
149 x_creation_date IN DATE ,
150 x_created_by IN NUMBER ,
151 x_last_update_date IN DATE ,
152 x_last_updated_by IN NUMBER ,
153 x_last_update_login IN NUMBER
154 ) AS
155 /*
156 || Created By : tray
157 || Created On : 22-OCT-2002
158 || Purpose : Initialises the columns, Checks Constraints, Calls the
159 || Trigger Handlers for the table, before any DML operation.
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165 BEGIN
166
167 set_column_values (
168 p_action,
169 x_rowid,
170 x_appl_perstat_id,
171 x_person_id,
172 x_admission_appl_number,
173 x_persl_stat_type,
174 x_date_received,
175 x_creation_date,
176 x_created_by,
177 x_last_update_date,
178 x_last_updated_by,
179 x_last_update_login
180 );
181
182 IF (p_action = 'INSERT') THEN
183 -- Call all the procedures related to Before Insert.
184 IF ( get_pk_for_validation( new_references.appl_perstat_id,
185 new_references.person_id,
186 new_references.admission_appl_number )
187 ) THEN
188 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
189 igs_ge_msg_stack.add;
190 app_exception.raise_exception;
191 END IF;
192 check_parent_existance;
193 END IF;
194 END before_dml;
195
196
197 PROCEDURE create_perstat_row (
198 x_rowid IN OUT NOCOPY VARCHAR2,
199 x_appl_perstat_id IN OUT NOCOPY NUMBER,
200 x_person_id IN NUMBER,
201 x_admission_appl_number IN NUMBER,
202 x_persl_stat_type IN VARCHAR2,
203 x_date_received IN DATE,
204 x_mode IN VARCHAR2
205 ) AS
206 /*
207 || Created By : tray
208 || Created On : 22-OCT-2002
209 || Purpose : Handles the INSERT DML logic for the table.
210 || Known limitations, enhancements or remarks :
211 || Change History :
212 || Who When What
213 || (reverse chronological order - newest change first)
214 */
215 CURSOR c IS
216 SELECT rowid
217 FROM igs_ad_appl_perstat
218 WHERE appl_perstat_id = x_appl_perstat_id;
219
220 x_last_update_date DATE;
221 x_last_updated_by NUMBER;
222 x_last_update_login NUMBER;
223 x_request_id NUMBER;
224 x_program_id NUMBER;
225 x_program_application_id NUMBER;
226 x_program_update_date DATE;
227
228 BEGIN
229
230 x_last_update_date := SYSDATE;
231 IF (x_mode = 'I') THEN
232 x_last_updated_by := 1;
233 x_last_update_login := 0;
234 ELSIF (x_mode = 'R') THEN
235 x_last_updated_by := fnd_global.user_id;
236 IF (x_last_updated_by IS NULL) THEN
237 x_last_updated_by := -1;
238 END IF;
239 x_last_update_login := fnd_global.login_id;
240 IF (x_last_update_login IS NULL) THEN
241 x_last_update_login := -1;
242 END IF;
243 x_request_id := fnd_global.conc_request_id;
244 x_program_id := fnd_global.conc_program_id;
245 x_program_application_id := fnd_global.prog_appl_id;
246
247 IF (x_request_id = -1) THEN
248 x_request_id := NULL;
249 x_program_id := NULL;
250 x_program_application_id := NULL;
251 x_program_update_date := NULL;
252 ELSE
253 x_program_update_date := SYSDATE;
254 END IF;
255 ELSE
256 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
257 igs_ge_msg_stack.add;
258 app_exception.raise_exception;
259 END IF;
260
261 before_dml(
262 p_action => 'INSERT',
263 x_rowid => x_rowid,
264 x_appl_perstat_id => x_appl_perstat_id,
265 x_person_id => x_person_id,
266 x_admission_appl_number => x_admission_appl_number,
267 x_persl_stat_type => x_persl_stat_type,
268 x_date_received => x_date_received,
269 x_creation_date => x_last_update_date,
270 x_created_by => x_last_updated_by,
271 x_last_update_date => x_last_update_date,
272 x_last_updated_by => x_last_updated_by,
273 x_last_update_login => x_last_update_login
274 );
275
276 INSERT INTO igs_ad_appl_perstat (
277 appl_perstat_id,
278 person_id,
279 admission_appl_number,
280 persl_stat_type,
281 date_received,
282 creation_date,
283 created_by,
284 last_update_date,
285 last_updated_by,
286 last_update_login,
287 request_id,
288 program_id,
289 program_application_id,
290 program_update_date
291 ) VALUES (
292 igs_ad_appl_perstat_s.NEXTVAL,
293 new_references.person_id,
294 new_references.admission_appl_number,
295 new_references.persl_stat_type,
296 new_references.date_received,
297 x_last_update_date,
298 x_last_updated_by,
299 x_last_update_date,
300 x_last_updated_by,
301 x_last_update_login ,
302 x_request_id,
303 x_program_id,
304 x_program_application_id,
305 x_program_update_date
306 )RETURNING appl_perstat_id INTO x_appl_perstat_id;
307 commit;
308 OPEN c;
309 FETCH c INTO x_rowid;
310 IF (c%NOTFOUND) THEN
311 CLOSE c;
312 RAISE NO_DATA_FOUND;
313 END IF;
314 CLOSE c;
315
316 END create_perstat_row;
317
318 -- To Be called from Process Req and navigate to dialog page with appropriate dialog page
319 PROCEDURE check_adm_due_date_isvalid (
320 p_adm_cal_type IN VARCHAR2 ,
321 p_adm_ci_sequence_number IN NUMBER ,
322 p_adm_cat IN VARCHAR2 ,
323 p_s_adm_prc_type IN VARCHAR2 ,
324 p_acad_cal_type IN VARCHAR2 ,
325 l_msg_count OUT NOCOPY NUMBER,
326 l_msg_data OUT NOCOPY VARCHAR2 ,
327 l_return_status OUT NOCOPY VARCHAR2) AS
328
329 CURSOR adm_cal_conf_cur IS
330 SELECT adm_appl_due_dt_alias
331 FROM igs_ad_cal_conf
332 WHERE s_control_num = 1;
333
334 l_adm_appl_due_dt_alias igs_ca_da_inst.dt_alias%TYPE;
335
336 CURSOR override_cur ( c_adm_cal_type VARCHAR2,
337 c_adm_sequence_number NUMBER,
338 c_adm_cat VARCHAR2,
339 c_acad_cal_type VARCHAR2,
340 c_s_adm_prcs_type VARCHAR2
341 ) IS
342 SELECT IGS_CA_GEN_001.calp_set_alias_value(
343 dai.absolute_val,
344 IGS_CA_GEN_002.cals_clc_dt_from_dai(
345 dai.ci_sequence_number, dai.CAL_TYPE, dai.DT_ALIAS, dai.sequence_number) ) adm_appl_due_dt_alias
346 FROM igs_ad_pecrs_ofop_dt pod, igs_ca_da_inst dai
347 WHERE dai.dt_alias = pod.dt_alias
348 AND dai.sequence_number = pod.dai_sequence_number
349 AND pod.adm_cal_type = c_adm_cal_type
350 AND pod.adm_ci_sequence_number = c_adm_sequence_number
351 AND pod.admission_cat = c_adm_cat
352 AND pod.s_admission_process_type = c_s_adm_prcs_type
353 AND NVL( pod.acad_cal_type, c_acad_cal_type) = c_acad_cal_type
354 AND dai.dt_alias = l_adm_appl_due_dt_alias;
355
356 CURSOR default_set_cur (c_adm_cal_type VARCHAR2,
357 c_adm_sequence_number NUMBER
358 ) IS
359 SELECT IGS_CA_GEN_001.calp_set_alias_value(
360 ca.absolute_val,
361 IGS_CA_GEN_002.cals_clc_dt_from_dai(
362 ca.ci_sequence_number, ca.CAL_TYPE, ca.DT_ALIAS, ca.sequence_number) ) alias_val
363 FROM IGS_CA_DA_INST ca, igs_ca_inst ci
364 WHERE ca.dt_alias = l_adm_appl_due_dt_alias
365 and ci.cal_type = ca.cal_type
366 and ci.sequence_number = ca.ci_sequence_number
367 and ci.cal_type = c_adm_cal_type
368 and ci.sequence_number = c_adm_sequence_number
369 ORDER BY 1 desc;
370
371 l_adm_due_date_alias igs_ca_da_inst_v.alias_val%TYPE;
372 l_adm_cal_type igs_ca_inst.cal_type%TYPE;
373 l_adm_sequence_number igs_ca_inst.sequence_number%TYPE;
374 l_admission_cat igs_ad_cat.admission_cat%TYPE;
375 l_s_adm_prcs_type igs_ad_pecrs_ofop_dt.s_admission_process_type%TYPE;
376 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
377
378
379 BEGIN
380 l_adm_due_date_alias:=NVL(l_adm_due_date_alias,NULL);
381 l_adm_cal_type:=p_adm_cal_type;
382 l_adm_sequence_number:=p_adm_ci_sequence_number;
383 l_admission_cat:=p_adm_cat;
384 l_s_adm_prcs_type:=p_s_adm_prc_type;
385 l_acad_cal_type:=p_acad_cal_type;
386
387 OPEN adm_cal_conf_cur;
388 FETCH adm_cal_conf_cur INTO l_adm_appl_due_dt_alias;
389 CLOSE adm_cal_conf_cur;
390 -- If the DUE-DATE alias is not defined then throw a warning message to the user
391 IF l_adm_appl_due_dt_alias IS NULL THEN
392 l_msg_count:=1;
393 l_msg_data:='IGS_AD_DUEDT_NOT_DEF';
394 l_return_status:='W';
395 RETURN;
396 END IF;
397
398 -- If the due date is defined check if there is any override for the
399 -- Admission calendar instance, admission category, academic calendar
400 OPEN override_cur ( l_adm_cal_type, l_adm_sequence_number, l_admission_cat, l_acad_cal_type, l_s_adm_prcs_type);
401 FETCH override_cur INTO l_adm_due_date_alias;
402 CLOSE override_cur;
403
404 -- If the DUE-DATE alias is not defined then check the default value
405 IF l_adm_due_date_alias IS NOT NULL THEN
406 IF TRUNC(l_adm_due_date_alias) < TRUNC(SYSDATE) THEN
407 l_msg_count:=1;
408 l_msg_data:='IGS_AD_FINDUEDT_PASD_NEWAPPL';
409 l_return_status:='W';
410 RETURN;
411 END IF;
412 ELSE
413 OPEN default_set_cur ( l_adm_cal_type, l_adm_sequence_number);
414 FETCH default_set_cur INTO l_adm_due_date_alias;
415 CLOSE default_set_cur;
416 IF TRUNC(l_adm_due_date_alias) < TRUNC(SYSDATE) THEN
417 l_msg_count:=1;
418 l_msg_data:='IGS_AD_FINDUEDT_PASD_NEWAPPL';
419 l_return_status:='W';
420 RETURN;
421 END IF;
422 END IF;
423 l_msg_count:=0;
424 l_msg_data:=null;
425 l_return_status:=null;
426 RETURN;
427 END check_adm_due_date_isvalid;
428
429 PROCEDURE validate_due_final_dt(
430 p_adm_cal_type IN VARCHAR2,
431 p_adm_ci_sequence_number IN NUMBER,
432 p_adm_cat IN VARCHAR2,
433 p_s_adm_prc_type IN VARCHAR2,
434 p_course_cd IN VARCHAR2,
435 p_crv_version_number IN NUMBER,
436 p_acad_cal_type IN VARCHAR2,
437 p_location_cd IN VARCHAR2,
438 p_attendance_mode IN VARCHAR2,
439 p_attendance_type IN VARCHAR2,
440 l_msg_count OUT NOCOPY NUMBER,
441 l_msg_data OUT NOCOPY VARCHAR2,
442 l_return_status OUT NOCOPY VARCHAR2) AS
443
444 v_adm_appl_due_dt_alias IGS_AD_CAL_CONF.adm_appl_due_dt_alias%TYPE;
445 v_adm_appl_final_dt_alias IGS_AD_CAL_CONF.adm_appl_final_dt_alias%TYPE;
446 v_due_dt DATE;
447 v_final_dt DATE;
448 l_max_duedt DATE;
449
450 CURSOR c_sacc IS
451 SELECT adm_appl_due_dt_alias,
452 adm_appl_final_dt_alias
453 FROM IGS_AD_CAL_CONF
454 WHERE s_control_num = 1;
455
456 l_adm_cal_type igs_ca_inst.cal_type%TYPE ;
457 l_adm_sequence_number igs_ca_inst.sequence_number%TYPE;
458 l_adm_cat IGS_AD_PRCS_CAT_STEP.ADMISSION_CAT%TYPE;
459 l_s_adm_prc_typ IGS_AD_PRCS_CAT_STEP.S_ADMISSION_PROCESS_TYPE%TYPE;
460 l_late_appl_exists VARCHAR2(2000);
461
462
463 CURSOR c_dai IS
464 SELECT MAX( IGS_CA_GEN_001.calp_set_alias_value(
465 dai.absolute_val,
466 IGS_CA_GEN_002.cals_clc_dt_from_dai(
467 dai.ci_sequence_number, dai.CAL_TYPE, dai.DT_ALIAS, dai.sequence_number) )) alias_val
468 FROM igs_ca_da da, igs_ca_da_inst dai
469 WHERE da.s_cal_cat = 'ADMISSION'
470 AND da.dt_alias = v_adm_appl_due_dt_alias
471 AND da.dt_alias = dai.dt_alias
472 AND dai.cal_type = l_adm_cal_type
473 AND dai.ci_sequence_number = l_adm_sequence_number;
474
475 CURSOR c_apcs (
476 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
477 cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
478 SELECT 'X'
479 FROM IGS_AD_PRCS_CAT_STEP apcs,
480 IGS_LOOKUPS_VIEW sasty
481 WHERE apcs.admission_cat = cp_admission_cat AND
482 apcs.s_admission_process_type = cp_s_admission_process_type AND
483 apcs.s_admission_step_type = 'LATE-APP' AND
484 sasty.step_group_type = 'APPL-VAL' AND
485 sasty.lookup_type = 'ADMISSION_STEP_TYPE' AND
486 sasty.lookup_code = apcs.s_admission_step_type
487 ORDER BY apcs.s_admission_step_type;
488
489 BEGIN
490 l_adm_cal_type:=l_adm_cal_type;
491 l_adm_sequence_number:=l_adm_sequence_number;
492 l_adm_cat:=p_adm_cat;
493 l_s_adm_prc_typ:=p_s_adm_prc_type;
494
495 OPEN c_sacc;
496 FETCH c_sacc INTO
497 v_adm_appl_due_dt_alias,
498 v_adm_appl_final_dt_alias;
499
500 IF c_sacc%NOTFOUND OR v_adm_appl_due_dt_alias IS NULL THEN --1
501 CLOSE c_sacc;
502 l_msg_count:=1;
503 l_msg_data:='IGS_AD_DUEDT_NOT_DEF';
504 l_return_status:='W';
505 RETURN;
506 ELSE --1
507 CLOSE c_sacc;
508 OPEN c_dai;
509 FETCH c_dai INTO l_max_duedt;
510 IF c_dai%NOTFOUND OR l_max_duedt IS NULL THEN --2
511 CLOSE c_dai;
512 l_msg_count:=1;
513 l_msg_data:='IGS_AD_DUEDT_INST_NOT_MAP';
514 l_return_status:='W';
515 RETURN;
516 ELSE --2
517 CLOSE c_dai;
518 IF l_max_duedt < TRUNC(SYSDATE) THEN --3
519 l_msg_count:=1;
520 l_msg_data:='IGS_AD_FINDUEDT_PASD';
521 l_return_status:='W';
522 RETURN;
523 END IF; --3
524 END IF; --2
525 END IF; --1
526
527 OPEN c_apcs(l_adm_cat,l_s_adm_prc_typ);
528 FETCH c_apcs INTO l_late_appl_exists;
529 CLOSE c_apcs;
530 IF l_late_appl_exists IS NOT NULL THEN
531 IF v_adm_appl_due_dt_alias IS NOT NULL THEN
532 v_due_dt := IGS_AD_GEN_003.ADMP_GET_ADM_PERD_DT(
533 v_adm_appl_due_dt_alias,
534 p_adm_cal_type,
535 p_adm_ci_sequence_number,
536 p_adm_cat,
537 p_s_adm_prc_type,
538 p_course_cd,
539 p_crv_version_number,
540 p_acad_cal_type,
541 p_location_cd,
542 p_attendance_mode,
543 p_attendance_type);
544 IF v_due_dt IS NULL THEN
545 l_msg_count:=1;
546 l_msg_data:='IGS_AD_NO_DUEDT_POO';
547 l_return_status:='W';
548 RETURN;
549 END IF;
550 END IF;
551 ELSE
552 IF v_adm_appl_final_dt_alias IS NOT NULL THEN
553 v_final_dt := IGS_AD_GEN_003.ADMP_GET_ADM_PERD_DT(
554 v_adm_appl_due_dt_alias,
555 p_adm_cal_type,
556 p_adm_ci_sequence_number,
557 p_adm_cat,
558 p_s_adm_prc_type,
559 p_course_cd,
560 p_crv_version_number,
561 p_acad_cal_type,
562 p_location_cd,
563 p_attendance_mode,
564 p_attendance_type);
565
566 IF v_final_dt IS NULL THEN
567 l_msg_count:=1;
568 l_msg_data:='IGS_AD_NO_FINALDT_POO';
569 l_return_status:='W';
570 RETURN;
571 END IF;
572 END IF;
573 END IF;
574 l_msg_count:=0;
575 l_msg_data:=null;
576 l_return_status:=null;
577 RETURN;
578 END validate_due_final_dt;
579
580 PROCEDURE validate_pref_unique(
581 p_person_id IN IGS_AD_PS_APPL_INST.person_id%TYPE,
582 p_adm_appl_no IN IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
583 p_course_cd IN IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
584 p_seq_number IN IGS_AD_PS_APPL_INST.sequence_number%TYPE,
585 p_pref_number IN NUMBER,
586 l_msg_count OUT NOCOPY NUMBER,
587 l_msg_data OUT NOCOPY VARCHAR2,
588 l_return_status OUT NOCOPY VARCHAR2) AS
589 CURSOR c_acai (
590 cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
591 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
592 cp_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
593 cp_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE) IS
594 SELECT acai.preference_number
595 FROM IGS_AD_PS_APPL_INST acai
596 WHERE acai.person_id = cp_person_id AND
597 acai.admission_appl_number = cp_admission_appl_number AND
598 NOT (acai.nominated_course_cd = cp_nominated_course_cd AND
599 acai.sequence_number = cp_acai_sequence_number)
600 ORDER BY
601 acai.preference_number;
602 BEGIN
603 FOR v_acai IN c_acai (
604 p_person_id,
605 p_adm_appl_no,
606 p_course_cd,
607 p_seq_number) LOOP
608 IF v_acai.preference_number = p_pref_number THEN
609 l_msg_count:=1;
610 l_msg_data:= 'IGS_AD_PREFNUM_NOT_UNIQUE';
611 l_return_status:='E';
612 RETURN;
613 END IF;
614 END LOOP;
615 l_msg_count:=0;
616 l_msg_data:= null;
617 l_return_status:=null;
618 RETURN;
619 END validate_pref_unique;
620
621 FUNCTION admp_val_chg_of_pref(
622 p_adm_cal_type IN VARCHAR2 ,
623 p_adm_ci_sequence_number IN NUMBER ,
624 p_admission_cat IN VARCHAR2 ,
625 p_s_admission_process_type IN VARCHAR2 ,
626 p_course_cd IN VARCHAR2 ,
627 p_crv_version_number IN NUMBER ,
628 p_acad_cal_type IN VARCHAR2 ,
629 p_location_cd IN VARCHAR2 ,
630 p_attendance_mode IN VARCHAR2 ,
631 p_attendance_type IN VARCHAR2 ,
632 l_message_name OUT NOCOPY VARCHAR2 )RETURN VARCHAR2 AS
633 lv_return_value VARCHAR2(2000);
634 BEGIN
635 IF IGS_AD_VAL_ACAI.admp_val_chg_of_pref(
636 p_adm_cal_type ,
637 p_adm_ci_sequence_number ,
638 p_admission_cat ,
639 p_s_admission_process_type ,
640 p_course_cd ,
641 p_crv_version_number ,
642 p_acad_cal_type ,
643 p_location_cd ,
644 p_attendance_mode ,
645 p_attendance_type ,
646 l_message_name) THEN
647 lv_return_value:='TRUE';
648 ELSE
649 lv_return_value:='FALSE';
650 END IF;
651 RETURN lv_return_value;
652 END admp_val_chg_of_pref;
653
654 FUNCTION admp_val_acai_update(
655 p_adm_appl_status IN VARCHAR2 ,
656 p_person_id IN NUMBER ,
657 p_admission_appl_number IN NUMBER ,
658 p_nominated_course_cd IN VARCHAR2 ,
659 p_acai_sequence_number IN NUMBER ,
660 p_message_name OUT NOCOPY VARCHAR2 ,
661 p_update_non_enrol_detail_ind OUT NOCOPY VARCHAR2 )
662 RETURN VARCHAR2 AS
663 lv_return_value VARCHAR2(2000);
664 BEGIN
665 IF IGS_AD_VAL_ACAI.admp_val_acai_update
666 ( p_adm_appl_status ,
667 p_person_id ,
668 p_admission_appl_number ,
669 p_nominated_course_cd ,
670 p_acai_sequence_number ,
671 p_message_name ,
672 p_update_non_enrol_detail_ind ) THEN
673 lv_return_value:='TRUE';
674 ELSE
675 lv_return_value:='FALSE';
676 END IF;
677 RETURN lv_return_value;
678 END admp_val_acai_update;
679
680 FUNCTION admp_val_acai_pref(
681 p_preference_number IN NUMBER ,
682 p_pref_allowed IN VARCHAR2 ,
683 p_pref_limit IN NUMBER ,
684 p_s_admission_process_type IN VARCHAR2 ,
685 p_message_name OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 AS
686 lv_return_value VARCHAR2(2000);
687 BEGIN
688 IF IGS_AD_VAL_ACAI.admp_val_acai_pref (
689 p_preference_number ,
690 p_pref_allowed ,
691 p_pref_limit ,
692 p_s_admission_process_type ,
693 p_message_name ) THEN
694 lv_return_value:='TRUE';
695 ELSE
696 lv_return_value:='FALSE';
697 END IF;
698 RETURN lv_return_value;
699 END admp_val_acai_pref;
700
701 FUNCTION admp_val_acai_opt(
702 p_course_cd IN VARCHAR2 ,
703 p_version_number IN NUMBER ,
704 p_acad_cal_type IN VARCHAR2 ,
705 p_acad_ci_sequence_number IN NUMBER ,
706 p_location_cd IN VARCHAR2 ,
707 p_attendance_mode IN VARCHAR2 ,
708 p_attendance_type IN VARCHAR2 ,
709 p_adm_cal_type IN VARCHAR2 ,
710 p_adm_ci_sequence_number IN NUMBER ,
711 p_admission_cat IN VARCHAR2 ,
712 p_s_admission_process_type IN VARCHAR2 ,
713 p_offer_ind IN VARCHAR2 ,
714 p_appl_dt IN DATE ,
715 p_late_appl_allowed IN VARCHAR2 ,
716 p_message_name OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 AS
717 lv_return_value VARCHAR2(2000);
718 BEGIN
719 IF IGS_AD_VAL_ACAI.admp_val_acai_opt
720 (
721 p_course_cd ,
722 p_version_number ,
723 p_acad_cal_type ,
724 p_acad_ci_sequence_number ,
725 p_location_cd ,
726 p_attendance_mode ,
727 p_attendance_type ,
728 p_adm_cal_type ,
729 p_adm_ci_sequence_number ,
730 p_admission_cat ,
731 p_s_admission_process_type ,
732 p_offer_ind ,
733 p_appl_dt ,
734 p_late_appl_allowed ,
735 p_message_name
736 ) THEN
737 lv_return_value:='TRUE';
738 ELSE
739 lv_return_value:='FALSE';
740 END IF;
741 RETURN lv_return_value;
742 END admp_val_acai_opt;
743
744 FUNCTION admp_val_acai_us(
745 p_unit_set_cd IN VARCHAR2 ,
746 p_us_version_number IN NUMBER ,
747 p_course_cd IN VARCHAR2 ,
748 p_crv_version_number IN NUMBER ,
749 p_acad_cal_type IN VARCHAR2 ,
750 p_location_cd IN VARCHAR2 ,
751 p_attendance_mode IN VARCHAR2 ,
752 p_attendance_type IN VARCHAR2 ,
753 p_admission_cat IN VARCHAR2 ,
754 p_offer_ind IN VARCHAR2 ,
755 p_unit_set_appl IN VARCHAR2 ,
756 p_message_name OUT NOCOPY VARCHAR2 ,
757 p_return_type OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 AS
758 lv_return_value VARCHAR2(2000);
759 BEGIN
760 IF
761 IGS_AD_VAL_ACAI.admp_val_acai_us
762 (
763 p_unit_set_cd ,
764 p_us_version_number ,
765 p_course_cd ,
766 p_crv_version_number ,
767 p_acad_cal_type ,
768 p_location_cd ,
769 p_attendance_mode ,
770 p_attendance_type ,
771 p_admission_cat ,
772 p_offer_ind ,
773 p_unit_set_appl ,
774 p_message_name ,
775 p_return_type
776 ) THEN
777 lv_return_value:='TRUE';
778 ELSE
779 lv_return_value:='FALSE';
780 END IF;
781 RETURN lv_return_value;
782 END admp_val_acai_us;
783
784 FUNCTION admp_val_aa_update(
785 p_adm_appl_status IN VARCHAR2 ,
786 p_message_name OUT NOCOPY VARCHAR2 )
787 RETURN VARCHAR2 AS
788 lv_return_value VARCHAR2(2000);
789 BEGIN
790 IF IGS_AD_VAL_AA.admp_val_aa_update
791 ( p_adm_appl_status ,
792 p_message_name
793 ) THEN
794 lv_return_value:='TRUE';
795 ELSE
796 lv_return_value:='FALSE';
797 END IF;
798 RETURN lv_return_value;
799 END;
800
801 PROCEDURE final_scrn_intw_event(
802 p_person_id IN NUMBER,
803 p_admission_appl_number IN NUMBER,
804 p_nominated_course_cd IN VARCHAR2,
805 p_sequence_number IN NUMBER,
806 p_final_screening_decision IN VARCHAR2,
807 p_final_screening_date IN DATE,
808 p_panel_code IN VARCHAR2,
809 p_raised_for IN VARCHAR2
810 ) AS
811 ----------------------------------------------------------------
812 --Created by : Navin Sinha
813 --Date created: 19-Jun-03
814 --
815 --Purpose: BUG NO : 1366894 - Interview Build.
816 -- This procedure would trigger the Final Screening Decision business event.
817 -- It is triggered from the form IGS_AD_PANEL_DTLS_PKG (TBH for IGS_AD_PANEL_DTLS)
818 --
819 --Known limitations/enhancements and/or remarks:
820 --
821 --Change History:
822 --Who When What
823 ----------------------------------------------------------------
824
825 l_event_t wf_event_t;
826 l_parameter_list_t wf_parameter_list_t;
827 l_itemKey varchar2(100);
828
829 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
830 CURSOR cur_prof_value IS
831 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
832 FROM dual;
833
834 l_cur_prof_value cur_prof_value%ROWTYPE;
835
836 -- Gets a unique sequence number
837 CURSOR c_seq_num IS
838 SELECT igs_ad_wf_scrn_intw_s.NEXTVAL
839 FROM dual;
840
841 l_seq_val_screen_int_s NUMBER;
842
843 BEGIN
844 -- Checking if the Workflow is installed at the environment or not.
845 OPEN cur_prof_value;
846 FETCH cur_prof_value INTO l_cur_prof_value;
847 CLOSE cur_prof_value;
848
849 IF (l_cur_prof_value.value = 'Y') THEN
850
851 -- Get the sequence value
852 OPEN c_seq_num;
853 FETCH c_seq_num INTO l_seq_val_screen_int_s ;
854 CLOSE c_seq_num ;
855
856 -- initialize the wf_event_t object
857 wf_event_t.Initialize(l_event_t);
858
859 -- Adding the parameters to the parameter list
860 wf_event.AddParameterToList (p_name => 'P_PERSON_ID',p_value=> p_person_id ,p_parameterlist => l_parameter_list_t);
861 wf_event.AddParameterToList (p_name => 'P_ADMISSION_APPL_NUMBER', p_value => p_admission_appl_number, p_parameterlist => l_parameter_list_t);
862 wf_event.AddParameterToList (p_name => 'P_NOMINATED_COURSE_CD', p_value => p_nominated_course_cd, p_ParameterList => l_parameter_list_t);
863 wf_event.AddParameterToList (p_name => 'P_SEQUENCE_NUMBER', p_value => p_sequence_number, p_parameterlist => l_parameter_list_t);
864 wf_event.AddParameterToList (p_name => 'P_FINAL_SCREENING_DECISION', p_value => p_final_screening_decision, p_parameterlist => l_parameter_list_t);
865 wf_event.AddParameterToList (p_name => 'P_FINAL_SCREENING_DATE', p_value => p_final_screening_date, p_parameterlist => l_parameter_list_t);
866 wf_event.AddParameterToList (p_name => 'P_PANEL_CODE', p_value => p_panel_code, p_parameterlist => l_parameter_list_t);
867
868 IF p_raised_for = 'SCREENING' THEN
869 -- Raise the Event
870 -- Generate a unique value for the event key by concatenating the event name with a sequence value
871 -- (IGS_AD_SCREEN_INT_S) and Raise the business event
872 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.ad.interview.finalscreening',
873 p_event_key => 'FINALSCREENING' || l_seq_val_screen_int_s,
874 p_parameters => l_parameter_list_t);
875
876 -- Deleting the Parameter list after the event is raised
877 l_parameter_list_t.delete;
878 ELSIF p_raised_for = 'INTERVIEW' THEN
879 -- Raise the Event
880 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.ad.interview.finalinterview',
881 p_event_key => 'FINALINTERVIEW' || l_seq_val_screen_int_s,
882 p_parameters => l_parameter_list_t);
883
884 -- Deleting the Parameter list after the event is raised
885 l_parameter_list_t.delete;
886 END IF;
887 END IF;
888 END final_scrn_intw_event;
889
890 END igs_ad_ss_appl_upd_page;