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