[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PS_APPL_PKG
Source
1 package body IGS_AD_PS_APPL_PKG as
2 /* $Header: IGSAI16B.pls 120.1 2005/07/14 00:57:40 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_PS_APPL_ALL%RowType;
6 new_references IGS_AD_PS_APPL_ALL%RowType;
7
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_org_id IN NUMBER DEFAULT NULL,
13 x_person_id IN NUMBER DEFAULT NULL,
14 x_admission_appl_number IN NUMBER DEFAULT NULL,
15 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
16 x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
17 x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
18 x_admission_cd IN VARCHAR2 DEFAULT NULL,
19 x_course_rank_set IN VARCHAR2 DEFAULT NULL,
20 x_course_rank_schedule IN VARCHAR2 DEFAULT NULL,
21 x_req_for_reconsideration_ind IN VARCHAR2 DEFAULT NULL,
22 x_req_for_adv_standing_ind IN VARCHAR2 DEFAULT NULL,
23 x_creation_date IN DATE DEFAULT NULL,
24 x_created_by IN NUMBER DEFAULT NULL,
25 x_last_update_date IN DATE DEFAULT NULL,
26 x_last_updated_by IN NUMBER DEFAULT NULL,
27 x_last_update_login IN NUMBER DEFAULT NULL
28 ) AS
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_AD_PS_APPL_ALL
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 Open cur_old_ref_values;
42 Fetch cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 App_Exception.Raise_Exception;
47 Close cur_old_ref_values;
48 Return;
49 END IF;
50 Close cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.org_id := x_org_id;
54 new_references.person_id := x_person_id;
55 new_references.admission_appl_number := x_admission_appl_number;
56 new_references.nominated_course_cd := x_nominated_course_cd;
57 new_references.transfer_course_cd := x_transfer_course_cd;
58 new_references.basis_for_admission_type := x_basis_for_admission_type;
59 new_references.admission_cd := x_admission_cd;
60 new_references.course_rank_set := x_course_rank_set;
61 new_references.course_rank_schedule := x_course_rank_schedule;
62 new_references.req_for_reconsideration_ind := x_req_for_reconsideration_ind;
63 new_references.req_for_adv_standing_ind := x_req_for_adv_standing_ind;
64 IF (p_action = 'UPDATE') THEN
65 new_references.creation_date := old_references.creation_date;
66 new_references.created_by := old_references.created_by;
67 ELSE
68 new_references.creation_date := x_creation_date;
69 new_references.created_by := x_created_by;
70 END IF;
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END Set_Column_Values;
76
77 PROCEDURE BeforeRowInsertUpdate1(
78 p_inserting IN BOOLEAN DEFAULT FALSE,
79 p_updating IN BOOLEAN DEFAULT FALSE,
80 p_deleting IN BOOLEAN DEFAULT FALSE
81 ) AS
82 v_message_name VARCHAR2(30);
83 v_return_type VARCHAR2(1);
84 v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
85 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
86 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
87 v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
88 v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
89 v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
90 v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
91 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
92 v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
93 v_crv_version_number IGS_PS_VER.version_number%TYPE;
94 v_pref_limit NUMBER;
95 v_check_course_encumb_ind VARCHAR2(1);
96 v_late_appl_allowed_ind VARCHAR2(1);
97 v_req_reconsider_allowed_ind VARCHAR2(1);
98 v_req_adv_standing_allowed_ind VARCHAR2(1);
99
100
101 CURSOR c_apcs (
102 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
103 cp_s_admission_process_type
104 IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
105 SELECT s_admission_step_type,
106 step_type_restriction_num
107 FROM IGS_AD_PRCS_CAT_STEP
108 WHERE admission_cat = cp_admission_cat AND
109 s_admission_process_type = cp_s_admission_process_type AND
110 step_group_type <> 'TRACK'; --2402377
111 cst_error CONSTANT VARCHAR2(1):= 'E';
112 BEGIN
113
114 v_check_course_encumb_ind := 'N';
115 v_late_appl_allowed_ind := 'N';
116 v_req_reconsider_allowed_ind := 'N';
117 v_req_adv_standing_allowed_ind := 'N';
118
119 --
120 -- Get admission application details required for validation
121 --
122 IGS_AD_GEN_002.ADMP_GET_AA_DTL(
123 new_references.person_id,
124 new_references.admission_appl_number,
125 v_admission_cat,
126 v_s_admission_process_type,
127 v_acad_cal_type,
128 v_acad_ci_sequence_number,
129 v_adm_cal_type,
130 v_adm_ci_sequence_number,
131 v_appl_dt,
132 v_adm_appl_status,
133 v_adm_fee_status);
134 --
135 -- Determine the admission process category steps.
136 --
137 FOR v_apcs_rec IN c_apcs (
138 v_admission_cat,
139 v_s_admission_process_type)
140 LOOP
141 IF v_apcs_rec.s_admission_step_type = 'PREF-LIMIT' THEN
142 v_pref_limit := v_apcs_rec.step_type_restriction_num;
143 ELSIF v_apcs_rec.s_admission_step_type = 'CHKCENCUMB' THEN
144 v_check_course_encumb_ind := 'Y';
145 ELSIF v_apcs_rec.s_admission_step_type = 'LATE-APP' THEN
146 v_late_appl_allowed_ind := 'Y';
147 ELSIF v_apcs_rec.s_admission_step_type = 'RECONSIDER' THEN
148 v_req_reconsider_allowed_ind := 'Y';
149 ELSIF v_apcs_rec.s_admission_step_type = 'ADVSTAND' THEN
150 v_req_adv_standing_allowed_ind := 'Y';
151 END IF;
152 END LOOP;
153 IF p_inserting THEN
154 --
155 -- Validate preference limit.
156 --
157 IF IGS_AD_VAL_ACA.admp_val_pref_limit (
158 new_references.person_id,
159 new_references.admission_appl_number,
160 new_references.nominated_course_cd,
161 -1, -- ACAI sequence number, not known yet.
162 v_s_admission_process_type,
163 v_pref_limit,
164 v_message_name) = FALSE THEN
165 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
166 FND_MESSAGE.SET_NAME('IGS',v_message_name);
167 IGS_GE_MSG_STACK.ADD;
168 APP_EXCEPTION.RAISE_EXCEPTION;
169 END IF;
170 -- Validate the nominated IGS_PS_COURSE code.
171 IF IGS_AD_VAL_ACAI.admp_val_acai_course (
172 new_references.nominated_course_cd,
173 NULL,
174 v_admission_cat,
175 v_s_admission_process_type,
176 v_acad_cal_type,
177 v_acad_ci_sequence_number,
178 v_adm_cal_type,
179 v_adm_ci_sequence_number,
180 v_appl_dt,
181 v_late_appl_allowed_ind,
182 'N',
183 v_crv_version_number,
184 v_message_name,
185 v_return_type) = FALSE THEN
186 IF NVL(v_return_type, '-1') = cst_error THEN
187 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
188 FND_MESSAGE.SET_NAME('IGS',v_message_name);
189 IGS_GE_MSG_STACK.ADD;
190 APP_EXCEPTION.RAISE_EXCEPTION;
191 END IF;
192 END IF;
193 --
194 -- Validate IGS_PS_COURSE encumbrances.
195 --
196 IF v_check_course_encumb_ind = 'Y' THEN
197 IF IGS_AD_VAL_ACAI.admp_val_acai_encmb (
198 new_references.person_id,
199 new_references.nominated_course_cd,
200 v_adm_cal_type,
201 v_adm_ci_sequence_number,
202 v_check_course_encumb_ind,
203 'N', -- Offer indicator.
204 v_message_name,
205 v_return_type) = FALSE THEN
206 IF NVL(v_return_type, '-1') = cst_error THEN
207 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
208 FND_MESSAGE.SET_NAME('IGS',v_message_name);
209 IGS_GE_MSG_STACK.ADD;
210 APP_EXCEPTION.RAISE_EXCEPTION;
211 END IF;
212 END IF;
213 END IF;
214 --
215 -- Validate against current student IGS_PS_COURSE attempt.
216 --
217 IF IGS_AD_VAL_ACAI.admp_val_aca_sca (
218 new_references.person_id,
219 new_references.nominated_course_cd,
220 v_appl_dt,
221 v_admission_cat,
222 v_s_admission_process_type,
223 NULL, -- Fee Category.
224 NULL, -- Correspondence Category.
225 NULL, -- Enrolment Category.
226 'N', -- Offer indicator.
227 v_message_name,
228 v_return_type) = FALSE THEN
229 IF NVL(v_return_type, '-1') = cst_error THEN
230 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
231 FND_MESSAGE.SET_NAME('IGS',v_message_name);
232 IGS_GE_MSG_STACK.ADD;
233 APP_EXCEPTION.RAISE_EXCEPTION;
234 END IF;
235 END IF;
236 --
237 -- Validate transfer IGS_PS_COURSE code.
238 --
239 IF IGS_AD_VAL_ACA.admp_val_aca_trnsfr (
240 new_references.person_id,
241 new_references.nominated_course_cd,
242 v_crv_version_number,
243 new_references.transfer_course_cd,
244 v_s_admission_process_type,
245 v_check_course_encumb_ind,
246 v_adm_cal_type,
247 v_adm_ci_sequence_number,
248 v_message_name,
249 v_return_type) = FALSE THEN
250 IF NVL(v_return_type, '-1') = cst_error THEN
251 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
252 FND_MESSAGE.SET_NAME('IGS',v_message_name);
253 IGS_GE_MSG_STACK.ADD;
254 APP_EXCEPTION.RAISE_EXCEPTION;
255 END IF;
256 END IF;
257 END IF; -- p_inserting
258 IF p_updating THEN
259 --
260 -- Cannot update the Transfer IGS_PS_COURSE Code.
261 --
262 IF (NVL(old_references.transfer_course_cd, '-1') <>
263 NVL(new_references.transfer_course_cd, '-1')) THEN
264 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(3161));
265 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UPD_TRNSFRCD_NOT_ALLOW');
266 IGS_GE_MSG_STACK.ADD;
267 APP_EXCEPTION.RAISE_EXCEPTION;
268 END IF;
269 --
270 -- Save the admission application key.
271 -- For processing in the after statement trigger
272 -- to derive the admission application status.
273 -- Only save if the request for reconsideration indicator has changed.
274 --
275
276 END IF; -- p_updating
277 IF v_s_admission_process_type = 'TRANSFER' THEN
278 /* Include here validation for course transfer */
279 IF Igs_Ad_Val_Aca.admp_val_aca_trnsfr(
280 new_references.person_id,
281 new_references.nominated_course_cd,
282 v_crv_version_number,
283 new_references.transfer_course_cd,
284 v_s_admission_process_type,
285 'N',
286 v_adm_cal_type,
287 v_adm_ci_sequence_number,
288 v_message_name,
289 v_return_type) = FALSE THEN
290 FND_MESSAGE.SET_NAME('IGS',v_message_name);
291 IGS_GE_MSG_STACK.ADD;
292 APP_EXCEPTION.RAISE_EXCEPTION;
293 END IF;
294 END IF;
295
296 --
297 -- Validate basis for admission type closed indicator.
298 --
299 IF (new_references.basis_for_admission_type IS NOT NULL AND
300 (NVL(old_references.basis_for_admission_type, '-1') <>
301 new_references.basis_for_admission_type)) THEN
302 IF IGS_AD_VAL_ACA.admp_val_bfa_closed (
303 new_references.basis_for_admission_type,
304 v_message_name) = FALSE THEN
305 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
306 FND_MESSAGE.SET_NAME('IGS',v_message_name);
307 IGS_GE_MSG_STACK.ADD;
308 APP_EXCEPTION.RAISE_EXCEPTION;
309 END IF;
310 END IF;
311 --
312 -- Validate admission code closed indicator.
313 --
314 IF (new_references.admission_cd IS NOT NULL AND
315 (NVL(old_references.admission_cd, '-1') <> new_references.admission_cd)) THEN
316 IF IGS_AD_VAL_ACA.admp_val_aco_closed (
317 new_references.admission_cd,
318 v_message_name) = FALSE THEN
319 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
320 FND_MESSAGE.SET_NAME('IGS',v_message_name);
321 IGS_GE_MSG_STACK.ADD;
322 APP_EXCEPTION.RAISE_EXCEPTION;
323 END IF;
324 END IF;
325 --
326 -- Validate request for reconsideration indicator.
327 --
328 IF (old_references.req_for_reconsideration_ind <> new_references.req_for_reconsideration_ind) THEN
329 IF IGS_AD_VAL_ACA.admp_val_aca_req_rec (
330 new_references.req_for_reconsideration_ind,
331 v_req_reconsider_allowed_ind,
332 v_message_name) = FALSE THEN
333 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
334 FND_MESSAGE.SET_NAME('IGS',v_message_name);
335 IGS_GE_MSG_STACK.ADD;
336 APP_EXCEPTION.RAISE_EXCEPTION;
337 END IF;
338 END IF;
339 --
340 -- Validate request for advanced standing indicator.
341 --
342 IF (old_references.req_for_adv_standing_ind <> new_references.req_for_adv_standing_ind) THEN
343 IF IGS_AD_VAL_ACA.admp_val_aca_req_adv (
344 new_references.req_for_adv_standing_ind,
345 v_req_adv_standing_allowed_ind,
346 v_message_name) = FALSE THEN
347 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
348 FND_MESSAGE.SET_NAME('IGS',v_message_name);
349 IGS_GE_MSG_STACK.ADD;
350 APP_EXCEPTION.RAISE_EXCEPTION;
351 END IF;
352 END IF;
353
354
355
356 END BeforeRowInsertUpdate1;
357
358 -- Trigger description :-
359 -- "OSS_TST".trg_aca_ar_ud_hist
360 -- AFTER DELETE OR UPDATE
361 -- ON IGS_AD_PS_APPL
362 -- FOR EACH ROW
363
364 PROCEDURE AfterRowUpdateDelete2(
365 p_inserting IN BOOLEAN DEFAULT FALSE,
366 p_updating IN BOOLEAN DEFAULT FALSE,
367 p_deleting IN BOOLEAN DEFAULT FALSE
368 ) AS
369 v_message_name VARCHAR2(30);
370
371 v_person_id IGS_AD_APPL.person_id%TYPE;
372 v_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
373 v_derived_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
374 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
375
376 -- cursor to get the old admission application status from the
377 -- database by rrengara on 9-APR-2002 for bug no 2298840
378
379 CURSOR c_adm_appl_status (cp_person_id igs_ad_appl.person_id%TYPE,
380 cp_admission_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
381 SELECT adm_appl_status
382 FROM igs_ad_appl
383 WHERE person_id = cp_person_id
384 AND admission_appl_number= cp_admission_appl_number;
385
386 BEGIN
387 IF p_updating THEN
388 -- Create admission IGS_PS_COURSE application history record.
389 IGS_AD_GEN_011.ADMP_INS_ACA_HIST (
390 new_references.person_id,
391 new_references.admission_appl_number,
392 new_references.nominated_course_cd,
393 new_references.transfer_course_cd,
394 old_references.transfer_course_cd,
395 new_references.basis_for_admission_type,
396 old_references.basis_for_admission_type,
397 new_references.admission_cd,
398 old_references.admission_cd,
399 new_references.course_rank_set,
400 old_references.course_rank_set,
401 new_references.course_rank_schedule,
402 old_references.course_rank_schedule,
403 new_references.req_for_reconsideration_ind,
404 old_references.req_for_reconsideration_ind,
405 new_references.req_for_adv_standing_ind,
406 old_references.req_for_adv_standing_ind,
407 new_references.last_updated_by,
408 old_references.last_updated_by,
409 new_references.last_update_date,
410 old_references.last_update_date);
411
412 -- added to handle mutation
413 -- Get the saved Admission Application details.
414 v_person_id := old_references.person_id;
415 v_admission_appl_number := old_references.admission_appl_number;
416
417 -- Added the cursor to get the old admission appl status
418 -- by rrengara on 9-apr-2002 bug no : 2298840
419
420 OPEN c_adm_appl_status (
421 v_person_id,
422 v_admission_appl_number);
423 FETCH c_adm_appl_status INTO v_adm_appl_status;
424 CLOSE c_adm_appl_status;
425
426
427 -- Derive the Admission Application status.
428 v_derived_adm_appl_status := IGS_AD_GEN_002.ADMP_GET_AA_AAS (
429 v_person_id,
430 v_admission_appl_number,
431 v_adm_appl_status);
432 -- Update the admission application status.
433
434 IF v_derived_adm_appl_status IS NOT NULL AND v_derived_adm_appl_status <> v_adm_appl_status THEN
435 UPDATE
436 IGS_AD_APPL
437 SET
438 adm_appl_status = v_derived_adm_appl_status
439 WHERE person_id = v_person_id AND
440 admission_appl_number = v_admission_appl_number;
441 END IF;
442 END IF;
443
444
445 IF p_deleting THEN
446 -- Delete admission IGS_PS_COURSE application history records.
447 IF IGS_AD_GEN_001.ADMP_DEL_ACA_HIST (
448 old_references.person_id,
449 old_references.admission_appl_number,
450 old_references.nominated_course_cd,
451 v_message_name) = FALSE THEN
452 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
453 FND_MESSAGE.SET_NAME('IGS',v_message_name);
454 IGS_GE_MSG_STACK.ADD;
455 APP_EXCEPTION.RAISE_EXCEPTION;
456 END IF;
457 END IF;
458
459
460 END AfterRowUpdateDelete2;
461
462 -- Trigger description :-
463 -- "OSS_TST".trg_aca_as_u
464 -- AFTER UPDATE
465 -- ON IGS_AD_PS_APPL
466
467
468 PROCEDURE Check_Parent_Existance AS
469 BEGIN
470
471 IF (((old_references.person_id = new_references.person_id) AND
472 (old_references.admission_appl_number = new_references.admission_appl_number)) OR
473 ((new_references.person_id IS NULL) OR
474 (new_references.admission_appl_number IS NULL))) THEN
475 NULL;
476 ELSE
477 IF NOT IGS_AD_APPL_PKG.Get_PK_For_Validation (
478 new_references.person_id,
479 new_references.admission_appl_number
480 ) THEN
481 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
482 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL'));
483 IGS_GE_MSG_STACK.ADD;
484 APP_EXCEPTION.RAISE_EXCEPTION;
485 END IF;
486 END IF;
487
488 IF (((old_references.admission_cd = new_references.admission_cd)) OR
489 ((new_references.admission_cd IS NULL))) THEN
490 NULL;
491 ELSE
492 IF NOT IGS_AD_CD_PKG.Get_PK_For_Validation (
493 new_references.admission_cd , 'N'
494 )THEN
495 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
496 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_CD'));
497 IGS_GE_MSG_STACK.ADD;
498 APP_EXCEPTION.RAISE_EXCEPTION;
499 END IF;
500 END IF;
501
502 IF (((old_references.basis_for_admission_type = new_references.basis_for_admission_type)) OR
503 ((new_references.basis_for_admission_type IS NULL))) THEN
504 NULL;
505 ELSE
506 IF NOT IGS_AD_BASIS_FOR_AD_PKG.Get_PK_For_Validation (
507 new_references.basis_for_admission_type , 'N'
508 )THEN
509 FND_MESSAGE.SET_NAME('IGS','IGS_AD_BASIS_ADM_TYPE_CLOSED');
510 IGS_GE_MSG_STACK.ADD;
511 APP_EXCEPTION.RAISE_EXCEPTION;
512 END IF;
513 END IF;
514
515 -- Removed the GET_PK call for IGS_PS_COURSE_PKG
516 -- Nominated course code and sequence number will be validated
517 -- at IGS_AD_PS_APPL_INST level
518 -- Bug no 2380815 by rrengara on 8-JAN-2003
519
520
521 IF (((old_references.person_id = new_references.person_id) AND
522 (old_references.transfer_course_cd = new_references.transfer_course_cd)) OR
523 ((new_references.person_id IS NULL) OR
524 (new_references.transfer_course_cd IS NULL))) THEN
525 NULL;
526 ELSE
527 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
528 new_references.person_id,
529 new_references.transfer_course_cd
530 )THEN
531 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
532 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON_TRANSFER_CD'));
533 IGS_GE_MSG_STACK.ADD;
534 APP_EXCEPTION.RAISE_EXCEPTION;
535 END IF;
536 END IF;
537
538 END Check_Parent_Existance;
539
540 PROCEDURE Check_Child_Existance AS
541 BEGIN
542
543 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_AD_PS_APPL (
544 old_references.person_id,
545 old_references.admission_appl_number,
546 old_references.nominated_course_cd
547 );
548
549 END Check_Child_Existance;
550
551 FUNCTION Get_PK_For_Validation (
552 x_person_id IN NUMBER,
553 x_admission_appl_number IN NUMBER,
554 x_nominated_course_cd IN VARCHAR2
555 )
556 RETURN BOOLEAN AS
557
558 CURSOR cur_rowid IS
559 SELECT rowid
560 FROM IGS_AD_PS_APPL_ALL
561 WHERE person_id = x_person_id
562 AND admission_appl_number = x_admission_appl_number
563 AND nominated_course_cd = x_nominated_course_cd
564 FOR UPDATE NOWAIT;
565
566 lv_rowid cur_rowid%RowType;
567
568 BEGIN
569
570 Open cur_rowid;
571 Fetch cur_rowid INTO lv_rowid;
572 IF (cur_rowid%FOUND) THEN
573 Close cur_rowid;
574 Return TRUE;
575 ELSE
576 Close cur_rowid;
577 Return FALSE;
578 END IF;
579
580
581 END Get_PK_For_Validation;
582
583 PROCEDURE GET_FK_IGS_AD_APPL (
584 x_person_id IN NUMBER,
585 x_admission_appl_number IN NUMBER
586 ) AS
587
588 CURSOR cur_rowid IS
589 SELECT rowid
590 FROM IGS_AD_PS_APPL_ALL
591 WHERE person_id = x_person_id
592 AND admission_appl_number = x_admission_appl_number ;
593
594 lv_rowid cur_rowid%RowType;
595
596 BEGIN
597
598 Open cur_rowid;
599 Fetch cur_rowid INTO lv_rowid;
600 IF (cur_rowid%FOUND) THEN
601 Close cur_rowid;
602 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACA_AA_FK');
603 IGS_GE_MSG_STACK.ADD;
604 App_Exception.Raise_Exception;
605 Return;
606 END IF;
607 Close cur_rowid;
608
609 END GET_FK_IGS_AD_APPL;
610
611 PROCEDURE GET_FK_IGS_AD_CD (
612 x_admission_cd IN VARCHAR2
613 ) AS
614
615 CURSOR cur_rowid IS
616 SELECT rowid
617 FROM IGS_AD_PS_APPL_ALL
618 WHERE admission_cd = x_admission_cd ;
619
620 lv_rowid cur_rowid%RowType;
621
622 BEGIN
623
624 Open cur_rowid;
625 Fetch cur_rowid INTO lv_rowid;
626 IF (cur_rowid%FOUND) THEN
627 Close cur_rowid;
628 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACA_ACO_FK');
629 IGS_GE_MSG_STACK.ADD;
630 App_Exception.Raise_Exception;
631 Return;
632 END IF;
633 Close cur_rowid;
634
635 END GET_FK_IGS_AD_CD;
636
637 PROCEDURE GET_FK_IGS_AD_BASIS_FOR_AD (
638 x_basis_for_admission_type IN VARCHAR2
639 ) AS
640
641 CURSOR cur_rowid IS
642 SELECT rowid
643 FROM IGS_AD_PS_APPL_ALL
644 WHERE basis_for_admission_type = x_basis_for_admission_type ;
645
646 lv_rowid cur_rowid%RowType;
647
648 BEGIN
649
650 Open cur_rowid;
651 Fetch cur_rowid INTO lv_rowid;
652 IF (cur_rowid%FOUND) THEN
653 Close cur_rowid;
654 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACA_BFA_FK');
655 IGS_GE_MSG_STACK.ADD;
656 App_Exception.Raise_Exception;
657 Return;
658 END IF;
659 Close cur_rowid;
660
661 END GET_FK_IGS_AD_BASIS_FOR_AD;
662
663 PROCEDURE GET_FK_IGS_PS_COURSE (
664 x_course_cd IN VARCHAR2
665 ) AS
666
667 CURSOR cur_rowid IS
668 SELECT rowid
669 FROM IGS_AD_PS_APPL_ALL
670 WHERE nominated_course_cd = x_course_cd ;
671
672 lv_rowid cur_rowid%RowType;
673
674 BEGIN
675
676 Open cur_rowid;
677 Fetch cur_rowid INTO lv_rowid;
678 IF (cur_rowid%FOUND) THEN
679 Close cur_rowid;
680 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACA_CRS_FK');
681 IGS_GE_MSG_STACK.ADD;
682 App_Exception.Raise_Exception;
683 Return;
684 END IF;
685 Close cur_rowid;
686
687 END GET_FK_IGS_PS_COURSE;
688
689 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
690 x_person_id IN NUMBER,
691 x_course_cd IN VARCHAR2
692 ) AS
693
694 CURSOR cur_rowid IS
695 SELECT rowid
696 FROM IGS_AD_PS_APPL_ALL
697 WHERE person_id = x_person_id
698 AND transfer_course_cd = x_course_cd ;
699
700 lv_rowid cur_rowid%RowType;
701
702 BEGIN
703
704 Open cur_rowid;
705 Fetch cur_rowid INTO lv_rowid;
706 IF (cur_rowid%FOUND) THEN
707 Close cur_rowid;
708 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACA_SCA_FK');
709 IGS_GE_MSG_STACK.ADD;
710 App_Exception.Raise_Exception;
711 Return;
712 END IF;
713 Close cur_rowid;
714
715 END GET_FK_IGS_EN_STDNT_PS_ATT;
716
717 -- procedure to check constraints
718 PROCEDURE CHECK_CONSTRAINTS(
719 column_name IN VARCHAR2 DEFAULT NULL,
720 column_value IN VARCHAR2 DEFAULT NULL
721 ) as
722 BEGIN
723 IF column_name is null THEN
724 NULL;
725 ELSIF upper(column_name) = 'ADMISSION_CD' THEN
726 new_references.admission_cd := column_value;
727 ELSIF upper(column_name) = 'BASIS_FOR_ADMISSION_TYPE' THEN
728 new_references.basis_for_admission_type := column_value;
729 ELSIF upper(column_name) = 'COURSE_RANK_SCHEDULE' THEN
730 new_references.course_rank_schedule := column_value;
731 ELSIF upper(column_name) = 'COURSE_RANK_SET' THEN
732 new_references.course_rank_set := column_value;
733 ELSIF upper(column_name) = 'NOMINATED_COURSE_CD' THEN
734 new_references.nominated_course_cd := column_value;
735 ELSIF upper(column_name) = 'REQ_FOR_ADV_STANDING_IND' THEN
736 new_references.req_for_adv_standing_ind := column_value;
737 ELSIF upper(column_name) = 'REQ_FOR_RECONSIDERATION_IND' THEN
738 new_references.req_for_reconsideration_ind := column_value;
739 ELSIF upper(column_name) = 'TRANSFER_COURSE_CD' THEN
740 new_references.transfer_course_cd := column_value;
741 END IF;
742
743 IF upper(column_name) = 'COURSE_RANK_SCHEDULE' OR column_name IS NULL THEN
744 IF new_references.course_rank_schedule <> UPPER(new_references.course_rank_schedule) THEN
745 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
746 IGS_GE_MSG_STACK.ADD;
747 APP_EXCEPTION.RAISE_EXCEPTION;
748 END IF;
749 END IF;
750 IF upper(column_name) = 'COURSE_RANK_SET' OR column_name IS NULL THEN
751 IF new_references.course_rank_set <> UPPER(new_references.course_rank_set) THEN
752 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
753 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_COURSE_RANK_DTLS'));
754 IGS_GE_MSG_STACK.ADD;
755 APP_EXCEPTION.RAISE_EXCEPTION;
756 END IF;
757 END IF;
758 IF upper(column_name) = 'NOMINATED_COURSE_CD' OR column_name IS NULL THEN
759 IF new_references.nominated_course_cd <> UPPER(new_references.nominated_course_cd) THEN
760 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
761 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM'));
762 IGS_GE_MSG_STACK.ADD;
763 APP_EXCEPTION.RAISE_EXCEPTION;
764 END IF;
765 END IF;
766 IF upper(column_name) = 'REQ_FOR_ADV_STANDING_IND' OR column_name IS NULL THEN
767 IF new_references.req_for_adv_standing_ind <> UPPER(new_references.req_for_adv_standing_ind) THEN
768 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
769 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_REQ_ADV_STD_IND'));
770 IGS_GE_MSG_STACK.ADD;
771 APP_EXCEPTION.RAISE_EXCEPTION;
772 END IF;
773 END IF;
774 IF upper(column_name) = 'REQ_FOR_RECONSIDERATION_IND' OR column_name IS NULL THEN
775 IF new_references.req_for_reconsideration_ind <> UPPER(new_references.req_for_reconsideration_ind) THEN
776 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
777 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_REQ_RECONS_IND'));
778 IGS_GE_MSG_STACK.ADD;
779 APP_EXCEPTION.RAISE_EXCEPTION;
780 END IF;
781 END IF;
782 IF upper(column_name) = 'TRANSFER_COURSE_CD' OR column_name IS NULL THEN
783 IF new_references.transfer_course_cd <> UPPER(new_references.transfer_course_cd) THEN
784 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
785 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TRANSFER_CD'));
786 IGS_GE_MSG_STACK.ADD;
787 APP_EXCEPTION.RAISE_EXCEPTION;
788 END IF;
789 END IF;
790
791 END CHECK_CONSTRAINTS;
792
793 PROCEDURE Before_DML (
794 p_action IN VARCHAR2,
795 x_rowid IN VARCHAR2 DEFAULT NULL,
796 x_org_id IN NUMBER DEFAULT NULL,
797 x_person_id IN NUMBER DEFAULT NULL,
798 x_admission_appl_number IN NUMBER DEFAULT NULL,
799 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
800 x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
801 x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
802 x_admission_cd IN VARCHAR2 DEFAULT NULL,
803 x_course_rank_set IN VARCHAR2 DEFAULT NULL,
804 x_course_rank_schedule IN VARCHAR2 DEFAULT NULL,
805 x_req_for_reconsideration_ind IN VARCHAR2 DEFAULT NULL,
806 x_req_for_adv_standing_ind IN VARCHAR2 DEFAULT NULL,
807 x_creation_date IN DATE DEFAULT NULL,
808 x_created_by IN NUMBER DEFAULT NULL,
809 x_last_update_date IN DATE DEFAULT NULL,
810 x_last_updated_by IN NUMBER DEFAULT NULL,
811 x_last_update_login IN NUMBER DEFAULT NULL
812 ) AS
813 BEGIN
814
815 Set_Column_Values (
816 p_action,
817 x_rowid,
818 x_org_id,
819 x_person_id,
820 x_admission_appl_number,
821 x_nominated_course_cd,
822 x_transfer_course_cd,
823 x_basis_for_admission_type,
824 x_admission_cd,
825 x_course_rank_set,
826 x_course_rank_schedule,
827 x_req_for_reconsideration_ind,
828 x_req_for_adv_standing_ind,
829 x_creation_date,
830 x_created_by,
831 x_last_update_date,
832 x_last_updated_by,
833 x_last_update_login
834 );
835
836 IF (p_action = 'INSERT') THEN
837 -- Call all the procedures related to Before Insert.
838 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
839 IF GET_PK_FOR_VALIDATION(
840 new_references.person_id,
841 new_references.admission_appl_number,
842 new_references.nominated_course_cd
843 )THEN
844 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
845 IGS_GE_MSG_STACK.ADD;
846 APP_EXCEPTION.RAISE_EXCEPTION;
847 END IF;
848 Check_Constraints;
849 Check_Parent_Existance;
850 ELSIF (p_action = 'UPDATE') THEN
851 -- Call all the procedures related to Before Update.
852 BeforeRowInsertUpdate1 ( p_updating => TRUE );
853 Check_Constraints;
854 Check_Parent_Existance;
855
856 ELSIF (p_action = 'DELETE') THEN
857 -- Call all the procedures related to Before Delete.
858 Null;
859 Check_Child_Existance;
860 ELSIF ( p_action = 'VALIDATE_INSERT') THEN
861 IF GET_PK_FOR_VALIDATION(
862 new_references.person_id,
863 new_references.admission_appl_number,
864 new_references.nominated_course_cd
865 )THEN
866 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
867 IGS_GE_MSG_STACK.ADD;
868 APP_EXCEPTION.RAISE_EXCEPTION;
869 END IF;
870 Check_Constraints;
871 ELSIF ( p_action = 'VALIDATE_UPDATE') THEN
872 Check_Constraints;
873 ELSIF ( p_action = 'VALIDATE_DELETE') THEN
874 Check_Child_Existance;
875 END IF;
876
877 END Before_DML;
878
879 PROCEDURE After_DML (
880 p_action IN VARCHAR2,
881 x_rowid IN VARCHAR2
882 ) AS
883 BEGIN
884
885 l_rowid := x_rowid;
886
887 IF (p_action = 'UPDATE') THEN
888 -- Call all the procedures related to After Update.
889 AfterRowUpdateDelete2 ( p_updating => TRUE );
890 ELSIF (p_action = 'DELETE') THEN
891 -- Call all the procedures related to After Delete.
892 AfterRowUpdateDelete2 ( p_deleting => TRUE );
893 END IF;
894
895 END After_DML;
896
897 procedure INSERT_ROW (
898 X_ROWID in out NOCOPY VARCHAR2,
899 X_ORG_ID in NUMBER,
900 X_PERSON_ID in NUMBER,
901 X_ADMISSION_APPL_NUMBER in NUMBER,
902 X_NOMINATED_COURSE_CD in VARCHAR2,
903 X_TRANSFER_COURSE_CD in VARCHAR2,
904 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
905 X_ADMISSION_CD in VARCHAR2,
906 X_COURSE_RANK_SET in VARCHAR2,
907 X_COURSE_RANK_SCHEDULE in VARCHAR2,
908 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
909 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2,
910 X_MODE in VARCHAR2
911 ) as
912 cursor C is select ROWID from IGS_AD_PS_APPL_ALL
913 where PERSON_ID = X_PERSON_ID
914 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
915 and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD;
916 X_LAST_UPDATE_DATE DATE;
917 X_LAST_UPDATED_BY NUMBER;
918 X_LAST_UPDATE_LOGIN NUMBER;
919 X_REQUEST_ID NUMBER;
920 X_PROGRAM_ID NUMBER;
921 X_PROGRAM_APPLICATION_ID NUMBER;
922 X_PROGRAM_UPDATE_DATE DATE;
923 begin
924 X_LAST_UPDATE_DATE := SYSDATE;
925 if(X_MODE = 'I') then
926 X_LAST_UPDATED_BY := 1;
927 X_LAST_UPDATE_LOGIN := 0;
928 elsif (X_MODE IN ('R', 'S')) then
929 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
930 if X_LAST_UPDATED_BY is NULL then
931 X_LAST_UPDATED_BY := -1;
932 end if;
933 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
934 if X_LAST_UPDATE_LOGIN is NULL then
935 X_LAST_UPDATE_LOGIN := -1;
936 end if;
937 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
938 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
939 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
940 if (X_REQUEST_ID = -1) then
941 X_REQUEST_ID := NULL;
942 X_PROGRAM_ID := NULL;
943 X_PROGRAM_APPLICATION_ID := NULL;
944 X_PROGRAM_UPDATE_DATE := NULL;
945 else
946 X_PROGRAM_UPDATE_DATE := SYSDATE;
947 end if;
948 else
949 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
950 IGS_GE_MSG_STACK.ADD;
951 app_exception.raise_exception;
952 end if;
953
954
955 Before_DML(p_action =>'INSERT',
956 x_rowid =>X_ROWID,
957 x_org_id => igs_ge_gen_003.get_org_id,
958 x_person_id => X_PERSON_ID,
959 x_admission_appl_number => X_ADMISSION_APPL_NUMBER,
960 x_nominated_course_cd => X_NOMINATED_COURSE_CD,
961 x_transfer_course_cd => X_TRANSFER_COURSE_CD,
962 x_basis_for_admission_type => X_BASIS_FOR_ADMISSION_TYPE,
963 x_admission_cd => X_ADMISSION_CD,
964 x_course_rank_set => X_COURSE_RANK_SET,
965 x_course_rank_schedule => X_COURSE_RANK_SCHEDULE,
966 x_req_for_reconsideration_ind => NVL(X_REQ_FOR_RECONSIDERATION_IND,'N'),
967 x_req_for_adv_standing_ind => NVL(X_REQ_FOR_ADV_STANDING_IND,'N'),
968 x_creation_date =>X_LAST_UPDATE_DATE,
969 x_created_by =>X_LAST_UPDATED_BY,
970 x_last_update_date =>X_LAST_UPDATE_DATE,
971 x_last_updated_by =>X_LAST_UPDATED_BY,
972 x_last_update_login =>X_LAST_UPDATE_LOGIN
973 );
974
975 IF (x_mode = 'S') THEN
976 igs_sc_gen_001.set_ctx('R');
977 END IF;
978 insert into IGS_AD_PS_APPL_ALL (
979 ORG_ID,
980 PERSON_ID,
981 ADMISSION_APPL_NUMBER,
982 NOMINATED_COURSE_CD,
983 TRANSFER_COURSE_CD,
984 BASIS_FOR_ADMISSION_TYPE,
985 ADMISSION_CD,
986 COURSE_RANK_SET,
987 COURSE_RANK_SCHEDULE,
988 REQ_FOR_RECONSIDERATION_IND,
989 REQ_FOR_ADV_STANDING_IND,
990 CREATION_DATE,
991 CREATED_BY,
992 LAST_UPDATE_DATE,
993 LAST_UPDATED_BY,
994 LAST_UPDATE_LOGIN,
995 REQUEST_ID,
996 PROGRAM_ID,
997 PROGRAM_APPLICATION_ID,
998 PROGRAM_UPDATE_DATE
999 ) values (
1000 NEW_REFERENCES.ORG_ID,
1001 NEW_REFERENCES.PERSON_ID,
1002 NEW_REFERENCES.ADMISSION_APPL_NUMBER,
1003 NEW_REFERENCES.NOMINATED_COURSE_CD,
1004 NEW_REFERENCES.TRANSFER_COURSE_CD,
1005 NEW_REFERENCES.BASIS_FOR_ADMISSION_TYPE,
1006 NEW_REFERENCES.ADMISSION_CD,
1007 NEW_REFERENCES.COURSE_RANK_SET,
1008 NEW_REFERENCES.COURSE_RANK_SCHEDULE,
1009 NEW_REFERENCES.REQ_FOR_RECONSIDERATION_IND,
1010 NEW_REFERENCES.REQ_FOR_ADV_STANDING_IND,
1011 X_LAST_UPDATE_DATE,
1012 X_LAST_UPDATED_BY,
1013 X_LAST_UPDATE_DATE,
1014 X_LAST_UPDATED_BY,
1015 X_LAST_UPDATE_LOGIN,
1016 X_REQUEST_ID,
1017 X_PROGRAM_ID,
1018 X_PROGRAM_APPLICATION_ID,
1019 X_PROGRAM_UPDATE_DATE
1020 );
1021 IF (x_mode = 'S') THEN
1022 igs_sc_gen_001.unset_ctx('R');
1023 END IF;
1024
1025
1026 open c;
1027 fetch c into X_ROWID;
1028 if (c%notfound) then
1029 close c;
1030 raise no_data_found;
1031 end if;
1032 close c;
1033
1034 After_DML(
1035 p_action =>'INSERT',
1036 x_rowid => X_ROWID
1037 );
1038
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 IF (x_mode = 'S') THEN
1042 igs_sc_gen_001.unset_ctx('R');
1043 END IF;
1044 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1045 -- Code to handle Security Policy error raised
1046 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1047 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1048 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1049 -- that the ownerof policy function does not have privilege to access.
1050 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1051 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1052 IGS_GE_MSG_STACK.ADD;
1053 app_exception.raise_exception;
1054 ELSE
1055 RAISE;
1056 END IF;
1057 end INSERT_ROW;
1058
1059 procedure LOCK_ROW (
1060 X_ROWID in VARCHAR2,
1061 X_PERSON_ID in NUMBER,
1062 X_ADMISSION_APPL_NUMBER in NUMBER,
1063 X_NOMINATED_COURSE_CD in VARCHAR2,
1064 X_TRANSFER_COURSE_CD in VARCHAR2,
1065 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
1066 X_ADMISSION_CD in VARCHAR2,
1067 X_COURSE_RANK_SET in VARCHAR2,
1068 X_COURSE_RANK_SCHEDULE in VARCHAR2,
1069 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
1070 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2
1071 ) as
1072 cursor c1 is select
1073 TRANSFER_COURSE_CD,
1074 BASIS_FOR_ADMISSION_TYPE,
1075 ADMISSION_CD,
1076 COURSE_RANK_SET,
1077 COURSE_RANK_SCHEDULE,
1078 REQ_FOR_RECONSIDERATION_IND,
1079 REQ_FOR_ADV_STANDING_IND
1080 from IGS_AD_PS_APPL_ALL
1081 where ROWID = X_ROWID
1082 for update nowait;
1083 tlinfo c1%rowtype;
1084
1085 begin
1086 open c1;
1087 fetch c1 into tlinfo;
1088 if (c1%notfound) then
1089 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1090 IGS_GE_MSG_STACK.ADD;
1091 app_exception.raise_exception;
1092 close c1;
1093 return;
1094 end if;
1095 close c1;
1096
1097 if ( ((tlinfo.TRANSFER_COURSE_CD = X_TRANSFER_COURSE_CD)
1098 OR ((tlinfo.TRANSFER_COURSE_CD is null)
1099 AND (X_TRANSFER_COURSE_CD is null)))
1100 AND ((tlinfo.BASIS_FOR_ADMISSION_TYPE = X_BASIS_FOR_ADMISSION_TYPE)
1101 OR ((tlinfo.BASIS_FOR_ADMISSION_TYPE is null)
1102 AND (X_BASIS_FOR_ADMISSION_TYPE is null)))
1103 AND ((tlinfo.ADMISSION_CD = X_ADMISSION_CD)
1104 OR ((tlinfo.ADMISSION_CD is null)
1105 AND (X_ADMISSION_CD is null)))
1106 AND ((tlinfo.COURSE_RANK_SET = X_COURSE_RANK_SET)
1107 OR ((tlinfo.COURSE_RANK_SET is null)
1108 AND (X_COURSE_RANK_SET is null)))
1109 AND ((tlinfo.COURSE_RANK_SCHEDULE = X_COURSE_RANK_SCHEDULE)
1110 OR ((tlinfo.COURSE_RANK_SCHEDULE is null)
1111 AND (X_COURSE_RANK_SCHEDULE is null)))
1112 AND (tlinfo.REQ_FOR_RECONSIDERATION_IND = X_REQ_FOR_RECONSIDERATION_IND)
1113 AND (tlinfo.REQ_FOR_ADV_STANDING_IND = X_REQ_FOR_ADV_STANDING_IND)
1114 ) then
1115 null;
1116 else
1117 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1118 IGS_GE_MSG_STACK.ADD;
1119 app_exception.raise_exception;
1120 end if;
1121 return;
1122 end LOCK_ROW;
1123
1124 procedure UPDATE_ROW (
1125 X_ROWID in VARCHAR2,
1126 X_PERSON_ID in NUMBER,
1127 X_ADMISSION_APPL_NUMBER in NUMBER,
1128 X_NOMINATED_COURSE_CD in VARCHAR2,
1129 X_TRANSFER_COURSE_CD in VARCHAR2,
1130 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
1131 X_ADMISSION_CD in VARCHAR2,
1132 X_COURSE_RANK_SET in VARCHAR2,
1133 X_COURSE_RANK_SCHEDULE in VARCHAR2,
1134 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
1135 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2,
1136 X_MODE in VARCHAR2
1137 ) as
1138 X_LAST_UPDATE_DATE DATE;
1139 X_LAST_UPDATED_BY NUMBER;
1140 X_LAST_UPDATE_LOGIN NUMBER;
1141 X_REQUEST_ID NUMBER;
1142 X_PROGRAM_ID NUMBER;
1143 X_PROGRAM_APPLICATION_ID NUMBER;
1144 X_PROGRAM_UPDATE_DATE DATE;
1145 begin
1146 X_LAST_UPDATE_DATE := SYSDATE;
1147 if(X_MODE = 'I') then
1148 X_LAST_UPDATED_BY := 1;
1149 X_LAST_UPDATE_LOGIN := 0;
1150 elsif (X_MODE IN ('R', 'S')) then
1151 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1152 if X_LAST_UPDATED_BY is NULL then
1153 X_LAST_UPDATED_BY := -1;
1154 end if;
1155 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1156 if X_LAST_UPDATE_LOGIN is NULL then
1157 X_LAST_UPDATE_LOGIN := -1;
1158 end if;
1159 else
1160 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1161 IGS_GE_MSG_STACK.ADD;
1162 app_exception.raise_exception;
1163 end if;
1164
1165 Before_DML(p_action =>'UPDATE',
1166 x_rowid =>X_ROWID,
1167 x_person_id => X_PERSON_ID,
1168 x_admission_appl_number => X_ADMISSION_APPL_NUMBER,
1169 x_nominated_course_cd => X_NOMINATED_COURSE_CD,
1170 x_transfer_course_cd => X_TRANSFER_COURSE_CD,
1171 x_basis_for_admission_type => X_BASIS_FOR_ADMISSION_TYPE,
1172 x_admission_cd => X_ADMISSION_CD,
1173 x_course_rank_set => X_COURSE_RANK_SET,
1174 x_course_rank_schedule => X_COURSE_RANK_SCHEDULE,
1175 x_req_for_reconsideration_ind => X_REQ_FOR_RECONSIDERATION_IND,
1176 x_req_for_adv_standing_ind => X_REQ_FOR_ADV_STANDING_IND,
1177 x_creation_date =>X_LAST_UPDATE_DATE,
1178 x_created_by =>X_LAST_UPDATED_BY,
1179 x_last_update_date =>X_LAST_UPDATE_DATE,
1180 x_last_updated_by =>X_LAST_UPDATED_BY,
1181 x_last_update_login =>X_LAST_UPDATE_LOGIN
1182 );
1183
1184
1185 if (X_MODE IN ('R', 'S')) then
1186 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1187 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1188 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1189 if (X_REQUEST_ID = -1) then
1190 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1191 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
1192 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1193 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1194 else
1195 X_PROGRAM_UPDATE_DATE := SYSDATE;
1196 end if;
1197 end if;
1198 IF (x_mode = 'S') THEN
1199 igs_sc_gen_001.set_ctx('R');
1200 END IF;
1201 update IGS_AD_PS_APPL_ALL set
1202 TRANSFER_COURSE_CD = NEW_REFERENCES.TRANSFER_COURSE_CD,
1203 BASIS_FOR_ADMISSION_TYPE = NEW_REFERENCES.BASIS_FOR_ADMISSION_TYPE,
1204 ADMISSION_CD = NEW_REFERENCES.ADMISSION_CD,
1205 COURSE_RANK_SET = NEW_REFERENCES.COURSE_RANK_SET,
1206 COURSE_RANK_SCHEDULE = NEW_REFERENCES.COURSE_RANK_SCHEDULE,
1207 REQ_FOR_RECONSIDERATION_IND = NEW_REFERENCES.REQ_FOR_RECONSIDERATION_IND,
1208 REQ_FOR_ADV_STANDING_IND = NEW_REFERENCES.REQ_FOR_ADV_STANDING_IND,
1209 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1210 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1211 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1212 REQUEST_ID = X_REQUEST_ID,
1213 PROGRAM_ID = X_PROGRAM_ID,
1214 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1215 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1216 where ROWID = X_ROWID
1217 ;
1218 if (sql%notfound) then
1219 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1220 igs_ge_msg_stack.add;
1221 IF (x_mode = 'S') THEN
1222 igs_sc_gen_001.unset_ctx('R');
1223 END IF;
1224 app_exception.raise_exception;
1225 end if;
1226 IF (x_mode = 'S') THEN
1227 igs_sc_gen_001.unset_ctx('R');
1228 END IF;
1229
1230
1231 After_DML(
1232 p_action =>'UPDATE',
1233 x_rowid => X_ROWID
1234 );
1235
1236 EXCEPTION
1237 WHEN OTHERS THEN
1238 IF (x_mode = 'S') THEN
1239 igs_sc_gen_001.unset_ctx('R');
1240 END IF;
1241 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1242 -- Code to handle Security Policy error raised
1243 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1244 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1245 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1246 -- that the ownerof policy function does not have privilege to access.
1247 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1248 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1249 IGS_GE_MSG_STACK.ADD;
1250 app_exception.raise_exception;
1251 ELSE
1252 RAISE;
1253 END IF;
1254 end UPDATE_ROW;
1255
1256 procedure ADD_ROW (
1257 X_ROWID in out NOCOPY VARCHAR2,
1258 X_ORG_ID in NUMBER,
1259 X_PERSON_ID in NUMBER,
1260 X_ADMISSION_APPL_NUMBER in NUMBER,
1261 X_NOMINATED_COURSE_CD in VARCHAR2,
1262 X_TRANSFER_COURSE_CD in VARCHAR2,
1263 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
1264 X_ADMISSION_CD in VARCHAR2,
1265 X_COURSE_RANK_SET in VARCHAR2,
1266 X_COURSE_RANK_SCHEDULE in VARCHAR2,
1267 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
1268 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2,
1269 X_MODE in VARCHAR2
1270 ) as
1271 cursor c1 is select rowid from IGS_AD_PS_APPL_ALL
1272 where PERSON_ID = X_PERSON_ID
1273 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
1274 and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
1275 ;
1276 begin
1277 open c1;
1278 fetch c1 into X_ROWID;
1279 if (c1%notfound) then
1280 close c1;
1281 INSERT_ROW (
1282 X_ROWID,
1283 X_ORG_ID,
1284 X_PERSON_ID,
1285 X_ADMISSION_APPL_NUMBER,
1286 X_NOMINATED_COURSE_CD,
1287 X_TRANSFER_COURSE_CD,
1288 X_BASIS_FOR_ADMISSION_TYPE,
1289 X_ADMISSION_CD,
1290 X_COURSE_RANK_SET,
1291 X_COURSE_RANK_SCHEDULE,
1292 X_REQ_FOR_RECONSIDERATION_IND,
1293 X_REQ_FOR_ADV_STANDING_IND,
1294 X_MODE);
1295 return;
1296 end if;
1297 close c1;
1298 UPDATE_ROW (
1299 X_ROWID,
1300 X_PERSON_ID,
1301 X_ADMISSION_APPL_NUMBER,
1302 X_NOMINATED_COURSE_CD,
1303 X_TRANSFER_COURSE_CD,
1304 X_BASIS_FOR_ADMISSION_TYPE,
1305 X_ADMISSION_CD,
1306 X_COURSE_RANK_SET,
1307 X_COURSE_RANK_SCHEDULE,
1308 X_REQ_FOR_RECONSIDERATION_IND,
1309 X_REQ_FOR_ADV_STANDING_IND,
1310 X_MODE);
1311 end ADD_ROW;
1312
1313 procedure DELETE_ROW (
1314 X_ROWID in VARCHAR2,
1315 x_mode IN VARCHAR2
1316 ) as
1317 begin
1318
1319 BEFORE_DML(
1320 p_action =>'DELETE',
1321 x_rowid => X_ROWID
1322 );
1323
1324 IF (x_mode = 'S') THEN
1325 igs_sc_gen_001.set_ctx('R');
1326 END IF;
1327 delete from IGS_AD_PS_APPL_ALL
1328 where ROWID = X_ROWID;
1329 if (sql%notfound) then
1330 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1331 igs_ge_msg_stack.add;
1332 IF (x_mode = 'S') THEN
1333 igs_sc_gen_001.unset_ctx('R');
1334 END IF;
1335 app_exception.raise_exception;
1336 end if;
1337 IF (x_mode = 'S') THEN
1338 igs_sc_gen_001.unset_ctx('R');
1339 END IF;
1340
1341
1342 After_DML(
1343 p_action =>'DELETE',
1344 x_rowid => X_ROWID
1345 );
1346
1347 EXCEPTION
1348 WHEN OTHERS THEN
1349 IF (x_mode = 'S') THEN
1350 igs_sc_gen_001.unset_ctx('R');
1351 END IF;
1352 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1353 -- Code to handle Security Policy error raised
1354 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1355 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1356 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1357 -- that the ownerof policy function does not have privilege to access.
1358 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1359 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1360 IGS_GE_MSG_STACK.ADD;
1361 app_exception.raise_exception;
1362 ELSE
1363 RAISE;
1364 END IF;
1365 end DELETE_ROW;
1366
1367 end IGS_AD_PS_APPL_PKG;