[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_APPL_PKG
Source
1 PACKAGE BODY IGS_AD_APPL_PKG as
2 /* $Header: IGSAI04B.pls 120.6 2005/09/30 05:55:10 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_APPL_ALL%RowType;
5 new_references IGS_AD_APPL_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2,
10 x_org_id IN NUMBER,
11 x_person_id IN NUMBER,
12 x_admission_appl_number IN NUMBER,
13 x_appl_dt IN DATE,
14 x_acad_cal_type IN VARCHAR2,
15 x_acad_ci_sequence_number IN NUMBER,
16 x_adm_cal_type IN VARCHAR2,
17 x_adm_ci_sequence_number IN NUMBER,
18 x_admission_cat IN VARCHAR2,
19 x_s_admission_process_type IN VARCHAR2,
20 x_adm_appl_status IN VARCHAR2,
21 x_adm_fee_status IN VARCHAR2,
22 x_tac_appl_ind IN VARCHAR2,
23 x_spcl_grp_1 IN NUMBER,
24 x_spcl_grp_2 IN NUMBER,
25 x_common_app IN VARCHAR2,
26 x_application_type IN VARCHAR2,
27 x_creation_date IN DATE,
28 x_created_by IN NUMBER,
29 x_last_update_date IN DATE,
30 x_last_updated_by IN NUMBER,
31 x_last_update_login IN NUMBER ,
32 x_choice_number IN VARCHAR2,
33 x_routeb_pref IN VARCHAR2,
34 x_alt_appl_id IN VARCHAR2,
35 x_appl_fee_amt IN NUMBER
36 ) AS
37 ------------------------------------------------------------------
38 --Change History:
39 --Who When What
40 --smadathi 12-Feb-2002 Bug 2217104. Added columns choice_number,routeb_pref
41 -------------------------------------------------------------------
42 CURSOR cur_old_ref_values IS
43 SELECT *
44 FROM IGS_AD_APPL_ALL
45 WHERE rowid = x_rowid;
46
47 BEGIN
48
49 l_rowid := x_rowid;
50
51 -- Code for setting the Old and New Reference Values.
52 -- Populate Old Values.
53 Open cur_old_ref_values;
54 Fetch cur_old_ref_values INTO old_references;
55 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
56 Close cur_old_ref_values;
57 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
58 IGS_GE_MSG_STACK.ADD;
59 App_Exception.Raise_Exception;
60 Return;
61 END IF;
62 Close cur_old_ref_values;
63
64 -- Populate New Values.
65 new_references.person_id := x_person_id;
66 new_references.org_id := x_org_id;
67 new_references.admission_appl_number := x_admission_appl_number;
68 new_references.appl_dt := TRUNC(x_appl_dt);
69 new_references.acad_cal_type := x_acad_cal_type;
70 new_references.acad_ci_sequence_number := x_acad_ci_sequence_number;
71 new_references.adm_cal_type := x_adm_cal_type;
72 new_references.adm_ci_sequence_number := x_adm_ci_sequence_number;
73 new_references.admission_cat := x_admission_cat;
74 new_references.s_admission_process_type := x_s_admission_process_type;
75 new_references.adm_appl_status := x_adm_appl_status;
76 new_references.adm_fee_status := x_adm_fee_status;
77 new_references.tac_appl_ind := x_tac_appl_ind;
78 new_references.spcl_grp_1 := x_spcl_grp_1;
79 new_references.spcl_grp_2 := x_spcl_grp_2;
80 new_references.common_app := x_common_app;
81 new_references.application_type := x_application_type;
82 new_references.choice_number := x_choice_number;
83 new_references.routeb_pref := x_routeb_pref;
84 new_references.alt_appl_id := x_alt_appl_id;
85 new_references.appl_fee_amt := x_appl_fee_amt;
86
87 IF (p_action = 'UPDATE') THEN
88 new_references.creation_date := old_references.creation_date;
89 new_references.created_by := old_references.created_by;
90 ELSE
91 new_references.creation_date := x_creation_date;
92 new_references.created_by := x_created_by;
93 END IF;
94 new_references.last_update_date := x_last_update_date;
95 new_references.last_updated_by := x_last_updated_by;
96 new_references.last_update_login := x_last_update_login;
97
98 END Set_Column_Values;
99
100
101 PROCEDURE BeforeRowInsertUpdateDelete1(
102 p_inserting IN BOOLEAN,
103 p_updating IN BOOLEAN,
104 p_deleting IN BOOLEAN
105 ) AS
106 v_message_name VARCHAR2(30);
107 v_return_type VARCHAR2(1);
108 v_title_required_ind VARCHAR2(1);
109 v_birth_dt_required_ind VARCHAR2(1);
110 v_fees_required_ind VARCHAR2(1);
111 v_person_encmb_chk_ind VARCHAR2(1);
112 v_cond_offer_fee_allowed_ind VARCHAR2(1);
113 cst_error CONSTANT VARCHAR2(1) := 'E';
114 cst_warn CONSTANT VARCHAR2(1) := 'W';
115 l_birth_date igs_pe_person_base_v.birth_date%TYPE;
116
117 CURSOR c_apcs (
118 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
119 cp_s_admission_process_type
120 IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE)
121 IS
122 SELECT s_admission_step_type
123 FROM IGS_AD_PRCS_CAT_STEP
124 WHERE admission_cat = cp_admission_cat AND
125 s_admission_process_type = cp_s_admission_process_type AND
126 step_group_type <> 'TRACK' ;-- 2402377
127
128
129 CURSOR c_birth_date(p_person_id igs_pe_person_base_v.person_id%TYPE) IS
130 SELECT birth_date
131 FROM igs_pe_person_base_v
132 WHERE person_id =p_person_id ;
133
134 BEGIN
135 v_title_required_ind := 'Y';
136 v_birth_dt_required_ind := 'Y';
137 v_fees_required_ind := 'N';
138 v_person_encmb_chk_ind := 'N';
139 v_cond_offer_fee_allowed_ind := 'N';
140
141
142 IF p_inserting OR p_updating THEN
143 OPEN c_birth_date(new_references.person_id);
144 FETCH c_birth_date INTO l_birth_date;
145 CLOSE c_birth_date;
146 IF ((l_birth_date IS NOT NULL) AND (l_birth_date > new_references.appl_dt)) THEN
147 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DOB_ERROR');
148 FND_MESSAGE.SET_TOKEN ('NAME',fnd_message.get_string('IGS','IGS_AD_APPL_DT'));
149 IGS_GE_MSG_STACK.ADD;
150 APP_EXCEPTION.RAISE_EXCEPTION;
151 END IF;
152 --
153 -- Determine the admission process category steps.
154 --
155 FOR v_apcs_rec IN c_apcs (
156 new_references.admission_cat,
157 new_references.s_admission_process_type)
158 LOOP
159 IF v_apcs_rec.s_admission_step_type = 'UN-TITLE' THEN
160 v_title_required_ind := 'N';
161 ELSIF v_apcs_rec.s_admission_step_type = 'UN-DOB' THEN
162 v_birth_dt_required_ind := 'N';
163 ELSIF v_apcs_rec.s_admission_step_type = 'APP-FEE' THEN
164 v_fees_required_ind := 'Y';
165 ELSIF v_apcs_rec.s_admission_step_type = 'CHKPENCUMB' THEN
166 v_person_encmb_chk_ind := 'Y';
167 ELSIF v_apcs_rec.s_admission_step_type = 'FEE-COND' THEN
168 v_cond_offer_fee_allowed_ind := 'Y';
169 END IF;
170 END LOOP;
171 END IF; -- p_inserting or p_updating.
172 -- IGS_GE_NOTE: The following fields only need to be validated
173 -- on insert because they cannot be updated.
174 IF p_inserting THEN
175 --
176 -- Validate insert of the admission application record.
177 --
178 IF IGS_AD_VAL_AA.admp_val_aa_insert (
179 new_references.person_id,
180 new_references.adm_cal_type,
181 new_references.adm_ci_sequence_number,
182 new_references.s_admission_process_type,
183 v_person_encmb_chk_ind,
184 new_references.appl_dt,
185 v_title_required_ind,
186 v_birth_dt_required_ind,
187 v_message_name,
188 v_return_type) = FALSE THEN
189 IF NVL(v_return_type, '-1') = cst_error THEN
190 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
191 FND_MESSAGE.SET_NAME('IGS',v_message_name);
192 IGS_GE_MSG_STACK.ADD;
193 APP_EXCEPTION.RAISE_EXCEPTION;
194 END IF;
195 END IF;
196 --
197 -- Validate the Academic Calendar.
198 --
199 IF IGS_AD_VAL_AA.admp_val_aa_acad_cal (
200 new_references.acad_cal_type,
201 new_references.acad_ci_sequence_number,
202 v_message_name) = FALSE THEN
203 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
204 FND_MESSAGE.SET_NAME('IGS',v_message_name);
205 IF v_message_name = 'IGS_AD_ADM_CAL_INSTNOT_DEFINE' THEN
206 FND_MESSAGE.SET_TOKEN('CAL_TYPE',new_references.acad_cal_type);
207 END IF;
208 IGS_GE_MSG_STACK.ADD;
209 APP_EXCEPTION.RAISE_EXCEPTION;
210 END IF;
211 --
212 -- Validate the Admission Calendar.
213 --
214 IF IGS_AD_VAL_AA.admp_val_aa_adm_cal (
215 new_references.adm_cal_type,
216 new_references.adm_ci_sequence_number,
217 new_references.acad_cal_type,
218 new_references.acad_ci_sequence_number,
219 new_references.admission_cat,
220 new_references.s_admission_process_type,
221 v_message_name) = FALSE THEN
222 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
223 FND_MESSAGE.SET_NAME('IGS',v_message_name);
224 IGS_GE_MSG_STACK.ADD;
225 APP_EXCEPTION.RAISE_EXCEPTION;
226 END IF;
227 --
228 -- Validate the Admission Category.
229 --
230 IF IGS_AD_VAL_AA.admp_val_aa_adm_cat (
231 new_references.admission_cat,
232 v_message_name) = FALSE THEN
233 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
234 FND_MESSAGE.SET_NAME('IGS',v_message_name);
235 IGS_GE_MSG_STACK.ADD;
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 END IF;
238 END IF; -- p_inserting.
239 IF p_updating THEN
240 -- Validate update of the admission application record.
241 IF (TRUNC(old_references.appl_dt) <> new_references.appl_dt OR
242 old_references.adm_fee_status <> new_references.adm_fee_status OR
243 old_references.tac_appl_ind <> new_references.tac_appl_ind) THEN
244 IF IGS_AD_VAL_AA.admp_val_aa_update (
245 old_references.adm_appl_status,
246 v_message_name) = FALSE THEN
247 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
248 FND_MESSAGE.SET_NAME('IGS',v_message_name);
249 IGS_GE_MSG_STACK.ADD;
250 APP_EXCEPTION.RAISE_EXCEPTION;
251 END IF;
252 END IF;
253 -- Cannot update the Commencement Period.
254 IF ((old_references.acad_cal_type <> new_references.acad_cal_type) OR
255 (old_references.acad_ci_sequence_number <> new_references.acad_ci_sequence_number) OR (old_references.adm_cal_type <> new_references.adm_cal_type) OR
256 (old_references.adm_ci_sequence_number <> new_references.adm_ci_sequence_number)) THEN
257 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(2537));
258 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UPD_COMPERIOD_NOTALLOW');
259 IGS_GE_MSG_STACK.ADD;
260 APP_EXCEPTION.RAISE_EXCEPTION;
261 END IF;
262 --
263 -- Cannot update the Admission Process Category.
264 --
265 IF ((old_references.admission_cat <> new_references.admission_cat) OR
266 (old_references.s_admission_process_type <> new_references.s_admission_process_type) OR
267 (old_references.application_type IS NOT NULL AND old_references.application_type <> new_references.application_type )) THEN
268 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(2538));
269 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UPD_ADMPRC_CAT_NOTALLO');
270 IGS_GE_MSG_STACK.ADD;
271 APP_EXCEPTION.RAISE_EXCEPTION;
272 END IF;
273 --
274 -- Cannot update the Choice Number for UK Profile.
275 --
276 IF FND_PROFILE.VALUE('OSS_COUNTRY_CODE') = 'GB' AND (old_references.choice_number <> new_references.choice_number) THEN
277 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UPD_CH_NUM');
278 IGS_GE_MSG_STACK.ADD;
279 APP_EXCEPTION.RAISE_EXCEPTION;
280 END IF;
281 --
282 -- Cannot update the Choice Number for Alternate Application ID for UK Profile.
283 --
284 IF FND_PROFILE.VALUE('OSS_COUNTRY_CODE') = 'GB' AND (old_references.alt_appl_id <> new_references.alt_appl_id) THEN
285 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UPD_ALT_APPL_ID');
286 IGS_GE_MSG_STACK.ADD;
287 APP_EXCEPTION.RAISE_EXCEPTION;
288 END IF;
289
290 END IF; -- p_updating.
291 IF p_deleting THEN
292 --
293 -- Validate delete of the admission application record.
294 --
295 IF IGS_AD_VAL_AA.admp_val_aa_delete (
296 old_references.adm_appl_status,
297 v_message_name) = FALSE THEN
298 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
299 FND_MESSAGE.SET_NAME('IGS',v_message_name);
300 IGS_GE_MSG_STACK.ADD;
301 APP_EXCEPTION.RAISE_EXCEPTION;
302 END IF;
303 END IF; -- p_deleting.
304 --
305 -- Validate the Application Date.
306 --
307 IF p_inserting OR
308 (p_updating AND
309 (TRUNC(old_references.appl_dt) <> new_references.appl_dt)) THEN
310 IF IGS_AD_VAL_AA.admp_val_aa_appl_dt (
311 new_references.appl_dt,
312 v_message_name) = FALSE THEN
313 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
314 FND_MESSAGE.SET_NAME('IGS',v_message_name);
315 IGS_GE_MSG_STACK.ADD;
316 APP_EXCEPTION.RAISE_EXCEPTION;
317 END IF;
318 END IF;
319 --
320 -- Validate the Admission Application Status.
321 --
322 IF p_inserting OR
323 (p_updating AND
324 (old_references.adm_appl_status <> new_references.adm_appl_status))
325 THEN
326 IF IGS_AD_VAL_AA.admp_val_aa_aas (
327 new_references.person_id,
328 new_references.admission_appl_number,
329 new_references.adm_appl_status,
330 v_message_name) = FALSE THEN
331 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
332 FND_MESSAGE.SET_NAME('IGS',v_message_name);
333 IGS_GE_MSG_STACK.ADD;
334 APP_EXCEPTION.RAISE_EXCEPTION;
335 END IF;
336 END IF;
337 --
338 -- Validate the Admission Fee Status.
339 --
340 IF p_inserting OR
341 (p_updating AND
342 (old_references.adm_fee_status <> new_references.adm_fee_status))
343 THEN
344 IF IGS_AD_VAL_AA.admp_val_aa_afs (
345 new_references.person_id,
346 new_references.admission_appl_number,
347 new_references.adm_fee_status,
348 v_fees_required_ind,
349 v_cond_offer_fee_allowed_ind,
350 v_message_name) = FALSE THEN
351 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
352 FND_MESSAGE.SET_NAME('IGS',v_message_name);
353 IGS_GE_MSG_STACK.ADD;
354 APP_EXCEPTION.RAISE_EXCEPTION;
355 END IF;
356 END IF;
357 --
358 -- Validate the TAC Application Indicator.
359 --
360 IF p_inserting OR
361 (p_updating AND
362 (old_references.tac_appl_ind <> new_references.tac_appl_ind) OR
363 (TRUNC(old_references.appl_dt) <> new_references.appl_dt) ) THEN
364 IF IGS_AD_VAL_AA.admp_val_aa_tac_appl (
365 new_references.person_id,
366 new_references.tac_appl_ind,
367 new_references.appl_dt,
368 new_references.s_admission_process_type,
369 v_message_name,
370 v_return_type) = FALSE THEN
371 IF NVL(v_return_type, '-1') = cst_error THEN
372 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
373 FND_MESSAGE.SET_NAME('IGS',v_message_name);
374 IGS_GE_MSG_STACK.ADD;
375 APP_EXCEPTION.RAISE_EXCEPTION;
376 END IF;
377 END IF;
378 END IF;
379
380 -- Validate Applciation fee amount (igsm fee enhancements arvsrini)
381 --
382 IF p_inserting OR
383 (p_updating AND
384 (NVL(old_references.appl_fee_amt,-1) <> NVL(new_references.appl_fee_amt,-2)))
385 THEN
386 IF new_references.appl_fee_amt < 0 OR
387 new_references.appl_fee_amt IS NULL THEN
388
389 FND_MESSAGE.SET_NAME('IGS','IGS_AD_FEE_AMT_NON_NEGATIVE');
390 IGS_GE_MSG_STACK.ADD;
391 APP_EXCEPTION.RAISE_EXCEPTION;
392 END IF;
393 END IF;
394 --
395
396
397
398
399
400 END BeforeRowInsertUpdateDelete1;
401
402
403 PROCEDURE AfterRowUpdateDelete2(
404 p_inserting IN BOOLEAN,
405 p_updating IN BOOLEAN,
406 p_deleting IN BOOLEAN
407 ) AS
408 v_message_name VARCHAR2(30);
409 BEGIN
410 IF p_updating THEN
411 -- Create admission application history record.
412 IGS_AD_GEN_010.ADMP_INS_AA_HIST (
413 new_references.person_id,
414 new_references.admission_appl_number,
415 new_references.appl_dt,
416 TRUNC(old_references.appl_dt),
417 new_references.acad_cal_type,
418 old_references.acad_cal_type,
419 new_references.acad_ci_sequence_number,
420 old_references.acad_ci_sequence_number,
421 new_references.adm_cal_type,
422 old_references.adm_cal_type,
423 new_references.adm_ci_sequence_number,
424 old_references.adm_ci_sequence_number,
425 new_references.admission_cat,
426 old_references.admission_cat,
427 new_references.s_admission_process_type,
428 old_references.s_admission_process_type,
429 new_references.adm_appl_status,
430 old_references.adm_appl_status,
431 new_references.adm_fee_status,
432 old_references.adm_fee_status,
433 new_references.tac_appl_ind,
434 old_references.tac_appl_ind,
435 new_references.last_updated_by,
436 old_references.last_updated_by,
437 new_references.last_update_date,
438 old_references.last_update_date);
439 END IF;
440 IF p_deleting THEN
441 -- Delete admission application history records.
442 IF IGS_AD_GEN_001.ADMP_DEL_AA_HIST (
443 old_references.person_id,
444 old_references.admission_appl_number,
445 v_message_name) = FALSE THEN
446 --raise_application_error(-20000,IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
447 FND_MESSAGE.SET_NAME('IGS',v_message_name);
448 IGS_GE_MSG_STACK.ADD;
449 APP_EXCEPTION.RAISE_EXCEPTION;
450 END IF;
451 END IF;
452
453
454 END AfterRowUpdateDelete2;
455
456 PROCEDURE Check_Parent_Existance AS
457 BEGIN
458
459 IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
460 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number) AND
461 (old_references.acad_cal_type = new_references.acad_cal_type) AND
462 (old_references.acad_ci_sequence_number = new_references.acad_ci_sequence_number)) OR
463 ((new_references.adm_cal_type IS NULL) OR
464 (new_references.adm_ci_sequence_number IS NULL) OR
465 (new_references.acad_cal_type IS NULL) OR
466 (new_references.acad_ci_sequence_number IS NULL))) THEN
467 NULL;
468 ELSE
469 IF NOT IGS_CA_INST_REL_PKG.Get_PK_For_Validation (
470 new_references.adm_cal_type,
471 new_references.adm_ci_sequence_number,
472 new_references.acad_cal_type,
473 new_references.acad_ci_sequence_number
474 )THEN
475 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACACAL_ADMCAL_NOTEXIST');
476 IGS_GE_MSG_STACK.ADD;
477 APP_EXCEPTION.RAISE_EXCEPTION;
478 END IF;
479 END IF;
480
481 IF (((old_references.person_id = new_references.person_id)) OR
482 ((new_references.person_id IS NULL))) THEN
483 NULL;
484 ELSE
485 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
486 new_references.person_id
487 )THEN
488 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
489 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON'));
490 IGS_GE_MSG_STACK.ADD;
491 APP_EXCEPTION.RAISE_EXCEPTION;
492 END IF;
493 END IF;
494
495 IF (((old_references.application_type = new_references.application_type)) OR
496 ((new_references.application_type IS NULL))) THEN
497 NULL;
498 ELSE
499 IF NOT IGS_AD_SS_APPL_TYP_PKG.Get_PK_For_Validation (
500 new_references.application_type,'N'
501 )THEN
502 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
503 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_APPL_TYPE'));
504 APP_EXCEPTION.RAISE_EXCEPTION;
505 END IF;
506 END IF;
507
508
509 IF (((old_references.adm_appl_status = new_references.adm_appl_status)) OR
510 ((new_references.adm_appl_status IS NULL))) THEN
511 NULL;
512 ELSE
513 IF NOT IGS_AD_APPL_STAT_PKG.Get_PK_For_Validation (
514 new_references.adm_appl_status ,'N'
515 )THEN
516 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
517 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_APPL_STATUS'));
518 IGS_GE_MSG_STACK.ADD;
519 APP_EXCEPTION.RAISE_EXCEPTION;
520 END IF;
521 END IF;
522
523 IF (((old_references.adm_fee_status = new_references.adm_fee_status)) OR
524 ((new_references.adm_fee_status IS NULL))) THEN
525 NULL;
526 ELSE
527 IF NOT IGS_AD_FEE_STAT_PKG.Get_PK_For_Validation (
528 new_references.adm_fee_status , 'N'
529 )THEN
530 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
531 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_FEE_STATUS'));
532 IGS_GE_MSG_STACK.ADD;
533 APP_EXCEPTION.RAISE_EXCEPTION;
534 END IF;
535 END IF;
536
537 IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
538 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number) AND
539 (old_references.admission_cat = new_references.admission_cat) AND
540 (old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
541 ((new_references.adm_cal_type IS NULL) OR
542 (new_references.adm_ci_sequence_number IS NULL) OR
543 (new_references.admission_cat IS NULL) OR
544 (new_references.s_admission_process_type IS NULL))) THEN
545 NULL;
546 ELSE
547 IF NOT IGS_AD_PRD_AD_PRC_CA_PKG.Get_PK_For_Validation (
548 new_references.adm_cal_type,
549 new_references.adm_ci_sequence_number,
550 new_references.admission_cat,
551 new_references.s_admission_process_type ,
552 'N'
553 )THEN
554 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
555 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_PRCS_CAT_ADM_CAL'));
556 IGS_GE_MSG_STACK.ADD;
557 APP_EXCEPTION.RAISE_EXCEPTION;
558 END IF;
559 END IF;
560
561 IF (((old_references.admission_cat = new_references.admission_cat) AND
562 (old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
563 ((new_references.admission_cat IS NULL) OR
564 (new_references.s_admission_process_type IS NULL))) THEN
565 NULL;
566 ELSE
567 IF NOT IGS_AD_PRCS_CAT_PKG.Get_PK_For_Validation (
568 new_references.admission_cat,
569 new_references.s_admission_process_type ,
570 'N'
571 )THEN
572 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
573 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_PRCS_CAT'));
574 IGS_GE_MSG_STACK.ADD;
575 APP_EXCEPTION.RAISE_EXCEPTION;
576 END IF;
577 END IF;
578
579 IF new_references.spcl_grp_1 IS NOT NULL AND NOT igs_ad_code_classes_pkg.Get_PK_For_Validation(new_references.spcl_grp_1,'N') THEN
580 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
581 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SPL_GRP'));
582 IGS_GE_MSG_STACK.ADD;
583 APP_EXCEPTION.RAISE_EXCEPTION;
584 END IF;
585 IF new_references.spcl_grp_1 IS NOT NULL AND NOT igs_ad_code_classes_pkg.Get_PK_For_Validation(new_references.spcl_grp_1,'N') THEN
586 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
587 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SPL_GRP'));
588 IGS_GE_MSG_STACK.ADD;
589 APP_EXCEPTION.RAISE_EXCEPTION;
590 END IF;
591
592 END Check_Parent_Existance;
593
594 PROCEDURE Check_Child_Existance AS
595 BEGIN
596
597 IGS_AD_APPL_LTR_PKG.GET_FK_IGS_AD_APPL (
598 old_references.person_id,
599 old_references.admission_appl_number
600 );
601
602 IGS_AD_PS_APPL_PKG.GET_FK_IGS_AD_APPL (
603 old_references.person_id,
604 old_references.admission_appl_number
605 );
606
607 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_AD_APPL (
608 old_references.person_id,
609 old_references.admission_appl_number
610 );
611 IGS_AD_APP_REQ_PKG.GET_FK_IGS_AD_APPL (
612 old_references.person_id,
613 old_references.admission_appl_number
614 );
615 --
616 -- Modified By : kamohan
617 -- Date : 1/21/02
618 -- Bug # 2177686
619 -- Added the fk check for the following six tables which are moved from igs_ad_ps_appl_inst table
620 --
621 igs_ad_other_inst_pkg.get_fk_igs_ad_appl (
622 old_references.person_id,
623 old_references.admission_appl_number
624 );
625 igs_ad_acad_interest_pkg.get_fk_igs_ad_appl (
626 old_references.person_id,
627 old_references.admission_appl_number
628 );
629 igs_ad_app_intent_pkg.get_fk_igs_ad_appl (
630 old_references.person_id,
631 old_references.admission_appl_number
632 );
633 igs_ad_spl_interests_pkg.get_fk_igs_ad_appl (
634 old_references.person_id,
635 old_references.admission_appl_number
636 );
637 igs_ad_spl_talents_pkg.get_fk_igs_ad_appl (
638 old_references.person_id,
639 old_references.admission_appl_number
640 );
641 --
642 -- End of Bug # 2177686 modifications
643 --
644
645 END Check_Child_Existance;
646
647 FUNCTION Get_PK_For_Validation (
648 x_person_id IN NUMBER,
649 x_admission_appl_number IN NUMBER
650 )
651 RETURN BOOLEAN AS
652
653 CURSOR cur_rowid IS
654 SELECT rowid
655 FROM IGS_AD_APPL_ALL
656 WHERE person_id = x_person_id
657 AND admission_appl_number = x_admission_appl_number
658 FOR UPDATE NOWAIT;
659
660 lv_rowid cur_rowid%RowType;
661
662 BEGIN
663
664 Open cur_rowid;
665 Fetch cur_rowid INTO lv_rowid;
666 IF (cur_rowid%FOUND) THEN
667 Close cur_rowid;
668 Return TRUE;
669 ELSE
670 Close cur_rowid;
671 Return FALSE;
672 END IF;
673
674 END Get_PK_For_Validation;
675
676 PROCEDURE GET_FK_IGS_AD_SS_APPL_TYP(
677 x_application_type IN VARCHAR2
678 ) AS
679 CURSOR cur_rowid IS
680 SELECT rowid
681 FROM igs_ad_appl_all
682 WHERE application_type = x_application_type;
683
684 lv_rowid cur_rowid%RowType;
685 BEGIN
686
687 Open cur_rowid;
688 Fetch cur_rowid INTO lv_rowid;
689 IF (cur_rowid%FOUND) THEN
690 Close cur_rowid;
691 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AA_SSAT_FK');
692 IGS_GE_MSG_STACK.ADD;
693 App_Exception.Raise_Exception;
694 Return;
695 END IF;
696 Close cur_rowid;
697 END GET_FK_IGS_AD_SS_APPL_TYP;
698
699 PROCEDURE GET_FK_IGS_CA_INST_REL (
700 x_sub_cal_type IN VARCHAR2,
701 x_sub_ci_sequence_number IN NUMBER,
702 x_sup_cal_type IN VARCHAR2,
703 x_sup_ci_sequence_number IN NUMBER
704 ) AS
705
706 CURSOR cur_rowid IS
707 SELECT rowid
708 FROM IGS_AD_APPL_ALL
709 WHERE adm_cal_type = x_sub_cal_type
710 AND adm_ci_sequence_number = x_sub_ci_sequence_number
711 AND acad_cal_type = x_sup_cal_type
712 AND acad_ci_sequence_number = x_sup_ci_sequence_number ;
713
714 lv_rowid cur_rowid%RowType;
715
716 BEGIN
717
718 Open cur_rowid;
719 Fetch cur_rowid INTO lv_rowid;
720 IF (cur_rowid%FOUND) THEN
721 Close cur_rowid;
722 Fnd_Message.Set_Name ('IGS', 'IGS_CA_AA_CIR_FK');
723 IGS_GE_MSG_STACK.ADD;
724 App_Exception.Raise_Exception;
725 Return;
726 END IF;
727 Close cur_rowid;
728
729 END GET_FK_IGS_CA_INST_REL;
730
731 PROCEDURE GET_FK_IGS_PE_PERSON (
732 x_person_id IN NUMBER
733 ) AS
734
735 CURSOR cur_rowid IS
736 SELECT rowid
737 FROM IGS_AD_APPL_ALL
738 WHERE person_id = x_person_id ;
739
740 lv_rowid cur_rowid%RowType;
741
742 BEGIN
743
744 Open cur_rowid;
745 Fetch cur_rowid INTO lv_rowid;
746 IF (cur_rowid%FOUND) THEN
747 Close cur_rowid;
748 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AA_PE_FK');
749 IGS_GE_MSG_STACK.ADD;
750 App_Exception.Raise_Exception;
751 Return;
752 END IF;
753 Close cur_rowid;
754
755 END GET_FK_IGS_PE_PERSON;
756
757 PROCEDURE GET_FK_IGS_AD_APPL_STAT (
758 x_adm_appl_status IN VARCHAR2
759 ) AS
760
761 CURSOR cur_rowid IS
762 SELECT rowid
763 FROM IGS_AD_APPL_ALL
764 WHERE adm_appl_status = x_adm_appl_status ;
765
766 lv_rowid cur_rowid%RowType;
767
768 BEGIN
769
770 Open cur_rowid;
771 Fetch cur_rowid INTO lv_rowid;
772 IF (cur_rowid%FOUND) THEN
773 Close cur_rowid;
774 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AA_AAS_FK');
775 IGS_GE_MSG_STACK.ADD;
776 App_Exception.Raise_Exception;
777 Return;
778 END IF;
779 Close cur_rowid;
780
781 END GET_FK_IGS_AD_APPL_STAT;
782
783 PROCEDURE GET_FK_IGS_AD_FEE_STAT (
784 x_adm_fee_status IN VARCHAR2
785 ) AS
786
787 CURSOR cur_rowid IS
788 SELECT rowid
789 FROM IGS_AD_APPL_ALL
790 WHERE adm_fee_status = x_adm_fee_status ;
791
792 lv_rowid cur_rowid%RowType;
793
794 BEGIN
795
796 Open cur_rowid;
797 Fetch cur_rowid INTO lv_rowid;
798 IF (cur_rowid%FOUND) THEN
799 Close cur_rowid;
800 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AA_AFS_FK');
801 IGS_GE_MSG_STACK.ADD;
802 App_Exception.Raise_Exception;
803 Return;
804 END IF;
805 Close cur_rowid;
806
807 END GET_FK_IGS_AD_FEE_STAT;
808
809 PROCEDURE GET_FK_IGS_AD_PRD_AD_PRC_CA (
810 x_adm_cal_type IN VARCHAR2,
811 x_adm_ci_sequence_number IN NUMBER,
812 x_admission_cat IN VARCHAR2,
813 x_s_admission_process_type IN VARCHAR2
814 ) AS
815
816 CURSOR cur_rowid IS
817 SELECT rowid
818 FROM IGS_AD_APPL_ALL
819 WHERE adm_cal_type = x_adm_cal_type
820 AND adm_ci_sequence_number = x_adm_ci_sequence_number
821 AND admission_cat = x_admission_cat
822 AND s_admission_process_type = x_s_admission_process_type ;
823
824 lv_rowid cur_rowid%RowType;
825
826 BEGIN
827
828 Open cur_rowid;
829 Fetch cur_rowid INTO lv_rowid;
830 IF (cur_rowid%FOUND) THEN
831 Close cur_rowid;
832 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AA_APAPC_FK');
833 IGS_GE_MSG_STACK.ADD;
834 App_Exception.Raise_Exception;
835 Return;
836 END IF;
837 Close cur_rowid;
838
839 END GET_FK_IGS_AD_PRD_AD_PRC_CA;
840
841 PROCEDURE GET_FK_IGS_AD_PRCS_CAT (
842 x_admission_cat IN VARCHAR2,
843 x_s_admission_process_type IN VARCHAR2
844 ) AS
845
846 CURSOR cur_rowid IS
847 SELECT rowid
848 FROM IGS_AD_APPL_ALL
849 WHERE admission_cat = x_admission_cat
850 AND s_admission_process_type = x_s_admission_process_type ;
851
852 lv_rowid cur_rowid%RowType;
853
854 BEGIN
855
856 Open cur_rowid;
857 Fetch cur_rowid INTO lv_rowid;
858 IF (cur_rowid%FOUND) THEN
859 Close cur_rowid;
860 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AA_APC_FK');
861 IGS_GE_MSG_STACK.ADD;
862 App_Exception.Raise_Exception;
863 Return;
864 END IF;
865 Close cur_rowid;
866
867 END GET_FK_IGS_AD_PRCS_CAT;
868
869 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
870 x_code_id IN NUMBER
871 ) AS
872 /*************************************************************
873 Created By : nsinha
874 Date Created By : 01-Aug-2001
875 Purpose :
876 Know limitations, enhancements or remarks
877 Change History
878 Who When What
879 (reverse chronological order - newest change first)
880 ***************************************************************/
881 CURSOR cur_rowid1 IS
882 SELECT rowid
883 FROM igs_ad_appl_all
884 WHERE spcl_grp_1 = x_code_id
885 OR spcl_grp_2 = x_code_id;
886 lv_rowid cur_rowid1%RowType;
887 BEGIN
888 Open cur_rowid1;
889 Fetch cur_rowid1 INTO lv_rowid;
890 IF (cur_rowid1%FOUND) THEN
891 Close cur_rowid1;
892 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AA_ACDC_FK1');
893 IGS_GE_MSG_STACK.ADD;
894 App_Exception.Raise_Exception;
895 Return;
896 END IF;
897 Close cur_rowid1;
898 END Get_FK_Igs_Ad_Code_Classes;
899
900 -- procedure to check constraints
901 PROCEDURE CHECK_CONSTRAINTS(
902 column_name IN VARCHAR2,
903 column_value IN VARCHAR2
904 ) AS
905 BEGIN
906 IF column_name is null THEN
907 NULL;
908 ELSIF upper(column_name) = 'ACAD_CAL_TYPE' THEN
909 new_references.acad_cal_type := column_value;
910 ELSIF upper(column_name) = 'ADMISSION_CAT' THEN
911 new_references.admission_cat := column_value;
912 ELSIF upper(column_name) = 'ADM_APPL_STATUS' THEN
913 new_references.adm_appl_status := column_value;
914 ELSIF upper(column_name) = 'ADM_CAL_TYPE' THEN
915 new_references.adm_cal_type := column_value;
916 ELSIF upper(column_name) = 'ADM_FEE_STATUS' THEN
917 new_references.adm_fee_status := column_value;
918 ELSIF upper(column_name) = 'S_ADMISSION_PROCESS_TYPE' THEN
919 new_references.s_admission_process_type := column_value;
920 ELSIF upper(column_name) = 'TAC_APPL_IND' THEN
921 new_references.tac_appl_ind := column_value;
922 ELSIF upper(column_name) = 'ADMISSION_APPL_NUMBER' THEN
923 new_references.admission_appl_number := igs_ge_number.to_num(column_value);
924 ELSIF upper(column_name) = 'ACAD_CI_SEQUENCE_NUMBER' THEN
925 new_references.acad_ci_sequence_number := igs_ge_number.to_num(column_value);
926 ELSIF upper(column_name) = 'ADM_CI_SEQUENCE_NUMBER' THEN
927 new_references.adm_ci_sequence_number := igs_ge_number.to_num(column_value);
928 ELSIF upper(column_name) = 'APPLICATION_TYPE' THEN-- Added as part of Enh Bug 2599457
929 new_references.application_type := column_value;
930 END IF;
931
932 IF upper(column_name) = 'ADMISSION_APPL_NUMBER' OR column_name IS NULL THEN
933 IF new_references.admission_appl_number < 0 OR new_references.admission_appl_number > 99 THEN
934 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
935 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_NO'));
936 IGS_GE_MSG_STACK.ADD;
937 APP_EXCEPTION.RAISE_EXCEPTION;
938 END IF;
939 END IF;
940 IF upper(column_name) = 'ACAD_CI_SEQUENCE_NUMBER' OR column_name IS NULL THEN
941 IF new_references.acad_ci_sequence_number < 1 OR new_references.acad_ci_sequence_number > 999999 THEN
942 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
943 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ACAD_CAL'));
944 IGS_GE_MSG_STACK.ADD;
945 APP_EXCEPTION.RAISE_EXCEPTION;
946 END IF;
947 END IF;
948 IF upper(column_name) = 'ADM_CI_SEQUENCE_NUMBER' OR column_name IS NULL THEN
949 IF (new_references.adm_ci_sequence_number < 1 OR new_references.adm_ci_sequence_number > 999999) THEN
950 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
951 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_CAL'));
952 IGS_GE_MSG_STACK.ADD;
953 APP_EXCEPTION.RAISE_EXCEPTION;
954 END IF;
955 END IF;
956 IF upper(column_name) = 'TAC_APPL_IND' OR column_name IS NULL THEN
957 IF new_references.tac_appl_ind NOT IN ('Y','N') THEN
958 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
959 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TAC_APPL_IND'));
960 IGS_GE_MSG_STACK.ADD;
961 APP_EXCEPTION.RAISE_EXCEPTION;
962 END IF;
963 END IF;
964 IF upper(column_name) = 'APPLICATION_TYPE' OR column_name IS NULL THEN -- Added as part of Enh Bug 2599457
965 IF new_references.application_type IS NULL THEN
966 FND_MESSAGE.SET_NAME('IGS','IGS_AD_APPL_TYPE_NULL');
967 IGS_GE_MSG_STACK.ADD;
968 APP_EXCEPTION.RAISE_EXCEPTION;
969 END IF;
970 END IF;
971 IF upper(column_name) = 'ACAD_CAL_TYPE' OR column_name IS NULL THEN
972 IF new_references.acad_cal_type IS NULL THEN
973 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
974 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ACAD_CAL'));
975 IGS_GE_MSG_STACK.ADD;
976 APP_EXCEPTION.RAISE_EXCEPTION;
977 END IF;
978 END IF;
979 IF upper(column_name) = 'ADMISSION_CAT' OR column_name IS NULL THEN
980 IF new_references.admission_cat IS NULL THEN
981 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
982 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_PRCS_CAT'));
983 IGS_GE_MSG_STACK.ADD;
984 APP_EXCEPTION.RAISE_EXCEPTION;
985 END IF;
986 END IF;
987 IF upper(column_name) = 'ADM_APPL_STATUS' OR column_name IS NULL THEN
988 IF new_references.adm_appl_status IS NULL THEN
989 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
990 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_APPL_STATUS'));
991 IGS_GE_MSG_STACK.ADD;
992 APP_EXCEPTION.RAISE_EXCEPTION;
993 END IF;
994 END IF;
995 IF upper(column_name) = 'ADM_CAL_TYPE' OR column_name IS NULL THEN
996 IF new_references.adm_cal_type IS NULL THEN
997 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
998 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_CAL'));
999 IGS_GE_MSG_STACK.ADD;
1000 APP_EXCEPTION.RAISE_EXCEPTION;
1001 END IF;
1002 END IF;
1003 IF upper(column_name) = 'ADM_FEE_STATUS' OR column_name IS NULL THEN
1004 IF new_references.adm_fee_status IS NULL THEN
1005 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1006 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_FEE_STATUS'));
1007 IGS_GE_MSG_STACK.ADD;
1008 APP_EXCEPTION.RAISE_EXCEPTION;
1009 END IF;
1010 END IF;
1011 IF upper(column_name) = 'S_ADMISSION_PROCESS_TYPE' OR column_name IS NULL THEN
1012 IF new_references.s_admission_process_type IS NULL THEN
1013 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1014 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_PRCS_CAT'));
1015 IGS_GE_MSG_STACK.ADD;
1016 APP_EXCEPTION.RAISE_EXCEPTION;
1017 END IF;
1018 END IF;
1019 IF new_references.Common_app IS NOT NULL AND new_references.common_app NOT IN ('Y','N') THEN
1020 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1021 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_COM_APP'));
1022 IGS_GE_MSG_STACK.ADD;
1023 APP_EXCEPTION.RAISE_EXCEPTION;
1024 END IF;
1025
1026 END CHECK_CONSTRAINTS;
1027
1028
1029 PROCEDURE Before_DML (
1030 p_action IN VARCHAR2,
1031 x_rowid IN VARCHAR2,
1032 x_org_id IN NUMBER,
1033 x_person_id IN NUMBER,
1034 x_admission_appl_number IN NUMBER,
1035 x_appl_dt IN DATE,
1036 x_acad_cal_type IN VARCHAR2,
1037 x_acad_ci_sequence_number IN NUMBER,
1038 x_adm_cal_type IN VARCHAR2,
1039 x_adm_ci_sequence_number IN NUMBER,
1040 x_admission_cat IN VARCHAR2,
1041 x_s_admission_process_type IN VARCHAR2,
1042 x_adm_appl_status IN VARCHAR2,
1043 x_adm_fee_status IN VARCHAR2,
1044 x_tac_appl_ind IN VARCHAR2,
1045 x_spcl_grp_1 IN NUMBER,
1046 x_spcl_grp_2 IN NUMBER,
1047 x_common_app IN VARCHAR2,
1048 x_application_type IN VARCHAR2,
1049 x_creation_date IN DATE,
1050 x_created_by IN NUMBER,
1051 x_last_update_date IN DATE,
1052 x_last_updated_by IN NUMBER,
1053 x_last_update_login IN NUMBER,
1054 x_choice_number IN VARCHAR2,
1055 x_routeb_pref IN VARCHAR2,
1056 x_alt_appl_id IN VARCHAR2,
1057 x_appl_fee_amt IN NUMBER
1058 ) AS
1059 ------------------------------------------------------------------
1060 --Change History:
1061 --Who When What
1062 --smadathi 12-Feb-2002 Bug 2217104. Added columns choice_number,routeb_pref
1063 --pbondugu 04-Mar-2003 Validation is added for checking whether application date
1064 -- is greater than birthdate or not.
1065 --pbondugu 23-apr-2003 validation for checking whether application date
1066 -- is greater than birthdate or not is moved to BeforeRowInsertUpdateDelete1
1067 -------------------------------------------------------------------
1068
1069 BEGIN
1070 Set_Column_Values (
1071 p_action,
1072 x_rowid,
1073 x_org_id,
1074 x_person_id,
1075 x_admission_appl_number,
1076 x_appl_dt,
1077 x_acad_cal_type,
1078 x_acad_ci_sequence_number,
1079 x_adm_cal_type,
1080 x_adm_ci_sequence_number,
1081 x_admission_cat,
1082 x_s_admission_process_type,
1083 x_adm_appl_status,
1084 x_adm_fee_status,
1085 x_tac_appl_ind,
1086 x_spcl_grp_1,
1087 x_spcl_grp_2,
1088 x_common_app,
1089 x_application_type,
1090 x_creation_date,
1091 x_created_by,
1092 x_last_update_date,
1093 x_last_updated_by,
1094 x_last_update_login ,
1095 x_choice_number,
1096 x_routeb_pref,
1097 x_alt_appl_id,
1098 x_appl_fee_amt
1099 );
1100
1101 IF (p_action = 'INSERT') THEN
1102 -- Call all the procedures related to Before Insert.
1103 BeforeRowInsertUpdateDelete1 (
1104 p_inserting => TRUE,
1105 p_updating => FALSE,
1106 p_deleting => FALSE );
1107 IF GET_PK_FOR_VALIDATION(
1108 new_references.person_id,
1109 new_references.admission_appl_number
1110 )THEN
1111 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
1112 IGS_GE_MSG_STACK.ADD;
1113 APP_EXCEPTION.RAISE_EXCEPTION;
1114 END IF;
1115 Check_Constraints;
1116 Check_Parent_Existance;
1117 ELSIF (p_action = 'UPDATE') THEN
1118 -- Call all the procedures related to Before Update.
1119 BeforeRowInsertUpdateDelete1 (
1120 p_inserting => FALSE,
1121 p_updating => TRUE,
1122 p_deleting => FALSE );
1123 Check_Constraints;
1124 Check_Parent_Existance;
1125 ELSIF (p_action = 'DELETE') THEN
1126 -- Call all the procedures related to Before Delete.
1127 BeforeRowInsertUpdateDelete1 (
1128 p_inserting => FALSE,
1129 p_updating => FALSE,
1130 p_deleting => TRUE );
1131 Check_Child_Existance;
1132 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1133 -- Call all the procedures related to Before Delete.
1134 IF GET_PK_FOR_VALIDATION(
1135 new_references.person_id,
1136 new_references.admission_appl_number
1137 )THEN
1138 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
1139 IGS_GE_MSG_STACK.ADD;
1140 APP_EXCEPTION.RAISE_EXCEPTION;
1141 END IF;
1142 Check_Constraints;
1143 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1144 -- Call all the procedures related to Before Delete.
1145 check_constraints;
1146 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1147 -- Call all the procedures related to Before Delete.
1148 Check_Child_Existance;
1149
1150 END IF;
1151
1152 END Before_DML;
1153
1154 PROCEDURE After_DML (
1155 p_action IN VARCHAR2,
1156 x_rowid IN VARCHAR2
1157 ) AS
1158 -------------------------------------------------------------------------------
1159 -- Bug ID : 1818617
1160 -- who when what
1161 -- rasahoo 01-Sep-2003 Removed the private procedure IGF_UPDATE_DATA And
1162 -- Removed the call of IGF_UPDATE_DATA as part of the Build
1163 -- FA 114(Obsoletion of base record history)
1164 -- sjadhav jun 28,2001 this procedure is modified to trigger
1165 -- a Concurrent Request (IGFAPJ10) which
1166 -- will create a new record in IGF To
1167 -- Do table
1168 -------------------------------------------------------------------------------
1169
1170
1171 BEGIN
1172
1173 l_rowid := x_rowid;
1174
1175 IF (p_action = 'UPDATE') THEN
1176 -- Call all the procedures related to After Update.
1177 AfterRowUpdateDelete2 (
1178 p_inserting => FALSE,
1179 p_updating => TRUE,
1180 p_deleting => FALSE );
1181 IF NEW_REFERENCES.ADM_APPL_STATUS <> OLD_REFERENCES.ADM_APPL_STATUS THEN
1182 igs_ad_wf_001.APP_PRCOC_STATUS_UPD_EVENT (
1183 P_PERSON_ID => new_references.person_id,
1184 P_ADMISSION_APPL_NUMBER => new_references.admission_appl_number,
1185 P_ADM_APPL_STATUS_NEW => new_references.adm_appl_status,
1186 P_ADM_APPL_STATUS_OLD => old_references.adm_appl_status);
1187
1188 END IF;
1189 ELSIF (p_action = 'DELETE') THEN
1190 -- Call all the procedures related to After Delete.
1191 AfterRowUpdateDelete2 (
1192 p_inserting => FALSE,
1193 p_updating => FALSE,
1194 p_deleting => TRUE );
1195 END IF;
1196
1197 END After_DML;
1198
1199 procedure INSERT_ROW (
1200 X_ROWID in out NOCOPY VARCHAR2,
1201 X_ORG_ID in NUMBER,
1202 X_PERSON_ID in NUMBER,
1203 X_ADMISSION_APPL_NUMBER in NUMBER,
1204 X_APPL_DT in DATE,
1205 X_ACAD_CAL_TYPE in VARCHAR2,
1206 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
1207 X_ADM_CAL_TYPE in VARCHAR2,
1208 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1209 X_ADMISSION_CAT in VARCHAR2,
1210 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
1211 X_ADM_APPL_STATUS in VARCHAR2,
1212 X_ADM_FEE_STATUS in VARCHAR2,
1213 X_TAC_APPL_IND in VARCHAR2,
1214 x_spcl_grp_1 IN NUMBER,
1215 x_spcl_grp_2 IN NUMBER,
1216 x_common_app IN VARCHAR2,
1217 x_application_type IN VARCHAR2,
1218 X_MODE IN VARCHAR2,
1219 x_choice_number IN VARCHAR2,
1220 x_routeb_pref IN VARCHAR2,
1221 x_alt_appl_id IN VARCHAR2,
1222 x_appl_fee_amt IN NUMBER DEFAULT NULL
1223 ) AS
1224 ------------------------------------------------------------------
1225 --Change History:
1226 --Who When What
1227 --ravishar 25-May-2005 Security related changes(Bug- 4344197)
1228 --smvk 14-Feb-2002 Call to igs_ge_gen_003.get_org_id w.r.t SWCR006
1229 --smadathi 12-Feb-2002 Bug 2217104. Added columns choice_number,routeb_pref
1230 -------------------------------------------------------------------
1231 cursor C is select ROWID from IGS_AD_APPL_ALL
1232 where PERSON_ID = X_PERSON_ID
1233 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER;
1234 X_LAST_UPDATE_DATE DATE;
1235 X_LAST_UPDATED_BY NUMBER;
1236 X_LAST_UPDATE_LOGIN NUMBER;
1237 X_REQUEST_ID NUMBER;
1238 X_PROGRAM_ID NUMBER;
1239 X_PROGRAM_APPLICATION_ID NUMBER;
1240 X_PROGRAM_UPDATE_DATE DATE;
1241 begin
1242 X_LAST_UPDATE_DATE := SYSDATE;
1243 if(X_MODE = 'I') then
1244 X_LAST_UPDATED_BY := 1;
1245 X_LAST_UPDATE_LOGIN := 0;
1246 elsif (X_MODE IN ('R', 'S')) then
1247 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1248 if X_LAST_UPDATED_BY is NULL then
1249 X_LAST_UPDATED_BY := -1;
1250 end if;
1251 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1252 if X_LAST_UPDATE_LOGIN is NULL then
1253 X_LAST_UPDATE_LOGIN := -1;
1254 end if;
1255 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1256 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1257 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1258 if (X_REQUEST_ID = -1) then
1259 X_REQUEST_ID := NULL;
1260 X_PROGRAM_ID := NULL;
1261 X_PROGRAM_APPLICATION_ID := NULL;
1262 X_PROGRAM_UPDATE_DATE := NULL;
1263 else
1264 X_PROGRAM_UPDATE_DATE := SYSDATE;
1265 end if;
1266 else
1267 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1268 IGS_GE_MSG_STACK.ADD;
1269 app_exception.raise_exception;
1270 end if;
1271
1272 Before_DML (
1273 p_action => 'INSERT',
1274 x_rowid => X_ROWID,
1275 x_org_id => igs_ge_gen_003.get_org_id,
1276 x_person_id => X_PERSON_ID,
1277 x_admission_appl_number => X_ADMISSION_APPL_NUMBER,
1278 x_appl_dt => Nvl(X_APPL_DT, SYSDATE),
1279 x_acad_cal_type => X_ACAD_CAL_TYPE,
1280 x_acad_ci_sequence_number => X_ACAD_CI_SEQUENCE_NUMBER,
1281 x_adm_cal_type => X_ADM_CAL_TYPE,
1282 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
1283 x_admission_cat => X_ADMISSION_CAT,
1284 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
1285 x_adm_appl_status => X_ADM_APPL_STATUS,
1286 x_adm_fee_status => X_ADM_FEE_STATUS,
1287 x_tac_appl_ind => Nvl(X_TAC_APPL_IND, 'N'),
1288 x_spcl_grp_1 => x_spcl_grp_1,
1289 x_spcl_grp_2 => x_spcl_grp_2,
1290 x_common_app => x_common_app,
1291 x_application_type => x_application_type,
1292 x_creation_date => X_LAST_UPDATE_DATE,
1293 x_created_by => X_LAST_UPDATED_BY,
1294 x_last_update_date => X_LAST_UPDATE_DATE,
1295 x_last_updated_by => X_LAST_UPDATED_BY,
1296 x_last_update_login => X_LAST_UPDATE_LOGIN,
1297 x_choice_number => x_choice_number,
1298 x_routeb_pref => x_routeb_pref,
1299 x_alt_appl_id => x_alt_appl_id,
1300 x_appl_fee_amt => x_appl_fee_amt
1301 );
1302
1303 IF (x_mode = 'S') THEN
1304 igs_sc_gen_001.set_ctx('R');
1305 END IF;
1306 insert into IGS_AD_APPL_ALL (
1307 PERSON_ID,
1308 ORG_ID,
1309 ADMISSION_APPL_NUMBER,
1310 APPL_DT,
1311 ACAD_CAL_TYPE,
1312 ACAD_CI_SEQUENCE_NUMBER,
1313 ADM_CAL_TYPE,
1314 ADM_CI_SEQUENCE_NUMBER,
1315 ADMISSION_CAT,
1316 S_ADMISSION_PROCESS_TYPE,
1317 ADM_APPL_STATUS,
1318 ADM_FEE_STATUS,
1319 TAC_APPL_IND,
1320 spcl_grp_1,
1321 spcl_grp_2,
1322 common_app,
1323 application_type,
1324 CREATION_DATE,
1325 CREATED_BY,
1326 LAST_UPDATE_DATE,
1327 LAST_UPDATED_BY,
1328 LAST_UPDATE_LOGIN,
1329 REQUEST_ID,
1330 PROGRAM_ID,
1331 PROGRAM_APPLICATION_ID,
1332 PROGRAM_UPDATE_DATE,
1333 choice_number,
1334 routeb_pref,
1335 application_id,
1336 alt_appl_id,
1337 appl_fee_amt
1338 ) values (
1339 NEW_REFERENCES.PERSON_ID,
1340 NEW_REFERENCES.ORG_ID,
1341 NEW_REFERENCES.ADMISSION_APPL_NUMBER,
1342 NEW_REFERENCES.APPL_DT,
1343 NEW_REFERENCES.ACAD_CAL_TYPE,
1344 NEW_REFERENCES.ACAD_CI_SEQUENCE_NUMBER,
1345 NEW_REFERENCES.ADM_CAL_TYPE,
1346 NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
1347 NEW_REFERENCES.ADMISSION_CAT,
1348 NEW_REFERENCES.S_ADMISSION_PROCESS_TYPE,
1349 NEW_REFERENCES.ADM_APPL_STATUS,
1350 NEW_REFERENCES.ADM_FEE_STATUS,
1351 NEW_REFERENCES.TAC_APPL_IND,
1352 NEW_REFERENCES.spcl_grp_1,
1353 NEW_REFERENCES.spcl_grp_2,
1354 NEW_REFERENCES.common_app,
1355 new_references.application_type,
1356 X_LAST_UPDATE_DATE,
1357 X_LAST_UPDATED_BY,
1358 X_LAST_UPDATE_DATE,
1359 X_LAST_UPDATED_BY,
1360 X_LAST_UPDATE_LOGIN,
1361 X_REQUEST_ID,
1362 X_PROGRAM_ID,
1363 X_PROGRAM_APPLICATION_ID,
1364 X_PROGRAM_UPDATE_DATE ,
1365 new_references.choice_number,
1366 new_references.routeb_pref,
1367 IGS_AD_APL_INT_S.nextval,
1368 new_references.alt_appl_id,
1369 new_references.appl_fee_amt
1370 );
1371 IF (x_mode = 'S') THEN
1372 igs_sc_gen_001.unset_ctx('R');
1373 END IF;
1374
1375
1376 open c;
1377 fetch c into X_ROWID;
1378 if (c%notfound) then
1379 close c;
1380 raise no_data_found;
1381 end if;
1382 close c;
1383 After_DML (
1384 p_action => 'INSERT',
1385 x_rowid => X_ROWID
1386 );
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 IF (x_mode = 'S') THEN
1390 igs_sc_gen_001.unset_ctx('R');
1391 END IF;
1392 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1393 -- Code to handle Security Policy error raised
1394 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1395 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1396 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1397 -- that the ownerof policy function does not have privilege to access.
1398 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1399 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1400 IGS_GE_MSG_STACK.ADD;
1401 app_exception.raise_exception;
1402 ELSE
1403 RAISE;
1404 END IF;
1405 end INSERT_ROW;
1406
1407 procedure LOCK_ROW (
1408 X_ROWID in VARCHAR2,
1409 X_PERSON_ID in NUMBER,
1410 X_ADMISSION_APPL_NUMBER in NUMBER,
1411 X_APPL_DT in DATE,
1412 X_ACAD_CAL_TYPE in VARCHAR2,
1413 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
1414 X_ADM_CAL_TYPE in VARCHAR2,
1415 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1416 X_ADMISSION_CAT in VARCHAR2,
1417 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
1418 X_ADM_APPL_STATUS in VARCHAR2,
1419 X_ADM_FEE_STATUS in VARCHAR2,
1420 X_TAC_APPL_IND in VARCHAR2,
1421 x_spcl_grp_1 IN NUMBER,
1422 x_spcl_grp_2 IN NUMBER,
1423 x_common_app IN VARCHAR2,
1424 x_application_type IN VARCHAR2,
1425 x_choice_number IN VARCHAR2,
1426 x_routeb_pref IN VARCHAR2,
1427 x_alt_appl_id IN VARCHAR2,
1428 x_appl_fee_amt IN NUMBER DEFAULT NULL
1429 ) AS
1430 cursor c1 is select
1431 APPL_DT,
1432 ACAD_CAL_TYPE,
1433 ACAD_CI_SEQUENCE_NUMBER,
1434 ADM_CAL_TYPE,
1435 ADM_CI_SEQUENCE_NUMBER,
1436 ADMISSION_CAT,
1437 S_ADMISSION_PROCESS_TYPE,
1438 ADM_APPL_STATUS,
1439 ADM_FEE_STATUS,
1440 TAC_APPL_IND,
1441 spcl_grp_1,
1442 spcl_grp_2,
1443 common_app,
1444 application_type,
1445 choice_number,
1446 routeb_pref,
1447 alt_appl_id,
1448 appl_fee_amt
1449 from IGS_AD_APPL_ALL
1450 where ROWID = X_ROWID
1451 for update nowait;
1452 tlinfo c1%rowtype;
1453
1454 begin
1455 open c1;
1456 fetch c1 into tlinfo;
1457 if (c1%notfound) then
1458 close c1;
1459 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1460 IGS_GE_MSG_STACK.ADD;
1461 app_exception.raise_exception;
1462 return;
1463 end if;
1464 close c1;
1465
1466 if ( (TRUNC(tlinfo.APPL_DT) = TRUNC(X_APPL_DT))
1467 AND (tlinfo.ACAD_CAL_TYPE = X_ACAD_CAL_TYPE)
1468 AND (tlinfo.ACAD_CI_SEQUENCE_NUMBER = X_ACAD_CI_SEQUENCE_NUMBER)
1469 AND (tlinfo.ADM_CAL_TYPE = X_ADM_CAL_TYPE)
1470 AND (tlinfo.ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER)
1471 AND (tlinfo.ADMISSION_CAT = X_ADMISSION_CAT)
1472 AND (tlinfo.S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE)
1473 AND (tlinfo.ADM_APPL_STATUS = X_ADM_APPL_STATUS)
1474 AND (tlinfo.ADM_FEE_STATUS = X_ADM_FEE_STATUS)
1475 AND (tlinfo.TAC_APPL_IND = X_TAC_APPL_IND)
1476 AND ((tlinfo.spcl_grp_1 = x_spcl_grp_1)
1477 OR ((tlinfo.spcl_grp_1 is null)
1478 AND (x_spcl_grp_1 is null)))
1479 AND ((tlinfo.spcl_grp_2 = x_spcl_grp_2)
1480 OR ((tlinfo.spcl_grp_2 is null)
1481 AND (x_spcl_grp_2 is null)))
1482 AND ((tlinfo.common_app = x_common_app)
1483 OR ((tlinfo.common_app is null)
1484 AND (x_common_app is null)))
1485 AND ((tlinfo.application_type = x_application_type)
1486 OR ((tlinfo.application_type IS NULL)
1487 AND (x_application_type IS NULL)))
1488 AND ((tlinfo.choice_number = x_choice_number)
1489 OR ((tlinfo.choice_number IS NULL)
1490 AND (x_choice_number IS NULL)))
1491 AND ((tlinfo.routeb_pref = x_routeb_pref)
1492 OR ((tlinfo.routeb_pref IS NULL)
1493 AND (x_routeb_pref IS NULL)))
1494 AND ((tlinfo.alt_appl_id = x_alt_appl_id)
1495 OR ((tlinfo.alt_appl_id IS NULL)
1496 AND (x_alt_appl_id IS NULL)))
1497 AND ((tlinfo.appl_fee_amt = x_appl_fee_amt)
1498 OR ((tlinfo.appl_fee_amt IS NULL)
1499 AND (x_appl_fee_amt IS NULL)))
1500 ) THEN
1501 NULL;
1502 ELSE
1503 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
1504 IGS_GE_MSG_STACK.ADD;
1505 APP_EXCEPTION.RAISE_EXCEPTION;
1506 end if;
1507 return;
1508 end LOCK_ROW;
1509
1510 procedure UPDATE_ROW (
1511 X_ROWID in VARCHAR2,
1512 X_PERSON_ID in NUMBER,
1513 X_ADMISSION_APPL_NUMBER in NUMBER,
1514 X_APPL_DT in DATE,
1515 X_ACAD_CAL_TYPE in VARCHAR2,
1516 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
1517 X_ADM_CAL_TYPE in VARCHAR2,
1518 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1519 X_ADMISSION_CAT in VARCHAR2,
1520 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
1521 X_ADM_APPL_STATUS in VARCHAR2,
1522 X_ADM_FEE_STATUS in VARCHAR2,
1523 X_TAC_APPL_IND in VARCHAR2,
1524 x_spcl_grp_1 IN NUMBER,
1525 x_spcl_grp_2 IN NUMBER,
1526 x_common_app IN VARCHAR2,
1527 x_application_type IN VARCHAR2,
1528 X_MODE IN VARCHAR2,
1529 x_choice_number IN VARCHAR2,
1530 x_routeb_pref IN VARCHAR2,
1531 x_alt_appl_id IN VARCHAR2,
1532 x_appl_fee_amt IN NUMBER DEFAULT NULL
1533 ) AS
1534 X_LAST_UPDATE_DATE DATE;
1535 X_LAST_UPDATED_BY NUMBER;
1536 X_LAST_UPDATE_LOGIN NUMBER;
1537 X_REQUEST_ID NUMBER;
1538 X_PROGRAM_ID NUMBER;
1539 X_PROGRAM_APPLICATION_ID NUMBER;
1540 X_PROGRAM_UPDATE_DATE DATE;
1541 begin
1542 X_LAST_UPDATE_DATE := SYSDATE;
1543 if(X_MODE = 'I') then
1544 X_LAST_UPDATED_BY := 1;
1545 X_LAST_UPDATE_LOGIN := 0;
1546 elsif (X_MODE IN ('R', 'S')) then
1547 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1548 if X_LAST_UPDATED_BY is NULL then
1549 X_LAST_UPDATED_BY := -1;
1550 end if;
1551 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1552 if X_LAST_UPDATE_LOGIN is NULL then
1553 X_LAST_UPDATE_LOGIN := -1;
1554 end if;
1555 else
1556 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1557 IGS_GE_MSG_STACK.ADD;
1558 app_exception.raise_exception;
1559 end if;
1560 Before_DML (
1561 p_action => 'UPDATE',
1562 x_rowid => X_ROWID,
1563 x_person_id => X_PERSON_ID,
1564 x_admission_appl_number => X_ADMISSION_APPL_NUMBER,
1565 x_appl_dt => X_APPL_DT,
1566 x_acad_cal_type => X_ACAD_CAL_TYPE,
1567 x_acad_ci_sequence_number => X_ACAD_CI_SEQUENCE_NUMBER,
1568 x_adm_cal_type => X_ADM_CAL_TYPE,
1569 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
1570 x_admission_cat => X_ADMISSION_CAT,
1571 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
1572 x_adm_appl_status => X_ADM_APPL_STATUS,
1573 x_adm_fee_status => X_ADM_FEE_STATUS,
1574 x_tac_appl_ind => X_TAC_APPL_IND,
1575 x_spcl_grp_1 => x_spcl_grp_1,
1576 x_spcl_grp_2 => x_spcl_grp_2,
1577 x_common_app => x_common_app,
1578 x_application_type => x_application_type,
1579 x_creation_date => X_LAST_UPDATE_DATE,
1580 x_created_by => X_LAST_UPDATED_BY,
1581 x_last_update_date => X_LAST_UPDATE_DATE,
1582 x_last_updated_by => X_LAST_UPDATED_BY,
1583 x_last_update_login => X_LAST_UPDATE_LOGIN,
1584 x_choice_number => x_choice_number,
1585 x_routeb_pref => x_routeb_pref,
1586 x_alt_appl_id => x_alt_appl_id,
1587 x_appl_fee_amt => x_appl_fee_amt
1588 );
1589
1590 if (X_MODE IN ('R', 'S')) then
1591 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1592 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1593 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1594 if (X_REQUEST_ID = -1) then
1595 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1596 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
1597 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1598 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1599 else
1600 X_PROGRAM_UPDATE_DATE := SYSDATE;
1601 end if;
1602 end if;
1603
1604 /* Removed the Commencement Period details (Acad and Adm calendars) from the update statement
1605 as they should not be allowed to update for an existing application. Bug: 2772337 */
1606 IF (x_mode = 'S') THEN
1607 igs_sc_gen_001.set_ctx('R');
1608 END IF;
1609 update IGS_AD_APPL_ALL set
1610 APPL_DT = NEW_REFERENCES.APPL_DT,
1611 ADM_APPL_STATUS = NEW_REFERENCES.ADM_APPL_STATUS,
1612 ADM_FEE_STATUS = NEW_REFERENCES.ADM_FEE_STATUS,
1613 TAC_APPL_IND = NEW_REFERENCES.TAC_APPL_IND,
1614 spcl_grp_1 = NEW_REFERENCES.spcl_grp_1,
1615 spcl_grp_2 = NEW_REFERENCES.spcl_grp_2,
1616 common_app = NEW_REFERENCES.common_app,
1617 application_type = NEW_REFERENCES.application_type,
1618 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1619 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1620 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1621 REQUEST_ID = X_REQUEST_ID,
1622 PROGRAM_ID = X_PROGRAM_ID,
1623 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1624 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1625 choice_number = new_references.choice_number,
1626 routeb_pref = new_references.routeb_pref,
1627 alt_appl_id = new_references.alt_appl_id,
1628 appl_fee_amt = new_references.appl_fee_amt
1629 where ROWID = X_ROWID
1630 ;
1631 if (sql%notfound) then
1632 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1633 igs_ge_msg_stack.add;
1634 IF (x_mode = 'S') THEN
1635 igs_sc_gen_001.unset_ctx('R');
1636 END IF;
1637 app_exception.raise_exception;
1638 end if;
1639 IF (x_mode = 'S') THEN
1640 igs_sc_gen_001.unset_ctx('R');
1641 END IF;
1642
1643 After_DML (
1644 p_action => 'UPDATE',
1645 x_rowid => X_ROWID
1646 );
1647 EXCEPTION
1648 WHEN OTHERS THEN
1649 IF (x_mode = 'S') THEN
1650 igs_sc_gen_001.unset_ctx('R');
1651 END IF;
1652 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1653 -- Code to handle Security Policy error raised
1654 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1655 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1656 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1657 -- that the ownerof policy function does not have privilege to access.
1658 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1659 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1660 IGS_GE_MSG_STACK.ADD;
1661 app_exception.raise_exception;
1662 ELSE
1663 RAISE;
1664 END IF;
1665 end UPDATE_ROW;
1666
1667 procedure ADD_ROW (
1668 X_ROWID in out NOCOPY VARCHAR2,
1669 X_ORG_ID in NUMBER,
1670 X_PERSON_ID in NUMBER,
1671 X_ADMISSION_APPL_NUMBER in NUMBER,
1672 X_APPL_DT in DATE,
1673 X_ACAD_CAL_TYPE in VARCHAR2,
1674 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
1675 X_ADM_CAL_TYPE in VARCHAR2,
1676 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1677 X_ADMISSION_CAT in VARCHAR2,
1678 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
1679 X_ADM_APPL_STATUS in VARCHAR2,
1680 X_ADM_FEE_STATUS in VARCHAR2,
1681 X_TAC_APPL_IND in VARCHAR2,
1682 x_spcl_grp_1 IN NUMBER,
1683 x_spcl_grp_2 IN NUMBER,
1684 x_common_app IN VARCHAR2,
1685 x_application_type IN VARCHAR2,
1686 X_MODE in VARCHAR2,
1687 x_choice_number IN VARCHAR2,
1688 x_routeb_pref IN VARCHAR2,
1689 x_alt_appl_id IN VARCHAR2,
1690 x_appl_fee_amt IN NUMBER DEFAULT NULL
1691 ) AS
1692 cursor c1 is select rowid from IGS_AD_APPL_ALL
1693 where PERSON_ID = X_PERSON_ID
1694 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
1695 ;
1696 begin
1697 open c1;
1698 fetch c1 into X_ROWID;
1699 if (c1%notfound) then
1700 close c1;
1701 INSERT_ROW (
1702 X_ROWID,
1703 X_ORG_ID,
1704 X_PERSON_ID,
1705 X_ADMISSION_APPL_NUMBER,
1706 X_APPL_DT,
1707 X_ACAD_CAL_TYPE,
1708 X_ACAD_CI_SEQUENCE_NUMBER,
1709 X_ADM_CAL_TYPE,
1710 X_ADM_CI_SEQUENCE_NUMBER,
1711 X_ADMISSION_CAT,
1712 X_S_ADMISSION_PROCESS_TYPE,
1713 X_ADM_APPL_STATUS,
1714 X_ADM_FEE_STATUS,
1715 X_TAC_APPL_IND,
1716 x_spcl_grp_1,
1717 x_spcl_grp_2,
1718 x_common_app,
1719 x_application_type,
1720 X_MODE,
1721 x_choice_number,
1722 x_routeb_pref,
1723 x_alt_appl_id,
1724 x_appl_fee_amt
1725 );
1726 return;
1727 end if;
1728 close c1;
1729 UPDATE_ROW (
1730 X_ROWID,
1731 X_PERSON_ID,
1732 X_ADMISSION_APPL_NUMBER,
1733 X_APPL_DT,
1734 X_ACAD_CAL_TYPE,
1735 X_ACAD_CI_SEQUENCE_NUMBER,
1736 X_ADM_CAL_TYPE,
1737 X_ADM_CI_SEQUENCE_NUMBER,
1738 X_ADMISSION_CAT,
1739 X_S_ADMISSION_PROCESS_TYPE,
1740 X_ADM_APPL_STATUS,
1741 X_ADM_FEE_STATUS,
1742 X_TAC_APPL_IND,
1743 x_spcl_grp_1,
1744 x_spcl_grp_2,
1745 x_common_app,
1746 x_application_type,
1747 X_MODE,
1748 x_choice_number,
1749 x_routeb_pref,
1750 x_alt_appl_id,
1751 x_appl_fee_amt
1752 );
1753 end ADD_ROW;
1754
1755 procedure DELETE_ROW (
1756 X_ROWID in VARCHAR2,
1757 x_mode IN VARCHAR2
1758 ) AS
1759 begin
1760 Before_DML (
1761 p_action => 'DELETE',
1762 x_rowid => X_ROWID
1763 );
1764
1765 IF (x_mode = 'S') THEN
1766 igs_sc_gen_001.set_ctx('R');
1767 END IF;
1768 delete from IGS_AD_APPL_ALL
1769 where ROWID = X_ROWID;
1770 if (sql%notfound) then
1771 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1772 igs_ge_msg_stack.add;
1773 IF (x_mode = 'S') THEN
1774 igs_sc_gen_001.unset_ctx('R');
1775 END IF;
1776 app_exception.raise_exception;
1777 end if;
1778 IF (x_mode = 'S') THEN
1779 igs_sc_gen_001.unset_ctx('R');
1780 END IF;
1781
1782 After_DML (
1783 p_action => 'DELETE',
1784 x_rowid => X_ROWID
1785 );
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788 IF (x_mode = 'S') THEN
1789 igs_sc_gen_001.unset_ctx('R');
1790 END IF;
1791 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1792 -- Code to handle Security Policy error raised
1793 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1794 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1795 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1796 -- that the ownerof policy function does not have privilege to access.
1797 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1798 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1799 IGS_GE_MSG_STACK.ADD;
1800 app_exception.raise_exception;
1801 ELSE
1802 RAISE;
1803 END IF;
1804 end DELETE_ROW;
1805
1806 end IGS_AD_APPL_PKG;