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.IRC_SEEKER_VAC_MATCHING_PKG dependencies on DBMS_SQL

Line 856: matching_values_tbl dbms_sql.varchar2_table;

852: l_last_value VARCHAR2(240);
853: l_matching_values VARCHAR2(240);
854: l_attribute VARCHAR2(14);
855: seeker_criteria_match_vac_rec g_posting_details_rec_type;
856: matching_values_tbl dbms_sql.varchar2_table;
857: l_posting_details_tab g_posting_details_tab_type;
858: l_message_subject VARCHAR2(240);
859: l_base_url VARCHAR2(250);
860: --

Line 1157: -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many

1153: l_counter := l_counter + 1;
1154:
1155: END LOOP;
1156:
1157: -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many
1158: -- times in the inner loop, saving parse time compared to NDS.
1159: l_cursor := DBMS_SQL.OPEN_CURSOR;
1160:
1161: DBMS_SQL.PARSE (c => l_cursor,

Line 1159: l_cursor := DBMS_SQL.OPEN_CURSOR;

1155: END LOOP;
1156:
1157: -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many
1158: -- times in the inner loop, saving parse time compared to NDS.
1159: l_cursor := DBMS_SQL.OPEN_CURSOR;
1160:
1161: DBMS_SQL.PARSE (c => l_cursor,
1162: statement => l_sql,
1163: language_flag => dbms_sql.native);

Line 1161: DBMS_SQL.PARSE (c => l_cursor,

1157: -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many
1158: -- times in the inner loop, saving parse time compared to NDS.
1159: l_cursor := DBMS_SQL.OPEN_CURSOR;
1160:
1161: DBMS_SQL.PARSE (c => l_cursor,
1162: statement => l_sql,
1163: language_flag => dbms_sql.native);
1164:
1165: /*Save existing session language to restore after sending notifications*/

Line 1163: language_flag => dbms_sql.native);

1159: l_cursor := DBMS_SQL.OPEN_CURSOR;
1160:
1161: DBMS_SQL.PARSE (c => l_cursor,
1162: statement => l_sql,
1163: language_flag => dbms_sql.native);
1164:
1165: /*Save existing session language to restore after sending notifications*/
1166: open get_nls_lang(userenv('LANG'));
1167: fetch get_nls_lang into l_dft_lang;

Line 1225: DBMS_SQL.DEFINE_COLUMN (

1221: -- And no applicant assignment has ever existed for the vacancy
1222: --dbms_output.put_line(' Processing Search Criteria:'||seeker_criteria_rec.search_criteria_id);
1223:
1224: -- define the posting_content_id column
1225: DBMS_SQL.DEFINE_COLUMN (
1226: c => l_cursor,
1227: position => 1,
1228: column => seeker_criteria_match_vac_rec.posting_content_id);
1229:

Line 1231: DBMS_SQL.DEFINE_COLUMN (

1227: position => 1,
1228: column => seeker_criteria_match_vac_rec.posting_content_id);
1229:
1230: -- define the job_title column
1231: DBMS_SQL.DEFINE_COLUMN (
1232: c => l_cursor,
1233: position => 2,
1234: column => seeker_criteria_match_vac_rec.job_title,
1235: column_size => 240);

Line 1238: DBMS_SQL.DEFINE_COLUMN (

1234: column => seeker_criteria_match_vac_rec.job_title,
1235: column_size => 240);
1236:
1237: -- define the name column
1238: DBMS_SQL.DEFINE_COLUMN (
1239: c => l_cursor,
1240: position => 3,
1241: column => seeker_criteria_match_vac_rec.name,
1242: column_size => 240);

Line 1245: DBMS_SQL.DEFINE_COLUMN (

1241: column => seeker_criteria_match_vac_rec.name,
1242: column_size => 240);
1243:
1244: -- define the object_id column
1245: DBMS_SQL.DEFINE_COLUMN (
1246: c => l_cursor,
1247: position => 4,
1248: column => seeker_criteria_match_vac_rec.object_id);
1249:

Line 1276: dbms_sql.bind_variable(

1272: dbms_output.put_line(' BINDS match_competence:'||seeker_criteria_rec.match_competence);
1273: */
1274:
1275: -- supply binds (bind by name)
1276: dbms_sql.bind_variable(
1277: l_cursor, 'person_id', seeker_criteria_rec.object_id);
1278: dbms_sql.bind_variable(
1279: l_cursor, 'current_employee', nvl(seeker_criteria_rec.current_employee_flag,'N'));
1280: dbms_sql.bind_variable(

Line 1278: dbms_sql.bind_variable(

1274:
1275: -- supply binds (bind by name)
1276: dbms_sql.bind_variable(
1277: l_cursor, 'person_id', seeker_criteria_rec.object_id);
1278: dbms_sql.bind_variable(
1279: l_cursor, 'current_employee', nvl(seeker_criteria_rec.current_employee_flag,'N'));
1280: dbms_sql.bind_variable(
1281: l_cursor, 'party_id', seeker_rec.party_id);
1282: dbms_sql.bind_variable(

Line 1280: dbms_sql.bind_variable(

1276: dbms_sql.bind_variable(
1277: l_cursor, 'person_id', seeker_criteria_rec.object_id);
1278: dbms_sql.bind_variable(
1279: l_cursor, 'current_employee', nvl(seeker_criteria_rec.current_employee_flag,'N'));
1280: dbms_sql.bind_variable(
1281: l_cursor, 'party_id', seeker_rec.party_id);
1282: dbms_sql.bind_variable(
1283: l_cursor, 'employee', seeker_criteria_rec.employee);
1284: dbms_sql.bind_variable(

Line 1282: dbms_sql.bind_variable(

1278: dbms_sql.bind_variable(
1279: l_cursor, 'current_employee', nvl(seeker_criteria_rec.current_employee_flag,'N'));
1280: dbms_sql.bind_variable(
1281: l_cursor, 'party_id', seeker_rec.party_id);
1282: dbms_sql.bind_variable(
1283: l_cursor, 'employee', seeker_criteria_rec.employee);
1284: dbms_sql.bind_variable(
1285: l_cursor, 'contractor', seeker_criteria_rec.contractor);
1286: dbms_sql.bind_variable(

Line 1284: dbms_sql.bind_variable(

1280: dbms_sql.bind_variable(
1281: l_cursor, 'party_id', seeker_rec.party_id);
1282: dbms_sql.bind_variable(
1283: l_cursor, 'employee', seeker_criteria_rec.employee);
1284: dbms_sql.bind_variable(
1285: l_cursor, 'contractor', seeker_criteria_rec.contractor);
1286: dbms_sql.bind_variable(
1287: l_cursor, 'travel_percentage', seeker_criteria_rec.travel_percentage);
1288: dbms_sql.bind_variable(

Line 1286: dbms_sql.bind_variable(

1282: dbms_sql.bind_variable(
1283: l_cursor, 'employee', seeker_criteria_rec.employee);
1284: dbms_sql.bind_variable(
1285: l_cursor, 'contractor', seeker_criteria_rec.contractor);
1286: dbms_sql.bind_variable(
1287: l_cursor, 'travel_percentage', seeker_criteria_rec.travel_percentage);
1288: dbms_sql.bind_variable(
1289: l_cursor, 'salary_currency', seeker_criteria_rec.salary_currency);
1290: dbms_sql.bind_variable(

Line 1288: dbms_sql.bind_variable(

1284: dbms_sql.bind_variable(
1285: l_cursor, 'contractor', seeker_criteria_rec.contractor);
1286: dbms_sql.bind_variable(
1287: l_cursor, 'travel_percentage', seeker_criteria_rec.travel_percentage);
1288: dbms_sql.bind_variable(
1289: l_cursor, 'salary_currency', seeker_criteria_rec.salary_currency);
1290: dbms_sql.bind_variable(
1291: l_cursor, 'min_salary', seeker_criteria_rec.min_salary);
1292: dbms_sql.bind_variable(

Line 1290: dbms_sql.bind_variable(

1286: dbms_sql.bind_variable(
1287: l_cursor, 'travel_percentage', seeker_criteria_rec.travel_percentage);
1288: dbms_sql.bind_variable(
1289: l_cursor, 'salary_currency', seeker_criteria_rec.salary_currency);
1290: dbms_sql.bind_variable(
1291: l_cursor, 'min_salary', seeker_criteria_rec.min_salary);
1292: dbms_sql.bind_variable(
1293: l_cursor, 'show_jobs_since', seeker_criteria_rec.show_jobs_since);
1294: dbms_sql.bind_variable(

Line 1292: dbms_sql.bind_variable(

1288: dbms_sql.bind_variable(
1289: l_cursor, 'salary_currency', seeker_criteria_rec.salary_currency);
1290: dbms_sql.bind_variable(
1291: l_cursor, 'min_salary', seeker_criteria_rec.min_salary);
1292: dbms_sql.bind_variable(
1293: l_cursor, 'show_jobs_since', seeker_criteria_rec.show_jobs_since);
1294: dbms_sql.bind_variable(
1295: l_cursor, 'job_title', seeker_criteria_rec.job_title);
1296: dbms_sql.bind_variable(

Line 1294: dbms_sql.bind_variable(

1290: dbms_sql.bind_variable(
1291: l_cursor, 'min_salary', seeker_criteria_rec.min_salary);
1292: dbms_sql.bind_variable(
1293: l_cursor, 'show_jobs_since', seeker_criteria_rec.show_jobs_since);
1294: dbms_sql.bind_variable(
1295: l_cursor, 'job_title', seeker_criteria_rec.job_title);
1296: dbms_sql.bind_variable(
1297: l_cursor, 'department', seeker_criteria_rec.department);
1298: dbms_sql.bind_variable(

Line 1296: dbms_sql.bind_variable(

1292: dbms_sql.bind_variable(
1293: l_cursor, 'show_jobs_since', seeker_criteria_rec.show_jobs_since);
1294: dbms_sql.bind_variable(
1295: l_cursor, 'job_title', seeker_criteria_rec.job_title);
1296: dbms_sql.bind_variable(
1297: l_cursor, 'department', seeker_criteria_rec.department);
1298: dbms_sql.bind_variable(
1299: l_cursor, 'employment_category', seeker_criteria_rec.employment_category);
1300: if seeker_criteria_rec.keywords is null then

Line 1298: dbms_sql.bind_variable(

1294: dbms_sql.bind_variable(
1295: l_cursor, 'job_title', seeker_criteria_rec.job_title);
1296: dbms_sql.bind_variable(
1297: l_cursor, 'department', seeker_criteria_rec.department);
1298: dbms_sql.bind_variable(
1299: l_cursor, 'employment_category', seeker_criteria_rec.employment_category);
1300: if seeker_criteria_rec.keywords is null then
1301: dbms_sql.bind_variable(
1302: l_cursor, 'keywords', to_char(null));

Line 1301: dbms_sql.bind_variable(

1297: l_cursor, 'department', seeker_criteria_rec.department);
1298: dbms_sql.bind_variable(
1299: l_cursor, 'employment_category', seeker_criteria_rec.employment_category);
1300: if seeker_criteria_rec.keywords is null then
1301: dbms_sql.bind_variable(
1302: l_cursor, 'keywords', to_char(null));
1303: else
1304: dbms_sql.bind_variable(
1305: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(seeker_criteria_rec.keywords));

Line 1304: dbms_sql.bind_variable(

1300: if seeker_criteria_rec.keywords is null then
1301: dbms_sql.bind_variable(
1302: l_cursor, 'keywords', to_char(null));
1303: else
1304: dbms_sql.bind_variable(
1305: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(seeker_criteria_rec.keywords));
1306: end if;
1307: dbms_sql.bind_variable(
1308: l_cursor, 'work_at_home', seeker_criteria_rec.work_at_home);

Line 1307: dbms_sql.bind_variable(

1303: else
1304: dbms_sql.bind_variable(
1305: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(seeker_criteria_rec.keywords));
1306: end if;
1307: dbms_sql.bind_variable(
1308: l_cursor, 'work_at_home', seeker_criteria_rec.work_at_home);
1309: dbms_sql.bind_variable(
1310: l_cursor, 'location', seeker_criteria_rec.location);
1311: dbms_sql.bind_variable(

Line 1309: dbms_sql.bind_variable(

1305: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(seeker_criteria_rec.keywords));
1306: end if;
1307: dbms_sql.bind_variable(
1308: l_cursor, 'work_at_home', seeker_criteria_rec.work_at_home);
1309: dbms_sql.bind_variable(
1310: l_cursor, 'location', seeker_criteria_rec.location);
1311: dbms_sql.bind_variable(
1312: l_cursor, 'longitude', seeker_criteria_rec.geometry.sdo_point.x);
1313: dbms_sql.bind_variable(

Line 1311: dbms_sql.bind_variable(

1307: dbms_sql.bind_variable(
1308: l_cursor, 'work_at_home', seeker_criteria_rec.work_at_home);
1309: dbms_sql.bind_variable(
1310: l_cursor, 'location', seeker_criteria_rec.location);
1311: dbms_sql.bind_variable(
1312: l_cursor, 'longitude', seeker_criteria_rec.geometry.sdo_point.x);
1313: dbms_sql.bind_variable(
1314: l_cursor, 'latitude', seeker_criteria_rec.geometry.sdo_point.y);
1315: dbms_sql.bind_variable(

Line 1313: dbms_sql.bind_variable(

1309: dbms_sql.bind_variable(
1310: l_cursor, 'location', seeker_criteria_rec.location);
1311: dbms_sql.bind_variable(
1312: l_cursor, 'longitude', seeker_criteria_rec.geometry.sdo_point.x);
1313: dbms_sql.bind_variable(
1314: l_cursor, 'latitude', seeker_criteria_rec.geometry.sdo_point.y);
1315: dbms_sql.bind_variable(
1316: l_cursor, 'distance_to_location', seeker_criteria_rec.distance_to_location);
1317: -- Newly added for geocode_location, geocode_country start

Line 1315: dbms_sql.bind_variable(

1311: dbms_sql.bind_variable(
1312: l_cursor, 'longitude', seeker_criteria_rec.geometry.sdo_point.x);
1313: dbms_sql.bind_variable(
1314: l_cursor, 'latitude', seeker_criteria_rec.geometry.sdo_point.y);
1315: dbms_sql.bind_variable(
1316: l_cursor, 'distance_to_location', seeker_criteria_rec.distance_to_location);
1317: -- Newly added for geocode_location, geocode_country start
1318: dbms_sql.bind_variable(
1319: l_cursor, 'geocode_location', seeker_criteria_rec.geocode_location);

Line 1318: dbms_sql.bind_variable(

1314: l_cursor, 'latitude', seeker_criteria_rec.geometry.sdo_point.y);
1315: dbms_sql.bind_variable(
1316: l_cursor, 'distance_to_location', seeker_criteria_rec.distance_to_location);
1317: -- Newly added for geocode_location, geocode_country start
1318: dbms_sql.bind_variable(
1319: l_cursor, 'geocode_location', seeker_criteria_rec.geocode_location);
1320: dbms_sql.bind_variable(
1321: l_cursor, 'geocode_country', seeker_criteria_rec.geocode_country);
1322: -- Newly added for geocode_location, geocode_country end

Line 1320: dbms_sql.bind_variable(

1316: l_cursor, 'distance_to_location', seeker_criteria_rec.distance_to_location);
1317: -- Newly added for geocode_location, geocode_country start
1318: dbms_sql.bind_variable(
1319: l_cursor, 'geocode_location', seeker_criteria_rec.geocode_location);
1320: dbms_sql.bind_variable(
1321: l_cursor, 'geocode_country', seeker_criteria_rec.geocode_country);
1322: -- Newly added for geocode_location, geocode_country end
1323: dbms_sql.bind_variable(
1324: l_cursor, 'location_id', seeker_criteria_rec.location_id);

Line 1323: dbms_sql.bind_variable(

1319: l_cursor, 'geocode_location', seeker_criteria_rec.geocode_location);
1320: dbms_sql.bind_variable(
1321: l_cursor, 'geocode_country', seeker_criteria_rec.geocode_country);
1322: -- Newly added for geocode_location, geocode_country end
1323: dbms_sql.bind_variable(
1324: l_cursor, 'location_id', seeker_criteria_rec.location_id);
1325: dbms_sql.bind_variable(
1326: l_cursor, 'search_criteria_id', seeker_criteria_rec.search_criteria_id);
1327: dbms_sql.bind_variable(

Line 1325: dbms_sql.bind_variable(

1321: l_cursor, 'geocode_country', seeker_criteria_rec.geocode_country);
1322: -- Newly added for geocode_location, geocode_country end
1323: dbms_sql.bind_variable(
1324: l_cursor, 'location_id', seeker_criteria_rec.location_id);
1325: dbms_sql.bind_variable(
1326: l_cursor, 'search_criteria_id', seeker_criteria_rec.search_criteria_id);
1327: dbms_sql.bind_variable(
1328: l_cursor, 'p_ignore_job_age', p_ignore_job_age);
1329: dbms_sql.bind_variable(

Line 1327: dbms_sql.bind_variable(

1323: dbms_sql.bind_variable(
1324: l_cursor, 'location_id', seeker_criteria_rec.location_id);
1325: dbms_sql.bind_variable(
1326: l_cursor, 'search_criteria_id', seeker_criteria_rec.search_criteria_id);
1327: dbms_sql.bind_variable(
1328: l_cursor, 'p_ignore_job_age', p_ignore_job_age);
1329: dbms_sql.bind_variable(
1330: l_cursor, 'match_qualification', seeker_criteria_rec.match_qualification);
1331: dbms_sql.bind_variable(

Line 1329: dbms_sql.bind_variable(

1325: dbms_sql.bind_variable(
1326: l_cursor, 'search_criteria_id', seeker_criteria_rec.search_criteria_id);
1327: dbms_sql.bind_variable(
1328: l_cursor, 'p_ignore_job_age', p_ignore_job_age);
1329: dbms_sql.bind_variable(
1330: l_cursor, 'match_qualification', seeker_criteria_rec.match_qualification);
1331: dbms_sql.bind_variable(
1332: l_cursor, 'match_competence', seeker_criteria_rec.match_competence);
1333:

Line 1331: dbms_sql.bind_variable(

1327: dbms_sql.bind_variable(
1328: l_cursor, 'p_ignore_job_age', p_ignore_job_age);
1329: dbms_sql.bind_variable(
1330: l_cursor, 'match_qualification', seeker_criteria_rec.match_qualification);
1331: dbms_sql.bind_variable(
1332: l_cursor, 'match_competence', seeker_criteria_rec.match_competence);
1333:
1334: for ct in 0..matching_values_tbl.count-1 loop
1335: IF (matching_values_tbl(ct)= 'ATTRIBUTE1')

Line 1336: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(seeker_criteria_rec.attribute1));

1332: l_cursor, 'match_competence', seeker_criteria_rec.match_competence);
1333:
1334: for ct in 0..matching_values_tbl.count-1 loop
1335: IF (matching_values_tbl(ct)= 'ATTRIBUTE1')
1336: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(seeker_criteria_rec.attribute1));
1337:
1338: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1339: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(seeker_criteria_rec.attribute2));
1340:

Line 1339: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(seeker_criteria_rec.attribute2));

1335: IF (matching_values_tbl(ct)= 'ATTRIBUTE1')
1336: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(seeker_criteria_rec.attribute1));
1337:
1338: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1339: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(seeker_criteria_rec.attribute2));
1340:
1341: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1342: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(seeker_criteria_rec.attribute3));
1343:

Line 1342: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(seeker_criteria_rec.attribute3));

1338: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1339: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(seeker_criteria_rec.attribute2));
1340:
1341: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1342: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(seeker_criteria_rec.attribute3));
1343:
1344: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1345: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(seeker_criteria_rec.attribute4));
1346:

Line 1345: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(seeker_criteria_rec.attribute4));

1341: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1342: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(seeker_criteria_rec.attribute3));
1343:
1344: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1345: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(seeker_criteria_rec.attribute4));
1346:
1347: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1348: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(seeker_criteria_rec.attribute5));
1349:

Line 1348: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(seeker_criteria_rec.attribute5));

1344: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1345: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(seeker_criteria_rec.attribute4));
1346:
1347: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1348: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(seeker_criteria_rec.attribute5));
1349:
1350: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1351: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(seeker_criteria_rec.attribute6));
1352:

Line 1351: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(seeker_criteria_rec.attribute6));

1347: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1348: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(seeker_criteria_rec.attribute5));
1349:
1350: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1351: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(seeker_criteria_rec.attribute6));
1352:
1353: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1354: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(seeker_criteria_rec.attribute7));
1355:

Line 1354: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(seeker_criteria_rec.attribute7));

1350: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1351: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(seeker_criteria_rec.attribute6));
1352:
1353: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1354: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(seeker_criteria_rec.attribute7));
1355:
1356: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1357: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(seeker_criteria_rec.attribute8));
1358:

Line 1357: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(seeker_criteria_rec.attribute8));

1353: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1354: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(seeker_criteria_rec.attribute7));
1355:
1356: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1357: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(seeker_criteria_rec.attribute8));
1358:
1359: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1360: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(seeker_criteria_rec.attribute9));
1361:

Line 1360: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(seeker_criteria_rec.attribute9));

1356: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1357: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(seeker_criteria_rec.attribute8));
1358:
1359: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1360: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(seeker_criteria_rec.attribute9));
1361:
1362: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1363: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(seeker_criteria_rec.attribute10));
1364:

Line 1363: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(seeker_criteria_rec.attribute10));

1359: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1360: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(seeker_criteria_rec.attribute9));
1361:
1362: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1363: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(seeker_criteria_rec.attribute10));
1364:
1365: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1366: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(seeker_criteria_rec.attribute11));
1367:

Line 1366: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(seeker_criteria_rec.attribute11));

1362: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1363: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(seeker_criteria_rec.attribute10));
1364:
1365: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1366: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(seeker_criteria_rec.attribute11));
1367:
1368: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1369: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(seeker_criteria_rec.attribute12));
1370:

Line 1369: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(seeker_criteria_rec.attribute12));

1365: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1366: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(seeker_criteria_rec.attribute11));
1367:
1368: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1369: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(seeker_criteria_rec.attribute12));
1370:
1371: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1372: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(seeker_criteria_rec.attribute13));
1373:

Line 1372: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(seeker_criteria_rec.attribute13));

1368: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1369: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(seeker_criteria_rec.attribute12));
1370:
1371: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1372: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(seeker_criteria_rec.attribute13));
1373:
1374: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1375: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(seeker_criteria_rec.attribute14));
1376:

Line 1375: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(seeker_criteria_rec.attribute14));

1371: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1372: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(seeker_criteria_rec.attribute13));
1373:
1374: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1375: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(seeker_criteria_rec.attribute14));
1376:
1377: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1378: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(seeker_criteria_rec.attribute15));
1379:

Line 1378: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(seeker_criteria_rec.attribute15));

1374: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1375: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(seeker_criteria_rec.attribute14));
1376:
1377: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1378: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(seeker_criteria_rec.attribute15));
1379:
1380: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1381: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(seeker_criteria_rec.attribute16));
1382:

Line 1381: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(seeker_criteria_rec.attribute16));

1377: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1378: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(seeker_criteria_rec.attribute15));
1379:
1380: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1381: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(seeker_criteria_rec.attribute16));
1382:
1383: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1384: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(seeker_criteria_rec.attribute17));
1385:

Line 1384: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(seeker_criteria_rec.attribute17));

1380: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1381: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(seeker_criteria_rec.attribute16));
1382:
1383: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1384: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(seeker_criteria_rec.attribute17));
1385:
1386: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1387: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(seeker_criteria_rec.attribute18));
1388:

Line 1387: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(seeker_criteria_rec.attribute18));

1383: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1384: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(seeker_criteria_rec.attribute17));
1385:
1386: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1387: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(seeker_criteria_rec.attribute18));
1388:
1389: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1390: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(seeker_criteria_rec.attribute19));
1391:

Line 1390: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(seeker_criteria_rec.attribute19));

1386: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1387: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(seeker_criteria_rec.attribute18));
1388:
1389: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1390: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(seeker_criteria_rec.attribute19));
1391:
1392: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1393: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(seeker_criteria_rec.attribute20));
1394:

Line 1393: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(seeker_criteria_rec.attribute20));

1389: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1390: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(seeker_criteria_rec.attribute19));
1391:
1392: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1393: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(seeker_criteria_rec.attribute20));
1394:
1395: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1396: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(seeker_criteria_rec.attribute21));
1397:

Line 1396: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(seeker_criteria_rec.attribute21));

1392: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1393: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(seeker_criteria_rec.attribute20));
1394:
1395: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1396: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(seeker_criteria_rec.attribute21));
1397:
1398: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1399: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(seeker_criteria_rec.attribute22));
1400:

Line 1399: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(seeker_criteria_rec.attribute22));

1395: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1396: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(seeker_criteria_rec.attribute21));
1397:
1398: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1399: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(seeker_criteria_rec.attribute22));
1400:
1401: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
1402: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(seeker_criteria_rec.attribute23));
1403:

Line 1402: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(seeker_criteria_rec.attribute23));

1398: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1399: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(seeker_criteria_rec.attribute22));
1400:
1401: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
1402: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(seeker_criteria_rec.attribute23));
1403:
1404: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
1405: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(seeker_criteria_rec.attribute24));
1406:

Line 1405: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(seeker_criteria_rec.attribute24));

1401: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
1402: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(seeker_criteria_rec.attribute23));
1403:
1404: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
1405: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(seeker_criteria_rec.attribute24));
1406:
1407: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
1408: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(seeker_criteria_rec.attribute25));
1409:

Line 1408: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(seeker_criteria_rec.attribute25));

1404: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
1405: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(seeker_criteria_rec.attribute24));
1406:
1407: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
1408: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(seeker_criteria_rec.attribute25));
1409:
1410: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
1411: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(seeker_criteria_rec.attribute26));
1412:

Line 1411: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(seeker_criteria_rec.attribute26));

1407: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
1408: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(seeker_criteria_rec.attribute25));
1409:
1410: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
1411: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(seeker_criteria_rec.attribute26));
1412:
1413: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
1414: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(seeker_criteria_rec.attribute27));
1415:

Line 1414: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(seeker_criteria_rec.attribute27));

1410: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
1411: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(seeker_criteria_rec.attribute26));
1412:
1413: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
1414: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(seeker_criteria_rec.attribute27));
1415:
1416: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
1417: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(seeker_criteria_rec.attribute28));
1418:

Line 1417: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(seeker_criteria_rec.attribute28));

1413: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
1414: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(seeker_criteria_rec.attribute27));
1415:
1416: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
1417: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(seeker_criteria_rec.attribute28));
1418:
1419: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
1420: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(seeker_criteria_rec.attribute29));
1421:

Line 1420: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(seeker_criteria_rec.attribute29));

1416: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
1417: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(seeker_criteria_rec.attribute28));
1418:
1419: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
1420: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(seeker_criteria_rec.attribute29));
1421:
1422: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE30')
1423: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(seeker_criteria_rec.attribute30));
1424: END IF;

Line 1423: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(seeker_criteria_rec.attribute30));

1419: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
1420: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(seeker_criteria_rec.attribute29));
1421:
1422: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE30')
1423: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(seeker_criteria_rec.attribute30));
1424: END IF;
1425: end loop;
1426:
1427: -- execute the cursor

Line 1428: l_row := DBMS_SQL.EXECUTE (c => l_cursor);

1424: END IF;
1425: end loop;
1426:
1427: -- execute the cursor
1428: l_row := DBMS_SQL.EXECUTE (c => l_cursor);
1429:
1430: -- while there is data to fetch
1431: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
1432: LOOP

Line 1431: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0

1427: -- execute the cursor
1428: l_row := DBMS_SQL.EXECUTE (c => l_cursor);
1429:
1430: -- while there is data to fetch
1431: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
1432: LOOP
1433: -- define the posting_content_id column
1434: DBMS_SQL.column_value (
1435: c => l_cursor,

Line 1434: DBMS_SQL.column_value (

1430: -- while there is data to fetch
1431: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
1432: LOOP
1433: -- define the posting_content_id column
1434: DBMS_SQL.column_value (
1435: c => l_cursor,
1436: position => 1,
1437: value => seeker_criteria_match_vac_rec.posting_content_id);
1438:

Line 1440: DBMS_SQL.column_value (

1436: position => 1,
1437: value => seeker_criteria_match_vac_rec.posting_content_id);
1438:
1439: -- define the job_title column
1440: DBMS_SQL.column_value (
1441: c => l_cursor,
1442: position => 2,
1443: value => seeker_criteria_match_vac_rec.job_title);
1444:

Line 1446: DBMS_SQL.column_value (

1442: position => 2,
1443: value => seeker_criteria_match_vac_rec.job_title);
1444:
1445: -- define the name column
1446: DBMS_SQL.column_value (
1447: c => l_cursor,
1448: position => 3,
1449: value => seeker_criteria_match_vac_rec.name);
1450:

Line 1452: DBMS_SQL.column_value (

1448: position => 3,
1449: value => seeker_criteria_match_vac_rec.name);
1450:
1451: -- define the object_id column
1452: DBMS_SQL.column_value (
1453: c => l_cursor,
1454: position => 4,
1455: value => seeker_criteria_match_vac_rec.object_id);
1456:

Line 1506: DBMS_SQL.CLOSE_CURSOR (c => l_cursor);--All done, so clean up!

1502: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_dft_lang);
1503: end;
1504: END LOOP;
1505: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_dft_lang);
1506: DBMS_SQL.CLOSE_CURSOR (c => l_cursor);--All done, so clean up!
1507:
1508: --
1509: hr_utility.set_location('Leaving'||l_proc, 80);
1510: EXCEPTION

Line 1537: matching_values_tbl dbms_sql.varchar2_table;

1533: l_last_value VARCHAR2(240);
1534: l_matching_values VARCHAR2(240);
1535: l_attribute VARCHAR2(14);
1536: seeker_details_rec g_seeker_details_rec_type;
1537: matching_values_tbl dbms_sql.varchar2_table;
1538: l_recruiter_problem_tab g_recruiter_problem_tab_type;
1539: --
1540: l_message_subject VARCHAR2(300);
1541: l_amount NUMBER;

Line 1816: -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many

1812: ' IS NULL) OR lower(icrit.'||l_attribute||') = :'||lower(l_attribute)||') ';
1813: l_counter := l_counter + 1;
1814:
1815: END LOOP;
1816: -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many
1817: -- times in the inner loop, saving parse time compared to NDS.
1818:
1819: l_cursor := DBMS_SQL.OPEN_CURSOR;
1820:

Line 1819: l_cursor := DBMS_SQL.OPEN_CURSOR;

1815: END LOOP;
1816: -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many
1817: -- times in the inner loop, saving parse time compared to NDS.
1818:
1819: l_cursor := DBMS_SQL.OPEN_CURSOR;
1820:
1821: DBMS_SQL.PARSE (c => l_cursor,
1822: statement => l_sql,
1823: language_flag => dbms_sql.native);

Line 1821: DBMS_SQL.PARSE (c => l_cursor,

1817: -- times in the inner loop, saving parse time compared to NDS.
1818:
1819: l_cursor := DBMS_SQL.OPEN_CURSOR;
1820:
1821: DBMS_SQL.PARSE (c => l_cursor,
1822: statement => l_sql,
1823: language_flag => dbms_sql.native);
1824:
1825:

Line 1823: language_flag => dbms_sql.native);

1819: l_cursor := DBMS_SQL.OPEN_CURSOR;
1820:
1821: DBMS_SQL.PARSE (c => l_cursor,
1822: statement => l_sql,
1823: language_flag => dbms_sql.native);
1824:
1825:
1826: l_base_url := fnd_profile.value('IRC_SUITABLE_SEEKERS_URL');
1827: IF l_base_url IS NULL THEN

Line 1864: DBMS_SQL.DEFINE_COLUMN (

1860: hr_utility.set_location('keywords not null', 30);
1861: -- define the posting_content_id column
1862:
1863: -- define the name column
1864: DBMS_SQL.DEFINE_COLUMN (
1865: c => l_cursor,
1866: position => 1,
1867: column => seeker_details_rec.full_name,
1868: column_size => 240);

Line 1871: DBMS_SQL.DEFINE_COLUMN (

1867: column => seeker_details_rec.full_name,
1868: column_size => 240);
1869:
1870: -- define the object_id column
1871: DBMS_SQL.DEFINE_COLUMN (
1872: c => l_cursor,
1873: position => 2,
1874: column => seeker_details_rec.person_id);
1875:

Line 1877: dbms_sql.bind_variable(

1873: position => 2,
1874: column => seeker_details_rec.person_id);
1875:
1876: -- supply binds (bind by name)
1877: dbms_sql.bind_variable(
1878: l_cursor, 'date_registered', l_date_registered);
1879: dbms_sql.bind_variable(
1880: l_cursor, 'internal', l_internal_site);
1881: dbms_sql.bind_variable(

Line 1879: dbms_sql.bind_variable(

1875:
1876: -- supply binds (bind by name)
1877: dbms_sql.bind_variable(
1878: l_cursor, 'date_registered', l_date_registered);
1879: dbms_sql.bind_variable(
1880: l_cursor, 'internal', l_internal_site);
1881: dbms_sql.bind_variable(
1882: l_cursor, 'external', l_external_site);
1883: dbms_sql.bind_variable(

Line 1881: dbms_sql.bind_variable(

1877: dbms_sql.bind_variable(
1878: l_cursor, 'date_registered', l_date_registered);
1879: dbms_sql.bind_variable(
1880: l_cursor, 'internal', l_internal_site);
1881: dbms_sql.bind_variable(
1882: l_cursor, 'external', l_external_site);
1883: dbms_sql.bind_variable(
1884: l_cursor, 'posting_content_id', available_vacancy.primary_posting_id);
1885: dbms_sql.bind_variable(

Line 1883: dbms_sql.bind_variable(

1879: dbms_sql.bind_variable(
1880: l_cursor, 'internal', l_internal_site);
1881: dbms_sql.bind_variable(
1882: l_cursor, 'external', l_external_site);
1883: dbms_sql.bind_variable(
1884: l_cursor, 'posting_content_id', available_vacancy.primary_posting_id);
1885: dbms_sql.bind_variable(
1886: l_cursor, 'vacancy_id', available_vacancy.vacancy_id);
1887: dbms_sql.bind_variable(

Line 1885: dbms_sql.bind_variable(

1881: dbms_sql.bind_variable(
1882: l_cursor, 'external', l_external_site);
1883: dbms_sql.bind_variable(
1884: l_cursor, 'posting_content_id', available_vacancy.primary_posting_id);
1885: dbms_sql.bind_variable(
1886: l_cursor, 'vacancy_id', available_vacancy.vacancy_id);
1887: dbms_sql.bind_variable(
1888: l_cursor, 'employee', available_vacancy.employee);
1889: dbms_sql.bind_variable(

Line 1887: dbms_sql.bind_variable(

1883: dbms_sql.bind_variable(
1884: l_cursor, 'posting_content_id', available_vacancy.primary_posting_id);
1885: dbms_sql.bind_variable(
1886: l_cursor, 'vacancy_id', available_vacancy.vacancy_id);
1887: dbms_sql.bind_variable(
1888: l_cursor, 'employee', available_vacancy.employee);
1889: dbms_sql.bind_variable(
1890: l_cursor, 'contractor', available_vacancy.contractor);
1891: dbms_sql.bind_variable(

Line 1889: dbms_sql.bind_variable(

1885: dbms_sql.bind_variable(
1886: l_cursor, 'vacancy_id', available_vacancy.vacancy_id);
1887: dbms_sql.bind_variable(
1888: l_cursor, 'employee', available_vacancy.employee);
1889: dbms_sql.bind_variable(
1890: l_cursor, 'contractor', available_vacancy.contractor);
1891: dbms_sql.bind_variable(
1892: l_cursor, 'travel_percentage', available_vacancy.travel_percentage);
1893: dbms_sql.bind_variable(

Line 1891: dbms_sql.bind_variable(

1887: dbms_sql.bind_variable(
1888: l_cursor, 'employee', available_vacancy.employee);
1889: dbms_sql.bind_variable(
1890: l_cursor, 'contractor', available_vacancy.contractor);
1891: dbms_sql.bind_variable(
1892: l_cursor, 'travel_percentage', available_vacancy.travel_percentage);
1893: dbms_sql.bind_variable(
1894: l_cursor, 'salary_currency', available_vacancy.salary_currency);
1895: dbms_sql.bind_variable(

Line 1893: dbms_sql.bind_variable(

1889: dbms_sql.bind_variable(
1890: l_cursor, 'contractor', available_vacancy.contractor);
1891: dbms_sql.bind_variable(
1892: l_cursor, 'travel_percentage', available_vacancy.travel_percentage);
1893: dbms_sql.bind_variable(
1894: l_cursor, 'salary_currency', available_vacancy.salary_currency);
1895: dbms_sql.bind_variable(
1896: l_cursor, 'max_salary', available_vacancy.max_salary);
1897: dbms_sql.bind_variable(

Line 1895: dbms_sql.bind_variable(

1891: dbms_sql.bind_variable(
1892: l_cursor, 'travel_percentage', available_vacancy.travel_percentage);
1893: dbms_sql.bind_variable(
1894: l_cursor, 'salary_currency', available_vacancy.salary_currency);
1895: dbms_sql.bind_variable(
1896: l_cursor, 'max_salary', available_vacancy.max_salary);
1897: dbms_sql.bind_variable(
1898: l_cursor, 'business_group_id', available_vacancy.business_group_id);
1899: dbms_sql.bind_variable(

Line 1897: dbms_sql.bind_variable(

1893: dbms_sql.bind_variable(
1894: l_cursor, 'salary_currency', available_vacancy.salary_currency);
1895: dbms_sql.bind_variable(
1896: l_cursor, 'max_salary', available_vacancy.max_salary);
1897: dbms_sql.bind_variable(
1898: l_cursor, 'business_group_id', available_vacancy.business_group_id);
1899: dbms_sql.bind_variable(
1900: l_cursor, 'employment_category', available_vacancy.employment_category);
1901: if available_vacancy.keywords is null then

Line 1899: dbms_sql.bind_variable(

1895: dbms_sql.bind_variable(
1896: l_cursor, 'max_salary', available_vacancy.max_salary);
1897: dbms_sql.bind_variable(
1898: l_cursor, 'business_group_id', available_vacancy.business_group_id);
1899: dbms_sql.bind_variable(
1900: l_cursor, 'employment_category', available_vacancy.employment_category);
1901: if available_vacancy.keywords is null then
1902: dbms_sql.bind_variable(
1903: l_cursor, 'keywords', to_char(null));

Line 1902: dbms_sql.bind_variable(

1898: l_cursor, 'business_group_id', available_vacancy.business_group_id);
1899: dbms_sql.bind_variable(
1900: l_cursor, 'employment_category', available_vacancy.employment_category);
1901: if available_vacancy.keywords is null then
1902: dbms_sql.bind_variable(
1903: l_cursor, 'keywords', to_char(null));
1904: else
1905: dbms_sql.bind_variable(
1906: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(available_vacancy.keywords));

Line 1905: dbms_sql.bind_variable(

1901: if available_vacancy.keywords is null then
1902: dbms_sql.bind_variable(
1903: l_cursor, 'keywords', to_char(null));
1904: else
1905: dbms_sql.bind_variable(
1906: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(available_vacancy.keywords));
1907: end if;
1908: dbms_sql.bind_variable(
1909: l_cursor, 'professional_area', available_vacancy.professional_area);

Line 1908: dbms_sql.bind_variable(

1904: else
1905: dbms_sql.bind_variable(
1906: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(available_vacancy.keywords));
1907: end if;
1908: dbms_sql.bind_variable(
1909: l_cursor, 'professional_area', available_vacancy.professional_area);
1910: dbms_sql.bind_variable(
1911: l_cursor, 'work_at_home', available_vacancy.work_at_home);
1912: dbms_sql.bind_variable(

Line 1910: dbms_sql.bind_variable(

1906: l_cursor, 'keywords', irc_query_parser_pkg.query_parser(available_vacancy.keywords));
1907: end if;
1908: dbms_sql.bind_variable(
1909: l_cursor, 'professional_area', available_vacancy.professional_area);
1910: dbms_sql.bind_variable(
1911: l_cursor, 'work_at_home', available_vacancy.work_at_home);
1912: dbms_sql.bind_variable(
1913: l_cursor, 'location_id', available_vacancy.location_id);
1914: dbms_sql.bind_variable(

Line 1912: dbms_sql.bind_variable(

1908: dbms_sql.bind_variable(
1909: l_cursor, 'professional_area', available_vacancy.professional_area);
1910: dbms_sql.bind_variable(
1911: l_cursor, 'work_at_home', available_vacancy.work_at_home);
1912: dbms_sql.bind_variable(
1913: l_cursor, 'location_id', available_vacancy.location_id);
1914: dbms_sql.bind_variable(
1915: l_cursor, 'derived_locale', available_vacancy.derived_locale);
1916: dbms_sql.bind_variable(

Line 1914: dbms_sql.bind_variable(

1910: dbms_sql.bind_variable(
1911: l_cursor, 'work_at_home', available_vacancy.work_at_home);
1912: dbms_sql.bind_variable(
1913: l_cursor, 'location_id', available_vacancy.location_id);
1914: dbms_sql.bind_variable(
1915: l_cursor, 'derived_locale', available_vacancy.derived_locale);
1916: dbms_sql.bind_variable(
1917: l_cursor, 'country', available_vacancy.country);
1918: if available_vacancy.match_qualification is null then

Line 1916: dbms_sql.bind_variable(

1912: dbms_sql.bind_variable(
1913: l_cursor, 'location_id', available_vacancy.location_id);
1914: dbms_sql.bind_variable(
1915: l_cursor, 'derived_locale', available_vacancy.derived_locale);
1916: dbms_sql.bind_variable(
1917: l_cursor, 'country', available_vacancy.country);
1918: if available_vacancy.match_qualification is null then
1919: dbms_sql.bind_variable(
1920: l_cursor, 'match_qualification', 'N');

Line 1919: dbms_sql.bind_variable(

1915: l_cursor, 'derived_locale', available_vacancy.derived_locale);
1916: dbms_sql.bind_variable(
1917: l_cursor, 'country', available_vacancy.country);
1918: if available_vacancy.match_qualification is null then
1919: dbms_sql.bind_variable(
1920: l_cursor, 'match_qualification', 'N');
1921: else
1922: dbms_sql.bind_variable(
1923: l_cursor, 'match_qualification', available_vacancy.match_qualification);

Line 1922: dbms_sql.bind_variable(

1918: if available_vacancy.match_qualification is null then
1919: dbms_sql.bind_variable(
1920: l_cursor, 'match_qualification', 'N');
1921: else
1922: dbms_sql.bind_variable(
1923: l_cursor, 'match_qualification', available_vacancy.match_qualification);
1924: end if;
1925: dbms_sql.bind_variable(
1926: l_cursor, 'match_competence', available_vacancy.match_competence);

Line 1925: dbms_sql.bind_variable(

1921: else
1922: dbms_sql.bind_variable(
1923: l_cursor, 'match_qualification', available_vacancy.match_qualification);
1924: end if;
1925: dbms_sql.bind_variable(
1926: l_cursor, 'match_competence', available_vacancy.match_competence);
1927: dbms_sql.bind_variable(
1928: l_cursor, 'min_qual_level', available_vacancy.min_qual_level);
1929: dbms_sql.bind_variable(

Line 1927: dbms_sql.bind_variable(

1923: l_cursor, 'match_qualification', available_vacancy.match_qualification);
1924: end if;
1925: dbms_sql.bind_variable(
1926: l_cursor, 'match_competence', available_vacancy.match_competence);
1927: dbms_sql.bind_variable(
1928: l_cursor, 'min_qual_level', available_vacancy.min_qual_level);
1929: dbms_sql.bind_variable(
1930: l_cursor, 'max_qual_level', available_vacancy.max_qual_level);
1931:

Line 1929: dbms_sql.bind_variable(

1925: dbms_sql.bind_variable(
1926: l_cursor, 'match_competence', available_vacancy.match_competence);
1927: dbms_sql.bind_variable(
1928: l_cursor, 'min_qual_level', available_vacancy.min_qual_level);
1929: dbms_sql.bind_variable(
1930: l_cursor, 'max_qual_level', available_vacancy.max_qual_level);
1931:
1932: for ct in 0..matching_values_tbl.count-1 loop
1933: IF (matching_values_tbl(ct)= 'ATTRIBUTE1')

Line 1934: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(available_vacancy.attribute1));

1930: l_cursor, 'max_qual_level', available_vacancy.max_qual_level);
1931:
1932: for ct in 0..matching_values_tbl.count-1 loop
1933: IF (matching_values_tbl(ct)= 'ATTRIBUTE1')
1934: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(available_vacancy.attribute1));
1935:
1936: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1937: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(available_vacancy.attribute2));
1938:

Line 1937: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(available_vacancy.attribute2));

1933: IF (matching_values_tbl(ct)= 'ATTRIBUTE1')
1934: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(available_vacancy.attribute1));
1935:
1936: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1937: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(available_vacancy.attribute2));
1938:
1939: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1940: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(available_vacancy.attribute3));
1941:

Line 1940: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(available_vacancy.attribute3));

1936: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1937: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(available_vacancy.attribute2));
1938:
1939: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1940: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(available_vacancy.attribute3));
1941:
1942: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1943: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(available_vacancy.attribute4));
1944:

Line 1943: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(available_vacancy.attribute4));

1939: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1940: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(available_vacancy.attribute3));
1941:
1942: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1943: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(available_vacancy.attribute4));
1944:
1945: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1946: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(available_vacancy.attribute5));
1947:

Line 1946: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(available_vacancy.attribute5));

1942: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1943: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(available_vacancy.attribute4));
1944:
1945: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1946: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(available_vacancy.attribute5));
1947:
1948: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1949: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(available_vacancy.attribute6));
1950:

Line 1949: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(available_vacancy.attribute6));

1945: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1946: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(available_vacancy.attribute5));
1947:
1948: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1949: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(available_vacancy.attribute6));
1950:
1951: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1952: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(available_vacancy.attribute7));
1953:

Line 1952: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(available_vacancy.attribute7));

1948: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1949: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(available_vacancy.attribute6));
1950:
1951: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1952: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(available_vacancy.attribute7));
1953:
1954: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1955: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(available_vacancy.attribute8));
1956:

Line 1955: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(available_vacancy.attribute8));

1951: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1952: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(available_vacancy.attribute7));
1953:
1954: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1955: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(available_vacancy.attribute8));
1956:
1957: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1958: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(available_vacancy.attribute9));
1959:

Line 1958: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(available_vacancy.attribute9));

1954: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1955: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(available_vacancy.attribute8));
1956:
1957: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1958: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(available_vacancy.attribute9));
1959:
1960: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1961: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(available_vacancy.attribute10));
1962:

Line 1961: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(available_vacancy.attribute10));

1957: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1958: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(available_vacancy.attribute9));
1959:
1960: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1961: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(available_vacancy.attribute10));
1962:
1963: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1964: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(available_vacancy.attribute11));
1965:

Line 1964: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(available_vacancy.attribute11));

1960: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1961: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(available_vacancy.attribute10));
1962:
1963: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1964: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(available_vacancy.attribute11));
1965:
1966: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1967: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(available_vacancy.attribute12));
1968:

Line 1967: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(available_vacancy.attribute12));

1963: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1964: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(available_vacancy.attribute11));
1965:
1966: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1967: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(available_vacancy.attribute12));
1968:
1969: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1970: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(available_vacancy.attribute13));
1971:

Line 1970: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(available_vacancy.attribute13));

1966: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1967: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(available_vacancy.attribute12));
1968:
1969: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1970: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(available_vacancy.attribute13));
1971:
1972: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1973: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(available_vacancy.attribute14));
1974:

Line 1973: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(available_vacancy.attribute14));

1969: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1970: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(available_vacancy.attribute13));
1971:
1972: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1973: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(available_vacancy.attribute14));
1974:
1975: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1976: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(available_vacancy.attribute15));
1977:

Line 1976: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(available_vacancy.attribute15));

1972: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1973: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(available_vacancy.attribute14));
1974:
1975: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1976: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(available_vacancy.attribute15));
1977:
1978: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1979: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(available_vacancy.attribute16));
1980:

Line 1979: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(available_vacancy.attribute16));

1975: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1976: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(available_vacancy.attribute15));
1977:
1978: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1979: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(available_vacancy.attribute16));
1980:
1981: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1982: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(available_vacancy.attribute17));
1983:

Line 1982: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(available_vacancy.attribute17));

1978: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1979: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(available_vacancy.attribute16));
1980:
1981: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1982: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(available_vacancy.attribute17));
1983:
1984: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1985: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(available_vacancy.attribute18));
1986:

Line 1985: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(available_vacancy.attribute18));

1981: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1982: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(available_vacancy.attribute17));
1983:
1984: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1985: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(available_vacancy.attribute18));
1986:
1987: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1988: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(available_vacancy.attribute19));
1989:

Line 1988: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(available_vacancy.attribute19));

1984: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1985: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(available_vacancy.attribute18));
1986:
1987: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1988: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(available_vacancy.attribute19));
1989:
1990: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1991: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(available_vacancy.attribute20));
1992:

Line 1991: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(available_vacancy.attribute20));

1987: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1988: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(available_vacancy.attribute19));
1989:
1990: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1991: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(available_vacancy.attribute20));
1992:
1993: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1994: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(available_vacancy.attribute21));
1995:

Line 1994: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(available_vacancy.attribute21));

1990: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1991: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(available_vacancy.attribute20));
1992:
1993: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1994: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(available_vacancy.attribute21));
1995:
1996: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1997: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(available_vacancy.attribute22));
1998:

Line 1997: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(available_vacancy.attribute22));

1993: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1994: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(available_vacancy.attribute21));
1995:
1996: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1997: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(available_vacancy.attribute22));
1998:
1999: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
2000: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(available_vacancy.attribute23));
2001:

Line 2000: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(available_vacancy.attribute23));

1996: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1997: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(available_vacancy.attribute22));
1998:
1999: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
2000: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(available_vacancy.attribute23));
2001:
2002: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
2003: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(available_vacancy.attribute24));
2004:

Line 2003: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(available_vacancy.attribute24));

1999: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
2000: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(available_vacancy.attribute23));
2001:
2002: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
2003: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(available_vacancy.attribute24));
2004:
2005: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
2006: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(available_vacancy.attribute25));
2007:

Line 2006: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(available_vacancy.attribute25));

2002: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
2003: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(available_vacancy.attribute24));
2004:
2005: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
2006: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(available_vacancy.attribute25));
2007:
2008: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
2009: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(available_vacancy.attribute26));
2010:

Line 2009: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(available_vacancy.attribute26));

2005: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
2006: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(available_vacancy.attribute25));
2007:
2008: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
2009: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(available_vacancy.attribute26));
2010:
2011: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
2012: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(available_vacancy.attribute27));
2013:

Line 2012: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(available_vacancy.attribute27));

2008: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
2009: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(available_vacancy.attribute26));
2010:
2011: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
2012: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(available_vacancy.attribute27));
2013:
2014: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
2015: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(available_vacancy.attribute28));
2016:

Line 2015: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(available_vacancy.attribute28));

2011: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
2012: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(available_vacancy.attribute27));
2013:
2014: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
2015: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(available_vacancy.attribute28));
2016:
2017: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
2018: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(available_vacancy.attribute29));
2019:

Line 2018: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(available_vacancy.attribute29));

2014: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
2015: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(available_vacancy.attribute28));
2016:
2017: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
2018: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(available_vacancy.attribute29));
2019:
2020: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE30')
2021: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(available_vacancy.attribute30));
2022: END IF;

Line 2021: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(available_vacancy.attribute30));

2017: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
2018: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(available_vacancy.attribute29));
2019:
2020: ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE30')
2021: THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(available_vacancy.attribute30));
2022: END IF;
2023: end loop;
2024:
2025: -- execute the cursor

Line 2026: l_row := DBMS_SQL.EXECUTE (c => l_cursor);

2022: END IF;
2023: end loop;
2024:
2025: -- execute the cursor
2026: l_row := DBMS_SQL.EXECUTE (c => l_cursor);
2027:
2028: -- while there is data to fetch
2029: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
2030: LOOP

Line 2029: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0

2025: -- execute the cursor
2026: l_row := DBMS_SQL.EXECUTE (c => l_cursor);
2027:
2028: -- while there is data to fetch
2029: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
2030: LOOP
2031: DBMS_SQL.column_value (
2032: c => l_cursor,
2033: position => 1,

Line 2031: DBMS_SQL.column_value (

2027:
2028: -- while there is data to fetch
2029: WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
2030: LOOP
2031: DBMS_SQL.column_value (
2032: c => l_cursor,
2033: position => 1,
2034: value => seeker_details_rec.full_name);
2035:

Line 2036: DBMS_SQL.column_value (

2032: c => l_cursor,
2033: position => 1,
2034: value => seeker_details_rec.full_name);
2035:
2036: DBMS_SQL.column_value (
2037: c => l_cursor,
2038: position => 2,
2039: value => seeker_details_rec.person_id);
2040: