[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_PERSID_GROUP_PKG
Source
1 package body IGS_PE_PERSID_GROUP_PKG AS
2 /* $Header: IGSNI24B.pls 120.0 2005/06/01 20:06:40 appldev noship $ */
3
4 /*
5 --------------------------------------------------------------------------------------
6 -- Bug ID : 2204085
7 -- who when what
8 -- kpadiyar Mar 15,2002 Modified the lock row - Reverted back the lock row changes made
9 -- as the existing records shld be updatable.
10
11 -------------------------------------------------------------------------------------------
12 --------------------------------------------------------------------------------------
13 -- Bug ID : 2203134 - NEED MORE UNIQUENESS ON IGS_PE_PERSID_GROUP_ALL
14 -- who when what
15 -- kpadiyar Mar 14,2002 Added Function get_uk_for_validation and check_uniqueness
16 -- for group_cd column
17
18 -------------------------------------------------------------------------------------------
19
20 --------------------------------------------------------------------------------------
21 -- Bug ID : 2204085
22 -- who when what
23 -- kpadiyar Mar 14,2002 Modified the lock row - Removed the condition check for
24 -- Creator_person_id being null as this column is made not null
25
26 -------------------------------------------------------------------------------------------
27
28 --------------------------------------------------------------------------------------
29 -- Bug ID : 2000408
30 -- who when what
31 -- CDCRUZ Sep 24,2002 New Flex Fld Col's added for
32 -- Person DLD
33
34 -------------------------------------------------------------------------------------------
35
36 Change History : 1220935 bshankar 00/03/25
37 Procedure Affected : Lock_Row
38 Purpose : Since the Creator_Person_Id column in the table has been made nullable, the check
39 in this procedure needs to be changed to prevent wrong locking.
40
41
42 */
43
44 l_rowid VARCHAR2(25);
45
46 old_references IGS_PE_PERSID_GROUP_ALL%RowType;
47
48 new_references IGS_PE_PERSID_GROUP_ALL%RowType;
49
50
51
52 PROCEDURE Set_Column_Values (
53
54 p_action IN VARCHAR2,
55
56 x_rowid IN VARCHAR2 DEFAULT NULL,
57
58 x_group_id IN NUMBER DEFAULT NULL,
59
60 x_group_cd IN VARCHAR2 DEFAULT NULL,
61
62 x_creator_person_id IN NUMBER DEFAULT NULL,
63
64 x_description IN VARCHAR2 DEFAULT NULL,
65
66 x_create_dt IN DATE DEFAULT NULL,
67
68 x_closed_ind IN VARCHAR2 DEFAULT NULL,
69
70 x_comments IN VARCHAR2 DEFAULT NULL,
71
72 x_file_name IN VARCHAR2 DEFAULT NULL,
73
74 x_attribute_category IN VARCHAR2 DEFAULT NULL,
75 x_attribute1 IN VARCHAR2 DEFAULT NULL,
76 x_attribute2 IN VARCHAR2 DEFAULT NULL,
77 x_attribute3 IN VARCHAR2 DEFAULT NULL,
78 x_attribute4 IN VARCHAR2 DEFAULT NULL,
79 x_attribute5 IN VARCHAR2 DEFAULT NULL,
80 x_attribute6 IN VARCHAR2 DEFAULT NULL,
81 x_attribute7 IN VARCHAR2 DEFAULT NULL,
82 x_attribute8 IN VARCHAR2 DEFAULT NULL,
83 x_attribute9 IN VARCHAR2 DEFAULT NULL,
84 x_attribute10 IN VARCHAR2 DEFAULT NULL,
85 x_attribute11 IN VARCHAR2 DEFAULT NULL,
86 x_attribute12 IN VARCHAR2 DEFAULT NULL,
87 x_attribute13 IN VARCHAR2 DEFAULT NULL,
88 x_attribute14 IN VARCHAR2 DEFAULT NULL,
89 x_attribute15 IN VARCHAR2 DEFAULT NULL,
90 x_attribute16 IN VARCHAR2 DEFAULT NULL,
91 x_attribute17 IN VARCHAR2 DEFAULT NULL,
92 x_attribute18 IN VARCHAR2 DEFAULT NULL,
93 x_attribute19 IN VARCHAR2 DEFAULT NULL,
94 x_attribute20 IN VARCHAR2 DEFAULT NULL,
95
96
97 X_ORG_ID in NUMBER DEFAULT NULL,
98
99 x_creation_date IN DATE DEFAULT NULL,
100
101 x_created_by IN NUMBER DEFAULT NULL,
102
103 x_last_update_date IN DATE DEFAULT NULL,
104
105 x_last_updated_by IN NUMBER DEFAULT NULL,
106
107 x_last_update_login IN NUMBER DEFAULT NULL
108
109 ) AS
110
111
112
113 CURSOR cur_old_ref_values IS
114
115 SELECT *
116
117 FROM IGS_PE_PERSID_GROUP_ALL
118
119 WHERE rowid = x_rowid;
120
121
122
123 BEGIN
124
125
126
127 l_rowid := x_rowid;
128
129
130
131 -- Code for setting the Old and New Reference Values.
132
133 -- Populate Old Values.
134
135 Open cur_old_ref_values;
136
137 Fetch cur_old_ref_values INTO old_references;
138
139 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
140
141 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
142
143 IGS_GE_MSG_STACK.ADD;
144
145 Close cur_old_ref_values;
146 App_Exception.Raise_Exception;
147 Return;
148
149 END IF;
150
151 Close cur_old_ref_values;
152
153
154
155 -- Populate New Values.
156
157 new_references.group_id := x_group_id;
158
159 new_references.group_cd := x_group_cd;
160
161 new_references.creator_person_id := x_creator_person_id;
162
163 new_references.description := x_description;
164
165 new_references.create_dt := trunc(x_create_dt);
166
167 new_references.closed_ind := x_closed_ind;
168
169 new_references.comments := x_comments;
170
171 new_references.org_id := x_org_id;
172
173 new_references.file_name := x_file_name ;
174
175 new_references.attribute_category := x_attribute_category ;
176 new_references.attribute1 := x_attribute1 ;
177 new_references.attribute2 := x_attribute2 ;
178 new_references.attribute3 := x_attribute3 ;
179 new_references.attribute4 := x_attribute4 ;
180 new_references.attribute5 := x_attribute5 ;
181 new_references.attribute6 := x_attribute6 ;
182 new_references.attribute7 := x_attribute7 ;
183 new_references.attribute8 := x_attribute8 ;
184 new_references.attribute9 := x_attribute9 ;
185 new_references.attribute10 := x_attribute10 ;
186 new_references.attribute11 := x_attribute11 ;
187 new_references.attribute12 := x_attribute12 ;
188 new_references.attribute13 := x_attribute13 ;
189 new_references.attribute14 := x_attribute14 ;
190 new_references.attribute15 := x_attribute15 ;
191 new_references.attribute16 := x_attribute16 ;
192 new_references.attribute17 := x_attribute17 ;
193 new_references.attribute18 := x_attribute18 ;
194 new_references.attribute19 := x_attribute19 ;
195 new_references.attribute20 := x_attribute20 ;
196
197 IF (p_action = 'UPDATE') THEN
198
199 new_references.creation_date := old_references.creation_date;
200
201 new_references.created_by := old_references.created_by;
202
203 ELSE
204
205 new_references.creation_date := x_creation_date;
206
207 new_references.created_by := x_created_by;
208
209 END IF;
210
211 new_references.last_update_date := x_last_update_date;
212
213 new_references.last_updated_by := x_last_updated_by;
214
215 new_references.last_update_login := x_last_update_login;
216
217
218
219 END Set_Column_Values;
220
221
222
223
224
225 PROCEDURE Check_Constraints (
226 Column_Name IN VARCHAR2 DEFAULT NULL,
227 Column_Value IN VARCHAR2 DEFAULT NULL
228 )
229 AS
230 BEGIN
231 IF column_name is null then
232 NULL;
233 ELSIF upper(Column_name) = 'GROUP_CD' then
234 new_references.group_cd:= column_value;
235 ELSIF upper(Column_name) = 'CLOSED_IND' then
236 new_references.closed_ind := column_value;
237 ELSIF upper(Column_name) = 'GROUP_ID' then
238 new_references.group_id := IGS_GE_NUMBER.to_num(column_value);
239
240 END IF;
241
242 IF upper(column_name) = 'GROUP_CD' OR
243 column_name is null Then
244 IF new_references.group_cd <>UPPER(new_references.group_cd)Then
245 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
246 IGS_GE_MSG_STACK.ADD;
247 App_Exception.Raise_Exception;
248 END IF;
249 END IF;
250
251 IF upper(column_name) = 'CLOSED_IND' OR
252 column_name is null Then
253 IF new_references.closed_ind NOT IN ( 'Y' , 'N' ) Then
254 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
255 IGS_GE_MSG_STACK.ADD;
256 App_Exception.Raise_Exception;
257 END IF;
258 END IF;
259 IF upper(column_name) = 'GROUP_ID' OR
260 column_name is null Then
261 IF new_references.group_id < 1 OR new_references.group_id > 999999 Then
262 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END IF;
266 END IF;
267 END Check_Constraints;
268
269 PROCEDURE check_uniqueness AS
270 /*************************************************************
271 Created By : kpadiyar
272 Date Created By : 14-MAR-2002
273 Purpose : To check uniqueness for GROUP_CD column
274 Know limitations, enhancements or remarks
275 Change History
276 Who When What
277 kpadiyar 14-Mar-2002 Bug # 2203134 - to enforce uniqueness of group_cd column
278 (reverse chronological order - newest change first)
279 ***************************************************************/
280
281 BEGIN
282 IF get_uk_for_validation (
283 new_references.group_cd
284 ) THEN
285 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
286 IGS_GE_MSG_STACK.ADD;
287 app_exception.raise_exception;
288 END IF;
289 END Check_Uniqueness ;
290
291
292 PROCEDURE Check_Parent_Existance AS
293
294 BEGIN
295
296
297
298 IF (((old_references.creator_person_id = new_references.creator_person_id)) OR
299
300 ((new_references.creator_person_id IS NULL))) THEN
301
302 NULL;
303
304 ELSE
305 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
306 new_references.creator_person_id) THEN
307 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
308 IGS_GE_MSG_STACK.ADD;
309 App_Exception.Raise_Exception;
310 END IF;
311 END IF;
312
313
314
315 END Check_Parent_Existance;
316
317 FUNCTION Get_PK_For_Validation (
318
319 x_group_id IN NUMBER
320
321 ) RETURN BOOLEAN AS
322
323
324
325 CURSOR cur_rowid IS
326
327 SELECT rowid
328
329 FROM IGS_PE_PERSID_GROUP_ALL
330
331 WHERE group_id = x_group_id;
332
333
334
335 lv_rowid cur_rowid%RowType;
336
337
338
339 BEGIN
340
341
342
343 Open cur_rowid;
344
345 Fetch cur_rowid INTO lv_rowid;
346
347 IF (cur_rowid%FOUND) THEN
348 Close cur_rowid;
349 Return (TRUE);
350 ELSE
351 Close cur_rowid;
352 Return (FALSE);
353 END IF;
354
355 END Get_PK_For_Validation;
356
357 FUNCTION get_uk_for_validation (
358 x_group_cd IN VARCHAR2
359 ) RETURN BOOLEAN AS
360
361 /*************************************************************
362 Created By : kpadiyar
363 Date Created By : 14-Mar-2002
364 Purpose : To check for unqiueness of group_cd column Bug # 2203134
365 Know limitations, enhancements or remarks
366 Change History
367 Who When What
368 kpadiyar 14-Mar-2002 Bug # 2203134 - To enforce uniqueness on column group_cd
369 (reverse chronological order - newest change first)
370 ***************************************************************/
371
372 CURSOR cur_rowid IS
373 SELECT rowid
374 FROM igs_pe_persid_group_all
375 WHERE group_cd = x_group_cd AND
376 ((l_rowid is null) or (rowid <> l_rowid)) ;
377
378 lv_rowid cur_rowid%RowType;
379
380 BEGIN
381
382 OPEN cur_rowid;
383 FETCH cur_rowid INTO lv_rowid;
384 IF (cur_rowid%FOUND) THEN
385 CLOSE cur_rowid;
386 RETURN (true);
387 ELSE
388 CLOSE cur_rowid;
389 RETURN(false);
390 END IF;
391 END get_uk_for_validation ;
392
393 FUNCTION val_persid_group(p_group_id IN NUMBER ,
394 p_message_name OUT NOCOPY VARCHAR2 )
395 RETURN BOOLEAN IS
396 v_closed_ind igs_pe_persid_group_all.closed_ind%TYPE;
397
398 CURSOR c_get_closed_ind ( cp_group_id igs_pe_persid_group_all.group_id%TYPE) IS
399 SELECT closed_ind
400 FROM igs_pe_persid_group_all
401 WHERE group_id = cp_group_id;
402
403 BEGIN
404 p_message_name := NULL;
405 OPEN c_get_closed_ind(p_group_id);
406 FETCH c_get_closed_ind INTO v_closed_ind;
407 IF (c_get_closed_ind%NOTFOUND) THEN
408 CLOSE c_get_closed_ind;
409 RETURN TRUE;
410 END IF;
411 CLOSE c_get_closed_ind;
412 IF (v_closed_ind = 'Y') THEN
413 p_message_name := 'IGS_PE_PERSID_CLOSED';
414 RETURN FALSE;
415 END IF;
416 RETURN TRUE;
417
418 END val_persid_group;
419
420
421 PROCEDURE GET_FK_IGS_PE_PERSON (
422
423 x_person_id IN NUMBER
424
425 ) AS
426
427
428
429 CURSOR cur_rowid IS
430
431 SELECT rowid
432
433 FROM IGS_PE_PERSID_GROUP_ALL
434
435 WHERE creator_person_id = x_person_id ;
436
437
438
439 lv_rowid cur_rowid%RowType;
440
441
442
443 BEGIN
444
445
446
447 Open cur_rowid;
448
449 Fetch cur_rowid INTO lv_rowid;
450
451 IF (cur_rowid%FOUND) THEN
452
453 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PIG_PE_FK');
454
455 IGS_GE_MSG_STACK.ADD;
456
457 Close cur_rowid;
458 App_Exception.Raise_Exception;
459 Return;
460
461 END IF;
462
463 Close cur_rowid;
464
465
466
467 END GET_FK_IGS_PE_PERSON;
468
469
470
471 PROCEDURE Before_DML (
472
473 p_action IN VARCHAR2,
474
475 x_rowid IN VARCHAR2 DEFAULT NULL,
476
477 x_group_id IN NUMBER DEFAULT NULL,
478
479 x_group_cd IN VARCHAR2 DEFAULT NULL,
480
481 x_creator_person_id IN NUMBER DEFAULT NULL,
482
483 x_description IN VARCHAR2 DEFAULT NULL,
484
485 x_create_dt IN DATE DEFAULT NULL,
486
487 x_closed_ind IN VARCHAR2 DEFAULT NULL,
488
489 x_comments IN VARCHAR2 DEFAULT NULL,
490
491 x_file_name IN VARCHAR2 DEFAULT NULL,
492 x_attribute_category IN VARCHAR2 DEFAULT NULL,
493 x_attribute1 IN VARCHAR2 DEFAULT NULL,
494 x_attribute2 IN VARCHAR2 DEFAULT NULL,
495 x_attribute3 IN VARCHAR2 DEFAULT NULL,
496 x_attribute4 IN VARCHAR2 DEFAULT NULL,
497 x_attribute5 IN VARCHAR2 DEFAULT NULL,
498 x_attribute6 IN VARCHAR2 DEFAULT NULL,
499 x_attribute7 IN VARCHAR2 DEFAULT NULL,
500 x_attribute8 IN VARCHAR2 DEFAULT NULL,
501 x_attribute9 IN VARCHAR2 DEFAULT NULL,
502 x_attribute10 IN VARCHAR2 DEFAULT NULL,
503 x_attribute11 IN VARCHAR2 DEFAULT NULL,
504 x_attribute12 IN VARCHAR2 DEFAULT NULL,
505 x_attribute13 IN VARCHAR2 DEFAULT NULL,
506 x_attribute14 IN VARCHAR2 DEFAULT NULL,
507 x_attribute15 IN VARCHAR2 DEFAULT NULL,
508 x_attribute16 IN VARCHAR2 DEFAULT NULL,
509 x_attribute17 IN VARCHAR2 DEFAULT NULL,
510 x_attribute18 IN VARCHAR2 DEFAULT NULL,
511 x_attribute19 IN VARCHAR2 DEFAULT NULL,
512 x_attribute20 IN VARCHAR2 DEFAULT NULL,
513
514 X_ORG_ID in NUMBER DEFAULT NULL,
515
516 x_creation_date IN DATE DEFAULT NULL,
517
518 x_created_by IN NUMBER DEFAULT NULL,
519
520 x_last_update_date IN DATE DEFAULT NULL,
521
522 x_last_updated_by IN NUMBER DEFAULT NULL,
523
524 x_last_update_login IN NUMBER DEFAULT NULL
525
526 ) AS
527 CURSOR is_creator_deceased IS SELECT deceased_ind FROM igs_pe_hz_parties WHERE party_id = x_creator_person_id;
528 CURSOR is_creator_too_young IS SELECT date_of_birth FROM HZ_PERSON_PROFILES where party_id = x_creator_person_id AND effective_end_date IS NULL;
529
530 l_deceased_ind IGS_PE_HZ_PARTIES.deceased_ind%TYPE;
531 l_date_of_birth HZ_PERSON_PROFILES.date_of_birth%TYPE;
532
533 BEGIN
534 Set_Column_Values (
535
536 p_action,
537
538 x_rowid,
539
540 x_group_id,
541
542 x_group_cd,
543
544 x_creator_person_id,
545
546 x_description,
547
548 x_create_dt,
549
550 x_closed_ind,
551
552 x_comments,
553
554 x_file_name,
555
556 x_attribute_category ,
557 x_attribute1 ,
558 x_attribute2 ,
559 x_attribute3 ,
560 x_attribute4 ,
561 x_attribute5 ,
562 x_attribute6 ,
563 x_attribute7 ,
564 x_attribute8 ,
565 x_attribute9 ,
566 x_attribute10 ,
567 x_attribute11 ,
568 x_attribute12 ,
569 x_attribute13 ,
570 x_attribute14 ,
571 x_attribute15 ,
572 x_attribute16 ,
573 x_attribute17 ,
574 x_attribute18 ,
575 x_attribute19 ,
576 x_attribute20 ,
577
578
579 x_org_id,
580
581 x_creation_date,
582
583 x_created_by,
584
585 x_last_update_date,
586
587 x_last_updated_by,
588
589 x_last_update_login
590
591 );
592
593
594 IF (p_action in ('INSERT','UPDATE')) THEN
595 OPEN is_creator_deceased; FETCH is_creator_deceased INTO l_deceased_ind; CLOSE is_creator_deceased;
596 IF(NVL(l_deceased_ind,'N')='Y')THEN
597 Fnd_Message.Set_Name ('IGS', 'IGS_PE_CRT_PRSN_DECEASED');
598 IGS_GE_MSG_STACK.ADD;
599 App_Exception.Raise_Exception;
600 END IF;
601 OPEN is_creator_too_young; FETCH is_creator_too_young INTO l_date_of_birth; CLOSE is_creator_too_young;
602 IF(l_date_of_birth IS NOT NULL AND l_date_of_birth > x_create_dt ) THEN
603 Fnd_Message.Set_Name ('IGS', 'IGS_PE_DOB_LT_CRT_DT');
604 IGS_GE_MSG_STACK.ADD;
605 App_Exception.Raise_Exception;
606 END IF;
607 END IF;
608 IF (p_action = 'INSERT') THEN
609 -- Call all the procedures related to Before Insert.
610 IF Get_PK_For_Validation (
611 new_references.group_id ) THEN
612 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
613 IGS_GE_MSG_STACK.ADD;
614 App_Exception.Raise_Exception;
615 END IF;
616 check_uniqueness;
617 Check_Constraints; -- if procedure present
618 Check_Parent_Existance; -- if procedure present
619 ELSIF (p_action = 'UPDATE') THEN
620 -- Call all the procedures related to Before Update.
621 check_uniqueness;
622 Check_Constraints; -- if procedure present
623 Check_Parent_Existance; -- if procedure present
624 ELSIF (p_action = 'VALIDATE_INSERT') THEN
625 IF Get_PK_For_Validation (
626 new_references.group_id ) THEN
627 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
628 IGS_GE_MSG_STACK.ADD;
629 App_Exception.Raise_Exception;
630 END IF;
631 Check_Constraints; -- if procedure present
632 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
633 check_uniqueness;
634 Check_Constraints; -- if procedure present
635 END IF;
636 l_rowid := NULL;
637 END Before_DML;
638
639
640
641 PROCEDURE After_DML (
642
643 p_action IN VARCHAR2,
644
645 x_rowid IN VARCHAR2
646
647 ) AS
648
649 BEGIN
650
651
652
653 l_rowid := x_rowid;
654
655
656
657 IF (p_action = 'INSERT') THEN
658
659 -- Call all the procedures related to After Insert.
660
661 Null;
662
663 ELSIF (p_action = 'UPDATE') THEN
664
665 -- Call all the procedures related to After Update.
666
667 Null;
668
669 END IF;
670 l_rowid := NULL;
671 END After_DML;
672
673
674 procedure INSERT_ROW (
675 X_ROWID in out NOCOPY VARCHAR2,
676 X_GROUP_ID in NUMBER,
677 X_GROUP_CD in VARCHAR2,
678 X_CREATOR_PERSON_ID in NUMBER,
679 X_DESCRIPTION in VARCHAR2,
680 X_CREATE_DT in DATE,
681 X_CLOSED_IND in VARCHAR2,
682 X_COMMENTS in VARCHAR2,
683 X_FILE_NAME in VARCHAR2 DEFAULT NULL,
684 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
685 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
686 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
687 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
688 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
689 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
690 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
691 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
692 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
693 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
694 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
695 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
696 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
697 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
698 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
699 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
700 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
701 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
702 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
703 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
704 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
705 X_ORG_ID in NUMBER,
706 X_MODE in VARCHAR2 default 'R'
707 ) AS
708 cursor C is select ROWID from IGS_PE_PERSID_GROUP_ALL
709 where GROUP_ID = X_GROUP_ID;
710 X_LAST_UPDATE_DATE DATE;
711 X_LAST_UPDATED_BY NUMBER;
712 X_LAST_UPDATE_LOGIN NUMBER;
713
714 X_REQUEST_ID NUMBER;
715
716 X_PROGRAM_ID NUMBER;
717
718 X_PROGRAM_APPLICATION_ID NUMBER;
719
720 X_PROGRAM_UPDATE_DATE DATE;
721
722 begin
723 X_LAST_UPDATE_DATE := SYSDATE;
724 if(X_MODE = 'I') then
725 X_LAST_UPDATED_BY := 1;
726 X_LAST_UPDATE_LOGIN := 0;
727 elsif (X_MODE = 'R') then
728 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
729 if X_LAST_UPDATED_BY is NULL then
730 X_LAST_UPDATED_BY := -1;
731 end if;
732 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
733 if X_LAST_UPDATE_LOGIN is NULL then
734 X_LAST_UPDATE_LOGIN := -1;
735 end if;
736
737 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
738
739 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
740
741
742
743 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
744
745 if (X_REQUEST_ID = -1) then
746
747 X_REQUEST_ID := NULL;
748
749 X_PROGRAM_ID := NULL;
750
751 X_PROGRAM_APPLICATION_ID := NULL;
752
753 X_PROGRAM_UPDATE_DATE := NULL;
754
755 else
756
757 X_PROGRAM_UPDATE_DATE := SYSDATE;
758
759 end if;
760 else
761 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
762 IGS_GE_MSG_STACK.ADD;
763 app_exception.raise_exception;
764 end if;
765
766 Before_DML(
767
768 p_action=>'INSERT',
769
770 x_rowid=>X_ROWID,
771
772 x_closed_ind=> NVL(X_CLOSED_IND,'N'),
773
774 x_comments=>X_COMMENTS,
775
776 x_create_dt=>X_CREATE_DT,
777
778 x_creator_person_id=>X_CREATOR_PERSON_ID,
779
780 x_description=>X_DESCRIPTION,
781
782 x_group_cd=>X_GROUP_CD,
783
784 x_group_id=>X_GROUP_ID,
785
786 x_file_name => X_FILE_NAME,
787 x_attribute_category => X_ATTRIBUTE_CATEGORY,
788 x_attribute1 => X_ATTRIBUTE1,
789 x_attribute2 => X_ATTRIBUTE2,
790 x_attribute3 => X_ATTRIBUTE3,
791 x_attribute4 => X_ATTRIBUTE4,
792 x_attribute5 => X_ATTRIBUTE5,
793 x_attribute6 => X_ATTRIBUTE6,
794 x_attribute7 => X_ATTRIBUTE7,
795 x_attribute8 => X_ATTRIBUTE8,
796 x_attribute9 => X_ATTRIBUTE9,
797 x_attribute10 => X_ATTRIBUTE10,
798 x_attribute11 => X_ATTRIBUTE11,
799 x_attribute12 => X_ATTRIBUTE12,
800 x_attribute13 => X_ATTRIBUTE13,
801 x_attribute14 => X_ATTRIBUTE14,
802 x_attribute15 => X_ATTRIBUTE15,
803 x_attribute16 => X_ATTRIBUTE16,
804 x_attribute17 => X_ATTRIBUTE17,
805 x_attribute18 => X_ATTRIBUTE18,
806 x_attribute19 => X_ATTRIBUTE19,
807 x_attribute20 => X_ATTRIBUTE20,
808
809
810 x_org_id => igs_ge_gen_003.get_org_id,
811
812 x_creation_date=>X_LAST_UPDATE_DATE,
813
814 x_created_by=>X_LAST_UPDATED_BY,
815
816 x_last_update_date=>X_LAST_UPDATE_DATE,
817
818 x_last_updated_by=>X_LAST_UPDATED_BY,
819
820 x_last_update_login=>X_LAST_UPDATE_LOGIN
821
822 );
823 insert into IGS_PE_PERSID_GROUP_ALL (
824 GROUP_ID,
825 GROUP_CD,
826 CREATOR_PERSON_ID,
827 DESCRIPTION,
828 CREATE_DT,
829 CLOSED_IND,
830 COMMENTS,
831 FILE_NAME,
832 ATTRIBUTE_CATEGORY,
833 ATTRIBUTE1 ,
834 ATTRIBUTE2 ,
835 ATTRIBUTE3 ,
836 ATTRIBUTE4 ,
837 ATTRIBUTE5 ,
838 ATTRIBUTE6 ,
839 ATTRIBUTE7 ,
840 ATTRIBUTE8 ,
841 ATTRIBUTE9 ,
842 ATTRIBUTE10 ,
843 ATTRIBUTE11 ,
844 ATTRIBUTE12 ,
845 ATTRIBUTE13 ,
846 ATTRIBUTE14 ,
847 ATTRIBUTE15 ,
848 ATTRIBUTE16 ,
849 ATTRIBUTE17 ,
850 ATTRIBUTE18 ,
851 ATTRIBUTE19 ,
852 ATTRIBUTE20 ,
853 ORG_ID,
854 CREATION_DATE,
855 CREATED_BY,
856 LAST_UPDATE_DATE,
857 LAST_UPDATED_BY,
858 LAST_UPDATE_LOGIN,
859
860 REQUEST_ID,
861
862 PROGRAM_ID,
863
864 PROGRAM_APPLICATION_ID,
865
866 PROGRAM_UPDATE_DATE
867
868 ) values (
869 NEW_REFERENCES.GROUP_ID,
870 NEW_REFERENCES.GROUP_CD,
871 NEW_REFERENCES.CREATOR_PERSON_ID,
872 NEW_REFERENCES.DESCRIPTION,
873 NEW_REFERENCES.CREATE_DT,
874 NEW_REFERENCES.CLOSED_IND,
875 NEW_REFERENCES.COMMENTS,
876 NEW_REFERENCES.FILE_NAME,
877 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
878 NEW_REFERENCES.ATTRIBUTE1 ,
879 NEW_REFERENCES.ATTRIBUTE2 ,
880 NEW_REFERENCES.ATTRIBUTE3 ,
881 NEW_REFERENCES.ATTRIBUTE4 ,
882 NEW_REFERENCES.ATTRIBUTE5 ,
883 NEW_REFERENCES.ATTRIBUTE6 ,
884 NEW_REFERENCES.ATTRIBUTE7 ,
885 NEW_REFERENCES.ATTRIBUTE8 ,
886 NEW_REFERENCES.ATTRIBUTE9 ,
887 NEW_REFERENCES.ATTRIBUTE10 ,
888 NEW_REFERENCES.ATTRIBUTE11 ,
889 NEW_REFERENCES.ATTRIBUTE12 ,
890 NEW_REFERENCES.ATTRIBUTE13 ,
891 NEW_REFERENCES.ATTRIBUTE14 ,
892 NEW_REFERENCES.ATTRIBUTE15 ,
893 NEW_REFERENCES.ATTRIBUTE16 ,
894 NEW_REFERENCES.ATTRIBUTE17 ,
895 NEW_REFERENCES.ATTRIBUTE18 ,
896 NEW_REFERENCES.ATTRIBUTE19 ,
897 NEW_REFERENCES.ATTRIBUTE20 ,
898 NEW_REFERENCES.ORG_ID,
899 X_LAST_UPDATE_DATE,
900 X_LAST_UPDATED_BY,
901 X_LAST_UPDATE_DATE,
902 X_LAST_UPDATED_BY,
903 X_LAST_UPDATE_LOGIN,
904
905 X_REQUEST_ID,
906
907 X_PROGRAM_ID,
908
909 X_PROGRAM_APPLICATION_ID,
910
911 X_PROGRAM_UPDATE_DATE
912 );
913
914 open c;
915 fetch c into X_ROWID;
916 if (c%notfound) then
917 close c;
918 raise no_data_found;
919 end if;
920 close c;
921 After_DML(
922
923 p_action => 'INSERT',
924
925 x_rowid => X_ROWID
926
927 );
928 end INSERT_ROW;
929
930 procedure LOCK_ROW (
931
932 X_ROWID in VARCHAR2,
933 X_GROUP_ID in NUMBER,
934 X_GROUP_CD in VARCHAR2,
935 X_CREATOR_PERSON_ID in NUMBER,
936 X_DESCRIPTION in VARCHAR2,
937 X_CREATE_DT in DATE,
938 X_CLOSED_IND in VARCHAR2,
939 X_COMMENTS in VARCHAR2,
940 X_FILE_NAME in VARCHAR2 DEFAULT NULL,
941 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
942 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
943 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
944 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
945 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
946 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
947 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
948 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
949 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
950 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
951 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
952 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
953 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
954 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
955 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
956 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
957 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
958 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
959 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
960 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
961 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL
962 ) AS
963 cursor c1 is select
964 GROUP_CD,
965 CREATOR_PERSON_ID,
966 DESCRIPTION,
967 CREATE_DT,
968 CLOSED_IND,
969 COMMENTS,
970 FILE_NAME,
971 ATTRIBUTE_CATEGORY,
972 ATTRIBUTE1,
973 ATTRIBUTE2,
974 ATTRIBUTE3,
975 ATTRIBUTE4,
976 ATTRIBUTE5,
977 ATTRIBUTE6,
978 ATTRIBUTE7,
979 ATTRIBUTE8,
980 ATTRIBUTE9,
981 ATTRIBUTE10,
982 ATTRIBUTE11,
983 ATTRIBUTE12,
984 ATTRIBUTE13,
985 ATTRIBUTE14,
986 ATTRIBUTE15,
987 ATTRIBUTE16,
988 ATTRIBUTE17,
989 ATTRIBUTE18,
990 ATTRIBUTE19,
991 ATTRIBUTE20
992 from IGS_PE_PERSID_GROUP_ALL
993 where ROWID = X_ROWID
994 for update nowait;
995 tlinfo c1%rowtype;
996
997 begin
998 open c1;
999 fetch c1 into tlinfo;
1000 if (c1%notfound) then
1001 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1002
1003 close c1;
1004 App_Exception.Raise_Exception;
1005 return;
1006 end if;
1007 close c1;
1008
1009 if ((tlinfo.GROUP_CD = X_GROUP_CD)
1010 AND ((tlinfo.CREATOR_PERSON_ID = X_CREATOR_PERSON_ID)
1011 OR ((tlinfo.CREATOR_PERSON_ID IS null)
1012 AND (X_CREATOR_PERSON_ID IS null)))
1013 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
1014 AND (tlinfo.CREATE_DT = X_CREATE_DT)
1015 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
1016 AND ((tlinfo.COMMENTS = X_COMMENTS) OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null)))
1017 AND (( tlinfo.FILE_NAME = X_FILE_NAME) OR (( tlinfo.FILE_NAME is null) AND (X_FILE_NAME is null)))
1018 AND (( tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY) OR (( tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
1019 AND (( tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1) OR (( tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
1020 AND (( tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2) OR (( tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
1021 AND (( tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3) OR (( tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
1022 AND (( tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4) OR (( tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
1023 AND (( tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5) OR (( tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
1024 AND (( tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6) OR (( tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
1025 AND (( tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7) OR (( tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
1026 AND (( tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8) OR (( tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
1027 AND (( tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9) OR (( tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
1028 AND (( tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10) OR (( tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
1029 AND (( tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11) OR (( tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
1030 AND (( tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12) OR (( tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
1031 AND (( tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13) OR (( tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
1032 AND (( tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14) OR (( tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
1033 AND (( tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15) OR (( tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
1034 AND (( tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16) OR (( tlinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
1035 AND (( tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17) OR (( tlinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
1036 AND (( tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18) OR (( tlinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
1037 AND (( tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19) OR (( tlinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
1038 AND (( tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20) OR (( tlinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
1039
1040 ) then
1041 null;
1042 else
1043 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1044 app_exception.raise_exception;
1045 end if;
1046 return;
1047 end LOCK_ROW;
1048
1049 procedure UPDATE_ROW (
1050
1051 X_ROWID in VARCHAR2,
1052 X_GROUP_ID in NUMBER,
1053 X_GROUP_CD in VARCHAR2,
1054 X_CREATOR_PERSON_ID in NUMBER,
1055 X_DESCRIPTION in VARCHAR2,
1056 X_CREATE_DT in DATE,
1057 X_CLOSED_IND in VARCHAR2,
1058 X_COMMENTS in VARCHAR2,
1059 X_FILE_NAME in VARCHAR2 DEFAULT NULL,
1060 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
1061 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
1062 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
1063 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
1064 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
1065 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
1066 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
1067 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
1068 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
1069 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
1070 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
1071 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
1072 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
1073 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
1074 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
1075 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
1076 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
1077 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
1078 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
1079 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
1080 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
1081 X_MODE in VARCHAR2 default 'R'
1082 ) AS
1083 X_LAST_UPDATE_DATE DATE;
1084 X_LAST_UPDATED_BY NUMBER;
1085 X_LAST_UPDATE_LOGIN NUMBER;
1086
1087 X_REQUEST_ID NUMBER;
1088
1089 X_PROGRAM_ID NUMBER;
1090
1091 X_PROGRAM_APPLICATION_ID NUMBER;
1092
1093 X_PROGRAM_UPDATE_DATE DATE;
1094 begin
1095 X_LAST_UPDATE_DATE := SYSDATE;
1096 if(X_MODE = 'I') then
1097 X_LAST_UPDATED_BY := 1;
1098 X_LAST_UPDATE_LOGIN := 0;
1099 elsif (X_MODE = 'R') then
1100 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1101 if X_LAST_UPDATED_BY is NULL then
1102 X_LAST_UPDATED_BY := -1;
1103 end if;
1104 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1105 if X_LAST_UPDATE_LOGIN is NULL then
1106 X_LAST_UPDATE_LOGIN := -1;
1107 end if;
1108 else
1109 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1110 IGS_GE_MSG_STACK.ADD;
1111 app_exception.raise_exception;
1112 end if;
1113
1114 Before_DML(
1115
1116 p_action=>'UPDATE',
1117
1118 x_rowid=>X_ROWID,
1119
1120 x_closed_ind=>X_CLOSED_IND,
1121
1122 x_comments=>X_COMMENTS,
1123
1124 x_create_dt=>X_CREATE_DT,
1125
1126 x_creator_person_id=>X_CREATOR_PERSON_ID,
1127
1128 x_description=>X_DESCRIPTION,
1129
1130 x_group_cd=>X_GROUP_CD,
1131
1132 x_group_id=>X_GROUP_ID,
1133
1134 x_file_name => X_FILE_NAME,
1135
1136 x_attribute_category => X_ATTRIBUTE_CATEGORY,
1137 x_attribute1 => X_ATTRIBUTE1,
1138 x_attribute2 => X_ATTRIBUTE2,
1139 x_attribute3 => X_ATTRIBUTE3,
1140 x_attribute4 => X_ATTRIBUTE4,
1141 x_attribute5 => X_ATTRIBUTE5,
1142 x_attribute6 => X_ATTRIBUTE6,
1143 x_attribute7 => X_ATTRIBUTE7,
1144 x_attribute8 => X_ATTRIBUTE8,
1145 x_attribute9 => X_ATTRIBUTE9,
1146 x_attribute10 => X_ATTRIBUTE10,
1147 x_attribute11 => X_ATTRIBUTE11,
1148 x_attribute12 => X_ATTRIBUTE12,
1149 x_attribute13 => X_ATTRIBUTE13,
1150 x_attribute14 => X_ATTRIBUTE14,
1151 x_attribute15 => X_ATTRIBUTE15,
1152 x_attribute16 => X_ATTRIBUTE16,
1153 x_attribute17 => X_ATTRIBUTE17,
1154 x_attribute18 => X_ATTRIBUTE18,
1155 x_attribute19 => X_ATTRIBUTE19,
1156 x_attribute20 => X_ATTRIBUTE20,
1157
1158
1159 x_creation_date=>X_LAST_UPDATE_DATE,
1160
1161 x_created_by=>X_LAST_UPDATED_BY,
1162
1163 x_last_update_date=>X_LAST_UPDATE_DATE,
1164
1165 x_last_updated_by=>X_LAST_UPDATED_BY,
1166
1167 x_last_update_login=>X_LAST_UPDATE_LOGIN
1168
1169 );
1170
1171 if (X_MODE = 'R') then
1172
1173 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1174
1175 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1176
1177 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1178
1179 if (X_REQUEST_ID = -1) then
1180
1181 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1182
1183 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1184
1185 X_PROGRAM_APPLICATION_ID :=
1186
1187 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1188
1189 X_PROGRAM_UPDATE_DATE :=
1190
1191 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1192
1193 else
1194
1195 X_PROGRAM_UPDATE_DATE := SYSDATE;
1196
1197 end if;
1198
1199 end if;
1200
1201
1202 update IGS_PE_PERSID_GROUP_ALL set
1203 GROUP_CD = NEW_REFERENCES.GROUP_CD,
1204 CREATOR_PERSON_ID = NEW_REFERENCES.CREATOR_PERSON_ID,
1205 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
1206 CREATE_DT = NEW_REFERENCES.CREATE_DT,
1207 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
1208 COMMENTS = NEW_REFERENCES.COMMENTS,
1209 FILE_NAME = NEW_REFERENCES.FILE_NAME,
1210 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1211 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1212 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1213 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1214 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1215 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1216 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1217 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1218 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1219 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1220 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1221 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1222 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1223 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1224 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1225 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1226 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1227 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1228 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1229 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1230 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1231 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1232 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1233 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1234
1235 REQUEST_ID = X_REQUEST_ID,
1236
1237 PROGRAM_ID = X_PROGRAM_ID,
1238
1239 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1240
1241 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1242
1243 where ROWID = X_ROWID
1244 ;
1245 if (sql%notfound) then
1246 raise no_data_found;
1247 end if;
1248
1249 After_DML(
1250
1251 p_action => 'UPDATE',
1252
1253 x_rowid => X_ROWID
1254
1255 );
1256 end UPDATE_ROW;
1257
1258 procedure ADD_ROW (
1259 X_ROWID in out NOCOPY VARCHAR2,
1260 X_GROUP_ID in NUMBER,
1261 X_GROUP_CD in VARCHAR2,
1262 X_CREATOR_PERSON_ID in NUMBER,
1263 X_DESCRIPTION in VARCHAR2,
1264 X_CREATE_DT in DATE,
1265 X_CLOSED_IND in VARCHAR2,
1266 X_COMMENTS in VARCHAR2,
1267 X_ORG_ID in NUMBER,
1268 X_FILE_NAME IN VARCHAR2 DEFAULT NULL,
1269 x_attribute_category IN VARCHAR2 DEFAULT NULL,
1270 x_attribute1 IN VARCHAR2 DEFAULT NULL,
1271 x_attribute2 IN VARCHAR2 DEFAULT NULL,
1272 x_attribute3 IN VARCHAR2 DEFAULT NULL,
1273 x_attribute4 IN VARCHAR2 DEFAULT NULL,
1274 x_attribute5 IN VARCHAR2 DEFAULT NULL,
1275 x_attribute6 IN VARCHAR2 DEFAULT NULL,
1276 x_attribute7 IN VARCHAR2 DEFAULT NULL,
1277 x_attribute8 IN VARCHAR2 DEFAULT NULL,
1278 x_attribute9 IN VARCHAR2 DEFAULT NULL,
1279 x_attribute10 IN VARCHAR2 DEFAULT NULL,
1280 x_attribute11 IN VARCHAR2 DEFAULT NULL,
1281 x_attribute12 IN VARCHAR2 DEFAULT NULL,
1282 x_attribute13 IN VARCHAR2 DEFAULT NULL,
1283 x_attribute14 IN VARCHAR2 DEFAULT NULL,
1284 x_attribute15 IN VARCHAR2 DEFAULT NULL,
1285 x_attribute16 IN VARCHAR2 DEFAULT NULL,
1286 x_attribute17 IN VARCHAR2 DEFAULT NULL,
1287 x_attribute18 IN VARCHAR2 DEFAULT NULL,
1288 x_attribute19 IN VARCHAR2 DEFAULT NULL,
1289 x_attribute20 IN VARCHAR2 DEFAULT NULL,
1290 X_MODE in VARCHAR2 default 'R'
1291 ) AS
1292 cursor c1 is select rowid from IGS_PE_PERSID_GROUP_ALL
1293 where GROUP_ID = X_GROUP_ID
1294 ;
1295
1296 begin
1297 open c1;
1298 fetch c1 into X_ROWID;
1299 if (c1%notfound) then
1300 close c1;
1301 INSERT_ROW (
1302 X_ROWID,
1303 X_GROUP_ID,
1304 X_GROUP_CD,
1305 X_CREATOR_PERSON_ID,
1306 X_DESCRIPTION,
1307 X_CREATE_DT,
1308 X_CLOSED_IND,
1309 X_COMMENTS,
1310 X_FILE_NAME,
1311 X_ATTRIBUTE_CATEGORY,
1312 X_ATTRIBUTE1,
1313 X_ATTRIBUTE2,
1314 X_ATTRIBUTE3,
1315 X_ATTRIBUTE4,
1316 X_ATTRIBUTE5,
1317 X_ATTRIBUTE6,
1318 X_ATTRIBUTE7,
1319 X_ATTRIBUTE8,
1320 X_ATTRIBUTE9,
1321 X_ATTRIBUTE10,
1322 X_ATTRIBUTE11,
1323 X_ATTRIBUTE12,
1324 X_ATTRIBUTE13,
1325 X_ATTRIBUTE14,
1326 X_ATTRIBUTE15,
1327 X_ATTRIBUTE16,
1328 X_ATTRIBUTE17,
1329 X_ATTRIBUTE18,
1330 X_ATTRIBUTE19,
1331 X_ATTRIBUTE20,
1332 x_org_id,
1333 X_MODE);
1334 return;
1335 end if;
1336 close c1;
1337 UPDATE_ROW (
1338
1339 X_ROWID,
1340 X_GROUP_ID,
1341 X_GROUP_CD,
1342 X_CREATOR_PERSON_ID,
1343 X_DESCRIPTION,
1344 X_CREATE_DT,
1345 X_CLOSED_IND,
1346 X_COMMENTS,
1347 X_FILE_NAME,
1348 X_ATTRIBUTE_CATEGORY,
1349 X_ATTRIBUTE1,
1350 X_ATTRIBUTE2,
1351 X_ATTRIBUTE3,
1352 X_ATTRIBUTE4,
1353 X_ATTRIBUTE5,
1354 X_ATTRIBUTE6,
1355 X_ATTRIBUTE7,
1356 X_ATTRIBUTE8,
1357 X_ATTRIBUTE9,
1358 X_ATTRIBUTE10,
1359 X_ATTRIBUTE11,
1360 X_ATTRIBUTE12,
1361 X_ATTRIBUTE13,
1362 X_ATTRIBUTE14,
1363 X_ATTRIBUTE15,
1364 X_ATTRIBUTE16,
1365 X_ATTRIBUTE17,
1366 X_ATTRIBUTE18,
1367 X_ATTRIBUTE19,
1368 X_ATTRIBUTE20,
1369 X_MODE);
1370 end ADD_ROW;
1371
1372 end IGS_PE_PERSID_GROUP_PKG;