[Home] [Help]
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;
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;
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;
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';
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;
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;
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;
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:
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
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
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
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
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
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';
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'
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;
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',
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;
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'
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;
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;
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;
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'
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;
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',
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;
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:
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
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;
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:
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:
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:
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:
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:
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:
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;
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
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:
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
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;
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',
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;
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',
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;
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',
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;
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',
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;
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',
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;
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',
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;
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:
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:
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:
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
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:
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:
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:
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:
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:
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:
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';
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
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:
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,
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;
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;
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;
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;
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;
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
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
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;
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;
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;
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;
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
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
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