[Home] [Help]
113: TYPE status_code_tbl IS TABLE OF pa_project_statuses.project_status_code%TYPE
114: INDEX BY BINARY_INTEGER;
115: l_status_code_tbl status_code_tbl;
116:
117: TYPE project_id_tbl IS TABLE OF pa_projects_all.project_id%TYPE
118: INDEX BY BINARY_INTEGER;
119: l_project_id_tbl project_id_tbl;
120:
121: TYPE project_name_tbl IS TABLE OF pa_projects_all.name%TYPE
117: TYPE project_id_tbl IS TABLE OF pa_projects_all.project_id%TYPE
118: INDEX BY BINARY_INTEGER;
119: l_project_id_tbl project_id_tbl;
120:
121: TYPE project_name_tbl IS TABLE OF pa_projects_all.name%TYPE
122: INDEX BY BINARY_INTEGER;
123: l_project_name_tbl project_name_tbl;
124:
125: TYPE project_number_tbl IS TABLE OF pa_projects_all.segment1%TYPE
121: TYPE project_name_tbl IS TABLE OF pa_projects_all.name%TYPE
122: INDEX BY BINARY_INTEGER;
123: l_project_name_tbl project_name_tbl;
124:
125: TYPE project_number_tbl IS TABLE OF pa_projects_all.segment1%TYPE
126: INDEX BY BINARY_INTEGER;
127: l_project_number_tbl project_number_tbl;
128:
129: --define the variable type
430: , loc.region
431: , loc.city
432: FROM pa_project_assignments asgn
433: , pa_org_hierarchy_denorm ohd
434: , pa_projects_all proj
435: , pa_locations loc
436: , fnd_territories_vl ter
437: , pa_project_statuses ps
438: , pa_advertised_open_req_v aor
481: , loc.region
482: , loc.city
483: FROM pa_project_assignments asgn
484: , pa_org_hierarchy_denorm ohd
485: , pa_projects_all proj
486: , pa_locations loc
487: , fnd_territories_vl ter
488: , pa_project_statuses ps
489: , pa_advertised_open_req_v aor
532: , loc.region
533: , loc.city
534: FROM pa_project_assignments asgn
535: , pa_org_hierarchy_denorm ohd
536: , pa_projects_all proj
537: , pa_locations loc
538: , fnd_territories_vl ter
539: , pa_project_statuses ps
540: , pa_advertised_open_req_v aor
582: , loc.region
583: , loc.city
584: FROM pa_project_assignments asgn
585: , pa_org_hierarchy_denorm ohd
586: , pa_projects_all proj
587: , pa_locations loc
588: , fnd_territories_vl ter
589: , pa_project_statuses ps
590: , pa_advertised_open_req_v aor
627: , loc.region
628: , loc.city
629: FROM pa_project_assignments asgn
630: , pa_org_hierarchy_denorm ohd
631: , pa_projects_all proj
632: , pa_locations loc
633: , fnd_territories_vl ter
634: , pa_project_statuses ps
635: , pa_advertised_open_req_v aor
1436: asgn.JOB_LEVEL_MATCH_WEIGHTING,
1437: asgn.ENABLE_AUTO_CAND_NOM_FLAG,
1438: proj.enable_automated_search
1439: FROM pa_project_assignments asgn,
1440: pa_projects_all proj,
1441: pa_project_statuses ps
1442: WHERE proj.project_id = p_project_id
1443: AND proj.project_id = asgn.project_id
1444: AND trunc(asgn.start_date) BETWEEN decode(p_req_start_date_days, NULL, asgn.start_date, trunc(SYSDATE)) AND decode(p_req_start_date_days, NULL, asgn.start_date, trunc(SYSDATE) + p_req_start_date_days)
1463: asgn.JOB_LEVEL_MATCH_WEIGHTING,
1464: asgn.ENABLE_AUTO_CAND_NOM_FLAG,
1465: proj.enable_automated_search
1466: FROM pa_project_assignments asgn,
1467: pa_projects_all proj,
1468: pa_project_statuses ps
1469: WHERE proj.segment1 BETWEEN nvl(p_project_number_from, proj.segment1) AND nvl(p_project_number_to, proj.segment1)
1470: -- AND trunc(proj.start_date) BETWEEN decode(p_proj_start_date_days, NULL, proj.start_date, trunc(SYSDATE)) AND decode(p_proj_start_date_days, NULL, proj.start_date, trunc(SYSDATE) + p_proj_start_date_days)
1471: -- Changed for Bug 5299668
1501: l_nomination_comments fnd_new_messages.message_text%TYPE;
1502: l_system_nom_candidate_text fnd_new_messages.message_text%TYPE;
1503: l_candidates_nom_in_cycle NUMBER := 0;
1504: l_req_enable_auto_cand_nom pa_project_assignments.ENABLE_AUTO_CAND_NOM_FLAG%TYPE;
1505: l_proj_enable_automated_search pa_projects_all.enable_automated_search%TYPE;
1506: l_status_code pa_project_statuses.project_status_code%TYPE;
1507: l_cand_system_status_code pa_project_statuses.project_system_status_code%TYPE;
1508:
1509: BEGIN