[Home] [Help]
PACKAGE BODY: APPS.PA_TASKS_MAINT_UTILS
Source
1 package body PA_TASKS_MAINT_UTILS as
2 /*$Header: PATSKSUB.pls 120.9 2009/07/21 14:10:26 anuragar ship $*/
3
4 --Begin add rtarway FP-M development ,
5 g_module_name VARCHAR2(100) := 'PA_TASKS_MAINT_UTILS';
6 --End add rtarway FP-M development
7
8 procedure CHECK_TASK_MGR_NAME_OR_ID
9 (
10 p_task_mgr_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
11 ,p_task_mgr_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
12 ,p_project_id IN NUMBER := NULL
13 ,p_check_id_flag IN VARCHAR2 := 'A'
14 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
15 ,x_task_mgr_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
16 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18 )
19 IS
20 l_current_id NUMBER := NULL;
21 l_rows NUMBER := 0;
22 l_id_found_flag VARCHAR2(1) := 'N';
23
24 cursor c IS
25 select person_id
26 from pa_employees
27 where upper(full_name) = upper(p_task_mgr_name)
28 and active = '*'; --for bug 3245820
29
30 cursor c1 IS
31 select person_id
32 from pa_employees
33 where upper(full_name) = upper(p_task_mgr_name)
34 and active = '*' -- for bug 3245820
35 and person_id in
36 ( select RESOURCE_source_ID
37 from pa_project_parties ppp
38 where ppp.RESOURCE_type_ID = 101
39 and ppp.project_id = p_project_id
40 and trunc(sysdate) between ppp.START_DATE_ACTIVE
41 and NVL(ppp.end_date_active, SYSDATE));
42
43 BEGIN
44 IF (p_task_mgr_id IS NULL OR p_task_mgr_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
45 -- ID is empty
46 IF (p_task_mgr_name IS NOT NULL AND p_task_mgr_name<> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
47 --Added for task manager changes;
48 IF (PA_TASKS_MAINT_UTILS.GET_TASK_MANAGER_PROFILE = 'N') THEN
49 --select from pa_employees
50 OPEN c;
51 LOOP
52 FETCH c INTO l_current_id;
53 EXIT when c%NOTFOUND;
54 IF (l_current_id = p_task_mgr_id) THEN
55 l_id_found_flag := 'Y';
56 x_task_mgr_id := l_current_id;
57 END IF;
58 END LOOP;
59 l_rows := c%ROWCOUNT;
60 CLOSE c;
61 ELSE
62 --select from team members
63 OPEN c1;
64 LOOP
65 FETCH c1 INTO l_current_id;
66 EXIT when c1%NOTFOUND;
67 IF (l_current_id = p_task_mgr_id) THEN
68 l_id_found_flag := 'Y';
69 x_task_mgr_id := l_current_id;
70 END IF;
71 END LOOP;
72 l_rows := c1%ROWCOUNT;
73 CLOSE c1;
74 END IF;
75
76 If (l_rows = 0) THEN
77 RAISE NO_DATA_FOUND;
78 ELSIF (l_rows = 1) THEN
79 x_task_mgr_id := l_current_id;
80 ELSIF (l_id_found_flag = 'N') THEN
81 RAISE TOO_MANY_ROWS;
82 END IF;
83 END IF;
84 ELSE
85 -- ID is not empty;
86 IF (p_check_id_flag = 'Y') THEN
87 --Added for task manager changes;
88 IF (PA_TASKS_MAINT_UTILS.GET_TASK_MANAGER_PROFILE = 'N') THEN
89 --select from pa_employees
90 SELECT person_id
91 INTO x_task_mgr_id
92 FROM pa_employees
93 WHERE person_id = p_task_mgr_id;
94 ELSE
95 --select from team members
96 SELECT person_id
97 INTO x_task_mgr_id
98 FROM pa_employees
99 WHERE person_id = p_task_mgr_id
100 AND person_id in
101 ( select RESOURCE_source_ID
102 from pa_project_parties ppp
103 where ppp.RESOURCE_type_ID = 101
104 and ppp.project_id = p_project_id
105 and trunc(sysdate) between ppp.START_DATE_ACTIVE
106 and NVL(ppp.end_date_active, SYSDATE));
107 END IF;
108 ELSIF (p_check_id_flag = 'N') THEN
109 x_task_mgr_id := p_task_mgr_id;
110 ELSIF (p_check_id_flag = 'A') THEN
111 --Added for task manager changes;
112 IF (PA_TASKS_MAINT_UTILS.GET_TASK_MANAGER_PROFILE = 'N') THEN
113 --select from pa_employees
114 OPEN c;
115 LOOP
116 FETCH c INTO l_current_id;
117 EXIT when c%NOTFOUND;
118 IF (l_current_id = p_task_mgr_id) THEN
119 l_id_found_flag := 'Y';
120 x_task_mgr_id := l_current_id;
121 END IF;
122 END LOOP;
123 l_rows := c%ROWCOUNT;
124 CLOSE c;
125 ELSE
126 --select from team members
127 OPEN c1;
128 LOOP
129 FETCH c1 INTO l_current_id;
130 EXIT when c1%NOTFOUND;
131 IF (l_current_id = p_task_mgr_id) THEN
132 l_id_found_flag := 'Y';
133 x_task_mgr_id := l_current_id;
134 END IF;
135 END LOOP;
136 l_rows := c1%ROWCOUNT;
137 CLOSE c1;
138 END IF;
139
140 If (l_rows = 0) THEN
141 RAISE NO_DATA_FOUND;
142 ELSIF (l_rows = 1) THEN
143 x_task_mgr_id := l_current_id;
144 ELSIF (l_id_found_flag = 'N') THEN
145 RAISE TOO_MANY_ROWS;
146 END IF;
147
148 END IF;
149 END IF;
150
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 x_task_mgr_id := NULL;
155 x_return_status := FND_API.G_RET_STS_ERROR;
156 x_error_msg_code := 'PA_TASK_MGR_ID_INVALID';
157 WHEN TOO_MANY_ROWS THEN
158 x_task_mgr_id := NULL;
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 x_error_msg_code := 'PA_TASK_MGR_ID_NOT_UNIQUE';
161 WHEN OTHERS THEN
162 x_task_mgr_id := NULL;
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 -- 4537865 : RESET x_error_msg_code also
165 x_error_msg_code := SQLCODE;
166
167 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
168 p_procedure_name => 'CHECK_TASK_MGR_NAME_OR_ID');
169 RAISE;
170 END CHECK_TASK_MGR_NAME_OR_ID;
171
172
173 procedure CHECK_PROJECT_NAME_OR_ID
174 (
175 p_project_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
176 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
177 ,p_check_id_flag IN VARCHAR2 := 'A'
178 ,x_project_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
179 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
180 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
181 ) AS
182 l_current_id NUMBER := NULL;
183 l_rows NUMBER := 0;
184 l_id_found_flag VARCHAR2(1) := 'N';
185
186 cursor c IS
187 select project_id
188 from pa_projects_all
189 where UPPER(name) = UPPER(p_project_name);
190
191 BEGIN
192 IF (p_project_id IS NULL OR p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
193 -- ID is empty
194 IF (p_project_name IS NOT NULL AND p_project_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
195 OPEN c;
196 LOOP
197 FETCH c INTO l_current_id;
198 EXIT WHEN c%NOTFOUND;
199 IF (l_current_id = p_project_id) THEN
200 l_id_found_flag := 'Y';
201 x_project_id := l_current_id;
202 END IF;
203 END LOOP;
204 l_rows := c%ROWCOUNT;
205 CLOSE c;
206 If (l_rows = 0) THEN
207 RAISE NO_DATA_FOUND;
208 ELSIF (l_rows = 1) THEN
209 x_project_id := l_current_id;
210 ELSIF (l_id_found_flag = 'N') THEN
211 RAISE TOO_MANY_ROWS;
212 END IF;
213 END IF;
214 ELSE
215 -- ID is not empty;
216 IF (p_check_id_flag = 'Y') THEN
217 SELECT project_id
218 INTO x_project_id
219 FROM pa_projects_all
220 WHERE project_id = p_project_id;
221 ELSIF (p_check_id_flag = 'N') THEN
222 x_project_id := p_project_id;
223 ELSIF (p_check_id_flag = 'A') THEN
224 OPEN c;
225 LOOP
226 FETCH c INTO l_current_id;
227 EXIT WHEN c%NOTFOUND;
228 IF (l_current_id = p_project_id) THEN
229 l_id_found_flag := 'Y';
230 x_project_id := l_current_id;
231 END IF;
232 END LOOP;
233 l_rows := c%ROWCOUNT;
234 CLOSE c;
235 If (l_rows = 0) THEN
236 RAISE NO_DATA_FOUND;
237 ELSIF (l_rows = 1) THEN
238 x_project_id := l_current_id;
239 ELSIF (l_id_found_flag = 'N') THEN
240 RAISE TOO_MANY_ROWS;
241 END IF;
242 END IF;
243 END IF;
244
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 x_project_id := NULL;
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 x_error_msg_code := 'PA_TASK_INV_PRJ_ID';
251 WHEN TOO_MANY_ROWS THEN
252 x_project_id := NULL;
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 x_error_msg_code := 'PA_TASK_PRJ_ID_NOT_UNIQ';
255 WHEN OTHERS THEN
256 x_project_id := NULL;
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 -- 4537865 : RESET x_error_msg_code also
259 x_error_msg_code := SQLCODE ;
260
261 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
262 p_procedure_name => 'CHECK_PROJECT_NAME_OR_ID');
263 RAISE;
264 END CHECK_PROJECT_NAME_OR_ID;
265
266 procedure CHECK_TASK_NAME_OR_ID
267 (
268 p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
269 ,p_task_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
270 ,p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
271 ,p_check_id_flag IN VARCHAR2 := 'A'
272 ,x_task_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
273 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
274 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
275 )
276 IS
277 l_current_id NUMBER := NULL;
278 l_rows NUMBER := 0;
279 l_id_found_flag VARCHAR2(1) := 'N';
280
281 cursor c IS
282 select task_id
283 from pa_tasks
284 where UPPER(task_name) = UPPER(p_task_name)
285 and project_id = p_project_id;
286 BEGIN
287 IF (p_task_id IS NULL OR p_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
288 -- ID is empty
289 IF (p_task_name IS NOT NULL AND p_task_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
290 OPEN c;
291 LOOP
292 FETCH c INTO l_current_id;
293 EXIT WHEN c%NOTFOUND;
294 IF (l_current_id = p_task_id) THEN
295 l_id_found_flag := 'Y';
296 x_task_id := l_current_id;
297 END IF;
298 END LOOP;
299 l_rows := c%ROWCOUNT;
300 CLOSE c;
301 If (l_rows = 0) THEN
302 RAISE NO_DATA_FOUND;
303 ELSIF (l_rows = 1) THEN
304 x_task_id := l_current_id;
305 ELSIF (l_id_found_flag = 'N') THEN
306 RAISE TOO_MANY_ROWS;
307 END IF;
308 END IF;
309 ELSE
310 -- ID is not empty;
311 IF (p_check_id_flag = 'Y') THEN
312 SELECT task_id
313 INTO x_task_id
314 FROM pa_tasks
315 WHERE task_id = p_task_id and project_id = p_project_id;
316 ELSIF (p_check_id_flag = 'N') THEN
317 x_task_id := p_task_id;
318 ELSIF (p_check_id_flag = 'A') THEN
319 OPEN c;
320 LOOP
321 FETCH c INTO l_current_id;
322 EXIT WHEN c%NOTFOUND;
323 IF (l_current_id = p_task_id) THEN
324 l_id_found_flag := 'Y';
325 x_task_id := l_current_id;
326 END IF;
327 END LOOP;
328 l_rows := c%ROWCOUNT;
329 CLOSE c;
330 If (l_rows = 0) THEN
331 RAISE NO_DATA_FOUND;
332 ELSIF (l_rows = 1) THEN
333 x_task_id := l_current_id;
334 ELSIF (l_id_found_flag = 'N') THEN
335 RAISE TOO_MANY_ROWS;
336 END IF;
337 END IF;
338 END IF;
339
340 x_return_status := FND_API.G_RET_STS_SUCCESS;
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 x_task_id := NULL;
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 x_error_msg_code := 'PA_TASK_ID_INVALID';
346 WHEN TOO_MANY_ROWS THEN
347 x_task_id := NULL;
348 x_return_status := FND_API.G_RET_STS_ERROR;
349 x_error_msg_code := 'PA_TASK_ID_NOT_UNIQUE';
350 WHEN OTHERS THEN
351 x_task_id := NULL;
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353 -- 4537865 : RESET x_error_msg_code also
354 x_error_msg_code := SQLCODE;
355
356 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
357 p_procedure_name => 'CHECK_TASK_NAME_OR_ID');
358 RAISE;
359 END CHECK_TASK_NAME_OR_ID;
360
361
362
363 FUNCTION Get_Sequence_Number(p_peer_or_sub IN VARCHAR2,
364 p_project_id IN NUMBER,
365 p_task_id IN NUMBER)
366 RETURN NUMBER
367 IS
368 l_s_num NUMBER;
369 l_s_num_min NUMBER;
370 BEGIN
371 /*HY
372 IF (p_peer_or_sub = 'SUB') THEN
373 select display_sequence
374 into l_s_num
375 from pa_tasks
376 where project_id = p_project_id
377 and task_id = p_task_id;
378
379 if (l_s_num < 0) then
380 return l_s_num - 1;
381 else
382 return -(l_s_num+1);
383 end if;
384 ELSE -- 'PEER'
385 select max(display_sequence), min(display_sequence)
386 into l_s_num, l_s_num_min
387 from (
388 select display_sequence
389 from pa_tasks
390 where project_id = p_project_id
391 start with task_id = p_task_id
392 connect by prior task_id = parent_task_id
393 );
394
395 if (l_s_num_min > 0) then
396 return -(l_s_num+1);
397 else
398 return l_s_num_min-1;
399 end if;
400 END IF;
401 */ return 1;
402 END;
403
404 --For getting address id when defaulting top task
405 FUNCTION default_address_id(p_proj_id IN NUMBER)
406 RETURN NUMBER
407 IS
408 CURSOR get_addr IS
409 select min(ship_to_address_id) address_id, count('1') count
410 from pa_project_customers
411 where project_id = p_proj_id;
412 temp_addr get_addr%ROWTYPE;
413
414 BEGIN
415 OPEN get_addr;
416 FETCH get_addr INTO temp_addr;
417 IF (temp_addr.count = 1) THEN
418 return temp_addr.address_id;
419 ELSE
420 return NULL;
421 END IF;
422 return NULL;
423 END default_address_id;
424
425
426 PROCEDURE CHECK_TASK_NUMBER_DISP(
427 p_project_id IN NUMBER,
428 p_task_id IN NUMBER,
429 p_task_number IN VARCHAR2,
430 p_rowid IN VARCHAR2)
431 IS
432 x_err_code Number := 0;
433 x_err_stage Varchar2(80);
434 x_err_stack Varchar2(630);
435 BEGIN
436 pa_task_utils.change_lowest_task_num_ok(
437 p_task_id,
438 x_err_code,
439 x_err_stage,
440 x_err_stack);
441 IF (x_err_code <> 0) THEN
442 PA_UTILS.ADD_MESSAGE('PA',substr(x_err_stage,1,30));
443 return;
444 END IF;
445
446 If Pa_Task_Utils.Check_Unique_Task_number (p_project_id,
447 p_task_number,
448 p_rowid ) <> 1 Then
449 PA_UTILS.ADD_MESSAGE('PA','PA_ALL_DUPLICATE_NUM');
450 return;
451 END IF;
452 END CHECK_TASK_NUMBER_DISP;
453
454
455 procedure Check_Start_Date(p_project_id IN NUMBER,
456 p_parent_task_id IN NUMBER,
457 p_task_id IN NUMBER,
458 p_start_date IN DATE,
459 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
460 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
461 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
462 IS
463 l_start_date DATE;
464 l_end_date DATE;
465
466 -- Bug 6163119
467 l_pstart_date DATE;
468 l_pend_date DATE;
469 l_tstart_date DATE;
470 l_tend_date DATE;
471
472 --bug 8566495 anuragag
473 l_sch_s_date DATE;
474 l_sch_e_date DATE;
475
476 CURSOR c1(tid NUMBER) IS
477 select min(start_date), max(completion_date) --Bug 6163119
478 from pa_tasks
479 where --parent_task_id = c1.tid --Bug 6163119
480 project_id = p_project_id
481 start with parent_task_id=c1.tid
482 connect by prior task_id= parent_task_id; --Bug 6163119
483
484 --bug 8566495 anuragag
485 cursor get_parent_dates(p_task_id NUMBER) is
486 select scheduled_start_date,scheduled_finish_date from pa_proj_elem_ver_schedule where
487 element_version_id =
488 (select por.object_id_from1 from pa_proj_element_versions ppev,pa_object_relationships por
489 where ppev.proj_element_id = p_task_id
490 and ppev.element_version_id = por.object_id_to1
491 and relationship_subtype = 'TASK_TO_TASK'
492 );
493
494
495 BEGIN
496
497 IF (p_parent_task_id IS NULL) THEN -- TOP TASK, compare with project
498
499 --select project start date
500 select start_date, completion_date
501 into l_start_date, l_end_date
502 from pa_projects_all
503 where project_id = p_project_id;
504 IF (p_start_date IS NOT NULL and
505 l_start_date > p_start_date) THEN
506 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_TK_OUTSIDE_PROJECT_RANGE');
507 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
508 RAISE FND_API.G_EXC_ERROR;
509 END IF;
510
511 IF (l_end_date IS NOT NULL and
512 p_start_date > l_end_date) THEN
513 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_TK_OUTSIDE_PROJECT_RANGE');
514 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518 if(PA_TASK_PVT1.G_CHG_DOC_CNTXT = 1 )
519 then
520 open get_parent_dates(p_task_id);
521 fetch get_parent_dates into l_sch_s_date,l_sch_e_date;
522 close get_parent_dates;
523 if(l_sch_s_date is not null and l_sch_s_date>p_start_date) then
524 x_msg_data := 'PA_PARENT_TASK_GREATER';
525 RAISE FND_API.G_EXC_ERROR;
526 END IF;
527
528 end if;
529 ELSE -- NOT A TOP TASK, compare with parent task
530 --select parent task start date
531 select start_date, completion_date
532 into l_start_date, l_end_date
533 from pa_tasks
534 where task_id = p_parent_task_id;
535 IF (p_start_date is NOT NULL and
536 l_start_date > p_start_date) THEN
537 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PARENT_TASK_GREATER');
538 x_msg_data := 'PA_PARENT_TASK_GREATER';
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541 IF (l_end_date IS NOT NULL and
542 p_start_date > l_end_date) THEN
543 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PARENT_TASK_GREATER');
544 x_msg_data := 'PA_PARENT_TASK_GREATER';
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547
548 -- Bug Start 6163119
549 select start_date, completion_date
550 into l_pstart_date, l_pend_date
551 from pa_projects_all
552 where project_id = p_project_id;
553
554 select max(start_date),
555 min(completion_date)
556 into l_tstart_date,
557 l_tend_date
558 from pa_tasks
559 where project_id=p_project_id
560 start with task_id=p_parent_task_id
561 connect by task_id= prior parent_task_id;
562
563 -- Bug fix 7482184
564 IF p_start_date IS NOT NULL AND l_start_date IS NULL THEN
565 IF l_tstart_date IS NOT NULL THEN
566 IF(l_tstart_date > p_start_date) THEN
567 x_msg_data := 'PA_PARENT_TASK_GREATER';
568 RAISE FND_API.G_EXC_ERROR;
569 END IF;
570 ELSIF l_pstart_date IS NOT NULL THEN
571 IF(l_pstart_date > p_start_date) THEN
572 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
573 RAISE FND_API.G_EXC_ERROR;
574 END IF;
575 END IF;
576 END IF;
577
578 -- Bug fix 7482184
579 IF p_start_date IS NOT NULL AND l_end_date IS NULL THEN
580 IF l_tend_date IS NOT NULL THEN
581 IF (p_start_date > l_tend_date) THEN
582 x_msg_data := 'PA_PARENT_TASK_GREATER';
583 RAISE FND_API.G_EXC_ERROR;
584 END IF;
585 ELSIF l_pend_date IS NOT NULL THEN
586 IF(p_start_date > l_pend_date) THEN
587 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590 END IF;
591 END IF;
592
593 -- Bug End 6163119
594
595
596 END IF;
597
598 IF (p_task_id IS NOT NULL) THEN
599 -- This is an existing task
600 -- select start date of children
601 OPEN c1(p_task_id);
602 LOOP
603 FETCH c1 INTO l_start_date, l_end_date;
604 EXIT WHEN c1%NOTFOUND;
605 IF (p_start_date is NOT NULL and
606 l_start_date < p_start_date) THEN -- Bug 7386335
607 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_CHILD_TASK_DATE_EARLIER');
608 x_msg_data := 'PA_CHILD_TASK_DATE_EARLIER';
609 CLOSE c1;
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612 IF (l_end_date IS NOT NULL and
613 p_start_date > l_end_date) THEN
614 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_CHILD_TASK_DATE_EARLIER');
615 x_msg_data := 'PA_CHILD_TASK_DATE_EARLIER';
616 CLOSE c1;
617 RAISE FND_API.G_EXC_ERROR;
618 END IF;
619 END LOOP;
620 CLOSE c1;
621 END IF;
622 x_return_status := FND_API.G_RET_STS_SUCCESS;
623
624 EXCEPTION
625 WHEN FND_API.G_EXC_ERROR THEN
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 x_msg_count := FND_MSG_PUB.count_msg;
628 WHEN OTHERS THEN
629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
630 -- 4537865 : RESET other OUT params too
631 x_msg_count := 1;
632 x_msg_data := SUBSTRB(SQLERRM ,1,240);
633
634 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
635 p_procedure_name => 'CHECK_START_DATE',
636 p_error_text => x_msg_data); -- 4537865
637 RAISE;
638 END Check_Start_Date;
639
640
641 procedure Check_End_Date( p_project_id IN NUMBER,
642 p_parent_task_id IN NUMBER,
643 p_task_id IN NUMBER,
644 p_end_date IN DATE,
645 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
646 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
647 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
648 IS
649 l_start_date DATE;
650 l_end_date DATE;
651
652 -- Bug 6163119
653 l_pstart_date DATE;
654 l_pend_date DATE;
655 l_tstart_date DATE;
656 l_tend_date DATE;
657
658 --bug 8566495 anuragag
659 l_sch_s_date DATE;
660 l_sch_e_date DATE;
661
662 CURSOR c1(tid NUMBER) IS
663 select min(start_date), max(completion_date) --Bug 6163119
664 from pa_tasks
665 where --parent_task_id = c1.tid --Bug 6163119
666 project_id = p_project_id
667 start with parent_task_id=c1.tid
668 connect by prior task_id= parent_task_id;--Bug 6163119
669
670 --bug 8566495 anuragag
671 cursor get_parent_dates(p_task_id NUMBER) is
672 select scheduled_start_date,scheduled_finish_date from pa_proj_elem_ver_schedule where
673 element_version_id =
674 (select por.object_id_from1 from pa_proj_element_versions ppev,pa_object_relationships por
675 where ppev.proj_element_id = p_task_id
676 and ppev.element_version_id = por.object_id_to1
677 and relationship_subtype = 'TASK_TO_TASK'
678 );
679
680 BEGIN
681
682 IF (p_parent_task_id IS NULL) THEN -- TOP TASK, compare with project
683 --select project completion date
684 select start_date, completion_date
685 into l_start_date, l_end_date
686 from pa_projects_all
687 where project_id = p_project_id;
688 IF (p_end_date IS NOT NULL and
689 l_end_date < p_end_date) THEN
690 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_TK_OUTSIDE_PROJECT_RANGE');
691 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
692 RAISE FND_API.G_EXC_ERROR;
693 END IF;
694 IF (p_end_date IS NOT NULL and
695 l_start_date > p_end_date) THEN
696 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_TK_OUTSIDE_PROJECT_RANGE');
697 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
698 RAISE FND_API.G_EXC_ERROR;
699 END IF;
700
701 if(PA_TASK_PVT1.G_CHG_DOC_CNTXT = 1 )
702 then
703 open get_parent_dates(p_task_id);
704 fetch get_parent_dates into l_sch_s_date,l_sch_e_date;
705 close get_parent_dates;
706 if(l_sch_s_date is not null and l_sch_e_date<p_end_date) then
707 x_msg_data := 'PA_PARENT_COMPLETION_EARLIER';
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710
711 end if;
712
713 ELSE -- NOT A TOP TASK, compare with parent task
714 --select parent task completion date
715 select start_date, completion_date
716 into l_start_date, l_end_date
717 from pa_tasks
718 where task_id = p_parent_task_id;
719 IF (p_end_date is NOT NULL and
720 l_end_date < p_end_date) THEN
721 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PARENT_COMPLETION_EARLIER');
722 x_msg_data := 'PA_PARENT_COMPLETION_EARLIER';
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;
725 IF (p_end_date is NOT NULL and
726 l_start_date > p_end_date) THEN
727 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PARENT_COMPLETION_EARLIER');
728 x_msg_data := 'PA_PARENT_COMPLETION_EARLIER';
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731
732 -- Bug Start 6163119
733 select start_date, completion_date
734 into l_pstart_date, l_pend_date
735 from pa_projects_all
736 where project_id = p_project_id;
737
738 select max(start_date),
739 min(completion_date)
740 into l_tstart_date,
741 l_tend_date
742 from pa_tasks
743 where project_id=p_project_id
744 start with task_id=p_parent_task_id
745 connect by task_id= prior parent_task_id;
746
747 -- Bug fix 7482184
748 IF p_end_date IS NOT NULL AND l_end_date IS NULL THEN
749 IF l_tend_date IS NOT NULL THEN
750 IF (l_tend_date < p_end_date) THEN
751 x_msg_data := 'PA_PARENT_COMPLETION_EARLIER';
752 RAISE FND_API.G_EXC_ERROR;
753 END IF;
754 ELSIF l_pend_date IS NOT NULL THEN
755 IF (l_pend_date < p_end_date) THEN
756 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
757 RAISE FND_API.G_EXC_ERROR;
758 END IF;
759 END IF;
760 END IF;
761
762 -- Bug fix 7482184
763 IF p_end_date IS NOT NULL AND l_start_date IS NULL THEN
764 IF l_tstart_date IS NOT NULL THEN
765 IF(l_tstart_date > p_end_date) THEN
766 x_msg_data := 'PA_PARENT_COMPLETION_EARLIER';
767 RAISE FND_API.G_EXC_ERROR;
768 END IF;
769 ELSIF l_pstart_date is NOT NULL THEN
770 IF(l_pstart_date > p_end_date) THEN
771 x_msg_data := 'PA_TK_OUTSIDE_PROJECT_RANGE';
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774 END IF;
775 END IF;
776
777 -- Bug End 6163119
778
779 END IF;
780
781 IF (p_task_id IS NOT NULL) THEN
782 -- This is an existing task
783 -- select start date of children
784 OPEN c1(p_task_id);
785 LOOP
786 FETCH c1 INTO l_start_date, l_end_date;
787 EXIT WHEN c1%NOTFOUND;
788 IF (p_end_date is NOT NULL and
789 l_end_date > p_end_date) THEN
790 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_CHILD_COMPLETION_LATER');
791 x_msg_data := 'PA_CHILD_COMPLETION_LATER';
792 CLOSE c1;
793 RAISE FND_API.G_EXC_ERROR;
794 END IF;
795 IF (p_end_date is NOT NULL and
796 l_start_date > p_end_date) THEN
797 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PARENT_TASK_GREATER');
798 x_msg_data := 'PA_CHILD_COMPLETION_LATER';
799 CLOSE c1;
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802
803 END LOOP;
804 CLOSE c1;
805 END IF;
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 EXCEPTION
809 WHEN FND_API.G_EXC_ERROR THEN
810 x_return_status := FND_API.G_RET_STS_ERROR;
811 x_msg_count := FND_MSG_PUB.count_msg;
812 WHEN OTHERS THEN
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
814 -- 4537865 : RESET other OUT params too
815 x_msg_count := 1;
816 x_msg_data := SUBSTRB(SQLERRM ,1,240);
817 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
818 p_procedure_name => 'CHECK_END_DATE',
819 p_error_text => x_msg_data); -- 4537865
820 END Check_End_Date;
821
822
823 PROCEDURE Check_Chargeable_Flag( p_chargeable_flag IN VARCHAR2,
824 p_receive_project_invoice_flag IN VARCHAR2,
825 p_project_type IN VARCHAR2,
826 p_project_id IN number, -- Added for bug#3512486
827 x_receive_project_invoice_flag OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
828 IS
829 -- MOAC Changes: Bug 4363092: removed nvl with org_id
830 CURSOR c1 IS
831 select nvl(cc_ic_billing_recvr_flag, 'N')
832 from pa_implementations_all -- Modified pa_implementations to pa_implementations_all for bug#3512486
833 where org_id = (select org_id from pa_projects_all where project_id = p_project_id); -- Added the where condition for bug#3512486
834
835 -- MOAC Changes: Bug 4363092: removed nvl with org_id
836 CURSOR c2 IS
837 select nvl(cc_prvdr_flag, 'N')
838 from pa_project_types_all -- Modified pa_project_types to pa_project_types_all for bug#3512486
839 where project_type = p_project_type
840 and org_id = (select org_id from pa_projects_all where project_id = p_project_id); -- Added the and condition for bug#3512486
841
842 l_c1_flag VARCHAR2(1);
843 l_c2_flag VARCHAR2(2);
844 BEGIN
845 IF (p_chargeable_flag = 'Y') THEN
846 BEGIN
847 OPEN c1;
848 FETCH c1 INTO l_c1_flag;
849 CLOSE c1;
850 OPEN c2;
851 FETCH c2 INTO l_c2_flag;
852 CLOSE c2;
853
854 IF (l_c1_flag = 'Y' AND l_c2_flag = 'N') THEN
855 x_receive_project_invoice_flag := p_receive_project_invoice_flag;
856 ELSE
857 x_receive_project_invoice_flag := 'N';
858 END IF;
859 EXCEPTION
860 WHEN OTHERS THEN
861 x_receive_project_invoice_flag := 'N';
862 END;
863 END IF;
864 END Check_Chargeable_Flag;
865
866
867
868 PROCEDURE CHECK_SCHEDULE_DATES(p_project_id IN NUMBER,
869 p_sch_start_date IN DATE,
870 p_sch_end_date IN DATE,
871 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
872 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
873 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
874 IS
875 CURSOR c1 IS
876 select SCHEDULED_START_DATE, SCHEDULED_FINISH_DATE
877 from pa_projects_all
878 where project_id = p_project_id;
879
880 l_start_date DATE;
881 l_finish_date DATE;
882 l_f1 VARCHAR2(1);
883 l_f2 VARCHAR2(1);
884 l_ret VARCHAR2(1);
885
886 BEGIN
887 IF (p_sch_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR
888 p_sch_start_date IS NULL) AND
889 (p_sch_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR
890 p_sch_end_date IS NULL) THEN
891 check_start_end_date(
892 p_old_start_date => null,
893 p_old_end_date => null,
894 p_new_start_date => p_sch_start_date,
895 p_new_end_date => p_sch_end_date,
896 p_update_start_date_flag => l_f1,
897 p_update_end_date_flag => l_f2,
898 p_return_status => l_ret);
899 IF (l_ret <> 'S') THEN
900 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_INVALID_SCH_DATES');
901 --commenting the following line after discussing with
902 --sakthi. The reason is that there are two messages being appended
903 --for the same error.
904 --x_msg_data := 'PA_INVALID_SCH_DATES';
905 RAISE FND_API.G_EXC_ERROR;
906 END IF;
907 END IF;
908
909 OPEN c1;
910 FETCH c1 INTO l_start_date, l_finish_date;
911 IF c1%NOTFOUND THEN
912 PA_UTILS.ADD_MESSAGE('PA', 'PA_PROJ_NOT_EXIST');
913 x_msg_data := 'PA_PROJ_NOT_EXIST';
914 CLOSE c1;
915 RAISE FND_API.G_EXC_ERROR;
916 END IF;
917 CLOSE c1;
918
919 IF (p_sch_start_date IS NOT NULL and l_start_date > p_sch_start_date) THEN
920 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_SCH_DATE_OUTSIDE_PROJ_RANGE');
921 x_msg_data := 'PA_SCH_DATE_OUTSIDE_PROJ_RANGE';
922 RAISE FND_API.G_EXC_ERROR;
923 END IF;
924
925 IF (p_sch_end_date IS NOT NULL and l_finish_date < p_sch_end_date) THEN
926 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_SCH_DATE_OUTSIDE_PROJ_RANGE');
927 x_msg_data := 'PA_SCH_DATE_OUTSIDE_PROJ_RANGE';
928 RAISE FND_API.G_EXC_ERROR;
929 END IF;
930
931 x_msg_data := NULL;
932 x_return_status := FND_API.G_RET_STS_SUCCESS;
933 EXCEPTION
934 WHEN FND_API.G_EXC_ERROR THEN
935 x_return_status := FND_API.G_RET_STS_ERROR;
936 x_msg_count := FND_MSG_PUB.count_msg;
937 WHEN OTHERS THEN
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
939 -- 4537865 : RESET other OUT params too
940 x_msg_count := 1;
941 x_msg_data := SUBSTRB(SQLERRM ,1,240);
942
943 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
944 p_procedure_name => 'CHECK_SCHEDULE_DATES',
945 p_error_text => x_msg_data ); -- 4537865
946 END CHECK_SCHEDULE_DATES;
947
948
949 PROCEDURE CHECK_ESTIMATE_DATES(p_project_id IN NUMBER,
950 p_estimate_start_date IN DATE,
951 p_estimate_end_date IN DATE,
952 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
953 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
954 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
955 IS
956 l_f1 VARCHAR2(1);
957 l_f2 VARCHAR2(1);
958 l_ret VARCHAR2(1);
959 BEGIN
960
961 IF (p_estimate_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR
962 p_estimate_start_date IS NULL) AND
963 (p_estimate_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR
964 p_estimate_end_date IS NULL) THEN
965 check_start_end_date(
966 p_old_start_date => null,
967 p_old_end_date => null,
968 p_new_start_date => p_estimate_start_date,
969 p_new_end_date => p_estimate_end_date,
970 p_update_start_date_flag => l_f1,
971 p_update_end_date_flag => l_f2,
972 p_return_status => l_ret);
973 IF (l_ret <> 'S') THEN
974 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_INVALID_EST_DATES');
975 x_msg_data := 'PA_INVALID_EST_DATES';
976 RAISE FND_API.G_EXC_ERROR;
977 END IF;
978 END IF;
979
980 x_msg_data := NULL;
981 x_return_status := FND_API.G_RET_STS_SUCCESS;
982
983 EXCEPTION
984 WHEN FND_API.G_EXC_ERROR THEN
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 x_msg_count := FND_MSG_PUB.count_msg;
987 WHEN OTHERS THEN
988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
989 -- 4537865 : RESET other OUT params too
990 x_msg_count := 1;
991 x_msg_data := SUBSTRB(SQLERRM ,1,240);
992 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
993 p_procedure_name => 'CHECK_ESTIMATE_DATES',
994 p_error_text => x_msg_data ); -- 4537865
995 END CHECK_ESTIMATE_DATES;
996
997
998 PROCEDURE CHECK_ACTUAL_DATES(p_project_id IN NUMBER,
999 p_actual_start_date IN DATE,
1000 p_actual_end_date IN DATE,
1001 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1002 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1003 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1004 IS
1005 l_f1 VARCHAR2(1);
1006 l_f2 VARCHAR2(1);
1007 l_ret VARCHAR2(1);
1008 BEGIN
1009
1010 IF (p_actual_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR
1011 p_actual_start_date IS NULL) AND
1012 (p_actual_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR
1013 p_actual_end_date IS NULL) THEN
1014 check_start_end_date(
1015 p_old_start_date => null,
1016 p_old_end_date => null,
1017 p_new_start_date => p_actual_start_date,
1018 p_new_end_date => p_actual_end_date,
1019 p_update_start_date_flag => l_f1,
1020 p_update_end_date_flag => l_f2,
1021 p_return_status => l_ret);
1022 IF (l_ret <> 'S') THEN
1023 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_INVALID_ACTUAL_DATES');
1024 x_msg_data := 'PA_INVALID_ACTUAL_DATES';
1025 RAISE FND_API.G_EXC_ERROR;
1026 END IF;
1027 END IF;
1028
1029 x_msg_data := NULL;
1030 x_return_status := FND_API.G_RET_STS_SUCCESS;
1031
1032 EXCEPTION
1033 WHEN FND_API.G_EXC_ERROR THEN
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 x_msg_count := FND_MSG_PUB.count_msg;
1036 WHEN OTHERS THEN
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038 -- 4537865 : RESET other OUT params too
1039 x_msg_count := 1;
1040 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1041 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
1042 p_procedure_name => 'CHECK_ACTUAL_DATES',
1043 p_error_text => x_msg_data ); -- 4537865
1044 END CHECK_ACTUAL_DATES;
1045
1046
1047 PROCEDURE SET_ORG_ID(p_project_id IN NUMBER)
1048 IS
1049 l_org_id NUMBER;
1050 BEGIN
1051 SELECT org_id INTO l_org_id
1052 FROM PA_PROJECTS_ALL
1053 WHERE project_id = p_project_id;
1054
1055 END SET_ORG_ID;
1056
1057
1058 function rearrange_display_seq (p_display_seq IN NUMBER,
1059 p_above_seq IN NUMBER,
1060 p_number_tasks IN NUMBER,
1061 p_mode IN VARCHAR2,
1062 p_operation IN VARCHAR2) return NUMBER
1063 is
1064 i NUMBER;
1065 begin
1066 if p_mode = 'INSERT' then
1067 if p_display_seq < 0 then
1068 i := abs(p_display_seq);
1069 elsif p_display_seq > 0 then
1070 if p_display_seq > p_above_seq then
1071 i := p_display_seq;
1072 else
1073 i := p_display_seq + p_number_tasks;
1074 end if;
1075 end if;
1076 end if;
1077 if p_mode = 'MOVE' then
1078 if p_operation = 'UP' then
1079 if p_display_seq < 0 then
1080 i := abs(p_display_seq);
1081 elsif p_display_seq > 0 then
1082 if p_display_seq >= p_above_seq then
1083 i := p_display_seq;
1084 else
1085 i := p_display_seq + p_number_tasks;
1086 end if;
1087 end if;
1088 end if;
1089 if p_operation = 'DOWN' then
1090 if p_display_seq < 0 then
1091 i := abs(p_display_seq) - p_number_tasks;
1092 elsif p_display_seq > 0 then
1093 --if p_display_seq >= p_above_seq then
1094 if p_display_seq > p_above_seq then
1095 i := p_display_seq;
1096 else
1097 i := p_display_seq - p_number_tasks;
1098 end if;
1099 end if;
1100 end if;
1101 end if;
1102
1103 if p_mode = 'DELETE' then
1104 i := p_display_seq - p_number_tasks;
1105 end if;
1106 return(i);
1107 end rearrange_display_seq;
1108
1109
1110 -- API name : DEFAULT_TASK_ATTRIBUTES
1111 -- Type : Utility procedure
1112 -- Pre-reqs : None
1113 -- Return Value : N/A
1114 -- Prameters
1115 -- p_reference_task_id IN NUMBER REQUIRED
1116 -- p_task_type IN VARCHAR2 REQUIRED
1117 -- x_carrying_out_org_id OUT NUMBER REQUIRED
1118 -- x_carrying_out_org_name OUT VARCHAR2 REQUIRED
1119 -- x_work_type_id OUT NUMBER REQUIRED
1120 -- x_work_type_name OUT VARCHAR2 REQUIRED
1121 -- x_service_type_code OUT VARCHAR2 REQUIRED
1122 -- x_service_type_name OUT VARCHAR2 REQUIRED
1123 -- x_return_status OUT VARCHAR2 REQUIRED
1124 -- x_error_msg_code OUT VARCHAR2 REQUIRED
1125 --
1126 -- History
1127 --
1128 -- 14-JUN-01 Majid Ansari -Created
1129 --
1130 --
1131
1132 PROCEDURE DEFAULT_TASK_ATTRIBUTES(
1133 p_reference_task_id IN NUMBER,
1134 p_task_type IN VARCHAR2,
1135 x_carrying_out_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1136 x_carrying_out_org_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1137 x_work_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1138 x_work_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1139 x_service_type_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1140 x_service_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1141 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1142 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1143 ) AS
1144
1145 CURSOR cur_pa_parent_task
1146 IS
1147 SELECT parent_task_id, project_id
1148 FROM pa_tasks
1149 WHERE task_id = p_reference_task_id;
1150
1151 l_parent_task_id NUMBER;
1152 l_project_id NUMBER;
1153
1154 BEGIN
1155 x_return_status := FND_API.G_RET_STS_SUCCESS;
1156 IF p_reference_task_id IS NULL
1157 THEN
1158 x_return_status := FND_API.G_RET_STS_ERROR;
1159 x_error_msg_code := 'PA_PRJ_TASK_ID_REQ';
1160 RAISE FND_API.G_EXC_ERROR;
1161 END IF;
1162
1163 IF p_task_type IS NULL
1164 THEN
1165 x_return_status := FND_API.G_RET_STS_ERROR;
1166 x_error_msg_code := 'PA_PRJ_TASK_TYPE_REQ';
1167 RAISE FND_API.G_EXC_ERROR;
1168 END IF;
1169
1170 IF p_task_type = 'SUB'
1171 THEN
1172 FETCH_TASK_ATTIBUTES(
1173 p_task_id => p_reference_task_id,
1174 x_carrying_out_org_id => x_carrying_out_org_id,
1175 x_carrying_out_org_name => x_carrying_out_org_name,
1176 x_work_type_id => x_work_type_id,
1177 x_work_type_name => x_work_type_name,
1178 x_service_type_code => x_service_type_code,
1179 x_service_type_name => x_service_type_name,
1180 x_return_status => x_return_status,
1181 x_error_msg_code => x_error_msg_code
1182 );
1183
1184 ELSIF p_task_type = 'PEER'
1185 THEN
1186 OPEN cur_pa_parent_task;
1187 FETCH cur_pa_parent_task INTO l_parent_task_id, l_project_id;
1188 CLOSE cur_pa_parent_task;
1189
1190 --if parent of the reference task exists then get the attributes --of the parent task.
1191 IF l_parent_task_id IS NOT NULL
1192 THEN
1193 FETCH_TASK_ATTIBUTES(
1194 p_task_id => l_parent_task_id,
1195 x_carrying_out_org_id => x_carrying_out_org_id,
1196 x_carrying_out_org_name => x_carrying_out_org_name,
1197 x_work_type_id => x_work_type_id,
1198 x_work_type_name => x_work_type_name,
1199 x_service_type_code => x_service_type_code,
1200 x_service_type_name => x_service_type_name,
1201 x_return_status => x_return_status,
1202 x_error_msg_code => x_error_msg_code
1203 );
1204 --otherwise fetch the attributes of their project.
1205 ELSE
1206 FETCH_PROJECT_ATTIBUTES(
1207 p_project_id => l_project_id,
1208 x_carrying_out_org_id => x_carrying_out_org_id,
1209 x_carrying_out_org_name => x_carrying_out_org_name,
1210 x_work_type_id => x_work_type_id,
1211 x_work_type_name => x_work_type_name,
1212 x_service_type_code => x_service_type_code,
1213 x_service_type_name => x_service_type_name,
1214 x_return_status => x_return_status,
1215 x_error_msg_code => x_error_msg_code
1216 );
1217 END IF;
1218 END IF;
1219
1220 EXCEPTION
1221 WHEN FND_API.G_EXC_ERROR THEN
1222 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1223 -- 4537865 : RESET other OUT params too
1224 x_carrying_out_org_id := NULL ;
1225 x_carrying_out_org_name := NULL ;
1226 x_work_type_id := NULL ;
1227 x_work_type_name := NULL ;
1228 x_service_type_code := NULL ;
1229 x_service_type_name := NULL ;
1230
1231 WHEN OTHERS THEN
1232 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1233 -- 4537865 : Start
1234 x_carrying_out_org_id := NULL ;
1235 x_carrying_out_org_name := NULL ;
1236 x_work_type_id := NULL ;
1237 x_work_type_name := NULL ;
1238 x_service_type_code := NULL ;
1239 x_service_type_name := NULL ;
1240
1241 x_error_msg_code := SQLCODE ;
1242 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UILS',
1243 p_procedure_name => 'DEFAULT_TASK_ATTRIBUTES',
1244 p_error_text => SUBSTRB(SQLERRM,1,240));
1245 -- 4537865 : End
1246 RAISE;
1247 END DEFAULT_TASK_ATTRIBUTES;
1248
1249 -- API name : FETCH_TASK_ATTIBUTES
1250 -- Type : Utility procedure
1251 -- Pre-reqs : None
1252 -- Return Value : N/A
1253 -- Prameters
1254 -- p_task_id IN NUMBER REQUIRED
1255 -- x_carrying_out_org_id OUT NUMBER REQUIRED
1256 -- x_carrying_out_org_name OUT VARCHAR2 REQUIRED
1257 -- x_work_type_id OUT NUMBER REQUIRED
1258 -- x_work_type_name OUT VARCHAR2 REQUIRED
1259 -- x_service_type_code OUT VARCHAR2 REQUIRED
1260 -- x_service_type_name OUT VARCHAR2 REQUIRED
1261 -- x_return_status OUT VARCHAR2 REQUIRED
1262 -- x_error_msg_code OUT VARCHAR2 REQUIRED
1263 --
1264 -- History
1265 --
1266 -- 14-JUN-01 Majid Ansari -Created
1267 --
1268 --
1269
1270 PROCEDURE FETCH_TASK_ATTIBUTES(
1271 p_task_id IN NUMBER,
1272 x_carrying_out_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1273 x_carrying_out_org_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1274 x_work_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1275 x_work_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1276 x_service_type_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1277 x_service_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1278 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1279 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1280 ) AS
1281
1282 /* Bug 2680486 -- Performance changes -- To avoid Non-mergable view issue,
1283 Changed PA_WORK_TYPES_VL to PA_WORK_TYPES_TL and added condition for userenev('lang')*/
1284
1285 CURSOR cur_pa_tasks_sub
1286 IS
1287 SELECT PT.CARRYING_OUT_ORGANIZATION_ID
1288 ,HOU.NAME CARRYING_OUT_ORGANIZATION_NAME
1289 ,PT.WORK_TYPE_ID
1290 ,PWT.NAME WORK_TYPE_NAME
1291 ,PT.SERVICE_TYPE_CODE
1292 ,PL.MEANING SERVICE_TYPE_NAME
1293 FROM PA_TASKS PT
1294 ,HR_ORGANIZATION_UNITS HOU
1295 ,PA_WORK_TYPES_TL PWT
1296 ,PA_LOOKUPS PL
1297 WHERE PT.TASK_ID = p_task_id
1298 AND PT.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID
1299 AND PT.WORK_TYPE_ID = PWT.WORK_TYPE_ID(+)
1300 AND userenv('lang') = PWT.language(+)
1301 AND PT.SERVICE_TYPE_CODE = PL.LOOKUP_CODE(+)
1302 AND PL.LOOKUP_TYPE(+) = 'SERVICE_TYPE';
1303
1304 l_record_found VARCHAR2(1) := 'N';
1305 l_num_of_records NUMBER;
1306
1307 BEGIN
1308 x_return_status := FND_API.G_RET_STS_SUCCESS;
1309 OPEN cur_pa_tasks_sub;
1310 LOOP
1311 FETCH cur_pa_tasks_sub INTO x_carrying_out_org_id
1312 ,x_carrying_out_org_name
1313 ,x_work_type_id
1314 ,x_work_type_name
1315 ,x_service_type_code
1316 ,x_service_type_name;
1317 IF cur_pa_tasks_sub%NOTFOUND
1318 THEN
1319 EXIT;
1320 ELSE
1321 l_record_found := 'Y';
1322 END IF;
1323 END LOOP;
1324 l_num_of_records := cur_pa_tasks_sub%ROWCOUNT;
1325 CLOSE cur_pa_tasks_sub;
1326 --more than one row is found
1327 IF l_num_of_records > 1 AND l_record_found = 'Y'
1328 THEN
1329 x_error_msg_code:= 'PA_PRJ_TOO_MANY_TASKS';
1330 RAISE TOO_MANY_ROWS;
1331 ELSIF l_num_of_records = 0 AND l_record_found = 'N'
1332 THEN
1333 --no row with p_task_id is found
1334 x_error_msg_code:= 'PA_PRJ_INV_TASK_ID';
1335 RAISE NO_DATA_FOUND;
1336 END IF;
1337 EXCEPTION
1338 WHEN no_data_found THEN
1339 x_return_status:= FND_API.G_RET_STS_ERROR;
1340 -- 4537865 : Start
1341 x_carrying_out_org_id := NULL ;
1342 x_carrying_out_org_name := NULL ;
1343 x_work_type_id := NULL ;
1344 x_work_type_name := NULL ;
1345 x_service_type_code := NULL ;
1346 x_service_type_name := NULL ;
1347 -- 4537865 : End
1348 WHEN too_many_rows THEN
1349 x_return_status:= FND_API.G_RET_STS_ERROR;
1350 -- 4537865 : Start
1351 x_carrying_out_org_id := NULL ;
1352 x_carrying_out_org_name := NULL ;
1353 x_work_type_id := NULL ;
1354 x_work_type_name := NULL ;
1355 x_service_type_code := NULL ;
1356 x_service_type_name := NULL ;
1357
1358 -- 4537865 : End
1359 WHEN OTHERS THEN
1360 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1361 -- 4537865 : Start
1362 x_carrying_out_org_id := NULL ;
1363 x_carrying_out_org_name := NULL ;
1364 x_work_type_id := NULL ;
1365 x_work_type_name := NULL ;
1366 x_service_type_code := NULL ;
1367 x_service_type_name := NULL ;
1368
1369 x_error_msg_code := SQLCODE ;
1370 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UILS',
1371 p_procedure_name => 'FETCH_TASK_ATTIBUTES',
1372 p_error_text => SUBSTRB(SQLERRM,1,240));
1373 -- 4537865 : End
1374 RAISE;
1375 END FETCH_TASK_ATTIBUTES;
1376
1377
1378 -- API name : FETCH_PROJECT_ATTIBUTES
1379 -- Type : Utility procedure
1380 -- Pre-reqs : None
1381 -- Return Value : N/A
1382 -- Prameters
1383 -- p_project_id IN NUMBER REQUIRED
1384 -- x_carrying_out_org_id OUT NUMBER REQUIRED
1385 -- x_carrying_out_org_name OUT VARCHAR2 REQUIRED
1386 -- x_work_type_id OUT NUMBER REQUIRED
1387 -- x_work_type_name OUT VARCHAR2 REQUIRED
1388 -- x_service_type_code OUT VARCHAR2 REQUIRED
1389 -- x_service_type_name OUT VARCHAR2 REQUIRED
1390 -- x_return_status OUT VARCHAR2 REQUIRED
1391 -- x_error_msg_code OUT VARCHAR2 REQUIRED
1392 --
1393 -- History
1394 --
1395 -- 14-JUN-01 Majid Ansari -Created
1396 --
1397 --
1398
1399 PROCEDURE FETCH_PROJECT_ATTIBUTES(
1400 p_project_id IN NUMBER,
1401 x_carrying_out_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1402 x_carrying_out_org_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1403 x_work_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1404 x_work_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1405 x_service_type_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1406 x_service_type_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1407 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1408 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1409 ) AS
1410
1411 /* Bug 2680486 -- Performance changes -- To avoid Non-mergable view issue,
1412 Changed PA_WORK_TYPES_VL to PA_WORK_TYPES_TL and added condition for userenev('lang')*/
1413
1414 -- MOAC Changes: Bug 4363092: removed nvl with org_id
1415 CURSOR cur_pa_project
1416 IS
1417 SELECT PPA.CARRYING_OUT_ORGANIZATION_ID
1418 ,HOU.NAME CARRYING_OUT_ORGANIZATION_NAME
1419 ,PPA.WORK_TYPE_ID
1420 ,PWT.NAME WORK_TYPE_NAME
1421 ,PPT.SERVICE_TYPE_CODE
1422 ,PL.MEANING SERVICE_TYPE_NAME
1423 FROM PA_PROJECTS_ALL PPA
1424 ,HR_ORGANIZATION_UNITS HOU
1425 ,PA_WORK_TYPES_TL PWT
1426 ,PA_LOOKUPS PL
1427 ,PA_PROJECT_TYPES_ALL PPT
1428 WHERE PPA.PROJECT_ID = p_project_id
1429 AND PPA.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID
1430 AND PPA.WORK_TYPE_ID = PWT.WORK_TYPE_ID(+)
1431 AND userenv('lang') = PWT.language(+)
1432 AND PPA.PROJECT_TYPE = PPT.PROJECT_TYPE
1433 AND PPA.ORG_ID = PPT.ORG_ID
1434 AND PPT.SERVICE_TYPE_CODE = PL.LOOKUP_CODE(+)
1435 AND PL.LOOKUP_TYPE(+) = 'SERVICE_TYPE';
1436
1437 l_record_found VARCHAR2(1) := 'N';
1438 l_num_of_records NUMBER;
1439
1440 BEGIN
1441 x_return_status := FND_API.G_RET_STS_SUCCESS;
1442 OPEN cur_pa_project;
1443 LOOP
1444 FETCH cur_pa_project INTO x_carrying_out_org_id
1445 ,x_carrying_out_org_name
1446 ,x_work_type_id
1447 ,x_work_type_name
1448 ,x_service_type_code
1449 ,x_service_type_name;
1450 IF cur_pa_project%NOTFOUND
1451 THEN
1452 EXIT;
1453 ELSE
1454 l_record_found := 'Y';
1455 END IF;
1456 END LOOP;
1457 l_num_of_records := cur_pa_project%ROWCOUNT;
1458 CLOSE cur_pa_project;
1459 --more than one row is found
1460 IF l_num_of_records > 1 AND l_record_found = 'Y'
1461 THEN
1462 x_error_msg_code:= 'PA_PRJ_TOO_MANY_PROJ';
1463 RAISE TOO_MANY_ROWS;
1464 ELSIF l_num_of_records = 0 AND l_record_found = 'N'
1465 THEN
1466 --no row with p_task_id is found
1467 x_error_msg_code:= 'PA_PRJ_INV_PROJECT_ID';
1468 RAISE NO_DATA_FOUND;
1469 END IF;
1470 EXCEPTION
1471 WHEN no_data_found THEN
1472 x_return_status:= FND_API.G_RET_STS_ERROR;
1473 -- 4537865 :Start
1474 x_carrying_out_org_id := NULL ;
1475 x_carrying_out_org_name := NULL ;
1476 x_work_type_id := NULL ;
1477 x_work_type_name := NULL ;
1478 x_service_type_code := NULL ;
1479 x_service_type_name := NULL ;
1480
1481 -- 4537865 :End
1482 WHEN too_many_rows THEN
1483 x_return_status:= FND_API.G_RET_STS_ERROR;
1484 -- 4537865 :Start
1485 x_carrying_out_org_id := NULL ;
1486 x_carrying_out_org_name := NULL ;
1487 x_work_type_id := NULL ;
1488 x_work_type_name := NULL ;
1489 x_service_type_code := NULL ;
1490 x_service_type_name := NULL ;
1491
1492 -- 4537865 :End
1493 WHEN OTHERS THEN
1494 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1495 -- 4537865 :Start
1496 x_carrying_out_org_id := NULL ;
1497 x_carrying_out_org_name := NULL ;
1498 x_work_type_id := NULL ;
1499 x_work_type_name := NULL ;
1500 x_service_type_code := NULL ;
1501 x_service_type_name := NULL ;
1502
1503
1504 x_error_msg_code := SQLCODE ;
1505 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UILS',
1506 p_procedure_name => 'FETCH_PROJECT_ATTIBUTES',
1507 p_error_text => SUBSTRB(SQLERRM,1,240));
1508 -- 4537865 :End
1509 RAISE;
1510 END FETCH_PROJECT_ATTIBUTES;
1511
1512
1513 Function IsSummaryTask(p_project_id IN NUMBER,
1514 p_task_id IN NUMBER)
1515 return varchar2
1516 IS
1517
1518 cursor c1 IS
1519 select 'Y'
1520 from pa_tasks t
1521 where t.project_id = p_project_id and
1522 t.parent_task_id = p_task_id;
1523
1524 l_summary_flag VARCHAR2(1);
1525
1526 BEGIN
1527
1528 OPEN c1;
1529 FETCH c1 INTO l_summary_flag;
1530 IF c1%NOTFOUND THEN
1531 CLOSE c1;
1532 return 'N';
1533 ELSE
1534 CLOSE c1;
1535 return 'Y';
1536 END IF;
1537
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540 return 'N';
1541 END IsSummaryTask;
1542
1543 -- API name : GetWbsLevel
1544 -- Type : Utility Procedure
1545 -- Pre-reqs : None
1546 -- Return Value : N/A
1547 -- Prameters
1548 -- p_project_id IN NUMBER REQUIRED
1549 -- p_task_id IN NUMBER REQUIRED
1550 -- x_task_level OUT NUMBER REQUIRED
1551 -- x_task_level_above OUT NUMBER REQUIRED
1552 -- x_return_status OUT VARCHAR2 REQUIRED
1553 -- x_error_msg_code OUT VARCHAR2 REQUIRED
1554 --
1555 -- History
1556 --
1557 -- 25-JUN-01 Majid Ansari -Created
1558 --
1559 --
1560
1561 PROCEDURE GetWbsLevel(
1562 p_project_id IN NUMBER,
1563 p_task_id IN NUMBER,
1564
1565 x_task_level OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1566 x_parent_task_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1567 x_top_task_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1568 x_display_sequence OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1569
1570 x_task_level_above OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1571 x_parent_task_id_above OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1572 x_top_task_id_above OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1573 x_display_sequence_above OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1574
1575 x_task_id_above OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1576 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1577 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1578 ) AS
1579
1580 --Get wbs of the task shown above the indenting task.
1581 /* CURSOR cur_wbs_above
1582 IS
1583 SELECT wbs_level, task_id, top_task_id, parent_task_id, display_sequence
1584 FROM pa_tasks
1585 WHERE project_id = p_project_id
1586 AND display_sequence = ( SELECT max( display_sequence )
1587 FROM pa_tasks
1588 WHERE project_id = p_project_id
1589 AND display_sequence < ( SELECT display_sequence
1590 FROM pa_tasks
1591 WHERE project_id = p_project_id
1592 AND task_id = p_task_id ) );*/
1593
1594 --WITH THE CHANGE IN THE DATA MODEL p_task_id will from now act as p_task_version_id
1595 CURSOR cur_wbs_above
1596 IS
1597 SELECT pt.wbs_level, pt.task_id, pt.top_task_id, pt.parent_task_id, ppev.display_sequence
1598 FROM pa_tasks pt, pa_proj_element_versions ppev
1599 WHERE pt.project_id = p_project_id
1600 AND ppev.proj_element_id = pt.task_id
1601 AND ppev.display_sequence = ( SELECT max( display_sequence )
1602 FROM pa_proj_element_versions
1603 WHERE project_id = p_project_id
1604 AND display_sequence < ( SELECT display_sequence
1605 FROM pa_proj_element_versions
1606 WHERE project_id = p_project_id
1607 AND proj_element_id = p_task_id ) );
1608 ---Get the wbs of the task being indented.
1609 /* CURSOR cur_wbs
1610 IS
1611 SELECT wbs_level, top_task_id, parent_task_id, display_sequence
1612 FROM pa_tasks
1613 WHERE project_id = p_project_id
1614 AND task_id = p_task_id;*/
1615
1616 CURSOR cur_wbs
1617 IS
1618 SELECT pt.wbs_level, pt.top_task_id, pt.parent_task_id, ppev.display_sequence
1619 FROM pa_tasks pt, pa_proj_element_versions ppev
1620 WHERE pt.project_id = p_project_id
1621 AND ppev.proj_element_id = p_task_id
1622 AND ppev.proj_element_id = pt.task_id;
1623
1624 BEGIN
1625
1626 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1627
1628 OPEN cur_wbs_above;
1629 FETCH cur_wbs_above INTO x_task_level_above, x_task_id_above, x_top_task_id_above,
1630 x_parent_task_id_above, x_display_sequence_above;
1631 CLOSE cur_wbs_above;
1632
1633 OPEN cur_wbs;
1634 FETCH cur_wbs INTO x_task_level, x_top_task_id,
1635 x_parent_task_id, x_display_sequence;
1636 CLOSE cur_wbs;
1637
1638 EXCEPTION
1639 WHEN OTHERS THEN
1640 x_return_status:= FND_API.G_RET_STS_ERROR;
1641 -- 4537865 : Start
1642 x_task_level := 1 ; -- set task level as 1 so that ,caller API throws error properly
1643 x_parent_task_id := NULL ;
1644 x_top_task_id := NULL ;
1645 x_display_sequence := 1; -- set x_display_sequence as 1 so that ,caller API throws error properly
1646
1647 x_task_level_above := NULL ;
1648 x_parent_task_id_above := NULL ;
1649 x_top_task_id_above := NULL ;
1650 x_display_sequence_above := NULL ;
1651
1652 x_task_id_above := NULL ;
1653 -- 4537865 : End
1654 END GetWbsLevel;
1655
1656 -- API name : REF_PRJ_TASK_ID_REQ_CHECK
1657 -- Type : Utility procedure
1658 -- Pre-reqs : None
1659 -- Return Value : N/A
1660 -- Prameters
1661 -- p_reference_project_id IN NUMBER REQUIRED
1662 -- p_reference_task_id IN NUMBER REQUIRED
1663 -- x_return_status OUT VARCHAR2 REQUIRED
1664 -- x_error_msg_code OUT VARCHAR2 REQUIRED
1665 --
1666 -- History
1667 --
1668 -- 25-MAY-01 Majid Ansari -Created
1669 --
1670 --
1671
1672 PROCEDURE REF_PRJ_TASK_ID_REQ_CHECK(
1673 p_reference_project_id IN NUMBER ,
1674 p_reference_task_id IN NUMBER ,
1675 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1676 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1677 ) AS
1678 BEGIN
1679 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1680 IF p_reference_project_id IS NULL OR p_reference_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1681 THEN
1682 x_error_msg_code := 'PA_TASK_TARGET_PRJ_ID_REQ';
1683 x_return_status:= FND_API.G_RET_STS_ERROR;
1684 RAISE FND_API.G_EXC_ERROR;
1685 END IF;
1686
1687 IF p_reference_task_id IS NULL OR p_reference_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1688 THEN
1689 x_error_msg_code := 'PA_TASK_TARGET_TASK_ID_REQ';
1690 x_return_status:= FND_API.G_RET_STS_ERROR;
1691 RAISE FND_API.G_EXC_ERROR;
1692 END IF;
1693 EXCEPTION
1694 WHEN FND_API.G_EXC_ERROR THEN
1695 x_return_status:= FND_API.G_RET_STS_ERROR;
1696 END REF_PRJ_TASK_ID_REQ_CHECK;
1697
1698
1699 -- API name : SRC_PRJ_TASK_ID_REQ_CHECK
1700 -- Type : Utility procedure
1701 -- Pre-reqs : None
1702 -- Return Value : N/A
1703 -- Prameters
1704 -- p_project_id IN NUMBER REQUIRED
1705 -- p_task_id IN NUMBER REQUIRED
1706 -- x_return_status OUT VARCHAR2 REQUIRED
1707 -- x_error_msg_code OUT VARCHAR2 REQUIRED
1708 --
1709 -- History
1710 --
1711 -- 25-MAY-01 Majid Ansari -Created
1712 --
1713 --
1714
1715 PROCEDURE SRC_PRJ_TASK_ID_REQ_CHECK(
1716 p_project_id IN NUMBER ,
1717 p_task_id IN NUMBER ,
1718 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1719 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1720 ) AS
1721 BEGIN
1722 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1723 IF p_project_id IS NULL OR p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1724 THEN
1725 x_error_msg_code := 'PA_TASK_SOURCE_PRJ_ID_REQ';
1726 x_return_status:= FND_API.G_RET_STS_ERROR;
1727 RAISE FND_API.G_EXC_ERROR;
1728 END IF;
1729
1730 IF p_task_id IS NULL OR p_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1731 THEN
1732 x_error_msg_code := 'PA_TASK_SOURCE_TASK_ID_REQ';
1733 x_return_status:= FND_API.G_RET_STS_ERROR;
1734 RAISE FND_API.G_EXC_ERROR;
1735 END IF;
1736 EXCEPTION
1737 WHEN FND_API.G_EXC_ERROR THEN
1738 x_return_status:= FND_API.G_RET_STS_ERROR;
1739 END SRC_PRJ_TASK_ID_REQ_CHECK;
1740
1741
1742 --procedure from pa_project_check_pvt.check_start_end_date_Pvt
1743 PROCEDURE check_start_end_date
1744 ( p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1745 ,p_context IN VARCHAR2 := 'START'
1746 ,p_old_start_date IN DATE
1747 ,p_new_start_date IN DATE
1748 ,p_old_end_date IN DATE
1749 ,p_new_end_date IN DATE
1750 ,p_update_start_date_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1751 ,p_update_end_date_flag OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1752 IS
1753 l_api_name CONSTANT VARCHAR2(30) := 'check_start_end_date';
1754 l_start_date DATE;
1755 l_end_date DATE;
1756 l_meaning pa_lookups.meaning%TYPE;
1757 BEGIN
1758 p_return_status := FND_API.G_RET_STS_SUCCESS;
1759
1760 -- added by hsiu
1761 -- set token
1762 IF p_context = 'START' then
1763 l_meaning := null;
1764 else
1765 select meaning into l_meaning
1766 from pa_lookups
1767 where lookup_type = 'PA_DATE' and lookup_code = p_context;
1768 end if;
1769
1770 IF p_new_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1771 AND p_new_start_date IS NOT NULL --redundant, but added for clarity
1772 THEN
1773 IF p_new_start_date <> NVL(p_old_start_date,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1774 THEN
1775 p_update_start_date_flag := 'Y';
1776 l_start_date := p_new_start_date;
1777 ELSE
1778 p_update_start_date_flag := 'N';
1779 l_start_date := p_new_start_date;
1780 END IF;
1781
1782 IF p_new_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1783 AND p_new_end_date IS NOT NULL --redundant, but added for clarity
1784 THEN
1785 IF p_new_end_date <> NVL(p_old_end_date,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1786 THEN
1787 p_update_end_date_flag := 'Y';
1788 l_end_date := p_new_end_date;
1789 ELSE
1790 p_update_end_date_flag := 'N';
1791 l_end_date := p_new_end_date;
1792 END IF;
1793
1794 IF l_start_date > l_end_date
1795 THEN
1796 IF FND_MSG_PUB.check_msg_level
1797 (FND_MSG_PUB.G_MSG_LVL_ERROR)
1798 THEN
1799 /*
1800 pa_interface_utils_pub.map_new_amg_msg
1801 ( p_old_message_code => 'PA_INVALID_START_DATE2'
1802 ,p_msg_attribute => 'CHANGE'
1803 ,p_resize_flag => 'N'
1804 ,p_msg_context => 'GENERAL'
1805 ,p_attribute1 => ''
1806 ,p_attribute2 => ''
1807 ,p_attribute3 => ''
1808 ,p_attribute4 => ''
1809 ,p_attribute5 => '');
1810
1811 */
1812 fnd_message.set_name('PA', 'PA_INVALID_START_DATE2');
1813 --hsiu: commented for bug 2686499
1814 -- fnd_message.set_token('PA_DATE',l_meaning);
1815 fnd_msg_pub.add;
1816 END IF;
1817 RAISE FND_API.G_EXC_ERROR;
1818 END IF;
1819
1820 ELSIF p_new_end_date IS NULL
1821 THEN
1822 IF p_old_end_date IS NOT NULL
1823 THEN
1824 p_update_end_date_flag := 'Y';
1825 ELSE
1826 p_update_end_date_flag := 'N';
1827 END IF;
1828 ELSE
1829
1830 p_update_end_date_flag := 'N';
1831
1832 IF p_old_end_date IS NULL
1833 THEN
1834 NULL;
1835 ELSE
1836
1837 IF l_start_date > p_old_end_date THEN
1838 IF FND_MSG_PUB.check_msg_level
1839 (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1840 /*
1841 pa_interface_utils_pub.map_new_amg_msg
1842 ( p_old_message_code => 'PA_INVALID_START_DATE2'
1843 ,p_msg_attribute => 'CHANGE'
1844 ,p_resize_flag => 'N'
1845 ,p_msg_context => 'GENERAL'
1846 ,p_attribute1 => ''
1847 ,p_attribute2 => ''
1848 ,p_attribute3 => ''
1849 ,p_attribute4 => ''
1850 ,p_attribute5 => '');
1851 fnd_message.set_token('PA_DATE',l_meaning);
1852 */
1853 fnd_message.set_name('PA', 'PA_INVALID_START_DATE2');
1854 --commented for bug 2686499
1855 -- fnd_message.set_token('PA_DATE',l_meaning);
1856 fnd_msg_pub.add;
1857 END IF;
1858 RAISE FND_API.G_EXC_ERROR;
1859 END IF;
1860 END IF;
1861 END IF;
1862
1863 ELSIF p_new_start_date IS NULL
1864 THEN
1865 IF p_old_start_date IS NOT NULL
1866 THEN
1867 p_update_start_date_flag := 'Y';
1868 ELSE
1869 p_update_start_date_flag := 'N';
1870 END IF;
1871
1872 IF p_new_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1873 AND p_new_end_date IS NOT NULL
1874 THEN
1875 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1876 THEN
1877 /*
1878 pa_interface_utils_pub.map_new_amg_msg
1879 ( p_old_message_code => 'PA_DATES_INVALID'
1880 ,p_msg_attribute => 'CHANGE'
1881 ,p_resize_flag => 'N'
1882 ,p_msg_context => 'GENERAL'
1883 ,p_attribute1 => ''
1884 ,p_attribute2 => ''
1885 ,p_attribute3 => ''
1886 ,p_attribute4 => ''
1887 ,p_attribute5 => '');
1888 fnd_message.set_token('PA_DATE',l_meaning);
1889 */
1890 fnd_message.set_name('PA', 'PA_DATES_INVALID');
1891 fnd_message.set_token('PA_DATE',l_meaning);
1892 fnd_msg_pub.add;
1893 END IF;
1894
1895 RAISE FND_API.G_EXC_ERROR;
1896
1897 ELSIF p_new_end_date IS NULL
1898 THEN
1899 IF p_old_end_date IS NOT NULL
1900 THEN
1901 p_update_end_date_flag := 'Y';
1902 ELSE
1903 p_update_end_date_flag := 'N';
1904 END IF;
1905 ELSE
1906
1907 p_update_end_date_flag := 'N';
1908
1909 IF p_old_end_date IS NOT NULL --start_date is null
1910 THEN
1911 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1912 THEN
1913 /*
1914 pa_interface_utils_pub.map_new_amg_msg
1915 ( p_old_message_code => 'PA_DATES_INVALID'
1916 ,p_msg_attribute => 'CHANGE'
1917 ,p_resize_flag => 'N'
1918 ,p_msg_context => 'GENERAL'
1919 ,p_attribute1 => ''
1920 ,p_attribute2 => ''
1921 ,p_attribute3 => ''
1922 ,p_attribute4 => ''
1923 ,p_attribute5 => '');
1924 fnd_message.set_token('PA_DATE',l_meaning);
1925 */
1926 fnd_message.set_name('PA', 'PA_INVALID_START_DATE2');
1927 --commented for bug 2686499
1928 -- fnd_message.set_token('PA_DATE',l_meaning);
1929 fnd_msg_pub.add;
1930 END IF;
1931
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934 END IF;
1935
1936 ELSE --p_new_start_date was not passed
1937
1938 p_update_start_date_flag := 'N';
1939
1940 IF p_new_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1941 AND p_new_end_date IS NOT NULL
1942 THEN
1943 IF p_new_end_date <> nvl(p_old_end_date,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1944 THEN
1945 p_update_end_date_flag := 'Y';
1946
1947 IF p_old_start_date IS NULL
1948 OR p_old_start_date > p_new_end_date
1949 THEN
1950 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1951 THEN
1952 /*
1953 pa_interface_utils_pub.map_new_amg_msg
1954 ( p_old_message_code => 'PA_INVALID_START_DATE2'
1955 ,p_msg_attribute => 'CHANGE'
1956 ,p_resize_flag => 'N'
1957 ,p_msg_context => 'GENERAL'
1958 ,p_attribute1 => ''
1959 ,p_attribute2 => ''
1960 ,p_attribute3 => ''
1961 ,p_attribute4 => ''
1962 ,p_attribute5 => '');
1963 fnd_message.set_token('PA_DATE',l_meaning);
1964 */
1965 fnd_message.set_name('PA', 'PA_INVALID_START_DATE2');
1966 --commented for bug 2686499
1967 -- fnd_message.set_token('PA_DATE',l_meaning);
1968 fnd_msg_pub.add;
1969 END IF;
1970
1971 RAISE FND_API.G_EXC_ERROR;
1972 END IF;
1973
1974 ELSE
1975 p_update_end_date_flag := 'N';
1976
1977 END IF;
1978
1979 ELSIF p_new_end_date IS NULL
1980 THEN
1981 IF p_old_end_date IS NOT NULL
1982 THEN
1983 p_update_end_date_flag := 'Y';
1984
1985 ELSE
1986 p_update_end_date_flag := 'N';
1987
1988 END IF;
1989 ELSE
1990 p_update_end_date_flag := 'N';
1991
1992 END IF;
1993 END IF;
1994
1995
1996 EXCEPTION
1997
1998 WHEN FND_API.G_EXC_ERROR
1999 THEN
2000
2001 p_return_status := FND_API.G_RET_STS_ERROR;
2002
2003 -- 4537865 : Start RESET other out params too.
2004 p_update_start_date_flag := NULL ;
2005 p_update_end_date_flag := NULL ;
2006
2007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2008 THEN
2009
2010 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2011
2012 -- 4537865 : Start RESET other out params too.
2013 p_update_start_date_flag := NULL ;
2014 p_update_end_date_flag := NULL ;
2015 -- 4537865 : End
2016
2017 WHEN OTHERS THEN
2018
2019 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2020 -- 4537865 : Start RESET other out params too.
2021 p_update_start_date_flag := NULL ;
2022 p_update_end_date_flag := NULL ;
2023 -- 4537865 : End
2024
2025 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2026 THEN
2027 FND_MSG_PUB.add_exc_msg
2028 ( p_pkg_name => 'PA_TASKS_MAINT_UTILS'
2029 , p_procedure_name => l_api_name );
2030
2031 END IF;
2032
2033 END check_start_end_date;
2034
2035 -- API name : LOCK_PROJECT
2036 -- Type : Utility procedure
2037 -- Pre-reqs : None
2038 -- Return Value : N/A
2039 -- Prameters
2040 -- p_project_id IN NUMBER REQUIRED
2041 -- p_wbs_record_version_number IN NUMBER REQUIRED
2042 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2043 -- x_return_status OUT VARCHAR2 REQUIRED
2044 -- x_error_msg_code OUT VARCHAR2 REQUIRED
2045 --
2046 -- History
2047 --
2048 -- 16-JUL-01 Majid Ansari -Created
2049 --
2050 --
2051
2052 PROCEDURE LOCK_PROJECT(
2053 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
2054 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
2055 p_project_id IN NUMBER,
2056 p_wbs_record_version_number IN NUMBER,
2057 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2058 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2059 ) AS
2060 l_dummy_char VARCHAR2(1);
2061 BEGIN
2062 IF p_validate_only <> FND_API.G_TRUE
2063 THEN
2064 BEGIN
2065 SELECT 'x' INTO l_dummy_char
2066 -- HY: changed from pa_projects_all to pa_proj_elem_ver_structure
2067 FROM pa_proj_elem_ver_structure
2068 WHERE project_id = p_project_id
2069 AND wbs_record_version_number = p_wbs_record_version_number
2070 FOR UPDATE OF record_version_number NOWAIT;
2071
2072 EXCEPTION
2073 WHEN TIMEOUT_ON_RESOURCE THEN
2074 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2075 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2076 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2077 x_return_status := 'E' ;
2078 WHEN NO_DATA_FOUND THEN
2079 if p_calling_module = 'FORM' then
2080 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
2081 p_msg_name => 'FORM_RECORD_CHANGED');
2082 x_msg_data := 'FORM_RECORD_CHANGED';
2083 else
2084 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2085 p_msg_name => 'PA_XC_RECORD_CHANGED');
2086 x_msg_data := 'PA_XC_RECORD_CHANGED';
2087 end if;
2088 x_return_status := 'E' ;
2089 WHEN OTHERS THEN
2090 IF SQLCODE = -54 THEN
2091 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2092 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2093 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2094 x_return_status := 'E' ;
2095 ELSE
2096 raise;
2097 END IF;
2098 END;
2099 ELSE
2100 BEGIN
2101 SELECT 'x' INTO l_dummy_char
2102 -- HY: changed from pa_projects_all to pa_proj_elem_ver_structure
2103 FROM pa_proj_elem_ver_structure
2104 WHERE project_id = p_project_id
2105 AND wbs_record_version_number = p_wbs_record_version_number;
2106 EXCEPTION
2107 WHEN NO_DATA_FOUND THEN
2108 if p_calling_module = 'FORM' then
2109 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
2110 p_msg_name => 'FORM_RECORD_CHANGED');
2111 x_msg_data := 'FORM_RECORD_CHANGED';
2112 else
2113 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2114 p_msg_name => 'PA_XC_RECORD_CHANGED');
2115 x_msg_data := 'PA_XC_RECORD_CHANGED';
2116 end if;
2117 x_return_status := 'E' ;
2118 WHEN OTHERS THEN
2119 IF SQLCODE = -54 THEN
2120 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2121 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2122 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2123 x_return_status := 'E' ;
2124 END IF;
2125 END;
2126 END IF;
2127 END LOCK_PROJECT;
2128
2129 -- API name : INCREMENT_WBS_REC_VER_NUM
2130 -- Type : Utility procedure
2131 -- Pre-reqs : None
2132 -- Return Value : N/A
2133 -- Prameters
2134 -- p_project_id IN NUMBER REQUIRED
2135 -- p_wbs_record_version_number IN NUMBER
2136 -- x_return_status OUT VARCHAR2 REQUIRED
2137 --
2138 -- History
2139 --
2140 -- 16-JUL-01 Majid Ansari -Created
2141 --
2142 --
2143
2144 PROCEDURE INCREMENT_WBS_REC_VER_NUM(
2145 p_project_id IN NUMBER,
2146 p_wbs_record_version_number IN NUMBER,
2147 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2148 ) AS
2149 BEGIN
2150 x_return_status := 'S';
2151
2152 /* increment wbs_record_version_number for project id */
2153 -- HY: changed from pa_projects_all to pa_proj_elem_ver_structure
2154 UPDATE pa_proj_elem_ver_structure
2155 SET wbs_record_version_number = NVL( wbs_record_version_number, 0 ) + 1
2156 WHERE project_id = p_project_id
2157 AND wbs_record_version_number = p_wbs_record_version_number;
2158
2159 EXCEPTION WHEN OTHERS THEN
2160 x_return_status := 'E';
2161 END INCREMENT_WBS_REC_VER_NUM;
2162
2163
2164 -- API name : GET_TASK_MANAGER_PROFILE
2165 -- Type : Utility procedure
2166 -- Pre-reqs : None
2167 -- Return Value : Y or N
2168 -- Parameters : N/A
2169 --
2170 -- History
2171 --
2172 -- 21-NOV-02 hubert siu -Created
2173 --
2174 --
2175 FUNCTION GET_TASK_MANAGER_PROFILE RETURN VARCHAR2
2176 IS
2177 l_ret VARCHAR2(1);
2178 BEGIN
2179 l_ret := fnd_profile.value('PA_TM_PROJ_MEMBER');
2180 IF (l_ret IS NULL) THEN
2181 return 'N';
2182 END IF;
2183 return l_ret;
2184 END GET_TASK_MANAGER_PROFILE;
2185
2186 --Begin add rtarway FP.M development
2187
2188 -- Procedure : CHECK_MOVE_FINANCIAL_TASK_OK
2189 -- Type : Public Procedure
2190 -- Purpose : The API will be used to check the financial task is not getting moved of Workplan task.
2191 -- : This API needs to be called from Move/Copy and Indent task
2192 -- Note : If the task being moved is financial task, check the task under which it is being moved.
2193 -- : If it is non-financial task, raise error.
2194 -- Assumptions : The API assumes that API is called under partial sharing case only
2195
2196 -- Parameters Type Required Description and Purpose
2197 -- --------------------------- ------ -------- --------------------------------------------------------
2198 -- p_task_version_id NUMBER Yes This indicates the financial task which is being moved
2199 -- p_ref_task_version_id NUMBER Yes This task indicates the task under which the financial task is being moved.
2200
2201 PROCEDURE CHECK_MOVE_FINANCIAL_TASK_OK
2202 (
2203 p_api_version IN NUMBER := 1.0
2204 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2205 , p_debug_mode IN VARCHAR2 := 'N'
2206 , p_task_version_id IN NUMBER
2207 , p_ref_task_version_id IN NUMBER
2208 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2209 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2210 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2211 , x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2212 )
2213 IS
2214
2215 l_msg_count NUMBER := 0;
2216 l_data VARCHAR2(2000);
2217 l_msg_data VARCHAR2(2000);
2218 l_msg_index_out NUMBER;
2219 l_debug_mode VARCHAR2(1);
2220
2221 l_fin_task_flag_ptask VARCHAR2(1);
2222 l_fin_task_flag_reftask VARCHAR2(1);
2223
2224 l_debug_level2 CONSTANT NUMBER := 2;
2225 l_debug_level3 CONSTANT NUMBER := 3;
2226 l_debug_level4 CONSTANT NUMBER := 4;
2227 l_debug_level5 CONSTANT NUMBER := 5;
2228
2229 --This CURSOR will fetch the financial task flag value for the passed task version id
2230
2231 CURSOR c_get_fin_task_flag (l_task_version_id NUMBER )
2232 IS
2233 SELECT FINANCIAL_TASK_FLAG
2234 FROM PA_PROJ_ELEMENT_VERSIONS plv
2235 WHERE plv.ELEMENT_VERSION_ID = l_task_version_id;
2236
2237 BEGIN
2238
2239 x_msg_count := 0;
2240 x_return_status := FND_API.G_RET_STS_SUCCESS;
2241 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2242
2243 IF l_debug_mode = 'Y' THEN
2244 PA_DEBUG.set_curr_function( p_function => 'CHECK_MOVE_FINANCIAL_TASK_OK',
2245 p_debug_mode => l_debug_mode );
2246 END IF;
2247
2248 IF l_debug_mode = 'Y' THEN
2249 Pa_Debug.g_err_stage:= 'PA_TASKS_MAINT_UTILS : CHECK_MOVE_FINANCIAL_TASK_OK : Printing Input parameters';
2250 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
2251 l_debug_level3);
2252
2253 Pa_Debug.WRITE(g_module_name,'p_task_version_id'||':'||p_task_version_id,
2254 l_debug_level3);
2255
2256 Pa_Debug.WRITE(g_module_name,'p_ref_task_version_id'||':'||p_ref_task_version_id,
2257 l_debug_level3);
2258 END IF;
2259
2260
2261 IF l_debug_mode = 'Y' THEN
2262 Pa_Debug.g_err_stage:= 'PA_TASKS_MAINT_UTILS : CHECK_MOVE_FINANCIAL_TASK_OK : Checking whether p_task_version_id is Financial or WorkPlan';
2263 Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
2264 END IF;
2265
2266
2267 OPEN c_get_fin_task_flag (p_task_version_id);
2268 FETCH c_get_fin_task_flag INTO l_fin_task_flag_ptask;
2269 CLOSE c_get_fin_task_flag ;
2270
2271 IF (l_fin_task_flag_ptask = 'Y')THEN
2272
2273
2274 OPEN c_get_fin_task_flag (p_ref_task_version_id);
2275 FETCH c_get_fin_task_flag INTO l_fin_task_flag_reftask;
2276 CLOSE c_get_fin_task_flag ;
2277
2278 IF (l_fin_task_flag_reftask = 'N')
2279 THEN
2280
2281 --Raise an error message
2282 x_error_msg_code := 'PA_CANT_MOVE_SELECTED_TASK';
2283 x_return_status := FND_API.G_RET_STS_ERROR;
2284 return;
2285 END IF;
2286 END IF;
2287
2288 EXCEPTION
2289
2290 WHEN OTHERS THEN
2291
2292 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2293 x_msg_count := 1;
2294 x_msg_data := SQLERRM;
2295
2296 -- 4537865 : RESET x_error_message_code also
2297 x_error_msg_code := SQLCODE ;
2298
2299 Fnd_Msg_Pub.add_exc_msg
2300 ( p_pkg_name => 'PA_TASKS_MAINT_UTILS'
2301 ,p_procedure_name => 'CHECK_MOVE_FINANCIAL_TASK_OK'
2302 ,p_error_text => x_msg_data);
2303
2304 IF l_debug_mode = 'Y' THEN
2305 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
2306 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
2307 l_debug_level5);
2308 Pa_Debug.reset_curr_function;
2309 END IF;
2310 RAISE;
2311 END CHECK_MOVE_FINANCIAL_TASK_OK;
2312
2313 -- Procedure : CHECK_WORKPLAN_TASK_EXISTS
2314 -- Type : Public Procedure
2315 -- Purpose : This API will be used to check whether there exists any workplan task under the
2316 -- : passed financial task. If there exists any workplan task below a financial task,
2317 -- : the task cannot be deleted.This API will be directly called from Delete Financial Task page
2318 -- Note :
2319 -- :
2320 -- Assumptions : This will be called with one task version id and not with many task version ids.
2321
2322 -- Parameters Type Required Description and Purpose
2323 -- --------------------------- ------ -------- --------------------------------------------------------
2324 -- p_task_version_id NUMBER Yes The column indicates the task Id that is getting deleted.
2325
2326
2327
2328 PROCEDURE CHECK_WORKPLAN_TASK_EXISTS
2329 (
2330 p_api_version IN NUMBER := 1.0
2331 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2332 , p_debug_mode IN NUMBER := 'N'
2333 , p_task_version_id IN NUMBER
2334 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2335 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2336 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2337 , x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2338 )
2339 IS
2340
2341 l_msg_count NUMBER := 0;
2342 l_data VARCHAR2(2000);
2343 l_msg_data VARCHAR2(2000);
2344 l_msg_index_out NUMBER;
2345 l_debug_mode VARCHAR2(1);
2346 l_counter NUMBER := 1;
2347 l_task_id pa_proj_element_versions.element_version_id%TYPE;
2348 l_fin_flag VARCHAR2(1);
2349
2350 l_debug_level2 CONSTANT NUMBER := 2;
2351 l_debug_level3 CONSTANT NUMBER := 3;
2352 l_debug_level4 CONSTANT NUMBER := 4;
2353 l_debug_level5 CONSTANT NUMBER := 5;
2354
2355
2356 --This cursor selects 'X' from dual if any non-financial task exists
2357 --in the hierarchy from PA_OBJECT_RELATIONSHIPS
2358 --for the passed task version id.
2359 CURSOR c_get_WP_rec(l_element_version_id NUMBER)
2360 IS
2361 SELECT 'X'
2362 FROM dual
2363 WHERE EXISTS
2364 (
2365 SELECT proj_element_id
2366 , element_version_id
2367 , financial_task_flag
2368 FROM PA_PROJ_ELEMENT_VERSIONS plv
2369 WHERE element_version_id
2370 IN
2371 ( -- This select statement tries to select childs task version ids
2372 SELECT object_id_to1
2373 FROM pa_object_relationships
2374 WHERE relationship_type='S'
2375 AND relationship_subtype='TASK_TO_TASK'
2376 START WITH object_id_from1 = l_element_version_id
2377 CONNECT BY object_id_from1 = PRIOR object_id_to1
2378 )
2379 AND financial_task_flag = 'N'
2380 );
2381
2382 BEGIN
2383 x_msg_count := 0;
2384 x_return_status := FND_API.G_RET_STS_SUCCESS;
2385 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2386
2387 IF l_debug_mode = 'Y' THEN
2388 PA_DEBUG.set_curr_function( p_function => 'CHECK_WORKPLAN_TASK_EXISTS',
2389 p_debug_mode => l_debug_mode );
2390 END IF;
2391
2392 IF l_debug_mode = 'Y' THEN
2393 Pa_Debug.g_err_stage:= 'PA_TASKS_MAINT_UTILS : CHECK_WORKPLAN_TASK_EXISTS : Printing Input parameters';
2394 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
2395 l_debug_level3);
2396
2397 Pa_Debug.WRITE(g_module_name,'p_task_version_id'||':'||p_task_version_id,
2398 l_debug_level3);
2399 END IF;
2400
2401 --If any non financial task exists in the hierarchy, the cursor will select 'X'
2402 OPEN c_get_WP_rec(p_task_version_id);
2403 FETCH c_get_WP_rec
2404 INTO l_fin_flag;
2405 CLOSE c_get_WP_rec;
2406
2407 IF (l_fin_flag = 'X')
2408 THEN
2409 --Populate error message
2410 x_error_msg_code := 'PA_WORKPLAN_TASK_EXISTS';
2411 x_return_status := FND_API.G_RET_STS_ERROR;
2412
2413 END IF;
2414 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS )
2415 THEN
2416 return;
2417 END IF;
2418
2419 EXCEPTION
2420
2421 WHEN OTHERS THEN
2422
2423 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2424 x_msg_count := 1;
2425 x_msg_data := SQLERRM;
2426
2427 -- 4537865 : RESET x_error_message_code also
2428 x_error_msg_code := SQLCODE ;
2429
2430 IF c_get_WP_rec%ISOPEN THEN
2431 CLOSE c_get_WP_rec;
2432 END IF;
2433
2434 Fnd_Msg_Pub.add_exc_msg
2435 ( p_pkg_name => 'PA_TASKS_MAINT_UTILS'
2436 ,p_procedure_name => 'CHECK_WORKPLAN_TASK_EXISTS'
2437 ,p_error_text => x_msg_data);
2438
2439 IF l_debug_mode = 'Y' THEN
2440 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
2441 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
2442 l_debug_level5);
2443 Pa_Debug.reset_curr_function;
2444 END IF;
2445 RAISE;
2446
2447 END CHECK_WORKPLAN_TASK_EXISTS;
2448
2449 -- End Add rtarway FP-M Development
2450
2451 --Added by rtarway for BUG 4081329
2452 /*Check_End_Date_EI
2453 This API validates if the passed end date is greater or equal to the maximum of all subtasks' EI dates
2454 */
2455 procedure Check_End_Date_EI( p_project_id IN NUMBER,
2456 p_task_id IN NUMBER,
2457 p_end_date IN DATE,
2458 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2459 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2460 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2461 IS
2462
2463 CURSOR cur_date(l_task_id NUMBER, l_project_id NUMBER ) IS
2464 SELECT MAX(pe.expenditure_item_date) ei_date
2465 FROM pa_expenditure_items_all pe
2466 WHERE pe.task_id IN (SELECT p.task_id
2467 FROM pa_tasks p
2468 where p.project_id = l_project_id
2469 --Added by rtarway for bug 4242216
2470 AND not exists
2471 (
2472 select parent_task_id
2473 from pa_tasks pt
2474 where pt.parent_task_id =p.task_id
2475 and pt.project_id=l_project_id
2476 )
2477 --Added by rtarway for bug 4242216
2478 START WITH p.task_id= l_task_id
2479 CONNECT BY PRIOR p.task_id = p.parent_task_id
2480 and p.project_id = l_project_id)
2481 AND pe.project_id = l_project_id;
2482
2483 x_ei_date pa_expenditure_items_all.expenditure_item_date%TYPE ;
2484
2485 -- Bug 6633233
2486 l_task_num pa_proj_elements.element_number%type;
2487
2488 BEGIN
2489
2490 OPEN cur_date(p_task_id, p_project_id);
2491 FETCH cur_date INTO x_ei_date ;
2492 IF cur_date%NOTFOUND THEN
2493 CLOSE cur_date ;
2494 ELSE
2495 IF (x_ei_date IS NOT NULL AND
2496 p_end_date IS NOT NULL AND
2497 p_end_date < x_ei_date ) THEN
2498 close cur_date;
2499 -- Start Changes for bug 6467429
2500 l_task_num := pa_task_utils.get_task_number(p_task_id);
2501 PA_UTILS.ADD_MESSAGE
2502 (p_app_short_name => 'PA',
2503 p_msg_name => 'PA_EI_INVALID_DATES_TSK',
2504 p_token1 => 'TASKNUM',
2505 p_value1 => l_task_num,
2506 p_token2 => 'EIFINISHDATE',
2507 p_value2 => x_ei_date);
2508 x_msg_data := 'PA_EI_INVALID_DATES_TSK';
2509 /*
2510 PA_UTILS.ADD_MESSAGE
2511 (p_app_short_name => 'PA',
2512 p_msg_name => 'PA_EI_INVALID_DATES',
2513 p_token1 => 'EIDATE',
2514 p_value1 => x_ei_date);
2515 x_msg_data := 'PA_EI_INVALID_DATES';
2516 */
2517 -- End Changes for bug 6467429
2518 RAISE FND_API.G_EXC_ERROR;
2519 ELSE
2520 close cur_date;
2521 END IF ;
2522 END IF ;
2523 x_return_status := FND_API.G_RET_STS_SUCCESS;
2524
2525 EXCEPTION
2526 WHEN FND_API.G_EXC_ERROR THEN
2527 x_return_status := FND_API.G_RET_STS_ERROR;
2528 x_msg_count := FND_MSG_PUB.count_msg;
2529 WHEN OTHERS THEN
2530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2531 -- 4537865 : RESET x_msg_count, x_msg_data also
2532 x_msg_count := 1 ;
2533 x_msg_data := SUBSTRB(SQLERRM,1,240);
2534
2535 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
2536 p_procedure_name => 'Check_End_Date_EI',
2537 p_error_text => x_msg_data); -- 4537865
2538 END Check_End_Date_EI;
2539 /*Check_Start_Date_EI
2540 This API validates if the passed start date is less or equal to the minimun of all subtasks' EI dates
2541 */
2542
2543 procedure Check_Start_Date_EI( p_project_id IN NUMBER,
2544 p_task_id IN NUMBER,
2545 p_start_date IN DATE,
2546 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2547 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2548 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2549 IS
2550
2551 CURSOR cur_start_date(l_task_id NUMBER, l_project_id NUMBER) IS
2552 SELECT MIN(pe.expenditure_item_date) ei_date
2553 FROM pa_expenditure_items_all pe
2554 WHERE pe.task_id IN (
2555 SELECT p.task_id
2556 FROM pa_tasks p
2557 WHERE p.project_id = l_project_id
2558 --Added by rtarway for bug 4242216
2559 AND not exists
2560 (
2561 select parent_task_id
2562 from pa_tasks pt
2563 where pt.parent_task_id =p.task_id
2564 and pt.project_id=l_project_id
2565 )
2566 --Added by rtarway for bug 4242216
2567 START WITH p.task_id= l_task_id
2568 CONNECT BY PRIOR p.task_id = p.parent_task_id
2569 AND p.project_id = l_project_id
2570 )
2571 and pe.project_id = l_project_id;
2572
2573 x_ei_min_date pa_expenditure_items_all.expenditure_item_date%TYPE ;
2574
2575 -- Bug 6633233
2576 l_task_num pa_proj_elements.element_number%type;
2577
2578 BEGIN
2579
2580 OPEN cur_start_date(p_task_id, p_project_id);
2581 FETCH cur_start_date INTO x_ei_min_date ;
2582 IF cur_start_date%NOTFOUND THEN
2583 CLOSE cur_start_date ;
2584 ELSE
2585 IF (x_ei_min_date IS NOT NULL AND
2586 p_start_date IS NOT NULL AND
2587 p_start_date > x_ei_min_date ) THEN
2588 close cur_start_date;
2589 -- Start Changes for Bug 6633233
2590 l_task_num := pa_task_utils.get_task_number(p_task_id);
2591 PA_UTILS.ADD_MESSAGE
2592 (p_app_short_name => 'PA',
2593 p_msg_name => 'PA_EI_INVALID_START_DATE_TSK',
2594 p_token1 => 'TASKNUM',
2595 p_value1 => l_task_num,
2596 p_token2 => 'EISTARTDATE',
2597 p_value2 => x_ei_min_date);
2598 x_msg_data := 'PA_EI_INVALID_START_DATE_TSK';
2599 /*
2600 PA_UTILS.ADD_MESSAGE
2601 (p_app_short_name => 'PA',
2602 p_msg_name => 'PA_EI_INVALID_START_DATE',
2603 p_token1 => 'EISTARTDATE',
2604 p_value1 => x_ei_min_date);
2605 x_msg_data := 'PA_EI_INVALID_START_DATE';
2606 */
2607 -- End Changes for Bug 6633233
2608 RAISE FND_API.G_EXC_ERROR;
2609 ELSE
2610 close cur_start_date;
2611 END IF ;
2612 END IF ;
2613 x_return_status := FND_API.G_RET_STS_SUCCESS;
2614
2615 EXCEPTION
2616 WHEN FND_API.G_EXC_ERROR THEN
2617 x_return_status := FND_API.G_RET_STS_ERROR;
2618 x_msg_count := FND_MSG_PUB.count_msg;
2619 WHEN OTHERS THEN
2620 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2621 -- 4537865 : RESET x_msg_count, x_msg_data also
2622 x_msg_count := 1 ;
2623 x_msg_data := SUBSTRB(SQLERRM,1,240);
2624
2625 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TASKS_MAINT_UTILS',
2626 p_procedure_name => 'Check_Start_Date_EI',
2627 p_error_text => x_msg_data); -- 4537865
2628 END Check_Start_Date_EI;
2629 --Added by rtarway for BUG 4081329
2630
2631 end PA_TASKS_MAINT_UTILS;