1 PACKAGE BODY PA_PROJ_ELEMENTS_UTILS AS
2 /* $Header: PATSK1UB.pls 120.23.12020000.4 2013/05/23 13:05:09 djambhek 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
1155 , p_structure_type VARCHAR2 -- Bug# 10385414
1156 ) RETURN VARCHAR2 IS
1157 /* CURSOR cur_obj_rel
1158 IS
1159 SELECT 'x'
1160 FROM pa_object_relationships
1161 WHERE object_id_from1 = p_element_version_id
1162 --hsiu: bug 2800553: performance
1163 and rownum < 2
1164 --start with object_id_from1 = p_element_version_id
1165 AND object_type_from = 'PA_TASKS'
1166 AND relationship_type = 'S'
1167 And object_id_to1 NOT IN (
1168 select b.object_id_from1
1169 from pa_object_relationships a,
1170 pa_object_relationships b
1171 where a.object_id_from1 = p_element_version_id
1172 and a.object_id_to1 = b.object_id_from1
1173 and a.relationship_type = 'S'
1174 and b.relationship_type IN ('LW', 'LF'))
1175 ;*/
1176
1177 -- Bug 6156686
1178 CURSOR cur_obj_rel
1179 IS
1180 SELECT NULL
1181 FROM DUAL
1182 WHERE EXISTS
1183 (SELECT NULL
1184 FROM pa_object_relationships por,
1185 pa_proj_element_versions pev,
1186 pa_proj_elements pe
1187 WHERE por.object_id_from1 = p_element_version_id
1188 AND por.object_type_from ='PA_TASKS'
1189 AND por.relationship_type = 'S'
1190 AND por.object_id_to1=pev.element_version_id
1191 AND pe.proj_element_id=pev.proj_element_id
1192 AND (((NVL(pe.link_task_flag,'N') <> 'Y'))
1193 -- AND pev.financial_task_flag = 'Y'))
1194 OR (pe.task_status is not null))); -- Added AND Condition for Bug 7210236
1195 --Added the OR condition for bug 8992059
1196
1197 --connect by object_id_from1 = prior object_id_to1 ;
1198
1199 -- Bug#10385414 - Cursor specific to financial structure. Cursor is important
1200 -- when there is a partially shared structure and the same data is shared between
1201 -- workplan and financial structure, but some tasks are not marked for financial structure
1202 CURSOR cur_obj_rel_fin
1203 IS
1204 SELECT NULL
1205 FROM DUAL
1206 WHERE EXISTS
1207 (SELECT NULL
1208 FROM pa_object_relationships por,
1209 pa_proj_element_versions pev,
1210 pa_proj_elements pe
1211 WHERE por.object_id_from1 = p_element_version_id
1212 AND por.object_type_from ='PA_TASKS'
1213 AND por.relationship_type = 'S'
1214 AND por.object_id_to1=pev.element_version_id
1215 AND pe.proj_element_id=pev.proj_element_id
1216 AND NVL(pe.link_task_flag,'N') <> 'Y'
1217 AND pev.financial_task_flag = 'Y'); -- checks for financial subtask for current task
1218
1219
1220 v_dummy_char VARCHAR2(1);
1221 BEGIN
1222
1223 IF p_structure_type = 'FINANCIAL' THEN
1224 OPEN cur_obj_rel_fin;
1225 FETCH cur_obj_rel_fin INTO v_dummy_char;
1226 IF cur_obj_rel_fin%FOUND
1227 THEN
1228 CLOSE cur_obj_rel_fin;
1229 RETURN 'Y';
1230 ELSE
1231 CLOSE cur_obj_rel_fin;
1232 RETURN 'N';
1233 END IF;
1234 ELSE -- if Structure is Workplan - which is the default option
1235
1236 OPEN cur_obj_rel;
1237 FETCH cur_obj_rel INTO v_dummy_char;
1238 IF cur_obj_rel%FOUND
1239 THEN
1240 CLOSE cur_obj_rel;
1241 RETURN 'Y';
1242 ELSE
1243 CLOSE cur_obj_rel;
1244 RETURN 'N';
1245 END IF;
1246 END IF;
1247
1248 END is_summary_task_or_structure;
1249
1250
1251 procedure Check_Date_range
1252 (
1253 p_scheduled_start_date IN DATE :=null
1254 ,p_scheduled_end_date IN DATE :=null
1255 ,p_obligation_start_date IN DATE :=null
1256 ,p_obligation_end_date IN DATE :=null
1257 ,p_actual_start_date IN DATE :=null
1258 ,p_actual_finish_date IN DATE :=null
1259 ,p_estimate_start_date IN DATE :=null
1260 ,p_estimate_finish_date IN DATE :=null
1261 ,p_early_start_date IN DATE :=null
1262 ,p_early_end_date IN DATE :=null
1263 ,p_late_start_date IN DATE :=null
1264 ,p_late_end_date IN DATE :=null
1265 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1266 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
1267 ) IS
1268
1269 begin
1270 x_return_status := FND_API.G_RET_STS_SUCCESS;
1271
1272 IF p_scheduled_start_date IS NOT NULL AND p_scheduled_end_date IS NOT NULL
1273 THEN
1274 IF p_scheduled_start_date > p_scheduled_end_date
1275 THEN
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277 x_error_message_code := 'PA_PS_SCH_ST_DT_GT_EN_DT';
1278 return;
1279 END IF;
1280 END IF;
1281
1282 IF p_obligation_start_date IS NOT NULL AND p_obligation_end_date IS NOT NULL
1283 THEN
1284 IF p_obligation_start_date > p_obligation_end_date
1285 THEN
1286 x_return_status := FND_API.G_RET_STS_ERROR;
1287 x_error_message_code := 'PA_PS_OBL_ST_DT_GT_EN_DT';
1288 return;
1289 END IF;
1290 END IF;
1291
1292 IF p_actual_start_date IS NOT NULL AND p_actual_finish_date IS NOT NULL
1293 THEN
1294 IF p_actual_start_date > p_actual_finish_date
1295 THEN
1296 x_return_status := FND_API.G_RET_STS_ERROR;
1297 x_error_message_code := 'PA_PS_ACT_ST_DT_GT_EN_DT';
1298 return;
1299 END IF;
1300 END IF;
1301
1302 IF p_estimate_start_date IS NOT NULL AND p_estimate_finish_date IS NOT NULL
1303 THEN
1304 IF p_estimate_start_date > p_estimate_finish_date
1305 THEN
1306 x_return_status := FND_API.G_RET_STS_ERROR;
1307 x_error_message_code := 'PA_PS_EST_ST_DT_GT_EN_DT';
1308 return;
1309 END IF;
1310 END IF;
1311
1312 IF p_early_start_date IS NOT NULL AND p_early_end_date IS NOT NULL
1313 THEN
1314 IF p_early_start_date > p_early_end_date
1315 THEN
1316 x_return_status := FND_API.G_RET_STS_ERROR;
1317 x_error_message_code := 'PA_PS_ERL_ST_DT_GT_EN_DT';
1318 return;
1319 END IF;
1320 END IF;
1321
1322 IF p_late_start_date IS NOT NULL AND p_late_end_date IS NOT NULL
1323 THEN
1324 IF p_late_start_date > p_late_end_date
1325 THEN
1326 x_return_status := FND_API.G_RET_STS_ERROR;
1327 x_error_message_code := 'PA_PS_LAT_ST_DT_GT_EN_DT';
1328 return;
1329 END IF;
1330 END IF;
1331 -- 4537865
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1335 x_error_message_code := SQLCODE ;
1336 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
1337 p_procedure_name => 'Check_Date_range',
1338 p_error_text => SUBSTRB(SQLERRM,1,240));
1339 RAISE;
1340 end Check_Date_range;
1341
1342 PROCEDURE Project_Name_Or_Id
1343 (
1344 p_project_name IN VARCHAR2
1345 ,p_project_id IN NUMBER
1346 ,p_check_id_flag IN VARCHAR2 := 'Y'
1347 ,x_project_id OUT NOCOPY NUMBER -- 4537865
1348 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1349 ,x_error_msg_code OUT NOCOPY VARCHAR2 -- 4537865
1350 )
1351 IS
1352 BEGIN
1353 IF (p_project_id IS NOT NULL) THEN
1354 IF (p_check_id_flag = 'Y') THEN
1355 select project_id
1356 into x_project_id
1357 from pa_projects_all
1358 where project_id = p_project_id;
1359 ELSE
1360 x_project_id := p_project_id;
1361 END IF;
1362 ELSE
1363 select project_id
1364 into x_project_id
1365 from pa_projects_all
1366 where segment1 = p_project_name;
1367 END IF;
1368 x_return_status := FND_API.G_RET_STS_SUCCESS;
1369 EXCEPTION
1370 WHEN no_data_found THEN
1371 x_return_status:= FND_API.G_RET_STS_ERROR;
1372 x_error_msg_code:= 'PA_PS_INVALID_PRJ_NAME';
1373 x_project_id := NULL ; -- 4537865
1374
1375 WHEN too_many_rows THEN
1376 x_return_status:= FND_API.G_RET_STS_ERROR;
1377 x_error_msg_code:= 'PA_PS_PRJ_NAME_NOT_UNIQUE';
1378 x_project_id := NULL ; -- 4537865
1379 WHEN OTHERS THEN
1380 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1381 x_project_id := NULL ; -- 4537865
1382 RAISE;
1383 END Project_Name_Or_Id;
1384
1385 PROCEDURE task_Ver_Name_Or_Id
1386 (
1387 p_task_name IN VARCHAR2
1388 ,p_task_version_id IN NUMBER
1389 ,p_structure_version_id IN NUMBER
1390 ,p_check_id_flag IN VARCHAR2 := 'Y'
1391 ,x_task_version_id OUT NOCOPY NUMBER -- 4537865
1392 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1393 ,x_error_msg_code OUT NOCOPY VARCHAR2 -- 4537865
1394 )
1395 IS
1396 BEGIN
1397 IF (p_task_version_id IS NOT NULL) THEN
1398 IF (p_check_id_flag = 'Y') THEN
1399 select element_version_id
1400 into x_task_version_id
1401 from pa_proj_element_versions
1402 where element_version_id = p_task_version_id;
1403 ELSE
1404 x_task_version_id := p_task_version_id;
1405 END IF;
1406 ELSE
1407 select element_version_id
1408 into x_task_version_id
1409 from pa_proj_elements ppe, pa_proj_element_versions ppev
1410 where ppe.proj_element_id = ppev.proj_element_id
1411 AND ppe.name = p_task_name
1412 AND ppev.parent_structure_version_id = p_structure_version_id;
1413 null;
1414 END IF;
1415 x_return_status := FND_API.G_RET_STS_SUCCESS;
1416 EXCEPTION
1417 WHEN no_data_found THEN
1418 x_return_status:= FND_API.G_RET_STS_ERROR;
1419 x_error_msg_code:= 'PA_PS_INVALID_TSK_NAME';
1420 -- 4537865
1421 x_task_version_id := NULL ;
1422
1423 WHEN too_many_rows THEN
1424 x_return_status:= FND_API.G_RET_STS_ERROR;
1425 x_error_msg_code:= 'PA_PS_TSK_NAME_NOT_UNIQUE';
1426 -- 4537865
1427 x_task_version_id := NULL ;
1428
1429 WHEN OTHERS THEN
1430 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1431 -- 4537865
1432 x_task_version_id := NULL ;
1433 x_error_msg_code:= SQLCODE ;
1434
1435 RAISE;
1436 END task_Ver_Name_Or_Id;
1437
1438 /* ----------------------------------------------------------------------
1439 ||
1440 || Procedure Name: UPDATE_WBS_NUMBERS
1441 ||
1442 || Author : Andrew Lee
1443 || Description:
1444 || This procedure is called update the wbs numbers in the task
1445 || hierarchy whenever any of the following actions occur:
1446 || INSERT
1447 || INDENT
1448 || OUTDENT
1449 || COPY
1450 || DELETE
1451 || ---------------------------------------------------------------------
1452 */
1453 PROCEDURE UPDATE_WBS_NUMBERS ( p_commit IN VARCHAR2
1454 ,p_debug_mode IN VARCHAR2
1455 ,p_parent_structure_ver_id IN NUMBER
1456 ,p_task_id IN NUMBER
1457 ,p_display_seq IN NUMBER
1458 ,p_action IN VARCHAR2
1459 ,p_parent_task_id IN NUMBER
1460 ,p_task_version_id IN NUMBER DEFAULT -1 --bug 13895419
1461 ,p_ref_task_version_id IN NUMBER DEFAULT -1 --bug 13895419
1462 ,p_peer_or_sub IN VARCHAR2 DEFAULT 'xxx' --bug 13895419
1463 ,x_return_status OUT NOCOPY VARCHAR2) -- 4537865
1464 IS
1465 CURSOR TASK_INFO_CSR(c_task_id NUMBER)
1466 IS
1467 SELECT rel.object_id_from1 parent_task_id, pev.wbs_number
1468 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1469 WHERE pev.element_version_id = c_task_id
1470 AND pev.object_type = 'PA_TASKS'
1471 AND rel.object_id_to1 = pev.element_version_id
1472 AND rel.relationship_type = 'S'
1473 AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
1474
1475 CURSOR UPDATE_MASKED_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER, c_mask VARCHAR2)
1476 IS
1477 SELECT element_version_id task_id, wbs_number, display_sequence
1478 FROM PA_PROJ_ELEMENT_VERSIONS
1479 WHERE parent_structure_version_id = c_parent_structure_ver_id
1480 AND object_type = 'PA_TASKS'
1481 AND abs(display_sequence) >= abs(c_display_seq)
1482 AND display_sequence <> c_display_seq
1483 -- AND substr(wbs_number, 1, length(c_mask)) = c_mask Commented for bug 3581030
1484 AND substr(wbs_number, 1, length(c_mask)+1) = c_mask||'.' -- Added for bug 3581030
1485 ORDER BY abs(display_sequence);
1486
1487 CURSOR UPDATE_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1488 IS
1489 SELECT element_version_id task_id, wbs_number, display_sequence
1490 FROM PA_PROJ_ELEMENT_VERSIONS
1491 WHERE parent_structure_version_id = c_parent_structure_ver_id
1492 AND object_type = 'PA_TASKS'
1493 AND abs(display_sequence) >= abs(c_display_seq)
1494 AND display_sequence <> c_display_seq
1495 ORDER BY abs(display_sequence);
1496
1497 CURSOR GET_TASK_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1498 IS
1499 SELECT element_version_id task_id
1500 FROM PA_PROJ_ELEMENT_VERSIONS
1501 WHERE parent_structure_version_id = c_parent_structure_ver_id
1502 AND object_type = 'PA_TASKS'
1503 AND display_sequence = c_display_seq;
1504
1505 CURSOR GET_PREV_PEER_TASK_CSR(c_parent_structure_ver_id NUMBER, c_parent_task_id NUMBER, c_display_seq NUMBER)
1506 IS
1507 SELECT element_version_id task_id
1508 FROM PA_PROJ_ELEMENT_VERSIONS
1509 WHERE parent_structure_version_id = c_parent_structure_ver_id
1510 AND object_type = 'PA_TASKS'
1511 AND display_sequence =
1512 (SELECT max(pev.display_sequence)
1513 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1514 WHERE rel.object_type_from = 'PA_TASKS'
1515 AND rel.object_id_from1 = c_parent_task_id
1516 AND rel.relationship_type = 'S'
1517 AND rel.object_type_to = 'PA_TASKS'
1518 AND rel.object_id_to1 = pev.element_version_id
1519 AND pev.parent_structure_version_id = c_parent_structure_ver_id
1520 AND pev.display_sequence < c_display_seq);
1521
1522 -- bug 13895419 fetch data from PA_PROJ_ELEM_RELATIONSHIP_T table
1523 CURSOR GET_PREV_PEER_TASK_CSR1(c_parent_structure_ver_id NUMBER, c_parent_task_id NUMBER, c_display_seq NUMBER)
1524 IS
1525 SELECT t2.child_ver_id task_id
1526 FROM (SELECT parent_ver_id, MAX (display_sequence) max_ds
1527 FROM PA_PROJ_ELEM_RELATIONSHIP_T
1528 where parent_ver_id = c_parent_task_id
1529 and display_sequence < c_display_seq
1530 GROUP BY parent_ver_id) t1,
1531 PA_PROJ_ELEM_RELATIONSHIP_T t2
1532 WHERE t1.parent_ver_id = t2.parent_ver_id AND t1.max_ds = t2.display_sequence;
1533
1534 CURSOR GET_PREV_TOP_PEER_TASK_CSR(c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1535 IS
1536 SELECT element_version_id task_id
1537 FROM PA_PROJ_ELEMENT_VERSIONS
1538 WHERE parent_structure_version_id = c_parent_structure_ver_id
1539 AND object_type = 'PA_TASKS'
1540 AND display_sequence =
1541 (SELECT max(pev.display_sequence)
1542 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1543 WHERE rel.object_type_from = 'PA_STRUCTURES'
1544 AND rel.object_id_from1 = c_parent_structure_ver_id
1545 AND rel.relationship_type = 'S'
1546 AND rel.object_type_to = 'PA_TASKS'
1547 AND rel.object_id_to1 = pev.element_version_id
1548 AND pev.parent_structure_version_id = c_parent_structure_ver_id
1549 AND display_sequence < c_display_seq);
1550
1551 l_task_rec TASK_INFO_CSR%ROWTYPE;
1552 l_prev_task_rec TASK_INFO_CSR%ROWTYPE;
1553 l_parent_task_rec TASK_INFO_CSR%ROWTYPE;
1554 l_temp TASK_INFO_CSR%ROWTYPE;
1555 l_update_task_rec UPDATE_TASKS_CSR%ROWTYPE;
1556 l_task_id NUMBER;
1557
1558 l_count NUMBER;
1559 l_wbs_number VARCHAR2(1000);
1560 l_mask VARCHAR2(1000);
1561 l_branch_mask VARCHAR2(1000);
1562 l_mask2 VARCHAR2(1000);
1563 l_prev_disp_seq NUMBER;
1564 l_prev_task_id NUMBER;
1565 l_increment VARCHAR2(255);
1566 l_str1 VARCHAR2(1000);
1567 l_str2 VARCHAR2(1000);
1568 l_loop_wbs_number VARCHAR2(1000);
1569 l_flag BOOLEAN;
1570
1571 API_ERROR EXCEPTION;
1572 l_number NUMBER; -- Bug 2786662
1573 --Bug 13895419 start
1574 l_parent_task_id PA_OBJECT_RELATIONSHIPS.object_id_from1%TYPE;
1575 l_ref_parent_task_id PA_OBJECT_RELATIONSHIPS.object_id_from1%TYPE;
1576 --Bug 13895419 end
1577 BEGIN
1578 if p_commit = 'Y' then
1579 savepoint update_wbs_numbers;
1580 end if;
1581
1582 if(p_action <> 'DELETE') then
1583 OPEN TASK_INFO_CSR(p_task_id);
1584 FETCH TASK_INFO_CSR INTO l_task_rec;
1585 if(TASK_INFO_CSR%NOTFOUND) then
1586 CLOSE TASK_INFO_CSR;
1587 x_return_status := 'E';
1588 raise API_ERROR;
1589 end if;
1590 CLOSE TASK_INFO_CSR;
1591 end if;
1592
1593 -- INSERT or COPY
1594 if((p_action = 'INSERT') OR (p_action = 'COPY')) then
1595 if(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1596 -- Inserted task is a top task
1597 -- Added the leading hint below for bug 3416314
1598 -- Smukka Merging branch 40 as of now with main branch
1599 SELECT /*+ LEADING (rel) */ count(pev.element_version_id)
1600 INTO l_count
1601 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1602 WHERE pev.parent_structure_version_id = p_parent_structure_ver_id
1603 AND pev.object_type = 'PA_TASKS'
1604 AND abs(pev.display_sequence) <= abs(p_display_seq)
1605 AND rel.object_id_to1 = pev.element_version_id
1606 AND rel.relationship_type = 'S'
1607 AND rel.object_type_from = 'PA_STRUCTURES'
1608 AND rel.object_id_from1 = p_parent_structure_ver_id
1609 AND rel.object_type_to = 'PA_TASKS'; --Added for Bug 6430953
1610
1611 l_wbs_number := l_count;
1612 l_mask := 'NONE';
1613 else
1614 -- Inserted task is a child task
1615 l_prev_disp_seq := abs(p_display_seq) - 1;
1616 OPEN GET_TASK_CSR(p_parent_structure_ver_id, l_prev_disp_seq);
1617 FETCH GET_TASK_CSR INTO l_prev_task_id;
1618 if(GET_TASK_CSR%NOTFOUND) then
1619 CLOSE GET_TASK_CSR;
1620 OPEN GET_TASK_CSR(p_parent_structure_ver_id, l_prev_disp_seq * -1);
1621 FETCH GET_TASK_CSR INTO l_prev_task_id;
1622 if(GET_TASK_CSR%NOTFOUND) then
1623 CLOSE GET_TASK_CSR;
1624 x_return_status := 'E';
1625 raise API_ERROR;
1626 end if;
1627 end if;
1628
1629 CLOSE GET_TASK_CSR;
1630
1631
1632 if(l_prev_task_id = l_task_rec.parent_task_id) then
1633 -- Previous task is a parent
1634 OPEN TASK_INFO_CSR(l_prev_task_id);
1635 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1636 CLOSE TASK_INFO_CSR;
1637
1638 l_wbs_number := l_prev_task_rec.wbs_number || '.1';
1639 l_mask := l_prev_task_rec.wbs_number;
1640 else
1641 -- Previous task is a peer task
1642 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1643 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1644 CLOSE TASK_INFO_CSR;
1645 --Bug 13895419 start
1646 IF p_peer_or_sub = 'xxx' then
1647 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, l_task_rec.parent_task_id, p_display_seq);
1648 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1649 CLOSE GET_PREV_PEER_TASK_CSR;
1650 ELSE --If p_peer_or_sub is not default value, then open GET_PREV_PEER_TASK_CSR1 cursor.
1651 OPEN GET_PREV_PEER_TASK_CSR1(p_parent_structure_ver_id, l_task_rec.parent_task_id, p_display_seq);
1652 FETCH GET_PREV_PEER_TASK_CSR1 INTO l_prev_task_id;
1653 CLOSE GET_PREV_PEER_TASK_CSR1;
1654 END if;
1655 --Bug 13895419 end
1656 OPEN TASK_INFO_CSR(l_prev_task_id);
1657 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1658 CLOSE TASK_INFO_CSR;
1659
1660 l_increment := to_char(to_number(substr(l_prev_task_rec.wbs_number, length(l_parent_task_rec.wbs_number) + 2)) + 1);
1661 l_wbs_number := substr(l_prev_task_rec.wbs_number, 1, length(l_parent_task_rec.wbs_number)) || '.' || l_increment;
1662 l_mask := l_parent_task_rec.wbs_number;
1663 end if;
1664 end if; -- ig(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1665
1666 -- Update the WBS number for the inserted task
1667 UPDATE PA_PROJ_ELEMENT_VERSIONS
1668 SET wbs_number = l_wbs_number
1669 WHERE element_version_id = p_task_id;
1670
1671 -- Loop through tasks that have a greater display seq than the current
1672 -- and begins with the same mask (in the same branch)
1673 if(l_mask = 'NONE') then
1674 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
1675 else
1676 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
1677 end if;
1678 --Bug 13895419 start
1679 --Fetch parent_task_id and ref_parent_task_id for source task and target task respectively from db.
1680 begin
1681 SELECT rel.object_id_from1 INTO l_parent_task_id
1682 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1683 WHERE pev.element_version_id = p_task_version_id
1684 AND pev.object_type = 'PA_TASKS'
1685 AND rel.object_id_to1 = pev.element_version_id
1686 AND rel.relationship_type = 'S'
1687 AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
1688
1689 SELECT rel.object_id_from1 INTO l_ref_parent_task_id
1690 FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1691 WHERE pev.element_version_id = p_ref_task_version_id
1692 AND pev.object_type = 'PA_TASKS'
1693 AND rel.object_id_to1 = pev.element_version_id
1694 AND rel.relationship_type = 'S'
1695 AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
1696 EXCEPTION
1697 WHEN OTHERS then
1698 l_parent_task_id := null;
1699 l_ref_parent_task_id := null;
1700 end;
1701 -- Excecute below loop only if p_peer_or_sub is Sub or default value or parent task is not same for source and destination task.
1702 IF p_peer_or_sub = 'SUB' OR p_peer_or_sub = 'xxx' or (p_peer_or_sub = 'PEER' and l_parent_task_id <> l_ref_parent_task_id)
1703 then
1704 --Bug 13895419 end
1705 LOOP
1706 if(l_mask = 'NONE') then
1707 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
1708 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
1709
1710 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
1711 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
1712 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
1713 l_str1 := to_char(to_number(l_str1 + 1));
1714
1715 l_wbs_number := l_str1 || l_str2;
1716 else
1717 l_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) + 1);
1718 end if;
1719 else
1720 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
1721 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
1722
1723 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
1724 if(instr(l_str1, '.') <> 0) then
1725 l_str2 := substr(l_str1, instr(l_str1, '.'));
1726 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
1727 l_str1 := to_char(to_number(l_str1) + 1);
1728
1729 l_wbs_number := l_mask || '.' || l_str1 || l_str2;
1730 else
1731 l_str1 := to_char(to_number(l_str1) + 1);
1732 l_wbs_number := l_mask || '.' || l_str1;
1733 end if;
1734
1735 end if;
1736
1737 -- Update the WBS number
1738 UPDATE PA_PROJ_ELEMENT_VERSIONS
1739 SET wbs_number = l_wbs_number
1740 WHERE element_version_id = l_update_task_rec.task_id;
1741 END LOOP;
1742 END if;
1743 --skkoppul bug 13895419 end
1744 if(l_mask = 'NONE') then
1745 CLOSE UPDATE_TASKS_CSR;
1746 else
1747 CLOSE UPDATE_MASKED_TASKS_CSR;
1748 end if;
1749
1750 elsif(p_action = 'INDENT') then
1751 -- Get the last previous peer task
1752 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, l_task_rec.parent_task_id, p_display_seq);
1753 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1754
1755 -- Increment the last digit of the peer task wbs number to
1756 -- get the indented task's wbs number
1757 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1758 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1759 CLOSE TASK_INFO_CSR;
1760
1761 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
1762 -- No previous peer tasks; first peer task under the parent
1763 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1764 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1765 CLOSE TASK_INFO_CSR;
1766
1767 l_wbs_number := l_prev_task_rec.wbs_number || '.1';
1768 else
1769 OPEN TASK_INFO_CSR(l_prev_task_id);
1770 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1771 CLOSE TASK_INFO_CSR;
1772
1773 l_str1 := substr(l_prev_task_rec.wbs_number, 1, length(l_parent_task_rec.wbs_number));
1774 l_str2 := substr(l_prev_task_rec.wbs_number, length(l_parent_task_rec.wbs_number) + 2);
1775 l_str2 := to_char(to_number(l_str2 + 1));
1776
1777 l_wbs_number := l_str1 || '.' || l_str2;
1778 end if;
1779
1780 CLOSE GET_PREV_PEER_TASK_CSR;
1781
1782 l_branch_mask := l_task_rec.wbs_number;
1783 --dbms_output.put_line('L_BRANCH_MASK: ' || l_branch_mask);
1784
1785 --dbms_output.put_line('L_WBS_NUMBER: ' || l_wbs_number);
1786
1787 -- Update the WBS number for the indented task
1788 UPDATE PA_PROJ_ELEMENT_VERSIONS
1789 SET wbs_number = l_wbs_number
1790 WHERE element_version_id = p_task_id;
1791
1792 -- Find l_mask
1793 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1794 FETCH TASK_INFO_CSR INTO l_temp;
1795 CLOSE TASK_INFO_CSR;
1796
1797 if(l_temp.parent_task_id = p_parent_structure_ver_id) then
1798 -- This indented task used to be a top task
1799 l_mask := 'NONE';
1800 else
1801 OPEN TASK_INFO_CSR(l_parent_task_rec.parent_task_id);
1802 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1803 CLOSE TASK_INFO_CSR;
1804
1805 l_mask := l_parent_task_rec.wbs_number;
1806 end if;
1807
1808 --dbms_output.put_line('L_MASK: ' || l_mask);
1809
1810 if(l_mask = 'NONE') then
1811 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
1812 else
1813 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
1814 end if;
1815
1816 LOOP
1817 if(l_mask = 'NONE') then
1818 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
1819 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
1820
1821 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
1822 -- Task is under the indented branch
1823 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
1824 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
1825 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
1826 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
1827 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
1828 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
1829
1830 else
1831
1832 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
1833 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
1834 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
1835 l_str1 := to_char(to_number(l_str1) - 1);
1836
1837 l_loop_wbs_number := l_str1 || l_str2;
1838 else
1839 l_loop_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) - 1);
1840 end if;
1841 end if;
1842
1843 else
1844 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
1845 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
1846
1847 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
1848 -- Task is under the indented branch
1849 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
1850 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
1851 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
1852 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
1853 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
1854 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
1855
1856 else
1857
1858 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
1859 if(instr(l_str1, '.') <> 0) then
1860 l_str2 := substr(l_str1, instr(l_str1, '.'));
1861 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
1862 l_str1 := to_char(to_number(l_str1) - 1);
1863
1864 l_loop_wbs_number := l_mask || '.' || l_str1 || l_str2;
1865 else
1866 l_str1:= to_char(to_number(l_str1) - 1);
1867 l_loop_wbs_number := l_mask || '.' || l_str1;
1868 end if;
1869 end if;
1870
1871 end if;
1872
1873 -- Update the WBS number
1874 UPDATE PA_PROJ_ELEMENT_VERSIONS
1875 SET wbs_number = l_loop_wbs_number
1876 WHERE element_version_id = l_update_task_rec.task_id;
1877 END LOOP;
1878
1879 if(l_mask = 'NONE') then
1880 CLOSE UPDATE_TASKS_CSR;
1881 else
1882 CLOSE UPDATE_MASKED_TASKS_CSR;
1883 end if;
1884
1885 elsif(p_action = 'OUTDENT') then
1886 if(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1887 -- At top level; no parent task
1888 OPEN GET_PREV_TOP_PEER_TASK_CSR(p_parent_structure_ver_id, p_display_seq);
1889 FETCH GET_PREV_TOP_PEER_TASK_CSR INTO l_prev_task_id;
1890
1891 if(GET_PREV_TOP_PEER_TASK_CSR%NOTFOUND) then
1892 x_return_status := 'E';
1893 raise API_ERROR;
1894 end if;
1895 CLOSE GET_PREV_TOP_PEER_TASK_CSR;
1896 else
1897 -- Get the last previous peer task
1898 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, l_task_rec.parent_task_id, p_display_seq);
1899 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1900
1901 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
1902 x_return_status := 'E';
1903 raise API_ERROR;
1904 end if;
1905 CLOSE GET_PREV_PEER_TASK_CSR;
1906 end if;
1907
1908 OPEN TASK_INFO_CSR(l_prev_task_id);
1909 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1910 CLOSE TASK_INFO_CSR;
1911
1912 --dbms_output.put_line('Previous peer task: ' || l_prev_task_rec.wbs_number);
1913
1914 -- Increment the last digit of the peer task wbs number to
1915 -- get the outdented task's wbs number
1916 OPEN TASK_INFO_CSR(l_task_rec.parent_task_id);
1917 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
1918 if(TASK_INFO_CSR%NOTFOUND) then
1919 -- Outdented task is now a top task
1920 l_wbs_number := l_prev_task_rec.wbs_number + 1;
1921 else
1922 l_str1 := substr(l_prev_task_rec.wbs_number, 1, length(l_parent_task_rec.wbs_number));
1923 l_str2 := substr(l_prev_task_rec.wbs_number, length(l_parent_task_rec.wbs_number) + 2);
1924 l_str2 := to_char(to_number(l_str2 + 1));
1925
1926 l_wbs_number := l_str1 || '.' || l_str2;
1927 end if;
1928
1929 CLOSE TASK_INFO_CSR;
1930
1931
1932 -- l_branch_mask contains the old wbs_number (before the task was outdented)
1933 l_branch_mask := l_task_rec.wbs_number;
1934 --dbms_output.put_line('L_BRANCH_MASK: ' || l_branch_mask);
1935
1936 -- l_mask2 contains the wbs number of the previous peer task
1937 -- This mask is used to find subsequent peer/child tasks of the outdented task (before it was
1938 -- outdented)
1939 l_mask2 := l_prev_task_rec.wbs_number;
1940 --dbms_output.put_line('L_MASK2: ' || l_mask2);
1941
1942 -- Update the WBS number for the outdented task
1943 UPDATE PA_PROJ_ELEMENT_VERSIONS
1944 SET wbs_number = l_wbs_number
1945 WHERE element_version_id = p_task_id;
1946
1947 --dbms_output.put_line('L_WBS_NUMBER: ' || l_wbs_number);
1948
1949 -- Find l_mask
1950 if(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1951 -- This outdented task has become a top task
1952 l_mask := 'NONE';
1953 else
1954 l_mask := l_parent_task_rec.wbs_number;
1955 end if;
1956
1957 --dbms_output.put_line('L_MASK: ' || l_mask);
1958
1959 if(l_mask = 'NONE') then
1960 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
1961 else
1962 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
1963 end if;
1964
1965 LOOP
1966 if(l_mask = 'NONE') then
1967 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
1968 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
1969
1970 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
1971 -- Task is under the outdented branch
1972 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
1973 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
1974 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
1975 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
1976 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
1977 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
1978
1979 elsif(substr(l_update_task_rec.wbs_number, 1, length(l_mask2)) = l_mask2) then
1980 -- Task used to be a peer of the outdented task
1981 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, p_task_id, l_update_task_rec.display_sequence);
1982 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
1983 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
1984 l_loop_wbs_number := l_wbs_number || '.1';
1985 else
1986 OPEN TASK_INFO_CSR(l_prev_task_id);
1987 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
1988 CLOSE TASK_INFO_CSR;
1989 l_str1 := substr(l_prev_task_rec.wbs_number, length(l_wbs_number) + 2);
1990 l_str1 := to_char(to_number(l_str1 + 1));
1991 l_loop_wbs_number := l_wbs_number || '.' || l_str1;
1992 end if;
1993 CLOSE GET_PREV_PEER_TASK_CSR;
1994
1995 else
1996 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
1997 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
1998 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
1999 l_str1 := to_char(to_number(l_str1) + 1);
2000
2001 l_loop_wbs_number := l_str1 || l_str2;
2002 else
2003 l_loop_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) + 1);
2004 end if;
2005 end if;
2006
2007 else
2008 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
2009 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
2010
2011 if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
2012 -- Task is under the indented branch
2013 -- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
2014 --l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
2015 l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
2016 l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
2017 l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
2018 l_loop_wbs_number := l_str1 || l_wbs_number || l_str2;
2019
2020 elsif(substr(l_update_task_rec.wbs_number, 1, length(l_mask2)) = l_mask2) then
2021 -- Task used to be a peer of the outdented task
2022 OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, p_task_id, l_update_task_rec.display_sequence);
2023 FETCH GET_PREV_PEER_TASK_CSR INTO l_prev_task_id;
2024 if(GET_PREV_PEER_TASK_CSR%NOTFOUND) then
2025 --dbms_output.put_line('HELLO2');
2026 l_loop_wbs_number := l_wbs_number || '.1';
2027 else
2028 OPEN TASK_INFO_CSR(l_prev_task_id);
2029 FETCH TASK_INFO_CSR INTO l_prev_task_rec;
2030 CLOSE TASK_INFO_CSR;
2031 --dbms_output.put_line('HELLO: ' || l_prev_task_rec.wbs_number);
2032 l_str1 := substr(l_prev_task_rec.wbs_number, length(l_wbs_number) + 2);
2033 l_str1 := to_char(to_number(l_str1 + 1));
2034 l_loop_wbs_number := l_wbs_number || '.' || l_str1;
2035 end if;
2036 CLOSE GET_PREV_PEER_TASK_CSR;
2037
2038 else
2039
2040 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
2041 if(instr(l_str1, '.') <> 0) then
2042 l_str2 := substr(l_str1, instr(l_str1, '.'));
2043 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
2044 l_str1 := to_char(to_number(l_str1) + 1);
2045
2046 l_loop_wbs_number := l_mask || '.' || l_str1 || l_str2;
2047 else
2048 l_str1 := to_char(to_number(l_str1) + 1);
2049 l_loop_wbs_number := l_mask || '.' || l_str1;
2050 end if;
2051 end if;
2052
2053 end if;
2054
2055 -- Update the WBS number
2056 UPDATE PA_PROJ_ELEMENT_VERSIONS
2057 SET wbs_number = l_loop_wbs_number
2058 WHERE element_version_id = l_update_task_rec.task_id;
2059 END LOOP;
2060
2061 if(l_mask = 'NONE') then
2062 CLOSE UPDATE_TASKS_CSR;
2063 else
2064 CLOSE UPDATE_MASKED_TASKS_CSR;
2065 end if;
2066
2067 elsif(p_action = 'DELETE') then
2068 if(p_parent_task_id = p_parent_structure_ver_id ) then
2069 -- Deleted task is a top task
2070 --dbms_output.put_line('Is parent task');
2071 l_mask := 'NONE';
2072 else
2073 -- Deleted task is not a top task
2074 OPEN TASK_INFO_CSR(p_parent_task_id);
2075 FETCH TASK_INFO_CSR INTO l_parent_task_rec;
2076 CLOSE TASK_INFO_CSR;
2077
2078 l_mask := l_parent_task_rec.wbs_number;
2079 end if;
2080
2081 -- Loop through tasks that have a greater display seq than the current
2082 -- and begins with the same mask (in the same branch)
2083 if(l_mask = 'NONE') then
2084 OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
2085 else
2086 OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
2087 end if;
2088
2089 --dbms_output.put_line('L_MASK: '||l_mask);
2090 LOOP
2091 if(l_mask = 'NONE') then
2092 --dbms_output.put_line('IN LOOP');
2093 FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
2094 EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
2095
2096 ----dbms_output.put_line('L_UPDATE_TASK_REC.WBS_NUMBER: '|| l_update_task_rec.wbs_number);
2097
2098 if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
2099 l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
2100 l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
2101 l_str1 := to_char(to_number(l_str1) - 1);
2102
2103 l_wbs_number := l_str1 || l_str2;
2104 else
2105 l_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) - 1);
2106 end if;
2107 else
2108 FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
2109 EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
2110
2111 l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
2112 if(instr(l_str1, '.') <> 0) then
2113 l_str2 := substr(l_str1, instr(l_str1, '.'));
2114 l_str1 := substr(l_str1, 1, instr(l_str1, '.') - 1);
2115 l_str1 := to_char(to_number(l_str1) - 1);
2116
2117 l_wbs_number := l_mask || '.' || l_str1 || l_str2;
2118 else
2119 l_str1 := to_char(to_number(l_str1) - 1);
2120 l_wbs_number := l_mask || '.' || l_str1;
2121 end if;
2122 end if;
2123
2124 -- Update the WBS number
2125 UPDATE PA_PROJ_ELEMENT_VERSIONS
2126 SET wbs_number = l_wbs_number
2127 WHERE element_version_id = l_update_task_rec.task_id;
2128 END LOOP;
2129
2130 if(l_mask = 'NONE') then
2131 CLOSE UPDATE_TASKS_CSR;
2132 else
2133 CLOSE UPDATE_MASKED_TASKS_CSR;
2134 end if;
2135 end if;
2136
2137 x_return_status := 'S';
2138
2139 if(p_commit = 'Y') then
2140 commit;
2141 end if;
2142 EXCEPTION
2143 when API_ERROR then
2144 if p_commit = 'Y' then
2145 rollback to update_wbs_numbers;
2146 end if;
2147
2148 x_return_status := 'E'; -- 4537865
2149 WHEN OTHERS THEN
2150 if p_commit = 'Y' then
2151 rollback to update_wbs_numbers;
2152 end if;
2153
2154 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_TASK_PUB1',
2155 p_procedure_name => 'UPDATE_WBS_NUMBERS',
2156 p_error_text => SUBSTRB(SQLERRM,1,240));
2157 x_return_status := 'E';
2158 END UPDATE_WBS_NUMBERS;
2159
2160
2161 FUNCTION task_exists_in_struc_ver(
2162 p_structure_version_id NUMBER,
2163 p_task_version_id NUMBER ) RETURN VARCHAR2 IS
2164
2165 CURSOR cur_ppev
2166 IS
2167 SELECT 'x'
2168 FROM pa_proj_element_versions
2169 WHERE element_version_id = p_task_version_id
2170 AND parent_structure_version_id = p_structure_version_id;
2171 l_dummy_char VARCHAR2(1);
2172 BEGIN
2173 OPEN cur_ppev;
2174 FETCH cur_ppev INTO l_dummy_char;
2175 IF cur_ppev%FOUND
2176 THEN
2177 CLOSE cur_ppev;
2178 RETURN 'Y';
2179 ELSE
2180 CLOSE cur_ppev;
2181 RETURN 'N';
2182 END IF;
2183
2184 END task_exists_in_struc_ver;
2185
2186 FUNCTION GET_LINKED_TASK_VERSION_ID(
2187 p_cur_element_id NUMBER ,
2188 p_cur_element_version_id NUMBER
2189 ) RETURN NUMBER IS
2190
2191 CURSOR cur_obj_rel
2192 IS
2193 SELECT object_id_to1
2194 FROM pa_object_relationships
2195 WHERE object_id_from1 = p_cur_element_version_id
2196 AND relationship_type = 'L';
2197 l_linked_task_ver_id NUMBER;
2198 BEGIN
2199 --A linking task can only link one task.
2200
2201 IF LINK_FLAG( p_cur_element_id ) = 'Y'
2202 THEN
2203 OPEN cur_obj_rel;
2204 FETCH cur_obj_rel INTO l_linked_task_ver_id;
2205 CLOSE cur_obj_rel;
2206 RETURN l_linked_task_ver_id;
2207 ELSE
2208 RETURN p_cur_element_version_id;
2209 END IF;
2210
2211 END GET_LINKED_TASK_VERSION_ID;
2212
2213 FUNCTION LINK_FLAG( p_element_id NUMBER ) RETURN VARCHAR2 IS
2214 CURSOR cur_proj_elements
2215 IS
2216 SELECT link_task_flag
2217 FROM pa_proj_elements
2218 WHERE proj_element_id = p_element_id;
2219
2220 l_link_task_flag VARCHAR2(1) :='N'; --bug 4180390
2221 BEGIN
2222
2223 /* comenting the code for bug 4180390
2224 OPEN cur_proj_elements;
2225 FETCH cur_proj_elements INTO l_link_task_flag;
2226 CLOSE cur_proj_elements;
2227 */
2228
2229 RETURN l_link_task_flag;
2230
2231 END LINK_FLAG;
2232
2233
2234 -- API name : CHECK_TASK_IN_STRUCTURE
2235 -- Type : Utils API
2236 -- Pre-reqs : None
2237 -- Return Value : Y if task is in structure; N for task in
2238 -- different struture.
2239 --
2240 -- Parameters
2241 -- p_structure_version_id IN NUMBER
2242 -- p_task_version_id IN NUMBER
2243 --
2244 -- History
2245 --
2246 -- 09-JAN-02 HSIU -Created
2247 --
2248 FUNCTION CHECK_TASK_IN_STRUCTURE(p_structure_version_id NUMBER,
2249 p_task_version_id NUMBER)
2250 RETURN VARCHAR2 IS
2251 CURSOR c1 IS
2252 select '1'
2253 from pa_proj_element_versions
2254 where p_task_version_id = element_version_id
2255 and p_structure_version_id = parent_structure_version_id;
2256
2257 l_dummy VARCHAR2(1);
2258 BEGIN
2259 OPEN c1;
2260 FETCH c1 INTO l_dummy;
2261 IF (c1%NOTFOUND) THEN
2262 CLOSE c1;
2263 return 'N';
2264 END IF;
2265 CLOSE c1;
2266 return 'Y';
2267 END CHECK_TASK_IN_STRUCTURE;
2268
2269
2270 FUNCTION GET_DISPLAY_PARENT_VERSION_ID(p_element_version_id NUMBER,
2271 p_parent_element_version_id NUMBER,
2272 p_relationship_type VARCHAR2,
2273 p_link_task_flag VARCHAR2)
2274 RETURN NUMBER IS
2275 cursor get_display_parent_id IS
2276 select object_id_from1
2277 from pa_object_relationships
2278 where relationship_type = 'S'
2279 and object_id_to1 = p_parent_element_version_id;
2280 l_display_parent_version_id NUMBER;
2281 BEGIN
2282 IF (p_relationship_type = 'L') THEN
2283 --return parent of the parent element version
2284 OPEN get_display_parent_id;
2285 FETCH get_display_parent_id into l_display_parent_version_id;
2286 CLOSE get_display_parent_id;
2287 return l_display_parent_version_id;
2288 ELSIF (p_link_task_flag = 'Y') THEN
2289 return to_number(NULL);
2290 END IF;
2291 --a normal task. Return its current parent
2292 return p_parent_element_version_id;
2293
2294 END GET_DISPLAY_PARENT_VERSION_ID;
2295
2296
2297 FUNCTION IS_ACTIVE_TASK(p_element_version_id NUMBER,
2298 p_object_type VARCHAR2)
2299 RETURN VARCHAR2
2300 IS
2301
2302 -- CURSOR c1 IS
2303 -- select 1
2304 -- from pa_percent_completes ppc,
2305 -- pa_proj_element_versions pev
2306 -- where pev.element_version_id = p_element_version_id
2307 -- and pev.project_id = ppc.project_id
2308 -- and pev.proj_element_id = ppc.task_id
2309 -- and ppc.submitted_flag = 'Y'
2310 -- and ppc.current_flag = 'Y'
2311 -- and ppc.actual_start_date IS NOT NULL
2312 -- and ppc.actual_finish_date IS NULL;
2313 -- l_dummy NUMBER;
2314 BEGIN
2315 -- OPEN c1;
2316 -- FETCH c1 into l_dummy;
2317 -- IF c1%FOUND THEN
2318 -- CLOSE c1;
2319 -- return 'Y';
2320 -- END IF;
2321 -- CLOSE c1;
2322 -- return 'N';
2323 return 'Y';
2324 END IS_ACTIVE_TASK;
2325
2326 FUNCTION Get_DAYS_TO_START(p_element_version_id NUMBER,
2327 p_object_type VARCHAR2)
2328 RETURN NUMBER
2329 IS
2330 CURSOR c1 IS
2331 select scheduled_start_date
2332 from pa_proj_elem_ver_schedule sch,
2333 pa_proj_element_versions ev
2334 where p_element_version_id = ev.element_version_id
2335 and ev.element_version_id = sch.element_version_id
2336 and ev.project_id = sch.project_id;
2337 l_date DATE;
2338 BEGIN
2339 IF (p_object_type = 'PA_STRUCTURES') THEN
2340 RETURN to_number(NULL);
2341 END IF;
2342 OPEN c1;
2343 FETCH c1 into l_date;
2344 IF c1%NOTFOUND THEN
2345 CLOSE c1;
2346 return to_number(NULL);
2347 END IF;
2348 CLOSE c1;
2349 return TRUNC(l_date) - TRUNC(SYSDATE);
2350 END Get_DAYS_TO_START;
2351
2352 FUNCTION Get_DAYS_TO_FINISH(p_element_version_id NUMBER,
2353 p_object_type VARCHAR2)
2354 RETURN NUMBER
2355 IS
2356 CURSOR c1 IS
2357 select scheduled_finish_date
2358 from pa_proj_elem_ver_schedule sch,
2359 pa_proj_element_versions ev
2360 where p_element_version_id = ev.element_version_id
2361 and ev.element_version_id = sch.element_version_id
2362 and ev.project_id = sch.project_id;
2363 l_date DATE;
2364 BEGIN
2365 IF (p_object_type = 'PA_STRUCTURES') THEN
2366 RETURN to_number(NULL);
2367 END IF;
2368 OPEN c1;
2369 FETCH c1 into l_date;
2370 IF c1%NOTFOUND THEN
2371 CLOSE c1;
2372 return to_number(NULL);
2373 END IF;
2374 CLOSE c1;
2375 return TRUNC(l_date) - TRUNC(sysdate);
2376 END Get_DAYS_TO_FINISH;
2377
2378 FUNCTION GET_PREV_SCH_START_DATE(p_element_version_id NUMBER,
2379 p_parent_structure_version_id NUMBER)
2380 RETURN DATE
2381 IS
2382 CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2383 select sch.scheduled_start_date
2384 from pa_proj_elem_ver_schedule sch,
2385 pa_proj_element_versions pev,
2386 pa_proj_element_versions pev2
2387 where pev.project_id = c_project_id
2388 and pev.parent_structure_version_id = c_structure_version_id
2389 and pev.element_version_id = sch.element_version_id
2390 and pev.project_id = sch.project_id
2391 and pev.proj_element_id = pev2.proj_element_id
2392 and pev.project_id = pev2.project_id
2393 and pev2.element_version_id = p_element_version_id;
2394
2395 CURSOR c2 IS
2396 select str.project_id, str.element_version_id
2397 from pa_proj_elem_ver_structure str,
2398 pa_proj_element_versions pev
2399 where pev.element_version_id = p_parent_structure_version_id
2400 and pev.project_id = str.project_id
2401 and pev.proj_element_id = str.proj_element_id
2402 and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
2403 l_project_id NUMBER;
2404 l_structure_version_id NUMBER;
2405 l_date DATE;
2406 BEGIN
2407 OPEN c2;
2408 FETCH c2 into l_project_id, l_structure_version_id;
2409 IF c2%NOTFOUND THEN
2410 --no published version
2411 CLOSE c2;
2412 return to_date(NULL);
2413 END IF;
2414 CLOSE c2;
2415
2416 OPEN c1(l_project_id, l_structure_version_id);
2417 FETCH c1 into l_date;
2418 CLOSE c1;
2419
2420 return l_date;
2421
2422 END GET_PREV_SCH_START_DATE;
2423
2424 FUNCTION GET_PREV_SCH_FINISH_DATE(p_element_version_id NUMBER,
2425 p_parent_structure_version_id NUMBER)
2426 RETURN DATE
2427 IS
2428 CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2429 select sch.scheduled_finish_date
2430 from pa_proj_elem_ver_schedule sch,
2431 pa_proj_element_versions pev,
2432 pa_proj_element_versions pev2
2433 where pev.project_id = c_project_id
2434 and pev.parent_structure_version_id = c_structure_version_id
2435 and pev.element_version_id = sch.element_version_id
2436 and pev.project_id = sch.project_id
2437 and pev.proj_element_id = pev2.proj_element_id
2438 and pev.project_id = pev2.project_id
2439 and pev2.element_version_id = p_element_version_id;
2440
2441 CURSOR c2 IS
2442 select str.project_id, str.element_version_id
2443 from pa_proj_elem_ver_structure str,
2444 pa_proj_element_versions pev
2445 where pev.element_version_id = p_parent_structure_version_id
2446 and pev.project_id = str.project_id
2447 and pev.proj_element_id = str.proj_element_id
2448 and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
2449 l_project_id NUMBER;
2450 l_structure_version_id NUMBER;
2451 l_date DATE;
2452 BEGIN
2453 OPEN c2;
2454 FETCH c2 into l_project_id, l_structure_version_id;
2455 IF c2%NOTFOUND THEN
2456 --no published version
2457 CLOSE c2;
2458 return to_date(NULL);
2459 END IF;
2460 CLOSE c2;
2461
2462 OPEN c1(l_project_id, l_structure_version_id);
2463 FETCH c1 into l_date;
2464 CLOSE c1;
2465
2466 return l_date;
2467
2468 END GET_PREV_SCH_FINISH_DATE;
2469
2470 FUNCTION CHECK_IS_FINANCIAL_TASK(p_proj_element_id NUMBER)
2471 RETURN VARCHAR2
2472 IS
2473 cursor c1 IS
2474 SELECT 1
2475 FROM PA_TASKS
2476 WHERE task_id = p_proj_element_id;
2477 l_dummy NUMBER;
2478 BEGIN
2479 OPEN c1;
2480 FETCH c1 into l_dummy;
2481 IF c1%NOTFOUND THEN
2482 CLOSE c1;
2483 return 'N';
2484 ELSE
2485 CLOSE c1;
2486 return 'Y';
2487 END IF;
2488 END CHECK_IS_FINANCIAL_TASK;
2489
2490 FUNCTION CONVERT_HR_TO_DAYS(p_hour NUMBER)
2491 RETURN NUMBER
2492 IS
2493
2494 l_fte_day NUMBER := 0;
2495
2496 cursor get_fte_days_csr
2497 IS
2498 SELECT fte_day
2499 FROM pa_implementations;
2500
2501 BEGIN
2502
2503 --commented out for bug 3612309
2504 -- OPEN get_fte_days_csr;
2505 -- FETCH get_fte_days_csr INTO l_fte_day;
2506 -- IF get_fte_days_csr%NOTFOUND then
2507 -- return 0;
2508 -- end if;
2509 -- CLOSE get_fte_days_csr;
2510
2511 -- if l_fte_day is null or l_fte_day = 0 then
2512 -- l_fte_day := 8;
2513 -- end if;
2514
2515 --dbms_output.put_line('fte_days = '||l_fte_day);
2516
2517 -- return round(p_hour/l_fte_day,2);
2518 return p_hour;
2519
2520 END CONVERT_HR_TO_DAYS;
2521
2522
2523 FUNCTION GET_FND_LOOKUP_MEANING(p_lookup_type VARCHAR2,
2524 p_lookup_code VARCHAR2)
2525 RETURN VARCHAR2
2526 IS
2527 /*
2528 cursor c1 is
2529 select meaning
2530 from fnd_lookups
2531 where lookup_type = p_lookup_type
2532 and lookup_code = p_lookup_code;
2533 l_dummy varchar2(80);
2534 BEGIN
2535 -- Bug Fix 5611871
2536 IF p_lookup_code IS NULL THEN
2537 l_dummy := NULL;
2538 RETURN l_dummy;
2539 END IF;
2540 -- End of Bug Fix 5611871
2541
2542 open c1;
2543 FETCH c1 into l_dummy;
2544 If c1%NOTFOUND THEN
2545 l_dummy := NULL;
2546 END IF;
2547 close c1;
2548 return l_dummy;
2549 */
2550
2551 -- Bug 6156686
2552 cursor c1 is
2553 select meaning
2554 from fnd_lookups
2555 where lookup_type = p_lookup_type
2556 and lookup_code = p_lookup_code;
2557 l_dummy varchar2(80);
2558 l_index varchar2(100);
2559 BEGIN
2560 l_index := p_lookup_type || '*'||p_lookup_code;
2561 IF l_fndlkp_cache_tbl.EXISTS(l_index) THEN
2562 l_dummy := l_fndlkp_cache_tbl(l_index);
2563 ELSE
2564 open c1;
2565 FETCH c1 into l_dummy;
2566 If c1%NOTFOUND THEN
2567 l_dummy := NULL;
2568 END IF;
2569 close c1;
2570 l_fndlkp_cache_tbl(l_index) := l_dummy;
2571
2572 END IF;
2573 return l_dummy;
2574
2575 END GET_FND_LOOKUP_MEANING;
2576
2577
2578 FUNCTION GET_PA_LOOKUP_MEANING(p_lookup_type VARCHAR2,
2579 p_lookup_code VARCHAR2)
2580 RETURN VARCHAR2
2581 IS
2582 cursor c1 is
2583 select meaning
2584 from pa_lookups
2585 where lookup_type = p_lookup_type
2586 and lookup_code = p_lookup_code;
2587 l_dummy varchar2(80);
2588 l_index varchar2(100); -- Bug 6156686
2589 BEGIN
2590 -- Bug 6156686
2591 l_index := p_lookup_type || '*'||p_lookup_code;
2592 IF l_lookup_cache_tbl.EXISTS(l_index) THEN
2593 l_dummy := l_lookup_cache_tbl(l_index);
2594 ELSE
2595 open c1;
2596 FETCH c1 into l_dummy;
2597 If c1%NOTFOUND THEN
2598 l_dummy := NULL;
2599 END IF;
2600 close c1;
2601 l_lookup_cache_tbl(l_index) := l_dummy;
2602
2603 END IF;
2604
2605 return l_dummy;
2606 END GET_PA_LOOKUP_MEANING;
2607
2608 -- API name : GET_DEFAULT_TASK_TYPE_ID
2609 -- Type : Utils API
2610 -- Pre-reqs : None
2611 -- Return Value : Default task type_id
2612 --
2613 -- Parameters
2614 --
2615 -- History
2616 --
2617 -- 26-JUL-02 HSIU -Created
2618 --
2619 FUNCTION GET_DEFAULT_TASK_TYPE_ID
2620 return NUMBER
2621 IS
2622 BEGIN
2623 return 1;
2624 END GET_DEFAULT_TASK_TYPE_ID;
2625
2626 FUNCTION IS_TASK_TYPE_USED(p_task_type_id IN NUMBER)
2627 return VARCHAR2
2628 IS
2629
2630 /* Bug2680486 -- Performance changes -- Commented the following cursor query and restructured it. */
2631 /* cursor c1 IS
2632 select 'Y'
2633 from PA_PROJ_ELEMENTS
2634 where type_id = p_task_type_id;
2635 */
2636
2637 cursor c1 IS
2638 select 'Y'
2639 from dual
2640 where exists (
2641 select 'xyz'
2642 from PA_PROJ_ELEMENTS
2643 where type_id = p_task_type_id
2644 AND project_id > -1
2645 AND object_type = 'PA_TASKS'
2646 );
2647
2648 l_ret_val VARCHAR2(1);
2649 BEGIN
2650 OPEN c1;
2651 FETCH c1 into l_ret_val;
2652 IF c1%NOTFOUND THEN
2653 l_ret_val := 'N';
2654 END IF;
2655 CLOSE c1;
2656 return l_ret_val;
2657 END IS_TASK_TYPE_USED;
2658
2659 -- API name : GET_LATEST_FIN_PUB_TASK_VER_ID
2660 -- Type : Utils API
2661 -- Pre-reqs : None
2662 -- Return Value : Task version id of the latest financial
2663 -- published task
2664 --
2665 -- Parameters
2666 -- p_project_id IN NUMBER
2667 -- p_task_id IN NUMBER
2668 --
2669 -- History
2670 --
2671 -- 26-JUL-02 HSIU -Created
2672 --
2673 FUNCTION GET_LATEST_FIN_PUB_TASK_VER_ID(
2674 p_project_id IN NUMBER
2675 ,p_task_id IN NUMBER
2676 ) return NUMBER
2677 IS
2678 CURSOR c1(c_structure_version_id NUMBER) IS
2679 select ppev.element_version_id
2680 from pa_proj_element_versions ppev
2681 where parent_structure_version_id = c_structure_version_id
2682 and project_id = p_project_id
2683 and proj_element_id = p_task_id;
2684 l_structure_version_id NUMBER;
2685 l_task_version_id NUMBER;
2686 BEGIN
2687 l_structure_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(p_project_id);
2688
2689 OPEN c1(l_structure_version_id);
2690 FETCH c1 into l_task_version_id;
2691 CLOSE c1;
2692 return l_task_version_id;
2693 END GET_LATEST_FIN_PUB_TASK_VER_ID;
2694
2695
2696 -- API name : CHECK_MODIFY_OK_FOR_STATUS
2697 -- Type : Utils API
2698 -- Pre-reqs : None
2699 -- Return Value : Check if this task can be modified with its
2700 -- current status. Y can be modified, N cannot.
2701 --
2702 -- Parameters
2703 -- p_project_id IN NUMBER
2704 -- p_task_id IN NUMBER
2705 --
2706 -- History
2707 --
2708 -- 26-JUL-02 HSIU -Created
2709 --
2710 FUNCTION CHECK_MODIFY_OK_FOR_STATUS(
2711 p_project_id IN NUMBER
2712 ,p_task_id IN NUMBER
2713 ) return VARCHAR2
2714 IS
2715 --bug 2863836: modified cursor to refer to system status
2716 cursor c1 is
2717 select b.project_system_status_code
2718 from pa_proj_elements a, pa_project_statuses b
2719 where a.proj_element_id = p_task_id
2720 and a.status_code = b.project_status_code
2721 and b.status_type = 'TASK';
2722 l_dummy pa_proj_elements.status_code%TYPE;
2723 l_retval VARCHAR2(1);
2724 BEGIN
2725 open c1;
2726 FETCH c1 into l_dummy;
2727 CLOSE c1;
2728 IF (l_dummy = 'ON_HOLD' OR l_dummy = 'CANCELLED') THEN
2729 return 'N';
2730 END IF;
2731 return 'Y';
2732 END CHECK_MODIFY_OK_FOR_STATUS;
2733
2734
2735 -- API name : GET_DISPLAY_SEQUENCE
2736 -- Type : FUNCTION
2737 -- Pre-reqs : N/A
2738 -- Return Value : The display sequence for a given task.
2739 --
2740 -- Parameters
2741 -- p_task_id IN NUMBER
2742 --
2743 -- History
2744 --
2745 -- 16-OCT-02 XXLU -Created
2746 --
2747 FUNCTION GET_DISPLAY_SEQUENCE (
2748 p_task_id IN NUMBER
2749 ) RETURN NUMBER
2750 IS
2751
2752 l_element_version_id pa_proj_element_versions.element_version_id%TYPE;
2753
2754 CURSOR c1(p_task_id IN NUMBER) IS
2755 SELECT project_id
2756 FROM pa_tasks
2757 WHERE task_id = p_task_id;
2758
2759 v_c1 c1%ROWTYPE;
2760
2761 CURSOR c2 (p_proj_element_id IN NUMBER) IS
2762 SELECT element_version_id
2763 FROM pa_proj_element_versions
2764 WHERE proj_element_id = p_proj_element_id;
2765
2766 v_c2 c2%ROWTYPE;
2767
2768 CURSOR c3 (p_element_version_id IN NUMBER) IS
2769 SELECT display_sequence
2770 FROM pa_proj_element_versions
2771 WHERE element_version_id = p_element_version_id;
2772
2773 v_c3 c3%ROWTYPE;
2774
2775
2776 BEGIN
2777
2778 OPEN c1 (p_task_id);
2779 FETCH c1 INTO v_c1;
2780 CLOSE c1;
2781
2782 l_element_version_id := PA_PROJ_ELEMENTS_UTILS.GET_LATEST_FIN_PUB_TASK_VER_ID
2783 (p_project_id => v_c1.project_id,
2784 p_task_id => p_task_id);
2785
2786 IF l_element_version_id IS NULL THEN
2787 OPEN c2 (p_task_id);
2788 FETCH c2 INTO v_c2;
2789 CLOSE c2;
2790
2791 l_element_version_id := v_c2.element_version_id;
2792
2793 END IF;
2794
2795 OPEN c3(l_element_version_id);
2796 FETCH c3 INTO v_c3;
2797 CLOSE c3;
2798
2799 RETURN(v_c3.display_sequence);
2800
2801 END GET_DISPLAY_SEQUENCE;
2802
2803 procedure Check_Del_all_task_Ver_Ok
2804 (
2805 p_project_id IN NUMBER
2806 ,p_task_version_id IN NUMBER
2807 ,p_parent_structure_ver_id IN NUMBER
2808 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2809 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
2810 )
2811 IS
2812 CURSOR c1 IS
2813 select object_Id_to1
2814 from pa_object_relationships
2815 where object_type_to = 'PA_TASKS'
2816 and relationship_type = 'S'
2817 start with object_id_from1 = p_task_version_id
2818 and object_type_from = 'PA_TASKS'
2819 and relationship_type = 'S'
2820 connect by prior object_id_to1 = object_id_from1
2821 and prior object_type_to = object_type_from
2822 and relationship_type = prior relationship_type; --Bug 3792616
2823 l_task_ver_id NUMBER;
2824
2825 l_task_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ; -- 4201927 for performance issue
2826 BEGIN
2827
2828 PA_PROJ_ELEMENTS_UTILS.CHECK_DELETE_TASK_VER_OK(
2829 p_project_id => p_project_id
2830 ,p_task_version_id => p_task_version_id
2831 ,p_parent_structure_ver_id => p_parent_structure_ver_id
2832 ,x_return_status => x_return_status
2833 ,x_error_message_code => x_error_message_code
2834 );
2835 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2836 raise FND_API.G_EXC_ERROR;
2837 END IF;
2838
2839
2840 -- 4201927 commented below code for performance issue
2841 /*
2842 OPEN c1;
2843 LOOP
2844 FETCH c1 into l_task_ver_id;
2845 EXIT WHEN c1%NOTFOUND;
2846 PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2847 p_project_id => p_project_id
2848 ,p_task_version_id => l_task_ver_id
2849 ,p_parent_structure_ver_id => p_parent_structure_ver_id
2850 ,x_return_status => x_return_status
2851 ,x_error_message_code => x_error_message_code
2852 );
2853 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2854 raise FND_API.G_EXC_ERROR;
2855 END IF;
2856 END LOOP;
2857 CLOSE c1;
2858 */
2859
2860 -- using bulk collect approach and then iterating through table
2861
2862 OPEN c1 ;
2863 FETCH c1 BULK COLLECT INTO l_task_ver_id_tbl;
2864 CLOSE c1 ;
2865
2866 IF nvl(l_task_ver_id_tbl.LAST,0) > 0 THEN
2867 FOR i in reverse l_task_ver_id_tbl.FIRST..l_task_ver_id_tbl.LAST LOOP
2868
2869 PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2870 p_project_id => p_project_id
2871 ,p_task_version_id => l_task_ver_id_tbl(i)
2872 ,p_parent_structure_ver_id => p_parent_structure_ver_id
2873 ,x_return_status => x_return_status
2874 ,x_error_message_code => x_error_message_code
2875 );
2876 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2877 raise FND_API.G_EXC_ERROR;
2878 END IF;
2879
2880 END LOOP;
2881 END IF;
2882
2883 -- 4201927 end
2884
2885 x_return_status := FND_API.G_RET_STS_SUCCESS;
2886
2887 EXCEPTION
2888 WHEN FND_API.G_EXC_ERROR THEN
2889 x_return_status := FND_API.G_RET_STS_ERROR;
2890 -- 4537865 NOT RESETTING x_error_message_code AS IT WILL reach this point only after x_error_message_code is set
2891 WHEN OTHERS THEN
2892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2893 -- 4537865
2894 x_error_message_code := SQLCODE ;
2895 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
2896 p_procedure_name => 'Check_Del_all_task_Ver_Ok
2897 ');
2898 RAISE;
2899 END Check_Del_all_task_Ver_Ok;
2900
2901 procedure Check_create_subtask_ok
2902 ( p_parent_task_ver_id IN NUMBER
2903 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2904 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
2905 )
2906 IS
2907 CURSOR get_status IS
2908 select ppe.project_id, ppe.proj_element_id
2909 from pa_project_statuses pps,
2910 pa_proj_elements ppe,
2911 pa_proj_element_versions ppev
2912 where ppev.element_version_id = p_parent_task_ver_id
2913 and ppe.project_id = ppev.project_id
2914 and ppe.proj_element_id = ppev.proj_element_id
2915 and ppe.status_code = pps.project_status_code
2916 and pps.project_system_status_code IN ('ON_HOLD', 'CANCELLED')
2917 and pps.status_type = 'TASK';
2918 l_project_id NUMBER;
2919 l_proj_element_id NUMBER;
2920
2921 CURSOR get_task_info IS
2922 select ppev.project_id, ppev.proj_element_id
2923 from pa_proj_element_versions ppev
2924 where ppev.element_version_id = p_parent_task_ver_id;
2925
2926 CURSOR get_schedule_info IS
2927 select 1
2928 from pa_proj_elem_ver_schedule ppvsch,
2929 pa_proj_element_versions ppev
2930 where ppev.element_version_id = p_parent_task_ver_id
2931 and ppev.project_id = ppvsch.project_id
2932 and ppev.proj_element_id = ppvsch.proj_element_id
2933 and ppev.element_version_id = ppvsch.element_version_id
2934 and ( ppvsch.wq_planned_quantity IS NOT NULL AND ppvsch.wq_planned_quantity <> 0 );
2935 l_dummy NUMBER;
2936
2937 l_err_code NUMBER:= 0;
2938 l_err_stack VARCHAR2(630);
2939 l_err_stage VARCHAR2(80);
2940
2941 l_return_status VARCHAR2(1);
2942 l_msg_data VARCHAR2(2000);
2943 l_msg_count NUMBER;
2944 BEGIN
2945 OPEN get_status;
2946 FETCH get_status into l_project_id, l_proj_element_id;
2947 IF get_status%NOTFOUND THEN
2948 x_return_status := 'Y';
2949 ELSE
2950 x_return_status := 'N';
2951 x_error_message_code := 'PA_PS_ONHOLD_CANCEL_TK_ERR';
2952 CLOSE get_status;
2953 return;
2954 END IF;
2955 CLOSE get_status;
2956
2957 OPEN get_task_info;
2958 FETCH get_task_info into l_project_id, l_proj_element_id;
2959 CLOSE get_task_info;
2960
2961 --hsiu: bug 2674107
2962 --if workplan, check if ok to create subtask.
2963 OPEN get_schedule_info;
2964 FETCH get_schedule_info into l_dummy;
2965 IF (get_schedule_info%FOUND) THEN
2966 x_return_status := 'N';
2967 x_error_message_code := 'PA_PS_PARENT_TK_PWQ_ERR';
2968 return;
2969 END IF;
2970 CLOSE get_schedule_info;
2971
2972 -- Begin fix for Bug # 4266540.
2973
2974 l_return_status := pa_relationship_utils.check_task_has_sub_proj(l_project_id
2975 , l_proj_element_id
2976 , p_parent_task_ver_id);
2977
2978 if (l_return_status = 'Y') then
2979
2980 x_return_status := 'N';
2981 x_error_message_code := 'PA_PS_TASK_HAS_SUB_PROJ';
2982 return;
2983
2984 end if;
2985
2986 l_return_status := null;
2987
2988 -- End fix for Bug # 4266540.
2989
2990 --if financial, check if ok to create subtask.
2991 --Bug 5988335 Adding condition for partial share project to skip financial validation
2992 --when a new subtask is getting created in workplan.
2993 If (PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id) = 'Y') AND
2994 (PA_PROJ_TASK_STRUC_PUB.GET_SHARE_TYPE(l_project_id) <> 'SHARE_PARTIAL') --Bug 5988335
2995 THEN
2996 PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK(x_task_id => l_proj_element_id,
2997 x_err_code => l_err_code,
2998 x_err_stack => l_err_stack,
2999 x_err_stage => l_err_stage
3000 );
3001 IF (l_err_code <> 0) THEN
3002 x_return_status := 'N';
3003 x_error_message_code := substrb(l_err_stage,1,30); -- 4537865 changed substr to substrb
3004 return;
3005 ELSE
3006 x_return_status := 'Y';
3007 return;
3008 END IF;
3009 ELSE
3010 --bug 3055708 (for financial or shared str it will be taken
3011 --care in PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK)
3012 PA_TASK_PUB1.Check_Task_Has_Association(
3013 p_task_id => l_proj_element_id
3014 ,x_return_status => l_return_status
3015 ,x_msg_count => l_msg_count
3016 ,x_msg_data => l_msg_data
3017
3018 );
3019
3020 IF (l_return_status <> 'S') Then
3021 x_return_status := 'N';
3022 x_error_message_code := l_msg_data;
3023 return;
3024 END IF;
3025 --end bug 3055708
3026
3027 --bug 3305199: cannot create subtask if dep exists in parent when
3028 --option for no dep in summary task is Y
3029 IF (PA_PROJECT_STRUCTURE_UTILS.check_dep_on_summary_tk_ok(l_project_id) = 'N') THEN
3030 IF ('Y' = PA_RELATIONSHIP_UTILS.CHECK_DEP_EXISTS(p_parent_task_ver_id)) THEN
3031 x_return_status := 'N';
3032 x_error_message_code := 'PA_DEP_ON_SUMM_TSK';
3033 return;
3034 END IF;
3035 END IF;
3036 END IF;
3037 --bug 3947726
3038 --do not allow sub-tasks creation if the lowest level task has progress.
3039 --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.
3040 IF pa_proj_task_struc_pub.wp_str_exists(l_project_id) = 'Y'
3041 AND pa_task_assignment_utils.get_task_level_record( l_project_id, p_parent_task_ver_id ) IS NOT NULL
3042 AND PA_PROGRESS_UTILS.check_object_has_prog(
3043 p_project_id => l_project_id
3044 ,p_proj_element_id => l_proj_element_id
3045 ,p_object_id => l_proj_element_id
3046 ,p_object_type => 'PA_TASKS'
3047 ,p_structure_type => 'WORKPLAN'
3048 ) = 'Y'
3049 AND PA_PROGRESS_UTILS.check_ta_has_prog(
3050 p_project_id => l_project_id
3051 ,p_proj_element_id => l_proj_element_id
3052 ,p_element_ver_id => p_parent_task_ver_id
3053 ) = 'Y' --Added for 7015986
3054 THEN
3055 x_return_status := 'N';
3056 x_error_message_code := 'PA_PS_TASK_HAS_PROG_ADD';
3057 return;
3058 END IF;
3059 --end bug 3947726
3060 -- 4537865
3061 EXCEPTION
3062 WHEN OTHERS THEN
3063 x_return_status := 'N';
3064 x_error_message_code := SQLCODE ;
3065 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3066 p_procedure_name => 'Check_create_subtask_ok',
3067 p_error_text => SUBSTRB(SQLERRM,1,240));
3068 RAISE ;
3069 END Check_create_subtask_ok;
3070
3071
3072 FUNCTION Check_task_stus_action_allowed
3073 (p_task_status_code IN VARCHAR2,
3074 p_action_code IN VARCHAR2 ) return
3075 VARCHAR2
3076 IS
3077 CURSOR l_taskstus_csr IS
3078 SELECT enabled_flag, project_system_status_code
3079 FROM pa_project_status_controls
3080 WHERE project_status_code = p_task_status_code
3081 AND action_code = p_action_code;
3082
3083 l_action_allowed VARCHAR2(1) := 'N';
3084
3085 l_proj_sys_status_code VARCHAR2(30);
3086 BEGIN
3087 OPEN l_taskstus_csr;
3088 FETCH l_taskstus_csr INTO l_action_allowed, l_proj_sys_status_code;
3089 IF l_taskstus_csr%NOTFOUND THEN
3090 CLOSE l_taskstus_csr;
3091 RETURN 'N';
3092 END IF;
3093 CLOSE l_taskstus_csr;
3094
3095 RETURN (NVL(l_action_allowed,'N'));
3096
3097 EXCEPTION
3098 WHEN OTHERS THEN
3099 RETURN 'N';
3100 END Check_task_stus_action_allowed;
3101
3102 -- hyau new apis for lifecycle changes
3103
3104 -- API name : CHECK_ELEMENT_HAS_PHASE
3105 -- Type : FUNCTION
3106 -- Pre-reqs : N/A
3107 -- Return Value : 'Y' if the element has a phase associated with it, else returns 'N'.
3108 --
3109 -- Parameters
3110 -- p_proj_element_id IN NUMBER
3111 --
3112 -- History
3113 --
3114 -- 30-OCT-02 hyau -Created
3115 --
3116 FUNCTION CHECK_ELEMENT_HAS_PHASE (
3117 p_proj_element_id IN NUMBER) RETURN
3118
3119 VARCHAR2
3120 IS
3121 CURSOR l_element_has_phase_csr IS
3122 SELECT 'Y'
3123 FROM pa_proj_elements
3124 WHERE proj_element_id = p_proj_element_id
3125 AND phase_version_id is not null;
3126
3127 l_has_phase VARCHAR2(1) := 'N';
3128
3129 BEGIN
3130 OPEN l_element_has_phase_csr;
3131 FETCH l_element_has_phase_csr INTO l_has_phase;
3132 IF l_element_has_phase_csr%NOTFOUND THEN
3133 CLOSE l_element_has_phase_csr;
3134 RETURN 'N';
3135 END IF;
3136 CLOSE l_element_has_phase_csr;
3137
3138 RETURN (NVL(l_has_phase,'N'));
3139
3140 EXCEPTION
3141 WHEN OTHERS THEN
3142 RETURN 'N';
3143 END CHECK_ELEMENT_HAS_PHASE;
3144
3145
3146 -- API name : IS_TOP_TASK_ACROSS_ALL_VER
3147 -- Type : FUNCTION
3148 -- Pre-reqs : N/A
3149 -- Return Value : 'Y' if the task is a top task across all versions, else returns 'N'.
3150 --
3151 -- Parameters
3152 -- p_proj_element_id IN NUMBER
3153 --
3154 -- History
3155 --
3156 -- 30-OCT-02 hyau -Created
3157 --
3158 FUNCTION IS_TOP_TASK_ACROSS_ALL_VER(
3159 p_proj_element_id IN NUMBER) RETURN
3160
3161 VARCHAR2
3162 IS
3163 CURSOR l_exist_non_top_task_csr IS
3164 select 'N'
3165 from pa_proj_elements ppe,
3166 pa_proj_element_versions ppev
3167 where ppe.proj_element_id = p_proj_element_id
3168 and ppe.proj_element_id = ppev.proj_element_id
3169 and nvl(ppev.wbs_level, 0) <> 1;
3170
3171 l_is_top_task VARCHAR2(1) := 'N';
3172
3173 BEGIN
3174 OPEN l_exist_non_top_task_csr;
3175 FETCH l_exist_non_top_task_csr INTO l_is_top_task;
3176 IF l_exist_non_top_task_csr%NOTFOUND THEN
3177 CLOSE l_exist_non_top_task_csr;
3178 RETURN 'Y';
3179 END IF;
3180 CLOSE l_exist_non_top_task_csr;
3181
3182 RETURN (NVL(l_is_top_task,'N'));
3183
3184 EXCEPTION
3185 WHEN OTHERS THEN
3186 RETURN 'N';
3187 END IS_TOP_TASK_ACROSS_ALL_VER;
3188
3189 -- API name : CHECK_PHASE_IN_USE
3190 -- Type : FUNCTION
3191 -- Pre-reqs : N/A
3192 -- Return Value : 'Y' if the phase is already used by another task in the structure, else returns 'N'.
3193 --
3194 -- Parameters
3195 -- p_task_id NUMBER
3196 -- phase_version_id NUMBER
3197 --
3198 -- History
3199 --
3200 -- 30-OCT-02 hyau -Created
3201 --
3202 FUNCTION CHECK_PHASE_IN_USE(
3203 p_task_id IN NUMBER
3204 ,p_phase_version_id IN NUMBER) RETURN
3205
3206 VARCHAR2
3207 IS
3208
3209 /* Bug 2680486 -- Performance changes -- Added join of project_id in the following cursor*/
3210 CURSOR l_phase_in_use_csr IS
3211 select 'Y'
3212 from pa_proj_elements ppe,
3213 pa_proj_elements ppe2
3214 where ppe.proj_element_id = p_task_id
3215 and ppe.parent_structure_id = ppe2.parent_structure_id
3216 and ppe2.phase_version_id = p_phase_version_id
3217 and ppe2.proj_element_id <> p_task_id
3218 and ppe2.project_id = ppe.project_id;
3219
3220 l_phase_in_use VARCHAR2(1) := 'Y';
3221
3222 BEGIN
3223 OPEN l_phase_in_use_csr;
3224 FETCH l_phase_in_use_csr INTO l_phase_in_use;
3225 IF l_phase_in_use_csr%NOTFOUND THEN
3226 CLOSE l_phase_in_use_csr;
3227 RETURN 'N';
3228 END IF;
3229 CLOSE l_phase_in_use_csr;
3230
3231 RETURN (NVL(l_phase_in_use,'Y'));
3232
3233 EXCEPTION
3234 WHEN OTHERS THEN
3235 RETURN 'N';
3236 END CHECK_PHASE_IN_USE;
3237
3238
3239 -- end hyau new apis for lifecycle changes
3240
3241 PROCEDURE Check_Fin_Task_Published(p_project_id IN NUMBER,
3242 p_task_id IN NUMBER,
3243 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
3244 x_error_message_code OUT NOCOPY VARCHAR2) -- 4537865
3245 IS
3246 CURSOR c1 is
3247 select 1 from
3248 pa_proj_elements a,
3249 pa_proj_element_versions b,
3250 pa_proj_elem_ver_structure c
3251 where a.proj_element_id = p_task_id
3252 and a.project_id = p_project_id
3253 and a.project_id = b.project_id
3254 and a.proj_element_id = b.proj_element_id
3255 and b.project_Id = c.project_id
3256 and b.parent_structure_version_id = c.element_version_id
3257 and c.status_code = 'STRUCTURE_PUBLISHED';
3258
3259 CURSOR c2 IS
3260 select 1 from
3261 pa_tasks a
3262 where a.task_id = p_task_id;
3263
3264 l_dummy NUMBER;
3265
3266 BEGIN
3267 OPEN c1;
3268 FETCH c1 into l_dummy;
3269 IF c1%NOTFOUND THEN
3270 --check if task has financial component
3271 OPEN c2;
3272 FETCH c2 into l_dummy;
3273 IF c2%NOTFOUND THEN
3274 x_return_status := 'N';
3275 x_error_message_code := 'PA_PS_TSK_NOT_PUB_ERR';
3276 ELSE
3277 x_return_status := 'Y';
3278 END IF;
3279 CLOSE c2;
3280 ELSE
3281 x_return_status := 'Y';
3282 END IF;
3283 CLOSE c1;
3284
3285 -- 4537865
3286 EXCEPTION
3287 WHEN OTHERS THEN
3288 x_return_status := 'N' ;
3289 x_error_message_code := SQLCODE ;
3290
3291 -- not included add_exc_msg call because caller of this API doesnt expect it.
3292 RAISE ;
3293 END Check_Fin_Task_Published;
3294
3295 PROCEDURE check_move_task_ok
3296 (
3297 p_task_ver_id IN NUMBER
3298 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3299 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
3300 )
3301 IS
3302 CURSOR get_status IS
3303 select '1'
3304 from pa_project_statuses pps,
3305 pa_proj_elements ppe,
3306 pa_proj_element_versions ppev
3307 where ppev.element_version_id = p_task_ver_id
3308 and ppe.project_id = ppev.project_id
3309 and ppe.proj_element_id = ppev.proj_element_id
3310 and ppe.status_code = pps.project_status_code
3311 and pps.project_system_status_code IN ('ON_HOLD', 'CANCELLED', 'COMPLETED')
3312 and pps.status_type = 'TASK';
3313 l_dummy varchar2(1);
3314 BEGIN
3315 OPEN get_status;
3316 FETCH get_status into l_dummy;
3317 IF get_status%NOTFOUND THEN
3318 x_return_status := 'Y';
3319 ELSE
3320 x_return_status := 'N';
3321 x_error_message_code := 'PA_PS_MOVE_TK_STAT_ERR';
3322 END IF;
3323 CLOSE get_status;
3324 -- 4537865
3325 EXCEPTION
3326 WHEN OTHERS THEN
3327 x_return_status := 'N' ;
3328 x_error_message_code := SQLCODE ;
3329 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3330 p_procedure_name => 'check_move_task_ok',
3331 p_error_text => SUBSTRB(SQLERRM,1,240));
3332 RAISE ;
3333 END check_move_task_ok;
3334
3335
3336 -- API name :
3337 -- Type : PROCEDURE
3338 -- Pre-reqs : N/A
3339 -- Return Value : Sucess, Error, or Unexpected error
3340 --
3341 -- Parameters
3342 -- p_task_id IN NUMBER
3343 -- p_task_version_id IN NUMBER
3344 -- p_new_task_status IN VARCHAR2
3345 -- x_return_status OUT VARCHAR2
3346 -- x_error_message_code OUT VARCHAR2
3347 --
3348 -- History
3349 --
3350 -- 30-OCT-02 hyau -Created
3351 --
3352 PROCEDURE Check_chg_stat_cancel_ok
3353 (
3354 p_task_id IN NUMBER
3355 ,p_task_version_id IN NUMBER
3356 ,p_new_task_status IN VARCHAR2
3357 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3358 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
3359 )
3360 IS
3361 CURSOR c1 IS
3362 select b.project_id, b.proj_element_id
3363 from pa_proj_element_versions a,
3364 pa_proj_elem_ver_structure b
3365 where a.element_version_id = p_task_version_id
3366 and a.parent_structure_version_id = b.element_version_id
3367 and a.project_id = b.project_id;
3368 l_project_id NUMBER;
3369 l_structure_id NUMBER;
3370 l_xtra_task_id NUMBER;
3371 l_versioned VARCHAR2(1);
3372
3373 CURSOR c2(c_project_id NUMBER, c_structure_id NUMBER) IS
3374 select distinct ppe.proj_element_id
3375 from pa_proj_element_versions ppe
3376 where ppe.element_version_id IN (
3377 select object_id_to1
3378 from pa_object_relationships
3379 where relationship_type = 'S'
3380 start with object_id_from1 IN (
3381 select a.element_version_id
3382 from pa_proj_element_versions a,
3383 pa_proj_elem_ver_structure b
3384 where b.project_id = c_project_id
3385 and b.proj_element_id = c_structure_id
3386 and b.status_code <> 'STRUCTURE_PUBLISHED'
3387 and b.element_version_id = a.parent_structure_version_id
3388 and a.proj_element_id = p_task_id)
3389 and relationship_type = 'S'
3390 connect by object_id_from1 = prior object_id_to1
3391 and object_type_from = prior object_type_to
3392 and relationship_type = prior relationship_type)
3393 minus
3394 select ppe.proj_element_id
3395 from pa_proj_element_versions ppe
3396 where ppe.element_version_id IN (
3397 select object_id_to1
3398 from pa_object_relationships
3399 where relationship_type = 'S'
3400 start with object_id_from1 = p_task_version_id
3401 and object_type_from = 'PA_TASKS'
3402 and relationship_type = 'S'
3403 connect by object_id_from1 = prior object_id_to1
3404 and object_type_from = prior object_type_to
3405 and relationship_type = prior relationship_type);
3406
3407 CURSOR c3(c_project_id NUMBER, c_structure_id NUMBER, c_xtra_task_id NUMBER) IS
3408 select ppev.element_version_id, ppev.TASK_UNPUB_VER_STATUS_CODE
3409 from pa_proj_element_Versions ppev,
3410 pa_proj_elem_ver_structure ppevs
3411 where ppev.proj_element_id = c_xtra_task_id
3412 and ppev.project_id = c_project_id
3413 and ppev.parent_structure_version_id = ppevs.element_version_id
3414 and ppevs.project_id = c_project_id
3415 and ppevs.proj_element_id = c_structure_id
3416 and ppevs.status_code <> 'STRUCTURE_PUBLISHED';
3417 l_xtra_task_ver_id NUMBER;
3418 l_task_ver_status VARCHAR2(30);
3419
3420 BEGIN
3421 OPEN c1;
3422 FETCH c1 into l_project_id, l_structure_id;
3423 CLOSE c1;
3424
3425 --Check if versioning is enabled
3426 l_versioned := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(
3427 l_project_id);
3428
3429 IF (l_versioned = 'Y') THEN
3430 OPEN c2(l_project_id, l_structure_id);
3431 LOOP
3432 --get the task id of all tasks that are in the working version but not in
3433 -- the latest published version.
3434 FETCH c2 into l_xtra_task_id;
3435 EXIT WHEN c2%NOTFOUND;
3436 OPEN c3(l_project_id, l_structure_id, l_xtra_task_id);
3437 LOOP
3438 --get all the task version status of unpublished tasks
3439 FETCH c3 INTO l_xtra_task_ver_id, l_task_ver_status;
3440 EXIT WHEN c3%NOTFOUND;
3441 IF (l_task_ver_status = 'PUBLISHED') THEN
3442 x_error_message_code:= 'PA_PS_TK_STAT_CNL_ERR';
3443 raise FND_API.G_EXC_ERROR;
3444 END IF;
3445 END LOOP;
3446 CLOSE c3;
3447 END LOOP;
3448 CLOSE c2;
3449 END IF;
3450
3451 x_return_status := FND_API.G_RET_STS_SUCCESS;
3452 EXCEPTION
3453 WHEN FND_API.G_EXC_ERROR THEN
3454 x_return_status := FND_API.G_RET_STS_ERROR;
3455 -- 4537865
3456 -- Not resetting x_error_message_code as at this point it would have been already populated.
3457 WHEN OTHERS THEN
3458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3459 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3460 p_procedure_name => 'Check_chg_stat_cancel_ok');
3461
3462 -- 4537865
3463 x_error_message_code := SQLCODE ;
3464 RAISE ;
3465 END Check_chg_stat_cancel_ok;
3466
3467 FUNCTION get_element_name(p_proj_element_id IN NUMBER) RETURN VARCHAR2
3468 IS
3469 l_ret VARCHAR2(240);
3470 BEGIN
3471 IF p_proj_element_id IS NULL OR p_proj_element_id<0 THEN
3472 RETURN NULL;
3473 END IF;
3474
3475 SELECT name
3476 INTO l_ret
3477 FROM pa_proj_elements
3478 WHERE proj_element_id = p_proj_element_id;
3479
3480 RETURN l_ret;
3481 EXCEPTION
3482 WHEN OTHERS THEN
3483 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3484 p_procedure_name => 'get_element_name');
3485 RAISE;
3486 END get_element_name;
3487
3488 FUNCTION get_element_number(p_proj_element_id IN NUMBER) RETURN VARCHAR2
3489 IS
3490 l_ret VARCHAR2(100);
3491 BEGIN
3492 IF p_proj_element_id IS NULL OR p_proj_element_id<0 THEN
3493 RETURN NULL;
3494 END IF;
3495
3496 SELECT element_number
3497 INTO l_ret
3498 FROM pa_proj_elements
3499 WHERE proj_element_id = p_proj_element_id;
3500
3501 RETURN l_ret;
3502 EXCEPTION
3503 WHEN OTHERS THEN
3504 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3505 p_procedure_name => 'get_element_number');
3506 RAISE;
3507 END get_element_number;
3508
3509 FUNCTION get_element_name_number(p_proj_element_id IN NUMBER) RETURN VARCHAR2
3510 IS
3511 l_ret VARCHAR2(1000);
3512 BEGIN
3513 IF p_proj_element_id IS NULL OR p_proj_element_id<0 THEN
3514 RETURN NULL;
3515 END IF;
3516
3517 SELECT name||'('||element_number||')'
3518 INTO l_ret
3519 FROM pa_proj_elements
3520 WHERE proj_element_id = p_proj_element_id;
3521
3522 RETURN l_ret;
3523 EXCEPTION
3524 WHEN OTHERS THEN
3525 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3526 p_procedure_name => 'get_element_name_number');
3527 RAISE;
3528 END get_element_name_number;
3529
3530 function check_child_element_exist(p_element_version_id NUMBER) RETURN VARCHAR2
3531 IS
3532 l_dummy number;
3533
3534 -- Bug 6156686
3535 cursor child_exist IS
3536 select null
3537 from dual where exists
3538 (select null from pa_object_relationships
3539 where object_id_from1 = p_element_version_id
3540 and relationship_type = 'S');
3541 BEGIN
3542
3543 OPEN child_exist;
3544 FETCH child_exist into l_dummy;
3545 IF child_exist%NOTFOUND then
3546 --Cannot find child. It is lowest task
3547 CLOSE child_exist;
3548 return 'N';
3549 ELSE
3550 --Child found. Not lowest task
3551 CLOSE child_exist;
3552 return 'Y';
3553 END IF;
3554 EXCEPTION
3555 WHEN OTHERS THEN
3556 return (SQLCODE);
3557 END check_child_element_exist;
3558
3559
3560 FUNCTION get_task_status_sys_code(p_task_status_code VARCHAR2) RETURN VARCHAR2
3561 IS
3562 l_task_stat_sys_code VARCHAR2(30);
3563 BEGIN
3564 select project_system_status_code into l_task_stat_sys_code
3565 from pa_project_statuses
3566 where p_task_status_code = project_status_code
3567 and status_type = 'TASK';
3568
3569 return l_task_stat_sys_code;
3570 EXCEPTION
3571 WHEN OTHERS THEN
3572 return NULL;
3573 END get_task_status_sys_code;
3574
3575 -- Returns the element_version_id of the next/previous task, given the project id, parent structure
3576 -- version id, and the display sequence of the current task. Pass value "NEXT" and "PREVIOUS" for
3577 -- p_previous_or_next parameter to indicate whether to get the next or the previous task. Returns
3578 -- -1 if there are no next/previous task.
3579 FUNCTION get_next_prev_task_id(
3580 p_project_id IN NUMBER
3581 ,p_structure_version_id IN NUMBER
3582 ,p_display_seq_id IN NUMBER
3583 ,p_previous_or_next IN VARCHAR2) RETURN NUMBER
3584 IS
3585 l_element_version_Id NUMBER;
3586
3587 cursor c_previous_task_id( c_project_id NUMBER, c_struct_version_id NUMBER, c_display_seq NUMBER) IS
3588 select element_version_id
3589 from pa_proj_element_versions
3590 where project_id = c_project_id
3591 and parent_structure_version_id = c_struct_version_id
3592 and display_sequence = (
3593 select max(display_sequence)
3594 from pa_proj_element_versions
3595 where project_id = c_project_id
3596 and parent_structure_version_id = c_struct_version_id
3597 and display_sequence < c_display_seq
3598 );
3599
3600 cursor c_next_task_id( c_project_id NUMBER, c_struct_version_id NUMBER, c_display_seq NUMBER) IS
3601 select element_version_id
3602 from pa_proj_element_versions
3603 where project_id = c_project_id
3604 and parent_structure_version_id = c_struct_version_id
3605 and display_sequence = (
3606 select min(display_sequence)
3607 from pa_proj_element_versions
3608 where project_id = c_project_id
3609 and parent_structure_version_id = c_struct_version_id
3610 and display_sequence > c_display_seq
3611 );
3612
3613 BEGIN
3614
3615 IF p_previous_or_next IS NOT NULL and p_previous_or_next = 'PREVIOUS' then
3616 OPEN c_previous_task_id(p_project_id, p_structure_version_id, p_display_seq_id);
3617 FETCH c_previous_task_id into l_element_version_Id;
3618 IF c_previous_task_id%NOTFOUND then
3619 CLOSE c_previous_task_id;
3620 return -1;
3621 ELSE
3622 CLOSE c_previous_task_id;
3623 return l_element_version_Id;
3624 END IF;
3625 ELSIF p_previous_or_next IS NOT NULL and p_previous_or_next = 'NEXT' then
3626 OPEN c_next_task_id(p_project_id, p_structure_version_id, p_display_seq_id);
3627 FETCH c_next_task_id into l_element_version_Id;
3628 IF c_next_task_id%NOTFOUND then
3629 CLOSE c_next_task_id;
3630 return -1;
3631 ELSE
3632 CLOSE c_next_task_id;
3633 return l_element_version_Id;
3634 END IF;
3635 END IF;
3636
3637 EXCEPTION
3638 WHEN OTHERS THEN
3639 return -1;
3640 END get_next_prev_task_id;
3641
3642 /*==================================================================
3643 This api obtains the structure version id to which task versions
3644 should be created and added to. This API is to be called only from
3645 the AMG context. Included the api for Post FP K one off. Bug 2931183.
3646 This api also returns the task unpublished version status code.
3647 This value is used when we create a task version.
3648 ==================================================================*/
3649
3650 PROCEDURE GET_STRUCTURE_INFO
3651 ( p_project_id IN pa_projects_all.project_id%TYPE
3652 ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
3653 ,p_structure_id IN pa_proj_elements.proj_element_id%TYPE
3654 ,p_is_wp_separate_from_fn IN VARCHAR2
3655 ,p_is_wp_versioning_enabled IN VARCHAR2
3656 ,x_structure_version_id OUT NOCOPY pa_proj_element_versions.element_version_id%TYPE -- 4537865
3657 ,x_task_unpub_ver_status_code OUT NOCOPY pa_proj_element_versions.task_unpub_ver_status_code%TYPE -- 4537865
3658 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3659 ,x_msg_count OUT NOCOPY NUMBER -- 4537865
3660 ,x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
3661 AS
3662
3663
3664 -- Cursors used in this API.
3665 CURSOR cur_struc_ver_wp(c_project_id number,c_structure_type varchar2,c_status_code varchar2)
3666 IS
3667 SELECT c.element_version_id
3668 FROM pa_proj_element_versions c,
3669 pa_structure_types a,
3670 pa_proj_structure_types b
3671 ,pa_proj_elem_ver_structure d
3672 WHERE c.project_id = c_project_id
3673 AND a.structure_type_id = b.structure_type_id
3674 AND b.proj_element_id = c.proj_element_id
3675 AND a.structure_type = c_structure_type
3676 AND d.project_id = c.project_id
3677 AND d.element_version_id = c.element_version_id
3678 AND d.status_code = c_status_code;
3679
3680 CURSOR cur_struc_ver_fin(c_project_id number,c_structure_type varchar2)
3681 IS
3682 SELECT c.element_version_id
3683 FROM pa_proj_element_versions c,
3684 pa_structure_types a,
3685 pa_proj_structure_types b,
3686 pa_proj_elem_ver_structure d
3687 WHERE c.project_id = c_project_id
3688 AND a.structure_type_id = b.structure_type_id
3689 AND b.proj_element_id = c.proj_element_id
3690 AND a.structure_type = c_structure_type
3691 AND d.project_id = c.project_id
3692 AND d.element_version_id = c.element_version_id
3693 AND d.status_code = 'STRUCTURE_PUBLISHED'
3694 AND d.latest_eff_published_flag = 'Y';
3695 -- End cursors used in this API.
3696
3697 l_msg_count NUMBER := 0;
3698 l_data VARCHAR2(2000);
3699 l_msg_data VARCHAR2(2000);
3700 l_msg_index_out NUMBER;
3701 l_debug_mode VARCHAR2(1);
3702
3703 l_debug_level2 CONSTANT NUMBER := 2;
3704 l_debug_level3 CONSTANT NUMBER := 3;
3705 l_debug_level4 CONSTANT NUMBER := 4;
3706 l_debug_level5 CONSTANT NUMBER := 5;
3707
3708 BEGIN
3709 IF l_debug_mode = 'Y' THEN
3710 pa_debug.g_err_stage:= 'Entering GET_STRUCTURE_INFO';
3711 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3712 l_debug_level2);
3713 END IF;
3714
3715 x_msg_count := 0;
3716 x_return_status := FND_API.G_RET_STS_SUCCESS;
3717 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3718
3719 IF l_debug_mode = 'Y' THEN --For bug 4252182
3720
3721 pa_debug.set_curr_function( p_function => 'GET_STRUCTURE_INFO',
3722 p_debug_mode => l_debug_mode );
3723 END IF;
3724
3725 -- Check for business rules violations
3726
3727 IF l_debug_mode = 'Y' THEN
3728
3729 pa_debug.g_err_stage:= 'Input parameter List :';
3730 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3731 l_debug_level3);
3732
3733 pa_debug.g_err_stage:= 'p_project_id : ' || p_project_id;
3734 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3735 l_debug_level3);
3736
3737
3738 pa_debug.g_err_stage:= 'p_structure_type : ' || p_structure_type;
3739 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3740 l_debug_level3);
3741
3742 pa_debug.g_err_stage:= 'p_structure_id : ' || p_structure_id;
3743 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3744 l_debug_level3);
3745
3746 pa_debug.g_err_stage:= 'p_is_wp_separate_from_fn :' || p_is_wp_separate_from_fn;
3747 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3748 l_debug_level3);
3749
3750 pa_debug.g_err_stage:= 'p_is_wp_versioning_enabled :' || p_is_wp_versioning_enabled;
3751 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3752 l_debug_level3);
3753 END IF;
3754
3755
3756 IF (p_project_id IS NULL) OR
3757 (p_structure_type IS NULL) OR
3758 (p_structure_id IS NULL) OR
3759 (p_is_wp_separate_from_fn IS NULL) OR
3760 (p_is_wp_versioning_enabled IS NULL)
3761 THEN
3762 PA_UTILS.ADD_MESSAGE
3763 (p_app_short_name => 'PA',
3764 p_msg_name => 'PA_INV_PARAM_PASSED'); -- Bug 2955589. Changed the message name to
3765 RAISE Invalid_Arg_Exc_WP; -- have a generic message.
3766
3767 END IF;
3768
3769 /*
3770 If workplan context, if versioning is enabled get the working version. else
3771 get the published version. In financial context get working version. If
3772 working version could not be found, get the published version.
3773 */
3774 IF p_structure_type = 'WORKPLAN' THEN
3775 IF p_is_wp_separate_from_fn = 'Y' AND p_is_wp_versioning_enabled = 'Y' THEN
3776 x_structure_version_id :=
3777 PA_PROJECT_STRUCTURE_UTILS.get_last_updated_working_ver(p_structure_id);
3778 x_task_unpub_ver_status_code := 'WORKING';
3779 END IF;
3780
3781 IF x_structure_version_id is null THEN
3782 open cur_struc_ver_wp(p_project_id,'WORKPLAN','STRUCTURE_PUBLISHED');
3783 fetch cur_struc_ver_wp into x_structure_version_id;
3784 close cur_struc_ver_wp;
3785 x_task_unpub_ver_status_code := 'PUBLISHED';
3786 END IF;
3787 ELSE -- structure type is financial
3788 open cur_struc_ver_wp(p_project_id,'FINANCIAL','STRUCTURE_WORKING');
3789 fetch cur_struc_ver_wp into x_structure_version_id;
3790 close cur_struc_ver_wp;
3791 x_task_unpub_ver_status_code := 'WORKING';
3792
3793 IF x_structure_version_id is null THEN
3794 open cur_struc_ver_fin(p_project_id,'FINANCIAL');
3795 fetch cur_struc_ver_fin into x_structure_version_id;
3796 close cur_struc_ver_fin;
3797 x_task_unpub_ver_status_code := 'PUBLISHED';
3798 END IF;
3799 END IF;
3800
3801 IF l_debug_mode = 'Y' THEN
3802 pa_debug.g_err_stage:= 'Obtained structure version id : '||x_structure_version_id;
3803 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3804 l_debug_level3);
3805 pa_debug.g_err_stage:= 'Task Unpublished version status code : '||x_task_unpub_ver_status_code;
3806 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3807 l_debug_level3);
3808 pa_debug.g_err_stage:= 'Exiting GET_STRUCTURE_INFO';
3809 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3810 l_debug_level2);
3811 END IF;
3812
3813 IF l_debug_mode = 'Y' THEN --For bug 4252182
3814 pa_debug.reset_curr_function;
3815 End IF;
3816 EXCEPTION
3817
3818 WHEN Invalid_Arg_Exc_WP THEN
3819
3820 x_return_status := FND_API.G_RET_STS_ERROR;
3821 l_msg_count := FND_MSG_PUB.count_msg;
3822
3823 IF cur_struc_ver_wp%ISOPEN THEN
3824 CLOSE cur_struc_ver_wp;
3825 END IF;
3826
3827 -- 4537865 : Start
3828 x_task_unpub_ver_status_code := NULL ;
3829 x_structure_version_id := NULL ;
3830 -- 4537865 : End
3831
3832 IF cur_struc_ver_fin%ISOPEN THEN
3833 CLOSE cur_struc_ver_fin;
3834 END IF;
3835
3836 IF l_msg_count = 1 and x_msg_data IS NULL THEN
3837 PA_INTERFACE_UTILS_PUB.get_messages
3838 (p_encoded => FND_API.G_TRUE
3839 ,p_msg_index => 1
3840 ,p_msg_count => l_msg_count
3841 ,p_msg_data => l_msg_data
3842 ,p_data => l_data
3843 ,p_msg_index_out => l_msg_index_out);
3844 x_msg_data := l_data;
3845 x_msg_count := l_msg_count;
3846 ELSE
3847 x_msg_count := l_msg_count;
3848 END IF;
3849
3850 IF l_debug_mode = 'Y' THEN --For bug 4252182
3851 pa_debug.reset_curr_function;
3852 End IF;
3853 RETURN;
3854
3855 WHEN others THEN
3856
3857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3858 x_msg_count := 1;
3859 x_msg_data := SQLERRM;
3860
3861 IF cur_struc_ver_wp%ISOPEN THEN
3862 CLOSE cur_struc_ver_wp;
3863 END IF;
3864
3865 IF cur_struc_ver_fin%ISOPEN THEN
3866 CLOSE cur_struc_ver_fin;
3867 END IF;
3868
3869 -- 4537865 : Start
3870 x_task_unpub_ver_status_code := NULL ;
3871 x_structure_version_id := NULL;
3872 -- 4537865 : End
3873
3874 FND_MSG_PUB.add_exc_msg
3875 ( p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS'
3876 ,p_procedure_name => 'GET_STRUCTURE_INFO'
3877 ,p_error_text => x_msg_data);
3878
3879 IF l_debug_mode = 'Y' THEN
3880 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3881 pa_debug.write(g_module_name,pa_debug.g_err_stage,
3882 l_debug_level5);
3883 pa_debug.reset_curr_function;
3884 END IF;
3885 RAISE;
3886 END GET_STRUCTURE_INFO;
3887
3888 --Begin add rtarway FP.M Develepment
3889 -- Procedure : CHECK_TASK_HAS_TRANSACTION
3890 -- Type : Public Procedure
3891 -- Purpose : This procedure will check whether the task has transaction or not.This API will be
3892 -- : called from Set_Financial_task_API.
3893 -- Note : Check whether it is a financial task or workplan task.
3894 -- : Fetch the parent_structure_version_id for the passed proj_element_Id from
3895 -- : PA_PROJ_ELEMENT_VERSIONS and pass to API PA_PROJ_ELEMENTS_UTILS.structure_type
3896
3897 -- Assumptions : Only called for Financial task
3898
3899 -- Parameters Type Required Description and Purpose
3900 -- --------------------------- ------ -------- --------------------------------------------------------
3901 -- p_task_id NUMBER Yes This indicates the task ID for which the transaction needs to be checked.
3902
3903
3904 PROCEDURE CHECK_TASK_HAS_TRANSACTION
3905 (
3906 p_api_version IN NUMBER := 1.0
3907 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3908 , p_debug_mode IN VARCHAR2 := 'N'
3909 , p_task_id IN NUMBER
3910 , p_project_id IN NUMBER -- Added for Performance fix 4903460
3911 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3912 , x_msg_count OUT NOCOPY NUMBER -- 4537865
3913 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
3914 , x_error_msg_code OUT NOCOPY VARCHAR2 -- 4537865
3915 , x_error_code OUT NOCOPY NUMBER -- 4537865
3916 )
3917 IS
3918
3919 l_msg_count NUMBER := 0;
3920 l_data VARCHAR2(2000);
3921 l_msg_data VARCHAR2(2000);
3922 l_msg_index_out NUMBER;
3923 l_debug_mode VARCHAR2(1);
3924
3925 l_prnt_str_ver_id NUMBER;
3926 l_return_val VARCHAR2(1);
3927 l_used_in_OTL BOOLEAN;
3928 l_status_code NUMBER;
3929 l_return_status VARCHAR2(1);
3930 Is_IEX_Installed BOOLEAN;
3931
3932 l_debug_level2 CONSTANT NUMBER := 2;
3933 l_debug_level3 CONSTANT NUMBER := 3;
3934 l_debug_level4 CONSTANT NUMBER := 4;
3935 l_debug_level5 CONSTANT NUMBER := 5;
3936
3937 --This cursor will select the parent structure version id for the passed proj_elem_id, here it is used for task
3938 --CURSOR c_get_parent_str_ver_id (task_id NUMBER)
3939 --IS
3940 --SELECT PARENT_STRUCTURE_VERSION_ID
3941 --FROM PA_PROJ_ELEMENT_VERSIONS
3942 --WHERE PROJ_ELEMENT_ID = task_id;
3943
3944 --Bug 3735089
3945 l_user_id NUMBER;
3946 l_login_id NUMBER;
3947
3948 BEGIN
3949 x_msg_count := 0;
3950 x_return_status := FND_API.G_RET_STS_SUCCESS;
3951 --Bug 3735089 - instead of fnd_profile.value use fnd_profile.value_specific
3952 --l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3953 l_user_id := fnd_global.user_id;
3954 l_login_id := fnd_global.login_id;
3955 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
3956
3957 IF l_debug_mode = 'Y' THEN
3958 PA_DEBUG.set_curr_function( p_function =>'CHECK_TASK_HAS_TRANSACTION' , p_debug_mode => l_debug_mode );
3959 END IF;
3960
3961 IF l_debug_mode = 'Y' THEN
3962 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Printing Input parameters';
3963 Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3964 Pa_Debug.WRITE(g_module_name , 'p_task_id'||':'||p_task_id , l_debug_level3);
3965 END IF;
3966
3967
3968 IF l_debug_mode = 'Y' THEN
3969 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Validating Input Paramater';
3970 Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3971 END IF;
3972
3973 -- Validating for Input parameter
3974 IF ( p_task_id IS NOT NULL ) THEN
3975
3976 --Commented to be reviewed once more with set financial task
3977 --IF l_debug_mode = 'Y' THEN
3978 -- Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Checking for financial type';
3979 -- Pa_Debug.WRITE ( g_module_name , Pa_Debug.g_err_stage , l_debug_level3 );
3980 --END IF;
3981
3982 --Select the parent structure version id for the passed task id
3983 --OPEN c_get_parent_str_ver_id ( p_task_id );
3984 --FETCH c_get_parent_str_ver_id INTO l_prnt_str_ver_id;
3985 --CLOSE c_get_parent_str_ver_id;
3986
3987 --check if the structure type is financial
3988 --IF (
3989 -- PA_PROJ_ELEMENTS_UTILS.structure_type
3990 -- ( p_structure_version_id => l_prnt_str_ver_id
3991 -- , p_task_version_id => null
3992 -- , p_structure_type => 'FINANCIAL'
3993 -- ) = 'Y'
3994 -- )THEN
3995 --put the tests of check_delete_task_ok here.
3996
3997 -- Commenting code for 4903460 and replacing this wth new code
3998 --Check if task has expenditure item
3999 /* IF l_debug_mode = 'Y' THEN
4000 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check expenditure item for '|| p_task_id;
4001 Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
4002 END IF;
4003 l_status_code :=
4004 pa_proj_tsk_utils.check_exp_item_exists(null, p_task_id);
4005 IF ( l_status_code = 1 ) THEN
4006 x_error_code := 50;
4007 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
4008 return;
4009 ELSIF ( l_status_code < 0 ) THEN
4010 x_error_code := l_status_code;
4011 return;
4012 END IF;
4013
4014 --Check if task has purchase order distribution
4015 IF l_debug_mode = 'Y' THEN
4016 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order for '|| p_task_id;
4017 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4018 l_debug_level3);
4019 END IF;
4020 l_status_code :=
4021 pa_proj_tsk_utils.check_po_dist_exists(NULL, p_task_id);
4022 IF ( l_status_code = 1 ) THEN
4023 x_error_code := 60;
4024 x_error_msg_code := 'PA_TSK_PO_DIST_EXIST';
4025 return;
4026 ELSIF ( l_status_code < 0 ) THEN
4027 x_error_code := l_status_code;
4028 return;
4029 END IF;
4030
4031 -- Check if task has purchase order requisition
4032 IF l_debug_mode = 'Y' THEN
4033 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order requisition for '|| p_task_id;
4034 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4035 l_debug_level3);
4036 END IF;
4037
4038 l_status_code :=
4039 pa_proj_tsk_utils.check_po_req_dist_exists(NULL, p_task_id);
4040 IF ( l_status_code = 1 ) THEN
4041 x_error_code := 70;
4042 x_error_msg_code := 'PA_TSK_PO_REQ_DIST_EXIST';
4043 return;
4044 ELSIF ( l_status_code < 0 ) THEN
4045 x_error_code := l_status_code;
4046 return;
4047 END IF;
4048
4049 -- Check if task has supplier invoices
4050 IF l_debug_mode = 'Y' THEN
4051 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier invoice for '|| p_task_id;
4052 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4053 l_debug_level3);
4054 END IF;
4055
4056 l_status_code :=
4057 pa_proj_tsk_utils.check_ap_invoice_exists(NULL, p_task_id);
4058 IF ( l_status_code = 1 ) THEN
4059 x_error_code := 80;
4060 x_error_msg_code := 'PA_TSK_AP_INV_EXIST';
4061 return;
4062 ELSIF ( l_status_code < 0 ) THEN
4063 x_error_code := l_status_code;
4064 return;
4065 END IF;
4066
4067 -- Check if task has supplier invoice distribution
4068 IF l_debug_mode = 'Y' THEN
4069 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier inv distribution for '|| p_task_id;
4070 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4071 l_debug_level3);
4072 END IF;
4073
4074 l_status_code :=
4075 pa_proj_tsk_utils.check_ap_inv_dist_exists(NULL, p_task_id);
4076 IF ( l_status_code = 1 ) THEN
4077 x_error_code := 90;
4078 x_error_msg_code := 'PA_TSK_AP_INV_DIST_EXIST';
4079 return;
4080 ELSIF ( l_status_code < 0 ) THEN
4081 x_error_code := l_status_code;
4082 return;
4083 END IF;
4084
4085 -- Check if task has commitment transaction
4086 IF l_debug_mode = 'Y' THEN
4087 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check commitment transaction for '|| p_task_id;
4088 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4089 l_debug_level3);
4090 END IF;
4091 l_status_code :=
4092 pa_proj_tsk_utils.check_commitment_txn_exists(null, p_task_id);
4093 IF ( l_status_code = 1 ) THEN
4094 x_error_code := 110;
4095 x_error_msg_code := 'PA_TSK_CMT_TXN_EXIST';
4096 return;
4097 ELSIF ( l_status_code < 0 ) THEN
4098 x_error_code := l_status_code;
4099 return;
4100 END IF;
4101
4102 -- Check if task has compensation rule set
4103 IF l_debug_mode = 'Y' THEN
4104 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check compensation rule set for '|| p_task_id;
4105 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4106 l_debug_level3);
4107 END IF;
4108
4109 l_status_code :=
4110 pa_proj_tsk_utils.check_comp_rule_set_exists(NULL, p_task_id);
4111 IF ( l_status_code = 1 ) THEN
4112 x_error_code := 120;
4113 x_error_msg_code := 'PA_TSK_COMP_RULE_SET_EXIST';
4114 return;
4115 ELSIF ( l_status_code < 0 ) THEN
4116 x_error_code := l_status_code;
4117 return;
4118 END IF; */
4119 -- End of Commenting for 4903460
4120 -- Check if task is in use in an external system
4121 IF l_debug_mode = 'Y' THEN
4122 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check for task used in external system for'|| p_task_id;
4123 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4124 l_debug_level3);
4125 END IF;
4126
4127 l_status_code :=
4128 pjm_projtask_deletion.CheckUse_ProjectTask(null, p_task_id);
4129 IF ( l_status_code = 1 ) THEN
4130 x_error_code := 130;
4131 /* Commented the existing error message and modified it to 'PA_PROJ_TASK_IN_USE_MFG' as below for bug 3600806
4132 x_error_msg_code := 'PA_TASK_IN_USE_EXTERNAL';*/
4133 x_error_msg_code := 'PA_PROJ_TASK_IN_USE_MFG';
4134 return;
4135 ELSIF ( l_status_code = 2 ) THEN -- Added elseif condition for bug 3600806.
4136 x_error_code := 130;
4137 x_error_msg_code := 'PA_PROJ_TASK_IN_USE_AUTO';
4138 return;
4139 ELSIF ( l_status_code < 0 ) THEN
4140 x_error_code := l_status_code;
4141 return;
4142 ELSIF ( l_status_code <> 0) then -- Added else condition for bug 3600806 to display a generic error message.
4143 x_error_code := 130;
4144 x_error_msg_code := 'PA_PROJ_TASK_IN_USE_EXTERNAL';
4145 return;
4146 END IF;
4147
4148 -- Check if task is used in allocations
4149 IF l_debug_mode = 'Y' THEN
4150 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if project allocations uses task '|| p_task_id;
4151 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4152 l_debug_level3);
4153 END IF;
4154
4155 l_return_val :=
4156 pa_alloc_utils.Is_Task_In_Allocations(p_task_id);
4157 IF ( l_return_val = 'Y' ) THEN
4158 x_error_code := 140;
4159 x_error_msg_code := 'PA_TASK_IN_ALLOC';
4160 return;
4161 END IF;
4162
4163 -- Commenting for Performance fix 4903460
4164 /*
4165 -- Check if task has draft invoices
4166 IF l_debug_mode = 'Y' THEN
4167 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check draft invoice for '|| p_task_id;
4168 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4169 l_debug_level3);
4170 END IF;
4171
4172 l_status_code :=
4173 pa_proj_tsk_utils.check_draft_inv_details_exists(p_task_id);
4174 IF ( l_status_code = 1 ) THEN
4175 x_error_code := 160;
4176 x_error_msg_code := 'PA_TSK_CC_DINV_EXIST';
4177 return;
4178 ELSIF ( l_status_code < 0 ) THEN
4179 x_error_code := l_status_code;
4180 return;
4181 END IF;
4182
4183 -- Check if task has Project_customers
4184 IF l_debug_mode = 'Y' THEN
4185 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check Project Customers for '|| p_task_id;
4186 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4187 l_debug_level3);
4188 END IF;
4189
4190
4191 l_status_code :=
4192 pa_proj_tsk_utils.check_project_customer_exists(p_task_id);
4193 IF ( l_status_code = 1 ) THEN
4194 x_error_code := 170;
4195 x_error_msg_code := 'PA_TSK_CC_CUST_EXIST';
4196 return;
4197 ELSIF ( l_status_code < 0 ) THEN
4198 x_error_code := l_status_code;
4199 return;
4200 END IF; */
4201 --End of Commenting for Performance fix 4903460
4202
4203 -- Start of new code for Performance fix 4903460
4204 PA_PROJ_ELEMENTS_UTILS.perform_task_validations
4205 (
4206 p_project_id => p_project_id
4207 ,p_task_id => p_task_id
4208 ,x_error_code => x_error_code
4209 ,x_error_msg_code => x_error_msg_code
4210 );
4211
4212 IF x_error_code <> 0 THEN
4213 return;
4214 END IF;
4215 -- End of new code for Performance fix 4903460
4216
4217 -- Check if project contract is installed
4218 IF (pa_install.is_product_installed('OKE')) THEN
4219 IF l_debug_mode = 'Y' THEN
4220 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Check contract association for task '|| p_task_id;
4221 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4222 l_debug_level3);
4223 END IF;
4224
4225 IF (PA_PROJ_STRUCTURE_PUB.CHECK_TASK_CONTRACT_ASSO(p_task_id) <>
4226 FND_API.G_RET_STS_SUCCESS) THEN
4227 x_error_code := 190;
4228 x_error_msg_code := 'PA_STRUCT_TK_HAS_CONTRACT';
4229 return;
4230 END IF;
4231 END IF;
4232 -- Finished checking if project contract is installed.
4233
4234 --Check to see if the task has been used in OTL
4235 PA_OTC_API.ProjectTaskUsed( p_search_attribute => 'TASK',
4236 p_search_value => p_task_id,
4237 x_used => l_used_in_OTL );
4238 --If exists in OTL
4239 IF l_used_in_OTL
4240 THEN
4241 x_error_code := 200;
4242 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
4243 return;
4244 END IF;
4245
4246 --end of OTL check.
4247 Is_IEX_Installed := pa_install.is_product_installed('IEX');
4248 If Is_IEX_Installed then
4249 IF l_debug_mode = 'Y' THEN
4250 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if task '|| p_task_id || ' is charged in iexpense';
4251 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4252 l_debug_level3);
4253 END IF;
4254
4255 l_status_code := pa_proj_tsk_utils.check_iex_task_charged(p_task_id);
4256 IF ( l_status_code = 1 ) THEN
4257 x_error_code := 210;
4258 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
4259 return;
4260 ELSIF ( l_status_code < 0 ) THEN
4261 x_error_code := l_status_code;
4262 return;
4263 END IF;
4264 END IF;
4265 --BEGIN
4266 IF l_debug_mode = 'Y' THEN
4267 Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK'|| p_task_id;
4268 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4269 l_debug_level3);
4270 END IF;
4271
4272 PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK(
4273 p_task_id => p_task_id
4274 ,p_validation_mode => 'U'
4275 ,x_return_status => l_return_status
4276 ,x_msg_count => l_msg_count
4277 ,x_msg_data => l_msg_data
4278 );
4279 IF (l_return_status <> 'S') Then
4280 x_error_code := 220;
4281 x_error_msg_code := pa_project_core1.get_message_from_stack( l_msg_data );
4282 return;
4283 END IF;
4284 --EXCEPTION WHEN OTHERS THEN
4285 -- IF l_debug_mode = 'Y' THEN
4286 -- Pa_Debug.g_err_stage:= 'API PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK FAILED';
4287 -- Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4288 -- l_debug_level3);
4289 -- END IF;
4290 --END;
4291
4292 --To be reviewed with set financial task
4293 --END IF;--If Financial task condition
4294 END IF;-- If task ID is not null condition
4295
4296 -- Bug 3735089 : using reset_curr_function too, just using set_curr_function may overflow it after several recursive calls
4297 -- and it gives ORA 06512 numeric or value error
4298 IF l_debug_mode = 'Y' THEN
4299 Pa_Debug.reset_curr_function;
4300 END IF;
4301
4302
4303 EXCEPTION
4304
4305 WHEN OTHERS THEN
4306
4307 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4308 x_msg_count := 1;
4309 x_msg_data := substrb(SQLERRM,1,120);-- Bug 3735089 Added substr -- 4537865 Changed substr to substrb
4310
4311 -- 4537865
4312 x_error_code := SQLCODE;
4313 x_error_msg_code := SQLCODE ;
4314
4315 Fnd_Msg_Pub.add_exc_msg
4316 ( p_pkg_name => 'PA_PROJ_ELEMENT_UTILS'
4317 ,p_procedure_name => 'CHECK_TASK_HAS_TRANSACTION'
4318 ,p_error_text => x_msg_data);
4319
4320 IF l_debug_mode = 'Y' THEN
4321 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4322 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4323 l_debug_level5);
4324
4325 Pa_Debug.reset_curr_function;
4326 END IF;
4327 RAISE;
4328
4329 END CHECK_TASK_HAS_TRANSACTION;
4330 --Begin add rtarway FP.M Develepment
4331
4332 function GET_TOP_TASK_VER_ID(p_element_version_id IN number) return number
4333 is
4334 --bug 4043647 , start
4335 /*cursor C1 is
4336 select object_id_from1
4337 from pa_object_relationships
4338 where relationship_type='S'
4339 and object_type_from='PA_TASKS'
4340 connect by prior object_id_from1 = object_id_to1
4341 and prior relationship_type = relationship_type
4342 start with object_id_to1 = p_element_version_id
4343 and relationship_type = 'S'
4344 union
4345 select p_element_version_id from dual ; --bug 3429648*/
4346
4347 cursor C1 is
4348 select object_id_to1
4349 from pa_object_relationships
4350 where relationship_type='S'
4351 and object_type_from='PA_STRUCTURES'
4352 and object_type_to='PA_TASKS'
4353 connect by prior object_id_from1 = object_id_to1
4354 start with object_id_to1 = p_element_version_id
4355 and relationship_type = 'S';
4356
4357 --bug 4043647 , end
4358
4359 /*
4360 intersect
4361 select a.object_id_to1
4362 from pa_object_relationships a, pa_proj_element_versions b
4363 where b.element_version_id = p_element_version_id
4364 and b.parent_structure_version_id = a.object_id_from1
4365 and a.relationship_type = 'S';
4366 */
4367
4368 l_top_task_ver_id NUMBER := p_element_version_id;
4369
4370 begin
4371 OPEN c1;
4372 FETCH c1 INTO l_top_task_ver_id;
4373 CLOSE c1;
4374 return l_top_task_ver_id;
4375 exception
4376 when others then
4377 return(SQLCODE);
4378 end GET_TOP_TASK_VER_ID;
4379
4380 function GET_TOP_TASK_ID(p_element_version_id IN number) return number
4381 is
4382 cursor C1 (evid number) is
4383 select proj_element_id from pa_proj_element_versions
4384 where element_version_id IN (
4385 /*select object_id_from1 --bug 4043647
4386 from pa_object_relationships
4387 where relationship_type='S'
4388 and object_type_from='PA_TASKS'
4389 connect by prior object_id_from1 = object_id_to1
4390 and prior relationship_type = relationship_type
4391 start with object_id_to1 = p_element_version_id
4392 and relationship_type = 'S'
4393 union
4394 select p_element_version_id from dual ); --bug 3429648*/
4395 select object_id_to1
4396 from pa_object_relationships
4397 where relationship_type='S'
4398 and object_type_from='PA_STRUCTURES'
4399 and object_type_to='PA_TASKS'
4400 connect by prior object_id_from1 = object_id_to1
4401 start with object_id_to1 = p_element_version_id
4402 and relationship_type = 'S');
4403
4404
4405 /*
4406 intersect
4407 select a.object_id_to1
4408 from pa_object_relationships a, pa_proj_element_versions b
4409 where b.element_version_id = p_element_version_id
4410 and b.parent_structure_version_id = a.object_id_from1
4411 and a.relationship_type = 'S');
4412 */
4413
4414 l_top_task_ver_id NUMBER := p_element_version_id;
4415 l_top_task_id NUMBER;
4416
4417 begin
4418 OPEN c1(p_element_version_id);
4419 FETCH c1 INTO l_top_task_id;
4420 CLOSE c1;
4421
4422 return l_top_task_id;
4423 exception
4424 when others then
4425 return(SQLCODE);
4426 end GET_TOP_TASK_ID;
4427
4428 /* 4156732 : This API returns Task Level for Workplan Tasks
4429 */
4430 function GET_TASK_LEVEL(p_element_version_id IN number) return varchar2
4431 is
4432 cursor C1 (evid number) is
4433 select 1
4434 from pa_object_relationships
4435 where object_id_to1 = evid
4436 and relationship_type='S'
4437 and object_type_from='PA_STRUCTURES';
4438
4439 cursor C2 (evid number) is
4440 select 1
4441 from pa_object_relationships
4442 where object_id_from1 = evid
4443 and relationship_type='S';
4444
4445 c1rec C1%ROWTYPE;
4446 c2rec C2%ROWTYPE;
4447
4448 l_tasks_above NUMBER :=0;
4449 l_tasks_below NUMBER :=0;
4450
4451 l_task_level VARCHAR2(1) :='L';
4452
4453 l_dummy NUMBER;
4454
4455 CURSOR c3(evid NUMBER) IS
4456 select 1 from pa_proj_element_versions
4457 where element_version_id = evid
4458 and object_type = 'PA_STRUCTURES';
4459
4460 begin
4461 OPEN c3(p_element_version_id);
4462 FETCH c3 into l_dummy;
4463 IF C3%FOUND THEN --it is a structure, return T or L
4464 OPEN c2(p_element_version_id);
4465 FETCH c2 into l_dummy;
4466 IF C2%FOUND THEN
4467 --it has child
4468 l_task_level := 'T';
4469 END IF;
4470 CLOSE c2;
4471 CLOSE c3;
4472 return l_task_level;
4473 END IF;
4474 CLOSE c3;
4475
4476 OPEN C1(p_element_version_id);
4477 FETCH c1 into l_dummy;
4478 IF c1%found THEN
4479 l_task_level := 'T';
4480 CLOSE c1;
4481 OPEN c2(p_element_version_id);
4482 FETCH c2 into l_dummy;
4483 IF c2%NOTFOUND THEN
4484 l_task_level := 'L';
4485 END IF;
4486 CLOSE c2;
4487 return l_task_level;
4488 END IF;
4489 CLOSE c1;
4490
4491 OPEN C2(p_element_version_id);
4492 FETCH c2 into l_dummy;
4493 IF c2%found THEN
4494 l_task_level := 'M';
4495 CLOSE c2;
4496 return l_task_level;
4497 END IF;
4498 CLOSE c2;
4499
4500 return l_task_level;
4501 exception
4502 when others then
4503 return(SQLERRM);
4504 end GET_TASK_LEVEL;
4505
4506 /* Created by avaithia for Bug 4156732
4507 This API (over-ridden GET_TASK_LEVEL API) which takes the following two parameters
4508 gives the Task Level of Financial Tasks.
4509
4510 API Name : GET_TASK_LEVEL
4511
4512 Parameters Description Type Of Parameter
4513 ========== =============== ===================
4514 p_project_id The Project ID PA_PROJECTS_ALL.PROJECT_ID%TYPE
4515 p_proj_element_id The ProjElementID PA_PROJ_ELEMENT_VERSIONS.PROJ_ELEMENT_ID%TYPE
4516
4517 Return Value of this function :
4518 ===============================
4519 'T' -> Top Task , 'M' -> Middle Task , 'L' -> Lowest Task , 'X' -> Not Financial Task
4520
4521 Note that it is very much possible that (say) the case of Partially Shared Structures,
4522 Some of the tasks may be both workplan and financial tasks,whereas some tasks are only workplan tasks.
4523
4524 In this case,There will not be any entry for those 'pure' workplan tasks in PA_TASKS table.
4525 Hence,for such passed Proj_element_id's this API will return 'X'
4526 */
4527 FUNCTION GET_TASK_LEVEL(p_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4528 p_proj_element_id PA_PROJ_ELEMENT_VERSIONS.PROJ_ELEMENT_ID%TYPE) RETURN VARCHAR2
4529 IS
4530 l_task_level VARCHAR2(1) :='L';
4531
4532 l_dummy NUMBER;
4533 l_parent_task NUMBER;
4534 l_mid_task NUMBER;
4535
4536 CURSOR c_task_exists
4537 IS
4538 select 1
4539 from pa_tasks
4540 where task_id = p_proj_element_id
4541 and project_id = p_project_id
4542 ;
4543
4544 CURSOR c_is_parent_task
4545 IS
4546 select 1
4547 from dual
4548 where exists
4549 (select 1
4550 from pa_tasks
4551 where nvl(parent_task_id,-9999) = p_proj_element_id
4552 and project_id = p_project_id
4553 ) ;
4554
4555 CURSOR c_is_mid_task
4556 IS
4557 select 1 from pa_tasks
4558 where parent_task_id is not null
4559 and task_id = p_proj_element_id ;
4560
4561 BEGIN
4562
4563 OPEN c_task_exists;
4564 FETCH c_task_exists INTO l_dummy ;
4565 CLOSE c_task_exists;
4566
4567 IF nvl(l_dummy,0) = 0
4568 THEN
4569 return 'X' ; -- as the task doesnt exist in PA_TASKS table
4570 END IF;
4571
4572 --At this point the task exists
4573
4574 OPEN c_is_parent_task ;
4575 FETCH c_is_parent_task INTO l_parent_task;
4576 CLOSE c_is_parent_task;
4577
4578 IF nvl(l_parent_task,0) = 0
4579 THEN
4580 return 'L' ; -- as the task is not parent of any other task,(i.e) No Children ,Hence it is the lowest task
4581 END IF;
4582
4583 -- At this point ,The Task exists and it has children ,So it can be a top task or a mid task
4584
4585 OPEN c_is_mid_task ;
4586 FETCH c_is_mid_task INTO l_mid_task;
4587 CLOSE c_is_mid_task ;
4588
4589 IF nvl(l_mid_task,0) = 0
4590 THEN
4591 return 'T' ; -- as the task exists and its parent_task_id is null => It has no parent,hence the top most task
4592 END IF;
4593
4594 -- At this point ,The Task exists and it has children as well as a parent task ,So this is a mid-task
4595 return 'M';
4596
4597 exception
4598 when others then
4599 return (SQLERRM);
4600
4601 END GET_TASK_LEVEL ;
4602
4603 --Begin Add sabansal
4604 --Function to check whether the given task is a workplan task or not
4605 FUNCTION CHECK_IS_WORKPLAN_TASK(p_project_id NUMBER,
4606 p_proj_element_id NUMBER) RETURN VARCHAR2
4607 IS
4608 str_sharing_code VARCHAR2(30):= null;
4609 return_flag VARCHAR2(1) := null;
4610
4611 BEGIN
4612
4613 SELECT structure_sharing_code INTO str_sharing_code
4614 FROM pa_projects_all
4615 WHERE project_id = p_project_id;
4616
4617 --If sharing is enabled, then financial and workplan tasks would be common
4618 IF str_sharing_code = 'SHARE_FULL' OR
4619 str_sharing_code = 'SHARE_PARTIAL' THEN
4620 return_flag := 'Y';
4621 ELSIF PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(p_proj_element_id) = 'N' THEN
4622 return_flag := 'Y';
4623 ELSE
4624 return_flag := 'N';
4625 END IF;
4626
4627 return return_flag;
4628 EXCEPTION
4629 WHEN OTHERS THEN
4630 return null;
4631 END CHECK_IS_WORKPLAN_TASK;
4632 --End Add sabansal
4633
4634 function GET_PARENT_TASK_ID(p_element_version_id IN number) return number
4635 is
4636 CURSOR c1 IS
4637 SELECT proj_element_id
4638 FROM pa_object_relationships, pa_proj_element_versions
4639 WHERE object_id_to1 = p_element_version_id
4640 AND object_type_from='PA_TASKS'
4641 AND object_id_from1 = element_version_id
4642 AND relationship_type = 'S'; -- added for bug 16170622 to eliminate 'D' dependency records
4643
4644 l_parent_task_id NUMBER := NULL;
4645 begin
4646 OPEN c1;
4647 FETCH c1 into l_parent_task_id;
4648 CLOSE c1;
4649
4650 return l_parent_task_id;
4651 exception
4652 when others then
4653 return(SQLCODE);
4654 end GET_PARENT_TASK_ID;
4655
4656 function GET_PARENT_TASK_VERSION_ID(p_element_version_id IN number) return number
4657 is
4658 l_parent_task_id NUMBER := NULL;
4659 l_parent_task_version_id NUMBER := NULL;
4660
4661
4662 CURSOR c1 IS
4663 SELECT object_id_from1
4664 FROM pa_object_relationships
4665 WHERE object_id_to1 = p_element_version_id
4666 AND object_type_from='PA_TASKS'
4667 AND relationship_type = 'S';
4668
4669 begin
4670 OPEN c1;
4671 FETCH c1 into l_parent_task_version_id;
4672 CLOSE c1;
4673 return l_parent_task_version_id;
4674 exception
4675 when others then
4676 return(SQLCODE);
4677 end GET_PARENT_TASK_VERSION_ID;
4678
4679 function GET_TASK_VERSION_ID(
4680 p_structure_version_id IN NUMBER
4681 ,p_task_id IN NUMBER) return NUMBER
4682 is
4683 l_task_version_id NUMBER;
4684 begin
4685 select b.element_version_id into l_task_version_id from pa_proj_elements a, pa_proj_element_versions b
4686 where a.proj_element_id = b.proj_element_id
4687 and a.proj_element_id = p_task_id
4688 and b.parent_structure_version_id = p_structure_version_id;
4689
4690 return l_task_version_id;
4691 exception
4692 when others then
4693 -- return(SQLCODE);
4694 return to_number(NULL); --Bug 3646375
4695 end GET_TASK_VERSION_ID;
4696
4697 function GET_RELATIONSHIP_ID(
4698 p_object_id_from1 IN NUMBER
4699 ,p_object_id_to1 IN NUMBER) return NUMBER
4700 is
4701 l_relationship_id NUMBER;
4702 begin
4703 select object_relationship_id into l_relationship_id from pa_object_relationships
4704 where object_id_from1 = p_object_id_from1
4705 and object_id_to1 = p_object_id_to1
4706 and relationship_type = 'D';
4707
4708 return l_relationship_id;
4709 exception
4710 when others then
4711 -- return(SQLCODE);
4712 return to_number(NULL); --Bug 3646375
4713 end GET_RELATIONSHIP_ID;
4714
4715 FUNCTION check_task_parents_deliv(p_element_version_id IN number)
4716 RETURN VARCHAR2
4717 IS
4718 --
4719 CURSOR cur_check_deliv (cp_task_version_id number) IS
4720 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4721 FROM pa_proj_element_versions ppev,
4722 pa_proj_elements ppe
4723 WHERE ppe.project_id = ppev.project_id
4724 AND ppe.proj_element_id = ppev.proj_element_id
4725 AND ppev.object_type = 'PA_TASKS'
4726 AND ppe.object_type = 'PA_TASKS'
4727 AND ppev.element_version_id IN (
4728 SELECT object_id_to1
4729 FROM pa_object_relationships
4730 WHERE relationship_type = 'S'
4731 START WITH object_id_to1 = cp_task_version_id --24628
4732 AND object_type_to = 'PA_TASKS'
4733 and relationship_type = 'S'
4734 CONNECT BY PRIOR object_id_from1 = object_id_to1
4735 AND PRIOR object_type_from = object_type_to
4736 AND PRIOR relationship_type = relationship_type);
4737 --
4738 cur_check_deliv_rec cur_check_deliv%ROWTYPE;
4739 --
4740 -- l_err_msg VARCHAR2(80) :=NULL;
4741 l_err_msg VARCHAR2(1) :='N'; --Bug 3475920
4742 --
4743 BEGIN
4744 OPEN cur_check_deliv(p_element_version_id);
4745 LOOP
4746 FETCH cur_check_deliv INTO cur_check_deliv_rec;
4747 EXIT WHEN cur_check_deliv%NOTFOUND;
4748 IF cur_check_deliv_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4749 l_err_msg := 'Y';
4750 EXIT;
4751 END IF;
4752 END LOOP;
4753 RETURN l_err_msg;
4754 EXCEPTION
4755 WHEN OTHERS THEN
4756 RETURN(SQLERRM);
4757 END check_task_parents_deliv;
4758
4759 --Can be used for update, indent, Move task
4760 FUNCTION check_deliv_in_hierarchy(p_element_version_id IN number,
4761 p_target_element_version_id IN number)
4762 RETURN VARCHAR2
4763 IS
4764 CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
4765 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4766 FROM pa_proj_element_versions ppev,
4767 pa_proj_elements ppe
4768 WHERE ppe.project_id = ppev.project_id
4769 AND ppe.proj_element_id = ppev.proj_element_id
4770 AND ppev.object_type = 'PA_TASKS'
4771 AND ppe.object_type = 'PA_TASKS'
4772 AND ppev.element_version_id IN (
4773 SELECT object_id_to1
4774 FROM pa_object_relationships
4775 WHERE relationship_type = 'S'
4776 START WITH object_id_to1 = cp_target_task_version_id
4777 AND object_type_to = 'PA_TASKS'
4778 and relationship_type = 'S'
4779 CONNECT BY PRIOR object_id_from1 = object_id_to1
4780 AND PRIOR object_type_from = object_type_to
4781 AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4782
4783 CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
4784 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4785 FROM pa_proj_element_versions ppev,
4786 pa_proj_elements ppe
4787 WHERE ppe.project_id = ppev.project_id
4788 AND ppe.proj_element_id = ppev.proj_element_id
4789 AND ppev.object_type = 'PA_TASKS'
4790 AND ppe.object_type = 'PA_TASKS'
4791 AND ppev.element_version_id IN (
4792 SELECT object_id_to1
4793 FROM pa_object_relationships
4794 WHERE relationship_type = 'S'
4795 START WITH object_id_to1 = cp_task_version_id
4796 AND object_type_to = 'PA_TASKS'
4797 and relationship_type = 'S'
4798 CONNECT BY object_id_from1 = prior object_id_to1
4799 AND object_type_from = prior object_type_to
4800 AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4801
4802 cur_check_deliv_bt_rec cur_check_deliv_bt%ROWTYPE;
4803 cur_check_deliv_tb_rec cur_check_deliv_tb%ROWTYPE;
4804 -- l_err_msg VARCHAR2(80) :=NULL;
4805 l_err_msg VARCHAR2(1) :='N'; --Bug 3475920
4806 l_cnt_no_del_in_branch NUMBER:=0;
4807 BEGIN
4808 FOR cur_check_deliv_bt_rec in cur_check_deliv_bt(p_target_element_version_id)
4809 LOOP
4810 IF cur_check_deliv_bt_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4811 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
4812 EXIT;
4813 END IF;
4814 END LOOP;
4815 --
4816 FOR cur_check_deliv_tb_rec in cur_check_deliv_tb(p_element_version_id)
4817 LOOP
4818 IF cur_check_deliv_tb_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4819 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
4820 EXIT;
4821 END IF;
4822 END LOOP;
4823 --
4824 -- IF l_cnt_no_del_in_branch > 2 AND (p_element_version_id = p_target_element_version_id) THEN
4825 IF l_cnt_no_del_in_branch >= 2 AND (p_element_version_id = p_target_element_version_id) THEN
4826 l_err_msg:= 'Y';
4827 ELSIF l_cnt_no_del_in_branch > 1 AND (p_element_version_id <> p_target_element_version_id) THEN
4828 l_err_msg:= 'Y';
4829 END IF;
4830 RETURN l_err_msg;
4831 EXCEPTION
4832 WHEN OTHERS THEN
4833 RETURN(SQLERRM);
4834 END check_deliv_in_hierarchy;
4835 --
4836 FUNCTION check_sharedstruct_deliv(p_element_version_id IN number)
4837 RETURN VARCHAR2
4838 IS
4839 /* This cursor get all the leaf nodes for a given structure*/
4840 CURSOR get_leaf_node_cur(cp_structure_elem_id NUMBER) IS
4841 SELECT object_id_to1
4842 FROM pa_proj_element_versions ppev,
4843 pa_object_relationships rel1
4844 WHERE ppev.parent_structure_version_id = cp_structure_elem_id --19671
4845 AND rel1.relationship_type = 'S'
4846 AND ppev.element_version_id = rel1.object_id_to1
4847 AND NOT EXISTS (SELECT 'XYZ'
4848 FROM pa_object_relationships rel2
4849 WHERE rel2.object_id_from1 = rel1.object_id_to1);
4850 get_leaf_node_rec get_leaf_node_cur%ROWTYPE;
4851
4852 /* This cursor goes from leaf node to top of the branch*/
4853 CURSOR check_for_deliv_cur (cp_task_version_id number) IS
4854 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4855 FROM pa_proj_element_versions ppev,
4856 pa_proj_elements ppe
4857 WHERE ppe.project_id = ppev.project_id
4858 AND ppe.proj_element_id = ppev.proj_element_id
4859 AND ppev.object_type = 'PA_TASKS'
4860 AND ppe.object_type = 'PA_TASKS'
4861 AND ppev.element_version_id IN (
4862 SELECT object_id_to1
4863 FROM pa_object_relationships
4864 WHERE relationship_type = 'S'
4865 START WITH object_id_to1 = cp_task_version_id --24628
4866 AND object_type_to = 'PA_TASKS'
4867 and relationship_type = 'S'
4868 CONNECT BY PRIOR object_id_from1 = object_id_to1
4869 AND PRIOR object_type_from = object_type_to
4870 AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4871 check_for_deliv_rec check_for_deliv_cur%ROWTYPE;
4872 branch_deliv_count NUMBER:=0;
4873 l_err_msg VARCHAR2(1) :='N';
4874 BEGIN
4875 -- OPEN get_leaf_node_cur(p_structure_elem_id);
4876 OPEN get_leaf_node_cur(p_element_version_id);
4877 LOOP
4878 FETCH get_leaf_node_cur INTO get_leaf_node_rec;
4879 EXIT WHEN get_leaf_node_cur%NOTFOUND;
4880 --
4881 IF branch_deliv_count = 2 THEN
4882 EXIT;
4883 END IF;
4884 --
4885 OPEN check_for_deliv_cur(get_leaf_node_rec.object_id_to1);
4886 LOOP
4887 FETCH check_for_deliv_cur INTO check_for_deliv_rec;
4888 IF check_for_deliv_cur%NOTFOUND THEN
4889 IF branch_deliv_count < 2 THEN
4890 branch_deliv_count:=0;
4891 END IF;
4892 EXIT;
4893 END IF;
4894 --
4895 IF check_for_deliv_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
4896 branch_deliv_count := branch_deliv_count + 1;
4897 END IF;
4898 IF branch_deliv_count = 2 THEN
4899 l_err_msg := 'Y';
4900 EXIT;
4901 END IF;
4902 --
4903 END LOOP; --End loop for check_for_deliv_cur cursor
4904 CLOSE check_for_deliv_cur;
4905 --
4906 END LOOP; --End loop for get_leaf_node_cur cursor
4907 CLOSE get_leaf_node_cur;
4908 --
4909 RETURN l_err_msg;
4910 --
4911 EXCEPTION
4912 WHEN OTHERS THEN
4913 RETURN(SQLERRM);
4914 END check_sharedstruct_deliv;
4915
4916 FUNCTION IS_WF_PROCESS_RUNNING(p_proj_element_id IN number)
4917 RETURN VARCHAR2
4918 IS
4919 CURSOR C
4920 IS
4921 SELECT 'Y'
4922 FROM pa_wf_processes pwp, pa_proj_elements ppe -- Bug #3967939
4923 WHERE pwp.ENTITY_KEY2 = to_char(p_proj_element_id) -- Bug#3619754 : Added to_char
4924 AND ppe.PROJ_ELEMENT_ID = p_proj_element_id -- Bug #3967939
4925 AND pwp.ITEM_TYPE = ppe.WF_ITEM_TYPE; -- Bug #3967939
4926
4927 l_dummy VARCHAR2(1) := 'N' ;
4928 BEGIN
4929 OPEN C;
4930 FETCH C INTO l_dummy ;
4931 IF C%NOTFOUND THEN
4932 l_dummy := 'N' ;
4933 END IF;
4934 CLOSE C;
4935 return l_dummy ;
4936 EXCEPTION
4937 WHEN OTHERS THEN
4938 return 'N' ;
4939 END IS_WF_PROCESS_RUNNING ;
4940
4941 FUNCTION GET_ELEMENT_WF_ITEMKEY(p_proj_element_id IN number,
4942 p_project_id IN number, p_wf_type_code IN VARCHAR2 := 'TASK_EXECUTION')
4943 RETURN VARCHAR2
4944
4945 IS
4946 CURSOR C
4947 IS
4948 select max(item_key)
4949 from pa_wf_processes wp
4950 , pa_proj_elements pe
4951 where wp.item_type = pe.wf_item_type
4952 and wp.wf_type_code = p_wf_type_code
4953 and to_char(pe.proj_element_id) = wp.entity_key2 --Bug 3619754 Added By avaithia
4954 and to_char(pe.project_id) = wp.entity_key1 --Bug 3619754 Added By avaithia
4955 and pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
4956
4957 l_item_key VARCHAR2(240) := '' ;
4958 BEGIN
4959 OPEN C;
4960 FETCH C INTO l_item_key ;
4961 CLOSE C;
4962 return l_item_key ;
4963 EXCEPTION
4964 WHEN OTHERS THEN
4965 return '' ;
4966 END GET_ELEMENT_WF_ITEMKEY;
4967
4968 FUNCTION GET_ELEMENT_WF_STATUS(p_proj_element_id IN number,
4969 p_project_id IN number, p_wf_type_code IN VARCHAR2 := 'TASK_EXECUTION')
4970 RETURN VARCHAR2
4971
4972 IS
4973 CURSOR c_item_type
4974 IS
4975 select wf_item_type
4976 from pa_proj_elements pe
4977 where
4978 pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
4979
4980 l_status VARCHAR2(240) := '';
4981 l_item_key VARCHAR2(240) := '';
4982 l_wf_status VARCHAR2(240) := '';
4983 l_item_type VARCHAR2(240) := '';
4984 l_result VARCHAR2(240) := '';
4985
4986 BEGIN
4987
4988 open c_item_type;
4989 FETCH c_item_type INTO l_item_type;
4990 CLOSE c_item_type;
4991
4992 if ( l_item_type is not null) then -- Commented this for Bug 4249993 and l_item_type <> '') then
4993 l_item_key := GET_ELEMENT_WF_ITEMKEY(p_proj_element_id,
4994 p_project_id, p_wf_type_code);
4995
4996 if (l_item_key is not null) then -- Commented this for Bug 4249993 and l_item_key <> '') then
4997 WF_ENGINE.ItemStatus
4998 (l_item_type,
4999 l_item_key,
5000 l_status ,
5001 l_result );
5002 end if;
5003 end if;
5004 return l_status;
5005
5006 EXCEPTION
5007 WHEN OTHERS THEN
5008 return '' ;
5009 END GET_ELEMENT_WF_STATUS;
5010
5011 -- Function : check_fin_or_wp_structure
5012 -- Purpose : Checks whether the passed proj_element_id record is a WP or FIN structure record
5013 -- Parameters Type Required Description and Purpose
5014 -- --------------------------- ------ -------- --------------------------------------------------------
5015 -- p_proj_element_id NUMBER Y The proj_element_id to be checked
5016 FUNCTION check_fin_or_wp_structure( p_proj_element_id IN NUMBER ) RETURN VARCHAR2 IS
5017 CURSOR cur_chk_fin_or_wp_structure IS
5018 SELECT 'Y'
5019 FROM pa_proj_elements ppe
5020 ,pa_proj_structure_types ppst
5021 ,pa_structure_types pst
5022 WHERE ppe.proj_element_id = p_proj_element_id
5023 AND ppe.object_type = 'PA_STRUCTURES'
5024 AND ppe.proj_element_id = ppst.proj_element_id
5025 AND ppst.structure_type_id = pst.structure_type_id
5026 AND pst.structure_type IN ('WORKPLAN','FINANCIAL') ;
5027
5028 l_return_flag VARCHAR2(1);
5029 BEGIN
5030 OPEN cur_chk_fin_or_wp_structure;
5031 FETCH cur_chk_fin_or_wp_structure INTO l_return_flag;
5032 CLOSE cur_chk_fin_or_wp_structure;
5033
5034 RETURN nvl(l_return_flag,'N');
5035 EXCEPTION
5036 WHEN OTHERS THEN
5037 RETURN '';
5038 END;
5039
5040 FUNCTION CHECK_USER_VIEW_TASK_PRIVILEGE
5041 (
5042 p_project_id IN NUMBER
5043 ) RETURN VARCHAR2
5044 IS
5045 l_ret_code VARCHAR2(1) ;
5046 BEGIN
5047 l_ret_code := PA_SECURITY_PVT.check_user_privilege
5048 ( p_privilege => 'PA_PAXPREPR_OPT_WORKPLAN_STR_V'
5049 ,p_object_name => 'PA_PROJECTS'
5050 ,p_object_key => p_project_id
5051 ) ;
5052 RETURN l_ret_code ;
5053 END CHECK_USER_VIEW_TASK_PRIVILEGE;
5054 --
5055 --
5056 function GET_SUB_TASK_VERSION_ID(p_task_version_id IN number) return number
5057 is
5058 l_sub_task_id NUMBER := NULL;
5059 l_sub_task_version_id NUMBER := NULL;
5060 --
5061 --
5062 CURSOR c1 IS
5063 SELECT object_id_to1
5064 FROM pa_object_relationships
5065 WHERE object_id_from1 = p_task_version_id
5066 AND object_type_to='PA_TASKS'
5067 AND relationship_type = 'S';
5068 --
5069 begin
5070 OPEN c1;
5071 FETCH c1 into l_sub_task_version_id;
5072 CLOSE c1;
5073 return l_sub_task_version_id;
5074 exception
5075 when others then
5076 return(SQLCODE);
5077 end GET_SUB_TASK_VERSION_ID;
5078 --
5079 --
5080 FUNCTION check_deliv_in_hie_upd(p_task_version_id IN number)
5081 RETURN NUMBER
5082 IS
5083 CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
5084 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5085 FROM pa_proj_element_versions ppev,
5086 pa_proj_elements ppe
5087 WHERE ppe.project_id = ppev.project_id
5088 AND ppe.proj_element_id = ppev.proj_element_id
5089 AND ppev.object_type = 'PA_TASKS'
5090 AND ppe.object_type = 'PA_TASKS'
5091 AND ppev.element_version_id IN (
5092 SELECT object_id_to1
5093 FROM pa_object_relationships
5094 WHERE relationship_type = 'S'
5095 START WITH object_id_to1 = cp_target_task_version_id
5096 AND object_type_to = 'PA_TASKS'
5097 and relationship_type = 'S'
5098 CONNECT BY PRIOR object_id_from1 = object_id_to1
5099 AND PRIOR object_type_from = object_type_to
5100 AND PRIOR relationship_type = RELATIONSHIP_TYPE);
5101
5102 CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
5103 SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5104 FROM pa_proj_element_versions ppev,
5105 pa_proj_elements ppe
5106 WHERE ppe.project_id = ppev.project_id
5107 AND ppe.proj_element_id = ppev.proj_element_id
5108 AND ppev.object_type = 'PA_TASKS'
5109 AND ppe.object_type = 'PA_TASKS'
5110 AND ppev.element_version_id IN (
5111 SELECT object_id_to1
5112 FROM pa_object_relationships
5113 WHERE relationship_type = 'S'
5114 START WITH object_id_to1 = cp_task_version_id
5115 AND object_type_to = 'PA_TASKS'
5116 and relationship_type = 'S'
5117 CONNECT BY object_id_from1 = prior object_id_to1
5118 AND object_type_from = prior object_type_to
5119 AND PRIOR relationship_type = RELATIONSHIP_TYPE);
5120
5121 cur_check_deliv_bt_rec cur_check_deliv_bt%ROWTYPE;
5122 cur_check_deliv_tb_rec cur_check_deliv_tb%ROWTYPE;
5123 -- l_err_msg VARCHAR2(80) :=NULL;
5124 l_err_msg VARCHAR2(1) :='N'; --Bug 3475920
5125 l_cnt_no_del_in_branch NUMBER:=0;
5126 l_parent_task_ver_id NUMBER:=NULL;
5127 l_sub_task_ver_id NUMBER:=NULL;
5128 BEGIN
5129 --
5130 l_parent_task_ver_id:=GET_PARENT_TASK_VERSION_ID(p_task_version_id);
5131 l_sub_task_ver_id:=GET_SUB_TASK_VERSION_ID(p_task_version_id);
5132 --
5133 IF l_parent_task_ver_id IS NOT NULL THEN
5134 FOR cur_check_deliv_bt_rec in cur_check_deliv_bt(l_parent_task_ver_id)
5135 LOOP
5136 IF cur_check_deliv_bt_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
5137 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
5138 EXIT;
5139 END IF;
5140 END LOOP;
5141 END IF;
5142 --
5143 IF l_sub_task_ver_id IS NOT NULL THEN
5144 FOR cur_check_deliv_tb_rec in cur_check_deliv_tb(l_sub_task_ver_id)
5145 LOOP
5146 IF cur_check_deliv_tb_rec.base_percent_comp_deriv_code LIKE 'DELIVERABLE' THEN
5147 l_cnt_no_del_in_branch := l_cnt_no_del_in_branch + 1;
5148 EXIT;
5149 END IF;
5150 END LOOP;
5151 END IF;
5152 --
5153 RETURN l_cnt_no_del_in_branch;
5154 EXCEPTION
5155 WHEN OTHERS THEN
5156 RETURN(SQLERRM);
5157 END check_deliv_in_hie_upd;
5158 --
5159 --
5160 -- FUNCTION check_pa_lookup_exists
5161 -- PURPOSE Checks whether the passed lookup_code and value are valid
5162 -- RETURN VALUE VARCHAR2 - 'Y' if the valid
5163 -- 'N' otherwise.
5164 --
5165 Function check_pa_lookup_exists(p_lookup_type VARCHAR2,
5166 p_lookup_code VARCHAR2)
5167 RETURN VARCHAR2
5168 IS
5169 CURSOR chk_lkp(cp_lookup_type VARCHAR2, cp_lookup_code VARCHAR2)
5170 IS
5171 SELECT 'Y'
5172 FROM pa_lookups
5173 WHERE lookup_type = cp_lookup_type
5174 AND lookup_code = cp_lookup_code;
5175 l_dummy varchar2(1):='Y';
5176 BEGIN
5177 --
5178 OPEN chk_lkp(p_lookup_type,p_lookup_code);
5179 FETCH chk_lkp INTO l_dummy;
5180 --
5181 IF chk_lkp%NOTFOUND THEN
5182 l_dummy:= 'N';
5183 END IF;
5184 --
5185 CLOSE chk_lkp;
5186 --
5187 RETURN l_dummy;
5188 --
5189 END check_pa_lookup_exists;
5190 --
5191 --
5192
5193 function GET_TASK_ID(
5194 p_project_id IN NUMBER
5195 ,p_structure_version_id IN NUMBER
5196 ,p_task_version_id IN NUMBER) return NUMBER
5197 IS
5198 l_task_id NUMBER;
5199 begin
5200 select b.proj_element_id into l_task_id
5201 from pa_proj_element_versions b
5202 where b.element_version_id = p_task_version_id
5203 and b.project_id = p_project_id
5204 and b.parent_structure_version_id = p_structure_version_id;
5205
5206 return l_task_id;
5207 exception
5208 when others then
5209 return to_number(NULL);
5210 end GET_TASK_ID;
5211
5212 -- Begin fix for Bug # 4237838.
5213
5214 function is_lowest_level_fin_task(p_project_id NUMBER
5215 , p_task_version_id NUMBER
5216 , p_include_sub_proj_flag VARCHAR2 := 'Y') -- Fix for Bug # 4290042.
5217 return VARCHAR2
5218 is
5219
5220 cursor cur_fin_task(c_project_id NUMBER
5221 , c_task_version_id NUMBER)
5222 is
5223 select ppev.financial_task_flag
5224 from pa_proj_element_versions ppev
5225 where ppev.project_id = c_project_id
5226 and ppev.element_version_id = c_task_version_id;
5227
5228 l_financial_task_flag VARCHAR2(1) := null;
5229
5230 cursor cur_lowest_level_fin_task (c_project_id NUMBER
5231 , c_task_version_id NUMBER
5232 , c_include_sub_proj_flag VARCHAR2) -- Fix for Bug # 4290042.
5233 is
5234 -- This query checks if the task version has a financial sub-task.
5235 select 'N'
5236 from pa_object_relationships por1, pa_proj_element_versions ppev1
5237 where por1.object_id_to1 = ppev1.element_version_id
5238 and por1.relationship_type = 'S'
5239 and ppev1.project_id = c_project_id
5240 and por1.object_id_from1 = c_task_version_id
5241 and ppev1.financial_task_flag = 'Y'
5242 union all
5243 -- This query checks if the task version has a linking sub-task that has a financial link to
5244 -- a sub-project if the input p_include_sub_proj_flag = 'Y'.
5245 select 'N'
5246 from pa_object_relationships por2, pa_proj_element_versions ppev2
5247 where por2.object_id_to1 = ppev2.element_version_id
5248 and por2.relationship_type = 'S'
5249 and ppev2.project_id = c_project_id
5250 and por2.object_id_from1 = c_task_version_id
5251 and exists (select 'Y'
5252 from pa_object_relationships por3
5253 where por3.object_id_from1 = ppev2.element_version_id
5254 and por3.object_id_from2 = ppev2.project_id
5255 and por3.relationship_type = 'LF')
5256 and c_include_sub_proj_flag = 'Y'; -- Fix for Bug # 4290042.
5257
5258
5259 l_lowest_level_fin_task VARCHAR2(1) := null;
5260
5261 l_return VARCHAR2(1) := null;
5262
5263 begin
5264
5265 l_return := 'Y';
5266
5267 -- Check if the Task is a Financial Task.
5268
5269 open cur_fin_task(p_project_id, p_task_version_id);
5270
5271 fetch cur_fin_task into l_financial_task_flag;
5272
5273 close cur_fin_task;
5274
5275 if l_financial_task_flag = 'N' then
5276
5277 l_return := 'N';
5278
5279 else
5280
5281
5282 -- Check if the Financial Task is a Lowest Level Financial Task.
5283
5284
5285 open cur_lowest_level_fin_task (p_project_id, p_task_version_id, p_include_sub_proj_flag);
5286 -- Fix for Bug # 4290042.
5287
5288 fetch cur_lowest_level_fin_task into l_lowest_level_fin_task;
5289
5290 if cur_lowest_level_fin_task%FOUND then
5291
5292 l_return := 'N';
5293
5294 end if;
5295
5296 close cur_lowest_level_fin_task;
5297
5298 end if;
5299
5300 return(l_return);
5301
5302 end is_lowest_level_fin_task;
5303
5304 -- End fix for Bug # 4237838.
5305
5306 -- Bug 4667361: Added this Function
5307 FUNCTION WP_STR_EXISTS_FOR_UPG
5308 (
5309 p_project_id IN NUMBER
5310 ) RETURN VARCHAR2 IS
5311 l_return_value VARCHAR2(1) := 'N';
5312 l_dummy_char VARCHAR2(1) := 'N';
5313
5314 CURSOR cur_pa_proj
5315 IS
5316
5317 SELECT 'x'
5318 FROM pa_proj_elements ppe, pa_proj_structure_types ppst
5319 WHERE ppe.project_id = p_project_id
5320 AND ppe.object_type = 'PA_STRUCTURES'
5321 AND ppe.proj_element_id = ppst.proj_element_id
5322 AND ppst.structure_type_id = 1; --'WORKPLAN'
5323
5324 BEGIN
5325
5326 open cur_pa_proj;
5327 fetch cur_pa_proj INTO l_dummy_char;
5328 IF cur_pa_proj%FOUND
5329 THEN
5330 l_return_value := 'Y';
5331 ELSE
5332 l_return_value := 'N';
5333 END IF;
5334 CLOSE cur_pa_proj;
5335
5336 RETURN ( NVL( l_return_value, 'N' ) );
5337
5338 END WP_STR_EXISTS_FOR_UPG;
5339
5340 -- Bug 4667361: Added this Function
5341 FUNCTION CHECK_SHARING_ENABLED_FOR_UPG
5342 ( p_project_id IN NUMBER
5343 ) return VARCHAR2
5344 IS
5345 CURSOR c1 IS
5346 SELECT 'Y'
5347 FROM pa_proj_elements a,
5348 pa_proj_structure_types b,
5349 pa_structure_types c,
5350 pa_proj_structure_types d,
5351 pa_structure_types e
5352 WHERE c.structure_type_class_code = 'WORKPLAN'
5353 AND e.structure_type_class_code = 'FINANCIAL'
5354 AND c.structure_type_id = b.structure_type_id
5355 AND e.structure_type_id = d.structure_type_id
5356 AND b.proj_element_id = a.proj_element_id
5357 AND d.proj_element_id = a.proj_element_id
5358 AND a.project_id = p_project_id;
5359
5360 l_dummy VARCHAR2(1);
5361 BEGIN
5362 OPEN c1;
5363 FETCH c1 into l_dummy;
5364 IF c1%NOTFOUND THEN
5365 l_dummy := 'N';
5366 END IF;
5367 CLOSE c1;
5368 return l_dummy;
5369
5370 END CHECK_SHARING_ENABLED_FOR_UPG;
5371
5372
5373 -- Procedure included for perf fix 4903460
5374 ---------------------------------------------
5375 -- Does the following validations :
5376 ---------------------------------------------
5377 -- Check if task has expenditure item
5378 -- Check if task has purchase order distribution
5379 -- Check if task has purchase order requisition
5380 -- Check if task has supplier invoices
5381 -- check if task has supplier invoice distribution
5382 -- Check if task has commitment transaction
5383 -- Check if task has compensation rule set
5384 -- Check if task has draft invoices
5385 -- Check if task has Project_customers
5386
5387 PROCEDURE perform_task_validations
5388 (
5389 p_project_id IN NUMBER,
5390 p_task_id IN NUMBER,
5391 x_error_code OUT NOCOPY NUMBER,
5392 x_error_msg_code OUT NOCOPY VARCHAR2
5393 )
5394 IS
5395 l_user_id NUMBER;
5396 l_login_id NUMBER;
5397 l_debug_mode VARCHAR2(1);
5398
5399 l_debug_level3 CONSTANT NUMBER := 3;
5400 l_debug_level5 CONSTANT NUMBER := 5;
5401
5402
5403 /*CURSOR c_tasks_in_hierarchy IS --Commented the following cursor as this is not needed after perf bug fix Bug#4964992
5404 SELECT TASK_ID
5405 FROM PA_TASKS
5406 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
5407 AND PROJECT_ID = p_project_id
5408 START WITH TASK_ID = p_TASK_ID
5409 AND PROJECT_ID = p_project_id; */
5410
5411 /*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.
5412 l_dummy number:=0;
5413 l_task_tbl sub_task; --Added this for Bug#4964992.
5414
5415 l_alt_task_Ids SYSTEM.pa_num_tbl_type := SYSTEM.PA_NUM_TBL_TYPE(); --bug#16461684
5416 l_valid_status_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE(); --bug#16461684
5417 l_Cbs_Enabled VARCHAR2(1) := 'N'; -- bug#16461684
5418 l_alt_task_tbl sub_task; --bug#16461684
5419 l_tmp_task_tbl sub_task; --bug#16461684
5420
5421 cursor alt_task_cur ( l_task_tbl sub_task ) is
5422 select alt_task_id from pa_alternate_tasks,table(cast(l_task_tbl as sub_task)) st
5423 where proj_element_id = st.task_id ;
5424
5425 BEGIN
5426 x_error_code := 0;
5427 x_error_msg_code := NULL ;
5428
5429 l_user_id := fnd_global.user_id;
5430 l_login_id := fnd_global.login_id;
5431 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
5432
5433 IF l_debug_mode = 'Y' THEN
5434 PA_DEBUG.set_curr_function( p_function =>'PERFORM_TASK_VALIDATIONS' , p_debug_mode => l_debug_mode );
5435 END IF;
5436
5437 IF l_debug_mode = 'Y' THEN
5438 Pa_Debug.g_err_stage:= 'TASK_VALIDATIONS : Printing Input parameters';
5439 Pa_Debug.WRITE('pa_proj_tsk_utils', Pa_Debug.g_err_stage ,l_debug_level3 );
5440 Pa_Debug.WRITE('pa_proj_tsk_utils', 'p_task_id'||':'||p_task_id , l_debug_level3);
5441 END IF;
5442
5443 l_Cbs_Enabled := PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(p_Project_Id => p_project_id); -- bug#16461684
5444
5445 /*OPEN c_tasks_in_hierarchy; -- Commented this for Bug#4964992
5446 FETCH c_tasks_in_hierarchy BULK COLLECT INTO l_task_id_tbl;
5447 CLOSE c_tasks_in_hierarchy;*/
5448
5449 l_task_tbl := get_task_hierarchy(p_project_id,p_task_id); -- Added this call for Bug#4964992
5450
5451 /* CBS enhancement bug#16461684*/
5452 l_tmp_task_tbl := l_task_tbl;
5453
5454 IF l_Cbs_Enabled = 'Y' THEN
5455 l_alt_task_tbl := get_Alt_task_List(p_project_id,p_task_id);
5456 END IF;
5457
5458 IF nvl(l_task_tbl.COUNT,0)>0 THEN
5459 /*FOR i IN l_task_id_tbl.FIRST..l_task_id_tbl.LAST --Commented by Sunkalya for perf fix Bug#4964992
5460 LOOP */
5461
5462 /* CBS enhancement bug#16461684*/
5463 IF l_Cbs_Enabled = 'Y' THEN
5464 open alt_task_cur ( l_task_tbl );
5465 fetch alt_task_cur
5466 bulk collect
5467 into l_alt_task_Ids;
5468 close alt_task_cur;
5469 IF (l_alt_task_Ids.COUNT > 0) THEN
5470 PA_ALTERNATE_TASK_PVT.Validate_Del_Alt_Tasks(
5471 p_Alt_Task_Id => l_alt_task_Ids,
5472 p_context => 'TASK',
5473 X_Valid_Status => l_valid_status_tbl
5474 );
5475 FOR i IN l_alt_task_Ids.FIRST .. l_alt_task_Ids.LAST LOOP
5476 IF l_valid_status_tbl(i) <> 'V' THEN -- Delete records which are in-valid
5477 x_error_code :=49;
5478 x_error_msg_code := 'PA_CBS_ALT_TSK_USED';
5479 return;
5480
5481 END IF;
5482
5483 END LOOP;
5484 END IF;
5485 END IF;
5486
5487
5488 --Check if task has expenditure item
5489 BEGIN
5490
5491 l_dummy := 0;
5492 /* commented for 16844815
5493 IF l_Cbs_Enabled = 'Y' THEN -- CBS enhancement bug#16461684
5494 SELECT
5495 1 into l_dummy
5496 FROM
5497 sys.dual
5498 WHERE
5499 exists (SELECT NULL
5500 FROM PA_EXPENDITURE_ITEMS_all pei,table(cast(l_alt_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5501 WHERE pei.TASK_ID = st.task_id)
5502 or exists (SELECT NULL
5503 FROM PA_EI_DENORM ped, table(cast(l_task_tbl as sub_task)) st
5504 WHERE ped.TASK_ID = st.task_id);
5505 ELSE */
5506 SELECT
5507 1 into l_dummy
5508 FROM
5509 sys.dual
5510 WHERE
5511 exists (SELECT NULL
5512 FROM PA_EXPENDITURE_ITEMS_all pei,table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5513 WHERE pei.TASK_ID = st.task_id)
5514 or exists (SELECT NULL
5515 FROM PA_EI_DENORM ped, table(cast(l_task_tbl as sub_task)) st
5516 WHERE ped.TASK_ID = st.task_id);
5517 --END IF; commented for 16844815
5518 IF l_dummy = 1 THEN
5519 x_error_code :=50;
5520 x_error_msg_code := 'PA_TSK_EXP_ITEM_EXIST';
5521 return;
5522 END IF;
5523
5524 EXCEPTION
5525 WHEN NO_DATA_FOUND THEN
5526 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS : No Expenditure Items exist in the entire task hierarchy';
5527 IF l_debug_mode = 'Y' THEN
5528 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5529 END IF;
5530 WHEN OTHERS THEN
5531 x_error_code := SQLCODE;
5532 x_error_msg_code := substrb(SQLERRM,1,120);
5533 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking Expenditure Items';
5534
5535 IF l_debug_mode = 'Y' THEN
5536 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5537 END IF;
5538 return;
5539 END;
5540
5541 -- Check if task has purchase order distribution
5542 BEGIN
5543 l_dummy := 0;
5544
5545 /* CBS enhancement bug#16461684*/
5546 l_task_tbl := l_tmp_task_tbl;
5547 IF l_Cbs_Enabled = 'Y' THEN
5548 l_task_tbl := l_alt_task_tbl;
5549 END IF;
5550 SELECT
5551 1 into l_dummy
5552 FROM
5553 sys.dual
5554 WHERE
5555 exists (SELECT NULL
5556 FROM po_distributions_all poa, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5557 where poa.project_id = p_project_id
5558 AND poa.TASK_ID = st.task_id);
5559
5560 IF l_dummy = 1 THEN
5561 x_error_code :=60;
5562 x_error_msg_code := 'PA_TSK_PO_DIST_EXIST';
5563 return;
5564 END IF;
5565
5566 EXCEPTION
5567 WHEN NO_DATA_FOUND THEN
5568 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No purchase order distribution exist in the entire task hierarchy';
5569 IF l_debug_mode = 'Y' THEN
5570 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5571 END IF;
5572 WHEN OTHERS THEN
5573 x_error_code := SQLCODE;
5574 x_error_msg_code := substrb(SQLERRM,1,120);
5575 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking purchase order distribution';
5576
5577 IF l_debug_mode = 'Y' THEN
5578 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5579 END IF;
5580
5581 return;
5582 END;
5583
5584 -- Check if task has purchase order requisition
5585 BEGIN
5586 l_dummy := 0;
5587 /* CBS enhancement bug#16461684*/
5588 l_task_tbl := l_tmp_task_tbl;
5589 IF l_Cbs_Enabled = 'Y' THEN
5590 l_task_tbl := l_alt_task_tbl;
5591 END IF;
5592 SELECT
5593 1 into l_dummy
5594 FROM
5595 sys.dual
5596 WHERE
5597 exists (SELECT NULL
5598 FROM po_req_distributions_all prd, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5599 where prd.project_id = p_project_id
5600 AND prd.TASK_ID = st.task_id);
5601
5602 IF l_dummy = 1 THEN
5603 x_error_code :=70;
5604 x_error_msg_code :='PA_TSK_PO_REQ_DIST_EXIST';
5605 return;
5606 END IF;
5607
5608 EXCEPTION
5609 WHEN NO_DATA_FOUND THEN
5610 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No purchase order requisition exist in the entire task hierarchy';
5611 IF l_debug_mode = 'Y' THEN
5612 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5613 END IF;
5614 WHEN OTHERS THEN
5615 x_error_code := SQLCODE;
5616 x_error_msg_code := substrb(SQLERRM,1,120);
5617 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking purchase order requisition';
5618
5619 IF l_debug_mode = 'Y' THEN
5620 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5621 END IF;
5622
5623 return;
5624 END;
5625
5626 -- Check if task has supplier invoices
5627 BEGIN
5628 l_dummy := 0;
5629 /* CBS enhancement bug#16461684*/
5630 l_task_tbl := l_tmp_task_tbl;
5631 IF l_Cbs_Enabled = 'Y' THEN
5632 l_task_tbl := l_alt_task_tbl;
5633 END IF;
5634 SELECT
5635 1 into l_dummy
5636 FROM
5637 sys.dual
5638 WHERE
5639 exists (SELECT NULL
5640 FROM ap_invoices_all aia, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5641 where aia.project_id = p_project_id
5642 AND aia.TASK_ID = st.task_id);
5643
5644 IF l_dummy = 1 THEN
5645 x_error_code :=80;
5646 x_error_msg_code :='PA_TSK_AP_INV_EXIST';
5647 return;
5648 END IF;
5649
5650 EXCEPTION
5651 WHEN NO_DATA_FOUND THEN
5652 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No supplier invoices exist in the entire task hierarchy' ;
5653 IF l_debug_mode = 'Y' THEN
5654 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5655 END IF;
5656 WHEN OTHERS THEN
5657 x_error_code := SQLCODE;
5658 x_error_msg_code := substrb(SQLERRM,1,120);
5659 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking supplier invoices';
5660
5661 IF l_debug_mode = 'Y' THEN
5662 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5663 END IF;
5664 return;
5665 END;
5666
5667 -- check if task has supplier invoice distribution
5668 BEGIN
5669 l_dummy := 0;
5670 /* CBS enhancement bug#16461684*/
5671 l_task_tbl := l_tmp_task_tbl;
5672 IF l_Cbs_Enabled = 'Y' THEN
5673 l_task_tbl := l_alt_task_tbl;
5674 END IF;
5675 SELECT
5676 1 into l_dummy
5677 FROM
5678 sys.dual
5679 WHERE
5680 exists (SELECT NULL
5681 FROM ap_invoice_distributions_all aid, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5682 where aid.project_id = p_project_id
5683 AND aid.TASK_ID = st.task_id);
5684
5685 IF l_dummy = 1 THEN
5686 x_error_code :=90;
5687 x_error_msg_code :='PA_TSK_AP_INV_DIST_EXIST';
5688 return;
5689 END IF;
5690
5691 EXCEPTION
5692 WHEN NO_DATA_FOUND THEN
5693 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No supplier invoice distribution exist';
5694 IF l_debug_mode = 'Y' THEN
5695 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5696 END IF;
5697 WHEN OTHERS THEN
5698 x_error_code := SQLCODE;
5699 x_error_msg_code := substrb(SQLERRM,1,120);
5700 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking supplier invoice distribution' ;
5701
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 return;
5706 END;
5707
5708 -- Check if task has commitment transaction
5709 BEGIN
5710 l_dummy := 0;
5711 /* CBS enhancement bug#16461684*/
5712 l_task_tbl := l_tmp_task_tbl;
5713 SELECT
5714 1 into l_dummy
5715 FROM
5716 sys.dual
5717 WHERE
5718 exists (SELECT NULL
5719 FROM pa_commitment_txns pct, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5720 where pct.project_id = p_project_id
5721 AND pct.TASK_ID = st.task_id);
5722
5723 IF l_dummy = 1 THEN
5724 x_error_code :=110;
5725 x_error_msg_code :='PA_TSK_CMT_TXN_EXIST';
5726 return;
5727 END IF;
5728
5729 EXCEPTION
5730 WHEN NO_DATA_FOUND THEN
5731 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No commitment transaction exist';
5732 IF l_debug_mode = 'Y' THEN
5733 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5734 END IF;
5735 WHEN OTHERS THEN
5736 x_error_code := SQLCODE;
5737 x_error_msg_code := substrb(SQLERRM,1,120);
5738 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking commitment transaction';
5739
5740 IF l_debug_mode = 'Y' THEN
5741 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5742 END IF;
5743 return;
5744 END;
5745
5746 -- Check if task has compensation rule set
5747 BEGIN
5748 l_dummy := 0;
5749 /* CBS enhancement bug#16461684*/
5750 l_task_tbl := l_tmp_task_tbl;
5751 SELECT
5752 1 into l_dummy
5753 FROM
5754 sys.dual
5755 WHERE
5756 exists (SELECT NULL
5757 FROM pa_comp_rule_ot_defaults_all pcr, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5758 where pcr.project_id = p_project_id
5759 AND pcr.TASK_ID = st.task_id)
5760 or exists (SELECT NULL
5761 FROM pa_org_labor_sch_rule pol, table(cast(l_task_tbl as sub_task)) st
5762 where overtime_project_id = p_project_id
5763 AND pol.overtime_TASK_ID = st.task_id);
5764
5765 IF l_dummy = 1 THEN
5766 x_error_code :=120;
5767 x_error_msg_code :='PA_TSK_COMP_RULE_SET_EXIST';
5768 return;
5769 END IF;
5770
5771 EXCEPTION
5772 WHEN NO_DATA_FOUND THEN
5773 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No compensation rule set exist';
5774 IF l_debug_mode = 'Y' THEN
5775 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5776 END IF;
5777 WHEN OTHERS THEN
5778 x_error_code := SQLCODE;
5779 x_error_msg_code := substrb(SQLERRM,1,120);
5780 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking compensation rule set';
5781
5782 IF l_debug_mode = 'Y' THEN
5783 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5784 END IF;
5785 return;
5786 END;
5787
5788 -- Check if task has draft invoices
5789 BEGIN
5790 l_dummy := 0;
5791 /* CBS enhancement bug#16461684*/
5792 l_task_tbl := l_tmp_task_tbl;
5793 IF l_Cbs_Enabled = 'Y' THEN
5794 l_task_tbl := l_alt_task_tbl;
5795 END IF;
5796 SELECT
5797 1 into l_dummy
5798 FROM
5799 sys.dual
5800 WHERE
5801 exists (SELECT NULL
5802 FROM pa_draft_invoice_details_all pdi, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5803 where pdi.CC_TAX_TASK_ID =st.task_id
5804 and pdi.project_id = p_project_id); -- Added for bug 8530541
5805
5806 IF l_dummy = 1 THEN
5807 x_error_code :=160;
5808 x_error_msg_code := 'PA_TSK_CC_DINV_EXIST';
5809 return;
5810 END IF;
5811
5812 EXCEPTION
5813 WHEN NO_DATA_FOUND THEN
5814 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No draft invoice exist';
5815 IF l_debug_mode = 'Y' THEN
5816 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5817 END IF;
5818 WHEN OTHERS THEN
5819 x_error_code := SQLCODE;
5820 x_error_msg_code := substrb(SQLERRM,1,120);
5821 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking draft invoices';
5822
5823 IF l_debug_mode = 'Y' THEN
5824 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5825 END IF;
5826 return;
5827 END;
5828
5829 -- Check if task has Project_customers
5830 BEGIN
5831 l_dummy := 0;
5832 /* CBS enhancement bug#16461684*/
5833 l_task_tbl := l_tmp_task_tbl;
5834 SELECT
5835 1 into l_dummy
5836 FROM
5837 sys.dual
5838 WHERE
5839 exists (SELECT NULL
5840 FROM pa_project_customers pc, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5841 where /*pc.project_id = p_project_id and */ -- commented for bug 8485835 */
5842 pc.receiver_task_id =st.task_id);
5843
5844 IF l_dummy = 1 THEN
5845 x_error_code :=170;
5846 x_error_msg_code := 'PA_TSK_CC_CUST_EXIST';
5847 return;
5848 END IF;
5849
5850 EXCEPTION
5851 WHEN NO_DATA_FOUND THEN
5852 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No Project_customers exist';
5853 IF l_debug_mode = 'Y' THEN
5854 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5855 END IF;
5856 WHEN OTHERS THEN
5857 x_error_code := SQLCODE;
5858 x_error_msg_code := substrb(SQLERRM,1,120);
5859 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking project customers';
5860
5861 IF l_debug_mode = 'Y' THEN
5862 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5863 END IF;
5864 return;
5865 END;
5866
5867 /* added these checks as a part of bug fix 6079887 */
5868 -- Check if task has iExpense records
5869 BEGIN
5870 l_dummy := 0;
5871 /* CBS enhancement bug#16461684*/
5872 l_task_tbl := l_tmp_task_tbl;
5873 IF l_Cbs_Enabled = 'Y' THEN
5874 l_task_tbl := l_alt_task_tbl;
5875 END IF;
5876 SELECT
5877 1 into l_dummy
5878 FROM
5879 sys.dual
5880 WHERE
5881 exists (SELECT NULL
5882 FROM ap_exp_report_dists_all er, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5883 where er.project_id = p_project_id
5884 AND er.TASK_ID = st.task_id);
5885
5886 IF l_dummy = 1 THEN
5887 x_error_code :=180;
5888 x_error_msg_code := 'PA_TSK_IEXP_EXIST';
5889 return;
5890 END IF;
5891
5892 EXCEPTION
5893 WHEN NO_DATA_FOUND THEN
5894 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No IExpenses exist in the entire task hierarchy';
5895 IF l_debug_mode = 'Y' THEN
5896 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5897 END IF;
5898 WHEN OTHERS THEN
5899 x_error_code := SQLCODE;
5900 x_error_msg_code := substrb(SQLERRM,1,120);
5901 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking IExpense Records';
5902
5903 IF l_debug_mode = 'Y' THEN
5904 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5905 END IF;
5906
5907 return;
5908 END;
5909
5910 -- Check if task has Inventory Transaction records
5911 BEGIN
5912 l_dummy := 0;
5913 /* CBS enhancement bug#16461684*/
5914 l_task_tbl := l_tmp_task_tbl;
5915 IF l_Cbs_Enabled = 'Y' THEN
5916 l_task_tbl := l_alt_task_tbl;
5917 END IF;
5918 SELECT
5919 1 into l_dummy
5920 FROM
5921 sys.dual
5922 WHERE
5923 exists (SELECT NULL
5924 FROM mtl_material_transactions mtl, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
5925 where mtl.project_id = p_project_id
5926 AND mtl.TASK_ID = st.task_id);
5927
5928 IF l_dummy = 1 THEN
5929 x_error_code :=190;
5930 x_error_msg_code := 'PA_TSK_INV_TRANS_EXIST';
5931 return;
5932 END IF;
5933
5934 EXCEPTION
5935 WHEN NO_DATA_FOUND THEN
5936 Pa_Debug.g_err_stage:= 'API : TASK_VALIDATIONS : No Inventory transactions exist in the entire task hierarchy';
5937 IF l_debug_mode = 'Y' THEN
5938 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5939 END IF;
5940 WHEN OTHERS THEN
5941 x_error_code := SQLCODE;
5942 x_error_msg_code := substrb(SQLERRM,1,120);
5943 Pa_Debug.g_err_stage:= ' TASK_VALIDATIONS :Unexpected Error occured while checking Inventory Records';
5944
5945 IF l_debug_mode = 'Y' THEN
5946 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
5947 END IF;
5948
5949 return;
5950 END;
5951 /* added these checks as a part of bug fix 6079887 */
5952
5953 /* END LOOP; */ --Commented By sunkalya for perf fix Bug#4964992
5954 END IF;
5955 IF l_debug_mode = 'Y' THEN
5956 Pa_Debug.reset_curr_function;
5957 END IF;
5958
5959 EXCEPTION
5960 WHEN OTHERS THEN
5961 x_error_code := SQLCODE;
5962 x_error_msg_code := substrb(SQLERRM,1,120);
5963
5964 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_error_msg_code;
5965
5966 Fnd_Msg_Pub.add_exc_msg
5967 ( p_pkg_name => 'pa_proj_tsk_utils'
5968 ,p_procedure_name => 'TASK_VALIDATIONS'
5969 ,p_error_text => x_error_msg_code);
5970
5971 IF l_debug_mode = 'Y' THEN
5972 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,
5973 l_debug_level5);
5974
5975 Pa_Debug.reset_curr_function;
5976 END IF;
5977 -- Important : Dont Raise : It will be taken care by Caller API
5978
5979 end PERFORM_TASK_VALIDATIONS;
5980
5981
5982
5983
5984 -- API name : get_task_hierarchy
5985 -- Type : Function
5986 -- Pre-reqs : None
5987 -- Return Value : Type sub_task (Table)
5988
5989 -- Prameters :
5990 -- p_project_id IN NUMBER
5991 -- p_task_id IN NUMBER
5992
5993 -- Created By : Sunkalya
5994 -- Created Date : 28-Feb-2006
5995
5996 -- Purpose:
5997 -- This Function has been included for perf fix 4964992
5998 ----------------------------------------------------------------------------------------------------------
5999 -- This function returns the entire hierarchy for the
6000 -- passed task_id.
6001
6002 -- Note that START WITH - CONNECT BY CLAUSE cant be avoided as we want the task passed and all its children
6003 -- in the hierarchy
6004 -- This is known to cause FTS on pa_tasks . But,this cant be avoided.
6005
6006 -- (ie) if the hierarchy is :
6007 -- 1
6008 -- 11
6009 -- 111
6010 -- 1111
6011
6012 -- then ,if 1's task id is passed ,then we need the entire branch as above in
6013 -- hierarchy.
6014 -------------------------------------------------------------------------------------------------------------
6015
6016 -- History
6017
6018 -- 28-Feb-2006 Sunkalya Created. Bug#4964992
6019
6020 FUNCTION get_task_hierarchy(
6021 p_project_id IN NUMBER,
6022 p_task_id IN NUMBER
6023 )
6024 RETURN sub_task
6025 IS
6026
6027 l_task_tbl sub_task;
6028 l_debug_level3 CONSTANT NUMBER := 3;
6029 l_user_id NUMBER;
6030 l_login_id NUMBER;
6031 l_debug_mode VARCHAR2(1);
6032
6033 CURSOR task_hierarchy IS
6034 SELECT
6035 task_rec(task_name,task_id)
6036 FROM
6037 pa_tasks
6038 CONNECT BY PRIOR
6039 task_id = parent_task_id AND
6040 project_id = p_project_id
6041 START WITH
6042 task_id = p_task_id AND
6043 project_id = p_project_id;
6044
6045 BEGIN
6046 l_user_id := fnd_global.user_id;
6047 l_login_id := fnd_global.login_id;
6048
6049 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
6050
6051 IF l_debug_mode = 'Y' THEN
6052 PA_DEBUG.set_curr_function( p_function =>'get_task_hierarchy' , p_debug_mode => l_debug_mode );
6053 END IF;
6054
6055 OPEN task_hierarchy;
6056 FETCH task_hierarchy BULK COLLECT INTO l_task_tbl;
6057 CLOSE task_hierarchy;
6058
6059 IF l_debug_mode = 'Y' THEN --Bug 8525293 Start
6060 pa_debug.reset_curr_function;
6061 END IF ; --Bug 8525293 End
6062
6063 RETURN l_task_tbl;
6064
6065 EXCEPTION
6066 WHEN OTHERS THEN
6067 Pa_Debug.g_err_stage:= ' API:get_task_hierarchy :Unexpected Error occured while retrieving task strucutre for '|| p_task_id;
6068 IF l_debug_mode = 'Y' THEN
6069 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
6070 pa_debug.reset_curr_function; --Bug 8525293
6071 END IF;
6072 RAISE;
6073 END get_task_hierarchy;
6074
6075 --bug#16461684 Cbs Enhancement
6076 FUNCTION get_Alt_task_List(
6077 p_project_id IN NUMBER,
6078 p_task_id IN NUMBER
6079 )
6080 RETURN sub_task
6081 IS
6082
6083 l_alt_task_tbl sub_task;
6084 l_debug_level3 CONSTANT NUMBER := 3;
6085 l_user_id NUMBER;
6086 l_login_id NUMBER;
6087 l_debug_mode VARCHAR2(1);
6088
6089 CURSOR task_hierarchy IS
6090 SELECT
6091 task_rec(null,alt_task_id)
6092 FROM
6093 pa_alternate_tasks where
6094 proj_element_id in (select task_id from
6095 pa_tasks
6096 CONNECT BY PRIOR
6097 task_id = parent_task_id AND
6098 project_id = p_project_id
6099 START WITH
6100 task_id = p_task_id AND
6101 project_id = p_project_id)
6102 ;
6103
6104 BEGIN
6105 l_user_id := fnd_global.user_id;
6106 l_login_id := fnd_global.login_id;
6107
6108 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
6109
6110 IF l_debug_mode = 'Y' THEN
6111 PA_DEBUG.set_curr_function( p_function =>'get_Alt_task_List' , p_debug_mode => l_debug_mode );
6112 END IF;
6113
6114 OPEN task_hierarchy;
6115 FETCH task_hierarchy BULK COLLECT INTO l_alt_task_tbl;
6116 CLOSE task_hierarchy;
6117
6118 IF l_debug_mode = 'Y' THEN
6119 pa_debug.reset_curr_function;
6120 END IF ;
6121
6122 RETURN l_alt_task_tbl;
6123
6124 EXCEPTION
6125 WHEN OTHERS THEN
6126 Pa_Debug.g_err_stage:= ' API:get_Alt_task_List :Unexpected Error occured while retrieving task strucutre for '|| p_task_id;
6127 IF l_debug_mode = 'Y' THEN
6128 Pa_Debug.WRITE('pa_proj_tsk_utils',Pa_Debug.g_err_stage,l_debug_level3);
6129 pa_debug.reset_curr_function;
6130 END IF;
6131 RAISE;
6132 END get_Alt_task_List;
6133
6134 function IS_LOWEST_PROJ_TASK(
6135 p_task_version_id NUMBER,
6136 p_project_id NUMBER) RETURN VARCHAR2
6137 is
6138 l_dummy number;
6139 cursor child_exist IS
6140 select 1 from dual where exists(
6141 select 1
6142 from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elements ppe
6143 where por.object_type_from = 'PA_TASKS'
6144 and por.object_id_from1 = p_task_version_id
6145 and por.relationship_type = 'S'
6146 and por.object_id_to1 = ppev.element_version_id
6147 and ppe.PROJ_ELEMENT_ID = ppev.PROJ_ELEMENT_ID
6148 and nvl(ppe.LINK_TASK_FLAG,'N') <> 'Y');
6149
6150 BEGIN
6151
6152 OPEN child_exist;
6153 FETCH child_exist into l_dummy;
6154 IF child_exist%NOTFOUND then
6155 --Cannot find child. It is lowest task
6156 CLOSE child_exist;
6157 return 'Y';
6158 ELSE
6159 --Child found. Not lowest task
6160 CLOSE child_exist;
6161 return 'N';
6162 END IF;
6163 EXCEPTION
6164 WHEN OTHERS THEN
6165 raise;
6166 END IS_LOWEST_PROJ_TASK;
6167
6168
6169 END PA_PROJ_ELEMENTS_UTILS;
[Home] [Help]