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