1 PACKAGE BODY igs_ad_app_intent_pkg AS
2 /* $Header: IGSAI94B.pls 120.3 2005/10/03 08:20:10 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_app_intent%RowType;
5 new_references igs_ad_app_intent%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_app_intent_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_admission_appl_number IN NUMBER DEFAULT NULL,
13 x_intent_type_id IN NUMBER DEFAULT NULL,
14 x_attribute_category IN VARCHAR2 DEFAULT NULL,
15 x_attribute1 IN VARCHAR2 DEFAULT NULL,
16 x_attribute2 IN VARCHAR2 DEFAULT NULL,
17 x_attribute3 IN VARCHAR2 DEFAULT NULL,
18 x_attribute4 IN VARCHAR2 DEFAULT NULL,
19 x_attribute5 IN VARCHAR2 DEFAULT NULL,
20 x_attribute6 IN VARCHAR2 DEFAULT NULL,
21 x_attribute7 IN VARCHAR2 DEFAULT NULL,
22 x_attribute8 IN VARCHAR2 DEFAULT NULL,
23 x_attribute9 IN VARCHAR2 DEFAULT NULL,
24 x_attribute10 IN VARCHAR2 DEFAULT NULL,
25 x_attribute11 IN VARCHAR2 DEFAULT NULL,
26 x_attribute12 IN VARCHAR2 DEFAULT NULL,
27 x_attribute13 IN VARCHAR2 DEFAULT NULL,
28 x_attribute14 IN VARCHAR2 DEFAULT NULL,
29 x_attribute15 IN VARCHAR2 DEFAULT NULL,
30 x_attribute16 IN VARCHAR2 DEFAULT NULL,
31 x_attribute17 IN VARCHAR2 DEFAULT NULL,
32 x_attribute18 IN VARCHAR2 DEFAULT NULL,
33 x_attribute19 IN VARCHAR2 DEFAULT NULL,
34 x_attribute20 IN VARCHAR2 DEFAULT NULL,
35 x_creation_date IN DATE DEFAULT NULL,
36 x_created_by IN NUMBER DEFAULT NULL,
37 x_last_update_date IN DATE DEFAULT NULL,
38 x_last_updated_by IN NUMBER DEFAULT NULL,
39 x_last_update_login IN NUMBER DEFAULT NULL
40 ) AS
41
42 /*************************************************************
43 Created By :
44 Date Created By :
45 Purpose :
46 Know limitations, enhancements or remarks
47 Change History
48 Who When What
49 kamohan 1/21/02 Removed the reference to nominated_course_cd and sequence_number
50
51 (reverse chronological order - newest change first)
52 ***************************************************************/
53
54 CURSOR cur_old_ref_values IS
55 SELECT *
56 FROM IGS_AD_APP_INTENT
57 WHERE rowid = x_rowid;
58
59 BEGIN
60
61 l_rowid := x_rowid;
62
63 -- Code for setting the Old and New Reference Values.
64 -- Populate Old Values.
65 Open cur_old_ref_values;
66 Fetch cur_old_ref_values INTO old_references;
67 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
68 Close cur_old_ref_values;
69 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
70 IGS_GE_MSG_STACK.ADD;
71 App_Exception.Raise_Exception;
72 Return;
73 END IF;
74 Close cur_old_ref_values;
75
76 -- Populate New Values.
77 new_references.app_intent_id := x_app_intent_id;
78 new_references.person_id := x_person_id;
79 new_references.admission_appl_number := x_admission_appl_number;
80 new_references.intent_type_id := x_intent_type_id;
81 new_references.attribute_category := x_attribute_category;
82 new_references.attribute1 := x_attribute1;
83 new_references.attribute2 := x_attribute2;
84 new_references.attribute3 := x_attribute3;
85 new_references.attribute4 := x_attribute4;
86 new_references.attribute5 := x_attribute5;
87 new_references.attribute6 := x_attribute6;
88 new_references.attribute7 := x_attribute7;
89 new_references.attribute8 := x_attribute8;
90 new_references.attribute9 := x_attribute9;
91 new_references.attribute10 := x_attribute10;
92 new_references.attribute11 := x_attribute11;
93 new_references.attribute12 := x_attribute12;
94 new_references.attribute13 := x_attribute13;
95 new_references.attribute14 := x_attribute14;
96 new_references.attribute15 := x_attribute15;
97 new_references.attribute16 := x_attribute16;
98 new_references.attribute17 := x_attribute17;
99 new_references.attribute18 := x_attribute18;
100 new_references.attribute19 := x_attribute19;
101 new_references.attribute20 := x_attribute20;
102 IF (p_action = 'UPDATE') THEN
103 new_references.creation_date := old_references.creation_date;
104 new_references.created_by := old_references.created_by;
105 ELSE
106 new_references.creation_date := x_creation_date;
107 new_references.created_by := x_created_by;
108 END IF;
109 new_references.last_update_date := x_last_update_date;
110 new_references.last_updated_by := x_last_updated_by;
111 new_references.last_update_login := x_last_update_login;
112
113 END Set_Column_Values;
114
115 PROCEDURE Check_Constraints (
116 Column_Name IN VARCHAR2 DEFAULT NULL,
117 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
118 /*************************************************************
119 Created By :
120 Date Created By :
121 Purpose :
122 Know limitations, enhancements or remarks
123 Change History
124 Who When What
125
126 (reverse chronological order - newest change first)
127 ***************************************************************/
128
129 BEGIN
130
131 IF column_name IS NULL THEN
132 NULL;
133 NULL;
134 END IF;
135
136 END Check_Constraints;
137
138 PROCEDURE Check_Uniqueness AS
139 /*************************************************************
140 Created By :
141 Date Created By :
142 Purpose :
143 Know limitations, enhancements or remarks
144 Change History
145 Who When What
146
147 (reverse chronological order - newest change first)
148 ***************************************************************/
149
150 begin
151 IF Get_Uk_For_Validation (
152 new_references.admission_appl_number
153 ,new_references.intent_type_id
154 ,new_references.person_id
155 ) THEN
156 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
157 IGS_GE_MSG_STACK.ADD;
158 app_exception.raise_exception;
159 END IF;
160 END Check_Uniqueness ;
161 PROCEDURE Check_Parent_Existance AS
162 /*************************************************************
163 Created By :
164 Date Created By :
165 Purpose :
166 Know limitations, enhancements or remarks
167 Change History
168 Who When What
169
170 (reverse chronological order - newest change first)
171 ***************************************************************/
172
173 BEGIN
174
175 IF (((old_references.intent_type_id = new_references.intent_type_id)) OR
176 ((new_references.intent_type_id IS NULL))) THEN
177 NULL;
178 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
179 new_references.intent_type_id ,
180 'INTENT_TYPES' ,
181 'N'
182 ) THEN
183 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
184 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_INTENT_TYPE'));
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188
189 IF (((old_references.person_id = new_references.person_id) AND
190 (old_references.admission_appl_number = new_references.admission_appl_number) ) OR
191 ((new_references.person_id IS NULL) OR
192 (new_references.admission_appl_number IS NULL))) THEN
193 NULL;
194 ELSIF NOT Igs_Ad_Appl_Pkg.Get_PK_For_Validation (
195 new_references.person_id,
196 new_references.admission_appl_number
197 ) THEN
198 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
199 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL'));
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203
204 END Check_Parent_Existance;
205
206 FUNCTION Get_PK_For_Validation (
207 x_app_intent_id IN NUMBER
208 ) RETURN BOOLEAN AS
209
210 /*************************************************************
211 Created By :
212 Date Created By :
213 Purpose :
214 Know limitations, enhancements or remarks
215 Change History
216 Who When What
217
218 (reverse chronological order - newest change first)
219 ***************************************************************/
220
221 CURSOR cur_rowid IS
222 SELECT rowid
223 FROM igs_ad_app_intent
224 WHERE app_intent_id = x_app_intent_id
225 FOR UPDATE NOWAIT;
226
227 lv_rowid cur_rowid%RowType;
228
229 BEGIN
230
231 Open cur_rowid;
232 Fetch cur_rowid INTO lv_rowid;
233 IF (cur_rowid%FOUND) THEN
234 Close cur_rowid;
235 Return(TRUE);
236 ELSE
237 Close cur_rowid;
238 Return(FALSE);
239 END IF;
240 END Get_PK_For_Validation;
241
242 FUNCTION Get_UK_For_Validation (
243 x_admission_appl_number IN NUMBER,
244 x_intent_type_id IN NUMBER,
245 x_person_id IN NUMBER
246 ) RETURN BOOLEAN AS
247
248 /*************************************************************
249 Created By :
250 Date Created By :
251 Purpose :
252 Know limitations, enhancements or remarks
253 Change History
254 Who When What
255
256 (reverse chronological order - newest change first)
257 ***************************************************************/
258
259 CURSOR cur_rowid IS
260 SELECT rowid
261 FROM igs_ad_app_intent
262 WHERE admission_appl_number = x_admission_appl_number
263 AND intent_type_id = x_intent_type_id
264 AND person_id = x_person_id
265 AND ((l_rowid is null) or (rowid <> l_rowid));
266 lv_rowid cur_rowid%RowType;
267
268 BEGIN
269
270 Open cur_rowid;
271 Fetch cur_rowid INTO lv_rowid;
272 IF (cur_rowid%FOUND) THEN
273 Close cur_rowid;
274 return (true);
275 ELSE
276 close cur_rowid;
277 return(false);
278 END IF;
279 END Get_UK_For_Validation ;
280 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
281 x_code_id IN NUMBER
282 ) AS
283
284 /*************************************************************
285 Created By :
286 Date Created By :
287 Purpose :
288 Know limitations, enhancements or remarks
289 Change History
290 Who When What
291
292 (reverse chronological order - newest change first)
293 ***************************************************************/
294
295 CURSOR cur_rowid IS
296 SELECT rowid
297 FROM igs_ad_app_intent
298 WHERE intent_type_id = x_code_id ;
299
300 lv_rowid cur_rowid%RowType;
301
302 BEGIN
303
304 Open cur_rowid;
305 Fetch cur_rowid INTO lv_rowid;
306 IF (cur_rowid%FOUND) THEN
307 Close cur_rowid;
308 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAINT_ACDC_FK');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 Return;
312 END IF;
313 Close cur_rowid;
314
315 END Get_FK_Igs_Ad_Code_Classes;
316
317 PROCEDURE Get_FK_Igs_Ad_Appl (
318 x_person_id IN NUMBER,
319 x_admission_appl_number IN NUMBER
320 ) AS
321
322 /*************************************************************
323 Created By :
324 Date Created By :
325 Purpose :
326 Know limitations, enhancements or remarks
327 Change History
328 Who When What
329
330 (reverse chronological order - newest change first)
331 ***************************************************************/
332
333 CURSOR cur_rowid IS
334 SELECT rowid
335 FROM igs_ad_app_intent
336 WHERE person_id = x_person_id
337 AND admission_appl_number = x_admission_appl_number;
338
339 lv_rowid cur_rowid%RowType;
340
341 BEGIN
342
343 Open cur_rowid;
344 Fetch cur_rowid INTO lv_rowid;
345 IF (cur_rowid%FOUND) THEN
346 Close cur_rowid;
347 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAINT_ACAI_FK');
348 IGS_GE_MSG_STACK.ADD;
349 App_Exception.Raise_Exception;
350 Return;
351 END IF;
352 Close cur_rowid;
353
354 END Get_FK_Igs_Ad_Appl;
355
356 PROCEDURE Before_DML (
357 p_action IN VARCHAR2,
358 x_rowid IN VARCHAR2 DEFAULT NULL,
359 x_app_intent_id IN NUMBER DEFAULT NULL,
360 x_person_id IN NUMBER DEFAULT NULL,
361 x_admission_appl_number IN NUMBER DEFAULT NULL,
362 x_intent_type_id IN NUMBER DEFAULT NULL,
363 x_attribute_category IN VARCHAR2 DEFAULT NULL,
364 x_attribute1 IN VARCHAR2 DEFAULT NULL,
365 x_attribute2 IN VARCHAR2 DEFAULT NULL,
366 x_attribute3 IN VARCHAR2 DEFAULT NULL,
367 x_attribute4 IN VARCHAR2 DEFAULT NULL,
371 x_attribute8 IN VARCHAR2 DEFAULT NULL,
368 x_attribute5 IN VARCHAR2 DEFAULT NULL,
369 x_attribute6 IN VARCHAR2 DEFAULT NULL,
370 x_attribute7 IN VARCHAR2 DEFAULT NULL,
372 x_attribute9 IN VARCHAR2 DEFAULT NULL,
373 x_attribute10 IN VARCHAR2 DEFAULT NULL,
374 x_attribute11 IN VARCHAR2 DEFAULT NULL,
375 x_attribute12 IN VARCHAR2 DEFAULT NULL,
376 x_attribute13 IN VARCHAR2 DEFAULT NULL,
377 x_attribute14 IN VARCHAR2 DEFAULT NULL,
378 x_attribute15 IN VARCHAR2 DEFAULT NULL,
379 x_attribute16 IN VARCHAR2 DEFAULT NULL,
380 x_attribute17 IN VARCHAR2 DEFAULT NULL,
381 x_attribute18 IN VARCHAR2 DEFAULT NULL,
382 x_attribute19 IN VARCHAR2 DEFAULT NULL,
383 x_attribute20 IN VARCHAR2 DEFAULT NULL,
384 x_creation_date IN DATE DEFAULT NULL,
385 x_created_by IN NUMBER DEFAULT NULL,
386 x_last_update_date IN DATE DEFAULT NULL,
387 x_last_updated_by IN NUMBER DEFAULT NULL,
388 x_last_update_login IN NUMBER DEFAULT NULL
389 ) AS
390 /*************************************************************
391 Created By :
392 Date Created By :
393 Purpose :
394 Know limitations, enhancements or remarks
395 Change History
396 Who When What
397
398 (reverse chronological order - newest change first)
399 ***************************************************************/
400
401 BEGIN
402
403 Set_Column_Values (
404 p_action,
405 x_rowid,
406 x_app_intent_id,
407 x_person_id,
408 x_admission_appl_number,
409 x_intent_type_id,
410 x_attribute_category,
411 x_attribute1,
412 x_attribute2,
413 x_attribute3,
414 x_attribute4,
415 x_attribute5,
416 x_attribute6,
417 x_attribute7,
418 x_attribute8,
419 x_attribute9,
420 x_attribute10,
421 x_attribute11,
422 x_attribute12,
423 x_attribute13,
424 x_attribute14,
425 x_attribute15,
426 x_attribute16,
427 x_attribute17,
428 x_attribute18,
429 x_attribute19,
430 x_attribute20,
431 x_creation_date,
432 x_created_by,
433 x_last_update_date,
434 x_last_updated_by,
435 x_last_update_login
436 );
437
438 igs_ad_gen_002.check_adm_appl_inst_stat(
439 nvl(x_person_id,old_references.person_id),
440 nvl(x_admission_appl_number,old_references.admission_appl_number),
441 NULL,
442 NULL,
443 'Y' -- proceed phase - apadegal adtd001 igs.m
444 );
445
446 IF (p_action = 'INSERT') THEN
447 -- Call all the procedures related to Before Insert.
448 Null;
449 IF Get_Pk_For_Validation(
450 new_references.app_intent_id) THEN
451 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 END IF;
455 Check_Uniqueness;
456 Check_Constraints;
457 Check_Parent_Existance;
458 ELSIF (p_action = 'UPDATE') THEN
459 -- Call all the procedures related to Before Update.
460 Null;
461 Check_Uniqueness;
462 Check_Constraints;
463 Check_Parent_Existance;
464 ELSIF (p_action = 'DELETE') THEN
465 -- Call all the procedures related to Before Delete.
466 Null;
467 ELSIF (p_action = 'VALIDATE_INSERT') THEN
468 -- Call all the procedures related to Before Insert.
469 IF Get_PK_For_Validation (
470 new_references.app_intent_id) THEN
471 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475 Check_Uniqueness;
476 Check_Constraints;
477 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
478 Check_Uniqueness;
479 Check_Constraints;
480 ELSIF (p_action = 'VALIDATE_DELETE') THEN
481 Null;
482 END IF;
483 l_rowid := NULL;
484 END Before_DML;
485
486 PROCEDURE After_DML (
487 p_action IN VARCHAR2,
488 x_rowid IN VARCHAR2
489 ) IS
490 /*************************************************************
491 Created By :
492 Date Created By :
493 Purpose :
494 Know limitations, enhancements or remarks
495 Change History
496 Who When What
497
498 (reverse chronological order - newest change first)
499 ***************************************************************/
500
501 BEGIN
502
503 l_rowid := x_rowid;
504
505 IF (p_action = 'INSERT') THEN
506 -- Call all the procedures related to After Insert.
507 Null;
508 ELSIF (p_action = 'UPDATE') THEN
509 -- Call all the procedures related to After Update.
510 Null;
511 ELSIF (p_action = 'DELETE') THEN
512 -- Call all the procedures related to After Delete.
513 Null;
514 END IF;
515
516 l_rowid:=NULL;
517 END After_DML;
518
522 x_PERSON_ID IN NUMBER,
519 procedure INSERT_ROW (
520 X_ROWID in out NOCOPY VARCHAR2,
521 x_APP_INTENT_ID IN OUT NOCOPY NUMBER,
523 x_ADMISSION_APPL_NUMBER IN NUMBER,
524 x_INTENT_TYPE_ID IN NUMBER,
525 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
526 x_ATTRIBUTE1 IN VARCHAR2,
527 x_ATTRIBUTE2 IN VARCHAR2,
528 x_ATTRIBUTE3 IN VARCHAR2,
529 x_ATTRIBUTE4 IN VARCHAR2,
530 x_ATTRIBUTE5 IN VARCHAR2,
531 x_ATTRIBUTE6 IN VARCHAR2,
532 x_ATTRIBUTE7 IN VARCHAR2,
533 x_ATTRIBUTE8 IN VARCHAR2,
534 x_ATTRIBUTE9 IN VARCHAR2,
535 x_ATTRIBUTE10 IN VARCHAR2,
536 x_ATTRIBUTE11 IN VARCHAR2,
537 x_ATTRIBUTE12 IN VARCHAR2,
538 x_ATTRIBUTE13 IN VARCHAR2,
539 x_ATTRIBUTE14 IN VARCHAR2,
540 x_ATTRIBUTE15 IN VARCHAR2,
541 x_ATTRIBUTE16 IN VARCHAR2,
542 x_ATTRIBUTE17 IN VARCHAR2,
543 x_ATTRIBUTE18 IN VARCHAR2,
544 x_ATTRIBUTE19 IN VARCHAR2,
545 x_ATTRIBUTE20 IN VARCHAR2,
546 X_MODE in VARCHAR2
547 ) AS
548 /*************************************************************
549 Created By :
550 Date Created By :
551 Purpose :
552 Know limitations, enhancements or remarks
553 Change History
554 Who When What
555
556 (reverse chronological order - newest change first)
557 ***************************************************************/
558
559 cursor C is select ROWID from IGS_AD_APP_INTENT
560 where APP_INTENT_ID= X_APP_INTENT_ID
561 ;
562 X_LAST_UPDATE_DATE DATE ;
563 X_LAST_UPDATED_BY NUMBER ;
564 X_LAST_UPDATE_LOGIN NUMBER ;
565 X_REQUEST_ID NUMBER;
566 X_PROGRAM_ID NUMBER;
567 X_PROGRAM_APPLICATION_ID NUMBER;
568 X_PROGRAM_UPDATE_DATE DATE;
569 begin
570 NULL;
571 X_LAST_UPDATE_DATE := SYSDATE;
572 if(X_MODE = 'I') then
573 X_LAST_UPDATED_BY := 1;
574 X_LAST_UPDATE_LOGIN := 0;
575 elsif (X_MODE IN ('R', 'S')) then
576 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
577 if X_LAST_UPDATED_BY is NULL then
578 X_LAST_UPDATED_BY := -1;
579 end if;
580 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
581 if X_LAST_UPDATE_LOGIN is NULL then
582 X_LAST_UPDATE_LOGIN := -1;
583 end if;
584 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
585 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
586 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
587 if (X_REQUEST_ID = -1) then
588 X_REQUEST_ID := NULL;
589 X_PROGRAM_ID := NULL;
590 X_PROGRAM_APPLICATION_ID := NULL;
591 X_PROGRAM_UPDATE_DATE := NULL;
592 else
593 X_PROGRAM_UPDATE_DATE := SYSDATE;
594 end if;
595 else
596 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
597 IGS_GE_MSG_STACK.ADD;
598 app_exception.raise_exception;
599 end if;
600
601 X_APP_INTENT_ID := -1;
602 Before_DML(
603 p_action=>'INSERT',
604 x_rowid=>X_ROWID,
605 x_app_intent_id=>X_APP_INTENT_ID,
606 x_person_id=>X_PERSON_ID,
607 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
608 x_intent_type_id=>X_INTENT_TYPE_ID,
609 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
610 x_attribute1=>X_ATTRIBUTE1,
611 x_attribute2=>X_ATTRIBUTE2,
612 x_attribute3=>X_ATTRIBUTE3,
613 x_attribute4=>X_ATTRIBUTE4,
614 x_attribute5=>X_ATTRIBUTE5,
615 x_attribute6=>X_ATTRIBUTE6,
616 x_attribute7=>X_ATTRIBUTE7,
617 x_attribute8=>X_ATTRIBUTE8,
618 x_attribute9=>X_ATTRIBUTE9,
619 x_attribute10=>X_ATTRIBUTE10,
620 x_attribute11=>X_ATTRIBUTE11,
621 x_attribute12=>X_ATTRIBUTE12,
622 x_attribute13=>X_ATTRIBUTE13,
623 x_attribute14=>X_ATTRIBUTE14,
624 x_attribute15=>X_ATTRIBUTE15,
625 x_attribute16=>X_ATTRIBUTE16,
626 x_attribute17=>X_ATTRIBUTE17,
627 x_attribute18=>X_ATTRIBUTE18,
628 x_attribute19=>X_ATTRIBUTE19,
629 x_attribute20=>X_ATTRIBUTE20,
630 x_creation_date=>X_LAST_UPDATE_DATE,
631 x_created_by=>X_LAST_UPDATED_BY,
632 x_last_update_date=>X_LAST_UPDATE_DATE,
633 x_last_updated_by=>X_LAST_UPDATED_BY,
634 x_last_update_login=>X_LAST_UPDATE_LOGIN);
635 IF (x_mode = 'S') THEN
636 igs_sc_gen_001.set_ctx('R');
637 END IF;
638 insert into IGS_AD_APP_INTENT (
639 APP_INTENT_ID
640 ,PERSON_ID
641 ,ADMISSION_APPL_NUMBER
642 ,INTENT_TYPE_ID
643 ,ATTRIBUTE_CATEGORY
644 ,ATTRIBUTE1
645 ,ATTRIBUTE2
646 ,ATTRIBUTE3
647 ,ATTRIBUTE4
648 ,ATTRIBUTE5
649 ,ATTRIBUTE6
650 ,ATTRIBUTE7
651 ,ATTRIBUTE8
652 ,ATTRIBUTE9
653 ,ATTRIBUTE10
654 ,ATTRIBUTE11
655 ,ATTRIBUTE12
656 ,ATTRIBUTE13
657 ,ATTRIBUTE14
658 ,ATTRIBUTE15
659 ,ATTRIBUTE16
660 ,ATTRIBUTE17
661 ,ATTRIBUTE18
662 ,ATTRIBUTE19
663 ,ATTRIBUTE20
664 ,CREATION_DATE
665 ,CREATED_BY
669 ,REQUEST_ID
666 ,LAST_UPDATE_DATE
667 ,LAST_UPDATED_BY
668 ,LAST_UPDATE_LOGIN
670 ,PROGRAM_ID
671 ,PROGRAM_APPLICATION_ID
672 ,PROGRAM_UPDATE_DATE
673 ) values (
674 IGS_AD_APP_INTENT_S.NEXTVAL
675 ,NEW_REFERENCES.PERSON_ID
676 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
677 ,NEW_REFERENCES.INTENT_TYPE_ID
678 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
679 ,NEW_REFERENCES.ATTRIBUTE1
680 ,NEW_REFERENCES.ATTRIBUTE2
681 ,NEW_REFERENCES.ATTRIBUTE3
682 ,NEW_REFERENCES.ATTRIBUTE4
683 ,NEW_REFERENCES.ATTRIBUTE5
684 ,NEW_REFERENCES.ATTRIBUTE6
685 ,NEW_REFERENCES.ATTRIBUTE7
686 ,NEW_REFERENCES.ATTRIBUTE8
687 ,NEW_REFERENCES.ATTRIBUTE9
688 ,NEW_REFERENCES.ATTRIBUTE10
689 ,NEW_REFERENCES.ATTRIBUTE11
690 ,NEW_REFERENCES.ATTRIBUTE12
691 ,NEW_REFERENCES.ATTRIBUTE13
692 ,NEW_REFERENCES.ATTRIBUTE14
693 ,NEW_REFERENCES.ATTRIBUTE15
694 ,NEW_REFERENCES.ATTRIBUTE16
695 ,NEW_REFERENCES.ATTRIBUTE17
696 ,NEW_REFERENCES.ATTRIBUTE18
697 ,NEW_REFERENCES.ATTRIBUTE19
698 ,NEW_REFERENCES.ATTRIBUTE20
699 ,X_LAST_UPDATE_DATE
700 ,X_LAST_UPDATED_BY
701 ,X_LAST_UPDATE_DATE
702 ,X_LAST_UPDATED_BY
703 ,X_LAST_UPDATE_LOGIN
704 ,X_REQUEST_ID
705 ,X_PROGRAM_ID
706 ,X_PROGRAM_APPLICATION_ID
707 ,X_PROGRAM_UPDATE_DATE
708 )RETURNING APP_INTENT_ID INTO X_APP_INTENT_ID;
709 IF (x_mode = 'S') THEN
710 igs_sc_gen_001.unset_ctx('R');
711 END IF;
712
713 open c;
714 fetch c into X_ROWID;
715 if (c%notfound) then
716 close c;
717 raise no_data_found;
718 end if;
719 close c;
720 After_DML (
721 p_action => 'INSERT' ,
722 x_rowid => X_ROWID );
723 EXCEPTION
724 WHEN OTHERS THEN
725 IF (x_mode = 'S') THEN
726 igs_sc_gen_001.unset_ctx('R');
727 END IF;
728 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
729 -- Code to handle Security Policy error raised
730 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
731 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
732 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
733 -- that the ownerof policy function does not have privilege to access.
734 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
735 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
736 IGS_GE_MSG_STACK.ADD;
737 app_exception.raise_exception;
738 ELSE
739 RAISE;
740 END IF;
741 END INSERT_ROW;
742 PROCEDURE LOCK_ROW (
743 X_ROWID in VARCHAR2,
744 x_APP_INTENT_ID IN NUMBER,
745 x_PERSON_ID IN NUMBER,
746 x_ADMISSION_APPL_NUMBER IN NUMBER,
747 x_INTENT_TYPE_ID IN NUMBER,
748 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
749 x_ATTRIBUTE1 IN VARCHAR2,
750 x_ATTRIBUTE2 IN VARCHAR2,
751 x_ATTRIBUTE3 IN VARCHAR2,
752 x_ATTRIBUTE4 IN VARCHAR2,
753 x_ATTRIBUTE5 IN VARCHAR2,
754 x_ATTRIBUTE6 IN VARCHAR2,
755 x_ATTRIBUTE7 IN VARCHAR2,
756 x_ATTRIBUTE8 IN VARCHAR2,
757 x_ATTRIBUTE9 IN VARCHAR2,
758 x_ATTRIBUTE10 IN VARCHAR2,
759 x_ATTRIBUTE11 IN VARCHAR2,
760 x_ATTRIBUTE12 IN VARCHAR2,
761 x_ATTRIBUTE13 IN VARCHAR2,
762 x_ATTRIBUTE14 IN VARCHAR2,
763 x_ATTRIBUTE15 IN VARCHAR2,
764 x_ATTRIBUTE16 IN VARCHAR2,
765 x_ATTRIBUTE17 IN VARCHAR2,
766 x_ATTRIBUTE18 IN VARCHAR2,
767 x_ATTRIBUTE19 IN VARCHAR2,
768 x_ATTRIBUTE20 IN VARCHAR2 ) AS
769 /*************************************************************
770 Created By :
771 Date Created By :
772 Purpose :
773 Know limitations, enhancements or remarks
774 Change History
775 Who When What
776
777 (reverse chronological order - newest change first)
778 ***************************************************************/
779
780 cursor c1 is select
781 PERSON_ID
782 , ADMISSION_APPL_NUMBER
783 , INTENT_TYPE_ID
784 , ATTRIBUTE_CATEGORY
785 , ATTRIBUTE1
786 , ATTRIBUTE2
787 , ATTRIBUTE3
788 , ATTRIBUTE4
789 , ATTRIBUTE5
790 , ATTRIBUTE6
791 , ATTRIBUTE7
792 , ATTRIBUTE8
793 , ATTRIBUTE9
794 , ATTRIBUTE10
795 , ATTRIBUTE11
796 , ATTRIBUTE12
797 , ATTRIBUTE13
798 , ATTRIBUTE14
799 , ATTRIBUTE15
800 , ATTRIBUTE16
801 , ATTRIBUTE17
802 , ATTRIBUTE18
803 , ATTRIBUTE19
804 , ATTRIBUTE20
805 from IGS_AD_APP_INTENT
806 where ROWID = X_ROWID
807 for update nowait;
808 tlinfo c1%rowtype;
809 begin
810 open c1;
811 fetch c1 into tlinfo;
812 if (c1%notfound) then
816 app_exception.raise_exception;
813 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
814 IGS_GE_MSG_STACK.ADD;
815 close c1;
817 return;
818 end if;
819 close c1;
820 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
821 AND (tlinfo.ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER)
822 AND (tlinfo.INTENT_TYPE_ID = X_INTENT_TYPE_ID)
823 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
824 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
825 AND (X_ATTRIBUTE_CATEGORY is null)))
826 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
827 OR ((tlinfo.ATTRIBUTE1 is null)
828 AND (X_ATTRIBUTE1 is null)))
829 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
830 OR ((tlinfo.ATTRIBUTE2 is null)
831 AND (X_ATTRIBUTE2 is null)))
832 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
833 OR ((tlinfo.ATTRIBUTE3 is null)
834 AND (X_ATTRIBUTE3 is null)))
835 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
836 OR ((tlinfo.ATTRIBUTE4 is null)
837 AND (X_ATTRIBUTE4 is null)))
838 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
839 OR ((tlinfo.ATTRIBUTE5 is null)
840 AND (X_ATTRIBUTE5 is null)))
841 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
842 OR ((tlinfo.ATTRIBUTE6 is null)
843 AND (X_ATTRIBUTE6 is null)))
844 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
845 OR ((tlinfo.ATTRIBUTE7 is null)
846 AND (X_ATTRIBUTE7 is null)))
847 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
848 OR ((tlinfo.ATTRIBUTE8 is null)
849 AND (X_ATTRIBUTE8 is null)))
850 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
851 OR ((tlinfo.ATTRIBUTE9 is null)
852 AND (X_ATTRIBUTE9 is null)))
853 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
854 OR ((tlinfo.ATTRIBUTE10 is null)
855 AND (X_ATTRIBUTE10 is null)))
856 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
857 OR ((tlinfo.ATTRIBUTE11 is null)
858 AND (X_ATTRIBUTE11 is null)))
859 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
860 OR ((tlinfo.ATTRIBUTE12 is null)
861 AND (X_ATTRIBUTE12 is null)))
862 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
863 OR ((tlinfo.ATTRIBUTE13 is null)
864 AND (X_ATTRIBUTE13 is null)))
865 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
866 OR ((tlinfo.ATTRIBUTE14 is null)
867 AND (X_ATTRIBUTE14 is null)))
868 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
869 OR ((tlinfo.ATTRIBUTE15 is null)
870 AND (X_ATTRIBUTE15 is null)))
871 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
872 OR ((tlinfo.ATTRIBUTE16 is null)
873 AND (X_ATTRIBUTE16 is null)))
874 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
875 OR ((tlinfo.ATTRIBUTE17 is null)
876 AND (X_ATTRIBUTE17 is null)))
877 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
878 OR ((tlinfo.ATTRIBUTE18 is null)
879 AND (X_ATTRIBUTE18 is null)))
880 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
881 OR ((tlinfo.ATTRIBUTE19 is null)
882 AND (X_ATTRIBUTE19 is null)))
883 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
884 OR ((tlinfo.ATTRIBUTE20 is null)
885 AND (X_ATTRIBUTE20 is null)))
886 ) then
887 null;
888 else
889 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
890 IGS_GE_MSG_STACK.ADD;
891 app_exception.raise_exception;
892 end if;
893 return;
894 end LOCK_ROW;
895 Procedure UPDATE_ROW (
896 X_ROWID in VARCHAR2,
897 x_APP_INTENT_ID IN NUMBER,
898 x_PERSON_ID IN NUMBER,
899 x_ADMISSION_APPL_NUMBER IN NUMBER,
900 x_INTENT_TYPE_ID IN NUMBER,
901 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
902 x_ATTRIBUTE1 IN VARCHAR2,
903 x_ATTRIBUTE2 IN VARCHAR2,
904 x_ATTRIBUTE3 IN VARCHAR2,
905 x_ATTRIBUTE4 IN VARCHAR2,
906 x_ATTRIBUTE5 IN VARCHAR2,
907 x_ATTRIBUTE6 IN VARCHAR2,
908 x_ATTRIBUTE7 IN VARCHAR2,
909 x_ATTRIBUTE8 IN VARCHAR2,
910 x_ATTRIBUTE9 IN VARCHAR2,
911 x_ATTRIBUTE10 IN VARCHAR2,
912 x_ATTRIBUTE11 IN VARCHAR2,
913 x_ATTRIBUTE12 IN VARCHAR2,
914 x_ATTRIBUTE13 IN VARCHAR2,
915 x_ATTRIBUTE14 IN VARCHAR2,
916 x_ATTRIBUTE15 IN VARCHAR2,
917 x_ATTRIBUTE16 IN VARCHAR2,
918 x_ATTRIBUTE17 IN VARCHAR2,
919 x_ATTRIBUTE18 IN VARCHAR2,
920 x_ATTRIBUTE19 IN VARCHAR2,
921 x_ATTRIBUTE20 IN VARCHAR2,
922 X_MODE in VARCHAR2
923 ) AS
924 /*************************************************************
925 Created By :
926 Date Created By :
927 Purpose :
928 Know limitations, enhancements or remarks
929 Change History
930 Who When What
931
932 (reverse chronological order - newest change first)
933 ***************************************************************/
934
935 X_LAST_UPDATE_DATE DATE ;
936 X_LAST_UPDATED_BY NUMBER ;
937 X_LAST_UPDATE_LOGIN NUMBER ;
938 X_REQUEST_ID NUMBER;
939 X_PROGRAM_ID NUMBER;
940 X_PROGRAM_APPLICATION_ID NUMBER;
941 X_PROGRAM_UPDATE_DATE DATE;
942 begin
943 X_LAST_UPDATE_DATE := SYSDATE;
944 if(X_MODE = 'I') then
945 X_LAST_UPDATED_BY := 1;
946 X_LAST_UPDATE_LOGIN := 0;
947 elsif (X_MODE IN ('R', 'S')) then
948 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
952 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
949 if X_LAST_UPDATED_BY is NULL then
950 X_LAST_UPDATED_BY := -1;
951 end if;
953 if X_LAST_UPDATE_LOGIN is NULL then
954 X_LAST_UPDATE_LOGIN := -1;
955 end if;
956 else
957 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
958 IGS_GE_MSG_STACK.ADD;
959 app_exception.raise_exception;
960 end if;
961 Before_DML(
962 p_action=>'UPDATE',
963 x_rowid=>X_ROWID,
964 x_app_intent_id=>X_APP_INTENT_ID,
965 x_person_id=>X_PERSON_ID,
966 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
967 x_intent_type_id=>X_INTENT_TYPE_ID,
968 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
969 x_attribute1=>X_ATTRIBUTE1,
970 x_attribute2=>X_ATTRIBUTE2,
971 x_attribute3=>X_ATTRIBUTE3,
972 x_attribute4=>X_ATTRIBUTE4,
973 x_attribute5=>X_ATTRIBUTE5,
974 x_attribute6=>X_ATTRIBUTE6,
975 x_attribute7=>X_ATTRIBUTE7,
976 x_attribute8=>X_ATTRIBUTE8,
977 x_attribute9=>X_ATTRIBUTE9,
978 x_attribute10=>X_ATTRIBUTE10,
979 x_attribute11=>X_ATTRIBUTE11,
980 x_attribute12=>X_ATTRIBUTE12,
981 x_attribute13=>X_ATTRIBUTE13,
982 x_attribute14=>X_ATTRIBUTE14,
983 x_attribute15=>X_ATTRIBUTE15,
984 x_attribute16=>X_ATTRIBUTE16,
985 x_attribute17=>X_ATTRIBUTE17,
986 x_attribute18=>X_ATTRIBUTE18,
987 x_attribute19=>X_ATTRIBUTE19,
988 x_attribute20=>X_ATTRIBUTE20,
989 x_creation_date=>X_LAST_UPDATE_DATE,
990 x_created_by=>X_LAST_UPDATED_BY,
991 x_last_update_date=>X_LAST_UPDATE_DATE,
992 x_last_updated_by=>X_LAST_UPDATED_BY,
993 x_last_update_login=>X_LAST_UPDATE_LOGIN);
994
995 if (X_MODE IN ('R', 'S')) then
996 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
997 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
998 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
999 if (X_REQUEST_ID = -1) then
1000 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1001 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1002 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1003 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1004 else
1005 X_PROGRAM_UPDATE_DATE := SYSDATE;
1006 end if;
1007 end if;
1008
1009 IF (x_mode = 'S') THEN
1010 igs_sc_gen_001.set_ctx('R');
1011 END IF;
1012 UPDATE IGS_AD_APP_INTENT set
1013 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1014 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
1015 INTENT_TYPE_ID = NEW_REFERENCES.INTENT_TYPE_ID,
1016 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1017 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1018 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1019 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1020 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1021 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1022 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1023 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1024 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1025 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1026 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1027 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1028 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1029 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1030 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1031 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1032 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1033 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1034 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1035 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1036 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1037 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1038 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1039 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1040 REQUEST_ID = X_REQUEST_ID,
1041 PROGRAM_ID = X_PROGRAM_ID,
1042 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
1043 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1044 where ROWID = X_ROWID;
1045 IF (sql%notfound) THEN
1046 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1047 igs_ge_msg_stack.add;
1048 IF (x_mode = 'S') THEN
1049 igs_sc_gen_001.unset_ctx('R');
1050 END IF;
1051 app_exception.raise_exception;
1052 END IF;
1053 IF (x_mode = 'S') THEN
1054 igs_sc_gen_001.unset_ctx('R');
1055 END IF;
1056
1057
1058 After_DML (
1059 p_action => 'UPDATE' ,
1060 x_rowid => X_ROWID
1061 );
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 IF (x_mode = 'S') THEN
1065 igs_sc_gen_001.unset_ctx('R');
1066 END IF;
1067 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1068 -- Code to handle Security Policy error raised
1069 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1070 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1071 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1072 -- that the ownerof policy function does not have privilege to access.
1073 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1074 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1075 IGS_GE_MSG_STACK.ADD;
1076 app_exception.raise_exception;
1077 ELSE
1078 RAISE;
1079 END IF;
1080 END UPDATE_ROW;
1081 PROCEDURE ADD_ROW (
1082 X_ROWID in out NOCOPY VARCHAR2,
1083 x_APP_INTENT_ID IN OUT NOCOPY NUMBER,
1084 x_PERSON_ID IN NUMBER,
1085 x_ADMISSION_APPL_NUMBER IN NUMBER,
1086 x_INTENT_TYPE_ID IN NUMBER,
1087 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1088 x_ATTRIBUTE1 IN VARCHAR2,
1089 x_ATTRIBUTE2 IN VARCHAR2,
1090 x_ATTRIBUTE3 IN VARCHAR2,
1091 x_ATTRIBUTE4 IN VARCHAR2,
1092 x_ATTRIBUTE5 IN VARCHAR2,
1093 x_ATTRIBUTE6 IN VARCHAR2,
1094 x_ATTRIBUTE7 IN VARCHAR2,
1095 x_ATTRIBUTE8 IN VARCHAR2,
1096 x_ATTRIBUTE9 IN VARCHAR2,
1097 x_ATTRIBUTE10 IN VARCHAR2,
1098 x_ATTRIBUTE11 IN VARCHAR2,
1099 x_ATTRIBUTE12 IN VARCHAR2,
1100 x_ATTRIBUTE13 IN VARCHAR2,
1101 x_ATTRIBUTE14 IN VARCHAR2,
1102 x_ATTRIBUTE15 IN VARCHAR2,
1103 x_ATTRIBUTE16 IN VARCHAR2,
1104 x_ATTRIBUTE17 IN VARCHAR2,
1105 x_ATTRIBUTE18 IN VARCHAR2,
1106 x_ATTRIBUTE19 IN VARCHAR2,
1107 x_ATTRIBUTE20 IN VARCHAR2,
1108 X_MODE in VARCHAR2
1109 ) AS
1110 /*************************************************************
1111 Created By :
1112 Date Created By :
1113 Purpose :
1114 Know limitations, enhancements or remarks
1115 Change History
1116 Who When What
1117
1118 (reverse chronological order - newest change first)
1119 ***************************************************************/
1120
1121 cursor c1 is select ROWID from IGS_AD_APP_INTENT
1122 where APP_INTENT_ID= X_APP_INTENT_ID
1123 ;
1124 begin
1125 open c1;
1126 fetch c1 into X_ROWID;
1127 if (c1%notfound) then
1128 close c1;
1129 INSERT_ROW (
1130 X_ROWID,
1131 X_APP_INTENT_ID,
1132 X_PERSON_ID,
1133 X_ADMISSION_APPL_NUMBER,
1134 X_INTENT_TYPE_ID,
1135 X_ATTRIBUTE_CATEGORY,
1136 X_ATTRIBUTE1,
1137 X_ATTRIBUTE2,
1138 X_ATTRIBUTE3,
1139 X_ATTRIBUTE4,
1140 X_ATTRIBUTE5,
1141 X_ATTRIBUTE6,
1142 X_ATTRIBUTE7,
1143 X_ATTRIBUTE8,
1144 X_ATTRIBUTE9,
1145 X_ATTRIBUTE10,
1146 X_ATTRIBUTE11,
1147 X_ATTRIBUTE12,
1148 X_ATTRIBUTE13,
1149 X_ATTRIBUTE14,
1150 X_ATTRIBUTE15,
1151 X_ATTRIBUTE16,
1152 X_ATTRIBUTE17,
1153 X_ATTRIBUTE18,
1154 X_ATTRIBUTE19,
1155 X_ATTRIBUTE20,
1156 X_MODE );
1157 return;
1158 end if;
1159 close c1;
1160 UPDATE_ROW (
1161 X_ROWID,
1162 X_APP_INTENT_ID,
1163 X_PERSON_ID,
1164 X_ADMISSION_APPL_NUMBER,
1165 X_INTENT_TYPE_ID,
1166 X_ATTRIBUTE_CATEGORY,
1167 X_ATTRIBUTE1,
1168 X_ATTRIBUTE2,
1169 X_ATTRIBUTE3,
1170 X_ATTRIBUTE4,
1171 X_ATTRIBUTE5,
1172 X_ATTRIBUTE6,
1173 X_ATTRIBUTE7,
1174 X_ATTRIBUTE8,
1175 X_ATTRIBUTE9,
1176 X_ATTRIBUTE10,
1177 X_ATTRIBUTE11,
1178 X_ATTRIBUTE12,
1179 X_ATTRIBUTE13,
1180 X_ATTRIBUTE14,
1181 X_ATTRIBUTE15,
1182 X_ATTRIBUTE16,
1183 X_ATTRIBUTE17,
1184 X_ATTRIBUTE18,
1185 X_ATTRIBUTE19,
1186 X_ATTRIBUTE20,
1187 X_MODE );
1188 end ADD_ROW;
1189 PROCEDURE DELETE_ROW (
1190 X_ROWID in VARCHAR2,
1191 x_mode IN VARCHAR2
1192 ) AS
1193 /*************************************************************
1194 Created By :
1195 Date Created By :
1196 Purpose :
1197 Know limitations, enhancements or remarks
1198 Change History
1199 Who When What
1200
1201 (reverse chronological order - newest change first)
1202 ***************************************************************/
1203
1204 begin
1205 Before_DML (
1206 p_action => 'DELETE',
1207 x_rowid => X_ROWID
1208 );
1209 IF (x_mode = 'S') THEN
1210 igs_sc_gen_001.set_ctx('R');
1211 END IF;
1212 delete from IGS_AD_APP_INTENT
1213 where ROWID = X_ROWID;
1214 if (sql%notfound) then
1215 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1216 igs_ge_msg_stack.add;
1217 IF (x_mode = 'S') THEN
1218 igs_sc_gen_001.unset_ctx('R');
1219 END IF;
1220 app_exception.raise_exception;
1221 end if;
1222 IF (x_mode = 'S') THEN
1223 igs_sc_gen_001.unset_ctx('R');
1224 END IF;
1225
1226 After_DML (
1227 p_action => 'DELETE',
1228 x_rowid => X_ROWID
1229 );
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 IF (x_mode = 'S') THEN
1233 igs_sc_gen_001.unset_ctx('R');
1234 END IF;
1235 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1236 -- Code to handle Security Policy error raised
1237 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1238 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1239 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1240 -- that the ownerof policy function does not have privilege to access.
1241 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1242 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1243 IGS_GE_MSG_STACK.ADD;
1244 app_exception.raise_exception;
1245 ELSE
1246 RAISE;
1247 END IF;
1248 end DELETE_ROW;
1249 END igs_ad_app_intent_pkg;
[Home] [Help]