[Home] [Help]
962: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
963:
964: CURSOR person_language_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
965: SELECT hii.*, i.person_id
966: FROM igs_ad_language_int_all hii, igs_ad_interface_all i
967: WHERE hii.interface_run_id = cp_interface_run_id
968: AND i.interface_id = hii.interface_id
969: AND i.interface_run_id = cp_interface_run_id
970: AND hii.status = '2';
1030:
1031: EXCEPTION
1032: WHEN NO_DATA_FOUND THEN
1033:
1034: UPDATE igs_ad_language_int_all
1035: SET status = '3',
1036: error_code = l_error_code
1037: WHERE interface_language_id = p_person_language_rec.interface_language_id;
1038:
1127: END LOOP;
1128: l_msg_data := l_tmp_var1;
1129: END IF;
1130:
1131: UPDATE igs_ad_language_int_all
1132: SET status = '3',
1133: error_code = 'E322'
1134: WHERE interface_language_id = p_person_language_rec.interface_language_id;
1135:
1158: END IF;
1159:
1160: ELSE
1161:
1162: UPDATE igs_ad_language_int_all
1163: SET status = '1',
1164: error_code = NULL
1165: WHERE interface_language_id = p_person_language_rec.interface_language_id;
1166:
1168: END IF;
1169:
1170: EXCEPTION
1171: WHEN OTHERS THEN
1172: UPDATE igs_ad_language_int_all
1173: SET status = '3',
1174: error_code = 'E322'
1175: WHERE interface_language_id = p_person_language_rec.interface_language_id;
1176:
1211: l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_LANGUAGES');
1212:
1213: -- 1.If rule is E or I, then if the match_ind is not null, the combination is invalid
1214: IF l_rule IN ('E','I') THEN
1215: UPDATE igs_ad_language_int_all
1216: SET status = cst_stat_val_3,
1217: ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
1218: WHERE match_ind IS NOT NULL
1219: AND interface_run_id = l_interface_run_id
1221: END IF;
1222:
1223: --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
1224: IF l_rule = 'E' THEN
1225: UPDATE igs_ad_language_int_all ai
1226: SET status = cst_stat_val_1,
1227: match_ind = cst_mi_val_19
1228: WHERE ai.interface_run_id = l_interface_run_id
1229: AND ai.status = cst_stat_val_2
1238:
1239: -- 3.If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
1240: -- processed in prior runs and didn't get updated .. update to status 1
1241: IF l_rule = 'R' THEN
1242: UPDATE igs_ad_language_int_all
1243: SET status = cst_stat_val_1
1244: WHERE interface_run_id = l_interface_run_id
1245: AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
1246: AND status=cst_stat_val_2;
1247: END IF;
1248:
1249: -- 4.If rule is R and match_ind is neither 21 nor 25 then error
1250: IF l_rule = 'R' THEN
1251: UPDATE igs_ad_language_int_all
1252: SET status = cst_stat_val_3,
1253: ERROR_CODE = cst_err_val_695
1254: WHERE interface_run_id = l_interface_run_id
1255: AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
1257: END IF;
1258:
1259: -- 5.If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
1260: IF l_rule = 'R' THEN
1261: UPDATE igs_ad_language_int_all mi
1262: SET status = cst_stat_val_1,
1263: match_ind = cst_mi_val_23
1264: WHERE mi.interface_run_id = l_interface_run_id
1265: AND mi.match_ind IS NULL
1281: END IF;
1282:
1283: -- 6.If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
1284: IF l_rule = 'R' THEN
1285: UPDATE igs_ad_language_int_all mi
1286: SET status = cst_stat_val_3,
1287: match_ind = cst_mi_val_20
1288: WHERE mi.interface_run_id = l_interface_run_id
1289: AND mi.match_ind IS NULL
1368: END LOOP;
1369: l_msg_data := l_tmp_var1;
1370: END IF;
1371:
1372: UPDATE igs_ad_language_int_all
1373: SET error_code = 'E014',
1374: status='3'
1375: WHERE interface_language_id = person_language_rec.interface_language_id;
1376:
1400:
1401:
1402: ELSE
1403:
1404: UPDATE igs_ad_language_int_all
1405: SET status=cst_stat_val_1,
1406: match_ind = cst_mi_val_18
1407: WHERE interface_language_id = person_language_rec.interface_language_id;
1408: END IF;
1433: IF l_enable_log = 'Y' THEN
1434: igs_ad_imp_001.logerrormessage(person_language_rec.interface_language_id,'E014');
1435: END IF;
1436:
1437: UPDATE igs_ad_language_int_all
1438: SET match_ind = NULL,
1439: status = cst_stat_val_3,
1440: error_code = cst_err_val_014
1441: WHERE interface_language_id = person_language_rec.interface_language_id;
1490: END LOOP;
1491: l_msg_data := l_tmp_var1;
1492: END IF;
1493:
1494: UPDATE igs_ad_language_int_all
1495: SET error_code = 'E014',
1496: status='3'
1497: WHERE interface_language_id = person_language_rec.interface_language_id;
1498:
1520: igs_ad_imp_001.logerrormessage(person_language_rec.interface_language_id,'E014');
1521: END IF;
1522:
1523: ELSE
1524: UPDATE igs_ad_language_int_all
1525: SET status=cst_stat_val_1,
1526: match_ind = cst_mi_val_18
1527: WHERE interface_language_id = person_language_rec.interface_language_id;
1528: END IF;
1553: IF l_enable_log = 'Y' THEN
1554: igs_ad_imp_001.logerrormessage(person_language_rec.interface_language_id,'E014');
1555: END IF;
1556:
1557: UPDATE igs_ad_language_int_all
1558: SET match_ind = NULL,
1559: status = cst_stat_val_3,
1560: error_code = cst_err_val_014
1561: WHERE interface_language_id = person_language_rec.interface_language_id;