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