1 PACKAGE BODY igs_pe_person_types_pkg AS
2 /* $Header: IGSNI43B.pls 115.17 2003/06/11 06:05:56 rnirwani ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_pe_person_types%RowType;
5 new_references igs_pe_person_types%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_type_code IN VARCHAR2 DEFAULT NULL,
11 x_description IN VARCHAR2 DEFAULT NULL,
12 x_system_type IN VARCHAR2 DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_rank IN NUMBER DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21
22 /*************************************************************
23 Created By : sraj
24 Date Created By : 2000/13/05
25 Purpose : To set the column values before inserting.
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29 (reverse chronological order - newest change first)
30 ***************************************************************/
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_PE_PERSON_TYPES
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.person_type_code := x_person_type_code;
56 new_references.description := x_description;
57 new_references.system_type := x_system_type;
58 new_references.closed_ind := x_closed_ind;
59 new_references.rank := x_rank;
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END Set_Column_Values;
72
73
74
75 PROCEDURE check_duplicate_per_type IS
76 /*************************************************************
77 Created By : kumma
78 Date Created By : 15-MAY-2002
79 Purpose : To check if dup records exist After inserting.
80 Know limitations, enhancements or remarks
81 Change History
82 Who When What
83
84 (reverse chronological order - newest change first)
85 ***************************************************************/
86 CURSOR dup_system_type IS
87 SELECT
88 COUNT(1)
89 FROM
90 IGS_PE_PERSON_TYPES A,
91 IGS_PE_PERSON_TYPES B
92 WHERE
93 A.System_Type NOT IN ('USER_DEFINED', 'SS_ENROLL_STAFF')
94 AND A.System_Type = B.System_Type
95 AND A.ROWID <> B.ROWID
96 AND A.CLOSED_IND = 'N'
97 AND A.CLOSED_IND = B.CLOSED_IND;
98
99 l_count NUMBER(2);
100 BEGIN
101
102 OPEN dup_system_type;
103 FETCH dup_system_type INTO l_count;
104 IF l_count > 0 THEN
105 Fnd_Message.Set_Name('IGS','IGS_PE_ONE_PE_TY_SYS_TY');
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109 CLOSE dup_system_type;
110 END check_duplicate_per_type;
111
112
113
114
115 PROCEDURE AfterInsertPTC (
116 X_person_type_code IN VARCHAR2
117 ) AS
118 /*************************************************************
119 Created By : sraj
120 Date Created By : 2000/13/05
121 Purpose : To set the column values before inserting.
122 Know limitations, enhancements or remarks
123 Change History
124 Who When What
125 (reverse chronological order - newest change first)
126 ***************************************************************/
127
128 lvRowId VARCHAR2(30);
129 ln_Setup_Data_Element_Id NUMBER;
130 CURSOR de IS
131 SELECT DATA_ELEMENT FROM IGS_PE_DATA_ELEMENT;
132 BEGIN
133 FOR de_rec IN de LOOP
134 Igs_Pe_Stup_Data_Emt_Pkg.INSERT_ROW (
135 X_ROWID => lvRowid,
136 X_SETUP_DATA_ELEMENT_ID => ln_Setup_Data_Element_Id,
137 X_PERSON_TYPE_CODE => X_person_type_code,
138 X_DATA_ELEMENT => de_rec.DATA_ELEMENT,
139 X_VALUE => NULL,
140 X_REQUIRED_IND => 'N',
141 X_MODE => 'R'
142 );
143 END LOOP;
144 END AfterInsertPTC;
145
146 PROCEDURE BeforeDeletePTC AS
147 /*************************************************************
148 Created By : sraj
149 Date Created By : 2000/13/05
150 Purpose : To set the column values before inserting.
151 Know limitations, enhancements or remarks
152 Change History
153 Who When What
154 (reverse chronological order - newest change first)
155 ***************************************************************/
156
157 lvRowId VARCHAR2(30);
158 CURSOR C1 IS
159 SELECT ROWID
160 FROM IGS_PE_STUP_DATA_EMT
161 WHERE person_type_code = new_references.person_type_code;
162
163 BEGIN
164 FOR tlinfo IN C1 LOOP
165 Igs_Pe_Stup_Data_Emt_Pkg.DELETE_ROW (X_ROWID =>tlinfo.ROWID);
166 END LOOP;
167 END BeforeDeletePTC;
168
169
170 PROCEDURE BeforeInsertPTC AS
171 /*************************************************************
172 Created By : sraj
173 Date Created By : 2000/13/05
174 Purpose : To set the column values before inserting.
175 Know limitations, enhancements or remarks
176 Change History
177 Who When What
178 KNAG.IN 11-APR-2001 Included check for SS_ENROLL_STAFF
179 in procedures BeforeInsertPTC and
180 BeforeUpdatePTC
181 KUMMA 21-MAY-2002 Modified cursor to fetch only rows
182 which are having closed ind set to 'N'
183 (reverse chronological order - newest change first)
184 ***************************************************************/
185
186 CURSOR C2 IS
187 SELECT 'X'
188 FROM IGS_PE_PERSON_TYPES
189 WHERE System_Type = new_references.system_type
190 AND CLOSED_IND = 'N';
191 tlinfo c2%ROWTYPE;
192 BEGIN
193 IF (UPPER(new_references.system_type) <> 'USER_DEFINED' AND
194 UPPER(new_references.system_type) <> 'SS_ENROLL_STAFF') THEN
195 OPEN C2;
196 FETCH C2 INTO tlinfo;
197 IF (C2%found) THEN
198 Fnd_Message.Set_Name('IGS','IGS_PE_ONE_PE_TY_SYS_TY');
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 END IF;
202 CLOSE C2;
203 END IF;
204 END BeforeInsertPTC;
205
206 PROCEDURE BeforeUpdatePTC AS
207 /*************************************************************
208 Created By : sraj
209 Date Created By : 2000/13/05
210 Purpose : To set the column values before inserting.
211 Know limitations, enhancements or remarks
212 Change History
213 Who When What
214 KNAG.IN 11-APR-2001 Included check for SS_ENROLL_STAFF
215 in procedures BeforeInsertPTC and
216 BeforeUpdatePTC
217 (reverse chronological order - newest change first)
218 ***************************************************************/
219
220 CURSOR C3 IS
221 SELECT 'X'
222 FROM IGS_PE_PERSON_TYPES
223 WHERE System_Type = new_references.system_type
224 AND rowid <> l_rowid
225 -- AND Person_Type_Code <> new_references.Person_Type_Code
226 AND CLOSED_IND = 'N'
227 AND new_references.closed_ind = 'N';
228 tlinfo c3%ROWTYPE;
229 BEGIN
230 IF (UPPER(new_references.system_type) <> 'USER_DEFINED' AND
231 UPPER(new_references.system_type) <> 'SS_ENROLL_STAFF') THEN
232 OPEN C3;
233 FETCH C3 INTO tlinfo;
234 IF (C3%found) THEN
235 Fnd_Message.Set_Name('IGS','IGS_PE_ONE_PE_TY_SYS_TY');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 CLOSE C3;
240 END IF;
241 END BeforeUpdatePTC;
242
243
244 PROCEDURE Check_Constraints (
245 Column_Name IN VARCHAR2 DEFAULT NULL,
246 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
247 /*************************************************************
248 Created By : sraj
249 Date Created By : 2000/13/05
250 Purpose : To check the constraints
251 Know limitations, enhancements or remarks
252 Change History
253 Who When What
254
255 (reverse chronological order - newest change first)
256 ***************************************************************/
257
258 BEGIN
259
260 IF column_name IS NULL THEN
261 NULL;
262 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
263 new_references.closed_ind := column_value;
264 END IF;
265
266
267
268 -- The following code checks for check constraints on the Columns.
269 IF Upper(Column_Name) = 'CLOSED_IND' OR
270 Column_Name IS NULL THEN
271 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
272 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
273 IGS_GE_MSG_STACK.ADD;
274 App_Exception.Raise_Exception;
275 END IF;
276 END IF;
277
278
279 END Check_Constraints;
280
281 PROCEDURE Check_Child_Existance IS
282 /*************************************************************
283 Created By : sraj
284 Date Created By : 2000/13/05
285 Purpose : To Check if child records exist before deleting.
286 Know limitations, enhancements or remarks
287 Change History
288 Who When What
289
290 (reverse chronological order - newest change first)
291 ***************************************************************/
292
293 BEGIN
294
295 Igs_Pe_Typ_Instances_Pkg.Get_FK_Igs_Pe_Person_Types (
296 old_references.person_type_code
297 );
298
299 Igs_Pe_Usr_Arg_pkg.Get_FK_Igs_Pe_Person_Types (
300 old_references.person_type_code
301 );
302
303 Igs_Pe_Usr_Aval_pkg.Get_FK_Igs_Pe_Person_Types (
304 old_references.person_type_code
305 );
306
307 END Check_Child_Existance;
308
309 FUNCTION Get_PK_For_Validation (
310 x_person_type_code IN VARCHAR2
311 ) RETURN BOOLEAN AS
312
313 /*************************************************************
314 Created By : sraj
315 Date Created By : 2000/13/05
316 Purpose : To check if dup records exist before inserting.
317 Know limitations, enhancements or remarks
318 Change History
319 Who When What
320 pkpatel 8-JUL-2002 Bug No: 2389552
321 The for update nowait was removed since records can not be deleted from the form
322 (reverse chronological order - newest change first)
323 ***************************************************************/
324
325 CURSOR cur_rowid IS
326 SELECT rowid
327 FROM igs_pe_person_types
328 WHERE UPPER(person_type_code) = UPPER(x_person_type_code);
329
330 lv_rowid cur_rowid%RowType;
331
332 BEGIN
333
334 Open cur_rowid;
335 Fetch cur_rowid INTO lv_rowid;
336 IF (cur_rowid%FOUND) THEN
337 Close cur_rowid;
338 Return(TRUE);
339 ELSE
340 Close cur_rowid;
341 Return(FALSE);
342 END IF;
343 END Get_PK_For_Validation;
344
345 PROCEDURE Before_DML (
346 p_action IN VARCHAR2,
347 x_rowid IN VARCHAR2 DEFAULT NULL,
348 x_person_type_code IN VARCHAR2 DEFAULT NULL,
349 x_description IN VARCHAR2 DEFAULT NULL,
350 x_system_type IN VARCHAR2 DEFAULT NULL,
351 x_closed_ind IN VARCHAR2 DEFAULT NULL,
352 x_rank IN NUMBER DEFAULT NULL,
353 x_creation_date IN DATE DEFAULT NULL,
354 x_created_by IN NUMBER DEFAULT NULL,
355 x_last_update_date IN DATE DEFAULT NULL,
356 x_last_updated_by IN NUMBER DEFAULT NULL,
357 x_last_update_login IN NUMBER DEFAULT NULL
358 ) AS
359 /*************************************************************
360 Created By : sraj
361 Date Created By : 2000/13/05
362 Purpose : To validate the fields before doing the DML operation.
363 Know limitations, enhancements or remarks
364 Change History
365 Who When What
366 kumma 21-MAY-2002 Commented the call to BeforeInsertPTC
367 and to --BeforeUpdatePTC as the logic have
368 been moved to post_forms_commit, Bug 2379779
369 (reverse chronological order - newest change first)
370 ***************************************************************/
371
372 BEGIN
373
374 Set_Column_Values (
375 p_action,
376 x_rowid,
377 x_person_type_code,
378 x_description,
379 x_system_type,
380 x_closed_ind,
381 x_rank,
382 x_creation_date,
383 x_created_by,
384 x_last_update_date,
385 x_last_updated_by,
386 x_last_update_login
387 );
388
389 IF (p_action = 'INSERT') THEN
390 -- Call all the procedures related to Before Insert.
391 IF Get_Pk_For_Validation(
392 new_references.person_type_code) THEN
393 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
394 IGS_GE_MSG_STACK.ADD;
395 App_Exception.Raise_Exception;
396 END IF;
397 Check_Constraints;
398 --BeforeInsertPTC;
399 ELSIF (p_action = 'UPDATE') THEN
400 -- Call all the procedures related to Before Update.
404 -- Call all the procedures related to Before Delete.
401 --BeforeUpdatePTC;
402 Check_Constraints;
403 ELSIF (p_action = 'DELETE') THEN
405 Null;
406 Check_Child_Existance;
407 ELSIF (p_action = 'VALIDATE_INSERT') THEN
408 -- Call all the procedures related to Before Insert.
409 IF Get_PK_For_Validation (
410 new_references.person_type_code) THEN
411 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
412 IGS_GE_MSG_STACK.ADD;
413 App_Exception.Raise_Exception;
414 END IF;
415 Check_Constraints;
416 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
417 Check_Constraints;
418 ELSIF (p_action = 'VALIDATE_DELETE') THEN
419 Check_Child_Existance;
420 BeforeDeletePTC;
421 END IF;
422
423 END Before_DML;
424
425 PROCEDURE After_DML (
426 p_action IN VARCHAR2,
427 x_rowid IN VARCHAR2
428 ) IS
429 /*************************************************************
430 Created By : sraj
431 Date Created By : 2000/13/05
432 Purpose : To validate the fields after doing the DML operation.
433 Know limitations, enhancements or remarks
434 Change History
435 Who When What
436 kumma 21-MAY-2002 Added call to check_duplicate_per_type, Bug 2379779
437 pkpatel 8-JUL-2002 Bug No: 2389552
438 Removed the call to check_duplicate_per_type. This logic is now moved to post_forms_commit of the form.
439 (reverse chronological order - newest change first)
440 ***************************************************************/
441
442 BEGIN
443
444 l_rowid := x_rowid;
445
446 IF (p_action = 'INSERT') THEN
447 -- Call all the procedures related to After Insert.
448 AfterInsertPTC(new_references.Person_Type_Code);
449 Null;
450 ELSIF (p_action = 'UPDATE') THEN
451 -- Call all the procedures related to After Update.
452 Null;
453 ELSIF (p_action = 'DELETE') THEN
454 -- Call all the procedures related to After Delete.
455 Null;
456 END IF;
457
458 END After_DML;
459
460
461 procedure INSERT_ROW (
462 X_ROWID in out NOCOPY VARCHAR2,
463 x_PERSON_TYPE_CODE IN VARCHAR2,
464 x_DESCRIPTION IN VARCHAR2,
465 x_SYSTEM_TYPE IN VARCHAR2,
466 x_CLOSED_IND IN VARCHAR2,
467 x_RANK IN NUMBER DEFAULT NULL,
468 X_MODE in VARCHAR2 default 'R'
469 ) AS
470 /*************************************************************
471 Created By : sraj
472 Date Created By : 2000/13/05
473 Purpose : To insert a record.
474 Know limitations, enhancements or remarks
475 Change History
476 Who When What
477
478 (reverse chronological order - newest change first)
479 ***************************************************************/
480
481 cursor C is select ROWID from IGS_PE_PERSON_TYPES
482 where PERSON_TYPE_CODE= X_PERSON_TYPE_CODE
483 ;
484 X_LAST_UPDATE_DATE DATE ;
485 X_LAST_UPDATED_BY NUMBER ;
486 X_LAST_UPDATE_LOGIN NUMBER ;
487 begin
488 X_LAST_UPDATE_DATE := SYSDATE;
489 if(X_MODE = 'I') then
490 X_LAST_UPDATED_BY := 1;
491 X_LAST_UPDATE_LOGIN := 0;
492 elsif (X_MODE = 'R') then
493 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
494 if X_LAST_UPDATED_BY is NULL then
495 X_LAST_UPDATED_BY := -1;
496 end if;
497 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
498 if X_LAST_UPDATE_LOGIN is NULL then
499 X_LAST_UPDATE_LOGIN := -1;
500 end if;
501 else
502 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
503 IGS_GE_MSG_STACK.ADD;
504 app_exception.raise_exception;
505 end if;
506 Before_DML(
507 p_action=>'INSERT',
508 x_rowid=>X_ROWID,
509 x_person_type_code=>X_PERSON_TYPE_CODE,
510 x_description=>X_DESCRIPTION,
511 x_system_type=>X_SYSTEM_TYPE,
512 x_closed_ind=>X_CLOSED_IND,
513 x_rank=>X_RANK,
514 x_creation_date=>X_LAST_UPDATE_DATE,
515 x_created_by=>X_LAST_UPDATED_BY,
516 x_last_update_date=>X_LAST_UPDATE_DATE,
517 x_last_updated_by=>X_LAST_UPDATED_BY,
518 x_last_update_login=>X_LAST_UPDATE_LOGIN);
519 insert into IGS_PE_PERSON_TYPES (
520 PERSON_TYPE_CODE
521 ,DESCRIPTION
522 ,SYSTEM_TYPE
523 ,CLOSED_IND
524 ,RANK
525 ,CREATION_DATE
526 ,CREATED_BY
527 ,LAST_UPDATE_DATE
528 ,LAST_UPDATED_BY
529 ,LAST_UPDATE_LOGIN
530 ) values (
531 NEW_REFERENCES.PERSON_TYPE_CODE
532 ,NEW_REFERENCES.DESCRIPTION
533 ,NEW_REFERENCES.SYSTEM_TYPE
534 ,NEW_REFERENCES.CLOSED_IND
535 ,NEW_REFERENCES.RANK
536 ,X_LAST_UPDATE_DATE
537 ,X_LAST_UPDATED_BY
538 ,X_LAST_UPDATE_DATE
539 ,X_LAST_UPDATED_BY
540 ,X_LAST_UPDATE_LOGIN
541 );
542 open c;
543 fetch c into X_ROWID;
544 if (c%notfound) then
548 close c;
545 close c;
546 raise no_data_found;
547 end if;
549 After_DML (
550 p_action => 'INSERT' ,
551 x_rowid => X_ROWID );
552 end INSERT_ROW;
553 procedure LOCK_ROW (
554 X_ROWID in VARCHAR2,
555 x_PERSON_TYPE_CODE IN VARCHAR2,
556 x_DESCRIPTION IN VARCHAR2,
557 x_SYSTEM_TYPE IN VARCHAR2,
558 x_CLOSED_IND IN VARCHAR2,
559 x_RANK IN NUMBER DEFAULT NULL ) AS
560 /*************************************************************
561 Created By : sraj
562 Date Created By : 2000/13/05
563 Purpose : To lock a record.
564 Know limitations, enhancements or remarks
565 Change History
566 Who When What
567
568 (reverse chronological order - newest change first)
569 ***************************************************************/
570
571 cursor c1 is select
572 DESCRIPTION
573 , SYSTEM_TYPE
574 , CLOSED_IND
575 from IGS_PE_PERSON_TYPES
576 where ROWID = X_ROWID
577 for update nowait;
578 tlinfo c1%rowtype;
579 begin
580 open c1;
581 fetch c1 into tlinfo;
582 if (c1%notfound) then
583 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
584 IGS_GE_MSG_STACK.ADD;
585 close c1;
586 app_exception.raise_exception;
587 return;
588 end if;
589 close c1;
590 if ( ( tlinfo.DESCRIPTION = X_DESCRIPTION)
591 AND (tlinfo.SYSTEM_TYPE = X_SYSTEM_TYPE)
592 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
593 ) then
594 null;
595 else
596 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
597 IGS_GE_MSG_STACK.ADD;
598 app_exception.raise_exception;
599 end if;
600 return;
601 end LOCK_ROW;
602 Procedure UPDATE_ROW (
603 X_ROWID in VARCHAR2,
604 x_PERSON_TYPE_CODE IN VARCHAR2,
605 x_DESCRIPTION IN VARCHAR2,
606 x_SYSTEM_TYPE IN VARCHAR2,
607 x_CLOSED_IND IN VARCHAR2,
608 x_RANK IN NUMBER DEFAULT NULL,
609 X_MODE in VARCHAR2 default 'R'
610 ) AS
611 /*************************************************************
612 Created By : sraj
613 Date Created By : 2000/13/05
614 Purpose : To update a record.
615 Know limitations, enhancements or remarks
616 Change History
617 Who When What
618
619 (reverse chronological order - newest change first)
620 ***************************************************************/
621
622 X_LAST_UPDATE_DATE DATE ;
623 X_LAST_UPDATED_BY NUMBER ;
624 X_LAST_UPDATE_LOGIN NUMBER ;
625 begin
626 X_LAST_UPDATE_DATE := SYSDATE;
627 if(X_MODE = 'I') then
628 X_LAST_UPDATED_BY := 1;
629 X_LAST_UPDATE_LOGIN := 0;
630 elsif (X_MODE = 'R') then
631 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
632 if X_LAST_UPDATED_BY is NULL then
633 X_LAST_UPDATED_BY := -1;
634 end if;
635 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
636 if X_LAST_UPDATE_LOGIN is NULL then
637 X_LAST_UPDATE_LOGIN := -1;
638 end if;
639 else
640 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
641 IGS_GE_MSG_STACK.ADD;
642 app_exception.raise_exception;
643 end if;
644
645 Before_DML(
646 p_action=>'UPDATE',
647 x_rowid=>X_ROWID,
648 x_person_type_code=>X_PERSON_TYPE_CODE,
649 x_description=>X_DESCRIPTION,
650 x_system_type=>X_SYSTEM_TYPE,
651 x_closed_ind=>X_CLOSED_IND,
652 x_rank=>X_RANK,
653 x_creation_date=>X_LAST_UPDATE_DATE,
654 x_created_by=>X_LAST_UPDATED_BY,
655 x_last_update_date=>X_LAST_UPDATE_DATE,
656 x_last_updated_by=>X_LAST_UPDATED_BY,
657 x_last_update_login=>X_LAST_UPDATE_LOGIN);
658 update IGS_PE_PERSON_TYPES set
659 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
660 SYSTEM_TYPE = NEW_REFERENCES.SYSTEM_TYPE,
661 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
662 RANK = NEW_REFERENCES.RANK,
663 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
664 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
665 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
666 where ROWID = X_ROWID;
667 if (sql%notfound) then
668 raise no_data_found;
669 end if;
670
671 After_DML (
672 p_action => 'UPDATE' ,
673 x_rowid => X_ROWID
674 );
675 end UPDATE_ROW;
676 procedure ADD_ROW (
677 X_ROWID in out NOCOPY VARCHAR2,
678 x_PERSON_TYPE_CODE IN VARCHAR2,
679 x_DESCRIPTION IN VARCHAR2,
680 x_SYSTEM_TYPE IN VARCHAR2,
681 x_CLOSED_IND IN VARCHAR2,
682 x_RANK IN NUMBER DEFAULT NULL,
683 X_MODE in VARCHAR2 default 'R'
684 ) AS
685 /*************************************************************
686 Created By : sraj
687 Date Created By : 2000/13/05
688 Purpose : To insert/update a record.
689 Know limitations, enhancements or remarks
690 Change History
691 Who When What
692
693 (reverse chronological order - newest change first)
694 ***************************************************************/
695
696 cursor c1 is select ROWID from IGS_PE_PERSON_TYPES
697 where PERSON_TYPE_CODE= X_PERSON_TYPE_CODE
698 ;
699 begin
700 open c1;
701 fetch c1 into X_ROWID;
702 if (c1%notfound) then
703 close c1;
704 INSERT_ROW (
705 X_ROWID,
706 X_PERSON_TYPE_CODE,
707 X_DESCRIPTION,
708 X_SYSTEM_TYPE,
709 X_CLOSED_IND,
710 X_RANK,
711 X_MODE );
712 return;
713 end if;
714 close c1;
715 UPDATE_ROW (
716 X_ROWID,
717 X_PERSON_TYPE_CODE,
718 X_DESCRIPTION,
719 X_SYSTEM_TYPE,
720 X_CLOSED_IND,
721 X_RANK,
722 X_MODE );
723 end ADD_ROW;
724
725 procedure DELETE_ROW (
726 X_ROWID in VARCHAR2
727 ) AS
728 /*************************************************************
729 Created By : sraj
730 Date Created By : 2000/13/05
731 Purpose : To delete a record.
732 Know limitations, enhancements or remarks
733 Change History
734 Who When What
735
736 (reverse chronological order - newest change first)
737 ***************************************************************/
738
739 begin
740 Before_DML (
741 p_action => 'DELETE',
742 x_rowid => X_ROWID
743 );
744 delete from IGS_PE_PERSON_TYPES
745 where ROWID = X_ROWID;
746 if (sql%notfound) then
747 raise no_data_found;
748 end if;
749 After_DML (
750 p_action => 'DELETE',
751 x_rowid => X_ROWID
752 );
753 end DELETE_ROW;
754 END igs_pe_person_types_pkg;
[Home] [Help]