[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PS_APPL_HIST_PKG
Source
1 package body IGS_AD_PS_APPL_HIST_PKG as
2 /* $Header: IGSAI17B.pls 120.0 2005/06/01 21:03:28 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_PS_APPL_HIST_ALL%RowType;
5 new_references IGS_AD_PS_APPL_HIST_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_admission_appl_number IN NUMBER DEFAULT NULL,
12 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
13 x_hist_start_dt IN DATE DEFAULT NULL,
14 x_hist_end_dt IN DATE DEFAULT NULL,
15 x_hist_who IN NUMBER DEFAULT NULL,
16 x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
17 x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
18 x_admission_cd IN VARCHAR2 DEFAULT NULL,
19 x_course_rank_set IN VARCHAR2 DEFAULT NULL,
20 x_course_rank_schedule IN VARCHAR2 DEFAULT NULL,
21 x_req_for_reconsideration_ind IN VARCHAR2 DEFAULT NULL,
22 x_req_for_adv_standing_ind IN VARCHAR2 DEFAULT NULL,
23 x_person_id IN NUMBER DEFAULT NULL,
24 x_creation_date IN DATE DEFAULT NULL,
25 x_created_by IN NUMBER DEFAULT NULL,
26 x_last_update_date IN DATE DEFAULT NULL,
27 x_last_updated_by IN NUMBER DEFAULT NULL,
28 x_last_update_login IN NUMBER DEFAULT NULL
29 ) AS
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_AD_PS_APPL_HIST_ALL
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 Open cur_old_ref_values;
43 Fetch cur_old_ref_values INTO old_references;
44 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
45 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
46 IGS_GE_MSG_STACK.ADD;
47 App_Exception.Raise_Exception;
48 Close cur_old_ref_values;
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.org_id := x_org_id;
55 new_references.admission_appl_number := x_admission_appl_number;
56 new_references.nominated_course_cd := x_nominated_course_cd;
57 new_references.hist_start_dt := x_hist_start_dt;
58 new_references.hist_end_dt := x_hist_end_dt;
59 new_references.hist_who := x_hist_who;
60 new_references.transfer_course_cd := x_transfer_course_cd;
61 new_references.basis_for_admission_type := x_basis_for_admission_type;
62 new_references.admission_cd := x_admission_cd;
63 new_references.course_rank_set := x_course_rank_set;
64 new_references.course_rank_schedule := x_course_rank_schedule;
65 new_references.req_for_reconsideration_ind := x_req_for_reconsideration_ind;
66 new_references.req_for_adv_standing_ind := x_req_for_adv_standing_ind;
67 new_references.person_id := x_person_id;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78
79 END Set_Column_Values;
80
81
82 FUNCTION Get_PK_For_Validation (
83 x_person_id IN NUMBER,
84 x_admission_appl_number IN NUMBER,
85 x_nominated_course_cd IN VARCHAR2,
86 x_hist_start_dt IN DATE
87 ) RETURN BOOLEAN AS
88
89 CURSOR cur_rowid IS
90 SELECT rowid
91 FROM IGS_AD_PS_APPL_HIST_ALL
92 WHERE person_id = x_person_id
93 AND admission_appl_number = x_admission_appl_number
94 AND nominated_course_cd = x_nominated_course_cd
95 AND hist_start_dt = x_hist_start_dt
96 FOR UPDATE NOWAIT;
97
98 lv_rowid cur_rowid%RowType;
99
100 BEGIN
101
102 Open cur_rowid;
103 Fetch cur_rowid INTO lv_rowid;
104 IF (cur_rowid%FOUND) THEN
105 Close cur_rowid;
106 Return TRUE;
107 ELSE
108 Close cur_rowid;
109 Return FALSE;
110 END IF;
111
112 END Get_PK_For_Validation;
113
114 -- procedure to check constraints
115 PROCEDURE CHECK_CONSTRAINTS(
116 column_name IN VARCHAR2 DEFAULT NULL,
117 column_value IN VARCHAR2 DEFAULT NULL
118 ) as
119 BEGIN
120 IF column_name is null THEN
121 NULL;
122 ELSIF upper(column_name) = 'ADMISSION_CD' THEN
123 new_references.admission_cd := column_value;
124 ELSIF upper(column_name) = 'BASIS_FOR_ADMISSION_TYPE' THEN
125 new_references.basis_for_admission_type := column_value;
126 ELSIF upper(column_name) = 'COURSE_RANK_SCHEDULE' THEN
127 new_references.course_rank_schedule := column_value;
128 ELSIF upper(column_name) = 'COURSE_RANK_SET' THEN
129 new_references.course_rank_set := column_value;
130 ELSIF upper(column_name) = 'NOMINATED_COURSE_CD' THEN
131 new_references.nominated_course_cd := column_value;
132 ELSIF upper(column_name) = 'REQ_FOR_ADV_STANDING_IND' THEN
133 new_references.req_for_adv_standing_ind := column_value;
134 ELSIF upper(column_name) = 'REQ_FOR_RECONSIDERATION_IND' THEN
135 new_references.req_for_reconsideration_ind := column_value;
136 ELSIF upper(column_name) = 'TRANSFER_COURSE_CD' THEN
137 new_references.transfer_course_cd := column_value;
138 END IF;
139
140 IF upper(column_name) = 'ADMISSION_CD' OR column_name IS NULL THEN
141 IF new_references.admission_cd <> UPPER(new_references.admission_cd) THEN
142 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
143 IGS_GE_MSG_STACK.ADD;
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 END IF;
146 END IF;
147 IF upper(column_name) = 'BASIS_FOR_ADMISSION_TYPE' OR column_name IS NULL THEN
148 IF new_references.basis_for_admission_type <> UPPER(new_references.basis_for_admission_type) THEN
149 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
150 IGS_GE_MSG_STACK.ADD;
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END IF;
153 END IF;
154 IF upper(column_name) = 'COURSE_RANK_SCHEDULE' OR column_name IS NULL THEN
155 IF new_references.course_rank_schedule <> UPPER(new_references.course_rank_schedule) THEN
156 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
157 IGS_GE_MSG_STACK.ADD;
158 APP_EXCEPTION.RAISE_EXCEPTION;
159 END IF;
160 END IF;
161 IF upper(column_name) = 'COURSE_RANK_SET' OR column_name IS NULL THEN
162 IF new_references.course_rank_set <> UPPER(new_references.course_rank_set) THEN
163 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
164 IGS_GE_MSG_STACK.ADD;
165 APP_EXCEPTION.RAISE_EXCEPTION;
166 END IF;
167 END IF;
168 IF upper(column_name) = 'NOMINATED_COURSE_CD' OR column_name IS NULL THEN
169 IF new_references.nominated_course_cd <> UPPER(new_references.nominated_course_cd) THEN
170 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
171 IGS_GE_MSG_STACK.ADD;
172 APP_EXCEPTION.RAISE_EXCEPTION;
173 END IF;
174 END IF;
175 IF upper(column_name) = 'REQ_FOR_ADV_STANDING_IND' OR column_name IS NULL THEN
176 IF new_references.req_for_adv_standing_ind <> UPPER(new_references.req_for_adv_standing_ind) THEN
177 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
178 IGS_GE_MSG_STACK.ADD;
179 APP_EXCEPTION.RAISE_EXCEPTION;
180 END IF;
181 END IF;
182 IF upper(column_name) = 'REQ_FOR_RECONSIDERATION_IND' OR column_name IS NULL THEN
183 IF new_references.req_for_reconsideration_ind <> UPPER(new_references.req_for_reconsideration_ind) THEN
184 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
185 IGS_GE_MSG_STACK.ADD;
186 APP_EXCEPTION.RAISE_EXCEPTION;
187 END IF;
188 END IF;
189 IF upper(column_name) = 'TRANSFER_COURSE_CD' OR column_name IS NULL THEN
190 IF new_references.transfer_course_cd <> UPPER(new_references.transfer_course_cd) THEN
191 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
192 IGS_GE_MSG_STACK.ADD;
193 APP_EXCEPTION.RAISE_EXCEPTION;
194 END IF;
195 END IF;
196
197 END CHECK_CONSTRAINTS;
198
199 PROCEDURE Before_DML (
200 p_action IN VARCHAR2,
201 x_rowid IN VARCHAR2 DEFAULT NULL,
202 x_org_id IN NUMBER DEFAULT NULL,
203 x_admission_appl_number IN NUMBER DEFAULT NULL,
204 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
205 x_hist_start_dt IN DATE DEFAULT NULL,
206 x_hist_end_dt IN DATE DEFAULT NULL,
207 x_hist_who IN NUMBER DEFAULT NULL,
208 x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
209 x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
210 x_admission_cd IN VARCHAR2 DEFAULT NULL,
211 x_course_rank_set IN VARCHAR2 DEFAULT NULL,
212 x_course_rank_schedule IN VARCHAR2 DEFAULT NULL,
213 x_req_for_reconsideration_ind IN VARCHAR2 DEFAULT NULL,
214 x_req_for_adv_standing_ind IN VARCHAR2 DEFAULT NULL,
215 x_person_id IN NUMBER DEFAULT NULL,
216 x_creation_date IN DATE DEFAULT NULL,
217 x_created_by IN NUMBER DEFAULT NULL,
218 x_last_update_date IN DATE DEFAULT NULL,
219 x_last_updated_by IN NUMBER DEFAULT NULL,
220 x_last_update_login IN NUMBER DEFAULT NULL
221 ) AS
222 BEGIN
223
224 Set_Column_Values (
225 p_action,
226 x_rowid,
227 x_org_id,
228 x_admission_appl_number,
229 x_nominated_course_cd,
230 x_hist_start_dt,
231 x_hist_end_dt,
232 x_hist_who,
233 x_transfer_course_cd,
234 x_basis_for_admission_type,
235 x_admission_cd,
236 x_course_rank_set,
237 x_course_rank_schedule,
238 x_req_for_reconsideration_ind,
239 x_req_for_adv_standing_ind,
240 x_person_id,
241 x_creation_date,
242 x_created_by,
243 x_last_update_date,
244 x_last_updated_by,
245 x_last_update_login
246 );
247
248 IF (p_action = 'INSERT') THEN
249 -- Call all the procedures related to Before Insert.
250 Null;
251 IF GET_PK_FOR_VALIDATION(
252 new_references.person_id,
253 new_references.admission_appl_number,
254 new_references.nominated_course_cd,
255 new_references.hist_start_dt
256 )THEN
257 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
258 IGS_GE_MSG_STACK.ADD;
259 APP_EXCEPTION.RAISE_EXCEPTION;
260 END IF;
261 Check_constraints;
262 ELSIF (p_action = 'UPDATE') THEN
263 -- Call all the procedures related to Before Update.
264 Null;
265 Check_constraints;
266 ELSIF (p_action = 'DELETE') THEN
267 -- Call all the procedures related to Before Delete.
268 Null;
269 ELSIF (p_action = 'VALIDATE_INSERT') THEN
270 IF GET_PK_FOR_VALIDATION(
271 new_references.person_id,
272 new_references.admission_appl_number,
273 new_references.nominated_course_cd,
274 new_references.hist_start_dt
275 )THEN
276 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
277 IGS_GE_MSG_STACK.ADD;
278 APP_EXCEPTION.RAISE_EXCEPTION;
279 END IF;
280 Check_constraints;
281 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
282 Check_constraints;
283 ELSIF (p_action = 'VALIDATE_DELETE') THEN
284 null;
285 END IF;
286
287 END Before_DML;
288
289 PROCEDURE After_DML (
290 p_action IN VARCHAR2,
291 x_rowid IN VARCHAR2
292 ) AS
293 BEGIN
294
295 l_rowid := x_rowid;
296
297 END After_DML;
298
299
300
301 procedure INSERT_ROW (
302 X_ROWID in out NOCOPY VARCHAR2,
303 X_ORG_ID in NUMBER,
304 X_PERSON_ID in NUMBER,
305 X_ADMISSION_APPL_NUMBER in NUMBER,
306 X_NOMINATED_COURSE_CD in VARCHAR2,
307 X_HIST_START_DT in DATE,
308 X_HIST_END_DT in DATE,
309 X_HIST_WHO in NUMBER,
310 X_TRANSFER_COURSE_CD in VARCHAR2,
311 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
312 X_ADMISSION_CD in VARCHAR2,
313 X_COURSE_RANK_SET in VARCHAR2,
314 X_COURSE_RANK_SCHEDULE in VARCHAR2,
315 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
316 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2,
317 X_MODE in VARCHAR2
318 ) as
319 cursor C is select ROWID from IGS_AD_PS_APPL_HIST_ALL
320 where PERSON_ID = X_PERSON_ID
321 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
322 and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
323 and HIST_START_DT = X_HIST_START_DT;
324 X_LAST_UPDATE_DATE DATE;
325 X_LAST_UPDATED_BY NUMBER;
326 X_LAST_UPDATE_LOGIN NUMBER;
327 begin
328 X_LAST_UPDATE_DATE := SYSDATE;
329 if(X_MODE = 'I') then
330 X_LAST_UPDATED_BY := 1;
331 X_LAST_UPDATE_LOGIN := 0;
332 elsif (X_MODE = 'R') then
333 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
334 if X_LAST_UPDATED_BY is NULL then
335 X_LAST_UPDATED_BY := -1;
336 end if;
337 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
338 if X_LAST_UPDATE_LOGIN is NULL then
339 X_LAST_UPDATE_LOGIN := -1;
340 end if;
341 else
342 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
343 IGS_GE_MSG_STACK.ADD;
344 app_exception.raise_exception;
345 end if;
346
347 Before_DML(p_action =>'INSERT',
348 x_rowid =>X_ROWID,
349 x_org_id => igs_ge_gen_003.get_org_id,
350 x_admission_appl_number => X_ADMISSION_APPL_NUMBER,
351 x_nominated_course_cd => X_NOMINATED_COURSE_CD,
352 x_hist_start_dt => X_HIST_START_DT,
353 x_hist_end_dt => X_HIST_END_DT,
354 x_hist_who => X_HIST_WHO,
355 x_transfer_course_cd => X_TRANSFER_COURSE_CD,
356 x_basis_for_admission_type => X_BASIS_FOR_ADMISSION_TYPE,
357 x_admission_cd => X_ADMISSION_CD,
358 x_course_rank_set => X_COURSE_RANK_SET,
359 x_course_rank_schedule => X_COURSE_RANK_SCHEDULE,
360 x_req_for_reconsideration_ind => NVL(X_REQ_FOR_RECONSIDERATION_IND,'N'),
361 x_req_for_adv_standing_ind => NVL(X_REQ_FOR_ADV_STANDING_IND,'N'),
362 x_person_id => X_PERSON_ID,
363 x_creation_date =>X_LAST_UPDATE_DATE,
364 x_created_by =>X_LAST_UPDATED_BY,
365 x_last_update_date =>X_LAST_UPDATE_DATE,
366 x_last_updated_by =>X_LAST_UPDATED_BY,
367 x_last_update_login =>X_LAST_UPDATE_LOGIN
368 );
369
370 insert into IGS_AD_PS_APPL_HIST_ALL (
371 ORG_ID,
372 PERSON_ID,
373 ADMISSION_APPL_NUMBER,
374 NOMINATED_COURSE_CD,
375 HIST_START_DT,
376 HIST_END_DT,
377 HIST_WHO,
378 TRANSFER_COURSE_CD,
379 BASIS_FOR_ADMISSION_TYPE,
380 ADMISSION_CD,
381 COURSE_RANK_SET,
382 COURSE_RANK_SCHEDULE,
383 REQ_FOR_RECONSIDERATION_IND,
384 REQ_FOR_ADV_STANDING_IND,
385 CREATION_DATE,
386 CREATED_BY,
387 LAST_UPDATE_DATE,
388 LAST_UPDATED_BY,
389 LAST_UPDATE_LOGIN
390 ) values (
391 NEW_REFERENCES.ORG_ID,
392 NEW_REFERENCES.PERSON_ID,
393 NEW_REFERENCES.ADMISSION_APPL_NUMBER,
394 NEW_REFERENCES.NOMINATED_COURSE_CD,
395 NEW_REFERENCES.HIST_START_DT,
396 NEW_REFERENCES.HIST_END_DT,
397 NEW_REFERENCES.HIST_WHO,
398 NEW_REFERENCES.TRANSFER_COURSE_CD,
399 NEW_REFERENCES.BASIS_FOR_ADMISSION_TYPE,
400 NEW_REFERENCES.ADMISSION_CD,
404 NEW_REFERENCES.REQ_FOR_ADV_STANDING_IND,
401 NEW_REFERENCES.COURSE_RANK_SET,
402 NEW_REFERENCES.COURSE_RANK_SCHEDULE,
403 NEW_REFERENCES.REQ_FOR_RECONSIDERATION_IND,
405 X_LAST_UPDATE_DATE,
406 X_LAST_UPDATED_BY,
407 X_LAST_UPDATE_DATE,
408 X_LAST_UPDATED_BY,
409 X_LAST_UPDATE_LOGIN
410 );
411
412 open c;
413 fetch c into X_ROWID;
414 if (c%notfound) then
415 close c;
416 raise no_data_found;
417 end if;
418 close c;
419
420 After_DML(
421 p_action =>'INSERT',
422 x_rowid => X_ROWID
423 );
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
428 -- Code to handle Security Policy error raised
429 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
430 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
431 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
432 -- that the ownerof policy function does not have privilege to access.
433 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
434 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
435 IGS_GE_MSG_STACK.ADD;
436 app_exception.raise_exception;
437 ELSE
438 RAISE;
439 END IF;
440
441 end INSERT_ROW;
442
443 procedure LOCK_ROW (
444 X_ROWID in VARCHAR2,
445 X_PERSON_ID in NUMBER,
446 X_ADMISSION_APPL_NUMBER in NUMBER,
447 X_NOMINATED_COURSE_CD in VARCHAR2,
448 X_HIST_START_DT in DATE,
449 X_HIST_END_DT in DATE,
450 X_HIST_WHO in NUMBER,
451 X_TRANSFER_COURSE_CD in VARCHAR2,
452 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
453 X_ADMISSION_CD in VARCHAR2,
454 X_COURSE_RANK_SET in VARCHAR2,
455 X_COURSE_RANK_SCHEDULE in VARCHAR2,
456 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
457 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2
458 ) as
459 cursor c1 is select
460 HIST_END_DT,
461 HIST_WHO,
462 TRANSFER_COURSE_CD,
463 BASIS_FOR_ADMISSION_TYPE,
464 ADMISSION_CD,
465 COURSE_RANK_SET,
466 COURSE_RANK_SCHEDULE,
467 REQ_FOR_RECONSIDERATION_IND,
468 REQ_FOR_ADV_STANDING_IND
469 from IGS_AD_PS_APPL_HIST_ALL
470 where ROWID = X_ROWID for update nowait;
471 tlinfo c1%rowtype;
472
473 begin
474 open c1;
475 fetch c1 into tlinfo;
476 if (c1%notfound) then
477 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
478 IGS_GE_MSG_STACK.ADD;
479 app_exception.raise_exception;
480 close c1;
481 return;
482 end if;
483 close c1;
484
485 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
486 AND (tlinfo.HIST_WHO = X_HIST_WHO)
487 AND ((tlinfo.TRANSFER_COURSE_CD = X_TRANSFER_COURSE_CD)
488 OR ((tlinfo.TRANSFER_COURSE_CD is null)
489 AND (X_TRANSFER_COURSE_CD is null)))
490 AND ((tlinfo.BASIS_FOR_ADMISSION_TYPE = X_BASIS_FOR_ADMISSION_TYPE)
491 OR ((tlinfo.BASIS_FOR_ADMISSION_TYPE is null)
492 AND (X_BASIS_FOR_ADMISSION_TYPE is null)))
493 AND ((tlinfo.ADMISSION_CD = X_ADMISSION_CD)
494 OR ((tlinfo.ADMISSION_CD is null)
495 AND (X_ADMISSION_CD is null)))
496 AND ((tlinfo.COURSE_RANK_SET = X_COURSE_RANK_SET)
497 OR ((tlinfo.COURSE_RANK_SET is null)
498 AND (X_COURSE_RANK_SET is null)))
499 AND ((tlinfo.COURSE_RANK_SCHEDULE = X_COURSE_RANK_SCHEDULE)
500 OR ((tlinfo.COURSE_RANK_SCHEDULE is null)
501 AND (X_COURSE_RANK_SCHEDULE is null)))
502 AND ((tlinfo.REQ_FOR_RECONSIDERATION_IND = X_REQ_FOR_RECONSIDERATION_IND)
503 OR ((tlinfo.REQ_FOR_RECONSIDERATION_IND is null)
504 AND (X_REQ_FOR_RECONSIDERATION_IND is null)))
505 AND ((tlinfo.REQ_FOR_ADV_STANDING_IND = X_REQ_FOR_ADV_STANDING_IND)
506 OR ((tlinfo.REQ_FOR_ADV_STANDING_IND is null)
507 AND (X_REQ_FOR_ADV_STANDING_IND is null)))
508 ) then
509 null;
510 else
511 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
512 IGS_GE_MSG_STACK.ADD;
513 app_exception.raise_exception;
514 end if;
515 return;
516 end LOCK_ROW;
517
518 procedure UPDATE_ROW (
519 X_ROWID in VARCHAR2,
520 X_PERSON_ID in NUMBER,
521 X_ADMISSION_APPL_NUMBER in NUMBER,
522 X_NOMINATED_COURSE_CD in VARCHAR2,
523 X_HIST_START_DT in DATE,
524 X_HIST_END_DT in DATE,
525 X_HIST_WHO in NUMBER,
526 X_TRANSFER_COURSE_CD in VARCHAR2,
527 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
528 X_ADMISSION_CD in VARCHAR2,
529 X_COURSE_RANK_SET in VARCHAR2,
530 X_COURSE_RANK_SCHEDULE in VARCHAR2,
531 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
532 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2,
533 X_MODE in VARCHAR2
534 ) as
535 X_LAST_UPDATE_DATE DATE;
536 X_LAST_UPDATED_BY NUMBER;
537 X_LAST_UPDATE_LOGIN NUMBER;
538 begin
539 X_LAST_UPDATE_DATE := SYSDATE;
540 if(X_MODE = 'I') then
541 X_LAST_UPDATED_BY := 1;
542 X_LAST_UPDATE_LOGIN := 0;
543 elsif (X_MODE = 'R') then
547 end if;
544 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
545 if X_LAST_UPDATED_BY is NULL then
546 X_LAST_UPDATED_BY := -1;
548 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
549 if X_LAST_UPDATE_LOGIN is NULL then
550 X_LAST_UPDATE_LOGIN := -1;
551 end if;
552 else
553 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
554 IGS_GE_MSG_STACK.ADD;
555 app_exception.raise_exception;
556 end if;
557
558 Before_DML(p_action =>'UPDATE',
559 x_rowid =>X_ROWID,
560 x_admission_appl_number => X_ADMISSION_APPL_NUMBER,
561 x_nominated_course_cd => X_NOMINATED_COURSE_CD,
562 x_hist_start_dt => X_HIST_START_DT,
563 x_hist_end_dt => X_HIST_END_DT,
564 x_hist_who => X_HIST_WHO,
565 x_transfer_course_cd => X_TRANSFER_COURSE_CD,
566 x_basis_for_admission_type => X_BASIS_FOR_ADMISSION_TYPE,
567 x_admission_cd => X_ADMISSION_CD,
568 x_course_rank_set => X_COURSE_RANK_SET,
569 x_course_rank_schedule => X_COURSE_RANK_SCHEDULE,
570 x_req_for_reconsideration_ind => X_REQ_FOR_RECONSIDERATION_IND,
571 x_req_for_adv_standing_ind => X_REQ_FOR_ADV_STANDING_IND,
572 x_person_id => X_PERSON_ID,
573 x_creation_date =>X_LAST_UPDATE_DATE,
574 x_created_by =>X_LAST_UPDATED_BY,
575 x_last_update_date =>X_LAST_UPDATE_DATE,
576 x_last_updated_by =>X_LAST_UPDATED_BY,
577 x_last_update_login =>X_LAST_UPDATE_LOGIN
578 );
579
580 update IGS_AD_PS_APPL_HIST_ALL set
581 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
582 HIST_WHO = NEW_REFERENCES.HIST_WHO,
583 TRANSFER_COURSE_CD = NEW_REFERENCES.TRANSFER_COURSE_CD,
584 BASIS_FOR_ADMISSION_TYPE = NEW_REFERENCES.BASIS_FOR_ADMISSION_TYPE,
585 ADMISSION_CD = NEW_REFERENCES.ADMISSION_CD,
586 COURSE_RANK_SET = NEW_REFERENCES.COURSE_RANK_SET,
587 COURSE_RANK_SCHEDULE = NEW_REFERENCES.COURSE_RANK_SCHEDULE,
588 REQ_FOR_RECONSIDERATION_IND = NEW_REFERENCES.REQ_FOR_RECONSIDERATION_IND,
589 REQ_FOR_ADV_STANDING_IND = NEW_REFERENCES.REQ_FOR_ADV_STANDING_IND,
590 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
591 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
592 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
593 where ROWID = X_ROWID
594 ;
595 if (sql%notfound) then
596 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
597 IGS_GE_MSG_STACK.ADD;
598 app_exception.raise_exception;
599 end if;
600
601 After_DML(
602 p_action =>'UPDATE',
603 x_rowid => X_ROWID
604 );
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
609 -- Code to handle Security Policy error raised
610 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
611 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
612 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
613 -- that the ownerof policy function does not have privilege to access.
614 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
615 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
616 IGS_GE_MSG_STACK.ADD;
617 app_exception.raise_exception;
618 ELSE
619 RAISE;
620 END IF;
621
622 end UPDATE_ROW;
623
624 procedure ADD_ROW (
625 X_ROWID in out NOCOPY VARCHAR2,
626 X_ORG_ID in NUMBER,
627 X_PERSON_ID in NUMBER,
628 X_ADMISSION_APPL_NUMBER in NUMBER,
629 X_NOMINATED_COURSE_CD in VARCHAR2,
630 X_HIST_START_DT in DATE,
631 X_HIST_END_DT in DATE,
632 X_HIST_WHO in NUMBER,
633 X_TRANSFER_COURSE_CD in VARCHAR2,
634 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
635 X_ADMISSION_CD in VARCHAR2,
636 X_COURSE_RANK_SET in VARCHAR2,
637 X_COURSE_RANK_SCHEDULE in VARCHAR2,
638 X_REQ_FOR_RECONSIDERATION_IND in VARCHAR2,
639 X_REQ_FOR_ADV_STANDING_IND in VARCHAR2,
640 X_MODE in VARCHAR2
641 ) as
642 cursor c1 is select rowid from IGS_AD_PS_APPL_HIST_ALL
643 where PERSON_ID = X_PERSON_ID
644 and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
645 and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
646 and HIST_START_DT = X_HIST_START_DT
647 ;
648 begin
649 open c1;
650 fetch c1 into X_ROWID;
651 if (c1%notfound) then
652 close c1;
653 INSERT_ROW (
654 X_ROWID,
655 X_ORG_ID,
656 X_PERSON_ID,
657 X_ADMISSION_APPL_NUMBER,
658 X_NOMINATED_COURSE_CD,
659 X_HIST_START_DT,
660 X_HIST_END_DT,
661 X_HIST_WHO,
662 X_TRANSFER_COURSE_CD,
663 X_BASIS_FOR_ADMISSION_TYPE,
664 X_ADMISSION_CD,
665 X_COURSE_RANK_SET,
666 X_COURSE_RANK_SCHEDULE,
667 X_REQ_FOR_RECONSIDERATION_IND,
668 X_REQ_FOR_ADV_STANDING_IND,
669 X_MODE);
670 return;
671 end if;
672 close c1;
673 UPDATE_ROW (
674 X_ROWID,
675 X_PERSON_ID,
676 X_ADMISSION_APPL_NUMBER,
677 X_NOMINATED_COURSE_CD,
678 X_HIST_START_DT,
679 X_HIST_END_DT,
680 X_HIST_WHO,
681 X_TRANSFER_COURSE_CD,
682 X_BASIS_FOR_ADMISSION_TYPE,
683 X_ADMISSION_CD,
684 X_COURSE_RANK_SET,
685 X_COURSE_RANK_SCHEDULE,
686 X_REQ_FOR_RECONSIDERATION_IND,
687 X_REQ_FOR_ADV_STANDING_IND,
688 X_MODE);
689 end ADD_ROW;
690
691 procedure DELETE_ROW (
692 X_ROWID in VARCHAR2
693 ) as
694 begin
695
696 Before_DML(
697 p_action =>'DELETE',
698 x_rowid => X_ROWID
699 );
700
701
702 delete from IGS_AD_PS_APPL_HIST_ALL
703 where ROWID = X_ROWID;
704 if (sql%notfound) then
705 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
706 IGS_GE_MSG_STACK.ADD;
707 app_exception.raise_exception;
708 end if;
709
710 After_DML(
711 p_action =>'DELETE',
712 x_rowid => X_ROWID
713 );
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
718 -- Code to handle Security Policy error raised
719 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
720 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
721 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
722 -- that the ownerof policy function does not have privilege to access.
723 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
724 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
725 IGS_GE_MSG_STACK.ADD;
726 app_exception.raise_exception;
727 ELSE
728 RAISE;
729 END IF;
730
731 end DELETE_ROW;
732
733 end IGS_AD_PS_APPL_HIST_PKG;