1 PACKAGE BODY igs_pe_priv_level_pkg AS
2 /* $Header: IGSNI61B.pls 120.1 2005/06/28 06:12:06 appldev ship $ */
3
4 /******************************************************
5 Created By : nigupta
6 Date Created By : 11-MAY-2000
7 Purpose : To create Table Handler Body
8 Know limitations, enhancements or remarks : None
9 Change History
10 Who When What
11 (reverse chronological order - newest change first)
12 ********************************************************/
13
14 l_rowid VARCHAR2(25);
15 old_references igs_pe_priv_level%RowType;
16 new_references igs_pe_priv_level%RowType;
17
18 PROCEDURE Set_Column_Values (
19 p_action IN VARCHAR2,
20 x_rowid IN VARCHAR2 DEFAULT NULL,
21 x_privacy_level_id IN NUMBER DEFAULT NULL,
22 x_person_id IN NUMBER DEFAULT NULL,
23 x_data_group IN VARCHAR2 DEFAULT NULL,
24 x_data_group_id IN NUMBER DEFAULT NULL,
25 x_action IN VARCHAR2 DEFAULT NULL,
26 x_whom IN VARCHAR2 DEFAULT NULL,
27 x_ref_notes_id IN NUMBER DEFAULT NULL,
28 x_start_date IN DATE DEFAULT NULL,
29 x_end_date IN DATE DEFAULT NULL,
30 x_creation_date IN DATE DEFAULT NULL,
31 x_created_by IN NUMBER DEFAULT NULL,
32 x_last_update_date IN DATE DEFAULT NULL,
33 x_last_updated_by IN NUMBER DEFAULT NULL,
34 x_last_update_login IN NUMBER DEFAULT NULL
35 ) AS
36
37
38 /******************************************************
39 Created By : nigupta
40 Date Created By : 11-MAY-2000
41 Purpose : To Set Column Values
42 Know limitations, enhancements or remarks : None
43 Change History
44 Who When What
45
46
47 (reverse chronological order - newest change first)
48 ********************************************************/
49
50 CURSOR cur_old_ref_values IS
51 SELECT *
52 FROM IGS_PE_PRIV_LEVEL
53 WHERE rowid = x_rowid;
54
55 BEGIN
56
57 l_rowid := x_rowid;
58
59 -- Code for setting the Old and New Reference Values.
60 -- Populate Old Values.
61 Open cur_old_ref_values;
62 Fetch cur_old_ref_values INTO old_references;
63 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
64 Close cur_old_ref_values;
65 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
66 IGS_GE_MSG_STACK.ADD;
67 App_Exception.Raise_Exception;
68 Return;
69 END IF;
70 Close cur_old_ref_values;
71
72 -- Populate New Values.
73 new_references.privacy_level_id := x_privacy_level_id;
74 new_references.person_id := x_person_id;
75 new_references.data_group := x_data_group;
76 new_references.data_group_id := x_data_group_id;
77 new_references.action := x_action;
78 new_references.whom := x_whom;
79 new_references.ref_notes_id := x_ref_notes_id;
80 new_references.start_date := x_start_date;
81 new_references.end_date := x_end_date;
82 IF (p_action = 'UPDATE') THEN
83 new_references.creation_date := old_references.creation_date;
84 new_references.created_by := old_references.created_by;
85 ELSE
86 new_references.creation_date := x_creation_date;
87 new_references.created_by := x_created_by;
88 END IF;
89 new_references.last_update_date := x_last_update_date;
90 new_references.last_updated_by := x_last_updated_by;
91 new_references.last_update_login := x_last_update_login;
92
93 END Set_Column_Values;
94
95 PROCEDURE Check_Constraints (
96 Column_Name IN VARCHAR2 DEFAULT NULL,
97 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
98
99 /******************************************************
100 Created By : nigupta
101 Date Created By : 11-MAY-2000
102 Purpose : To check constraints
103 Know limitations, enhancements or remarks : None
104 Change History
105 Who When What
106
107
108 (reverse chronological order - newest change first)
109 ********************************************************/
110
111 BEGIN
112
113 IF column_name IS NULL THEN
114 NULL;
115 NULL;
116 END IF;
117 END Check_Constraints;
118
119 PROCEDURE Check_Parent_Existance AS
120
121 /******************************************************
122 Created By : nigupta
123 Date Created By : 11-MAY-2000
124 Purpose : To Check Parent Existance
125 Know limitations, enhancements or remarks : None
126 Change History
127 Who When What
128
129
130 (reverse chronological order - newest change first)
131 ********************************************************/
132
133 BEGIN
134
135 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation ('PERSON_PRIVACY_ACTION',
136 new_references.ACTION) THEN
137 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
138 IGS_GE_MSG_STACK.ADD;
139 App_Exception.Raise_Exception;
140 END IF;
141
142 IF (((old_references.person_id = new_references.person_id)) OR
143 ((new_references.person_id IS NULL))) THEN
144 NULL;
145 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
146 new_references.person_id
147 ) THEN
148 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152
153 IF (((old_references.data_group_id = new_references.data_group_id)) OR
154 ((new_references.data_group_id IS NULL))) THEN
155 NULL;
156 ELSIF NOT Igs_Pe_Data_Groups_Pkg.Get_PK_For_Validation (
157 new_references.data_group_id
158 ) THEN
159 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 IF (((old_references.ref_notes_id = new_references.ref_notes_id)) OR
164 ((new_references.ref_notes_id IS NULL))) THEN
165 NULL;
166 ELSIF NOT Igs_Ge_Note_Pkg.Get_PK_For_Validation (
167 new_references.ref_notes_id
168 ) THEN
169 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END IF;
173
174 END Check_Parent_Existance;
175
176 FUNCTION Get_PK_For_Validation (
177 x_privacy_level_id IN NUMBER
178 ) RETURN BOOLEAN AS
179
180 /******************************************************
181 Created By : nigupta
182 Date Created By : 11-MAY-2000
183 Purpose : To enforce Primary Key validations
184 Know limitations, enhancements or remarks : None
185 Change History
186 Who When What
187
188
189 (reverse chronological order - newest change first)
190 ********************************************************/
191
192 CURSOR cur_rowid IS
193 SELECT rowid
194 FROM igs_pe_priv_level
195 WHERE privacy_level_id = x_privacy_level_id
196 FOR UPDATE NOWAIT;
197
198 lv_rowid cur_rowid%RowType;
199
200 BEGIN
201
202 Open cur_rowid;
203 Fetch cur_rowid INTO lv_rowid;
204 IF (cur_rowid%FOUND) THEN
205 Close cur_rowid;
206 Return(TRUE);
207 ELSE
208 Close cur_rowid;
209 Return(FALSE);
210 END IF;
211 END Get_PK_For_Validation;
212
213 PROCEDURE Get_FK_Igs_Pe_Person (
214 x_person_id IN NUMBER
215 ) AS
216
217 /******************************************************
218 Created By : nigupta
219 Date Created By : 11-MAY-2000
220 Purpose : To enforce Foriegn Key validation
221 Know limitations, enhancements or remarks : None
222 Change History
223 Who When What
224
225
226 (reverse chronological order - newest change first)
227 ********************************************************/
228
229 CURSOR cur_rowid IS
230 SELECT rowid
231 FROM igs_pe_priv_level
232 WHERE person_id = x_person_id ;
233
234 lv_rowid cur_rowid%RowType;
235
236 BEGIN
237
238 Open cur_rowid;
239 Fetch cur_rowid INTO lv_rowid;
240 IF (cur_rowid%FOUND) THEN
241 Close cur_rowid;
242 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PP_PPL_FK');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 Return;
246 END IF;
247 Close cur_rowid;
248
249 END Get_FK_Igs_Pe_Person;
250
251 PROCEDURE BeforeDeletePrivLevel
252 IS
253 CURSOR ref_notes IS
254 SELECT rowid
255 FROM igs_ge_note
256 WHERE reference_number = old_references.ref_notes_id;
257 BEGIN
258 FOR ref_rec IN ref_notes LOOP
259 igs_ge_note_pkg.delete_row (ref_rec.rowid);
260 END LOOP;
261
262 END BeforeDeletePrivLevel;
263
264 PROCEDURE beforeinsertupdate(p_inserting BOOLEAN , p_updating BOOLEAN) IS
265 p_message_name VARCHAR2(30);
266 BEGIN
267 IF ( p_inserting = TRUE OR ( p_updating = TRUE AND new_references.data_group_id <> old_references.data_group_id ) ) THEN
268 IF NOT igs_pe_data_groups_pkg.val_data_group(new_references.data_group_id , p_message_name) THEN
269 Fnd_Message.Set_Name ('IGS',p_message_name);
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END IF;
273 END IF;
274
275 IF p_inserting OR p_updating THEN
276 IF(new_references.start_date IS NOT NULL) THEN
277
278 /* kumma, 2902713, Modified the following if condition so that start date should not get compare with the sysdate if user has not changed the start date */
279
280 --IF(trunc(new_references.start_date) <> trunc(sysdate) aND new_references.start_date < sysdate) THEN
281 IF(trunc(new_references.start_date) < trunc(sysdate) AND new_references.start_date <> nvl((old_references.start_date),trunc(sysdate))) THEN
282 Fnd_Message.Set_Name('IGS','IGS_FI_ST_NOT_LT_CURRDT');
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF;
286 END IF;
287
288 IF(new_references.start_date > new_references.end_date) THEN
289 Fnd_Message.Set_name('IGS','IGS_PE_FROM_DT_GRT_TO_DATE');
290 IGS_GE_MSG_STACK.ADD;
291 App_Exception.Raise_Exception;
292 END IF;
293 END IF;
294 END;
295
296
297
298 PROCEDURE Before_DML (
299 p_action IN VARCHAR2,
300 x_rowid IN VARCHAR2 DEFAULT NULL,
301 x_privacy_level_id IN NUMBER DEFAULT NULL,
302 x_person_id IN NUMBER DEFAULT NULL,
303 x_data_group IN VARCHAR2 DEFAULT NULL,
304 x_data_group_id IN NUMBER DEFAULT NULL,
305 x_lvl IN NUMBER DEFAULT NULL,
306 x_action IN VARCHAR2 DEFAULT NULL,
307 x_whom IN VARCHAR2 DEFAULT NULL,
308 x_ref_notes_id IN NUMBER DEFAULT NULL,
309 x_start_date IN DATE DEFAULT NULL,
310 x_end_date IN DATE DEFAULT NULL,
311 x_creation_date IN DATE DEFAULT NULL,
312 x_created_by IN NUMBER DEFAULT NULL,
313 x_last_update_date IN DATE DEFAULT NULL,
314 x_last_updated_by IN NUMBER DEFAULT NULL,
315 x_last_update_login IN NUMBER DEFAULT NULL
316 ) AS
317
318 /******************************************************
319 Created By : nigupta
320 Date Created By : 11-MAY-2000
321 Purpose : To check before DML
322 Know limitations, enhancements or remarks : None
323 Change History
324 Who When What
325
326
327 (reverse chronological order - newest change first)
328 ********************************************************/
329 BEGIN
330
331 Set_Column_Values (
332 p_action,
333 x_rowid,
334 x_privacy_level_id,
335 x_person_id,
336 x_data_group,
337 x_data_group_id,
338 x_action,
339 x_whom,
340 x_ref_notes_id,
341 x_start_date,
342 x_end_date,
343 x_creation_date,
344 x_created_by,
345 x_last_update_date,
346 x_last_updated_by,
347 x_last_update_login
348 );
349 IF (p_action = 'INSERT') THEN
350 -- Call all the procedures related to Before Insert.
351 beforeinsertupdate(TRUE,FALSE);
352 IF Get_Pk_For_Validation(
353 new_references.privacy_level_id) THEN
354 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END IF;
358 Check_Constraints;
359 Check_Parent_Existance;
360 ELSIF (p_action = 'UPDATE') THEN
361 -- Call all the procedures related to Before Update.
362 beforeinsertupdate(FALSE,TRUE);
363 Check_Constraints;
364 Check_Parent_Existance;
365 ELSIF (p_action = 'DELETE') THEN
366 BeforeDeletePrivLevel;
367 -- Call all the procedures related to Before Delete.
368 Null;
369 ELSIF (p_action = 'VALIDATE_INSERT') THEN
370 -- Call all the procedures related to Before Insert.
371 IF Get_PK_For_Validation (
372 new_references.privacy_level_id) THEN
373 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377 Check_Constraints;
378 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
379 Check_Constraints;
380 ELSIF (p_action = 'VALIDATE_DELETE') THEN
381 Null;
382 END IF;
383
384 END Before_DML;
385
386
387 PROCEDURE afterrowinsertupdate(
388 p_inserting IN BOOLEAN,
389 p_updating IN BOOLEAN,
390 p_deleting IN BOOLEAN
391 ) AS
392 ------------------------------------------------------------------------------------------
393 --Created by : kumma
394 --Date created: 23-APR-2003
395 --
396 --Purpose:Bug 2902713. Moved the overlap validation from library
397 --
398 --Known limitations/enhancements and/or remarks:
399 --
400 --Change History:
401 --Who When What
402 ----------------------------------------------------------------------------------------------
403 CURSOR c_priv_overlap(cp_person_id igs_pe_visa.person_id%TYPE, cp_data_group_id igs_pe_priv_level.data_group_id%TYPE) IS
404 SELECT count(1)
405 FROM
406 igs_pe_priv_level p1,
407 igs_pe_priv_level p2
408 WHERE
409 p1.person_id = cp_person_id and
410 p1.person_id = p2.person_id and
411 p1.data_group_id = cp_data_group_id and
412 p1.data_group_id = p2.data_group_id and
413 NVL(p1.end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) >= p2.start_date and
414 NVL(p1.end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) <= NVL(p2.end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) and
415 p1.rowid <> p2.rowid;
416
417 l_count NUMBER(1);
418 BEGIN
419 OPEN c_priv_overlap(new_references.person_id,new_references.data_group_id);
420 FETCH c_priv_overlap INTO l_count;
421 CLOSE c_priv_overlap;
422
423 IF l_count > 0 THEN
424 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PRIV_DT_OVERLAP');
425 IGS_GE_MSG_STACK.ADD;
426 APP_EXCEPTION.RAISE_EXCEPTION;
427 END IF;
428 END afterrowinsertupdate;
429
430
431
432 PROCEDURE After_DML (
433 p_action IN VARCHAR2,
434 x_rowid IN VARCHAR2
435 ) IS
436
437 /******************************************************
438 Created By : nigupta
439 Date Created By : 11-MAY-2000
440 Purpose : To check after DML
441 Know limitations, enhancements or remarks : None
442 Change History
443 Who When What
444
445
446 (reverse chronological order - newest change first)
447 ********************************************************/
448
449 BEGIN
450
451 l_rowid := x_rowid;
452
453 IF (p_action = 'INSERT') THEN
454 -- Call all the procedures related to After Insert.
455 AfterRowInsertUpdate (
456 p_inserting => TRUE,
457 p_updating => FALSE,
458 p_deleting => FALSE
459 );
460
461 ELSIF (p_action = 'UPDATE') THEN
462 -- Call all the procedures related to After Update.
463 AfterRowInsertUpdate (
464 p_inserting => FALSE,
465 p_updating => TRUE,
466 p_deleting => FALSE
467 );
468 ELSIF (p_action = 'DELETE') THEN
469 -- Call all the procedures related to After Delete.
470 Null;
471 END IF;
472
473 END After_DML;
474
475 procedure INSERT_ROW (
476 X_ROWID in out NOCOPY VARCHAR2,
477 x_PRIVACY_LEVEL_ID IN OUT NOCOPY NUMBER,
478 x_PERSON_ID IN NUMBER,
479 x_DATA_GROUP IN VARCHAR2,
480 x_DATA_GROUP_ID IN NUMBER,
481 x_LVL IN NUMBER,
482 x_ACTION IN VARCHAR2,
483 x_WHOM IN VARCHAR2,
484 x_REF_NOTES_ID IN NUMBER,
485 x_START_DATE IN DATE,
486 x_END_DATE IN DATE,
487 X_MODE in VARCHAR2 default 'R'
488 ) AS
489
490 /******************************************************
491 Created By : nigupta
492 Date Created By : 11-MAY-2000
493 Purpose : To insert row
494 Know limitations, enhancements or remarks : None
495 Change History
496 Who When What
497 kumma 03-JUN-2002 Passes NULL for LVL, bug # 2377971
498
499 (reverse chronological order - newest change first)
500 ********************************************************/
501
502 cursor C is select ROWID from IGS_PE_PRIV_LEVEL
503 where PRIVACY_LEVEL_ID= X_PRIVACY_LEVEL_ID
504 ;
505 X_LAST_UPDATE_DATE DATE ;
506 X_LAST_UPDATED_BY NUMBER ;
507 X_LAST_UPDATE_LOGIN NUMBER ;
508 begin
509 X_LAST_UPDATE_DATE := SYSDATE;
510 if(X_MODE = 'I') then
511 X_LAST_UPDATED_BY := 1;
512 X_LAST_UPDATE_LOGIN := 0;
513 elsif (X_MODE IN ('R', 'S')) then
514 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
515 if X_LAST_UPDATED_BY is NULL then
516 X_LAST_UPDATED_BY := -1;
517 end if;
518 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
519 if X_LAST_UPDATE_LOGIN is NULL then
520 X_LAST_UPDATE_LOGIN := -1;
521 end if;
522 else
523 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
524 IGS_GE_MSG_STACK.ADD;
525 app_exception.raise_exception;
526 end if;
527
528 Select IGS_PE_PRIV_LEVEL_S.NEXTVAL into X_PRIVACY_LEVEL_ID from Dual;
529
530 Before_DML(
531 p_action=>'INSERT',
532 x_rowid=>X_ROWID,
533 x_privacy_level_id=>X_PRIVACY_LEVEL_ID,
534 x_person_id=>X_PERSON_ID,
535 x_data_group=>X_DATA_GROUP,
536 x_data_group_id=>X_DATA_GROUP_ID,
537 x_action=>X_ACTION,
538 x_whom=>X_WHOM,
539 x_ref_notes_id=>X_REF_NOTES_ID,
540 x_start_date=>X_START_DATE,
541 x_end_date=>X_END_DATE,
542 x_creation_date=>X_LAST_UPDATE_DATE,
543 x_created_by=>X_LAST_UPDATED_BY,
544 x_last_update_date=>X_LAST_UPDATE_DATE,
545 x_last_updated_by=>X_LAST_UPDATED_BY,
546 x_last_update_login=>X_LAST_UPDATE_LOGIN);
547 IF (x_mode = 'S') THEN
548 igs_sc_gen_001.set_ctx('R');
549 END IF;
550 insert into IGS_PE_PRIV_LEVEL (
551 PRIVACY_LEVEL_ID
552 ,PERSON_ID
553 ,DATA_GROUP
554 ,DATA_GROUP_ID
555 ,LVL
556 ,ACTION
557 ,WHOM
558 ,REF_NOTES_ID
559 ,START_DATE
560 ,END_DATE
561 ,CREATION_DATE
562 ,CREATED_BY
563 ,LAST_UPDATE_DATE
564 ,LAST_UPDATED_BY
565 ,LAST_UPDATE_LOGIN
566 ) values (
567 NEW_REFERENCES.PRIVACY_LEVEL_ID
568 ,NEW_REFERENCES.PERSON_ID
569 ,NEW_REFERENCES.DATA_GROUP
570 ,NEW_REFERENCES.DATA_GROUP_ID
571 ,NULL
572 ,NEW_REFERENCES.ACTION
573 ,NEW_REFERENCES.WHOM
574 ,NEW_REFERENCES.REF_NOTES_ID
575 ,NEW_REFERENCES.START_DATE
576 ,NEW_REFERENCES.END_DATE
577 ,X_LAST_UPDATE_DATE
578 ,X_LAST_UPDATED_BY
579 ,X_LAST_UPDATE_DATE
580 ,X_LAST_UPDATED_BY
581 ,X_LAST_UPDATE_LOGIN
582 );
583 IF (x_mode = 'S') THEN
584 igs_sc_gen_001.unset_ctx('R');
585 END IF;
586
587 open c;
588 fetch c into X_ROWID;
589 if (c%notfound) then
590 close c;
591 raise no_data_found;
592 end if;
593 close c;
594 After_DML (
595 p_action => 'INSERT' ,
596 x_rowid => X_ROWID );
597 EXCEPTION
598 WHEN OTHERS THEN
599 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
600 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
601 fnd_message.set_token ('ERR_CD', SQLCODE);
602 igs_ge_msg_stack.add;
603 igs_sc_gen_001.unset_ctx('R');
604 app_exception.raise_exception;
605 ELSE
606 igs_sc_gen_001.unset_ctx('R');
607 RAISE;
608 END IF;
609
610 end INSERT_ROW;
611
612 procedure LOCK_ROW (
613 X_ROWID in VARCHAR2,
614 x_PRIVACY_LEVEL_ID IN NUMBER,
615 x_PERSON_ID IN NUMBER,
616 x_DATA_GROUP IN VARCHAR2,
617 x_DATA_GROUP_ID IN NUMBER,
618 x_LVL IN NUMBER,
619 x_ACTION IN VARCHAR2,
620 x_WHOM IN VARCHAR2,
621 x_REF_NOTES_ID IN NUMBER,
622 x_START_DATE IN DATE,
623 x_END_DATE IN DATE ) AS
624
625 /******************************************************
626 Created By : nigupta
627 Date Created By : 11-MAY-2000
628 Purpose : To lock row
629 Know limitations, enhancements or remarks : None
630 Change History
631 Who When What
632 kumma 03-JUN-2002 Removed the comparison for LVL, Bug # 2377971
633 and also modified the cursor query to not to select LVL
634
635 (reverse chronological order - newest change first)
636 ********************************************************/
637
638 cursor c1 is select
639 PERSON_ID
640 , DATA_GROUP
641 , DATA_GROUP_ID
642 , ACTION
643 , WHOM
644 , REF_NOTES_ID
645 , START_DATE
646 , END_DATE
647 from IGS_PE_PRIV_LEVEL
648 where ROWID = X_ROWID
649 for update nowait;
650 tlinfo c1%rowtype;
651 begin
652 open c1;
653 fetch c1 into tlinfo;
654 if (c1%notfound) then
655 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
656 IGS_GE_MSG_STACK.ADD;
657 close c1;
658 app_exception.raise_exception;
659 return;
660 end if;
661 close c1;
662 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
663 AND (tlinfo.DATA_GROUP = X_DATA_GROUP)
664 AND (tlinfo.DATA_GROUP_ID = X_DATA_GROUP_ID)
665 AND (tlinfo.ACTION = X_ACTION)
666 AND (tlinfo.WHOM = X_WHOM)
667 AND ((tlinfo.REF_NOTES_ID = X_REF_NOTES_ID)
668 OR ((tlinfo.REF_NOTES_ID is null)
669 AND (X_REF_NOTES_ID is null)))
670 AND (tlinfo.START_DATE = X_START_DATE)
671 AND ((tlinfo.END_DATE = X_END_DATE)
672 OR ((tlinfo.END_DATE is null)
673 AND (X_END_DATE is null)))
674 ) then
675 null;
676 else
677 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
678 IGS_GE_MSG_STACK.ADD;
679 app_exception.raise_exception;
680 end if;
681 return;
682 end LOCK_ROW;
683
684 Procedure UPDATE_ROW (
685 X_ROWID in VARCHAR2,
686 x_PRIVACY_LEVEL_ID IN NUMBER,
687 x_PERSON_ID IN NUMBER,
688 x_DATA_GROUP IN VARCHAR2,
689 x_DATA_GROUP_ID IN NUMBER,
690 x_LVL IN NUMBER,
691 x_ACTION IN VARCHAR2,
692 x_WHOM IN VARCHAR2,
693 x_REF_NOTES_ID IN NUMBER,
694 x_START_DATE IN DATE,
695 x_END_DATE IN DATE,
696 X_MODE in VARCHAR2 default 'R'
697 ) AS
698
699 /******************************************************
700 Created By : nigupta
701 Date Created By : 11-MAY-2000
702 Purpose : To update row
703 Know limitations, enhancements or remarks : None
704 Change History
705 Who When What
706 kumma 03-JUN-2002 Removed the code to update LVL, Bug # 2377971
707
708 (reverse chronological order - newest change first)
709 ********************************************************/
710
711 X_LAST_UPDATE_DATE DATE ;
712 X_LAST_UPDATED_BY NUMBER ;
713 X_LAST_UPDATE_LOGIN NUMBER ;
714 begin
715 X_LAST_UPDATE_DATE := SYSDATE;
716 if(X_MODE = 'I') then
717 X_LAST_UPDATED_BY := 1;
718 X_LAST_UPDATE_LOGIN := 0;
719 elsif (X_MODE IN ('R', 'S')) then
720 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
721 if X_LAST_UPDATED_BY is NULL then
722 X_LAST_UPDATED_BY := -1;
723 end if;
724 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
725 if X_LAST_UPDATE_LOGIN is NULL then
726 X_LAST_UPDATE_LOGIN := -1;
727 end if;
728 else
729 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
730 IGS_GE_MSG_STACK.ADD;
731 app_exception.raise_exception;
732 end if;
733 Before_DML(
734 p_action=>'UPDATE',
735 x_rowid=>X_ROWID,
736 x_privacy_level_id=>X_PRIVACY_LEVEL_ID,
737 x_person_id=>X_PERSON_ID,
738 x_data_group=>X_DATA_GROUP,
739 x_data_group_id=>X_DATA_GROUP_ID,
740 x_action=>X_ACTION,
741 x_whom=>X_WHOM,
742 x_ref_notes_id=>X_REF_NOTES_ID,
743 x_start_date=>X_START_DATE,
744 x_end_date=>X_END_DATE,
745 x_creation_date=>X_LAST_UPDATE_DATE,
746 x_created_by=>X_LAST_UPDATED_BY,
747 x_last_update_date=>X_LAST_UPDATE_DATE,
748 x_last_updated_by=>X_LAST_UPDATED_BY,
749 x_last_update_login=>X_LAST_UPDATE_LOGIN);
750 IF (x_mode = 'S') THEN
751 igs_sc_gen_001.set_ctx('R');
752 END IF;
753 update IGS_PE_PRIV_LEVEL set
754 PERSON_ID = NEW_REFERENCES.PERSON_ID,
755 DATA_GROUP = NEW_REFERENCES.DATA_GROUP,
756 DATA_GROUP_ID = NEW_REFERENCES.DATA_GROUP_ID,
757 ACTION = NEW_REFERENCES.ACTION,
758 WHOM = NEW_REFERENCES.WHOM,
759 REF_NOTES_ID = NEW_REFERENCES.REF_NOTES_ID,
760 START_DATE = NEW_REFERENCES.START_DATE,
761 END_DATE = NEW_REFERENCES.END_DATE,
762 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
763 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
764 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
765 where ROWID = X_ROWID;
766 if (sql%notfound) then
767 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
768 igs_ge_msg_stack.add;
769 igs_sc_gen_001.unset_ctx('R');
770 app_exception.raise_exception;
771 end if;
772 IF (x_mode = 'S') THEN
773 igs_sc_gen_001.unset_ctx('R');
774 END IF;
775
776
777 After_DML (
778 p_action => 'UPDATE' ,
779 x_rowid => X_ROWID
780 );
781 EXCEPTION
782 WHEN OTHERS THEN
783 IF (SQLCODE = (-28115)) THEN
784 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
785 fnd_message.set_token ('ERR_CD', SQLCODE);
786 igs_ge_msg_stack.add;
787 igs_sc_gen_001.unset_ctx('R');
788 app_exception.raise_exception;
789 ELSE
790 igs_sc_gen_001.unset_ctx('R');
791 RAISE;
792 END IF;
793
794 end UPDATE_ROW;
795
796 procedure ADD_ROW (
797 X_ROWID in out NOCOPY VARCHAR2,
798 x_PRIVACY_LEVEL_ID IN OUT NOCOPY NUMBER,
799 x_PERSON_ID IN NUMBER,
800 x_DATA_GROUP IN VARCHAR2,
801 x_DATA_GROUP_ID IN NUMBER,
802 x_LVL IN NUMBER,
803 x_ACTION IN VARCHAR2,
804 x_WHOM IN VARCHAR2,
805 x_REF_NOTES_ID IN NUMBER,
806 x_START_DATE IN DATE,
807 x_END_DATE IN DATE,
808 X_MODE in VARCHAR2 default 'R'
809 ) AS
810
811 /******************************************************
812 Created By : nigupta
813 Date Created By : 11-MAY-2000
814 Purpose : To add row
815 Know limitations, enhancements or remarks : None
816 Change History
817 Who When What
818
819
820 (reverse chronological order - newest change first)
821 ********************************************************/
822
823 cursor c1 is select ROWID from IGS_PE_PRIV_LEVEL
824 where PRIVACY_LEVEL_ID= X_PRIVACY_LEVEL_ID
825 ;
826 begin
827 open c1;
828 fetch c1 into X_ROWID;
829 if (c1%notfound) then
830 close c1;
831 INSERT_ROW (
832 X_ROWID,
833 X_PRIVACY_LEVEL_ID,
834 X_PERSON_ID,
835 X_DATA_GROUP,
836 X_DATA_GROUP_ID,
837 X_LVL,
838 X_ACTION,
839 X_WHOM,
840 X_REF_NOTES_ID,
841 X_START_DATE,
842 X_END_DATE,
843 X_MODE );
844 return;
845 end if;
846 close c1;
847 UPDATE_ROW (
848 X_ROWID,
849 X_PRIVACY_LEVEL_ID,
850 X_PERSON_ID,
851 X_DATA_GROUP,
852 X_DATA_GROUP_ID,
853 X_LVL,
854 X_ACTION,
855 X_WHOM,
856 X_REF_NOTES_ID,
857 X_START_DATE,
858 X_END_DATE,
859 X_MODE );
860 end ADD_ROW;
861
862 procedure DELETE_ROW (
863 X_ROWID in VARCHAR2,
864 x_mode IN VARCHAR2
865 ) AS
866
867 /******************************************************
868 Created By : nigupta
869 Date Created By : 11-MAY-2000
870 Purpose : To Delete row
871 Know limitations, enhancements or remarks : None
872 Change History
873 Who When What
874
875
876 (reverse chronological order - newest change first)
877 ********************************************************/
878
879 begin
880 Before_DML (
881 p_action => 'DELETE',
882 x_rowid => X_ROWID
883 );
884 IF (x_mode = 'S') THEN
885 igs_sc_gen_001.set_ctx('R');
886 END IF;
887 delete from IGS_PE_PRIV_LEVEL
888 where ROWID = X_ROWID;
889 if (sql%notfound) then
890 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
891 igs_ge_msg_stack.add;
892 igs_sc_gen_001.unset_ctx('R');
893 app_exception.raise_exception;
894 end if;
895 IF (x_mode = 'S') THEN
896 igs_sc_gen_001.unset_ctx('R');
897 END IF;
898
899 After_DML (
900 p_action => 'DELETE',
901 x_rowid => X_ROWID
902 );
903 end DELETE_ROW;
904 END igs_pe_priv_level_pkg;
[Home] [Help]