[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PS_APLINSTUNT_PKG
Source
1 package body IGS_AD_PS_APLINSTUNT_PKG as
2 /* $Header: IGSAI20B.pls 120.3 2005/10/03 08:19:56 appldev ship $*/
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_PS_APLINSTUNT_ALL%RowType;
5 new_references IGS_AD_PS_APLINSTUNT_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_admission_appl_number IN NUMBER DEFAULT NULL,
13 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
14 x_acai_sequence_number IN NUMBER DEFAULT NULL,
15 x_unit_cd IN VARCHAR2 DEFAULT NULL,
16 x_uv_version_number IN NUMBER DEFAULT NULL,
17 x_cal_type IN VARCHAR2 DEFAULT NULL,
18 x_ci_sequence_number IN NUMBER DEFAULT NULL,
19 x_location_cd IN VARCHAR2 DEFAULT NULL,
20 x_unit_class IN VARCHAR2 DEFAULT NULL,
21 x_unit_mode IN VARCHAR2 DEFAULT NULL,
22 x_adm_unit_outcome_status IN VARCHAR2 DEFAULT NULL,
23 x_ass_tracking_id IN NUMBER DEFAULT NULL,
24 x_rule_waived_dt IN DATE DEFAULT NULL,
25 x_rule_waived_person_id IN NUMBER DEFAULT NULL,
26 x_sup_unit_cd IN VARCHAR2 DEFAULT NULL,
27 x_sup_uv_version_number IN NUMBER DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL,
33 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL
34 ) AS
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_AD_PS_APLINSTUNT_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 Open cur_old_ref_values;
48 Fetch cur_old_ref_values INTO old_references;
49 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
50 Close cur_old_ref_values;
51 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
52 IGS_GE_MSG_STACK.ADD;
53 App_Exception.Raise_Exception;
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.org_id := x_org_id;
60 new_references.person_id := x_person_id;
61 new_references.admission_appl_number := x_admission_appl_number;
62 new_references.nominated_course_cd := x_nominated_course_cd;
63 new_references.acai_sequence_number := x_acai_sequence_number;
64 new_references.unit_cd := x_unit_cd;
65 new_references.uv_version_number := x_uv_version_number;
66 new_references.cal_type := x_cal_type;
67 new_references.ci_sequence_number := x_ci_sequence_number;
68 new_references.location_cd := x_location_cd;
69 new_references.unit_class:= x_unit_class;
70 new_references.unit_mode:= x_unit_mode;
71 new_references.adm_unit_outcome_status:= x_adm_unit_outcome_status;
72 new_references.ass_tracking_id := x_ass_tracking_id;
73 new_references.rule_waived_dt := TRUNC(x_rule_waived_dt);
74 new_references.rule_waived_person_id := x_rule_waived_person_id;
75 new_references.sup_unit_cd := x_sup_unit_cd;
76 new_references.sup_uv_version_number := x_sup_uv_version_number;
77 new_references.adm_ps_appl_inst_unit_id := x_adm_ps_appl_inst_unit_id;
78 IF (p_action = 'UPDATE') THEN
79 new_references.creation_date := old_references.creation_date;
80 new_references.created_by := old_references.created_by;
81 ELSE
82 new_references.creation_date := x_creation_date;
83 new_references.created_by := x_created_by;
84 END IF;
85 new_references.last_update_date := x_last_update_date;
86 new_references.last_updated_by := x_last_updated_by;
87 new_references.last_update_login := x_last_update_login;
88
89 END Set_Column_Values;
90
91 PROCEDURE BeforeRowInsertUpdateDelete1(
92 p_inserting IN BOOLEAN DEFAULT FALSE,
93 p_updating IN BOOLEAN DEFAULT FALSE,
94 p_deleting IN BOOLEAN DEFAULT FALSE
95 ) AS
96 v_message_name varchar2(30);
97 v_return_type VARCHAR2(1);
98 v_person_id IGS_AD_APPL.person_id%TYPE;
99 v_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
100 v_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE;
101 v_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE;
102 v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
103 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
104 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
105 v_aa_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
106 v_aa_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
107 v_aa_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
108 v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
109 v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
110 v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
111 v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
112 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
113 v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
114 v_acai_adm_cal_type IGS_AD_PS_APPL_INST.adm_cal_type%TYPE;
115 v_acai_adm_ci_sequence_number
116 IGS_AD_PS_APPL_INST.adm_ci_sequence_number%TYPE;
117 v_offer_ind VARCHAR2(1);
118 v_unit_encmb_chk_ind VARCHAR2(1);
119 v_unit_restr_ind VARCHAR2(1);
120 cst_error CONSTANT VARCHAR2(1):= 'E';
121 CURSOR c_apcs (
122 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
123 cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
124 SELECT s_admission_step_type
125 FROM IGS_AD_PRCS_CAT_STEP
126 WHERE admission_cat = cp_admission_cat AND
127 s_admission_process_type = cp_s_admission_process_type AND
128 step_group_type <> 'TRACK'; --2402377
129 CURSOR c_acai (
130 cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
131 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
132 cp_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
133 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE) IS
134 SELECT adm_cal_type,
135 adm_ci_sequence_number,
136 course_cd
137 FROM IGS_AD_PS_APPL_INST
138 WHERE person_id = cp_person_id AND
139 admission_appl_number = cp_admission_appl_number AND
140 nominated_course_cd = cp_nominated_course_cd AND
141 sequence_number = cp_sequence_number;
142 v_acai_rec c_acai%ROWTYPE;
143 BEGIN
144 v_offer_ind := 'N';
145 v_unit_encmb_chk_ind := 'N';
146 v_unit_restr_ind := 'N';
147
148 IF p_inserting OR p_updating OR p_deleting THEN
149 IF p_deleting THEN
150 v_person_id := old_references.person_id;
151 v_admission_appl_number := old_references.admission_appl_number;
152 v_nominated_course_cd := old_references.nominated_course_cd;
153 v_acai_sequence_number := old_references.acai_sequence_number;
154 ELSE
155 v_person_id := new_references.person_id;
156 v_admission_appl_number := new_references.admission_appl_number;
157 v_nominated_course_cd := new_references.nominated_course_cd;
158 v_acai_sequence_number := new_references.acai_sequence_number;
159 END IF;
160 -- Get admission application details required for validation.
161 IGS_AD_GEN_002.ADMP_GET_AA_DTL(
162 v_person_id,
163 v_admission_appl_number,
164 v_admission_cat,
165 v_s_admission_process_type,
166 v_acad_cal_type,
167 v_aa_acad_ci_sequence_number,
168 v_aa_adm_cal_type,
169 v_aa_adm_ci_sequence_number,
170 v_appl_dt,
171 v_adm_appl_status,
172 v_adm_fee_status);
173 FOR v_apcs_rec IN c_apcs (
174 v_admission_cat,
175 v_s_admission_process_type)
176 LOOP
177 IF v_apcs_rec.s_admission_step_type = 'CHKUENCUMB' THEN
178 v_unit_encmb_chk_ind := 'Y';
179 ELSIF v_apcs_rec.s_admission_step_type = 'UNIT-RESTR' THEN
180 v_unit_restr_ind := 'Y';
181 END IF;
182 END LOOP;
183 -- Validate inserts,updates,deletes
184 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_iud(
185 v_person_id,
186 v_admission_appl_number,
187 v_nominated_course_cd,
188 v_acai_sequence_number,
189 v_unit_restr_ind,
190 v_message_name) = FALSE THEN
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 IF p_inserting OR p_updating THEN
197 -- Determine the Academic and Admission period for validation.
198 OPEN c_acai (
199 new_references.person_id,
200 new_references.admission_appl_number,
201 new_references.nominated_course_cd,
202 new_references.acai_sequence_number);
203 FETCH c_acai INTO v_acai_rec;
204 CLOSE c_acai;
205 IF v_acai_rec.adm_cal_type IS NULL THEN
206 v_acad_ci_sequence_number := v_aa_acad_ci_sequence_number;
207 v_adm_cal_type := v_aa_adm_cal_type;
208 v_adm_ci_sequence_number := v_aa_adm_ci_sequence_number;
209 ELSE
210 v_acad_ci_sequence_number := IGS_CA_GEN_001.CALP_GET_SUP_INST (
211 v_acad_cal_type,
212 v_acai_rec.adm_cal_type,
213 v_acai_rec.adm_ci_sequence_number);
214 v_adm_cal_type := v_acai_rec.adm_cal_type;
215 v_adm_ci_sequence_number := v_acai_rec.adm_ci_sequence_number;
216 END IF;
217 -- Determine the offer indicator.
218 IF NVL(IGS_AD_GEN_008.ADMP_GET_SAUOS(new_references.adm_unit_outcome_status), 'NONE') = 'OFFER' THEN
219 v_offer_ind := 'Y';
220 END IF;
221 END IF;
222 IF p_inserting THEN
223 -- Validate the unit code.
224 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_unit (
225 new_references.unit_cd,
226 new_references.uv_version_number,
227 v_adm_cal_type,
228 v_adm_ci_sequence_number,
229 v_acad_cal_type,
230 v_acad_ci_sequence_number,
231 v_s_admission_process_type,
232 v_offer_ind,
233 v_message_name) = FALSE THEN
234 Fnd_Message.Set_Name('IGS',v_message_name);
235 IGS_GE_MSG_STACK.ADD;
236 App_Exception.Raise_Exception;
237 END IF;
238 -- Validate a research unit.
239 IF IGS_AD_VAL_ACAIU.admp_val_res_unit (
240 new_references.person_id,
241 new_references.admission_appl_number,
242 new_references.nominated_course_cd,
243 new_references.acai_sequence_number,
244 new_references.unit_cd,
245 new_references.uv_version_number,
246 v_acai_rec.course_cd,
247 v_offer_ind,
248 v_s_admission_process_type,
249 v_message_name,
250 v_return_type) = FALSE THEN
251 IF v_return_type = cst_error THEN
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 -- Validate unit encumbrances.
258 IF v_unit_encmb_chk_ind = 'Y' THEN
259 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_encmb (
260 new_references.person_id,
261 v_acai_rec.course_cd,
262 new_references.unit_cd,
263 v_adm_cal_type,
264 v_adm_ci_sequence_number,
265 v_unit_encmb_chk_ind,
266 v_offer_ind,
267 v_message_name,
268 v_return_type) = FALSE THEN
269 IF NVL(v_return_type, '-1') = cst_error THEN
270 Fnd_Message.Set_Name('IGS',v_message_name);
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 END IF;
274 END IF;
275 END IF;
276 END IF;
277 IF p_inserting OR p_updating THEN
278 -- Validate admission unit outcome status
279 IF NVL(old_references.adm_unit_outcome_status, '-1') <>
280 new_references.adm_unit_outcome_status THEN
281 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_auos (
282 new_references.person_id,
283 new_references.admission_appl_number,
284 new_references.nominated_course_cd,
285 new_references.acai_sequence_number,
286 new_references.adm_unit_outcome_status,
287 v_message_name) = FALSE THEN
288 Fnd_Message.Set_Name('IGS',v_message_name);
289 IGS_GE_MSG_STACK.ADD;
290 App_Exception.Raise_Exception;
291 END IF;
292 IF v_offer_ind = 'Y' THEN
293 -- Validate that unit version is active
294 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_uv(
295 new_references.unit_cd,
296 new_references.uv_version_number,
297 v_s_admission_process_type,
298 v_offer_ind,
299 v_message_name) = FALSE THEN
300 Fnd_Message.Set_Name('IGS',v_message_name);
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303 END IF;
304 -- Validate a research unit.
305 IF IGS_AD_VAL_ACAIU.admp_val_res_unit (
306 new_references.person_id,
307 new_references.admission_appl_number,
308 new_references.nominated_course_cd,
309 new_references.acai_sequence_number,
310 new_references.unit_cd,
311 new_references.uv_version_number,
312 v_acai_rec.course_cd,
313 v_offer_ind,
314 v_s_admission_process_type,
315 v_message_name,
316 v_return_type) = FALSE THEN
317 IF v_return_type = cst_error THEN
318 Fnd_Message.Set_Name('IGS',v_message_name);
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 END IF;
322 END IF;
323 END IF;
324 END IF;
325 -- Validate unit mode.
326 IF (NVL(old_references.unit_mode, '-1') <> NVL(new_references.unit_mode, '-1')) THEN
327 -- As part of the bug# 1956374 changed to the below call from IGS_AD_VAL_ACAIU.crsp_val_um_closed
328 IF IGS_AS_VAL_UAI.crsp_val_um_closed (
329 new_references.unit_mode,
330 v_message_name) = FALSE THEN
331 Fnd_Message.Set_Name('IGS',v_message_name);
332 IGS_GE_MSG_STACK.ADD;
333 App_Exception.Raise_Exception;
334 END IF;
335 END IF;
336 IF (NVL(old_references.unit_mode, '-1') <> NVL(new_references.unit_mode, '-1')) OR
337 (NVL(old_references.unit_class, '-1') <> NVL(new_references.unit_class, '-1')) THEN
338 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_um (
339 new_references.unit_class,
340 new_references.unit_mode,
341 v_message_name) = FALSE THEN
342 Fnd_Message.Set_Name('IGS',v_message_name);
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 END IF;
347 -- Validate the options of the admission course application unit
348 IF (NVL(old_references.uv_version_number, -1) <> new_references.uv_version_number) OR
349 (NVL(old_references.cal_type, '-1') <> NVL(new_references.cal_type, '-1')) OR
350 (NVL(old_references.ci_sequence_number, -1) <> NVL(new_references.ci_sequence_number, -1)) OR
351 (NVL(old_references.location_cd, '-1') <> NVL(new_references.location_cd, '-1')) OR
352 (NVL(old_references.unit_class, '-1') <> NVL(new_references.unit_class, '-1')) OR
353 (NVL(old_references.unit_mode, '-1') <> NVL(new_references.unit_mode, '-1')) OR
354 ((NVL(old_references.adm_unit_outcome_status, '-1') <>
355 new_references.adm_unit_outcome_status) AND
356 v_offer_ind = 'Y') THEN
357 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_opt (
358 new_references.unit_cd,
359 new_references.uv_version_number,
360 new_references.cal_type,
361 new_references.ci_sequence_number,
362 new_references.location_cd,
363 new_references.unit_class,
364 new_references.unit_mode,
365 v_adm_cal_type,
366 v_adm_ci_sequence_number,
367 v_acad_cal_type,
368 v_acad_ci_sequence_number,
369 v_offer_ind,
370 v_message_name) = FALSE THEN
371 Fnd_Message.Set_Name('IGS',v_message_name);
372 IGS_GE_MSG_STACK.ADD;
373 App_Exception.Raise_Exception;
374 END IF;
375 END IF;
376 -- Validate the unit version against the teaching period.
377 IF (NVL(old_references.uv_version_number, -1) <> new_references.uv_version_number) OR
378 (NVL(old_references.cal_type, '-1') <> NVL(new_references.cal_type, '-1')) OR
379 (NVL(old_references.ci_sequence_number, -1) <> NVL(new_references.ci_sequence_number, -1)) THEN
380 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_uv_ci (
381 new_references.unit_cd,
382 new_references.uv_version_number,
383 new_references.cal_type,
384 new_references.ci_sequence_number,
385 v_message_name) = FALSE THEN
386 Fnd_Message.Set_Name('IGS',v_message_name);
387 IGS_GE_MSG_STACK.ADD;
388 App_Exception.Raise_Exception;
389 END IF;
390 END IF;
391 END IF;
392
393
394 END BeforeRowInsertUpdateDelete1;
395
396 PROCEDURE AfterRowInsertUpdate2(
397 p_inserting IN BOOLEAN DEFAULT FALSE,
398 p_updating IN BOOLEAN DEFAULT FALSE,
399 p_deleting IN BOOLEAN DEFAULT FALSE
400 ) AS
401 v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
402 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
403 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
404 v_aa_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
405 v_aa_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
406 v_aa_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
407 v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
408 v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
409 v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
410 v_appl_dt DATE;
411 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
412 v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
413 v_unit_restriction_ind VARCHAR2(1);
414 v_unit_restriction_num NUMBER;
415 v_message_name varchar2(30);
416 CURSOR c_apcs (
417 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
418 cp_s_admission_process_type
419 IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
420 SELECT s_admission_step_type,
421 step_type_restriction_num
422 FROM IGS_AD_PRCS_CAT_STEP
423 WHERE admission_cat = cp_admission_cat AND
424 s_admission_process_type = cp_s_admission_process_type AND
425 step_group_type <> 'TRACK'; --2402377
426 BEGIN
427 v_unit_restriction_ind := 'N';
428 IF p_inserting OR p_updating THEN
429 IF p_inserting THEN
430 --
431 -- Get admission application details required for validation.
432 --
433 IGS_AD_GEN_002.ADMP_GET_AA_DTL(
434 new_references.person_id,
435 new_references.admission_appl_number,
436 v_admission_cat,
437 v_s_admission_process_type,
438 v_acad_cal_type,
439 v_aa_acad_ci_sequence_number,
440 v_aa_adm_cal_type,
441 v_aa_adm_ci_sequence_number,
442 v_appl_dt,
443 v_adm_appl_status,
444 v_adm_fee_status);
445 --
446 -- Determine the admission process category steps.
447 --
448 FOR v_apcs_rec IN c_apcs (
449 v_admission_cat,
450 v_s_admission_process_type)
451 LOOP
452 IF v_apcs_rec.s_admission_step_type = 'UNIT-RESTR' THEN
453 v_unit_restriction_num := v_apcs_rec.step_type_restriction_num;
454 END IF;
455 END LOOP;
456 END IF;
457 IF p_inserting THEN
458 -- Validate restriction number of admission course application instance unit.
459 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_restr (
460 new_references.person_id,
461 new_references.admission_appl_number,
462 new_references.nominated_course_cd,
463 new_references.acai_sequence_number,
464 new_references.unit_cd,
465 v_unit_restriction_num,
466 v_message_name,
467 new_references.uv_version_number,
468 new_references.cal_type,
469 new_references.ci_sequence_number,
470 new_references.location_cd,
471 new_references.unit_class ) = FALSE THEN
472 Fnd_Message.Set_Name('IGS',v_message_name);
473 Fnd_Message.Set_Token('UNIT_RSTR_NUM',IGS_GE_NUMBER.TO_CANN(v_unit_restriction_num));
474 IGS_GE_MSG_STACK.ADD;
475 App_Exception.Raise_Exception;
476 END IF;
477 END IF;
478 -- Save the rowid of the current row.
479 END IF;
480 END AfterRowInsertUpdate2;
481
482 -- Trigger description :-
483 -- "OSS_TST".trg_acaiu_ar_ud_hist
484 -- AFTER DELETE OR UPDATE
485 -- ON IGS_AD_PS_APLINSTUNT
486 -- FOR EACH ROW
487
488 PROCEDURE AfterRowUpdateDelete3(
489 p_inserting IN BOOLEAN DEFAULT FALSE,
490 p_updating IN BOOLEAN DEFAULT FALSE,
491 p_deleting IN BOOLEAN DEFAULT FALSE
492 ) AS
493 v_message_name varchar2(30);
494 CURSOR c_acaiuh IS
495 SELECT rowid,acaiuh.*
496 FROM IGS_AD_PS_APINTUNTHS acaiuh
497 WHERE acaiuh.adm_ps_appl_inst_unit_id = old_references.adm_ps_appl_inst_unit_id
498 FOR UPDATE OF acaiuh.person_id NOWAIT;
499 v_acaiuh_rec c_acaiuh%ROWTYPE;
500 BEGIN
501 IF p_updating THEN
502 -- Create admission course application instance unit history record.
503 IGS_AD_GEN_010.ADMP_INS_ACAIU_HIST (
504 new_references.person_id,
505 new_references.admission_appl_number,
506 new_references.nominated_course_cd,
507 new_references.acai_sequence_number,
508 new_references.unit_cd,
509 new_references.adm_ps_appl_inst_unit_id,
510 new_references.uv_version_number,
511 old_references.uv_version_number,
512 new_references.cal_type,
513 old_references.cal_type,
514 new_references.ci_sequence_number,
515 old_references.ci_sequence_number,
516 new_references.location_cd,
517 old_references.location_cd,
518 new_references.unit_class,
519 old_references.unit_class,
520 new_references.unit_mode,
521 old_references.unit_mode,
522 new_references.adm_unit_outcome_status,
523 old_references.adm_unit_outcome_status,
524 new_references.ass_tracking_id,
525 old_references.ass_tracking_id,
526 TRUNC(new_references.rule_waived_dt),
527 TRUNC(old_references.rule_waived_dt),
528 new_references.rule_waived_person_id,
529 old_references.rule_waived_person_id,
530 new_references.sup_unit_cd,
531 old_references.sup_unit_cd,
532 new_references.sup_uv_version_number,
533 old_references.sup_uv_version_number,
534 new_references.last_updated_by,
535 old_references.last_updated_by,
536 new_references.last_update_date,
537 old_references.last_update_date);
538 END IF;
539 IF p_deleting THEN
540 -- Delete admission course application instance unit history records.
541
542 FOR v_acaiuh_rec IN c_acaiuh LOOP
543
544 IGS_AD_PS_APINTUNTHS_PKG.DELETE_ROW (
545 X_ROWID => v_acaiuh_rec.rowid );
546
547 END LOOP;
548
549 END IF;
550
551
552 END AfterRowUpdateDelete3;
553
554 PROCEDURE Check_Parent_Existance AS
555 BEGIN
556
557 IF (((old_references.person_id = new_references.person_id) AND
558 (old_references.admission_appl_number = new_references.admission_appl_number) AND
559 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
560 (old_references.acai_sequence_number = new_references.acai_sequence_number)) OR
561 ((new_references.person_id IS NULL) OR
562 (new_references.admission_appl_number IS NULL) OR
563 (new_references.nominated_course_cd IS NULL) OR
564 (new_references.acai_sequence_number IS NULL))) THEN
565 NULL;
566 ELSE
567
568 IF NOT IGS_AD_PS_APPL_INST_PKG.Get_PK_For_Validation (
569 new_references.person_id,
570 new_references.admission_appl_number,
571 new_references.nominated_course_cd,
572 new_references.acai_sequence_number
573 ) THEN
574 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
575 IGS_GE_MSG_STACK.ADD;
576 App_Exception.Raise_Exception;
577 END IF;
578 END IF;
579
580 IF (((old_references.adm_unit_outcome_status = new_references.adm_unit_outcome_status)) OR
581 ((new_references.adm_unit_outcome_status IS NULL))) THEN
582 NULL;
583 ELSE
584 IF NOT IGS_AD_UNIT_OU_STAT_PKG.Get_PK_For_Validation (
585 new_references.adm_unit_outcome_status , 'N'
586 ) THEN
587 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
588 IGS_GE_MSG_STACK.ADD;
589 App_Exception.Raise_Exception;
590 END IF;
591 END IF;
592
593 IF (((old_references.location_cd = new_references.location_cd)) OR
594 ((new_references.location_cd IS NULL))) THEN
595 NULL;
596 ELSE
597 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
598 new_references.location_cd , 'N'
599 ) THEN
600 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
601 IGS_GE_MSG_STACK.ADD;
602 App_Exception.Raise_Exception;
603 END IF;
604 END IF;
605
606 IF (((old_references.rule_waived_person_id = new_references.rule_waived_person_id)) OR
607 ((new_references.rule_waived_person_id IS NULL))) THEN
608 NULL;
609 ELSE
610 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
611 new_references.rule_waived_person_id
612 ) THEN
613 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
614 IGS_GE_MSG_STACK.ADD;
615 App_Exception.Raise_Exception;
616 END IF;
617 END IF;
618
619 IF (((old_references.sup_unit_cd = new_references.sup_unit_cd) AND
620 (old_references.sup_uv_version_number = new_references.sup_uv_version_number)) OR
621 ((new_references.sup_unit_cd IS NULL) OR
622 (new_references.sup_uv_version_number IS NULL))) THEN
623 NULL;
624 ELSE
625 IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
626 new_references.sup_unit_cd,
627 new_references.sup_uv_version_number
628 ) THEN
629 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
630 IGS_GE_MSG_STACK.ADD;
631 App_Exception.Raise_Exception;
632 END IF;
633 END IF;
634
635 IF (((old_references.ass_tracking_id = new_references.ass_tracking_id)) OR
636 ((new_references.ass_tracking_id IS NULL))) THEN
637 NULL;
638 ELSE
639 IF NOT IGS_TR_ITEM_PKG.Get_PK_For_Validation (
640 new_references.ass_tracking_id
641 ) THEN
642 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
643 IGS_GE_MSG_STACK.ADD;
644 App_Exception.Raise_Exception;
645 END IF;
646 END IF;
647
648 IF (((old_references.unit_class = new_references.unit_class)) OR
649 ((new_references.unit_class IS NULL))) THEN
650 NULL;
651 ELSE
652 IF NOT IGS_AS_UNIT_CLASS_PKG.Get_PK_For_Validation (
653 new_references.unit_class
654 ) THEN
655 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
656 IGS_GE_MSG_STACK.ADD;
657 App_Exception.Raise_Exception;
658 END IF;
659 END IF;
660
661 IF (((old_references.unit_mode= new_references.unit_mode)) OR
662 ((new_references.unit_mode IS NULL))) THEN
663 NULL;
664 ELSE
665 IF NOT IGS_AS_UNIT_MODE_PKG.Get_PK_For_Validation (
666 new_references.unit_mode
667 ) THEN
668 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
669 IGS_GE_MSG_STACK.ADD;
670 App_Exception.Raise_Exception;
671 END IF;
672 END IF;
673
674 IF (((old_references.unit_cd = new_references.unit_cd) AND
675 (old_references.uv_version_number = new_references.uv_version_number) AND
676 (old_references.cal_type = new_references.cal_type) AND
677 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
678 (old_references.location_cd = new_references.location_cd) AND
679 (old_references.unit_class = new_references.unit_class)) OR
680 ((new_references.unit_cd IS NULL) OR
681 (new_references.uv_version_number IS NULL) OR
682 (new_references.cal_type IS NULL) OR
683 (new_references.ci_sequence_number IS NULL) OR
684 (new_references.location_cd IS NULL) OR
685 (new_references.unit_class IS NULL))) THEN
686 NULL;
687 ELSE
688 IF NOT IGS_PS_UNIT_OFR_OPT_PKG.Get_PK_For_Validation (
689 new_references.unit_cd,
690 new_references.uv_version_number,
691 new_references.cal_type,
692 new_references.ci_sequence_number,
693 new_references.location_cd,
694 new_references.unit_class
695 ) THEN
696 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
697 IGS_GE_MSG_STACK.ADD;
698 App_Exception.Raise_Exception;
699 END IF;
700 END IF;
701
702 IF (((old_references.unit_cd = new_references.unit_cd) AND
703 (old_references.uv_version_number = new_references.uv_version_number) AND
704 (old_references.cal_type = new_references.cal_type) AND
705 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
706 ((new_references.unit_cd IS NULL) OR
707 (new_references.uv_version_number IS NULL) OR
708 (new_references.cal_type IS NULL) OR
709 (new_references.ci_sequence_number IS NULL))) THEN
710 NULL;
711 ELSE
712 IF NOT IGS_PS_UNIT_OFR_PAT_PKG.Get_PK_For_Validation (
713 new_references.unit_cd,
714 new_references.uv_version_number,
715 new_references.cal_type,
716 new_references.ci_sequence_number
717 ) THEN
718 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
719 IGS_GE_MSG_STACK.ADD;
720 App_Exception.Raise_Exception;
721 END IF;
722 END IF;
723
724 END Check_Parent_Existance;
725
726 PROCEDURE Check_Constraints (
727 Column_Name IN VARCHAR2 DEFAULT NULL,
728 Column_Value IN VARCHAR2 DEFAULT NULL
729 )
730 AS
731 BEGIN
732 IF column_name is null then
733 NULL;
734 ELSIF upper(Column_name) = 'ADM_UNIT_OUTCOME_STATUS' then
735 new_references.adm_unit_outcome_status := column_value;
736 ELSIF upper(Column_name) = 'CAL_TYPE' then
737 new_references.cal_type := column_value;
738 ELSIF upper(Column_name) = 'LOCATION_CD' then
739 new_references.location_cd := column_value;
740 ELSIF upper(Column_name) = 'UNIT_CD' then
741 new_references.unit_cd := column_value;
742 ELSIF upper(Column_name) = 'UNIT_CLASS' then
743 new_references.unit_class := column_value;
744 ELSIF upper(Column_name) = 'UNIT_MODE' then
745 new_references.unit_mode := column_value;
746 ELSIF upper(Column_name) = 'SUP_UNIT_CD' then
747 new_references.sup_unit_cd := column_value;
748 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
749 new_references.ci_sequence_number := igs_ge_number.to_num(column_value);
750 END IF;
751
752 IF upper(column_name) = 'ADM_UNIT_OUTCOME_STATUS' OR
753 column_name is null Then
754 IF new_references.adm_unit_outcome_status <> UPPER(new_references.adm_unit_outcome_status) Then
755 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
756 IGS_GE_MSG_STACK.ADD;
757 App_Exception.Raise_Exception;
758 END IF;
759 END IF;
760
761 IF upper(column_name) = 'CAL_TYPE' OR
762 column_name is null Then
763 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
764 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
765 IGS_GE_MSG_STACK.ADD;
766 App_Exception.Raise_Exception;
767 END IF;
768 END IF;
769
770 IF upper(column_name) = 'LOCATION_CD' OR
771 column_name is null Then
772 IF new_references.location_cd <> UPPER(new_references.location_cd) Then
773 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
774 IGS_GE_MSG_STACK.ADD;
775 App_Exception.Raise_Exception;
776 END IF;
777 END IF;
778
779 IF upper(column_name) = 'UNIT_CD' OR
780 column_name is null Then
781 IF new_references.unit_cd <> UPPER(new_references.unit_cd) Then
782 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
783 IGS_GE_MSG_STACK.ADD;
784 App_Exception.Raise_Exception;
785 END IF;
786 END IF;
787
788 IF upper(column_name) = 'UNIT_CLASS' OR
789 column_name is null Then
790 IF new_references.unit_class <> UPPER(new_references.unit_class) Then
791 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
792 IGS_GE_MSG_STACK.ADD;
793 App_Exception.Raise_Exception;
794 END IF;
795 END IF;
796
797 IF upper(column_name) = 'UNIT_MODE' OR
798 column_name is null Then
799 IF new_references.unit_mode <> UPPER(new_references.unit_mode) Then
800 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
801 IGS_GE_MSG_STACK.ADD;
802 App_Exception.Raise_Exception;
803 END IF;
804 END IF;
805
806 IF upper(column_name) = 'SUP_UNIT_CD' OR
807 column_name is null Then
808 IF new_references.sup_unit_cd <> UPPER(new_references.sup_unit_cd) Then
809 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
810 IGS_GE_MSG_STACK.ADD;
811 App_Exception.Raise_Exception;
812 END IF;
813 END IF;
814
815 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
816 column_name is null Then
817 IF new_references.ci_sequence_number < 1 OR
818 new_references.ci_sequence_number > 999999 Then
819 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
820 IGS_GE_MSG_STACK.ADD;
821 App_Exception.Raise_Exception;
822 END IF;
823 END IF;
824
825 END Check_Constraints;
826
827 FUNCTION Get_PK_For_Validation (
828 x_adm_ps_appl_inst_unit_id IN NUMBER
829 )
830 RETURN BOOLEAN
831 AS
832
833 CURSOR cur_rowid IS
834 SELECT rowid
835 FROM IGS_AD_PS_APLINSTUNT_ALL
836 WHERE adm_ps_appl_inst_unit_id = x_adm_ps_appl_inst_unit_id
837 FOR UPDATE NOWAIT;
838
839 lv_rowid cur_rowid%RowType;
840
841 BEGIN
842 Open cur_rowid;
843 Fetch cur_rowid INTO lv_rowid;
844 IF (cur_rowid%FOUND) THEN
845 Close cur_rowid;
846 Return (TRUE);
847 ELSE
848 Close cur_rowid;
849 Return (FALSE);
850 END IF;
851 END Get_PK_For_Validation;
852
853 FUNCTION Get_UK_For_Validation (
854 x_person_id IN NUMBER,
855 x_admission_appl_number IN NUMBER,
856 x_nominated_course_cd IN VARCHAR2,
857 x_acai_sequence_number IN NUMBER,
858 x_unit_cd IN VARCHAR2,
859 x_uv_version_number IN NUMBER ,
860 x_cal_type IN VARCHAR2 ,
861 x_ci_sequence_number IN NUMBER ,
862 x_location_cd IN VARCHAR2 ,
863 x_unit_class IN VARCHAR2
864 )
865 RETURN BOOLEAN
866 AS
867
868 CURSOR cur_rowid IS
869 SELECT rowid
870 FROM IGS_AD_PS_APLINSTUNT_ALL
871 WHERE person_id = x_person_id
872 AND admission_appl_number = x_admission_appl_number
873 AND nominated_course_cd = x_nominated_course_cd
874 AND acai_sequence_number = x_acai_sequence_number
875 AND unit_cd = x_unit_cd
876 AND uv_version_number = x_uv_version_number
877 AND NVL(cal_type,'*') = NVL(x_cal_type,'*')
878 AND NVL(ci_sequence_number,-1) = NVL(x_ci_sequence_number,-1)
879 AND NVL(location_cd,'*') = NVL(x_location_cd,'*')
880 AND NVL(unit_class,'*') = NVL(x_unit_class,'*')
881 AND (l_rowid IS NULL OR rowid <> l_rowid)
882 FOR UPDATE NOWAIT;
883
884 lv_rowid cur_rowid%RowType;
885
886 BEGIN
887
888 Open cur_rowid;
889 Fetch cur_rowid INTO lv_rowid;
890 IF (cur_rowid%FOUND) THEN
891 Close cur_rowid;
892 Return (TRUE);
893 ELSE
894 Close cur_rowid;
895 Return (FALSE);
896 END IF;
897 END Get_UK_For_Validation;
898
899 PROCEDURE GET_FK_IGS_AD_PS_APPL_INST (
900 x_person_id IN NUMBER,
901 x_admission_appl_number IN NUMBER,
902 x_nominated_course_cd IN VARCHAR2,
903 x_sequence_number IN NUMBER
904 ) AS
905
906 CURSOR cur_rowid IS
907 SELECT rowid
908 FROM IGS_AD_PS_APLINSTUNT_ALL
909 WHERE person_id = x_person_id
910 AND admission_appl_number = x_admission_appl_number
911 AND nominated_course_cd = x_nominated_course_cd
912 AND acai_sequence_number = x_sequence_number ;
913
914 lv_rowid cur_rowid%RowType;
915
916 BEGIN
917
918 Open cur_rowid;
919 Fetch cur_rowid INTO lv_rowid;
920 IF (cur_rowid%FOUND) THEN
921 Close cur_rowid;
922 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_ACAI_FK');
923 IGS_GE_MSG_STACK.ADD;
924 App_Exception.Raise_Exception;
925 Return;
926 END IF;
927 Close cur_rowid;
928
929 END GET_FK_IGS_AD_PS_APPL_INST;
930
931 PROCEDURE GET_FK_IGS_AD_UNIT_OU_STAT (
932 x_adm_unit_outcome_status IN VARCHAR2
933 ) AS
934
935 CURSOR cur_rowid IS
936 SELECT rowid
937 FROM IGS_AD_PS_APLINSTUNT_ALL
938 WHERE adm_unit_outcome_status = x_adm_unit_outcome_status ;
939
940 lv_rowid cur_rowid%RowType;
941
942 BEGIN
943
944 Open cur_rowid;
945 Fetch cur_rowid INTO lv_rowid;
946 IF (cur_rowid%FOUND) THEN
947 Close cur_rowid;
948 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_AUOS_FK');
949 IGS_GE_MSG_STACK.ADD;
950 App_Exception.Raise_Exception;
951 Return;
952 END IF;
953 Close cur_rowid;
954
955 END GET_FK_IGS_AD_UNIT_OU_STAT;
956
957 PROCEDURE GET_FK_IGS_AD_LOCATION (
958 x_location_cd IN VARCHAR2
959 ) AS
960
961 CURSOR cur_rowid IS
962 SELECT rowid
963 FROM IGS_AD_PS_APLINSTUNT_ALL
964 WHERE location_cd = x_location_cd ;
965
966 lv_rowid cur_rowid%RowType;
967
968 BEGIN
969
970 Open cur_rowid;
971 Fetch cur_rowid INTO lv_rowid;
972 IF (cur_rowid%FOUND) THEN
973 Close cur_rowid;
974 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_LOC_FK');
975 IGS_GE_MSG_STACK.ADD;
976 App_Exception.Raise_Exception;
977 Return;
978 END IF;
979 Close cur_rowid;
980
981 END GET_FK_IGS_AD_LOCATION;
982
983 PROCEDURE GET_FK_IGS_PE_PERSON (
984 x_person_id IN NUMBER
985 ) AS
986
987 CURSOR cur_rowid IS
988 SELECT rowid
989 FROM IGS_AD_PS_APLINSTUNT_ALL
990 WHERE rule_waived_person_id = x_person_id ;
991
992 lv_rowid cur_rowid%RowType;
993
994 BEGIN
995
996 Open cur_rowid;
997 Fetch cur_rowid INTO lv_rowid;
998 IF (cur_rowid%FOUND) THEN
999 Close cur_rowid;
1000 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_PE_FK');
1001 IGS_GE_MSG_STACK.ADD;
1002 App_Exception.Raise_Exception;
1003 Return;
1004 END IF;
1005 Close cur_rowid;
1006
1007 END GET_FK_IGS_PE_PERSON;
1008
1009 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
1010 x_unit_cd IN VARCHAR2,
1011 x_version_number IN NUMBER
1012 ) AS
1013
1014 CURSOR cur_rowid IS
1015 SELECT rowid
1016 FROM IGS_AD_PS_APLINSTUNT_ALL
1017 WHERE sup_unit_cd = x_unit_cd
1018 AND sup_uv_version_number = x_version_number ;
1019
1020 lv_rowid cur_rowid%RowType;
1021
1022 BEGIN
1023
1024 Open cur_rowid;
1025 Fetch cur_rowid INTO lv_rowid;
1026 IF (cur_rowid%FOUND) THEN
1027 Close cur_rowid;
1028 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_SUP_UV_FK');
1029 IGS_GE_MSG_STACK.ADD;
1030 App_Exception.Raise_Exception;
1031 Return;
1032 END IF;
1033 Close cur_rowid;
1034
1035 END GET_FK_IGS_PS_UNIT_VER;
1036
1037 PROCEDURE GET_FK_IGS_TR_ITEM (
1038 x_tracking_id IN VARCHAR2
1039 ) AS
1040
1041 CURSOR cur_rowid IS
1042 SELECT rowid
1043 FROM IGS_AD_PS_APLINSTUNT_ALL
1044 WHERE ass_tracking_id = x_tracking_id ;
1045
1046 lv_rowid cur_rowid%RowType;
1047
1048 BEGIN
1049
1050 Open cur_rowid;
1051 Fetch cur_rowid INTO lv_rowid;
1052 IF (cur_rowid%FOUND) THEN
1053 Close cur_rowid;
1054 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_TRI_FK');
1055 IGS_GE_MSG_STACK.ADD;
1056 App_Exception.Raise_Exception;
1057 Return;
1058 END IF;
1059 Close cur_rowid;
1060
1061 END GET_FK_IGS_TR_ITEM;
1062
1063 PROCEDURE GET_FK_IGS_AS_UNIT_MODE (
1064 x_unit_mode IN VARCHAR2
1065 ) AS
1066
1067 CURSOR cur_rowid IS
1068 SELECT rowid
1069 FROM IGS_AD_PS_APLINSTUNT_ALL
1070 WHERE unit_mode = x_unit_mode ;
1071
1072 lv_rowid cur_rowid%RowType;
1073
1074 BEGIN
1075
1076 Open cur_rowid;
1077 Fetch cur_rowid INTO lv_rowid;
1078 IF (cur_rowid%FOUND) THEN
1079 Close cur_rowid;
1080 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_UM_FK');
1081 IGS_GE_MSG_STACK.ADD;
1082 App_Exception.Raise_Exception;
1083 Return;
1084 END IF;
1085 Close cur_rowid;
1086
1087 END GET_FK_IGS_AS_UNIT_MODE;
1088
1089 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_OPT (
1090 x_unit_cd IN VARCHAR2,
1091 x_version_number IN NUMBER,
1092 x_cal_type IN VARCHAR2,
1093 x_ci_sequence_number IN NUMBER,
1094 x_location_cd IN VARCHAR2,
1095 x_unit_class IN VARCHAR2
1096 ) AS
1097
1098 CURSOR cur_rowid IS
1099 SELECT rowid
1100 FROM IGS_AD_PS_APLINSTUNT_ALL
1101 WHERE unit_cd = x_unit_cd
1102 AND uv_version_number = x_version_number
1103 AND cal_type = x_cal_type
1104 AND ci_sequence_number = x_ci_sequence_number
1105 AND location_cd = x_location_cd
1106 AND unit_class = x_unit_class ;
1107
1108 lv_rowid cur_rowid%RowType;
1109
1110 BEGIN
1111
1112 Open cur_rowid;
1113 Fetch cur_rowid INTO lv_rowid;
1114 IF (cur_rowid%FOUND) THEN
1115 Close cur_rowid;
1116 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_UOO_FK');
1117 IGS_GE_MSG_STACK.ADD;
1118 App_Exception.Raise_Exception;
1119 Return;
1120 END IF;
1121 Close cur_rowid;
1122
1123 END GET_FK_IGS_PS_UNIT_OFR_OPT;
1124
1125 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_PAT (
1126 x_unit_cd IN VARCHAR2,
1127 x_version_number IN NUMBER,
1128 x_cal_type IN VARCHAR2,
1129 x_ci_sequence_number IN NUMBER
1130 ) AS
1131
1132 CURSOR cur_rowid IS
1133 SELECT rowid
1134 FROM IGS_AD_PS_APLINSTUNT_ALL
1135 WHERE unit_cd = x_unit_cd
1136 AND uv_version_number = x_version_number
1137 AND cal_type = x_cal_type
1138 AND ci_sequence_number = x_ci_sequence_number ;
1139
1140 lv_rowid cur_rowid%RowType;
1141
1142 BEGIN
1143
1144 Open cur_rowid;
1145 Fetch cur_rowid INTO lv_rowid;
1146 IF (cur_rowid%FOUND) THEN
1147 Close cur_rowid;
1148 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACAIU_UOP_FK');
1149 IGS_GE_MSG_STACK.ADD;
1150 App_Exception.Raise_Exception;
1151 Return;
1152 END IF;
1153 Close cur_rowid;
1154
1155 END GET_FK_IGS_PS_UNIT_OFR_PAT;
1156
1157 PROCEDURE Before_DML (
1158 p_action IN VARCHAR2,
1159 x_rowid IN VARCHAR2 DEFAULT NULL,
1160 x_org_id IN NUMBER DEFAULT NULL,
1161 x_person_id IN NUMBER DEFAULT NULL,
1162 x_admission_appl_number IN NUMBER DEFAULT NULL,
1163 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
1164 x_acai_sequence_number IN NUMBER DEFAULT NULL,
1165 x_unit_cd IN VARCHAR2 DEFAULT NULL,
1166 x_uv_version_number IN NUMBER DEFAULT NULL,
1167 x_cal_type IN VARCHAR2 DEFAULT NULL,
1168 x_ci_sequence_number IN NUMBER DEFAULT NULL,
1169 x_location_cd IN VARCHAR2 DEFAULT NULL,
1170 x_unit_class IN VARCHAR2 DEFAULT NULL,
1171 x_unit_mode IN VARCHAR2 DEFAULT NULL,
1172 x_adm_unit_outcome_status IN VARCHAR2 DEFAULT NULL,
1173 x_ass_tracking_id IN NUMBER DEFAULT NULL,
1174 x_rule_waived_dt IN DATE DEFAULT NULL,
1175 x_rule_waived_person_id IN NUMBER DEFAULT NULL,
1176 x_sup_unit_cd IN VARCHAR2 DEFAULT NULL,
1177 x_sup_uv_version_number IN NUMBER DEFAULT NULL,
1178 x_creation_date IN DATE DEFAULT NULL,
1179 x_created_by IN NUMBER DEFAULT NULL,
1180 x_last_update_date IN DATE DEFAULT NULL,
1181 x_last_updated_by IN NUMBER DEFAULT NULL,
1182 x_last_update_login IN NUMBER DEFAULT NULL,
1183 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL
1184 ) AS
1185 BEGIN
1186
1187 Set_Column_Values (
1188 p_action,
1189 x_rowid,
1190 x_org_id,
1191 x_person_id,
1192 x_admission_appl_number,
1193 x_nominated_course_cd,
1194 x_acai_sequence_number,
1195 x_unit_cd,
1196 x_uv_version_number,
1197 x_cal_type,
1198 x_ci_sequence_number,
1199 x_location_cd,
1200 x_unit_class,
1201 x_unit_mode,
1202 x_adm_unit_outcome_status,
1203 x_ass_tracking_id,
1204 x_rule_waived_dt,
1205 x_rule_waived_person_id,
1206 x_sup_unit_cd,
1207 x_sup_uv_version_number,
1208 x_creation_date,
1209 x_created_by,
1210 x_last_update_date,
1211 x_last_updated_by,
1212 x_last_update_login,
1213 x_adm_ps_appl_inst_unit_id
1214 );
1215
1216 igs_ad_gen_002.check_adm_appl_inst_stat(
1217 nvl(x_person_id,old_references.person_id),
1218 nvl(x_admission_appl_number,old_references.admission_appl_number),
1219 nvl(x_nominated_course_cd,old_references.nominated_course_cd),
1220 nvl(x_acai_sequence_number,old_references.acai_sequence_number)
1221 );
1222
1223 IF (p_action = 'INSERT') THEN
1224 -- Call all the procedures related to Before Insert.
1225 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
1226 IF Get_PK_For_Validation (new_references.adm_ps_appl_inst_unit_id) OR
1227 Get_UK_For_Validation (
1228 new_references.person_id,
1229 new_references.admission_appl_number,
1230 new_references.nominated_course_cd,
1231 new_references.acai_sequence_number,
1232 new_references.unit_cd,
1233 new_references.uv_version_number,
1234 new_references.cal_type,
1235 new_references.ci_sequence_number,
1236 new_references.location_cd,
1237 new_references.unit_class
1238 ) THEN
1239 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1240 IGS_GE_MSG_STACK.ADD;
1241 App_Exception.Raise_Exception;
1242 END IF;
1243 Check_Constraints;
1244 Check_Parent_Existance;
1245 ELSIF (p_action = 'UPDATE') THEN
1246 -- Call all the procedures related to Before Update.
1247 IF Get_UK_For_Validation (
1248 new_references.person_id,
1249 new_references.admission_appl_number,
1250 new_references.nominated_course_cd,
1251 new_references.acai_sequence_number,
1252 new_references.unit_cd,
1253 new_references.uv_version_number,
1254 new_references.cal_type,
1255 new_references.ci_sequence_number,
1256 new_references.location_cd,
1257 new_references.unit_class
1258 ) THEN
1259 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1260 IGS_GE_MSG_STACK.ADD;
1261 App_Exception.Raise_Exception;
1262 END IF;
1263 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
1264 Check_Constraints;
1265 Check_Parent_Existance;
1266 ELSIF (p_action = 'DELETE') THEN
1267 -- Call all the procedures related to Before Delete.
1268 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
1269 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1270 IF Get_PK_For_Validation (new_references.adm_ps_appl_inst_unit_id) OR
1271 Get_UK_For_Validation (
1272 new_references.person_id,
1273 new_references.admission_appl_number,
1274 new_references.nominated_course_cd,
1275 new_references.acai_sequence_number,
1276 new_references.unit_cd,
1277 new_references.uv_version_number,
1278 new_references.cal_type,
1279 new_references.ci_sequence_number,
1280 new_references.location_cd,
1281 new_references.unit_class
1282 ) THEN
1283 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1284 IGS_GE_MSG_STACK.ADD;
1285 App_Exception.Raise_Exception;
1286 END IF;
1287 Check_Constraints;
1288 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1289 IF Get_UK_For_Validation (
1290 new_references.person_id,
1291 new_references.admission_appl_number,
1292 new_references.nominated_course_cd,
1293 new_references.acai_sequence_number,
1294 new_references.unit_cd,
1295 new_references.uv_version_number,
1296 new_references.cal_type,
1297 new_references.ci_sequence_number,
1298 new_references.location_cd,
1299 new_references.unit_class
1300 ) THEN
1301 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1302 IGS_GE_MSG_STACK.ADD;
1303 App_Exception.Raise_Exception;
1304 END IF;
1305 Check_Constraints;
1306 END IF;
1307 END Before_DML;
1308
1309 PROCEDURE After_DML (
1310 p_action IN VARCHAR2,
1311 x_rowid IN VARCHAR2
1312 ) AS
1313 BEGIN
1314
1315 l_rowid := x_rowid;
1316
1317 IF (p_action = 'INSERT') THEN
1318 AfterRowInsertUpdate2 ( p_inserting => TRUE );
1319 ELSIF (p_action = 'UPDATE') THEN
1320 AfterRowInsertUpdate2 ( p_updating => TRUE );
1321 AfterRowUpdateDelete3 ( p_updating => TRUE );
1322 ELSIF (p_action = 'DELETE') THEN
1323 AfterRowUpdateDelete3 ( p_deleting => TRUE );
1324 END IF;
1325
1326 END After_DML;
1327
1328 procedure INSERT_ROW (
1329 X_ROWID in out NOCOPY VARCHAR2,
1330 X_ORG_ID in NUMBER,
1331 X_PERSON_ID in NUMBER,
1332 X_ADMISSION_APPL_NUMBER in NUMBER,
1333 X_NOMINATED_COURSE_CD in VARCHAR2,
1334 X_ACAI_SEQUENCE_NUMBER in NUMBER,
1335 X_UNIT_CD in VARCHAR2,
1336 X_UV_VERSION_NUMBER in NUMBER,
1337 X_CAL_TYPE in VARCHAR2,
1338 X_CI_SEQUENCE_NUMBER in NUMBER,
1339 X_LOCATION_CD in VARCHAR2,
1340 X_UNIT_CLASS in VARCHAR2,
1341 X_UNIT_MODE in VARCHAR2,
1342 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
1343 X_ASS_TRACKING_ID in NUMBER,
1344 X_RULE_WAIVED_DT in DATE,
1345 X_RULE_WAIVED_PERSON_ID in NUMBER,
1346 X_SUP_UNIT_CD in VARCHAR2,
1347 X_SUP_UV_VERSION_NUMBER in NUMBER,
1348 X_MODE in VARCHAR2,
1349 X_ADM_PS_APPL_INST_UNIT_ID in out NOCOPY NUMBER
1350 ) as
1351 cursor C is select ROWID, ADM_PS_APPL_INST_UNIT_ID from IGS_AD_PS_APLINSTUNT_ALL
1352 where PERSON_ID = X_PERSON_ID
1353 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
1354 and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
1355 and ACAI_SEQUENCE_NUMBER = X_ACAI_SEQUENCE_NUMBER
1356 and UNIT_CD = X_UNIT_CD
1357 and NVL(UV_VERSION_NUMBER,-1) = NVL(X_UV_VERSION_NUMBER,-1)
1358 and NVL(CAL_TYPE,'*') = NVL(X_CAL_TYPE,'*')
1359 and NVL(CI_SEQUENCE_NUMBER,-1) = NVL(X_CI_SEQUENCE_NUMBER,-1)
1360 and NVL(LOCATION_CD,'*') = NVL(X_LOCATION_CD,'*')
1361 and NVL(UNIT_CLASS,'*') = NVL(X_UNIT_CLASS,'*');
1362 X_LAST_UPDATE_DATE DATE;
1363 X_LAST_UPDATED_BY NUMBER;
1364 X_LAST_UPDATE_LOGIN NUMBER;
1365 X_REQUEST_ID NUMBER;
1366 X_PROGRAM_ID NUMBER;
1367 X_PROGRAM_APPLICATION_ID NUMBER;
1368 X_PROGRAM_UPDATE_DATE DATE;
1369 begin
1370 X_LAST_UPDATE_DATE := SYSDATE;
1371 if(X_MODE = 'I') then
1372 X_LAST_UPDATED_BY := 1;
1373 X_LAST_UPDATE_LOGIN := 0;
1374 elsif (X_MODE IN ('R', 'S')) then
1375 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1376 if X_LAST_UPDATED_BY is NULL then
1377 X_LAST_UPDATED_BY := -1;
1378 end if;
1379 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1380 if X_LAST_UPDATE_LOGIN is NULL then
1381 X_LAST_UPDATE_LOGIN := -1;
1382 end if;
1383 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1384 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1385
1386 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1387 if (X_REQUEST_ID = -1) then
1388 X_REQUEST_ID := NULL;
1389 X_PROGRAM_ID := NULL;
1390 X_PROGRAM_APPLICATION_ID := NULL;
1391 X_PROGRAM_UPDATE_DATE := NULL;
1392 else
1393 X_PROGRAM_UPDATE_DATE := SYSDATE;
1394 end if;
1395 else
1396 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1397 IGS_GE_MSG_STACK.ADD;
1398 app_exception.raise_exception;
1399 end if;
1400
1401 Before_DML(p_action =>'INSERT',
1402 x_rowid =>X_ROWID,
1403 x_org_id => igs_ge_gen_003.get_org_id,
1404 x_person_id =>X_PERSON_ID,
1405 x_admission_appl_number =>X_ADMISSION_APPL_NUMBER,
1406 x_nominated_course_cd =>X_NOMINATED_COURSE_CD,
1407 x_acai_sequence_number =>X_ACAI_SEQUENCE_NUMBER,
1408 x_unit_cd =>X_UNIT_CD,
1409 x_uv_version_number =>X_UV_VERSION_NUMBER,
1410 x_cal_type =>X_CAL_TYPE ,
1411 x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
1412 x_location_cd =>X_LOCATION_CD,
1413 x_unit_class =>X_UNIT_CLASS,
1414 x_unit_mode => X_UNIT_MODE,
1415 x_adm_unit_outcome_status => X_ADM_UNIT_OUTCOME_STATUS,
1416 x_ass_tracking_id => X_ASS_TRACKING_ID,
1417 x_rule_waived_dt => X_RULE_WAIVED_DT,
1418 x_rule_waived_person_id => X_RULE_WAIVED_PERSON_ID,
1419 x_sup_unit_cd => X_SUP_UNIT_CD,
1420 x_sup_uv_version_number => X_SUP_UV_VERSION_NUMBER,
1421 x_creation_date => X_LAST_UPDATE_DATE,
1422 x_created_by => X_LAST_UPDATED_BY,
1423 x_last_update_date => X_LAST_UPDATE_DATE,
1424 x_last_updated_by => X_LAST_UPDATED_BY,
1425 x_last_update_login => X_LAST_UPDATE_LOGIN,
1426 x_adm_ps_appl_inst_unit_id => X_ADM_PS_APPL_INST_UNIT_ID
1427 );
1428 IF (x_mode = 'S') THEN
1429 igs_sc_gen_001.set_ctx('R');
1430 END IF;
1431 insert into IGS_AD_PS_APLINSTUNT_ALL (
1432 ORG_ID,
1433 PERSON_ID,
1434 ADMISSION_APPL_NUMBER,
1435 NOMINATED_COURSE_CD,
1436 ACAI_SEQUENCE_NUMBER,
1437 UNIT_CD,
1438 UV_VERSION_NUMBER,
1439 CAL_TYPE,
1440 CI_SEQUENCE_NUMBER,
1441 LOCATION_CD,
1442 UNIT_CLASS,
1443 UNIT_MODE,
1444 ADM_UNIT_OUTCOME_STATUS,
1445 ASS_TRACKING_ID,
1446 RULE_WAIVED_DT,
1447 RULE_WAIVED_PERSON_ID,
1448 SUP_UNIT_CD,
1449 SUP_UV_VERSION_NUMBER,
1450 CREATION_DATE,
1451 CREATED_BY,
1452 LAST_UPDATE_DATE,
1453 LAST_UPDATED_BY,
1454 LAST_UPDATE_LOGIN,
1455 REQUEST_ID,
1456 PROGRAM_ID,
1457 PROGRAM_APPLICATION_ID,
1458 PROGRAM_UPDATE_DATE ,
1459 ADM_PS_APPL_INST_UNIT_ID
1460 ) values (
1461 NEW_REFERENCES.ORG_ID,
1462 NEW_REFERENCES.PERSON_ID,
1463 NEW_REFERENCES.ADMISSION_APPL_NUMBER,
1464 NEW_REFERENCES.NOMINATED_COURSE_CD,
1465 NEW_REFERENCES.ACAI_SEQUENCE_NUMBER,
1466 NEW_REFERENCES.UNIT_CD,
1467 NEW_REFERENCES.UV_VERSION_NUMBER,
1468 NEW_REFERENCES.CAL_TYPE,
1469 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
1470 NEW_REFERENCES.LOCATION_CD,
1471 NEW_REFERENCES.UNIT_CLASS,
1472 NEW_REFERENCES.UNIT_MODE,
1473 NEW_REFERENCES.ADM_UNIT_OUTCOME_STATUS,
1474 NEW_REFERENCES.ASS_TRACKING_ID,
1475 NEW_REFERENCES.RULE_WAIVED_DT,
1476 NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
1477 NEW_REFERENCES.SUP_UNIT_CD,
1478 NEW_REFERENCES.SUP_UV_VERSION_NUMBER,
1479 X_LAST_UPDATE_DATE,
1480 X_LAST_UPDATED_BY,
1481 X_LAST_UPDATE_DATE,
1482 X_LAST_UPDATED_BY,
1483 X_LAST_UPDATE_LOGIN,
1484 X_REQUEST_ID,
1485 X_PROGRAM_ID,
1486 X_PROGRAM_APPLICATION_ID,
1487 X_PROGRAM_UPDATE_DATE,
1488 IGS_AD_PS_APLINSTUNT_S.NEXTVAL
1489 );
1490 IF (x_mode = 'S') THEN
1491 igs_sc_gen_001.unset_ctx('R');
1492 END IF;
1493
1494
1495 open c;
1496 fetch c into X_ROWID, X_ADM_PS_APPL_INST_UNIT_ID;
1497 if (c%notfound) then
1498 close c;
1499 raise no_data_found;
1500 end if;
1501 close c;
1502 After_DML(
1503 p_action =>'INSERT',
1504 x_rowid => X_ROWID
1505 );
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1509 -- Code to handle Security Policy error raised
1510 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1511 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1512 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1513 -- that the ownerof policy function does not have privilege to access.
1514 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1515 fnd_message.set_token ('ERR_CD', SQLCODE);
1516 igs_ge_msg_stack.add;
1517 igs_sc_gen_001.unset_ctx('R');
1518 app_exception.raise_exception;
1519 ELSE
1520 igs_sc_gen_001.unset_ctx('R');
1521 RAISE;
1522 END IF;
1523
1524 end INSERT_ROW;
1525
1526 procedure LOCK_ROW (
1527 X_ROWID in VARCHAR2,
1528 X_PERSON_ID in NUMBER,
1529 X_ADMISSION_APPL_NUMBER in NUMBER,
1530 X_NOMINATED_COURSE_CD in VARCHAR2,
1531 X_ACAI_SEQUENCE_NUMBER in NUMBER,
1532 X_UNIT_CD in VARCHAR2,
1533 X_UV_VERSION_NUMBER in NUMBER,
1534 X_CAL_TYPE in VARCHAR2,
1535 X_CI_SEQUENCE_NUMBER in NUMBER,
1536 X_LOCATION_CD in VARCHAR2,
1537 X_UNIT_CLASS in VARCHAR2,
1538 X_UNIT_MODE in VARCHAR2,
1539 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
1540 X_ASS_TRACKING_ID in NUMBER,
1541 X_RULE_WAIVED_DT in DATE,
1542 X_RULE_WAIVED_PERSON_ID in NUMBER,
1543 X_SUP_UNIT_CD in VARCHAR2,
1544 X_SUP_UV_VERSION_NUMBER in NUMBER,
1545 X_ADM_PS_APPL_INST_UNIT_ID in NUMBER
1546 ) as
1547 cursor c1 is select
1548 UV_VERSION_NUMBER,
1549 CAL_TYPE,
1550 CI_SEQUENCE_NUMBER,
1551 LOCATION_CD,
1552 UNIT_CLASS,
1553 UNIT_MODE,
1554 ADM_UNIT_OUTCOME_STATUS,
1555 ASS_TRACKING_ID,
1556 RULE_WAIVED_DT,
1557 RULE_WAIVED_PERSON_ID,
1558 SUP_UNIT_CD,
1559 SUP_UV_VERSION_NUMBER
1560 from IGS_AD_PS_APLINSTUNT_ALL
1561 where ROWID = X_ROWID for update nowait;
1562 tlinfo c1%rowtype;
1563
1564 begin
1565 open c1;
1566 fetch c1 into tlinfo;
1567 if (c1%notfound) then
1568 close c1;
1569 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1570 IGS_GE_MSG_STACK.ADD;
1571 app_exception.raise_exception;
1572 return;
1573 end if;
1574 close c1;
1575
1576 if ( (tlinfo.UV_VERSION_NUMBER = X_UV_VERSION_NUMBER)
1577 AND ((tlinfo.CAL_TYPE = X_CAL_TYPE)
1578 OR ((tlinfo.CAL_TYPE is null)
1579 AND (X_CAL_TYPE is null)))
1580 AND ((tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
1581 OR ((tlinfo.CI_SEQUENCE_NUMBER is null)
1582 AND (X_CI_SEQUENCE_NUMBER is null)))
1583 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
1584 OR ((tlinfo.LOCATION_CD is null)
1585 AND (X_LOCATION_CD is null)))
1586 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
1587 OR ((tlinfo.UNIT_CLASS is null)
1588 AND (X_UNIT_CLASS is null)))
1589 AND ((tlinfo.UNIT_MODE = X_UNIT_MODE)
1590 OR ((tlinfo.UNIT_MODE is null)
1591 AND (X_UNIT_MODE is null)))
1592 AND (tlinfo.ADM_UNIT_OUTCOME_STATUS = X_ADM_UNIT_OUTCOME_STATUS)
1593 AND ((tlinfo.ASS_TRACKING_ID = X_ASS_TRACKING_ID)
1594 OR ((tlinfo.ASS_TRACKING_ID is null)
1595 AND (X_ASS_TRACKING_ID is null)))
1596 AND ((TRUNC(tlinfo.RULE_WAIVED_DT) = TRUNC(X_RULE_WAIVED_DT))
1597 OR ((tlinfo.RULE_WAIVED_DT is null)
1598 AND (X_RULE_WAIVED_DT is null)))
1599 AND ((tlinfo.RULE_WAIVED_PERSON_ID = X_RULE_WAIVED_PERSON_ID)
1600 OR ((tlinfo.RULE_WAIVED_PERSON_ID is null)
1601 AND (X_RULE_WAIVED_PERSON_ID is null)))
1602 AND ((tlinfo.SUP_UNIT_CD = X_SUP_UNIT_CD)
1603 OR ((tlinfo.SUP_UNIT_CD is null)
1604 AND (X_SUP_UNIT_CD is null)))
1605 AND ((tlinfo.SUP_UV_VERSION_NUMBER = X_SUP_UV_VERSION_NUMBER)
1606 OR ((tlinfo.SUP_UV_VERSION_NUMBER is null)
1607 AND (X_SUP_UV_VERSION_NUMBER is null)))
1608 ) then
1609 null;
1610 else
1611 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1612 IGS_GE_MSG_STACK.ADD;
1613 app_exception.raise_exception;
1614 end if;
1615 return;
1616 end LOCK_ROW;
1617
1618 procedure UPDATE_ROW (
1619 X_ROWID in VARCHAR2,
1620 X_PERSON_ID in NUMBER,
1621 X_ADMISSION_APPL_NUMBER in NUMBER,
1622 X_NOMINATED_COURSE_CD in VARCHAR2,
1623 X_ACAI_SEQUENCE_NUMBER in NUMBER,
1624 X_UNIT_CD in VARCHAR2,
1625 X_UV_VERSION_NUMBER in NUMBER,
1626 X_CAL_TYPE in VARCHAR2,
1627 X_CI_SEQUENCE_NUMBER in NUMBER,
1628 X_LOCATION_CD in VARCHAR2,
1629 X_UNIT_CLASS in VARCHAR2,
1630 X_UNIT_MODE in VARCHAR2,
1631 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
1632 X_ASS_TRACKING_ID in NUMBER,
1633 X_RULE_WAIVED_DT in DATE,
1634 X_RULE_WAIVED_PERSON_ID in NUMBER,
1635 X_SUP_UNIT_CD in VARCHAR2,
1636 X_SUP_UV_VERSION_NUMBER in NUMBER,
1637 X_MODE in VARCHAR2,
1638 X_ADM_PS_APPL_INST_UNIT_ID in NUMBER
1639 ) AS
1640 X_LAST_UPDATE_DATE DATE;
1641 X_LAST_UPDATED_BY NUMBER;
1642 X_LAST_UPDATE_LOGIN NUMBER;
1643 X_REQUEST_ID NUMBER;
1644 X_PROGRAM_ID NUMBER;
1645 X_PROGRAM_APPLICATION_ID NUMBER;
1646 X_PROGRAM_UPDATE_DATE DATE;
1647
1648 begin
1649 X_LAST_UPDATE_DATE := SYSDATE;
1650 if(X_MODE = 'I') then
1651 X_LAST_UPDATED_BY := 1;
1652 X_LAST_UPDATE_LOGIN := 0;
1653 elsif (X_MODE IN ('R', 'S')) then
1654 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1655 if X_LAST_UPDATED_BY is NULL then
1656 X_LAST_UPDATED_BY := -1;
1657 end if;
1658 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1659 if X_LAST_UPDATE_LOGIN is NULL then
1660 X_LAST_UPDATE_LOGIN := -1;
1661 end if;
1662 else
1663 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1664 IGS_GE_MSG_STACK.ADD;
1665 app_exception.raise_exception;
1666 end if;
1667
1668 Before_DML(p_action =>'UPDATE',
1669 x_rowid =>X_ROWID,
1670 x_person_id =>X_PERSON_ID,
1671 x_admission_appl_number =>X_ADMISSION_APPL_NUMBER,
1672 x_nominated_course_cd =>X_NOMINATED_COURSE_CD,
1673 x_acai_sequence_number =>X_ACAI_SEQUENCE_NUMBER,
1674 x_unit_cd =>X_UNIT_CD,
1675 x_uv_version_number =>X_UV_VERSION_NUMBER,
1676 x_cal_type =>X_CAL_TYPE ,
1677 x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
1678 x_location_cd =>X_LOCATION_CD,
1679 x_unit_class =>X_UNIT_CLASS,
1680 x_unit_mode => X_UNIT_MODE,
1681 x_adm_unit_outcome_status => X_ADM_UNIT_OUTCOME_STATUS,
1682 x_ass_tracking_id => X_ASS_TRACKING_ID,
1683 x_rule_waived_dt => X_RULE_WAIVED_DT,
1684 x_rule_waived_person_id => X_RULE_WAIVED_PERSON_ID,
1685 x_sup_unit_cd => X_SUP_UNIT_CD,
1686 x_sup_uv_version_number => X_SUP_UV_VERSION_NUMBER,
1687 x_creation_date => X_LAST_UPDATE_DATE,
1688 x_created_by => X_LAST_UPDATED_BY,
1689 x_last_update_date => X_LAST_UPDATE_DATE,
1690 x_last_updated_by => X_LAST_UPDATED_BY,
1691 x_last_update_login => X_LAST_UPDATE_LOGIN,
1692 x_adm_ps_appl_inst_unit_id => X_ADM_PS_APPL_INST_UNIT_ID
1693 );
1694
1695
1696 if (X_MODE IN ('R', 'S')) then
1697 X_REQUEST_ID :=FND_GLOBAL.CONC_REQUEST_ID;
1698 X_PROGRAM_ID :=FND_GLOBAL.CONC_PROGRAM_ID;
1699 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1700 if (X_REQUEST_ID = -1) then
1701 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1702 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
1703 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1704 else
1705 X_PROGRAM_UPDATE_DATE := SYSDATE;
1706 end if;
1707 end if;
1708 IF (x_mode = 'S') THEN
1709 igs_sc_gen_001.set_ctx('R');
1710 END IF;
1711 update IGS_AD_PS_APLINSTUNT_ALL set
1712 UV_VERSION_NUMBER = NEW_REFERENCES.UV_VERSION_NUMBER,
1713 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
1714 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
1715 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1716 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
1717 UNIT_MODE = NEW_REFERENCES.UNIT_MODE,
1718 ADM_UNIT_OUTCOME_STATUS = NEW_REFERENCES.ADM_UNIT_OUTCOME_STATUS,
1719 ASS_TRACKING_ID = NEW_REFERENCES.ASS_TRACKING_ID,
1720 RULE_WAIVED_DT = NEW_REFERENCES.RULE_WAIVED_DT,
1721 RULE_WAIVED_PERSON_ID = NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
1722 SUP_UNIT_CD = NEW_REFERENCES.SUP_UNIT_CD,
1723 SUP_UV_VERSION_NUMBER = NEW_REFERENCES.SUP_UV_VERSION_NUMBER,
1724 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1725 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1726 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1727 REQUEST_ID = X_REQUEST_ID,
1728 PROGRAM_ID = X_PROGRAM_ID,
1729 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1730 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1731 where ROWID = X_ROWID
1732 ;
1733 if (sql%notfound) then
1734 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1735 igs_ge_msg_stack.add;
1736 igs_sc_gen_001.unset_ctx('R');
1737 app_exception.raise_exception;
1738 end if;
1739 IF (x_mode = 'S') THEN
1740 igs_sc_gen_001.unset_ctx('R');
1741 END IF;
1742
1743 After_DML(
1744 p_action =>'UPDATE',
1745 x_rowid => X_ROWID
1746 );
1747 EXCEPTION
1748 WHEN OTHERS THEN
1749 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1750 -- Code to handle Security Policy error raised
1751 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1752 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1753 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1754 -- that the ownerof policy function does not have privilege to access.
1755 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1756 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1757 IGS_GE_MSG_STACK.ADD;
1758 app_exception.raise_exception;
1759 ELSE
1760 RAISE;
1761 END IF;
1762 end UPDATE_ROW;
1763
1764 procedure ADD_ROW (
1765 X_ROWID in out NOCOPY VARCHAR2,
1766 X_ORG_ID in NUMBER,
1767 X_PERSON_ID in NUMBER,
1768 X_ADMISSION_APPL_NUMBER in NUMBER,
1769 X_NOMINATED_COURSE_CD in VARCHAR2,
1770 X_ACAI_SEQUENCE_NUMBER in NUMBER,
1771 X_UNIT_CD in VARCHAR2,
1772 X_UV_VERSION_NUMBER in NUMBER,
1773 X_CAL_TYPE in VARCHAR2,
1774 X_CI_SEQUENCE_NUMBER in NUMBER,
1775 X_LOCATION_CD in VARCHAR2,
1776 X_UNIT_CLASS in VARCHAR2,
1777 X_UNIT_MODE in VARCHAR2,
1778 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
1779 X_ASS_TRACKING_ID in NUMBER,
1780 X_RULE_WAIVED_DT in DATE,
1781 X_RULE_WAIVED_PERSON_ID in NUMBER,
1782 X_SUP_UNIT_CD in VARCHAR2,
1783 X_SUP_UV_VERSION_NUMBER in NUMBER,
1784 X_MODE in VARCHAR2,
1785 X_ADM_PS_APPL_INST_UNIT_ID in out NOCOPY NUMBER
1786 ) AS
1787 cursor c1 is select rowid from IGS_AD_PS_APLINSTUNT_ALL
1788 where ADM_PS_APPL_INST_UNIT_ID = X_ADM_PS_APPL_INST_UNIT_ID
1789 ;
1790 begin
1791 open c1;
1792 fetch c1 into X_ROWID;
1793 if (c1%notfound) then
1794 close c1;
1795 INSERT_ROW (
1796 X_ROWID,
1797 X_ORG_ID,
1798 X_PERSON_ID,
1799 X_ADMISSION_APPL_NUMBER,
1800 X_NOMINATED_COURSE_CD,
1801 X_ACAI_SEQUENCE_NUMBER,
1802 X_UNIT_CD,
1803 X_UV_VERSION_NUMBER,
1804 X_CAL_TYPE,
1805 X_CI_SEQUENCE_NUMBER,
1806 X_LOCATION_CD,
1807 X_UNIT_CLASS,
1808 X_UNIT_MODE,
1809 X_ADM_UNIT_OUTCOME_STATUS,
1810 X_ASS_TRACKING_ID,
1811 X_RULE_WAIVED_DT,
1812 X_RULE_WAIVED_PERSON_ID,
1813 X_SUP_UNIT_CD,
1814 X_SUP_UV_VERSION_NUMBER,
1815 X_MODE,
1816 X_ADM_PS_APPL_INST_UNIT_ID);
1817 return;
1818 end if;
1819 close c1;
1820 UPDATE_ROW (
1821 X_ROWID,
1822 X_PERSON_ID,
1823 X_ADMISSION_APPL_NUMBER,
1824 X_NOMINATED_COURSE_CD,
1825 X_ACAI_SEQUENCE_NUMBER,
1826 X_UNIT_CD,
1827 X_UV_VERSION_NUMBER,
1828 X_CAL_TYPE,
1829 X_CI_SEQUENCE_NUMBER,
1830 X_LOCATION_CD,
1831 X_UNIT_CLASS,
1832 X_UNIT_MODE,
1833 X_ADM_UNIT_OUTCOME_STATUS,
1834 X_ASS_TRACKING_ID,
1835 X_RULE_WAIVED_DT,
1836 X_RULE_WAIVED_PERSON_ID,
1837 X_SUP_UNIT_CD,
1838 X_SUP_UV_VERSION_NUMBER,
1839 X_MODE,
1840 X_ADM_PS_APPL_INST_UNIT_ID);
1841 end ADD_ROW;
1842
1843 procedure DELETE_ROW (
1844 X_ROWID in VARCHAR2,
1845 x_mode IN VARCHAR2
1846 ) AS
1847 begin
1848 Before_DML(
1849 p_action =>'DELETE',
1850 x_rowid => X_ROWID
1851 );
1852 IF (x_mode = 'S') THEN
1853 igs_sc_gen_001.set_ctx('R');
1854 END IF;
1855 delete from IGS_AD_PS_APLINSTUNT_ALL
1856 where ROWID = X_ROWID;
1857 if (sql%notfound) then
1858 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1859 igs_ge_msg_stack.add;
1860 igs_sc_gen_001.unset_ctx('R');
1861 app_exception.raise_exception;
1862 end if;
1863 IF (x_mode = 'S') THEN
1864 igs_sc_gen_001.unset_ctx('R');
1865 END IF;
1866
1867 After_DML(
1868 p_action =>'DELETE',
1869 x_rowid => X_ROWID
1870 );
1871 EXCEPTION
1872 WHEN OTHERS THEN
1873 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1874 -- Code to handle Security Policy error raised
1875 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1876 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1877 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1878 -- that the ownerof policy function does not have privilege to access.
1879 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1880 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1881 IGS_GE_MSG_STACK.ADD;
1882 app_exception.raise_exception;
1883 ELSE
1884 RAISE;
1885 END IF;
1886 end DELETE_ROW;
1887
1888 end IGS_AD_PS_APLINSTUNT_PKG;