[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_PERSON_PKG
Source
1 PACKAGE BODY Igs_Pe_Person_PKG AS
2 /* $Header: IGSNI19B.pls 120.6 2006/01/20 05:42:17 skpandey ship $ */
3
4 ------------------------------------------------------------------
5 -- Change History
6 --
7 -- Bug ID : 2000408
8 -- who when what
9 -- CDCRUZ Sep 24,2002 Modified calls to IGS_PE_ALT_PERS_ID_PKG
10 -- vrathi 2003/07/11 Bug No:3045079 The sysdate should be truncated before inserting into
11 -- IGS_PE_ALT_PERS_ID to eliminate the time component
12 -- asbala 13-APR-2004 Removed DEFAULT NULL from the parameters of Set_Column_Values procedure
13 -- since the parameters of Before_Dml are DEFAULT NULL
14 ------------------------------------------------------------------
15
16 l_rowid VARCHAR2(25);
17 old_references igs_pe_person%ROWTYPE;
18 new_references igs_pe_person%ROWTYPE;
19 v_person_rec hz_party_v2pub.person_rec_type;
20
21 PROCEDURE Set_Column_Values (
22 p_action IN VARCHAR2,
23 x_rowid IN VARCHAR2,
24 x_person_id IN NUMBER,
25 x_person_number IN VARCHAR2,
26 x_surname IN VARCHAR2,
27 x_middle_name IN VARCHAR2,
28 x_given_names IN VARCHAR2,
29 x_sex IN VARCHAR2,
30 x_title IN VARCHAR2,
31 x_staff_member_ind IN VARCHAR2,
32 x_deceased_ind IN VARCHAR2,
33 x_suffix IN VARCHAR2,
34 x_pre_name_adjunct IN VARCHAR2,
35 x_archive_exclusion_ind IN VARCHAR2,
36 x_archive_dt IN DATE,
37 x_purge_exclusion_ind IN VARCHAR2,
38 x_purge_dt IN DATE,
39 x_deceased_date IN DATE,
40 x_proof_of_ins IN VARCHAR2,
41 x_proof_of_immu IN VARCHAR2,
42 x_birth_dt IN DATE,
43 x_salutation IN VARCHAR2,
44 x_oracle_username IN VARCHAR2,
45 x_preferred_given_name IN VARCHAR2,
46 x_email_addr IN VARCHAR2,
47 x_level_of_qual_id IN NUMBER,
48 x_military_service_reg IN VARCHAR2,
49 x_veteran IN VARCHAR2,
50 x_hz_parties_ovn IN NUMBER,
51 x_status IN VARCHAR2,
52 x_attribute_category IN VARCHAR2,
53 x_attribute1 IN VARCHAR2,
54 x_attribute2 IN VARCHAR2,
55 x_attribute3 IN VARCHAR2,
56 x_attribute4 IN VARCHAR2,
57 x_attribute5 IN VARCHAR2,
58 x_attribute6 IN VARCHAR2,
59 x_attribute7 IN VARCHAR2,
60 x_attribute8 IN VARCHAR2,
61 x_attribute9 IN VARCHAR2,
62 x_attribute10 IN VARCHAR2,
63 x_attribute11 IN VARCHAR2,
64 x_attribute12 IN VARCHAR2,
65 x_attribute13 IN VARCHAR2,
66 x_attribute14 IN VARCHAR2,
67 x_attribute15 IN VARCHAR2,
68 x_attribute16 IN VARCHAR2,
69 x_attribute17 IN VARCHAR2,
70 x_attribute18 IN VARCHAR2,
71 x_attribute19 IN VARCHAR2,
72 x_attribute20 IN VARCHAR2,
73 x_creation_date IN DATE,
74 x_created_by IN NUMBER,
75 x_last_update_date IN DATE,
76 x_last_updated_by IN NUMBER,
77 x_last_update_login IN NUMBER,
78 x_attribute21 IN VARCHAR2 DEFAULT NULL,
79 x_attribute22 IN VARCHAR2 DEFAULT NULL,
80 x_attribute23 IN VARCHAR2 DEFAULT NULL,
81 x_attribute24 IN VARCHAR2 DEFAULT NULL
82 ) AS
83 /*************************************************************
84 Created By :
85 Date Created By :
86 Purpose :
87 Know limitations, enhancements or remarks
88 Change History
89 Who When What
90 sraj 2000/05/11 Changed according the new table structure.
91 (reverse chronological order - newest change first)
92 ***************************************************************/
93 CURSOR cur_old_ref_values IS
94 SELECT *
95 FROM IGS_PE_PERSON
96 WHERE row_id = x_rowid;
97 BEGIN
98 l_rowid := x_rowid;
99 -- Code for setting the Old and New Reference Values.
100 -- Populate Old Values.
101 OPEN cur_old_ref_values;
102 FETCH cur_old_ref_values INTO old_references;
103 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
104 CLOSE cur_old_ref_values;
105
106 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 RETURN;
110 END IF;
111 CLOSE cur_old_ref_values;
112 -- Populate New Values.
113
114 -- there are 2 record types in the p_person_rec of HZ_PARTY_PUB, one is party_rec and the other one is person_rec
115 -- so while creating a record type this should be very clearly note. SSAWHNEY
116
117 v_person_rec.party_rec.party_id := x_person_id;
118 v_person_rec.party_rec.party_number := x_person_number;
119 v_person_rec.person_pre_name_adjunct := x_pre_name_adjunct; -- ssawhney v2 API change
120 v_person_rec.person_first_name := x_given_names;
121 v_person_rec.person_middle_name := x_middle_name;
122 v_person_rec.person_last_name := x_surname;
123 v_person_rec.person_name_suffix := x_suffix;
124 v_person_rec.person_title := x_title;
125
126 v_person_rec.known_as := x_preferred_given_name;
127 v_person_rec.date_of_birth := x_birth_dt;
128 v_person_rec.date_of_death := x_deceased_date;
129 v_person_rec.gender := x_sex;
130
131 v_person_rec.party_rec.status := x_status;
132 v_person_rec.party_rec.attribute_category := x_attribute_category;
133 v_person_rec.party_rec.attribute1 := x_attribute1;
134 v_person_rec.party_rec.attribute2 := x_attribute2;
135 v_person_rec.party_rec.attribute3 := x_attribute3;
136 v_person_rec.party_rec.attribute4 := x_attribute4;
137 v_person_rec.party_rec.attribute5 := x_attribute5;
138 v_person_rec.party_rec.attribute6 := x_attribute6;
139 v_person_rec.party_rec.attribute7 := x_attribute7;
140 v_person_rec.party_rec.attribute8 := x_attribute8;
141 v_person_rec.party_rec.attribute9 := x_attribute9;
142 v_person_rec.party_rec.attribute10 := x_attribute10;
143 v_person_rec.party_rec.attribute11 := x_attribute11;
144 v_person_rec.party_rec.attribute12 := x_attribute12;
145 v_person_rec.party_rec.attribute13 := x_attribute13;
146 v_person_rec.party_rec.attribute14 := x_attribute14;
147 v_person_rec.party_rec.attribute15 := x_attribute15;
148 v_person_rec.party_rec.attribute16 := x_attribute16;
149 v_person_rec.party_rec.attribute17 := x_attribute17;
150 v_person_rec.party_rec.attribute18 := x_attribute18;
151 v_person_rec.party_rec.attribute19 := x_attribute19;
152 v_person_rec.party_rec.attribute20 := x_attribute20;
153 v_person_rec.party_rec.attribute21 := x_attribute21;
154 v_person_rec.party_rec.attribute22 := x_attribute22;
155 v_person_rec.party_rec.attribute23 := x_attribute23;
156 v_person_rec.party_rec.attribute24 := x_attribute24;
157
158 new_references.person_id := x_person_id;
159 new_references.person_number := x_person_number;
160 new_references.surname := x_surname;
161 new_references.middle_name := x_middle_name;
162 new_references.given_names := x_given_names;
163 new_references.sex := x_sex;
164 new_references.title := x_title;
165 new_references.staff_member_ind := x_staff_member_ind;
166 new_references.deceased_ind := x_deceased_ind;
167 new_references.suffix := x_suffix;
168 new_references.pre_name_adjunct := x_pre_name_adjunct;
169 new_references.archive_exclusion_ind := x_archive_exclusion_ind;
170 new_references.archive_dt := x_archive_dt;
171 new_references.purge_exclusion_ind := x_purge_exclusion_ind;
172 new_references.purge_dt := x_purge_dt;
173 new_references.deceased_date := x_deceased_date;
174 new_references.proof_of_ins := x_proof_of_ins;
175 new_references.proof_of_immu := x_proof_of_immu;
176 new_references.birth_dt := x_birth_dt;
177 new_references.salutation := x_salutation;
178 new_references.oracle_username := x_oracle_username;
179 new_references.preferred_given_name := x_preferred_given_name;
180 new_references.email_addr := x_email_addr;
181 new_references.level_of_qual_id := x_level_of_qual_id;
182 new_references.military_service_reg := x_military_service_reg;
183 new_references.veteran := x_veteran;
184 new_references.object_version_number := x_hz_parties_ovn;
185 new_references.status := x_status;
186 new_references.attribute_category := x_attribute_category;
187 new_references.attribute1 := x_attribute1;
188 new_references.attribute2 := x_attribute2;
189 new_references.attribute3 := x_attribute3;
190 new_references.attribute4 := x_attribute4;
191 new_references.attribute5 := x_attribute5;
192 new_references.attribute6 := x_attribute6;
193 new_references.attribute7 := x_attribute7;
194 new_references.attribute8 := x_attribute8;
195 new_references.attribute9 := x_attribute9;
196 new_references.attribute10 := x_attribute10;
197 new_references.attribute11 := x_attribute11;
198 new_references.attribute12 := x_attribute12;
199 new_references.attribute13 := x_attribute13;
200 new_references.attribute14 := x_attribute14;
201 new_references.attribute15 := x_attribute15;
202 new_references.attribute16 := x_attribute16;
203 new_references.attribute17 := x_attribute17;
204 new_references.attribute18 := x_attribute18;
205 new_references.attribute19 := x_attribute19;
206 new_references.attribute20 := x_attribute20;
207 new_references.attribute21 := x_attribute21;
208 new_references.attribute22 := x_attribute22;
209 new_references.attribute23 := x_attribute23;
210 new_references.attribute24 := x_attribute24;
211 IF (p_action = 'UPDATE') THEN
212 new_references.creation_date := old_references.creation_date;
213 new_references.created_by := old_references.created_by;
214 ELSE
215 new_references.creation_date := x_creation_date;
216 new_references.created_by := x_created_by;
217
218 END IF;
219 new_references.last_update_date := x_last_update_date;
220 new_references.last_updated_by := x_last_updated_by;
221 new_references.last_update_login := x_last_update_login;
222 END Set_Column_Values;
223
224
225 PROCEDURE Check_Parent_Existance AS
226 /*************************************************************
227 Created By :
228 Date Created By :
229 Purpose :
230 Know limitations, enhancements or remarks
231 Change History
232 Who When What
233 sraj 2000/05/05 the table structure has been changed
234 asbala 12-APR-2004 3313276: Validation for gender not done
235 against igs_lookup_values anymore.
236 (reverse chronological order - newest change first)
237 ***************************************************************/
238 BEGIN
239
240 NULL;
241
242 END Check_Parent_Existance;
243
244 FUNCTION Get_PK_For_Validation (
245 x_person_id IN NUMBER
246 ) RETURN BOOLEAN AS
247 /*************************************************************
248 Created By :
249 Date Created By :
250 Purpose :
251 Know limitations, enhancements or remarks
252 Change History
253 Who When What
254 skpandey 19-JAN-2006 Bug#4937960: Changed chk_igs_pe_hz_party definition to optimize query
255 sraj 2000/05/05 the table structure has been changed
256 mmkumar 18-Jul-2005 added a new parameter x_oss_org_unit_cd for party_number impact
257 (reverse chronological order - newest change first)
258 ***************************************************************/
259 CURSOR cur_rowid(cp_person_id hz_parties.party_id%TYPE) IS
260 SELECT rowid
261 FROM hz_parties
262 WHERE party_id = cp_person_id;
263
264 lv_rowid cur_rowid%ROWTYPE;
265
266 CURSOR chk_igs_pe_hz_party(cp_person_id hz_parties.party_id%TYPE) is
267 SELECT rowid
268 FROM igs_pe_hz_parties
269 WHERE party_id = cp_person_id;
270
271 pehz_rowid varchar2(25);
272
273 BEGIN
274 OPEN cur_rowid(x_person_id);
275 FETCH cur_rowid INTO lv_rowid;
276 IF (cur_rowid%FOUND) THEN
277 CLOSE cur_rowid;
278
279 -- check if the person exits in teh IGS_PE_HZ_PARTIES
280 OPEN chk_igs_pe_hz_party(x_person_id);
281 FETCH chk_igs_pe_hz_party INTO pehz_rowid;
282
283 -- if the person does not exist tehn insert with the minimum required data
284 IF (chk_igs_pe_hz_party%NOTFOUND) THEN
285
286 pehz_rowid := null;
287
288 IGS_PE_HZ_PARTIES_PKG.INSERT_ROW(
289 X_ROWID => pehz_rowid,
290 X_PARTY_ID => X_PERSON_ID,
291 X_DECEASED_IND => NULL,
292 X_ARCHIVE_EXCLUSION_IND => NULL,
293 X_ARCHIVE_DT => NULL,
294 X_PURGE_EXCLUSION_IND => NULL,
295 X_PURGE_DT => NULL,
296 X_ORACLE_USERNAME => NULL,
297 X_PROOF_OF_INS => NULL,
298 X_PROOF_OF_IMMU => NULL,
299 X_LEVEL_OF_QUAL => NULL,
300 X_MILITARY_SERVICE_REG => NULL,
301 X_VETERAN => NULL,
302 X_INSTITUTION_CD => NULL,
303 X_OI_LOCAL_INSTITUTION_IND => NULL,
304 X_OI_OS_IND => NULL,
305 X_OI_GOVT_INSTITUTION_CD => NULL,
306 X_OI_INST_CONTROL_TYPE => NULL,
307 X_OI_INSTITUTION_TYPE => NULL,
308 X_OI_INSTITUTION_STATUS => NULL,
309 X_OU_START_DT => NULL,
310 X_OU_END_DT => NULL,
311 X_OU_MEMBER_TYPE => NULL,
312 X_OU_ORG_STATUS => NULL,
313 X_OU_ORG_TYPE => NULL,
314 X_INST_ORG_IND => NULL,
315 X_FUND_AUTHORIZATION => NULL,
316 X_PE_INFO_VERIFY_TIME => NULL,
317 X_birth_city => NULL,
318 X_birth_country => NULL,
319 x_oss_org_unit_cd => NULL, --mmkumar, party_number impact
320 X_felony_convicted_flag => NULL,
321 X_MODE => 'R'
322 );
323
324 END IF;
325
326 CLOSE chk_igs_pe_hz_party;
327
328 RETURN(TRUE);
329 ELSE
330 CLOSE cur_rowid;
331 RETURN(FALSE);
332 END IF;
333 END Get_PK_For_Validation;
334
335 PROCEDURE Before_DML (
336 p_action IN VARCHAR2,
337 x_rowid IN VARCHAR2,
338 x_person_id IN NUMBER,
339 x_person_number IN VARCHAR2,
340 x_surname IN VARCHAR2,
341 x_middle_name IN VARCHAR2,
342 x_given_names IN VARCHAR2,
343 x_sex IN VARCHAR2,
344 x_title IN VARCHAR2,
345 x_staff_member_ind IN VARCHAR2,
346 x_deceased_ind IN VARCHAR2,
347 x_suffix IN VARCHAR2,
348 x_pre_name_adjunct IN VARCHAR2,
349 x_archive_exclusion_ind IN VARCHAR2,
350 x_archive_dt IN DATE,
351 x_purge_exclusion_ind IN VARCHAR2,
352 x_purge_dt IN DATE,
353 x_deceased_date IN DATE,
354 x_proof_of_ins IN VARCHAR2,
355 x_proof_of_immu IN VARCHAR2,
356 x_birth_dt IN DATE,
357 x_salutation IN VARCHAR2,
358 x_oracle_username IN VARCHAR2,
359 x_preferred_given_name IN VARCHAR2,
360 x_email_addr IN VARCHAR2,
361 x_level_of_qual_id IN NUMBER,
362 x_military_service_reg IN VARCHAR2,
363 x_veteran IN VARCHAR2,
364 x_hz_parties_ovn IN NUMBER,
365 x_status IN VARCHAR2,
366 x_attribute_category IN VARCHAR2,
367 x_attribute1 IN VARCHAR2,
368 x_attribute2 IN VARCHAR2,
369 x_attribute3 IN VARCHAR2,
370 x_attribute4 IN VARCHAR2,
371 x_attribute5 IN VARCHAR2,
372 x_attribute6 IN VARCHAR2,
373 x_attribute7 IN VARCHAR2,
374 x_attribute8 IN VARCHAR2,
375 x_attribute9 IN VARCHAR2,
376 x_attribute10 IN VARCHAR2,
377 x_attribute11 IN VARCHAR2,
378 x_attribute12 IN VARCHAR2,
379 x_attribute13 IN VARCHAR2,
380 x_attribute14 IN VARCHAR2,
381 x_attribute15 IN VARCHAR2,
382 x_attribute16 IN VARCHAR2,
383 x_attribute17 IN VARCHAR2,
384 x_attribute18 IN VARCHAR2,
385 x_attribute19 IN VARCHAR2,
386 x_attribute20 IN VARCHAR2,
387 x_creation_date IN DATE,
388 x_created_by IN NUMBER,
389 x_last_update_date IN DATE,
390 x_last_updated_by IN NUMBER,
391 x_last_update_login IN NUMBER,
392 x_attribute21 IN VARCHAR2,
393 x_attribute22 IN VARCHAR2,
394 x_attribute23 IN VARCHAR2,
395 x_attribute24 IN VARCHAR2
396 ) AS
397 /*************************************************************
398 Created By :
399 Date Created By :
400 Purpose :
401 Know limitations, enhancements or remarks
402 Change History
403 Who When What
404 sraj 2000/05/05 the table structure has been changed
405 (reverse chronological order - newest change first)
406 ***************************************************************/
407 BEGIN
408 Set_Column_Values (
409 p_action,
410 x_rowid,
411 x_person_id,
412 x_person_number,
413 x_surname,
414 x_middle_name,
415 x_given_names,
416 x_sex,
417 x_title,
418 x_staff_member_ind,
419 x_deceased_ind,
420 x_suffix,
421 x_pre_name_adjunct,
422 x_archive_exclusion_ind,
423 x_archive_dt,
424 x_purge_exclusion_ind,
425 x_purge_dt,
426 x_deceased_date,
427 x_proof_of_ins,
428 x_proof_of_immu,
429 x_birth_dt,
430 x_salutation,
431 x_oracle_username,
432 x_preferred_given_name,
433 x_email_addr,
434 x_level_of_qual_id,
435 x_military_service_reg,
436 x_veteran,
437 x_hz_parties_ovn,
438 x_status,
439 x_attribute_category,
440 x_attribute1,
441 x_attribute2,
442 x_attribute3,
443 x_attribute4,
444 x_attribute5,
445 x_attribute6,
446 x_attribute7,
447 x_attribute8,
448 x_attribute9,
449 x_attribute10,
450 x_attribute11,
451 x_attribute12,
452 x_attribute13,
453 x_attribute14,
454 x_attribute15,
455 x_attribute16,
456 x_attribute17,
457 x_attribute18,
458 x_attribute19,
459 x_attribute20,
460 x_creation_date,
461 x_created_by,
462 x_last_update_date,
463 x_last_updated_by,
464 x_last_update_login,
465 x_attribute21,
466 x_attribute22,
467 x_attribute23,
468 x_attribute24
469 );
470 --The following validations with Birth Date and Deceased Date have been moved here from plds to aviod duplication of
471 --code in IGSAD032, IGSEN022, IGSAD034, and IGSAD045. bug 2389837
472 IF (p_action IN ('INSERT','UPDATE')) THEN
473 IF(x_birth_dt IS NOT NULL AND x_birth_dt > sysdate) THEN
474 FND_MESSAGE.SET_NAME('IGS','IGS_AD_BIRTH_DT');
475 IGS_GE_MSG_STACK.ADD;
476 App_Exception.Raise_Exception;
477 END IF;
478 IF(x_deceased_date IS NOT NULL AND x_deceased_date > sysdate) THEN
479 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DEC_DT');
480 IGS_GE_MSG_STACK.ADD;
481 App_Exception.Raise_Exception;
482 END IF;
483 IF(x_deceased_date IS NOT NULL AND x_birth_dt IS NOT NULL AND x_deceased_date < x_birth_dt) THEN
484 Fnd_Message.Set_Name('IGS','IGS_AD_DEC_BIRTH_DT');
485 IGS_GE_MSG_STACK.ADD;
486 App_Exception.Raise_Exception;
487 END IF;
488 END IF;
489 IF (p_action = 'INSERT') THEN
490 -- Call all the procedures related to Before Insert.
491 IF Get_PK_For_Validation (
492 new_references.person_id ) THEN
493 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
494 IGS_GE_MSG_STACK.ADD;
495 App_Exception.Raise_Exception;
496 END IF;
497 Check_Parent_Existance; -- if procedure present
498 ELSIF (p_action = 'UPDATE') THEN
499 -- Call all the procedures related to Before Update.
500 Check_Parent_Existance; -- if procedure present
501 ELSIF (p_action = 'DELETE') THEN
502 -- Call all the procedures related to Before Delete.
503 NULL;
504 ELSIF (p_action = 'VALIDATE_INSERT') THEN
505 IF Get_PK_For_Validation (
506 new_references.person_id ) THEN
507 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
508 IGS_GE_MSG_STACK.ADD;
509 App_Exception.Raise_Exception;
510 END IF;
511 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
512 NULL;
513 ELSIF (p_action = 'VALIDATE_DELETE') THEN
514 NULL;
515 END IF;
516 END Before_DML;
517
518 PROCEDURE INSERT_ROW (
519 X_MSG_COUNT OUT NOCOPY NUMBER,
520 X_MSG_DATA OUT NOCOPY VARCHAR2,
521 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
522 X_ROWID IN OUT NOCOPY VARCHAR2,
523 x_PERSON_ID IN OUT NOCOPY NUMBER,
524 x_PERSON_NUMBER IN OUT NOCOPY VARCHAR2,
525 x_SURNAME IN VARCHAR2,
526 x_middle_name IN VARCHAR2,
527 x_GIVEN_NAMES IN VARCHAR2,
528 x_SEX IN VARCHAR2,
529 x_TITLE IN VARCHAR2,
530 x_STAFF_MEMBER_IND IN VARCHAR2,
531 x_DECEASED_IND IN VARCHAR2,
532 x_SUFFIX IN VARCHAR2,
533 x_pre_name_adjunct IN VARCHAR2,
534 x_ARCHIVE_EXCLUSION_IND IN VARCHAR2,
535 x_ARCHIVE_DT IN DATE,
536 x_PURGE_EXCLUSION_IND IN VARCHAR2,
537 x_PURGE_DT IN DATE,
538 x_DECEASED_DATE IN DATE,
539 x_PROOF_OF_INS IN VARCHAR2,
540 x_PROOF_OF_IMMU IN VARCHAR2,
541 x_BIRTH_DT IN DATE,
542 x_SALUTATION IN VARCHAR2,
543 x_ORACLE_USERNAME IN VARCHAR2,
544 x_PREFERRED_GIVEN_NAME IN VARCHAR2,
545 x_EMAIL_ADDR IN VARCHAR2,
546 x_LEVEL_OF_QUAL_ID IN NUMBER,
547 x_MILITARY_SERVICE_REG IN VARCHAR2,
548 x_VETERAN IN VARCHAR2,
549 x_HZ_PARTIES_OVN IN OUT NOCOPY NUMBER,
550 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
551 x_ATTRIBUTE1 IN VARCHAR2,
552 x_ATTRIBUTE2 IN VARCHAR2,
553 x_ATTRIBUTE3 IN VARCHAR2,
554 x_ATTRIBUTE4 IN VARCHAR2,
555 x_ATTRIBUTE5 IN VARCHAR2,
556 x_ATTRIBUTE6 IN VARCHAR2,
557 x_ATTRIBUTE7 IN VARCHAR2,
558 x_ATTRIBUTE8 IN VARCHAR2,
559 x_ATTRIBUTE9 IN VARCHAR2,
560 x_ATTRIBUTE10 IN VARCHAR2,
561 x_ATTRIBUTE11 IN VARCHAR2,
562 x_ATTRIBUTE12 IN VARCHAR2,
563 x_ATTRIBUTE13 IN VARCHAR2,
564 x_ATTRIBUTE14 IN VARCHAR2,
565 x_ATTRIBUTE15 IN VARCHAR2,
566 x_ATTRIBUTE16 IN VARCHAR2,
567 x_ATTRIBUTE17 IN VARCHAR2,
568 x_ATTRIBUTE18 IN VARCHAR2,
569 x_ATTRIBUTE19 IN VARCHAR2,
570 x_ATTRIBUTE20 IN VARCHAR2,
571 x_PERSON_ID_TYPE IN VARCHAR2,
572 x_API_PERSON_ID IN VARCHAR2,
573 X_STATUS IN VARCHAR2,
574 X_MODE IN VARCHAR2,
575 x_ATTRIBUTE21 IN VARCHAR2,
576 x_ATTRIBUTE22 IN VARCHAR2,
577 x_ATTRIBUTE23 IN VARCHAR2,
578 x_ATTRIBUTE24 IN VARCHAR2
579 ) AS
580 /*************************************************************
581 Created By :
582 Date Created By :
583 Purpose :
584 Know limitations, enhancements or remarks
585 Change History
586 Who When What
587 skpandey 19-JAN-2005 Bug#4937960
588 Changed c_pe_rowid cursor definition to optimize query
589 ssawhney feb15 customer creation removed. 2225917 SWCR008
590 sraj 2000/05/05 the table structure has been changed
591 kumma 23-MAY-2002 Added the condition in cursor ptc to fetch
592 only those record which are not closed., Bug # 2379840
593 (reverse chronological order - newest change first)
594 ***************************************************************/
595
596 lv_rowid VARCHAR2(30);
597 lv_rowid1 VARCHAR2(30);
598 ln_type_instance_id IGS_PE_TYP_INSTANCES.TYPE_INSTANCE_ID%TYPE;
599 x_profile_id HZ_PERSON_PROFILES.PERSON_PROFILE_ID%TYPE;
600 lv_Person_Type_Code IGS_PE_PERSON_TYPES.person_type_code%TYPE;
601
602 CURSOR c_pe_rowid (cp_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
603 SELECT ROWID
604 FROM HZ_PARTIES
605 WHERE party_id = cp_person_id ;
606
607 -- AND party_number = cp_person_number; -- ssawhney 2203778 this is not required, party no is the primary key.
608
609
610 CURSOR ptc IS
611 SELECT pt.person_type_code
612 FROM igs_pe_person_types pt
613 WHERE pt.system_type = 'OTHER'
614 AND pt.closed_ind = 'N';
615
616
617 l_tmp_var1 VARCHAR2(2000);
618 l_tmp_var VARCHAR2(2000);
619
620
621
622 -- Cursor to check if automatic customer numbering is ON/OFF. (tray,18-04-2001)
623 -- removed from OSS, SWCR008 : 2225917 : ssawhney
624
625 l_person_number hz_parties.party_number%TYPE ;
626 -- new parameter, this is the out NOCOPY parameter from hz_cust_account pub
627 -- ssawhney 2203778. HZ F api doesnt return the out NOCOPY parameter person_number
628 -- if its already passed. Bug with HZ F
629 BEGIN
630
631
632 Before_DML(
633 p_action=>'INSERT',
634 x_rowid=>X_ROWID,
635 x_person_id=>X_PERSON_ID,
636 x_person_number => X_PERSON_NUMBER,
637 x_surname=>X_SURNAME,
638 x_middle_name=>X_middle_name,
639 x_given_names=>X_GIVEN_NAMES,
640 x_sex=>X_SEX,
641 x_title=>X_TITLE,
642 x_staff_member_ind=>NVL(X_STAFF_MEMBER_IND,'N' ),
643 x_deceased_ind=>NVL(X_DECEASED_IND,'N' ),
644 x_suffix=>X_SUFFIX,
645 x_pre_name_adjunct=>X_pre_name_adjunct,
646 x_archive_exclusion_ind=>NVL(X_ARCHIVE_EXCLUSION_IND,'N' ),
647 x_archive_dt=>X_ARCHIVE_DT,
648 x_purge_exclusion_ind=>NVL(X_PURGE_EXCLUSION_IND,'N' ),
649 x_purge_dt=>X_PURGE_DT,
650 x_deceased_date=>X_DECEASED_DATE,
651 x_proof_of_ins=>X_PROOF_OF_INS,
652 x_proof_of_immu=>X_PROOF_OF_IMMU,
653 x_birth_dt=>X_BIRTH_DT,
654 x_salutation=>X_SALUTATION,
655 x_oracle_username=>X_ORACLE_USERNAME,
656 x_preferred_given_name=>X_PREFERRED_GIVEN_NAME,
657 x_email_addr=>X_EMAIL_ADDR,
658 x_level_of_qual_id =>X_LEVEL_OF_QUAL_ID,
659 x_military_service_reg=>X_MILITARY_SERVICE_REG,
660 x_veteran=>X_VETERAN,
661 x_hz_parties_ovn =>x_HZ_PARTIES_OVN,
662 x_status => x_status,
663 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
664 x_attribute1=>X_ATTRIBUTE1,
665 x_attribute2=>X_ATTRIBUTE2,
666 x_attribute3=>X_ATTRIBUTE3,
667 x_attribute4=>X_ATTRIBUTE4,
668 x_attribute5=>X_ATTRIBUTE5,
669 x_attribute6=>X_ATTRIBUTE6,
670 x_attribute7=>X_ATTRIBUTE7,
671 x_attribute8=>X_ATTRIBUTE8,
672 x_attribute9=>X_ATTRIBUTE9,
673 x_attribute10=>X_ATTRIBUTE10,
674 x_attribute11=>X_ATTRIBUTE11,
675 x_attribute12=>X_ATTRIBUTE12,
676 x_attribute13=>X_ATTRIBUTE13,
677 x_attribute14=>X_ATTRIBUTE14,
678 x_attribute15=>X_ATTRIBUTE15,
679 x_attribute16=>X_ATTRIBUTE16,
680 x_attribute17=>X_ATTRIBUTE17,
681 x_attribute18=>X_ATTRIBUTE18,
682 x_attribute19=>X_ATTRIBUTE19,
683 x_attribute20=>X_ATTRIBUTE20,
684 x_attribute21=>X_ATTRIBUTE21,
685 x_attribute22=>X_ATTRIBUTE22,
686 x_attribute23=>X_ATTRIBUTE23,
687 x_attribute24=>X_ATTRIBUTE24
688 );
689
690 -- these 2 new parameters are to be used only in case of INSERT.
691 v_person_rec.content_source_type:='USER_ENTERED';
692 v_person_rec.created_by_module :='IGS';
693
694 HZ_PARTY_V2PUB.CREATE_PERSON(
695
696 P_PERSON_REC => v_person_rec,
697 X_RETURN_STATUS => x_RETURN_STATUS,
698 X_MSG_COUNT => x_MSG_COUNT,
699 X_MSG_DATA => x_MSG_DATA,
700 X_PARTY_ID => x_PERSON_ID,
701 X_PARTY_NUMBER => x_PERSON_NUMBER,
702 X_PROFILE_ID => x_PROFILE_ID
703
704 );
705
706
707 IF X_RETURN_STATUS IN ('E','U') THEN
708 --code added by sbaliga as part of 2338473
709 IF x_msg_count > 1 THEN
710 FOR i IN 1..x_msg_count
711 LOOP
712 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
713 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
714 END LOOP;
715 x_msg_data := l_tmp_var1;
716 END IF;
717
718
719 ELSE
720
721
722 v_person_rec.party_rec.PARTY_ID := X_PERSON_ID;
723 v_person_rec.party_rec.PARTY_number := X_PERSON_number;
724 x_HZ_PARTIES_OVN := 1;
725
726
727 -- Code to resolve Design issue for bug#1700178 (tray,18-04-2001)
728 -- removed that code SSAWHNEY : 2225917
729 -- If party creation is successful, create records in OSS
730
731 IGS_PE_HZ_PARTIES_PKG.INSERT_ROW(
732 X_ROWID => l_rowid,
733 X_PARTY_ID => X_PERSON_ID,
734 X_DECEASED_IND => new_references.deceased_ind,
735 X_ARCHIVE_EXCLUSION_IND => new_references.archive_exclusion_ind,
736 X_ARCHIVE_DT => new_references.archive_dt,
737 X_PURGE_EXCLUSION_IND => new_references.purge_exclusion_ind,
738 X_PURGE_DT => new_references.purge_dt,
739 X_ORACLE_USERNAME => new_references.oracle_username,
740 X_PROOF_OF_INS => new_references.proof_of_ins,
741 X_PROOF_OF_IMMU => new_references.proof_of_immu,
742 X_LEVEL_OF_QUAL => new_references.level_of_qual_id,
743 X_MILITARY_SERVICE_REG => new_references.military_service_reg,
744 X_VETERAN => new_references.veteran,
745 X_INSTITUTION_CD => NULL,
746 X_OI_LOCAL_INSTITUTION_IND => NULL,
747 X_OI_OS_IND => NULL,
748 X_OI_GOVT_INSTITUTION_CD => NULL,
749 X_OI_INST_CONTROL_TYPE => NULL,
750 X_OI_INSTITUTION_TYPE => NULL,
751 X_OI_INSTITUTION_STATUS => NULL,
752 X_OU_START_DT => NULL,
753 X_OU_END_DT => NULL,
754 X_OU_MEMBER_TYPE => NULL,
755 X_OU_ORG_STATUS => NULL,
756 X_OU_ORG_TYPE => NULL,
757 X_INST_ORG_IND => NULL,
758 X_FUND_AUTHORIZATION => NULL,
759 X_PE_INFO_VERIFY_TIME => NULL,
760 X_birth_city => NULL,
761 X_birth_country => NULL,
762 x_oss_org_unit_cd => NULL, --mmkumar, party_number impact
763 X_felony_convicted_flag => NULL,
764 X_MODE => X_MODE -- gmaheswa Security
765 );
766
767
768 IF x_api_person_id IS NOT NULL AND x_person_id_type IS NOT NULL THEN
769 IGS_PE_ALT_PERS_ID_PKG.INSERT_ROW (
770 X_ROWID => lv_rowid,
771 X_PE_PERSON_ID => X_PERSON_ID,
772 X_API_PERSON_ID => x_api_person_id,
773 X_PERSON_ID_TYPE => x_person_id_type,
774 X_START_DT => trunc(SYSDATE),
775 X_END_DT => NULL,
776 X_ATTRIBUTE_CATEGORY => NULL,
777 X_ATTRIBUTE1 => NULL,
778 X_ATTRIBUTE2 => NULL,
779 X_ATTRIBUTE3 => NULL,
780 X_ATTRIBUTE4 => NULL,
781 X_ATTRIBUTE5 => NULL,
782 X_ATTRIBUTE6 => NULL,
783 X_ATTRIBUTE7 => NULL,
784 X_ATTRIBUTE8 => NULL,
785 X_ATTRIBUTE9 => NULL,
786 X_ATTRIBUTE10 => NULL,
787 X_ATTRIBUTE11 => NULL,
788 X_ATTRIBUTE12 => NULL,
789 X_ATTRIBUTE13 => NULL,
790 X_ATTRIBUTE14 => NULL,
791 X_ATTRIBUTE15 => NULL,
792 X_ATTRIBUTE16 => NULL,
793 X_ATTRIBUTE17 => NULL,
794 X_ATTRIBUTE18 => NULL,
795 X_ATTRIBUTE19 => NULL,
796 X_ATTRIBUTE20 => NULL,
797 X_REGION_CD => NULL,
798 X_MODE => 'R'
799 );
800 END IF;
801
802 FOR ptc1 IN ptc LOOP
803 lv_Person_Type_Code := ptc1.person_type_code;
804 END LOOP;
805
806 Igs_Pe_Typ_Instances_Pkg.INSERT_ROW (
807 X_ROWID => lv_rowid1,
808 x_PERSON_ID => X_PERSON_ID,-- new_references.PERSON_ID,
809 x_COURSE_CD => NULL,
810 x_TYPE_INSTANCE_ID => ln_TYPE_INSTANCE_ID,
811 x_PERSON_TYPE_CODE => lv_Person_Type_Code,
812 x_CC_VERSION_NUMBER => NULL,
813 x_FUNNEL_STATUS => NULL,
814 x_ADMISSION_APPL_NUMBER => NULL,
815 x_NOMINATED_COURSE_CD => NULL,
816 x_NCC_VERSION_NUMBER => NULL,
817 x_SEQUENCE_NUMBER => NULL,
818 x_START_DATE => SYSDATE,
819 x_END_DATE => NULL,
820 x_CREATE_METHOD => 'PERSON_CREATED' ,
821 x_ENDED_BY => NULL,
822 x_END_METHOD => NULL,
823 X_ORG_ID => FND_PROFILE.VALUE('ORG_ID'),
824 X_MODE => 'R',
825 X_EMPLMNT_CATEGORY_CODE => null
826 );
827
828 OPEN c_pe_rowid (X_PERSON_ID);
829 FETCH c_pe_rowid INTO X_ROWID;
830 IF c_pe_rowid%NOTFOUND THEN
831 CLOSE c_pe_rowid;
832 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
833 IGS_GE_MSG_STACK.ADD;
834 App_Exception.Raise_Exception;
835 END IF;
836 CLOSE c_pe_rowid;
837
838 END IF;
839
840 END INSERT_ROW;
841
842 PROCEDURE UPDATE_ROW (
843 X_LAST_UPDATE_DATE IN OUT NOCOPY DATE,
844 X_MSG_COUNT OUT NOCOPY NUMBER,
845 X_MSG_DATA OUT NOCOPY VARCHAR2,
846 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
847 X_ROWID IN VARCHAR2,
848 x_PERSON_ID IN NUMBER,
849 x_PERSON_NUMBER IN OUT NOCOPY VARCHAR2,
850 x_SURNAME IN VARCHAR2,
851 x_middle_name IN VARCHAR2,
852 x_GIVEN_NAMES IN VARCHAR2,
853 x_SEX IN VARCHAR2,
854 x_TITLE IN VARCHAR2,
855 x_STAFF_MEMBER_IND IN VARCHAR2,
856 x_DECEASED_IND IN VARCHAR2,
857 x_SUFFIX IN VARCHAR2,
858 x_pre_name_adjunct IN VARCHAR2,
859 x_ARCHIVE_EXCLUSION_IND IN VARCHAR2,
860 x_ARCHIVE_DT IN DATE,
861 x_PURGE_EXCLUSION_IND IN VARCHAR2,
862 x_PURGE_DT IN DATE,
863 x_DECEASED_DATE IN DATE,
864 x_PROOF_OF_INS IN VARCHAR2,
865 x_PROOF_OF_IMMU IN VARCHAR2,
866 x_BIRTH_DT IN DATE,
867 x_SALUTATION IN VARCHAR2,
868 x_ORACLE_USERNAME IN VARCHAR2,
869 x_PREFERRED_GIVEN_NAME IN VARCHAR2,
870 x_EMAIL_ADDR IN VARCHAR2,
871 x_LEVEL_OF_QUAL_ID IN NUMBER,
872 x_MILITARY_SERVICE_REG IN VARCHAR2,
873 x_VETERAN IN VARCHAR2,
874 x_HZ_PARTIES_OVN IN OUT NOCOPY NUMBER,
875 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
876 x_ATTRIBUTE1 IN VARCHAR2,
877 x_ATTRIBUTE2 IN VARCHAR2,
878 x_ATTRIBUTE3 IN VARCHAR2,
879 x_ATTRIBUTE4 IN VARCHAR2,
880 x_ATTRIBUTE5 IN VARCHAR2,
881 x_ATTRIBUTE6 IN VARCHAR2,
882 x_ATTRIBUTE7 IN VARCHAR2,
883 x_ATTRIBUTE8 IN VARCHAR2,
884 x_ATTRIBUTE9 IN VARCHAR2,
885 x_ATTRIBUTE10 IN VARCHAR2,
886 x_ATTRIBUTE11 IN VARCHAR2,
887 x_ATTRIBUTE12 IN VARCHAR2,
888 x_ATTRIBUTE13 IN VARCHAR2,
889 x_ATTRIBUTE14 IN VARCHAR2,
890 x_ATTRIBUTE15 IN VARCHAR2,
891 x_ATTRIBUTE16 IN VARCHAR2,
892 x_ATTRIBUTE17 IN VARCHAR2,
893 x_ATTRIBUTE18 IN VARCHAR2,
894 x_ATTRIBUTE19 IN VARCHAR2,
895 x_ATTRIBUTE20 IN VARCHAR2,
896 x_PERSON_ID_TYPE IN VARCHAR2,
897 x_API_PERSON_ID IN VARCHAR2,
898 X_STATUS IN VARCHAR2,
899 X_MODE IN VARCHAR2,
900 x_ATTRIBUTE21 IN VARCHAR2,
901 x_ATTRIBUTE22 IN VARCHAR2,
902 x_ATTRIBUTE23 IN VARCHAR2,
903 x_ATTRIBUTE24 IN VARCHAR2
904 ) AS
905 /*************************************************************
906 Created By :
907 Date Created By :
908 Purpose :
909 Know limitations, enhancements or remarks
910 Change History
911 Who When What
912 sraj 2000/05/05 the table structure has been changed
913 vrathi 2003/07/11 Bug No:3045079 The sysdate should be truncated before inserting into
914 IGS_PE_ALT_PERS_ID to eliminate the time component
915 (reverse chronological order - newest change first)
916 ***************************************************************/
917 x_profile_id HZ_PERSON_PROFILES.PERSON_PROFILE_ID%TYPE;
918
919 lv_rowid VARCHAR2(25);
920
921 CURSOR c1 IS
922 SELECT P_ID.ROWID,P_ID.*
923 FROM IGS_PE_ALT_PERS_ID P_ID
924 WHERE pe_person_id = x_person_id AND
925 person_id_type = x_person_id_type AND
926 sysdate between start_dt AND NVL(end_dt, sysdate);
927
928 tlinfo c1%ROWTYPE;
929
930 CURSOR c2 IS
931 SELECT pehz.ROWID, pehz.*
932 FROM IGS_PE_HZ_PARTIES pehz
933 WHERE party_id = x_person_id;
934
935 tlinfo2 c2%ROWTYPE;
936 l_tmp_var1 VARCHAR2(2000);
937 l_tmp_var VARCHAR2(2000);
938
939 BEGIN
940 Before_DML(
941 p_action=>'UPDATE',
942 x_rowid=>X_ROWID,
943 x_person_id=>X_PERSON_ID,
944 x_person_number =>X_PERSON_NUMBER,
945 x_surname=>X_SURNAME,
946 x_middle_name=>X_middle_name,
947 x_given_names=>X_GIVEN_NAMES,
948 x_sex=>X_SEX,
949 x_title=>X_TITLE,
950 x_staff_member_ind=>NVL(X_STAFF_MEMBER_IND,'N' ),
951 x_deceased_ind=>NVL(X_DECEASED_IND,'N' ),
952 x_suffix=>X_SUFFIX,
953 x_pre_name_adjunct=>X_pre_name_adjunct,
954 x_archive_exclusion_ind=>NVL(X_ARCHIVE_EXCLUSION_IND,'N' ),
955 x_archive_dt=>X_ARCHIVE_DT,
956 x_purge_exclusion_ind=>NVL(X_PURGE_EXCLUSION_IND,'N' ),
957 x_purge_dt=>X_PURGE_DT,
958 x_deceased_date=>X_DECEASED_DATE,
959 x_proof_of_ins=>X_PROOF_OF_INS,
960 x_proof_of_immu=>X_PROOF_OF_IMMU,
961 x_birth_dt=>X_BIRTH_DT,
962 x_salutation=>X_SALUTATION,
963 x_oracle_username=>X_ORACLE_USERNAME,
964 x_preferred_given_name=>X_PREFERRED_GIVEN_NAME,
965 x_email_addr=>X_EMAIL_ADDR,
966 x_level_of_qual_id =>X_LEVEL_OF_QUAL_ID,
967 x_military_service_reg=>X_MILITARY_SERVICE_REG,
968 x_veteran=>X_VETERAN,
969 x_hz_parties_ovn =>x_HZ_PARTIES_OVN,
970 x_status => x_status,
971 x_attribute_category=>NVL(X_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR),
972 x_attribute1=>NVL(X_ATTRIBUTE1,FND_API.G_MISS_CHAR),
973 x_attribute2=>NVL(X_ATTRIBUTE2,FND_API.G_MISS_CHAR),
974 x_attribute3=>NVL(X_ATTRIBUTE3,FND_API.G_MISS_CHAR),
975 x_attribute4=>NVL(X_ATTRIBUTE4,FND_API.G_MISS_CHAR),
976 x_attribute5=>NVL(X_ATTRIBUTE5,FND_API.G_MISS_CHAR),
977 x_attribute6=>NVL(X_ATTRIBUTE6,FND_API.G_MISS_CHAR),
978 x_attribute7=>NVL(X_ATTRIBUTE7,FND_API.G_MISS_CHAR),
979 x_attribute8=>NVL(X_ATTRIBUTE8,FND_API.G_MISS_CHAR),
980 x_attribute9=>NVL(X_ATTRIBUTE9,FND_API.G_MISS_CHAR),
981 x_attribute10=>NVL(X_ATTRIBUTE10,FND_API.G_MISS_CHAR),
982 x_attribute11=>NVL(X_ATTRIBUTE11,FND_API.G_MISS_CHAR),
983 x_attribute12=>NVL(X_ATTRIBUTE12,FND_API.G_MISS_CHAR),
984 x_attribute13=>NVL(X_ATTRIBUTE13,FND_API.G_MISS_CHAR),
985 x_attribute14=>NVL(X_ATTRIBUTE14,FND_API.G_MISS_CHAR),
986 x_attribute15=>NVL(X_ATTRIBUTE15,FND_API.G_MISS_CHAR),
987 x_attribute16=>NVL(X_ATTRIBUTE16,FND_API.G_MISS_CHAR),
988 x_attribute17=>NVL(X_ATTRIBUTE17,FND_API.G_MISS_CHAR),
989 x_attribute18=>NVL(X_ATTRIBUTE18,FND_API.G_MISS_CHAR),
990 x_attribute19=>NVL(X_ATTRIBUTE19,FND_API.G_MISS_CHAR),
991 x_attribute20=>NVL(X_ATTRIBUTE20,FND_API.G_MISS_CHAR),
992 x_attribute21=>NVL(X_ATTRIBUTE21,FND_API.G_MISS_CHAR),
993 x_attribute22=>NVL(X_ATTRIBUTE22,FND_API.G_MISS_CHAR),
994 x_attribute23=>NVL(X_ATTRIBUTE23,FND_API.G_MISS_CHAR),
995 x_attribute24=>NVL(X_ATTRIBUTE24,FND_API.G_MISS_CHAR)
996 );
997
998
999 -- explicity the record variables are assigned here and not in Before DML, as there were
1000 -- validations getting fired after before DML due to initialization of variables.
1001
1002 v_person_rec.person_pre_name_adjunct := NVL(x_pre_name_adjunct,FND_API.G_MISS_CHAR);
1003 v_person_rec.person_first_name := NVL(x_given_names,FND_API.G_MISS_CHAR);
1004 v_person_rec.person_middle_name := NVL(x_middle_name,FND_API.G_MISS_CHAR);
1005 v_person_rec.person_last_name := NVL(x_surname,FND_API.G_MISS_CHAR);
1006 v_person_rec.person_name_suffix := NVL(x_suffix,FND_API.G_MISS_CHAR);
1007 v_person_rec.person_title := NVL(x_title,FND_API.G_MISS_CHAR);
1008 v_person_rec.known_as := NVL(x_preferred_given_name,FND_API.G_MISS_CHAR);
1009 v_person_rec.date_of_birth := NVL(x_birth_dt,FND_API.G_MISS_DATE);
1010 v_person_rec.date_of_death := NVL(x_deceased_date,FND_API.G_MISS_DATE);
1011 v_person_rec.gender := NVL(x_sex,FND_API.G_MISS_CHAR);
1012
1013
1014 HZ_PARTY_V2PUB.UPDATE_PERSON (
1015 p_party_object_version_number => x_hz_parties_ovn,
1016 p_person_rec => v_person_rec,
1017 x_profile_id => x_profile_id,
1018 x_return_status =>x_return_status,
1019 x_msg_count => x_msg_count,
1020 x_msg_data => x_msg_data
1021 );
1022
1023
1024
1025 IF X_RETURN_STATUS IN ('E','U') THEN
1026 --code added by sbaliga as part of 2338473
1027 IF x_msg_count > 1 THEN
1028 FOR i IN 1..x_msg_count
1029 LOOP
1030 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1031 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
1032 END LOOP;
1033 x_msg_data := l_tmp_var1;
1034 END IF;
1035 ELSE
1036
1037 OPEN c2;
1038 FETCH c2 INTO tlinfo2;
1039
1040 IF c2%FOUND THEN
1041
1042 IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW(
1043 X_ROWID => tlinfo2.ROWID,
1044 X_PARTY_ID => X_PERSON_ID,
1045 X_DECEASED_IND => new_references.deceased_ind,
1046 X_ARCHIVE_EXCLUSION_IND => new_references.archive_exclusion_ind,
1047 X_ARCHIVE_DT => new_references.archive_dt,
1048 X_PURGE_EXCLUSION_IND => new_references.purge_exclusion_ind,
1049 X_PURGE_DT => new_references.purge_dt,
1050 X_ORACLE_USERNAME => new_references.oracle_username,
1051 X_PROOF_OF_INS => new_references.proof_of_ins,
1052 X_PROOF_OF_IMMU => new_references.proof_of_immu,
1053 X_LEVEL_OF_QUAL => new_references.level_of_qual_id,
1054 X_MILITARY_SERVICE_REG => new_references.military_service_reg,
1055 X_VETERAN => new_references.VETERAN,
1056 X_INSTITUTION_CD => tlinfo2.INSTITUTION_CD,
1057 X_OI_LOCAL_INSTITUTION_IND => tlinfo2.OI_LOCAL_INSTITUTION_IND,
1058 X_OI_OS_IND => tlinfo2.OI_OS_IND,
1059 X_OI_GOVT_INSTITUTION_CD => tlinfo2.OI_GOVT_INSTITUTION_CD,
1060 X_OI_INST_CONTROL_TYPE => tlinfo2.OI_INST_CONTROL_TYPE,
1061 X_OI_INSTITUTION_TYPE => tlinfo2.OI_INSTITUTION_TYPE,
1062 X_OI_INSTITUTION_STATUS => tlinfo2.OI_INSTITUTION_STATUS,
1063 X_OU_START_DT => tlinfo2.OU_START_DT,
1064 X_OU_END_DT => tlinfo2.OU_END_DT,
1065 X_OU_MEMBER_TYPE => tlinfo2.OU_MEMBER_TYPE,
1066 X_OU_ORG_STATUS => tlinfo2.OU_ORG_STATUS,
1067 X_OU_ORG_TYPE => tlinfo2.OU_ORG_TYPE,
1068 X_INST_ORG_IND => tlinfo2.INST_ORG_IND,
1069 X_FUND_AUTHORIZATION => tlinfo2.FUND_AUTHORIZATION,
1070 X_PE_INFO_VERIFY_TIME => tlinfo2.PE_INFO_VERIFY_TIME,
1071 X_birth_city => tlinfo2.birth_city,
1072 X_birth_country => tlinfo2.birth_country,
1073 x_oss_org_unit_cd => tlinfo2.oss_org_unit_cd, --mmkumar, party_number impact
1074 X_felony_convicted_flag => tlinfo2.felony_convicted_flag,
1075 X_MODE => x_mode -- gmaheswa Security
1076 );
1077 END IF;
1078 CLOSE c2;
1079
1080 IF x_api_person_id IS NOT NULL AND x_person_id_type IS NOT NULL THEN
1081 OPEN c1; FETCH c1 INTO tlinfo;
1082 IF c1%NOTFOUND THEN
1083 CLOSE c1;
1084 IGS_PE_ALT_PERS_ID_PKG.INSERT_ROW (
1085 X_ROWID => lv_rowid,
1086 X_PE_PERSON_ID => new_references.person_id ,
1087 X_API_PERSON_ID => x_api_person_id,
1088 X_PERSON_ID_TYPE => x_person_id_type,
1089 X_START_DT => TRUNC(SYSDATE),
1090 X_END_DT => NULL,
1091 X_ATTRIBUTE_CATEGORY => NULL,
1092 X_ATTRIBUTE1 => NULL,
1093 X_ATTRIBUTE2 => NULL,
1094 X_ATTRIBUTE3 => NULL,
1095 X_ATTRIBUTE4 => NULL,
1096 X_ATTRIBUTE5 => NULL,
1097 X_ATTRIBUTE6 => NULL,
1098 X_ATTRIBUTE7 => NULL,
1099 X_ATTRIBUTE8 => NULL,
1100 X_ATTRIBUTE9 => NULL,
1101 X_ATTRIBUTE10 => NULL,
1102 X_ATTRIBUTE11 => NULL,
1103 X_ATTRIBUTE12 => NULL,
1104 X_ATTRIBUTE13 => NULL,
1105 X_ATTRIBUTE14 => NULL,
1106 X_ATTRIBUTE15 => NULL,
1107 X_ATTRIBUTE16 => NULL,
1108 X_ATTRIBUTE17 => NULL,
1109 X_ATTRIBUTE18 => NULL,
1110 X_ATTRIBUTE19 => NULL,
1111 X_ATTRIBUTE20 => NULL,
1112 X_REGION_CD => NULL,
1113 X_MODE => 'R'
1114 );
1115 END IF;
1116 END IF;
1117
1118 END IF;
1119 END UPDATE_ROW;
1120
1121 END Igs_Pe_Person_Pkg;