1 PACKAGE BODY pa_project_set_utils AS
2 /*$Header: PAPPSUTB.pls 120.2.12000000.2 2007/10/09 13:09:21 kmaddi ship $*/
3
4 --+
5
6 /*------------------------------------------------
7 Procedure: getPartyIdName
8 Description: Returns the party_id and
9 party_name of the passed in user id
10 ------------------------------------------------*/
11 PROCEDURE getPartyIdName (p_user_id IN NUMBER
12 ,x_party_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
13 ,x_party_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
14 )
15 IS
16 l_employee_id fnd_user.employee_id%type;
17 l_customer_id fnd_user.person_party_id%type; -- 4586987 changed from customer_id to person_party_id
18 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
19 BEGIN
20 -- 4586987 changed customer_id to person_party_id
21 /*
22 SELECT employee_id, customer_id
23 INTO l_employee_id, l_customer_id
24 FROM fnd_user
25 WHERE user_id = p_user_id;
26 */
27
28 SELECT employee_id,person_party_id -- selecting person_party_id instead of customer_id
29 INTO l_employee_id,l_customer_id
30 from fnd_user
31 WHERE user_id = p_user_id;
32
33 -- 4586987 end
34
35 IF l_employee_id IS NOT NULL THEN
36 SELECT hp.party_id, hp.party_name
37 INTO x_party_id, x_party_name
38 FROM per_all_people_f pap, hz_parties hp
39 WHERE pap.person_id = l_employee_id
40 AND pap.party_id = hp.party_id
41 AND rownum = 1;
42
43 ELSIF l_customer_id IS NOT NULL THEN
44 SELECT party_id, party_name
45 INTO x_party_id, x_party_name
46 FROM hz_parties
47 WHERE party_type = 'PERSON'
48 AND party_id = l_customer_id
49 AND rownum = 1;
50
51 END IF;
52
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 x_party_id := NULL;
56 x_party_name := NULL;
57 END getPartyIdName;
58
59
60 FUNCTION get_project_set_lines(p_project_set_id IN NUMBER)
61 RETURN project_set_lines_tbl_type
62 IS
63
64 l_project_set_lines_tbl pa_project_set_utils.project_set_lines_tbl_type;
65
66 TYPE number_tbl_type IS TABLE OF NUMBER
67 INDEX BY BINARY_INTEGER;
68
69 l_project_set_id_tbl number_tbl_type;
70 l_project_id_tbl number_tbl_type;
71
72 BEGIN
73
74 SELECT project_set_id,
75 project_id
76 BULK COLLECT INTO
77 l_project_set_id_tbl,
78 l_project_id_tbl
79 FROM pa_project_set_lines
80 WHERE project_set_id = p_project_set_id;
81
82 IF l_project_set_id_tbl.COUNT > 0 THEN
83
84 FOR i IN l_project_set_id_tbl.FIRST .. l_project_set_id_tbl.LAST LOOP
85
86 l_project_set_lines_tbl(i).project_set_id := l_project_set_id_tbl(i);
87 l_project_set_lines_tbl(i).project_id := l_project_id_tbl(i);
88
89 END LOOP;
90
91 END IF;
92
93 RETURN l_project_set_lines_tbl;
94
95 END get_project_set_lines;
96
97
98 FUNCTION do_lines_exist(p_project_set_id IN NUMBER)
99 RETURN VARCHAR2
100 IS
101 l_lines_exist VARCHAR2(1);
102
103 CURSOR do_lines_exist IS
104 SELECT 'Y'
105 FROM pa_project_set_lines
106 WHERE project_set_id = p_project_set_id;
107 BEGIN
108
109 OPEN do_lines_exist;
110 FETCH do_lines_exist into l_lines_exist;
111
112 IF do_lines_exist%NOTFOUND THEN
113 l_lines_exist := 'N';
114 END IF;
115
116 CLOSE do_lines_exist;
117
118 return l_lines_exist;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 RAISE;
123 END do_lines_exist;
124
125
126 FUNCTION check_projects_in_set(p_project_set_id IN NUMBER
127 ,p_project_id IN NUMBER)
128 RETURN VARCHAR2
129 IS
130 l_exists VARCHAR2(1);
131 BEGIN
132
133 SELECT 'Y' INTO l_exists
134 FROM pa_project_set_lines
135 WHERE project_set_id = p_project_set_id
136 AND project_id = p_project_id;
137
138 RETURN l_exists;
139
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 RETURN 'N';
143
144 END check_projects_in_set;
145
146
147 FUNCTION check_security_on_set(p_party_id IN NUMBER,
148 p_user_id NUMBER,
149 p_resp_id NUMBER)
150 RETURN VARCHAR2
151 IS
152 l_update_sup_user VARCHAR2(1);
153 l_party_id HZ_PARTIES.party_id%TYPE;
154 l_party_name HZ_PARTIES.party_name%TYPE;
155 BEGIN
156
157 l_update_sup_user := PA_SECURITY_PVT.check_user_privilege(
158 p_privilege => 'PA_PROJ_SET_UPDATE'
159 ,p_object_name => null
160 ,p_object_key => null);
161
162 IF l_update_sup_user = 'T' THEN
163 RETURN 'T';
164 ELSE
165 getPartyIdName ( p_user_id => p_user_id
166 ,x_party_id => l_party_id
167 ,x_party_name => l_party_name);
168
169 IF p_party_id = l_party_id THEN
170 RETURN 'T';
171 ELSE
172 RETURN 'F';
173 END IF;
174 END IF;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 RETURN 'F';
179 END check_security_on_set;
180
181
182 FUNCTION is_name_unique(p_project_set_name IN VARCHAR2
183 ,p_project_set_id IN NUMBER := NULL)
184 RETURN VARCHAR2
185 IS
186 l_unique VARCHAR2(1);
187
188 CURSOR check_name_unique IS
189 SELECT 'N'
190 FROM pa_project_sets_tl
191 WHERE name = p_project_set_name
192 AND language = userenv('lang')
193 AND ((project_set_id <> p_project_set_id AND p_project_set_id IS NOT NULL)
194 OR p_project_set_id IS NULL);
195
196 BEGIN
197
198 OPEN check_name_unique;
199 FETCH check_name_unique into l_unique;
200
201 IF check_name_unique%NOTFOUND THEN
202 l_unique := 'Y';
203 END IF;
204
205 CLOSE check_name_unique;
206
207 return l_unique;
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 RAISE;
212 END is_name_unique;
213
214
215 FUNCTION get_proj_set_name (p_project_set_id IN NUMBER)
216 RETURN VARCHAR2
217 IS
218 l_project_set_name pa_project_sets_tl.name%type;
219
220 BEGIN
221
222 SELECT name
223 INTO l_project_set_name
224 FROM pa_project_sets_tl
225 WHERE project_set_id = p_project_set_id
226 AND userenv('lang') = language(+); --Bug 6208018
227
228 return l_project_set_name;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 RETURN NULL;
233 END get_proj_set_name;
234
235
236 PROCEDURE add_projects_to_proj_set
237 ( p_project_set_id IN pa_project_sets_b.project_set_id%TYPE
238 ,p_project_id_tbl IN SYSTEM.pa_num_tbl_type
239 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
240 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
241 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
242 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
243 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
244 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
245 ,x_project_list OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
246 ,x_project_set_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
247 )
248 IS
249
250 l_msg_index_out NUMBER;
251 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
252 l_project_id_tbl SYSTEM.pa_num_tbl_type := p_project_id_tbl;
253 l_msg_count NUMBER;
254 l_msg_data VARCHAR2(200);
255 l_exists VARCHAR2(4);
256 l_proj_set_name pa_project_sets_tl.name%TYPE;
257 l_project_list VARCHAR2(2000) := '';
258 l_project_name pa_projects_all.name%TYPE;
259 l_project_number pa_projects_all.segment1%TYPE;
260 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
261
262 CURSOR get_proj_name_number(l_project_id IN NUMBER)
263 IS
264 SELECT name, segment1
265 FROM pa_projects_all
266 WHERE project_id = l_project_id;
267
268 BEGIN
269
270 -- initialize return status to success
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272
273 IF p_init_msg_list = FND_API.G_TRUE THEN
274 fnd_msg_pub.initialize;
275 END IF;
276
277 -- get the project set name to be displayed in the informational msg if necessary
278 l_proj_set_name := get_proj_set_name(p_project_set_id);
279
280 -- loop through all project ids table to insert each project id
281 -- into the Project Set.
282 IF l_project_id_tbl.count > 0 THEN
283
284 FOR I IN 1..l_project_id_tbl.count LOOP
285
286 l_exists := check_projects_in_set(p_project_set_id, l_project_id_tbl(I));
287
288 IF l_exists = 'N' THEN
289 -- add the project to the project set, if it does not yet exist
290 PA_PROJECT_SETS_PUB.create_project_set_line
291 ( p_project_set_id => p_project_set_id
292 ,p_project_id => l_project_id_tbl(I)
293 ,x_return_status => l_return_status
294 ,x_msg_count => l_msg_count
295 ,x_msg_data => l_msg_data
296 );
297 --dbms_output.put_line ('Successfully inserted ProjectId ###' || l_project_id_tbl(I) || '### into Project Set');
298
299 ELSE
300 -- The project already exists in the set. Add the project name, project number
301 -- to the project list message to be later displayed on the project list page
302 OPEN get_proj_name_number(l_project_id_tbl(I));
303 FETCH get_proj_name_number INTO l_project_name, l_project_number;
304 CLOSE get_proj_name_number;
305
306 l_project_list := l_project_list || l_project_name || '(' || l_project_number || '), ';
307 --dbms_output.put_line ('Project already exists. Add name, number to list');
308
309 END IF;
310
311 END LOOP;
312
313 END IF;
314
315 --dbms_output.put_line ('All concatenated project list is ' || l_project_list);
316
317 x_project_list := l_project_list;
318 x_project_set_name := l_proj_set_name;
319
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 x_project_list := '';
324 x_project_set_name := '';
325 x_return_status := FND_API.G_RET_STS_ERROR;
326
327 END add_projects_to_proj_set;
328
329
330 PROCEDURE Check_PartyName_Or_Id(
331 p_party_id IN NUMBER,
332 p_party_name IN VARCHAR2,
333 p_check_id_flag IN VARCHAR2,
334 x_party_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
335 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
336 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
337 IS
338
339 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
340
341 BEGIN
342
343 -- Initialize the Error Stack
344 IF l_enable_log = 'Y' THEN
345 PA_DEBUG.init_err_stack('PA_PROJECT_SET_UTILS.Check_PartyName_Or_Id');
346 END IF;
347
348 -- Initialize the return status to success
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350
351 IF p_party_id IS NOT NULL THEN
352 IF p_check_id_flag = 'Y' THEN
353 SELECT party_id
354 INTO x_party_id
355 FROM hz_parties
356 WHERE party_id = p_party_id;
357 ELSE
358 x_party_id := p_party_id;
359 END IF;
360 ELSE
361 SELECT party_id
362 INTO x_party_id
363 FROM hz_parties
364 WHERE party_name = p_party_name;
365 END IF;
366
367 EXCEPTION
368 WHEN NO_DATA_FOUND THEN
369 x_party_id := NULL;
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 x_error_msg_code := 'PA_PARTY_INVALID';
372 WHEN TOO_MANY_ROWS then
373 x_party_id := NULL;
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 x_error_msg_code := 'PA_MULTIPLE_PARTY';
376 WHEN OTHERS THEN
377 -- Set the exception Message and the stack
378 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_SET_UTILS.Check_PartyName_Or_Id'
379 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
381 RAISE;
382 END Check_PartyName_Or_Id;
383
384
385 PROCEDURE Check_ProjectSetName_Or_Id(
386 p_project_set_id IN NUMBER
387 ,p_project_set_name IN VARCHAR2
388 ,p_check_id_flag IN VARCHAR2
389 ,x_project_set_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
390 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
391 ,x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
392 IS
393 l_current_id NUMBER := NULL;
394 l_num_ids NUMBER := 0;
395 l_id_found_flag VARCHAR(1) := 'N';
396 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
397
398 CURSOR p_ids IS
399 SELECT project_set_id
400 FROM pa_project_sets_vl
401 WHERE upper(name) = upper(p_project_set_name);
402 BEGIN
403
404 if (p_project_set_id = FND_API.G_MISS_NUM) OR (p_project_set_id is NULL) then
405 if (p_project_set_name is not NULL) then
406 SELECT project_set_id
407 INTO x_project_set_id
408 FROM pa_project_sets_vl
409 WHERE upper(name) = upper(p_project_set_name);
410 else
411 x_project_set_id := NULL;
412 end if;
413
414 else
415 IF p_check_id_flag = 'Y' THEN
416 SELECT project_set_id
417 INTO x_project_set_id
418 FROM pa_project_sets_vl
419 WHERE project_set_id = p_project_set_id;
420
421 ELSIF (p_check_id_flag='N') THEN
422 x_project_set_id := p_project_set_id;
423
424 ELSIF (p_check_id_flag = 'A') THEN
425 IF (p_project_set_name IS NULL) THEN
426 -- Return a null ID since the name is null.
427 x_project_set_id := NULL;
428 ELSE
429
430 -- Find the ID which matches the Name passed
431 OPEN p_ids;
432 LOOP
433 FETCH p_ids INTO l_current_id;
434 EXIT WHEN p_ids%NOTFOUND;
435 IF (l_current_id = p_project_set_id) THEN
436 l_id_found_flag := 'Y';
437 x_project_set_id := p_project_set_id;
438 END IF;
439 END LOOP;
440 l_num_ids := p_ids%ROWCOUNT;
441 CLOSE p_ids;
442
443 IF (l_num_ids = 0) THEN
444 -- No IDs for name
445 RAISE NO_DATA_FOUND;
446 ELSIF (l_num_ids = 1) THEN
447 -- Since there is only one ID for the name use it.
448 x_project_set_id := l_current_id;
449 ELSIF (l_id_found_flag = 'N') THEN
450 -- More than one ID for the name and none of the IDs matched
451 -- the ID passed in.
452 RAISE TOO_MANY_ROWS;
453 END IF;
454 END IF;
455
456 ELSE
457 x_project_set_id := NULL;
458 END IF;
459
460 end if;
461
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463
464 EXCEPTION
465 when NO_DATA_FOUND then
466 x_project_set_id := NULL;
467 x_return_status := FND_API.G_RET_STS_ERROR;
468 x_error_msg_code := 'PA_PROJECT_SET_INVALID';
469 when TOO_MANY_ROWS then
470 x_project_set_id := NULL;
471 x_return_status := FND_API.G_RET_STS_ERROR;
472 x_error_msg_code := 'PA_PROJECT_SET_INVALID';
473 when OTHERS then
474 x_project_set_id := NULL;
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_SETS_UTILS', p_procedure_name => 'CHECK_PROJECTSETNAME_OR_ID');
477 raise;
478
479 END Check_ProjectSetName_Or_Id;
480
481 FUNCTION is_party_internal (p_party_id IN NUMBER) RETURN VARCHAR2
482 IS
483 CURSOR c1 IS
484 SELECT orig_system_reference
485 FROM hz_parties
486 WHERE party_id = p_party_id;
487 v_c1 c1%ROWTYPE;
488
489 BEGIN
490 OPEN c1;
491 FETCH c1 INTO v_c1;
492 CLOSE c1;
493
494 IF SUBSTR(v_c1.orig_system_reference, 1, 4) = 'PER:' THEN
495 RETURN 'Y';
496 ELSE
497 RETURN 'N';
498 END IF;
499
500 END is_party_internal;
501
502 END PA_PROJECT_SET_UTILS;
[Home] [Help]