[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_002
Source
1 PACKAGE BODY Igs_Ad_Imp_002 AS
2 /* $Header: IGSAD80B.pls 120.21 2006/06/23 05:50:10 gmaheswa ship $ */
3
4 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
5 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
6 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
7 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
8
9 cst_stat_val_1 CONSTANT VARCHAR2(2) := '1';
10 cst_stat_val_3 CONSTANT VARCHAR2(2) := '3';
11
12 PROCEDURE validate_oss_ext_attr(p_person_rec IN igs_ad_interface_dtl_dscp_v%ROWTYPE,
13 p_person_id IN NUMBER,
14 p_validation_success OUT NOCOPY VARCHAR2)
15 IS
16 /*
17 || Created By : pkpatel
18 || Created On : 24-JUL-2003
19 || Change History :
20 || Who When What
21 */
22
23 -- Cursor to check veteran
24 CURSOR level_of_qual_cur(cp_class igs_ad_code_classes.class%TYPE,
25 cp_code_id igs_ad_code_classes.code_id%TYPE,
26 cp_closed_ind igs_ad_code_classes.closed_ind%TYPE)IS
27 SELECT 'X'
28 FROM igs_ad_code_classes
29 WHERE class = cp_class
30 AND code_id = cp_code_id
31 AND closed_ind = cp_closed_ind;
32
33 l_var VARCHAR2(1);
34 validation_failed EXCEPTION;
35 l_error_code VARCHAR2(30);
36 l_prog_label VARCHAR2(4000);
37 l_label VARCHAR2(4000);
38 l_debug_str VARCHAR2(4000);
39 l_enable_log VARCHAR2(1);
40 l_request_id NUMBER(10);
41 l_felony_validation VARCHAR2(30);
42
43 BEGIN
44
45 l_enable_log := igs_ad_imp_001.g_enable_log;
46 l_prog_label := 'igs.plsql.igs_ad_imp_002.validate_oss_ext_attr';
47 l_label := 'igs.plsql.igs_ad_imp_002.validate_oss_ext_attr.';
48
49 IF p_person_rec.PROOF_OF_INS NOT IN ('Y','N') THEN
50 IF l_enable_log = 'Y' THEN
51 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E273','IGS_AD_INTERFACE_ALL');
52 END IF;
53 l_error_code := 'E273';
54 RAISE validation_failed;
55 END IF;
56
57 IF p_person_rec.PROOF_OF_IMMU NOT IN ('Y','N') THEN
58 IF l_enable_log = 'Y' THEN
59 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E274','IGS_AD_INTERFACE_ALL');
60 END IF;
61 l_error_code := 'E274';
62 RAISE validation_failed;
63 END IF;
64
65 IF p_person_rec.MILITARY_SERVICE_REG NOT IN ('Y','N') THEN
66 IF l_enable_log = 'Y' THEN
67 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E275','IGS_AD_INTERFACE_ALL');
68 END IF;
69 l_error_code := 'E275';
70 RAISE validation_failed;
71 END IF;
72
73 IF p_person_rec.level_of_qual_id IS NOT NULL THEN
74 OPEN level_of_qual_cur('LEVEL_OF_QUAL',p_person_rec.level_of_qual_id,'N');
75 FETCH level_of_qual_cur INTO l_var;
76 IF level_of_qual_cur%NOTFOUND THEN
77 IF l_enable_log = 'Y' THEN
78 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E276','IGS_AD_INTERFACE_ALL');
79 END IF;
80
81 CLOSE level_of_qual_cur;
82 l_error_code := 'E276';
83 RAISE validation_failed;
84 END IF;
85 CLOSE level_of_qual_cur;
86 END IF;
87
88 IF p_person_rec.birth_country IS NOT NULL THEN
89 IF NOT
90 (igs_pe_pers_imp_001.validate_country_code(p_person_rec.birth_country)) -- change for country code inconsistency bug 3738488
91 THEN
92 IF l_enable_log = 'Y' THEN
93 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E576','IGS_AD_INTERFACE_ALL');
94 END IF;
95 l_error_code := 'E576';
96 RAISE validation_failed;
97 END IF;
98 END IF;
99
100 IF p_person_rec.veteran IS NOT NULL THEN
101 IF NOT
102 (igs_pe_pers_imp_001.validate_lookup_type_code('VETERAN_STATUS',p_person_rec.veteran,8405))
103 THEN
104 IF l_enable_log = 'Y' THEN
105 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E174','IGS_AD_INTERFACE_ALL');
106 END IF;
107 l_error_code := 'E174';
108 RAISE validation_failed;
109 END IF;
110 END IF;
111 IF (p_person_rec.felony_convicted_flag NOT IN ('Y','N',FND_API.G_MISS_CHAR)
112 AND p_person_rec.felony_convicted_flag IS NOT NULL ) THEN
113 IF l_enable_log = 'Y' THEN
114 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E164','IGS_AD_INTERFACE_ALL');
115 END IF;
116 l_error_code := 'E164';
117 RAISE validation_failed;
118 END IF;
119
120 IF p_person_rec.felony_convicted_flag IS NOT NULL THEN
121
122 IF P_Person_Rec.felony_convicted_flag = FND_API.G_MISS_CHAR THEN
123 l_felony_validation := igs_pe_gen_004.validate_felony(p_person_id,NULL);
124 ELSE
125 l_felony_validation := igs_pe_gen_004.validate_felony(p_person_id,P_Person_Rec.felony_convicted_flag);
126 END IF;
127
128 IF l_felony_validation IS NOT NULL THEN
129 IF l_enable_log = 'Y' THEN
130 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E165','IGS_AD_INTERFACE_ALL');
131 END IF;
132 l_error_code := 'E165';
133 RAISE validation_failed;
134 END IF;
135 END IF;
136
137 p_validation_success := 'Y';
138
139 EXCEPTION
140 WHEN validation_failed THEN
141
142 p_validation_success := 'N';
143
144 UPDATE igs_ad_interface_all
145 SET ERROR_CODE = l_error_code,
146 status = '3'
147 WHERE interface_id = p_person_rec.interface_id;
148
149 END validate_oss_ext_attr;
150
151
152 PROCEDURE CREATE_PERSON(P_person_rec IN IGS_AD_INTERFACE_DTL_DSCP_V%ROWTYPE,
153 P_ADDR_TYPE IN VARCHAR2,
154 P_PERSON_ID_TYPE IN VARCHAR2,
155 P_PERSON_ID OUT NOCOPY IGS_PE_PERSON.PERSON_ID%TYPE) AS
156 /*
157 || Created By : nsinha
158 || Created On : 22-JUN-2001
159 || Purpose : This procedure process the Application
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || skpandey 21-SEP-2005 Bug: 3663505
164 || Description: Added ATTRIBUTES 21 TO 24 to store additional information
165 || pkpatel 25-DEC-2002 Bug No: 2702536
166 || Added commit after the processing each person record.
167 || asbala 12-APR-2004 3313276: Use lookup_type HZ_GENDER to validate Gender
168 || mmkumar 19-JUL-2005 party number impact , passed NULL for x_oss_org_unit_cd in uupdate_row
169 || (reverse chronological order - newest change first)
170 */
171
172 L_MISS_person_rec HZ_PARTY_V2PUB.person_rec_TYPE;
173
174 l_prog_label VARCHAR2(4000);
175 l_label VARCHAR2(4000);
176 l_debug_str VARCHAR2(4000);
177 l_enable_log VARCHAR2(1);
178 l_request_id NUMBER(10);
179 l_message_name VARCHAR2(30);
180 l_app VARCHAR2(50);
181
182 CURSOR STAT_cur (lnInterfaceID NUMBER) IS
183 SELECT SI.*
184 FROM IGS_AD_STAT_INT_all SI, IGS_AD_INTERFACE_all I
185 WHERE si.interface_id = lnInterfaceID
186 AND I.INTERFACE_ID = SI.INTERFACE_ID
187 AND SI.STATUS = '2'
188 AND I.STATUS IN ('1','4'); --4035277, if address errors out, it sets status =4 for ad_interface
189 -- furture processing should happen..so always check for 1,4 in ad_interface
190
191
192 stat_rec stat_cur%ROWTYPE;
193 l_statistice_id NUMBER;
194 l_error_code VARCHAR2(100);
195
196
197 CURSOR addr_cur(cp_interface_id igs_ad_interface_all.interface_id%TYPE) IS
198 SELECT ai.*
199 FROM igs_ad_addr_int_all ai,
200 igs_ad_interface_all i
201 WHERE ai.interface_id = cp_interface_id
202 AND ai.status = '2'
203 AND i.interface_id = ai.interface_id
204 AND i.status IN ('1','4'); --4035277, if address errors out, it sets status =4 for ad_interface
205 --furture processing should happen..so always check for 1,4 in ad_interface
206
207
208 addr_rec ADDR_cur%ROWTYPE;
209
210 CURSOR API_cur ( cp_interface_id igs_ad_interface_all.interface_id%TYPE) IS
211 SELECT api.*
212 FROM igs_ad_api_int_all api,
213 igs_ad_interface_all ai
214 WHERE api.interface_id = cp_interface_id
215 AND api.status = '2'
216 AND api.interface_id = ai.interface_id
217 AND ai.status IN ('1','4'); --4035277, if address errors out, it sets status =4 for ad_interface
218 -- furture processing should happen..so always check for 1,4 in ad_interface
219
220 -- Cursor to get format mask to validate person alternate id.
221 CURSOR api_type_cur(cp_person_id_type igs_pe_person_id_typ.person_id_type%TYPE) IS
222 SELECT format_mask
223 FROM igs_pe_person_id_typ
224 WHERE person_id_type = cp_person_id_type;
225
226 api_type_rec api_type_cur%ROWTYPE;
227 api_rec api_cur%ROWTYPE;
228
229
230 /* Following cursor is added as a fix for bug number 2333026 */
231 CURSOR c_pref_alt_id_type IS
232 SELECT PERSON_ID_TYPE
233 FROM IGS_PE_PERSON_ID_TYP
234 WHERE PREFERRED_IND ='Y';
235
236 CURSOR pe_hz_parties_cur(cp_person_id igs_pe_hz_parties.party_id%TYPE) IS
237 SELECT pehz.ROWID, pehz.*
238 FROM IGS_PE_HZ_PARTIES pehz
239 WHERE party_id = cp_person_id;
240
241 l_pref_altid_type IGS_PE_PERSON_ID_TYP.PERSON_ID_TYPE%TYPE;
242 l_pref_altid p_person_rec.PREF_ALTERNATE_ID%TYPE;
243 tlinfo2 pe_hz_parties_cur%ROWTYPE;
244 l_rowid VARCHAR2(25);
245 l_person_id IGS_PE_PERSON.PERSON_ID%TYPE;
246 l_person_number VARCHAR2(30);
247 l_PrefPersonIDType IGS_PE_PERSON_ID_TYP.PERSON_ID_TYPE%TYPE;
248 l_statistics_id NUMBER;
249 l_Return_Status VARCHAR2(1);
250 l_Status VARCHAR2(1);
251 l_Msg_Data VARCHAR2(4000);
252 l_Party_Id NUMBER;
253 l_Party_Number VARCHAR2(100);
254 l_Profile_Id NUMBER;
255 l_Msg_Count NUMBER;
256 l_Count NUMBER;
257 lnDupExist NUMBER;
258 l_location_id NUMBER;
259 l_party_last_update_date DATE;
260 l_person_profile_id NUMBER;
261 l_generate_party_number VARCHAR2(1);
262 l_var VARCHAR2(1);
263 l_object_version_number NUMBER;
264 l_oss_ext_attr_val VARCHAR2(1);
265 l_preferred_given_name igs_ad_interface_all.PREFERRED_GIVEN_NAME%TYPE;
266 l_felony_convicted_flag VARCHAR2(1);
267 BEGIN
268 l_enable_log := igs_ad_imp_001.g_enable_log;
269 l_prog_label := 'igs.plsql.igs_ad_imp_002.create_person';
270 l_label := 'igs.plsql.igs_ad_imp_002.create_person.';
271 l_rowid := '';
272 l_oss_ext_attr_val := 'Y';
273
274 -- Call Log header
275 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
276
277 IF (l_request_id IS NULL) THEN
278 l_request_id := fnd_global.conc_request_id;
279 END IF;
280
281 l_label := 'igs.plsql.igs_ad_imp_002.create_person.begin';
282 l_debug_str := 'Interface Id : ' || P_person_rec.INTERFACE_ID;
283
284 fnd_log.string_with_context( fnd_log.level_procedure,
285 l_label,
286 l_debug_str, NULL,
287 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
288 END IF;
289
290 SAVEPOINT before_insert;
291 BEGIN
292 -- Validate the title
293 IF P_Person_Rec.pre_name_adjunct IS NOT NULL THEN
294 IF NOT
295 (igs_pe_pers_imp_001.validate_lookup_type_code('CONTACT_TITLE',P_Person_Rec.pre_name_adjunct,222))
296 THEN
297 IF l_enable_log = 'Y' THEN
298 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_id,'E201','IGS_AD_INTERFACE_ALL');
299 END IF;
300 UPDATE IGS_AD_INTERFACE_ALL
301 SET ERROR_CODE = 'E201',
302 STATUS = '3'
303 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
304 RETURN;
305 END IF;
306 END IF;
307
308 -- Validate Sex.
309 IF P_Person_Rec.sex IS NOT NULL THEN
310 IF NOT
311 (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_GENDER',P_Person_Rec.sex,222))
312 THEN
313 IF l_enable_log = 'Y' THEN
314 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E202','IGS_AD_INTERFACE_ALL');
315 END IF;
316
317 UPDATE IGS_AD_INTERFACE_ALL
318 SET ERROR_CODE = 'E202',
319 STATUS = '3'
320 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
321 RETURN;
322 END IF;
323 END IF;
324 -- Validate birth_dt
325 IF (((P_Person_Rec.birth_dt IS NOT NULL) AND (P_Person_Rec.birth_dt > SYSDATE)) ) THEN
326 IF l_enable_log = 'Y' THEN
327 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E203','IGS_AD_INTERFACE_ALL');
328 END IF;
329 UPDATE IGS_AD_INTERFACE_ALL
330 SET ERROR_CODE = 'E203',
331 STATUS = '3'
332 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
333 RETURN;
334 END IF;
335
336
337 l_generate_party_number := fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER');
338 IF (l_generate_party_number = 'N') THEN
339 IF (p_person_rec.person_number IS NULL) THEN
340 UPDATE IGS_AD_INTERFACE_all
341 SET STATUS = '3' ,
342 ERROR_CODE = 'E204'
343 WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
344 P_PERSON_ID := NULL;
345 IF l_enable_log = 'Y' THEN
346 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E204','IGS_AD_INTERFACE_ALL');
347 END IF;
348 RETURN;
349 ELSE
350 l_person_number := p_person_rec.person_number;
351 END IF;
352 END IF;
353
354 IF p_person_rec.pref_alternate_id IS NOT NULL THEN
355 -- Added as a fix for Bug Number 2333026
356 OPEN c_pref_alt_id_type;
357 FETCH c_pref_alt_id_type INTO l_pref_altid_type;
358 IF (c_pref_alt_id_type%NOTFOUND) THEN
359 l_pref_altid_type := NULL;
360 l_pref_altid := NULL;
361
362 -- (pathipat) For Bug: 2485638
363 IF l_enable_log = 'Y' THEN
364 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E285','IGS_AD_INTERFACE_ALL');
365 END IF;
366
367 UPDATE IGS_AD_INTERFACE_ALL
368 SET ERROR_CODE = 'E285', STATUS = '3'
369 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
370 CLOSE c_pref_alt_id_type;
371 RETURN;
372
373 ELSE
374 --validate Person ID type
375 OPEN api_type_cur(l_pref_altid_type);
376 FETCH api_type_cur INTO api_type_rec;
377 CLOSE api_type_cur;
378
379 -- Validate the format mask
380 IF api_type_rec.format_mask IS NOT NULL THEN
381 IF NOT igs_en_val_api.fm_equal(p_person_rec.PREF_ALTERNATE_ID,api_type_rec.format_mask) THEN
382 IF l_enable_log = 'Y' THEN
383 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E268','IGS_AD_INTERFACE_ALL');
384 END IF;
385
386 UPDATE IGS_AD_INTERFACE_ALL
387 SET ERROR_CODE = 'E268',
388 STATUS = '3'
389 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
390 CLOSE c_pref_alt_id_type;
391 RETURN;
392 END IF;
393 END IF;
394 l_pref_altid := p_person_rec.PREF_ALTERNATE_ID;
395
396 END IF;
397 CLOSE c_pref_alt_id_type;
398 ELSE
399 l_pref_altid_type := NULL;
400 l_pref_altid := NULL;
401 END IF;
402
403 -- Validate the OSS extensible attributes
404 validate_oss_ext_attr(p_person_rec,P_person_rec.person_id,l_oss_ext_attr_val);
405
406 IF l_oss_ext_attr_val = 'N' THEN
407 -- The validation failed for OSS extensible attributes. The record would have updated with status and error code in the above procedure validate_oss_ext_attr
408 RETURN;
409 END IF;
410
411 -- nsidana bug 4063206 : Preferred given name to be derived from given names.
412 IF (p_person_rec.preferred_given_name IS NULL)
413 THEN
414 -- Copy the first part of the first name
415 IF (instr(p_person_rec.given_names,' ') = 0) then
416 l_preferred_given_name := substr(p_person_rec.given_names,1,length(p_person_rec.given_names));
417 ELSE
418 l_preferred_given_name := substr(p_person_rec.given_names,1,instr(p_person_rec.given_names,' '));
419 END IF;
420 ELSE
421 l_preferred_given_name := p_person_rec.preferred_given_name;
422 END IF;
423
424 IGS_PE_PERSON_PKG.INSERT_ROW( X_MSG_COUNT => l_msg_count,
425 X_MSG_DATA => l_msg_data,
426 X_RETURN_STATUS=> l_return_status,
427 X_ROWID=> l_rowid,
428 X_PERSON_ID => l_person_id,
429 X_PERSON_NUMBER => l_person_number,
430 X_SURNAME => p_person_rec.surname,
431 X_MIDDLE_NAME => p_person_rec.middle_name,
432 X_GIVEN_NAMES=> p_person_rec.given_names,
433 X_SEX => p_person_rec.sex,
434 X_TITLE => p_person_rec.title,
435 X_STAFF_MEMBER_IND => NULL,
436 X_DECEASED_IND => 'N',
437 X_SUFFIX => p_person_rec.suffix,
438 X_PRE_NAME_ADJUNCT => p_person_rec.pre_name_adjunct,
439 X_ARCHIVE_EXCLUSION_IND => 'N',
440 X_ARCHIVE_DT => NULL,
441 X_PURGE_EXCLUSION_IND=> 'N',
442 X_PURGE_DT => NULL,
443 X_DECEASED_DATE => NULL,
444 X_PROOF_OF_INS => NVL(p_person_rec.proof_of_ins,'N'),
445 X_PROOF_OF_IMMU=> NVL(p_person_rec.proof_of_immu,'N'),
446 X_BIRTH_DT=>p_person_rec.birth_dt,
447 X_SALUTATION => NULL,
448 X_ORACLE_USERNAME => NULL,
449 X_PREFERRED_GIVEN_NAME=> l_preferred_given_name,
450 X_EMAIL_ADDR=> NULL,
451 X_LEVEL_OF_QUAL_ID => p_person_rec.level_of_qual_id,
452 X_MILITARY_SERVICE_REG=>NVL(p_person_rec.MILITARY_SERVICE_REG,'N'),
453 X_VETERAN=> NVL(p_person_rec.veteran,'VETERAN_NOT'), -- ssawhney 2203778, lookup_code
454 x_hz_parties_ovn => l_object_version_number,
455 X_attribute_CATEGORY => p_person_rec.attribute_category,
456 X_attribute1 => p_person_rec.attribute1,
457 X_attribute2 => p_person_rec.attribute2,
458 X_attribute3 => p_person_rec.attribute3,
459 X_attribute4 => p_person_rec.attribute4,
460 X_attribute5 => p_person_rec.attribute5,
461 X_attribute6 => p_person_rec.attribute6,
462 X_attribute7 => p_person_rec.attribute7,
463 X_attribute8 => p_person_rec.attribute8,
464 X_attribute9 => p_person_rec.attribute9,
465 X_attribute10 => p_person_rec.attribute10,
466 X_attribute11 => p_person_rec.attribute11,
467 X_attribute12 => p_person_rec.attribute12,
468 X_attribute13 => p_person_rec.attribute13,
469 X_attribute14 => p_person_rec.attribute14,
470 X_attribute15 => p_person_rec.attribute15,
471 X_attribute16 => p_person_rec.attribute16,
472 X_attribute17 => p_person_rec.attribute17,
473 X_attribute18 => p_person_rec.attribute18,
474 X_attribute19 => p_person_rec.attribute19,
475 X_attribute20 => p_person_rec.attribute20,
476 X_PERSON_ID_TYPE=> l_pref_altid_type,
477 X_API_PERSON_ID => l_pref_altid,
478 X_MODE => 'R',
479 X_attribute21 => p_person_rec.attribute21,
480 X_attribute22 => p_person_rec.attribute22,
481 X_attribute23 => p_person_rec.attribute23,
482 X_attribute24 => p_person_rec.attribute24
483 );
484
485 IF p_person_rec.birth_city IS NOT NULL OR p_person_rec.birth_country IS NOT NULL OR p_person_rec.felony_convicted_flag IS NOT NULL THEN
486 OPEN pe_hz_parties_cur(l_person_id);
487 FETCH pe_hz_parties_cur INTO tlinfo2;
488
489 IF pe_hz_parties_cur%FOUND THEN
490 IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW(
491 X_ROWID => tlinfo2.ROWID,
492 X_PARTY_ID => tlinfo2.party_id,
493 X_DECEASED_IND => tlinfo2.deceased_ind,
494 X_ARCHIVE_EXCLUSION_IND => tlinfo2.archive_exclusion_ind,
495 X_ARCHIVE_DT => tlinfo2.archive_dt,
496 X_PURGE_EXCLUSION_IND => tlinfo2.purge_exclusion_ind,
497 X_PURGE_DT => tlinfo2.purge_dt,
498 X_ORACLE_USERNAME => tlinfo2.oracle_username,
499 X_PROOF_OF_INS => tlinfo2.proof_of_ins,
500 X_PROOF_OF_IMMU => tlinfo2.proof_of_immu,
501 X_LEVEL_OF_QUAL => tlinfo2.level_of_qual,
502 X_MILITARY_SERVICE_REG => tlinfo2.military_service_reg,
503 X_VETERAN => tlinfo2.veteran,
504 X_INSTITUTION_CD => tlinfo2.institution_cd,
505 X_OI_LOCAL_INSTITUTION_IND => tlinfo2.oi_local_institution_ind,
506 X_OI_OS_IND => tlinfo2.oi_os_ind,
507 X_OI_GOVT_INSTITUTION_CD => tlinfo2.oi_govt_institution_cd,
508 X_OI_INST_CONTROL_TYPE => tlinfo2.oi_inst_control_type,
509 X_OI_INSTITUTION_TYPE => tlinfo2.oi_institution_type,
510 X_OI_INSTITUTION_STATUS => tlinfo2.oi_institution_status,
511 X_OU_START_DT => tlinfo2.ou_start_dt,
512 X_OU_END_DT => tlinfo2.ou_end_dt,
513 X_OU_MEMBER_TYPE => tlinfo2.ou_member_type,
514 X_OU_ORG_STATUS => tlinfo2.ou_org_status,
515 X_OU_ORG_TYPE => tlinfo2.ou_org_type,
516 X_INST_ORG_IND => tlinfo2.inst_org_ind,
517 X_FUND_AUTHORIZATION => tlinfo2.fund_authorization,
518 X_PE_INFO_VERIFY_TIME => tlinfo2.pe_info_verify_time,
519 X_birth_city => p_person_rec.birth_city,
520 X_birth_country => p_person_rec.birth_country,
521 x_oss_org_unit_cd => NULL, --mmkumar, party number impact
522 X_felony_convicted_flag => p_person_rec.felony_convicted_flag,
523 X_MODE => 'R'
524 );
525 END IF;
526 CLOSE pe_hz_parties_cur;
527 END IF;
528
529 IF (l_Return_Status IN ('E','U') ) THEN
530 ROLLBACK TO BEFORE_INSERT;
531 P_PERSON_ID := NULL;
532
533 UPDATE IGS_AD_INTERFACE_all
534 SET status = '3',
535 error_code = 'E322'
536 WHERE interface_id = p_person_rec.interface_id;
537
538 -- Call Log detail
539
540 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
541
542 IF (l_request_id IS NULL) THEN
543 l_request_id := fnd_global.conc_request_id;
544 END IF;
545
546 l_label := 'igs.plsql.igs_ad_imp_002.create_person.exception'||'E322';
547
548 l_debug_str := 'Interface ID:'||p_person_rec.interface_id||' Person Creation Failed HZMessage: '||l_msg_data||' SQLERRM:' || SQLERRM;
549
550 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
551 END IF;
552
553 IF l_enable_log = 'Y' THEN
554 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E322','IGS_AD_INTERFACE_ALL');
555 END IF;
556 RETURN;
557 ELSE
558 UPDATE IGS_AD_INTERFACE_all
559 SET STATUS = '1',
560 ERROR_CODE = NULL --ssomani, added this 3/15/01
561 WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
562 P_PERSON_ID := l_person_id;
563 -- Record is successfully processed, commit the record
564 COMMIT;
565 END IF;
566
567 EXCEPTION WHEN OTHERS THEN
568 -- Person creation failed
569 ROLLBACK TO BEFORE_INSERT;
570 IF pe_hz_parties_cur%ISOPEN THEN
571 CLOSE pe_hz_parties_cur;
572 END IF;
573
574 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
575
576 IF l_message_name = 'IGS_PE_UNIQUE_PID' THEN
577 l_error_code := 'E567';
578 ELSE
579 l_error_code := 'E322';
580 END IF;
581
582 UPDATE IGS_AD_INTERFACE_all
583 SET status = '3',
584 error_code = l_error_code
585 WHERE interface_id = p_person_rec.interface_id;
586
587 -- Call Log detail
588 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
589
590 IF (l_request_id IS NULL) THEN
591 l_request_id := fnd_global.conc_request_id;
592 END IF;
593
594 l_label := 'igs.plsql.igs_ad_imp_002.create_person.exception'||l_error_code;
595
596 l_debug_str := 'IGS_AD_IMP_002.Create_Person' || ' Exception from IGS_PE_PERSON_PKG '|| ' Interface Id : ' || (P_person_rec.INTERFACE_ID) ||
597 ' Status : 3' || ' ErrorCode:'||l_error_code||' SQLERRM '|| SQLERRM ;
598
599 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str,NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
600 END IF;
601
602 IF l_enable_log = 'Y' THEN
603 igs_ad_imp_001.logerrormessage(p_person_rec.interface_id,l_error_code,'IGS_AD_INTERFACE_ALL');
604 END IF;
605
606 P_PERSON_ID := NULL;
607
608 RETURN;
609 END;
610 -- stats creation starts here
611 DECLARE
612 l_rowId VARCHAR2(25);
613 BEGIN
614 OPEN stat_cur(p_person_rec.INTERFACE_ID);
615 FETCH stat_cur INTO stat_rec;
616 SAVEPOINT before_insert_stats;
617 IF stat_cur%FOUND THEN
618 -- marital status and ethnic origin are made case insensitive
619 stat_rec.marital_status := UPPER(stat_rec.marital_status);
620 stat_rec.ethnic_origin := UPPER(stat_rec.ethnic_origin);
621 BEGIN
622 --Validation check of Descriptive Flexfield
623 -- Added as a part of bug number 2203778
624 IF NOT igs_ad_imp_018.validate_desc_flex(
625 p_attribute_category =>stat_rec.attribute_category,
626 p_attribute1 =>stat_rec.attribute1 ,
627 p_attribute2 =>stat_rec.attribute2 ,
628 p_attribute3 =>stat_rec.attribute3 ,
629 p_attribute4 =>stat_rec.attribute4 ,
630 p_attribute5 =>stat_rec.attribute5 ,
631 p_attribute6 =>stat_rec.attribute6 ,
632 p_attribute7 =>stat_rec.attribute7 ,
633 p_attribute8 =>stat_rec.attribute8 ,
634 p_attribute9 =>stat_rec.attribute9 ,
635 p_attribute10 =>stat_rec.attribute10 ,
636 p_attribute11 =>stat_rec.attribute11 ,
637 p_attribute12 =>stat_rec.attribute12 ,
638 p_attribute13 =>stat_rec.attribute13 ,
639 p_attribute14 =>stat_rec.attribute14 ,
640 p_attribute15 =>stat_rec.attribute15 ,
641 p_attribute16 =>stat_rec.attribute16 ,
642 p_attribute17 =>stat_rec.attribute17 ,
643 p_attribute18 =>stat_rec.attribute18 ,
644 p_attribute19 =>stat_rec.attribute19 ,
645 p_attribute20 =>stat_rec.attribute20 ,
646 p_desc_flex_name =>'IGS_PE_PERS_STAT' ) THEN
647 l_error_code := 'E170' ;
648 RAISE NO_DATA_FOUND;
649 END IF;
650 IF stat_rec.RELIGION_CD IS NOT NULL THEN
651 BEGIN
652 IF NOT
653 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_RELIGION',stat_rec.religion_cd,8405))
654 THEN
655 IF l_enable_log = 'Y' THEN
656 igs_ad_imp_001.logerrormessage(stat_rec.INTERFACE_STAT_ID,'E205','IGS_AD_STAT_INT_ALL');
657 END IF;
658 l_error_code := 'E205' ;
659 RAISE NO_DATA_FOUND;
660 END IF;
661 END;
662 END IF;
663 IF stat_rec.MARITAL_STATUS IS NOT NULL THEN
664 BEGIN
665 IF NOT
666 (igs_pe_pers_imp_001.validate_lookup_type_code('MARITAL_STATUS',stat_rec.MARITAL_STATUS,222))
667 THEN
668 IF l_enable_log = 'Y' THEN
669 igs_ad_imp_001.logerrormessage(stat_rec.INTERFACE_STAT_ID,'E206','IGS_AD_STAT_INT_ALL');
670 END IF;
671 l_error_code :='E206';
672 RAISE NO_DATA_FOUND;
673 END IF;
674 END;
675 END IF;
676 IF stat_rec.MARITAL_STATUS_EFFECTIVE_DATE IS NOT NULL THEN
677 DECLARE
678 CURSOR c_mar_eff_dt(cp_person_id igs_pe_person.person_id%TYPE) IS
679 SELECT BIRTH_DATE
680 FROM IGS_PE_PERSON_BASE_V WHERE
681 PERSON_ID =cp_person_id;
682 l_birth_date IGS_PE_PERSON_BASE_V.BIRTH_DATE%TYPE;
683 BEGIN
684 IF l_birth_date IS NOT NULL THEN
685 OPEN c_mar_eff_dt(p_person_id);
686 FETCH c_mar_eff_dt INTO l_birth_date;
687 CLOSE c_mar_eff_dt;
688 IF stat_rec.MARITAL_STATUS_EFFECTIVE_DATE < l_birth_date THEN
689 RAISE NO_DATA_FOUND;
690 END IF;
691 END IF;
692 EXCEPTION
693 WHEN NO_DATA_FOUND THEN
694 IF l_enable_log = 'Y' THEN
695 igs_ad_imp_001.logerrormessage(stat_rec.INTERFACE_STAT_ID,'E277','IGS_AD_STAT_INT_ALL');
696 END IF;
697 l_error_code :='E277';
698 RAISE NO_DATA_FOUND;
699 END;
700 END IF;
701 IF stat_rec.ETHNIC_ORIGIN IS NOT NULL THEN
702 DECLARE
703 l_object_version_number NUMBER;
704 BEGIN
705 IF NOT
706 (igs_pe_pers_imp_001.validate_lookup_type_code('IGS_ETHNIC_ORIGIN',stat_rec.ETHNIC_ORIGIN,8405))
707 THEN
708 IF l_enable_log = 'Y' THEN
709 igs_ad_imp_001.logerrormessage(stat_rec.INTERFACE_STAT_ID,'E207','IGS_AD_STAT_INT_ALL');
710 END IF;
711 l_error_code := 'E207' ;
712 RAISE NO_DATA_FOUND;
713 END IF;
714 END;
715 END IF;
716 igs_pe_stat_pkg.insert_row(
717 X_ACTION=> 'INSERT',
718 X_ROWID=> l_rowid,
719 X_PERSON_ID => p_person_id,
720 X_ETHNIC_ORIGIN_ID =>stat_rec.ethnic_origin,
721 X_MARITAL_STATUS => stat_rec.marital_status,
722 X_MARITAL_STAT_EFFECT_DT => stat_rec.marital_status_effective_date,
723 X_ANN_FAMILY_INCOME=> NULL,
724 X_NUMBER_IN_FAMILY=> NULL,
725 X_CONTENT_SOURCE_TYPE => 'USER_ENTERED',
726 X_INTERNAL_FLAG=> NULL,
727 X_PERSON_NUMBER => NULL,
728 X_EFFECTIVE_START_DATE => SYSDATE,
729 X_EFFECTIVE_END_DATE => NULL,
730 X_ETHNIC_ORIGIN => NULL,
731 X_RELIGION=> stat_rec.religion_cd,
732 X_NEXT_TO_KIN => NULL,
733 X_NEXT_TO_KIN_MEANING => NULL,
734 X_PLACE_OF_BIRTH => stat_rec.place_of_birth,
735 X_SOCIO_ECO_STATUS => NULL,
736 X_SOCIO_ECO_STATUS_DESC => NULL,
737 X_FURTHER_EDUCATION => NULL,
738 X_FURTHER_EDUCATION_DESC => NULL,
739 X_IN_STATE_TUITION=> NULL,
740 X_TUITION_ST_DATE=> NULL,
741 X_TUITION_END_DATE => NULL,
742 X_PERSON_INITIALS => NULL,
743 X_PRIMARY_CONTACT_ID => NULL,
744 X_PERSONAL_INCOME => NULL,
745 X_HEAD_OF_HOUSEHOLD_FLAG => NULL,
746 X_CONTENT_SOURCE_NUMBER => NULL,
747 x_hz_parties_ovn => l_object_version_number,
748 X_attribute_category => stat_rec.attribute_category,
749 X_attribute1 => stat_rec.attribute1 ,
750 X_attribute2 => stat_rec.attribute2 ,
751 X_attribute3 => stat_rec.attribute3 ,
752 X_attribute4 => stat_rec.attribute4 ,
753 X_attribute5 => stat_rec.attribute5 ,
754 X_attribute6 => stat_rec.attribute6 ,
755 X_attribute7 => stat_rec.attribute7 ,
756 X_attribute8 => stat_rec.attribute8 ,
757 X_attribute9 => stat_rec.attribute9 ,
758 X_attribute10 => stat_rec.attribute10 ,
759 X_attribute11 => stat_rec.attribute11 ,
760 X_attribute12 => stat_rec.attribute12 ,
761 X_attribute13 => stat_rec.attribute13 ,
762 X_attribute14 => stat_rec.attribute14 ,
763 X_attribute15 => stat_rec.attribute15 ,
764 X_attribute16 => stat_rec.attribute16 ,
765 X_attribute17 => stat_rec.attribute17 ,
766 X_attribute18 => stat_rec.attribute18 ,
767 X_attribute19 => stat_rec.attribute19 ,
768 X_attribute20 => stat_rec.attribute20 ,
769 X_GLOBAL_attribute_CATEGORY => NULL,
770 X_GLOBAL_attribute1 => NULL,
771 X_GLOBAL_attribute2 => NULL,
772 X_GLOBAL_attribute3 => NULL,
773 X_GLOBAL_attribute4 => NULL,
774 X_GLOBAL_attribute5 => NULL,
775 X_GLOBAL_attribute6 => NULL,
776 X_GLOBAL_attribute7 => NULL,
777 X_GLOBAL_attribute8 => NULL,
778 X_GLOBAL_attribute9 => NULL,
779 X_GLOBAL_attribute10 => NULL,
780 X_GLOBAL_attribute11 => NULL,
781 X_GLOBAL_attribute12 => NULL,
782 X_GLOBAL_attribute13 => NULL,
783 X_GLOBAL_attribute14 => NULL,
784 X_GLOBAL_attribute15 => NULL,
785 X_GLOBAL_attribute16 => NULL,
786 X_GLOBAL_attribute17 => NULL,
787 X_GLOBAL_attribute18 => NULL,
788 X_GLOBAL_attribute19 => NULL,
789 X_GLOBAL_attribute20 => NULL,
790 X_PARTY_LAST_UPDATE_DATE => L_party_last_update_date,
791 X_PERSON_PROFILE_ID=> l_person_profile_id,
792 X_MATR_CAL_TYPE => NULL,
793 X_MATR_SEQUENCE_NUMBER => NULL,
794 X_INIT_CAL_TYPE => NULL,
795 X_INIT_SEQUENCE_NUMBER => NULL,
796 X_RECENT_CAL_TYPE => NULL,
797 X_RECENT_SEQUENCE_NUMBER => NULL,
798 X_CATALOG_CAL_TYPE => NULL,
799 X_CATALOG_SEQUENCE_NUMBER => NULL,
800 Z_RETURN_STATUS => l_return_status,
801 Z_MSG_COUNT => l_msg_count,
802 Z_MSG_DATA => l_msg_data,
803 X_BIRTH_CNTRY_RESN_CODE => NULL --- prbhardw
804 );
805
806 IF l_return_status IN ('E','U') THEN
807 ROLLBACK TO before_insert_stats;
808 UPDATE IGS_AD_STAT_INT_ALL
809 SET STATUS = '3',
810 ERROR_CODE = 'E005'
811 WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
812
813 UPDATE igs_ad_interface_all
814 SET status = '4',
815 error_code = 'E005'
816 WHERE interface_id = p_person_rec.interface_id;
817
818 -- Call Log detail
819 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
820
821 IF (l_request_id IS NULL) THEN
822 l_request_id := fnd_global.conc_request_id;
823 END IF;
824
825 l_label := 'igs.plsql.IGS_AD_IMP_002.create_person.exception:E005';
826
827 l_debug_str := 'IGS_AD_IMP_002.Create_Person ' || 'Error from IGS_PE_STAT_PKG. HzMesg : '
828 || l_msg_data || ' Interface stat ID : ' || stat_rec.interface_stat_id||' Status : 3' || ' ErrorCode : E005';
829
830 fnd_log.string_with_context( fnd_log.level_exception,
831 l_label,
832 l_debug_str, NULL,
833 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
834 END IF;
835
836 IF l_enable_log = 'Y' THEN
837 igs_ad_imp_001.logerrormessage(stat_rec.interface_stat_id,'E005','IGS_AD_STAT_INT_ALL');
838 END IF;
839
840 ELSE
841 UPDATE IGS_AD_STAT_INT_ALL
842 SET STATUS = '1',
843 ERROR_CODE = NULL, --ssomani, added this 3/15/01
844 PERSON_ID = P_PERSON_ID
845 WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
846 END IF;
847
848
849
850 EXCEPTION
851 WHEN NO_DATA_FOUND THEN
852 ROLLBACK TO before_insert_stats;
853 L_MSG_DATA := SQLERRM;
854 UPDATE IGS_AD_STAT_INT_ALL
855 SET status = '3',
856 error_code = l_error_code
857 WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
858
859 UPDATE igs_ad_interface_all
860 SET status = '4',
861 error_code = 'E005'
862 WHERE interface_id = p_person_rec.interface_id;
863
864 WHEN OTHERS THEN
865 L_MSG_DATA := SQLERRM;
866 ROLLBACK TO before_insert_stats;
867
868 UPDATE IGS_AD_STAT_INT_ALL
869 SET STATUS = '3',
870 ERROR_CODE = 'E005'
871 WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
872
873 UPDATE igs_ad_interface_all
874 SET status = '4',
875 error_code = 'E005'
876 WHERE interface_id = p_person_rec.interface_id;
877
878 -- Call Log detail
879 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
880
881 IF (l_request_id IS NULL) THEN
882 l_request_id := fnd_global.conc_request_id;
883 END IF;
884 l_label := 'igs.plsql.igs_ad_imp_002.create_person.exception'||'E005';
885
886 l_debug_str := 'IGS_AD_IMP_002.Create_Person ' || 'Error from IGS_PE_STAT_PKG' || l_msg_data ||
887 ' Interface Stat Id : ' || stat_rec.interface_stat_id
888 || ' Status : 3' || ' ErrorCode: E005 SQLERRM:' || SQLERRM;
889
890 fnd_log.string_with_context( fnd_log.level_exception,
891 l_label,
892 l_debug_str, NULL,
893 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
894 END IF;
895
896 IF l_enable_log = 'Y' THEN
897 igs_ad_imp_001.logerrormessage(stat_rec.interface_stat_id,'E005','IGS_AD_STAT_INT_ALL');
898 END IF;
899
900 END;
901 END IF;
902 CLOSE stat_cur;
903 END;
904
905 IF stat_cur%ISOPEN THEN
906 CLOSE stat_cur;
907 END IF;
908 -- End Statistics
909
910
911 -- Begin Address
912 DECLARE
913 CURSOR check_addr_dup_cur(cp_person_id hz_party_sites.party_id%TYPE,
914 cp_addr_rec addr_cur%ROWTYPE) IS
915 SELECT site.location_id, site.party_site_id
916 FROM hz_locations loc, hz_party_sites site
917 WHERE site.party_id = cp_person_id
918 AND site.location_id = loc.location_id
919 AND UPPER(NVL(loc.address1,'X')) = UPPER(NVL(cp_addr_rec.addr_line_1,'X'))
920 AND UPPER(NVL(loc.address2,'X')) = UPPER(NVL(cp_addr_rec.addr_line_2,'X'))
921 AND UPPER(NVL(loc.address3,'X')) = UPPER(NVL(cp_addr_rec.addr_line_3,'X'))
922 AND UPPER(NVL(loc.address4,'X')) = UPPER(NVL(cp_addr_rec.addr_line_4,'X'))
923 AND UPPER(NVL(loc.city,'X')) = UPPER(NVL(cp_addr_rec.city,'X'))
924 AND UPPER(NVL(loc.state,'X')) = UPPER(NVL(cp_addr_rec.state,'X'))
925 AND loc.country = cp_addr_rec.country
926 AND UPPER(NVL(loc.county,'X')) = UPPER(NVL(cp_addr_rec.county,'X'))
927 AND UPPER(NVL(loc.province,'X')) = UPPER(NVL(cp_addr_rec.province,'X'));
928
929 l_party_site_id hz_party_sites.party_site_id%TYPE;
930 BEGIN
931 g_addr_process := FALSE;
932
933 OPEN ADDR_CUR(p_person_rec.INTERFACE_ID);
934 LOOP
935 FETCH addr_cur INTO ADDR_REC;
936 EXIT WHEN addr_cur%NOTFOUND;
937
938 ADDR_REC.country := UPPER(ADDR_REC.country);
939 l_location_id := NULL;
940
941 OPEN check_addr_dup_cur(p_person_id,addr_rec);
942 FETCH check_addr_dup_cur INTO l_location_id, l_party_site_id;
943 CLOSE check_addr_dup_cur;
944
945
946 IF l_location_id IS NOT NULL THEN
947 --Make a call to IGS_AD_UPDATE_ADDRESS with the following parameters.
948 -- since this address exists already. Update it.
949 UPDATE_ADDRESS(
950 P_ADDR_REC => ADDR_REC,
951 P_PERSON_ID => p_person_id,
952 P_LOCATION_ID => l_location_id,
953 p_party_site_id => l_party_site_id);
954 ELSE
955 -- Address not exists. Create it.
956 CREATE_ADDRESS(P_addr_rec => addr_rec,
957 P_PERSON_ID =>p_person_id,
958 P_STATUS=>l_Status,
959 p_error_code=>l_Error_Code);
960 END IF;
961
962 IF l_status = '3' THEN
963 UPDATE igs_ad_interface_all
964 SET status = '4',
965 error_code = 'E006'
966 WHERE interface_id = p_person_rec.interface_id;
967
968 IF l_enable_log = 'Y' THEN
969 igs_ad_imp_001.logerrormessage(P_person_rec.INTERFACE_ID,'E006','IGS_AD_INTERFACE_ALL');
970 END IF;
971 END IF;
972 END LOOP;
973 CLOSE ADDR_CUR;
974
975 IF g_addr_process THEN
976 igs_pe_wf_gen.ti_addr_chg_persons(NVL(igs_pe_wf_gen.ti_addr_chg_persons.LAST,0)+1) := p_person_id;
977 END IF;
978 END;
979 -- End address
980
981 BEGIN
982 OPEN api_cur(p_person_rec.interface_id);
983 LOOP
984 FETCH api_cur INTO api_rec;
985 EXIT WHEN api_cur%NOTFOUND;
986 api_rec.person_id_type := UPPER(api_rec.person_id_type);
987 api_rec.alternate_id := UPPER(api_rec.alternate_id);
988 create_api(p_api_rec =>api_rec,
989 p_person_id =>l_person_id,
990 p_status =>l_status,
991 p_error_code => l_error_code);
992
993 IF l_status = '3' THEN
994
995 UPDATE igs_ad_api_int_all
996 SET status = l_status,
997 error_code = l_error_code
998 WHERE interface_api_id = api_rec.interface_api_id;
999 UPDATE igs_ad_interface_all
1000 SET status = '4',
1001 error_code = 'E007'
1002 WHERE interface_id = p_person_rec.interface_id;
1003 IF l_enable_log = 'Y' THEN
1004 igs_ad_imp_001.logerrormessage(P_person_rec.INTERFACE_ID,'E007','IGS_AD_INTERFACE_ALL');
1005 END IF;
1006
1007 ELSIF l_status = '1' THEN
1008
1009 UPDATE igs_ad_api_int_all
1010 SET status = '1',
1011 error_code = null
1012 WHERE interface_api_id = api_rec.interface_api_id;
1013
1014
1015 END IF;
1016 END LOOP;
1017 CLOSE api_cur;
1018
1019 END;
1020 END CREATE_PERSON;
1021
1022 PROCEDURE UPDATE_PERSON( p_person_rec IN IGS_AD_INTERFACE_DTL_DSCP_V%ROWTYPE,
1023 P_ADDR_TYPE IN VARCHAR2,
1024 P_PERSON_ID_TYPE IN VARCHAR2,
1025 P_PERSON_ID IN IGS_PE_PERSON.PERSON_ID%TYPE) AS
1026 /*
1027 || Created By : nsinha
1028 || Created On : 22-JUN-2001
1029 || Purpose : This procedure process the Application
1030 || Known limitations, enhancements or remarks :
1031 || Change History :
1032 || Who When What
1033 || skpandey 21-SEP-2005 Bug: 3663505
1034 || Description: Added ATTRIBUTES 21 TO 24 to store additional information
1035 || pkpatel 22-JUN-2001 Bug no.1834307 :For Modeling and Forecasting DLD
1036 || Modified the signature by changing the datatype of parameter from
1037 || igs_ad_interface_all%ROWTYPE to igs_ad_interface_dtl_dscp_v%ROWTYPE
1038 || nsinha 08-Apr-2002 Bug no.2028066: Added Cursor for Null handling Rule.
1039 || pkpatel 25-DEC-2002 Bug No: 2702536
1040 || Added commit after the processing each person record.
1041 || vrathi 25-jun-2003 Bug No:3019813
1042 || Added check to set error code and status if the person already exists in HR
1043 || asbala 12-APR-2004 3313276: Use lookup_type HZ_GENDER to validate Gender
1044 || mmkumar 19-JUL-2005 passed NULL for x_oss_org_unit_cd in add_row call to igs_pe_hz_parties_pkg,
1045 || (reverse chronological order - newest change first)
1046 */
1047
1048 l_miss_person_rec HZ_PARTY_V2PUB.person_rec_TYPE;
1049 l_prog_label VARCHAR2(4000);
1050 l_label VARCHAR2(4000);
1051 l_debug_str VARCHAR2(4000);
1052 l_enable_log VARCHAR2(1);
1053 l_request_id NUMBER(10);
1054 l_message_name VARCHAR2(30);
1055 l_app VARCHAR2(50);
1056
1057 CURSOR addr_cur(cp_interface_id IGS_AD_ADDR_INT_ALL.INTERFACE_ID%TYPE) IS
1058 SELECT AI.*
1059 FROM IGS_AD_ADDR_INT_ALL AI
1060 WHERE (AI.INTERFACE_ID = cp_interface_id)
1061 AND NVL(AI.STATUS, '2') = '2';
1062
1063
1064 addr_rec ADDR_cur%ROWTYPE;
1065
1066
1067 CURSOR API_cur(cp_interface_id IGS_AD_API_INT_ALL.INTERFACE_ID%TYPE) IS
1068 SELECT *
1069 FROM IGS_AD_API_INT_ALL
1070 WHERE INTERFACE_ID = cp_interface_id
1071 AND NVL(STATUS, '2') = '2'; -- The mandatory data restriction for other person type is removed
1072 -- Therefore the person ID type check is removed
1073
1074 api_rec api_cur%ROWTYPE;
1075
1076 -- Cursor for Null handling Rule.
1077 -- Cursor for Null handling Rule
1078 CURSOR c_null_hdlg_per_cur(cp_person_id igs_pe_person.person_id%TYPE) IS
1079 SELECT
1080 p.rowid row_id,
1081 p.party_id person_id,
1082 p.party_number person_number,
1083 p.party_name person_name,
1084 NULL staff_member_ind,
1085 p.person_last_name surname,
1086 p.person_first_name given_names,
1087 p.person_middle_name middle_name,
1088 p.person_name_suffix suffix,
1089 p.person_pre_name_adjunct pre_name_adjunct,
1090 p.person_title title,
1091 p.email_address email_addr,
1092 p.salutation,
1093 p.known_as preferred_given_name,
1094 pd.proof_of_ins,
1095 pd.proof_of_immu,
1096 pd.level_of_qual level_of_qual_id,
1097 pd.military_service_reg,
1098 pd.veteran,
1099 DECODE(pp.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') deceased_ind,
1100 pp.gender sex,
1101 pp.date_of_death deceased_date,
1102 pp.date_of_birth birth_dt,
1103 pd.archive_exclusion_ind,
1104 pd.archive_dt,
1105 pd.purge_exclusion_ind,
1106 pd.purge_dt,
1107 pit.person_id_type,
1108 pit.api_person_id,
1109 pd.fund_authorization,
1110 p.attribute_category,
1111 p.attribute1,
1112 p.attribute2,
1113 p.attribute3,
1114 p.attribute4,
1115 p.attribute5,
1116 p.attribute6,
1117 p.attribute7,
1118 p.attribute8,
1119 p.attribute9,
1120 p.attribute10,
1121 p.attribute11,
1122 p.attribute12,
1123 p.attribute13,
1124 p.attribute14,
1125 p.attribute15,
1126 p.attribute16,
1127 p.attribute17,
1128 p.attribute18,
1129 p.attribute19,
1130 p.attribute20,
1131 p.attribute21,
1132 p.attribute22,
1133 p.attribute23,
1134 p.attribute24,
1135 pd.oracle_username ,
1136 pd.birth_city,
1137 pd.birth_country,
1138 p.object_version_number,
1139 p.status,
1140 pd.felony_convicted_flag
1141 FROM
1142 hz_parties p,
1143 igs_pe_hz_parties pd,
1144 hz_person_profiles pp,
1145 igs_pe_person_id_type_v pit
1146 WHERE p.party_id = cp_person_id
1147 AND p.party_id = pit.pe_person_id (+)
1148 AND p.party_id = pd.party_id (+)
1149 AND p.party_id = pp.party_id
1150 AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
1151
1152 -- Cursor to get format mask to validate person alternate id.
1153 CURSOR api_type_cur(cp_person_id_type igs_pe_person_id_typ.person_id_type%TYPE) IS
1154 SELECT format_mask
1155 FROM igs_pe_person_id_typ
1156 WHERE person_id_type = cp_person_id_type;
1157
1158 api_type_rec api_type_cur%ROWTYPE;
1159
1160 CURSOR c_pref_alt_id_type IS
1161 SELECT PERSON_ID_TYPE
1162 FROM IGS_PE_PERSON_ID_TYP
1163 WHERE PREFERRED_IND ='Y';
1164
1165 CURSOR pe_hz_parties_cur(cp_person_id igs_pe_hz_parties.party_id%TYPE) IS
1166 SELECT pehz.ROWID, pehz.*
1167 FROM IGS_PE_HZ_PARTIES pehz
1168 WHERE party_id = cp_person_id;
1169
1170 CURSOR hz_parties_cur(cp_person_id hz_parties.party_id%TYPE)IS
1171 SELECT last_update_date, ROWID
1172 FROM hz_parties
1173 WHERE party_id = cp_person_id;
1174
1175 l_pref_altid_type IGS_PE_PERSON_ID_TYP.PERSON_ID_TYPE%TYPE;
1176 l_pref_altid p_person_rec.PREF_ALTERNATE_ID%TYPE;
1177 tlinfo2 pe_hz_parties_cur%ROWTYPE;
1178 c_null_hdlg_per_rec c_null_hdlg_per_cur%ROWTYPE;
1179 lv_deceased_ind igs_pe_hz_parties.deceased_ind%TYPE;
1180
1181 l_location_Id NUMBER;
1182 l_rowid VARCHAR2(25);
1183 l_person_id IGS_PE_PERSON.PERSON_ID%TYPE;
1184 l_PrefPersonIDType IGS_PE_PERSON_ID_TYP.PERSON_ID_TYPE%TYPE;
1185 l_status VARCHAR2(2);
1186 l_error_code VARCHAR2(6);
1187 l_Last_Update_Date DATE;
1188 l_Error_Code_Num NUMBER;
1189 lnDupExist NUMBER;
1190 l_Return_Status VARCHAR2(1);
1191 l_Msg_Data VARCHAR2(4000);
1192 l_Party_Id NUMBER;
1193 l_Party_Number VARCHAR2(100);
1194 l_Profile_Id NUMBER;
1195 l_Msg_Count NUMBER;
1196 l_var VARCHAR2(1);
1197 l_object_version_number NUMBER;
1198 l_oss_ext_attr_val VARCHAR2(1);
1199 l_preferred_given_name igs_ad_interface_all.PREFERRED_GIVEN_NAME%TYPE;
1200 l_felony_convicted_flag VARCHAR2(1);
1201 BEGIN
1202
1203 l_enable_log := igs_ad_imp_001.g_enable_log;
1204 l_prog_label := 'igs.plsql.igs_ad_imp_002.update_person';
1205 l_label := 'igs.plsql.igs_ad_imp_002.update_person.';
1206 l_oss_ext_attr_val := 'Y';
1207
1208 -- Call Log header
1209 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1210
1211 IF (l_request_id IS NULL) THEN
1212 l_request_id := fnd_global.conc_request_id;
1213 END IF;
1214
1215 l_label := 'igs.plsql.igs_ad_imp_002.update_person.begin';
1216 l_debug_str := 'Interface Id : ' || P_person_rec.INTERFACE_ID;
1217
1218 fnd_log.string_with_context( fnd_log.level_procedure,
1219 l_label,
1220 l_debug_str, NULL,
1221 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1222 END IF;
1223
1224 -- validations before update
1225
1226 -- Validate the title
1227 IF P_Person_Rec.pre_name_adjunct IS NOT NULL THEN
1228 IF NOT
1229 (igs_pe_pers_imp_001.validate_lookup_type_code('CONTACT_TITLE',P_Person_Rec.pre_name_adjunct,222))
1230 THEN
1231 IF l_enable_log = 'Y' THEN
1232 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E201','IGS_AD_INTERFACE_ALL');
1233 END IF;
1234
1235 UPDATE IGS_AD_INTERFACE_ALL
1236 SET ERROR_CODE = 'E201',
1237 STATUS = '3'
1238 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
1239 RETURN;
1240 END IF;
1241 END IF;
1242
1243 -- Validate Sex.
1244 IF P_Person_Rec.sex IS NOT NULL THEN
1245 IF NOT
1246 (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_GENDER',p_Person_Rec.sex,222))
1247 THEN
1248 IF l_enable_log = 'Y' THEN
1249 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E202','IGS_AD_INTERFACE_ALL');
1250 END IF;
1251
1252 UPDATE IGS_AD_INTERFACE_ALL
1253 SET ERROR_CODE = 'E202',
1254 STATUS = '3'
1255 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
1256 RETURN;
1257 END IF;
1258 END IF;
1259
1260 -- Validate birth_dt
1261 IF ( ((P_Person_Rec.birth_dt IS NOT NULL) AND (P_Person_Rec.birth_dt > SYSDATE)) ) THEN
1262 IF l_enable_log = 'Y' THEN
1263 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E203','IGS_AD_INTERFACE_ALL');
1264 END IF;
1265
1266 UPDATE IGS_AD_INTERFACE_ALL
1267 SET ERROR_CODE = 'E203',
1268 STATUS = '3'
1269 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
1270 RETURN;
1271 END IF;
1272
1273 IF p_person_rec.pref_alternate_id IS NOT NULL THEN
1274 -- Added as a fix for Bug Number 2333026
1275 OPEN c_pref_alt_id_type;
1276 FETCH c_pref_alt_id_type INTO l_pref_altid_type;
1277 IF (c_pref_alt_id_type%NOTFOUND) THEN
1278 l_pref_altid_type := NULL;
1279 l_pref_altid := NULL;
1280
1281 -- (pathipat) For Bug: 2485638
1282 IF l_enable_log = 'Y' THEN
1283 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E285','IGS_AD_INTERFACE_ALL');
1284 END IF;
1285
1286 UPDATE IGS_AD_INTERFACE_ALL
1287 SET ERROR_CODE = 'E285',
1288 STATUS = '3'
1289 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
1290 CLOSE c_pref_alt_id_type;
1291 RETURN;
1292
1293 ELSE
1294 --validate Person ID type
1295 OPEN api_type_cur(l_pref_altid_type);
1296 FETCH api_type_cur INTO api_type_rec;
1297 CLOSE api_type_cur;
1298
1299 -- Validate the format mask
1300 IF api_type_rec.format_mask IS NOT NULL THEN
1301 IF NOT igs_en_val_api.fm_equal(p_person_rec.PREF_ALTERNATE_ID,api_type_rec.format_mask) THEN
1302 IF l_enable_log = 'Y' THEN
1303 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E268','IGS_AD_INTERFACE_ALL');
1304 END IF;
1305
1306 UPDATE IGS_AD_INTERFACE_ALL
1307 SET ERROR_CODE = 'E268',
1308 STATUS = '3'
1309 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
1310 CLOSE c_pref_alt_id_type;
1311 RETURN;
1312 END IF;
1313 END IF;
1314 l_pref_altid := p_person_rec.PREF_ALTERNATE_ID;
1315
1316 END IF;
1317 CLOSE c_pref_alt_id_type;
1318 ELSE
1319 l_pref_altid_type := NULL;
1320 l_pref_altid := NULL;
1321 END IF;
1322
1323
1324 OPEN hz_parties_cur(p_person_id);
1325 FETCH hz_parties_cur INTO l_last_update_date, l_rowid;
1326 IF hz_parties_cur%NOTFOUND THEN
1327 IF l_enable_log = 'Y' THEN
1328 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E019','IGS_AD_INTERFACE_ALL');
1329 END IF;
1330
1331 UPDATE IGS_AD_INTERFACE_ALL
1332 SET ERROR_CODE = 'E019',
1333 STATUS = '3'
1334 WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
1335 CLOSE hz_parties_cur;
1336
1337 RETURN;
1338 END IF;
1339 CLOSE hz_parties_cur;
1340
1341 -- Validate the OSS extensible attributes
1342 validate_oss_ext_attr(p_person_rec,P_PERSON_ID,l_oss_ext_attr_val);
1343
1344 IF l_oss_ext_attr_val = 'N' THEN
1345 -- The validation failed for OSS extensible attributes. The record would have updated with status and error code in the above procedure validate_oss_ext_attr
1346 RETURN;
1347 END IF;
1348
1349
1350 -- Open Cursor for the ADMISSION table for the particular person to import the
1351 -- selected attributes from the INTERFACE table
1352 OPEN c_null_hdlg_per_cur(p_person_id);
1353 FETCH c_null_hdlg_per_cur INTO c_null_hdlg_per_rec;
1354 CLOSE c_null_hdlg_per_cur;
1355
1356 IF (p_person_rec.preferred_given_name IS NOT NULL)
1357 THEN
1358 l_preferred_given_name := p_person_rec.preferred_given_name;
1359 ELSE
1360 IF (p_person_rec.given_names = c_null_hdlg_per_rec.given_names)
1361 THEN
1362 l_preferred_given_name := null;
1363 ELSE
1364 -- nsidana bug 4063206 : Preferred given name to be derived from given names.
1365 -- Copy the first part of the first name
1366 IF (instr(p_person_rec.given_names,' ') = 0) then
1367 l_preferred_given_name := substr(p_person_rec.given_names,1,length(p_person_rec.given_names));
1368 ELSE
1369 l_preferred_given_name := substr(p_person_rec.given_names,1,instr(p_person_rec.given_names,' '));
1370 END IF;
1371 END IF;
1372 END IF;
1373
1374 SAVEPOINT BEFORE_UPDATE;
1375
1376 BEGIN
1377 -- Update Person
1378 IGS_PE_PERSON_PKG.UPDATE_ROW(
1379 X_LAST_UPDATE_DATE => l_last_update_date,
1380 X_MSG_COUNT => l_msg_count,
1381 X_MSG_DATA => l_msg_data,
1382 X_RETURN_STATUS=> l_return_status,
1383 X_ROWID=> l_rowid,
1384 X_PERSON_ID => p_person_id,
1385 X_PERSON_NUMBER => c_null_hdlg_per_rec.person_number,
1386 X_SURNAME => NVL(p_person_rec.surname,c_null_hdlg_per_rec.surname),
1387 X_MIDDLE_NAME => NVL(p_person_rec.middle_name,c_null_hdlg_per_rec.middle_name),
1388 X_GIVEN_NAMES=> NVL(p_person_rec.given_names,c_null_hdlg_per_rec.given_names),
1389 X_SEX => NVL(p_person_rec.sex,c_null_hdlg_per_rec.sex),
1390 X_TITLE => NVL(p_person_rec.title,c_null_hdlg_per_rec.title),
1391 X_STAFF_MEMBER_IND => c_null_hdlg_per_rec.staff_member_ind,
1392 X_DECEASED_IND => c_null_hdlg_per_rec.deceased_ind,
1393 X_SUFFIX => NVL(p_person_rec.suffix,c_null_hdlg_per_rec.suffix),
1394 X_PRE_NAME_ADJUNCT => NVL(p_person_rec.pre_name_adjunct,c_null_hdlg_per_rec.pre_name_adjunct), ---here
1395 X_ARCHIVE_EXCLUSION_IND => c_null_hdlg_per_rec.archive_exclusion_ind,
1396 X_ARCHIVE_DT => c_null_hdlg_per_rec.archive_dt,
1397 X_PURGE_EXCLUSION_IND => c_null_hdlg_per_rec.purge_exclusion_ind,
1398 X_PURGE_DT => c_null_hdlg_per_rec.purge_dt,
1399 X_DECEASED_DATE => c_null_hdlg_per_rec.deceased_date,
1400 X_PROOF_OF_INS => NVL(p_person_rec.proof_of_ins,c_null_hdlg_per_rec.proof_of_ins),
1401 X_PROOF_OF_IMMU=> NVL(p_person_rec.proof_of_immu,c_null_hdlg_per_rec.proof_of_immu),
1402 X_BIRTH_DT => NVL(p_person_rec.birth_dt,c_null_hdlg_per_rec.birth_dt),
1403 X_SALUTATION => c_null_hdlg_per_rec.salutation,
1404 X_ORACLE_USERNAME => c_null_hdlg_per_rec.oracle_username,
1405 X_PREFERRED_GIVEN_NAME=> NVL(l_preferred_given_name,c_null_hdlg_per_rec.preferred_given_name),
1406 X_EMAIL_ADDR=> c_null_hdlg_per_rec.email_addr,
1407 X_LEVEL_OF_QUAL_ID => NVL(p_person_rec.level_of_qual_id,c_null_hdlg_per_rec.level_of_qual_id),
1408 X_MILITARY_SERVICE_REG=> NVL( p_person_rec.military_service_reg,c_null_hdlg_per_rec.military_service_reg),
1409 X_VETERAN=> NVL(p_person_rec.veteran,'VETERAN_NOT'), --ssawhney 2203778 lookup_code
1410 x_hz_parties_ovn => c_null_hdlg_per_rec.object_version_number,
1411 X_attribute_CATEGORY => NVL( p_person_rec.attribute_category,c_null_hdlg_per_rec.attribute_category),
1412 X_attribute1 => NVL(p_person_rec.attribute1,c_null_hdlg_per_rec.attribute1),
1413 X_attribute2 => NVL(p_person_rec.attribute2,c_null_hdlg_per_rec.attribute2),
1414 X_attribute3 => NVL(p_person_rec.attribute3,c_null_hdlg_per_rec.attribute3),
1415 X_attribute4 => NVL(p_person_rec.attribute4,c_null_hdlg_per_rec.attribute4),
1416 X_attribute5 => NVL(p_person_rec.attribute5,c_null_hdlg_per_rec.attribute5),
1417 X_attribute6 => NVL(p_person_rec.attribute6,c_null_hdlg_per_rec.attribute6),
1418 X_attribute7 => NVL(p_person_rec.attribute7,c_null_hdlg_per_rec.attribute7),
1419 X_attribute8 => NVL(p_person_rec.attribute8,c_null_hdlg_per_rec.attribute8),
1420 X_attribute9 => NVL(p_person_rec.attribute9,c_null_hdlg_per_rec.attribute9),
1421 X_attribute10 => NVL(p_person_rec.attribute10,c_null_hdlg_per_rec.attribute10),
1422 X_attribute11 => NVL(p_person_rec.attribute11,c_null_hdlg_per_rec.attribute11),
1423 X_attribute12 => NVL(p_person_rec.attribute12,c_null_hdlg_per_rec.attribute12),
1424 X_attribute13 => NVL(p_person_rec.attribute13,c_null_hdlg_per_rec.attribute13),
1425 X_attribute14 => NVL(p_person_rec.attribute14,c_null_hdlg_per_rec.attribute14),
1426 X_attribute15 => NVL(p_person_rec.attribute15,c_null_hdlg_per_rec.attribute15),
1427 X_attribute16 => NVL(p_person_rec.attribute16,c_null_hdlg_per_rec.attribute16),
1428 X_attribute17 => NVL(p_person_rec.attribute17,c_null_hdlg_per_rec.attribute17),
1429 X_attribute18 => NVL(p_person_rec.attribute18,c_null_hdlg_per_rec.attribute18),
1430 X_attribute19 => NVL(p_person_rec.attribute19,c_null_hdlg_per_rec.attribute19),
1431 X_attribute20 => NVL(p_person_rec.attribute20,c_null_hdlg_per_rec.attribute20),
1432 X_PERSON_ID_TYPE=> l_pref_altid_type,
1433 X_API_PERSON_ID => l_pref_altid,
1434 X_MODE => 'R',
1435 X_attribute21 => NVL(p_person_rec.attribute21,c_null_hdlg_per_rec.attribute21),
1436 X_attribute22 => NVL(p_person_rec.attribute22,c_null_hdlg_per_rec.attribute22),
1437 X_attribute23 => NVL(p_person_rec.attribute23,c_null_hdlg_per_rec.attribute23),
1438 X_attribute24 => NVL(p_person_rec.attribute24,c_null_hdlg_per_rec.attribute24)
1439 );
1440 IF p_person_rec.level_of_qual_id IS NOT NULL OR p_person_rec.proof_of_ins IS NOT NULL OR p_person_rec.proof_of_immu IS NOT NULL OR
1441 p_person_rec.military_service_reg IS NOT NULL OR p_person_rec.veteran IS NOT NULL OR p_person_rec.birth_city IS NOT NULL OR
1442 p_person_rec.birth_country IS NOT NULL OR p_person_rec.felony_convicted_flag IS NOT NULL THEN
1443
1444 OPEN pe_hz_parties_cur(p_person_id);
1445 FETCH pe_hz_parties_cur INTO tlinfo2;
1446 CLOSE pe_hz_parties_cur;
1447
1448 IF p_person_rec.felony_convicted_flag = FND_API.G_MISS_CHAR THEN
1449 l_felony_convicted_flag := NULL;
1450 ELSE
1451 l_felony_convicted_flag := NVL(p_person_rec.felony_convicted_flag,tlinfo2.felony_convicted_flag);
1452 END IF;
1453
1454 IGS_PE_HZ_PARTIES_PKG.ADD_ROW(
1455 X_ROWID => tlinfo2.ROWID,
1456 X_PARTY_ID => p_person_id,
1457 X_DECEASED_IND => tlinfo2.deceased_ind,
1458 X_ARCHIVE_EXCLUSION_IND => tlinfo2.archive_exclusion_ind,
1459 X_ARCHIVE_DT => tlinfo2.archive_dt,
1460 X_PURGE_EXCLUSION_IND => tlinfo2.purge_exclusion_ind,
1461 X_PURGE_DT => tlinfo2.purge_dt,
1462 X_ORACLE_USERNAME => tlinfo2.oracle_username,
1463 X_PROOF_OF_INS => NVL(p_person_rec.proof_of_ins,tlinfo2.proof_of_ins),
1464 X_PROOF_OF_IMMU => NVL(p_person_rec.proof_of_immu,tlinfo2.proof_of_immu),
1465 X_LEVEL_OF_QUAL => NVL(p_person_rec.level_of_qual_id,tlinfo2.level_of_qual),
1466 X_MILITARY_SERVICE_REG => NVL(p_person_rec.military_service_reg,tlinfo2.military_service_reg),
1467 X_VETERAN => NVL(p_person_rec.veteran,tlinfo2.veteran),
1468 X_INSTITUTION_CD => tlinfo2.institution_cd,
1469 X_OI_LOCAL_INSTITUTION_IND => tlinfo2.oi_local_institution_ind,
1470 X_OI_OS_IND => tlinfo2.oi_os_ind,
1471 X_OI_GOVT_INSTITUTION_CD => tlinfo2.oi_govt_institution_cd,
1472 X_OI_INST_CONTROL_TYPE => tlinfo2.oi_inst_control_type,
1473 X_OI_INSTITUTION_TYPE => tlinfo2.oi_institution_type,
1474 X_OI_INSTITUTION_STATUS => tlinfo2.oi_institution_status,
1475 X_OU_START_DT => tlinfo2.ou_start_dt,
1476 X_OU_END_DT => tlinfo2.ou_end_dt,
1477 X_OU_MEMBER_TYPE => tlinfo2.ou_member_type,
1478 X_OU_ORG_STATUS => tlinfo2.ou_org_status,
1479 X_OU_ORG_TYPE => tlinfo2.ou_org_type,
1480 X_INST_ORG_IND => tlinfo2.inst_org_ind,
1481 X_FUND_AUTHORIZATION => tlinfo2.fund_authorization,
1482 X_PE_INFO_VERIFY_TIME => tlinfo2.pe_info_verify_time,
1483 X_birth_city => NVL(p_person_rec.birth_city,tlinfo2.birth_city),
1484 X_birth_country => NVL(p_person_rec.birth_country,tlinfo2.birth_country),
1485 x_oss_org_unit_cd => NULL, --mmkumar, party number impact
1486 X_felony_convicted_flag => l_felony_convicted_flag,
1487 X_MODE => 'R'
1488 );
1489 END IF;
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493
1494 ROLLBACK TO BEFORE_UPDATE;
1495
1496 IF pe_hz_parties_cur%ISOPEN THEN
1497 CLOSE pe_hz_parties_cur;
1498 END IF;
1499 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1500
1501 IF l_message_name = 'IGS_PE_UNIQUE_PID' THEN
1502 l_error_code := 'E567';
1503 ELSE
1504 l_error_code := 'E014';
1505 END IF;
1506
1507 UPDATE IGS_AD_INTERFACE_all
1508 SET status = '3',
1509 error_code = l_error_code
1510 WHERE interface_id = p_person_rec.interface_id;
1511
1512
1513 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1514 l_msg_data := SQLERRM ; --ssomani, added this 3/15/01
1515 IF (l_request_id IS NULL) THEN
1516 l_request_id := fnd_global.conc_request_id;
1517 END IF;
1518
1519 l_label := 'igs.plsql.igs_ad_imp_002.update_person.exception'||l_error_code;
1520
1521 l_debug_str := 'IGS_AD_IMP_002.Update_Person' || ' Exception from IGS_PE_PERSON_PKG ' ||
1522 ' Interface Id : ' || ( P_person_rec.INTERFACE_ID) ||
1523 ' Status : 3' || ' ErrorCode : '||l_error_code||' SQLERRM:' || SQLERRM;
1524
1525 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1526 END IF;
1527
1528 IF l_enable_log = 'Y' THEN
1529 igs_ad_imp_001.logerrormessage(p_person_rec.interface_id,l_error_code,'IGS_AD_INTERFACE_ALL');
1530 END IF;
1531 RETURN;
1532 END;
1533
1534 IF (l_Return_Status IN ('E','U') ) THEN
1535
1536 l_msg_data := l_msg_data ||'-'||SQLERRM;
1537
1538 ROLLBACK TO BEFORE_UPDATE;
1539
1540 UPDATE IGS_AD_INTERFACE_all
1541 SET STATUS = '3',
1542 ERROR_CODE = 'E014'
1543 WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
1544
1545 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1546
1547 IF (l_request_id IS NULL) THEN
1548 l_request_id := fnd_global.conc_request_id;
1549 END IF;
1550
1551 l_label := 'igs.plsql.igs_ad_imp_002.update_person.exception'||'E014';
1552
1553 l_debug_str := 'IGS_AD_IMP_002.Update_Person ' || 'Error from IGS_PE_PERSON_PKG. HzMesg : ' || l_msg_data ||
1554 ' Interface Id : ' || (P_person_rec.INTERFACE_ID) ||
1555 ' Status : 3' || ' ErrorCode : E014';
1556
1557 fnd_log.string_with_context( fnd_log.level_exception,
1558 l_label,
1559 l_debug_str, NULL,
1560 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1561 END IF;
1562
1563 IF l_enable_log = 'Y' THEN
1564 igs_ad_imp_001.logerrormessage(P_person_rec.INTERFACE_ID,'E014','IGS_AD_INTERFACE_ALL');
1565 END IF;
1566 RETURN;
1567
1568 ELSE
1569 UPDATE IGS_AD_INTERFACE_all
1570 SET STATUS = '1',
1571 ERROR_CODE = NULL --ssomani, added this 3/15/01
1572 WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
1573
1574 COMMIT;
1575 END IF;
1576
1577
1578 DECLARE
1579 CURSOR check_addr_dup_cur(cp_person_id hz_party_sites.party_id%TYPE,
1580 cp_addr_rec addr_cur%ROWTYPE) IS
1581 SELECT site.location_id , site.party_site_id
1582 FROM hz_locations loc, hz_party_sites site
1583 WHERE site.party_id = cp_person_id
1584 AND site.location_id = loc.location_id
1585 AND UPPER(NVL(loc.address1,'X')) = UPPER(NVL(cp_addr_rec.addr_line_1,'X'))
1586 AND UPPER(NVL(loc.address2,'X')) = UPPER(NVL(cp_addr_rec.addr_line_2,'X'))
1587 AND UPPER(NVL(loc.address3,'X')) = UPPER(NVL(cp_addr_rec.addr_line_3,'X'))
1588 AND UPPER(NVL(loc.address4,'X')) = UPPER(NVL(cp_addr_rec.addr_line_4,'X'))
1589 AND UPPER(NVL(loc.city,'X')) = UPPER(NVL(cp_addr_rec.city,'X'))
1590 AND UPPER(NVL(loc.state,'X')) = UPPER(NVL(cp_addr_rec.state,'X'))
1591 AND loc.country = cp_addr_rec.country
1592 AND UPPER(NVL(loc.county,'X')) = UPPER(NVL(cp_addr_rec.county,'X'))
1593 AND UPPER(NVL(loc.province,'X')) = UPPER(NVL(cp_addr_rec.province,'X'));
1594
1595 l_party_site_id hz_party_sites.party_site_id%TYPE;
1596
1597 BEGIN
1598
1599 g_addr_process := FALSE;
1600
1601 OPEN addr_cur(P_person_rec.Interface_Id);
1602 LOOP
1603 FETCH addr_cur INTO addr_rec;
1604
1605 EXIT WHEN addr_cur%NOTFOUND;
1606
1607 ADDR_REC.country := UPPER(ADDR_REC.country);
1608 l_location_id := NULL;
1609
1610 OPEN check_addr_dup_cur(p_person_id,addr_rec);
1611 FETCH check_addr_dup_cur INTO l_location_id,l_party_site_id;
1612 CLOSE check_addr_dup_cur;
1613
1614 IF l_location_id IS NOT NULL THEN
1615 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1616
1617 IF (l_request_id IS NULL) THEN
1618 l_request_id := fnd_global.conc_request_id;
1619 END IF;
1620
1621 l_label := 'igs.plsql.igs_ad_imp_002.update_person.duplicate_address_exists';
1622 l_debug_str := 'Interface Id : ' || P_person_rec.INTERFACE_ID;
1623
1624 fnd_log.string_with_context( fnd_log.level_procedure,
1625 l_label,
1626 l_debug_str, NULL,
1627 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1628 END IF;
1629
1630 UPDATE_ADDRESS( P_ADDR_REC => ADDR_REC,
1631 P_PERSON_ID => p_person_id,
1632 P_LOCATION_ID => l_location_id,
1633 p_party_site_id => l_party_site_id);
1634 ELSE
1635 -- Address not exists. Create it.
1636 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1637
1638 IF (l_request_id IS NULL) THEN
1639 l_request_id := fnd_global.conc_request_id;
1640 END IF;
1641
1642 l_label := 'igs.plsql.igs_ad_imp_002.update_person.duplicate_address_doesnot_exist';
1643 l_debug_str := 'Interface Id : ' || P_person_rec.INTERFACE_ID;
1644
1645 fnd_log.string_with_context( fnd_log.level_procedure,
1646 l_label,
1647 l_debug_str, NULL,
1648 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1649 END IF;
1650
1651
1652 CREATE_ADDRESS(P_addr_rec => addr_rec,
1653 P_PERSON_ID =>p_person_id,
1654 P_STATUS=>L_STATUS,
1655 p_error_code=>L_ERROR_CODE);
1656 END IF;
1657 END LOOP;
1658 CLOSE addr_cur;
1659
1660 IF g_addr_process THEN
1661 igs_pe_wf_gen.ti_addr_chg_persons(NVL(igs_pe_wf_gen.ti_addr_chg_persons.LAST,0)+1) := p_person_id;
1662 END IF;
1663 END;
1664
1665 BEGIN
1666 OPEN api_cur(p_person_rec.interface_id);
1667 LOOP
1668 FETCH api_cur INTO api_rec;
1669 EXIT WHEN api_cur%NOTFOUND;
1670
1671 api_rec.person_id_type := UPPER(api_rec.person_id_type);
1672 api_rec.alternate_id := UPPER(api_rec.alternate_id);
1673
1674 create_api(p_api_rec =>api_rec,
1675 p_person_id =>p_person_id,
1676 p_status =>l_status,
1677 p_error_code => l_error_code);
1678
1679 IF l_status = '3' THEN
1680 UPDATE igs_ad_api_int_all
1681 SET status = l_status,
1682 ERROR_CODE = l_error_code
1683 WHERE interface_api_id = api_rec.interface_api_id;
1684 UPDATE igs_ad_interface_all
1685 SET status = '4',
1686 ERROR_CODE = 'E007'
1687 WHERE interface_id = p_person_rec.interface_id;
1688
1689 IF l_enable_log = 'Y' THEN
1690 igs_ad_imp_001.logerrormessage(P_Person_Rec.Interface_Id,'E007','IGS_AD_INTERFACE_ALL');
1691 END IF;
1692
1693 ELSIF l_status = '1' THEN
1694
1695 UPDATE igs_ad_api_int_all
1696 SET status = '1',
1697 ERROR_CODE = NULL
1698 WHERE interface_api_id = api_rec.interface_api_id;
1699
1700 END IF;
1701 END LOOP;
1702 CLOSE api_cur;
1703 END;
1704 END UPDATE_PERSON;
1705
1706
1707 PROCEDURE CREATE_API(p_api_rec IN IGS_AD_API_INT_ALL%ROWTYPE,
1708 p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
1709 p_status OUT NOCOPY VARCHAR2,
1710 p_error_code OUT NOCOPY VARCHAR2) AS
1711
1712 /*
1713 || Created By : pkpatel
1714 || Created On : 10-JUN-2002
1715 || Purpose : Bug No:2402077 Validate the Person ID type and Format mask for Alternate ID
1716 || Known limitations, enhancements or remarks :
1717 || Change History :
1718 || Who When What
1719 || (reverse chronological order - newest change first)
1720 || pkpatel 15-JAN-2003 Bug NO: 2397876
1721 || Added all the missing validations and replaced E008 with proper error codes
1722 || asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row changed
1723 */
1724
1725 CURSOR source_type_cur(cp_source_type igs_pe_src_types_all.source_type%TYPE) Is
1726 SELECT source_type_id
1727 FROM igs_pe_src_types_all
1728 WHERE source_type = cp_source_type;
1729
1730 l_rowid VARCHAR2(25);
1731 lnDupExist NUMBER;
1732 l_start_dt IGS_AD_API_INT.START_DT%TYPE;
1733 l_message_name VARCHAR2(30);
1734 l_app VARCHAR2(50);
1735 l_prog_label VARCHAR2(4000);
1736 l_label VARCHAR2(4000);
1737 l_debug_str VARCHAR2(4000);
1738 l_enable_log VARCHAR2(1);
1739 l_request_id NUMBER(10);
1740 l_ucas_action VARCHAR2(1);
1741 l_ucas_error_code VARCHAR2(10);
1742 l_call_ucas_user_hook BOOLEAN;
1743 l_source_type_id1 NUMBER;
1744 l_source_type_id2 NUMBER;
1745
1746 FUNCTION validate_api(p_api_rec IN IGS_AD_API_INT_ALL%ROWTYPE )
1747 RETURN BOOLEAN AS
1748 /*
1749 || Created By : pkpatel
1750 || Created On : 10-JUN-2002
1751 || Purpose : Bug No:2402077 Validate the Person ID type and Format mask for Alternate ID
1752 || Known limitations, enhancements or remarks :
1753 || Change History :
1754 || Who When What
1755 || skpandey 09-Jan-2006 Changed the definition of region_cd_cur cursor as a part of New Geography Model
1756 || gmaheswa 29-Sep-2004 Bug:3787210 Added Closed indicator check for the Alternate Person Id type.
1757 || (reverse chronological order - newest change first)
1758 */
1759
1760 l_exists VARCHAR2(1);
1761
1762 CURSOR api_type_cur(cp_person_id_type igs_pe_person_id_typ.person_id_type%TYPE) IS
1763 SELECT format_mask, region_ind
1764 FROM igs_pe_person_id_typ
1765 WHERE person_id_type = cp_person_id_type
1766 AND closed_ind = 'N';
1767
1768 CURSOR region_cd_cur(cp_geography_type hz_geographies.geography_type%TYPE,
1769 cp_geography_cd hz_geographies.geography_code%TYPE,
1770 cp_country_cd hz_geographies.country_code%TYPE) IS
1771 SELECT 'X'
1772 FROM hz_geographies
1773 WHERE GEOGRAPHY_TYPE = cp_geography_type
1774 AND geography_code = cp_geography_cd
1775 AND country_code = cp_country_cd;
1776
1777 api_type_rec api_type_cur%ROWTYPE;
1778 BEGIN
1779
1780 --validate Alternate Person ID descriptive Flex field
1781 IF NOT igs_ad_imp_018.validate_desc_flex(
1782 p_attribute_category =>p_api_rec.attribute_category,
1783 p_attribute1 =>p_api_rec.attribute1 ,
1784 p_attribute2 =>p_api_rec.attribute2 ,
1785 p_attribute3 =>p_api_rec.attribute3 ,
1786 p_attribute4 =>p_api_rec.attribute4 ,
1787 p_attribute5 =>p_api_rec.attribute5 ,
1788 p_attribute6 =>p_api_rec.attribute6 ,
1789 p_attribute7 =>p_api_rec.attribute7 ,
1790 p_attribute8 =>p_api_rec.attribute8 ,
1791 p_attribute9 =>p_api_rec.attribute9 ,
1792 p_attribute10 =>p_api_rec.attribute10 ,
1793 p_attribute11 =>p_api_rec.attribute11 ,
1794 p_attribute12 =>p_api_rec.attribute12 ,
1795 p_attribute13 =>p_api_rec.attribute13 ,
1796 p_attribute14 =>p_api_rec.attribute14 ,
1797 p_attribute15 =>p_api_rec.attribute15 ,
1798 p_attribute16 =>p_api_rec.attribute16 ,
1799 p_attribute17 =>p_api_rec.attribute17 ,
1800 p_attribute18 =>p_api_rec.attribute18 ,
1801 p_attribute19 =>p_api_rec.attribute19 ,
1802 p_attribute20 =>p_api_rec.attribute20 ,
1803 p_desc_flex_name =>'IGS_PE_ALT_PERS_ID_FLEX' ) THEN
1804
1805 p_error_code:='E255';
1806 RAISE NO_DATA_FOUND;
1807 END IF;
1808
1809 --validate Person ID type
1810 OPEN api_type_cur(p_api_rec.person_id_type);
1811 FETCH api_type_cur INTO api_type_rec;
1812 IF api_type_cur%NOTFOUND THEN
1813 CLOSE api_type_cur;
1814 p_error_code:='E258';
1815 RAISE NO_DATA_FOUND;
1816 ELSE
1817 CLOSE api_type_cur;
1818 END IF;
1819
1820 -- Validate the format mask
1821 IF api_type_rec.format_mask IS NOT NULL THEN
1822 IF NOT igs_en_val_api.fm_equal(p_api_rec.alternate_id,api_type_rec.format_mask) THEN
1823 p_error_code:='E268';
1824 RAISE NO_DATA_FOUND;
1825 END IF;
1826 END IF;
1827
1828 -- Validation for Region Code
1829 IF api_type_rec.region_ind IS NULL OR api_type_rec.region_ind = 'N' THEN
1830 IF p_api_rec.region_cd IS NOT NULL THEN
1831 p_error_code:='E573';
1832 RAISE NO_DATA_FOUND;
1833 END IF;
1834 ELSE
1835 IF p_api_rec.region_cd IS NULL THEN
1836 p_error_code:='E574';
1837 RAISE NO_DATA_FOUND;
1838 ELSE
1839 OPEN region_cd_cur('STATE',p_api_rec.region_cd,FND_PROFILE.VALUE('OSS_COUNTRY_CODE'));
1840 FETCH region_cd_cur INTO l_exists;
1841 IF region_cd_cur%NOTFOUND THEN
1842 CLOSE region_cd_cur;
1843 p_error_code:='E575';
1844 RAISE NO_DATA_FOUND;
1845 END IF;
1846 CLOSE region_cd_cur;
1847 END IF;
1848 END IF;
1849
1850 RETURN TRUE;
1851
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 p_status := '3';
1855 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1856
1857 IF (l_request_id IS NULL) THEN
1858 l_request_id := fnd_global.conc_request_id;
1859 END IF;
1860 l_label := 'igs.plsql.igs_ad_imp_002.validate_api.exception'||p_error_code;
1861 l_debug_str := 'Igs_Ad_Imp_002.Create_Api.validate_api'
1862 ||'Validation Failed'
1863 ||'Interface_Api_Id:'
1864 ||(p_api_rec.Interface_api_Id)
1865 ||' Status:3 '
1866 ||'Error Code: '||p_error_code ||' SQLERRM: '|| SQLERRM;
1867
1868 fnd_log.string_with_context( fnd_log.level_exception,
1869 l_label,
1870 l_debug_str, NULL,
1871 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1872 END IF;
1873
1874 IF l_enable_log = 'Y' THEN
1875 igs_ad_imp_001.logerrormessage(p_api_rec.Interface_api_Id,p_error_code,'IGS_AD_API_INT_ALL');
1876 END IF;
1877
1878 RETURN FALSE;
1879 END validate_api;
1880
1881 BEGIN
1882
1883 l_enable_log := igs_ad_imp_001.g_enable_log;
1884 l_prog_label := 'igs.plsql.igs_ad_imp_002.create_api';
1885 l_label := 'igs.plsql.igs_ad_imp_002.create_api.';
1886
1887 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1888
1889 IF (l_request_id IS NULL) THEN
1890 l_request_id := fnd_global.conc_request_id;
1891 END IF;
1892
1893 l_label := 'igs.plsql.igs_ad_imp_002.create_api.begin';
1894 l_debug_str := 'start of create_api proc';
1895
1896 fnd_log.string_with_context( fnd_log.level_procedure,
1897 l_label,
1898 l_debug_str, NULL,
1899 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1900 END IF;
1901
1902 OPEN source_type_cur('UCAS PER');
1903 FETCH source_type_cur INTO l_source_type_id1;
1904 CLOSE source_type_cur;
1905
1906 OPEN source_type_cur('UCAS APPL');
1907 FETCH source_type_cur INTO l_source_type_id2;
1908 CLOSE source_type_cur;
1909
1910 IF ((l_source_type_id1 = Igs_Pe_Identify_Dups.g_source_type_id) OR (l_source_type_id2 = Igs_Pe_Identify_Dups.g_source_type_id))THEN
1911 -- source cat is UCAS PER / UCAS APPL.
1912 l_call_ucas_user_hook := TRUE;
1913 END IF;
1914
1915 IF ((l_call_ucas_user_hook) AND (p_api_rec.person_id_type IN ('UCASID','NMASID','SWASID','GTTRID','UCASREGNO')))
1916 THEN
1917 igs_pe_pers_imp_001.validate_ucas_id(p_api_rec.alternate_id,p_person_id,p_api_rec.person_id_type,l_ucas_action,l_ucas_error_code);
1918
1919 /* S - Skip record. P - Process record. E - Error out record. */
1920
1921 IF (l_ucas_action = 'S')
1922 THEN
1923 p_status := '1';
1924 p_error_code := null;
1925 RETURN;
1926 ELSIF (l_ucas_action = 'E')
1927 THEN
1928 p_status := '3';
1929 p_error_code := 'E560';
1930 RETURN;
1931 END IF;
1932
1933 END IF;
1934
1935 IF validate_api(p_api_rec) THEN
1936
1937 IF p_api_rec.start_dt IS NULL THEN
1938 l_start_dt := TRUNC(SYSDATE);
1939 ELSE
1940 l_start_dt := TRUNC(p_api_rec.start_dt);
1941 END IF;
1942
1943 IF (l_ucas_action = 'P')
1944 THEN
1945 lnDupExist := 0;
1946 ELSE
1947 SELECT COUNT(*)
1948 INTO lnDupExist
1949 FROM IGS_PE_ALT_PERS_ID
1950 WHERE PE_PERSON_ID = P_PERSON_ID
1951 AND API_PERSON_ID = P_API_REC.ALTERNATE_ID
1952 AND PERSON_ID_TYPE = P_API_REC.PERSON_ID_TYPE
1953 AND TRUNC(START_DT) = l_start_dt;
1954 END IF;
1955
1956 IF lnDupExist = 0 THEN
1957
1958 BEGIN
1959 SAVEPOINT before_insert;
1960
1961 Igs_Pe_Alt_Pers_Id_Pkg.insert_row(
1962 X_ROWID => l_rowid,
1963 X_PE_PERSON_ID => P_PERSON_ID,
1964 X_API_PERSON_ID => p_api_rec.ALTERNATE_ID,
1965 X_PERSON_ID_TYPE => p_api_rec.PERSON_ID_TYPE,
1966 X_START_DT => l_start_dt,
1967 X_END_DT => p_api_rec.end_dt,
1968 x_attribute_category => p_api_rec.attribute_category,
1969 x_attribute1 => p_api_rec.attribute1,
1970 x_attribute2 => p_api_rec.attribute2,
1971 x_attribute3 => p_api_rec.attribute3,
1972 x_attribute4 => p_api_rec.attribute4,
1973 x_attribute5 => p_api_rec.attribute5,
1974 x_attribute6 => p_api_rec.attribute6,
1975 x_attribute7 => p_api_rec.attribute7,
1976 x_attribute8 => p_api_rec.attribute8,
1977 x_attribute9 => p_api_rec.attribute9,
1978 x_attribute10 => p_api_rec.attribute10,
1979 x_attribute11 => p_api_rec.attribute11,
1980 x_attribute12 => p_api_rec.attribute12,
1981 x_attribute13 => p_api_rec.attribute13,
1982 x_attribute14 => p_api_rec.attribute14,
1983 x_attribute15 => p_api_rec.attribute15,
1984 x_attribute16 => p_api_rec.attribute16,
1985 x_attribute17 => p_api_rec.attribute17,
1986 x_attribute18 => p_api_rec.attribute18,
1987 x_attribute19 => p_api_rec.attribute19,
1988 x_attribute20 => p_api_rec.attribute20,
1989 x_region_cd => p_api_rec.region_cd,
1990 X_MODE=>'R');
1991 p_status :='1';
1992 p_error_code:= NULL;
1993 RETURN;
1994 EXCEPTION
1995 WHEN OTHERS THEN
1996
1997 ROLLBACK TO before_insert;
1998 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1999
2000 IF l_message_name IN ('IGS_PE_PERS_ID_PRD_OVRLP', 'IGS_PE_SSN_PERS_ID_PRD_OVRLP') THEN
2001 p_error_code := 'E560';
2002 p_status := '3';
2003 ELSIF l_message_name = 'IGS_PE_UNIQUE_PID' THEN
2004 p_error_code := 'E567';
2005 p_status := '3';
2006 ELSIF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
2007 p_error_code := 'E222';
2008 p_status := '3';
2009 ELSIF l_message_name = 'IGS_GE_INVALID_DATE' THEN
2010 p_error_code := 'E208';
2011 p_status := '3';
2012 ELSE
2013 p_error_code := 'E007';
2014 p_status := '3';
2015 END IF;
2016
2017 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2018
2019 IF (l_request_id IS NULL) THEN
2020 l_request_id := fnd_global.conc_request_id;
2021 END IF;
2022
2023 l_label := 'igs.plsql.igs_ad_imp_002.create_api.exception'||p_error_code;
2024
2025 l_debug_str := 'IGS_AD_IMP_002.create_api ' ||'Interface Api Id : '
2026 || p_api_rec.interface_api_id ||' Status : 3 '
2027 || 'ErrorCode : '||p_error_code||' SQLERRM:' || SQLERRM;
2028
2029 fnd_log.string_with_context( fnd_log.level_exception,
2030 l_label,
2031 l_debug_str, NULL,
2032 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2033 END IF;
2034
2035 IF l_enable_log = 'Y' THEN
2036 igs_ad_imp_001.logerrormessage(p_api_rec.interface_api_id,p_error_code,'IGS_AD_API_INT_ALL');
2037 END IF;
2038 RETURN;
2039 END;
2040 ELSE
2041
2042 DECLARE
2043 CURSOR c_null_hdlg_alt_pers_cur(cp_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE,
2044 cp_person_id_type igs_pe_alt_pers_id.person_id_type%TYPE,
2045 cp_start_dt igs_pe_alt_pers_id.start_dt%TYPE,
2046 cp_person_id igs_pe_alt_pers_id.pe_person_id%TYPE)IS
2047 SELECT api.rowid,api.*
2048 FROM igs_pe_alt_pers_id api
2049 WHERE pe_person_id = cp_person_id
2050 AND api_person_id = cp_api_person_id
2051 AND person_id_type = cp_person_id_type
2052 AND TRUNC(start_dt) = cp_start_dt;
2053 c_null_hdlg_alt_pers_cur_rec c_null_hdlg_alt_pers_cur%ROWTYPE;
2054 BEGIN
2055 SAVEPOINT before_api_update;
2056 OPEN c_null_hdlg_alt_pers_cur(p_api_rec.alternate_id,
2057 p_api_rec.person_id_type,
2058 l_start_dt,
2059 p_person_id);
2060 FETCH c_null_hdlg_alt_pers_cur INTO c_null_hdlg_alt_pers_cur_rec;
2061 CLOSE c_null_hdlg_alt_pers_cur;
2062
2063 igs_pe_alt_pers_id_pkg.update_row(
2064 x_rowid =>c_null_hdlg_alt_pers_cur_rec.rowid,
2065 x_pe_person_id =>c_null_hdlg_alt_pers_cur_rec.pe_person_id,
2066 x_api_person_id =>c_null_hdlg_alt_pers_cur_rec.api_person_id,
2067 x_person_id_type => c_null_hdlg_alt_pers_cur_rec.person_id_type,
2068 x_start_dt => NVL(p_api_rec.start_dt,c_null_hdlg_alt_pers_cur_rec.start_dt),
2069 x_end_dt => NVL(p_api_rec.end_dt,c_null_hdlg_alt_pers_cur_rec.end_dt),
2070 x_mode => 'R',
2071 X_ATTRIBUTE_CATEGORY =>NVL(p_api_rec.attribute_category ,c_null_hdlg_alt_pers_cur_rec.attribute_category),
2072 X_ATTRIBUTE1 =>NVL(p_api_rec.attribute1 ,c_null_hdlg_alt_pers_cur_rec.attribute1),
2073 X_ATTRIBUTE2 =>NVL(p_api_rec.attribute2 ,c_null_hdlg_alt_pers_cur_rec.attribute2),
2074 X_ATTRIBUTE3 =>NVL(p_api_rec.attribute3 ,c_null_hdlg_alt_pers_cur_rec.attribute3),
2075 X_ATTRIBUTE4 =>NVL(p_api_rec.attribute4 ,c_null_hdlg_alt_pers_cur_rec.attribute4),
2076 X_ATTRIBUTE5 =>NVL(p_api_rec.attribute5 ,c_null_hdlg_alt_pers_cur_rec.attribute5),
2077 X_ATTRIBUTE6 =>NVL(p_api_rec.attribute6 ,c_null_hdlg_alt_pers_cur_rec.attribute6),
2078 X_ATTRIBUTE7 =>NVL(p_api_rec.attribute7 ,c_null_hdlg_alt_pers_cur_rec.attribute7),
2079 X_ATTRIBUTE8 =>NVL(p_api_rec.attribute8 ,c_null_hdlg_alt_pers_cur_rec.attribute8),
2080 X_ATTRIBUTE9 =>NVL(p_api_rec.attribute9 ,c_null_hdlg_alt_pers_cur_rec.attribute9),
2081 X_ATTRIBUTE10 =>NVL(p_api_rec.attribute10 ,c_null_hdlg_alt_pers_cur_rec.attribute10),
2082 X_ATTRIBUTE11 =>NVL(p_api_rec.attribute11 ,c_null_hdlg_alt_pers_cur_rec.attribute11),
2083 X_ATTRIBUTE12 =>NVL(p_api_rec.attribute12 ,c_null_hdlg_alt_pers_cur_rec.attribute12),
2084 X_ATTRIBUTE13 =>NVL(p_api_rec.attribute13 ,c_null_hdlg_alt_pers_cur_rec.attribute13),
2085 X_ATTRIBUTE14 =>NVL(p_api_rec.attribute14 ,c_null_hdlg_alt_pers_cur_rec.attribute14),
2086 X_ATTRIBUTE15 =>NVL(p_api_rec.attribute15 ,c_null_hdlg_alt_pers_cur_rec.attribute15),
2087 X_ATTRIBUTE16 =>NVL(p_api_rec.attribute16 ,c_null_hdlg_alt_pers_cur_rec.attribute16),
2088 X_ATTRIBUTE17 =>NVL(p_api_rec.attribute17 ,c_null_hdlg_alt_pers_cur_rec.attribute17),
2089 X_ATTRIBUTE18 =>NVL(p_api_rec.attribute18 ,c_null_hdlg_alt_pers_cur_rec.attribute18),
2090 X_ATTRIBUTE19 =>NVL(p_api_rec.attribute19 ,c_null_hdlg_alt_pers_cur_rec.attribute19),
2091 X_ATTRIBUTE20 =>NVL(p_api_rec.attribute20 ,c_null_hdlg_alt_pers_cur_rec.attribute20),
2092 X_REGION_CD =>NVL(p_api_rec.region_cd ,c_null_hdlg_alt_pers_cur_rec.region_cd));
2093
2094 p_status :='1';
2095 p_error_code:= NULL;
2096 RETURN;
2097
2098 EXCEPTION
2099 WHEN OTHERS THEN
2100 ROLLBACK TO before_api_update;
2101 -- To find the message name raised from the TBH
2102 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
2103
2104 IF l_message_name IN ('IGS_PE_PERS_ID_PRD_OVRLP', 'IGS_PE_SSN_PERS_ID_PRD_OVRLP') THEN
2105 p_error_code := 'E560';
2106 ELSIF l_message_name = 'IGS_PE_ALT_END_DT_VAL' THEN
2107 p_error_code := 'E581';
2108 ELSIF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
2109 p_error_code := 'E222';
2110 ELSIF l_message_name = 'IGS_GE_INVALID_DATE' THEN
2111 p_error_code := 'E208';
2112 ELSE
2113 p_error_code := 'E014';
2114 END IF;
2115 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2116 IF (l_request_id IS NULL) THEN
2117 l_request_id := fnd_global.conc_request_id;
2118 END IF;
2119
2120 l_label := 'igs.plsql.igs_ad_imp_002.create_api.exception1'||p_error_code;
2121
2122 l_debug_str := 'IGS_AD_IMP_002.create_api (UPDATE) ' ||'Interface Api Id : '
2123 || p_api_rec.interface_api_id ||'Status : 3'
2124 || 'ErrorCode : '||p_error_code||' SQLERRM:' ||SQLERRM;
2125
2126 fnd_log.string_with_context( fnd_log.level_exception,
2127 l_label,
2128 l_debug_str, NULL,
2129 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2130 END IF;
2131 IF l_enable_log = 'Y' THEN
2132 igs_ad_imp_001.logerrormessage(p_api_rec.interface_api_id,p_error_code,'IGS_AD_API_INT_ALL');
2133 END IF;
2134 p_status := '3';
2135 RETURN;
2136 END;
2137 END IF;
2138 ELSE -- validation failed
2139 p_status :='3';
2140 END IF;
2141 END create_api;
2142
2143 PROCEDURE CREATE_ADDRESS(P_addr_rec IN IGS_ad_Addr_int_all%ROWTYPE,
2144 P_PERSON_ID IN IGS_PE_PERSON.PERSON_ID%TYPE,
2145 P_STATUS OUT NOCOPY VARCHAR2,
2146 p_error_code OUT NOCOPY VARCHAR2) AS
2147 l_rowid VARCHAR2(25);
2148 l_return_status VARCHAR2(100);
2149 l_msg_data VARCHAR2(4000);
2150 l_location_id NUMBER;
2151 l_party_site_id NUMBER;
2152 l_party_site_use_id NUMBER;
2153 l_last_update_date DATE;
2154 l_prog_label VARCHAR2(4000);
2155 l_label VARCHAR2(4000);
2156 l_debug_str VARCHAR2(4000);
2157 l_enable_log VARCHAR2(1);
2158 l_request_id NUMBER(10);
2159 l_party_site_ovn hz_party_sites.object_version_number%TYPE;
2160 l_location_ovn hz_locations.object_version_number%TYPE;
2161
2162 -- local procedure to check whether duplicate record exists for hz_party_site_uses for a party site id
2163 PROCEDURE check_dup_addr_usage(l_party_site_id IN HZ_PARTY_SITE_USES.PARTY_SITE_ID%TYPE,
2164 l_site_use_code IN HZ_PARTY_SITE_USES.SITE_USE_TYPE%TYPE,
2165 l_dup_var OUT NOCOPY VARCHAR2 ) AS
2166
2167 l_var varchar2(5);
2168
2169 CURSOR check_dup_cur(cp_party_site_id HZ_PARTY_SITE_USES.party_site_id%TYPE,
2170 cp_site_use_code HZ_PARTY_SITE_USES.site_use_type%TYPE) IS
2171 SELECT 'X'
2172 FROM HZ_PARTY_SITE_USES
2173 WHERE party_site_id = cp_party_site_id
2174 AND site_use_type = cp_site_use_code;
2175
2176 BEGIN
2177 OPEN check_dup_cur(l_party_site_id,l_site_use_code);
2178 FETCH check_dup_cur INTO l_var;
2179 IF check_dup_cur%FOUND THEN
2180 l_dup_var := 'TRUE';
2181 ELSE
2182 l_dup_var := 'FALSE';
2183 END IF;
2184 CLOSE check_dup_cur;
2185
2186 END check_dup_addr_usage;
2187
2188 --- local procedure to import address usage
2189 PROCEDURE process_addrusage(l_interface_addr_id IN igs_ad_addr_int.interface_addr_id%TYPE) AS
2190
2191 CURSOR c_usage ( cp_interface_addr_id NUMBER) IS
2192 SELECT *
2193 FROM IGS_AD_ADDRUSAGE_INT_all
2194 WHERE interface_addr_id = cp_interface_addr_id
2195 AND status = '2';
2196
2197 l_exists VARCHAR2(1);
2198 l_error_code VARCHAR2(30);
2199 l_dup_var VARCHAR2(10);
2200 l_profile_last_update_date DATE;
2201 l_site_use_last_update_date DATE;
2202 l_interface_addrusage_id igs_ad_addrusage_int.interface_addrusage_id%TYPE;
2203 l_failure_child NUMBER(3);
2204 l_object_version_number NUMBER;
2205 BEGIN
2206
2207 l_failure_child := 0;
2208 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2209
2210 IF (l_request_id IS NULL) THEN
2211 l_request_id := fnd_global.conc_request_id;
2212 END IF;
2213
2214 l_label := 'igs.plsql.igs_ad_imp_002.create_address.process_addrusage_begin';
2215 l_debug_str := 'Interface Addr Id : ' || l_interface_addr_id;
2216
2217 fnd_log.string_with_context( fnd_log.level_procedure,
2218 l_label,
2219 l_debug_str, NULL,
2220 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2221 END IF;
2222
2223 FOR c_usage_rec IN c_usage(l_interface_addr_id)
2224 LOOP
2225 BEGIN
2226 c_usage_rec.site_use_code := UPPER(c_usage_rec.site_use_code);
2227 l_interface_addrusage_id := c_usage_rec.interface_addrusage_id;
2228 IF NOT
2229 (igs_pe_pers_imp_001.validate_lookup_type_code('PARTY_SITE_USE_CODE',c_usage_rec.site_use_code,222))
2230 THEN
2231 l_error_code := 'E211';
2232 RAISE NO_DATA_FOUND;
2233 END IF;
2234 l_dup_var := NULL;
2235 check_dup_addr_usage(l_party_site_id , c_usage_rec.site_use_code,l_dup_var);
2236
2237 IF l_dup_var = 'TRUE' THEN
2238 -- Update is not allowed in party site usage except the STATUS
2239 -- But there is no status column in the interface table hence removing
2240 -- the unnecessary update.
2241 UPDATE IGS_AD_ADDRUSAGE_INT_all
2242 SET STATUS = '1',
2243 ERROR_CODE = NULL
2244 WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
2245
2246
2247 ELSIF l_dup_var = 'FALSE' THEN
2248
2249 l_party_site_use_id := NULL;
2250 l_rowid := NULL;
2251
2252 IGS_PE_PARTY_SITE_USE_PKG.HZ_PARTY_SITE_USES_AK(
2253 p_action => 'INSERT',
2254 p_rowid => l_rowid,
2255 p_party_site_use_id => l_party_site_use_id,
2256 p_party_site_id => l_party_site_id,
2257 p_site_use_type => c_usage_rec.site_use_code,
2258 p_status => 'A',
2259 p_return_status => l_return_status ,
2260 p_msg_data => l_msg_data,
2261 p_last_update_date => l_last_update_date,
2262 p_site_use_last_update_date => l_site_use_last_update_date,
2263 p_profile_last_update_date => l_profile_last_update_date,
2264 p_hz_party_site_use_ovn => l_object_version_number
2265 );
2266
2267 IF (l_return_status IN ('E','U') ) THEN
2268
2269 l_error_code := 'E244';
2270 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2271
2272 IF (l_request_id IS NULL) THEN
2273 l_request_id := fnd_global.conc_request_id;
2274 END IF;
2275
2276 l_label := 'igs.plsql.igs_ad_imp_002.process_addrusage.exception'||'E224';
2277
2278 l_debug_str := 'Interface Address Usage ID: '||c_usage_rec.interface_addrusage_id||' HZMessage: '||l_msg_data||' SQLERRM:' || SQLERRM;
2279
2280 fnd_log.string_with_context( fnd_log.level_exception,
2281 l_label,
2282 l_debug_str, NULL,
2283 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2284 END IF;
2285 RAISE NO_DATA_FOUND;
2286 ELSE
2287 UPDATE IGS_AD_ADDRUSAGE_INT_ALL
2288 SET STATUS = '1',
2289 ERROR_CODE = NULL
2290 WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
2291 END IF;
2292
2293 END IF;
2294 EXCEPTION
2295 WHEN NO_DATA_FOUND THEN
2296
2297 l_failure_child := 1;
2298
2299 UPDATE IGS_AD_ADDRUSAGE_INT_ALL
2300 SET STATUS = '3',
2301 ERROR_CODE = l_error_code
2302 WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
2303
2304 IF l_enable_log = 'Y' THEN
2305 igs_ad_imp_001.logerrormessage(l_interface_addrusage_id,l_error_code,'IGS_AD_ADDRUSAGE_INT_ALL');
2306 END IF;
2307
2308 END;
2309
2310 END LOOP;
2311
2312 -- Update the parent if any of the child fails.
2313 IF l_failure_child = 1 THEN
2314
2315 UPDATE igs_ad_addr_int_all
2316 SET status = '4',
2317 error_code = 'E244'
2318 WHERE interface_addr_id = l_interface_addr_id;
2319
2320 END IF;
2321
2322 EXCEPTION
2323 WHEN OTHERS THEN
2324 UPDATE IGS_AD_ADDRUSAGE_INT_ALL
2325 SET STATUS = '3',
2326 ERROR_CODE = 'E244'
2327 WHERE interface_addrusage_id = l_interface_addrusage_id;
2328
2329 UPDATE igs_ad_addr_int_all
2330 SET status = '4',
2331 error_code = 'E244'
2332 WHERE interface_addr_id = l_interface_addr_id;
2333
2334 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2335
2336 IF (l_request_id IS NULL) THEN
2337 l_request_id := fnd_global.conc_request_id;
2338 END IF;
2339
2340 l_label := 'igs.plsql.igs_ad_imp_002.process_addrusage.exception'||'E244';
2341
2342 l_debug_str := 'IGS_AD_IMP_002.Create_Address.process_addrusage ' ||
2343 'Error from process_addrusage ' ||
2344 ' for Interface addrusage Id : ' || (l_interface_addrusage_id) ||' '|| SQLERRM ;
2345
2346 fnd_log.string_with_context( fnd_log.level_exception,
2347 l_label,
2348 l_debug_str, NULL,
2349 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2350 END IF;
2351
2352 IF l_enable_log = 'Y' THEN
2353 igs_ad_imp_001.logerrormessage(l_interface_addrusage_id,'E244','IGS_AD_ADDRUSAGE_INT_ALL');
2354 END IF;
2355
2356 END process_addrusage;
2357
2358 BEGIN
2359
2360 l_enable_log := igs_ad_imp_001.g_enable_log;
2361 l_prog_label := 'igs.plsql.igs_ad_imp_002.create_address';
2362 l_label := 'igs.plsql.igs_ad_imp_002.create_address.';
2363
2364 -- Call Log header
2365 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2366
2367 IF (l_request_id IS NULL) THEN
2368 l_request_id := fnd_global.conc_request_id;
2369 END IF;
2370
2371 l_label := 'igs.plsql.igs_ad_imp_002.create_address.begin';
2372 l_debug_str := 'Start of create_address proc';
2373
2374 fnd_log.string_with_context( fnd_log.level_procedure,
2375 l_label,
2376 l_debug_str, NULL,
2377 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2378 END IF;
2379
2380 /* Validate all columns before Inserting. */
2381 validate_address( p_addr_rec => p_addr_rec,
2382 p_person_id => p_person_id,
2383 p_status => p_status,
2384 p_error_code => p_error_code);
2385
2386 IF ( p_status = '2' ) THEN
2387
2388 IGS_PE_PERSON_ADDR_PKG.Insert_Row(
2389 P_ACTION => 'INSERT',
2390 P_ROWID => l_RowId,
2391 P_LOCATION_ID => l_location_Id,
2392 P_START_DT => p_addr_rec.Start_date,
2393 P_END_DT => p_addr_rec.End_Date,
2394 P_COUNTRY => p_addr_rec.country,
2395 P_ADDRESS_STYLE => NULL,
2396 P_ADDR_LINE_1 => p_addr_rec.addr_line_1,
2397 P_ADDR_LINE_2 => p_addr_rec.addr_line_2,
2398 P_ADDR_LINE_3 => p_addr_rec.addr_line_3,
2399 P_ADDR_LINE_4 => p_addr_rec.addr_line_4,
2400 P_DATE_LAST_VERIFIED => p_addr_rec.Date_Last_Verified,
2401 P_CORRESPONDENCE => p_addr_rec.CORRESPONDENCE_FLAG,
2402 P_CITY => p_addr_rec.city,
2403 P_STATE => p_addr_rec.state,
2404 P_PROVINCE => p_addr_rec.province,
2405 P_COUNTY => p_addr_rec.county,
2406 P_POSTAL_CODE => p_addr_rec.postcode,
2407 P_ADDRESS_LINES_PHONETIC => NULL,
2408 P_DELIVERY_POINT_CODE => p_addr_rec.delivery_point_code,
2409 P_OTHER_DETAILS_1 => p_addr_rec.other_details_1,
2410 P_OTHER_DETAILS_2 => p_addr_rec.other_details_2,
2411 P_OTHER_DETAILS_3 => p_addr_rec.other_details_3,
2412 L_RETURN_STATUS => l_Return_Status,
2413 L_MSG_DATA => l_Msg_Data,
2414 P_PARTY_ID => P_PERSON_ID,
2415 P_PARTY_SITE_ID => l_Party_Site_Id,
2416 P_PARTY_TYPE => 'PERSON',
2417 P_LAST_UPDATE_DATE => l_last_update_date,
2418 p_party_site_ovn => l_party_site_ovn,
2419 p_location_ovn => l_location_ovn,
2420 p_status => 'A'
2421 );
2422
2423 IF (l_return_status IN ('E','U') ) THEN
2424
2425 UPDATE IGS_AD_ADDR_INT_ALL
2426 SET STATUS = '3',
2427 ERROR_CODE = 'E006'
2428 WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
2429
2430 UPDATE igs_ad_interface_all
2431 SET status = '4',
2432 error_code = 'E006'
2433 WHERE interface_id = p_addr_rec.interface_id;
2434
2435 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2436
2437 IF (l_request_id IS NULL) THEN
2438 l_request_id := fnd_global.conc_request_id;
2439 END IF;
2440
2441 l_label := 'igs.plsql.igs_ad_imp_002.create_address.exception'||'E006';
2442
2443 l_debug_str := 'IGS_AD_IMP_002.Create_Address '
2444 || 'Error from IGS_PE_PERSON_ADDR_PKG.INSERT_ROW HzMesg : ' || l_msg_data
2445 || ' Interface Addr Id : ' || p_addr_rec.interface_addr_id
2446 || ' Status : 3' || ' ErrorCode : E006';
2447
2448 fnd_log.string_with_context( fnd_log.level_exception,
2449 l_label,
2450 l_debug_str, NULL,
2451 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2452 END IF;
2453
2454 IF l_enable_log = 'Y' THEN
2455 igs_ad_imp_001.logerrormessage(p_addr_rec.interface_addr_id,'E006','IGS_AD_ADDR_INT_ALL');
2456 END IF;
2457 ELSE
2458 IF l_return_status = 'W' THEN
2459 UPDATE IGS_AD_ADDR_INT_ALL
2460 SET STATUS = '4',
2461 ERROR_CODE = 'E073'
2462 WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
2463
2464 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2465
2466 IF (l_request_id IS NULL) THEN
2467 l_request_id := fnd_global.conc_request_id;
2468 END IF;
2469
2470 l_label := 'igs.plsql.igs_ad_imp_002.create_address.warning'||'E073';
2471
2472 l_debug_str := 'IGS_AD_IMP_002.Create_Address '
2473 || 'Warning from IGS_PE_PERSON_ADDR_PKG.INSERT_ROW HzMesg : ' || l_msg_data
2474 || ' Interface Addr Id : ' || p_addr_rec.interface_addr_id
2475 || ' Status : 4' || ' ErrorCode : E073';
2476
2477 fnd_log.string_with_context( fnd_log.level_exception,
2478 l_label,
2479 l_debug_str, NULL,
2480 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2481 END IF;
2482 IF l_enable_log = 'Y' THEN
2483 igs_ad_imp_001.logerrormessage(p_addr_rec.interface_addr_id,'E073','IGS_AD_ADDR_INT_ALL');
2484 END IF;
2485 ELSE
2486 UPDATE IGS_AD_ADDR_INT_ALL
2487 SET STATUS = '1',
2488 ERROR_CODE = NULL --ssomani, added this 3/15/01
2489 WHERE INTERFACE_ADDR_ID = P_ADDR_REC.INTERFACE_ADDR_ID;
2490
2491 p_status := '1';
2492 p_error_code := NULL;
2493 END IF;
2494 g_addr_process := TRUE;
2495 process_addrusage(p_addr_rec.interface_addr_id);
2496
2497 END IF;
2498 ELSE
2499 UPDATE IGS_AD_ADDR_INT_ALL
2500 SET STATUS = '3',
2501 ERROR_CODE = p_error_code
2502 WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
2503
2504 UPDATE igs_ad_interface_all
2505 SET status = '4',
2506 error_code = 'E006'
2507 WHERE interface_id = p_addr_rec.interface_id;
2508
2509 IF l_enable_log = 'Y' THEN
2510 igs_ad_imp_001.logerrormessage(P_addr_REC.INTERFACE_addr_ID,'E006','IGS_AD_ADDR_INT_ALL');
2511 END IF;
2512 p_status := '3';
2513 END IF;
2514 EXCEPTION
2515 WHEN OTHERS THEN
2516 l_msg_data := SQLERRM;
2517 p_status := '3';
2518 p_error_code := 'E006';
2519
2520 UPDATE IGS_AD_ADDR_INT_ALL
2521 SET STATUS = '3',
2522 ERROR_CODE = 'E006'
2523 WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
2524
2525 UPDATE igs_ad_interface_all
2526 SET status = '4',
2527 error_code = 'E006'
2528 WHERE interface_id = p_addr_rec.interface_id;
2529
2530 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2531
2532 IF (l_request_id IS NULL) THEN
2533 l_request_id := fnd_global.conc_request_id;
2534 END IF;
2535
2536 l_label := 'igs.plsql.igs_ad_imp_002.create_address.exception'||'E006';
2537
2538 l_debug_str := 'IGS_AD_IMP_002.Create_Address '
2539 || 'Error from IGS_PE_PERSON_ADDR_PKG .INSERT_ROW :' || l_msg_data
2540 || ' Interface addr Id : ' || P_addr_REC.INTERFACE_addr_ID
2541 || ' Status : 3' || ' ErrorCode : E006';
2542
2543 fnd_log.string_with_context( fnd_log.level_exception,
2544 l_label,
2545 l_debug_str, NULL,
2546 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2547 END IF;
2548
2549 IF l_enable_log = 'Y' THEN
2550 igs_ad_imp_001.logerrormessage(P_addr_REC.INTERFACE_addr_ID,'E006','IGS_AD_ADDR_INT_ALL');
2551 END IF;
2552
2553 END CREATE_ADDRESS;
2554
2555
2556 PROCEDURE UPDATE_ADDRESS(
2557 p_addr_rec IN IGS_AD_ADDR_INT_ALL%ROWTYPE,
2558 p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
2559 p_location_id IN hz_party_sites.location_id%TYPE,
2560 p_party_site_id IN hz_party_sites.party_site_id%TYPE) AS
2561
2562 l_rowid VARCHAR2(25);
2563 l_return_status VARCHAR2(100);
2564 l_msg_data VARCHAR2(4000);
2565 l_location_id hz_party_sites.location_id%TYPE;
2566 l_party_site_id hz_party_sites.party_site_id%TYPE;
2567 l_party_site_use_id NUMBER;
2568 l_date hz_party_sites.last_update_date%TYPE;
2569 l_prog_label VARCHAR2(4000);
2570 l_label VARCHAR2(4000);
2571 l_debug_str VARCHAR2(4000);
2572 l_enable_log VARCHAR2(1);
2573 l_request_id NUMBER(10);
2574 l_party_site_ovn hz_party_sites.object_version_number%TYPE;
2575 l_location_ovn hz_locations.object_version_number%TYPE;
2576
2577
2578 CURSOR null_hand_addr_cur(cp_location_id IGS_PE_PERSON_ADDR_V.location_id%TYPE,
2579 cp_party_site_id IGS_PE_PERSON_ADDR_V.party_site_id%TYPE)IS
2580 SELECT *
2581 FROM IGS_PE_PERSON_ADDR_V
2582 WHERE location_id = cp_location_id AND
2583 party_site_id = cp_party_site_id;
2584
2585 null_hand_addr_rec null_hand_addr_cur%ROWTYPE;
2586 l_last_update_date DATE;
2587 p_status VARCHAR2(1);
2588 p_error_code VARCHAR2(100);
2589
2590 -- local procedure to check whether duplicate record exists for hz_party_site_uses for a party site id
2591 PROCEDURE check_dup_addr_usage(l_party_site_id IN HZ_PARTY_SITE_USES.PARTY_SITE_ID%TYPE,
2592 l_site_use_code IN HZ_PARTY_SITE_USES.SITE_USE_TYPE%TYPE,
2593 l_dup_var OUT NOCOPY VARCHAR2 ) AS
2594
2595 l_var VARCHAR2(5);
2596
2597 CURSOR check_dup_cur (cp_party_site_id hz_party_site_uses.party_site_id%TYPE,
2598 cp_site_use_code hz_party_site_uses.site_use_type%TYPE) IS
2599 SELECT 'X'
2600 FROM HZ_PARTY_SITE_USES
2601 WHERE party_site_id = cp_party_site_id
2602 AND site_use_type = cp_site_use_code;
2603
2604 BEGIN
2605 OPEN check_dup_cur(l_party_site_id,l_site_use_code);
2606 FETCH check_dup_cur INTO l_var;
2607 IF check_dup_cur%FOUND THEN
2608 l_dup_var := 'TRUE';
2609 ELSE
2610 l_dup_var := 'FALSE';
2611 END IF;
2612 CLOSE check_dup_cur;
2613
2614 END check_dup_addr_usage;
2615
2616 --- local procedure to import address usage
2617 PROCEDURE process_addrusage(l_interface_addr_id IN igs_ad_addr_int.interface_addr_id%TYPE) AS
2618
2619 CURSOR c_usage ( cp_interface_addr_id NUMBER) IS
2620 SELECT *
2621 FROM IGS_AD_ADDRUSAGE_INT_ALL
2622 WHERE interface_addr_id = cp_interface_addr_id
2623 AND status = '2';
2624
2625 l_exists VARCHAR2(1);
2626 l_error_code VARCHAR2(30);
2627 l_dup_var VARCHAR2(10);
2628 l_profile_last_update_date DATE;
2629 l_site_use_last_update_date DATE;
2630 l_interface_addrusage_id igs_ad_addrusage_int.interface_addrusage_id%TYPE;
2631 l_failure_child NUMBER(3);
2632 l_object_version_number NUMBER;
2633 BEGIN
2634 l_failure_child := 0;
2635 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2636
2637 IF (l_request_id IS NULL) THEN
2638 l_request_id := fnd_global.conc_request_id;
2639 END IF;
2640
2641 l_label := 'igs.plsql.igs_ad_imp_006.update_address.process_addrusage_begin';
2642 l_debug_str := 'Interface addr Id : ' || l_interface_addr_id;
2643
2644 fnd_log.string_with_context( fnd_log.level_procedure,
2645 l_label,
2646 l_debug_str, NULL,
2647 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2648 END IF;
2649
2650
2651 FOR c_usage_rec IN c_usage(l_interface_addr_id)
2652 LOOP
2653 BEGIN
2654 c_usage_rec.site_use_code := UPPER(c_usage_rec.site_use_code);
2655 l_interface_addrusage_id := c_usage_rec.interface_addrusage_id;
2656 IF NOT
2657 (igs_pe_pers_imp_001.validate_lookup_type_code('PARTY_SITE_USE_CODE',c_usage_rec.site_use_code,222))
2658 THEN
2659 l_error_code := 'E211';
2660 RAISE NO_DATA_FOUND;
2661 END IF;
2662 l_dup_var := NULL;
2663 check_dup_addr_usage(l_party_site_id , c_usage_rec.site_use_code,l_dup_var);
2664
2665 IF l_dup_var = 'TRUE' THEN
2666 -- Update is not allowed in party site usage except the STATUS
2667 -- But there is no status column in the interface table hence removing
2668 -- the unnecessary update.
2669 UPDATE IGS_AD_ADDRUSAGE_INT_ALL
2670 SET STATUS = '1',
2671 ERROR_CODE = NULL
2672 WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
2673
2674
2675 ELSIF l_dup_var = 'FALSE' THEN
2676
2677 l_party_site_use_id := NULL;
2678 l_rowid := NULL;
2679
2680 IGS_PE_PARTY_SITE_USE_PKG.HZ_PARTY_SITE_USES_AK(
2681 p_action => 'INSERT',
2682 p_rowid => l_rowid,
2683 p_party_site_use_id => l_party_site_use_id,
2684 p_party_site_id => l_party_site_id,
2685 p_site_use_type => c_usage_rec.site_use_code,
2686 p_status => 'A',
2687 p_return_status => l_return_status ,
2688 p_msg_data => l_msg_data,
2689 p_last_update_date => l_last_update_date,
2690 p_site_use_last_update_date => l_site_use_last_update_date,
2691 p_profile_last_update_date => l_profile_last_update_date,
2692 p_hz_party_site_use_ovn => l_object_version_number
2693 );
2694
2695 IF (l_return_status IN ('E','U') ) THEN
2696
2697 l_error_code := 'E244';
2698 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2699
2700 IF (l_request_id IS NULL) THEN
2701 l_request_id := fnd_global.conc_request_id;
2702 END IF;
2703
2704 l_label := 'igs.plsql.igs_ad_imp_002.process_addrusage.exception'||'E244';
2705
2706 l_debug_str := 'Interface Address Usage ID: '||c_usage_rec.interface_addrusage_id||'HZMess: '||l_msg_data||' SQLERRM: '|| SQLERRM;
2707
2708 fnd_log.string_with_context( fnd_log.level_exception,
2709 l_label,
2710 l_debug_str, NULL,
2711 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2712 END IF;
2713 RAISE NO_DATA_FOUND;
2714 ELSE
2715 UPDATE IGS_AD_ADDRUSAGE_INT_ALL
2716 SET STATUS = '1',
2717 ERROR_CODE = NULL
2718 WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
2719 END IF;
2720
2721 END IF;
2722 EXCEPTION
2723 WHEN NO_DATA_FOUND THEN
2724
2725 l_failure_child := 1;
2726
2727 UPDATE IGS_AD_ADDRUSAGE_INT_ALL
2728 SET STATUS = '3',
2729 ERROR_CODE = l_error_code
2730 WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
2731
2732 IF l_enable_log = 'Y' THEN
2733 igs_ad_imp_001.logerrormessage(l_interface_addrusage_id,l_error_code,'IGS_AD_ADDRUSAGE_INT_ALL');
2734 END IF;
2735
2736 END;
2737
2738 END LOOP;
2739
2740 -- Update the parent if any of the child fails.
2741 IF l_failure_child = 1 THEN
2742
2743 UPDATE igs_ad_addr_int_all
2744 SET status = '4',
2745 error_code = 'E244'
2746 WHERE interface_addr_id = l_interface_addr_id;
2747
2748 END IF;
2749
2750 EXCEPTION
2751 WHEN OTHERS THEN
2752 UPDATE IGS_AD_ADDRUSAGE_INT_ALL
2753 SET STATUS = '3',
2754 ERROR_CODE = 'E244'
2755 WHERE interface_addrusage_id = l_interface_addrusage_id;
2756
2757 UPDATE igs_ad_addr_int_all
2758 SET status = '4',
2759 error_code = 'E244'
2760 WHERE interface_addr_id = l_interface_addr_id;
2761
2762 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2763
2764 IF (l_request_id IS NULL) THEN
2765 l_request_id := fnd_global.conc_request_id;
2766 END IF;
2767
2768 l_label := 'igs.plsql.igs_ad_imp_002.process_addrusage.exception'||'E244';
2769
2770 l_debug_str := 'IGS_AD_IMP_002.Create_Address.process_addrusage ' ||
2771 'Error from process_addrusage ' ||
2772 ' for Interface addrusage Id : ' || (l_interface_addrusage_id) ||' '|| SQLERRM;
2773
2774 fnd_log.string_with_context( fnd_log.level_exception,
2775 l_label,
2776 l_debug_str, NULL,
2777 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2778 END IF;
2779
2780 IF l_enable_log = 'Y' THEN
2781 igs_ad_imp_001.logerrormessage(l_interface_addrusage_id,'E244','IGS_AD_ADDRUSAGE_INT_ALL');
2782 END IF;
2783
2784 END process_addrusage;
2785
2786 BEGIN
2787
2788 l_enable_log := igs_ad_imp_001.g_enable_log;
2789 l_prog_label := 'igs.plsql.igs_ad_imp_002.update_address';
2790 l_label := 'igs.plsql.igs_ad_imp_002.update_address.';
2791 l_location_id := p_location_id;
2792 l_party_site_id := p_party_site_id;
2793 p_status := '2';
2794
2795 -- Call Log header
2796 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2797
2798 IF (l_request_id IS NULL) THEN
2799 l_request_id := fnd_global.conc_request_id;
2800 END IF;
2801
2802 l_label := 'igs.plsql.igs_ad_imp_002.Update_Address.begin';
2803 l_debug_str := 'start of update_address';
2804
2805 fnd_log.string_with_context( fnd_log.level_procedure,
2806 l_label,
2807 l_debug_str, NULL,
2808 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2809 END IF;
2810
2811 validate_address( p_addr_rec => p_addr_rec,
2812 p_person_id => p_person_id,
2813 p_status => p_status,
2814 p_error_code => p_error_code);
2815
2816 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2817
2818 IF (l_request_id IS NULL) THEN
2819 l_request_id := fnd_global.conc_request_id;
2820 END IF;
2821
2822 l_label := 'igs.plsql.igs_ad_imp_002.validate_address.exception'||p_error_code;
2823
2824 l_debug_str := 'p_status :'||p_status||'p_error_code :'||p_error_code;
2825
2826 fnd_log.string_with_context( fnd_log.level_exception,
2827 l_label,
2828 l_debug_str, NULL,
2829 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2830 END IF;
2831
2832
2833 IF p_status = '3' THEN
2834 RAISE NO_DATA_FOUND;
2835 END IF;
2836
2837 IF p_status = '2' THEN
2838
2839
2840 OPEN null_hand_addr_cur(p_location_id,p_party_site_id);
2841 FETCH null_hand_addr_cur INTO null_hand_addr_rec;
2842 IF null_hand_addr_cur%NOTFOUND THEN
2843 CLOSE null_hand_addr_cur;
2844 RAISE NO_DATA_FOUND;
2845 END IF;
2846 CLOSE null_hand_addr_cur;
2847
2848 l_location_ovn := null_hand_addr_rec.location_ovn;
2849 l_party_site_ovn := null_hand_addr_rec.party_site_ovn;
2850 l_location_id := p_location_id;
2851 l_party_site_id := p_party_site_id;
2852
2853 IGS_PE_PERSON_ADDR_PKG.Update_Row(
2854 P_ACTION => 'UPDATE',
2855 P_ROWID => l_RowId,
2856 P_LOCATION_ID => l_location_Id,
2857 P_START_DT => nvl(p_addr_rec.Start_date,null_hand_addr_rec.start_dt),
2858 P_END_DT => nvl(p_addr_rec.End_Date,null_hand_addr_rec.end_dt),
2859 P_COUNTRY => p_addr_rec.country,
2860 P_ADDRESS_STYLE => NULL,
2861 P_ADDR_LINE_1 => nvl(p_addr_rec.addr_line_1,null_hand_addr_rec.addr_line_1),
2862 P_ADDR_LINE_2 => nvl(p_addr_rec.addr_line_2,null_hand_addr_rec.addr_line_2),
2863 P_ADDR_LINE_3 => nvl(p_addr_rec.addr_line_3,null_hand_addr_rec.addr_line_3),
2864 P_ADDR_LINE_4 => nvl(p_addr_rec.addr_line_4,null_hand_addr_rec.addr_line_4),
2865 P_DATE_LAST_VERIFIED => nvl(p_addr_rec.Date_Last_Verified,null_hand_addr_rec.date_last_verified),
2866 P_CORRESPONDENCE => nvl(p_addr_rec.CORRESPONDENCE_FLAG,null_hand_addr_rec. CORRESPONDENCE_IND),
2867 P_CITY => nvl(p_addr_rec.city,null_hand_addr_rec.city),
2868 P_STATE => nvl(p_addr_rec.state,null_hand_addr_rec.state),
2869 P_PROVINCE => nvl(p_addr_rec.province,null_hand_addr_rec.province),
2870 P_COUNTY => nvl(p_addr_rec.county,null_hand_addr_rec.county),
2871 P_POSTAL_CODE => nvl(p_addr_rec.postcode,null_hand_addr_rec.postal_code),
2872 P_ADDRESS_LINES_PHONETIC => NULL,
2873 P_DELIVERY_POINT_CODE => nvl(p_addr_rec.delivery_point_code,null_hand_addr_rec.delivery_point_code),
2874 P_OTHER_DETAILS_1 => nvl(p_addr_rec.other_details_1,null_hand_addr_rec.other_details_1),
2875 P_OTHER_DETAILS_2 => nvl(p_addr_rec.other_details_2,null_hand_addr_rec.other_details_2),
2876 P_OTHER_DETAILS_3 => nvl(p_addr_rec.other_details_3,null_hand_addr_rec.other_details_3),
2877 L_RETURN_STATUS => l_Return_Status,
2878 L_MSG_DATA => l_Msg_Data,
2879 P_PARTY_ID => P_PERSON_ID,
2880 P_PARTY_SITE_ID => l_party_site_id,
2881 P_PARTY_TYPE => 'PERSON',
2882 P_LAST_UPDATE_DATE => l_date,
2883 p_party_site_ovn => l_location_ovn,
2884 p_location_ovn => l_party_site_ovn,
2885 p_status => null_hand_addr_rec.status
2886 );
2887 IF (l_return_status IN ('E','U') ) THEN
2888
2889 UPDATE igs_ad_addr_int_all
2890 SET status = '3', error_code = 'E014'
2891 WHERE interface_addr_id = p_addr_rec.interface_addr_id;
2892
2893 UPDATE igs_ad_interface_all
2894 SET status = '4', error_code = 'E014'
2895 WHERE interface_id = p_addr_rec.interface_id;
2896
2897 p_error_code := 'E014';
2898 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2899
2900 IF (l_request_id IS NULL) THEN
2901 l_request_id := fnd_global.conc_request_id;
2902 END IF;
2903
2904 l_label := 'igs.plsql.igs_ad_imp_002.update_address.exception'||p_error_code;
2905
2906 l_debug_str := 'IGS_AD_IMP_002.Update_Address ' || 'Error from IGS_PE_PERSON_ADDRESS_PKG : HzMesg'
2907 || l_msg_data || ' Interface Addr Id : '
2908 || (P_addr_REC.INTERFACE_ADDR_ID) ||' Status : 3' || ' ErrorCode : E014 ';
2909
2910 fnd_log.string_with_context( fnd_log.level_exception,
2911 l_label,
2912 l_debug_str, NULL,
2913 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2914 END IF;
2915
2916 IF l_enable_log = 'Y' THEN
2917 igs_ad_imp_001.logerrormessage(P_addr_REC.INTERFACE_ADDR_ID,p_error_code,'IGS_AD_ADDR_INT_ALL');
2918 END IF;
2919 ELSE
2920 IF l_return_status = 'W' THEN
2921 UPDATE igs_ad_addr_int_all
2922 SET status = '4', error_code = 'E073'
2923 WHERE interface_addr_id = p_addr_rec.interface_addr_id;
2924
2925 p_error_code := 'E073';
2926 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2927 IF (l_request_id IS NULL) THEN
2928 l_request_id := fnd_global.conc_request_id;
2929 END IF;
2930
2931 l_label := 'igs.plsql.igs_ad_imp_002.update_address.warning'||p_error_code;
2932
2933 l_debug_str := 'IGS_AD_IMP_002.Update_Address ' || 'Warning from IGS_PE_PERSON_ADDRESS_PKG : HzMesg'
2934 || l_msg_data || ' Interface Addr Id : '
2935 || (P_addr_REC.INTERFACE_ADDR_ID) ||' Status : 4' || ' ErrorCode : E073';
2936
2937 fnd_log.string_with_context( fnd_log.level_exception,
2938 l_label,
2939 l_debug_str, NULL,
2940 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2941 END IF;
2942
2943 IF l_enable_log = 'Y' THEN
2944 igs_ad_imp_001.logerrormessage(P_addr_REC.INTERFACE_ADDR_ID,p_error_code,'IGS_AD_ADDR_INT_ALL');
2945 END IF;
2946 ELSE
2947 UPDATE igs_ad_addr_int_all
2948 SET status = '1',
2949 ERROR_CODE = NULL
2950 WHERE interface_addr_id = p_addr_rec.interface_addr_id;
2951 END IF;
2952 g_addr_process := TRUE;
2953 process_addrusage(p_addr_rec.interface_addr_id);
2954
2955 END IF;
2956
2957 END IF;
2958 EXCEPTION
2959 WHEN OTHERS THEN
2960
2961 l_msg_data := SQLERRM;
2962 UPDATE igs_ad_addr_int_all
2963 SET status = '3', error_code = p_error_code
2964 WHERE interface_addr_id = p_addr_rec.interface_addr_id;
2965
2966 UPDATE igs_ad_interface_all
2967 SET status = '4', error_code = 'E014'
2968 WHERE interface_id = p_addr_rec.interface_id;
2969
2970 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2971
2972 IF (l_request_id IS NULL) THEN
2973 l_request_id := fnd_global.conc_request_id;
2974 END IF;
2975
2976 l_label := 'igs.plsql.igs_ad_imp_002.update_address.exception'||'E014';
2977
2978 l_debug_str := 'IGS_AD_IMP_002.Update_Address ' || 'Exception from IGS_PE_PERSON_ADDRESS_PKG : HzMesg '
2979 || l_msg_data || ' Interface Addr Id : '
2980 || (P_addr_REC.INTERFACE_ADDR_ID) || ' Status : 3' ||
2981 ' ErrorCode : '||p_error_code;
2982
2983 fnd_log.string_with_context( fnd_log.level_exception,
2984 l_label,
2985 l_debug_str, NULL,
2986 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2987 END IF;
2988
2989 IF l_enable_log = 'Y' THEN
2990 igs_ad_imp_001.logerrormessage(P_addr_REC.INTERFACE_ADDR_ID,'E014','IGS_AD_ADDR_INT_ALL');
2991 END IF;
2992
2993 END UPDATE_ADDRESS;
2994
2995
2996 PROCEDURE prc_pe_dtls(p_d_batch_id IN NUMBER,
2997 p_d_source_type_id IN NUMBER,
2998 p_match_set_id IN NUMBER)
2999 AS
3000 /*
3001 || Created By : nsinha
3002 || Created On : 22-jun-2001
3003 || Purpose : This procedure process the person details.
3004 || Known limitations, enhancements or remarks :
3005 || Change History :
3006 || Who When What
3007 || pkpatel 22-Jun-2001 For Modeling and Forecasting DLD modified the code
3008 || to handle the details level descripancy rule.
3009 || Modified all SELECT Query into Cursors.
3010 || pkpatel 25-DEC-2002 Bug No: 2702536
3011 || Added the new duplicate checking process. This will happen for each record, instead of at batch level
3012 || asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
3013 || pkpatel 23-Feb-2006 Bug 4869740 (Modified the datatype in cursor c_get_global_var for cp_match_set_id)
3014 || (reverse chronological order - newest change first)
3015 */
3016 l_lvcAction VARCHAR2(1);
3017 l_lvcRecordExist VARCHAR2(1);
3018 l_prog_label VARCHAR2(4000);
3019 l_label VARCHAR2(4000);
3020 l_debug_str VARCHAR2(4000);
3021 l_enable_log VARCHAR2(1);
3022 l_request_id NUMBER(10);
3023 l_int_pk_where_clause VARCHAR2(2000);
3024 l_ad_pk_where_clause VARCHAR2(2000);
3025 l_discrepancy_exists BOOLEAN;
3026 l_attribute_action VARCHAR2(1);
3027 l_default_date DATE;
3028 l_person_id igs_ad_interface.person_id%TYPE;
3029
3030 -- cursor to populate global variable g_partial_if_null and g_primary_addr_flag
3031 CURSOR c_get_global_var(cp_match_set_id igs_pe_match_sets_all.match_set_id%TYPE) IS
3032 SELECT partial_if_null,primary_addr_flag, exclude_inactive_ind
3033 FROM igs_pe_match_sets_all
3034 WHERE match_set_id = cp_match_set_id;
3035
3036 CURSOR c_matchset_data_cur(cp_match_set_id igs_pe_mtch_set_data_all.match_set_id%TYPE) IS
3037 SELECT data_element, drop_if_null, partial_include, exact_include
3038 FROM igs_pe_mtch_set_data_all
3039 WHERE match_set_id = cp_match_set_id;
3040
3041 --Bug no.1834307 MOdified the source table from igs_ad_interface to igs_ad_interface_dtl_dscp_v
3042 CURSOR person_cur(cp_d_batch_id igs_ad_interface_dtl_dscp_v.batch_id%TYPE,
3043 cp_d_source_type_id igs_ad_interface_dtl_dscp_v.source_type_id%TYPE) IS
3044 SELECT ai.*
3045 FROM igs_ad_interface_dtl_dscp_v ai
3046 WHERE status = '2'
3047 AND batch_id = cp_d_batch_id
3048 AND source_type_id = cp_d_source_type_id;
3049
3050 -- Cursor for Null handling Rule
3051 CURSOR c_null_hdlg_per_cur(cp_person_id igs_pe_person.person_id%TYPE) IS
3052 SELECT
3053 p.person_last_name surname,
3054 p.person_first_name given_names,
3055 p.person_middle_name middle_name,
3056 p.person_name_suffix suffix,
3057 p.person_pre_name_adjunct pre_name_adjunct,
3058 p.person_title title,
3059 p.known_as preferred_given_name,
3060 pd.level_of_qual level_of_qual_id,
3061 pp.gender sex,
3062 pp.date_of_birth birth_dt
3063 FROM
3064 hz_parties p,
3065 igs_pe_hz_parties pd,
3066 hz_person_profiles pp
3067 WHERE p.party_id = cp_person_id
3068 AND p.party_id = pd.party_id (+)
3069 AND p.party_id = pp.party_id
3070 AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
3071
3072 CURSOR Status_cur(cp_interface_id IGS_AD_INTERFACE_ALL.INTERFACE_ID%TYPE) IS
3073 SELECT status
3074 FROM IGS_AD_INTERFACE_all
3075 WHERE interface_id = cp_interface_id;
3076
3077 -- Cursor to check whether the set up is done for Address type/person id type in th ematch set.
3078 CURSOR addr_personid_type_cur(cp_match_set_id igs_pe_mtch_set_data.match_set_id%TYPE,
3079 cp_type igs_pe_mtch_set_data.data_element%TYPE) IS
3080 SELECT value
3081 FROM igs_pe_mtch_set_data md
3082 WHERE match_set_id = cp_match_set_id
3083 AND md.data_element =cp_type;
3084
3085
3086 --Cursor for record level Review
3087 CURSOR discrepancy_exist_cur(cp_person_id igs_pe_person.person_id%TYPE,
3088 c_person_rec person_cur%ROWTYPE) IS
3089 SELECT 'X'
3090 FROM hz_parties p,
3091 igs_pe_hz_parties pd,
3092 hz_person_profiles pp,
3093 igs_pe_person_id_type_v pit
3094 WHERE p.party_id = cp_person_id
3095 AND p.party_id = pit.pe_person_id (+)
3096 AND p.party_id = pd.party_id (+)
3097 AND p.party_id = pp.party_id
3098 AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE)
3099 AND NVL(p.person_last_name, '*') = NVL(c_person_rec.surname, '*')
3100 AND NVL(p.person_first_name, '*') = NVL(c_person_rec.given_names, '*')
3101 AND NVL(p.person_name_suffix, '*') = NVL(c_person_rec.suffix, '*')
3102 AND NVL(pp.gender, '*') = NVL(c_person_rec.sex, '*')
3103 AND NVL(p.person_title, '*') = NVL(c_person_rec.title, '*')
3104 AND NVL(pp.date_of_birth, l_default_date) = NVL(c_person_rec.birth_dt, l_default_date)
3105 AND NVL(pd.proof_of_ins, '*') = NVL(c_person_rec.proof_of_ins, '*')
3106 AND NVL(pd.proof_of_immu, '*') = NVL(c_person_rec.proof_of_immu, '*')
3107 AND NVL(pd.level_of_qual, -99) = NVL(c_person_rec.level_of_qual_id, -99)
3108 AND NVL(pd.military_service_reg, '*') = NVL(c_person_rec.military_service_reg, '*')
3109 AND NVL(pd.veteran, '*') = NVL(c_person_rec.veteran, '*')
3110 AND NVL(p.known_as, '*') = NVL(c_person_rec.preferred_given_name, '*')
3111 AND NVL(p.attribute_category, '*') = NVL(c_person_rec.attribute_category, '*')
3112 AND NVL(p.person_middle_name,'*') = NVL(c_person_rec.middle_name,'*')
3113 AND NVL(p.person_pre_name_adjunct,'*') = NVL(c_person_rec.pre_name_adjunct,'*')
3114 AND NVL(p.attribute1, '*') = NVL(c_person_rec.attribute1, '*')
3115 AND NVL(p.attribute2, '*') = NVL(c_person_rec.attribute2, '*')
3116 AND NVL(p.attribute3, '*') = NVL(c_person_rec.attribute3, '*')
3117 AND NVL(p.attribute4, '*') = NVL(c_person_rec.attribute4, '*')
3118 AND NVL(p.attribute5, '*') = NVL(c_person_rec.attribute5, '*')
3119 AND NVL(p.attribute6, '*') = NVL(c_person_rec.attribute6, '*')
3120 AND NVL(p.attribute7, '*') = NVL(c_person_rec.attribute7, '*')
3121 AND NVL(p.attribute8, '*') = NVL(c_person_rec.attribute8, '*')
3122 AND NVL(p.attribute9, '*') = NVL(c_person_rec.attribute9, '*')
3123 AND NVL(p.attribute10, '*') = NVL(c_person_rec.attribute10, '*')
3124 AND NVL(p.attribute11, '*') = NVL(c_person_rec.attribute11, '*')
3125 AND NVL(p.attribute12, '*') = NVL(c_person_rec.attribute12, '*')
3126 AND NVL(p.attribute13, '*') = NVL(c_person_rec.attribute13, '*')
3127 AND NVL(p.attribute14, '*') = NVL(c_person_rec.attribute14, '*')
3128 AND NVL(p.attribute15, '*') = NVL(c_person_rec.attribute15, '*')
3129 AND NVL(p.attribute16, '*') = NVL(c_person_rec.attribute16, '*')
3130 AND NVL(p.attribute17, '*') = NVL(c_person_rec.attribute17, '*')
3131 AND NVL(p.attribute18, '*') = NVL(c_person_rec.attribute18, '*')
3132 AND NVL(p.attribute19, '*') = NVL(c_person_rec.attribute19, '*')
3133 AND NVL(p.attribute20, '*') = NVL(c_person_rec.attribute20, '*')
3134 AND NVL(p.attribute21, '*') = NVL(c_person_rec.attribute21, '*')
3135 AND NVL(p.attribute22, '*') = NVL(c_person_rec.attribute22, '*')
3136 AND NVL(p.attribute23, '*') = NVL(c_person_rec.attribute23, '*')
3137 AND NVL(p.attribute24, '*') = NVL(c_person_rec.attribute24, '*')
3138 AND NVL(pd.felony_convicted_flag, '*') = NVL(c_person_rec.felony_convicted_flag, '*')
3139 AND NVL(pd.birth_city, '*') = NVL(c_person_rec.birth_city, '*')
3140 AND NVL(pd.birth_country, '*') = NVL(c_person_rec.birth_country, '*')
3141 AND NVL(pit.api_person_id, '*') = NVL(c_person_rec.pref_alternate_id, '*');
3142
3143 c_null_hdlg_per_rec c_null_hdlg_per_cur%ROWTYPE;
3144 person_rec person_cur%ROWTYPE;
3145 status_rec status_cur%ROWTYPE;
3146 get_global_var_rec c_get_global_var%ROWTYPE;
3147 matchset_data_rec c_matchset_data_cur%ROWTYPE;
3148 l_count_exact NUMBER;
3149 l_count_partial NUMBER;
3150 l_addrtype igs_pe_mtch_set_data.VALUE%TYPE;
3151 l_personidtype igs_pe_mtch_set_data.VALUE%TYPE;
3152 l_match_ind igs_ad_interface.match_ind%TYPE;
3153
3154 BEGIN
3155
3156 l_enable_log := igs_ad_imp_001.g_enable_log;
3157 l_prog_label := 'igs.plsql.igs_ad_imp_002.prc_pe_dtls';
3158 l_label := 'igs.plsql.igs_ad_imp_002.prc_pe_dtls.';
3159 -- populate global variables
3160 Igs_Pe_Identify_Dups.g_match_set_id := p_match_set_id;
3161 Igs_Pe_Identify_Dups.g_source_type_id := p_d_source_type_id;
3162 l_default_date := TRUNC(SYSDATE);
3163 l_addrtype := NULL;
3164 l_personidtype := NULL;
3165
3166 OPEN c_get_global_var(p_match_set_id);
3167 FETCH c_get_global_var INTO get_global_var_rec;
3168 Igs_Pe_Identify_Dups.g_partial_if_null := get_global_var_rec.partial_if_null;
3169 Igs_Pe_Identify_Dups.g_primary_addr_flag := get_global_var_rec.primary_addr_flag;
3170 Igs_Pe_Identify_Dups.g_exclude_inactive_ind := get_global_var_rec.exclude_inactive_ind;
3171 CLOSE c_get_global_var;
3172
3173 l_count_exact := 1;
3174 l_count_partial := 1;
3175
3176 FOR matchset_data_rec IN c_matchset_data_cur(p_match_set_id) LOOP
3177 IF matchset_data_rec.data_element NOT IN ('SURNAME','GIVEN_NAME_1_CHAR') THEN
3178 IF matchset_data_rec.exact_include = 'Y' THEN
3179 Igs_Pe_Identify_Dups.g_matchset_exact(l_count_exact).data_element := matchset_data_rec.data_element;
3180 Igs_Pe_Identify_Dups.g_matchset_exact(l_count_exact).drop_if_null := matchset_data_rec.drop_if_null;
3181 l_count_exact := l_count_exact + 1;
3182 END IF;
3183 IF matchset_data_rec.partial_include = 'Y' THEN
3184 Igs_Pe_Identify_Dups.g_matchset_partial(l_count_partial).data_element := matchset_data_rec.data_element;
3185 Igs_Pe_Identify_Dups.g_matchset_partial(l_count_partial).drop_if_null := matchset_data_rec.drop_if_null;
3186 l_count_partial := l_count_partial + 1;
3187 END IF;
3188
3189 IF matchset_data_rec.data_element = 'ADDR_TYPE' THEN
3190 Igs_Pe_Identify_Dups.g_addr_type_din := matchset_data_rec.drop_if_null;
3191 END IF;
3192 IF matchset_data_rec.data_element = 'PERSON_ID_TYPE' THEN
3193 Igs_Pe_Identify_Dups.g_person_id_type_din := matchset_data_rec.drop_if_null;
3194 END IF;
3195 END IF;
3196 END LOOP;
3197
3198 OPEN addr_personid_type_cur(p_match_set_id,'ADDR_TYPE');
3199 FETCH addr_personid_type_cur INTO l_addrtype;
3200 CLOSE addr_personid_type_cur;
3201
3202 OPEN addr_personid_type_cur(p_match_set_id,'PERSON_ID_TYPE');
3203 FETCH addr_personid_type_cur INTO l_personidtype;
3204 CLOSE addr_personid_type_cur;
3205
3206 l_lvcAction := Igs_Ad_Imp_001.FIND_SOURCE_CAT_RULE(p_d_source_type_id,'PERSON');
3207 IF l_lvcAction = 'D' THEN
3208 -- Get the attribute level discrepancy rule.
3209 l_attribute_action := Igs_Ad_Imp_023.find_attribute_rule(
3210 p_source_type_id => p_d_source_type_id,
3211 p_category => 'PERSON');
3212 END IF;
3213
3214
3215 -- Open the cursor and iterate on the cursor
3216 OPEN person_cur(p_d_batch_id,p_d_source_type_id);
3217 LOOP
3218 FETCH person_cur INTO person_rec;
3219 EXIT WHEN person_cur%NOTFOUND;
3220
3221 l_match_ind := person_rec.match_ind;
3222 -- Call the procedure Identify Imports Duplicate Record.
3223 Igs_Ad_Imp_009.IGS_AD_IMP_FIND_DUP_PERSONS(
3224 p_d_batch_id,
3225 p_d_source_type_id ,
3226 p_match_set_id,
3227 person_rec.interface_id,
3228 l_match_ind,
3229 l_person_id,
3230 l_addrtype,
3231 l_personidtype);
3232
3233
3234 person_rec.pre_name_adjunct := UPPER(person_rec.pre_name_adjunct);
3235 person_rec.Sex := UPPER(person_rec.Sex);
3236 person_rec.veteran := UPPER(person_rec.veteran);
3237 person_rec.PROOF_OF_INS := UPPER(person_rec.PROOF_OF_INS);
3238 person_rec.PROOF_OF_IMMU := UPPER(person_rec.PROOF_OF_IMMU );
3239 person_rec.MILITARY_SERVICE_REG := UPPER(person_rec.MILITARY_SERVICE_REG);
3240 person_rec.PREF_ALTERNATE_ID := UPPER(person_rec.PREF_ALTERNATE_ID);
3241 person_rec.birth_country := UPPER(person_rec.birth_country);
3242
3243 IF l_match_ind IN ('12','15') THEN --12 -Match To Single Person
3244
3245 IF l_match_ind = '15' THEN
3246 l_person_id := person_rec.person_id;
3247 END IF;
3248
3249 IF l_lvcAction = 'E' THEN
3250 UPDATE igs_ad_interface_all
3251 SET person_match_ind = cst_mi_val_19, --19 -Match exists and retained existing values
3252 status = cst_stat_val_1,
3253 ERROR_CODE = NULL --ssomani, added this 3/15/01
3254 WHERE interface_id = person_rec.interface_id;
3255
3256 ELSIF l_lvcAction = 'I' THEN
3257
3258 update_person
3259 (p_person_rec=>person_rec,
3260 p_addr_type=> NULL ,
3261 p_person_id_type=> NULL ,
3262 p_person_id=> l_person_id);
3263
3264 OPEN status_cur(person_rec.interface_id);
3265 FETCH status_cur INTO status_rec;
3266 IF status_cur%FOUND THEN
3267 IF status_rec.status = '1' THEN
3268 UPDATE igs_ad_interface_all
3269 SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
3270 status = cst_stat_val_1,
3271 ERROR_CODE = NULL
3272 WHERE interface_id = person_rec.interface_id;
3273 END IF;
3274 END IF;
3275 CLOSE status_cur;
3276
3277 ELSIF l_lvcAction = 'R' THEN
3278 IF person_rec.person_match_ind = '21' THEN --21 -Match reviewed and to be imported
3279
3280 update_person (p_person_rec=>person_rec,
3281 p_addr_type=> NULL ,
3282 p_person_id_type=> NULL ,
3283 p_person_id=>l_person_id);
3284
3285 OPEN status_cur(person_rec.interface_id);
3286 FETCH status_cur INTO status_rec;
3287 IF status_cur%FOUND THEN
3288 IF status_rec.status = '1' THEN
3289 UPDATE igs_ad_interface_all
3290 SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
3291 status = cst_stat_val_1,
3292 ERROR_CODE = NULL
3293 WHERE interface_id = person_rec.interface_id;
3294 END IF;
3295 END IF;
3296 CLOSE status_cur;
3297
3298 ELSIF NVL(person_rec.person_match_ind,'-1') NOT IN('20','23') THEN --20 - Match To Be Reviewed For Discrepancy
3299 --23 - Match to be reviewed, but there was no discrepancy
3300 -- and so retaining the existing values
3301 --Bug no.1834307 :Added the primary key where clause and modified the Default values in the NVL
3302 BEGIN
3303 OPEN discrepancy_exist_cur(l_person_id,person_rec);
3304 FETCH discrepancy_exist_cur INTO l_lvcrecordexist;
3305 IF discrepancy_exist_cur%NOTFOUND THEN
3306 RAISE NO_DATA_FOUND;
3307 END IF;
3308 CLOSE discrepancy_exist_cur;
3309
3310 UPDATE igs_ad_interface_all
3311 SET person_match_ind = cst_mi_val_23, --ssomani corrected the status updation 3/15/01
3312 status = cst_stat_val_1, --23 - Match to be reviewed, but there was no discrepancy
3313 ERROR_CODE = NULL -- and so retaining the existing values
3314 WHERE interface_id = person_rec.interface_id;
3315 EXCEPTION
3316 WHEN OTHERS THEN
3317 IF discrepancy_exist_cur%ISOPEN THEN
3318 CLOSE discrepancy_exist_cur;
3319 END IF;
3320
3321 UPDATE igs_ad_interface_all
3322 SET person_match_ind = cst_mi_val_20, --20 - Match To Be Reviewed For Discrepancy
3323 status = cst_stat_val_3--ssomani corrected the status updation 3/15/01
3324 WHERE interface_id = person_rec.interface_id;
3325 END;
3326 ELSE
3327 IF person_rec.person_match_ind = '20' THEN
3328 UPDATE igs_ad_interface_all
3329 SET status = cst_stat_val_3 -- Record must have been processed in a previous run
3330 WHERE interface_id = person_rec.interface_id;
3331 ELSIF person_rec.person_match_ind = '23' THEN
3332 UPDATE igs_ad_interface_all
3333 SET status = cst_stat_val_1 -- Record must have been processed in a previous run
3334 WHERE interface_id = person_rec.interface_id;
3335 END IF;
3336 END IF; -- End IF for person_match_ind
3337
3338 ELSIF l_lvcAction = 'D' THEN
3339 /*
3340 || Added By : [email protected]
3341 || Added On : 22-Jun-2001
3342 || Purpose : This part of code is enhanced to handle the attribute level discrepancy rule,
3343 || as part of Modeling and Forecasting DLD.
3344 */
3345
3346 -- Open Cursor for the ADMISSION table for the particular person to import the
3347 -- selected attributes from the INTERFACE table
3348 OPEN c_null_hdlg_per_cur(l_person_id);
3349 FETCH c_null_hdlg_per_cur INTO c_null_hdlg_per_rec;
3350 CLOSE c_null_hdlg_per_cur;
3351
3352 IF l_attribute_action = 'E' THEN
3353 --
3354 -- All the columns are marked as Keep('E') and nothing is marked for Review or Import.
3355 -- Only update the interface table with match_ind = '19', status = '1' and error_code = NULL.
3356 --
3357 UPDATE igs_ad_interface_all
3358 SET person_match_ind = cst_mi_val_19,--19 -Match exists and retained existing values
3359 status = cst_stat_val_1,
3360 error_code = NULL
3361 WHERE interface_id = person_rec.interface_id;
3362
3363
3364 ELSIF l_attribute_action = 'I' THEN
3365 --
3366 -- Few of the columns are marked for Import and nothing is marked for Review.
3367 -- Process the record by evaluating only 'Keep' and 'Import' Discrepancy Rules.
3368 -- Evaluate the Discrepancy Rules for each column and re-prepare the person_rec.
3369 --
3370 person_rec.SURNAME := Igs_Ad_Imp_023.get_discrepancy_result(
3371 p_attribute_name => 'SURNAME',
3372 p_ad_col_value => c_null_hdlg_per_rec.SURNAME,
3373 p_int_col_value => person_rec.SURNAME,
3374 p_source_type_id => p_d_source_type_id,
3375 p_category => 'PERSON'
3376 );
3377
3378 person_rec.MIDDLE_NAME := Igs_Ad_Imp_023.get_discrepancy_result(
3379 p_attribute_name => 'MIDDLE_NAME',
3380 p_ad_col_value => c_null_hdlg_per_rec.MIDDLE_NAME,
3381 p_int_col_value => person_rec.MIDDLE_NAME,
3382 p_source_type_id => p_d_source_type_id,
3383 p_category => 'PERSON'
3384 );
3385
3386 person_rec.GIVEN_NAMES := Igs_Ad_Imp_023.get_discrepancy_result(
3387 p_attribute_name => 'GIVEN_NAMES',
3388 p_ad_col_value => c_null_hdlg_per_rec.GIVEN_NAMES,
3389 p_int_col_value => person_rec.GIVEN_NAMES,
3390 p_source_type_id => p_d_source_type_id,
3391 p_category => 'PERSON'
3392 );
3393
3394 person_rec.PREFERRED_GIVEN_NAME := Igs_Ad_Imp_023.get_discrepancy_result(
3395 p_attribute_name => 'PREFERRED_GIVEN_NAME',
3396 p_ad_col_value => c_null_hdlg_per_rec.PREFERRED_GIVEN_NAME,
3397 p_int_col_value => person_rec.PREFERRED_GIVEN_NAME,
3398 p_source_type_id => p_d_source_type_id,
3399 p_category => 'PERSON'
3400 );
3401
3402 person_rec.SEX := Igs_Ad_Imp_023.get_discrepancy_result(
3403 p_attribute_name => 'SEX',
3404 p_ad_col_value => c_null_hdlg_per_rec.SEX,
3405 p_int_col_value => person_rec.SEX,
3406 p_source_type_id => p_d_source_type_id,
3407 p_category => 'PERSON'
3408 );
3409
3410 person_rec.BIRTH_DT := Igs_Ad_Imp_023.get_discrepancy_result(
3411 p_attribute_name => 'BIRTH_DT',
3412 p_ad_col_value => c_null_hdlg_per_rec.BIRTH_DT,
3413 p_int_col_value => person_rec.BIRTH_DT,
3414 p_source_type_id => p_d_source_type_id,
3415 p_category => 'PERSON'
3416 );
3417
3418 person_rec.TITLE := Igs_Ad_Imp_023.get_discrepancy_result(
3419 p_attribute_name => 'TITLE',
3420 p_ad_col_value => c_null_hdlg_per_rec.TITLE,
3421 p_int_col_value => person_rec.TITLE,
3422 p_source_type_id => p_d_source_type_id,
3423 p_category => 'PERSON'
3424 );
3425
3426 person_rec.SUFFIX := Igs_Ad_Imp_023.get_discrepancy_result(
3427 p_attribute_name => 'SUFFIX',
3428 p_ad_col_value => c_null_hdlg_per_rec.SUFFIX,
3429 p_int_col_value => person_rec.SUFFIX,
3430 p_source_type_id => p_d_source_type_id,
3431 p_category => 'PERSON'
3432 );
3433
3434 person_rec.PRE_NAME_ADJUNCT := Igs_Ad_Imp_023.get_discrepancy_result(
3435 p_attribute_name => 'PRE_NAME_ADJUNCT',
3436 p_ad_col_value => c_null_hdlg_per_rec.PRE_NAME_ADJUNCT,
3437 p_int_col_value => person_rec.PRE_NAME_ADJUNCT,
3438 p_source_type_id => p_d_source_type_id,
3439 p_category => 'PERSON'
3440 );
3441
3442 person_rec.LEVEL_OF_QUAL_ID := Igs_Ad_Imp_023.get_discrepancy_result(
3443 p_attribute_name => 'LEVEL_OF_QUAL_ID',
3444 p_ad_col_value => c_null_hdlg_per_rec.LEVEL_OF_QUAL_ID,
3445 p_int_col_value => person_rec.LEVEL_OF_QUAL_ID,
3446 p_source_type_id => p_d_source_type_id,
3447 p_category => 'PERSON'
3448 );
3449
3450 update_person
3451 (p_person_rec=>person_rec,
3452 p_addr_type=> NULL ,
3453 p_person_id_type=> NULL ,
3454 p_person_id=> l_person_id);
3455
3456
3457 OPEN status_cur(person_rec.interface_id);
3458 FETCH status_cur INTO status_rec;
3459 IF status_cur%FOUND THEN
3460 IF status_rec.status = '1' THEN
3461 UPDATE igs_ad_interface_all
3462 SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
3463 status = cst_stat_val_1,
3464 ERROR_CODE = NULL
3465 WHERE interface_id = person_rec.interface_id;
3466 END IF;
3467 END IF;
3468 CLOSE status_cur;
3469
3470 ELSIF l_attribute_action = 'R' THEN
3471 -- Few of the columns are marked for review
3472
3473 IF person_rec.person_match_ind = '21' THEN --21 -Match reviewed and to be imported
3474
3475 update_person
3476 (p_person_rec=>person_rec,
3477 p_addr_type=> NULL ,
3478 p_person_id_type=> NULL ,
3479 p_person_id=>l_person_id);
3480
3481 OPEN status_cur(person_rec.interface_id);
3482 FETCH status_cur INTO status_rec;
3483 IF status_cur%FOUND THEN
3484 IF status_rec.status = '1' THEN
3485 UPDATE igs_ad_interface_all
3486 SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
3487 status = cst_stat_val_1,
3488 ERROR_CODE = NULL
3489 WHERE interface_id = person_rec.interface_id;
3490 END IF;
3491 END IF;
3492 CLOSE status_cur;
3493
3494 ELSIF NVL(person_rec.person_match_ind,'-1') NOT IN('20','23') THEN --20 - Match To Be Reviewed For Discrepancy
3495 --23 - Match to be reviewed, but there was no discrepancy
3496 -- and so retaining the existing values
3497 -- Then check to see if discrepancy exists at detail level.
3498 --gmaheswa: modified to Fix literals issue.
3499 l_discrepancy_exists := Igs_Ad_Imp_023.find_detail_discrepancy_rule(p_source_type_id => p_d_source_type_id,
3500 p_category => 'PERSON',
3501 p_int_pk_col_name => 'INTERFACE_ID',
3502 p_int_pk_col_val => person_rec.interface_id,
3503 p_ad_pk_col_name => 'PERSON_ID',
3504 p_ad_pk_col_val => l_person_id);
3505
3506 IF l_discrepancy_exists THEN
3507 --Discrepancy exists.
3508 --Update the match_ind = '20', status = '3' and error_code = NULL IN THE INTERFACE TABLE.
3509
3510 UPDATE igs_ad_interface_all
3511 SET person_match_ind = cst_mi_val_20, --20 - Match To Be Reviewed For Discrepancy
3512 status = cst_stat_val_3,
3513 error_code = NULL
3514 WHERE interface_id = person_rec.interface_id;
3515
3516 ELSE
3517 --Discrepancy does not exist.
3518 --Update the match_ind = '23', status = '1' and error_code = NULL IN THE INTERFACE TABLE.
3519
3520 UPDATE igs_ad_interface_all
3521 SET person_match_ind = cst_mi_val_23, --23 - Match to be reviewed, but there was no discrepancy
3522 -- and so retaining the existing values
3523 status = cst_stat_val_1,
3524 error_code = NULL
3525 WHERE interface_id = person_rec.interface_id;
3526 END IF;
3527 ELSE
3528 IF person_rec.person_match_ind = '20' THEN
3529 UPDATE igs_ad_interface_all
3530 SET status = '3' -- Record must have been processed in a previous run
3531 WHERE interface_id = person_rec.interface_id;
3532 ELSIF person_rec.person_match_ind = '23' THEN
3533 UPDATE igs_ad_interface_all
3534 SET status = '1' -- Record must have been processed in a previous run
3535 WHERE interface_id = person_rec.interface_id;
3536 END IF;
3537 END IF; -- person_match_ind check
3538 END IF; -- l_attribute_action check for 'I','R' or 'E'
3539 END IF; -- End IF for l_lvcAction = 'E','I','R' OR 'D'
3540
3541 ELSIF l_match_ind IN ('11','16') THEN --No Match
3542
3543 CREATE_PERSON(P_person_rec => person_rec,
3544 P_ADDR_TYPE => NULL ,
3545 P_PERSON_ID_TYPE => NULL ,
3546 P_PERSON_ID => person_rec.person_id );
3547
3548 -- We need to populate the person_id into the table igs_ad_interface
3549 -- since it has been created just now
3550 -- if we are not updating the person_id in igs_ad_interface
3551 -- next time when we run the import process
3552 -- we can't update the person
3553 IF person_rec.person_id IS NOT NULL THEN
3554 UPDATE igs_ad_interface_all
3555 SET person_id = person_rec.person_id
3556 WHERE interface_id = person_rec.interface_id;
3557 END IF;
3558 END IF;-- End If for person_rec.match_ind
3559 -- This process should update the interface_run_id for each processed row
3560 -- in the IGS_AD_INTERFACE with the interface_run_id passed as the parameter
3561
3562 -- this will be helpful, after we have finished all the import process
3563 -- and when we try to find out NOCOPY whether the status is complete or failed
3564
3565 -- the code to update interface run id in igs_ad_interface is removed from here
3566 -- Now the updation is done in IGSAD79B.pls before call to prc_pe_dtls
3567 END LOOP;
3568 CLOSE person_cur;
3569 END prc_pe_dtls;
3570
3571
3572 PROCEDURE validate_address (p_addr_rec IN IGS_AD_ADDR_INT_ALL%ROWTYPE,
3573 p_person_id IN igs_pe_person_base_v.PERSON_ID%TYPE,
3574 p_status OUT NOCOPY VARCHAR2,
3575 p_error_code OUT NOCOPY VARCHAR2) AS
3576
3577 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
3578 SELECT birth_date
3579 FROM igs_pe_person_base_v
3580 WHERE person_id = cp_person_id;
3581
3582 CURSOR terr_name_cur(cp_territory_code FND_TERRITORIES_VL.TERRITORY_CODE%TYPE) IS
3583 SELECT territory_short_name
3584 FROM FND_TERRITORIES_VL
3585 WHERE territory_code = UPPER(cp_territory_code);
3586
3587 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
3588 l_territory_short_name FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE;
3589 BEGIN
3590
3591
3592 IF p_addr_rec.START_DATE IS NULL THEN
3593 p_error_code := 'E212';
3594 RAISE NO_DATA_FOUND;
3595 END IF;
3596
3597 IF p_addr_rec.END_DATE IS NOT NULL THEN
3598 IF p_addr_rec.END_DATE < p_addr_rec.START_DATE THEN
3599 p_error_code := 'E208';
3600 RAISE NO_DATA_FOUND;
3601 END IF;
3602 END IF;
3603
3604 OPEN birth_dt_cur(p_person_id);
3605 FETCH birth_dt_cur INTO l_birth_dt;
3606 CLOSE birth_dt_cur;
3607
3608 IF l_birth_dt IS NOT NULL THEN
3609 IF p_addr_rec.START_DATE < l_birth_dt THEN
3610 p_error_code := 'E222';
3611 RAISE NO_DATA_FOUND;
3612 END IF;
3613 END IF;
3614
3615 OPEN terr_name_cur(p_addr_rec.country);
3616 FETCH terr_name_cur INTO l_territory_short_name ;
3617 IF terr_name_cur%NOTFOUND THEN
3618 p_error_code := 'E209';
3619 RAISE NO_DATA_FOUND;
3620 END IF;
3621 CLOSE terr_name_cur;
3622
3623
3624 IF p_addr_rec.CORRESPONDENCE_flag IS NOT NULL AND
3625 p_addr_rec.CORRESPONDENCE_flag NOT IN ('Y', 'N') THEN
3626 p_error_code := 'E213';
3627 RAISE NO_DATA_FOUND;
3628 END IF;
3629
3630
3631 p_status := p_addr_rec.status;
3632 p_error_code := p_addr_rec.error_code;
3633
3634
3635
3636 EXCEPTION
3637 WHEN NO_DATA_FOUND THEN
3638
3639 IF terr_name_cur%ISOPEN THEN
3640 CLOSE terr_name_cur;
3641 END IF;
3642
3643 p_status := 3;
3644
3645 END validate_address;
3646 END Igs_Ad_Imp_002;