eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_AD_IMP_026 dependencies on IGS_PE_PASSPORT

Line 60: CURSOR pass_dtls(cp_interface_run_id igs_pe_passport_int.interface_run_id%TYPE) IS

56: AND vh.interface_run_id = cp_interface_run_id
57: AND pev.visa_type = UPPER(vi.visa_type)
58: AND pev.visa_issue_date = TRUNC(vi.visa_issue_date);
59:
60: CURSOR pass_dtls(cp_interface_run_id igs_pe_passport_int.interface_run_id%TYPE) IS
61: SELECT pi.*, i.person_id
62: FROM igs_pe_passport_int pi,
63: igs_ad_interface_all i
64: WHERE pi.interface_id = i.interface_id

Line 62: FROM igs_pe_passport_int pi,

58: AND pev.visa_issue_date = TRUNC(vi.visa_issue_date);
59:
60: CURSOR pass_dtls(cp_interface_run_id igs_pe_passport_int.interface_run_id%TYPE) IS
61: SELECT pi.*, i.person_id
62: FROM igs_pe_passport_int pi,
63: igs_ad_interface_all i
64: WHERE pi.interface_id = i.interface_id
65: AND pi.STATUS = '2'
66: AND pi.interface_run_id = cp_interface_run_id

Line 94: FROM igs_pe_passport p

90: WHERE person_id = visa_rec.AGENT_PERSON_ID ;
91:
92: CURSOR valid_pas_id IS
93: SELECT passport_number
94: FROM igs_pe_passport p
95: WHERE p.person_id = visa_rec.person_id AND
96: p.passport_id = visa_rec.passport_id;
97:
98: CURSOR visa_issue_match_cur(cp_lookup_type igs_lookup_values.lookup_type%TYPE,

Line 962: UPDATE igs_pe_passport_int

958:
959: p_err_code := NULL;
960:
961: IF p_mode IS NULL THEN
962: UPDATE igs_pe_passport_int
963: SET status = '1',
964: error_code = p_err_code
965: WHERE interface_passport_id = pass_rec.interface_passport_id;
966: END IF;

Line 978: UPDATE igs_pe_passport_int

974: CLOSE birth_dt_cur;
975: END IF;
976:
977: IF p_mode IS NULL THEN
978: UPDATE igs_pe_passport_int
979: SET status = '3',
980: error_code = p_err_code
981: WHERE interface_passport_id = pass_rec.interface_passport_id;
982:

Line 984: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_err_code,'IGS_PE_PASSPORT_INT');

980: error_code = p_err_code
981: WHERE interface_passport_id = pass_rec.interface_passport_id;
982:
983: IF l_enable_log = 'Y' THEN
984: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_err_code,'IGS_PE_PASSPORT_INT');
985: END IF;
986: END IF;
987:
988: RETURN FALSE;

Line 992: UPDATE igs_pe_passport_int

988: RETURN FALSE;
989: WHEN OTHERS THEN
990:
991: IF p_mode IS NULL THEN
992: UPDATE igs_pe_passport_int
993: SET status = '3',
994: error_code = p_err_code
995: WHERE interface_passport_id = pass_rec.interface_passport_id;
996: END IF;

Line 1021: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_err_code,'IGS_PE_PASSPORT_INT');

1017: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1018: END IF;
1019:
1020: IF l_enable_log = 'Y' THEN
1021: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_err_code,'IGS_PE_PASSPORT_INT');
1022: END IF;
1023:
1024: RETURN FALSE;
1025: END Validate_Passport;

Line 1069: CURSOR chk_duplicate(cp_person_id igs_pe_passport.person_id%TYPE,

1065: || Performance enhancements done, validations and status
1066: || updations done outside the main loop
1067: */
1068:
1069: CURSOR chk_duplicate(cp_person_id igs_pe_passport.person_id%TYPE,
1070: cp_passport_number igs_pe_passport.passport_number%TYPE ,
1071: cp_passport_cntry_code igs_pe_passport.passport_cntry_code%TYPE) IS
1072: SELECT rowid, pi.*
1073: FROM igs_pe_passport pi

Line 1070: cp_passport_number igs_pe_passport.passport_number%TYPE ,

1066: || updations done outside the main loop
1067: */
1068:
1069: CURSOR chk_duplicate(cp_person_id igs_pe_passport.person_id%TYPE,
1070: cp_passport_number igs_pe_passport.passport_number%TYPE ,
1071: cp_passport_cntry_code igs_pe_passport.passport_cntry_code%TYPE) IS
1072: SELECT rowid, pi.*
1073: FROM igs_pe_passport pi
1074: WHERE person_id = cp_person_id AND

Line 1071: cp_passport_cntry_code igs_pe_passport.passport_cntry_code%TYPE) IS

1067: */
1068:
1069: CURSOR chk_duplicate(cp_person_id igs_pe_passport.person_id%TYPE,
1070: cp_passport_number igs_pe_passport.passport_number%TYPE ,
1071: cp_passport_cntry_code igs_pe_passport.passport_cntry_code%TYPE) IS
1072: SELECT rowid, pi.*
1073: FROM igs_pe_passport pi
1074: WHERE person_id = cp_person_id AND
1075: UPPER(passport_number) = UPPER(cp_passport_number) AND

Line 1073: FROM igs_pe_passport pi

1069: CURSOR chk_duplicate(cp_person_id igs_pe_passport.person_id%TYPE,
1070: cp_passport_number igs_pe_passport.passport_number%TYPE ,
1071: cp_passport_cntry_code igs_pe_passport.passport_cntry_code%TYPE) IS
1072: SELECT rowid, pi.*
1073: FROM igs_pe_passport pi
1074: WHERE person_id = cp_person_id AND
1075: UPPER(passport_number) = UPPER(cp_passport_number) AND
1076: passport_cntry_code = UPPER(cp_passport_cntry_code);
1077:

Line 1086: l_dup_id igs_pe_passport.passport_id%TYPE;

1082: l_error_code VARCHAR2(30);
1083: l_status VARCHAR2(10);
1084: l_dup_var BOOLEAN;
1085: pass_rec pass_dtls%ROWTYPE;
1086: l_dup_id igs_pe_passport.passport_id%TYPE;
1087: l_processed_records NUMBER(5) := 0;
1088: -- l_pass_rec variable will get populated during duplicate check
1089: l_pass_rec chk_duplicate%ROWTYPE;
1090:

Line 1105: l_pass_id IGS_PE_PASSPORT.passport_id%TYPE;

1101: status OUT NOCOPY VARCHAR2)
1102: AS
1103: l_rowid ROWID := NULL;
1104: l_error VARCHAR2(30);
1105: l_pass_id IGS_PE_PASSPORT.passport_id%TYPE;
1106:
1107: BEGIN
1108: --CALL TO PASSPORT INSERT RECORD
1109:

Line 1110: IGS_PE_PASSPORT_PKG.INSERT_ROW(

1106:
1107: BEGIN
1108: --CALL TO PASSPORT INSERT RECORD
1109:
1110: IGS_PE_PASSPORT_PKG.INSERT_ROW(
1111: X_ROWID => l_rowid,
1112: X_PASSPORT_ID => l_pass_id ,
1113: X_PERSON_ID => pass_rec.person_id,
1114: X_PASSPORT_NUMBER => pass_rec.passport_number,

Line 1144: UPDATE igs_pe_passport_int

1140:
1141: -- IF SUCCESSFUL INSERT THEN
1142:
1143: l_error := NULL;
1144: UPDATE igs_pe_passport_int
1145: SET status = '1',
1146: error_code = l_error
1147: WHERE interface_passport_id = pass_rec.interface_passport_id;
1148:

Line 1153: UPDATE igs_pe_passport_int

1149: EXCEPTION
1150: WHEN OTHERS THEN
1151: l_error := 'E322';
1152:
1153: UPDATE igs_pe_passport_int
1154: SET status = '3',
1155: error_code = l_error
1156: WHERE interface_passport_id = pass_rec.interface_passport_id;
1157:

Line 1181: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,l_error,'IGS_PE_PASSPORT_INT');

1177: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1178: END IF;
1179:
1180: IF l_enable_log = 'Y' THEN
1181: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,l_error,'IGS_PE_PASSPORT_INT');
1182: END IF;
1183:
1184: END crt_pe_pass;
1185:

Line 1201: -- MAKE CALL TO THE TBH i.e IGS_PE_PASSPORT_PKG.UPDATE_ROW

1197: l_app VARCHAR2(50);
1198:
1199: BEGIN
1200:
1201: -- MAKE CALL TO THE TBH i.e IGS_PE_PASSPORT_PKG.UPDATE_ROW
1202: igs_pe_passport_pkg.update_row(
1203: X_ROWID => dup_pass_rec.rowid,
1204: X_PASSPORT_ID => dup_pass_rec.passport_id,
1205: X_PERSON_ID => NVL(pass_rec.person_id,dup_pass_rec.person_id),

Line 1202: igs_pe_passport_pkg.update_row(

1198:
1199: BEGIN
1200:
1201: -- MAKE CALL TO THE TBH i.e IGS_PE_PASSPORT_PKG.UPDATE_ROW
1202: igs_pe_passport_pkg.update_row(
1203: X_ROWID => dup_pass_rec.rowid,
1204: X_PASSPORT_ID => dup_pass_rec.passport_id,
1205: X_PERSON_ID => NVL(pass_rec.person_id,dup_pass_rec.person_id),
1206: X_PASSPORT_NUMBER => NVL(pass_rec.passport_number,dup_pass_rec.passport_number),

Line 1278: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_error_code,'IGS_PE_PASSPORT_INT');

1274:
1275: END IF;
1276:
1277: IF l_enable_log = 'Y' THEN
1278: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_error_code,'IGS_PE_PASSPORT_INT');
1279: END IF;
1280:
1281: UPDATE igs_pe_passport_int
1282: SET status = p_status,

Line 1281: UPDATE igs_pe_passport_int

1277: IF l_enable_log = 'Y' THEN
1278: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_error_code,'IGS_PE_PASSPORT_INT');
1279: END IF;
1280:
1281: UPDATE igs_pe_passport_int
1282: SET status = p_status,
1283: error_code = p_error_code
1284: WHERE interface_passport_id = pass_rec.interface_passport_id;
1285:

Line 1315: UPDATE igs_pe_passport_int

1311: l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
1312:
1313: IF l_rule = 'E' OR l_rule = 'I' THEN
1314:
1315: UPDATE igs_pe_passport_int
1316: SET status='3',
1317: error_code = 'E695'
1318: WHERE
1319: interface_run_id=l_interface_run_id

Line 1325: UPDATE igs_pe_passport_int pi

1321: AND match_ind IS NOT NULL;
1322:
1323: IF l_rule = 'E' THEN
1324:
1325: UPDATE igs_pe_passport_int pi
1326: SET status='1',
1327: match_ind='19'
1328: WHERE
1329: interface_run_id=l_interface_run_id

Line 1332: FROM igs_pe_passport ps,

1328: WHERE
1329: interface_run_id=l_interface_run_id
1330: AND STATUS = '2'
1331: AND EXISTS( SELECT ps.rowid
1332: FROM igs_pe_passport ps,
1333: igs_ad_interface_all ad
1334: WHERE ad.interface_id = pi.interface_id AND
1335: ad.interface_run_id = l_interface_run_id AND
1336: ps.person_id = ad.person_id AND

Line 1343: UPDATE igs_pe_passport_int

1339: END IF;
1340:
1341: ELSIF l_rule = 'R' THEN
1342:
1343: UPDATE igs_pe_passport_int
1344: SET status = '1'
1345: WHERE
1346: interface_run_id=l_interface_run_id
1347: AND status = '2'

Line 1350: UPDATE igs_pe_passport_int

1346: interface_run_id=l_interface_run_id
1347: AND status = '2'
1348: AND match_ind IN ('18','19','22','23');
1349:
1350: UPDATE igs_pe_passport_int
1351: SET status = '3',
1352: error_code = 'E695'
1353: WHERE
1354: interface_run_id=l_interface_run_id

Line 1358: UPDATE igs_pe_passport_int pi

1354: interface_run_id=l_interface_run_id
1355: AND status = '2'
1356: AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
1357:
1358: UPDATE igs_pe_passport_int pi
1359: SET status='1',
1360: match_ind = '23'
1361: WHERE
1362: interface_run_id=l_interface_run_id

Line 1366: FROM igs_pe_passport ps,

1362: interface_run_id=l_interface_run_id
1363: AND status = '2'
1364: AND match_ind IS NULL
1365: AND EXISTS( SELECT ps.rowid
1366: FROM igs_pe_passport ps,
1367: igs_ad_interface_all ad
1368: WHERE ad.interface_id = pi.interface_id AND
1369: ad.interface_run_id = l_interface_run_id AND
1370: ps.person_id = ad.person_id AND

Line 1396: UPDATE igs_pe_passport_int pi

1392: ((ps.attribute18 = pi.attribute18) OR ((ps.attribute18 IS NULL) AND (pi.attribute18 IS NULL))) AND
1393: ((ps.attribute19 = pi.attribute19) OR ((ps.attribute19 IS NULL) AND (pi.attribute19 IS NULL))) AND
1394: ((ps.attribute20 = pi.attribute20) OR ((ps.attribute20 IS NULL) AND (pi.attribute20 IS NULL))));
1395:
1396: UPDATE igs_pe_passport_int pi
1397: SET status = '3',
1398: match_ind='20',
1399: dup_passport_id = (SELECT passport_id
1400: FROM igs_pe_passport ps,

Line 1400: FROM igs_pe_passport ps,

1396: UPDATE igs_pe_passport_int pi
1397: SET status = '3',
1398: match_ind='20',
1399: dup_passport_id = (SELECT passport_id
1400: FROM igs_pe_passport ps,
1401: igs_ad_interface_all ad
1402: WHERE ad.interface_id = pi.interface_id AND
1403: ad.interface_run_id = l_interface_run_id AND
1404: ps.person_id = ad.person_id AND

Line 1411: FROM igs_pe_passport ps,

1407: WHERE interface_run_id=l_interface_run_id AND
1408: status = '2' AND
1409: match_ind IS NULL AND
1410: EXISTS (SELECT ps.rowid
1411: FROM igs_pe_passport ps,
1412: igs_ad_interface_all ad
1413: WHERE ad.interface_id = pi.interface_id AND
1414: ps.person_id = ad.person_id AND
1415: ad.interface_run_id = l_interface_run_id AND

Line 1452: UPDATE igs_pe_passport_int

1448: dup_pass_rec => l_pass_rec,
1449: p_error_code => l_error_code,
1450: p_status => l_status);
1451:
1452: UPDATE igs_pe_passport_int
1453: SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
1454: status = l_status ,
1455: error_code = l_error_code
1456: WHERE interface_passport_id= pass_rec.interface_passport_id;

Line 1482: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');

1478: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1479: END IF;
1480:
1481: IF l_enable_log = 'Y' THEN
1482: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');
1483: END IF;
1484:
1485: UPDATE igs_pe_passport_int
1486: SET match_ind = '18',

Line 1485: UPDATE igs_pe_passport_int

1481: IF l_enable_log = 'Y' THEN
1482: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');
1483: END IF;
1484:
1485: UPDATE igs_pe_passport_int
1486: SET match_ind = '18',
1487: status = '3',
1488: error_code = 'E014'
1489: WHERE interface_passport_id= pass_rec.interface_passport_id;

Line 1500: UPDATE igs_pe_passport_int

1496: dup_pass_rec => l_pass_rec,
1497: p_error_code => l_error_code,
1498: p_status => l_status);
1499:
1500: UPDATE igs_pe_passport_int
1501: SET status = l_status ,
1502: error_code = l_error_code
1503: WHERE interface_passport_id= pass_rec.interface_passport_id;
1504:

Line 1529: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');

1525: 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(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');
1530: END IF;
1531:
1532: UPDATE igs_pe_passport_int
1533: SET status = '3',

Line 1532: UPDATE igs_pe_passport_int

1528: IF l_enable_log = 'Y' THEN
1529: igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');
1530: END IF;
1531:
1532: UPDATE igs_pe_passport_int
1533: SET status = '3',
1534: error_code = 'E014'
1535: WHERE interface_passport_id= pass_rec.interface_passport_id;
1536: END;