1 PACKAGE BODY pa_project_search_utils AS
2 /*$Header: PAPRSUTB.pls 120.2 2006/02/28 05:07:45 dthakker noship $*/
3
4 ------------------------------------------------------------
5 -- Procedure: Check_Customer_Name_Or_Id
6 -- Copied From: PA_CUSTOMERS_CONTACTS_UTILS package
7 -- Description:
8 -- Modify the logic to return at least one id
9 -- if more than one is found for Project Lists
10 -- search enhancement name to id conversion
11 -- Only retain code that's using name to get Id
12 ------------------------------------------------------------
13
14 PROCEDURE Check_Customer_Name_Or_Id
15 ( p_customer_id IN NUMBER
16 ,p_customer_name IN VARCHAR2
17 ,x_customer_id OUT NOCOPY NUMBER
18 ,x_return_status OUT NOCOPY VARCHAR2
19 ,x_error_msg_code OUT NOCOPY VARCHAR2
20 )
21 IS
22 l_current_id NUMBER := NULL;
23 l_num_ids NUMBER := 0;
24
25 CURSOR c_ids IS
26 SELECT customer_id
27 FROM pa_customers_v
28 WHERE upper(customer_name) = upper(p_customer_name) and status = 'A';
29
30 BEGIN
31
32 if (p_customer_id = FND_API.G_MISS_NUM) OR (p_customer_id is NULL) then
33 if (p_customer_name is not NULL) then
34
35 OPEN c_ids;
36 LOOP
37 FETCH c_ids INTO l_current_id;
38 EXIT WHEN c_ids%NOTFOUND;
39 x_customer_id := l_current_id;
40 END LOOP;
41 l_num_ids := c_ids%ROWCOUNT;
42 CLOSE c_ids;
43
44 IF (l_num_ids = 0) THEN
45 -- No IDs for name
46 RAISE NO_DATA_FOUND;
47 END IF;
48 else
49 x_customer_id := NULL;
50 end if;
51
52 end if;
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 EXCEPTION
56 when NO_DATA_FOUND then
57 x_customer_id := NULL;
58 x_return_status := FND_API.G_RET_STS_ERROR;
59 x_error_msg_code := 'PA_CUSTOMER_ID_INVALID';
60
61 when OTHERS then
62 x_customer_id := NULL;
63 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
64 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_SEARCH_UTILS', p_procedure_name => 'CHECK_CUSTOMER_NAME_OR_ID');
65 raise;
66
67 END Check_Customer_Name_Or_Id;
68
69
70 ------------------------------------------------------------
71 -- Procedure: Check_PersonName_Or_Id
72 -- Referred From: PA_RESOURCE_UTILS package
73 -- Description:
74 -- Using the passed resource name, this API
75 -- will return the Id based on cursor using
76 -- PER table and HZ tables as the resource can
77 -- be an internal or external person.
78 -- The logic is that we will return at least
79 -- one Id if more than one is found.
80 -- This API will return the login user Id if
81 -- no matching Id is found.
82 ------------------------------------------------------------
83 PROCEDURE Check_PersonName_Or_Id(
84 p_resource_id IN NUMBER
85 ,p_resource_name IN VARCHAR2
86 ,x_resource_id OUT NOCOPY NUMBER
87 ,x_resource_type_id OUT NOCOPY NUMBER
88 ,x_return_status OUT NOCOPY VARCHAR2
89 ,x_error_msg_code OUT NOCOPY VARCHAR2
90 )
91 IS
92 l_current_id NUMBER := NULL;
93 l_num_ids NUMBER := 0;
94
95 CURSOR r_ids IS
96 SELECT person_id
97 FROM per_all_people_f
98 WHERE full_name = p_resource_name
99 AND trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date);
100
101 CURSOR p_ids IS
102 SELECT party_id
103 FROM pa_party_resource_details_v
104 WHERE party_name = p_resource_name
105 AND party_type = 'PERSON' -- Added for Bug 4745885
106 AND trunc(sysdate) between trunc(start_date)
107 and trunc(nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')));
108
109 CURSOR login_id IS
110 SELECT employee_id
111 FROM fnd_user
112 WHERE user_id = FND_GLOBAL.user_id;
113
114 BEGIN
115
116 IF (p_resource_id IS NULL) OR (p_resource_id = FND_API.G_MISS_NUM) THEN
117
118 if (p_resource_name IS NOT NULL) then
119
120 OPEN r_ids;
121 LOOP
122 FETCH r_ids INTO l_current_id;
123 EXIT WHEN r_ids%NOTFOUND;
124 x_resource_id := l_current_id;
125 x_resource_type_id := 101;
126 END LOOP;
127 l_num_ids := r_ids%ROWCOUNT;
128 CLOSE r_ids;
129
130 IF (l_num_ids = 0) THEN
131 -- No IDs for name when checking HR employee name
132 -- Now try to find external person name
133 OPEN p_ids;
134 LOOP
135 FETCH p_ids INTO l_current_id;
136 EXIT WHEN p_ids%NOTFOUND;
137 x_resource_id := l_current_id;
138 x_resource_type_id := 112;
139 END LOOP;
140 l_num_ids := p_ids%ROWCOUNT;
141 CLOSE p_ids;
142 END IF;
143
144 -- login specific to Project List Search
145 -- returns the Id of the login user
146 IF (l_num_ids = 0) THEN
147 -- if internal
148 OPEN login_id;
149 FETCH login_id INTO l_current_id;
150 x_resource_id := l_current_id;
151 x_resource_type_id := 101;
152 CLOSE login_id;
153
154 IF l_current_id is null THEN
155 -- if external
156 x_resource_id := PA_UTILS.get_party_id(FND_GLOBAL.user_id);
157 x_resource_type_id := 112;
158 END IF;
159 END IF;
160
161 else
162 x_resource_id := NULL;
163 x_resource_type_id := NULL;
164 end if;
165
166 END IF;
167
168 EXCEPTION
169 when NO_DATA_FOUND then
170 x_resource_id := NULL;
171 x_resource_type_id := NULL;
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 x_error_msg_code := 'PA_RESOURCE_INVALID_AMBIGUOUS';
174
175 when OTHERS then
176 x_resource_id := NULL;
177 x_resource_type_id := NULL;
178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
179 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_SEARCH_UTILS', p_procedure_name => 'Check_PersonName_Or_Id');
180 raise;
181
182 END Check_PersonName_Or_Id;
183
184
185 ------------------------------------------------------------
186 -- Procedure: Check_ResourceName_Or_Id
187 -- Description:
188 -- Using the passed person name, this API
189 -- will return the Id based on cursor based on
190 -- all past, current and future resource.
191 -- The logic is that we will return at least
192 -- one Id if more than one is found.
193 -- This API will return the login user Id if
194 -- no matching Id is found.
195 ------------------------------------------------------------
196 PROCEDURE Check_ResourceName_Or_Id(
197 p_person_id IN NUMBER
198 ,p_person_name IN VARCHAR2
199 ,x_person_id OUT NOCOPY NUMBER
200 ,x_return_status OUT NOCOPY VARCHAR2
201 ,x_error_msg_code OUT NOCOPY VARCHAR2
202 )
203 IS
204 l_current_id NUMBER := NULL;
205 l_num_ids NUMBER := NULL;
206
207 CURSOR r_ids IS
208 SELECT person_id
209 FROM per_all_people_f
210 WHERE full_name = p_person_name;
211
212 CURSOR login_id IS
213 SELECT employee_id
214 FROM fnd_user
215 WHERE user_id = FND_GLOBAL.user_id;
216
217 BEGIN
218
219 IF (p_person_id IS NULL) OR (p_person_id = FND_API.G_MISS_NUM) THEN
220
221 if (p_person_name IS NOT NULL) then
222
223 OPEN r_ids;
224 LOOP
225 FETCH r_ids INTO l_current_id;
226 EXIT WHEN r_ids%NOTFOUND;
227 x_person_id := l_current_id;
228 END LOOP;
229 l_num_ids := r_ids%ROWCOUNT;
230 CLOSE r_ids;
231
232 -- login specific to Search
233 -- returns the Id of the login user
234 IF (l_num_ids = 0) THEN
235
236 OPEN login_id;
237 FETCH login_id INTO l_current_id;
238 x_person_id := l_current_id;
239 CLOSE login_id;
240
241 END IF;
242
243 else
244 x_person_id := NULL;
245 end if;
246
247 END IF;
248
249 EXCEPTION
250 when NO_DATA_FOUND then
251 x_person_id := NULL;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 x_error_msg_code := 'PA_RESOURCE_INVALID_AMBIGUOUS';
254
255 when OTHERS then
256 x_person_id := NULL;
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_SEARCH_UTILS', p_procedure_name => 'Check_PersonName_Or_Id');
259 raise;
260
261 END Check_ResourceName_Or_Id;
262
263
264 ------------------------------------------------------------
265 -- Procedure: CONVERT_NAMETOID
266 -- Description:
267 -- Convert a list of parameter names into
268 -- the corresponding Ids. Used by Project Lists
269 -- Search.
270 ------------------------------------------------------------
271 PROCEDURE Convert_NameToId
272 ( p_param_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type
273 ,p_param_value_tbl IN SYSTEM.pa_varchar2_240_tbl_type
274 ,p_param_value2_tbl IN SYSTEM.pa_varchar2_240_tbl_type
275 ,p_init_msg_list IN VARCHAR2
276 ,x_param_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
277 ,x_return_status OUT NOCOPY VARCHAR2
278 ,x_msg_count OUT NOCOPY NUMBER
279 ,x_msg_data OUT NOCOPY VARCHAR2
280 )
281 IS
282
283 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
284 l_msg_count NUMBER;
285 l_msg_data VARCHAR2(200);
286 l_param_type_tbl SYSTEM.pa_varchar2_30_tbl_type := p_param_type_tbl;
287 l_param_value_tbl SYSTEM.pa_varchar2_240_tbl_type := p_param_value_tbl;
288 l_param_value2_tbl SYSTEM.pa_varchar2_240_tbl_type := p_param_value2_tbl;
289 l_id NUMBER := null;
290 l_type_id NUMBER := null;
291
292 BEGIN
293
294 -- initialize return status to success
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296
297 IF p_init_msg_list = FND_API.G_TRUE THEN
298 fnd_msg_pub.initialize;
299 END IF;
300
301 -- if there is at least one item in the array, process the name to id
302 -- conversion
303
304 IF l_param_type_tbl.EXISTS(1) THEN
305
306 FOR i IN l_param_type_tbl.FIRST..l_param_type_tbl.LAST LOOP
307
308 IF l_param_type_tbl(i) = 'PROJECTSET' THEN
309
310 -- call API to validate project set name
311 PA_PROJECT_SET_UTILS.Check_ProjectSetName_Or_Id(
312 p_project_set_id => null
313 ,p_project_set_name => l_param_value_tbl(i)
314 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
315 ,x_project_set_id => l_id
316 ,x_return_status => l_return_status
317 ,x_error_msg_code => l_msg_data );
318
319 ELSIF l_param_type_tbl(i) = 'ROLE' THEN
320
321 -- call API to validate role name
322 PA_ROLE_UTILS.Check_Role_Name_Or_Id(
323 p_role_id => null
324 ,p_role_name => l_param_value_tbl(i)
325 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
326 ,x_role_id => l_id
327 ,x_return_status => l_return_status
328 ,x_error_message_code => l_msg_data);
329
330 ELSIF l_param_type_tbl(i) = 'CUSTOMER' THEN
331
332 -- call API to validate customer name
333 PA_PROJECT_SEARCH_UTILS.CHECK_CUSTOMER_NAME_OR_ID(
334 p_customer_id => null
335 ,p_customer_name => l_param_value_tbl(i)
336 ,x_customer_id => l_id
337 ,x_return_status => l_return_status
338 ,x_error_msg_code => l_msg_data);
339
340 ELSIF l_param_type_tbl(i) = 'PERSON' OR l_param_type_tbl(i) = 'MANAGER' THEN
341 -- call API to validate person or manager name
342 PA_PROJECT_SEARCH_UTILS.Check_PersonName_Or_Id(
343 p_resource_id => null
344 ,p_resource_name => l_param_value_tbl(i)
345 ,x_resource_id => l_id
346 ,x_resource_type_id => l_type_id
347 ,x_return_status => l_return_status
348 ,x_error_msg_code => l_msg_data);
349
350 ELSIF l_param_type_tbl(i) = 'CLASSCODE' THEN
351
352 -- call API to validate class category and code
353 PA_PROJECTS_MAINT_UTILS.Check_ClassCode_Name_Or_Id(
354 p_classcode_id => null
355 ,p_classcode_name => l_param_value_tbl(i)
356 ,p_classcategory => l_param_value2_tbl(i)
357 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
358 ,x_classcode_id => l_id
359 ,x_return_status => l_return_status
360 ,x_error_message_code => l_msg_data);
361
362 ELSE
363 -- no param type or invalid param type - set Id to null
364 x_param_id_tbl(i) := null;
365
366 END IF;
367
368
369 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
370 x_param_id_tbl(i) := -999;
371 -- also add message to error stack
372 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
373 ,p_msg_name => l_msg_data);
374 ELSE
375 -- store value in the out parameter Id table
376 x_param_id_tbl(i) := l_id;
377 END IF;
378
379
380 END LOOP; --end of loop for all the parameter types
381
382 END IF;
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 RAISE;
388
389 END Convert_NameToId;
390
391 ------------------------------------------------------------
392 -- Procedure: Get_Perf_Measures
393 -- Description:
394 -- Wrapper API for Project Performances
395 ------------------------------------------------------------
396 PROCEDURE Get_Perf_Measures
397 (
398 p_source_api IN VARCHAR2,
399 p_project_id IN NUMBER,
400 p_measure_codes_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
401 p_measure_set_codes_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT NULL, -- added for bug4361663
402 p_timeslices_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT NULL, -- added for bug4361663
403 p_measure_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT NULL, -- added for bug4361663
404 x_measure_values_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
405 x_exception_indicator_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE,
406 -- x_exception_labels_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_240_TBL_TYPE,
407 x_sec_ret_code OUT NOCOPY VARCHAR2,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2
411 )
412 IS
413 l_sec_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
414 l_sec_msg_count NUMBER := 0;
415 l_sec_msg_data VARCHAR2(1) := NULL;
416
417 BEGIN
418
419 x_sec_ret_code := 'F';
420
421 PA_SECURITY_PVT.check_user_privilege
422 (
423 p_privilege => 'PA_MY_PROJ_MAIN_TAB_PSI_COL',
424 p_object_name => 'PA_PROJECTS',
425 p_object_key => p_project_id,
426 x_ret_code => x_sec_ret_code,
427 x_return_status => l_sec_return_status,
428 x_msg_count => l_sec_msg_count,
429 x_msg_data => l_sec_msg_data
430 );
431
432 IF (x_sec_ret_code = 'T')
433 THEN
434 IF (p_source_api = 'FM')
435 THEN
436 PJI_REP_MEASURE_UTIL.Get_Financial_Measures
437 (
438 p_project_id => p_project_id,
439 p_measure_codes_tbl => p_measure_codes_tbl,
440 p_measure_set_codes_tbl => p_measure_set_codes_tbl, -- Added for bug4361663
441 p_timeslices_tbl => p_timeslices_tbl, -- Added for bug4361663
442 p_measure_id_tbl => p_measure_id_tbl, -- Added for bug4361663
443 x_measure_values_tbl => x_measure_values_tbl,
444 x_exception_indicator_tbl => x_exception_indicator_tbl,
445 -- x_exception_labels_tbl => x_exception_labels_tbl,
446 x_return_status => x_return_status,
447 x_msg_count => x_msg_count,
448 x_msg_data => x_msg_data
449 );
450 ELSIF (p_source_api = 'AC')
451 THEN
452 PJI_REP_MEASURE_UTIL.Get_Activity_Measures
453 (
454 p_project_id => p_project_id,
455 p_measure_codes_tbl => p_measure_codes_tbl,
456 p_measure_set_codes_tbl => p_measure_set_codes_tbl, -- Added for bug4361663
457 p_timeslices_tbl => p_timeslices_tbl, -- Added for bug4361663
458 p_measure_id_tbl => p_measure_id_tbl, -- Added for bug4361663
459 x_measure_values_tbl => x_measure_values_tbl,
460 x_exception_indicator_tbl => x_exception_indicator_tbl,
461 -- x_exception_labels_tbl => x_exception_labels_tbl,
462 x_return_status => x_return_status,
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data
465 );
466 END IF;
467 END IF;
468
469 EXCEPTION
470 when OTHERS then
471 raise;
472
473 END Get_Perf_Measures;
474
475 END PA_PROJECT_SEARCH_UTILS;
[Home] [Help]