1 PACKAGE BODY igs_ad_appl_pgmapprv_pkg AS
2 /* $Header: IGSAIA5B.pls 120.5 2005/10/03 08:23:00 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_appl_pgmapprv%RowType;
5 new_references igs_ad_appl_pgmapprv%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_appl_pgmapprv_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_sequence_number IN NUMBER DEFAULT NULL,
15 x_pgm_approver_id IN NUMBER DEFAULT NULL,
16 x_assign_type IN VARCHAR2 DEFAULT NULL,
17 x_assign_date IN DATE DEFAULT NULL,
18 x_program_approval_date IN DATE DEFAULT NULL,
19 x_program_approval_status IN VARCHAR2 DEFAULT NULL,
20 x_approval_notes IN VARCHAR2 DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL
26 ) AS
27
28 /*************************************************************
29 Created By :
30 Date Created By :
31 Purpose :
32 Know limitations, enhancements or remarks
33 Change History
34 Who When What
35
36 (reverse chronological order - newest change first)
37 ***************************************************************/
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM IGS_AD_APPL_PGMAPPRV
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 Open cur_old_ref_values;
51 Fetch cur_old_ref_values INTO old_references;
52 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
53 Close cur_old_ref_values;
54 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
55 IGS_GE_MSG_STACK.ADD;
56 App_Exception.Raise_Exception;
57 Return;
58 END IF;
59 Close cur_old_ref_values;
60
61 -- Populate New Values.
62 new_references.appl_pgmapprv_id := x_appl_pgmapprv_id;
63 new_references.person_id := x_person_id;
64 new_references.admission_appl_number := x_admission_appl_number;
65 new_references.nominated_course_cd := x_nominated_course_cd;
66 new_references.sequence_number := x_sequence_number;
67 new_references.pgm_approver_id := x_pgm_approver_id;
68 new_references.assign_type := x_assign_type;
69 new_references.assign_date := TRUNC(x_assign_date);
70 new_references.program_approval_date := TRUNC(x_program_approval_date);
71 new_references.program_approval_status := x_program_approval_status;
72 new_references.approval_notes := x_approval_notes;
73 IF (p_action = 'UPDATE') THEN
74 new_references.creation_date := old_references.creation_date;
75 new_references.created_by := old_references.created_by;
76 ELSE
77 new_references.creation_date := x_creation_date;
78 new_references.created_by := x_created_by;
79 END IF;
80 new_references.last_update_date := x_last_update_date;
81 new_references.last_updated_by := x_last_updated_by;
82 new_references.last_update_login := x_last_update_login;
83
84 END Set_Column_Values;
85
86 PROCEDURE Check_Constraints (
87 Column_Name IN VARCHAR2 DEFAULT NULL,
88 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
89 /*************************************************************
90 Created By :
91 Date Created By :
92 Purpose :
93 Know limitations, enhancements or remarks
94 Change History
95 Who When What
96
97 (reverse chronological order - newest change first)
98 ***************************************************************/
99
100 BEGIN
101
102 IF column_name IS NULL THEN
103 NULL;
104 ELSIF UPPER(column_name) = 'ASSIGN_TYPE' THEN
105 new_references.assign_type := column_value;
106 NULL;
107 END IF;
108
109
110
111 -- The following code checks for check constraints on the Columns.
112 IF Upper(Column_Name) = 'ASSIGN_TYPE' OR
113 Column_Name IS NULL THEN
114 IF NOT (new_references.assign_type IN ('M','A')) THEN
115 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 END IF;
119 END IF;
120
121
122 END Check_Constraints;
123
124 PROCEDURE Check_Uniqueness AS
125 /*************************************************************
126 Created By :
127 Date Created By :
128 Purpose :
129 Know limitations, enhancements or remarks
130 Change History
131 Who When What
132
133 (reverse chronological order - newest change first)
134 ***************************************************************/
135
136 begin
137 IF Get_Uk_For_Validation (
138 new_references.pgm_approver_id
139 ,new_references.sequence_number
140 ,new_references.admission_appl_number
141 ,new_references.nominated_course_cd
142 ,new_references.person_id
143 ) THEN
144 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
145 IGS_GE_MSG_STACK.ADD;
146 app_exception.raise_exception;
147 END IF;
148 END Check_Uniqueness ;
149 PROCEDURE Check_Parent_Existance AS
150 /*************************************************************
151 Created By :
152 Date Created By :
153 Purpose :
154 Know limitations, enhancements or remarks
155 Change History
156 Who When What
157
158 (reverse chronological order - newest change first)
159 ***************************************************************/
160
161 BEGIN
162
163 IF (((old_references.person_id = new_references.person_id) AND
164 (old_references.admission_appl_number = new_references.admission_appl_number) AND
165 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
166 (old_references.sequence_number = new_references.sequence_number)) OR
167 ((new_references.person_id IS NULL) OR
168 (new_references.admission_appl_number IS NULL) OR
169 (new_references.nominated_course_cd IS NULL) OR
170 (new_references.sequence_number IS NULL))) THEN
171 NULL;
172 ELSIF NOT Igs_Ad_Ps_Appl_Inst_Pkg.Get_PK_For_Validation (
173 new_references.person_id,
174 new_references.admission_appl_number,
175 new_references.nominated_course_cd,
176 new_references.sequence_number
177 ) THEN
178 Fnd_Message.Set_Name ('FND','IGS_GE_PK_UK_NOT_FOUND');
179 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM_APPL'));
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END IF;
183 IF (((old_references.Program_Approval_Status = new_references.Program_Approval_Status)) OR
184 ((new_references.Program_Approval_Status IS NULL))) THEN
185 NULL;
186 ELSIF NOT Igs_lookups_view_pkg.get_pk_for_validation(
187 'PROGRAM_APPROVAL_STATUS',
188 new_references.Program_Approval_Status) THEN
189 Fnd_Message.Set_Name ('FND','IGS_GE_PK_UK_NOT_FOUND');
190 --Message changed by ravishar
191 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PGM_APPROVAL_STATUS'));
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195
196 IF (((old_references.pgm_approver_id = new_references.pgm_approver_id)) OR
197 ((new_references.pgm_approver_id IS NULL))) THEN
198 NULL;
199 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
200 new_references.pgm_approver_id
201 ) THEN
202 Fnd_Message.Set_Name ('FND','IGS_GE_PK_UK_NOT_FOUND');
203 --Message changed by ravishar
204 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PGM_APPROVER'));
205 IGS_GE_MSG_STACK.ADD;
206 App_Exception.Raise_Exception;
207 END IF;
208
209 END Check_Parent_Existance;
210
211 FUNCTION Get_PK_For_Validation (
212 x_appl_pgmapprv_id IN NUMBER
213 ) RETURN BOOLEAN AS
214
215 /*************************************************************
216 Created By :
217 Date Created By :
218 Purpose :
219 Know limitations, enhancements or remarks
220 Change History
221 Who When What
222
223 (reverse chronological order - newest change first)
224 ***************************************************************/
225
226 CURSOR cur_rowid IS
227 SELECT rowid
228 FROM igs_ad_appl_pgmapprv
229 WHERE appl_pgmapprv_id = x_appl_pgmapprv_id
230 FOR UPDATE NOWAIT;
231
232 lv_rowid cur_rowid%RowType;
233
234 BEGIN
235
236 Open cur_rowid;
237 Fetch cur_rowid INTO lv_rowid;
238 IF (cur_rowid%FOUND) THEN
239 Close cur_rowid;
240 Return(TRUE);
241 ELSE
242 Close cur_rowid;
243 Return(FALSE);
244 END IF;
245 END Get_PK_For_Validation;
246
247 FUNCTION Get_UK_For_Validation (
248 x_pgm_approver_id IN NUMBER,
249 x_sequence_number IN NUMBER,
250 x_admission_appl_number IN NUMBER,
251 x_nominated_course_cd IN VARCHAR2,
252 x_person_id IN NUMBER
253 ) RETURN BOOLEAN AS
254
255 /*************************************************************
256 Created By :
257 Date Created By :
258 Purpose :
259 Know limitations, enhancements or remarks
260 Change History
261 Who When What
262
263 (reverse chronological order - newest change first)
264 ***************************************************************/
265
266 CURSOR cur_rowid IS
267 SELECT rowid
268 FROM igs_ad_appl_pgmapprv
269 WHERE pgm_approver_id = x_pgm_approver_id
270 AND sequence_number = x_sequence_number
271 AND admission_appl_number = x_admission_appl_number
272 AND nominated_course_cd = x_nominated_course_cd
273 AND person_id = x_person_id and ((l_rowid is null) or (rowid <> l_rowid))
274
275 ;
276 lv_rowid cur_rowid%RowType;
277
278 BEGIN
279
280 Open cur_rowid;
281 Fetch cur_rowid INTO lv_rowid;
282 IF (cur_rowid%FOUND) THEN
283 Close cur_rowid;
284 return (true);
285 ELSE
286 close cur_rowid;
287 return(false);
288 END IF;
289 END Get_UK_For_Validation ;
290 PROCEDURE Get_FK_Igs_Ad_Ps_Appl_Inst (
291 x_person_id IN NUMBER,
292 x_admission_appl_number IN NUMBER,
293 x_nominated_course_cd IN VARCHAR2,
294 x_sequence_number IN NUMBER
295 ) AS
296
297 /*************************************************************
298 Created By :
299 Date Created By :
300 Purpose :
301 Know limitations, enhancements or remarks
302 Change History
303 Who When What
304
305 (reverse chronological order - newest change first)
306 ***************************************************************/
307
308 CURSOR cur_rowid IS
309 SELECT rowid
310 FROM igs_ad_appl_pgmapprv
311 WHERE person_id = x_person_id
312 AND admission_appl_number = x_admission_appl_number
313 AND nominated_course_cd = x_nominated_course_cd
314 AND sequence_number = x_sequence_number ;
315
316 lv_rowid cur_rowid%RowType;
317
318 BEGIN
319
320 Open cur_rowid;
321 Fetch cur_rowid INTO lv_rowid;
322 IF (cur_rowid%FOUND) THEN
323 Close cur_rowid;
324 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAPGM_ACAI_FK');
325 IGS_GE_MSG_STACK.ADD;
326 App_Exception.Raise_Exception;
327 Return;
328 END IF;
329 Close cur_rowid;
330
331 END Get_FK_Igs_Ad_Ps_Appl_Inst;
332
333 PROCEDURE Get_FK_Igs_Pe_Person (
334 x_person_id IN NUMBER
335 ) AS
336
337 /*************************************************************
338 Created By :
339 Date Created By :
340 Purpose :
341 Know limitations, enhancements or remarks
342 Change History
343 Who When What
344
345 (reverse chronological order - newest change first)
346 ***************************************************************/
347
348 CURSOR cur_rowid IS
349 SELECT rowid
350 FROM igs_ad_appl_pgmapprv
351 WHERE pgm_approver_id = x_person_id ;
352
353 lv_rowid cur_rowid%RowType;
354
355 BEGIN
356
357 Open cur_rowid;
358 Fetch cur_rowid INTO lv_rowid;
359 IF (cur_rowid%FOUND) THEN
360 Close cur_rowid;
361 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAPGM_PE_FK');
362 IGS_GE_MSG_STACK.ADD;
363 App_Exception.Raise_Exception;
364 Return;
365 END IF;
366 Close cur_rowid;
367
368 END Get_FK_Igs_Pe_Person;
369
370 PROCEDURE Before_DML (
371 p_action IN VARCHAR2,
372 x_rowid IN VARCHAR2 DEFAULT NULL,
373 x_appl_pgmapprv_id IN NUMBER DEFAULT NULL,
374 x_person_id IN NUMBER DEFAULT NULL,
375 x_admission_appl_number IN NUMBER DEFAULT NULL,
376 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
377 x_sequence_number IN NUMBER DEFAULT NULL,
378 x_pgm_approver_id IN NUMBER DEFAULT NULL,
379 x_assign_type IN VARCHAR2 DEFAULT NULL,
380 x_assign_date IN DATE DEFAULT NULL,
381 x_program_approval_date IN DATE DEFAULT NULL,
382 x_program_approval_status IN VARCHAR2 DEFAULT NULL,
383 x_approval_notes 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_appl_pgmapprv_id,
407 x_person_id,
408 x_admission_appl_number,
409 x_nominated_course_cd,
410 x_sequence_number,
414 x_program_approval_date,
411 x_pgm_approver_id,
412 x_assign_type,
413 x_assign_date,
415 x_program_approval_status,
416 x_approval_notes,
417 x_creation_date,
418 x_created_by,
419 x_last_update_date,
420 x_last_updated_by,
421 x_last_update_login
422 );
423
424 igs_ad_gen_002.check_adm_appl_inst_stat(
425 nvl(x_person_id,old_references.person_id),
426 nvl(x_admission_appl_number,old_references.admission_appl_number),
427 nvl(x_nominated_course_cd,old_references.nominated_course_cd),
428 nvl(x_sequence_number,old_references.sequence_number)
429 );
430
431 IF (p_action = 'INSERT') THEN
432 -- Call all the procedures related to Before Insert.
433 Null;
434 IF Get_Pk_For_Validation(
435 new_references.appl_pgmapprv_id) THEN
436 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
437 IGS_GE_MSG_STACK.ADD;
438 App_Exception.Raise_Exception;
439 END IF;
440 Check_Uniqueness;
441 Check_Constraints;
442 Check_Parent_Existance;
443 ELSIF (p_action = 'UPDATE') THEN
444 -- Call all the procedures related to Before Update.
445 Null;
446 Check_Uniqueness;
447 Check_Constraints;
448 Check_Parent_Existance;
449 ELSIF (p_action = 'DELETE') THEN
450 -- Call all the procedures related to Before Delete.
451 Null;
452 ELSIF (p_action = 'VALIDATE_INSERT') THEN
453 -- Call all the procedures related to Before Insert.
454 IF Get_PK_For_Validation (
455 new_references.appl_pgmapprv_id) THEN
456 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
457 IGS_GE_MSG_STACK.ADD;
458 App_Exception.Raise_Exception;
459 END IF;
460 Check_Uniqueness;
461 Check_Constraints;
462 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
463 Check_Uniqueness;
464 Check_Constraints;
465 ELSIF (p_action = 'VALIDATE_DELETE') THEN
466 Null;
467 END IF;
468
469 END Before_DML;
470
471 PROCEDURE After_DML (
472 p_action IN VARCHAR2,
473 x_rowid IN VARCHAR2
474 ) IS
475 /*************************************************************
476 Created By :
477 Date Created By :
478 Purpose :
479 Know limitations, enhancements or remarks
480 Change History
481 Who When What
482
483 (reverse chronological order - newest change first)
484 ***************************************************************/
485
486 BEGIN
487
488 l_rowid := x_rowid;
489
490 IF (p_action = 'INSERT') THEN
491 -- Call all the procedures related to After Insert.
492 Null;
493 ELSIF (p_action = 'UPDATE') THEN
494 -- Call all the procedures related to After Update.
495 Null;
496 ELSIF (p_action = 'DELETE') THEN
497 -- Call all the procedures related to After Delete.
498 Null;
499 END IF;
500
501 l_rowid:=NULL;
502 END After_DML;
503
504 procedure INSERT_ROW (
505 X_ROWID in out NOCOPY VARCHAR2,
506 x_APPL_PGMAPPRV_ID IN OUT NOCOPY NUMBER,
507 x_PERSON_ID IN NUMBER,
508 x_ADMISSION_APPL_NUMBER IN NUMBER,
509 x_NOMINATED_COURSE_CD IN VARCHAR2,
510 x_SEQUENCE_NUMBER IN NUMBER,
511 x_PGM_APPROVER_ID IN NUMBER,
512 x_ASSIGN_TYPE IN VARCHAR2,
513 x_ASSIGN_DATE IN DATE,
514 x_PROGRAM_APPROVAL_DATE IN DATE,
515 x_PROGRAM_APPROVAL_STATUS IN VARCHAR2,
516 x_APPROVAL_NOTES IN VARCHAR2,
517 X_MODE in VARCHAR2
518 ) AS
519 /*************************************************************
520 Created By :
521 Date Created By :
522 Purpose :
523 Know limitations, enhancements or remarks
524 Change History
525 Who When What
526 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
527 body of this package for bug 4163319
528 GSCC standard says that default value should be
529 present only in specification
530 (reverse chronological order - newest change first)
531 ***************************************************************/
532
533 cursor C is select ROWID from IGS_AD_APPL_PGMAPPRV
534 where APPL_PGMAPPRV_ID= X_APPL_PGMAPPRV_ID
535 ;
536 X_LAST_UPDATE_DATE DATE ;
537 X_LAST_UPDATED_BY NUMBER ;
538 X_LAST_UPDATE_LOGIN NUMBER ;
539 X_REQUEST_ID NUMBER;
540 X_PROGRAM_ID NUMBER;
541 X_PROGRAM_APPLICATION_ID NUMBER;
542 X_PROGRAM_UPDATE_DATE DATE;
543
544 l_mode VARCHAR2(1);
545 begin
546 l_mode := NVL(X_MODE , 'R');
547 X_LAST_UPDATE_DATE := SYSDATE;
548 if(l_mode = 'I') then
549 X_LAST_UPDATED_BY := 1;
550 X_LAST_UPDATE_LOGIN := 0;
551 elsif (l_mode IN ('R','S')) then
552 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
556 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
553 if X_LAST_UPDATED_BY is NULL then
554 X_LAST_UPDATED_BY := -1;
555 end if;
557 if X_LAST_UPDATE_LOGIN is NULL then
558 X_LAST_UPDATE_LOGIN := -1;
559 end if;
560 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
561 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
562 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
563 if (X_REQUEST_ID = -1) then
564 X_REQUEST_ID := NULL;
565 X_PROGRAM_ID := NULL;
566 X_PROGRAM_APPLICATION_ID := NULL;
567 X_PROGRAM_UPDATE_DATE := NULL;
568 else
569 X_PROGRAM_UPDATE_DATE := SYSDATE;
570 end if;
571 else
572 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
573 IGS_GE_MSG_STACK.ADD;
574 app_exception.raise_exception;
575 end if;
576
577 X_APPL_PGMAPPRV_ID := -1;
578 Before_DML(
579 p_action=>'INSERT',
580 x_rowid=>X_ROWID,
581 x_appl_pgmapprv_id=>X_APPL_PGMAPPRV_ID,
582 x_person_id=>X_PERSON_ID,
583 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
584 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
585 x_sequence_number=>X_SEQUENCE_NUMBER,
586 x_pgm_approver_id=>X_PGM_APPROVER_ID,
587 x_assign_type=>X_ASSIGN_TYPE,
588 x_assign_date=>X_ASSIGN_DATE,
589 x_program_approval_date=>X_PROGRAM_APPROVAL_DATE,
590 x_program_approval_status=>X_PROGRAM_APPROVAL_STATUS,
591 x_approval_notes=>X_APPROVAL_NOTES,
592 x_creation_date=>X_LAST_UPDATE_DATE,
593 x_created_by=>X_LAST_UPDATED_BY,
594 x_last_update_date=>X_LAST_UPDATE_DATE,
595 x_last_updated_by=>X_LAST_UPDATED_BY,
596 x_last_update_login=>X_LAST_UPDATE_LOGIN);
597 IF (x_mode = 'S') THEN
598 igs_sc_gen_001.set_ctx('R');
599 END IF;
600
601 insert into IGS_AD_APPL_PGMAPPRV (
602 APPL_PGMAPPRV_ID
603 ,PERSON_ID
604 ,ADMISSION_APPL_NUMBER
605 ,NOMINATED_COURSE_CD
606 ,SEQUENCE_NUMBER
607 ,PGM_APPROVER_ID
608 ,ASSIGN_TYPE
609 ,ASSIGN_DATE
610 ,PROGRAM_APPROVAL_DATE
611 ,PROGRAM_APPROVAL_STATUS
612 ,APPROVAL_NOTES
613 ,CREATION_DATE
614 ,CREATED_BY
615 ,LAST_UPDATE_DATE
616 ,LAST_UPDATED_BY
617 ,LAST_UPDATE_LOGIN
618 ,REQUEST_ID
619 ,PROGRAM_ID
620 ,PROGRAM_APPLICATION_ID
621 ,PROGRAM_UPDATE_DATE
622 ) values (
623 IGS_AD_APPL_PGMAPPRV_S.NEXTVAL
624 ,NEW_REFERENCES.PERSON_ID
625 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
626 ,NEW_REFERENCES.NOMINATED_COURSE_CD
627 ,NEW_REFERENCES.SEQUENCE_NUMBER
628 ,NEW_REFERENCES.PGM_APPROVER_ID
629 ,NEW_REFERENCES.ASSIGN_TYPE
630 ,NEW_REFERENCES.ASSIGN_DATE
631 ,NEW_REFERENCES.PROGRAM_APPROVAL_DATE
632 ,NEW_REFERENCES.PROGRAM_APPROVAL_STATUS
633 ,NEW_REFERENCES.APPROVAL_NOTES
634 ,X_LAST_UPDATE_DATE
635 ,X_LAST_UPDATED_BY
636 ,X_LAST_UPDATE_DATE
637 ,X_LAST_UPDATED_BY
638 ,X_LAST_UPDATE_LOGIN
639 ,X_REQUEST_ID
640 ,X_PROGRAM_ID
641 ,X_PROGRAM_APPLICATION_ID
642 ,X_PROGRAM_UPDATE_DATE
643 )RETURNING APPL_PGMAPPRV_ID INTO X_APPL_PGMAPPRV_ID;
644 IF (x_mode = 'S') THEN
645 igs_sc_gen_001.unset_ctx('R');
646 END IF;
647
648 open c;
649 fetch c into X_ROWID;
650 if (c%notfound) then
651 close c;
652 raise no_data_found;
653 end if;
654 close c;
655
656 After_DML (
657 p_action => 'INSERT' ,
658 x_rowid => X_ROWID );
659
660 NULL;
661 EXCEPTION
662 WHEN OTHERS THEN
663 IF (x_mode = 'S') THEN
664 igs_sc_gen_001.unset_ctx('R');
665 END IF;
666 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
667 -- Code to handle Security Policy error raised
668 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
669 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
670 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
671 -- that the ownerof policy function does not have privilege to access.
672 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
673 fnd_message.set_token ('ERR_CD', SQLCODE);
674 igs_ge_msg_stack.add;
675 app_exception.raise_exception;
676 ELSE
677 RAISE;
681 procedure LOCK_ROW (
678 END IF;
679
680 end INSERT_ROW;
682 X_ROWID in VARCHAR2,
683 x_APPL_PGMAPPRV_ID IN NUMBER,
684 x_PERSON_ID IN NUMBER,
685 x_ADMISSION_APPL_NUMBER IN NUMBER,
686 x_NOMINATED_COURSE_CD IN VARCHAR2,
687 x_SEQUENCE_NUMBER IN NUMBER,
688 x_PGM_APPROVER_ID IN NUMBER,
689 x_ASSIGN_TYPE IN VARCHAR2,
690 x_ASSIGN_DATE IN DATE,
691 x_PROGRAM_APPROVAL_DATE IN DATE,
692 x_PROGRAM_APPROVAL_STATUS IN VARCHAR2,
693 x_APPROVAL_NOTES IN VARCHAR2 ) AS
694 /*************************************************************
695 Created By :
696 Date Created By :
697 Purpose :
698 Know limitations, enhancements or remarks
699 Change History
700 Who When What
701
702 rgangara 23-Oct-2001 Added code to allow Program_approval_date, program_aproval_status being NULL.
703 Since these are Nullabel columns in the Table.
704 Also in the comparison of Assign Date, the TRUNC has been added so that the time
705 part is not compared because the table column has time stored and the input parameter
706 just sends in the date without Time part.
707 Bug No: 2048513
708 (reverse chronological order - newest change first)
709 ***************************************************************/
710
711 cursor c1 is select
712 PERSON_ID
713 , ADMISSION_APPL_NUMBER
714 , NOMINATED_COURSE_CD
715 , SEQUENCE_NUMBER
716 , PGM_APPROVER_ID
717 , ASSIGN_TYPE
718 , ASSIGN_DATE
719 , PROGRAM_APPROVAL_DATE
720 , PROGRAM_APPROVAL_STATUS
721 , APPROVAL_NOTES
722 from IGS_AD_APPL_PGMAPPRV
723 where ROWID = X_ROWID
724 for update nowait;
725 tlinfo c1%rowtype;
726 begin
727 open c1;
728 fetch c1 into tlinfo;
729 if (c1%notfound) then
730 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
731 IGS_GE_MSG_STACK.ADD;
732 close c1;
733 app_exception.raise_exception;
734 return;
735 end if;
736 close c1;
737 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
738 AND (tlinfo.ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER)
739 AND (tlinfo.NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD)
740 AND (tlinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
741 AND (tlinfo.PGM_APPROVER_ID = X_PGM_APPROVER_ID)
742 AND (tlinfo.ASSIGN_TYPE = X_ASSIGN_TYPE)
743 AND (TRUNC(tlinfo.ASSIGN_DATE) = TRUNC(X_ASSIGN_DATE))
744 AND (TRUNC(tlinfo.PROGRAM_APPROVAL_DATE) = TRUNC(X_PROGRAM_APPROVAL_DATE))
745 OR ((tlinfo.program_approval_date IS NULL)
746 AND (x_program_approval_date IS NULL))
747 AND (tlinfo.PROGRAM_APPROVAL_STATUS = X_PROGRAM_APPROVAL_STATUS)
748 OR ((tlinfo.program_approval_status IS NULL)
749 AND (x_program_approval_status IS NULL))
750 AND ((tlinfo.APPROVAL_NOTES = X_APPROVAL_NOTES)
751 OR ((tlinfo.APPROVAL_NOTES is null)
752 AND (X_APPROVAL_NOTES is null)))
753 ) then
754 null;
755 else
756 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
757 IGS_GE_MSG_STACK.ADD;
758 app_exception.raise_exception;
759 end if;
760 return;
761 end LOCK_ROW;
762 Procedure UPDATE_ROW (
763 X_ROWID in VARCHAR2,
764 x_APPL_PGMAPPRV_ID IN NUMBER,
765 x_PERSON_ID IN NUMBER,
766 x_ADMISSION_APPL_NUMBER IN NUMBER,
767 x_NOMINATED_COURSE_CD IN VARCHAR2,
768 x_SEQUENCE_NUMBER IN NUMBER,
769 x_PGM_APPROVER_ID IN NUMBER,
770 x_ASSIGN_TYPE IN VARCHAR2,
771 x_ASSIGN_DATE IN DATE,
772 x_PROGRAM_APPROVAL_DATE IN DATE,
773 x_PROGRAM_APPROVAL_STATUS IN VARCHAR2,
774 x_APPROVAL_NOTES IN VARCHAR2,
775 X_MODE in VARCHAR2
776 ) AS
777 /*************************************************************
778 Created By :
779 Date Created By :
780 Purpose :
781 Know limitations, enhancements or remarks
782 Change History
783 Who When What
784 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
785 body of this package for bug 4163319
786 GSCC standard says that default value should be
787 present only in specification
788 (reverse chronological order - newest change first)
789 ***************************************************************/
790
791 X_LAST_UPDATE_DATE DATE ;
792 X_LAST_UPDATED_BY NUMBER ;
793 X_LAST_UPDATE_LOGIN NUMBER ;
794 X_REQUEST_ID NUMBER;
795 X_PROGRAM_ID NUMBER;
796 X_PROGRAM_APPLICATION_ID NUMBER;
797 X_PROGRAM_UPDATE_DATE DATE;
798
799 l_mode VARCHAR2(1);
800 begin
801 l_mode := NVL(X_MODE,'R');
802 X_LAST_UPDATE_DATE := SYSDATE;
803 if(l_mode = 'I') then
804 X_LAST_UPDATED_BY := 1;
805 X_LAST_UPDATE_LOGIN := 0;
806 elsif (l_mode IN ('R','S')) then
807 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
808 if X_LAST_UPDATED_BY is NULL then
809 X_LAST_UPDATED_BY := -1;
810 end if;
814 end if;
811 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
812 if X_LAST_UPDATE_LOGIN is NULL then
813 X_LAST_UPDATE_LOGIN := -1;
815 else
816 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
817 IGS_GE_MSG_STACK.ADD;
818 app_exception.raise_exception;
819 end if;
820 Before_DML(
821 p_action=>'UPDATE',
822 x_rowid=>X_ROWID,
823 x_appl_pgmapprv_id=>X_APPL_PGMAPPRV_ID,
824 x_person_id=>X_PERSON_ID,
825 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
826 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
827 x_sequence_number=>X_SEQUENCE_NUMBER,
828 x_pgm_approver_id=>X_PGM_APPROVER_ID,
829 x_assign_type=>X_ASSIGN_TYPE,
830 x_assign_date=>X_ASSIGN_DATE,
831 x_program_approval_date=>X_PROGRAM_APPROVAL_DATE,
832 x_program_approval_status=>X_PROGRAM_APPROVAL_STATUS,
833 x_approval_notes=>X_APPROVAL_NOTES,
834 x_creation_date=>X_LAST_UPDATE_DATE,
835 x_created_by=>X_LAST_UPDATED_BY,
836 x_last_update_date=>X_LAST_UPDATE_DATE,
837 x_last_updated_by=>X_LAST_UPDATED_BY,
838 x_last_update_login=>X_LAST_UPDATE_LOGIN);
839
840 if (l_mode IN ('R','S')) then
841 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
842 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
843 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
844 if (X_REQUEST_ID = -1) then
845 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
846 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
847 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
848 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
849 else
850 X_PROGRAM_UPDATE_DATE := SYSDATE;
851 end if;
852 end if;
853
854 IF (x_mode = 'S') THEN
855 igs_sc_gen_001.set_ctx('R');
856 END IF;
857 update IGS_AD_APPL_PGMAPPRV set
858 PERSON_ID = NEW_REFERENCES.PERSON_ID,
859 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
860 NOMINATED_COURSE_CD = NEW_REFERENCES.NOMINATED_COURSE_CD,
861 SEQUENCE_NUMBER = NEW_REFERENCES.SEQUENCE_NUMBER,
862 PGM_APPROVER_ID = NEW_REFERENCES.PGM_APPROVER_ID,
863 ASSIGN_TYPE = NEW_REFERENCES.ASSIGN_TYPE,
864 ASSIGN_DATE = NEW_REFERENCES.ASSIGN_DATE,
865 PROGRAM_APPROVAL_DATE = NEW_REFERENCES.PROGRAM_APPROVAL_DATE,
866 PROGRAM_APPROVAL_STATUS = NEW_REFERENCES.PROGRAM_APPROVAL_STATUS,
867 APPROVAL_NOTES = NEW_REFERENCES.APPROVAL_NOTES,
868 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
869 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
870 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
871 , REQUEST_ID = X_REQUEST_ID,
872 PROGRAM_ID = X_PROGRAM_ID,
873 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
874 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
875 where ROWID = X_ROWID;
876 if (sql%notfound) then
877 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
878 igs_ge_msg_stack.add;
879 igs_sc_gen_001.unset_ctx('R');
880 app_exception.raise_exception;
881 end if;
882 IF (x_mode = 'S') THEN
883 igs_sc_gen_001.unset_ctx('R');
884 END IF;
885
886
887 After_DML (
888 p_action => 'UPDATE' ,
889 x_rowid => X_ROWID
890 );
891 EXCEPTION
892 WHEN OTHERS THEN
893 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
894 -- Code to handle Security Policy error raised
895 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
896 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
897 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
898 -- that the ownerof policy function does not have privilege to access.
899 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
900 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
901 IGS_GE_MSG_STACK.ADD;
902 app_exception.raise_exception;
903 ELSE
904 RAISE;
905 END IF;
906 end UPDATE_ROW;
907 procedure ADD_ROW (
908 X_ROWID in out NOCOPY VARCHAR2,
909 x_APPL_PGMAPPRV_ID IN OUT NOCOPY NUMBER,
910 x_PERSON_ID IN NUMBER,
911 x_ADMISSION_APPL_NUMBER IN NUMBER,
912 x_NOMINATED_COURSE_CD IN VARCHAR2,
913 x_SEQUENCE_NUMBER IN NUMBER,
914 x_PGM_APPROVER_ID IN NUMBER,
915 x_ASSIGN_TYPE IN VARCHAR2,
916 x_ASSIGN_DATE IN DATE,
917 x_PROGRAM_APPROVAL_DATE IN DATE,
918 x_PROGRAM_APPROVAL_STATUS IN VARCHAR2,
919 x_APPROVAL_NOTES IN VARCHAR2,
920 X_MODE in VARCHAR2
921 ) AS
922 /*************************************************************
923 Created By :
924 Date Created By :
925 Purpose :
926 Know limitations, enhancements or remarks
927 Change History
928 Who When What
929 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
930 body of this package for bug 4163319
931 GSCC standard says that default value should be
932 present only in specification
933 (reverse chronological order - newest change first)
934 ***************************************************************/
935
936 cursor c1 is select ROWID from IGS_AD_APPL_PGMAPPRV
937 where APPL_PGMAPPRV_ID= X_APPL_PGMAPPRV_ID
938 ;
939
940 l_mode VARCHAR2(1);
941 begin
942 l_mode := NVL(X_MODE,'R');
943 open c1;
944 fetch c1 into X_ROWID;
945 if (c1%notfound) then
946 close c1;
947 INSERT_ROW (
948 X_ROWID,
949 X_APPL_PGMAPPRV_ID,
950 X_PERSON_ID,
951 X_ADMISSION_APPL_NUMBER,
952 X_NOMINATED_COURSE_CD,
953 X_SEQUENCE_NUMBER,
954 X_PGM_APPROVER_ID,
955 X_ASSIGN_TYPE,
956 X_ASSIGN_DATE,
957 X_PROGRAM_APPROVAL_DATE,
958 X_PROGRAM_APPROVAL_STATUS,
959 X_APPROVAL_NOTES,
960 l_mode );
961 return;
962 end if;
963 close c1;
964 UPDATE_ROW (
965 X_ROWID,
966 X_APPL_PGMAPPRV_ID,
967 X_PERSON_ID,
968 X_ADMISSION_APPL_NUMBER,
969 X_NOMINATED_COURSE_CD,
970 X_SEQUENCE_NUMBER,
971 X_PGM_APPROVER_ID,
972 X_ASSIGN_TYPE,
973 X_ASSIGN_DATE,
974 X_PROGRAM_APPROVAL_DATE,
975 X_PROGRAM_APPROVAL_STATUS,
976 X_APPROVAL_NOTES,
977 l_mode );
978 end ADD_ROW;
979 procedure DELETE_ROW (
980 X_ROWID in VARCHAR2,
981 x_mode IN VARCHAR2
982 ) AS
983 /*************************************************************
984 Created By :
985 Date Created By :
986 Purpose :
987 Know limitations, enhancements or remarks
988 Change History
989 Who When What
990
991 (reverse chronological order - newest change first)
992 ***************************************************************/
993
994 begin
995 Before_DML (
996 p_action => 'DELETE',
997 x_rowid => X_ROWID
998 );
999 IF (x_mode = 'S') THEN
1000 igs_sc_gen_001.set_ctx('R');
1001 END IF;
1002 delete from IGS_AD_APPL_PGMAPPRV
1003 where ROWID = X_ROWID;
1004 if (sql%notfound) then
1005 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1006 igs_ge_msg_stack.add;
1007 igs_sc_gen_001.unset_ctx('R');
1008 app_exception.raise_exception;
1009 end if;
1010 IF (x_mode = 'S') THEN
1011 igs_sc_gen_001.unset_ctx('R');
1012 END IF;
1013
1014 After_DML (
1015 p_action => 'DELETE',
1016 x_rowid => X_ROWID
1017 );
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1021 -- Code to handle Security Policy error raised
1022 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1023 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1024 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1025 -- that the ownerof policy function does not have privilege to access.
1026 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1027 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1028 IGS_GE_MSG_STACK.ADD;
1029 app_exception.raise_exception;
1030 ELSE
1031 RAISE;
1032 END IF;
1033 end DELETE_ROW;
1034 END igs_ad_appl_pgmapprv_pkg;
[Home] [Help]