[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_ALT_PERS_ID_PKG
Source
1 package body IGS_PE_ALT_PERS_ID_PKG as
2 /* $Header: IGSNI02B.pls 120.4 2005/10/17 02:23:06 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --avenkatr 09-OCT-2001 Bug No. 2037667 .Comparision in the Lock_row procedure of the Start_dt and End_dt has been changed to compare only the date part.
7 --smadathi 28-AUG-2001 Bug No. 1956374 .The Call to igs_en_val_api.genp_val_strt_end_dt
8 -- is replaced by igs_ad_val_edtl.genp_val_strt_end_dt
9 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_en_val_api.genp_val_sdtt_sess
10 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
11 --
12 -- who when what
13 -- CDCRUZ Sep 24,2002 Bug ID : 2000408
14 -- New Flex Fld Col's added for Person DLD
15 --askapoor 31-JAN-2005 Bug No: 3882788
16 -- saving trunc(start_dt) and trunc(end_dt)
17 --skpandey 01-AUG-2005 Bug No:4327807
18 -- Added an additional condition for p_action='DELETE' to accomodate Business logic
19 -------------------------------------------------------------------------------------------
20 l_rowid VARCHAR2(25);
21 old_references IGS_PE_ALT_PERS_ID%RowType;
22 new_references IGS_PE_ALT_PERS_ID%RowType;
23 PROCEDURE Set_Column_Values (
24 p_action IN VARCHAR2,
25 x_rowid IN VARCHAR2,
26 x_pe_person_id IN NUMBER,
27 x_api_person_id IN VARCHAR2,
28 X_API_PERSON_ID_UF IN VARCHAR2,
29 x_person_id_type IN VARCHAR2,
30 x_start_dt IN DATE,
31 x_end_dt IN DATE,
32 x_creation_date IN DATE,
33 x_created_by IN NUMBER,
34 x_last_update_date IN DATE,
35 x_last_updated_by IN NUMBER,
36 x_last_update_login IN NUMBER,
37 x_attribute_category IN VARCHAR2,
38 x_attribute1 IN VARCHAR2,
39 x_attribute2 IN VARCHAR2,
40 x_attribute3 IN VARCHAR2,
41 x_attribute4 IN VARCHAR2,
42 x_attribute5 IN VARCHAR2,
43 x_attribute6 IN VARCHAR2,
44 x_attribute7 IN VARCHAR2,
45 x_attribute8 IN VARCHAR2,
46 x_attribute9 IN VARCHAR2,
47 x_attribute10 IN VARCHAR2,
48 x_attribute11 IN VARCHAR2,
49 x_attribute12 IN VARCHAR2,
50 x_attribute13 IN VARCHAR2,
51 x_attribute14 IN VARCHAR2,
52 x_attribute15 IN VARCHAR2,
53 x_attribute16 IN VARCHAR2,
54 x_attribute17 IN VARCHAR2,
55 x_attribute18 IN VARCHAR2,
56 x_attribute19 IN VARCHAR2,
57 x_attribute20 IN VARCHAR2,
58 x_region_cd IN VARCHAR2
59 ) as
60 CURSOR cur_old_ref_values IS
61 SELECT *
62 FROM IGS_PE_ALT_PERS_ID
63 WHERE rowid = x_rowid;
64 BEGIN
65 l_rowid := x_rowid;
66 -- Code for setting the Old and New Reference Values.
67 -- Populate Old Values.
68 Open cur_old_ref_values;
69 Fetch cur_old_ref_values INTO old_references;
70 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
71 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
72 IGS_GE_MSG_STACK.ADD;
73 Close cur_old_ref_values;
74 App_Exception.Raise_Exception;
75 Return;
76 END IF;
77 Close cur_old_ref_values;
78 -- Populate New Values.
79
80 IF p_action = 'DELETE' THEN
81 RETURN;
82 END IF;
83
84 new_references.pe_person_id := x_pe_person_id;
85 new_references.api_person_id := x_api_person_id;
86 new_references.api_person_id_uf := igs_en_val_api.unformat_api(x_api_person_id);
87 new_references.person_id_type := x_person_id_type;
88 new_references.start_dt := trunc(x_start_dt);
89 new_references.end_dt := trunc(x_end_dt);
90 new_references.attribute_category := x_attribute_category ;
91 new_references.attribute1 := x_attribute1 ;
92 new_references.attribute2 := x_attribute2 ;
93 new_references.attribute3 := x_attribute3 ;
94 new_references.attribute4 := x_attribute4 ;
95 new_references.attribute5 := x_attribute5 ;
96 new_references.attribute6 := x_attribute6 ;
97 new_references.attribute7 := x_attribute7 ;
98 new_references.attribute8 := x_attribute8 ;
99 new_references.attribute9 := x_attribute9 ;
100 new_references.attribute10 := x_attribute10 ;
101 new_references.attribute11 := x_attribute11 ;
102 new_references.attribute12 := x_attribute12 ;
103 new_references.attribute13 := x_attribute13 ;
104 new_references.attribute14 := x_attribute14 ;
105 new_references.attribute15 := x_attribute15 ;
106 new_references.attribute16 := x_attribute16 ;
107 new_references.attribute17 := x_attribute17 ;
108 new_references.attribute18 := x_attribute18 ;
109 new_references.attribute19 := x_attribute19 ;
110 new_references.attribute20 := x_attribute20 ;
111 new_references.region_cd := x_region_cd ;
112
113 IF (p_action = 'UPDATE') THEN
114 new_references.creation_date := old_references.creation_date;
115 new_references.created_by := old_references.created_by;
116 ELSE
117 new_references.creation_date := x_creation_date;
118 new_references.created_by := x_created_by;
119 END IF;
120 new_references.last_update_date := x_last_update_date;
121 new_references.last_updated_by := x_last_updated_by;
122 new_references.last_update_login := x_last_update_login;
123 END Set_Column_Values;
124
125 PROCEDURE BeforeRowInsertUpdate1(
126 p_inserting IN BOOLEAN,
127 p_updating IN BOOLEAN,
128 p_deleting IN BOOLEAN
129 ) as
130 ------------------------------------------------------------------------------------------
131 --Created by : pkpatel
132 --Date created: 06-JUN-2002
133 --
134 --Purpose:
135 --Known limitations/enhancements and/or remarks:
136 --
137 --Change History:
138 --Who When What
139 --pkpatel 8-JUN-2002 Bug No: 2402077
140 -- Removed the call to igs_as_val_suaap.genp_val_sdtt_sess('IGS_PE_ALT_PERS_ID') and
141 -- all unnecessary check so that the date validation procedures can be called properly.
142 --askapoor 31-JAN-2005 Bug No: 3882788
143 -- Removed end_dt < sysdate and added check start_dt = end_dt
144 --pkpatel 16-JUL-2005 Bug 4327807 (Person SS Enhancement)
145 -- Validate Format Mask
146 ----------------------------------------------------------------------------------------------
147 v_message_name varchar2(30);
148
149 CURSOR birth_date_cur(cp_person_id hz_parties.party_id%TYPE) IS
150 SELECT birth_date
151 FROM igs_pe_person_base_v
152 WHERE person_id = cp_person_id;
153
154 CURSOR format_mask_cur (cp_person_id_type VARCHAR2) IS
155 SELECT format_mask
156 FROM igs_pe_person_id_typ
157 WHERE person_id_type = cp_person_id_type;
158
159 birth_date_rec birth_date_cur%ROWTYPE;
160 format_mask_rec format_mask_cur%ROWTYPE;
161
162 BEGIN
163
164 IF p_inserting THEN
165 OPEN format_mask_cur(new_references.person_id_type);
166 FETCH format_mask_cur INTO format_mask_rec;
167 CLOSE format_mask_cur;
168
169 IF format_mask_rec.format_mask IS NOT NULL THEN
170 IF NOT igs_en_val_api.fm_equal(new_references.api_person_id, format_mask_rec.format_mask) THEN
171 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PID_MASK');
172 FND_MESSAGE.SET_TOKEN('FORMAT',format_mask_rec.format_mask);
173 IGS_GE_MSG_STACK.ADD;
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 END IF;
176 END IF;
177
178 END IF;
179
180 -- Validate START DATE AND END DATE.
181 IF p_inserting OR p_updating THEN
182
183 -- Validate that if end date is specified, then start date is also specified.
184 -- As part of the bug 1956374 changed the following call from IGS_EN_VAL_API.enrp_val_api_end_dt
185 IF IGS_EN_VAL_PAL.enrp_val_api_end_dt (
186 new_references.start_dt,
187 new_references.end_dt,
188 v_message_name) = FALSE THEN
189 Fnd_Message.Set_Name('IGS', v_message_name);
190 IGS_GE_MSG_STACK.ADD;
191 App_Exception.Raise_Exception;
192 END IF;
193
194 -- Validate that if both are specified, then end is not greater than start.
195 IF (new_references.end_dt IS NOT NULL) THEN
196 IF igs_ad_val_edtl.genp_val_strt_end_dt (
197 new_references.start_dt,
198 new_references.end_dt,
199 v_message_name) = FALSE THEN
200 FND_MESSAGE.SET_NAME('IGS', v_message_name);
201 IGS_GE_MSG_STACK.ADD;
202 APP_EXCEPTION.RAISE_EXCEPTION;
203 END IF;
204 END IF;
205
206 OPEN birth_date_cur(new_references.pe_person_id);
207 FETCH birth_date_cur INTO birth_date_rec;
208 CLOSE birth_date_cur;
209
210 IF birth_date_rec.birth_date IS NOT NULL THEN
211 IF new_references.start_dt < birth_date_rec.birth_date THEN
212 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
213 IGS_GE_MSG_STACK.ADD;
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 END IF;
216 END IF;
217
218 END IF;
219
220 IF (old_references.end_dt IS NOT NULL) AND
221 (trunc(new_references.end_dt) <> trunc(old_references.end_dt)) AND
222 (trunc(old_references.end_dt) = trunc(old_references.start_dt) ) THEN
223 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_ALT_END_DT_VAL');
224 IGS_GE_MSG_STACK.ADD;
225 APP_EXCEPTION.RAISE_EXCEPTION;
226 END IF;
227
228 END BeforeRowInsertUpdate1;
229
230 PROCEDURE AfterRowInsertUpdate2(
231 p_inserting IN BOOLEAN,
232 p_updating IN BOOLEAN,
233 p_deleting IN BOOLEAN
234 ) as
235 ------------------------------------------------------------------------------------------
236 --Created by : pkpatel
237 --Date created: 06-JUN-2002
238 --
239 --Purpose:
240 --
241 --Known limitations/enhancements and/or remarks:
242 --
243 --Change History:
244 --Who When What
245 --pkpatel 8-JUN-2002 Bug No: 2402077
246 -- Added the call igs_en_val_api.val_overlap_api so that there would be only one ACTIVE
247 -- alternate person id exist for a person ID type for a person
248 -- Added the call igs_en_val_api.val_ssn_overlap_api so that there would be only one ACTIVE
249 -- alternate person id exist for Social Security Number for a person
250 --ssaleem 17-Sep-2004 Bug 3787210 -- added Closed Ind igs_pe_person_id_typ table
251 --gmaheswa 29-Sep-2004 BUG 3787210 removed Closed indicator check for the Alternate Person Id type while end date overlap check.
252
253 ----------------------------------------------------------------------------------------------
254 v_message_name varchar2(30);
255 v_rowid_saved BOOLEAN := FALSE;
256 cst_pay_adv_no CONSTANT VARCHAR2(10) := 'PAY_ADV_NO';
257 v_dummy VARCHAR2(1);
258 CURSOR c_pit (cp_person_id_type IGS_PE_PERSON_ID_TYP.person_id_type%TYPE) IS
259 SELECT 'x'
260 FROM IGS_PE_PERSON_ID_TYP pit
261 WHERE pit.person_id_type = cp_person_id_type AND
262 pit.s_person_id_type = cst_pay_adv_no AND
263 pit.closed_ind = 'N';
264
265 CURSOR sys_pit_cur (cp_person_id_type IGS_PE_PERSON_ID_TYP.person_id_type%TYPE) IS
266 SELECT pit.s_person_id_type
267 FROM igs_pe_person_id_typ pit
268 WHERE pit.person_id_type = cp_person_id_type;
269
270 l_s_person_id_type IGS_PE_PERSON_ID_TYP.s_person_id_type%TYPE;
271
272 BEGIN
273 -- Validate the alternate IGS_PE_PERSON id when a 'PAY_ADV_NO' is unique.
274 IF p_inserting OR p_updating THEN
275
276 OPEN sys_pit_cur(new_references.person_id_type);
277 FETCH sys_pit_cur INTO l_s_person_id_type;
278 CLOSE sys_pit_cur;
279
280 IF l_s_person_id_type = 'PAY_ADV_NO' THEN
281 -- Validate the alternate person id when a 'PAY_ADV_NO' is unique.
282 OPEN c_pit (new_references.person_id_type);
283 FETCH c_pit INTO v_dummy;
284 IF (c_pit%FOUND) THEN
285 CLOSE c_pit;
286 IF IGS_EN_VAL_API.enrp_val_api_pan (
287 new_references.pe_person_id,
288 new_references.api_person_id,
289 v_message_name) = FALSE THEN
290 Fnd_Message.Set_Name('IGS', v_message_name);
291 IGS_GE_MSG_STACK.ADD;
292 APP_EXCEPTION.RAISE_EXCEPTION;
293 END IF;
294 ELSE
295 CLOSE c_pit;
296 END IF;
297 END IF;
298
299
300 IF l_s_person_id_type <> 'SSN' THEN
301 IF NOT igs_en_val_api.val_overlap_api(new_references.pe_person_id) THEN
302 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PERS_ID_PRD_OVRLP');
303 IGS_GE_MSG_STACK.ADD;
304 APP_EXCEPTION.RAISE_EXCEPTION;
305 END IF;
306 ELSE
307 IF NOT igs_en_val_api.val_ssn_overlap_api(new_references.pe_person_id) THEN
308 FND_MESSAGE.SET_NAME('IGS','IGS_PE_SSN_PERS_ID_PRD_OVRLP');
309 IGS_GE_MSG_STACK.ADD;
310 APP_EXCEPTION.RAISE_EXCEPTION;
311 END IF;
312 END IF;
313 END IF;
314
315 END AfterRowInsertUpdate2;
316
317 PROCEDURE BeforeInsert IS
318 ------------------------------------------------------------------------------------------
319 --Created by : pkpatel
320 --Date created: 06-JUN-2002
321 --
322 --Purpose: Bug No: 2402077. Modified to show the message to which person the Alternate Person ID
323 -- is associate, whenever the uniqueness is violated for a Person ID Type with unique indicator
324 -- checked.
325 --Known limitations/enhancements and/or remarks:
326 --
327 --Change History:
328 --Who When What
329 --pkpatel 13-JAN-2003 Bug 2397876
330 -- Remove the FOR UPDATE NOWAIT
331 --pkpatel 3-APR-2003 Bug No: 2859277
332 -- Closed the cursor cptu for cursor%NOTFOUND condition
333 --ssawhney 7-sep-2004 Bug No: 3832912
334 -- introduced date check in the uniqueness cursor, cptu. uniqueness of alt id to be checked only for
335 -- active records, for all alt id types other than PAY_ADV_NO, not sure why though, checked no team uses this
336 -- s-alt-persid.
337 --askapoor 31-JAN-2005 Bug No: 3882788
338 -- Included condition start_dt <> end_dt or end_dt is null
339 -- Removed condition start_dt < end_dt and end_dt > start_dt
340 ----------------------------------------------------------------------------------------------
341 CURSOR pt IS
342 SELECT unique_ind
343 FROM igs_pe_person_id_typ
344 WHERE person_id_type = new_references.person_id_type AND
345 closed_ind = 'N';
346
347 CURSOR cptu IS
348 SELECT hz.party_number
349 FROM igs_pe_alt_pers_id alt, hz_parties hz
350 WHERE alt.person_id_type = new_references.person_id_type
351 AND alt.api_person_id = new_references.api_person_id
352 AND alt.pe_person_id <> new_references.pe_person_id
353 AND (alt.start_dt <> alt.end_dt OR alt.end_dt IS NULL)
354 AND alt.pe_person_id = hz.party_id;
355
356 lv_UniqueInd VARCHAR2(1);
357 l_person_number hz_parties.party_number%TYPE;
358
359 BEGIN
360 FOR pt_rec IN pt LOOP
361 lv_UniqueInd := pt_rec.unique_ind;
362 END LOOP;
363
364 IF NVL(lv_UniqueInd, 'N') = 'Y' THEN
365 Open cptu;
366 FETCH cptu INTO l_person_number;
367 IF (cptu%FOUND) THEN
368 Close cptu;
369 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_UNIQUE_PID');
370 FND_MESSAGE.SET_TOKEN ('PREF_ALTERNATE_ID1', new_references.person_id_type);
371 FND_MESSAGE.SET_TOKEN ('PREF_ALTERNATE_ID2', new_references.person_id_type);
372 FND_MESSAGE.SET_TOKEN ('PERSON_NUMBER', l_person_number);
373 IGS_GE_MSG_STACK.ADD;
374 APP_EXCEPTION.RAISE_EXCEPTION;
375 END IF;
376 Close cptu;
377 END IF;
378
379 END beforeinsert;
380
381 PROCEDURE Check_Constraints (
382 Column_Name IN VARCHAR2,
383 Column_Value IN VARCHAR2
384 )
385 as
386 BEGIN
387 IF column_name is null then
388 NULL;
389 ELSIF upper(Column_name) = 'API_PERSON_ID' then
390 new_references.api_person_id:= column_value;
391 ELSIF upper(Column_name) = 'PERSON_ID_TYPE' then
392 new_references. person_id_type := column_value;
393 END IF;
394
395 IF upper(column_name) = 'API_PERSON_ID' OR
396 column_name is null Then
397 IF new_references.api_person_id <> UPPER(new_references.api_person_id) Then
398 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
399 IGS_GE_MSG_STACK.ADD;
400 App_Exception.Raise_Exception;
401 END IF;
402 END IF;
403
404 IF upper(column_name) = 'PERSON_ID_TYPE' OR
405 column_name is null Then
406 IF new_references.person_id_type <>
407 UPPER(new_references.person_id_type ) Then
408 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
409 IGS_GE_MSG_STACK.ADD;
410 App_Exception.Raise_Exception;
411 END IF;
412 END IF;
413 END Check_Constraints;
414
415 PROCEDURE Check_Parent_Existance as
416 BEGIN
417 IF (((old_references.pe_person_id = new_references.pe_person_id)) OR
418 ((new_references.pe_person_id IS NULL))) THEN
419 NULL;
420 ELSE
421 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
422 new_references.pe_person_id ) THEN
423 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
424 IGS_GE_MSG_STACK.ADD;
425 App_Exception.Raise_Exception;
426 END IF;
427 END IF;
428 IF (((old_references.person_id_type = new_references.person_id_type)) OR
429 ((new_references.person_id_type IS NULL))) THEN
430 NULL;
431 ELSE
432 IF NOT IGS_PE_PERSON_ID_TYP_PKG.Get_PID_Type_Validation (
433 new_references.person_id_type) THEN
434 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
435 IGS_GE_MSG_STACK.ADD;
436 App_Exception.Raise_Exception;
437 END IF;
438 END IF;
439 END Check_Parent_Existance;
440
441 FUNCTION Get_PK_For_Validation (
442 x_pe_person_id IN NUMBER,
443 x_api_person_id IN VARCHAR2,
444 x_person_id_type IN VARCHAR2,
445 x_start_dt IN DATE
446 ) RETURN BOOLEAN as
447 ------------------------------------------------------------------------------------------
448 --Created by : pkpatel
449 --Date created: 06-JUN-2002
450 --
451 --Purpose:
452 --Known limitations/enhancements and/or remarks:
453 --
454 --Change History:
455 --Who When What
456 --kpadiyar 27-JAN-2003 Bug 2726415 - Added start_dt as part of the pk
457 ----------------------------------------------------------------------------------------------
458 CURSOR cur_rowid IS
459 SELECT rowid
460 FROM IGS_PE_ALT_PERS_ID
461 WHERE pe_person_id = x_pe_person_id
462 AND api_person_id = x_api_person_id
463 AND person_id_type = x_person_id_type
464 AND trunc(start_dt) = trunc(x_start_dt)
465 FOR UPDATE NOWAIT;
466 lv_rowid cur_rowid%RowType;
467 BEGIN
468 Open cur_rowid;
469 Fetch cur_rowid INTO lv_rowid;
470 IF (cur_rowid%FOUND) THEN
471 Close cur_rowid;
472 Return (TRUE);
473 ELSE
474 Close cur_rowid;
475 Return (FALSE);
476 END IF;
477 END Get_PK_For_Validation;
478
479 PROCEDURE GET_FK_IGS_PE_PERSON (
480 x_person_id IN VARCHAR2
481 ) as
482 CURSOR cur_rowid IS
483 SELECT rowid
484 FROM IGS_PE_ALT_PERS_ID
485 WHERE pe_person_id = x_person_id ;
486 lv_rowid cur_rowid%RowType;
487 BEGIN
488 Open cur_rowid;
489 Fetch cur_rowid INTO lv_rowid;
490 IF (cur_rowid%FOUND) THEN
491 Fnd_Message.Set_Name ('IGS', 'IGS_PE_API_PE_FK');
492 IGS_GE_MSG_STACK.ADD;
493 Close cur_rowid;
494 App_Exception.Raise_Exception;
495 Return;
496 END IF;
497 Close cur_rowid;
498 END GET_FK_IGS_PE_PERSON;
499
500 PROCEDURE Before_DML (
501 p_action IN VARCHAR2,
502 x_rowid IN VARCHAR2,
503 x_pe_person_id IN NUMBER,
504 x_api_person_id IN VARCHAR2,
505 X_API_PERSON_ID_UF IN VARCHAR2,
506 x_person_id_type IN VARCHAR2,
507 x_start_dt IN DATE,
508 x_end_dt IN DATE,
509 x_attribute_category IN VARCHAR2,
510 x_attribute1 IN VARCHAR2,
511 x_attribute2 IN VARCHAR2,
512 x_attribute3 IN VARCHAR2,
513 x_attribute4 IN VARCHAR2,
514 x_attribute5 IN VARCHAR2,
515 x_attribute6 IN VARCHAR2,
516 x_attribute7 IN VARCHAR2,
517 x_attribute8 IN VARCHAR2,
518 x_attribute9 IN VARCHAR2,
519 x_attribute10 IN VARCHAR2,
520 x_attribute11 IN VARCHAR2,
521 x_attribute12 IN VARCHAR2,
522 x_attribute13 IN VARCHAR2,
523 x_attribute14 IN VARCHAR2,
524 x_attribute15 IN VARCHAR2,
525 x_attribute16 IN VARCHAR2,
526 x_attribute17 IN VARCHAR2,
527 x_attribute18 IN VARCHAR2,
528 x_attribute19 IN VARCHAR2,
529 x_attribute20 IN VARCHAR2,
530 x_region_cd IN VARCHAR2,
531 x_creation_date IN DATE,
532 x_created_by IN NUMBER,
533 x_last_update_date IN DATE,
534 x_last_updated_by IN NUMBER,
535 x_last_update_login IN NUMBER
536 ) as
537 BEGIN
538 Set_Column_Values (
539 p_action,
540 x_rowid,
541 x_pe_person_id,
542 x_api_person_id,
543 x_api_person_id_uf,
544 x_person_id_type,
545 x_start_dt,
546 x_end_dt,
547 x_creation_date,
548 x_created_by,
549 x_last_update_date,
550 x_last_updated_by,
551 x_last_update_login ,
552 x_attribute_category,
553 x_attribute1 ,
554 x_attribute2 ,
555 x_attribute3 ,
556 x_attribute4 ,
557 x_attribute5 ,
558 x_attribute6 ,
559 x_attribute7 ,
560 x_attribute8 ,
561 x_attribute9 ,
562 x_attribute10 ,
563 x_attribute11 ,
564 x_attribute12 ,
565 x_attribute13 ,
566 x_attribute14 ,
567 x_attribute15 ,
568 x_attribute16 ,
569 x_attribute17 ,
570 x_attribute18 ,
571 x_attribute19 ,
572 x_attribute20 ,
573 x_region_cd
574 );
575 IF (p_action = 'INSERT') THEN
576 -- Call all the procedures related to Before Insert.
577 BeforeRowInsertUpdate1(
578 p_inserting => TRUE,
579 p_updating => FALSE,
580 p_deleting => FALSE
581 );
582
583 IF Get_PK_For_Validation (
584 new_references.pe_person_id ,
585 new_references.api_person_id ,
586 new_references.person_id_type,
587 new_references.start_dt )
588 THEN
589 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_ALT_DUP_EXISTS');
590 IGS_GE_MSG_STACK.ADD;
591 APP_EXCEPTION.RAISE_EXCEPTION;
592 END IF;
593
594 Check_Constraints; -- if procedure present
595 Check_Parent_Existance; -- if procedure present
596 BeforeInsert;
597
598 ELSIF (p_action = 'UPDATE') THEN
599 -- Call all the procedures related to Before Update.
600 BeforeRowInsertUpdate1(
601 p_inserting => FALSE,
602 p_updating => TRUE,
603 p_deleting => FALSE
604 );
605
606 Check_Constraints; -- if procedure present
607 Check_Parent_Existance; -- if procedure present
608
609 ELSIF (p_action = 'DELETE') THEN NULL;
610 -- Call all the procedures related to Before Delete.
611 NULL;
612
613 ELSIF (p_action = 'VALIDATE_INSERT') THEN
614
615 BeforeRowInsertUpdate1(
616 p_inserting => TRUE,
617 p_updating => FALSE,
618 p_deleting => FALSE
619 );
620
621 IF Get_PK_For_Validation (
622 new_references.pe_person_id ,
623 new_references.api_person_id ,
624 new_references.person_id_type,
625 new_references.start_dt
626 ) THEN
627 Fnd_Message.Set_Name ('IGS', 'IGS_PE_ALT_DUP_EXISTS');
628 IGS_GE_MSG_STACK.ADD;
629 App_Exception.Raise_Exception;
630 END IF;
631
632 Check_Constraints; -- if procedure present
633 BeforeInsert;
634 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
635
636 BeforeRowInsertUpdate1(
637 p_inserting => FALSE,
638 p_updating => TRUE,
639 p_deleting => FALSE
640 );
641
642 Check_Constraints; -- if procedure present
643
644
645 ELSIF (p_action = 'VALIDATE_DELETE') THEN
646 NULL;
647 END IF;
648 END Before_DML;
649
650 PROCEDURE After_DML (
651 p_action IN VARCHAR2,
652 x_rowid IN VARCHAR2
653 ) as
654 BEGIN
655 l_rowid := x_rowid;
656 IF (p_action = 'INSERT') THEN
657 -- Call all the procedures related to After Insert.
658 AfterRowInsertUpdate2 (
659 p_inserting => TRUE,
660 p_updating => FALSE,
661 p_deleting => FALSE
662 );
663 ELSIF (p_action = 'UPDATE') THEN
664 -- Call all the procedures related to After Update.
665 AfterRowInsertUpdate2 (
666 p_inserting => FALSE,
667 p_updating => TRUE,
668 p_deleting => FALSE
669 );
670 ELSIF (p_action = 'DELETE') THEN
671 -- Call all the procedures related to After Delete.
672 NULL;
673 END IF;
674 END After_DML;
675
676 procedure INSERT_ROW (
677 X_ROWID in out NOCOPY VARCHAR2,
678 X_PE_PERSON_ID in NUMBER,
679 X_API_PERSON_ID in VARCHAR2,
680 X_API_PERSON_ID_UF IN VARCHAR2,
681 X_PERSON_ID_TYPE in VARCHAR2,
682 X_START_DT in DATE,
683 X_END_DT in DATE,
684 x_attribute_category IN VARCHAR2,
685 x_attribute1 IN VARCHAR2,
686 x_attribute2 IN VARCHAR2,
687 x_attribute3 IN VARCHAR2,
688 x_attribute4 IN VARCHAR2,
689 x_attribute5 IN VARCHAR2,
690 x_attribute6 IN VARCHAR2,
691 x_attribute7 IN VARCHAR2,
692 x_attribute8 IN VARCHAR2,
693 x_attribute9 IN VARCHAR2,
694 x_attribute10 IN VARCHAR2,
695 x_attribute11 IN VARCHAR2,
696 x_attribute12 IN VARCHAR2,
697 x_attribute13 IN VARCHAR2,
698 x_attribute14 IN VARCHAR2,
699 x_attribute15 IN VARCHAR2,
700 x_attribute16 IN VARCHAR2,
701 x_attribute17 IN VARCHAR2,
702 x_attribute18 IN VARCHAR2,
703 x_attribute19 IN VARCHAR2,
704 x_attribute20 IN VARCHAR2,
705 x_region_cd IN VARCHAR2,
706 X_MODE IN VARCHAR2
707 ) as
708 cursor C is select ROWID from IGS_PE_ALT_PERS_ID
709 where PE_PERSON_ID = X_PE_PERSON_ID
710 and API_PERSON_ID = X_API_PERSON_ID
711 and PERSON_ID_TYPE = X_PERSON_ID_TYPE
712 and start_dt = x_start_dt;
713 X_LAST_UPDATE_DATE DATE;
714 X_LAST_UPDATED_BY NUMBER;
715 X_LAST_UPDATE_LOGIN NUMBER;
716 X_REQUEST_ID NUMBER;
717 X_PROGRAM_ID NUMBER;
718 X_PROGRAM_APPLICATION_ID NUMBER;
719 X_PROGRAM_UPDATE_DATE DATE;
720 begin
721 X_LAST_UPDATE_DATE := SYSDATE;
722 if(X_MODE = 'I') then
723 X_LAST_UPDATED_BY := 1;
724 X_LAST_UPDATE_LOGIN := 0;
725 elsif (X_MODE IN ('R', 'S')) then
726 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
727 if X_LAST_UPDATED_BY is NULL then
728 X_LAST_UPDATED_BY := -1;
729 end if;
730 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
731 if X_LAST_UPDATE_LOGIN is NULL then
732 X_LAST_UPDATE_LOGIN := -1;
733 end if;
734 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
735 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
736 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
737 if (X_REQUEST_ID = -1) then
738 X_REQUEST_ID := NULL;
739 X_PROGRAM_ID := NULL;
740 X_PROGRAM_APPLICATION_ID := NULL;
741 X_PROGRAM_UPDATE_DATE := NULL;
742 else
743 X_PROGRAM_UPDATE_DATE := SYSDATE;
744 end if;
745 else
746 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
747 IGS_GE_MSG_STACK.ADD;
748 app_exception.raise_exception;
749 end if;
750 Before_DML(
751 p_action=>'INSERT',
752 x_rowid=>X_ROWID,
753 x_api_person_id=>X_API_PERSON_ID,
754 x_api_person_id_uf=>X_API_PERSON_ID_UF,
755 x_end_dt=>X_END_DT,
756 x_pe_person_id=>X_PE_PERSON_ID,
757 x_person_id_type=>X_PERSON_ID_TYPE,
758 x_start_dt=>X_START_DT,
759 x_creation_date=>X_LAST_UPDATE_DATE,
760 x_created_by=>X_LAST_UPDATED_BY,
761 x_last_update_date=>X_LAST_UPDATE_DATE,
762 x_last_updated_by=>X_LAST_UPDATED_BY,
763 x_last_update_login=>X_LAST_UPDATE_LOGIN,
764 x_attribute_category => X_ATTRIBUTE_CATEGORY,
765 x_attribute1 => X_ATTRIBUTE1,
766 x_attribute2 => X_ATTRIBUTE2,
767 x_attribute3 => X_ATTRIBUTE3,
768 x_attribute4 => X_ATTRIBUTE4,
769 x_attribute5 => X_ATTRIBUTE5,
770 x_attribute6 => X_ATTRIBUTE6,
771 x_attribute7 => X_ATTRIBUTE7,
772 x_attribute8 => X_ATTRIBUTE8,
773 x_attribute9 => X_ATTRIBUTE9,
774 x_attribute10 => X_ATTRIBUTE10,
775 x_attribute11 => X_ATTRIBUTE11,
776 x_attribute12 => X_ATTRIBUTE12,
777 x_attribute13 => X_ATTRIBUTE13,
778 x_attribute14 => X_ATTRIBUTE14,
779 x_attribute15 => X_ATTRIBUTE15,
780 x_attribute16 => X_ATTRIBUTE16,
781 x_attribute17 => X_ATTRIBUTE17,
782 x_attribute18 => X_ATTRIBUTE18,
783 x_attribute19 => X_ATTRIBUTE19,
784 x_attribute20 => X_ATTRIBUTE20,
785 x_region_cd => X_region_cd
786 );
787 IF (x_mode = 'S') THEN
788 igs_sc_gen_001.set_ctx('R');
789 END IF;
790 insert into IGS_PE_ALT_PERS_ID (
791 PE_PERSON_ID,
792 API_PERSON_ID,
793 API_PERSON_ID_UF,
794 PERSON_ID_TYPE,
795 START_DT,
796 END_DT,
797 CREATION_DATE,
798 CREATED_BY,
799 LAST_UPDATE_DATE,
800 LAST_UPDATED_BY,
801 LAST_UPDATE_LOGIN,
802 REQUEST_ID,
803 PROGRAM_ID,
804 PROGRAM_APPLICATION_ID,
805 PROGRAM_UPDATE_DATE ,
806 ATTRIBUTE_CATEGORY,
807 ATTRIBUTE1 ,
808 ATTRIBUTE2 ,
809 ATTRIBUTE3 ,
810 ATTRIBUTE4 ,
811 ATTRIBUTE5 ,
812 ATTRIBUTE6 ,
813 ATTRIBUTE7 ,
814 ATTRIBUTE8 ,
815 ATTRIBUTE9 ,
816 ATTRIBUTE10 ,
817 ATTRIBUTE11 ,
818 ATTRIBUTE12 ,
819 ATTRIBUTE13 ,
820 ATTRIBUTE14 ,
821 ATTRIBUTE15 ,
822 ATTRIBUTE16 ,
823 ATTRIBUTE17 ,
824 ATTRIBUTE18 ,
825 ATTRIBUTE19 ,
826 ATTRIBUTE20 ,
827 REGION_CD
828 ) values (
829 NEW_REFERENCES.PE_PERSON_ID,
830 NEW_REFERENCES.API_PERSON_ID,
831 NEW_REFERENCES.API_PERSON_ID_UF,
832 NEW_REFERENCES.PERSON_ID_TYPE,
833 NEW_REFERENCES.START_DT,
834 NEW_REFERENCES.END_DT,
835 X_LAST_UPDATE_DATE,
836 X_LAST_UPDATED_BY,
837 X_LAST_UPDATE_DATE,
838 X_LAST_UPDATED_BY,
839 X_LAST_UPDATE_LOGIN,
840 X_REQUEST_ID,
841 X_PROGRAM_ID,
842 X_PROGRAM_APPLICATION_ID,
843 X_PROGRAM_UPDATE_DATE,
844 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
845 NEW_REFERENCES.ATTRIBUTE1 ,
846 NEW_REFERENCES.ATTRIBUTE2 ,
847 NEW_REFERENCES.ATTRIBUTE3 ,
848 NEW_REFERENCES.ATTRIBUTE4 ,
849 NEW_REFERENCES.ATTRIBUTE5 ,
850 NEW_REFERENCES.ATTRIBUTE6 ,
851 NEW_REFERENCES.ATTRIBUTE7 ,
852 NEW_REFERENCES.ATTRIBUTE8 ,
853 NEW_REFERENCES.ATTRIBUTE9 ,
854 NEW_REFERENCES.ATTRIBUTE10 ,
855 NEW_REFERENCES.ATTRIBUTE11 ,
856 NEW_REFERENCES.ATTRIBUTE12 ,
857 NEW_REFERENCES.ATTRIBUTE13 ,
858 NEW_REFERENCES.ATTRIBUTE14 ,
859 NEW_REFERENCES.ATTRIBUTE15 ,
860 NEW_REFERENCES.ATTRIBUTE16 ,
861 NEW_REFERENCES.ATTRIBUTE17 ,
862 NEW_REFERENCES.ATTRIBUTE18 ,
863 NEW_REFERENCES.ATTRIBUTE19 ,
864 NEW_REFERENCES.ATTRIBUTE20 ,
865 NEW_REFERENCES.REGION_CD
866 )RETURNING ROWID INTO X_ROWID;
867 IF (x_mode = 'S') THEN
868 igs_sc_gen_001.unset_ctx('R');
869 END IF;
870 -- Adding the returning clause for bug 4188189
871 -- Commenting out the following cursor fetch lines for bug 4188189
872 --open c;
873 --fetch c into X_ROWID;
874 --if (c%notfound) then
875 -- close c;
876 -- raise no_data_found;
877 --end if;
878 --close c;
879 After_DML(
880 p_action => 'INSERT',
881 x_rowid => X_ROWID
882 );
883 EXCEPTION
884 WHEN OTHERS THEN
885 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
886 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
887 fnd_message.set_token ('ERR_CD', SQLCODE);
888 igs_ge_msg_stack.add;
889 igs_sc_gen_001.unset_ctx('R');
890 app_exception.raise_exception;
891 ELSE
892 igs_sc_gen_001.unset_ctx('R');
893 RAISE;
894 END IF;
895
896 end INSERT_ROW;
897 procedure LOCK_ROW (
898 X_ROWID in VARCHAR2,
899 X_PE_PERSON_ID in NUMBER,
900 X_API_PERSON_ID in VARCHAR2,
901 X_API_PERSON_ID_UF IN VARCHAR2,
902 X_PERSON_ID_TYPE in VARCHAR2,
903 X_START_DT in DATE,
904 X_END_DT in DATE,
905 x_attribute_category IN VARCHAR2,
906 x_attribute1 IN VARCHAR2,
907 x_attribute2 IN VARCHAR2,
908 x_attribute3 IN VARCHAR2,
909 x_attribute4 IN VARCHAR2,
910 x_attribute5 IN VARCHAR2,
911 x_attribute6 IN VARCHAR2,
912 x_attribute7 IN VARCHAR2,
913 x_attribute8 IN VARCHAR2,
914 x_attribute9 IN VARCHAR2,
915 x_attribute10 IN VARCHAR2,
916 x_attribute11 IN VARCHAR2,
917 x_attribute12 IN VARCHAR2,
918 x_attribute13 IN VARCHAR2,
919 x_attribute14 IN VARCHAR2,
920 x_attribute15 IN VARCHAR2,
921 x_attribute16 IN VARCHAR2,
922 x_attribute17 IN VARCHAR2,
923 x_attribute18 IN VARCHAR2,
924 x_attribute19 IN VARCHAR2,
925 x_attribute20 IN VARCHAR2,
926 x_region_cd IN VARCHAR2
927
928 ) as
929 cursor c1 is select
930 API_PERSON_ID_UF,
931 START_DT,
932 END_DT,
933 ATTRIBUTE_CATEGORY,
934 ATTRIBUTE1 ,
935 ATTRIBUTE2 ,
936 ATTRIBUTE3 ,
937 ATTRIBUTE4 ,
938 ATTRIBUTE5 ,
939 ATTRIBUTE6 ,
940 ATTRIBUTE7 ,
941 ATTRIBUTE8 ,
942 ATTRIBUTE9 ,
943 ATTRIBUTE10 ,
944 ATTRIBUTE11 ,
945 ATTRIBUTE12 ,
946 ATTRIBUTE13 ,
947 ATTRIBUTE14 ,
948 ATTRIBUTE15 ,
949 ATTRIBUTE16 ,
950 ATTRIBUTE17 ,
951 ATTRIBUTE18 ,
952 ATTRIBUTE19 ,
953 ATTRIBUTE20 ,
954 REGION_CD
955 from IGS_PE_ALT_PERS_ID
956 where ROWID = X_ROWID
957 for update nowait;
958 tlinfo c1%rowtype;
959 begin
960 open c1;
961 fetch c1 into tlinfo;
962 if (c1%notfound) then
963 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
964
965 close c1;
966 App_Exception.Raise_Exception;
967 return;
968 end if;
969 close c1;
970
971 if ( ((trunc(tlinfo.START_DT) = trunc(X_START_DT))
972 OR ((tlinfo.START_DT is null)
973 AND (X_START_DT is null)))
974 AND ((trunc(tlinfo.END_DT) = trunc(X_END_DT))
975 OR ((tlinfo.END_DT is null)
976 AND (X_END_DT is null)))
977 AND (( tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY) OR (( tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
978 AND (( tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1) OR (( tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
979 AND (( tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2) OR (( tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
980 AND (( tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3) OR (( tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
981 AND (( tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4) OR (( tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
982 AND (( tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5) OR (( tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
983 AND (( tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6) OR (( tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
984 AND (( tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7) OR (( tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
985 AND (( tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8) OR (( tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
986 AND (( tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9) OR (( tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
987 AND (( tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10) OR (( tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
988 AND (( tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11) OR (( tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
989 AND (( tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12) OR (( tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
990 AND (( tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13) OR (( tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
991 AND (( tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14) OR (( tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
992 AND (( tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15) OR (( tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
993 AND (( tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16) OR (( tlinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
994 AND (( tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17) OR (( tlinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
995 AND (( tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18) OR (( tlinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
996 AND (( tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19) OR (( tlinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
997 AND (( tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20) OR (( tlinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
998 AND (( tlinfo.REGION_CD = X_REGION_CD) OR (( tlinfo.REGION_CD is null) AND (X_REGION_CD is null)))
999
1000 ) then
1001 null;
1002 else
1003 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1004 app_exception.raise_exception;
1005 end if;
1006 return;
1007 end LOCK_ROW;
1008 procedure UPDATE_ROW (
1009 X_ROWID in VARCHAR2,
1010 X_PE_PERSON_ID in NUMBER,
1011 X_API_PERSON_ID in VARCHAR2,
1012 X_API_PERSON_ID_UF IN VARCHAR2,
1013 X_PERSON_ID_TYPE in VARCHAR2,
1014 X_START_DT in DATE,
1015 X_END_DT in DATE,
1016 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1017 X_ATTRIBUTE1 in VARCHAR2,
1018 X_ATTRIBUTE2 in VARCHAR2,
1019 X_ATTRIBUTE3 in VARCHAR2,
1020 X_ATTRIBUTE4 in VARCHAR2,
1021 X_ATTRIBUTE5 in VARCHAR2,
1022 X_ATTRIBUTE6 in VARCHAR2,
1023 X_ATTRIBUTE7 in VARCHAR2,
1024 X_ATTRIBUTE8 in VARCHAR2,
1025 X_ATTRIBUTE9 in VARCHAR2,
1026 X_ATTRIBUTE10 in VARCHAR2,
1027 X_ATTRIBUTE11 in VARCHAR2,
1028 X_ATTRIBUTE12 in VARCHAR2,
1029 X_ATTRIBUTE13 in VARCHAR2,
1030 X_ATTRIBUTE14 in VARCHAR2,
1031 X_ATTRIBUTE15 in VARCHAR2,
1032 X_ATTRIBUTE16 in VARCHAR2,
1033 X_ATTRIBUTE17 in VARCHAR2,
1034 X_ATTRIBUTE18 in VARCHAR2,
1035 X_ATTRIBUTE19 in VARCHAR2,
1036 X_ATTRIBUTE20 in VARCHAR2,
1037 x_region_cd IN VARCHAR2,
1038 X_MODE in VARCHAR2
1039 ) as
1040 X_LAST_UPDATE_DATE DATE;
1041 X_LAST_UPDATED_BY NUMBER;
1042 X_LAST_UPDATE_LOGIN NUMBER;
1043 X_REQUEST_ID NUMBER;
1044 X_PROGRAM_ID NUMBER;
1045 X_PROGRAM_APPLICATION_ID NUMBER;
1046 X_PROGRAM_UPDATE_DATE DATE;
1047 begin
1048 X_LAST_UPDATE_DATE := SYSDATE;
1049 if(X_MODE = 'I') then
1050 X_LAST_UPDATED_BY := 1;
1051 X_LAST_UPDATE_LOGIN := 0;
1052 elsif (X_MODE IN ('R', 'S')) then
1053 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1054 if X_LAST_UPDATED_BY is NULL then
1055 X_LAST_UPDATED_BY := -1;
1056 end if;
1057 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1058 if X_LAST_UPDATE_LOGIN is NULL then
1059 X_LAST_UPDATE_LOGIN := -1;
1060 end if;
1061 else
1062 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1063 IGS_GE_MSG_STACK.ADD;
1064 app_exception.raise_exception;
1065 end if;
1066 Before_DML(
1067 p_action=>'UPDATE',
1068 x_rowid=>X_ROWID,
1069 x_api_person_id=>X_API_PERSON_ID,
1070 x_api_person_id_uf=>X_API_PERSON_ID_UF,
1071 x_end_dt=>X_END_DT,
1072 x_pe_person_id=>X_PE_PERSON_ID,
1073 x_person_id_type=>X_PERSON_ID_TYPE,
1074 x_start_dt=>X_START_DT,
1075 x_creation_date=>X_LAST_UPDATE_DATE,
1076 x_created_by=>X_LAST_UPDATED_BY,
1077 x_last_update_date=>X_LAST_UPDATE_DATE,
1078 x_last_updated_by=>X_LAST_UPDATED_BY,
1079 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1080 x_attribute_category => X_ATTRIBUTE_CATEGORY,
1081 x_attribute1 => X_ATTRIBUTE1,
1082 x_attribute2 => X_ATTRIBUTE2,
1083 x_attribute3 => X_ATTRIBUTE3,
1084 x_attribute4 => X_ATTRIBUTE4,
1085 x_attribute5 => X_ATTRIBUTE5,
1086 x_attribute6 => X_ATTRIBUTE6,
1087 x_attribute7 => X_ATTRIBUTE7,
1088 x_attribute8 => X_ATTRIBUTE8,
1089 x_attribute9 => X_ATTRIBUTE9,
1090 x_attribute10 => X_ATTRIBUTE10,
1091 x_attribute11 => X_ATTRIBUTE11,
1092 x_attribute12 => X_ATTRIBUTE12,
1093 x_attribute13 => X_ATTRIBUTE13,
1094 x_attribute14 => X_ATTRIBUTE14,
1095 x_attribute15 => X_ATTRIBUTE15,
1096 x_attribute16 => X_ATTRIBUTE16,
1097 x_attribute17 => X_ATTRIBUTE17,
1098 x_attribute18 => X_ATTRIBUTE18,
1099 x_attribute19 => X_ATTRIBUTE19,
1100 x_attribute20 => X_ATTRIBUTE20,
1101 x_region_cd => X_REGION_CD
1102
1103 );
1104 if (X_MODE IN ('R', 'S')) then
1105 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1106 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1107 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1108 if (X_REQUEST_ID = -1) then
1109 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1110 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1111 X_PROGRAM_APPLICATION_ID :=
1112 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1113 X_PROGRAM_UPDATE_DATE :=
1114 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1115 else
1116 X_PROGRAM_UPDATE_DATE := SYSDATE;
1117 end if;
1118 end if;
1119 IF (x_mode = 'S') THEN
1120 igs_sc_gen_001.set_ctx('R');
1121 END IF;
1122 update IGS_PE_ALT_PERS_ID set
1123 API_PERSON_ID_UF = NEW_REFERENCES.API_PERSON_ID_UF,
1124 START_DT = NEW_REFERENCES.START_DT,
1125 END_DT = NEW_REFERENCES.END_DT,
1126 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1127 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1128 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1129 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1130 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1131 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1132 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1133 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1134 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1135 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1136 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1137 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1138 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1139 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1140 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1141 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1142 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1143 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1144 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1145 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1146 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1147 REGION_CD = NEW_REFERENCES.REGION_CD,
1148 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1149 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1150 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1151 REQUEST_ID = X_REQUEST_ID,
1152 PROGRAM_ID = X_PROGRAM_ID,
1153 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1154 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1155
1156 where ROWID = X_ROWID
1157 ;
1158 if (sql%notfound) then
1159 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1160 igs_ge_msg_stack.add;
1161 igs_sc_gen_001.unset_ctx('R');
1162 app_exception.raise_exception;
1163 end if;
1164 IF (x_mode = 'S') THEN
1165 igs_sc_gen_001.unset_ctx('R');
1166 END IF;
1167
1168 After_DML(
1169 p_action => 'UPDATE',
1170 x_rowid => X_ROWID
1171 );
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 IF (SQLCODE = (-28115)) THEN
1175 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1176 fnd_message.set_token ('ERR_CD', SQLCODE);
1177 igs_ge_msg_stack.add;
1178 igs_sc_gen_001.unset_ctx('R');
1179 app_exception.raise_exception;
1180 ELSE
1181 igs_sc_gen_001.unset_ctx('R');
1182 RAISE;
1183 END IF;
1184
1185 end UPDATE_ROW;
1186 procedure ADD_ROW (
1187 X_ROWID in out NOCOPY VARCHAR2,
1188 X_PE_PERSON_ID in NUMBER,
1189 X_API_PERSON_ID in VARCHAR2,
1190 X_API_PERSON_ID_UF IN VARCHAR2,
1191 X_PERSON_ID_TYPE in VARCHAR2,
1192 X_START_DT in DATE,
1193 X_END_DT in DATE,
1194 x_attribute_category IN VARCHAR2,
1195 x_attribute1 IN VARCHAR2,
1196 x_attribute2 IN VARCHAR2,
1197 x_attribute3 IN VARCHAR2,
1198 x_attribute4 IN VARCHAR2,
1199 x_attribute5 IN VARCHAR2,
1200 x_attribute6 IN VARCHAR2,
1201 x_attribute7 IN VARCHAR2,
1202 x_attribute8 IN VARCHAR2,
1203 x_attribute9 IN VARCHAR2,
1204 x_attribute10 IN VARCHAR2,
1205 x_attribute11 IN VARCHAR2,
1206 x_attribute12 IN VARCHAR2,
1207 x_attribute13 IN VARCHAR2,
1208 x_attribute14 IN VARCHAR2,
1209 x_attribute15 IN VARCHAR2,
1210 x_attribute16 IN VARCHAR2,
1211 x_attribute17 IN VARCHAR2,
1212 x_attribute18 IN VARCHAR2,
1213 x_attribute19 IN VARCHAR2,
1214 x_attribute20 IN VARCHAR2,
1215 x_region_cd IN VARCHAR2,
1216 X_MODE in VARCHAR2
1217 ) as
1218 cursor c1 is select rowid from IGS_PE_ALT_PERS_ID
1219 where PE_PERSON_ID = X_PE_PERSON_ID
1220 and API_PERSON_ID = X_API_PERSON_ID
1221 and PERSON_ID_TYPE = X_PERSON_ID_TYPE
1222 and start_dt = x_start_dt
1223 ;
1224 begin
1225 open c1;
1226 fetch c1 into X_ROWID;
1227 if (c1%notfound) then
1228 close c1;
1229 INSERT_ROW (
1230 X_ROWID,
1231 X_PE_PERSON_ID,
1232 X_API_PERSON_ID,
1233 X_API_PERSON_ID_UF,
1234 X_PERSON_ID_TYPE,
1235 X_START_DT,
1236 X_END_DT,
1237 X_ATTRIBUTE_CATEGORY,
1238 X_ATTRIBUTE1,
1239 X_ATTRIBUTE2,
1240 X_ATTRIBUTE3,
1241 X_ATTRIBUTE4,
1242 X_ATTRIBUTE5,
1243 X_ATTRIBUTE6,
1244 X_ATTRIBUTE7,
1245 X_ATTRIBUTE8,
1246 X_ATTRIBUTE9,
1247 X_ATTRIBUTE10,
1248 X_ATTRIBUTE11,
1249 X_ATTRIBUTE12,
1250 X_ATTRIBUTE13,
1251 X_ATTRIBUTE14,
1252 X_ATTRIBUTE15,
1253 X_ATTRIBUTE16,
1254 X_ATTRIBUTE17,
1255 X_ATTRIBUTE18,
1256 X_ATTRIBUTE19,
1257 X_ATTRIBUTE20,
1258 X_REGION_CD,
1259 X_MODE);
1260 return;
1261 end if;
1262 close c1;
1263 UPDATE_ROW (
1264 X_ROWID,
1265 X_PE_PERSON_ID,
1266 X_API_PERSON_ID,
1267 X_API_PERSON_ID_UF,
1268 X_PERSON_ID_TYPE,
1269 X_START_DT,
1270 X_END_DT,
1271 X_ATTRIBUTE_CATEGORY,
1272 X_ATTRIBUTE1,
1273 X_ATTRIBUTE2,
1274 X_ATTRIBUTE3,
1275 X_ATTRIBUTE4,
1276 X_ATTRIBUTE5,
1277 X_ATTRIBUTE6,
1278 X_ATTRIBUTE7,
1279 X_ATTRIBUTE8,
1280 X_ATTRIBUTE9,
1281 X_ATTRIBUTE10,
1282 X_ATTRIBUTE11,
1283 X_ATTRIBUTE12,
1284 X_ATTRIBUTE13,
1285 X_ATTRIBUTE14,
1286 X_ATTRIBUTE15,
1287 X_ATTRIBUTE16,
1288 X_ATTRIBUTE17,
1289 X_ATTRIBUTE18,
1290 X_ATTRIBUTE19,
1291 X_ATTRIBUTE20,
1292 X_REGION_CD,
1293 X_MODE);
1294 end ADD_ROW;
1295 procedure DELETE_ROW (
1296 X_ROWID in VARCHAR2,
1297 x_mode IN VARCHAR2
1298 ) as
1299 begin
1300 Before_DML(
1301 p_action => 'DELETE',
1302 x_rowid => X_ROWID
1303 );
1304 IF (x_mode = 'S') THEN
1305 igs_sc_gen_001.set_ctx('R');
1306 END IF;
1307 delete from IGS_PE_ALT_PERS_ID
1308 where ROWID = X_ROWID;
1309 if (sql%notfound) then
1310 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1311 igs_ge_msg_stack.add;
1312 igs_sc_gen_001.unset_ctx('R');
1313 app_exception.raise_exception;
1314 end if;
1315 IF (x_mode = 'S') THEN
1316 igs_sc_gen_001.unset_ctx('R');
1317 END IF;
1318
1319 After_DML(
1320 p_action => 'DELETE',
1321 x_rowid => X_ROWID
1322 );
1323 end DELETE_ROW;
1324 end IGS_PE_ALT_PERS_ID_PKG;