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