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.PA_SEARCH_PVT dependencies on PA_SEARCH_RESULTS_TEMP

Line 147: FROM PA_SEARCH_RESULTS_TEMP

143: --Bug Ref #6144255
144: CURSOR cur_res_temp_search
145: IS
146: SELECT RESOURCE_ID
147: FROM PA_SEARCH_RESULTS_TEMP
148: WHERE MANDATORY_COMPETENCE_COUNT = MANDATORY_COMPETENCE_MATCH;
149:
150: TYPE id_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
151:

Line 723: INSERT INTO PA_SEARCH_RESULTS_TEMP ( person_id

719:
720: END IF; --p_competence_criteria.COUNT >0
721:
722: FORALL k IN l_person_id_tbl.FIRST .. l_person_id_tbl.LAST
723: INSERT INTO PA_SEARCH_RESULTS_TEMP ( person_id
724: , resource_id
725: , resource_organization_id
726: , resource_name
727: , resource_type

Line 785: -- UPDATE pa_search_results_temp

781: -- update the search results table with the match.
782: --
783: -- IF p_search_mode = 'RESOURCE' THEN
784: -- FORALL i IN l_person_id_tbl.FIRST .. l_person_id_tbl.LAST
785: -- UPDATE pa_search_results_temp
786: -- SET (mandatory_competence_match,optional_competence_match)=
787: -- (SELECT /*+ ORDERED */nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0), nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0)
788: -- FROM pa_competence_criteria_temp pct,
789: -- per_competence_elements pce,

Line 814: UPDATE pa_search_results_temp

810: -- not null.
811: --
812:
813: FORALL i IN l_person_id_tbl.FIRST .. l_person_id_tbl.LAST
814: UPDATE pa_search_results_temp
815: SET (mandatory_competence_match,optional_competence_match)=
816: (SELECT /*+ leading(PCE PCT ) use_nl(PCT) */nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0), nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0) --Bug 12701655
817: FROM pa_competence_criteria_temp pct,
818: per_competence_elements pce,

Line 867: UPDATE pa_search_results_temp

863: CLOSE cur_competence_id_search;
864:
865: IF (l_comp_id_num >0) THEN
866: FOR i IN l_person_id_tbl.FIRST .. l_person_id_tbl.LAST LOOP
867: UPDATE pa_search_results_temp
868: SET (mandatory_competence_match,optional_competence_match)=
869: (SELECT /*+ ORDERED */
870: (nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0) + nvl(mandatory_competence_match, 0)),
871: (nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0) + nvl(optional_competence_match, 0))

Line 937: -- in the pa_search_results_temp table. The availability is

933: -- hr_utility.trace('l_assignment_days is : ' || to_char(l_assignment_days));
934: IF l_assignment_days > 0 THEN
935:
936: -- this sql statement calculates availability for each resource
937: -- in the pa_search_results_temp table. The availability is
938: -- calculated on a daily basis. The resource avail hours is
939: -- divided by the assignment hours for each day, summed up and
940: -- then divided by the total number of assignment days
941: -- with non-zero hours. We need to divide by l_assignment_days

Line 1050: FROM PA_SEARCH_RESULTS_TEMP srt,

1046: -- Caculation from forecast items until new table solution
1047: --
1048: SELECT /*+ ORDERED USE_NL(srt res) INDEX (res, PA_FORECAST_ITEMS_N3)*/srt.person_id, count(res.resource_id)
1049: BULK COLLECT INTO l_person_id_tbl, l_days_in_period_tbl
1050: FROM PA_SEARCH_RESULTS_TEMP srt,
1051: PA_FORECAST_ITEMS res
1052: WHERE srt.resource_id = res.resource_id
1053: AND res.forecast_item_type = 'U'
1054: AND res.delete_flag = 'N'

Line 1081: FROM PA_SEARCH_RESULTS_TEMP srt,

1077: nvl(res.confirmed_qty, 0)), 0) / res.capacity_quantity) /
1078: l_days_in_period_tbl(i) * 100)
1079: INTO l_pot_availability_tbl(i),
1080: l_availability_tbl(i)
1081: FROM PA_SEARCH_RESULTS_TEMP srt,
1082: PA_FORECAST_ITEMS res
1083: WHERE srt.person_id = l_person_id_tbl(i)
1084: AND srt.resource_id = res.resource_id
1085: AND res.forecast_item_type = 'U'

Line 1129: FROM PA_SEARCH_RESULTS_TEMP srt,

1125: potential.percent
1126: BULK COLLECT INTO l_person_id_tbl,
1127: l_availability_tbl,
1128: l_pot_availability_tbl
1129: FROM PA_SEARCH_RESULTS_TEMP srt,
1130: PA_RES_AVAILABILITY confirmed,
1131: PA_RES_AVAILABILITY potential
1132: WHERE srt.resource_id = confirmed.resource_id
1133: AND confirmed.record_type = 'C'

Line 1164: UPDATE pa_search_results_temp

1160: -- bug#8833203
1161: -- Bug 12335394 Start
1162: IF p_search_mode = 'ADHOC' THEN
1163: FORALL k IN l_person_id_tbl.FIRST .. l_person_id_tbl.LAST
1164: UPDATE pa_search_results_temp
1165: SET availability = l_availability_tbl(k),
1166: potential_availability = l_pot_availability_tbl(k),
1167: --resource_organization_name = pa_expenditures_utils.GetOrgTlName(resource_organization_id), 4778073
1168: resource_organization_name = pa_resource_utils.get_organization_name(resource_organization_id),

Line 1195: UPDATE pa_search_results_temp

1191: p_search_criteria.min_availability));
1192: ELSE
1193: -- Bug 12335394 end.
1194: FORALL k IN l_person_id_tbl_tmp.FIRST .. l_person_id_tbl_tmp.LAST
1195: UPDATE pa_search_results_temp
1196: SET availability = l_availability_tbl(k),
1197: potential_availability = l_pot_availability_tbl(k),
1198: --resource_organization_name = pa_expenditures_utils.GetOrgTlName(resource_organization_id), 4778073
1199: resource_organization_name = pa_resource_utils.get_organization_name(resource_organization_id),

Line 1243: UPDATE pa_search_results_temp

1239: -- which means they were not updated in the above update statement.
1240:
1241: IF p_search_criteria.min_availability = 0 THEN
1242:
1243: UPDATE pa_search_results_temp
1244: SET availability = 0,
1245: potential_availability = 0,
1246: --resource_organization_name = pa_expenditures_utils.GetOrgTlName(resource_organization_id), 4778073
1247: resource_organization_name = pa_resource_utils.get_organization_name(resource_organization_id),

Line 1296: INSERT INTO PA_SEARCH_RESULTS_TEMP ( assignment_id

1292: l_city_tbl;
1293:
1294: IF l_assignment_id_tbl.COUNT > 0 THEN
1295: FORALL k IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST
1296: INSERT INTO PA_SEARCH_RESULTS_TEMP ( assignment_id
1297: , assignment_name
1298: , assignment_number
1299: , assignment_start_date
1300: , assignment_end_date

Line 1339: DELETE FROM pa_search_results_temp

1335: --to be shown due to the status controls.
1336: --**Calling this bulk delete is slightly faster than calling the
1337: --**function directly in the insert statement above.
1338: FORALL i IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST
1339: DELETE FROM pa_search_results_temp
1340: WHERE show_req_in_search(l_assignment_id_tbl(i), l_status_code_tbl(i)) = 'N'
1341: AND assignment_id = l_assignment_id_tbl(i);
1342:
1343: --if resource source id is not null then do the competence count/match

Line 1349: UPDATE pa_search_results_temp

1345: IF p_search_criteria.resource_source_id IS NOT NULL THEN
1346: --update the search results table with the mandatory/optional competence count
1347: --for all requirements.
1348: FORALL i IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST
1349: UPDATE pa_search_results_temp
1350: SET (mandatory_competence_count, optional_competence_count) =
1351: (SELECT nvl(sum(decode(mandatory,'Y',1,0)),0), nvl(sum(decode(mandatory,'N',1,0)),0)
1352: FROM per_competence_elements pce
1353: WHERE pce.object_name = 'OPEN_ASSIGNMENT'

Line 1360: UPDATE pa_search_results_temp

1356:
1357: --update the search results table with the mandatory/optional competence match
1358: --for all requirments based on the resource competencies.
1359: FORALL i IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST
1360: UPDATE pa_search_results_temp
1361: SET (mandatory_competence_match,optional_competence_match)=
1362: (SELECT nvl(sum(decode(pce.mandatory,'Y',1,0)),0), nvl(sum(decode(pce.mandatory,'N',1,0)),0)
1363: FROM per_competence_elements pce,
1364: per_competence_elements pce2,

Line 1383: UPDATE pa_search_results_temp

1379: --and the candidate in req flag (if resource source id is not null)
1380: --for only requirements that passed the mandatory competence match if restrict to
1381: --resource's competencies is true, or for all requirements if restrict to resource's
1382: --competencies is false.
1383: UPDATE pa_search_results_temp
1384: SET candidate_in_req_flag = decode(p_search_criteria.resource_source_id, NULL, NULL,PA_CANDIDATE_UTILS.Check_Resource_Is_Candidate(PA_RESOURCE_UTILS.Get_Resource_Id(p_search_criteria.resource_source_id), assignment_id))
1385: WHERE ((mandatory_competence_count=mandatory_competence_match AND p_search_criteria.restrict_res_comp = FND_API.G_TRUE)
1386: OR p_search_criteria.restrict_res_comp = FND_API.G_FALSE);
1387:

Line 1712: DELETE FROM pa_search_results_temp;

1708: IF p_debug_mode = 'Y' THEN
1709: fnd_file.put_line(FND_FILE.LOG,'about to clear the global temp tables');
1710: END IF;
1711:
1712: DELETE FROM pa_search_results_temp;
1713: DELETE FROM pa_competence_criteria_temp;
1714:
1715: IF p_debug_mode = 'Y' THEN
1716: fnd_file.put_line(FND_FILE.LOG,'done clearing the global temp tables');

Line 1734: FROM pa_search_results_temp

1730: END IF;
1731:
1732: --bulk collect the resources to be nominated
1733: SELECT resource_id BULK COLLECT INTO l_resource_id_tbl
1734: FROM pa_search_results_temp
1735: WHERE candidate_score >= l_min_candidate_score;
1736:
1737: IF p_debug_mode = 'Y' THEN
1738: fnd_file.put_line(FND_FILE.LOG,'done with bulk collect');

Line 1789: FROM pa_search_results_temp

1785: AND ps.status_type = 'CANDIDATE'
1786: AND ps.project_system_status_code = l_cand_system_status_code
1787: AND resource_id NOT IN
1788: (SELECT resource_id
1789: FROM pa_search_results_temp
1790: WHERE candidate_score >= l_min_candidate_score)
1791: ;
1792:
1793: IF p_debug_mode = 'Y' THEN