1 PACKAGE BODY PA_PROJ_ELEMENTS_UTILS AS
2 /* $Header: PATSK1UB.pls 120.15.12010000.3 2008/11/28 11:09:39 vgovvala ship $ */
3
4 Invalid_Arg_Exc_WP Exception ;
5 g_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_elements_utils';
6
7 -- Added for Bug 6156686
8 TYPE l_lookup_cache_tbl_typ IS TABLE OF VARCHAR2(80)
9 INDEX BY VARCHAR2(70);
10
11 l_lookup_cache_tbl l_lookup_cache_tbl_typ;
12
13 TYPE l_fndlkp_cache_tbl_typ IS TABLE OF VARCHAR2(80)
14 INDEX BY VARCHAR2(70);
15
16 l_fndlkp_cache_tbl l_lookup_cache_tbl_typ;
17
18 PROCEDURE SetGlobalStrucVerId ( p_structure_version_id IN NUMBER )
19 IS
20 BEGIN
21 PA_PROJ_ELEMENTS_UTILS.g_Struc_Ver_Id := p_structure_version_id;
22 END SetGlobalStrucVerId;
23
24 FUNCTION GetGlobalStrucVerId RETURN NUMBER
25 IS
26 BEGIN
27 RETURN ( PA_PROJ_ELEMENTS_UTILS.g_Struc_Ver_Id );
28 END GetGlobalStrucVerId;
29
30 --This function checks if a WORKPLAN (split or shared) task
31 --may be updtaed by the logged in user.
32 --Do not use this function for split FINANCIAL tasks
33 -- 5/13/05: DHI ER: Allowing multiple user to update task assignment
34 -- Added p_require_lock_flag parameter defauled to 'Y'.
35 -- 8/29/05: R12 Bug fix 4533152: Added p_add_error_flag paramter
36 -- defaulted to 'N'.
37 FUNCTION Check_Edit_Task_Ok(p_project_id IN NUMBER,
38 p_structure_version_id IN NUMBER,
39 p_curr_struct_version_id IN NUMBER,
40 p_element_id IN NUMBER := NULL,
41 p_require_lock_flag IN VARCHAR := 'Y',
42 p_add_error_flag IN VARCHAR := 'N')
43 RETURN VARCHAR2
44 IS
45 l_ret_code varchar2(1);
46 l_ret_stat varchar2(1);
47 l_msg_count NUMBER;
48 l_msg_data varchar2(250);
49
50 cursor c1 IS
51 select '1' from pa_projects_all
52 where project_id = p_project_id
53 and template_flag = 'Y';
54 l_dummy VARCHAR2(1);
55 -- bug 4239490
56 l_lock VARCHAR2(1);
57 l_is_task_manager_flag VARCHAR2(1);
58 l_version_enable_flag VARCHAR2(1);
59 --bug 4239490
60 BEGIN
61
62 OPEN c1;
63 FETCH c1 into l_dummy;
64 IF c1%FOUND THEN
65 CLOSE c1;
66 return 'Y';
67 END IF;
68 CLOSE c1;
69
70 --check if this is published
71 -- IF (PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(p_project_id, p_structure_version_id) = 'Y') THEN
72 -- return 'N';
73 -- END IF;
74 IF ('N' = PA_PROJECT_STRUCTURE_UTILS.check_edit_wp_ok(p_project_id, p_structure_version_id)) THEN
75 -- Bug 4533152
76 IF p_add_error_flag = 'Y' THEN
77 PA_UTILS.ADD_MESSAGE
78 (p_app_short_name => 'PA',
79 p_msg_name => 'PA_CHANGE_PUB_VER_ERR');
80 END IF;
81 -- End of Bug 4533152
82 return 'N';
83 END IF;
84 /* commented out--bug 3071008
85 since linking is not shipped
86 --check if this is linked element version
87 If (p_structure_version_id = p_curr_struct_version_id) THEN
88 --Same structure
89 return 'Y';
90 END IF;
91 */
92
93 l_version_enable_flag := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(p_project_id);
94 --check if this is locked
95 --bug 3071008: added condition for versioned projects only
96 IF (l_version_enable_flag = 'Y') THEN
97 -- The following API returns 'Y' if locked by current user , '0' if Locked by some other user and 'N' if no one is locking
98 -- If no one is locking we can still allow editing.
99 --bug 4239490
100 l_lock := PA_PROJECT_STRUCTURE_UTILS.IS_STRUC_VER_LOCKED_BY_USER(FND_GLOBAL.USER_ID, p_structure_version_id);
101
102 -- Bug Fix: 4725901
103 -- DHI ER:
104 -- Need to consider the p_require_lock_flag.
105 -- Currently the flow is returning N which is preventing other users
106 -- to update/create/delete the task assignments using the PA_TASK_ASSIGNMENT_PUB
107 --
108 -- In order to allow the users to update/delete/create the Task Assignments
109 -- even though the structure is locked we need to consider the p_required_lock flag.
110 -- The flow will be
111 -- If the structure is locked AND p_required_lock_flag is Y then only return N
112 -- In case of DHI ER the call to this procedure from
113 -- PA_TASK_ASSIGNMENT_UTILS.check_edit_task_ok will pass N. So the error is
114 -- bypassed.
115 --
116 -- IF (l_lock = 'O') THEN --bug 4239490
117
118 IF ((l_lock = 'O') AND (p_require_lock_flag = 'Y')) THEN --bug 4725901
119
120 -- Bug 4533152
121 IF p_add_error_flag = 'Y' THEN
122 PA_UTILS.ADD_MESSAGE
123 (p_app_short_name => 'PA',
124 p_msg_name => 'PA_STR_LOCKED_BY_OTHER');
125 END IF;
126 -- End of Bug 4533152
127 return 'N';
128
129 END IF;
130
131 END IF;
132
133 -- This API can be called from so many other places also,where this p_element_id may not be available
134 -- Hence PA_PROGRESS_UTILS.IS_TASK_MANAGER API will return 'N'
135 -- which may produce undesirable results
136 -- Here ,Introduced the condition to call this API if and only if p_element_id is passed
137
138 IF p_element_id is NOT NULL -- 4267419 <avaithia> tracking
139 THEN
140 --bug 4239490 No access if there is no lock and the user is not task manager.
141 l_is_task_manager_flag := PA_PROGRESS_UTILS.IS_TASK_MANAGER(p_element_id, p_project_id, FND_GLOBAL.USER_ID);
142
143 IF l_version_enable_flag = 'Y' AND
144 l_is_task_manager_flag = 'N' and l_lock = 'N' AND
145 p_require_lock_flag = 'Y' -- 5/13/05: DHI ER: Allowing multiple user to update task assignment
146 -- If structure version is NOT locked by another user,
147 -- also return 'Y';
148 THEN
149 -- Bug 4533152
150 IF p_add_error_flag = 'Y' THEN
151 PA_UTILS.ADD_MESSAGE
152 (p_app_short_name => 'PA',
153 p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
154 END IF;
155 -- End of Bug 4533152
156 return 'N';
157 END IF;
158 --end bug 4239490
159 END IF ; -- 4267419 <avaithia> tracking
160
161 --check if user can edit
162 IF (PA_SECURITY_PVT.check_user_privilege('PA_PAXPREPR_OPT_WORKPLAN_STR', 'PA_PROJECTS', p_project_id
163 , 'N') -- Fix for Bug # 4319137.
164 <> FND_API.G_TRUE) THEN
165 IF p_element_id is NOT NULL THEN
166 IF (l_is_task_manager_flag = 'Y') THEN --bug 4239490 replaced the is_task_manager api call with the variable.
167 IF (PA_SECURITY_PVT.check_user_privilege('PA_TASKS_UPDATE_DETAILS', 'PA_PROJECTS', p_project_id
168 , 'N') -- Fix for Bug # 4319137.
169 <> FND_API.G_TRUE) THEN
170 -- Bug 4533152
171 IF p_add_error_flag = 'Y' THEN
172 PA_UTILS.ADD_MESSAGE
173 (p_app_short_name => 'PA',
174 p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
175 END IF;
176 -- End of Bug 4533152
177 return 'N';
178 END IF;
179 ELSE
180 -- Bug 4533152
181 IF p_add_error_flag = 'Y' THEN
182 PA_UTILS.ADD_MESSAGE
183 (p_app_short_name => 'PA',
184 p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
185 END IF;
186 -- End of Bug 4533152
187 return 'N';
188 END IF;
189 ELSE
190 -- Bug 4533152
191 IF p_add_error_flag = 'Y' THEN
192 PA_UTILS.ADD_MESSAGE
193 (p_app_short_name => 'PA',
194 p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
195 END IF;
196 -- End of Bug 4533152
197 return 'N';
198 END IF;
199 END IF;
200 return 'Y';
201 END Check_Edit_Task_Ok;
202
203
204 PROCEDURE Get_Structure_Attributes(
205 p_element_version_id NUMBER,
206 p_structure_type_code VARCHAR2 := 'WORKPLAN',
207 x_task_name OUT NOCOPY VARCHAR2, -- 4537865
208 x_task_number OUT NOCOPY VARCHAR2, -- 4537865
209 x_task_version_id OUT NOCOPY NUMBER, -- 4537865
210 x_structure_version_name OUT NOCOPY VARCHAR2, -- 4537865
211 x_structure_version_number OUT NOCOPY NUMBER, -- 4537865
212 x_structure_name OUT NOCOPY VARCHAR2, -- 4537865
213 x_structure_number OUT NOCOPY VARCHAR2, -- 4537865
214 x_structure_id OUT NOCOPY NUMBER, -- 4537865
215 x_structure_type_code_name OUT NOCOPY VARCHAR2, -- 4537865
216 x_structure_version_id OUT NOCOPY NUMBER, -- 4537865
217 x_project_id OUT NOCOPY NUMBER -- 4537865
218
219 ) AS
220
221 CURSOR cur_elem_ver
222 IS
223 SELECT ppe.name, ppe.element_number, ppev.parent_structure_version_id, ppe.project_id
224 FROM pa_proj_element_versions ppev,
225 pa_proj_elements ppe
226 WHERE ppe.proj_element_id = ppev.proj_element_id
227 AND ppev.element_version_id = p_element_version_id
228 AND ppev.object_type = 'PA_TASKS';
229
230 /* Bug 2680486 -- Performance changes -- Added one more condition to compare project_id in the following cursor.
231 The project_id is got from pa_proj_element_versions*/
232
233 CURSOR cur_elem_ver_stru( p_version_id NUMBER )
234 IS
235 SELECT ppevs.version_number, ppevs.name, ppe.element_number, ppe.name, ppe.proj_element_id, ppe.project_id
236 FROM pa_proj_elem_ver_structure ppevs,
237 pa_proj_elements ppe
238 WHERE ppevs.element_version_id = p_version_id
239 AND ppe.proj_element_id = ppevs.proj_element_id
240 AND ppevs.project_id = (select project_id
241 from pa_proj_element_versions
242 where element_version_id = p_version_id) ;
243
244 CURSOR cur_lookups
245 IS
246 SELECT meaning
247 FROM pa_lookups
248 WHERE lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
249 AND lookup_code = p_structure_type_code;
250
251
252 x_version_id NUMBER;
253 BEGIN
254 OPEN cur_elem_ver;
255 FETCH cur_elem_ver INTO x_task_name, x_task_number, x_structure_version_id,x_project_id;
256 IF cur_elem_ver%FOUND THEN
257 --If element is TASK Get the structure details. This structure is parent of the task p_elemnt_version_id.
258 x_version_id := x_structure_version_id;
259 --here we get the task.
260 x_task_version_id := p_element_version_id;
261 ELSE
262 --If element is STRUCTURE. Pass back only STRUCTURE details.
263 x_version_id := p_element_version_id;
264 x_structure_version_id := p_element_version_id;
265 END IF;
266 CLOSE cur_elem_ver;
267
268 OPEN cur_elem_ver_stru( x_version_id );
269 FETCH cur_elem_ver_stru INTO x_structure_version_number, x_structure_version_name,
270 x_structure_number, x_structure_name, x_structure_id, x_project_id;
271 CLOSE cur_elem_ver_stru;
272
273 OPEN cur_lookups;
274 FETCH cur_lookups INTO x_structure_type_code_name;
275 CLOSE cur_lookups;
276
277 -- 4537865 I havent changed WHEN OTHERS block because existing code wanted to ignore it.
278 -- Discussed with Rajnish reg. this
279
280 EXCEPTION WHEN OTHERS THEN
281 null;
282 END Get_Structure_Attributes;
283
284
285 FUNCTION latest_published_ver_id(
286 p_project_id NUMBER,
287 p_structure_type_code VARCHAR2 := 'WORKPLAN'
288 ) RETURN NUMBER IS
289 CURSOR cur_elem_ver_stru
290 IS
291 SELECT element_version_id
292 FROM pa_proj_elem_ver_structure ppevs,
293 pa_proj_structure_types ppst,
294 pa_structure_types pst
295 WHERE ppevs.project_id = p_project_id
296 AND latest_eff_published_flag = 'Y'
297 AND ppst.proj_element_id = ppevs.proj_element_id
298 AND ppst.structure_type_id = pst.structure_type_id
299 AND pst.structure_type_class_code = p_structure_type_code;
300 v_element_version_id NUMBER;
301 BEGIN
302 OPEN cur_elem_ver_stru;
303 FETCH cur_elem_ver_stru INTO v_element_version_id;
304 CLOSE cur_elem_ver_stru;
305 RETURN NVL( v_element_version_id, -1);
306 END latest_published_ver_id;
307
308 -- POST K:Added for Shortcut to get the last updated Workplan version
309
310 Procedure Get_Last_Upd_Working_Wp_Ver(
311 p_project_id IN pa_proj_elem_ver_structure.project_id%TYPE
312 ,x_pev_structure_id OUT NOCOPY pa_proj_elem_ver_structure.pev_structure_id%TYPE -- 4537865
313 ,x_element_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE -- 4537865
314 ,x_element_version_name OUT NOCOPY pa_proj_elem_ver_structure.name%TYPE -- 4537865
315 ,x_record_version_number OUT NOCOPY pa_proj_elem_ver_structure.record_version_number%TYPE -- 4537865
316 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
317 ,x_msg_count OUT NOCOPY NUMBER -- 4537865
318 ,x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
319 AS
320
321 l_msg_index_out NUMBER;
322 l_debug_mode VARCHAR2(1);
323
324 l_data VARCHAR2(2000); -- 4537865
325
326 l_debug_level2 CONSTANT NUMBER := 2;
327 l_debug_level3 CONSTANT NUMBER := 3;
328 l_debug_level4 CONSTANT NUMBER := 4;
329 l_debug_level5 CONSTANT NUMBER := 5;
330 l_module_name VARCHAR2(100) := 'pa.plsql.PA_PROJ_ELEMENTS_UTILS';
331
332 l_date1 DATE ;
333 l_pev_structure_id1 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
334 l_element_version_id1 pa_proj_elem_ver_structure.element_version_id%TYPE ;
335 l_element_version_name1 pa_proj_elem_ver_structure.name%TYPE ;
336 l_record_version_number1 pa_proj_elem_ver_structure.record_version_number%TYPE ;
337
338 l_date2 DATE ;
339 l_pev_structure_id2 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
340 l_element_version_id2 pa_proj_elem_ver_structure.element_version_id%TYPE ;
341 l_element_version_name2 pa_proj_elem_ver_structure.name%TYPE ;
342 l_record_version_number2 pa_proj_elem_ver_structure.record_version_number%TYPE ;
343
344 CURSOR cur_elem_ver_stru1
345 IS
346 SELECT ppevs.last_update_date,
347 ppevs.pev_structure_id,
348 ppevs.element_version_id,
349 ppevs.name,
350 ppevs.record_version_number
351 FROM pa_proj_elem_ver_structure ppevs,
352 pa_proj_structure_types ppst,
353 pa_structure_types pst
354 WHERE ppevs.project_id = p_project_id
355 AND ppevs.status_code = 'STRUCTURE_WORKING'
356 AND ppst.proj_element_id = ppevs.proj_element_id
357 AND ppst.structure_type_id = pst.structure_type_id
358 AND pst.structure_type_class_code = 'WORKPLAN'
359 ORDER BY ppevs.last_update_date desc ;
360
361 --bug#2956325 :
362 --Added the below mentioned cursor to take care of
363 --scenerios such as delete,indent,outdent,move,add
364 --etc.The above cursor doesn't take care of these
365 --scenerios as the last_updated_date is not updated
366 --for the structure version in pa_proj_elem_ver_structure
367 --table.The above cursor is though needed when the structure
368 --version infirmation such as description is updated.
369
370 --Now after the fix ,both the maximum dates from
371 --pa_proj_elem_ver_structure and pa_proj_elem_ver_schedule
372 --are compared.The structure version info corresponding
373 --to greater last_updated_date is returned.
374
375 CURSOR cur_elem_ver_stru2
376 IS
377 SELECT MAX(a.last_update_date),
378 c.pev_structure_id,
379 c.element_version_id,
380 c.name,
381 c.record_version_number
382 FROM pa_proj_element_versions b,
383 pa_proj_elem_ver_schedule a,
384 pa_proj_elem_ver_structure c,
385 pa_structure_types d ,
386 pa_proj_structure_types e
387 WHERE a.element_version_id= b.element_version_id
388 AND a.project_id = b.project_id
389 AND a.proj_element_id = b.proj_element_id
390 AND b.parent_structure_version_id = c.element_version_id
391 AND b.project_id = c.project_id
392 AND b.project_id = p_project_id
393 AND c.status_code = 'STRUCTURE_WORKING'
394 AND e.proj_element_id = c.proj_element_id
395 AND d.structure_type_id = e.structure_type_id
396 AND d.structure_type_class_code = 'WORKPLAN'
397 GROUP BY c.pev_structure_id
398 ,c.element_version_id
399 ,c.name
400 ,c.record_version_number
401 ORDER BY MAX(a.last_update_date) desc ;
402
403 --cursor to select the structure version id for a workplan structure
404 CURSOR cur_elem_ver_stru3
405 IS
406 SELECT ppe.proj_element_id
407 FROM pa_proj_elements ppe,
408 pa_proj_structure_types ppst,
409 pa_structure_types pst
410 WHERE ppe.project_id = p_project_id
411 AND ppe.object_type = 'PA_STRUCTURES'
412 -- AND ppe.status_code <> 'STRUCTURE_PUBLISHED'
413 AND ppst.proj_element_id = ppe.proj_element_id
414 AND ppst.structure_type_id = pst.structure_type_id
415 AND pst.structure_type_class_code = 'WORKPLAN';
416 l_structure_id NUMBER;
417
418 /* Bug No 3692971, For performance reasons the following tables are removed from the cursor query as */
419 /* pa_proj_structure_types, pa_structure_types tables are not used in select list and where clause. */
420 /* In case these two tables are required please add the following two join conditions to avoid full */
421 /* table scan on the structure_type tables */
422 /* AND ppst.proj_element_id = ppevs.proj_element_id */
423 /* AND ppst.structure_type_id = pst.structure_type_id */
424 CURSOR cur_elem_ver_stru4(c_struc_id NUMBER, c_struc_ver_id NUMBER)
425 IS
426 SELECT ppevs.last_update_date,
427 ppevs.pev_structure_id,
428 ppevs.element_version_id,
429 ppevs.name,
430 ppevs.record_version_number
431 FROM pa_proj_elem_ver_structure ppevs
432 WHERE ppevs.project_id = p_project_id
433 AND ppevs.proj_element_id = c_struc_id
434 AND ppevs.element_version_id = c_struc_ver_id;
435 /* SELECT ppevs.last_update_date,
436 ppevs.pev_structure_id,
437 ppevs.element_version_id,
438 ppevs.name,
439 ppevs.record_version_number
440 FROM pa_proj_elem_ver_structure ppevs,
441 pa_proj_structure_types ppst,
442 pa_structure_types pst
443 WHERE ppevs.project_id = p_project_id
444 AND ppevs.proj_element_id = c_struc_id
445 AND ppevs.element_version_id = c_struc_ver_id;*/
446 BEGIN
447
448 x_msg_count := 0;
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
451
452
453 IF l_debug_mode = 'Y' THEN
454 --Moved here for bug 4252182
455 pa_debug.set_curr_function( p_function => 'Get_Last_Upd_Working_Wp_Ver',
456 p_debug_mode => l_debug_mode );
457
458 pa_debug.g_err_stage:= 'Validating input parameters';
459 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
460 END IF;
461
462 IF (p_project_id IS NULL) THEN
463 IF l_debug_mode = 'Y' THEN
464 pa_debug.g_err_stage:= 'Value of input parameter = '|| p_project_id;
465 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
466 END IF;
467 PA_UTILS.ADD_MESSAGE
468 (p_app_short_name => 'PA',
469 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
470 RAISE Invalid_Arg_Exc_WP;
471
472 END IF;
473
474 --added for bug 3476162
475 OPEN cur_elem_ver_stru3;
476 FETCH cur_elem_ver_stru3 into l_structure_id;
477 CLOSE cur_elem_ver_stru3;
478
479 x_element_version_id := PA_PROJECT_STRUCTURE_UTILS.get_last_updated_working_ver(l_structure_id);
480
481 OPEN cur_elem_ver_stru4(l_structure_id, x_element_version_id);
482 FETCH cur_elem_ver_stru4 INTO
483 l_date1
484 ,l_pev_structure_id1
485 ,l_element_version_id1
486 ,l_element_version_name1
487 ,l_record_version_number1;
488 IF cur_elem_ver_stru4%NOTFOUND THEN
489 x_pev_structure_id := -1 ;
490 x_element_version_id := -1 ;
491 x_element_version_name := '-1';
492 x_record_version_number := -1;
493 ELSE
494 x_pev_structure_id := l_pev_structure_id1 ;
495 x_element_version_id := l_element_version_id1 ;
496 x_element_version_name := l_element_version_name1 ;
497 x_record_version_number := l_record_version_number1 ;
498 END IF;
499 CLOSE cur_elem_ver_stru4;
500 --end added for bug 3476162
501
502 --commented out for bug 3476162
503 /*
504 OPEN cur_elem_ver_stru1;
505 FETCH cur_elem_ver_stru1 INTO
506 l_date1
507 ,l_pev_structure_id1
508 ,l_element_version_id1
509 ,l_element_version_name1
510 ,l_record_version_number1;
511 IF cur_elem_ver_stru1%NOTFOUND THEN
512 x_pev_structure_id := -1 ;
513 x_element_version_id := -1 ;
514 x_element_version_name := '-1';
515 x_record_version_number := -1 ;
516 CLOSE cur_elem_ver_stru1 ;
517 ELSE
518 OPEN cur_elem_ver_stru2;
519 FETCH cur_elem_ver_stru2 INTO
520 l_date2
521 ,l_pev_structure_id2
522 ,l_element_version_id2
523 ,l_element_version_name2
524 ,l_record_version_number2;
525
526 IF l_date1>l_date2 THEN
527 x_pev_structure_id := l_pev_structure_id1 ;
528 x_element_version_id := l_element_version_id1 ;
529 x_element_version_name := l_element_version_name1 ;
530 x_record_version_number := l_record_version_number1 ;
531 ELSE
532 x_pev_structure_id := l_pev_structure_id2 ;
533 x_element_version_id := l_element_version_id2 ;
534 x_element_version_name := l_element_version_name2 ;
535 x_record_version_number := l_record_version_number2 ;
536 END IF ;
537 CLOSE cur_elem_ver_stru2 ;
538 END IF;
539 */
540
541 IF l_debug_mode = 'Y' THEN --For bug 4252182
542
543 pa_debug.reset_curr_function;
544
545 END IF;
546
547 EXCEPTION
548
549 WHEN Invalid_Arg_Exc_WP THEN
550
551 x_return_status := FND_API.G_RET_STS_ERROR;
552 x_msg_count := FND_MSG_PUB.count_msg;
553
554 -- 4537865 RESET OUT PARAMS
555 x_pev_structure_id := NULL ;
556 x_element_version_id := NULL ;
557 x_element_version_name := NULL ;
558 x_record_version_number := NULL ;
559
560 IF x_msg_count = 1 and x_msg_data IS NULL THEN
561 PA_INTERFACE_UTILS_PUB.get_messages
562 (p_encoded => FND_API.G_TRUE
563 ,p_msg_index => 1
564 ,p_msg_count => x_msg_count
565 ,p_msg_data => x_msg_data
566 ,p_data => l_data, -- 4537865
567 p_msg_index_out => l_msg_index_out);
568
569 x_msg_data := l_data ; -- 4537865
570
571 END IF;
572
573 IF l_debug_mode = 'Y' THEN --For bug 4252182
574 pa_debug.reset_curr_function;
575 END IF;
576
577 RETURN;
578
579 WHEN others THEN
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 x_msg_count := 1;
582 x_msg_data := SQLERRM;
583
584 -- 4537865 RESET OUT PARAMS
585 x_pev_structure_id := NULL ;
586 x_element_version_id := NULL ;
587 x_element_version_name := NULL ;
588 x_record_version_number := NULL ;
589
590 IF cur_elem_ver_stru1%ISOPEN THEN
591 CLOSE cur_elem_ver_stru1;
592 END IF;
593 IF cur_elem_ver_stru2%ISOPEN THEN
594 CLOSE cur_elem_ver_stru2;
595 END IF;
596
597 FND_MSG_PUB.add_exc_msg
598 ( p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS'
599 ,p_procedure_name => 'Get_Last_Upd_Working_Wp_Ver'
600 ,p_error_text => x_msg_data);
601
602 IF l_debug_mode = 'Y' THEN
603 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
604 pa_debug.write(l_module_name,pa_debug.g_err_stage,
605 l_debug_level5);
606 pa_debug.reset_curr_function; --For bug 4252182
607 END IF;
608 RAISE;
609 END Get_Last_Upd_Working_Wp_Ver;
610
611 FUNCTION element_has_child(
612 p_structure_version_id NUMBER
613 ) RETURN VARCHAR2 IS
614 CURSOR cur_obj_rel
615 IS
616 SELECT 'x'
617 FROM pa_object_relationships por, pa_proj_elements ppe, pa_proj_element_versions ppev
618 WHERE object_id_from1 = p_structure_version_id
619 AND relationship_type = 'S'
620 AND por.object_id_to1 = ppev.element_version_id
621 AND ppe.proj_element_id = ppev.proj_element_id
622 AND ppe.link_task_flag = 'N';
623 /*START WITH object_id_from1 = p_structure_version_id
624 CONNECT BY object_id_from1 = PRIOR object_id_to1;*/
625 v_dummy_char VARCHAR2(1);
626 BEGIN
627 OPEN cur_obj_rel;
628 FETCH cur_obj_rel INTO v_dummy_char;
629 IF cur_obj_rel%FOUND
630 THEN
631 CLOSE cur_obj_rel;
632 RETURN 'Y';
633 ELSE
634 CLOSE cur_obj_rel;
635 RETURN 'N';
636 END IF;
637 END element_has_child;
638
639 -- API name : IS_LOWEST_Task
640 -- Type : Task Utils API
641 -- Pre-reqs : None
642 -- Return Value : 1 if it is lowest task; 0 if not.
643 -- Parameters
644 -- p_task_version_id IN NUMBER
645 --
646 -- History
647 --
648 -- 31-OCT-01 HSIU -Created
649 --
650
651
652 function IS_LOWEST_TASK(p_task_version_id NUMBER) RETURN VARCHAR2
653 IS
654 l_dummy number;
655
656 cursor child_exist IS
657 select 1
658 from pa_object_relationships
659 where object_type_from = 'PA_TASKS'
660 and object_id_from1 = p_task_version_id
661 and relationship_type = 'S';
662
663 BEGIN
664
665 OPEN child_exist;
666 FETCH child_exist into l_dummy;
667 IF child_exist%NOTFOUND then
668 --Cannot find child. It is lowest task
669 CLOSE child_exist;
670 return 'Y';
671 ELSE
672 --Child found. Not lowest task
673 CLOSE child_exist;
674 return 'N';
675 END IF;
676 EXCEPTION
677 WHEN OTHERS THEN
678 return (SQLCODE);
679 END IS_LOWEST_TASK;
680
681 -- API name : Check_element_Name_Unique
682 -- Type : Utils API
683 -- Pre-reqs : None
684 -- Return Value : Y if not exists; N if exists.
685 -- Parameters
686 -- p_element_name IN VARCHAR2
687 -- p_element_id IN NUMBER
688 -- p_project_id IN NUMBER
689 -- p_object_type IN VARCHAR2 := 'PA_TASKS'
690 --
691 -- History
692 --
693 -- 01-NOV-01 MAANSARI -Created
694 --
695 --
696
697 function Check_element_NUmber_Unique
698 (
699 p_element_number IN VARCHAR2
700 ,p_element_id IN NUMBER
701 ,p_project_id IN NUMBER
702 ,p_structure_id IN NUMBER
703 ,p_object_type IN VARCHAR2 := 'PA_TASKS'
704 ) return VARCHAR2
705 IS
706 cursor c1 is
707 select 1 from pa_proj_elements
708 where project_id = p_project_id
709 and object_type = p_object_type
710 and element_number = p_element_number
711 and PARENT_STRUCTURE_ID = p_structure_id
712 and (p_element_id is NULL or proj_element_id <> p_element_id);
713
714 l_dummy NUMBER;
715
716 BEGIN
717 if (p_project_id IS NULL or p_element_number is NULL) then
718 return (null);
719 end if;
720
721 open c1;
722 fetch c1 into l_dummy;
723 if c1%notfound THEN
724 close c1;
725 return('Y');
726 else
727 close c1;
728 return('N');
729 end if;
730 EXCEPTION
731 when others then
732 return (SQLCODE);
733 END Check_element_number_Unique;
734
735 -- API name : Check_Struc_Published
736 -- Type : Utils API
737 -- Pre-reqs : None
738 -- Return Value : N if not published; Y if published.
739 -- Parameters
740 -- p_structure_id IN NUMBER
741 -- p_project_id IN NUMBER
742 --
743 -- History
744 --
745 -- 01-NOV-01 MAANSARI -Created
746 --
747 --
748
749
750 function Check_Struc_Published
751 (
752 p_project_id IN NUMBER
753 ,p_structure_id IN NUMBER
754 ) return VARCHAR2
755 IS
756 cursor c1 is
757 select '1'
758 from pa_proj_elem_ver_structure
759 where project_id = p_project_id
760 and proj_element_id = p_structure_id
761 and published_date IS NULL;
762 c1_rec c1%rowtype;
763
764 BEGIN
765 if (p_project_id IS NULL or p_structure_id IS NULL) then
766 return (null);
767 end if;
768
769 open c1;
770 fetch c1 into c1_rec;
771 if c1%notfound THEN
772 close c1;
773 return('N');
774 else
775 close c1;
776 return('Y');
777 end if;
778 EXCEPTION
779 when others then
780 return (SQLCODE);
781 END Check_Struc_Published;
782
783
784 -- API name : Check_Delete_task_Ver_Ok
785 -- Type : Utils API
786 -- Pre-reqs : None
787 -- Return Value : N/A
788 -- Parameters
789 -- p_structure_version_id IN NUMBER
790 -- x_return_status OUT VARCHAR2
791 -- x_error_msg_code OUT VARCHAR2
792 --
793 -- History
794 --
795 -- 25-JUN-01 HSIU -Created
796 --
797 --
798
799
800 procedure Check_Delete_task_Ver_Ok
801 (
802 p_project_id IN NUMBER
803 ,p_task_version_id IN NUMBER
804 ,p_parent_structure_ver_id IN NUMBER
805 ,p_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
806 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
807 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
808 )
809 IS
810 l_user_id NUMBER;
811 l_person_id NUMBER;
812 l_dummy VARCHAR2(1);
813 l_proj_element_id NUMBER;
814
815 l_err_code VARCHAR2(2000);
816 l_err_stage VARCHAR2(2000);
817 l_err_stack VARCHAR2(2000);
818
819 cursor get_person_id(p_user_id NUMBER) IS
820 select p.person_id
821 from per_all_people_f p, fnd_user f
822 where f.employee_id = p.person_id
823 and sysdate between p.effective_start_date and p.effective_end_date
824 and f.user_id = p_user_id;
825
826 cursor get_lock_user(p_person_id NUMBER) IS
827 select '1'
828 from pa_proj_element_versions v, pa_proj_elem_ver_structure s
829 where v.element_version_id = p_parent_structure_ver_id
830 and v.project_id = s.project_id
831 and v.element_version_id = s.element_version_id
832 and (locked_by_person_id IS NULL
833 or locked_by_person_id = p_person_id);
834
835 cursor get_link IS
836 select '1'
837 from pa_object_relationships
838 where ( object_id_from1 = p_task_version_id
839 or object_id_to1 = p_task_version_id )
840 and relationship_type = 'L';
841
842 CURSOR cur_chk_last_ver
843 IS
844 SELECT 'x'
845 FROM pa_proj_element_versions
846 WHERE proj_element_id = ( SELECT proj_element_id
847 FROM pa_proj_element_versions
848 WHERE element_version_id = p_task_version_id )
849 AND element_version_id <> p_task_version_id;
850
851 CURSOR cur_proj_elem_ver
852 IS
853 SELECT ppev.proj_element_id
854 FROM pa_proj_element_versions ppev, pa_tasks pt
855 WHERE ppev.element_version_id = p_task_version_id
856 and ppev.proj_element_id = pt.task_id;
857
858 -- Bug 3933576 : Added cursor cur_wp_proj_elem_ver and variable l_wp_proj_elem_id
859 CURSOR cur_wp_proj_elem_ver
860 IS
861 SELECT ppev.proj_element_id
862 FROM pa_proj_element_versions ppev
863 WHERE ppev.element_version_id = p_task_version_id
864 ;
865
866
867 --hsiu: added for bug 2682805
868 l_dummy2 NUMBER;
869
870 -- anlee Added for ENG
871 l_return_status VARCHAR2(1);
872 l_msg_count NUMBER;
873 l_msg_data VARCHAR2(2000);
874 l_delete_ok VARCHAR2(250);
875
876 BEGIN
877 l_user_id := FND_GLOBAL.USER_ID;
878 --dbms_output.put_line('user id = '||l_user_id);
879 --get the current user's person_id
880 open get_person_id(l_user_id);
881 fetch get_person_id into l_person_id;
882 if get_person_id%NOTFOUND then
883 l_person_id := -1;
884 end if;
885 close get_person_id;
886
887 --dbms_output.put_line( 'Person Id = '||l_person_id);
888
889 --comment out because linking is not available
890 -- open get_lock_user(l_person_id);
891 -- fetch get_lock_user into l_dummy;
892 -- if get_lock_user%NOTFOUND then
893 -- --the structure version is locked by another user.
894 -- close get_lock_user;
895 -- x_return_status := FND_API.G_RET_STS_ERROR;
896 -- x_error_message_code := 'PA_PS_STRUC_VER_LOCKED';
897 -- --return;
898 -- raise FND_API.G_EXC_ERROR;
899 --
900 -- end if;
901 -- close get_lock_user;
902
903 --dbms_output.put_line( 'Check if this is a published version ');
904
905 --Check if this is a published version
906 --hsiu
907 --versioning changes
908 --
909 -- If (PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(p_project_id, p_parent_structure_ver_id) = 'Y') THEN
910 --version is published. Error.
911 -- x_return_status := FND_API.G_RET_STS_ERROR;
912 -- x_error_message_code := 'PA_PS_STRUC_VER_LOCKED';
913 -- return;
914 -- END IF;
915
916 --check if the structure is worplan
917 --dbms_output.put_line( 'Before if 1 ');
918
919 --hsiu: added for bug 2682805
920 -- Bug 3933576 : proj_element_id shd come from cur_wp_proj_elem_ver rather than cur_proj_elem_ver
921 -- because CI, EGO Items and progress check will happen, even if entry is not there in pa_tasks.
922
923 -- OPEN cur_proj_elem_ver;
924 -- FETCH cur_proj_elem_ver INTO l_proj_element_id;
925 -- CLOSE cur_proj_elem_ver;
926
927 OPEN cur_wp_proj_elem_ver;
928 FETCH cur_wp_proj_elem_ver INTO l_proj_element_id;
929 CLOSE cur_wp_proj_elem_ver;
930
931
932 IF (1 = PA_CONTROL_ITEMS_UTILS.CHECK_CONTROL_ITEM_EXISTS(p_project_id, l_proj_element_id) ) THEN
933 x_return_status := FND_API.G_RET_STS_ERROR;
934 --use this error message according to mthai: PA_CI_ITEMS_EXIST
935 --changed to PA_CI_PROJ_TASK_IN_USE, according to Margaret
936 x_error_message_code := 'PA_CI_PROJ_TASK_IN_USE';
937 return;
938 END IF;
939 --end bug 2682805
940
941 --anlee
942 --Added for ENG integration
943
944 PA_EGO_WRAPPER_PUB.check_delete_task_ok_eng(
945 p_api_version => 1.0 ,
946 p_task_id => l_proj_element_id ,
947 p_init_msg_list => NULL ,
948 x_delete_ok => l_delete_ok ,
949 x_return_status => l_return_status ,
950 x_errorcode => l_err_code ,
951 x_msg_count => l_msg_count ,
952 x_msg_data => l_msg_data );
953
954 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
955 x_return_status := l_return_status;
956 return;
957 END IF;
958 -- anlee end of changes
959
960 IF PA_PROJ_ELEMENTS_UTILS.structure_type(
961 p_structure_version_id => p_parent_structure_ver_id
962 ,p_task_version_id => null
963 ,p_structure_type => 'WORKPLAN'
964 ) = 'Y'
965 THEN
966 --call selvas API
967 -- Bug 3933576 : NO need to open cursor cur_proj_elem_ver here, l_proj_element_id is already fetched
968 --OPEN cur_proj_elem_ver;
969 --FETCH cur_proj_elem_ver INTO l_proj_element_id;
970 --CLOSE cur_proj_elem_ver;
971
972 -- 4201927 commented below code as check_task_progress_exist is deriving project id and then
973 -- calling check_object_has_prog api
974 /*
975 IF pa_project_structure_utils.check_task_progress_exist(l_proj_element_id) = 'Y' THEN
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 x_error_message_code := 'PA_PS_TASK_HAS_PROG';
978 raise FND_API.G_EXC_ERROR;
979 END IF;
980 */
981
982 -- using the direct api check_object_has_prog
983
984 IF pa_progress_utils.check_object_has_prog(p_project_id => p_project_id, p_object_id => l_proj_element_id) = 'Y' THEN
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 x_error_message_code := 'PA_PS_TASK_HAS_PROG';
987 raise FND_API.G_EXC_ERROR;
988 END IF;
989 -- 4201927 end
990
991 -- 4201927 , delete dlvr to task association api should get called for
992 -- all the tasks
993
994 PA_DELIVERABLE_PUB.delete_dlv_task_asscn_in_bulk
995 (
996 p_task_element_id => l_proj_element_id
997 ,p_project_id => p_project_id
998 ,p_task_version_id => p_task_version_id
999 ,p_delete_or_validate => 'V'
1000 ,x_return_status => l_return_status
1001 ,x_msg_count => l_msg_count
1002 ,x_msg_data => l_msg_data
1003 );
1004
1005 -- 4537865 Changed from x_return_status to l_return_status
1006 IF l_return_status = FND_API.G_RET_STS_ERROR then
1007 RAISE FND_API.G_EXC_ERROR;
1008 End If;
1009
1010 -- 4201927 end
1011
1012 /*
1013 IF pa_progress_utils.PROJ_TASK_PROG_EXISTS(p_project_id, l_proj_element_id) = 'Y' THEN
1014 x_return_status := FND_API.G_RET_STS_ERROR;
1015 x_error_message_code := 'PA_PS_TASK_HAS_PROG';
1016 raise FND_API.G_EXC_ERROR;
1017 END IF;
1018
1019 IF pa_progress_utils.progress_record_exists( p_task_version_id, 'PA_TASKS'
1020 ,p_project_id) = 'Y' -- Fixed bug # 3688901.
1021 THEN
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 x_error_message_code := 'PA_PS_CANT_DELETE_TASK_VER';
1024 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_CANT_DELETE_TASK_VER');
1025 raise FND_API.G_EXC_ERROR;
1026 END IF;
1027 */
1028 END IF;
1029
1030 IF PA_PROJ_ELEMENTS_UTILS.structure_type(
1031 p_structure_version_id => p_parent_structure_ver_id
1032 ,p_task_version_id => null
1033 ,p_structure_type => 'FINANCIAL'
1034 ) = 'Y' THEN
1035 --Check if this is a billing/costing structure version
1036 --if it is, check to see if it is the last version
1037
1038 --dbms_output.put_line( 'Before cur_chk_last_ver');
1039
1040 -- OPEN cur_chk_last_ver;
1041 -- FETCH cur_chk_last_ver INTO l_dummy;
1042 -- IF cur_chk_last_ver%NOTFOUND -- p_task_version is the last version
1043 -- THEN
1044 OPEN cur_proj_elem_ver;
1045 FETCH cur_proj_elem_ver INTO l_proj_element_id;
1046 CLOSE cur_proj_elem_ver;
1047 --Check if it is okay to delete task
1048
1049 IF (l_proj_element_id IS NOT NULL) THEN --it can be partial share
1050 PA_TASK_UTILS.CHECK_DELETE_TASK_OK( x_task_id => l_proj_element_id,
1051 x_validation_mode => p_validation_mode, --bug 2947492
1052 x_err_code => l_err_code,
1053 x_err_stage => l_err_stage,
1054 x_err_stack => l_err_stack);
1055
1056 IF (l_err_code <> 0) THEN
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058 x_error_message_code := l_err_stage;
1059 raise FND_API.G_EXC_ERROR;
1060 END IF;
1061 END IF;
1062
1063 -- END IF;
1064 -- CLOSE cur_chk_last_ver;
1065 END IF;
1066
1067 --linking is not available yet. Comment out
1068 --Check if this task version has any links
1069 -- open get_link;
1070 -- fetch get_link into l_dummy;
1071 -- if get_link%FOUND then
1072 -- --a link exists
1073 -- close get_link;
1074 -- x_return_status := FND_API.G_RET_STS_ERROR;
1075 -- x_error_message_code := 'PA_PS_LINK_EXISTS';
1076 -- return;
1077 -- end if;
1078 -- close get_link;
1079
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082 EXCEPTION
1083 WHEN FND_API.G_EXC_ERROR THEN
1084 x_return_status := FND_API.G_RET_STS_ERROR;
1085
1086 -- 4537865
1087 -- Not Resetting x_error_message_code unconditionally as after its population only
1088 -- it will reach here. In case if its not populated then,populate the generic msg
1089
1090 IF x_error_message_code is NULL THEN
1091 x_error_message_code := 'PA_CHECK_DELETE_TASK_FAILED';
1092 END IF;
1093
1094 WHEN OTHERS THEN
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1096
1097 -- 4537865
1098 x_error_message_code := SQLCODE;
1099 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
1100 p_procedure_name => 'Check_Delete_task_Ver_Ok',
1101 p_error_text => SUBSTRB(SQLERRM,1,240)); -- 4537865
1102 RAISE;
1103 END Check_Delete_task_Ver_Ok;
1104
1105 function structure_type(
1106 p_structure_version_id NUMBER,
1107 p_task_version_id NUMBER,
1108 p_structure_type VARCHAR2 ) RETURN VARCHAR2 IS
1109
1110 CURSOR cur_proj_elem
1111 IS
1112 SELECT parent_structure_version_id
1113 FROM pa_proj_element_versions
1114 WHERE element_version_id = p_task_version_id
1115 AND object_type = 'PA_TASKS';
1116
1117 CURSOR cur_struc_type( x_structure_version_id NUMBER )
1118 IS
1119 SELECT 'Y'
1120 FROM pa_proj_element_versions ppev
1121 ,pa_proj_structure_types ppst
1122 ,pa_structure_types pst
1123 WHERE ppev.element_version_id = x_structure_version_id
1124 AND ppev.proj_element_id = ppst.proj_element_id
1125 AND ppst.structure_type_id = pst.structure_type_id
1126 AND pst.structure_type_class_code = p_structure_type;
1127
1128
1129 l_structure_version_id NUMBER;
1130 l_dummy_char VARCHAR2(1);
1131 begin
1132 OPEN cur_proj_elem;
1133 FETCH cur_proj_elem INTO l_structure_version_id;
1134 CLOSE cur_proj_elem;
1135
1136 IF l_structure_version_id IS NULL
1137 THEN
1138 l_structure_version_id := p_structure_version_id;
1139 END IF;
1140
1141 OPEN cur_struc_type( l_structure_version_id );
1142 FETCH cur_struc_type INTO l_dummy_char;
1143 IF cur_struc_type%FOUND
1144 THEN
1145 CLOSE cur_struc_type;
1146 RETURN 'Y';
1147 ELSE
1148 CLOSE cur_struc_type;
1149 RETURN 'N';
1150 END IF;
1151
1152 end structure_type;
1153
1154 FUNCTION is_summary_task_or_structure( p_element_version_id NUMBER ) RETURN VARCHAR2 IS
1155 /* CURSOR cur_obj_rel
1156 IS
1157 SELECT 'x'
1158 FROM pa_object_relationships
1159 WHERE object_id_from1 = p_element_version_id
1160 --hsiu: bug 2800553: performance
1161 and rownum < 2
1162 --start with object_id_from1 = p_element_version_id
1163 AND object_type_from = 'PA_TASKS'
1164 AND relationship_type = 'S'
1165 And object_id_to1 NOT IN (
1166 select b.object_id_from1
1167 from pa_object_relationships a,
1168 pa_object_relationships b
1169 where a.object_id_from1 = p_element_version_id
1170 and a.object_id_to1 = b.object_id_from1
1171 and a.relationship_type = 'S'
1172 and b.relationship_type IN ('LW', 'LF'))
1173 ;*/
1174
1175 -- Bug 6156686
1176 CURSOR cur_obj_rel
1177 IS
1178 SELECT NULL
1179 FROM DUAL
1180 WHERE EXISTS
1181 (SELECT NULL
1182 FROM pa_object_relationships por,
1183 pa_proj_element_versions pev,
1184 pa_proj_elements pe
1185 WHERE por.object_id_from1 = p_element_version_id
1186 AND por.object_type_from ='PA_TASKS'
1187 AND por.relationship_type = 'S'
1188 AND por.object_id_to1=pev.element_version_id
1189 AND pe.proj_element_id=pev.proj_element_id
1190 AND NVL(pe.link_task_flag,'N') <> 'Y'
1191 AND pev.financial_task_flag = 'Y'); -- Added AND Condition for Bug 7210236
1192
1193 --connect by object_id_from1 = prior object_id_to1 ;
1194 v_dummy_char VARCHAR2(1);
1195 BEGIN
1196 OPEN cur_obj_rel;
1197 FETCH cur_obj_rel INTO v_dummy_char;
1198 IF cur_obj_rel%FOUND
1199 THEN
1200 CLOSE cur_obj_rel;
1201 RETURN 'Y';
1202 ELSE
1203 CLOSE cur_obj_rel;
1204 RETURN 'N';
1205 END IF;
1206
1207 END is_summary_task_or_structure;
1208
1209
1210 procedure Check_Date_range
1211 (
1212 p_scheduled_start_date IN DATE :=null
1213 ,p_scheduled_end_date IN DATE :=null
1214 ,p_obligation_start_date IN DATE :=null
1215 ,p_obligation_end_date IN DATE :=null
1216 ,p_actual_start_date IN DATE :=null
1217 ,p_actual_finish_date IN DATE :=null
1218 ,p_estimate_start_date IN DATE :=null
1219 ,p_estimate_finish_date IN DATE :=null
1220 ,p_early_start_date IN DATE :=null
1221 ,p_early_end_date IN DATE :=null
1222 ,p_late_start_date IN DATE :=null
1223 ,p_late_end_date IN DATE :=null
1224 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1225 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
1226 ) IS
1227
1228 begin
1229 x_return_status := FND_API.G_RET_STS_SUCCESS;
1230
1231 IF p_scheduled_start_date IS NOT NULL AND p_scheduled_end_date IS NOT NULL
1232 THEN
1233 IF p_scheduled_start_date > p_scheduled_end_date
1234 THEN
1235 x_return_status := FND_API.G_RET_STS_ERROR;
1236 x_error_message_code := 'PA_PS_SCH_ST_DT_GT_EN_DT';
1237 return;
1238 END IF;
1239 END IF;
1240
1241 IF p_obligation_start_date IS NOT NULL AND p_obligation_end_date IS NOT NULL
1242 THEN
1243 IF p_obligation_start_date > p_obligation_end_date
1244 THEN
1245 x_return_status := FND_API.G_RET_STS_ERROR;
1246 x_error_message_code := 'PA_PS_OBL_ST_DT_GT_EN_DT';
1247 return;
1248 END IF;
1249 END IF;
1250
1251 IF p_actual_start_date IS NOT NULL AND p_actual_finish_date IS NOT NULL
1252 THEN
1253 IF p_actual_start_date > p_actual_finish_date
1254 THEN
1255 x_return_status := FND_API.G_RET_STS_ERROR;
1256 x_error_message_code := 'PA_PS_ACT_ST_DT_GT_EN_DT';
1257 return;
1258 END IF;
1259 END IF;
1260
1261 IF p_estimate_start_date IS NOT NULL AND p_estimate_finish_date IS NOT NULL
1262 THEN
1263 IF p_estimate_start_date > p_estimate_finish_date
1264 THEN
1265 x_return_status := FND_API.G_RET_STS_ERROR;
1266 x_error_message_code := 'PA_PS_EST_ST_DT_GT_EN_DT';
1267 return;
1268 END IF;
1269 END IF;
1270
1271 IF p_early_start_date IS NOT NULL AND p_early_end_date IS NOT NULL
1272 THEN
1273 IF p_early_start_date > p_early_end_date
1274 THEN
1275 x_return_status := FND_API.G_RET_STS_ERROR;
1276 x_error_message_code := 'PA_PS_ERL_ST_DT_GT_EN_DT';
1277 return;
1278 END IF;
1279 END IF;
1280
1281 IF p_late_start_date IS NOT NULL AND p_late_end_date IS NOT NULL
1282 THEN
1283 IF p_late_start_date > p_late_end_date
1284 THEN
1285 x_return_status := FND_API.G_RET_STS_ERROR;
1286 x_error_message_code := 'PA_PS_LAT_ST_DT_GT_EN_DT';
1287 return;
1288 END IF;
1289 END IF;
1290 -- 4537865
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 x_error_message_code := SQLCODE ;
1295 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
1296 p_procedure_name => 'Check_Date_range',
1297 p_error_text => SUBSTRB(SQLERRM,1,240));
1298 RAISE;
1299 end Check_Date_range;
1300
1301 PROCEDURE Project_Name_Or_Id
1302 (
1303 p_project_name IN VARCHAR2
1304 ,p_project_id IN NUMBER
1305 ,p_check_id_flag IN VARCHAR2 := 'Y'
1306 ,x_project_id OUT NOCOPY NUMBER -- 4537865
1307 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1308 ,x_error_msg_code OUT NOCOPY VARCHAR2 -- 4537865
1309 )
1310 IS
1311 BEGIN
1312 IF (p_project_id IS NOT NULL) THEN
1313 IF (p_check_id_flag = 'Y') THEN
1314 select project_id
1315 into x_project_id
1316 from pa_projects_all
1317 where project_id = p_project_id;
1318 ELSE
1319 x_project_id := p_project_id;
1320 END IF;
1321 ELSE
1322 select project_id
1323 into x_project_id
1324 from pa_projects_all
1325 where segment1 = p_project_name;
1326 END IF;
1327 x_return_status := FND_API.G_RET_STS_SUCCESS;
1328 EXCEPTION
1329 WHEN no_data_found THEN
1330 x_return_status:= FND_API.G_RET_STS_ERROR;
1331 x_error_msg_code:= 'PA_PS_INVALID_PRJ_NAME';
1332 x_project_id := NULL ; -- 4537865
1333
1334 WHEN too_many_rows THEN
1335 x_return_status:= FND_API.G_RET_STS_ERROR;
1336 x_error_msg_code:= 'PA_PS_PRJ_NAME_NOT_UNIQUE';
1337 x_project_id := NULL ; -- 4537865
1338 WHEN OTHERS THEN
1339 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1340 x_project_id := NULL ; -- 4537865
1341 RAISE;
1342 END Project_Name_Or_Id;
1343
1344 PROCEDURE task_Ver_Name_Or_Id
1345 (
1346 p_task_name IN VARCHAR2
1347 ,p_task_version_id IN NUMBER
1348 ,p_structure_version_id IN NUMBER
1349 ,p_check_id_flag IN VARCHAR2 := 'Y'
1350 ,x_task_version_id OUT NOCOPY NUMBER -- 4537865
1351 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1352 ,x_error_msg_code OUT NOCOPY VARCHAR2 -- 4537865
1353 )
1354 IS
1355 BEGIN
1356 IF (p_task_version_id IS NOT NULL) THEN
1357 IF (p_check_id_flag = 'Y') THEN
1358 select element_version_id
1359 into x_task_version_id
1360 from pa_proj_element_versions
1361 where element_version_id = p_task_version_id;
1362 ELSE
1363 x_task_version_id := p_task_version_id;
1364 END IF;
1365 ELSE
1366 select element_version_id
1367 into x_task_version_id
1368 from pa_proj_elements ppe, pa_proj_element_versions ppev
1369 where ppe.proj_element_id = ppev.proj_element_id
1370 AND ppe.name = p_task_name
1371 AND ppev.parent_structure_version_id = p_structure_version_id;
1372 null;
1373 END IF;
1374 x_return_status := FND_API.G_RET_STS_SUCCESS;
1375 EXCEPTION
1376 WHEN no_data_found THEN
1377 x_return_status:= FND_API.G_RET_STS_ERROR;
1378 x_error_msg_code:= 'PA_PS_INVALID_TSK_NAME';
1379 -- 4537865
1380 x_task_version_id := NULL ;
1381
1382 WHEN too_many_rows THEN
1383 x_return_status:= FND_API.G_RET_STS_ERROR;
1384 x_error_msg_code:= 'PA_PS_TSK_NAME_NOT_UNIQUE';
1385 -- 4537865
1386 x_task_version_id := NULL ;
1387
1388 WHEN OTHERS THEN
1389 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1390 -- 4537865
1391 x_task_version_id := NULL ;
1392 x_error_msg_code:= SQLCODE ;
1393
1394 RAISE;
1395 END task_Ver_Name_Or_Id;
1396
1397 /* ----------------------------------------------------------------------
1398 ||
1399 || Procedure Name: UPDATE_WBS_NUMBERS
1400 ||
1401 || Author : Andrew Lee
1402 || Description:
1403 || This procedure is called update the wbs numbers in the task
1404 || hierarchy whenever any of the following actions occur:
1405 || INSERT
1406 || INDENT
1407 || OUTDENT
1408 || COPY
1409 || DELETE
1410 || ---------------------------------------------------------------------
1411 */
1412 PROCEDURE UPDATE_WBS_NUMBERS ( p_commit IN VARCHAR2
1413 ,p_debug_mode IN VARCHAR2
1414 ,p_parent_structure_ver_id IN NUMBER
1415 ,p_task_id IN NUMBER
1416 ,p_display_seq IN NUMBER
1417 ,p_action IN VARCHAR2
1418 ,p_parent_task_id IN NUMBER
1419 ,x_return_status OUT NOCOPY VARCHAR2) -- 4537865
1420 IS
1421 CURSOR TASK_INFO_CSR(c_task_id NUMBER)
1422 IS
1423 SELECT rel.object_id_from1 parent_task_id, pev.wbs_number
1424 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1425 WHERE pev.element_version_id = c_task_id
1426 AND pev.object_type = 'PA_TASKS'
1427 AND rel.object_id_to1 = pev.element_version_id
1428 AND rel.relationship_type = 'S'
1429 AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
1430
1431 CURSOR UPDATE_MASKED_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER, c_mask VARCHAR2)
1432 IS
1433 SELECT element_version_id task_id, wbs_number, display_sequence
1434 FROM PA_PROJ_ELEMENT_VERSIONS
1435 WHERE parent_structure_version_id = c_parent_structure_ver_id
1436 AND object_type = 'PA_TASKS'
1437 AND abs(display_sequence) >= abs(c_display_seq)
1438 AND display_sequence <> c_display_seq
1439 -- AND substr(wbs_number, 1, length(c_mask)) = c_mask Commented for bug 3581030
1440 AND substr(wbs_number, 1, length(c_mask)+1) = c_mask||'.' -- Added for bug 3581030
1441 ORDER BY abs(display_sequence);
1442
1443 CURSOR UPDATE_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1444 IS
1445 SELECT element_version_id task_id, wbs_number, display_sequence
1446 FROM PA_PROJ_ELEMENT_VERSIONS
1447 WHERE parent_structure_version_id = c_parent_structure_ver_id
1448 AND object_type = 'PA_TASKS'
1449 AND abs(display_sequence) >= abs(c_display_seq)
1450 AND display_sequence <> c_display_seq
1451 ORDER BY abs(display_sequence);
1452
1453 CURSOR GET_TASK_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1454 IS
1455 SELECT element_version_id task_id
1456 FROM PA_PROJ_ELEMENT_VERSIONS
1457 WHERE parent_structure_version_id = c_parent_structure_ver_id
1458 AND object_type = 'PA_TASKS'
1459 AND display_sequence = c_display_seq;
1460
1461 CURSOR GET_PREV_PEER_TASK_CSR(c_parent_structure_ver_id NUMBER, c_parent_task_id NUMBER, c_display_seq NUMBER)
1462 IS
1463 SELECT element_version_id task_id
1464 FROM PA_PROJ_ELEMENT_VERSIONS
1465 WHERE parent_structure_version_id = c_parent_structure_ver_id
1466 AND object_type = 'PA_TASKS'
1467 AND display_sequence =
1468 (SELECT max(pev.display_sequence)
1469 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1470 WHERE rel.object_type_from = 'PA_TASKS'
1471 AND rel.object_id_from1 = c_parent_task_id
1472 AND rel.relationship_type = 'S'
1473 AND rel.object_type_to = 'PA_TASKS'
1474 AND rel.object_id_to1 = pev.element_version_id
1475 AND pev.parent_structure_version_id = c_parent_structure_ver_id
1476 AND pev.display_sequence < c_display_seq);
1477
1478 CURSOR GET_PREV_TOP_PEER_TASK_CSR(c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1479 IS
1480 SELECT element_version_id task_id
1481 FROM PA_PROJ_ELEMENT_VERSIONS
1482 WHERE parent_structure_version_id = c_parent_structure_ver_id
1483 AND object_type = 'PA_TASKS'
1484 AND display_sequence =
1485 (SELECT max(pev.display_sequence)
1486 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1487 WHERE rel.object_type_from = 'PA_STRUCTURES'
1488 AND rel.object_id_from1 = c_parent_structure_ver_id
1489 AND rel.relationship_type = 'S'
1490 AND rel.object_type_to = 'PA_TASKS'
1491 AND rel.object_id_to1 = pev.element_version_id
1492 AND pev.parent_structure_version_id = c_parent_structure_ver_id
1493 AND display_sequence < c_display_seq);
1494
1495 l_task_rec TASK_INFO_CSR%ROWTYPE;
1496 l_prev_task_rec TASK_INFO_CSR%ROWTYPE;
1497 l_parent_task_rec TASK_INFO_CSR%ROWTYPE;
1498 l_temp TASK_INFO_CSR%ROWTYPE;
1499 l_update_task_rec UPDATE_TASKS_CSR%ROWTYPE;
1500 l_task_id NUMBER;
1501
1502 l_count NUMBER;
1503 l_wbs_number VARCHAR2(1000);
1504 l_mask VARCHAR2(1000);
1505 l_branch_mask VARCHAR2(1000);
1506 l_mask2 VARCHAR2(1000);
1507 l_prev_disp_seq NUMBER;
1508 l_prev_task_id NUMBER;
1509 l_increment VARCHAR2(255);
1510 l_str1 VARCHAR2(1000);
1511 l_str2 VARCHAR2(1000);
1512 l_loop_wbs_number VARCHAR2(1000);
1513 l_flag BOOLEAN;
1514
1515 API_ERROR EXCEPTION;
1516 l_number NUMBER; -- Bug 2786662
1517 BEGIN
1518 if p_commit = 'Y' then
1519 savepoint update_wbs_numbers;
1520 end if;
1521
1522 if(p_action <> 'DELETE') then
1523 OPEN TASK_INFO_CSR(p_task_id);
1524 FETCH TASK_INFO_CSR INTO l_task_rec;
1525 if(TASK_INFO_CSR%NOTFOUND) then
1526 CLOSE TASK_INFO_CSR;
1527 x_return_status := 'E';
1528 raise API_ERROR;
1529 end if;
1530 CLOSE TASK_INFO_CSR;
1531 end if;
1532
1533 -- INSERT or COPY
1534 if((p_action = 'INSERT') OR (p_action = 'COPY')) then
1535 if(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1536 -- Inserted task is a top task
1537 -- Added the leading hint below for bug 3416314
1538 -- Smukka Merging branch 40 as of now with main branch
1539 SELECT /*+ LEADING (rel) */ count(pev.element_version_id)
1540 INTO l_count
1541 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1542 WHERE pev.parent_structure_version_id = p_parent_structure_ver_id
1543 AND pev.object_type = 'PA_TASKS'
1544 AND abs(pev.display_sequence) <= abs(p_display_seq)
1545 AND rel.object_id_to1 = pev.element_version_id
1546 AND rel.relationship_type = 'S'
1547 AND rel.object_type_from = 'PA_STRUCTURES'
1548 AND rel.object_id_from1 = p_parent_structure_ver_id
1549 AND rel.object_type_to = 'PA_TASKS'; --Added for Bug 6430953
1550
1551 l_wbs_number := l_count;
1552 l_mask := 'NONE';
1553 else
1554 -- Inserted task is a child task
1555 l_prev_disp_seq := abs(p_display_seq) - 1;
1556 OPEN GET_TASK_CSR(p_parent_structure_ver_id, l_prev_disp_seq);
1557 FETCH GET_TASK_CSR INTO l_prev_task_id;
1558 if(GET_TASK_CSR%NOTFOUND) then
1559 CLOSE GET_TASK_CSR;
1560 OPEN GET_TASK_CSR(p_parent_structure_ver_id, l_prev_disp_seq * -1);
1561 FETCH GET_TASK_CSR INTO l_prev_task_id;
1562 if(GET_TASK_CSR%NOTFOUND) then
1563 CLOSE GET_TASK_CSR;
1564 x_return_status := 'E';
1565 raise API_ERROR;
1566 end if;
1567 end if;
1568
1569 CLOSE GET_TASK_CSR;
1570
1571
1572 if(l_prev_task_id = l_task_rec.parent_task_id) then
1573 -- Previous task is a parent
1574 OPEN TASK_INFO_CSR(l_prev_task_id);
1575 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1576 CLOSE TASK_INFO_CSR;
1577
1578 l_wbs_number := l_prev_task_rec.wbs_number || '.1';
1579 l_mask := l_prev_task_rec.wbs_number;
1580 else
1581 -- Previous task is a peer task
1582 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1583 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1584 CLOSE TASK_INFO_CSR;
1585
1586 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, l_task_rec.parent_task_id, p_display_seq);
1587 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1588 CLOSE GET_PREV_PEER_TASK_CSR;
1589
1590 OPEN TASK_INFO_CSR(l_prev_task_id);
1591 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1592 CLOSE TASK_INFO_CSR;
1593
1594 l_increment := to_char(to_number(substr(l_prev_task_rec.wbs_number, length(l_parent_task_rec.wbs_number) + 2)) + 1);
1595 l_wbs_number := substr(l_prev_task_rec.wbs_number, 1, length(l_parent_task_rec.wbs_number)) || '.' || l_increment;
1596 l_mask := l_parent_task_rec.wbs_number;
1597 end if;
1598 end if; -- ig(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1599
1600 -- Update the WBS number for the inserted task
1601 UPDATE PA_PROJ_ELEMENT_VERSIONS
1602 SET wbs_number = l_wbs_number
1603 WHERE element_version_id = p_task_id;
1604
1605 -- Loop through tasks that have a greater display seq than the current
1606 -- and begins with the same mask (in the same branch)
1607 if(l_mask = 'NONE') then
1608 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
1609 else
1610 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
1611 end if;
1612
1613 LOOP
1614 if(l_mask = 'NONE') then
1615 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
1616 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
1617
1618 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
1619 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
1620 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
1621 l_str1 := to_char(to_number(l_str1 + 1));
1622
1623 l_wbs_number := l_str1 || l_str2;
1624 else
1625 l_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) + 1);
1626 end if;
1627 else
1628 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
1629 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
1630
1631 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
1632 if(instr(l_str1, '.') <> 0) then
1633 l_str2 := substr(l_str1, instr(l_str1, '.'));
1634 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
1635 l_str1 := to_char(to_number(l_str1) + 1);
1636
1637 l_wbs_number := l_mask || '.' || l_str1 || l_str2;
1638 else
1639 l_str1 := to_char(to_number(l_str1) + 1);
1640 l_wbs_number := l_mask || '.' || l_str1;
1641 end if;
1642
1643 end if;
1644
1645 -- Update the WBS number
1646 UPDATE PA_PROJ_ELEMENT_VERSIONS
1647 SET wbs_number = l_wbs_number
1648 WHERE element_version_id = l_update_task_rec.task_id;
1649 END LOOP;
1650
1651 if(l_mask = 'NONE') then
1652 CLOSE UPDATE_TASKS_CSR;
1653 else
1654 CLOSE UPDATE_MASKED_TASKS_CSR;
1655 end if;
1656
1657 elsif(p_action = 'INDENT') then
1658 -- Get the last previous peer task
1659 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, l_task_rec.parent_task_id, p_display_seq);
1660 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1661
1662 -- Increment the last digit of the peer task wbs number to
1663 -- get the indented task's wbs number
1664 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1665 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1666 CLOSE TASK_INFO_CSR;
1667
1668 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
1669 -- No previous peer tasks; first peer task under the parent
1670 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1671 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1672 CLOSE TASK_INFO_CSR;
1673
1674 l_wbs_number := l_prev_task_rec.wbs_number || '.1';
1675 else
1676 OPEN TASK_INFO_CSR(l_prev_task_id);
1677 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1678 CLOSE TASK_INFO_CSR;
1679
1680 l_str1 := substr(l_prev_task_rec.wbs_number, 1, length(l_parent_task_rec.wbs_number));
1681 l_str2 := substr(l_prev_task_rec.wbs_number, length(l_parent_task_rec.wbs_number) + 2);
1682 l_str2 := to_char(to_number(l_str2 + 1));
1683
1684 l_wbs_number := l_str1 || '.' || l_str2;
1685 end if;
1686
1687 CLOSE GET_PREV_PEER_TASK_CSR;
1688
1689 l_branch_mask := l_task_rec.wbs_number;
1690 --dbms_output.put_line('L_BRANCH_MASK: ' || l_branch_mask);
1691
1692 --dbms_output.put_line('L_WBS_NUMBER: ' || l_wbs_number);
1693
1694 -- Update the WBS number for the indented task
1695 UPDATE PA_PROJ_ELEMENT_VERSIONS
1696 SET wbs_number = l_wbs_number
1697 WHERE element_version_id = p_task_id;
1698
1699 -- Find l_mask
1700 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1701 FETCH TASK_INFO_CSR INTO l_temp;
1702 CLOSE TASK_INFO_CSR;
1703
1704 if(l_temp.parent_task_id = p_parent_structure_ver_id) then
1705 -- This indented task used to be a top task
1706 l_mask := 'NONE';
1707 else
1708 OPEN TASK_INFO_CSR(l_parent_task_rec.parent_task_id);
1709 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1710 CLOSE TASK_INFO_CSR;
1711
1712 l_mask := l_parent_task_rec.wbs_number;
1713 end if;
1714
1715 --dbms_output.put_line('L_MASK: ' || l_mask);
1716
1717 if(l_mask = 'NONE') then
1718 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
1719 else
1720 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
1721 end if;
1722
1723 LOOP
1724 if(l_mask = 'NONE') then
1725 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
1726 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
1727
1728 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
1729 -- Task is under the indented branch
1730 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
1731 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
1732 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
1733 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
1734 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
1735 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
1736
1737 else
1738
1739 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
1740 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
1741 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
1742 l_str1 := to_char(to_number(l_str1) - 1);
1743
1744 l_loop_wbs_number := l_str1 || l_str2;
1745 else
1746 l_loop_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) - 1);
1747 end if;
1748 end if;
1749
1750 else
1751 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
1752 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
1753
1754 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
1755 -- Task is under the indented branch
1756 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
1757 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
1758 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
1759 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
1760 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
1761 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
1762
1763 else
1764
1765 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
1766 if(instr(l_str1, '.') <> 0) then
1767 l_str2 := substr(l_str1, instr(l_str1, '.'));
1768 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
1769 l_str1 := to_char(to_number(l_str1) - 1);
1770
1771 l_loop_wbs_number := l_mask || '.' || l_str1 || l_str2;
1772 else
1773 l_str1:= to_char(to_number(l_str1) - 1);
1774 l_loop_wbs_number := l_mask || '.' || l_str1;
1775 end if;
1776 end if;
1777
1778 end if;
1779
1780 -- Update the WBS number
1781 UPDATE PA_PROJ_ELEMENT_VERSIONS
1782 SET wbs_number = l_loop_wbs_number
1783 WHERE element_version_id = l_update_task_rec.task_id;
1784 END LOOP;
1785
1786 if(l_mask = 'NONE') then
1787 CLOSE UPDATE_TASKS_CSR;
1788 else
1789 CLOSE UPDATE_MASKED_TASKS_CSR;
1790 end if;
1791
1792 elsif(p_action = 'OUTDENT') then
1793 if(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1794 -- At top level; no parent task
1795 OPEN GET_PREV_TOP_PEER_TASK_CSR(p_parent_structure_ver_id, p_display_seq);
1796 FETCH GET_PREV_TOP_PEER_TASK_CSR INTO l_prev_task_id;
1797
1798 if(GET_PREV_TOP_PEER_TASK_CSR%NOTFOUND) then
1799 x_return_status := 'E';
1800 raise API_ERROR;
1801 end if;
1802 CLOSE GET_PREV_TOP_PEER_TASK_CSR;
1803 else
1804 -- Get the last previous peer task
1805 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, l_task_rec.parent_task_id, p_display_seq);
1806 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1807
1808 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
1809 x_return_status := 'E';
1810 raise API_ERROR;
1811 end if;
1812 CLOSE GET_PREV_PEER_TASK_CSR;
1813 end if;
1814
1815 OPEN TASK_INFO_CSR(l_prev_task_id);
1816 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1817 CLOSE TASK_INFO_CSR;
1818
1819 --dbms_output.put_line('Previous peer task: ' || l_prev_task_rec.wbs_number);
1820
1821 -- Increment the last digit of the peer task wbs number to
1822 -- get the outdented task's wbs number
1823 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1824 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1825 if(TASK_INFO_CSR%NOTFOUND) then
1826 -- Outdented task is now a top task
1827 l_wbs_number := l_prev_task_rec.wbs_number + 1;
1828 else
1829 l_str1 := substr(l_prev_task_rec.wbs_number, 1, length(l_parent_task_rec.wbs_number));
1830 l_str2 := substr(l_prev_task_rec.wbs_number, length(l_parent_task_rec.wbs_number) + 2);
1831 l_str2 := to_char(to_number(l_str2 + 1));
1832
1833 l_wbs_number := l_str1 || '.' || l_str2;
1834 end if;
1835
1836 CLOSE TASK_INFO_CSR;
1837
1838
1839 -- l_branch_mask contains the old wbs_number (before the task was outdented)
1840 l_branch_mask := l_task_rec.wbs_number;
1841 --dbms_output.put_line('L_BRANCH_MASK: ' || l_branch_mask);
1842
1843 -- l_mask2 contains the wbs number of the previous peer task
1844 -- This mask is used to find subsequent peer/child tasks of the outdented task (before it was
1845 -- outdented)
1846 l_mask2 := l_prev_task_rec.wbs_number;
1847 --dbms_output.put_line('L_MASK2: ' || l_mask2);
1848
1849 -- Update the WBS number for the outdented task
1850 UPDATE PA_PROJ_ELEMENT_VERSIONS
1851 SET wbs_number = l_wbs_number
1852 WHERE element_version_id = p_task_id;
1853
1854 --dbms_output.put_line('L_WBS_NUMBER: ' || l_wbs_number);
1855
1856 -- Find l_mask
1857 if(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1858 -- This outdented task has become a top task
1859 l_mask := 'NONE';
1860 else
1861 l_mask := l_parent_task_rec.wbs_number;
1862 end if;
1863
1864 --dbms_output.put_line('L_MASK: ' || l_mask);
1865
1866 if(l_mask = 'NONE') then
1867 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
1868 else
1869 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
1870 end if;
1871
1872 LOOP
1873 if(l_mask = 'NONE') then
1874 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
1875 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
1876
1877 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
1878 -- Task is under the outdented branch
1879 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
1880 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
1881 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
1882 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
1883 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
1884 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
1885
1886 elsif(substr(l_update_task_rec.wbs_number, 1, length(l_mask2)) = l_mask2) then
1887 -- Task used to be a peer of the outdented task
1888 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, p_task_id, l_update_task_rec.display_sequence);
1889 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1890 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
1891 l_loop_wbs_number := l_wbs_number || '.1';
1892 else
1893 OPEN TASK_INFO_CSR(l_prev_task_id);
1894 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1895 CLOSE TASK_INFO_CSR;
1896 l_str1 := substr(l_prev_task_rec.wbs_number, length(l_wbs_number) + 2);
1897 l_str1 := to_char(to_number(l_str1 + 1));
1898 l_loop_wbs_number := l_wbs_number || '.' || l_str1;
1899 end if;
1900 CLOSE GET_PREV_PEER_TASK_CSR;
1901
1902 else
1903 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
1904 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
1905 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
1906 l_str1 := to_char(to_number(l_str1) + 1);
1907
1908 l_loop_wbs_number := l_str1 || l_str2;
1909 else
1910 l_loop_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) + 1);
1911 end if;
1912 end if;
1913
1914 else
1915 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
1916 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
1917
1918 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
1919 -- Task is under the indented branch
1920 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
1921 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
1922 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
1923 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
1924 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
1925 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
1926
1927 elsif(substr(l_update_task_rec.wbs_number, 1, length(l_mask2)) = l_mask2) then
1928 -- Task used to be a peer of the outdented task
1929 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, p_task_id, l_update_task_rec.display_sequence);
1930 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1931 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
1932 --dbms_output.put_line('HELLO2');
1933 l_loop_wbs_number := l_wbs_number || '.1';
1934 else
1935 OPEN TASK_INFO_CSR(l_prev_task_id);
1936 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1937 CLOSE TASK_INFO_CSR;
1938 --dbms_output.put_line('HELLO: ' || l_prev_task_rec.wbs_number);
1939 l_str1 := substr(l_prev_task_rec.wbs_number, length(l_wbs_number) + 2);
1940 l_str1 := to_char(to_number(l_str1 + 1));
1941 l_loop_wbs_number := l_wbs_number || '.' || l_str1;
1942 end if;
1943 CLOSE GET_PREV_PEER_TASK_CSR;
1944
1945 else
1946
1947 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
1948 if(instr(l_str1, '.') <> 0) then
1949 l_str2 := substr(l_str1, instr(l_str1, '.'));
1950 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
1951 l_str1 := to_char(to_number(l_str1) + 1);
1952
1953 l_loop_wbs_number := l_mask || '.' || l_str1 || l_str2;
1954 else
1955 l_str1 := to_char(to_number(l_str1) + 1);
1956 l_loop_wbs_number := l_mask || '.' || l_str1;
1957 end if;
1958 end if;
1959
1960 end if;
1961
1962 -- Update the WBS number
1963 UPDATE PA_PROJ_ELEMENT_VERSIONS
1964 SET wbs_number = l_loop_wbs_number
1965 WHERE element_version_id = l_update_task_rec.task_id;
1966 END LOOP;
1967
1968 if(l_mask = 'NONE') then
1969 CLOSE UPDATE_TASKS_CSR;
1970 else
1971 CLOSE UPDATE_MASKED_TASKS_CSR;
1972 end if;
1973
1974 elsif(p_action = 'DELETE') then
1975 if(p_parent_task_id = p_parent_structure_ver_id ) then
1976 -- Deleted task is a top task
1977 --dbms_output.put_line('Is parent task');
1978 l_mask := 'NONE';
1979 else
1980 -- Deleted task is not a top task
1981 OPEN TASK_INFO_CSR(p_parent_task_id);
1982 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1983 CLOSE TASK_INFO_CSR;
1984
1985 l_mask := l_parent_task_rec.wbs_number;
1986 end if;
1987
1988 -- Loop through tasks that have a greater display seq than the current
1989 -- and begins with the same mask (in the same branch)
1990 if(l_mask = 'NONE') then
1991 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
1992 else
1993 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
1994 end if;
1995
1996 --dbms_output.put_line('L_MASK: '||l_mask);
1997 LOOP
1998 if(l_mask = 'NONE') then
1999 --dbms_output.put_line('IN LOOP');
2000 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
2001 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
2002
2003 ----dbms_output.put_line('L_UPDATE_TASK_REC.WBS_NUMBER: '|| l_update_task_rec.wbs_number);
2004
2005 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
2006 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
2007 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
2008 l_str1 := to_char(to_number(l_str1) - 1);
2009
2010 l_wbs_number := l_str1 || l_str2;
2011 else
2012 l_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) - 1);
2013 end if;
2014 else
2015 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
2016 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
2017
2018 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
2019 if(instr(l_str1, '.') <> 0) then
2020 l_str2 := substr(l_str1, instr(l_str1, '.'));
2021 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
2022 l_str1 := to_char(to_number(l_str1) - 1);
2023
2024 l_wbs_number := l_mask || '.' || l_str1 || l_str2;
2025 else
2026 l_str1 := to_char(to_number(l_str1) - 1);
2027 l_wbs_number := l_mask || '.' || l_str1;
2028 end if;
2029 end if;
2030
2031 -- Update the WBS number
2032 UPDATE PA_PROJ_ELEMENT_VERSIONS
2033 SET wbs_number = l_wbs_number
2034 WHERE element_version_id = l_update_task_rec.task_id;
2035 END LOOP;
2036
2037 if(l_mask = 'NONE') then
2038 CLOSE UPDATE_TASKS_CSR;
2039 else
2040 CLOSE UPDATE_MASKED_TASKS_CSR;
2041 end if;
2042 end if;
2043
2044 x_return_status := 'S';
2045
2046 if(p_commit = 'Y') then
2047 commit;
2048 end if;
2049 EXCEPTION
2050 when API_ERROR then
2051 if p_commit = 'Y' then
2052 rollback to update_wbs_numbers;
2053 end if;
2054
2055 x_return_status := 'E'; -- 4537865
2056 WHEN OTHERS THEN
2057 if p_commit = 'Y' then
2058 rollback to update_wbs_numbers;
2059 end if;
2060
2061 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_TASK_PUB1',
2062 p_procedure_name => 'UPDATE_WBS_NUMBERS',
2063 p_error_text => SUBSTRB(SQLERRM,1,240));
2064 x_return_status := 'E';
2065 END UPDATE_WBS_NUMBERS;
2066
2067
2068 FUNCTION task_exists_in_struc_ver(
2069 p_structure_version_id NUMBER,
2070 p_task_version_id NUMBER ) RETURN VARCHAR2 IS
2071
2072 CURSOR cur_ppev
2073 IS
2074 SELECT 'x'
2075 FROM pa_proj_element_versions
2076 WHERE element_version_id = p_task_version_id
2077 AND parent_structure_version_id = p_structure_version_id;
2078 l_dummy_char VARCHAR2(1);
2079 BEGIN
2080 OPEN cur_ppev;
2081 FETCH cur_ppev INTO l_dummy_char;
2082 IF cur_ppev%FOUND
2083 THEN
2084 CLOSE cur_ppev;
2085 RETURN 'Y';
2086 ELSE
2087 CLOSE cur_ppev;
2088 RETURN 'N';
2089 END IF;
2090
2091 END task_exists_in_struc_ver;
2092
2093 FUNCTION GET_LINKED_TASK_VERSION_ID(
2094 p_cur_element_id NUMBER ,
2095 p_cur_element_version_id NUMBER
2096 ) RETURN NUMBER IS
2097
2098 CURSOR cur_obj_rel
2099 IS
2100 SELECT object_id_to1
2101 FROM pa_object_relationships
2102 WHERE object_id_from1 = p_cur_element_version_id
2103 AND relationship_type = 'L';
2104 l_linked_task_ver_id NUMBER;
2105 BEGIN
2106 --A linking task can only link one task.
2107
2108 IF LINK_FLAG( p_cur_element_id ) = 'Y'
2109 THEN
2110 OPEN cur_obj_rel;
2111 FETCH cur_obj_rel INTO l_linked_task_ver_id;
2112 CLOSE cur_obj_rel;
2113 RETURN l_linked_task_ver_id;
2114 ELSE
2115 RETURN p_cur_element_version_id;
2116 END IF;
2117
2118 END GET_LINKED_TASK_VERSION_ID;
2119
2120 FUNCTION LINK_FLAG( p_element_id NUMBER ) RETURN VARCHAR2 IS
2121 CURSOR cur_proj_elements
2122 IS
2123 SELECT link_task_flag
2124 FROM pa_proj_elements
2125 WHERE proj_element_id = p_element_id;
2126
2127 l_link_task_flag VARCHAR2(1) :='N'; --bug 4180390
2128 BEGIN
2129
2130 /* comenting the code for bug 4180390
2131 OPEN cur_proj_elements;
2132 FETCH cur_proj_elements INTO l_link_task_flag;
2133 CLOSE cur_proj_elements;
2134 */
2135
2136 RETURN l_link_task_flag;
2137
2138 END LINK_FLAG;
2139
2140
2141 -- API name : CHECK_TASK_IN_STRUCTURE
2142 -- Type : Utils API
2143 -- Pre-reqs : None
2144 -- Return Value : Y if task is in structure; N for task in
2145 -- different struture.
2146 --
2147 -- Parameters
2148 -- p_structure_version_id IN NUMBER
2149 -- p_task_version_id IN NUMBER
2150 --
2151 -- History
2152 --
2153 -- 09-JAN-02 HSIU -Created
2154 --
2155 FUNCTION CHECK_TASK_IN_STRUCTURE(p_structure_version_id NUMBER,
2156 p_task_version_id NUMBER)
2157 RETURN VARCHAR2 IS
2158 CURSOR c1 IS
2159 select '1'
2160 from pa_proj_element_versions
2161 where p_task_version_id = element_version_id
2162 and p_structure_version_id = parent_structure_version_id;
2163
2164 l_dummy VARCHAR2(1);
2165 BEGIN
2166 OPEN c1;
2167 FETCH c1 INTO l_dummy;
2168 IF (c1%NOTFOUND) THEN
2169 CLOSE c1;
2170 return 'N';
2171 END IF;
2172 CLOSE c1;
2173 return 'Y';
2174 END CHECK_TASK_IN_STRUCTURE;
2175
2176
2177 FUNCTION GET_DISPLAY_PARENT_VERSION_ID(p_element_version_id NUMBER,
2178 p_parent_element_version_id NUMBER,
2179 p_relationship_type VARCHAR2,
2180 p_link_task_flag VARCHAR2)
2181 RETURN NUMBER IS
2182 cursor get_display_parent_id IS
2183 select object_id_from1
2184 from pa_object_relationships
2185 where relationship_type = 'S'
2186 and object_id_to1 = p_parent_element_version_id;
2187 l_display_parent_version_id NUMBER;
2188 BEGIN
2189 IF (p_relationship_type = 'L') THEN
2190 --return parent of the parent element version
2191 OPEN get_display_parent_id;
2192 FETCH get_display_parent_id into l_display_parent_version_id;
2193 CLOSE get_display_parent_id;
2194 return l_display_parent_version_id;
2195 ELSIF (p_link_task_flag = 'Y') THEN
2196 return to_number(NULL);
2197 END IF;
2198 --a normal task. Return its current parent
2199 return p_parent_element_version_id;
2200
2201 END GET_DISPLAY_PARENT_VERSION_ID;
2202
2203
2204 FUNCTION IS_ACTIVE_TASK(p_element_version_id NUMBER,
2205 p_object_type VARCHAR2)
2206 RETURN VARCHAR2
2207 IS
2208
2209 -- CURSOR c1 IS
2210 -- select 1
2211 -- from pa_percent_completes ppc,
2212 -- pa_proj_element_versions pev
2213 -- where pev.element_version_id = p_element_version_id
2214 -- and pev.project_id = ppc.project_id
2215 -- and pev.proj_element_id = ppc.task_id
2216 -- and ppc.submitted_flag = 'Y'
2217 -- and ppc.current_flag = 'Y'
2218 -- and ppc.actual_start_date IS NOT NULL
2219 -- and ppc.actual_finish_date IS NULL;
2220 -- l_dummy NUMBER;
2221 BEGIN
2222 -- OPEN c1;
2223 -- FETCH c1 into l_dummy;
2224 -- IF c1%FOUND THEN
2225 -- CLOSE c1;
2226 -- return 'Y';
2227 -- END IF;
2228 -- CLOSE c1;
2229 -- return 'N';
2230 return 'Y';
2231 END IS_ACTIVE_TASK;
2232
2233 FUNCTION Get_DAYS_TO_START(p_element_version_id NUMBER,
2234 p_object_type VARCHAR2)
2235 RETURN NUMBER
2236 IS
2237 CURSOR c1 IS
2238 select scheduled_start_date
2239 from pa_proj_elem_ver_schedule sch,
2240 pa_proj_element_versions ev
2241 where p_element_version_id = ev.element_version_id
2242 and ev.element_version_id = sch.element_version_id
2243 and ev.project_id = sch.project_id;
2244 l_date DATE;
2245 BEGIN
2246 IF (p_object_type = 'PA_STRUCTURES') THEN
2247 RETURN to_number(NULL);
2248 END IF;
2249 OPEN c1;
2250 FETCH c1 into l_date;
2251 IF c1%NOTFOUND THEN
2252 CLOSE c1;
2253 return to_number(NULL);
2254 END IF;
2255 CLOSE c1;
2256 return TRUNC(l_date) - TRUNC(SYSDATE);
2257 END Get_DAYS_TO_START;
2258
2259 FUNCTION Get_DAYS_TO_FINISH(p_element_version_id NUMBER,
2260 p_object_type VARCHAR2)
2261 RETURN NUMBER
2262 IS
2263 CURSOR c1 IS
2264 select scheduled_finish_date
2265 from pa_proj_elem_ver_schedule sch,
2266 pa_proj_element_versions ev
2267 where p_element_version_id = ev.element_version_id
2268 and ev.element_version_id = sch.element_version_id
2269 and ev.project_id = sch.project_id;
2270 l_date DATE;
2271 BEGIN
2272 IF (p_object_type = 'PA_STRUCTURES') THEN
2273 RETURN to_number(NULL);
2274 END IF;
2275 OPEN c1;
2276 FETCH c1 into l_date;
2277 IF c1%NOTFOUND THEN
2278 CLOSE c1;
2279 return to_number(NULL);
2280 END IF;
2281 CLOSE c1;
2282 return TRUNC(l_date) - TRUNC(sysdate);
2283 END Get_DAYS_TO_FINISH;
2284
2285 FUNCTION GET_PREV_SCH_START_DATE(p_element_version_id NUMBER,
2286 p_parent_structure_version_id NUMBER)
2287 RETURN DATE
2288 IS
2289 CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2290 select sch.scheduled_start_date
2291 from pa_proj_elem_ver_schedule sch,
2292 pa_proj_element_versions pev,
2293 pa_proj_element_versions pev2
2294 where pev.project_id = c_project_id
2295 and pev.parent_structure_version_id = c_structure_version_id
2296 and pev.element_version_id = sch.element_version_id
2297 and pev.project_id = sch.project_id
2298 and pev.proj_element_id = pev2.proj_element_id
2299 and pev.project_id = pev2.project_id
2300 and pev2.element_version_id = p_element_version_id;
2301
2302 CURSOR c2 IS
2303 select str.project_id, str.element_version_id
2304 from pa_proj_elem_ver_structure str,
2305 pa_proj_element_versions pev
2306 where pev.element_version_id = p_parent_structure_version_id
2307 and pev.project_id = str.project_id
2308 and pev.proj_element_id = str.proj_element_id
2309 and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
2310 l_project_id NUMBER;
2311 l_structure_version_id NUMBER;
2312 l_date DATE;
2313 BEGIN
2314 OPEN c2;
2315 FETCH c2 into l_project_id, l_structure_version_id;
2316 IF c2%NOTFOUND THEN
2317 --no published version
2318 CLOSE c2;
2319 return to_date(NULL);
2320 END IF;
2321 CLOSE c2;
2322
2323 OPEN c1(l_project_id, l_structure_version_id);
2324 FETCH c1 into l_date;
2325 CLOSE c1;
2326
2327 return l_date;
2328
2329 END GET_PREV_SCH_START_DATE;
2330
2331 FUNCTION GET_PREV_SCH_FINISH_DATE(p_element_version_id NUMBER,
2332 p_parent_structure_version_id NUMBER)
2333 RETURN DATE
2334 IS
2335 CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2336 select sch.scheduled_finish_date
2337 from pa_proj_elem_ver_schedule sch,
2338 pa_proj_element_versions pev,
2339 pa_proj_element_versions pev2
2340 where pev.project_id = c_project_id
2341 and pev.parent_structure_version_id = c_structure_version_id
2342 and pev.element_version_id = sch.element_version_id
2343 and pev.project_id = sch.project_id
2344 and pev.proj_element_id = pev2.proj_element_id
2345 and pev.project_id = pev2.project_id
2346 and pev2.element_version_id = p_element_version_id;
2347
2348 CURSOR c2 IS
2349 select str.project_id, str.element_version_id
2350 from pa_proj_elem_ver_structure str,
2351 pa_proj_element_versions pev
2352 where pev.element_version_id = p_parent_structure_version_id
2353 and pev.project_id = str.project_id
2354 and pev.proj_element_id = str.proj_element_id
2355 and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
2356 l_project_id NUMBER;
2357 l_structure_version_id NUMBER;
2358 l_date DATE;
2359 BEGIN
2360 OPEN c2;
2361 FETCH c2 into l_project_id, l_structure_version_id;
2362 IF c2%NOTFOUND THEN
2363 --no published version
2364 CLOSE c2;
2365 return to_date(NULL);
2366 END IF;
2367 CLOSE c2;
2368
2369 OPEN c1(l_project_id, l_structure_version_id);
2370 FETCH c1 into l_date;
2371 CLOSE c1;
2372
2373 return l_date;
2374
2375 END GET_PREV_SCH_FINISH_DATE;
2376
2377 FUNCTION CHECK_IS_FINANCIAL_TASK(p_proj_element_id NUMBER)
2378 RETURN VARCHAR2
2379 IS
2380 cursor c1 IS
2381 SELECT 1
2382 FROM PA_TASKS
2383 WHERE task_id = p_proj_element_id;
2384 l_dummy NUMBER;
2385 BEGIN
2386 OPEN c1;
2387 FETCH c1 into l_dummy;
2388 IF c1%NOTFOUND THEN
2389 CLOSE c1;
2390 return 'N';
2391 ELSE
2392 CLOSE c1;
2393 return 'Y';
2394 END IF;
2395 END CHECK_IS_FINANCIAL_TASK;
2396
2397 FUNCTION CONVERT_HR_TO_DAYS(p_hour NUMBER)
2398 RETURN NUMBER
2399 IS
2400
2401 l_fte_day NUMBER := 0;
2402
2403 cursor get_fte_days_csr
2404 IS
2405 SELECT fte_day
2406 FROM pa_implementations;
2407
2408 BEGIN
2409
2410 --commented out for bug 3612309
2411 -- OPEN get_fte_days_csr;
2412 -- FETCH get_fte_days_csr INTO l_fte_day;
2413 -- IF get_fte_days_csr%NOTFOUND then
2414 -- return 0;
2415 -- end if;
2416 -- CLOSE get_fte_days_csr;
2417
2418 -- if l_fte_day is null or l_fte_day = 0 then
2419 -- l_fte_day := 8;
2420 -- end if;
2421
2422 --dbms_output.put_line('fte_days = '||l_fte_day);
2423
2424 -- return round(p_hour/l_fte_day,2);
2425 return p_hour;
2426
2427 END CONVERT_HR_TO_DAYS;
2428
2429
2430 FUNCTION GET_FND_LOOKUP_MEANING(p_lookup_type VARCHAR2,
2431 p_lookup_code VARCHAR2)
2432 RETURN VARCHAR2
2433 IS
2434 /*
2435 cursor c1 is
2436 select meaning
2437 from fnd_lookups
2438 where lookup_type = p_lookup_type
2439 and lookup_code = p_lookup_code;
2440 l_dummy varchar2(80);
2441 BEGIN
2442 -- Bug Fix 5611871
2443 IF p_lookup_code IS NULL THEN
2444 l_dummy := NULL;
2445 RETURN l_dummy;
2446 END IF;
2447 -- End of Bug Fix 5611871
2448
2449 open c1;
2450 FETCH c1 into l_dummy;
2451 If c1%NOTFOUND THEN
2452 l_dummy := NULL;
2453 END IF;
2454 close c1;
2455 return l_dummy;
2456 */
2457
2458 -- Bug 6156686
2459 cursor c1 is
2460 select meaning
2461 from fnd_lookups
2462 where lookup_type = p_lookup_type
2463 and lookup_code = p_lookup_code;
2464 l_dummy varchar2(80);
2465 l_index varchar2(100);
2466 BEGIN
2467 l_index := p_lookup_type || '*'||p_lookup_code;
2468 IF l_fndlkp_cache_tbl.EXISTS(l_index) THEN
2469 l_dummy := l_fndlkp_cache_tbl(l_index);
2470 ELSE
2471 open c1;
2472 FETCH c1 into l_dummy;
2473 If c1%NOTFOUND THEN
2474 l_dummy := NULL;
2475 END IF;
2476 close c1;
2477 l_fndlkp_cache_tbl(l_index) := l_dummy;
2478
2479 END IF;
2480 return l_dummy;
2481
2482 END GET_FND_LOOKUP_MEANING;
2483
2484
2485 FUNCTION GET_PA_LOOKUP_MEANING(p_lookup_type VARCHAR2,
2486 p_lookup_code VARCHAR2)
2487 RETURN VARCHAR2
2488 IS
2489 cursor c1 is
2490 select meaning
2491 from pa_lookups
2492 where lookup_type = p_lookup_type
2493 and lookup_code = p_lookup_code;
2494 l_dummy varchar2(80);
2495 l_index varchar2(100); -- Bug 6156686
2496 BEGIN
2497 -- Bug 6156686
2498 l_index := p_lookup_type || '*'||p_lookup_code;
2499 IF l_lookup_cache_tbl.EXISTS(l_index) THEN
2500 l_dummy := l_lookup_cache_tbl(l_index);
2501 ELSE
2502 open c1;
2503 FETCH c1 into l_dummy;
2504 If c1%NOTFOUND THEN
2505 l_dummy := NULL;
2506 END IF;
2507 close c1;
2508 l_lookup_cache_tbl(l_index) := l_dummy;
2509
2510 END IF;
2511
2512 return l_dummy;
2513 END GET_PA_LOOKUP_MEANING;
2514
2515 -- API name : GET_DEFAULT_TASK_TYPE_ID
2516 -- Type : Utils API
2517 -- Pre-reqs : None
2518 -- Return Value : Default task type_id
2519 --
2520 -- Parameters
2521 --
2522 -- History
2523 --
2524 -- 26-JUL-02 HSIU -Created
2525 --
2526 FUNCTION GET_DEFAULT_TASK_TYPE_ID
2527 return NUMBER
2528 IS
2529 BEGIN
2530 return 1;
2531 END GET_DEFAULT_TASK_TYPE_ID;
2532
2533 FUNCTION IS_TASK_TYPE_USED(p_task_type_id IN NUMBER)
2534 return VARCHAR2
2535 IS
2536
2537 /* Bug2680486 -- Performance changes -- Commented the following cursor query and restructured it. */
2538 /* cursor c1 IS
2539 select 'Y'
2540 from PA_PROJ_ELEMENTS
2541 where type_id = p_task_type_id;
2542 */
2543
2544 cursor c1 IS
2545 select 'Y'
2546 from dual
2547 where exists (
2548 select 'xyz'
2549 from PA_PROJ_ELEMENTS
2550 where type_id = p_task_type_id
2551 AND project_id > -1
2552 AND object_type = 'PA_TASKS'
2553 );
2554
2555 l_ret_val VARCHAR2(1);
2556 BEGIN
2557 OPEN c1;
2558 FETCH c1 into l_ret_val;
2559 IF c1%NOTFOUND THEN
2560 l_ret_val := 'N';
2561 END IF;
2562 CLOSE c1;
2563 return l_ret_val;
2564 END IS_TASK_TYPE_USED;
2565
2566 -- API name : GET_LATEST_FIN_PUB_TASK_VER_ID
2567 -- Type : Utils API
2568 -- Pre-reqs : None
2569 -- Return Value : Task version id of the latest financial
2570 -- published task
2571 --
2572 -- Parameters
2573 -- p_project_id IN NUMBER
2574 -- p_task_id IN NUMBER
2575 --
2576 -- History
2577 --
2578 -- 26-JUL-02 HSIU -Created
2579 --
2580 FUNCTION GET_LATEST_FIN_PUB_TASK_VER_ID(
2581 p_project_id IN NUMBER
2582 ,p_task_id IN NUMBER
2583 ) return NUMBER
2584 IS
2585 CURSOR c1(c_structure_version_id NUMBER) IS
2586 select ppev.element_version_id
2587 from pa_proj_element_versions ppev
2588 where parent_structure_version_id = c_structure_version_id
2589 and project_id = p_project_id
2590 and proj_element_id = p_task_id;
2591 l_structure_version_id NUMBER;
2592 l_task_version_id NUMBER;
2593 BEGIN
2594 l_structure_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(p_project_id);
2595
2596 OPEN c1(l_structure_version_id);
2597 FETCH c1 into l_task_version_id;
2598 CLOSE c1;
2599 return l_task_version_id;
2600 END GET_LATEST_FIN_PUB_TASK_VER_ID;
2601
2602
2603 -- API name : CHECK_MODIFY_OK_FOR_STATUS
2604 -- Type : Utils API
2605 -- Pre-reqs : None
2606 -- Return Value : Check if this task can be modified with its
2607 -- current status. Y can be modified, N cannot.
2608 --
2609 -- Parameters
2610 -- p_project_id IN NUMBER
2611 -- p_task_id IN NUMBER
2612 --
2613 -- History
2614 --
2615 -- 26-JUL-02 HSIU -Created
2616 --
2617 FUNCTION CHECK_MODIFY_OK_FOR_STATUS(
2618 p_project_id IN NUMBER
2619 ,p_task_id IN NUMBER
2620 ) return VARCHAR2
2621 IS
2622 --bug 2863836: modified cursor to refer to system status
2623 cursor c1 is
2624 select b.project_system_status_code
2625 from pa_proj_elements a, pa_project_statuses b
2626 where a.proj_element_id = p_task_id
2627 and a.status_code = b.project_status_code
2628 and b.status_type = 'TASK';
2629 l_dummy pa_proj_elements.status_code%TYPE;
2630 l_retval VARCHAR2(1);
2631 BEGIN
2632 open c1;
2633 FETCH c1 into l_dummy;
2634 CLOSE c1;
2635 IF (l_dummy = 'ON_HOLD' OR l_dummy = 'CANCELLED') THEN
2636 return 'N';
2637 END IF;
2638 return 'Y';
2639 END CHECK_MODIFY_OK_FOR_STATUS;
2640
2641
2642 -- API name : GET_DISPLAY_SEQUENCE
2643 -- Type : FUNCTION
2644 -- Pre-reqs : N/A
2645 -- Return Value : The display sequence for a given task.
2646 --
2647 -- Parameters
2648 -- p_task_id IN NUMBER
2649 --
2650 -- History
2651 --
2652 -- 16-OCT-02 XXLU -Created
2653 --
2654 FUNCTION GET_DISPLAY_SEQUENCE (
2655 p_task_id IN NUMBER
2656 ) RETURN NUMBER
2657 IS
2658
2659 l_element_version_id pa_proj_element_versions.element_version_id%TYPE;
2660
2661 CURSOR c1(p_task_id IN NUMBER) IS
2662 SELECT project_id
2663 FROM pa_tasks
2664 WHERE task_id = p_task_id;
2665
2666 v_c1 c1%ROWTYPE;
2667
2668 CURSOR c2 (p_proj_element_id IN NUMBER) IS
2669 SELECT element_version_id
2670 FROM pa_proj_element_versions
2671 WHERE proj_element_id = p_proj_element_id;
2672
2673 v_c2 c2%ROWTYPE;
2674
2675 CURSOR c3 (p_element_version_id IN NUMBER) IS
2676 SELECT display_sequence
2677 FROM pa_proj_element_versions
2678 WHERE element_version_id = p_element_version_id;
2679
2680 v_c3 c3%ROWTYPE;
2681
2682
2683 BEGIN
2684
2685 OPEN c1 (p_task_id);
2686 FETCH c1 INTO v_c1;
2687 CLOSE c1;
2688
2689 l_element_version_id := PA_PROJ_ELEMENTS_UTILS.GET_LATEST_FIN_PUB_TASK_VER_ID
2690 (p_project_id => v_c1.project_id,
2691 p_task_id => p_task_id);
2692
2693 IF l_element_version_id IS NULL THEN
2694 OPEN c2 (p_task_id);
2695 FETCH c2 INTO v_c2;
2696 CLOSE c2;
2697
2698 l_element_version_id := v_c2.element_version_id;
2699
2700 END IF;
2701
2702 OPEN c3(l_element_version_id);
2703 FETCH c3 INTO v_c3;
2704 CLOSE c3;
2705
2706 RETURN(v_c3.display_sequence);
2707
2708 END GET_DISPLAY_SEQUENCE;
2709
2710 procedure Check_Del_all_task_Ver_Ok
2711 (
2712 p_project_id IN NUMBER
2713 ,p_task_version_id IN NUMBER
2714 ,p_parent_structure_ver_id IN NUMBER
2715 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2716 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
2717 )
2718 IS
2719 CURSOR c1 IS
2720 select object_Id_to1
2721 from pa_object_relationships
2722 where object_type_to = 'PA_TASKS'
2723 and relationship_type = 'S'
2724 start with object_id_from1 = p_task_version_id
2725 and object_type_from = 'PA_TASKS'
2726 and relationship_type = 'S'
2727 connect by prior object_id_to1 = object_id_from1
2728 and prior object_type_to = object_type_from
2729 and relationship_type = prior relationship_type; --Bug 3792616
2730 l_task_ver_id NUMBER;
2731
2732 l_task_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ; -- 4201927 for performance issue
2733 BEGIN
2734
2735 PA_PROJ_ELEMENTS_UTILS.CHECK_DELETE_TASK_VER_OK(
2736 p_project_id => p_project_id
2737 ,p_task_version_id => p_task_version_id
2738 ,p_parent_structure_ver_id => p_parent_structure_ver_id
2739 ,x_return_status => x_return_status
2740 ,x_error_message_code => x_error_message_code
2741 );
2742 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2743 raise FND_API.G_EXC_ERROR;
2744 END IF;
2745
2746
2747 -- 4201927 commented below code for performance issue
2748 /*
2749 OPEN c1;
2750 LOOP
2751 FETCH c1 into l_task_ver_id;
2752 EXIT WHEN c1%NOTFOUND;
2753 PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2754 p_project_id => p_project_id
2755 ,p_task_version_id => l_task_ver_id
2756 ,p_parent_structure_ver_id => p_parent_structure_ver_id
2757 ,x_return_status => x_return_status
2758 ,x_error_message_code => x_error_message_code
2759 );
2760 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2761 raise FND_API.G_EXC_ERROR;
2762 END IF;
2763 END LOOP;
2764 CLOSE c1;
2765 */
2766
2767 -- using bulk collect approach and then iterating through table
2768
2769 OPEN c1 ;
2770 FETCH c1 BULK COLLECT INTO l_task_ver_id_tbl;
2771 CLOSE c1 ;
2772
2773 IF nvl(l_task_ver_id_tbl.LAST,0) > 0 THEN
2774 FOR i in reverse l_task_ver_id_tbl.FIRST..l_task_ver_id_tbl.LAST LOOP
2775
2776 PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2777 p_project_id => p_project_id
2778 ,p_task_version_id => l_task_ver_id_tbl(i)
2779 ,p_parent_structure_ver_id => p_parent_structure_ver_id
2780 ,x_return_status => x_return_status
2781 ,x_error_message_code => x_error_message_code
2782 );
2783 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2784 raise FND_API.G_EXC_ERROR;
2785 END IF;
2786
2787 END LOOP;
2788 END IF;
2789
2790 -- 4201927 end
2791
2792 x_return_status := FND_API.G_RET_STS_SUCCESS;
2793
2794 EXCEPTION
2795 WHEN FND_API.G_EXC_ERROR THEN
2796 x_return_status := FND_API.G_RET_STS_ERROR;
2797 -- 4537865 NOT RESETTING x_error_message_code AS IT WILL reach this point only after x_error_message_code is set
2798 WHEN OTHERS THEN
2799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2800 -- 4537865
2801 x_error_message_code := SQLCODE ;
2802 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
2803 p_procedure_name => 'Check_Del_all_task_Ver_Ok
2804 ');
2805 RAISE;
2806 END Check_Del_all_task_Ver_Ok;
2807
2808 procedure Check_create_subtask_ok
2809 ( p_parent_task_ver_id IN NUMBER
2810 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2811 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
2812 )
2813 IS
2814 CURSOR get_status IS
2815 select ppe.project_id, ppe.proj_element_id
2816 from pa_project_statuses pps,
2817 pa_proj_elements ppe,
2818 pa_proj_element_versions ppev
2819 where ppev.element_version_id = p_parent_task_ver_id
2820 and ppe.project_id = ppev.project_id
2821 and ppe.proj_element_id = ppev.proj_element_id
2822 and ppe.status_code = pps.project_status_code
2823 and pps.project_system_status_code IN ('ON_HOLD', 'CANCELLED')
2824 and pps.status_type = 'TASK';
2825 l_project_id NUMBER;
2826 l_proj_element_id NUMBER;
2827
2828 CURSOR get_task_info IS
2829 select ppev.project_id, ppev.proj_element_id
2830 from pa_proj_element_versions ppev
2831 where ppev.element_version_id = p_parent_task_ver_id;
2832
2833 CURSOR get_schedule_info IS
2834 select 1
2835 from pa_proj_elem_ver_schedule ppvsch,
2836 pa_proj_element_versions ppev
2837 where ppev.element_version_id = p_parent_task_ver_id
2838 and ppev.project_id = ppvsch.project_id
2839 and ppev.proj_element_id = ppvsch.proj_element_id
2840 and ppev.element_version_id = ppvsch.element_version_id
2841 and ( ppvsch.wq_planned_quantity IS NOT NULL AND ppvsch.wq_planned_quantity <> 0 );
2842 l_dummy NUMBER;
2843
2844 l_err_code NUMBER:= 0;
2845 l_err_stack VARCHAR2(630);
2846 l_err_stage VARCHAR2(80);
2847
2848 l_return_status VARCHAR2(1);
2849 l_msg_data VARCHAR2(2000);
2850 l_msg_count NUMBER;
2851 BEGIN
2852 OPEN get_status;
2853 FETCH get_status into l_project_id, l_proj_element_id;
2854 IF get_status%NOTFOUND THEN
2855 x_return_status := 'Y';
2856 ELSE
2857 x_return_status := 'N';
2858 x_error_message_code := 'PA_PS_ONHOLD_CANCEL_TK_ERR';
2859 CLOSE get_status;
2860 return;
2861 END IF;
2862 CLOSE get_status;
2863
2864 OPEN get_task_info;
2865 FETCH get_task_info into l_project_id, l_proj_element_id;
2866 CLOSE get_task_info;
2867
2868 --hsiu: bug 2674107
2869 --if workplan, check if ok to create subtask.
2870 OPEN get_schedule_info;
2871 FETCH get_schedule_info into l_dummy;
2872 IF (get_schedule_info%FOUND) THEN
2873 x_return_status := 'N';
2874 x_error_message_code := 'PA_PS_PARENT_TK_PWQ_ERR';
2875 return;
2876 END IF;
2877 CLOSE get_schedule_info;
2878
2879 -- Begin fix for Bug # 4266540.
2880
2881 l_return_status := pa_relationship_utils.check_task_has_sub_proj(l_project_id
2882 , l_proj_element_id
2883 , p_parent_task_ver_id);
2884
2885 if (l_return_status = 'Y') then
2886
2887 x_return_status := 'N';
2888 x_error_message_code := 'PA_PS_TASK_HAS_SUB_PROJ';
2889 return;
2890
2891 end if;
2892
2893 l_return_status := null;
2894
2895 -- End fix for Bug # 4266540.
2896
2897 --if financial, check if ok to create subtask.
2898 --Bug 5988335 Adding condition for partial share project to skip financial validation
2899 --when a new subtask is getting created in workplan.
2900 If (PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id) = 'Y') AND
2901 (PA_PROJ_TASK_STRUC_PUB.GET_SHARE_TYPE(l_project_id) <> 'SHARE_PARTIAL') --Bug 5988335
2902 THEN
2903 PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK(x_task_id => l_proj_element_id,
2904 x_err_code => l_err_code,
2905 x_err_stack => l_err_stack,
2906 x_err_stage => l_err_stage
2907 );
2908 IF (l_err_code <> 0) THEN
2909 x_return_status := 'N';
2910 x_error_message_code := substrb(l_err_stage,1,30); -- 4537865 changed substr to substrb
2911 return;
2912 ELSE
2913 x_return_status := 'Y';
2914 return;
2915 END IF;
2916 ELSE
2917 --bug 3055708 (for financial or shared str it will be taken
2918 --care in PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK)
2919 PA_TASK_PUB1.Check_Task_Has_Association(
2920 p_task_id => l_proj_element_id
2921 ,x_return_status => l_return_status
2922 ,x_msg_count => l_msg_count
2923 ,x_msg_data => l_msg_data
2924
2925 );
2926
2927 IF (l_return_status <> 'S') Then
2928 x_return_status := 'N';
2929 x_error_message_code := l_msg_data;
2930 return;
2931 END IF;
2932 --end bug 3055708
2933
2934 --bug 3305199: cannot create subtask if dep exists in parent when
2935 --option for no dep in summary task is Y
2936 IF (PA_PROJECT_STRUCTURE_UTILS.check_dep_on_summary_tk_ok(l_project_id) = 'N') THEN
2937 IF ('Y' = PA_RELATIONSHIP_UTILS.CHECK_DEP_EXISTS(p_parent_task_ver_id)) THEN
2938 x_return_status := 'N';
2939 x_error_message_code := 'PA_DEP_ON_SUMM_TSK';
2940 return;
2941 END IF;
2942 END IF;
2943 END IF;
2944 --bug 3947726
2945 --do not allow sub-tasks creation if the lowest level task has progress.
2946 --This is also applicable for shared case if the task has only ETC but not actual. If actual is there then it means it will be stopped in expenditure items validation.
2947 IF pa_proj_task_struc_pub.wp_str_exists(l_project_id) = 'Y'
2948 AND pa_task_assignment_utils.get_task_level_record( l_project_id, p_parent_task_ver_id ) IS NOT NULL
2949 AND PA_PROGRESS_UTILS.check_object_has_prog(
2950 p_project_id => l_project_id
2951 ,p_proj_element_id => l_proj_element_id
2952 ,p_object_id => l_proj_element_id
2953 ,p_object_type => 'PA_TASKS'
2954 ,p_structure_type => 'WORKPLAN'
2955 ) = 'Y'
2956 AND PA_PROGRESS_UTILS.check_ta_has_prog(
2957 p_project_id => l_project_id
2958 ,p_proj_element_id => l_proj_element_id
2959 ,p_element_ver_id => p_parent_task_ver_id
2960 ) = 'Y' --Added for 7015986
2961 THEN
2962 x_return_status := 'N';
2963 x_error_message_code := 'PA_PS_TASK_HAS_PROG_ADD';
2964 return;
2965 END IF;
2966 --end bug 3947726
2967 -- 4537865
2968 EXCEPTION
2969 WHEN OTHERS THEN
2970 x_return_status := 'N';
2971 x_error_message_code := SQLCODE ;
2972 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
2973 p_procedure_name => 'Check_create_subtask_ok',
2974 p_error_text => SUBSTRB(SQLERRM,1,240));
2975 RAISE ;
2976 END Check_create_subtask_ok;
2977
2978
2979 FUNCTION Check_task_stus_action_allowed
2980 (p_task_status_code IN VARCHAR2,
2981 p_action_code IN VARCHAR2 ) return
2982 VARCHAR2
2983 IS
2984 CURSOR l_taskstus_csr IS
2985 SELECT enabled_flag, project_system_status_code
2986 FROM pa_project_status_controls
2987 WHERE project_status_code = p_task_status_code
2988 AND action_code = p_action_code;
2989
2990 l_action_allowed VARCHAR2(1) := 'N';
2991
2992 l_proj_sys_status_code VARCHAR2(30);
2993 BEGIN
2994 OPEN l_taskstus_csr;
2995 FETCH l_taskstus_csr INTO l_action_allowed, l_proj_sys_status_code;
2996 IF l_taskstus_csr%NOTFOUND THEN
2997 CLOSE l_taskstus_csr;
2998 RETURN 'N';
2999 END IF;
3000 CLOSE l_taskstus_csr;
3001
3002 RETURN (NVL(l_action_allowed,'N'));
3003
3004 EXCEPTION
3005 WHEN OTHERS THEN
3006 RETURN 'N';
3007 END Check_task_stus_action_allowed;
3008
3009 -- hyau new apis for lifecycle changes
3010
3011 -- API name : CHECK_ELEMENT_HAS_PHASE
3012 -- Type : FUNCTION
3013 -- Pre-reqs : N/A
3014 -- Return Value : 'Y' if the element has a phase associated with it, else returns 'N'.
3015 --
3016 -- Parameters
3017 -- p_proj_element_id IN NUMBER
3018 --
3019 -- History
3020 --
3021 -- 30-OCT-02 hyau -Created
3022 --
3023 FUNCTION CHECK_ELEMENT_HAS_PHASE (
3024 p_proj_element_id IN NUMBER) RETURN
3025
3026 VARCHAR2
3027 IS
3028 CURSOR l_element_has_phase_csr IS
3029 SELECT 'Y'
3030 FROM pa_proj_elements
3031 WHERE proj_element_id = p_proj_element_id
3032 AND phase_version_id is not null;
3033
3034 l_has_phase VARCHAR2(1) := 'N';
3035
3036 BEGIN
3037 OPEN l_element_has_phase_csr;
3038 FETCH l_element_has_phase_csr INTO l_has_phase;
3039 IF l_element_has_phase_csr%NOTFOUND THEN
3040 CLOSE l_element_has_phase_csr;
3041 RETURN 'N';
3042 END IF;
3043 CLOSE l_element_has_phase_csr;
3044
3045 RETURN (NVL(l_has_phase,'N'));
3046
3047 EXCEPTION
3048 WHEN OTHERS THEN
3049 RETURN 'N';
3050 END CHECK_ELEMENT_HAS_PHASE;
3051
3052
3053 -- API name : IS_TOP_TASK_ACROSS_ALL_VER
3054 -- Type : FUNCTION
3055 -- Pre-reqs : N/A
3056 -- Return Value : 'Y' if the task is a top task across all versions, else returns 'N'.
3057 --
3058 -- Parameters
3059 -- p_proj_element_id IN NUMBER
3060 --
3061 -- History
3062 --
3063 -- 30-OCT-02 hyau -Created
3064 --
3065 FUNCTION IS_TOP_TASK_ACROSS_ALL_VER(
3066 p_proj_element_id IN NUMBER) RETURN
3067
3068 VARCHAR2
3069 IS
3070 CURSOR l_exist_non_top_task_csr IS
3071 select 'N'
3072 from pa_proj_elements ppe,
3073 pa_proj_element_versions ppev
3074 where ppe.proj_element_id = p_proj_element_id
3075 and ppe.proj_element_id = ppev.proj_element_id
3076 and nvl(ppev.wbs_level, 0) <> 1;
3077
3078 l_is_top_task VARCHAR2(1) := 'N';
3079
3080 BEGIN
3081 OPEN l_exist_non_top_task_csr;
3082 FETCH l_exist_non_top_task_csr INTO l_is_top_task;
3083 IF l_exist_non_top_task_csr%NOTFOUND THEN
3084 CLOSE l_exist_non_top_task_csr;
3085 RETURN 'Y';
3086 END IF;
3087 CLOSE l_exist_non_top_task_csr;
3088
3089 RETURN (NVL(l_is_top_task,'N'));
3090
3091 EXCEPTION
3092 WHEN OTHERS THEN
3093 RETURN 'N';
3094 END IS_TOP_TASK_ACROSS_ALL_VER;
3095
3096 -- API name : CHECK_PHASE_IN_USE
3097 -- Type : FUNCTION
3098 -- Pre-reqs : N/A
3099 -- Return Value : 'Y' if the phase is already used by another task in the structure, else returns 'N'.
3100 --
3101 -- Parameters
3102 -- p_task_id NUMBER
3103 -- phase_version_id NUMBER
3104 --
3105 -- History
3106 --
3107 -- 30-OCT-02 hyau -Created
3108 --
3109 FUNCTION CHECK_PHASE_IN_USE(
3110 p_task_id IN NUMBER
3111 ,p_phase_version_id IN NUMBER) RETURN
3112
3113 VARCHAR2
3114 IS
3115
3116 /* Bug 2680486 -- Performance changes -- Added join of project_id in the following cursor*/
3117 CURSOR l_phase_in_use_csr IS
3118 select 'Y'
3119 from pa_proj_elements ppe,
3120 pa_proj_elements ppe2
3121 where ppe.proj_element_id = p_task_id
3122 and ppe.parent_structure_id = ppe2.parent_structure_id
3123 and ppe2.phase_version_id = p_phase_version_id
3124 and ppe2.proj_element_id <> p_task_id
3125 and ppe2.project_id = ppe.project_id;
3126
3127 l_phase_in_use VARCHAR2(1) := 'Y';
3128
3129 BEGIN
3130 OPEN l_phase_in_use_csr;
3131 FETCH l_phase_in_use_csr INTO l_phase_in_use;
3132 IF l_phase_in_use_csr%NOTFOUND THEN
3133 CLOSE l_phase_in_use_csr;
3134 RETURN 'N';
3135 END IF;
3136 CLOSE l_phase_in_use_csr;
3137
3138 RETURN (NVL(l_phase_in_use,'Y'));
3139
3140 EXCEPTION
3141 WHEN OTHERS THEN
3142 RETURN 'N';
3143 END CHECK_PHASE_IN_USE;
3144
3145
3146 -- end hyau new apis for lifecycle changes
3147
3148 PROCEDURE Check_Fin_Task_Published(p_project_id IN NUMBER,
3149 p_task_id IN NUMBER,
3150 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
3151 x_error_message_code OUT NOCOPY VARCHAR2) -- 4537865
3152 IS
3153 CURSOR c1 is
3154 select 1 from
3155 pa_proj_elements a,
3156 pa_proj_element_versions b,
3157 pa_proj_elem_ver_structure c
3158 where a.proj_element_id = p_task_id
3159 and a.project_id = p_project_id
3160 and a.project_id = b.project_id
3161 and a.proj_element_id = b.proj_element_id
3162 and b.project_Id = c.project_id
3163 and b.parent_structure_version_id = c.element_version_id
3164 and c.status_code = 'STRUCTURE_PUBLISHED';
3165
3166 CURSOR c2 IS
3167 select 1 from
3168 pa_tasks a
3169 where a.task_id = p_task_id;
3170
3171 l_dummy NUMBER;
3172
3173 BEGIN
3174 OPEN c1;
3175 FETCH c1 into l_dummy;
3176 IF c1%NOTFOUND THEN
3177 --check if task has financial component
3178 OPEN c2;
3179 FETCH c2 into l_dummy;
3180 IF c2%NOTFOUND THEN
3181 x_return_status := 'N';
3182 x_error_message_code := 'PA_PS_TSK_NOT_PUB_ERR';
3183 ELSE
3184 x_return_status := 'Y';
3185 END IF;
3186 CLOSE c2;
3187 ELSE
3188 x_return_status := 'Y';
3189 END IF;
3190 CLOSE c1;
3191
3192 -- 4537865
3193 EXCEPTION
3194 WHEN OTHERS THEN
3195 x_return_status := 'N' ;
3196 x_error_message_code := SQLCODE ;
3197
3198 -- not included add_exc_msg call because caller of this API doesnt expect it.
3199 RAISE ;
3200 END Check_Fin_Task_Published;
3201
3202 PROCEDURE check_move_task_ok
3203 (
3204 p_task_ver_id IN NUMBER
3205 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3206 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
3207 )
3208 IS
3209 CURSOR get_status IS
3210 select '1'
3211 from pa_project_statuses pps,
3212 pa_proj_elements ppe,
3213 pa_proj_element_versions ppev
3214 where ppev.element_version_id = p_task_ver_id
3215 and ppe.project_id = ppev.project_id
3216 and ppe.proj_element_id = ppev.proj_element_id
3217 and ppe.status_code = pps.project_status_code
3218 and pps.project_system_status_code IN ('ON_HOLD', 'CANCELLED', 'COMPLETED')
3219 and pps.status_type = 'TASK';
3220 l_dummy varchar2(1);
3221 BEGIN
3222 OPEN get_status;
3223 FETCH get_status into l_dummy;
3224 IF get_status%NOTFOUND THEN
3225 x_return_status := 'Y';
3226 ELSE
3227 x_return_status := 'N';
3228 x_error_message_code := 'PA_PS_MOVE_TK_STAT_ERR';
3229 END IF;
3230 CLOSE get_status;
3231 -- 4537865
3232 EXCEPTION
3233 WHEN OTHERS THEN
3234 x_return_status := 'N' ;
3235 x_error_message_code := SQLCODE ;
3236 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3237 p_procedure_name => 'check_move_task_ok',
3238 p_error_text => SUBSTRB(SQLERRM,1,240));
3239 RAISE ;
3240 END check_move_task_ok;
3241
3242
3243 -- API name :
3244 -- Type : PROCEDURE
3245 -- Pre-reqs : N/A
3246 -- Return Value : Sucess, Error, or Unexpected error
3247 --
3248 -- Parameters
3249 -- p_task_id IN NUMBER
3250 -- p_task_version_id IN NUMBER
3251 -- p_new_task_status IN VARCHAR2
3252 -- x_return_status OUT VARCHAR2
3253 -- x_error_message_code OUT VARCHAR2
3254 --
3255 -- History
3256 --
3257 -- 30-OCT-02 hyau -Created
3258 --
3259 PROCEDURE Check_chg_stat_cancel_ok
3260 (
3261 p_task_id IN NUMBER
3262 ,p_task_version_id IN NUMBER
3263 ,p_new_task_status IN VARCHAR2
3264 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3265 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
3266 )
3267 IS
3268 CURSOR c1 IS
3269 select b.project_id, b.proj_element_id
3270 from pa_proj_element_versions a,
3271 pa_proj_elem_ver_structure b
3272 where a.element_version_id = p_task_version_id
3273 and a.parent_structure_version_id = b.element_version_id
3274 and a.project_id = b.project_id;
3275 l_project_id NUMBER;
3276 l_structure_id NUMBER;
3277 l_xtra_task_id NUMBER;
3278 l_versioned VARCHAR2(1);
3279
3280 CURSOR c2(c_project_id NUMBER, c_structure_id NUMBER) IS
3281 select distinct ppe.proj_element_id
3282 from pa_proj_element_versions ppe
3283 where ppe.element_version_id IN (
3284 select object_id_to1
3285 from pa_object_relationships
3286 where relationship_type = 'S'
3287 start with object_id_from1 IN (
3288 select a.element_version_id
3289 from pa_proj_element_versions a,
3290 pa_proj_elem_ver_structure b
3291 where b.project_id = c_project_id
3292 and b.proj_element_id = c_structure_id
3293 and b.status_code <> 'STRUCTURE_PUBLISHED'
3294 and b.element_version_id = a.parent_structure_version_id
3295 and a.proj_element_id = p_task_id)
3296 and relationship_type = 'S'
3297 connect by object_id_from1 = prior object_id_to1
3298 and object_type_from = prior object_type_to
3299 and relationship_type = prior relationship_type)
3300 minus
3301 select ppe.proj_element_id
3302 from pa_proj_element_versions ppe
3303 where ppe.element_version_id IN (
3304 select object_id_to1
3305 from pa_object_relationships
3306 where relationship_type = 'S'
3307 start with object_id_from1 = p_task_version_id
3308 and object_type_from = 'PA_TASKS'
3309 and relationship_type = 'S'
3310 connect by object_id_from1 = prior object_id_to1
3311 and object_type_from = prior object_type_to
3312 and relationship_type = prior relationship_type);
3313
3314 CURSOR c3(c_project_id NUMBER, c_structure_id NUMBER, c_xtra_task_id NUMBER) IS
3315 select ppev.element_version_id, ppev.TASK_UNPUB_VER_STATUS_CODE
3316 from pa_proj_element_Versions ppev,
3317 pa_proj_elem_ver_structure ppevs
3318 where ppev.proj_element_id = c_xtra_task_id
3319 and ppev.project_id = c_project_id
3320 and ppev.parent_structure_version_id = ppevs.element_version_id
3321 and ppevs.project_id = c_project_id
3322 and ppevs.proj_element_id = c_structure_id
3323 and ppevs.status_code <> 'STRUCTURE_PUBLISHED';
3324 l_xtra_task_ver_id NUMBER;
3325 l_task_ver_status VARCHAR2(30);
3326
3327 BEGIN
3328 OPEN c1;
3329 FETCH c1 into l_project_id, l_structure_id;
3330 CLOSE c1;
3331
3332 --Check if versioning is enabled
3333 l_versioned := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(
3334 l_project_id);
3335
3336 IF (l_versioned = 'Y') THEN
3337 OPEN c2(l_project_id, l_structure_id);
3338 LOOP
3339 --get the task id of all tasks that are in the working version but not in
3340 -- the latest published version.
3341 FETCH c2 into l_xtra_task_id;
3342 EXIT WHEN c2%NOTFOUND;
3343 OPEN c3(l_project_id, l_structure_id, l_xtra_task_id);
3344 LOOP
3345 --get all the task version status of unpublished tasks
3346 FETCH c3 INTO l_xtra_task_ver_id, l_task_ver_status;
3347 EXIT WHEN c3%NOTFOUND;
3348 IF (l_task_ver_status = 'PUBLISHED') THEN
3349 x_error_message_code:= 'PA_PS_TK_STAT_CNL_ERR';
3350 raise FND_API.G_EXC_ERROR;
3351 END IF;
3352 END LOOP;
3353 CLOSE c3;
3354 END LOOP;
3355 CLOSE c2;
3356 END IF;
3357
3358 x_return_status := FND_API.G_RET_STS_SUCCESS;
3359 EXCEPTION
3360 WHEN FND_API.G_EXC_ERROR THEN
3361 x_return_status := FND_API.G_RET_STS_ERROR;
3362 -- 4537865
3363 -- Not resetting x_error_message_code as at this point it would have been already populated.
3364 WHEN OTHERS THEN
3365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3366 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3367 p_procedure_name => 'Check_chg_stat_cancel_ok');
3368
3369 -- 4537865
3370 x_error_message_code := SQLCODE ;
3371 RAISE ;
3372 END Check_chg_stat_cancel_ok;
3373
3374 FUNCTION get_element_name(p_proj_element_id IN NUMBER) RETURN VARCHAR2
3375 IS
3376 l_ret VARCHAR2(240);
3377 BEGIN
3378 IF p_proj_element_id IS NULL OR p_proj_element_id<0 THEN
3379 RETURN NULL;
3380 END IF;
3381
3382 SELECT name
3383 INTO l_ret
3384 FROM pa_proj_elements
3385 WHERE proj_element_id = p_proj_element_id;
3386
3387 RETURN l_ret;
3388 EXCEPTION
3389 WHEN OTHERS THEN
3390 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3391 p_procedure_name => 'get_element_name');
3392 RAISE;
3393 END get_element_name;
3394
3395 FUNCTION get_element_number(p_proj_element_id IN NUMBER) RETURN VARCHAR2
3396 IS
3397 l_ret VARCHAR2(100);
3398 BEGIN
3399 IF p_proj_element_id IS NULL OR p_proj_element_id<0 THEN
3400 RETURN NULL;
3401 END IF;
3402
3403 SELECT element_number
3404 INTO l_ret
3405 FROM pa_proj_elements
3406 WHERE proj_element_id = p_proj_element_id;
3407
3408 RETURN l_ret;
3409 EXCEPTION
3410 WHEN OTHERS THEN
3411 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3412 p_procedure_name => 'get_element_number');
3413 RAISE;
3414 END get_element_number;
3415
3416 FUNCTION get_element_name_number(p_proj_element_id IN NUMBER) RETURN VARCHAR2
3417 IS
3418 l_ret VARCHAR2(1000);
3419 BEGIN
3420 IF p_proj_element_id IS NULL OR p_proj_element_id<0 THEN
3421 RETURN NULL;
3422 END IF;
3423
3424 SELECT name||'('||element_number||')'
3425 INTO l_ret
3426 FROM pa_proj_elements
3427 WHERE proj_element_id = p_proj_element_id;
3428
3429 RETURN l_ret;
3430 EXCEPTION
3431 WHEN OTHERS THEN
3432 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3433 p_procedure_name => 'get_element_name_number');
3434 RAISE;
3435 END get_element_name_number;
3436
3437 function check_child_element_exist(p_element_version_id NUMBER) RETURN VARCHAR2
3438 IS
3439 l_dummy number;
3440
3441 -- Bug 6156686
3442 cursor child_exist IS
3443 select null
3444 from dual where exists
3445 (select null from pa_object_relationships
3446 where object_id_from1 = p_element_version_id
3447 and relationship_type = 'S');
3448 BEGIN
3449
3450 OPEN child_exist;
3451 FETCH child_exist into l_dummy;
3452 IF child_exist%NOTFOUND then
3453 --Cannot find child. It is lowest task
3454 CLOSE child_exist;
3455 return 'N';
3456 ELSE
3457 --Child found. Not lowest task
3458 CLOSE child_exist;
3459 return 'Y';
3460 END IF;
3461 EXCEPTION
3462 WHEN OTHERS THEN
3463 return (SQLCODE);
3464 END check_child_element_exist;
3465
3466
3467 FUNCTION get_task_status_sys_code(p_task_status_code VARCHAR2) RETURN VARCHAR2
3468 IS
3469 l_task_stat_sys_code VARCHAR2(30);
3470 BEGIN
3471 select project_system_status_code into l_task_stat_sys_code
3472 from pa_project_statuses
3473 where p_task_status_code = project_status_code
3474 and status_type = 'TASK';
3475
3476 return l_task_stat_sys_code;
3477 EXCEPTION
3478 WHEN OTHERS THEN
3479 return NULL;
3480 END get_task_status_sys_code;
3481
3482 -- Returns the element_version_id of the next/previous task, given the project id, parent structure
3483 -- version id, and the display sequence of the current task. Pass value "NEXT" and "PREVIOUS" for
3484 -- p_previous_or_next parameter to indicate whether to get the next or the previous task. Returns
3485 -- -1 if there are no next/previous task.
3486 FUNCTION get_next_prev_task_id(
3487 p_project_id IN NUMBER
3488 ,p_structure_version_id IN NUMBER
3489 ,p_display_seq_id IN NUMBER
3490 ,p_previous_or_next IN VARCHAR2) RETURN NUMBER
3491 IS
3492 l_element_version_Id NUMBER;
3493
3494 cursor c_previous_task_id( c_project_id NUMBER, c_struct_version_id NUMBER, c_display_seq NUMBER) IS
3495 select element_version_id
3496 from pa_proj_element_versions
3497 where project_id = c_project_id
3498 and parent_structure_version_id = c_struct_version_id
3499 and display_sequence = (
3500 select max(display_sequence)
3501 from pa_proj_element_versions
3502 where project_id = c_project_id
3503 and parent_structure_version_id = c_struct_version_id
3504 and display_sequence < c_display_seq
3505 );
3506
3507 cursor c_next_task_id( c_project_id NUMBER, c_struct_version_id NUMBER, c_display_seq NUMBER) IS
3508 select element_version_id
3509 from pa_proj_element_versions
3510 where project_id = c_project_id
3511 and parent_structure_version_id = c_struct_version_id
3512 and display_sequence = (
3513 select min(display_sequence)
3514 from pa_proj_element_versions
3515 where project_id = c_project_id
3516 and parent_structure_version_id = c_struct_version_id
3517 and display_sequence > c_display_seq
3518 );
3519
3520 BEGIN
3521
3522 IF p_previous_or_next IS NOT NULL and p_previous_or_next = 'PREVIOUS' then
3523 OPEN c_previous_task_id(p_project_id, p_structure_version_id, p_display_seq_id);
3524 FETCH c_previous_task_id into l_element_version_Id;
3525 IF c_previous_task_id%NOTFOUND then
3526 CLOSE c_previous_task_id;
3527 return -1;
3528 ELSE
3529 CLOSE c_previous_task_id;
3530 return l_element_version_Id;
3531 END IF;
3532 ELSIF p_previous_or_next IS NOT NULL and p_previous_or_next = 'NEXT' then
3533 OPEN c_next_task_id(p_project_id, p_structure_version_id, p_display_seq_id);
3534 FETCH c_next_task_id into l_element_version_Id;
3535 IF c_next_task_id%NOTFOUND then
3536 CLOSE c_next_task_id;
3537 return -1;
3538 ELSE
3539 CLOSE c_next_task_id;
3540 return l_element_version_Id;
3541 END IF;
3542 END IF;
3543
3544 EXCEPTION
3545 WHEN OTHERS THEN
3546 return -1;
3547 END get_next_prev_task_id;
3548
3549 /*==================================================================
3550 This api obtains the structure version id to which task versions
3551 should be created and added to. This API is to be called only from
3552 the AMG context. Included the api for Post FP K one off. Bug 2931183.
3553 This api also returns the task unpublished version status code.
3554 This value is used when we create a task version.
3555 ==================================================================*/
3556
3557 PROCEDURE GET_STRUCTURE_INFO
3558 ( p_project_id IN pa_projects_all.project_id%TYPE
3559 ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
3560 ,p_structure_id IN pa_proj_elements.proj_element_id%TYPE
3561 ,p_is_wp_separate_from_fn IN VARCHAR2
3562 ,p_is_wp_versioning_enabled IN VARCHAR2
3563 ,x_structure_version_id OUT NOCOPY pa_proj_element_versions.element_version_id%TYPE -- 4537865
3564 ,x_task_unpub_ver_status_code OUT NOCOPY pa_proj_element_versions.task_unpub_ver_status_code%TYPE -- 4537865
3565 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3566 ,x_msg_count OUT NOCOPY NUMBER -- 4537865
3567 ,x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
3568 AS
3569
3570
3571 -- Cursors used in this API.
3572 CURSOR cur_struc_ver_wp(c_project_id number,c_structure_type varchar2,c_status_code varchar2)
3573 IS
3574 SELECT c.element_version_id
3575 FROM pa_proj_element_versions c,
3576 pa_structure_types a,
3577 pa_proj_structure_types b
3578 ,pa_proj_elem_ver_structure d
3579 WHERE c.project_id = c_project_id
3580 AND a.structure_type_id = b.structure_type_id
3581 AND b.proj_element_id = c.proj_element_id
3582 AND a.structure_type = c_structure_type
3583 AND d.project_id = c.project_id
3584 AND d.element_version_id = c.element_version_id
3585 AND d.status_code = c_status_code;
3586
3587 CURSOR cur_struc_ver_fin(c_project_id number,c_structure_type varchar2)
3588 IS
3589 SELECT c.element_version_id
3590 FROM pa_proj_element_versions c,
3591 pa_structure_types a,
3592 pa_proj_structure_types b,
3593 pa_proj_elem_ver_structure d
3594 WHERE c.project_id = c_project_id
3595 AND a.structure_type_id = b.structure_type_id
3596 AND b.proj_element_id = c.proj_element_id
3597 AND a.structure_type = c_structure_type
3598 AND d.project_id = c.project_id
3599 AND d.element_version_id = c.element_version_id
3600 AND d.status_code = 'STRUCTURE_PUBLISHED'
3601 AND d.latest_eff_published_flag = 'Y';
3602 -- End cursors used in this API.
3603
3604 l_msg_count NUMBER := 0;
3605 l_data VARCHAR2(2000);
3606 l_msg_data VARCHAR2(2000);
3607 l_msg_index_out NUMBER;
3608 l_debug_mode VARCHAR2(1);
3609
3610 l_debug_level2 CONSTANT NUMBER := 2;
3611 l_debug_level3 CONSTANT NUMBER := 3;
3612 l_debug_level4 CONSTANT NUMBER := 4;
3613 l_debug_level5 CONSTANT NUMBER := 5;
3614
3615 BEGIN
3616 IF l_debug_mode = 'Y' THEN
3617 pa_debug.g_err_stage:= 'Entering GET_STRUCTURE_INFO';
3618 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3619 l_debug_level2);
3620 END IF;
3621
3622 x_msg_count := 0;
3623 x_return_status := FND_API.G_RET_STS_SUCCESS;
3624 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3625
3626 IF l_debug_mode = 'Y' THEN --For bug 4252182
3627
3628 pa_debug.set_curr_function( p_function => 'GET_STRUCTURE_INFO',
3629 p_debug_mode => l_debug_mode );
3630 END IF;
3631
3632 -- Check for business rules violations
3633
3634 IF l_debug_mode = 'Y' THEN
3635
3636 pa_debug.g_err_stage:= 'Input parameter List :';
3637 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3638 l_debug_level3);
3639
3640 pa_debug.g_err_stage:= 'p_project_id : ' || p_project_id;
3641 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3642 l_debug_level3);
3643
3644
3645 pa_debug.g_err_stage:= 'p_structure_type : ' || p_structure_type;
3646 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3647 l_debug_level3);
3648
3649 pa_debug.g_err_stage:= 'p_structure_id : ' || p_structure_id;
3650 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3651 l_debug_level3);
3652
3653 pa_debug.g_err_stage:= 'p_is_wp_separate_from_fn :' || p_is_wp_separate_from_fn;
3654 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3655 l_debug_level3);
3656
3657 pa_debug.g_err_stage:= 'p_is_wp_versioning_enabled :' || p_is_wp_versioning_enabled;
3658 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3659 l_debug_level3);
3660 END IF;
3661
3662
3663 IF (p_project_id IS NULL) OR
3664 (p_structure_type IS NULL) OR
3665 (p_structure_id IS NULL) OR
3666 (p_is_wp_separate_from_fn IS NULL) OR
3667 (p_is_wp_versioning_enabled IS NULL)
3668 THEN
3669 PA_UTILS.ADD_MESSAGE
3670 (p_app_short_name => 'PA',
3671 p_msg_name => 'PA_INV_PARAM_PASSED'); -- Bug 2955589. Changed the message name to
3672 RAISE Invalid_Arg_Exc_WP; -- have a generic message.
3673
3674 END IF;
3675
3676 /*
3677 If workplan context, if versioning is enabled get the working version. else
3678 get the published version. In financial context get working version. If
3679 working version could not be found, get the published version.
3680 */
3681 IF p_structure_type = 'WORKPLAN' THEN
3682 IF p_is_wp_separate_from_fn = 'Y' AND p_is_wp_versioning_enabled = 'Y' THEN
3683 x_structure_version_id :=
3684 PA_PROJECT_STRUCTURE_UTILS.get_last_updated_working_ver(p_structure_id);
3685 x_task_unpub_ver_status_code := 'WORKING';
3686 END IF;
3687
3688 IF x_structure_version_id is null THEN
3689 open cur_struc_ver_wp(p_project_id,'WORKPLAN','STRUCTURE_PUBLISHED');
3690 fetch cur_struc_ver_wp into x_structure_version_id;
3691 close cur_struc_ver_wp;
3692 x_task_unpub_ver_status_code := 'PUBLISHED';
3693 END IF;
3694 ELSE -- structure type is financial
3695 open cur_struc_ver_wp(p_project_id,'FINANCIAL','STRUCTURE_WORKING');
3696 fetch cur_struc_ver_wp into x_structure_version_id;
3697 close cur_struc_ver_wp;
3698 x_task_unpub_ver_status_code := 'WORKING';
3699
3700 IF x_structure_version_id is null THEN
3701 open cur_struc_ver_fin(p_project_id,'FINANCIAL');
3702 fetch cur_struc_ver_fin into x_structure_version_id;
3703 close cur_struc_ver_fin;
3704 x_task_unpub_ver_status_code := 'PUBLISHED';
3705 END IF;
3706 END IF;
3707
3708 IF l_debug_mode = 'Y' THEN
3709 pa_debug.g_err_stage:= 'Obtained structure version id : '||x_structure_version_id;
3710 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3711 l_debug_level3);
3712 pa_debug.g_err_stage:= 'Task Unpublished version status code : '||x_task_unpub_ver_status_code;
3713 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3714 l_debug_level3);
3715 pa_debug.g_err_stage:= 'Exiting GET_STRUCTURE_INFO';
3716 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3717 l_debug_level2);
3718 END IF;
3719
3720 IF l_debug_mode = 'Y' THEN --For bug 4252182
3721 pa_debug.reset_curr_function;
3722 End IF;
3723 EXCEPTION
3724
3725 WHEN Invalid_Arg_Exc_WP THEN
3726
3727 x_return_status := FND_API.G_RET_STS_ERROR;
3728 l_msg_count := FND_MSG_PUB.count_msg;
3729
3730 IF cur_struc_ver_wp%ISOPEN THEN
3731 CLOSE cur_struc_ver_wp;
3732 END IF;
3733
3734 -- 4537865 : Start
3735 x_task_unpub_ver_status_code := NULL ;
3736 x_structure_version_id := NULL ;
3737 -- 4537865 : End
3738
3739 IF cur_struc_ver_fin%ISOPEN THEN
3740 CLOSE cur_struc_ver_fin;
3741 END IF;
3742
3743 IF l_msg_count = 1 and x_msg_data IS NULL THEN
3744 PA_INTERFACE_UTILS_PUB.get_messages
3745 (p_encoded => FND_API.G_TRUE
3746 ,p_msg_index => 1
3747 ,p_msg_count => l_msg_count
3748 ,p_msg_data => l_msg_data
3749 ,p_data => l_data
3750 ,p_msg_index_out => l_msg_index_out);
3751 x_msg_data := l_data;
3752 x_msg_count := l_msg_count;
3753 ELSE
3754 x_msg_count := l_msg_count;
3755 END IF;
3756
3757 IF l_debug_mode = 'Y' THEN --For bug 4252182
3758 pa_debug.reset_curr_function;
3759 End IF;
3760 RETURN;
3761
3762 WHEN others THEN
3763
3764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3765 x_msg_count := 1;
3766 x_msg_data := SQLERRM;
3767
3768 IF cur_struc_ver_wp%ISOPEN THEN
3769 CLOSE cur_struc_ver_wp;
3770 END IF;
3771
3772 IF cur_struc_ver_fin%ISOPEN THEN
3773 CLOSE cur_struc_ver_fin;
3774 END IF;
3775
3776 -- 4537865 : Start
3777 x_task_unpub_ver_status_code := NULL ;
3778 x_structure_version_id := NULL;
3779 -- 4537865 : End
3780
3781 FND_MSG_PUB.add_exc_msg
3782 ( p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS'
3783 ,p_procedure_name => 'GET_STRUCTURE_INFO'
3784 ,p_error_text => x_msg_data);
3785
3786 IF l_debug_mode = 'Y' THEN
3787 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3788 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3789 l_debug_level5);
3790 pa_debug.reset_curr_function;
3791 END IF;
3792 RAISE;
3793 END GET_STRUCTURE_INFO;
3794
3795 --Begin add rtarway FP.M Develepment
3796 -- Procedure : CHECK_TASK_HAS_TRANSACTION
3797 -- Type : Public Procedure
3798 -- Purpose : This procedure will check whether the task has transaction or not.This API will be
3799 -- : called from Set_Financial_task_API.
3800 -- Note : Check whether it is a financial task or workplan task.
3801 -- : Fetch the parent_structure_version_id for the passed proj_element_Id from
3802 -- : PA_PROJ_ELEMENT_VERSIONS and pass to API PA_PROJ_ELEMENTS_UTILS.structure_type
3803
3804 -- Assumptions : Only called for Financial task
3805
3806 -- Parameters Type Required Description and Purpose
3807 -- --------------------------- ------ -------- --------------------------------------------------------
3808 -- p_task_id NUMBER Yes This indicates the task ID for which the transaction needs to be checked.
3809
3810
3811 PROCEDURE CHECK_TASK_HAS_TRANSACTION
3812 (
3813 p_api_version IN NUMBER := 1.0
3814 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3815 , p_debug_mode IN VARCHAR2 := 'N'
3816 , p_task_id IN NUMBER
3817 , p_project_id IN NUMBER -- Added for Performance fix 4903460
3818 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3819 , x_msg_count OUT NOCOPY NUMBER -- 4537865
3820 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
3821 , x_error_msg_code OUT NOCOPY VARCHAR2 -- 4537865
3822 , x_error_code OUT NOCOPY NUMBER -- 4537865
3823 )
3824 IS
3825
3826 l_msg_count NUMBER := 0;
3827 l_data VARCHAR2(2000);
3828 l_msg_data VARCHAR2(2000);
3829 l_msg_index_out NUMBER;
3830 l_debug_mode VARCHAR2(1);
3831
3832 l_prnt_str_ver_id NUMBER;
3833 l_return_val VARCHAR2(1);
3834 l_used_in_OTL BOOLEAN;
3835 l_status_code NUMBER;
3836 l_return_status VARCHAR2(1);
3837 Is_IEX_Installed BOOLEAN;
3838
3839 l_debug_level2 CONSTANT NUMBER := 2;
3840 l_debug_level3 CONSTANT NUMBER := 3;
3841 l_debug_level4 CONSTANT NUMBER := 4;
3842 l_debug_level5 CONSTANT NUMBER := 5;
3843
3844 --This cursor will select the parent structure version id for the passed proj_elem_id, here it is used for task
3845 --CURSOR c_get_parent_str_ver_id (task_id NUMBER)
3846 --IS
3847 --SELECT PARENT_STRUCTURE_VERSION_ID
3848 --FROM PA_PROJ_ELEMENT_VERSIONS
3849 --WHERE PROJ_ELEMENT_ID = task_id;
3850
3851 --Bug 3735089
3852 l_user_id NUMBER;
3853 l_login_id NUMBER;
3854
3855 BEGIN
3856 x_msg_count := 0;
3857 x_return_status := FND_API.G_RET_STS_SUCCESS;
3858 --Bug 3735089 - instead of fnd_profile.value use fnd_profile.value_specific
3859 --l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3860 l_user_id := fnd_global.user_id;
3861 l_login_id := fnd_global.login_id;
3862 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
3863
3864 IF l_debug_mode = 'Y' THEN
3865 PA_DEBUG.set_curr_function( p_function =>'CHECK_TASK_HAS_TRANSACTION' , p_debug_mode => l_debug_mode );
3866 END IF;
3867
3868 IF l_debug_mode = 'Y' THEN
3869 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Printing Input parameters';
3870 Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3871 Pa_Debug.WRITE(g_module_name , 'p_task_id'||':'||p_task_id , l_debug_level3);
3872 END IF;
3873
3874
3875 IF l_debug_mode = 'Y' THEN
3876 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Validating Input Paramater';
3877 Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3878 END IF;
3879
3880 -- Validating for Input parameter
3881 IF ( p_task_id IS NOT NULL ) THEN
3882
3883 --Commented to be reviewed once more with set financial task
3884 --IF l_debug_mode = 'Y' THEN
3885 -- Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Checking for financial type';
3886 -- Pa_Debug.WRITE ( g_module_name , Pa_Debug.g_err_stage , l_debug_level3 );
3887 --END IF;
3888
3889 --Select the parent structure version id for the passed task id
3890 --OPEN c_get_parent_str_ver_id ( p_task_id );
3891 --FETCH c_get_parent_str_ver_id INTO l_prnt_str_ver_id;
3892 --CLOSE c_get_parent_str_ver_id;
3893
3894 --check if the structure type is financial
3895 --IF (
3896 -- PA_PROJ_ELEMENTS_UTILS.structure_type
3897 -- ( p_structure_version_id => l_prnt_str_ver_id
3898 -- , p_task_version_id => null
3899 -- , p_structure_type => 'FINANCIAL'
3900 -- ) = 'Y'
3901 -- )THEN
3902 --put the tests of check_delete_task_ok here.
3903
3904 -- Commenting code for 4903460 and replacing this wth new code
3905 --Check if task has expenditure item
3906 /* IF l_debug_mode = 'Y' THEN
3907 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check expenditure item for '|| p_task_id;
3908 Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3909 END IF;
3910 l_status_code :=
3911 pa_proj_tsk_utils.check_exp_item_exists(null, p_task_id);
3912 IF ( l_status_code = 1 ) THEN
3913 x_error_code := 50;
3914 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
3915 return;
3916 ELSIF ( l_status_code < 0 ) THEN
3917 x_error_code := l_status_code;
3918 return;
3919 END IF;
3920
3921 --Check if task has purchase order distribution
3922 IF l_debug_mode = 'Y' THEN
3923 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order for '|| p_task_id;
3924 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3925 l_debug_level3);
3926 END IF;
3927 l_status_code :=
3928 pa_proj_tsk_utils.check_po_dist_exists(NULL, p_task_id);
3929 IF ( l_status_code = 1 ) THEN
3930 x_error_code := 60;
3931 x_error_msg_code := 'PA_TSK_PO_DIST_EXIST';
3932 return;
3933 ELSIF ( l_status_code < 0 ) THEN
3934 x_error_code := l_status_code;
3935 return;
3936 END IF;
3937
3938 -- Check if task has purchase order requisition
3939 IF l_debug_mode = 'Y' THEN
3940 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order requisition for '|| p_task_id;
3941 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3942 l_debug_level3);
3943 END IF;
3944
3945 l_status_code :=
3946 pa_proj_tsk_utils.check_po_req_dist_exists(NULL, p_task_id);
3947 IF ( l_status_code = 1 ) THEN
3948 x_error_code := 70;
3949 x_error_msg_code := 'PA_TSK_PO_REQ_DIST_EXIST';
3950 return;
3951 ELSIF ( l_status_code < 0 ) THEN
3952 x_error_code := l_status_code;
3953 return;
3954 END IF;
3955
3956 -- Check if task has supplier invoices
3957 IF l_debug_mode = 'Y' THEN
3958 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier invoice for '|| p_task_id;
3959 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3960 l_debug_level3);
3961 END IF;
3962
3963 l_status_code :=
3964 pa_proj_tsk_utils.check_ap_invoice_exists(NULL, p_task_id);
3965 IF ( l_status_code = 1 ) THEN
3966 x_error_code := 80;
3967 x_error_msg_code := 'PA_TSK_AP_INV_EXIST';
3968 return;
3969 ELSIF ( l_status_code < 0 ) THEN
3970 x_error_code := l_status_code;
3971 return;
3972 END IF;
3973
3974 -- Check if task has supplier invoice distribution
3975 IF l_debug_mode = 'Y' THEN
3976 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier inv distribution for '|| p_task_id;
3977 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3978 l_debug_level3);
3979 END IF;
3980
3981 l_status_code :=
3982 pa_proj_tsk_utils.check_ap_inv_dist_exists(NULL, p_task_id);
3983 IF ( l_status_code = 1 ) THEN
3984 x_error_code := 90;
3985 x_error_msg_code := 'PA_TSK_AP_INV_DIST_EXIST';
3986 return;
3987 ELSIF ( l_status_code < 0 ) THEN
3988 x_error_code := l_status_code;
3989 return;
3990 END IF;
3991
3992 -- Check if task has commitment transaction
3993 IF l_debug_mode = 'Y' THEN
3994 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check commitment transaction for '|| p_task_id;
3995 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3996 l_debug_level3);
3997 END IF;
3998 l_status_code :=
3999 pa_proj_tsk_utils.check_commitment_txn_exists(null, p_task_id);
4000 IF ( l_status_code = 1 ) THEN
4001 x_error_code := 110;
4002 x_error_msg_code := 'PA_TSK_CMT_TXN_EXIST';
4003 return;
4004 ELSIF ( l_status_code < 0 ) THEN
4005 x_error_code := l_status_code;
4006 return;
4007 END IF;
4008
4009 -- Check if task has compensation rule set
4010 IF l_debug_mode = 'Y' THEN
4011 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check compensation rule set for '|| p_task_id;
4012 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4013 l_debug_level3);
4014 END IF;
4015
4016 l_status_code :=
4017 pa_proj_tsk_utils.check_comp_rule_set_exists(NULL, p_task_id);
4018 IF ( l_status_code = 1 ) THEN
4019 x_error_code := 120;
4020 x_error_msg_code := 'PA_TSK_COMP_RULE_SET_EXIST';
4021 return;
4022 ELSIF ( l_status_code < 0 ) THEN
4023 x_error_code := l_status_code;
4024 return;
4025 END IF; */
4026 -- End of Commenting for 4903460
4027 -- Check if task is in use in an external system
4028 IF l_debug_mode = 'Y' THEN
4029 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check for task used in external system for'|| p_task_id;
4030 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4031 l_debug_level3);
4032 END IF;
4033
4034 l_status_code :=
4035 pjm_projtask_deletion.CheckUse_ProjectTask(null, p_task_id);
4036 IF ( l_status_code = 1 ) THEN
4037 x_error_code := 130;
4038 /* Commented the existing error message and modified it to 'PA_PROJ_TASK_IN_USE_MFG' as below for bug 3600806
4039 x_error_msg_code := 'PA_TASK_IN_USE_EXTERNAL';*/
4040 x_error_msg_code := 'PA_PROJ_TASK_IN_USE_MFG';
4041 return;
4042 ELSIF ( l_status_code = 2 ) THEN -- Added elseif condition for bug 3600806.
4043 x_error_code := 130;
4044 x_error_msg_code := 'PA_PROJ_TASK_IN_USE_AUTO';
4045 return;
4046 ELSIF ( l_status_code < 0 ) THEN
4047 x_error_code := l_status_code;
4048 return;
4049 ELSIF ( l_status_code <> 0) then -- Added else condition for bug 3600806 to display a generic error message.
4050 x_error_code := 130;
4051 x_error_msg_code := 'PA_PROJ_TASK_IN_USE_EXTERNAL';
4052 return;
4053 END IF;
4054
4055 -- Check if task is used in allocations
4056 IF l_debug_mode = 'Y' THEN
4057 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if project allocations uses task '|| p_task_id;
4058 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4059 l_debug_level3);
4060 END IF;
4061
4062 l_return_val :=
4063 pa_alloc_utils.Is_Task_In_Allocations(p_task_id);
4064 IF ( l_return_val = 'Y' ) THEN
4065 x_error_code := 140;
4066 x_error_msg_code := 'PA_TASK_IN_ALLOC';
4067 return;
4068 END IF;
4069
4070 -- Commenting for Performance fix 4903460
4071 /*
4072 -- Check if task has draft invoices
4073 IF l_debug_mode = 'Y' THEN
4074 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check draft invoice for '|| p_task_id;
4075 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4076 l_debug_level3);
4077 END IF;
4078
4079 l_status_code :=
4080 pa_proj_tsk_utils.check_draft_inv_details_exists(p_task_id);
4081 IF ( l_status_code = 1 ) THEN
4082 x_error_code := 160;
4083 x_error_msg_code := 'PA_TSK_CC_DINV_EXIST';
4084 return;
4085 ELSIF ( l_status_code < 0 ) THEN
4086 x_error_code := l_status_code;
4087 return;
4088 END IF;
4089
4090 -- Check if task has Project_customers
4091 IF l_debug_mode = 'Y' THEN
4092 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check Project Customers for '|| p_task_id;
4093 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4094 l_debug_level3);
4095 END IF;
4096
4097
4098 l_status_code :=
4099 pa_proj_tsk_utils.check_project_customer_exists(p_task_id);
4100 IF ( l_status_code = 1 ) THEN
4101 x_error_code := 170;
4102 x_error_msg_code := 'PA_TSK_CC_CUST_EXIST';
4103 return;
4104 ELSIF ( l_status_code < 0 ) THEN
4105 x_error_code := l_status_code;
4106 return;
4107 END IF; */
4108 --End of Commenting for Performance fix 4903460
4109
4110 -- Start of new code for Performance fix 4903460
4111 PA_PROJ_ELEMENTS_UTILS.perform_task_validations
4112 (
4113 p_project_id => p_project_id
4114 ,p_task_id => p_task_id
4115 ,x_error_code => x_error_code
4116 ,x_error_msg_code => x_error_msg_code
4117 );
4118
4119 IF x_error_code <> 0 THEN
4120 return;
4121 END IF;
4122 -- End of new code for Performance fix 4903460
4123
4124 -- Check if project contract is installed
4125 IF (pa_install.is_product_installed('OKE')) THEN
4126 IF l_debug_mode = 'Y' THEN
4127 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Check contract association for task '|| p_task_id;
4128 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4129 l_debug_level3);
4130 END IF;
4131
4132 IF (PA_PROJ_STRUCTURE_PUB.CHECK_TASK_CONTRACT_ASSO(p_task_id) <>
4133 FND_API.G_RET_STS_SUCCESS) THEN
4134 x_error_code := 190;
4135 x_error_msg_code := 'PA_STRUCT_TK_HAS_CONTRACT';
4136 return;
4137 END IF;
4138 END IF;
4139 -- Finished checking if project contract is installed.
4140
4141 --Check to see if the task has been used in OTL
4142 PA_OTC_API.ProjectTaskUsed( p_search_attribute => 'TASK',
4143 p_search_value => p_task_id,
4144 x_used => l_used_in_OTL );
4145 --If exists in OTL
4146 IF l_used_in_OTL
4147 THEN
4148 x_error_code := 200;
4149 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
4150 return;
4151 END IF;
4152
4153 --end of OTL check.
4154 Is_IEX_Installed := pa_install.is_product_installed('IEX');
4155 If Is_IEX_Installed then
4156 IF l_debug_mode = 'Y' THEN
4157 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if task '|| p_task_id || ' is charged in iexpense';
4158 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4159 l_debug_level3);
4160 END IF;
4161
4162 l_status_code := pa_proj_tsk_utils.check_iex_task_charged(p_task_id);
4163 IF ( l_status_code = 1 ) THEN
4164 x_error_code := 210;
4165 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
4166 return;
4167 ELSIF ( l_status_code < 0 ) THEN
4168 x_error_code := l_status_code;
4169 return;
4170 END IF;
4171 END IF;
4172 --BEGIN
4173 IF l_debug_mode = 'Y' THEN
4174 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK'|| p_task_id;
4175 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4176 l_debug_level3);
4177 END IF;
4178
4179 PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK(
4180 p_task_id => p_task_id
4181 ,p_validation_mode => 'U'
4182 ,x_return_status => l_return_status
4183 ,x_msg_count => l_msg_count
4184 ,x_msg_data => l_msg_data
4185 );
4186 IF (l_return_status <> 'S') Then
4187 x_error_code := 220;
4188 x_error_msg_code := pa_project_core1.get_message_from_stack( l_msg_data );
4189 return;
4190 END IF;
4191 --EXCEPTION WHEN OTHERS THEN
4192 -- IF l_debug_mode = 'Y' THEN
4193 -- Pa_Debug.g_err_stage:= 'API PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK FAILED';
4194 -- Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4195 -- l_debug_level3);
4196 -- END IF;
4197 --END;
4198
4199 --To be reviewed with set financial task
4200 --END IF;--If Financial task condition
4201 END IF;-- If task ID is not null condition
4202
4203 -- Bug 3735089 : using reset_curr_function too, just using set_curr_function may overflow it after several recursive calls
4204 -- and it gives ORA 06512 numeric or value error
4205 IF l_debug_mode = 'Y' THEN
4206 Pa_Debug.reset_curr_function;
4207 END IF;
4208
4209
4210 EXCEPTION
4211
4212 WHEN OTHERS THEN
4213
4214 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4215 x_msg_count := 1;
4216 x_msg_data := substrb(SQLERRM,1,120);-- Bug 3735089 Added substr -- 4537865 Changed substr to substrb
4217
4218 -- 4537865
4219 x_error_code := SQLCODE;
4220 x_error_msg_code := SQLCODE ;
4221
4222 Fnd_Msg_Pub.add_exc_msg
4223 ( p_pkg_name => 'PA_PROJ_ELEMENT_UTILS'
4224 ,p_procedure_name => 'CHECK_TASK_HAS_TRANSACTION'
4225 ,p_error_text => x_msg_data);
4226
4227 IF l_debug_mode = 'Y' THEN
4228 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4229 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4230 l_debug_level5);
4231
4232 Pa_Debug.reset_curr_function;
4233 END IF;
4234 RAISE;
4235
4236 END CHECK_TASK_HAS_TRANSACTION;
4237 --Begin add rtarway FP.M Develepment
4238
4239 function GET_TOP_TASK_VER_ID(p_element_version_id IN number) return number
4240 is
4241 --bug 4043647 , start
4242 /*cursor C1 is
4243 select object_id_from1
4244 from pa_object_relationships
4245 where relationship_type='S'
4246 and object_type_from='PA_TASKS'
4247 connect by prior object_id_from1 = object_id_to1
4248 and prior relationship_type = relationship_type
4249 start with object_id_to1 = p_element_version_id
4250 and relationship_type = 'S'
4251 union
4252 select p_element_version_id from dual ; --bug 3429648*/
4253
4254 cursor C1 is
4255 select object_id_to1
4256 from pa_object_relationships
4257 where relationship_type='S'
4258 and object_type_from='PA_STRUCTURES'
4259 and object_type_to='PA_TASKS'
4260 connect by prior object_id_from1 = object_id_to1
4261 start with object_id_to1 = p_element_version_id
4262 and relationship_type = 'S';
4263
4264 --bug 4043647 , end
4265
4266 /*
4267 intersect
4268 select a.object_id_to1
4269 from pa_object_relationships a, pa_proj_element_versions b
4270 where b.element_version_id = p_element_version_id
4271 and b.parent_structure_version_id = a.object_id_from1
4272 and a.relationship_type = 'S';
4273 */
4274
4275 l_top_task_ver_id NUMBER := p_element_version_id;
4276
4277 begin
4278 OPEN c1;
4279 FETCH c1 INTO l_top_task_ver_id;
4280 CLOSE c1;
4281 return l_top_task_ver_id;
4282 exception
4283 when others then
4284 return(SQLCODE);
4285 end GET_TOP_TASK_VER_ID;
4286
4287 function GET_TOP_TASK_ID(p_element_version_id IN number) return number
4288 is
4289 cursor C1 (evid number) is
4290 select proj_element_id from pa_proj_element_versions
4291 where element_version_id IN (
4292 /*select object_id_from1 --bug 4043647
4293 from pa_object_relationships
4294 where relationship_type='S'
4295 and object_type_from='PA_TASKS'
4296 connect by prior object_id_from1 = object_id_to1
4297 and prior relationship_type = relationship_type
4298 start with object_id_to1 = p_element_version_id
4299 and relationship_type = 'S'
4300 union
4301 select p_element_version_id from dual ); --bug 3429648*/
4302 select object_id_to1
4303 from pa_object_relationships
4304 where relationship_type='S'
4305 and object_type_from='PA_STRUCTURES'
4306 and object_type_to='PA_TASKS'
4307 connect by prior object_id_from1 = object_id_to1
4308 start with object_id_to1 = p_element_version_id
4309 and relationship_type = 'S');
4310
4311
4312 /*
4313 intersect
4314 select a.object_id_to1
4315 from pa_object_relationships a, pa_proj_element_versions b
4316 where b.element_version_id = p_element_version_id
4317 and b.parent_structure_version_id = a.object_id_from1
4318 and a.relationship_type = 'S');
4319 */
4320
4321 l_top_task_ver_id NUMBER := p_element_version_id;
4322 l_top_task_id NUMBER;
4323
4324 begin
4325 OPEN c1(p_element_version_id);
4326 FETCH c1 INTO l_top_task_id;
4327 CLOSE c1;
4328
4329 return l_top_task_id;
4330 exception
4331 when others then
4332 return(SQLCODE);
4333 end GET_TOP_TASK_ID;
4334
4335 /* 4156732 : This API returns Task Level for Workplan Tasks
4336 */
4337 function GET_TASK_LEVEL(p_element_version_id IN number) return varchar2
4338 is
4339 cursor C1 (evid number) is
4340 select 1
4341 from pa_object_relationships
4342 where object_id_to1 = evid
4343 and relationship_type='S'
4344 and object_type_from='PA_STRUCTURES';
4345
4346 cursor C2 (evid number) is
4347 select 1
4348 from pa_object_relationships
4349 where object_id_from1 = evid
4350 and relationship_type='S';
4351
4352 c1rec C1%ROWTYPE;
4353 c2rec C2%ROWTYPE;
4354
4355 l_tasks_above NUMBER :=0;
4356 l_tasks_below NUMBER :=0;
4357
4358 l_task_level VARCHAR2(1) :='L';
4359
4360 l_dummy NUMBER;
4361
4362 CURSOR c3(evid NUMBER) IS
4363 select 1 from pa_proj_element_versions
4364 where element_version_id = evid
4365 and object_type = 'PA_STRUCTURES';
4366
4367 begin
4368 OPEN c3(p_element_version_id);
4369 FETCH c3 into l_dummy;
4370 IF C3%FOUND THEN --it is a structure, return T or L
4371 OPEN c2(p_element_version_id);
4372 FETCH c2 into l_dummy;
4373 IF C2%FOUND THEN
4374 --it has child
4375 l_task_level := 'T';
4376 END IF;
4377 CLOSE c2;
4378 CLOSE c3;
4379 return l_task_level;
4380 END IF;
4381 CLOSE c3;
4382
4383 OPEN C1(p_element_version_id);
4384 FETCH c1 into l_dummy;
4385 IF c1%found THEN
4386 l_task_level := 'T';
4387 CLOSE c1;
4388 OPEN c2(p_element_version_id);
4389 FETCH c2 into l_dummy;
4390 IF c2%NOTFOUND THEN
4391 l_task_level := 'L';
4392 END IF;
4393 CLOSE c2;
4394 return l_task_level;
4395 END IF;
4396 CLOSE c1;
4397
4398 OPEN C2(p_element_version_id);
4399 FETCH c2 into l_dummy;
4400 IF c2%found THEN
4401 l_task_level := 'M';
4402 CLOSE c2;
4403 return l_task_level;
4404 END IF;
4405 CLOSE c2;
4406
4407 return l_task_level;
4408 exception
4409 when others then
4410 return(SQLERRM);
4411 end GET_TASK_LEVEL;
4412
4413 /* Created by avaithia for Bug 4156732
4414 This API (over-ridden GET_TASK_LEVEL API) which takes the following two parameters
4415 gives the Task Level of Financial Tasks.
4416
4417 API Name : GET_TASK_LEVEL
4418
4419 Parameters Description Type Of Parameter
4420 ========== =============== ===================
4421 p_project_id The Project ID PA_PROJECTS_ALL.PROJECT_ID%TYPE
4422 p_proj_element_id The ProjElementID PA_PROJ_ELEMENT_VERSIONS.PROJ_ELEMENT_ID%TYPE
4423
4424 Return Value of this function :
4425 ===============================
4426 'T' -> Top Task , 'M' -> Middle Task , 'L' -> Lowest Task , 'X' -> Not Financial Task
4427
4428 Note that it is very much possible that (say) the case of Partially Shared Structures,
4429 Some of the tasks may be both workplan and financial tasks,whereas some tasks are only workplan tasks.
4430
4431 In this case,There will not be any entry for those 'pure' workplan tasks in PA_TASKS table.
4432 Hence,for such passed Proj_element_id's this API will return 'X'
4433 */
4434 FUNCTION GET_TASK_LEVEL(p_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4435 p_proj_element_id PA_PROJ_ELEMENT_VERSIONS.PROJ_ELEMENT_ID%TYPE) RETURN VARCHAR2
4436 IS
4437 l_task_level VARCHAR2(1) :='L';
4438
4439 l_dummy NUMBER;
4440 l_parent_task NUMBER;
4441 l_mid_task NUMBER;
4442
4443 CURSOR c_task_exists
4444 IS
4445 select 1
4446 from pa_tasks
4447 where task_id = p_proj_element_id
4448 and project_id = p_project_id
4449 ;
4450
4451 CURSOR c_is_parent_task
4452 IS
4453 select 1
4454 from dual
4455 where exists
4456 (select 1
4457 from pa_tasks
4458 where nvl(parent_task_id,-9999) = p_proj_element_id
4459 and project_id = p_project_id
4460 ) ;
4461
4462 CURSOR c_is_mid_task
4463 IS
4464 select 1 from pa_tasks
4465 where parent_task_id is not null
4466 and task_id = p_proj_element_id ;
4467
4468 BEGIN
4469
4470 OPEN c_task_exists;
4471 FETCH c_task_exists INTO l_dummy ;
4472 CLOSE c_task_exists;
4473
4474 IF nvl(l_dummy,0) = 0
4475 THEN
4476 return 'X' ; -- as the task doesnt exist in PA_TASKS table
4477 END IF;
4478
4479 --At this point the task exists
4480
4481 OPEN c_is_parent_task ;
4482 FETCH c_is_parent_task INTO l_parent_task;
4483 CLOSE c_is_parent_task;
4484
4485 IF nvl(l_parent_task,0) = 0
4486 THEN
4487 return 'L' ; -- as the task is not parent of any other task,(i.e) No Children ,Hence it is the lowest task
4488 END IF;
4489
4490 -- At this point ,The Task exists and it has children ,So it can be a top task or a mid task
4491
4492 OPEN c_is_mid_task ;
4493 FETCH c_is_mid_task INTO l_mid_task;
4494 CLOSE c_is_mid_task ;
4495
4496 IF nvl(l_mid_task,0) = 0
4497 THEN
4498 return 'T' ; -- as the task exists and its parent_task_id is null => It has no parent,hence the top most task
4499 END IF;
4500
4501 -- At this point ,The Task exists and it has children as well as a parent task ,So this is a mid-task
4502 return 'M';
4503
4504 exception
4505 when others then
4506 return (SQLERRM);
4507
4508 END GET_TASK_LEVEL ;
4509
4510 --Begin Add sabansal
4511 --Function to check whether the given task is a workplan task or not
4512 FUNCTION CHECK_IS_WORKPLAN_TASK(p_project_id NUMBER,
4513 p_proj_element_id NUMBER) RETURN VARCHAR2
4514 IS
4515 str_sharing_code VARCHAR2(30):= null;
4516 return_flag VARCHAR2(1) := null;
4517
4518 BEGIN
4519
4520 SELECT structure_sharing_code INTO str_sharing_code
4521 FROM pa_projects_all
4522 WHERE project_id = p_project_id;
4523
4524 --If sharing is enabled, then financial and workplan tasks would be common
4525 IF str_sharing_code = 'SHARE_FULL' OR
4526 str_sharing_code = 'SHARE_PARTIAL' THEN
4527 return_flag := 'Y';
4528 ELSIF PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(p_proj_element_id) = 'N' THEN
4529 return_flag := 'Y';
4530 ELSE
4531 return_flag := 'N';
4532 END IF;
4533
4534 return return_flag;
4535 EXCEPTION
4536 WHEN OTHERS THEN
4537 return null;
4538 END CHECK_IS_WORKPLAN_TASK;
4539 --End Add sabansal
4540
4541 function GET_PARENT_TASK_ID(p_element_version_id IN number) return number
4542 is
4543 CURSOR c1 IS
4544 SELECT proj_element_id
4545 FROM pa_object_relationships, pa_proj_element_versions
4546 WHERE object_id_to1 = p_element_version_id
4547 AND object_type_from='PA_TASKS'
4548 AND object_id_from1 = element_version_id;
4549
4550 l_parent_task_id NUMBER := NULL;
4551 begin
4552 OPEN c1;
4553 FETCH c1 into l_parent_task_id;
4554 CLOSE c1;
4555
4556 return l_parent_task_id;
4557 exception
4558 when others then
4559 return(SQLCODE);
4560 end GET_PARENT_TASK_ID;
4561
4562 function GET_PARENT_TASK_VERSION_ID(p_element_version_id IN number) return number
4563 is
4564 l_parent_task_id NUMBER := NULL;
4565 l_parent_task_version_id NUMBER := NULL;
4566
4567
4568 CURSOR c1 IS
4569 SELECT object_id_from1
4570 FROM pa_object_relationships
4571 WHERE object_id_to1 = p_element_version_id
4572 AND object_type_from='PA_TASKS'
4573 AND relationship_type = 'S';
4574
4575 begin
4576 OPEN c1;
4577 FETCH c1 into l_parent_task_version_id;
4578 CLOSE c1;
4579 return l_parent_task_version_id;
4580 exception
4581 when others then
4582 return(SQLCODE);
4583 end GET_PARENT_TASK_VERSION_ID;
4584
4585 function GET_TASK_VERSION_ID(
4586 p_structure_version_id IN NUMBER
4587 ,p_task_id IN NUMBER) return NUMBER
4588 is
4589 l_task_version_id NUMBER;
4590 begin
4591 select b.element_version_id into l_task_version_id from pa_proj_elements a, pa_proj_element_versions b
4592 where a.proj_element_id = b.proj_element_id
4593 and a.proj_element_id = p_task_id
4594 and b.parent_structure_version_id = p_structure_version_id;
4595
4596 return l_task_version_id;
4597 exception
4598 when others then
4599 -- return(SQLCODE);
4600 return to_number(NULL); --Bug 3646375
4601 end GET_TASK_VERSION_ID;
4602
4603 function GET_RELATIONSHIP_ID(
4604 p_object_id_from1 IN NUMBER
4605 ,p_object_id_to1 IN NUMBER) return NUMBER
4606 is
4607 l_relationship_id NUMBER;
4608 begin
4609 select object_relationship_id into l_relationship_id from pa_object_relationships
4610 where object_id_from1 = p_object_id_from1
4611 and object_id_to1 = p_object_id_to1
4612 and relationship_type = 'D';
4613
4614 return l_relationship_id;
4615 exception
4616 when others then
4617 -- return(SQLCODE);
4618 return to_number(NULL); --Bug 3646375
4619 end GET_RELATIONSHIP_ID;
4620
4621 FUNCTION check_task_parents_deliv(p_element_version_id IN number)
4622 RETURN VARCHAR2
4623 IS
4624 --
4625 CURSOR cur_check_deliv (cp_task_version_id number) IS
4626 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4627 FROM pa_proj_element_versions ppev,
4628 pa_proj_elements ppe
4629 WHERE ppe.project_id = ppev.project_id
4630 AND ppe.proj_element_id = ppev.proj_element_id
4631 AND ppev.object_type = 'PA_TASKS'
4632 AND ppe.object_type = 'PA_TASKS'
4633 AND ppev.element_version_id IN (
4634 SELECT object_id_to1
4635 FROM pa_object_relationships
4636 WHERE relationship_type = 'S'
4637 START WITH object_id_to1 = cp_task_version_id --24628
4638 AND object_type_to = 'PA_TASKS'
4639 and relationship_type = 'S'
4640 CONNECT BY PRIOR object_id_from1 = object_id_to1
4641 AND PRIOR object_type_from = object_type_to
4642 AND PRIOR relationship_type = relationship_type);
4643 --
4644 cur_check_deliv_rec cur_check_deliv%ROWTYPE;
4645 --
4646 -- l_err_msg VARCHAR2(80) :=NULL;
4647 l_err_msg VARCHAR2(1) :='N'; --Bug 3475920
4648 --
4649 BEGIN
4650 OPEN cur_check_deliv(p_element_version_id);
4651 LOOP
4652 FETCH cur_check_deliv INTO cur_check_deliv_rec;
4653 EXIT WHEN cur_check_deliv%NOTFOUND;
4654 IF cur_check_deliv_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4655 l_err_msg := 'Y';
4656 EXIT;
4657 END IF;
4658 END LOOP;
4659 RETURN l_err_msg;
4660 EXCEPTION
4661 WHEN OTHERS THEN
4662 RETURN(SQLERRM);
4663 END check_task_parents_deliv;
4664
4665 --Can be used for update, indent, Move task
4666 FUNCTION check_deliv_in_hierarchy(p_element_version_id IN number,
4667 p_target_element_version_id IN number)
4668 RETURN VARCHAR2
4669 IS
4670 CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
4671 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4672 FROM pa_proj_element_versions ppev,
4673 pa_proj_elements ppe
4674 WHERE ppe.project_id = ppev.project_id
4675 AND ppe.proj_element_id = ppev.proj_element_id
4676 AND ppev.object_type = 'PA_TASKS'
4677 AND ppe.object_type = 'PA_TASKS'
4678 AND ppev.element_version_id IN (
4679 SELECT object_id_to1
4680 FROM pa_object_relationships
4681 WHERE relationship_type = 'S'
4682 START WITH object_id_to1 = cp_target_task_version_id
4683 AND object_type_to = 'PA_TASKS'
4684 and relationship_type = 'S'
4685 CONNECT BY PRIOR object_id_from1 = object_id_to1
4686 AND PRIOR object_type_from = object_type_to
4687 AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4688
4689 CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
4690 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4691 FROM pa_proj_element_versions ppev,
4692 pa_proj_elements ppe
4693 WHERE ppe.project_id = ppev.project_id
4694 AND ppe.proj_element_id = ppev.proj_element_id
4695 AND ppev.object_type = 'PA_TASKS'
4696 AND ppe.object_type = 'PA_TASKS'
4697 AND ppev.element_version_id IN (
4698 SELECT object_id_to1
4699 FROM pa_object_relationships
4700 WHERE relationship_type = 'S'
4701 START WITH object_id_to1 = cp_task_version_id
4702 AND object_type_to = 'PA_TASKS'
4703 and relationship_type = 'S'
4704 CONNECT BY object_id_from1 = prior object_id_to1
4705 AND object_type_from = prior object_type_to
4706 AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4707
4708 cur_check_deliv_bt_rec cur_check_deliv_bt%ROWTYPE;
4709 cur_check_deliv_tb_rec cur_check_deliv_tb%ROWTYPE;
4710 -- l_err_msg VARCHAR2(80) :=NULL;
4711 l_err_msg VARCHAR2(1) :='N'; --Bug 3475920
4712 l_cnt_no_del_in_branch NUMBER:=0;
4713 BEGIN
4714 FOR cur_check_deliv_bt_rec in cur_check_deliv_bt(p_target_element_version_id)
4715 LOOP
4716 IF cur_check_deliv_bt_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4717 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
4718 EXIT;
4719 END IF;
4720 END LOOP;
4721 --
4722 FOR cur_check_deliv_tb_rec in cur_check_deliv_tb(p_element_version_id)
4723 LOOP
4724 IF cur_check_deliv_tb_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4725 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
4726 EXIT;
4727 END IF;
4728 END LOOP;
4729 --
4730 -- IF l_cnt_no_del_in_branch > 2 AND (p_element_version_id = p_target_element_version_id) THEN
4731 IF l_cnt_no_del_in_branch >= 2 AND (p_element_version_id = p_target_element_version_id) THEN
4732 l_err_msg:= 'Y';
4733 ELSIF l_cnt_no_del_in_branch > 1 AND (p_element_version_id <> p_target_element_version_id) THEN
4734 l_err_msg:= 'Y';
4735 END IF;
4736 RETURN l_err_msg;
4737 EXCEPTION
4738 WHEN OTHERS THEN
4739 RETURN(SQLERRM);
4740 END check_deliv_in_hierarchy;
4741 --
4742 FUNCTION check_sharedstruct_deliv(p_element_version_id IN number)
4743 RETURN VARCHAR2
4744 IS
4745 /* This cursor get all the leaf nodes for a given structure*/
4746 CURSOR get_leaf_node_cur(cp_structure_elem_id NUMBER) IS
4747 SELECT object_id_to1
4748 FROM pa_proj_element_versions ppev,
4749 pa_object_relationships rel1
4750 WHERE ppev.parent_structure_version_id = cp_structure_elem_id --19671
4751 AND rel1.relationship_type = 'S'
4752 AND ppev.element_version_id = rel1.object_id_to1
4753 AND NOT EXISTS (SELECT 'XYZ'
4754 FROM pa_object_relationships rel2
4755 WHERE rel2.object_id_from1 = rel1.object_id_to1);
4756 get_leaf_node_rec get_leaf_node_cur%ROWTYPE;
4757
4758 /* This cursor goes from leaf node to top of the branch*/
4759 CURSOR check_for_deliv_cur (cp_task_version_id number) IS
4760 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4761 FROM pa_proj_element_versions ppev,
4762 pa_proj_elements ppe
4763 WHERE ppe.project_id = ppev.project_id
4764 AND ppe.proj_element_id = ppev.proj_element_id
4765 AND ppev.object_type = 'PA_TASKS'
4766 AND ppe.object_type = 'PA_TASKS'
4767 AND ppev.element_version_id IN (
4768 SELECT object_id_to1
4769 FROM pa_object_relationships
4770 WHERE relationship_type = 'S'
4771 START WITH object_id_to1 = cp_task_version_id --24628
4772 AND object_type_to = 'PA_TASKS'
4773 and relationship_type = 'S'
4774 CONNECT BY PRIOR object_id_from1 = object_id_to1
4775 AND PRIOR object_type_from = object_type_to
4776 AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4777 check_for_deliv_rec check_for_deliv_cur%ROWTYPE;
4778 branch_deliv_count NUMBER:=0;
4779 l_err_msg VARCHAR2(1) :='N';
4780 BEGIN
4781 -- OPEN get_leaf_node_cur(p_structure_elem_id);
4782 OPEN get_leaf_node_cur(p_element_version_id);
4783 LOOP
4784 FETCH get_leaf_node_cur INTO get_leaf_node_rec;
4785 EXIT WHEN get_leaf_node_cur%NOTFOUND;
4786 --
4787 IF branch_deliv_count = 2 THEN
4788 EXIT;
4789 END IF;
4790 --
4791 OPEN check_for_deliv_cur(get_leaf_node_rec.object_id_to1);
4792 LOOP
4793 FETCH check_for_deliv_cur INTO check_for_deliv_rec;
4794 IF check_for_deliv_cur%NOTFOUND THEN
4795 IF branch_deliv_count < 2 THEN
4796 branch_deliv_count:=0;
4797 END IF;
4798 EXIT;
4799 END IF;
4800 --
4801 IF check_for_deliv_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4802 branch_deliv_count := branch_deliv_count + 1;
4803 END IF;
4804 IF branch_deliv_count = 2 THEN
4805 l_err_msg := 'Y';
4806 EXIT;
4807 END IF;
4808 --
4809 END LOOP; --End loop for check_for_deliv_cur cursor
4810 CLOSE check_for_deliv_cur;
4811 --
4812 END LOOP; --End loop for get_leaf_node_cur cursor
4813 CLOSE get_leaf_node_cur;
4814 --
4815 RETURN l_err_msg;
4816 --
4817 EXCEPTION
4818 WHEN OTHERS THEN
4819 RETURN(SQLERRM);
4820 END check_sharedstruct_deliv;
4821
4822 FUNCTION IS_WF_PROCESS_RUNNING(p_proj_element_id IN number)
4823 RETURN VARCHAR2
4824 IS
4825 CURSOR C
4826 IS
4827 SELECT 'Y'
4828 FROM pa_wf_processes pwp, pa_proj_elements ppe -- Bug #3967939
4829 WHERE pwp.ENTITY_KEY2 = to_char(p_proj_element_id) -- Bug#3619754 : Added to_char
4830 AND ppe.PROJ_ELEMENT_ID = p_proj_element_id -- Bug #3967939
4831 AND pwp.ITEM_TYPE = ppe.WF_ITEM_TYPE; -- Bug #3967939
4832
4833 l_dummy VARCHAR2(1) := 'N' ;
4834 BEGIN
4835 OPEN C;
4836 FETCH C INTO l_dummy ;
4837 IF C%NOTFOUND THEN
4838 l_dummy := 'N' ;
4839 END IF;
4840 CLOSE C;
4841 return l_dummy ;
4842 EXCEPTION
4843 WHEN OTHERS THEN
4844 return 'N' ;
4845 END IS_WF_PROCESS_RUNNING ;
4846
4847 FUNCTION GET_ELEMENT_WF_ITEMKEY(p_proj_element_id IN number,
4848 p_project_id IN number, p_wf_type_code IN VARCHAR2 := 'TASK_EXECUTION')
4849 RETURN VARCHAR2
4850
4851 IS
4852 CURSOR C
4853 IS
4854 select max(item_key)
4855 from pa_wf_processes wp
4856 , pa_proj_elements pe
4857 where wp.item_type = pe.wf_item_type
4858 and wp.wf_type_code = p_wf_type_code
4859 and to_char(pe.proj_element_id) = wp.entity_key2 --Bug 3619754 Added By avaithia
4860 and to_char(pe.project_id) = wp.entity_key1 --Bug 3619754 Added By avaithia
4861 and pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
4862
4863 l_item_key VARCHAR2(240) := '' ;
4864 BEGIN
4865 OPEN C;
4866 FETCH C INTO l_item_key ;
4867 CLOSE C;
4868 return l_item_key ;
4869 EXCEPTION
4870 WHEN OTHERS THEN
4871 return '' ;
4872 END GET_ELEMENT_WF_ITEMKEY;
4873
4874 FUNCTION GET_ELEMENT_WF_STATUS(p_proj_element_id IN number,
4875 p_project_id IN number, p_wf_type_code IN VARCHAR2 := 'TASK_EXECUTION')
4876 RETURN VARCHAR2
4877
4878 IS
4879 CURSOR c_item_type
4880 IS
4881 select wf_item_type
4882 from pa_proj_elements pe
4883 where
4884 pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
4885
4886 l_status VARCHAR2(240) := '';
4887 l_item_key VARCHAR2(240) := '';
4888 l_wf_status VARCHAR2(240) := '';
4889 l_item_type VARCHAR2(240) := '';
4890 l_result VARCHAR2(240) := '';
4891
4892 BEGIN
4893
4894 open c_item_type;
4895 FETCH c_item_type INTO l_item_type;
4896 CLOSE c_item_type;
4897
4898 if ( l_item_type is not null) then -- Commented this for Bug 4249993 and l_item_type <> '') then
4899 l_item_key := GET_ELEMENT_WF_ITEMKEY(p_proj_element_id,
4900 p_project_id, p_wf_type_code);
4901
4902 if (l_item_key is not null) then -- Commented this for Bug 4249993 and l_item_key <> '') then
4903 WF_ENGINE.ItemStatus
4904 (l_item_type,
4905 l_item_key,
4906 l_status ,
4907 l_result );
4908 end if;
4909 end if;
4910 return l_status;
4911
4912 EXCEPTION
4913 WHEN OTHERS THEN
4914 return '' ;
4915 END GET_ELEMENT_WF_STATUS;
4916
4917 -- Function : check_fin_or_wp_structure
4918 -- Purpose : Checks whether the passed proj_element_id record is a WP or FIN structure record
4919 -- Parameters Type Required Description and Purpose
4920 -- --------------------------- ------ -------- --------------------------------------------------------
4921 -- p_proj_element_id NUMBER Y The proj_element_id to be checked
4922 FUNCTION check_fin_or_wp_structure( p_proj_element_id IN NUMBER ) RETURN VARCHAR2 IS
4923 CURSOR cur_chk_fin_or_wp_structure IS
4924 SELECT 'Y'
4925 FROM pa_proj_elements ppe
4926 ,pa_proj_structure_types ppst
4927 ,pa_structure_types pst
4928 WHERE ppe.proj_element_id = p_proj_element_id
4929 AND ppe.object_type = 'PA_STRUCTURES'
4930 AND ppe.proj_element_id = ppst.proj_element_id
4931 AND ppst.structure_type_id = pst.structure_type_id
4932 AND pst.structure_type IN ('WORKPLAN','FINANCIAL') ;
4933
4934 l_return_flag VARCHAR2(1);
4935 BEGIN
4936 OPEN cur_chk_fin_or_wp_structure;
4937 FETCH cur_chk_fin_or_wp_structure INTO l_return_flag;
4938 CLOSE cur_chk_fin_or_wp_structure;
4939
4940 RETURN nvl(l_return_flag,'N');
4941 EXCEPTION
4942 WHEN OTHERS THEN
4943 RETURN '';
4944 END;
4945
4946 FUNCTION CHECK_USER_VIEW_TASK_PRIVILEGE
4947 (
4948 p_project_id IN NUMBER
4949 ) RETURN VARCHAR2
4950 IS
4951 l_ret_code VARCHAR2(1) ;
4952 BEGIN
4953 l_ret_code := PA_SECURITY_PVT.check_user_privilege
4954 ( p_privilege => 'PA_PAXPREPR_OPT_WORKPLAN_STR_V'
4955 ,p_object_name => 'PA_PROJECTS'
4956 ,p_object_key => p_project_id
4957 ) ;
4958 RETURN l_ret_code ;
4959 END CHECK_USER_VIEW_TASK_PRIVILEGE;
4960 --
4961 --
4962 function GET_SUB_TASK_VERSION_ID(p_task_version_id IN number) return number
4963 is
4964 l_sub_task_id NUMBER := NULL;
4965 l_sub_task_version_id NUMBER := NULL;
4966 --
4967 --
4968 CURSOR c1 IS
4969 SELECT object_id_to1
4970 FROM pa_object_relationships
4971 WHERE object_id_from1 = p_task_version_id
4972 AND object_type_to='PA_TASKS'
4973 AND relationship_type = 'S';
4974 --
4975 begin
4976 OPEN c1;
4977 FETCH c1 into l_sub_task_version_id;
4978 CLOSE c1;
4979 return l_sub_task_version_id;
4980 exception
4981 when others then
4982 return(SQLCODE);
4983 end GET_SUB_TASK_VERSION_ID;
4984 --
4985 --
4986 FUNCTION check_deliv_in_hie_upd(p_task_version_id IN number)
4987 RETURN NUMBER
4988 IS
4989 CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
4990 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4991 FROM pa_proj_element_versions ppev,
4992 pa_proj_elements ppe
4993 WHERE ppe.project_id = ppev.project_id
4994 AND ppe.proj_element_id = ppev.proj_element_id
4995 AND ppev.object_type = 'PA_TASKS'
4996 AND ppe.object_type = 'PA_TASKS'
4997 AND ppev.element_version_id IN (
4998 SELECT object_id_to1
4999 FROM pa_object_relationships
5000 WHERE relationship_type = 'S'
5001 START WITH object_id_to1 = cp_target_task_version_id
5002 AND object_type_to = 'PA_TASKS'
5003 and relationship_type = 'S'
5004 CONNECT BY PRIOR object_id_from1 = object_id_to1
5005 AND PRIOR object_type_from = object_type_to
5006 AND PRIOR relationship_type = RELATIONSHIP_TYPE);
5007
5008 CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
5009 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5010 FROM pa_proj_element_versions ppev,
5011 pa_proj_elements ppe
5012 WHERE ppe.project_id = ppev.project_id
5013 AND ppe.proj_element_id = ppev.proj_element_id
5014 AND ppev.object_type = 'PA_TASKS'
5015 AND ppe.object_type = 'PA_TASKS'
5016 AND ppev.element_version_id IN (
5017 SELECT object_id_to1
5018 FROM pa_object_relationships
5019 WHERE relationship_type = 'S'
5020 START WITH object_id_to1 = cp_task_version_id
5021 AND object_type_to = 'PA_TASKS'
5022 and relationship_type = 'S'
5023 CONNECT BY object_id_from1 = prior object_id_to1
5024 AND object_type_from = prior object_type_to
5025 AND PRIOR relationship_type = RELATIONSHIP_TYPE);
5026
5027 cur_check_deliv_bt_rec cur_check_deliv_bt%ROWTYPE;
5028 cur_check_deliv_tb_rec cur_check_deliv_tb%ROWTYPE;
5029 -- l_err_msg VARCHAR2(80) :=NULL;
5030 l_err_msg VARCHAR2(1) :='N'; --Bug 3475920
5031 l_cnt_no_del_in_branch NUMBER:=0;
5032 l_parent_task_ver_id NUMBER:=NULL;
5033 l_sub_task_ver_id NUMBER:=NULL;
5034 BEGIN
5035 --
5036 l_parent_task_ver_id:=GET_PARENT_TASK_VERSION_ID(p_task_version_id);
5037 l_sub_task_ver_id:=GET_SUB_TASK_VERSION_ID(p_task_version_id);
5038 --
5039 IF l_parent_task_ver_id IS NOT NULL THEN
5040 FOR cur_check_deliv_bt_rec in cur_check_deliv_bt(l_parent_task_ver_id)
5041 LOOP
5042 IF cur_check_deliv_bt_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
5043 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
5044 EXIT;
5045 END IF;
5046 END LOOP;
5047 END IF;
5048 --
5049 IF l_sub_task_ver_id IS NOT NULL THEN
5050 FOR cur_check_deliv_tb_rec in cur_check_deliv_tb(l_sub_task_ver_id)
5051 LOOP
5052 IF cur_check_deliv_tb_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
5053 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
5054 EXIT;
5055 END IF;
5056 END LOOP;
5057 END IF;
5058 --
5059 RETURN l_cnt_no_del_in_branch;
5060 EXCEPTION
5061 WHEN OTHERS THEN
5062 RETURN(SQLERRM);
5063 END check_deliv_in_hie_upd;
5064 --
5065 --
5066 -- FUNCTION check_pa_lookup_exists
5067 -- PURPOSE Checks whether the passed lookup_code and value are valid
5068 -- RETURN VALUE VARCHAR2 - 'Y' if the valid
5069 -- 'N' otherwise.
5070 --
5071 Function check_pa_lookup_exists(p_lookup_type VARCHAR2,
5072 p_lookup_code VARCHAR2)
5073 RETURN VARCHAR2
5074 IS
5075 CURSOR chk_lkp(cp_lookup_type VARCHAR2, cp_lookup_code VARCHAR2)
5076 IS
5077 SELECT 'Y'
5078 FROM pa_lookups
5079 WHERE lookup_type = cp_lookup_type
5080 AND lookup_code = cp_lookup_code;
5081 l_dummy varchar2(1):='Y';
5082 BEGIN
5083 --
5084 OPEN chk_lkp(p_lookup_type,p_lookup_code);
5085 FETCH chk_lkp INTO l_dummy;
5086 --
5087 IF chk_lkp%NOTFOUND THEN
5088 l_dummy:= 'N';
5089 END IF;
5090 --
5091 CLOSE chk_lkp;
5092 --
5093 RETURN l_dummy;
5094 --
5095 END check_pa_lookup_exists;
5096 --
5097 --
5098
5099 function GET_TASK_ID(
5100 p_project_id IN NUMBER
5101 ,p_structure_version_id IN NUMBER
5102 ,p_task_version_id IN NUMBER) return NUMBER
5103 IS
5104 l_task_id NUMBER;
5105 begin
5106 select b.proj_element_id into l_task_id
5107 from pa_proj_element_versions b
5108 where b.element_version_id = p_task_version_id
5109 and b.project_id = p_project_id
5110 and b.parent_structure_version_id = p_structure_version_id;
5111
5112 return l_task_id;
5113 exception
5114 when others then
5115 return to_number(NULL);
5116 end GET_TASK_ID;
5117
5118 -- Begin fix for Bug # 4237838.
5119
5120 function is_lowest_level_fin_task(p_project_id NUMBER
5121 , p_task_version_id NUMBER
5122 , p_include_sub_proj_flag VARCHAR2 := 'Y') -- Fix for Bug # 4290042.
5123 return VARCHAR2
5124 is
5125
5126 cursor cur_fin_task(c_project_id NUMBER
5127 , c_task_version_id NUMBER)
5128 is
5129 select ppev.financial_task_flag
5130 from pa_proj_element_versions ppev
5131 where ppev.project_id = c_project_id
5132 and ppev.element_version_id = c_task_version_id;
5133
5134 l_financial_task_flag VARCHAR2(1) := null;
5135
5136 cursor cur_lowest_level_fin_task (c_project_id NUMBER
5137 , c_task_version_id NUMBER
5138 , c_include_sub_proj_flag VARCHAR2) -- Fix for Bug # 4290042.
5139 is
5140 -- This query checks if the task version has a financial sub-task.
5141 select 'N'
5142 from pa_object_relationships por1, pa_proj_element_versions ppev1
5143 where por1.object_id_to1 = ppev1.element_version_id
5144 and por1.relationship_type = 'S'
5145 and ppev1.project_id = c_project_id
5146 and por1.object_id_from1 = c_task_version_id
5147 and ppev1.financial_task_flag = 'Y'
5148 union all
5149 -- This query checks if the task version has a linking sub-task that has a financial link to
5150 -- a sub-project if the input p_include_sub_proj_flag = 'Y'.
5151 select 'N'
5152 from pa_object_relationships por2, pa_proj_element_versions ppev2
5153 where por2.object_id_to1 = ppev2.element_version_id
5154 and por2.relationship_type = 'S'
5155 and ppev2.project_id = c_project_id
5156 and por2.object_id_from1 = c_task_version_id
5157 and exists (select 'Y'
5158 from pa_object_relationships por3
5159 where por3.object_id_from1 = ppev2.element_version_id
5160 and por3.object_id_from2 = ppev2.project_id
5161 and por3.relationship_type = 'LF')
5162 and c_include_sub_proj_flag = 'Y'; -- Fix for Bug # 4290042.
5163
5164
5165 l_lowest_level_fin_task VARCHAR2(1) := null;
5166
5167 l_return VARCHAR2(1) := null;
5168
5169 begin
5170
5171 l_return := 'Y';
5172
5173 -- Check if the Task is a Financial Task.
5174
5175 open cur_fin_task(p_project_id, p_task_version_id);
5176
5177 fetch cur_fin_task into l_financial_task_flag;
5178
5179 close cur_fin_task;
5180
5181 if l_financial_task_flag = 'N' then
5182
5183 l_return := 'N';
5184
5185 else
5186
5187
5188 -- Check if the Financial Task is a Lowest Level Financial Task.
5189
5190
5191 open cur_lowest_level_fin_task (p_project_id, p_task_version_id, p_include_sub_proj_flag);
5192 -- Fix for Bug # 4290042.
5193
5194 fetch cur_lowest_level_fin_task into l_lowest_level_fin_task;
5195
5196 if cur_lowest_level_fin_task%FOUND then
5197
5198 l_return := 'N';
5199
5200 end if;
5201
5202 close cur_lowest_level_fin_task;
5203
5204 end if;
5205
5206 return(l_return);
5207
5208 end is_lowest_level_fin_task;
5209
5210 -- End fix for Bug # 4237838.
5211
5212 -- Bug 4667361: Added this Function
5213 FUNCTION WP_STR_EXISTS_FOR_UPG
5214 (
5215 p_project_id IN NUMBER
5216 ) RETURN VARCHAR2 IS
5217 l_return_value VARCHAR2(1) := 'N';
5218 l_dummy_char VARCHAR2(1) := 'N';
5219
5220 CURSOR cur_pa_proj
5221 IS
5222
5223 SELECT 'x'
5224 FROM pa_proj_elements ppe, pa_proj_structure_types ppst
5225 WHERE ppe.project_id = p_project_id
5226 AND ppe.object_type = 'PA_STRUCTURES'
5227 AND ppe.proj_element_id = ppst.proj_element_id
5228 AND ppst.structure_type_id = 1; --'WORKPLAN'
5229
5230 BEGIN
5231
5232 open cur_pa_proj;
5233 fetch cur_pa_proj INTO l_dummy_char;
5234 IF cur_pa_proj%FOUND
5235 THEN
5236 l_return_value := 'Y';
5237 ELSE
5238 l_return_value := 'N';
5239 END IF;
5240 CLOSE cur_pa_proj;
5241
5242 RETURN ( NVL( l_return_value, 'N' ) );
5243
5244 END WP_STR_EXISTS_FOR_UPG;
5245
5246 -- Bug 4667361: Added this Function
5247 FUNCTION CHECK_SHARING_ENABLED_FOR_UPG
5248 ( p_project_id IN NUMBER
5249 ) return VARCHAR2
5250 IS
5251 CURSOR c1 IS
5252 SELECT 'Y'
5253 FROM pa_proj_elements a,
5254 pa_proj_structure_types b,
5255 pa_structure_types c,
5256 pa_proj_structure_types d,
5257 pa_structure_types e
5258 WHERE c.structure_type_class_code = 'WORKPLAN'
5259 AND e.structure_type_class_code = 'FINANCIAL'
5260 AND c.structure_type_id = b.structure_type_id
5261 AND e.structure_type_id = d.structure_type_id
5262 AND b.proj_element_id = a.proj_element_id
5263 AND d.proj_element_id = a.proj_element_id
5264 AND a.project_id = p_project_id;
5265
5266 l_dummy VARCHAR2(1);
5267 BEGIN
5268 OPEN c1;
5269 FETCH c1 into l_dummy;
5270 IF c1%NOTFOUND THEN
5271 l_dummy := 'N';
5272 END IF;
5273 CLOSE c1;
5274 return l_dummy;
5275
5276 END CHECK_SHARING_ENABLED_FOR_UPG;
5277
5278
5279 -- Procedure included for perf fix 4903460
5280 ---------------------------------------------
5281 -- Does the following validations :
5282 ---------------------------------------------
5283 -- Check if task has expenditure item
5284 -- Check if task has purchase order distribution
5285 -- Check if task has purchase order requisition
5286 -- Check if task has supplier invoices
5287 -- check if task has supplier invoice distribution
5288 -- Check if task has commitment transaction
5289 -- Check if task has compensation rule set
5290 -- Check if task has draft invoices
5291 -- Check if task has Project_customers
5292
5293 PROCEDURE perform_task_validations
5294 (
5295 p_project_id IN NUMBER,
5296 p_task_id IN NUMBER,
5297 x_error_code OUT NOCOPY NUMBER,
5298 x_error_msg_code OUT NOCOPY VARCHAR2
5299 )
5300 IS
5301 l_user_id NUMBER;
5302 l_login_id NUMBER;
5303 l_debug_mode VARCHAR2(1);
5304
5305 l_debug_level3 CONSTANT NUMBER := 3;
5306 l_debug_level5 CONSTANT NUMBER := 5;
5307
5308
5309 /*CURSOR c_tasks_in_hierarchy IS --Commented the following cursor as this is not needed after perf bug fix Bug#4964992
5310 SELECT TASK_ID
5311 FROM PA_TASKS
5312 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
5313 AND PROJECT_ID = p_project_id
5314 START WITH TASK_ID = p_TASK_ID
5315 AND PROJECT_ID = p_project_id; */
5316
5317 /*l_task_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(); */ -- Commented this as this is no more needed after Bug#4964992 fix.
5318 l_dummy number:=0;
5319 l_task_tbl sub_task; --Added this for Bug#4964992.
5320
5321 BEGIN
5322 x_error_code := 0;
5323 x_error_msg_code := NULL ;
5324
5325 l_user_id := fnd_global.user_id;
5326 l_login_id := fnd_global.login_id;
5327 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
5328
5329 IF l_debug_mode = 'Y' THEN
5330 PA_DEBUG.set_curr_function( p_function =>'PERFORM_TASK_VALIDATIONS' , p_debug_mode => l_debug_mode );
5331 END IF;
5332
5333 IF l_debug_mode = 'Y' THEN
5334 Pa_Debug.g_err_stage:= 'TASK_VALIDATIONS : Printing Input parameters';
5335 Pa_Debug.WRITE('pa_proj_tsk_utils', Pa_Debug.g_err_stage ,l_debug_level3 );
5336 Pa_Debug.WRITE('pa_proj_tsk_utils', 'p_task_id'||':'||p_task_id , l_debug_level3);
5337 END IF;
5338
5339 /*OPEN c_tasks_in_hierarchy; -- Commented this for Bug#4964992
5340 FETCH c_tasks_in_hierarchy BULK COLLECT INTO l_task_id_tbl;
5341 CLOSE c_tasks_in_hierarchy;*/
5342
5343 l_task_tbl := get_task_hierarchy(p_project_id,p_task_id); -- Added this call for Bug#4964992
5344 IF nvl(l_task_tbl.COUNT,0)>0 THEN
5345 /*FOR i IN l_task_id_tbl.FIRST..l_task_id_tbl.LAST --Commented by Sunkalya for perf fix Bug#4964992
5346 LOOP */
5347
5348 --Check if task has expenditure item
5349 BEGIN
5350
5351 l_dummy := 0;
5352 SELECT
5353 1 into l_dummy
5354 FROM
5355 sys.dual
5356 WHERE
5357 exists (SELECT NULL
5358 FROM PA_EXPENDITURE_ITEMS_all pei,table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5359 WHERE pei.TASK_ID = st.task_id)
5360 or exists (SELECT NULL
5361 FROM PA_EI_DENORM ped, table(cast(l_task_tbl as sub_task)) st
5362 WHERE ped.TASK_ID = st.task_id);
5363 IF l_dummy = 1 THEN
5364 x_error_code :=50;
5365 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
5366 return;
5367 END IF;
5368
5369 EXCEPTION
5370 WHEN NO_DATA_FOUND THEN
5371 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS : No Expenditure Items exist in the entire task hierarchy';
5372 IF l_debug_mode = 'Y' THEN
5373 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5374 END IF;
5375 WHEN OTHERS THEN
5376 x_error_code := SQLCODE;
5377 x_error_msg_code := substrb(SQLERRM,1,120);
5378 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking Expenditure Items';
5379
5380 IF l_debug_mode = 'Y' THEN
5381 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5382 END IF;
5383 return;
5384 END;
5385
5386 -- Check if task has purchase order distribution
5387 BEGIN
5388 l_dummy := 0;
5389 SELECT
5390 1 into l_dummy
5391 FROM
5392 sys.dual
5393 WHERE
5394 exists (SELECT NULL
5395 FROM po_distributions_all poa, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5396 where poa.project_id = p_project_id
5397 AND poa.TASK_ID = st.task_id);
5398
5399 IF l_dummy = 1 THEN
5400 x_error_code :=60;
5401 x_error_msg_code := 'PA_TSK_PO_DIST_EXIST';
5402 return;
5403 END IF;
5404
5405 EXCEPTION
5406 WHEN NO_DATA_FOUND THEN
5407 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No purchase order distribution exist in the entire task hierarchy';
5408 IF l_debug_mode = 'Y' THEN
5409 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5410 END IF;
5411 WHEN OTHERS THEN
5412 x_error_code := SQLCODE;
5413 x_error_msg_code := substrb(SQLERRM,1,120);
5414 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking purchase order distribution';
5415
5416 IF l_debug_mode = 'Y' THEN
5417 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5418 END IF;
5419
5420 return;
5421 END;
5422
5423 -- Check if task has purchase order requisition
5424 BEGIN
5425 l_dummy := 0;
5426 SELECT
5427 1 into l_dummy
5428 FROM
5429 sys.dual
5430 WHERE
5431 exists (SELECT NULL
5432 FROM po_req_distributions_all prd, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5433 where prd.project_id = p_project_id
5434 AND prd.TASK_ID = st.task_id);
5435
5436 IF l_dummy = 1 THEN
5437 x_error_code :=70;
5438 x_error_msg_code :='PA_TSK_PO_REQ_DIST_EXIST';
5439 return;
5440 END IF;
5441
5442 EXCEPTION
5443 WHEN NO_DATA_FOUND THEN
5444 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No purchase order requisition exist in the entire task hierarchy';
5445 IF l_debug_mode = 'Y' THEN
5446 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5447 END IF;
5448 WHEN OTHERS THEN
5449 x_error_code := SQLCODE;
5450 x_error_msg_code := substrb(SQLERRM,1,120);
5451 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking purchase order requisition';
5452
5453 IF l_debug_mode = 'Y' THEN
5454 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5455 END IF;
5456
5457 return;
5458 END;
5459
5460 -- Check if task has supplier invoices
5461 BEGIN
5462 l_dummy := 0;
5463 SELECT
5464 1 into l_dummy
5465 FROM
5466 sys.dual
5467 WHERE
5468 exists (SELECT NULL
5469 FROM ap_invoices_all aia, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5470 where aia.project_id = p_project_id
5471 AND aia.TASK_ID = st.task_id);
5472
5473 IF l_dummy = 1 THEN
5474 x_error_code :=80;
5475 x_error_msg_code :='PA_TSK_AP_INV_EXIST';
5476 return;
5477 END IF;
5478
5479 EXCEPTION
5480 WHEN NO_DATA_FOUND THEN
5481 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No supplier invoices exist in the entire task hierarchy' ;
5482 IF l_debug_mode = 'Y' THEN
5483 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5484 END IF;
5485 WHEN OTHERS THEN
5486 x_error_code := SQLCODE;
5487 x_error_msg_code := substrb(SQLERRM,1,120);
5488 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking supplier invoices';
5489
5490 IF l_debug_mode = 'Y' THEN
5491 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5492 END IF;
5493 return;
5494 END;
5495
5496 -- check if task has supplier invoice distribution
5497 BEGIN
5498 l_dummy := 0;
5499 SELECT
5500 1 into l_dummy
5501 FROM
5502 sys.dual
5503 WHERE
5504 exists (SELECT NULL
5505 FROM ap_invoice_distributions_all aid, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5506 where aid.project_id = p_project_id
5507 AND aid.TASK_ID = st.task_id);
5508
5509 IF l_dummy = 1 THEN
5510 x_error_code :=90;
5511 x_error_msg_code :='PA_TSK_AP_INV_DIST_EXIST';
5512 return;
5513 END IF;
5514
5515 EXCEPTION
5516 WHEN NO_DATA_FOUND THEN
5517 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No supplier invoice distribution exist';
5518 IF l_debug_mode = 'Y' THEN
5519 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5520 END IF;
5521 WHEN OTHERS THEN
5522 x_error_code := SQLCODE;
5523 x_error_msg_code := substrb(SQLERRM,1,120);
5524 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking supplier invoice distribution' ;
5525
5526 IF l_debug_mode = 'Y' THEN
5527 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5528 END IF;
5529 return;
5530 END;
5531
5532 -- Check if task has commitment transaction
5533 BEGIN
5534 l_dummy := 0;
5535 SELECT
5536 1 into l_dummy
5537 FROM
5538 sys.dual
5539 WHERE
5540 exists (SELECT NULL
5541 FROM pa_commitment_txns pct, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5542 where pct.project_id = p_project_id
5543 AND pct.TASK_ID = st.task_id);
5544
5545 IF l_dummy = 1 THEN
5546 x_error_code :=110;
5547 x_error_msg_code :='PA_TSK_CMT_TXN_EXIST';
5548 return;
5549 END IF;
5550
5551 EXCEPTION
5552 WHEN NO_DATA_FOUND THEN
5553 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No commitment transaction exist';
5554 IF l_debug_mode = 'Y' THEN
5555 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5556 END IF;
5557 WHEN OTHERS THEN
5558 x_error_code := SQLCODE;
5559 x_error_msg_code := substrb(SQLERRM,1,120);
5560 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking commitment transaction';
5561
5562 IF l_debug_mode = 'Y' THEN
5563 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5564 END IF;
5565 return;
5566 END;
5567
5568 -- Check if task has compensation rule set
5569 BEGIN
5570 l_dummy := 0;
5571 SELECT
5572 1 into l_dummy
5573 FROM
5574 sys.dual
5575 WHERE
5576 exists (SELECT NULL
5577 FROM pa_comp_rule_ot_defaults_all pcr, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5578 where pcr.project_id = p_project_id
5579 AND pcr.TASK_ID = st.task_id)
5580 or exists (SELECT NULL
5581 FROM pa_org_labor_sch_rule pol, table(cast(l_task_tbl as sub_task)) st
5582 where overtime_project_id = p_project_id
5583 AND pol.overtime_TASK_ID = st.task_id);
5584
5585 IF l_dummy = 1 THEN
5586 x_error_code :=120;
5587 x_error_msg_code :='PA_TSK_COMP_RULE_SET_EXIST';
5588 return;
5589 END IF;
5590
5591 EXCEPTION
5592 WHEN NO_DATA_FOUND THEN
5593 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No compensation rule set exist';
5594 IF l_debug_mode = 'Y' THEN
5595 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5596 END IF;
5597 WHEN OTHERS THEN
5598 x_error_code := SQLCODE;
5599 x_error_msg_code := substrb(SQLERRM,1,120);
5600 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking compensation rule set';
5601
5602 IF l_debug_mode = 'Y' THEN
5603 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5604 END IF;
5605 return;
5606 END;
5607
5608 -- Check if task has draft invoices
5609 BEGIN
5610 l_dummy := 0;
5611 SELECT
5612 1 into l_dummy
5613 FROM
5614 sys.dual
5615 WHERE
5616 exists (SELECT NULL
5617 FROM pa_draft_invoice_details_all pdi, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5618 where pdi.CC_TAX_TASK_ID =st.task_id);
5619
5620 IF l_dummy = 1 THEN
5621 x_error_code :=160;
5622 x_error_msg_code := 'PA_TSK_CC_DINV_EXIST';
5623 return;
5624 END IF;
5625
5626 EXCEPTION
5627 WHEN NO_DATA_FOUND THEN
5628 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No draft invoice exist';
5629 IF l_debug_mode = 'Y' THEN
5630 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5631 END IF;
5632 WHEN OTHERS THEN
5633 x_error_code := SQLCODE;
5634 x_error_msg_code := substrb(SQLERRM,1,120);
5635 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking draft invoices';
5636
5637 IF l_debug_mode = 'Y' THEN
5638 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5639 END IF;
5640 return;
5641 END;
5642
5643 -- Check if task has Project_customers
5644 BEGIN
5645 l_dummy := 0;
5646 SELECT
5647 1 into l_dummy
5648 FROM
5649 sys.dual
5650 WHERE
5651 exists (SELECT NULL
5652 FROM pa_project_customers pc, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5653 where pc.project_id = p_project_id
5654 and pc.receiver_task_id =st.task_id);
5655
5656 IF l_dummy = 1 THEN
5657 x_error_code :=170;
5658 x_error_msg_code := 'PA_TSK_CC_CUST_EXIST';
5659 return;
5660 END IF;
5661
5662 EXCEPTION
5663 WHEN NO_DATA_FOUND THEN
5664 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No Project_customers exist';
5665 IF l_debug_mode = 'Y' THEN
5666 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5667 END IF;
5668 WHEN OTHERS THEN
5669 x_error_code := SQLCODE;
5670 x_error_msg_code := substrb(SQLERRM,1,120);
5671 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking project customers';
5672
5673 IF l_debug_mode = 'Y' THEN
5674 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5675 END IF;
5676 return;
5677 END;
5678
5679 /* added these checks as a part of bug fix 6079887 */
5680 -- Check if task has iExpense records
5681 BEGIN
5682 l_dummy := 0;
5683 SELECT
5684 1 into l_dummy
5685 FROM
5686 sys.dual
5687 WHERE
5688 exists (SELECT NULL
5689 FROM ap_exp_report_dists_all er, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5690 where er.project_id = p_project_id
5691 AND er.TASK_ID = st.task_id);
5692
5693 IF l_dummy = 1 THEN
5694 x_error_code :=180;
5695 x_error_msg_code := 'PA_TSK_IEXP_EXIST';
5696 return;
5697 END IF;
5698
5699 EXCEPTION
5700 WHEN NO_DATA_FOUND THEN
5701 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No IExpenses exist in the entire task hierarchy';
5702 IF l_debug_mode = 'Y' THEN
5703 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5704 END IF;
5705 WHEN OTHERS THEN
5706 x_error_code := SQLCODE;
5707 x_error_msg_code := substrb(SQLERRM,1,120);
5708 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking IExpense Records';
5709
5710 IF l_debug_mode = 'Y' THEN
5711 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5712 END IF;
5713
5714 return;
5715 END;
5716
5717 -- Check if task has Inventory Transaction records
5718 BEGIN
5719 l_dummy := 0;
5720 SELECT
5721 1 into l_dummy
5722 FROM
5723 sys.dual
5724 WHERE
5725 exists (SELECT NULL
5726 FROM mtl_material_transactions mtl, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5727 where mtl.project_id = p_project_id
5728 AND mtl.TASK_ID = st.task_id);
5729
5730 IF l_dummy = 1 THEN
5731 x_error_code :=190;
5732 x_error_msg_code := 'PA_TSK_INV_TRANS_EXIST';
5733 return;
5734 END IF;
5735
5736 EXCEPTION
5737 WHEN NO_DATA_FOUND THEN
5738 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No Inventory transactions exist in the entire task hierarchy';
5739 IF l_debug_mode = 'Y' THEN
5740 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5741 END IF;
5742 WHEN OTHERS THEN
5743 x_error_code := SQLCODE;
5744 x_error_msg_code := substrb(SQLERRM,1,120);
5745 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking Inventory Records';
5746
5747 IF l_debug_mode = 'Y' THEN
5748 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5749 END IF;
5750
5751 return;
5752 END;
5753 /* added these checks as a part of bug fix 6079887 */
5754
5755 /* END LOOP; */ --Commented By sunkalya for perf fix Bug#4964992
5756 END IF;
5757 IF l_debug_mode = 'Y' THEN
5758 Pa_Debug.reset_curr_function;
5759 END IF;
5760
5761 EXCEPTION
5762 WHEN OTHERS THEN
5763 x_error_code := SQLCODE;
5764 x_error_msg_code := substrb(SQLERRM,1,120);
5765
5766 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_error_msg_code;
5767
5768 Fnd_Msg_Pub.add_exc_msg
5769 ( p_pkg_name => 'pa_proj_tsk_utils'
5770 ,p_procedure_name => 'TASK_VALIDATIONS'
5771 ,p_error_text => x_error_msg_code);
5772
5773 IF l_debug_mode = 'Y' THEN
5774 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,
5775 l_debug_level5);
5776
5777 Pa_Debug.reset_curr_function;
5778 END IF;
5779 -- Important : Dont Raise : It will be taken care by Caller API
5780
5781 end PERFORM_TASK_VALIDATIONS;
5782
5783
5784
5785
5786 -- API name : get_task_hierarchy
5787 -- Type : Function
5788 -- Pre-reqs : None
5789 -- Return Value : Type sub_task (Table)
5790
5791 -- Prameters :
5792 -- p_project_id IN NUMBER
5793 -- p_task_id IN NUMBER
5794
5795 -- Created By : Sunkalya
5796 -- Created Date : 28-Feb-2006
5797
5798 -- Purpose:
5799 -- This Function has been included for perf fix 4964992
5800 ----------------------------------------------------------------------------------------------------------
5801 -- This function returns the entire hierarchy for the
5802 -- passed task_id.
5803
5804 -- Note that START WITH - CONNECT BY CLAUSE cant be avoided as we want the task passed and all its children
5805 -- in the hierarchy
5806 -- This is known to cause FTS on pa_tasks . But,this cant be avoided.
5807
5808 -- (ie) if the hierarchy is :
5809 -- 1
5810 -- 11
5811 -- 111
5812 -- 1111
5813
5814 -- then ,if 1's task id is passed ,then we need the entire branch as above in
5815 -- hierarchy.
5816 -------------------------------------------------------------------------------------------------------------
5817
5818 -- History
5819
5820 -- 28-Feb-2006 Sunkalya Created. Bug#4964992
5821
5822 FUNCTION get_task_hierarchy(
5823 p_project_id IN NUMBER,
5824 p_task_id IN NUMBER
5825 )
5826 RETURN sub_task
5827 IS
5828
5829 l_task_tbl sub_task;
5830 l_debug_level3 CONSTANT NUMBER := 3;
5831 l_user_id NUMBER;
5832 l_login_id NUMBER;
5833 l_debug_mode VARCHAR2(1);
5834
5835 CURSOR task_hierarchy IS
5836 SELECT
5837 task_rec(task_name,task_id)
5838 FROM
5839 pa_tasks
5840 CONNECT BY PRIOR
5841 task_id = parent_task_id AND
5842 project_id = p_project_id
5843 START WITH
5844 task_id = p_task_id AND
5845 project_id = p_project_id;
5846
5847 BEGIN
5848 l_user_id := fnd_global.user_id;
5849 l_login_id := fnd_global.login_id;
5850
5851 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
5852
5853 IF l_debug_mode = 'Y' THEN
5854 PA_DEBUG.set_curr_function( p_function =>'get_task_hierarchy' , p_debug_mode => l_debug_mode );
5855 END IF;
5856
5857 OPEN task_hierarchy;
5858 FETCH task_hierarchy BULK COLLECT INTO l_task_tbl;
5859 CLOSE task_hierarchy;
5860
5861 RETURN l_task_tbl;
5862
5863 EXCEPTION
5864 WHEN OTHERS THEN
5865 Pa_Debug.g_err_stage:= ' API:get_task_hierarchy :Unexpected Error occured while retrieving task strucutre for '|| p_task_id;
5866 IF l_debug_mode = 'Y' THEN
5867 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5868 END IF;
5869 RAISE;
5870 END get_task_hierarchy;
5871
5872 function IS_LOWEST_PROJ_TASK(
5873 p_task_version_id NUMBER,
5874 p_project_id NUMBER) RETURN VARCHAR2
5875 is
5876 l_dummy number;
5877 cursor child_exist IS
5878 select 1 from dual where exists(
5879 select 1
5880 from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elements ppe
5881 where por.object_type_from = 'PA_TASKS'
5882 and por.object_id_from1 = p_task_version_id
5883 and por.relationship_type = 'S'
5884 and por.object_id_to1 = ppev.element_version_id
5885 and ppe.PROJ_ELEMENT_ID = ppev.PROJ_ELEMENT_ID
5886 and nvl(ppe.LINK_TASK_FLAG,'N') <> 'Y');
5887
5888 BEGIN
5889
5890 OPEN child_exist;
5891 FETCH child_exist into l_dummy;
5892 IF child_exist%NOTFOUND then
5893 --Cannot find child. It is lowest task
5894 CLOSE child_exist;
5895 return 'Y';
5896 ELSE
5897 --Child found. Not lowest task
5898 CLOSE child_exist;
5899 return 'N';
5900 END IF;
5901 EXCEPTION
5902 WHEN OTHERS THEN
5903 raise;
5904 END IS_LOWEST_PROJ_TASK;
5905
5906
5907 END PA_PROJ_ELEMENTS_UTILS;
[Home] [Help]