1 package body IGS_PE_PERSON_ID_TYP_PKG AS
2 /* $Header: IGSNI25B.pls 120.1 2006/01/25 09:20:33 skpandey noship $ */
3
4 ------------------------------------------------------------------
5 -- Change History
6 --
7 -- Bug ID : 2000408
8 -- who when what
9 -- CDCRUZ Sep 24,2002 New Col added for
10 -- Person DLD / FORMAT_MASK
11 ------------------------------------------------------------------
12
13 /******************************************************
14 Created By : nigupta
15 Date Created By : 11-MAY-2000
16 Purpose : To create TBH Body
17 Know limitations, enhancements or remarks : None
18 Change History
19 Who When What
20 sraj 17-MAY-2000 Two columns have been added to this table.
21 (reverse chronological order - newest change first)
22 ********************************************************/
23
24 l_rowid VARCHAR2(25);
25 old_references IGS_PE_PERSON_ID_TYP%RowType;
26 new_references IGS_PE_PERSON_ID_TYP%RowType;
27
28 PROCEDURE Set_Column_Values (
29 p_action IN VARCHAR2,
30 x_rowid IN VARCHAR2 ,
31 x_person_id_type IN VARCHAR2 ,
32 x_description IN VARCHAR2 ,
33 x_s_person_id_type IN VARCHAR2 ,
34 x_institution_cd IN VARCHAR2 ,
35 x_preferred_ind IN VARCHAR2 ,
36 x_unique_ind IN VARCHAR2 ,
37 X_FORMAT_MASK IN VARCHAR2 ,
38 X_REGION_IND IN VARCHAR2,
39 x_creation_date IN DATE ,
40 x_created_by IN NUMBER ,
41 x_last_update_date IN DATE ,
42 x_last_updated_by IN NUMBER ,
43 x_last_update_login IN NUMBER ,
44 x_closed_ind IN VARCHAR2
45 ) AS
46
47 /*************************************************************
48 Created By :
49 Date Created By :
50 Purpose :
51 Know limitations, enhancements or remarks
52 Change History
53 Who When What
54 sraj 17-MAY-2000 Two columns have been added to this table.
55 (reverse chronological order - newest change first)
56 ***************************************************************/
57
58 CURSOR cur_old_ref_values IS
59 SELECT *
60 FROM IGS_PE_PERSON_ID_TYP
61 WHERE rowid = x_rowid;
62
63 BEGIN
64
65 l_rowid := x_rowid;
66
67 -- Code for setting the Old and New Reference Values.
68 -- Populate Old Values.
69 Open cur_old_ref_values;
70 Fetch cur_old_ref_values INTO old_references;
71 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
72 Close cur_old_ref_values;
73 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
74 IGS_GE_MSG_STACK.ADD;
75 App_Exception.Raise_Exception;
76 Return;
77 END IF;
78 Close cur_old_ref_values;
79
80 -- Populate New Values.
81 new_references.person_id_type := x_person_id_type;
82 new_references.description := x_description;
83 new_references.s_person_id_type := x_s_person_id_type;
84 new_references.institution_cd := x_institution_cd;
85 new_references.preferred_ind := x_preferred_ind;
86 new_references.unique_ind := x_unique_ind;
87 new_references.format_mask := x_format_mask ;
88 new_references.region_ind := x_region_ind;
89 new_references.closed_ind := x_closed_ind;
90
91 IF (p_action = 'UPDATE') THEN
92 new_references.creation_date := old_references.creation_date;
93 new_references.created_by := old_references.created_by;
94 ELSE
95 new_references.creation_date := x_creation_date;
96 new_references.created_by := x_created_by;
97 END IF;
98 new_references.last_update_date := x_last_update_date;
99 new_references.last_updated_by := x_last_updated_by;
100 new_references.last_update_login := x_last_update_login;
101
102 END Set_Column_Values;
103
104 PROCEDURE BeforeRowInsertUpdate1(
105 p_inserting IN BOOLEAN ,
106 p_updating IN BOOLEAN ,
107 p_deleting IN BOOLEAN
108 ) AS
109
110 /******************************************************
111 Created By : nigupta
112 Date Created By : 11-MAY-2000
113 Purpose : Before row insert and update
114 Know limitations, enhancements or remarks : None
115 Change History
116 Who When What
117
118
119 (reverse chronological order - newest change first)
120 ********************************************************/
121
122 v_message_name varchar2(30);
123 BEGIN
124 -- Validate IGS_PE_PERSON ID TYPE.
125 -- IGS_OR_INSTITUTION closed indicator.
126 IF new_references.institution_cd IS NOT NULL AND
127 (NVL(old_references.institution_cd, 'NULL') <> new_references.institution_cd) THEN
128 IF IGS_EN_VAL_PIT.enrp_val_pit_inst_cd (
129 new_references.institution_cd,
130 v_message_name) = FALSE THEN
131 Fnd_Message.Set_Name('IGS', v_message_name);
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136 END BeforeRowInsertUpdate1;
137
138 PROCEDURE Check_Constraints (
139 Column_Name IN VARCHAR2 ,
140 Column_Value IN VARCHAR2 ) AS
141 /*************************************************************
142 Created By : sraj
143 Date Created By : 17-MAY-2000
144 Purpose :
145 Know limitations, enhancements or remarks
146 Change History
147 Who When What
148 sraj 17-MAY-2000 Two columns have been added to this table.
149 pkpatel 19-JUL-2002 Bug No: 2384824
150 Removed the upper check for Institution Code
151 (reverse chronological order - newest change first)
152 ***************************************************************/
153
154 BEGIN
155
156 IF column_name IS NULL THEN
157 NULL;
158 ELSIF UPPER(column_name) = 'PREFERRED_IND' THEN
159 new_references.preferred_ind := column_value;
160 ELSIF UPPER(column_name) = 'UNIQUE_IND' THEN
161 new_references.unique_ind := column_value;
162 ELSIF UPPER(column_name) = 'REGION_IND' THEN
163 new_references.region_ind := column_value;
164 ELSIF UPPER(column_name) = 'PERSON_ID_TYPE' THEN
165 new_references.person_id_type := column_value;
166 ELSIF UPPER(column_name) = 'S_PERSON_ID_TYPE' THEN
167 new_references.s_person_id_type := column_value;
168 ELSIF UPPER(column_name) = 'INSTITUTION_CD' THEN
169 new_references.institution_cd := column_value;
170 NULL;
171 END IF;
172
173 -- The following code checks for check constraints on the Columns.
174 IF Upper(Column_Name) = 'PREFERRED_IND' OR
175 Column_Name IS NULL THEN
176 IF NOT (new_references.preferred_ind IN ('Y', 'N')) THEN
177 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
178 IGS_GE_MSG_STACK.ADD;
179 App_Exception.Raise_Exception;
180 END IF;
181 END IF;
182
183 -- The following code checks for check constraints on the Columns.
184 IF Upper(Column_Name) = 'UNIQUE_IND' OR
185 Column_Name IS NULL THEN
186 IF NOT (new_references.unique_ind IN ('N', 'Y')) THEN
187 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192
193
194 IF Upper(Column_Name) = 'REGION_IND' OR
195 Column_Name IS NULL THEN
196 IF NOT (new_references.region_ind IN ('N', 'Y')) THEN
197 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END IF;
201 END IF;
202
203 IF UPPER(Column_Name) = 'PERSON_ID_TYPE' OR
204 Column_Name IS NULL THEN
205 IF new_references.PERSON_ID_TYPE <> UPPER(new_references.person_id_type) THEN
206 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END IF;
210 END IF;
211
212 IF UPPER(Column_Name) = 'S_PERSON_ID_TYPE' OR
213 Column_Name IS NULL THEN
214 IF new_references.S_PERSON_ID_TYPE <> UPPER(new_references.s_person_id_type) THEN
215 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END IF;
219 END IF;
220
221
222 END Check_Constraints;
223
224 PROCEDURE Check_Parent_Existance AS
225
226 /******************************************************
227 Created By : nigupta
228 Date Created By : 11-MAY-2000
229 Purpose : To check parent existance
230 Know limitations, enhancements or remarks : None
231 Change History
232 Who When What
233
234
235 (reverse chronological order - newest change first)
236 ********************************************************/
237
238 BEGIN
239 IF (((old_references.institution_cd = new_references.institution_cd)) OR
240 ((new_references.institution_cd IS NULL))) THEN
241 NULL;
242 ELSE
243
244 IF NOT IGS_OR_INSTITUTION_PKG.Get_PK_For_Validation (
245 new_references.institution_cd) THEN
246 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
247 IGS_GE_MSG_STACK.ADD;
248 IGS_GE_MSG_STACK.ADD;
249 App_Exception.Raise_Exception;
250 END IF;
251 END IF;
252 IF (((old_references.s_person_id_type = new_references.s_person_id_type)) OR
253 ((new_references.s_person_id_type IS NULL))) THEN
254 NULL;
255 ELSE
256 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
257 'PERSON_ID_TYPE',new_references.s_person_id_type) THEN
258 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
259 IGS_GE_MSG_STACK.ADD;
260 App_Exception.Raise_Exception;
261 END IF;
262
263 END IF;
264 END Check_Parent_Existance;
265
266 FUNCTION Get_PK_For_Validation (
267 x_person_id_type IN VARCHAR2
268 ) RETURN BOOLEAN AS
269
270 /******************************************************
271 Created By : nigupta
272 Date Created By : 11-MAY-2000
273 Purpose : To enforce primary key validations
274 Know limitations, enhancements or remarks : None
275 Change History
276 Who When What
277 pkpatel 19-JUL-2002 Bug No: 2384824
278 Modified the logic to lock the parent record only while the child records are not present for that person_id_type
279 If the childs are present then the person_id_type can not be deleted since the child alternate person I can only
280 be end dated. Hence no need to lock the records.
281 (reverse chronological order - newest change first)
282 ********************************************************/
283 --Cursor to check the existence of parent
284 CURSOR cur_rowid IS
285 SELECT rowid
286 FROM IGS_PE_PERSON_ID_TYP
287 WHERE person_id_type = x_person_id_type;
288
289 lv_rowid cur_rowid%ROWTYPE;
290
291 BEGIN
292 OPEN cur_rowid;
293 FETCH cur_rowid INTO lv_rowid;
294 IF (cur_rowid%FOUND) THEN
295 CLOSE cur_rowid;
296 RETURN (TRUE);
297 ELSE
298 CLOSE cur_rowid;
299 RETURN (FALSE);
300 END IF;
301 END Get_PK_For_Validation;
302
303 FUNCTION Get_PID_Type_Validation (
304 x_person_id_type IN VARCHAR2
305 ) RETURN BOOLEAN AS
306
307 /******************************************************
308 Created By : ssaleem
309 Date Created By : 17-SEP-2004
310 Purpose : To enforce primary key validations with closed indicator
311 Know limitations, enhancements or remarks : This function is created
312 after adding closed indicator in IGS_PE_PERSON_ID_TYP
313 Change History
314 Who When What
315 (reverse chronological order - newest change first)
316 ********************************************************/
317 --Cursor to check the existence of parent
318 CURSOR cur_rowid IS
319 SELECT rowid
320 FROM IGS_PE_PERSON_ID_TYP
321 WHERE person_id_type = x_person_id_type AND
322 CLOSED_IND = 'N';
323
324 lv_rowid cur_rowid%ROWTYPE;
325
326 BEGIN
327 OPEN cur_rowid;
328 FETCH cur_rowid INTO lv_rowid;
329 IF (cur_rowid%FOUND) THEN
330 CLOSE cur_rowid;
331 RETURN (TRUE);
332 ELSE
333 CLOSE cur_rowid;
334 RETURN (FALSE);
335 END IF;
336 END Get_PID_Type_Validation;
337
338
339
340 PROCEDURE GET_FK_IGS_OR_INSTITUTION (
341 x_institution_cd IN VARCHAR2
342 ) AS
343
344 /******************************************************
345 Created By : nigupta
346 Date Created By : 11-MAY-2000
347 Purpose : To enforce foriegn key validations
348 Know limitations, enhancements or remarks : None
349 Change History
350 Who When What
351
352
353 (reverse chronological order - newest change first)
354 ********************************************************/
355
356 CURSOR cur_rowid IS
357 SELECT rowid
358 FROM IGS_PE_PERSON_ID_TYP
359 WHERE institution_cd = x_institution_cd ;
360 lv_rowid cur_rowid%RowType;
361 BEGIN
362 Open cur_rowid;
363 Fetch cur_rowid INTO lv_rowid;
364 IF (cur_rowid%FOUND) THEN
365 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PIT_INS_FK');
366 IGS_GE_MSG_STACK.ADD;
367 Close cur_rowid;
368 App_Exception.Raise_Exception;
369 Return;
370 END IF;
371 Close cur_rowid;
372 END GET_FK_IGS_OR_INSTITUTION;
373
374 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
375 x_s_person_id_type IN VARCHAR2
376 ) AS
377
378 /******************************************************
379 Created By : nigupta
380 Date Created By : 11-MAY-2000
381 Purpose : for lookup views
382 Know limitations, enhancements or remarks : None
383 Change History
384 Who When What
385 skpandey 24-JAN-2006 Bug#3686538: Stubbed as a part of query optimization
386 (reverse chronological order - newest change first)
387 ********************************************************/
388 BEGIN
389 NULL;
390 END GET_FK_IGS_LOOKUPS_VIEW;
391
392 PROCEDURE Before_DML (
393 p_action IN VARCHAR2,
394 x_rowid IN VARCHAR2 ,
395 x_person_id_type IN VARCHAR2 ,
396 x_description IN VARCHAR2 ,
397 x_s_person_id_type IN VARCHAR2 ,
398 x_institution_cd IN VARCHAR2 ,
399 x_preferred_ind IN VARCHAR2 ,
400 x_unique_ind IN VARCHAR2 ,
401 X_FORMAT_MASK IN VARCHAR2 ,
402 X_REGION_IND IN VARCHAR2,
403 x_closed_ind IN VARCHAR2,
404 x_creation_date IN DATE ,
405 x_created_by IN NUMBER ,
406 x_last_update_date IN DATE ,
407 x_last_updated_by IN NUMBER ,
408 x_last_update_login IN NUMBER
409 ) AS
410 /*************************************************************
411 Created By : sraj
412 Date Created By : 17-MAY-2000
413 Purpose :
414 Know limitations, enhancements or remarks
415 Change History
416 Who When What
417 sraj 17-MAY-2000 Two columns have been added to this table.
418 (reverse chronological order - newest change first)
419 ***************************************************************/
420
421 BEGIN
422
423 Set_Column_Values (
424 p_action,
425 x_rowid,
426 x_person_id_type,
427 x_description,
428 x_s_person_id_type,
429 x_institution_cd,
430 x_preferred_ind,
431 x_unique_ind,
432 x_format_mask,
433 x_region_ind,
434 x_creation_date,
435 x_created_by,
436 x_last_update_date,
437 x_last_updated_by,
438 x_last_update_login,
439 x_closed_ind
440 );
441
442 IF (p_action = 'INSERT') THEN
443 -- Call all the procedures related to Before Insert.
444 BeforeRowInsertUpdate1 ( p_inserting => TRUE,
445 p_updating => FALSE ,
446 p_deleting => FALSE);
447 IF Get_PK_For_Validation (
448 new_references.person_id_type ) THEN
449 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
450 IGS_GE_MSG_STACK.ADD;
451 App_Exception.Raise_Exception;
452 END IF;
453
454 Check_Constraints; -- if procedure present
455 Check_Parent_Existance; -- if procedure present
456
457 ELSIF (p_action = 'UPDATE') THEN
458 -- Call all the procedures related to Before Update.
459 BeforeRowInsertUpdate1 ( p_updating => TRUE,
460 p_inserting => FALSE ,
461 p_deleting => FALSE);
462 Check_Constraints; -- if procedure present
463 Check_Parent_Existance; -- if procedure present
464
465 ELSIF (p_action = 'VALIDATE_INSERT') THEN
466 IF Get_PK_For_Validation (
467 new_references.person_id_type ) THEN
468 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
469 IGS_GE_MSG_STACK.ADD;
470 App_Exception.Raise_Exception;
471 END IF;
472
473 Check_Constraints; -- if procedure present
474
475 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
476 Check_Constraints; -- if procedure present
477 END IF;
478
479 END Before_DML;
480
481 PROCEDURE After_DML (
482 p_action IN VARCHAR2,
483 x_rowid IN VARCHAR2
484 ) AS
485
486 /******************************************************
487 Created By : nigupta
488 Date Created By : 11-MAY-2000
489 Purpose : To check after DML
490 Know limitations, enhancements or remarks : None
491 Change History
492 Who When What
493
494
495 (reverse chronological order - newest change first)
496 ********************************************************/
497
498 BEGIN
499 l_rowid := x_rowid;
500 IF (p_action = 'INSERT') THEN
501 -- Call all the procedures related to After Insert.
502 Null;
503 ELSIF (p_action = 'UPDATE') THEN
504 -- Call all the procedures related to After Update.
505 Null;
506 END IF;
507 END After_DML;
508
509 procedure INSERT_ROW (
510 X_ROWID in out NOCOPY VARCHAR2,
511 x_PERSON_ID_TYPE IN VARCHAR2,
512 x_DESCRIPTION IN VARCHAR2,
513 x_S_PERSON_ID_TYPE IN VARCHAR2,
514 x_INSTITUTION_CD IN VARCHAR2,
515 x_PREFERRED_IND IN VARCHAR2,
516 x_UNIQUE_IND IN VARCHAR2,
517 X_FORMAT_MASK IN VARCHAR2 ,
518 X_REGION_IND IN VARCHAR2,
519 X_MODE in VARCHAR2,
520 X_CLOSED_IND IN VARCHAR2
521 ) AS
522 /*************************************************************
523 Created By : sraj
524 Date Created By : 17-MAY-2000
525 Purpose :
526 Know limitations, enhancements or remarks
527 Change History
528 Who When What
529 sraj 17-MAY-2000 Two columns have been added to this table.
530 (reverse chronological order - newest change first)
531 ***************************************************************/
532
533 cursor C is select ROWID from IGS_PE_PERSON_ID_TYP
534 where PERSON_ID_TYPE= X_PERSON_ID_TYPE
535 ;
536 X_LAST_UPDATE_DATE DATE ;
537 X_LAST_UPDATED_BY NUMBER ;
538 X_LAST_UPDATE_LOGIN NUMBER ;
539 begin
540 X_LAST_UPDATE_DATE := SYSDATE;
541 if(X_MODE = 'I') then
542 X_LAST_UPDATED_BY := 1;
543 X_LAST_UPDATE_LOGIN := 0;
544 elsif (X_MODE = 'R') then
545 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
546 if X_LAST_UPDATED_BY is NULL then
547 X_LAST_UPDATED_BY := -1;
548 end if;
549 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
550 if X_LAST_UPDATE_LOGIN is NULL then
551 X_LAST_UPDATE_LOGIN := -1;
552 end if;
553 else
554 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
555 IGS_GE_MSG_STACK.ADD;
556 app_exception.raise_exception;
557 end if;
558 Before_DML(
559 p_action=>'INSERT',
560 x_rowid=>X_ROWID,
561 x_person_id_type=>X_PERSON_ID_TYPE,
562 x_description=>X_DESCRIPTION,
563 x_s_person_id_type=>X_S_PERSON_ID_TYPE,
564 x_institution_cd=>X_INSTITUTION_CD,
565 x_preferred_ind=>X_PREFERRED_IND,
566 x_unique_ind=>X_UNIQUE_IND,
567 x_format_mask=>X_format_mask,
568 x_region_ind => X_REGION_IND,
569 x_closed_ind=> X_CLOSED_IND,
570 x_creation_date=>X_LAST_UPDATE_DATE,
571 x_created_by=>X_LAST_UPDATED_BY,
572 x_last_update_date=>X_LAST_UPDATE_DATE,
573 x_last_updated_by=>X_LAST_UPDATED_BY,
574 x_last_update_login=>X_LAST_UPDATE_LOGIN
575 );
576 insert into IGS_PE_PERSON_ID_TYP (
577 PERSON_ID_TYPE
578 ,DESCRIPTION
579 ,S_PERSON_ID_TYPE
580 ,INSTITUTION_CD
581 ,PREFERRED_IND
582 ,UNIQUE_IND
583 ,FORMAT_MASK
584 ,REGION_IND
585 ,CREATION_DATE
586 ,CREATED_BY
587 ,LAST_UPDATE_DATE
588 ,LAST_UPDATED_BY
589 ,LAST_UPDATE_LOGIN
590 ,CLOSED_IND
591 ) values (
592 NEW_REFERENCES.PERSON_ID_TYPE
593 ,NEW_REFERENCES.DESCRIPTION
594 ,NEW_REFERENCES.S_PERSON_ID_TYPE
595 ,NEW_REFERENCES.INSTITUTION_CD
596 ,NEW_REFERENCES.PREFERRED_IND
597 ,NEW_REFERENCES.UNIQUE_IND
598 ,NEW_REFERENCES.FORMAT_MASK
599 ,NEW_REFERENCES.REGION_IND
600 ,X_LAST_UPDATE_DATE
601 ,X_LAST_UPDATED_BY
602 ,X_LAST_UPDATE_DATE
603 ,X_LAST_UPDATED_BY
604 ,X_LAST_UPDATE_LOGIN
605 ,NEW_REFERENCES.CLOSED_IND
606 );
607 open c;
608 fetch c into X_ROWID;
609 if (c%notfound) then
610 close c;
611 raise no_data_found;
612 end if;
613 close c;
614 After_DML (
615 p_action => 'INSERT' ,
616 x_rowid => X_ROWID );
617 end INSERT_ROW;
618 procedure LOCK_ROW (
619 X_ROWID in VARCHAR2,
620 x_PERSON_ID_TYPE IN VARCHAR2,
621 x_DESCRIPTION IN VARCHAR2,
622 x_S_PERSON_ID_TYPE IN VARCHAR2,
623 x_INSTITUTION_CD IN VARCHAR2,
624 x_PREFERRED_IND IN VARCHAR2,
625 x_UNIQUE_IND IN VARCHAR2 ,
626 X_FORMAT_MASK IN VARCHAR2,
627 X_REGION_IND IN VARCHAR2
628 ) AS
629 /*************************************************************
630 Created By : sraj
631 Date Created By : 17-MAY-2000
632 Purpose :
633 Know limitations, enhancements or remarks
634 Change History
635 Who When What
636 sraj 17-MAY-2000 Two columns have been added to this table.
637 (reverse chronological order - newest change first)
638 ***************************************************************/
639
640 cursor c1 is select
641 DESCRIPTION
642 , S_PERSON_ID_TYPE
643 , INSTITUTION_CD
644 , PREFERRED_IND
645 , UNIQUE_IND
646 , FORMAT_MASK
647 , REGION_IND
648 from IGS_PE_PERSON_ID_TYP
649 where ROWID = X_ROWID
650 for update nowait;
651 tlinfo c1%rowtype;
652 begin
653 open c1;
654 fetch c1 into tlinfo;
655 if (c1%notfound) then
656 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
657 IGS_GE_MSG_STACK.ADD;
658 close c1;
659 app_exception.raise_exception;
660 return;
661 end if;
662 close c1;
663 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
664 AND ((tlinfo.S_PERSON_ID_TYPE = X_S_PERSON_ID_TYPE)
665 OR ((tlinfo.S_PERSON_ID_TYPE is null)
666 AND (X_S_PERSON_ID_TYPE is null)))
667 AND ((tlinfo.INSTITUTION_CD = X_INSTITUTION_CD)
668 OR ((tlinfo.INSTITUTION_CD is null)
669 AND (X_INSTITUTION_CD is null)))
670 AND ((tlinfo.PREFERRED_IND = X_PREFERRED_IND)
671 OR ((tlinfo.PREFERRED_IND is null)
672 AND (X_PREFERRED_IND is null)))
673
674 AND ((tlinfo.FORMAT_MASK = X_FORMAT_MASK)
675 OR ((tlinfo.FORMAT_MASK is null)
676 AND (X_FORMAT_MASK is null)))
677
678 AND ((tlinfo.UNIQUE_IND = X_UNIQUE_IND)
679 OR ((tlinfo.UNIQUE_IND is null)
680 AND (X_UNIQUE_IND is null)))
681
682 AND ((tlinfo.REGION_IND = X_REGION_IND)
683 OR((tlinfo.REGION_IND IS NULL)
684 AND (X_REGION_IND IS NULL)))
685
686 )then
687 null;
688 else
689 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
690 IGS_GE_MSG_STACK.ADD;
691 app_exception.raise_exception;
692 end if;
693 return;
694 end LOCK_ROW;
695
696 PROCEDURE UPDATE_ROW (
697 X_ROWID in VARCHAR2,
698 x_PERSON_ID_TYPE IN VARCHAR2,
699 x_DESCRIPTION IN VARCHAR2,
700 x_S_PERSON_ID_TYPE IN VARCHAR2,
701 x_INSTITUTION_CD IN VARCHAR2,
702 x_PREFERRED_IND IN VARCHAR2,
703 x_UNIQUE_IND IN VARCHAR2,
704 X_FORMAT_MASK IN VARCHAR2 ,
705 X_REGION_IND IN VARCHAR2,
706 X_MODE in VARCHAR2,
707 X_CLOSED_IND IN VARCHAR2
708 ) AS
709 /*************************************************************
710 Created By : sraj
711 Date Created By : 17-MAY-2000
712 Purpose :
713 Know limitations, enhancements or remarks
714 Change History
715 Who When What
716 sraj 17-MAY-2000 Two columns have been added to this table.
717 (reverse chronological order - newest change first)
718 ***************************************************************/
719
720 X_LAST_UPDATE_DATE DATE ;
721 X_LAST_UPDATED_BY NUMBER ;
722 X_LAST_UPDATE_LOGIN NUMBER ;
723 begin
724 X_LAST_UPDATE_DATE := SYSDATE;
725 if(X_MODE = 'I') then
726 X_LAST_UPDATED_BY := 1;
727 X_LAST_UPDATE_LOGIN := 0;
728 elsif (X_MODE = 'R') then
729 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
730 if X_LAST_UPDATED_BY is NULL then
731 X_LAST_UPDATED_BY := -1;
732 end if;
733 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
734 if X_LAST_UPDATE_LOGIN is NULL then
735 X_LAST_UPDATE_LOGIN := -1;
736 end if;
737 else
738 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
739 IGS_GE_MSG_STACK.ADD;
740 app_exception.raise_exception;
741 end if;
742 Before_DML(
743 p_action=>'UPDATE',
744 x_rowid=>X_ROWID,
745 x_person_id_type=>X_PERSON_ID_TYPE,
746 x_description=>X_DESCRIPTION,
747 x_s_person_id_type=>X_S_PERSON_ID_TYPE,
748 x_institution_cd=>X_INSTITUTION_CD,
749 x_preferred_ind=>X_PREFERRED_IND,
750 x_unique_ind=>X_UNIQUE_IND,
751 x_format_mask=>X_FORMAT_MASK,
752 x_region_ind => X_REGION_IND,
753 x_closed_ind => X_CLOSED_IND,
754 x_creation_date=>X_LAST_UPDATE_DATE,
755 x_created_by=>X_LAST_UPDATED_BY,
756 x_last_update_date=>X_LAST_UPDATE_DATE,
757 x_last_updated_by=>X_LAST_UPDATED_BY,
758 x_last_update_login=>X_LAST_UPDATE_LOGIN
759 );
760 update IGS_PE_PERSON_ID_TYP set
761 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
762 S_PERSON_ID_TYPE = NEW_REFERENCES.S_PERSON_ID_TYPE,
763 INSTITUTION_CD = NEW_REFERENCES.INSTITUTION_CD,
764 PREFERRED_IND = NEW_REFERENCES.PREFERRED_IND,
765 UNIQUE_IND = NEW_REFERENCES.UNIQUE_IND,
766 FORMAT_MASK = NEW_REFERENCES.FORMAT_MASK,
767 REGION_IND = NEW_REFERENCES.REGION_IND,
768 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
769 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
770 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
771 CLOSED_IND = NEW_REFERENCES.CLOSED_IND
772 where ROWID = X_ROWID;
773 if (sql%notfound) then
774 raise no_data_found;
775 end if;
776
777 After_DML (
778 p_action => 'UPDATE' ,
779 x_rowid => X_ROWID
780 );
781 end UPDATE_ROW;
782
783 procedure ADD_ROW (
784 X_ROWID in out NOCOPY VARCHAR2,
785 x_PERSON_ID_TYPE IN VARCHAR2,
786 x_DESCRIPTION IN VARCHAR2,
787 x_S_PERSON_ID_TYPE IN VARCHAR2,
788 x_INSTITUTION_CD IN VARCHAR2,
789 x_PREFERRED_IND IN VARCHAR2,
790 x_UNIQUE_IND IN VARCHAR2,
791 X_FORMAT_MASK IN VARCHAR2 ,
792 X_REGION_IND IN VARCHAR2,
793 X_MODE in VARCHAR2,
794 X_CLOSED_IND IN VARCHAR2
795 ) AS
796 /*************************************************************
797 Created By : sraj
798 Date Created By : 17-MAY-2000
799 Purpose :
800 Know limitations, enhancements or remarks
801 Change History
802 Who When What
803 sraj 17-MAY-2000 Two columns have been added to this table.
804 (reverse chronological order - newest change first)
805 ***************************************************************/
806
807 cursor c1 is select ROWID from IGS_PE_PERSON_ID_TYP
808 where PERSON_ID_TYPE= X_PERSON_ID_TYPE
809 ;
810 begin
811 open c1;
812 fetch c1 into X_ROWID;
813 if (c1%notfound) then
814 close c1;
815 INSERT_ROW (
816 X_ROWID,
817 X_PERSON_ID_TYPE,
818 X_DESCRIPTION,
819 X_S_PERSON_ID_TYPE,
820 X_INSTITUTION_CD,
821 X_PREFERRED_IND,
822 X_UNIQUE_IND,
823 X_FORMAT_MASK,
824 X_REGION_IND,
825 X_MODE,
826 X_CLOSED_IND);
827 return;
828 end if;
829 close c1;
830 UPDATE_ROW (
831 X_ROWID,
832 X_PERSON_ID_TYPE,
833 X_DESCRIPTION,
834 X_S_PERSON_ID_TYPE,
835 X_INSTITUTION_CD,
836 X_PREFERRED_IND,
837 X_UNIQUE_IND,
838 X_FORMAT_MASK,
839 X_REGION_IND ,
840 X_MODE,
841 X_CLOSED_IND);
842 end ADD_ROW;
843
844 END igs_pe_person_id_typ_pkg;
[Home] [Help]