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