1 PACKAGE BODY igs_ps_faclty_disp_pkg AS
2 /* $Header: IGSPI0PB.pls 120.1 2005/06/28 06:24:44 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_faclty_disp%RowType;
5 new_references igs_ps_faclty_disp%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_displn_unit_id IN NUMBER DEFAULT NULL,
11 x_displn_unit_type IN VARCHAR2 DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_displn_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20
21 /*************************************************************
22 Created By :ssuri
23 Date Created By :11-MAY-2000
24 Purpose :NEW TABLE
25 Know limitations, enhancements or remarks
26 Change History
27 Who When What
28
29 (reverse chronological order - newest change first)
30 ***************************************************************/
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_PS_FACLTY_DISP
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 Open cur_old_ref_values;
44 Fetch cur_old_ref_values INTO old_references;
45 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
46 Close cur_old_ref_values;
47 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
48 IGS_GE_MSG_STACK.ADD;
49 App_Exception.Raise_Exception;
50 Return;
51 END IF;
52 Close cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.displn_unit_id := x_displn_unit_id;
56 new_references.displn_unit_type := x_displn_unit_type;
57 new_references.person_id := x_person_id;
58 new_references.displn_unit_cd := x_displn_unit_cd;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 PROCEDURE Check_Constraints (
73 Column_Name IN VARCHAR2 DEFAULT NULL,
74 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
75 /*************************************************************
76 Created By :ssuri
77 Date Created By :11-MAY-2000
78 Purpose :NEW TABLE
79 Know limitations, enhancements or remarks
80 Change History
81 Who When What
82
83 (reverse chronological order - newest change first)
84 ***************************************************************/
85
86 BEGIN
87
88 IF column_name IS NULL THEN
89 NULL;
90 ELSIF UPPER(column_name) = 'DISPLN_UNIT_TYPE' THEN
91 new_references.displn_unit_type := column_value;
92 NULL;
93 END IF;
94
95
96
97 -- The following code checks for check constraints on the Columns.
98 IF Upper(Column_Name) = 'DISPLN_UNIT_TYPE' OR
99 Column_Name IS NULL THEN
100 IF NOT (new_references.displn_unit_type IN ('DISCIPLINE', 'UNIT')) THEN
101 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106
107
108 END Check_Constraints;
109
110 PROCEDURE Check_Uniqueness AS
111 /*************************************************************
112 Created By :ssuri
113 Date Created By :11-MAY-2000
114 Purpose :NEW TABLE
115 Know limitations, enhancements or remarks
116 Change History
117 Who When What
118
119 (reverse chronological order - newest change first)
120 ***************************************************************/
121
122 begin
123 IF Get_Uk_For_Validation (
124 new_references.displn_unit_cd
125 ,new_references.displn_unit_type
126 ,new_references.person_id
127 ) THEN
128 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
129 IGS_GE_MSG_STACK.ADD;
130 app_exception.raise_exception;
131 END IF;
132 END Check_Uniqueness ;
133 PROCEDURE Check_Parent_Existance AS
134 /*************************************************************
135 Created By :ssuri
136 Date Created By :11-MAY-2000
137 Purpose :NEW TABLE
138 Know limitations, enhancements or remarks
139 Change History
140 Who When What
141
142 (reverse chronological order - newest change first)
143 ***************************************************************/
144
145 BEGIN
146
147 IF (((old_references.person_id = new_references.person_id)) OR
148 ((new_references.person_id IS NULL))) THEN
149 NULL;
150 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
151 new_references.person_id
152 ) THEN
153 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF;
157
158 --- Special Condition to check in 2 tables (The ARC Relationship )
159 IF new_references.displn_unit_type = 'UNIT' THEN
160
161 IF (((old_references.displn_unit_cd = new_references.displn_unit_cd)) OR
162 ((new_references.displn_unit_cd IS NULL))) THEN
163 NULL;
164 ELSIF NOT Igs_Ps_Unit_Ver_Pkg.Get_PK_For_Validation (
165 new_references.displn_unit_cd,
166 1
167 ) THEN
168 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172
173 ELSE --- (Arc Relation)
174
175 IF (((old_references.displn_unit_cd = new_references.displn_unit_cd)) OR
176 ((new_references.displn_unit_cd IS NULL))) THEN
177 NULL;
178 ELSIF NOT Igs_Ps_Fld_OF_Study_Pkg.Get_PK_For_Validation (
179 new_references.displn_unit_cd
180 ) THEN
181 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
182 IGS_GE_MSG_STACK.ADD;
183 App_Exception.Raise_Exception;
184 END IF;
185
186 END IF; ---- (ARC Relation)
187 END Check_Parent_Existance;
188
189 FUNCTION Get_PK_For_Validation (
190 x_displn_unit_id IN NUMBER
191 ) RETURN BOOLEAN AS
192
193 /*************************************************************
194 Created By :ssuri
195 Date Created By :11-MAY-2000
196 Purpose :NEW TABLE
197 Know limitations, enhancements or remarks
198 Change History
199 Who When What
200
201 (reverse chronological order - newest change first)
202 ***************************************************************/
203
204 CURSOR cur_rowid IS
205 SELECT rowid
206 FROM igs_ps_faclty_disp
207 WHERE displn_unit_id = x_displn_unit_id
208 FOR UPDATE NOWAIT;
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213
214 Open cur_rowid;
215 Fetch cur_rowid INTO lv_rowid;
216 IF (cur_rowid%FOUND) THEN
217 Close cur_rowid;
218 Return(TRUE);
219 ELSE
220 Close cur_rowid;
221 Return(FALSE);
222 END IF;
223 END Get_PK_For_Validation;
224
225 FUNCTION Get_UK_For_Validation (
226 x_displn_unit_cd IN VARCHAR2,
227 x_displn_unit_type IN VARCHAR2,
228 x_person_id IN NUMBER
229 ) RETURN BOOLEAN AS
230
231 /*************************************************************
232 Created By :ssuri
233 Date Created By :11-MAY-2000
234 Purpose :NEW TABLE
235 Know limitations, enhancements or remarks
236 Change History
237 Who When What
238
239 (reverse chronological order - newest change first)
240 ***************************************************************/
241
242 CURSOR cur_rowid IS
243 SELECT rowid
244 FROM igs_ps_faclty_disp
245 WHERE displn_unit_cd = x_displn_unit_cd
246 AND displn_unit_type = x_displn_unit_type
247 AND person_id = x_person_id and ((l_rowid is null) or (rowid <> l_rowid))
248
249 ;
250 lv_rowid cur_rowid%RowType;
251
252 BEGIN
253
254 Open cur_rowid;
255 Fetch cur_rowid INTO lv_rowid;
256 IF (cur_rowid%FOUND) THEN
257 Close cur_rowid;
258 return (true);
259 ELSE
260 close cur_rowid;
261 return(false);
262 END IF;
263 END Get_UK_For_Validation ;
264 PROCEDURE Get_FK_Igs_Pe_Person (
265 x_person_id IN NUMBER
266 ) AS
267
268 /*************************************************************
269 Created By :ssuri
270 Date Created By :11-MAY-2000
271 Purpose :NEW TABLE
272 Know limitations, enhancements or remarks
273 Change History
274 Who When What
275
276 (reverse chronological order - newest change first)
277 ***************************************************************/
278
279 CURSOR cur_rowid IS
280 SELECT rowid
281 FROM igs_ps_faclty_disp
282 WHERE person_id = x_person_id ;
283
284 lv_rowid cur_rowid%RowType;
285
286 BEGIN
287
288 Open cur_rowid;
289 Fetch cur_rowid INTO lv_rowid;
290 IF (cur_rowid%FOUND) THEN
291 Close cur_rowid;
292 Fnd_Message.Set_Name ('IGS', 'IGS_PS_FDS_PE_FK');
293 IGS_GE_MSG_STACK.ADD;
294 App_Exception.Raise_Exception;
295 Return;
296 END IF;
297 Close cur_rowid;
298
299 END Get_FK_Igs_Pe_Person;
300
301
302 PROCEDURE Get_FK_Igs_Ps_Unit_Ver (
303 x_displn_unit_cd IN VARCHAR2
304 ) AS
305
306 /*************************************************************
307 Created By :ssuri
308 Date Created By :11-MAY-2000
309 Purpose :NEW TABLE
310 Know limitations, enhancements or remarks
311 Change History
312 Who When What
313
314 (reverse chronological order - newest change first)
315 ***************************************************************/
316
317 CURSOR cur_rowid IS
318 SELECT rowid
319 FROM igs_ps_faclty_disp
320 WHERE displn_unit_cd = x_displn_unit_cd
321 AND displn_unit_type='UNIT' ;
322
323 lv_rowid cur_rowid%RowType;
324
325 BEGIN
326
327 Open cur_rowid;
328 Fetch cur_rowid INTO lv_rowid;
329 IF (cur_rowid%FOUND) THEN
330 Close cur_rowid;
331 Fnd_Message.Set_Name ('IGS', 'IGS_PS_FDS_UV_FK');
332 IGS_GE_MSG_STACK.ADD;
333 App_Exception.Raise_Exception;
334 Return;
335 END IF;
336 Close cur_rowid;
337
338 END Get_FK_Igs_Ps_Unit_Ver;
339
340 PROCEDURE Before_DML (
341 p_action IN VARCHAR2,
342 x_rowid IN VARCHAR2 DEFAULT NULL,
343 x_displn_unit_id IN NUMBER DEFAULT NULL,
344 x_displn_unit_type IN VARCHAR2 DEFAULT NULL,
345 x_person_id IN NUMBER DEFAULT NULL,
346 x_displn_unit_cd IN VARCHAR2 DEFAULT NULL,
347 x_creation_date IN DATE DEFAULT NULL,
348 x_created_by IN NUMBER DEFAULT NULL,
349 x_last_update_date IN DATE DEFAULT NULL,
350 x_last_updated_by IN NUMBER DEFAULT NULL,
351 x_last_update_login IN NUMBER DEFAULT NULL
352 ) AS
353 /*************************************************************
354 Created By :ssuri
355 Date Created By :11-MAY-2000
356 Purpose :NEW TABLE
357 Know limitations, enhancements or remarks
358 Change History
359 Who When What
360
361 (reverse chronological order - newest change first)
362 ***************************************************************/
363
364 BEGIN
365
366 Set_Column_Values (
367 p_action,
368 x_rowid,
369 x_displn_unit_id,
370 x_displn_unit_type,
371 x_person_id,
372 x_displn_unit_cd,
373 x_creation_date,
374 x_created_by,
375 x_last_update_date,
376 x_last_updated_by,
377 x_last_update_login
378 );
379
380 IF (p_action = 'INSERT') THEN
381 -- Call all the procedures related to Before Insert.
382 Null;
383 IF Get_Pk_For_Validation(
384 new_references.displn_unit_id) THEN
385 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
386 IGS_GE_MSG_STACK.ADD;
387 App_Exception.Raise_Exception;
388 END IF;
389 Check_Uniqueness;
390 Check_Constraints;
391 Check_Parent_Existance;
392 ELSIF (p_action = 'UPDATE') THEN
393 -- Call all the procedures related to Before Update.
394 Null;
395 Check_Uniqueness;
396 Check_Constraints;
397 Check_Parent_Existance;
398 ELSIF (p_action = 'DELETE') THEN
399 -- Call all the procedures related to Before Delete.
400 Null;
401 ELSIF (p_action = 'VALIDATE_INSERT') THEN
402 -- Call all the procedures related to Before Insert.
403 IF Get_PK_For_Validation (
404 new_references.displn_unit_id) THEN
405 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
406 IGS_GE_MSG_STACK.ADD;
407 App_Exception.Raise_Exception;
408 END IF;
409 Check_Uniqueness;
410 Check_Constraints;
411 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
412 Check_Uniqueness;
413 Check_Constraints;
414 ELSIF (p_action = 'VALIDATE_DELETE') THEN
415 Null;
416 END IF;
417
418 END Before_DML;
419
420 PROCEDURE After_DML (
421 p_action IN VARCHAR2,
422 x_rowid IN VARCHAR2
423 ) IS
424 /*************************************************************
425 Created By :ssuri
426 Date Created By :11-MAY-2000
427 Purpose :NEW TABLE
428 Know limitations, enhancements or remarks
429 Change History
430 Who When What
431
432 (reverse chronological order - newest change first)
433 ***************************************************************/
434
435 BEGIN
436
437 l_rowid := x_rowid;
438
439 IF (p_action = 'INSERT') THEN
440 -- Call all the procedures related to After Insert.
441 Null;
442 ELSIF (p_action = 'UPDATE') THEN
443 -- Call all the procedures related to After Update.
444 Null;
445 ELSIF (p_action = 'DELETE') THEN
446 -- Call all the procedures related to After Delete.
447 Null;
448 END IF;
449
450 l_rowid := null;
451 END After_DML;
452
453 procedure INSERT_ROW (
454 X_ROWID in out NOCOPY VARCHAR2,
455 x_DISPLN_UNIT_ID IN OUT NOCOPY NUMBER,
456 x_DISPLN_UNIT_TYPE IN VARCHAR2,
457 x_PERSON_ID IN NUMBER,
458 x_DISPLN_UNIT_CD IN VARCHAR2,
459 X_MODE in VARCHAR2 default 'R'
460 ) AS
461 /*************************************************************
462 Created By :ssuri
463 Date Created By :11-MAY-2000
464 Purpose :NEW TABLE
465 Know limitations, enhancements or remarks
466 Change History
467 Who When What
468
469 (reverse chronological order - newest change first)
470 ***************************************************************/
471
472 cursor C is select ROWID from IGS_PS_FACLTY_DISP
473 where DISPLN_UNIT_ID= X_DISPLN_UNIT_ID
474 ;
475 X_LAST_UPDATE_DATE DATE ;
476 X_LAST_UPDATED_BY NUMBER ;
477 X_LAST_UPDATE_LOGIN NUMBER ;
478 begin
479 X_LAST_UPDATE_DATE := SYSDATE;
480 if(X_MODE = 'I') then
481 X_LAST_UPDATED_BY := 1;
482 X_LAST_UPDATE_LOGIN := 0;
483 elsif (X_MODE IN ('R', 'S')) then
484 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
485 if X_LAST_UPDATED_BY is NULL then
486 X_LAST_UPDATED_BY := -1;
487 end if;
488 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
489 if X_LAST_UPDATE_LOGIN is NULL then
490 X_LAST_UPDATE_LOGIN := -1;
491 end if;
492 else
493 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
494 IGS_GE_MSG_STACK.ADD;
495 app_exception.raise_exception;
496 end if;
497 SELECT IGS_PS_DISPLN_UNIT_CD_S.nextval into x_DISPLN_UNIT_ID FROM dual;
498 Before_DML(
499 p_action=>'INSERT',
500 x_rowid=>X_ROWID,
501 x_displn_unit_id=>X_DISPLN_UNIT_ID,
502 x_displn_unit_type=>X_DISPLN_UNIT_TYPE,
503 x_person_id=>X_PERSON_ID,
504 x_displn_unit_cd=>X_DISPLN_UNIT_CD,
505 x_creation_date=>X_LAST_UPDATE_DATE,
506 x_created_by=>X_LAST_UPDATED_BY,
507 x_last_update_date=>X_LAST_UPDATE_DATE,
508 x_last_updated_by=>X_LAST_UPDATED_BY,
509 x_last_update_login=>X_LAST_UPDATE_LOGIN);
510 IF (x_mode = 'S') THEN
511 igs_sc_gen_001.set_ctx('R');
512 END IF;
513 insert into IGS_PS_FACLTY_DISP (
514 DISPLN_UNIT_ID
515 ,DISPLN_UNIT_TYPE
516 ,PERSON_ID
517 ,DISPLN_UNIT_CD
518 ,CREATION_DATE
519 ,CREATED_BY
520 ,LAST_UPDATE_DATE
521 ,LAST_UPDATED_BY
522 ,LAST_UPDATE_LOGIN
523 ) values (
524 NEW_REFERENCES.DISPLN_UNIT_ID
525 ,NEW_REFERENCES.DISPLN_UNIT_TYPE
526 ,NEW_REFERENCES.PERSON_ID
527 ,NEW_REFERENCES.DISPLN_UNIT_CD
528 ,X_LAST_UPDATE_DATE
529 ,X_LAST_UPDATED_BY
530 ,X_LAST_UPDATE_DATE
531 ,X_LAST_UPDATED_BY
532 ,X_LAST_UPDATE_LOGIN
533 );
534 IF (x_mode = 'S') THEN
535 igs_sc_gen_001.unset_ctx('R');
536 END IF;
537
538 open c;
539 fetch c into X_ROWID;
540 if (c%notfound) then
541 close c;
542 raise no_data_found;
543 end if;
544 close c;
545 After_DML (
546 p_action => 'INSERT' ,
547 x_rowid => X_ROWID );
548 EXCEPTION
549 WHEN OTHERS THEN
550 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
551 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
552 fnd_message.set_token ('ERR_CD', SQLCODE);
553 igs_ge_msg_stack.add;
554 igs_sc_gen_001.unset_ctx('R');
555 app_exception.raise_exception;
556 ELSE
557 igs_sc_gen_001.unset_ctx('R');
558 RAISE;
559 END IF;
560
561 end INSERT_ROW;
562 procedure LOCK_ROW (
563 X_ROWID in VARCHAR2,
564 x_DISPLN_UNIT_ID IN NUMBER,
565 x_DISPLN_UNIT_TYPE IN VARCHAR2,
566 x_PERSON_ID IN NUMBER,
567 x_DISPLN_UNIT_CD IN VARCHAR2 ) AS
568 /*************************************************************
569 Created By :ssuri
570 Date Created By :11-MAY-2000
571 Purpose :NEW TABLE
572 Know limitations, enhancements or remarks
573 Change History
574 Who When What
575
576 (reverse chronological order - newest change first)
577 ***************************************************************/
578
579 cursor c1 is select
580 DISPLN_UNIT_TYPE
581 , PERSON_ID
582 , DISPLN_UNIT_CD
583 from IGS_PS_FACLTY_DISP
584 where ROWID = X_ROWID
585 for update nowait;
586 tlinfo c1%rowtype;
587 begin
588 open c1;
589 fetch c1 into tlinfo;
590 if (c1%notfound) then
591 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
592 IGS_GE_MSG_STACK.ADD;
593 close c1;
594 app_exception.raise_exception;
595 return;
596 end if;
597 close c1;
598 if ( ( tlinfo.DISPLN_UNIT_TYPE = X_DISPLN_UNIT_TYPE)
599 AND (tlinfo.PERSON_ID = X_PERSON_ID)
600 AND (tlinfo.DISPLN_UNIT_CD = X_DISPLN_UNIT_CD)
601 ) then
602 null;
603 else
604 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
605 IGS_GE_MSG_STACK.ADD;
606 app_exception.raise_exception;
607 end if;
608 return;
609 end LOCK_ROW;
610 Procedure UPDATE_ROW (
611 X_ROWID in VARCHAR2,
612 x_DISPLN_UNIT_ID IN NUMBER,
613 x_DISPLN_UNIT_TYPE IN VARCHAR2,
614 x_PERSON_ID IN NUMBER,
615 x_DISPLN_UNIT_CD IN VARCHAR2,
616 X_MODE in VARCHAR2 default 'R'
617 ) AS
618 /*************************************************************
619 Created By :ssuri
620 Date Created By :11-MAY-2000
621 Purpose :NEW TABLE
622 Know limitations, enhancements or remarks
623 Change History
624 Who When What
625
626 (reverse chronological order - newest change first)
627 ***************************************************************/
628
629 X_LAST_UPDATE_DATE DATE ;
630 X_LAST_UPDATED_BY NUMBER ;
631 X_LAST_UPDATE_LOGIN NUMBER ;
632 begin
633 X_LAST_UPDATE_DATE := SYSDATE;
634 if(X_MODE = 'I') then
635 X_LAST_UPDATED_BY := 1;
636 X_LAST_UPDATE_LOGIN := 0;
637 elsif (X_MODE IN ('R', 'S')) then
638 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
639 if X_LAST_UPDATED_BY is NULL then
640 X_LAST_UPDATED_BY := -1;
641 end if;
642 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
643 if X_LAST_UPDATE_LOGIN is NULL then
644 X_LAST_UPDATE_LOGIN := -1;
645 end if;
646 else
647 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
648 IGS_GE_MSG_STACK.ADD;
649 app_exception.raise_exception;
650 end if;
651 Before_DML(
652 p_action=>'UPDATE',
653 x_rowid=>X_ROWID,
654 x_displn_unit_id=>X_DISPLN_UNIT_ID,
655 x_displn_unit_type=>X_DISPLN_UNIT_TYPE,
656 x_person_id=>X_PERSON_ID,
657 x_displn_unit_cd=>X_DISPLN_UNIT_CD,
658 x_creation_date=>X_LAST_UPDATE_DATE,
659 x_created_by=>X_LAST_UPDATED_BY,
660 x_last_update_date=>X_LAST_UPDATE_DATE,
661 x_last_updated_by=>X_LAST_UPDATED_BY,
662 x_last_update_login=>X_LAST_UPDATE_LOGIN);
663 IF (x_mode = 'S') THEN
664 igs_sc_gen_001.set_ctx('R');
665 END IF;
666 update IGS_PS_FACLTY_DISP set
667 DISPLN_UNIT_TYPE = NEW_REFERENCES.DISPLN_UNIT_TYPE,
668 PERSON_ID = NEW_REFERENCES.PERSON_ID,
669 DISPLN_UNIT_CD = NEW_REFERENCES.DISPLN_UNIT_CD,
670 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
671 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
672 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
673 where ROWID = X_ROWID;
674 if (sql%notfound) then
675 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
676 igs_ge_msg_stack.add;
677 igs_sc_gen_001.unset_ctx('R');
678 app_exception.raise_exception;
679 end if;
680 IF (x_mode = 'S') THEN
681 igs_sc_gen_001.unset_ctx('R');
682 END IF;
683
684
685 After_DML (
686 p_action => 'UPDATE' ,
687 x_rowid => X_ROWID
688 );
689 EXCEPTION
690 WHEN OTHERS THEN
691 IF (SQLCODE = (-28115)) THEN
692 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
693 fnd_message.set_token ('ERR_CD', SQLCODE);
694 igs_ge_msg_stack.add;
695 igs_sc_gen_001.unset_ctx('R');
696 app_exception.raise_exception;
697 ELSE
698 igs_sc_gen_001.unset_ctx('R');
699 RAISE;
700 END IF;
701
702 end UPDATE_ROW;
703 procedure ADD_ROW (
704 X_ROWID in out NOCOPY VARCHAR2,
705 x_DISPLN_UNIT_ID IN OUT NOCOPY NUMBER,
706 x_DISPLN_UNIT_TYPE IN VARCHAR2,
707 x_PERSON_ID IN NUMBER,
708 x_DISPLN_UNIT_CD IN VARCHAR2,
709 X_MODE in VARCHAR2 default 'R'
710 ) AS
711 /*************************************************************
712 Created By :ssuri
713 Date Created By :11-MAY-2000
714 Purpose :NEW TABLE
715 Know limitations, enhancements or remarks
716 Change History
717 Who When What
718
719 (reverse chronological order - newest change first)
720 ***************************************************************/
721
722 cursor c1 is select ROWID from IGS_PS_FACLTY_DISP
723 where DISPLN_UNIT_ID= X_DISPLN_UNIT_ID
724 ;
725 begin
726 open c1;
727 fetch c1 into X_ROWID;
728 if (c1%notfound) then
729 close c1;
730 INSERT_ROW (
731 X_ROWID,
732 X_DISPLN_UNIT_ID,
733 X_DISPLN_UNIT_TYPE,
734 X_PERSON_ID,
735 X_DISPLN_UNIT_CD,
736 X_MODE );
737 return;
738 end if;
739 close c1;
740 UPDATE_ROW (
741 X_ROWID,
742 X_DISPLN_UNIT_ID,
743 X_DISPLN_UNIT_TYPE,
744 X_PERSON_ID,
745 X_DISPLN_UNIT_CD,
746 X_MODE );
747 end ADD_ROW;
748 procedure DELETE_ROW (
749 X_ROWID in VARCHAR2,
750 x_mode IN VARCHAR2
751 ) AS
752 /*************************************************************
753 Created By :ssuri
754 Date Created By :11-MAY-2000
755 Purpose :NEW TABLE
756 Know limitations, enhancements or remarks
757 Change History
758 Who When What
759
760 (reverse chronological order - newest change first)
761 ***************************************************************/
762
763 begin
764 Before_DML (
765 p_action => 'DELETE',
766 x_rowid => X_ROWID
767 );
768 IF (x_mode = 'S') THEN
769 igs_sc_gen_001.set_ctx('R');
770 END IF;
771 delete from IGS_PS_FACLTY_DISP
772 where ROWID = X_ROWID;
773 if (sql%notfound) then
774 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
775 igs_ge_msg_stack.add;
776 igs_sc_gen_001.unset_ctx('R');
777 app_exception.raise_exception;
778 end if;
779 IF (x_mode = 'S') THEN
780 igs_sc_gen_001.unset_ctx('R');
781 END IF;
782
783 After_DML (
784 p_action => 'DELETE',
785 x_rowid => X_ROWID
786 );
787 end DELETE_ROW;
788 END igs_ps_faclty_disp_pkg;
[Home] [Help]