1 package body IGS_PE_PERSON_ALIAS_PKG AS
2 /* $Header: IGSNI11B.pls 120.2 2005/07/31 23:35:56 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374. The Call to igs_as_val_pal.genp_val_strt_end_dt
7 -- is replaced by igs_ad_val_edtl.genp_val_strt_end_dt
8 --smadathi 24-AUG-2001 Bug No. 1956374. The call to igs_en_val_pal.genp_val_sdtt_sess
9 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
10 --skpandey 01-JUL-2005 Bug No. 4327807
11 -- Added an additional condition in "BeforeRowInsertUpdate1" prodecure
12 -- to check that the Effective start date must not be earlier than the person's year of birth.
13 -------------------------------------------------------------------------------------------
14 /***********************************************************************
15
16 CHANGE HISTORY : 1219551 FIXED BY - ahemmige 04-MAR-2000
17 PROCEDURE/PROGRAM UNIT/FORM object affected : check_constraints
18 PURPOSE/RATIONALE : There is no need to check the alias_comments
19 field for upper case restrictions.
20 KNOWN LIMITATIONS/ENHANCEMENTS and REMARKS :
21
22 ***********************************************************************/
23
24 l_rowid VARCHAR2(25);
25 old_references IGS_PE_PERSON_ALIAS%RowType;
26 new_references IGS_PE_PERSON_ALIAS%RowType;
27
28 PROCEDURE Set_Column_Values (
29 p_action IN VARCHAR2,
30 x_rowid IN VARCHAR2 DEFAULT NULL,
31 x_person_id IN NUMBER DEFAULT NULL,
32 X_alias_type IN VARCHAR2 DEFAULT NULL,
33 x_sequence_number IN NUMBER DEFAULT NULL,
34 x_start_dt IN DATE DEFAULT NULL,
35 x_end_dt IN DATE DEFAULT NULL,
36 x_surname IN VARCHAR2 DEFAULT NULL,
37 x_given_names IN VARCHAR2 DEFAULT NULL,
38 x_title IN VARCHAR2 DEFAULT NULL,
39 x_alias_comment IN VARCHAR2 DEFAULT NULL,
40 x_creation_date IN DATE DEFAULT NULL,
41 x_created_by IN NUMBER DEFAULT NULL,
42 x_last_update_date IN DATE DEFAULT NULL,
43 x_last_updated_by IN NUMBER DEFAULT NULL,
44 x_last_update_login IN NUMBER DEFAULT NULL
45 ) AS
46
47 CURSOR cur_old_ref_values IS
48 SELECT *
49 FROM IGS_PE_PERSON_ALIAS
50 WHERE rowid = x_rowid;
51
52 BEGIN
53
54 l_rowid := x_rowid;
55
56 -- Code for setting the Old and New Reference Values.
57 -- Populate Old Values.
58 Open cur_old_ref_values;
59 Fetch cur_old_ref_values INTO old_references;
60 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
61 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
62 IGS_GE_MSG_STACK.ADD;
63 Close cur_old_ref_values;
64 App_Exception.Raise_Exception;
65 Return;
66 END IF;
67 Close cur_old_ref_values;
68
69 -- Populate New Values.
70 new_references.person_id := x_person_id;
71 new_references.alias_type := x_alias_type;
72 new_references.sequence_number := x_sequence_number;
73 new_references.start_dt := x_start_dt;
74 new_references.end_dt := x_end_dt;
75 new_references.surname := x_surname;
76 new_references.given_names := x_given_names;
77 new_references.title := x_title;
78 new_references.alias_comment := x_alias_comment;
79 IF (p_action = 'UPDATE') THEN
80 new_references.creation_date := old_references.creation_date;
81 new_references.created_by := old_references.created_by;
82 ELSE
83 new_references.creation_date := x_creation_date;
84 new_references.created_by := x_created_by;
85 END IF;
86 new_references.last_update_date := x_last_update_date;
87 new_references.last_updated_by := x_last_updated_by;
88 new_references.last_update_login := x_last_update_login;
89
90 END Set_Column_Values;
91 PROCEDURE BeforeRowInsertUpdate1(
92 p_inserting IN BOOLEAN DEFAULT FALSE,
93 p_updating IN BOOLEAN DEFAULT FALSE,
94 p_deleting IN BOOLEAN DEFAULT FALSE
95 ) AS
96
97 CURSOR get_dob_dt_cur(cp_person_id igs_pe_passport.person_id%TYPE)
98 IS
99 SELECT birth_date
100 FROM igs_pe_person_base_v
101 WHERE person_id = cp_person_id;
102 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
103 v_message_name varchar2(30);
104 BEGIN
105 -- If trigger has not been disabled, perform required processing
106 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_PE_PERSON_ALIAS') THEN
107 -- Validate START DATE AND END DATE.
108 -- Validate that if end date is specified, then start date is also specified.
109 IF (new_references.end_dt IS NOT NULL) AND
110 ((p_inserting OR p_updating) OR
111 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <> new_references.end_dt))
112 THEN
113 IF IGS_EN_VAL_PAL.enrp_val_api_end_dt (
114 new_references.start_dt,
115 new_references.end_dt,
116 v_message_name) = FALSE THEN
117 Fnd_Message.Set_Name('IGS', v_message_name);
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END IF;
122 -- Validate that if both are specified, then end is not greater than start.
123 IF (new_references.end_dt IS NOT NULL) AND
124 ((p_inserting OR p_updating) OR
125 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <> new_references.end_dt)) THEN
126 IF igs_ad_val_edtl.genp_val_strt_end_dt (
127 new_references.start_dt,
128 new_references.end_dt,
129 v_message_name) = FALSE THEN
130 Fnd_Message.Set_Name('IGS', v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 END IF;
134 END IF;
135 -- Prevent the start date being set to null if the end date is specified.
136 IF p_updating AND (new_references.start_dt IS NULL AND new_references.end_dt IS NOT NULL) THEN
137 Fnd_Message.Set_Name('IGS', 'IGS_EN_CANT_REMOVE_ST_DATE');
138 IGS_GE_MSG_STACK.ADD;
139 App_Exception.Raise_Exception;
140 END IF;
141 -- Validate Surname and Given Names.
142 IF (p_inserting OR p_updating) THEN
143 IF IGS_EN_VAL_PAL.enrp_val_pal_names (
144 new_references.surname,
145 new_references.given_names,
146 v_message_name) = FALSE THEN
147 Fnd_Message.Set_Name('IGS', v_message_name);
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 -- Validate that if both are specified, then the Effective start date must not be earlier than the person's year of birth.
152 OPEN get_dob_dt_cur(new_references.person_id);
153 FETCH get_dob_dt_cur INTO l_birth_dt;
154 CLOSE get_dob_dt_cur;
155 IF l_birth_dt IS NOT NULL AND new_references.start_dt IS NOT NULL THEN
156 IF l_birth_dt > new_references.start_dt THEN
157 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_STDT_NOTLESS_BIRTHDT');
158 IGS_GE_MSG_STACK.ADD;
159 APP_EXCEPTION.RAISE_EXCEPTION;
160 END IF;
161 END IF;
162 END IF;
163
164 -- This following IF block is removed as a fix for bug number 2045753
165 /* IF (p_inserting OR p_updating) THEN
166 IF IGS_EN_VAL_PAL.enrp_val_pal_alias (
167 new_references.person_id,
168 new_references.surname,
169 new_references.given_names,
170 new_references.title,
171 v_message_name) = FALSE THEN
172 Fnd_Message.Set_Name('IGS', v_message_name);
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177 */
178 END IF;
179
180 END BeforeRowInsertUpdate1;
181
182 PROCEDURE Check_Constraints (
183 Column_Name IN VARCHAR2 DEFAULT NULL,
184 Column_Value IN VARCHAR2 DEFAULT NULL
185 )
186 AS
187 BEGIN
188 IF column_name is null then
189 NULL;
190 ELSIF upper(Column_name) = 'GIVEN_NAMES' then
191 new_references.given_names:= column_value;
192 ELSIF upper(Column_name) = 'SURNAME' then
193 new_references.surname:= column_value;
194 END IF;
195
196 IF upper(column_name) = 'GIVEN_NAMES' OR
197 column_name is null Then
198 IF new_references.given_names <>
199 UPPER(new_references.given_names) Then
200 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 END IF;
204 END IF;
205 IF upper(column_name) = 'SURNAME' OR
206 column_name is null Then
207 IF new_references.surname<>
208 UPPER(new_references.surname) Then
209 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
210 IGS_GE_MSG_STACK.ADD;
211 App_Exception.Raise_Exception;
212 END IF;
213 END IF;
214
215 END Check_Constraints;
216
217
218 PROCEDURE Check_Parent_Existance AS
219 BEGIN
220
221 IF (((old_references.person_id = new_references.person_id)) OR
222 ((new_references.person_id IS NULL))) THEN
223 NULL;
224 ELSE
225 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
226 new_references.person_id ) THEN
227 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
228 IGS_GE_MSG_STACK.ADD;
229 App_Exception.Raise_Exception;
230 END IF;
231 END IF;
232
233 END Check_Parent_Existance;
234
235 FUNCTION Get_PK_For_Validation (
236 x_person_id IN NUMBER,
237 x_sequence_number IN NUMBER
238 ) RETURN BOOLEAN AS
239
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM IGS_PE_PERSON_ALIAS
243 WHERE person_id = x_person_id
244 AND sequence_number = x_sequence_number
245 FOR UPDATE NOWAIT;
246
247 lv_rowid cur_rowid%RowType;
248
249 BEGIN
250
251 Open cur_rowid;
252 Fetch cur_rowid INTO lv_rowid;
253 IF (cur_rowid%FOUND) THEN
254 Close cur_rowid;
255 Return (TRUE);
256 ELSE
257 Close cur_rowid;
258 Return (FALSE);
259 END IF;
260
261 END Get_PK_For_Validation;
262
263 PROCEDURE GET_FK_IGS_PE_PERSON (
264 x_person_id IN NUMBER
265 ) AS
266
267 CURSOR cur_rowid IS
268 SELECT rowid
269 FROM IGS_PE_PERSON_ALIAS
270 WHERE person_id = x_person_id ;
271
272 lv_rowid cur_rowid%RowType;
273
274 BEGIN
275
276 Open cur_rowid;
277 Fetch cur_rowid INTO lv_rowid;
278 IF (cur_rowid%FOUND) THEN
279 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PAL_PE_FK');
280 IGS_GE_MSG_STACK.ADD;
281 Close cur_rowid;
282 App_Exception.Raise_Exception;
283 Return;
284 END IF;
285 Close cur_rowid;
286
287 END GET_FK_IGS_PE_PERSON;
288
289 PROCEDURE Before_DML (
290 p_action IN VARCHAR2,
291 x_rowid IN VARCHAR2 DEFAULT NULL,
292 x_person_id IN NUMBER DEFAULT NULL,
293 x_alias_type IN VARCHAR2 DEFAULT NULL,
294 x_sequence_number IN NUMBER DEFAULT NULL,
295 x_start_dt IN DATE DEFAULT NULL,
296 x_end_dt IN DATE DEFAULT NULL,
297 x_surname IN VARCHAR2 DEFAULT NULL,
298 x_given_names IN VARCHAR2 DEFAULT NULL,
299 x_title IN VARCHAR2 DEFAULT NULL,
300 x_alias_comment IN VARCHAR2 DEFAULT NULL,
301 x_creation_date IN DATE DEFAULT NULL,
302 x_created_by IN NUMBER DEFAULT NULL,
303 x_last_update_date IN DATE DEFAULT NULL,
304 x_last_updated_by IN NUMBER DEFAULT NULL,
305 x_last_update_login IN NUMBER DEFAULT NULL
306 ) AS
307 BEGIN
308
309 Set_Column_Values (
310 p_action,
311 x_rowid,
312 x_person_id,
313 x_alias_type,
314 x_sequence_number,
315 x_start_dt,
316 x_end_dt,
317 x_surname,
318 x_given_names,
319 x_title,
320 x_alias_comment,
321 x_creation_date,
322 x_created_by,
323 x_last_update_date,
324 x_last_updated_by,
325 x_last_update_login
326 );
327
328 IF (p_action = 'INSERT') THEN
329 -- Call all the procedures related to Before Insert.
330 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
331 IF Get_PK_For_Validation (
332 new_references.person_id ,
333 new_references.sequence_number) THEN
334 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 END IF;
338
339 Check_Parent_Existance; -- if procedure present
340 ELSIF (p_action = 'UPDATE') THEN
341 -- Call all the procedures related to Before Update.
342 BeforeRowInsertUpdate1 ( p_updating => TRUE );
343
344 Check_Parent_Existance; -- if procedure present
345
346 ELSIF (p_action = 'DELETE') THEN
347 -- Call all the procedures related to Before Delete.
348
349 NULL;
350 ELSIF (p_action = 'VALIDATE_INSERT') THEN
351 IF Get_PK_For_Validation (
352 new_references.person_id ,
353 new_references.sequence_number) THEN
354 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END IF;
358
359 END IF;
360
361 END Before_DML;
362
363 PROCEDURE After_DML (
364 p_action IN VARCHAR2,
365 x_rowid IN VARCHAR2
366 ) AS
367 BEGIN
368
369 l_rowid := x_rowid;
370
371 IF (p_action = 'INSERT') THEN
372 -- Call all the procedures related to After Insert.
373 Null;
374 ELSIF (p_action = 'UPDATE') THEN
375 -- Call all the procedures related to After Update.
376 Null;
377 ELSIF (p_action = 'DELETE') THEN
378 -- Call all the procedures related to After Delete.
379 Null;
380 END IF;
381
382 END After_DML;
383
384 procedure INSERT_ROW (
385 X_ROWID in out NOCOPY VARCHAR2,
386 X_PERSON_ID in NUMBER,
387 X_ALIAS_TYPE in VARCHAR2,
388 X_SEQUENCE_NUMBER in NUMBER,
389 X_TITLE in VARCHAR2,
390 X_ALIAS_COMMENT in VARCHAR2,
391 X_START_DT in DATE,
392 X_END_DT in DATE,
393 X_SURNAME in VARCHAR2,
394 X_GIVEN_NAMES in VARCHAR2,
395 X_MODE in VARCHAR2 default 'R'
396 ) is
397 cursor C is select ROWID from IGS_PE_PERSON_ALIAS
398 where PERSON_ID = X_PERSON_ID
399 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
400 X_LAST_UPDATE_DATE DATE;
401 X_LAST_UPDATED_BY NUMBER;
402 X_LAST_UPDATE_LOGIN NUMBER;
403 begin
404 X_LAST_UPDATE_DATE := SYSDATE;
405 if(X_MODE = 'I') then
406 X_LAST_UPDATED_BY := 1;
407 X_LAST_UPDATE_LOGIN := 0;
408 elsif (X_MODE IN ('R', 'S')) then
409 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
410 if X_LAST_UPDATED_BY is NULL then
411 X_LAST_UPDATED_BY := -1;
412 end if;
413 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
414 if X_LAST_UPDATE_LOGIN is NULL then
415 X_LAST_UPDATE_LOGIN := -1;
416 end if;
417 else
418 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
419 IGS_GE_MSG_STACK.ADD;
420 app_exception.raise_exception;
421 end if;
422
423 Before_DML(
424 p_action=>'INSERT',
425 x_rowid=>X_ROWID,
426 x_alias_comment=>X_ALIAS_COMMENT,
427 x_end_dt=>X_END_DT,
428 x_given_names=>X_GIVEN_NAMES,
429 x_person_id=>X_PERSON_ID,
430 x_alias_type=>X_ALIAS_TYPE,
431 x_sequence_number=>X_SEQUENCE_NUMBER,
432 x_start_dt=>X_START_DT,
433 x_surname=>X_SURNAME,
434 x_title=>X_TITLE,
435 x_creation_date=>X_LAST_UPDATE_DATE,
436 x_created_by=>X_LAST_UPDATED_BY,
437 x_last_update_date=>X_LAST_UPDATE_DATE,
438 x_last_updated_by=>X_LAST_UPDATED_BY,
439 x_last_update_login=>X_LAST_UPDATE_LOGIN
440 );
441
442 IF (x_mode = 'S') THEN
443 igs_sc_gen_001.set_ctx('R');
444 END IF;
445 insert into IGS_PE_PERSON_ALIAS (
446 TITLE,
447 ALIAS_COMMENT,
448 PERSON_ID,
449 ALIAS_TYPE,
450 SEQUENCE_NUMBER,
451 START_DT,
452 END_DT,
453 SURNAME,
454 GIVEN_NAMES,
455 CREATION_DATE,
456 CREATED_BY,
457 LAST_UPDATE_DATE,
458 LAST_UPDATED_BY,
459 LAST_UPDATE_LOGIN
460 ) values (
461 NEW_REFERENCES.TITLE,
462 NEW_REFERENCES.ALIAS_COMMENT,
463 NEW_REFERENCES.PERSON_ID,
464 NEW_REFERENCES.ALIAS_TYPE,
465 NEW_REFERENCES.SEQUENCE_NUMBER,
466 NEW_REFERENCES.START_DT,
467 NEW_REFERENCES.END_DT,
468 NEW_REFERENCES.SURNAME,
469 NEW_REFERENCES.GIVEN_NAMES,
470 X_LAST_UPDATE_DATE,
471 X_LAST_UPDATED_BY,
472 X_LAST_UPDATE_DATE,
473 X_LAST_UPDATED_BY,
474 X_LAST_UPDATE_LOGIN
475 );
476 IF (x_mode = 'S') THEN
477 igs_sc_gen_001.unset_ctx('R');
478 END IF;
479
480
481 open c;
482 fetch c into X_ROWID;
483 if (c%notfound) then
484 close c;
485 raise no_data_found;
486 end if;
487 After_DML(
488 p_action => 'INSERT',
489 x_rowid => X_ROWID
490 );
491 close c;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
496 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
497 fnd_message.set_token ('ERR_CD', SQLCODE);
498 igs_ge_msg_stack.add;
499 igs_sc_gen_001.unset_ctx('R');
500 app_exception.raise_exception;
501 ELSE
502 igs_sc_gen_001.unset_ctx('R');
503 RAISE;
504 END IF;
505
506 end INSERT_ROW;
507
508 procedure LOCK_ROW (
509 X_ROWID in VARCHAR2,
510 X_PERSON_ID in NUMBER,
511 X_ALIAS_TYPE in VARCHAR2,
512 X_SEQUENCE_NUMBER in NUMBER,
513 X_TITLE in VARCHAR2,
514 X_ALIAS_COMMENT in VARCHAR2,
515 X_START_DT in DATE,
516 X_END_DT in DATE,
517 X_SURNAME in VARCHAR2,
518 X_GIVEN_NAMES in VARCHAR2
519 ) is
520 cursor c1 is select
521 TITLE,
522 ALIAS_COMMENT,
523 START_DT,
524 END_DT,
525 SURNAME,
526 GIVEN_NAMES
527 from IGS_PE_PERSON_ALIAS
528 where ROWID = X_ROWID
529 for update nowait;
530 tlinfo c1%rowtype;
531
532 begin
533 open c1;
534 fetch c1 into tlinfo;
535 if (c1%notfound) then
536 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
537
538 close c1;
539 App_Exception.Raise_Exception;
540 return;
541 end if;
542 close c1;
543
544 if ( ((tlinfo.TITLE = X_TITLE)
545 OR ((tlinfo.TITLE is null)
546 AND (X_TITLE is null)))
547 AND ((tlinfo.ALIAS_COMMENT = X_ALIAS_COMMENT)
548 OR ((tlinfo.ALIAS_COMMENT is null)
549 AND (X_ALIAS_COMMENT is null)))
550 AND ((tlinfo.START_DT = X_START_DT)
551 OR ((tlinfo.START_DT is null)
552 AND (X_START_DT is null)))
553 AND ((tlinfo.END_DT = X_END_DT)
554 OR ((tlinfo.END_DT is null)
555 AND (X_END_DT is null)))
556 AND ((tlinfo.SURNAME = X_SURNAME)
557 OR ((tlinfo.SURNAME is null)
558 AND (X_SURNAME is null)))
559 AND ((tlinfo.GIVEN_NAMES = X_GIVEN_NAMES)
560 OR ((tlinfo.GIVEN_NAMES is null)
561 AND (X_GIVEN_NAMES is null)))
562 ) then
563 null;
564 else
565 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
566 app_exception.raise_exception;
567 end if;
568 return;
569 end LOCK_ROW;
570
571 procedure UPDATE_ROW (
572 X_ROWID in VARCHAR2,
573 X_PERSON_ID in NUMBER,
574 X_ALIAS_TYPE in VARCHAR2,
575 X_SEQUENCE_NUMBER in NUMBER,
576 X_TITLE in VARCHAR2,
577 X_ALIAS_COMMENT in VARCHAR2,
578 X_START_DT in DATE,
579 X_END_DT in DATE,
580 X_SURNAME in VARCHAR2,
581 X_GIVEN_NAMES in VARCHAR2,
582 X_MODE in VARCHAR2 default 'R'
583 ) is
584 X_LAST_UPDATE_DATE DATE;
585 X_LAST_UPDATED_BY NUMBER;
586 X_LAST_UPDATE_LOGIN NUMBER;
587 begin
588 X_LAST_UPDATE_DATE := SYSDATE;
589 if(X_MODE = 'I') then
590 X_LAST_UPDATED_BY := 1;
591 X_LAST_UPDATE_LOGIN := 0;
592 elsif (X_MODE IN ('R', 'S')) then
593 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
594 if X_LAST_UPDATED_BY is NULL then
595 X_LAST_UPDATED_BY := -1;
596 end if;
597 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
598 if X_LAST_UPDATE_LOGIN is NULL then
599 X_LAST_UPDATE_LOGIN := -1;
600 end if;
601 else
602 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
603 IGS_GE_MSG_STACK.ADD;
604 app_exception.raise_exception;
605 end if;
606 Before_DML(
607 p_action=>'UPDATE',
608 x_rowid=>X_ROWID,
609 x_alias_comment=>X_ALIAS_COMMENT,
610 x_end_dt=>X_END_DT,
611 x_given_names=>X_GIVEN_NAMES,
612 x_person_id=>X_PERSON_ID,
613 x_alias_type=>X_ALIAS_TYPE,
614 x_sequence_number=>X_SEQUENCE_NUMBER,
615 x_start_dt=>X_START_DT,
616 x_surname=>X_SURNAME,
617 x_title=>X_TITLE,
618 x_creation_date=>X_LAST_UPDATE_DATE,
619 x_created_by=>X_LAST_UPDATED_BY,
620 x_last_update_date=>X_LAST_UPDATE_DATE,
621 x_last_updated_by=>X_LAST_UPDATED_BY,
622 x_last_update_login=>X_LAST_UPDATE_LOGIN
623 );
624 IF (x_mode = 'S') THEN
625 igs_sc_gen_001.set_ctx('R');
626 END IF;
627 update IGS_PE_PERSON_ALIAS set
628 TITLE = NEW_REFERENCES.TITLE,
629 ALIAS_COMMENT = NEW_REFERENCES.ALIAS_COMMENT,
630 START_DT = NEW_REFERENCES.START_DT,
631 END_DT = NEW_REFERENCES.END_DT,
632 SURNAME = NEW_REFERENCES.SURNAME,
633 GIVEN_NAMES = NEW_REFERENCES.GIVEN_NAMES,
634 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
635 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
636 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
637 where ROWID = X_ROWID
638 ;
639 if (sql%notfound) then
640 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
641 igs_ge_msg_stack.add;
642 igs_sc_gen_001.unset_ctx('R');
643 app_exception.raise_exception;
644 end if;
645 IF (x_mode = 'S') THEN
646 igs_sc_gen_001.unset_ctx('R');
647 END IF;
648
649 After_DML(
650 p_action => 'UPDATE',
651 x_rowid => X_ROWID
652 );
653 EXCEPTION
654 WHEN OTHERS THEN
655 IF (SQLCODE = (-28115)) THEN
656 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
657 fnd_message.set_token ('ERR_CD', SQLCODE);
658 igs_ge_msg_stack.add;
659 igs_sc_gen_001.unset_ctx('R');
660 app_exception.raise_exception;
661 ELSE
662 igs_sc_gen_001.unset_ctx('R');
663 RAISE;
664 END IF;
665
666 end UPDATE_ROW;
667
668 procedure ADD_ROW (
669 X_ROWID in out NOCOPY VARCHAR2,
670 X_PERSON_ID in NUMBER,
671 X_ALIAS_TYPE in VARCHAR2,
672 X_SEQUENCE_NUMBER in NUMBER,
673 X_TITLE in VARCHAR2,
674 X_ALIAS_COMMENT in VARCHAR2,
675 X_START_DT in DATE,
676 X_END_DT in DATE,
677 X_SURNAME in VARCHAR2,
678 X_GIVEN_NAMES in VARCHAR2,
679 X_MODE in VARCHAR2 default 'R'
680 ) AS
681 cursor c1 is select rowid from IGS_PE_PERSON_ALIAS
682 where PERSON_ID = X_PERSON_ID
683 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
684 ;
685
686 begin
687 open c1;
688 fetch c1 into X_ROWID;
689 if (c1%notfound) then
690 close c1;
691 INSERT_ROW (
692 X_ROWID,
693 X_PERSON_ID,
694 X_ALIAS_TYPE,
695 X_SEQUENCE_NUMBER,
696 X_TITLE,
697 X_ALIAS_COMMENT,
698 X_START_DT,
699 X_END_DT,
700 X_SURNAME,
701 X_GIVEN_NAMES,
702 X_MODE);
703 return;
704 end if;
705 close c1;
706 UPDATE_ROW (
707 X_ROWID,
708 X_PERSON_ID,
709 X_ALIAS_TYPE,
710 X_SEQUENCE_NUMBER,
711 X_TITLE,
712 X_ALIAS_COMMENT,
713 X_START_DT,
714 X_END_DT,
715 X_SURNAME,
716 X_GIVEN_NAMES,
717 X_MODE);
718 end ADD_ROW;
719
720 procedure DELETE_ROW (
721 X_ROWID in VARCHAR2,
722 x_mode IN VARCHAR2
723 ) AS
724 begin
725 Before_DML(
726 p_action => 'DELETE',
727 x_rowid => X_ROWID
728 );
729 IF (x_mode = 'S') THEN
730 igs_sc_gen_001.set_ctx('R');
731 END IF;
732 delete from IGS_PE_PERSON_ALIAS
733 where ROWID = X_ROWID;
734 if (sql%notfound) then
735 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
736 igs_ge_msg_stack.add;
737 igs_sc_gen_001.unset_ctx('R');
738 app_exception.raise_exception;
739 end if;
740 IF (x_mode = 'S') THEN
741 igs_sc_gen_001.unset_ctx('R');
742 END IF;
743
744 After_DML(
745 p_action => 'DELETE',
746 x_rowid => X_ROWID
747 );
748 end DELETE_ROW;
749 end IGS_PE_PERSON_ALIAS_PKG;
[Home] [Help]