1 PACKAGE BODY PA_SEARCH_PUB AS
2 --$Header: PARISPBB.pls 120.4 2007/10/29 12:13:35 anuragar ship $
3 --
4
5 PROCEDURE Run_Search(
6 p_search_mode IN VARCHAR2
7 , p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
8 , p_assignment_number IN pa_project_assignments.assignment_number%TYPE := FND_API.G_MISS_NUM
9 , p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM
10 , p_resource_name IN pa_resources.name%TYPE := FND_API.G_MISS_CHAR
11 , p_project_id IN pa_projects_all.project_id%TYPE := FND_API.G_MISS_NUM
12 , p_role_id IN pa_project_role_types.project_role_id%TYPE := FND_API.G_MISS_NUM
13 , p_role_name IN pa_project_role_types.meaning%TYPE := FND_API.G_MISS_CHAR
14 , p_min_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
15 , p_max_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
16 , p_org_hierarchy_version_id IN per_org_structure_versions.org_structure_version_id%TYPE := FND_API.G_MISS_NUM
17 , p_org_hierarchy_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
18 , p_organization_id IN hr_organization_units.organization_id%TYPE := FND_API.G_MISS_NUM
19 , p_organization_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
20 , p_employees_only IN VARCHAR2 := FND_API.G_MISS_CHAR
21 , p_territory_code IN fnd_territories_vl.territory_code%TYPE := FND_API.G_MISS_CHAR
22 , p_territory_short_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
23 , p_start_date IN DATE := FND_API.G_MISS_DATE
24 , p_end_date IN DATE := FND_API.G_MISS_DATE
25 , p_competence_id IN system.pa_num_tbl_type := NULL
26 , p_competence_alias IN system.pa_varchar2_30_tbl_type := NULL
27 , p_competence_name IN system.pa_varchar2_240_tbl_type := NULL
28 , p_rating IN system.pa_num_tbl_type := NULL
29 , p_mandatory IN system.pa_varchar2_1_tbl_type := NULL
30 , p_provisional_availability IN VARCHAR
31 , p_region IN VARCHAR := FND_API.G_MISS_CHAR
32 , p_city IN VARCHAR := FND_API.G_MISS_CHAR
33 --, p_competences IN pa_search_glob.Competence_Criteria_Tbl_Type
34 , p_work_current_loc IN VARCHAR
35 , p_work_all_loc IN VARCHAR
36 , p_travel_domestically IN VARCHAR
37 , p_travel_internationally IN VARCHAR
38 -- , p_ad_hoc_search IN VARCHAR := 'N'
39 , p_minimum_availability IN NUMBER := FND_API.G_MISS_NUM
40 , p_restrict_res_comp IN VARCHAR := FND_API.G_MISS_CHAR
41 , p_exclude_candidates IN VARCHAR := FND_API.G_MISS_CHAR
42 , p_staffing_priority_code IN VARCHAR := FND_API.G_MISS_CHAR
43 , p_staffing_priority_name IN VARCHAR := FND_API.G_MISS_CHAR
44 , p_staffing_owner_person_id IN NUMBER := FND_API.G_MISS_NUM
45 , p_staffing_owner_name IN VARCHAR := FND_API.G_MISS_CHAR
46 , p_comp_match_weighting IN NUMBER := FND_API.G_MISS_NUM
47 , p_avail_match_weighting IN NUMBER := FND_API.G_MISS_NUM
48 , p_job_level_match_weighting IN NUMBER := FND_API.G_MISS_NUM
49 , p_get_search_criteria IN VARCHAR2 := FND_API.G_FALSE
50 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
51 , p_api_version IN NUMBER := 1.0
52 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
53 , p_commit IN VARCHAR2 := FND_API.G_FALSE
54 , p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
55 , p_person_type IN VARCHAR2 := FND_API.G_MISS_CHAR
56 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
57 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
58 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
59 ) IS
60
61 l_index NUMBER;
62 l_return_status VARCHAR2(1);
63 l_error_msg_data fnd_new_messages.message_name%TYPE;
64 l_error_message_code fnd_new_messages.message_name%TYPE;
65 l_competency_tbl PA_HR_COMPETENCE_UTILS.Competency_Tbl_Typ;
66 l_no_of_competencies NUMBER;
67 l_msg_index_out NUMBER;
68 l_competence_id per_competences.competence_id%TYPE;
69 -- added for Bug fix: 4537865
70 l_new_competence_id per_competences.competence_id%TYPE;
71 -- added for Bug fix: 4537865
72 l_competence_alias per_competences.competence_alias%TYPE;
73 l_resource_type_id NUMBER;
74 l_competence_criteria PA_SEARCH_GLOB.Competence_Criteria_Tbl_Type;
75 l_org_structure_type PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE;
76 i NUMBER := 1;
77 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
78
79 -- cursor to get the search criteria if a Resource search is performed
80 -- directly on a requirement (without entering the search criteria
81 -- on the screen).
82 --
83 CURSOR get_search_criteria IS
84 SELECT asgn.min_resource_job_level,
85 asgn.max_resource_job_level,
86 asgn.start_date,
87 asgn.end_date,
88 asgn.competence_match_weighting,
89 asgn.availability_match_weighting,
90 asgn.job_level_match_weighting,
91 asgn.search_min_availability,
92 asgn.search_country_code,
93 asgn.SEARCH_EXP_ORG_STRUCT_VER_ID,
94 asgn.SEARCH_EXP_START_ORG_ID
95 FROM pa_project_assignments asgn
96 WHERE asgn.assignment_id = p_assignment_id;
97
98 -- cursor to the the rating level from the rating level id.
99 CURSOR get_step_value(l_rating_level_id NUMBER) IS
100 SELECT step_value
101 FROM per_rating_levels
102 WHERE rating_level_id = l_rating_level_id;
103
104 BEGIN
105
106 IF (P_DEBUG_MODE ='Y') THEN
107 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.begin',
108 x_Msg => 'in PA_SEARCH_PUB.Run_Search',
109 x_Log_Level => 6);
110 End if;
111
112 -- Initialize the return status.
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 -- Initialise the error stack
116 PA_DEBUG.init_err_stack('PA_SEARCH_PUB.Run_Search');
117
118 -- Issue API savepoint if the transaction is to be committed
119 IF (p_commit = FND_API.G_TRUE) THEN
120 SAVEPOINT SEARCH_PUB_RUN_OA_SEARCH;
121 END IF;
122
123 -- Initialise message stack if required
124 IF FND_API.TO_BOOLEAN (p_init_msg_list) THEN
125 FND_MSG_PUB.initialize;
126 END IF;
127
128 -- assign the assignment id to the g_search_criteria global pl/sql record
129 -- if the assignment id is passed to the API.
130 IF p_assignment_id <> FND_API.G_MISS_NUM AND p_assignment_id IS NOT NULL THEN
131
132 PA_SEARCH_GLOB.g_search_criteria.assignment_id := p_assignment_id;
133
134 END IF;
135
136 -- assign the project id to the g_search_criteria global pl/sql record
137 -- if the project id is passed to the API.
138 IF p_project_id <> FND_API.G_MISS_NUM AND p_project_id IS NOT NULL THEN
139
140 PA_SEARCH_GLOB.g_search_criteria.project_id := p_project_id;
141
142 END IF;
143
144 IF (P_DEBUG_MODE ='Y') THEN
145 PA_DEBUG.WRITE_LOG(
146 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.validation',
147 x_Msg => 'validate input parameters',
148 x_Log_Level => 6);
149 End If;
150
151 -- if this is a Requirement Search
152 -- or if this is a Resource search and p_get_search_criteria is false then
153 -- validate the search criteria and assign to globals.
154 IF (p_search_mode = 'REQUIREMENT') OR (p_search_mode = 'ADHOC') OR
155 (p_search_mode = 'RESOURCE' AND p_get_search_criteria = FND_API.G_FALSE)
156 THEN
157
158 -- validate the assignment number if it is passed to the API.
159 IF p_assignment_number <> FND_API.G_MISS_NUM AND
160 p_assignment_number IS NOT NULL THEN
161
162 PA_ASSIGNMENT_UTILS.Check_Assignment_Number_Or_Id(
163 p_assignment_number => p_assignment_number,
164 p_assignment_id => p_assignment_id,
165 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
166 x_assignment_id => PA_SEARCH_GLOB.g_search_criteria.assignment_id,
167 x_return_status => l_return_status,
168 x_error_message_code => l_error_message_code);
169
170 -- if the assignment number is not valid then
171 -- add a message to the error stack.
172 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
173 PA_UTILS.Add_Message ('PA', l_error_message_code);
174 END IF;
175
176 END IF;
177
178 -- assign the p_employees_only parameter, indicating if the search is for
179 -- employees only, to the g_search_criteria global record.
180 IF p_employees_only <> FND_API.G_MISS_CHAR THEN
181
182 PA_SEARCH_GLOB.g_search_criteria.employees_only := p_employees_only;
183
184 END IF;
185
186 -- assign the p_restrict_res_comp parameter to the g_search_criteria
187 -- global record. This parameter is used in the Requirement Search
188 -- to indicate if the a requirement should not be returned if the resource
189 -- does not have one of the requirement's mandatory competences.
190 --
191 IF p_restrict_res_comp <> FND_API.G_MISS_CHAR THEN
192
193 PA_SEARCH_GLOB.g_search_criteria.restrict_res_comp :=
194 p_restrict_res_comp;
195
196 END IF;
197
198 -- assign the p_exclude_candidates parameter to the g_search_criteria
199 -- global record. This parameter is used in the Requirement Search
200 -- to indicate if a requirement should not be returned if the resource
201 -- is already a candidate for that requirement.
202 --
203 IF p_exclude_candidates <> FND_API.G_MISS_CHAR THEN
204
205 PA_SEARCH_GLOB.g_search_criteria.exclude_candidates :=
206 p_exclude_candidates;
207
208 END IF;
209
210 -- Validate input to retrieve ID values
211
212 IF (p_search_mode = 'RESOURCE' OR p_search_mode = 'ADHOC') THEN
213 l_org_structure_type := 'EXPENDITURES';
214 ELSE
215 l_org_structure_type := 'PROJECTS';
216 END IF;
217
218 -- Validate the org hierarchy and assign the id to the global record.
219 IF (p_org_hierarchy_version_id <> FND_API.G_MISS_NUM AND
220 p_org_hierarchy_version_id IS NOT NULL) OR
221 (p_org_hierarchy_name <> FND_API.G_MISS_CHAR AND
222 p_org_hierarchy_name IS NOT NULL )
223 THEN
224
225 PA_HR_ORG_UTILS.Check_OrgHierName_Or_Id(
226 p_org_hierarchy_version_id => p_org_hierarchy_version_id,
227 p_org_hierarchy_name => p_org_hierarchy_name,
228 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
229 x_org_hierarchy_version_id => PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id,
230 x_return_status => l_return_status,
231 x_error_msg_code => l_error_msg_data);
232
233 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
234 PA_UTILS.Add_Message ('PA', l_error_msg_data);
235 ELSE
236
237 -- check if the org hierarchy is of the correct type
238 -- i.e. EXPENDITURES for resource search
239 -- PROJECTS for requirement search
240 PA_ORG_UTILS.Check_OrgHierarchy_Type(
241 p_org_structure_version_id =>
242 PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id,
243 p_org_structure_type => l_org_structure_type,
244 x_return_status => l_return_status,
245 x_error_message_code => l_error_msg_data);
246
247 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
248 PA_UTILS.Add_Message ('PA', l_error_msg_data);
249 END IF;
250 END IF;
251
252 END IF;
253
254 -- validate the organization and assign the id to the global record.
255 IF (p_organization_id <> FND_API.G_MISS_NUM AND
256 p_organization_id IS NOT NULL) OR
257 (p_organization_name <> FND_API.G_MISS_CHAR AND
258 p_organization_name IS NOT NULL)
259 THEN
260 PA_HR_ORG_UTILS.Check_OrgName_Or_Id(
261 p_organization_id => p_organization_id,
262 p_organization_name => p_organization_name,
263 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
264 x_organization_id =>
265 PA_SEARCH_GLOB.g_search_criteria.organization_id,
266 x_return_status => l_return_status,
267 x_error_msg_code => l_error_msg_data);
268
269 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
270 PA_UTILS.Add_Message ('PA', l_error_msg_data);
271 ELSE
272 -- check if the starting org is of the correct type
273 -- i.e. EXPENDITURES for resource search
274 -- PROJECTS for requirement search
275 PA_ORG_UTILS.Check_Org_Type(
276 p_organization_id =>
277 PA_SEARCH_GLOB.g_search_criteria.organization_id,
278 p_org_structure_type => l_org_structure_type,
279 x_return_status => l_return_status,
280 x_error_message_code => l_error_msg_data);
281
282 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
283 PA_UTILS.Add_Message ('PA', l_error_msg_data);
284 END IF;
285 END IF;
286
287 END IF;
288
289 -- check if the organization is in the organization hierarchy
290 IF (p_organization_id <> FND_API.G_MISS_NUM AND
291 p_organization_id IS NOT NULL) OR
292 (p_organization_name <> FND_API.G_MISS_CHAR AND
293 p_organization_name IS NOT NULL)
294 THEN
295
296 IF PA_SEARCH_GLOB.g_search_criteria.organization_id IS NOT NULL AND
297 PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id IS NOT NULL
298 THEN
299
300 PA_ORG_UTILS.Check_Org_In_OrgHierarchy(
301 p_organization_id =>
302 PA_SEARCH_GLOB.g_search_criteria.organization_id,
303 p_org_structure_version_id =>
304 PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id,
305 p_org_structure_type => l_org_structure_type,
306 x_return_status => l_return_status,
307 x_error_message_code => l_error_msg_data);
308
309 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
310 PA_UTILS.Add_Message ('PA', l_error_msg_data);
311 END IF;
312 END IF;
313 END IF;
314
315 -- validate the staffing priority code and assign it to global record
316 IF (p_staffing_priority_code <> FND_API.G_MISS_CHAR AND
317 p_staffing_priority_code IS NOT NULL) OR
318 (p_staffing_priority_name IS NOT NULL AND
319 p_staffing_priority_name <> FND_API.G_MISS_CHAR)
320 THEN
321 PA_ASSIGNMENT_UTILS.Check_STF_PriorityName_Or_Code(
322 p_staffing_priority_code => p_staffing_priority_code,
323 p_staffing_priority_name => p_staffing_priority_name,
324 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
325 x_staffing_priority_code =>
326 PA_SEARCH_GLOB.g_search_criteria.staffing_priority_code,
327 x_return_status => l_return_status,
328 x_error_message_code => l_error_msg_data);
329
330 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
331 PA_UTILS.Add_Message ('PA', l_error_msg_data);
332 END IF;
333 END IF;
334
335 -- validate the staffing owner and assign it to global record
336 IF (p_staffing_owner_person_id <> FND_API.G_MISS_NUM AND
337 p_staffing_owner_person_id IS NOT NULL) OR
338 (p_staffing_owner_name IS NOT NULL AND
339 p_staffing_owner_name <> FND_API.G_MISS_CHAR)
340 THEN
341
342 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id (
343 p_resource_id => p_staffing_owner_person_id
344 ,p_resource_name => p_staffing_owner_name
345 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
346 ,p_date => sysdate
347 ,x_resource_id => PA_SEARCH_GLOB.g_search_criteria.staffing_owner_person_id
348 ,x_resource_type_id => l_resource_type_id
349 ,x_return_status => l_return_status
350 ,x_error_message_code => l_error_message_code);
351
352 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_resource_type_id <> 101 THEN
353 PA_UTILS.Add_Message ('PA', 'PA_INV_STAFF_OWNER');
354 END IF;
355
356 END IF;
357
358 -- validate the territory code and assign the territory code
359 -- to the global record.
360 --
361 IF (p_territory_code <> FND_API.G_MISS_CHAR AND
362 p_territory_code IS NOT NULL) OR
363 (p_territory_short_name <> FND_API.G_MISS_CHAR AND
364 p_territory_short_name IS NOT NULL)
365 THEN
366 PA_LOCATION_UTILS.Check_Country_Name_Or_Code(
367 p_country_code => p_territory_code,
368 p_country_name => p_territory_short_name,
369 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
370 x_country_code => PA_SEARCH_GLOB.g_search_criteria.territory_code,
371 x_return_status => l_return_status,
372 x_error_message_code => l_error_msg_data);
373
374 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
375 PA_UTILS.Add_Message ('PA', l_error_msg_data);
376 END IF;
377 END IF;
378
379 -- Add region and city values to global search criteria.
380 PA_SEARCH_GLOB.g_search_criteria.region := p_region;
381 PA_SEARCH_GLOB.g_search_criteria.city := p_city;
382
383 -- Add person type to global search criteria. Bug 6526674
384 PA_SEARCH_GLOB.g_search_criteria.person_type := p_person_type;
385
386 -- Add work preferences to global search criteria.
387 PA_SEARCH_GLOB.g_search_criteria.work_current_loc := p_work_current_loc;
388 PA_SEARCH_GLOB.g_search_criteria.work_all_loc := p_work_all_loc;
389 PA_SEARCH_GLOB.g_search_criteria.travel_domestically :=
390 p_travel_domestically;
391 PA_SEARCH_GLOB.g_search_criteria.travel_internationally :=
392 p_travel_internationally;
393
394 -- validate match weightings and assign them to global record
395
396 IF (p_comp_match_weighting <> FND_API.G_MISS_NUM AND
397 p_comp_match_weighting IS NOT NULL) OR
398 (p_avail_match_weighting <> FND_API.G_MISS_NUM AND
399 p_avail_match_weighting IS NOT NULL) OR
400 (p_job_level_match_weighting <> FND_API.G_MISS_NUM AND
401 p_job_level_match_weighting IS NOT NULL)
402 THEN
403
404 IF p_comp_match_weighting < 0 OR p_comp_match_weighting > 100 OR
405 p_avail_match_weighting < 0 OR p_avail_match_weighting > 100 OR
406 p_job_level_match_weighting < 0 OR p_job_level_match_weighting > 100
407 THEN
408
409 PA_UTILS.Add_Message(
410 p_app_short_name => 'PA'
411 ,p_msg_name => 'PA_INVALID_MATCH_WEIGHTING');
412 ELSE
413 -- assign match weightings to global record.
414 PA_SEARCH_GLOB.g_search_criteria.competence_match_weighting :=
415 p_comp_match_weighting;
416 PA_SEARCH_GLOB.g_search_criteria.availability_match_weighting :=
417 p_avail_match_weighting;
418 PA_SEARCH_GLOB.g_search_criteria.job_level_match_weighting :=
419 p_job_level_match_weighting;
420 END IF;
421 END IF;
422
423
424 -- A resource name or id may be passed in only for a requirement
425 -- search for the competence match criteria.
426 -- Validate the resource and get that resource's competences.
427 --
428 IF (p_resource_source_id <> FND_API.G_MISS_NUM AND
429 p_resource_source_id IS NOT NULL) OR
430 (p_resource_name <> FND_API.G_MISS_CHAR AND
431 p_resource_name IS NOT NULL)
432 THEN
433
434 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id (
435 p_resource_id => p_resource_source_id
436 ,p_resource_name => p_resource_name
437 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
438 ,p_date => p_start_date
439 ,x_resource_id => PA_SEARCH_GLOB.g_search_criteria.resource_source_id
440 ,x_resource_type_id => l_resource_type_id
441 ,x_return_status => l_return_status
442 ,x_error_message_code => l_error_message_code);
443
444 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
445 PA_UTILS.Add_Message ('PA', l_error_message_code);
446 END IF;
447
448 END IF;
449
450 -- validate the role if passed in, and assign the role id to
451 -- the global record.
452 --
453 IF (p_role_id <> FND_API.G_MISS_NUM AND p_role_id IS NOT NULL) OR
454 (p_role_name <> FND_API.G_MISS_CHAR AND p_role_name IS NOT NULL)
455 THEN
456
457 PA_ROLE_UTILS.Check_Role_Name_Or_Id(
458 p_role_id => p_role_id
459 ,p_role_name => p_role_name
460 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
461 ,x_role_id => PA_SEARCH_GLOB.g_search_criteria.role_id
462 ,x_return_status => l_return_status
463 ,x_error_message_code => l_error_message_code );
464
465 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
466 PA_UTILS.Add_Message ('PA', l_error_message_code);
467 END IF;
468
469 END IF;
470
471 --
472 -- Check that max job level is >= min job level
473 --
474 IF (p_min_job_level <> FND_API.G_MISS_NUM AND
475 p_min_job_level IS NOT NULL) OR
476 (p_max_job_level <> FND_API.G_MISS_NUM AND
477 p_max_job_level IS NOT NULL)
478 THEN
479 IF p_min_job_level > p_max_job_level THEN
480 PA_UTILS.Add_Message(
481 p_app_short_name => 'PA'
482 ,p_msg_name => 'PA_MIN_JL_GREATER_THAN_MAX');
483 ELSE
484
485 -- assign job levels to global record.
486 PA_SEARCH_GLOB.g_search_criteria.min_job_level := p_min_job_level;
487 PA_SEARCH_GLOB.g_search_criteria.max_job_level := p_max_job_level;
488 END IF;
489 END IF;
490
491 --Check that min availability is between 0 and 100
492 IF p_minimum_availability <> FND_API.G_MISS_NUM AND
493 p_minimum_availability IS NOT NULL
494 THEN
495 IF p_minimum_availability < 0 OR p_minimum_availability > 100 THEN
496 PA_UTILS.Add_Message( p_app_short_name => 'PA'
497 ,p_msg_name => 'PA_MIN_AVAIL_INVALID');
498 END IF;
499
500 -- assign the min availability to the global record.
501 PA_SEARCH_GLOB.g_search_criteria.min_availability :=
502 p_minimum_availability;
503
504 END IF;
505
506 -- Assign Provisional Availability to the global record.
507 PA_SEARCH_GLOB.g_search_criteria.provisional_availability :=
508 p_provisional_availability;
509
510 -- validate that start date <= end date.
511 -- assign p_start_date to the global record before the validation
512 -- in this case because for a requirement search ONLY the start
513 -- date is required. So we only need to do the validation
514 -- if the end date is passed in as well. The end date will only
515 -- be assigned to the global record if it is passed in and valid.
516
517 IF p_start_date <> FND_API.G_MISS_DATE AND p_start_date IS NOT NULL THEN
518 PA_SEARCH_GLOB.g_search_criteria.start_date := p_start_date;
519 END IF;
520
521 IF (p_start_date <> FND_API.G_MISS_DATE AND p_start_date IS NOT NULL) AND
522 (p_end_date <> FND_API.G_MISS_DATE AND p_end_date IS NOT NULL)
523 THEN
524
525 IF p_start_date > p_end_date THEN
526 PA_UTILS.Add_Message( p_app_short_name => 'PA'
527 ,p_msg_name => 'PA_INVALID_START_DATE');
528
529 ELSE
530
531 -- if end date >= start date then assignment end date
532 -- to the global record.
533 --
534 PA_SEARCH_GLOB.g_search_criteria.end_date := p_end_date;
535
536 END IF;
537
538 IF (p_search_mode = 'RESOURCE') AND
539 p_start_date < SYSDATE AND
540 p_end_date < SYSDATE THEN
541 PA_UTILS.Add_Message(p_app_short_name => 'PA'
542 ,p_msg_name => 'PA_REQ_DATES_IN_PAST');
543 END IF;
544
545 ELSIF (p_start_date <> FND_API.G_MISS_DATE AND
546 p_start_date IS NOT NULL) AND
547 (p_end_date = FND_API.G_MISS_DATE OR p_end_date IS NULL) THEN
548 PA_SEARCH_GLOB.g_search_criteria.end_date := ADD_MONTHS(p_start_date,6);
549 END IF;
550
551 -- validate competence
552 -- competence name/alias/id will only be passed in if this is a
553 -- Resource Search.
554 --
555 -- IF (p_competence_id IS NOT NULL OR p_competence_alias IS NOT NULL OR
556 -- p_competence_name IS NOT NULL) THEN
557 --
558 IF p_competence_id.COUNT > 0 THEN
559 FOR i in p_competence_id.first .. p_competence_id.last LOOP
560
561 -- find the number of competences already in the global comp table.
562 l_index := PA_SEARCH_GLOB.g_competence_criteria.COUNT;
563
564 IF p_competence_id(i) <> -1 THEN
565 IF p_competence_id(i) = -999 THEN
566 l_competence_id := NULL;
567 ELSE
568 l_competence_id := p_competence_id(i);
569 END IF;
570
571 IF p_competence_alias(i) = ' ' THEN
572 l_competence_alias := NULL;
573 ELSE
574 l_competence_alias := p_competence_alias(i);
575 END IF;
576
577 PA_HR_COMPETENCE_UTILS.Check_CompName_Or_Id (
578 p_competence_id => l_competence_id, --p_competence_id(i),
579 p_competence_alias => l_competence_alias, --p_competence_alias(i);
580 p_competence_name => p_competence_name(i),
581 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag,
582 --x_competence_id => l_competence_id, * Commented for Bug: 4537865
583 x_competence_id => l_new_competence_id, -- added for Bug fix: 4537865
584 x_return_status => l_return_status,
585 x_error_msg_code => l_error_msg_data);
586
587 -- added for Bug fix: 4537865
588 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
589 l_competence_id := l_new_competence_id;
590 END IF;
591 -- added for Bug fix: 4537865
592
593 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
594 PA_UTILS.Add_Message ('PA', l_error_msg_data);
595 ELSE
596
597 -- insert the competence into the global table at l_index.
598 PA_SEARCH_GLOB.g_competence_criteria(l_index).competence_id :=
599 l_competence_id;
600 END IF;
601 ELSE
602 -- insert the competence category into the global table at l_index.
603 PA_SEARCH_GLOB.g_competence_criteria(l_index).competence_id :=
604 p_competence_id(i);
605 END IF;
606
607 PA_SEARCH_GLOB.g_competence_criteria(l_index).rating_level :=
608 p_rating(i);
609 PA_SEARCH_GLOB.g_competence_criteria(l_index).mandatory_flag :=
610 p_mandatory(i);
611 PA_SEARCH_GLOB.g_competence_criteria(l_index).competence_name :=
612 p_competence_name(i);
613
614 END LOOP;
615 END IF;
616 --
617 -- END IF;
618 -- IF p_search_mode <> REQUIREMENT OR RESOURCE
619 -- resource search being execute from page OTHER than the resource
620 -- search criteria page
621 -- so we need to get the search criteria
622 ELSIF p_get_search_criteria = FND_API.G_TRUE THEN
623
624 IF (P_DEBUG_MODE ='Y') THEN
625 PA_DEBUG.WRITE_LOG(
626 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.get_search_criteria'
627 ,x_Msg => 'resource search being execute from page OTHER than the resource search criteria page, get search criteria'
628 ,x_Log_Level => 6);
629 End If;
630
631 PA_SEARCH_GLOB.g_search_criteria.work_current_loc := 'N';
632 PA_SEARCH_GLOB.g_search_criteria.work_all_loc := 'N';
633 PA_SEARCH_GLOB.g_search_criteria.travel_domestically := 'N';
634 PA_SEARCH_GLOB.g_search_criteria.travel_internationally := 'N';
635
636 -- Assign Provisional Availability to the global record.
637 PA_SEARCH_GLOB.g_search_criteria.provisional_availability := 'N';
638
639 -- get the search criteria for the assignment.
640 OPEN get_search_criteria;
641
642 FETCH get_search_criteria INTO
643 PA_SEARCH_GLOB.g_search_criteria.min_job_level,
644 PA_SEARCH_GLOB.g_search_criteria.max_job_level,
645 PA_SEARCH_GLOB.g_search_criteria.start_date,
646 PA_SEARCH_GLOB.g_search_criteria.end_date,
647 PA_SEARCH_GLOB.g_search_criteria.competence_match_weighting,
648 PA_SEARCH_GLOB.g_search_criteria.availability_match_weighting,
649 PA_SEARCH_GLOB.g_search_criteria.job_level_match_weighting,
650 PA_SEARCH_GLOB.g_search_criteria.min_availability,
651 PA_SEARCH_GLOB.g_search_criteria.territory_code,
652 PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id,
653 PA_SEARCH_GLOB.g_search_criteria.organization_id;
654
655 IF PA_SEARCH_GLOB.g_search_criteria.start_date < SYSDATE AND
656 PA_SEARCH_GLOB.g_search_criteria.end_date < SYSDATE
657 THEN
658 PA_UTILS.Add_Message(p_app_short_name => 'PA'
659 ,p_msg_name => 'PA_REQ_DATES_IN_PAST');
660 END IF;
661
662 IF get_search_criteria%NOTFOUND THEN
663
664 RAISE NO_DATA_FOUND;
665
666 END IF;
667
668 CLOSE get_search_criteria;
669
670 -- get the competencies for the requirement
671 PA_HR_COMPETENCE_UTILS.get_competencies(
672 p_object_name => 'OPEN_ASSIGNMENT',
673 p_object_id => PA_SEARCH_GLOB.g_search_criteria.assignment_id,
674 x_competency_tbl => l_competency_tbl,
675 x_no_of_competencies => l_no_of_competencies,
676 x_error_message_code => l_error_msg_data,
677 x_return_status => l_return_status);
678
679 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
680 PA_UTILS.Add_Message ('PA', l_error_msg_data);
681
682 ELSIF l_competency_tbl.COUNT > 0 THEN
683
684 -- store the competences in the global comp table.
685 FOR i IN l_competency_tbl.FIRST..l_competency_tbl.LAST LOOP
686
687 PA_SEARCH_GLOB.g_competence_criteria(i).competence_id :=
688 l_competency_tbl(i).competence_id;
689 PA_SEARCH_GLOB.g_competence_criteria(i).competence_alias :=
690 l_competency_tbl(i).competence_alias;
691 PA_SEARCH_GLOB.g_competence_criteria(i).mandatory_flag :=
692 l_competency_tbl(i).mandatory;
693
694 -- get the rating level given the id if id is not null
695 -- and store in global comp table.
696 IF l_competency_tbl(i).rating_level_id IS NOT NULL THEN
697 OPEN get_step_value(l_competency_tbl(i).rating_level_id);
698 FETCH get_step_value INTO
699 PA_SEARCH_GLOB.g_competence_criteria(i).rating_level;
700 CLOSE get_step_value;
701 ELSE
702 PA_SEARCH_GLOB.g_competence_criteria(i).rating_level := NULL;
703 END IF;
704
705 END LOOP;
706
707 END IF;
708
709 END IF;
710
711 -- If there are no errors and
712 -- 1) p_validate_only = 'F', OR
713 -- 2) the search criteria has not been passed to the API --
714 -- we got the criteria in the API, then call the private API.
715
716 IF FND_MSG_PUB.Count_Msg = 0 AND
717 (p_validate_only = FND_API.G_FALSE OR
718 p_get_search_criteria = FND_API.G_TRUE)
719 THEN
720
721 -- DEBUG
722 IF (p_search_mode = 'RESOURCE' OR p_search_mode = 'ADHOC') AND
723 p_get_search_criteria = FND_API.G_FALSE
724 THEN
725 IF (P_DEBUG_MODE ='Y') THEN
726 PA_DEBUG.WRITE_LOG(
727 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameters',
728 x_Msg => 'calling pvt',
729 x_Log_Level => 6);
730 PA_DEBUG.WRITE_LOG(
731 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
732 x_Msg => 'p_search_mode=' || p_search_mode,
733 x_Log_Level => 6);
734 PA_DEBUG.WRITE_LOG(
735 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
736 x_Msg => 'assignment_id=' || PA_SEARCH_GLOB.g_search_criteria.assignment_id,
737 x_Log_Level => 6);
738 PA_DEBUG.WRITE_LOG(
739 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
740 x_Msg => 'resource_source_id' || PA_SEARCH_GLOB.g_search_criteria.resource_source_id,
741 x_Log_Level => 6);
742 PA_DEBUG.WRITE_LOG(
743 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
744 x_Msg => 'project_id=' || PA_SEARCH_GLOB.g_search_criteria.project_id,
745 x_Log_Level => 6);
746 PA_DEBUG.WRITE_LOG(
747 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
748 x_Msg => 'min_job_level=' || PA_SEARCH_GLOB.g_search_criteria.min_job_level,
749 x_Log_Level => 6);
750 PA_DEBUG.WRITE_LOG(
751 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
752 x_Msg => 'max_job_level=' || PA_SEARCH_GLOB.g_search_criteria.max_job_level,
753 x_Log_Level => 6);
754 PA_DEBUG.WRITE_LOG(
755 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
756 x_Msg => 'org_hierarchy_version_id=' || PA_SEARCH_GLOB.g_search_criteria.org_hierarchy_version_id,
757 x_Log_Level => 6);
758 PA_DEBUG.WRITE_LOG(
759 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
760 x_Msg => 'organization_id=' || PA_SEARCH_GLOB.g_search_criteria.organization_id,
761 x_Log_Level => 6);
762 PA_DEBUG.WRITE_LOG(
763 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
764 x_Msg => 'territory_code=' || PA_SEARCH_GLOB.g_search_criteria.territory_code,
765 x_Log_Level => 6);
766 PA_DEBUG.WRITE_LOG(
767 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
768 x_Msg => 'start_date=' || PA_SEARCH_GLOB.g_search_criteria.start_date,
769 x_Log_Level => 6);
770 PA_DEBUG.WRITE_LOG(
771 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
772 x_Msg => 'end_date=' || PA_SEARCH_GLOB.g_search_criteria.end_date,
773 x_Log_Level => 6);
774 PA_DEBUG.WRITE_LOG(
775 x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.parameter',
776 x_Msg => 'min_availability=' || PA_SEARCH_GLOB.g_search_criteria.min_availability,
777 x_Log_Level => 6);
778 End if;
779
780 END IF;
781
782 -- Call Private API
783
784 IF (P_DEBUG_MODE ='Y') THEN
785 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_SEARCH_PUB.Run_Search.call_pvt',x_Msg => 'calling PA_SEARCH_PVT.Run_Search', x_Log_Level => 6);
786 End If;
787
788 PA_SEARCH_PVT.Run_Search(
789 p_search_mode => p_search_mode
790 , p_search_criteria => PA_SEARCH_GLOB.g_search_criteria
791 , p_competence_criteria => PA_SEARCH_GLOB.g_competence_criteria
792 , p_commit => p_commit
793 , p_validate_only => p_validate_only
794 , x_return_status => l_return_status
795 );
796
797 -- clear the globals
798 PA_SEARCH_GLOB.g_search_criteria := NULL;
799 PA_SEARCH_GLOB.g_competence_criteria.DELETE;
800
801 -- Reset the error stack when returning to the calling environment
802 PA_DEBUG.Reset_Err_Stack;
803
804 END IF;
805
806 --
807 -- IF the number of messages is 1 then fetch the message code
808 -- from the stack and return its text
809 --
810
811 x_msg_count := FND_MSG_PUB.Count_Msg;
812
813 IF x_msg_count = 1 THEN
814 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
815 ,p_msg_index => 1
816 ,p_data => x_msg_data
817 ,p_msg_index_out => l_msg_index_out
818 );
819
820 END IF;
821
822 --if there are errors in the stack then set x_return_status.
823 IF FND_MSG_PUB.Count_Msg <> 0 THEN
824
825 x_return_status := FND_API.G_RET_STS_ERROR;
826
827 -- clear the globals
828 PA_SEARCH_GLOB.g_search_criteria := NULL;
829 PA_SEARCH_GLOB.g_competence_criteria.DELETE;
830
831 END IF;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835
836 -- clear the globals
837 PA_SEARCH_GLOB.g_search_criteria := NULL;
838 PA_SEARCH_GLOB.g_competence_criteria.DELETE;
839
840 IF p_commit = FND_API.G_TRUE THEN
841 ROLLBACK TO SEARCH_PUB_RUN_OA_SEARCH;
842 END IF;
843 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_SEARCH_PUB.Run_Search'
844 , p_procedure_name => PA_DEBUG.G_Err_Stack);
845 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
846 RAISE;
847
848 END Run_Search;
849
850 END PA_SEARCH_PUB;
[Home] [Help]