1 PACKAGE BODY PA_SEARCH_PVT AS
2 --$Header: PARISPVB.pls 120.22 2011/07/07 17:38:05 svmohamm ship $
3 --
4
5 PROCEDURE Run_Search (
6 p_search_mode IN VARCHAR2
7 , p_search_criteria IN PA_SEARCH_GLOB.Search_Criteria_Rec_Type
8 , p_competence_criteria IN PA_SEARCH_GLOB.Competence_Criteria_Tbl_Type
9 , p_commit IN VARCHAR2
10 , p_validate_only IN VARCHAR2
11 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
12 ) IS
13
14 l_assignment_days NUMBER := 0;
15
16 --declare local variables
17 TYPE number_tbl IS TABLE OF NUMBER
18 INDEX BY BINARY_INTEGER;
19
20 l_competence_id_tbl number_tbl;
21 l_rating_level_tbl number_tbl;
22
23 TYPE mandatory_flag IS TABLE OF pa_competence_criteria_temp.mandatory_flag%TYPE
24 INDEX BY BINARY_INTEGER;
25 l_mandatory_flag_tbl mandatory_flag;
26
27 TYPE competence_name IS TABLE OF per_competences.name%TYPE
28 INDEX BY BINARY_INTEGER;
29 l_competence_name_tbl competence_name;
30
31 l_mandatory_competence_count NUMBER:= 0;
32 l_mandatory_competence_match NUMBER:= 0;
33 l_optional_competence_count NUMBER:= 0;
34 l_optional_competence_match NUMBER:= 0;
35 l_availability_tbl number_tbl;
36 l_pot_availability_tbl number_tbl;
37 l_person_avail NUMBER:= 0;
38 l_person_pot_avail NUMBER:= 0;
39 l_days_in_period_tbl number_tbl;
40 l_resource_organization_name hr_organization_units.name%TYPE;
41 l_person_type fnd_lookup_values.lookup_code%TYPE; -- Bug 6526674
42 TYPE resource_organization_name_tbl IS TABLE OF hr_organization_units.name%TYPE
43 INDEX BY BINARY_INTEGER;
44
45 TYPE person_id_tbl IS TABLE OF pa_resources_denorm.person_id%TYPE
46 INDEX BY BINARY_INTEGER;
47 l_person_id_tbl person_id_tbl;
48
49 TYPE resource_id_tbl IS TABLE OF pa_resources_denorm.resource_id%TYPE
50 INDEX BY BINARY_INTEGER;
51 l_resource_id_tbl resource_id_tbl;
52
53 TYPE resource_name_tbl IS TABLE OF pa_resources_denorm.resource_name%TYPE
54 INDEX BY BINARY_INTEGER;
55 l_resource_name_tbl resource_name_tbl;
56
57 TYPE resource_type_tbl IS TABLE OF pa_resources_denorm.resource_type%TYPE
58 INDEX BY BINARY_INTEGER;
59 l_resource_type_tbl resource_type_tbl;
60
61 TYPE resource_organization_id_tbl IS TABLE OF pa_resources_denorm.resource_organization_id%TYPE
62 INDEX BY BINARY_INTEGER;
63 l_resource_organization_id_tbl resource_organization_id_tbl;
64
65 TYPE country_code_tbl IS TABLE OF fnd_territories_vl.territory_code%TYPE
66 INDEX BY BINARY_INTEGER;
67 l_country_code_tbl country_code_tbl;
68
69 TYPE country_tbl IS TABLE OF fnd_territories_vl.territory_short_name%TYPE
70 INDEX BY BINARY_INTEGER;
71 l_country_tbl country_tbl;
72
73 TYPE region_tbl IS TABLE OF pa_locations.region%TYPE
74 INDEX BY BINARY_INTEGER;
75 l_region_tbl region_tbl;
76
77 TYPE city_tbl IS TABLE OF pa_locations.city%TYPE
78 INDEX BY BINARY_INTEGER;
79 l_city_tbl city_tbl;
80
81 TYPE email_tbl IS TABLE OF per_all_people_f.email_address%TYPE
82 INDEX BY BINARY_INTEGER;
83 l_email_tbl email_tbl;
84
85 TYPE manager_tbl IS TABLE OF pa_resources_denorm.manager_name%TYPE
86 INDEX BY BINARY_INTEGER;
87 l_manager_tbl manager_tbl;
88
89 TYPE resource_job_level_tbl IS TABLE OF pa_resources_denorm.resource_job_level%TYPE
90 INDEX BY BINARY_INTEGER;
91 l_resource_job_level_tbl resource_job_level_tbl;
92
93 TYPE assignment_id_tbl IS TABLE OF pa_project_assignments.assignment_id%TYPE
94 INDEX BY BINARY_INTEGER;
95 l_assignment_id_tbl assignment_id_tbl;
96
97 TYPE assignment_name_tbl IS TABLE OF pa_project_assignments.assignment_name%TYPE
98 INDEX BY BINARY_INTEGER;
99 l_assignment_name_tbl assignment_name_tbl;
100
101 TYPE assignment_number_tbl IS TABLE OF pa_project_assignments.assignment_number%TYPE
102 INDEX BY BINARY_INTEGER;
103 l_assignment_number_tbl assignment_number_tbl;
104
105 TYPE assignment_start_date_tbl IS TABLE OF pa_project_assignments.start_date%TYPE
106 INDEX BY BINARY_INTEGER;
107 l_assignment_start_date_tbl assignment_start_date_tbl;
108
109 TYPE assignment_end_date_tbl IS TABLE OF pa_project_assignments.end_date%TYPE
110 INDEX BY BINARY_INTEGER;
111 l_assignment_end_date_tbl assignment_end_date_tbl;
112
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
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
130 TYPE search_results IS REF CURSOR;
131 --declare the cursor or table variable
132 l_search_results search_results;
133 --
134
135 --Bug Ref 9212362
136 CURSOR cur_competence_id_search
137 IS
138 SELECT COUNT(1)
139 FROM PA_COMPETENCE_CRITERIA_TEMP
140 WHERE COMPETENCE_ID = -1;
141
142 l_comp_id_num NUMBER := 0;
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
152 TYPE cur_avail_type IS REF CURSOR;
153
154 cur_avail cur_avail_type ;
155 l_person_id_tbl_tmp id_tab;
156 l_pot_availability_tbl_tmp id_tab;
157 l_availability_tbl_tmp id_tab;
158 l_resource_tab id_tab;
159 index_1 NUMBER := 0;
160 len NUMBER := 0;
161 str_resource_ids VARCHAR2(32767);
162 stmt_select VARCHAR2(2000);
163 stmt_from VARCHAR2(2000);
164 stmt_where VARCHAR2(2000);
165 stmt_groupby VARCHAR2(2000);
166
167 --Bug Ref #7457064
168 P_DEBUG_MODE VARCHAR2(1);
169 l_res_sql VARCHAR2(32767); --Added for bug 4624826
170 -- Bug 7457064
171 TYPE hash_map_tbl_typ IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
172 l_temp VARCHAR2(15);
173 l_index NUMBER;
174 l_resource_id_tmp_tbl PA_PLSQL_DATATYPES.NumTabTyp;
175 l_pot_avail_tmp_tbl PA_PLSQL_DATATYPES.NumTabTyp;
176 l_avail_tmp_tbl PA_PLSQL_DATATYPES.NumTabTyp;
177 l_pot_avail_tmp_hash_tbl hash_map_tbl_typ;
178 l_avail_tmp_hash_tbl hash_map_tbl_typ;
179
180 BEGIN
181 --hr_utility.trace_on(NULL, 'RMPA');
182 --hr_utility.trace('BEGINNING');
183
184 P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
185 IF (P_DEBUG_MODE ='Y') THEN
186 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_SEARCH_PVT.Run_Search.begin',
187 x_Msg => 'in PA_SEARCH_PVT.Run_Search',
188 x_Log_Level => 6);
189 END IF;
190
191 -- Bug 6526674 changes start here
192
193 if p_search_criteria.person_type is null then l_person_type := 'ALL';
194 else
195 select decode(lookup_code,'ALL','ALL','EMP','Y','CWK','N') into l_person_type
196 from fnd_lookup_values
197 where lookup_type in ('PERSON_TYPE','PA_ANY_ALL') and
198 meaning = p_search_criteria.person_type and language = userenv('LANG')
199 and lookup_code in ('EMP','CWK','ALL');
200 end if;
201
202 -- Bug 6526674 changes end here
203
204 -- if this is a Resource Search then open the cursor variable with the
205 -- appropriate SELECT statement. The first part is executed if the
206 -- country is part of the search criteria, and the second part
207 -- is executed if the country is not part of the criteria. This will allow
208 -- the county part of the index to be used if it is part of the criteria.
209
210 -- CASE 1: country is not null
211 -- added the nvl around the country code join below due to the
212 -- functionality change in bug 1925620. This will cause
213 -- only the first 2 columns in pa_resources_denorm_n1 to be used
214 -- (country_code column in the index cannot be used b/c of the nvl)
215 --RD.resource_job_level BETWEEN p_search_criteria.min_job_level
216 --AND p_search_criteria.max_job_level
217
218 IF p_search_mode = 'RESOURCE' or p_search_mode = 'ADHOC' THEN
219 -- AND p_search_criteria.territory_code IS NOT NULL THEN
220 -- Start of addition for bug 4624826
221 l_res_sql :=
222 -- 'SELECT /*+ INDEX (RD, PA_RESOURCES_DENORM_N6) */ RD.person_id, RD.resource_id, RD.resource_name, RD.resource_type ' || -- adding hint for 6911907
223 'SELECT /*+ LEADING(RD) INDEX(RD PA_RESOURCES_DENORM_N6) USE_NL(RD PDF OHD PPF) */ DISTINCT RD.person_id, RD.resource_id, RD.resource_name, RD.resource_type ' || -- adding another hint for 6911907, --added distinct bug 9206026
224 -- 'SELECT RD.person_id, RD.resource_id, RD.resource_name, RD.resource_type ' ||
225 ', RD.resource_organization_id, RD.resource_country_code,RD.resource_country, RD.resource_region '||
226 ', RD.resource_city, RD.resource_job_level, RD.manager_name,PPF.email_address ';
227
228 l_res_sql := l_res_sql || ' FROM PA_RESOURCES_DENORM RD, PER_ALL_PEOPLE_F ppf,PA_ORG_HIERARCHY_DENORM ohd, PER_DEPLOYMENT_FACTORS pdf ';
229
230 if p_search_criteria.min_job_level IS NOT NULL then
231 l_res_sql := l_res_sql || ' WHERE RD.resource_job_level >= :1 ';
232 else
233 l_res_sql := l_res_sql || ' WHERE :1 IS NULL ';
234 end if;
235
236 if p_search_criteria.max_job_level IS NOT NULL then
237 l_res_sql := l_res_sql || ' AND RD.resource_job_level <= :2 ';
238 else
239 l_res_sql := l_res_sql || ' AND :2 IS NULL ';
240 end if;
241
242 if p_search_criteria.territory_code IS NOT NULL then
243 l_res_sql := l_res_sql ||
244 ' AND (nvl(RD.resource_country_code, :3) = :4) ';
245 else
246 l_res_sql := l_res_sql ||
247 ' AND (:3 is null or :4 is null)';
248 end if;
249
250 if p_search_criteria.region IS NOT NULL then
251 l_res_sql := l_res_sql ||
252 ' AND (nvl(RD.resource_region, :5) = :6) ';
253 else
254 l_res_sql := l_res_sql ||
255 ' AND (:5 is null or :6 is null) ';
256 end if;
257
258 if p_search_criteria.city IS NOT NULL then
259 l_res_sql := l_res_sql ||
260 ' AND (nvl(RD.resource_city, :7) = :8) ';
261 else
262 l_res_sql := l_res_sql ||
263 ' AND (:7 is null or :8 is null)';
264 end if;
265
266 l_res_sql := l_res_sql ||
267 ' AND RD.employee_flag = decode(:9,:10,:11,RD.employee_flag)' ||
268 ' AND RD.resource_organization_id = OHD.child_organization_id' ||
269 ' AND OHD.PARENT_ORGANIZATION_ID = :12'||
270 ' AND OHD.org_hierarchy_version_id = :13'||
271 ' AND OHD.pa_org_use_type = :14'||
272 ' AND RD.SCHEDULABLE_FLAG = :15'||
273 ' AND RD.resource_effective_start_date <= :16'||
274 ' AND RD.resource_effective_end_date >= :17'||
275 ' AND RD.person_id = ppf.person_id'||
276 ' AND :18 BETWEEN ppf.effective_start_date AND ppf.effective_end_date'||
277 ' AND RD.person_id = pdf.person_id(+)' ||
278 ' AND (ppf.current_employee_flag = ' || '''Y''' || 'OR ppf.current_npw_flag = ' || '''Y''' || ')';
279 --Bug#9739340
280
281 if p_search_criteria.work_current_loc = 'Y' then
282 l_res_sql := l_res_sql ||
283 ' AND (PDF.only_current_location = :19'||
284 ' or PDF.only_current_location = :20) ';
285 elsif p_search_criteria.work_current_loc is null then
286 l_res_sql := l_res_sql || ' AND (:19 is null or :20 is null) ';
287 elsif p_search_criteria.work_current_loc = 'N' then
288 l_res_sql := l_res_sql || ' AND :19 = :20';
289 else
290 l_res_sql := l_res_sql || ' AND :19 <> :20';
291 end if;
292
293 if p_search_criteria.work_all_loc = 'Y' then
294 l_res_sql := l_res_sql ||
295 ' AND (PDF.work_any_location = :21'||
296 ' or PDF.work_any_location = :22)';
297 elsif p_search_criteria.work_all_loc is null then
298 l_res_sql := l_res_sql || ' AND (:21 is null or :22 is null)';
299 elsif p_search_criteria.work_all_loc = 'N' then
300 l_res_sql := l_res_sql || ' AND :21 = :22';
301 else
302 l_res_sql := l_res_sql || ' AND :21 <> :22';
303 end if;
304
305 if p_search_criteria.travel_domestically = 'Y' then
306 l_res_sql := l_res_sql ||
307 ' AND (PDF.travel_required = :23'||
308 ' or PDF.travel_required = :24)';
309 elsif p_search_criteria.travel_domestically is null then
310 l_res_sql := l_res_sql || ' AND (:23 is null or :24 is null) ';
311 elsif p_search_criteria.travel_domestically = 'N' then
312 l_res_sql := l_res_sql || ' AND :23 = :24';
313 else l_res_sql := l_res_sql || ' AND :23 <> :24';
314 end if;
315
316 if p_search_criteria.travel_internationally = 'Y' then
317 l_res_sql := l_res_sql ||
318 ' AND (PDF.visit_internationally = :25'||
319 ' or PDF.visit_internationally = :26)';
320 elsif p_search_criteria.travel_internationally is null then
321 l_res_sql := l_res_sql || ' AND (:25 is null and :26 is null) ';
322 elsif p_search_criteria.travel_internationally = 'N' then
323 l_res_sql := l_res_sql || ' AND :25 = :26';
324 else l_res_sql := l_res_sql || ' AND :25 <> :26';
325 end if;
326
327 if nvl(l_person_type,'S') <> 'ALL' then
328 if l_person_type = 'N' then
329 l_res_sql := l_res_sql ||' AND ppf.current_npw_flag = ' || '''Y''' ;
330 else
331 l_res_sql := l_res_sql ||' AND ppf.current_employee_flag = ''' || l_person_type || '''';
332 end if;
333 end if;
334
335 OPEN l_search_results FOR l_res_sql using p_search_criteria.min_job_level,p_search_criteria.max_job_level,
336 p_search_criteria.territory_code,p_search_criteria.territory_code,
337 p_search_criteria.region,p_search_criteria.region,
338 p_search_criteria.city,p_search_criteria.city,
339 p_search_criteria.employees_only,'Y','Y',
340 p_search_criteria.organization_id,p_search_criteria.org_hierarchy_version_id,
341 'EXPENDITURES','Y',
342 p_search_criteria.start_date,p_search_criteria.start_date,
343 p_search_criteria.start_date,
344 p_search_criteria.work_current_loc,p_search_criteria.work_current_loc,
345 p_search_criteria.work_all_loc,p_search_criteria.work_all_loc,
346 p_search_criteria.travel_domestically,p_search_criteria.travel_domestically,
347 p_search_criteria.travel_internationally,p_search_criteria.travel_internationally;
348
349 -- End of addition for bug 4624826
350 /* Commented for bug 4624826
351 OPEN l_search_results FOR
352 SELECT RD.person_id
353 , RD.resource_id
354 , RD.resource_name
355 , RD.resource_type
356 , RD.resource_organization_id
357 , RD.resource_country_code
358 , RD.resource_country
359 , RD.resource_region
360 , RD.resource_city
361 , RD.resource_job_level
362 , RD.manager_name
363 , PPF.email_address
364 FROM PA_RESOURCES_DENORM RD
365 , PA_ORG_HIERARCHY_DENORM ohd
366 , PER_ALL_PEOPLE_F ppf
367 , PER_DEPLOYMENT_FACTORS pdf
368 WHERE ((p_search_criteria.min_job_level IS NOT NULL AND
369 RD.resource_job_level >= p_search_criteria.min_job_level)
370 OR
371 p_search_criteria.min_job_level IS NULL)
372 AND ((p_search_criteria.max_job_level IS NOT NULL AND
373 RD.resource_job_level <= p_search_criteria.max_job_level)
374 OR
375 p_search_criteria.max_job_level IS NULL)
376 AND ((p_search_criteria.territory_code IS NOT NULL AND
377 (nvl(RD.resource_country_code,
378 p_search_criteria.territory_code) =
379 p_search_criteria.territory_code)) OR
380 (p_search_criteria.territory_code IS NULL))
381 AND ((p_search_criteria.region IS NOT NULL AND
382 (nvl(RD.resource_region, p_search_criteria.region) =
383 p_search_criteria.region)) OR
384 (p_search_criteria.region IS NULL))
385 AND ((p_search_criteria.city IS NOT NULL AND
386 (nvl(RD.resource_city, p_search_criteria.city) =
387 p_search_criteria.city)) OR
388 (p_search_criteria.city IS NULL))
389 AND RD.employee_flag =
390 decode(p_search_criteria.employees_only,'Y','Y',RD.employee_flag)
391 AND RD.resource_organization_id = OHD.child_organization_id
392 AND OHD.PARENT_ORGANIZATION_ID = p_search_criteria.organization_id
393 AND OHD.org_hierarchy_version_id =
394 p_search_criteria.org_hierarchy_version_id
395 AND OHD.pa_org_use_type = 'EXPENDITURES'
396 AND RD.SCHEDULABLE_FLAG = 'Y'
397 AND RD.resource_effective_start_date <= p_search_criteria.start_date
398 AND NVL(RD.resource_effective_end_date, p_search_criteria.start_date) >= p_search_criteria.start_date
399 AND RD.person_id = ppf.person_id
400 AND p_search_criteria.start_date BETWEEN ppf.effective_start_date
401 AND ppf.effective_end_date
402 AND RD.person_id = pdf.person_id(+)
403 AND ((p_search_criteria.work_current_loc = 'Y' AND
404 PDF.only_current_location = 'Y') OR
405 (nvl(p_search_criteria.work_current_loc, 'N') = 'N'))
406 AND ((p_search_criteria.work_all_loc = 'Y' AND
407 PDF.work_any_location = 'Y') OR
408 (nvl(p_search_criteria.work_all_loc, 'N') = 'N'))
409 AND ((p_search_criteria.travel_domestically = 'Y' AND
410 PDF.travel_required = 'Y') OR
411 (nvl(p_search_criteria.travel_domestically, 'N') = 'N'))
412 AND ((p_search_criteria.travel_internationally = 'Y' AND
413 PDF.visit_internationally = 'Y') OR
414 (nvl(p_search_criteria.travel_internationally, 'N') = 'N')); */
415
416 --if this is a Requirement Search then open the cursor variable with the
417 --appropriate SELECT statement.
418
419 -- CASE 1: assignment_id is passed in
420 --use this select statement if assignment id is part of the criteria. Do not need
421 --separate select statements based on whether or not country or role is part of the criteria in
422 --this case because the assignment_id unique index will be used to access the assignment
423 --record.
424 ELSIF p_search_mode = 'REQUIREMENT' AND (p_search_criteria.assignment_id IS NOT NULL AND p_search_criteria.assignment_id <> FND_API.G_MISS_NUM) THEN
425
426 OPEN l_search_results FOR
427 SELECT asgn.assignment_id
428 , asgn.assignment_name
429 , asgn.assignment_number
430 , asgn.start_date
431 , asgn.end_date
432 , asgn.status_code
433 , proj.project_id
434 , proj.name project_name
435 , proj.segment1 project_number
436 , loc.country_code
437 , ter.territory_short_name
438 , loc.region
439 , loc.city
440 FROM pa_project_assignments asgn
441 , pa_org_hierarchy_denorm ohd
442 , pa_projects_all proj
443 , pa_locations loc
444 , fnd_territories_vl ter
445 , pa_project_statuses ps
446 , pa_advertised_open_req_v aor
447 WHERE asgn.assignment_id = p_search_criteria.assignment_id
448 AND asgn.min_resource_job_level <= p_search_criteria.min_job_level
449 AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
450 AND asgn.project_role_id = nvl(p_search_criteria.role_id, asgn.project_role_id)
451 AND asgn.start_date >= p_search_criteria.start_date
452 AND asgn.start_date <= p_search_criteria.end_date
453 AND nvl(asgn.staffing_priority_code, -999) = nvl(p_search_criteria.staffing_priority_code, nvl(asgn.staffing_priority_code, -999))
454 AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
455 AND asgn.assignment_type='OPEN_ASSIGNMENT'
456 AND asgn.status_code = ps.project_status_code (+)
457 AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
458 AND asgn.project_id = proj.project_id
459 AND proj.carrying_out_organization_id = OHD.child_organization_id
460 AND OHD.parent_organization_id = p_search_criteria.organization_id
461 AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
462 AND OHD.pa_org_use_type = 'PROJECTS'
463 AND asgn.location_id = loc.location_id(+)
464 AND nvl(loc.country_code, -999) = nvl(p_search_criteria.territory_code, nvl(loc.country_code, -999))
465 AND loc.country_code = ter.territory_code(+)
466 AND asgn.assignment_id = aor.assignment_id;
467
468 -- CASE 2: no assignment_id,
469 -- but role, staffing_priority_code, country are passed in
470 -- use this select statement for a requirement search if assignment id is not part
471 -- of the criteria and the country, staffing priority and role are part of the criteria.
472 ELSIF p_search_mode = 'REQUIREMENT' AND (p_search_criteria.assignment_id IS NULL OR p_search_criteria.assignment_id = FND_API.G_MISS_NUM)
473 AND (p_search_criteria.territory_code IS NOT NULL AND p_search_criteria.territory_code <> FND_API.G_MISS_CHAR)
474 AND (p_search_criteria.role_id IS NOT NULL AND p_search_criteria.role_id <> FND_API.G_MISS_NUM)
475 AND (p_search_criteria.staffing_priority_code IS NOT NULL AND p_search_criteria.staffing_priority_code <> FND_API.G_MISS_CHAR) THEN
476
477 OPEN l_search_results FOR
478 SELECT asgn.assignment_id
479 , asgn.assignment_name
480 , asgn.assignment_number
481 , asgn.start_date
482 , asgn.end_date
483 , asgn.status_code
484 , proj.project_id
485 , proj.name project_name
486 , proj.segment1 project_number
487 , loc.country_code
488 , ter.territory_short_name
489 , loc.region
490 , loc.city
491 FROM pa_project_assignments asgn
492 , pa_org_hierarchy_denorm ohd
493 , pa_projects_all proj
494 , pa_locations loc
495 , fnd_territories_vl ter
496 , pa_project_statuses ps
497 , pa_advertised_open_req_v aor
498 WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
499 AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
500 AND asgn.project_role_id = p_search_criteria.role_id
501 AND asgn.start_date >= p_search_criteria.start_date
502 AND asgn.start_date <= p_search_criteria.end_date
503 AND asgn.staffing_priority_code = p_search_criteria.staffing_priority_code
504 AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
505 AND asgn.assignment_type='OPEN_ASSIGNMENT'
506 AND asgn.status_code = ps.project_status_code (+)
507 AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
508 AND asgn.project_id = proj.project_id
509 AND proj.carrying_out_organization_id = OHD.child_organization_id
510 AND OHD.parent_organization_id = p_search_criteria.organization_id
511 AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
512 AND OHD.pa_org_use_type = 'PROJECTS'
513 AND asgn.location_id = loc.location_id
514 AND loc.country_code = p_search_criteria.territory_code
515 AND loc.country_code = ter.territory_code
516 AND asgn.assignment_id = aor.assignment_id;
517
518 -- CASE 3: no assignment_id and no country
519 -- but role, staffing_priority_code are passed in
520 -- use this select statement for a requirement search if assignment id
521 -- and country are not part of the criteria, but role and staffing priority
522 -- are part of the criteria.
523 ELSIF p_search_mode = 'REQUIREMENT' AND (p_search_criteria.assignment_id IS NULL OR p_search_criteria.assignment_id = FND_API.G_MISS_NUM)
524 AND (p_search_criteria.territory_code IS NULL OR p_search_criteria.territory_code = FND_API.G_MISS_CHAR)
525 AND (p_search_criteria.role_id IS NOT NULL AND p_search_criteria.role_id <> FND_API.G_MISS_NUM)
526 AND (p_search_criteria.staffing_priority_code IS NOT NULL AND p_search_criteria.staffing_priority_code <> FND_API.G_MISS_CHAR) THEN
527
528 OPEN l_search_results FOR
529 SELECT asgn.assignment_id
530 , asgn.assignment_name
531 , asgn.assignment_number
532 , asgn.start_date
533 , asgn.end_date
534 , asgn.status_code
535 , proj.project_id
536 , proj.name project_name
537 , proj.segment1 project_number
538 , loc.country_code
539 , ter.territory_short_name
540 , loc.region
541 , loc.city
542 FROM pa_project_assignments asgn
543 , pa_org_hierarchy_denorm ohd
544 , pa_projects_all proj
545 , pa_locations loc
546 , fnd_territories_vl ter
547 , pa_project_statuses ps
548 , pa_advertised_open_req_v aor
549 WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
550 AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
551 AND asgn.project_role_id = p_search_criteria.role_id
552 AND asgn.start_date >= p_search_criteria.start_date
553 AND asgn.start_date <= p_search_criteria.end_date
554 AND asgn.staffing_priority_code = p_search_criteria.staffing_priority_code
555 AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
556 AND asgn.assignment_type='OPEN_ASSIGNMENT'
557 AND asgn.status_code = ps.project_status_code (+)
558 AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
559 AND asgn.project_id = proj.project_id
560 AND proj.carrying_out_organization_id = OHD.child_organization_id
561 AND OHD.parent_organization_id = p_search_criteria.organization_id
562 AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
563 AND OHD.pa_org_use_type = 'PROJECTS'
564 AND asgn.location_id = loc.location_id(+)
565 AND loc.country_code = ter.territory_code(+)
566 AND asgn.assignment_id = aor.assignment_id;
567
568
569 -- CASE 4: no assignment_id and no staffing_priority_code
570 -- but role is passed in
571 -- use this select statement for a requirement search if assignment id
572 -- and staffing priority are not part of the criteria,
573 -- but role is part of the criteria.
574 ELSIF p_search_mode = 'REQUIREMENT' AND (p_search_criteria.assignment_id IS NULL OR p_search_criteria.assignment_id = FND_API.G_MISS_NUM)
575 AND (p_search_criteria.staffing_priority_code IS NULL OR p_search_criteria.staffing_priority_code = FND_API.G_MISS_CHAR)
576 AND (p_search_criteria.role_id IS NOT NULL AND p_search_criteria.role_id <> FND_API.G_MISS_NUM) THEN
577
578 OPEN l_search_results FOR
579 SELECT asgn.assignment_id
580 , asgn.assignment_name
581 , asgn.assignment_number
582 , asgn.start_date
583 , asgn.end_date
584 , asgn.status_code
585 , proj.project_id
586 , proj.name project_name
587 , proj.segment1 project_number
588 , loc.country_code
589 , ter.territory_short_name
590 , loc.region
591 , loc.city
592 FROM pa_project_assignments asgn
593 , pa_org_hierarchy_denorm ohd
594 , pa_projects_all proj
595 , pa_locations loc
596 , fnd_territories_vl ter
597 , pa_project_statuses ps
598 , pa_advertised_open_req_v aor
599 WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
600 AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
601 AND asgn.project_role_id = p_search_criteria.role_id
602 AND asgn.start_date >= p_search_criteria.start_date
603 AND asgn.start_date <= p_search_criteria.end_date
604 AND asgn.assignment_type='OPEN_ASSIGNMENT'
605 AND asgn.status_code = ps.project_status_code (+)
606 AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
607 AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
608 AND asgn.project_id = proj.project_id
609 AND proj.carrying_out_organization_id = OHD.child_organization_id
610 AND OHD.parent_organization_id = p_search_criteria.organization_id
611 AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
612 AND OHD.pa_org_use_type = 'PROJECTS'
613 AND asgn.location_id = loc.location_id(+)
614 AND nvl(loc.country_code, -999) = nvl(p_search_criteria.territory_code, nvl(loc.country_code, -999))
615 AND loc.country_code = ter.territory_code(+)
616 AND asgn.assignment_id = aor.assignment_id;
617
618 -- CASE 5: no assignment_id, no role
619 -- use this select statement for a requirement search if assignment_id and
620 -- role are not part of the criteria.
621 ELSIF p_search_mode = 'REQUIREMENT' AND (p_search_criteria.assignment_id IS NULL OR p_search_criteria.assignment_id = FND_API.G_MISS_NUM) AND (p_search_criteria.role_id IS NULL OR p_search_criteria.role_id = FND_API.G_MISS_NUM) THEN
622
623 OPEN l_search_results FOR
624 SELECT asgn.assignment_id
625 , asgn.assignment_name
626 , asgn.assignment_number
627 , asgn.start_date
628 , asgn.end_date
629 , asgn.status_code
630 , proj.project_id
631 , proj.name project_name
632 , proj.segment1 project_number
633 , loc.country_code
634 , ter.territory_short_name
635 , loc.region
636 , loc.city
637 FROM pa_project_assignments asgn
638 , pa_org_hierarchy_denorm ohd
639 , pa_projects_all proj
640 , pa_locations loc
641 , fnd_territories_vl ter
642 , pa_project_statuses ps
643 , pa_advertised_open_req_v aor
644 WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
645 AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
646 AND asgn.start_date >= p_search_criteria.start_date
647 AND asgn.start_date <= p_search_criteria.end_date
648 AND nvl(asgn.staffing_priority_code, -999) = nvl(p_search_criteria.staffing_priority_code, nvl(asgn.staffing_priority_code, -999))
649 AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
650 AND asgn.assignment_type='OPEN_ASSIGNMENT'
651 AND asgn.status_code = ps.project_status_code (+)
652 AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
653 AND asgn.project_id = proj.project_id
654 AND proj.carrying_out_organization_id = OHD.child_organization_id
655 AND OHD.parent_organization_id = p_search_criteria.organization_id
656 AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
657 AND OHD.pa_org_use_type = 'PROJECTS'
658 AND asgn.location_id = loc.location_id(+)
659 AND nvl(loc.country_code, -999) = nvl(p_search_criteria.territory_code, nvl(loc.country_code, -999))
660 AND loc.country_code = ter.territory_code(+)
661 AND asgn.assignment_id = aor.assignment_id;
662
663 END IF;
664
665 P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
666 IF (P_DEBUG_MODE ='Y') THEN
667 PA_DEBUG.WRITE_LOG(
668 x_Module =>'pa.plsql.PA_SEARCH_PVT.Run_Search.fetch_values',
669 x_Msg =>'if this is a Requirement Search then fetch the values from the cursor',
670 x_Log_Level => 6);
671 end if;
672
673 -- if this is a Resource Search then fetch the values from the cursor
674 -- into the appropriate local variables.
675 IF p_search_mode = 'RESOURCE' or p_search_mode = 'ADHOC' THEN
676
677 FETCH l_search_results BULK COLLECT INTO l_person_id_tbl,
678 l_resource_id_tbl,
679 l_resource_name_tbl,
680 l_resource_type_tbl,
681 l_resource_organization_id_tbl,
682 l_country_code_tbl,
683 l_country_tbl,
684 l_region_tbl,
685 l_city_tbl,
686 l_resource_job_level_tbl,
687 l_manager_tbl,
688 l_email_tbl;
689
690
691 -- if there are any competencies then
692 -- put the competencies into plsql tables of scalars and count
693 -- the mandatory/optional competencies.
694 -- Need to copy into tables of scalars because can't use tables of
695 -- records in bulk binds in 8i.
696 IF l_person_id_tbl.COUNT > 0 THEN
697
698 IF p_competence_criteria.COUNT > 0 THEN
699
700 FOR i IN p_competence_criteria.FIRST .. p_competence_criteria.LAST
701 LOOP
702
703 l_competence_id_tbl(i) :=
704 p_competence_criteria(i).competence_id;
705
706 l_competence_name_tbl(i) :=
707 p_competence_criteria(i).competence_name;
708 l_rating_level_tbl(i) := p_competence_criteria(i).rating_level;
709 l_mandatory_flag_tbl(i) :=
710 p_competence_criteria(i).mandatory_flag;
711
712 IF p_competence_criteria(i).mandatory_flag = 'Y' THEN
713 l_mandatory_competence_count := l_mandatory_competence_count+1;
714 ELSE
715 l_optional_competence_count := l_optional_competence_count+1;
716 END IF;
717
718 END LOOP;
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
728 , resource_job_level
729 , country
730 , country_code
731 , region
732 , city
733 , optional_competence_match
734 , optional_competence_count
735 , mandatory_competence_match
736 , mandatory_competence_count
737 , availability
738 , potential_availability
739 , resource_email
740 , resource_manager
741 )
742 VALUES ( l_person_id_tbl(k)
743 , l_resource_id_tbl(k)
744 , l_resource_organization_id_tbl(k)
745 , l_resource_name_tbl(k)
746 , l_resource_type_tbl(k)
747 , l_resource_job_level_tbl(k)
748 , l_country_tbl(k)
749 , l_country_code_tbl(k)
750 , l_region_tbl(k)
751 , l_city_tbl(k)
752 , 0
753 , l_optional_competence_count
754 , 0
755 , l_mandatory_competence_count
756 , null
757 , null
758 , l_email_tbl(k)
759 , l_manager_tbl(k)
760 );
761
762 --if there is any competence criteria then do the competence match
763 IF p_competence_criteria.COUNT > 0 THEN
764
765 -- insert competence critieria into a global temporary table.
766 -- that way I can join to the temp table to do the competence match
767 -- instead of doing the match once per competence.
768 FORALL i IN l_competence_id_tbl.FIRST .. l_competence_id_tbl.LAST
769 INSERT INTO pa_competence_criteria_temp(competence_id,
770 competence_name,
771 rating_level,
772 mandatory_flag)
773 VALUES(l_competence_id_tbl(i),
774 l_competence_name_tbl(i),
775 l_rating_level_tbl(i),
776 l_mandatory_flag_tbl(i));
777
778 -- do competence match.
779 -- ordered hint is required because global temporary table does not
780 -- have any statistics. This was OK'd by Ahmed Alomari.
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,
790 -- per_rating_levels prl
791 -- WHERE pce.competence_id = pct.competence_id
792 -- AND pce.person_id = l_person_id_tbl(i)
793 -- AND pce.proficiency_level_id =
794 -- prl.rating_level_id(+)
795 -- AND decode(prl.step_value, NULL,
796 -- decode(pct.rating_level, NULL, -999,
797 -- PA_SEARCH_GLOB.get_min_prof_level(pct.competence_id)),
798 -- prl.step_value) >=
799 -- nvl(pct.rating_level, nvl(prl.step_value , -999))
800 -- )
801 -- WHERE person_id = l_person_id_tbl(i);
802
803 IF p_search_mode = 'ADHOC' OR p_search_mode = 'RESOURCE' THEN
804
805 -- hr_utility.trace('IN Competence Calculation.');
806 --
807 -- First do the calculation for competences, then do it
808 -- for the categories. The one for competences is same
809 -- as the above, with a check for competence id to be
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,
819 per_rating_levels prl
820 WHERE (pct.competence_id <> -1 AND
821 pce.competence_id = pct.competence_id)
822 AND pce.person_id = l_person_id_tbl(i)
823 AND pce.proficiency_level_id =
824 prl.rating_level_id(+)
825 /*code added for bug 2932045*/
826 AND PA_SEARCH_GLOB.g_search_criteria.start_date between
827 NVL(pce.EFFECTIVE_DATE_FROM,PA_SEARCH_GLOB.g_search_criteria.start_date)
828 and nvl(pce.EFFECTIVE_DATE_TO,PA_SEARCH_GLOB.g_search_criteria.start_date)
829 /*code addition ends for bug 2932045*/
830 AND decode(prl.step_value, NULL,
831 decode(pct.rating_level, NULL, -999,
832 PA_SEARCH_GLOB.get_min_prof_level(pct.competence_id)),
833 prl.step_value) >=
834 nvl(pct.rating_level, nvl(prl.step_value , -999))
835 )
836 WHERE person_id = l_person_id_tbl(i);
837 --
838 -- Next, do the calculation for competence category - +1
839 -- if one exists. Add it to the competence match that exists.
840 --
841 -- hr_utility.trace('IN Competence Category Calculation.');
842 --
843 /*
844 IF p_competence_criteria.COUNT > 0 THEN
845
846 FOR i IN p_competence_criteria.FIRST ..
847 p_competence_criteria.LAST LOOP
848
849 hr_utility.trace('Comp ID is : ' ||
850 to_char(p_competence_criteria(i).competence_id));
851 hr_utility.trace('Comp Name is : ' ||
852 p_competence_criteria(i).competence_name);
853 hr_utility.trace('Comp Name is : ' ||
854 p_competence_criteria(i).mandatory_flag);
855 END LOOP;
856 END IF;
857 */
858
859 --BUG#9212362
860
861 OPEN cur_competence_id_search;
862 FETCH cur_competence_id_search INTO l_comp_id_num;
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))
872 FROM pa_competence_criteria_temp pct
873 WHERE pct.competence_id = -1
874 AND EXISTS ( SELECT 'Y'
875 FROM per_competence_elements pce,
876 per_competences pc,
877 per_rating_levels prl
878 WHERE pce.person_id = l_person_id_tbl(i)
879 AND pce.competence_id = pc.competence_id
880 /*code added for bug 2932045*/
881
882 AND PA_SEARCH_GLOB.g_search_criteria.start_date between
883 NVL(pce.EFFECTIVE_DATE_FROM,PA_SEARCH_GLOB.g_search_criteria.start_date)
884 and nvl(pce.EFFECTIVE_DATE_TO,PA_SEARCH_GLOB.g_search_criteria.start_date)
885 /*code addition end for bug 2932045*/
886 AND pc.name like
887 (replace(pct.competence_name, '...') || '%')
888 AND pce.proficiency_level_id =
889 prl.rating_level_id(+)
890 AND decode(prl.step_value, NULL,
891 decode(pct.rating_level, NULL, -999,
892 PA_SEARCH_GLOB.get_min_prof_level(pce.competence_id)),
893 prl.step_value) >=
894 nvl(pct.rating_level, nvl(prl.step_value , -999))
895 )
896 )
897 WHERE person_id = l_person_id_tbl(i);
898 END LOOP;
899 END IF; --IF (l_comp_id_num >0) THEN
900
901 END IF;
902
903 END IF; --p_competence_criteria.COUNT > 0
904
905 -- Availability calculation is different for adhoc vs non-adhoc
906 -- This part is for requirement-based resource search.
907 -- do availability calculation for resources who pass the
908 -- mandatory competence match
909 -- or for everyone if there are no mandatory competencies. (0=0)
910 -- ordered hint is required because global temporary table does not
911 -- have any statistics. This was OK'd by Ahmed Alomari.
912 -- bulk collect the resources who passed the mandatory competence
913 -- check and their availability into plsql tables.
914
915 -- need to know the number of assignment days because the
916 -- pa_forecast_items table does not store unassigned time
917 -- resource records with 0 hours.
918 -- This will be used in the following sql statement...
919 --
920 -- hr_utility.trace('SEARCH MODE IS' || p_search_mode);
921 -- hr_utility.trace('assignment_id is : ' || to_char(p_search_criteria.assignment_id));
922
923 IF p_search_mode = 'RESOURCE' THEN
924 SELECT count(*) INTO l_assignment_days
925 FROM pa_forecast_items
926 WHERE assignment_id = p_search_criteria.assignment_id
927 AND delete_flag = 'N'
928 AND error_flag IN ('Y','N')
929 AND item_date >= trunc(SYSDATE)
930 AND item_quantity > 0;
931
932 -- hr_utility.trace('after getting l_assignment_days');
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
942 -- because days for which a resource has 0 hours will not be in
943 -- the forecast items table, but we need to take those days into
944 -- account for the availability calculation as the assignment
945 -- does have hours on that day - basically we need to average
946 -- in 0 for those days - this is taken care of by dividing by
947 -- l_assignment_days as opposed to dividing by the number of
948 -- days which make the join below.
949 --
950 --Added for bug # 6144255
951 DELETE FROM PA_FI_ASSIG_TEMP;
952 INSERT INTO PA_FI_ASSIG_TEMP
953 (
954 SELECT asgmt.item_date ,
955 asgmt.item_quantity
956 FROM pa_forecast_items asgmt
957 WHERE asgmt.assignment_id = p_search_criteria.assignment_id
958 AND asgmt.delete_flag = 'N'
959 AND asgmt.error_flag IN ('Y','N')
960 AND asgmt.item_date >= trunc(SYSDATE)
961 AND asgmt.item_quantity > 0
962 );
963 l_resource_tab.delete;
964 IF ( l_person_id_tbl.COUNT > 0 ) THEN
965 FOR k IN l_person_id_tbl.FIRST..l_person_id_tbl.LAST LOOP
966 l_availability_tbl(k) := 0;
967 l_pot_availability_tbl(k) := 0;
968 END LOOP;
969 END IF;
970 -- Bug Ref # 7457064, Removed multiple joins of PA_FORECAST_ITEMS
971 -- Using the temp table instead.
972 --Bug 6911907: Changed the index to PA_FORECAST_ITEMS_N5 from PA_FORECAST_ITEMS_N3
973 --Bug 6911907: Changed the index back to PA_FORECAST_ITEMS_N3
974 -- Added filter condition
975 stmt_select := 'SELECT /*+ INDEX (res, PA_FORECAST_ITEMS_N3) */ ' ||
976 ' res.person_id, ' ||
977 ' TRUNC(SUM(DECODE(SIGN( ' ||
978 ' (nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0) - ' ||
979 ' nvl(res.provisional_qty,0)) / asgmt.item_quantity-1),1, 1, ' ||
980 ' greatest((nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0) - ' || /*Greatest function is added for bug 2782464*/
981 ' nvl(res.provisional_qty,0)),0) / ' ||
982 ' asgmt.item_quantity))/ :1 ' || ' * 100), ' ||
983 ' TRUNC(SUM(DECODE(SIGN( ' ||
984 ' (nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0))/asgmt.item_quantity-1),1, 1,' ||
985 ' greatest((nvl(res.capacity_quantity, 0) - ' ||
986 ' nvl(res.confirmed_qty, 0)), 0)/ asgmt.item_quantity))/ :1 ' || '* 100) ';
987
988 stmt_from := ' FROM ' ||
989 ' PA_FORECAST_ITEMS res, ' ||
990 ' PA_FI_ASSIG_TEMP asgmt ';
991
992 stmt_where := ' WHERE ' ||
993 ' res.forecast_item_type = ''U'' ' ||
994 ' AND res.delete_flag = ''N'' ' ||
995 ' AND res.item_date = asgmt.item_date ' ||
996 ' AND res.item_date BETWEEN :2 AND :3'|| -- added for Bug 6911907
997 ' AND res.resource_id IN (';
998
999 stmt_groupby := ' ) GROUP BY res.person_id ';
1000
1001 OPEN cur_res_temp_search;
1002 str_resource_ids := ' ';
1003 index_1 := 0;
1004 LOOP
1005 FETCH cur_res_temp_search BULK COLLECT INTO l_resource_tab limit 1000;
1006 IF ( l_resource_tab.COUNT > 0 ) THEN
1007 FOR i IN l_resource_tab.FIRST..l_resource_tab.LAST LOOP
1008 str_resource_ids := str_resource_ids || l_resource_tab(i) || ',';
1009 END LOOP;
1010 len := LENGTH(str_resource_ids) -1 ;
1011 str_resource_ids := SUBSTR(str_resource_ids,1,len);
1012 ELSE
1013 EXIT;
1014 END IF;
1015 OPEN cur_avail FOR (stmt_select || stmt_from || stmt_where || str_resource_ids || stmt_groupby)
1016 -- USING l_assignment_days, l_assignment_days; -- added below for Bug 6911907
1017 USING l_assignment_days, l_assignment_days,p_search_criteria.start_date,p_search_criteria.end_date;
1018
1019 FETCH cur_avail BULK COLLECT INTO l_person_id_tbl_tmp, l_pot_availability_tbl_tmp,l_availability_tbl_tmp;
1020 CLOSE cur_avail;
1021
1022 IF l_person_id_tbl_tmp.COUNT >0 THEN
1023
1024 FOR j IN l_person_id_tbl_tmp.FIRST..l_person_id_tbl_tmp.LAST LOOP
1025 index_1 := index_1 + 1 ;
1026 l_person_id_tbl(index_1) := l_person_id_tbl_tmp(j);
1027 l_pot_availability_tbl(index_1) := l_pot_availability_tbl_tmp(j);
1028 l_availability_tbl(index_1) := l_availability_tbl_tmp(j);
1029
1030 END LOOP;
1031 END IF;
1032
1033 str_resource_ids := ' ';
1034 END LOOP;
1035 CLOSE cur_res_temp_search;
1036 --End for bug 7457064
1037
1038 END IF; --l_assignment_days > 0
1039
1040 -- hr_utility.trace('availabi count is ' || to_char(l_availability_tbl.COUNT));
1041 ELSIF p_search_mode = 'ADHOC' THEN
1042 -- hr_utility.trace('IN HERE ');
1043 -- hr_utility.trace('start date is ' || to_char(p_search_criteria.start_date, 'DD-MON-YYYY'));
1044 -- hr_utility.trace('end_date is ' || to_char(p_search_criteria.end_date, 'DD-MON-YYYY'));
1045 --
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'
1055 AND res.error_flag IN ('Y','N')
1056 AND res.item_date BETWEEN p_search_criteria.start_date AND
1057 p_search_criteria.end_date
1058 AND srt.mandatory_competence_count =
1059 srt.mandatory_competence_match /* Added for bug 4624826 */
1060 AND res.capacity_quantity > 0
1061 GROUP BY srt.person_id ;
1062 /*ORDER BY srt.person_id;commented for bug 4624826 */
1063 --
1064
1065 IF l_person_id_tbl.count > 0 THEN
1066 FOR i in l_person_id_tbl.first .. l_person_id_tbl.last LOOP
1067 --
1068
1069 IF l_days_in_period_tbl(i) > 0 THEN
1070 --
1071 BEGIN
1072 SELECT
1073 TRUNC(SUM(greatest((nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0) -
1074 nvl(res.provisional_qty,0)), 0) / res.capacity_quantity) /
1075 l_days_in_period_tbl(i) * 100),
1076 TRUNC(SUM(greatest((nvl(res.capacity_quantity, 0) -
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'
1086 AND res.delete_flag = 'N'
1087 AND res.capacity_quantity > 0
1088 AND res.item_date BETWEEN p_search_criteria.start_date AND
1089 p_search_criteria.end_date
1090 AND srt.mandatory_competence_count =
1091 srt.mandatory_competence_match
1092 GROUP BY srt.person_id;
1093
1094 EXCEPTION WHEN NO_DATA_FOUND THEN
1095 l_pot_availability_tbl(i) := 0;
1096 l_availability_tbl(i) := 0;
1097 END;
1098 --
1099 ELSE
1100 l_pot_availability_tbl(i) := 0;
1101 l_availability_tbl(i) := 0;
1102 END IF;
1103 --
1104 END LOOP;
1105 END IF;
1106 --
1107 /*
1108 hr_utility.trace('Person table is '|| to_char(l_person_id_tbl.count));
1109 hr_utility.trace('Days table is '|| to_char(l_days_in_period_tbl.count));
1110 hr_utility.trace('Avail table is '|| to_char(l_availability_tbl.count));
1111 hr_utility.trace('Pot Avail table is '|| to_char(l_pot_availability_tbl.count));
1112 IF l_availability_tbl.count > 0 THEN
1113 FOR j in l_availability_tbl.first .. l_availability_tbl.last LOOP
1114 hr_utility.trace('Avail is : ' || to_char(l_availability_tbl(j)));
1115 hr_utility.trace('Days is : ' || to_char(l_days_in_period_tbl(j)));
1116 hr_utility.trace('Person ID is : ' || to_char(l_person_id_tbl(j)));
1117 END LOOP;
1118 END IF;
1119 */
1120 -- Do availability calculation based on new table
1121 --
1122 /*
1123 SELECT srt.person_id,
1124 confirmed.percent,
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'
1134 AND p_search_criteria.start_date BETWEEN confirmed.start_date
1135 AND confirmed.end_date
1136 AND srt.resource_id = potential.resource_id
1137 AND potential.record_type = 'B'
1138 AND p_search_criteria.start_date BETWEEN potential.start_date
1139 AND potential.end_date
1140 AND srt.mandatory_competence_count =
1141 srt.mandatory_competence_match
1142 GROUP BY srt.person_id;
1143 */
1144
1145 END IF;
1146
1147 -- if any resources passed the mandatory competence
1148 -- check(exist in l_person_id_tbl)
1149 -- then update the search results table with the availability,
1150 -- resource org name and candidate score
1151 -- if availability > min availabilty.
1152 -- hr_utility.trace('RANJANA 1 ');
1153 -- hr_utility.trace('l_person_id_tbl count is ' || to_char(l_person_id_tbl.COUNT));
1154 -- hr_utility.trace('l_pot_availability_tbl count is ' || to_char(l_pot_availability_tbl.COUNT));
1155 -- hr_utility.trace('l_availability_tbl count is ' || to_char(l_availability_tbl.COUNT));
1156
1157 IF l_person_id_tbl.COUNT > 0 THEN
1158 IF ( l_availability_tbl.COUNT > 0 AND l_pot_availability_tbl.COUNT > 0 ) THEN /*Added for bug 7628377*/
1159 -- hr_utility.trace('l_person_id is ' || to_char(l_person_id_tbl(1)));
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),
1169 candidate_score = PA_CANDIDATE_UTILS.Get_Candidate_Score(
1170 resource_id,
1171 l_person_id_tbl(k),
1172 p_search_criteria.assignment_id,
1173 p_search_criteria.project_id,
1174 null,
1175 optional_competence_match+mandatory_competence_match,
1176 optional_competence_count+mandatory_competence_count,
1177 l_availability_tbl(k),
1178 resource_job_level,
1179 p_search_criteria.min_job_level,
1180 p_search_criteria.max_job_level,
1181 p_search_criteria.competence_match_weighting,
1182 p_search_criteria.availability_match_weighting,
1183 p_search_criteria.job_level_match_weighting)
1184 WHERE person_id = l_person_id_tbl(k)
1185 AND ((nvl(p_search_criteria.provisional_availability, 'N') = 'N' AND
1186 l_availability_tbl(k) >=
1187 p_search_criteria.min_availability)
1188 OR
1189 (p_search_criteria.provisional_availability = 'Y' AND
1190 l_pot_availability_tbl(k) >=
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),
1200 candidate_score = PA_CANDIDATE_UTILS.Get_Candidate_Score(
1201 resource_id,
1202 l_person_id_tbl(k),
1203 p_search_criteria.assignment_id,
1204 p_search_criteria.project_id,
1205 null,
1206 optional_competence_match+mandatory_competence_match,
1207 optional_competence_count+mandatory_competence_count,
1208 l_availability_tbl(k),
1209 resource_job_level,
1210 p_search_criteria.min_job_level,
1211 p_search_criteria.max_job_level,
1212 p_search_criteria.competence_match_weighting,
1213 p_search_criteria.availability_match_weighting,
1214 p_search_criteria.job_level_match_weighting)
1215 WHERE person_id = l_person_id_tbl(k)
1216 AND ((nvl(p_search_criteria.provisional_availability, 'N') = 'N' AND
1217 l_availability_tbl(k) >=
1218 p_search_criteria.min_availability)
1219 OR
1220 (p_search_criteria.provisional_availability = 'Y' AND
1221 l_pot_availability_tbl(k) >=
1222 p_search_criteria.min_availability));
1223 END IF; -- p-searchmode -- Bug 12335394
1224 END IF ; -- IF ( l_availability_tbl.COUNT > 0 AND l_pot_availability_tbl.COUNT > 0 )
1225 END IF; --l_person_id_tbl.COUNT >0
1226 -- hr_utility.trace('RANJANA 2 ');
1227 -- if p_search_criteria.min_availability =0 then it is possible
1228 -- that some people in the denorm table meet the criteria but
1229 -- were not updated in the above update statement because they
1230 -- were not in l_person_id_tbl(k) because they were not available
1231 -- on ANY of the requirement days. Resource records with 0 hours
1232 -- do not exist in the pa_forecast_items table.
1233 -- In that case we need to update the availability to 0 and update
1234 -- the resource org name and candidate score.
1235 -- NOTE that the update only happens if
1236 -- p_search_criteria.min_availability = 0 AND
1237 -- availability, resource org name, and candidate score are all null,
1238 -- (and mandatory competence match = optional competence match)
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),
1248 candidate_score = PA_CANDIDATE_UTILS.Get_Candidate_Score(
1249 resource_id,
1250 person_id,
1251 p_search_criteria.assignment_id,
1252 p_search_criteria.project_id,
1253 null,
1254 optional_competence_match+mandatory_competence_match,
1255 optional_competence_count+mandatory_competence_count,
1256 0,
1257 resource_job_level,
1258 p_search_criteria.min_job_level,
1259 p_search_criteria.max_job_level,
1260 p_search_criteria.competence_match_weighting,
1261 p_search_criteria.availability_match_weighting,
1262 p_search_criteria.job_level_match_weighting)
1263 WHERE mandatory_competence_count = mandatory_competence_match
1264 AND availability IS NULL
1265 AND resource_organization_name IS NULL
1266 AND candidate_score IS NULL;
1267
1268 END IF; --p_search_criteria.min_availability > 0
1269 -- hr_utility.trace('RANJANA 3 ');
1270
1271 -- END IF; --l_assignment_days > 0
1272
1273 END IF; --l_person_id_tbl.COUNT > 0
1274
1275 --hr_utility.trace_off;
1276 -- if this is a Requirement Search then fetch the values from the cursor
1277 -- into the appropriate local variables.
1278 ELSIF p_search_mode = 'REQUIREMENT' THEN
1279
1280 FETCH l_search_results BULK COLLECT INTO l_assignment_id_tbl,
1281 l_assignment_name_tbl,
1282 l_assignment_number_tbl,
1283 l_assignment_start_date_tbl,
1284 l_assignment_end_date_tbl,
1285 l_status_code_tbl,
1286 l_project_id_tbl,
1287 l_project_name_tbl,
1288 l_project_number_tbl,
1289 l_country_code_tbl,
1290 l_country_tbl,
1291 l_region_tbl,
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
1301 , project_id
1302 , project_name
1303 , project_number
1304 , country
1305 , country_code
1306 , region
1307 , city
1308 , optional_competence_match
1309 , optional_competence_count
1310 , mandatory_competence_match
1311 , mandatory_competence_count
1312 )
1313 VALUES ( l_assignment_id_tbl(k)
1314 , l_assignment_name_tbl(k)
1315 , l_assignment_number_tbl(k)
1316 , l_assignment_start_date_tbl(k)
1317 , l_assignment_end_date_tbl(k)
1318 , l_project_id_tbl(k)
1319 , l_project_name_tbl(k)
1320 , l_project_number_tbl(k)
1321 , l_country_tbl(k)
1322 , l_country_code_tbl(k)
1323 , l_region_tbl(k)
1324 , l_city_tbl(k)
1325 ,0
1326 ,0
1327 ,0
1328 ,0
1329 );
1330
1331 --call function show_req_in_search which checks the
1332 --visible in requirement search status controls for the
1333 --requirement's status(es).
1334 --delete the requirement from the results table if the requirement is not
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
1344 --for all requirements returned by the initial cursor based on that resource.
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'
1354 AND pce.object_id = l_assignment_id_tbl(i))
1355 WHERE assignment_id = l_assignment_id_tbl(i);
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,
1365 per_rating_levels prl,
1366 per_rating_levels prl2
1367 WHERE pce.object_id = l_assignment_id_tbl(i)
1368 AND pce.object_name = 'OPEN_ASSIGNMENT'
1369 AND pce.proficiency_level_id = prl.rating_level_id(+)
1370 AND pce.competence_id = pce2.competence_id
1371 AND pce2.person_id = p_search_criteria.resource_source_id
1372 AND pce2.proficiency_level_id = prl2.rating_level_id(+)
1373 AND decode(prl2.step_value, NULL, decode(prl.step_value, NULL, -999, PA_SEARCH_GLOB.get_min_prof_level(pce2.competence_id)), prl2.step_value) >= nvl(prl.step_value, nvl(prl2.step_value , -999)))
1374 WHERE assignment_id = l_assignment_id_tbl(i);
1375
1376 END IF; --resource source id is not null
1377
1378 --update the search results table with the number of active candidates
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
1388 END IF; --l_assignment_id_tbl.COUNT > 0
1389
1390 END IF; -- search mode check
1391
1392
1393 EXCEPTION
1394
1395 WHEN OTHERS THEN
1396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ; -- 4537865
1397 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_SEARCH_PVT.Run_Search'
1398 , p_procedure_name => PA_DEBUG.G_Err_Stack);
1399 RAISE;
1400
1401
1402 END Run_Search;
1403
1404
1405 FUNCTION Show_Req_In_Search(p_assignment_id pa_project_assignments.assignment_id%TYPE,
1406 p_status_code pa_project_statuses.project_status_code%TYPE)
1407 RETURN VARCHAR2 IS
1408
1409 l_show_in_search VARCHAR2(1);
1410
1411 TYPE status_code_tbl IS TABLE OF pa_project_statuses.project_status_code%TYPE
1412 INDEX BY BINARY_INTEGER;
1413 l_status_code_tbl status_code_tbl;
1414
1415 BEGIN
1416
1417 --if the status code is not passed to the function then the requirement has
1418 --multiple statuses - so get all the statuses for the requirement
1419 --from pa_schedules.
1420 IF p_status_code IS NULL THEN
1421
1422 SELECT DISTINCT status_code BULK COLLECT INTO l_status_code_tbl
1423 FROM pa_schedules
1424 WHERE assignment_id = p_assignment_id;
1425
1426 --if the status code is passed to the function then assign to the plsql table.
1427 ELSE
1428
1429 l_status_code_tbl(1) := p_status_code;
1430
1431 END IF;
1432
1433 --check the Visible in Requirement Search status control for all
1434 --statuses of the requirement.
1435 --Business rule is that if the requirement has multiple statuses
1436 --then if ANY of the statuses should be shown in requirement search
1437 --then show that requirement.
1438 FOR i IN l_status_code_tbl.FIRST .. l_status_code_tbl.LAST LOOP
1439
1440 l_show_in_search := PA_PROJECT_UTILS.Check_prj_stus_action_allowed
1441 ( x_project_status_code => l_status_code_tbl(i)
1442 ,x_action_code => 'OPEN_ASGMT_VISIBLE');
1443
1444 IF l_show_in_search = 'Y' THEN
1445 RETURN 'Y';
1446 END IF;
1447
1448 END LOOP;
1449
1450 RETURN 'N';
1451
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 RAISE;
1455
1456 END;
1457
1458 -- END RUN_SEARCH
1459
1460 PROCEDURE Run_Auto_Search(errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1461 retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1462 p_auto_search_mode IN VARCHAR2,
1463 p_project_id IN NUMBER,
1464 p_project_number_from IN VARCHAR2,
1465 p_project_number_to IN VARCHAR2,
1466 p_proj_start_date_days IN NUMBER,
1467 p_req_start_date_days IN NUMBER,
1468 p_project_status_code IN VARCHAR2,
1469 p_debug_mode IN VARCHAR2)
1470 IS
1471
1472 CURSOR get_auto_search_criteria IS
1473 SELECT asgn.assignment_id,
1474 asgn.project_id,
1475 asgn.min_resource_job_level,
1476 asgn.max_resource_job_level,
1477 asgn.start_date,
1478 asgn.end_date,
1479 asgn.search_min_availability,
1480 asgn.search_country_code,
1481 asgn.search_exp_org_struct_ver_id,
1482 asgn.search_exp_start_org_id,
1483 asgn.search_min_candidate_score,
1484 asgn.COMPETENCE_MATCH_WEIGHTING,
1485 asgn.AVAILABILITY_MATCH_WEIGHTING,
1486 asgn.JOB_LEVEL_MATCH_WEIGHTING,
1487 asgn.ENABLE_AUTO_CAND_NOM_FLAG,
1488 proj.enable_automated_search
1489 FROM pa_project_assignments asgn,
1490 pa_projects_all proj,
1491 pa_project_statuses ps
1492 WHERE proj.project_id = p_project_id
1493 AND proj.project_id = asgn.project_id
1494 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)
1495 AND asgn.assignment_type='OPEN_ASSIGNMENT'
1496 AND asgn.status_code = ps.project_status_code (+)
1497 AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
1498 AND p_auto_search_mode = 'SINGLE_PROJECT'
1499 UNION ALL
1500 SELECT asgn.assignment_id,
1501 asgn.project_id,
1502 asgn.min_resource_job_level,
1503 asgn.max_resource_job_level,
1504 asgn.start_date,
1505 asgn.end_date,
1506 asgn.search_min_availability,
1507 asgn.search_country_code,
1508 asgn.search_exp_org_struct_ver_id,
1509 asgn.search_exp_start_org_id,
1510 asgn.search_min_candidate_score,
1511 asgn.COMPETENCE_MATCH_WEIGHTING,
1512 asgn.AVAILABILITY_MATCH_WEIGHTING,
1513 asgn.JOB_LEVEL_MATCH_WEIGHTING,
1514 asgn.ENABLE_AUTO_CAND_NOM_FLAG,
1515 proj.enable_automated_search
1516 FROM pa_project_assignments asgn,
1517 pa_projects_all proj,
1518 pa_project_statuses ps
1519 WHERE proj.segment1 BETWEEN nvl(p_project_number_from, proj.segment1) AND nvl(p_project_number_to, proj.segment1)
1520 -- 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)
1521 -- Changed for Bug 5299668
1522 AND trunc(nvl(proj.start_date, SYSDATE)) BETWEEN
1523 decode(p_proj_start_date_days, NULL, nvl(proj.start_date, trunc(SYSDATE)), trunc(SYSDATE))
1524 AND
1525 decode(p_proj_start_date_days, NULL, nvl(proj.start_date, trunc(SYSDATE)), trunc(SYSDATE) + p_proj_start_date_days)
1526 AND proj.project_status_code = nvl(p_project_status_code, proj.project_status_code)
1527 AND proj.project_id = asgn.project_id
1528 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)
1529 AND asgn.assignment_type='OPEN_ASSIGNMENT'
1530 AND asgn.status_code = ps.project_status_code (+)
1531 AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
1532 AND p_auto_search_mode = 'MULTI_PROJECT'
1533 ;
1534
1535 TYPE resource_id_tbl IS TABLE OF pa_resources_denorm.resource_id%TYPE
1536 INDEX BY BINARY_INTEGER;
1537 l_resource_id_tbl resource_id_tbl;
1538
1539 TYPE candidate_id_tbl IS TABLE OF pa_candidates.candidate_id%TYPE
1540 INDEX BY BINARY_INTEGER;
1541 l_candidate_id_tbl candidate_id_tbl;
1542
1543 l_competency_tbl PA_HR_COMPETENCE_UTILS.Competency_Tbl_Typ;
1544 l_min_candidate_score NUMBER;
1545 l_no_of_competencies NUMBER;
1546 l_error_msg_code fnd_new_messages.message_name%TYPE;
1547 l_return_status VARCHAR2(1);
1548 l_msg_data fnd_new_messages.message_text%TYPE;
1549 l_msg_count NUMBER;
1550 l_is_resource_candidate VARCHAR2(1);
1551 l_nomination_comments fnd_new_messages.message_text%TYPE;
1552 l_system_nom_candidate_text fnd_new_messages.message_text%TYPE;
1553 l_candidates_nom_in_cycle NUMBER := 0;
1554 l_req_enable_auto_cand_nom pa_project_assignments.ENABLE_AUTO_CAND_NOM_FLAG%TYPE;
1555 l_proj_enable_automated_search pa_projects_all.enable_automated_search%TYPE;
1556 l_status_code pa_project_statuses.project_status_code%TYPE;
1557 l_cand_system_status_code pa_project_statuses.project_system_status_code%TYPE;
1558 l_candidate_in_rec PA_RES_MANAGEMENT_AMG_PUB.CANDIDATE_IN_REC_TYPE; -- Added for bug 9187892
1559
1560 BEGIN
1561
1562 IF p_debug_mode = 'Y' THEN
1563 fnd_file.put_line(FND_FILE.LOG,'Parameters are:');
1564 fnd_file.put_line(FND_FILE.LOG,'p_auto_search_mode = '||p_auto_search_mode);
1565 fnd_file.put_line(FND_FILE.LOG,'p_project_id = '||p_project_id);
1566 fnd_file.put_line(FND_FILE.LOG,'p_project_number_from = '||p_project_number_from);
1567 fnd_file.put_line(FND_FILE.LOG,'p_project_number_to = '||p_project_number_to);
1568 fnd_file.put_line(FND_FILE.LOG,'p_proj_start_date_days = '||p_proj_start_date_days);
1569 fnd_file.put_line(FND_FILE.LOG,'p_req_start_date_days = '||p_req_start_date_days);
1570 fnd_file.put_line(FND_FILE.LOG,'p_project_status_code = '||p_project_status_code);
1571 fnd_file.put_line(FND_FILE.LOG,'p_debug_mode = '||p_debug_mode);
1572 fnd_file.put_line(FND_FILE.LOG,'about to open get_auto_search_criteria cursor');
1573 END IF;
1574
1575 OPEN get_auto_search_criteria;
1576
1577 IF p_debug_mode = 'Y' THEN
1578 fnd_file.put_line(FND_FILE.LOG,'Cursor has been opened');
1579 END IF;
1580
1581 LOOP
1582
1583 IF p_debug_mode = 'Y' THEN
1584 fnd_file.put_line(FND_FILE.LOG,' ');
1585 fnd_file.put_line(FND_FILE.LOG,'Looping through requirements');
1586 fnd_file.put_line(FND_FILE.LOG,'about to fetch from cursor');
1587 END IF;
1588
1589 FETCH get_auto_search_criteria INTO PA_SEARCH_GLOB.g_search_criteria.assignment_id,
1590 PA_SEARCH_GLOB.g_search_criteria.project_id,
1591 PA_SEARCH_GLOB.g_search_criteria.min_job_level,
1592 PA_SEARCH_GLOB.g_search_criteria.max_job_level,
1593 PA_SEARCH_GLOB.g_search_criteria.start_date,
1594 PA_SEARCH_GLOB.g_search_criteria.end_date,
1595 PA_SEARCH_GLOB.g_search_criteria.min_availability,
1596 PA_SEARCH_GLOB.g_search_criteria.territory_code,
1597 PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id,
1598 PA_SEARCH_GLOB.g_search_criteria.organization_id,
1599 l_min_candidate_score,
1600 PA_SEARCH_GLOB.g_search_criteria.COMPETENCE_MATCH_WEIGHTING,
1601 PA_SEARCH_GLOB.g_search_criteria.AVAILABILITY_MATCH_WEIGHTING,
1602 PA_SEARCH_GLOB.g_search_criteria.JOB_LEVEL_MATCH_WEIGHTING,
1603 l_req_enable_auto_cand_nom,
1604 l_proj_enable_automated_search
1605 ;
1606
1607 EXIT WHEN get_auto_search_criteria%NOTFOUND;
1608
1609 IF p_debug_mode = 'Y' THEN
1610 fnd_file.put_line(FND_FILE.LOG,'record fetched from cursor');
1611 fnd_file.put_line(FND_FILE.LOG,'fetch from cursor complete');
1612 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.assignment_id='||PA_SEARCH_GLOB.g_search_criteria.assignment_id);
1613 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.project_id='||PA_SEARCH_GLOB.g_search_criteria.project_id);
1614 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.min_job_level='||PA_SEARCH_GLOB.g_search_criteria.min_job_level);
1615 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.max_job_level='||PA_SEARCH_GLOB.g_search_criteria.max_job_level);
1616 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.start_date='||PA_SEARCH_GLOB.g_search_criteria.start_date);
1617 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.end_date='||PA_SEARCH_GLOB.g_search_criteria.end_date);
1618 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.min_availability='||PA_SEARCH_GLOB.g_search_criteria.min_availability);
1619 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.territory_code='||PA_SEARCH_GLOB.g_search_criteria.territory_code);
1620 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id='||PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id);
1621 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.organization_id='||PA_SEARCH_GLOB.g_search_criteria.organization_id);
1622 fnd_file.put_line(FND_FILE.LOG,'l_min_candidate_score='||l_min_candidate_score);
1623 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.COMPETENCE_MATCH_WEIGHTING='||PA_SEARCH_GLOB.g_search_criteria.COMPETENCE_MATCH_WEIGHTING);
1624 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.AVAILABILITY_MATCH_WEIGHTING='||PA_SEARCH_GLOB.g_search_criteria.AVAILABILITY_MATCH_WEIGHTING);
1625 fnd_file.put_line(FND_FILE.LOG,'PA_SEARCH_GLOB.g_search_criteria.JOB_LEVEL_MATCH_WEIGHTING='||PA_SEARCH_GLOB.g_search_criteria.JOB_LEVEL_MATCH_WEIGHTING);
1626 fnd_file.put_line(FND_FILE.LOG,'l_req_enable_auto_cand_nom='||l_req_enable_auto_cand_nom);
1627 fnd_file.put_line(FND_FILE.LOG,'l_proj_enable_automated_search='||l_proj_enable_automated_search);
1628 fnd_file.put_line(FND_FILE.LOG,'about to call get_competencies API');
1629 END IF;
1630
1631 PA_SEARCH_GLOB.g_search_criteria.work_current_loc := 'N';
1632 PA_SEARCH_GLOB.g_search_criteria.work_all_loc := 'N';
1633 PA_SEARCH_GLOB.g_search_criteria.travel_domestically := 'N';
1634 PA_SEARCH_GLOB.g_search_criteria.travel_internationally := 'N';
1635
1636 -- Assign Provisional Availability to the global record.
1637 PA_SEARCH_GLOB.g_search_criteria.provisional_availability := 'N';
1638
1639 -- Make sure that the Requirement is not end-dated
1640
1641 IF PA_SEARCH_GLOB.g_search_criteria.start_date < SYSDATE AND
1642 PA_SEARCH_GLOB.g_search_criteria.end_date < SYSDATE THEN
1643 IF p_debug_mode = 'Y' THEN
1644 fnd_file.put_line(FND_FILE.LOG,'Ignoring Assignment ID '||PA_SEARCH_GLOB.g_search_criteria.assignment_id ||' because it has been end-dated');
1645 END IF;
1646
1647 ELSE -- Only Process non-end-dated assignments
1648
1649 --get the competencies for the requirement
1650 l_competency_tbl.delete; -- Added for Bug 3098252
1651 PA_SEARCH_GLOB.g_competence_criteria.delete; -- Added for Bug 3098252
1652
1653 PA_HR_COMPETENCE_UTILS.get_competencies(p_object_name => 'OPEN_ASSIGNMENT',
1654 p_object_id => PA_SEARCH_GLOB.g_search_criteria.assignment_id,
1655 x_competency_tbl => l_competency_tbl,
1656 x_no_of_competencies => l_no_of_competencies,
1657 x_error_message_code => l_error_msg_code,
1658 x_return_status => l_return_status);
1659
1660
1661
1662 IF p_debug_mode = 'Y' THEN
1663 fnd_file.put_line(FND_FILE.LOG,'after call to get_competencies');
1664 fnd_file.put_line(FND_FILE.LOG,'requirement has '||l_competency_tbl.COUNT||' competencies');
1665 END IF;
1666
1667 IF l_competency_tbl.COUNT > 0 THEN
1668
1669 IF p_debug_mode = 'Y' THEN
1670 fnd_file.put_line(FND_FILE.LOG,'about to loop through competencies');
1671 END IF;
1672
1673 --store the competences in the global comp table.
1674 FOR i IN l_competency_tbl.FIRST..l_competency_tbl.LAST LOOP
1675
1676 IF p_debug_mode = 'Y' THEN
1677 fnd_file.put_line(FND_FILE.LOG,'in competencies loop');
1678 END IF;
1679
1680 PA_SEARCH_GLOB.g_competence_criteria(i).competence_id := l_competency_tbl(i).competence_id;
1681 PA_SEARCH_GLOB.g_competence_criteria(i).competence_alias := l_competency_tbl(i).competence_alias;
1682 PA_SEARCH_GLOB.g_competence_criteria(i).mandatory_flag := l_competency_tbl(i).mandatory;
1683 PA_SEARCH_GLOB.g_competence_criteria(i).competence_name := NULL;
1684
1685 --get the rating level given the id if id is not null
1686 --and store in global comp table.
1687 IF l_competency_tbl(i).rating_level_id IS NOT NULL THEN
1688
1689 IF p_debug_mode = 'Y' THEN
1690 fnd_file.put_line(FND_FILE.LOG,'about to get the rating level');
1691 END IF;
1692
1693 SELECT step_value INTO PA_SEARCH_GLOB.g_competence_criteria(i).rating_level
1694 FROM per_rating_levels
1695 WHERE rating_level_id = l_competency_tbl(i).rating_level_id;
1696
1697 IF p_debug_mode = 'Y' THEN
1698 fnd_file.put_line(FND_FILE.LOG,'got the rating level');
1699 END IF;
1700 ELSE
1701 PA_SEARCH_GLOB.g_competence_criteria(i).rating_level := NULL;
1702 END IF;
1703
1704 END LOOP;
1705
1706 END IF;
1707
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');
1717 fnd_file.put_line(FND_FILE.LOG,'about to call run search API');
1718 END IF;
1719
1720 Run_Search(p_search_mode => 'RESOURCE',
1721 p_search_criteria => PA_SEARCH_GLOB.g_search_criteria,
1722 p_competence_criteria => PA_SEARCH_GLOB.g_competence_criteria,
1723 p_commit => FND_API.G_TRUE,
1724 p_validate_only => FND_API.G_FALSE,
1725 x_return_status => l_return_status);
1726
1727 IF p_debug_mode = 'Y' THEN
1728 fnd_file.put_line(FND_FILE.LOG,'done with run_search API');
1729 fnd_file.put_line(FND_FILE.LOG,'about to bulk collect resources matching search criteria');
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');
1739 fnd_file.put_line(FND_FILE.LOG,'number of resources returned = '||l_resource_id_tbl.COUNT);
1740 END IF;
1741
1742 IF (l_proj_enable_automated_search = 'Y' AND l_req_enable_auto_cand_nom = 'Y') THEN
1743
1744 IF p_debug_mode = 'Y' THEN
1745 fnd_file.put_line(FND_FILE.LOG,'auto search is enable for this requirement at the project and requirement level');
1746 END IF;
1747
1748 --candidates will be nomimated in this run, so delete any
1749 --qualified candidates from the requirement.
1750 IF p_debug_mode = 'Y' THEN
1751 fnd_file.put_line(FND_FILE.LOG,'about to call API to delete qualified candidates');
1752 END IF;
1753
1754 PA_CANDIDATE_PUB.Delete_Candidates
1755 (p_assignment_id => PA_SEARCH_GLOB.g_search_criteria.assignment_id,
1756 p_status_code => '114',
1757 x_return_status => l_return_status,
1758 x_msg_count => l_msg_count,
1759 x_msg_data => l_msg_data);
1760
1761 IF p_debug_mode = 'Y' THEN
1762 fnd_file.put_line(FND_FILE.LOG,'done with call to API to delete qualified candidates');
1763 END IF;
1764
1765 l_cand_system_status_code := 'CANDIDATE_SYSTEM_NOMINATED';
1766
1767 ELSE
1768
1769 l_cand_system_status_code := 'CANDIDATE_SYSTEM_QUALIFIED';
1770
1771 END IF;
1772
1773 --delete the previous system nominated candidates who will NOT
1774 --be system nominated in current run.
1775
1776 IF p_debug_mode = 'Y' THEN
1777 fnd_file.put_line(FND_FILE.LOG,'about to bulk collect the previous system nominated / qualified candidates who will NOT be system nominated in current run.');
1778 END IF;
1779
1780 SELECT candidate_id BULK COLLECT INTO l_candidate_id_tbl
1781 FROM pa_candidates cand,
1782 pa_project_statuses ps
1783 WHERE assignment_id = PA_SEARCH_GLOB.g_search_criteria.assignment_id
1784 AND cand.status_code = ps.project_status_code
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
1794 fnd_file.put_line(FND_FILE.LOG,'number of previous system nominated candidates to be deleted = '||l_candidate_id_tbl.COUNT);
1795 END IF;
1796
1797 IF l_candidate_id_tbl.COUNT > 0 THEN
1798
1799 --** add code to LOOP THROUGH l_candidate_id_tbl AND CALL API TO DELETE CANDIDATES
1800 FOR i IN l_candidate_id_tbl.FIRST .. l_candidate_id_tbl.LAST LOOP
1801
1802 IF p_debug_mode = 'Y' THEN
1803 fnd_file.put_line(FND_FILE.LOG,'looping through candidates in order to delete');
1804 fnd_file.put_line(FND_FILE.LOG,'calling withdraw_candidate API to delete candidate id '||l_candidate_id_tbl(i));
1805 END IF;
1806
1807 PA_CANDIDATE_PUB.Withdraw_Candidate
1808 (p_candidate_id => l_candidate_id_tbl(i),
1809 x_return_status => l_return_status,
1810 x_msg_count => l_msg_count,
1811 x_msg_data => l_msg_data);
1812
1813 IF p_debug_mode = 'Y' THEN
1814 fnd_file.put_line(FND_FILE.LOG,'after call to withdraw_candidate');
1815 END IF;
1816
1817 END LOOP;
1818
1819 END IF;
1820
1821
1822 IF p_debug_mode = 'Y' THEN
1823 fnd_file.put_line(FND_FILE.LOG,'about to nominate candidates or qualified candidates');
1824 END IF;
1825
1826
1827 IF l_resource_id_tbl.COUNT > 0 THEN
1828
1829 IF p_debug_mode = 'Y' THEN
1830 fnd_file.put_line(FND_FILE.LOG,'there are candidates or qualified candidates to be nominated');
1831 END IF;
1832
1833 FOR i IN l_resource_id_tbl.FIRST .. l_resource_id_tbl.LAST LOOP
1834
1835 IF p_debug_mode = 'Y' THEN
1836 fnd_file.put_line(FND_FILE.LOG,'in loop: resource_id to be nominated = '||l_resource_id_tbl(i));
1837 END IF;
1838
1839 IF (l_proj_enable_automated_search = 'Y' AND l_req_enable_auto_cand_nom = 'Y') THEN
1840
1841 IF p_debug_mode = 'Y' THEN
1842 fnd_file.put_line(FND_FILE.LOG,'auto search is enable for this requirement at the project and requirement level');
1843 END IF;
1844
1845 IF l_system_nom_candidate_text IS NULL THEN
1846
1847 IF p_debug_mode = 'Y' THEN
1848 fnd_file.put_line(FND_FILE.LOG,'about to get system nominated candidate text');
1849 END IF;
1850 /* 2590651 - Added two conditions for application id and language code for the
1851 query from fnd_new_messages below */
1852
1853 SELECT message_text INTO l_system_nom_candidate_text
1854 FROM fnd_new_messages
1855 WHERE message_name = 'PA_SYSTEM_NOMINATED_CANDIDATE'
1856 and application_id = 275
1857 and language_code = userenv('LANG');
1858
1859 IF p_debug_mode = 'Y' THEN
1860 fnd_file.put_line(FND_FILE.LOG,'got system nominated candidate text');
1861 END IF;
1862
1863 END IF;
1864
1865 l_nomination_comments := l_system_nom_candidate_text;
1866 l_status_code := '113';
1867
1868 ELSE
1869 IF p_debug_mode = 'Y' THEN
1870 fnd_file.put_line(FND_FILE.LOG,'auto search is NOT enabled for this requirement - nominate as qualified canidate');
1871 END IF;
1872 l_nomination_comments := NULL;
1873 l_status_code := '114';
1874 END IF;
1875
1876 --nominate the resource
1877 IF p_debug_mode = 'Y' THEN
1878 fnd_file.put_line(FND_FILE.LOG,'about to call Add_candidate API');
1879 fnd_file.put_line(FND_FILE.LOG,'nominate resource '||l_resource_id_tbl(i)||' for assignment '||PA_SEARCH_GLOB.g_search_criteria.assignment_id|| ' on project '||PA_SEARCH_GLOB.g_search_criteria.project_id);
1880 END IF;
1881
1882 --add candidate API will
1883 --when I nominate as candidate:
1884 -- do nothing if resource is already a CANDIDATE - any status
1885 -- change status to system nominated if resource is already qualified candidate
1886 -- else nominate as system nominated candidate
1887 --when I nominate as qualified candidate
1888 -- do nothing if resource is already a CANDIDATE - any status
1889 -- do nothing if resource is already a qualified candidate
1890 -- else nominate as qualified candidate
1891
1892 PA_CANDIDATE_PUB.Add_Candidate(
1893 p_assignment_id => PA_SEARCH_GLOB.g_search_criteria.assignment_id,
1894 p_resource_name => NULL,
1895 p_resource_id => l_resource_id_tbl(i),
1896 p_status_code => l_status_code, --**need to change to the system nominated status code when it is entered in SEED.
1897 p_nomination_comments => l_nomination_comments,
1898 -- Added for bug 9187892
1899 p_attribute_category => l_candidate_in_rec.attribute_category,
1900 p_attribute1 => l_candidate_in_rec.attribute1,
1901 p_attribute2 => l_candidate_in_rec.attribute2,
1902 p_attribute3 => l_candidate_in_rec.attribute3,
1903 p_attribute4 => l_candidate_in_rec.attribute4,
1904 p_attribute5 => l_candidate_in_rec.attribute5,
1905 p_attribute6 => l_candidate_in_rec.attribute6,
1906 p_attribute7 => l_candidate_in_rec.attribute7,
1907 p_attribute8 => l_candidate_in_rec.attribute8,
1908 p_attribute9 => l_candidate_in_rec.attribute9,
1909 p_attribute10 => l_candidate_in_rec.attribute10,
1910 p_attribute11 => l_candidate_in_rec.attribute11,
1911 p_attribute12 => l_candidate_in_rec.attribute12,
1912 p_attribute13 => l_candidate_in_rec.attribute13,
1913 p_attribute14 => l_candidate_in_rec.attribute14,
1914 p_attribute15 => l_candidate_in_rec.attribute15,
1915 x_return_status => l_return_status,
1916 x_msg_count => l_msg_count,
1917 x_msg_data => l_msg_data);
1918
1919 IF p_debug_mode = 'Y' THEN
1920 fnd_file.put_line(FND_FILE.LOG,'after call to Add_candidate API');
1921 fnd_file.put_line(FND_FILE.LOG,'x_return_status = '||l_return_status);
1922 END IF;
1923
1924 l_candidates_nom_in_cycle := l_candidates_nom_in_cycle + 1;
1925
1926 IF p_debug_mode = 'Y' THEN
1927 fnd_file.put_line(FND_FILE.LOG,'candidates/qualified candidates nominated in this cycle = '||l_candidates_nom_in_cycle);
1928 END IF;
1929
1930 END LOOP;
1931
1932 END IF;
1933
1934 IF p_debug_mode = 'Y' THEN
1935 fnd_file.put_line(FND_FILE.LOG,'about to update last_auto_search_date for assignment id = '||PA_SEARCH_GLOB.g_search_criteria.assignment_id);
1936 END IF;
1937
1938 --stamp the date/time on the run on the requirement record
1939 UPDATE PA_PROJECT_ASSIGNMENTS
1940 SET last_auto_search_date = SYSDATE
1941 WHERE assignment_id = PA_SEARCH_GLOB.g_search_criteria.assignment_id;
1942
1943 IF p_debug_mode = 'Y' THEN
1944 fnd_file.put_line(FND_FILE.LOG,'after updating last_auto_search_date for assignment id = '||PA_SEARCH_GLOB.g_search_criteria.assignment_id);
1945 END IF;
1946
1947 IF l_candidates_nom_in_cycle > 100 THEN
1948
1949 IF p_debug_mode = 'Y' THEN
1950 fnd_file.put_line(FND_FILE.LOG,'about to COMMIT');
1951 END IF;
1952
1953 COMMIT;
1954
1955 IF p_debug_mode = 'Y' THEN
1956 fnd_file.put_line(FND_FILE.LOG,'COMMIT complete');
1957 END IF;
1958
1959 l_candidates_nom_in_cycle := 0;
1960
1961 END IF;
1962 END IF; -- end-dated requirements check.
1963
1964 END LOOP;
1965
1966 CLOSE get_auto_search_criteria;
1967
1968 IF p_debug_mode = 'Y' THEN
1969 fnd_file.put_line(FND_FILE.LOG,'about to COMMIT');
1970 END IF;
1971
1972 COMMIT;
1973
1974 IF p_debug_mode = 'Y' THEN
1975 fnd_file.put_line(FND_FILE.LOG,'COMMIT complete');
1976 END IF;
1977
1978 retcode := '0';
1979
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 errbuf := SUBSTR(SQLERRM,1,240);
1983 retcode := '2';
1984 fnd_file.put_line(FND_FILE.LOG,'in when others exception block');
1985 fnd_file.put_line(FND_FILE.LOG,SUBSTR(SQLERRM,1,240));
1986
1987 END Run_Auto_Search;
1988
1989
1990 END PA_SEARCH_PVT;
[Home] [Help]