1 PACKAGE BODY PA_PROJECT_DATES_UTILS AS
2 /* $Header: PARMPDUB.pls 120.16.12010000.2 2008/10/20 10:32:54 vgovvala ship $ */
3
4
5 -- Global constant
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJECT_DATES_UTILS';
7
8
9 -- API name : Get_Project_Start_Date
10 -- Type : Utility
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- p_project_id IN NUMBER
14
15 FUNCTION GET_PROJECT_START_DATE
16 ( p_project_id IN NUMBER
17 ) RETURN DATE
18 IS
19 l_target_start_date DATE;
20 l_scheduled_start_date DATE;
21 l_actual_start_date DATE;
22 l_start_date DATE; -- Fix for Bug # 4506308.
23
24 CURSOR get_dates_csr
25 IS
26 SELECT target_start_date, scheduled_start_date, actual_start_date, start_date -- Fix for Bug # 4506308.
27 FROM PA_PROJECTS_ALL
28 WHERE project_id = p_project_id;
29
30 BEGIN
31
32 OPEN get_dates_csr;
33 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date, l_start_date;
34 -- Fix for Bug # 4506308.
35 CLOSE get_dates_csr;
36
37 IF l_actual_start_date IS NULL THEN
38 IF l_scheduled_start_date IS NULL THEN
39
40 -- Begin fix for Bug # 4506308.
41
42 -- return l_target_start_date;
43
44 if l_target_start_date is null then
45 return l_start_date;
46 else
47 return l_target_start_date;
48 end if;
49
50 -- End fix for Bug # 4506308.
51
52 ELSE
53 return l_scheduled_start_date;
54 END IF;
55 ELSE
56 return l_actual_start_date;
57 END IF;
58
59 EXCEPTION
60 when OTHERS then
61 return NULL;
62 END GET_PROJECT_START_DATE;
63
64
65
66 -- API name : Get_Project_Finish_Date
67 -- Type : Utility
68 -- Pre-reqs : None.
69 -- Parameters :
70 -- p_project_id IN NUMBER
71
72 FUNCTION GET_PROJECT_FINISH_DATE
73 ( p_project_id IN NUMBER
74 ) RETURN DATE
75 IS
76 l_target_finish_date DATE;
77 l_scheduled_finish_date DATE;
78 l_actual_finish_date DATE;
79 l_completion_date DATE; -- Fix for Bug # 4506308.
80
81 CURSOR get_dates_csr
82 IS
83 SELECT target_finish_date, scheduled_finish_date, actual_finish_date, completion_date
84 -- Fix for Bug # 4506308.
85 FROM PA_PROJECTS_ALL
86 WHERE project_id = p_project_id;
87
88 BEGIN
89
90 OPEN get_dates_csr;
91 FETCH get_dates_csr INTO l_target_finish_date, l_scheduled_finish_date, l_actual_finish_date
92 , l_completion_date; -- Fix for Bug # 4506308.
93 CLOSE get_dates_csr;
94
95 IF l_actual_finish_date IS NULL THEN
96 IF l_scheduled_finish_date IS NULL THEN
97
98 -- Begin fix for Bug # 4506308.
99
100 -- return l_target_finish_date;
101
102 if l_target_finish_date is null then
103 return l_completion_date;
104 else
105 return l_target_finish_date;
106 end if;
107
108 -- End fix for Bug # 4506308.
109
110 ELSE
111 return l_scheduled_finish_date;
112 END IF;
113 ELSE
114 return l_actual_finish_date;
115 END IF;
116
117 EXCEPTION
118 when OTHERS then
119 return NULL;
120 END GET_PROJECT_FINISH_DATE;
121
122
123
124 -- API name : Check_Financial_Task_Exists
125 -- Type : Utility
126 -- Pre-reqs : None.
127 -- Parameters :
128 -- p_proj_element_id IN NUMBER
129
130 FUNCTION CHECK_FINANCIAL_TASK_EXISTS
131 ( p_proj_element_id IN NUMBER
132 ) RETURN VARCHAR2
133 IS
134 l_dummy VARCHAR2(1);
135
136 CURSOR C1
137 IS
138 SELECT 'Y'
139 FROM PA_TASKS
140 WHERE task_id = p_proj_element_id;
141
142 BEGIN
143 OPEN C1;
144 FETCH C1 INTO l_dummy;
145
146 if C1%NOTFOUND then
147 CLOSE C1;
148 return 'N';
149 else
150 CLOSE C1;
151 return 'Y';
152 end if;
153
154 EXCEPTION
155 when OTHERS then
156 return 'N';
157 END CHECK_FINANCIAL_TASK_EXISTS;
158
159
160 -- API name : Get_Task_Start_Date
161 -- Type : Utility
162 -- Pre-reqs : None.
163 -- Parameters :
164 -- p_project_id IN NUMBER
165 -- p_proj_element_id IN NUMBER
166 -- p_parent_structure_version_id IN NUMBER
167 -- x_task_start_date OUT DATE
168 -- x_start_as_of_date OUT DATE
169 PROCEDURE GET_TASK_START_DATE
170 ( p_project_id IN NUMBER
171 ,p_proj_element_id IN NUMBER
172 ,p_parent_structure_version_id IN NUMBER
173 ,x_task_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
174 ,x_start_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
175 )
176 IS
177 CURSOR get_task_sch_dates_csr
178 IS
179 SELECT a.scheduled_start_date, a.last_update_date
180 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
181 PA_PROJ_ELEMENT_VERSIONS b
182 WHERE b.parent_structure_version_id = p_parent_structure_version_id
183 AND b.project_id = p_project_id
184 AND b.proj_element_id = p_proj_element_id
185 AND b.element_version_id = a.element_version_id
186 AND b.project_id = a.project_id;
187
188 CURSOR get_task_act_dates_csr
189 IS
190 SELECT a.actual_start_date, a.last_update_date
191 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
192 PA_PROJ_ELEMENT_VERSIONS b
193 WHERE b.parent_structure_version_id = p_parent_structure_version_id
194 AND b.project_id = p_project_id
195 AND b.proj_element_id = p_proj_element_id
196 AND b.element_version_id = a.element_version_id
197 AND b.project_id = a.project_id;
198
199 l_scheduled_start_date DATE;
200 l_actual_start_date DATE;
201 l_scheduled_update_date DATE;
202 l_actual_update_date DATE;
203 BEGIN
204
205 x_task_start_date := NULL;
206 x_start_as_of_date := NULL;
207
208 OPEN get_task_sch_dates_csr;
209 FETCH get_task_sch_dates_csr INTO l_scheduled_start_date, l_scheduled_update_date;
210 CLOSE get_task_sch_dates_csr;
211
212 OPEN get_task_act_dates_csr;
213 FETCH get_task_act_dates_csr INTO l_actual_start_date, l_actual_update_date;
214 CLOSE get_task_act_dates_csr;
215
216 if l_actual_start_date IS NULL then
217 if l_scheduled_start_date IS NULL then
218 NULL;
219 else
220 x_task_start_date := l_scheduled_start_date;
221 x_start_as_of_date := l_scheduled_update_date;
222 end if;
223 else
224 x_task_start_date := l_actual_start_date;
225 x_start_as_of_date := l_actual_update_date;
226 end if;
227
228 EXCEPTION
229 when OTHERS then
230 x_task_start_date := NULL;
231 x_start_as_of_date := NULL;
232 END GET_TASK_START_DATE;
233
234
235
236 -- API name : Get_Task_Finish_Date
237 -- Type : Utility
238 -- Pre-reqs : None.
239 -- Parameters :
240 -- p_project_id IN NUMBER
241 -- p_proj_element_id IN NUMBER
242 -- p_parent_structure_version_id IN NUMBER
243 -- x_task_finish_date OUT DATE
244 -- x_finish_as_of_date OUT DATE
245 PROCEDURE GET_TASK_FINISH_DATE
246 ( p_project_id IN NUMBER
247 ,p_proj_element_id IN NUMBER
248 ,p_parent_structure_version_id IN NUMBER
249 ,x_task_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
250 ,x_finish_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
251 )
252 IS
253 CURSOR get_task_sch_dates_csr
254 IS
255 SELECT a.scheduled_finish_date, a.last_update_date
256 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
257 PA_PROJ_ELEMENT_VERSIONS b
258 WHERE b.parent_structure_version_id = p_parent_structure_version_id
259 AND b.project_id = p_project_id
260 AND b.proj_element_id = p_proj_element_id
261 AND b.element_version_id = a.element_version_id
262 AND b.project_id = a.project_id;
263
264 CURSOR get_task_act_dates_csr
265 IS
266 SELECT a.actual_finish_date, a.last_update_date
267 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
268 PA_PROJ_ELEMENT_VERSIONS b
269 WHERE b.parent_structure_version_id = p_parent_structure_version_id
270 AND b.project_id = p_project_id
271 AND b.proj_element_id = p_proj_element_id
272 AND b.element_version_id = a.element_version_id
273 AND b.project_id = a.project_id;
274
275 l_scheduled_finish_date DATE;
276 l_actual_finish_date DATE;
277 l_scheduled_update_date DATE;
278 l_actual_update_date DATE;
279 l_finish_update_date DATE;
280 BEGIN
281
282 x_task_finish_date := NULL;
283 x_finish_as_of_date := NULL;
284
285 OPEN get_task_sch_dates_csr;
286 FETCH get_task_sch_dates_csr INTO l_scheduled_finish_date, l_scheduled_update_date;
287 CLOSE get_task_sch_dates_csr;
288
289 OPEN get_task_act_dates_csr;
290 FETCH get_task_act_dates_csr INTO l_actual_finish_date, l_actual_update_date;
291 CLOSE get_task_act_dates_csr;
292
293 if l_actual_finish_date IS NULL then
294 if l_scheduled_finish_date IS NULL then
295 NULL;
296 else
297 x_task_finish_date := l_scheduled_finish_date;
298 x_finish_as_of_date := l_scheduled_update_date;
299 end if;
300 else
301 x_task_finish_date := l_actual_finish_date;
302 x_finish_as_of_date := l_actual_update_date;
303 end if;
304
305 EXCEPTION
306 when OTHERS then
307 x_task_finish_date := NULL;
308 x_finish_as_of_date := NULL;
309 END GET_TASK_FINISH_DATE;
310
311
312 -- API name : Get_Task_Derived_Dates
313 -- Type : Utility
314 -- Pre-reqs : None.
315 -- Parameters :
316 -- p_project_id IN NUMBER
317 -- p_proj_element_id IN NUMBER
318 -- p_parent_structure_version_id IN NUMBER
319 -- x_task_start_date OUT DATE
320 -- x_task_finish_date OUT DATE
321 -- x_task_as_of_date OUT DATE
322 PROCEDURE GET_TASK_DERIVED_DATES
323 ( p_project_id IN NUMBER
324 ,p_proj_element_id IN NUMBER
325 ,p_parent_structure_version_id IN NUMBER
326 ,x_task_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
327 ,x_task_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
328 ,x_task_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
329 )
330 IS
331 l_task_start_date DATE;
332 l_task_finish_date DATE;
333 l_task_as_of_date DATE;
334
335 l_start_as_of_date DATE;
336 l_finish_as_of_date DATE;
337 BEGIN
338 PA_PROJECT_DATES_UTILS.GET_TASK_START_DATE (
339 p_project_id => p_project_id
340 ,p_proj_element_id => p_proj_element_id
341 ,p_parent_structure_version_id => p_parent_structure_version_id
342 ,x_task_start_date => l_task_start_date
343 ,x_start_as_of_date => l_start_as_of_date );
344
345 PA_PROJECT_DATES_UTILS.GET_TASK_FINISH_DATE (
346 p_project_id => p_project_id
347 ,p_proj_element_id => p_proj_element_id
348 ,p_parent_structure_version_id => p_parent_structure_version_id
349 ,x_task_finish_date => l_task_finish_date
350 ,x_finish_as_of_date => l_finish_as_of_date );
351
352 x_task_start_date := l_task_start_date;
353 x_task_finish_date := l_task_finish_date;
354 -- Commented out for bug 2635769
355 --if l_start_as_of_date > l_finish_as_of_date then
356 -- x_task_as_of_date := l_start_as_of_date;
357 --else
358 -- x_task_as_of_date := l_finish_as_of_date;
359 --end if;
360 x_task_as_of_date := NULL;
361
362 EXCEPTION
363 when OTHERS then
364 x_task_start_date := NULL;
365 x_task_finish_date := NULL;
366 x_task_as_of_date := NULL;
367 END GET_TASK_DERIVED_DATES;
368
369
370 -- API name : Get_Task_Copy_Dates
371 -- Type : Utility
372 -- Pre-reqs : None.
373 -- Parameters :
374 -- p_project_id IN NUMBER
375 -- p_proj_element_id IN NUMBER
376 -- p_parent_structure_version_id IN NUMBER
377 -- x_task_start_date OUT DATE
378 -- x_task_finish_date OUT DATE
379 PROCEDURE GET_TASK_COPY_DATES
380 ( p_project_id IN NUMBER
381 ,p_proj_element_id IN NUMBER
382 ,p_parent_structure_version_id IN NUMBER
383 ,x_task_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
384 ,x_task_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
385 ,p_act_fin_date_flag IN VARCHAR2 DEFAULT 'Y' --bug 4229865
386 )
387 IS
388 /* Commented for bug 5258713
389 CURSOR get_task_sch_dates_csr
390 IS
391 SELECT a.scheduled_start_date, a.scheduled_finish_date
392 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
393 PA_PROJ_ELEMENT_VERSIONS b
394 WHERE b.parent_structure_version_id = p_parent_structure_version_id
395 AND b.project_id = p_project_id
396 AND b.proj_element_id = p_proj_element_id
397 AND b.element_version_id = a.element_version_id;
398
399 CURSOR get_task_act_dates_csr
400 IS
401 SELECT a.actual_start_date, a.actual_finish_date
402 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
403 PA_PROJ_ELEMENT_VERSIONS b
404 WHERE b.parent_structure_version_id = p_parent_structure_version_id
405 AND b.project_id = p_project_id
406 AND b.proj_element_id = p_proj_element_id
407 AND b.element_version_id = a.element_version_id;
408 */
409
410 /*Added for bug 5258713 - Merged the two cursors*/
411 CURSOR get_task_dates_csr
412 IS
413 SELECT a.actual_start_date, a.actual_finish_date,a.scheduled_start_date, a.scheduled_finish_date
414 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
415 PA_PROJ_ELEMENT_VERSIONS b
416 WHERE b.parent_structure_version_id = p_parent_structure_version_id
417 AND b.project_id = p_project_id
418 AND b.proj_element_id = p_proj_element_id
419 AND b.element_version_id = a.element_version_id
420 AND A.PROJECT_ID = B.PROJECT_ID; -- Also added a new condition for 5258713
421 /* End Added for bug 5258713 */
422
423 l_scheduled_start_date DATE;
424 l_scheduled_finish_date DATE;
425 l_actual_start_date DATE;
426 l_actual_finish_date DATE;
427 BEGIN
428 /*Commented for bug 5258713
429 OPEN get_task_sch_dates_csr;
430 FETCH get_task_sch_dates_csr INTO l_scheduled_start_date, l_scheduled_finish_date;
431 CLOSE get_task_sch_dates_csr;
432
433 OPEN get_task_act_dates_csr;
434 FETCH get_task_act_dates_csr INTO l_actual_start_date, l_actual_finish_date;
435 CLOSE get_task_act_dates_csr;
436 */
437
438 /*Added for bug 5258713 */
439 OPEN get_task_dates_csr;
440 FETCH get_task_dates_csr INTO l_actual_start_date, l_actual_finish_date, l_scheduled_start_date, l_scheduled_finish_date;
441 CLOSE get_task_dates_csr;
442 /* End Added for bug 5258713 */
443
444 --Added condition OR (l_actual_finish_date IS NULL) for bug 5338208
445 if (l_actual_start_date IS NULL) OR (l_actual_finish_date IS NULL) then
446 x_task_start_date := l_scheduled_start_date;
447 x_task_finish_date := l_scheduled_finish_date;
448 else
449 x_task_start_date := l_actual_start_date;
450 x_task_finish_date := l_actual_finish_date;
451 end if;
452
453 -- bug 4229865
454 /* Commented for bug 5338208
455 IF p_act_fin_date_flag = 'N'
456 THEN
457 x_task_finish_date := NULL;
458 END IF;*/
459 -- end bug 4229865
460
461
462 EXCEPTION
463 when OTHERS then
464 x_task_start_date := NULL;
465 x_task_finish_date := NULL;
466 END GET_TASK_COPY_DATES;
467
468
469 -- API name : Get_Default_Proj_Start_Date
470 -- Type : Utility
471 -- Pre-reqs : None.
472 -- Parameters :
473 -- p_project_id IN NUMBER
474
475 FUNCTION GET_DEFAULT_PROJ_START_DATE
476 ( p_project_id IN NUMBER
477 ) RETURN DATE
478 IS
479 l_target_start_date DATE;
480 l_scheduled_start_date DATE;
481 l_actual_start_date DATE;
482
483 CURSOR get_dates_csr
484 IS
485 SELECT target_start_date, scheduled_start_date, actual_start_date
486 FROM PA_PROJECTS_ALL
487 WHERE project_id = p_project_id;
488
489 BEGIN
490
491 OPEN get_dates_csr;
492 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date;
493 CLOSE get_dates_csr;
494
495 IF l_actual_start_date IS NULL THEN
496 IF l_scheduled_start_date IS NULL THEN
497 IF l_target_start_date IS NULL THEN
498 return SYSDATE;
499 ELSE
500 return l_target_start_date;
501 END IF;
502 ELSE
503 return l_scheduled_start_date;
504 END IF;
505 ELSE
506 return l_actual_start_date;
507 END IF;
508
509 EXCEPTION
510 when OTHERS then
511 return SYSDATE;
512 END GET_DEFAULT_PROJ_START_DATE;
513
514
515
516 -- Bug 6335446: Start
517
518 -- API name : Get_Default_Assign_Start_Date
519 -- Type : Utility
520 -- Pre-reqs : None.
521 -- Parameters :
522 -- p_project_id IN NUMBER
523
524 FUNCTION GET_DEFAULT_ASSIGN_START_DATE
525 ( p_project_id IN NUMBER
526 ) RETURN DATE
527 IS
528 l_target_start_date DATE;
529 l_scheduled_start_date DATE;
530 l_actual_start_date DATE;
531 l_return_date DATE;
532 l_util_start_date DATE;
533 l_transaction_date DATE;
534
535 CURSOR get_dates_csr
536 IS
537 SELECT target_start_date, scheduled_start_date, actual_start_date,start_date
538 FROM PA_PROJECTS_ALL
539 WHERE project_id = p_project_id;
540
541 BEGIN
542
543 OPEN get_dates_csr;
544 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date, l_transaction_date;
545 CLOSE get_dates_csr;
546
547 IF l_transaction_date IS NULL THEN -- 6853114
548 IF l_actual_start_date IS NULL THEN
549 IF l_scheduled_start_date IS NULL THEN
550 IF l_target_start_date IS NULL THEN
551 l_return_date := SYSDATE;
552 ELSE
553 l_return_date := l_target_start_date;
554 END IF;
555 ELSE
556 l_return_date := l_scheduled_start_date;
557 END IF;
558 ELSE
559 l_return_date := l_actual_start_date;
560 END IF;
561 ELSE
562 l_return_date := l_transaction_date;
563 END IF;
564 -- l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY'); /* commenting for For Bug 7304151 */
565 l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151 */
566 IF( l_util_start_date IS NOT NULL ) THEN
567 IF ( l_util_start_date >= l_return_date ) THEN
568 l_return_date := l_util_start_date ;
569 END IF;
570 END IF;
571 return l_return_date ;
572 EXCEPTION
573 when OTHERS then
574 return SYSDATE;
575 END GET_DEFAULT_ASSIGN_START_DATE;
576
577
578 -- API name : Is_Valid_Assign_Start_Date
579 -- Type : Utility
580 -- Pre-reqs : None.
581 -- Parameters :
582 -- p_project_id IN NUMBER
583 -- p_assign_start_date IN DATE
584
585 FUNCTION IS_VALID_ASSIGN_START_DATE
586 ( p_project_id IN NUMBER,
587 p_assign_start_date IN DATE
588 ) RETURN VARCHAR2
589 IS
593 BEGIN
590 l_result_date DATE;
591 l_util_start_date DATE;
592
594
595 -- l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY'); /* commenting for For Bug 7304151 */
596 l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151 */
597 IF( l_util_start_date IS NOT NULL ) THEN
598 l_result_date := l_util_start_date ;
599 END IF;
600 IF ( l_util_start_date IS NOT NULL ) THEN
601 IF ( p_assign_start_date < l_result_date ) THEN
602 RETURN 'N';
603 ELSE
604 RETURN 'Y';
605 END IF;
606 ELSE
607 RETURN 'Y';
608 END IF;
609 EXCEPTION
610 WHEN OTHERS THEN
611 RETURN 'N' ;
612 END IS_VALID_ASSIGN_START_DATE;
613
614 -- Bug 6335446: End
615
616
617
618 -- API name : Get_Struct_Schedule_Dates
619 -- Type : Utility
620 -- Pre-reqs : None.
621 -- Parameters :
622 -- p_structure_version_id IN NUMBER
623 -- x_schedule_start_date OUT DATE
624 -- x_schedule_finish_date OUT DATE
625 -- x_schedule_as_of_date OUT DATE
626 -- x_schedule_duration OUT NUMBER
627 PROCEDURE GET_STRUCT_SCHEDULE_DATES
628 ( p_structure_version_id IN NUMBER
629 ,x_schedule_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
630 ,x_schedule_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
631 ,x_schedule_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
632 ,x_schedule_duration OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
633 )
634 IS
635 CURSOR get_sch_dates_csr
636 IS
637 SELECT scheduled_start_date,
638 scheduled_finish_date,
639 last_update_date,
640 pa_proj_elements_utils.convert_hr_to_days(duration)
641 FROM PA_PROJ_ELEM_VER_SCHEDULE
642 WHERE element_version_id = p_structure_version_id;
643
644 l_scheduled_start_date DATE;
645 l_scheduled_finish_date DATE;
646 l_schedule_as_of_date DATE;
647 l_schedule_duration NUMBER;
648
649 BEGIN
650 OPEN get_sch_dates_csr;
651 FETCH get_sch_dates_csr
652 INTO l_scheduled_start_date,
653 l_scheduled_finish_date,
654 l_schedule_as_of_date,
655 l_schedule_duration;
656 IF get_sch_dates_csr%NOTFOUND then
657 CLOSE get_sch_dates_csr;
658 x_schedule_start_date := NULL;
659 x_schedule_finish_date := NULL;
660 x_schedule_as_of_date := NULL;
661 x_schedule_duration := NULL;
662 return;
663 ELSE
664 CLOSE get_sch_dates_csr;
665 x_schedule_start_date := l_scheduled_start_date;
666 x_schedule_finish_date := l_scheduled_finish_date;
667 x_schedule_as_of_date := l_schedule_as_of_date;
668 x_schedule_duration := l_schedule_duration;
669 END IF;
670
671 EXCEPTION
672 when OTHERS then
673 x_schedule_start_date := NULL;
674 x_schedule_finish_date := NULL;
675 x_schedule_as_of_date := NULL;
676 x_schedule_duration := NULL;
677 END GET_STRUCT_SCHEDULE_DATES;
678
679 -- API name : Get_Project_Start_Date_Src
680 -- Type : Utility
681 -- Pre-reqs : None.
682 -- Parameters :
683 -- p_project_id IN NUMBER
684
685 FUNCTION GET_PROJECT_START_DATE_SRC
686 ( p_project_id IN NUMBER
687 ) RETURN VARCHAR2
688 IS
689 l_target_start_date DATE;
690 l_scheduled_start_date DATE;
691 l_actual_start_date DATE;
692
693 CURSOR get_dates_csr
694 IS
695 SELECT target_start_date, scheduled_start_date, actual_start_date
696 FROM PA_PROJECTS_ALL
697 WHERE project_id = p_project_id;
698
699 BEGIN
700
701 OPEN get_dates_csr;
702 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date;
703 CLOSE get_dates_csr;
704
705 IF l_actual_start_date IS NULL THEN
706 IF l_scheduled_start_date IS NULL THEN
707 return 'T';
708 ELSE
709 return 'S';
710 END IF;
711 ELSE
712 return 'A';
713 END IF;
714
715 EXCEPTION
716 when OTHERS then
717 return NULL;
718 END GET_PROJECT_START_DATE_SRC;
719
720
721
722 -- API name : Get_Project_Finish_Date_Src
723 -- Type : Utility
724 -- Pre-reqs : None.
725 -- Parameters :
726 -- p_project_id IN NUMBER
727
728 FUNCTION GET_PROJECT_FINISH_DATE_SRC
729 ( p_project_id IN NUMBER
730 ) RETURN VARCHAR2
731 IS
732 l_target_finish_date DATE;
733 l_scheduled_finish_date DATE;
734 l_actual_finish_date DATE;
735
736 CURSOR get_dates_csr
737 IS
738 SELECT target_finish_date, scheduled_finish_date, actual_finish_date
739 FROM PA_PROJECTS_ALL
740 WHERE project_id = p_project_id;
741
742 BEGIN
743
744 OPEN get_dates_csr;
745 FETCH get_dates_csr INTO l_target_finish_date, l_scheduled_finish_date, l_actual_finish_date;
746 CLOSE get_dates_csr;
747
751 ELSE
748 IF l_actual_finish_date IS NULL THEN
749 IF l_scheduled_finish_date IS NULL THEN
750 return 'T';
752 return 'S';
753 END IF;
754 ELSE
755 return 'A';
756 END IF;
757
758 EXCEPTION
759 when OTHERS then
760 return NULL;
761 END GET_PROJECT_FINISH_DATE_SRC;
762
763 -- API name : chek_all_tsk_have_act_fin_dt
764 -- Type : Utility
765 -- Pre-reqs : None.
766 -- Parameters :
767 -- p_project_id IN NUMBER
768 -- Description : This API returns 'N' if any of the tasks in the structure version does not have actual finish date.
769
770 FUNCTION chek_all_tsk_have_act_fin_dt
771 ( p_project_id IN NUMBER,
772 p_parent_structure_version_id IN NUMBER
773 ) RETURN VARCHAR2 AS
774
775 CURSOR cur_chk_act_fin_dt
776 IS
777 SELECT 'x'
778 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
779 PA_PROJ_ELEMENT_VERSIONS b
780 WHERE b.parent_structure_version_id = p_parent_structure_version_id
781 AND b.project_id = p_project_id
782 AND b.proj_element_id = a.proj_element_id
783 AND b.element_version_id = a.element_version_id
784 AND a.actual_finish_date IS NULL
785 ;
786 l_dummy VARCHAR2(1);
787 BEGIN
788 OPEN cur_chk_act_fin_dt;
789 FETCH cur_chk_act_fin_dt INTO l_dummy;
790 IF cur_chk_act_fin_dt%FOUND
791 THEN
792 return 'N';
793 ELSE
794 return 'Y';
795 END IF;
796
797 EXCEPTION
798 when OTHERS then
799 return NULL;
800 end chek_all_tsk_have_act_fin_dt;
801
802 -- API name : chek_one_task_has_act_st_date
803 -- Type : Utility
804 -- Pre-reqs : None.
805 -- Parameters :
806 -- p_project_id IN NUMBER
807 -- Description : This API checks and returns 'Y' if any of the task has actual start date.
808
809 FUNCTION chek_one_task_has_act_st_date
810 ( p_project_id IN NUMBER,
811 p_parent_structure_version_id IN NUMBER
812 ) RETURN VARCHAR2 AS
813
814 CURSOR cur_chk_act_fin_dt
815 IS
816 SELECT 'x'
817 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
818 PA_PROJ_ELEMENT_VERSIONS b
819 WHERE b.parent_structure_version_id = p_parent_structure_version_id
820 AND b.project_id = p_project_id
821 AND b.proj_element_id = a.proj_element_id
822 AND b.element_version_id = a.element_version_id
823 AND a.actual_start_date IS NOT NULL
824 ;
825 l_dummy VARCHAR2(1);
826 BEGIN
827 OPEN cur_chk_act_fin_dt;
828 FETCH cur_chk_act_fin_dt INTO l_dummy;
829 IF cur_chk_act_fin_dt%FOUND
830 THEN
831 return 'Y';
832 ELSE
833 return 'N';
834 END IF;
835
836 EXCEPTION
837 when OTHERS then
838 return NULL;
839 end chek_one_task_has_act_st_date;
840
841 /*============Bug 6511907:PJR DATE VALIDATION ENHANCEMENT=======START=======*/
842
843 /* ===============================================
844 Validates the Project Transaction start and end dates against
845 Resource Assignment/Requirement Least and Most dates.
846 x_start_date_status/x_end_date_status: V-->Valid, I-->Invalid.
847 x_validate: W/Y/N --> Warning/Yes/No.
848 ================================================ */
849
850 PROCEDURE Validate_Project_Dates(p_project_id IN NUMBER,
851 p_start_date IN DATE,
852 p_end_date IN DATE,
853 x_validate OUT NOCOPY VARCHAR2,
854 x_start_date_status OUT NOCOPY VARCHAR2,
855 x_end_date_status OUT NOCOPY VARCHAR2)
856
857 IS
858 l_res_min_date DATE;
859 l_res_max_date DATE;
860 l_tsk_min_date DATE; -- Added for bug6866666
861 l_tsk_max_date DATE; -- Added for bug6866666
862 l_validate DATE;
863
864 CURSOR l_res_dt_minmax_csr (l_proj_id NUMBER)
865 IS
866 SELECT min(start_date), max(end_date)
867 FROM pa_project_assignments
868 WHERE project_id = l_proj_id
869 AND assignment_type IN ('OPEN_ASSIGNMENT', 'STAFFED_ASSIGNMENT');
870
871 CURSOR l_tsk_dt_minmax_csr (l_proj_id NUMBER) -- Added for bug6866666
872 IS
873 SELECT min(start_date), max(completion_date)
874 FROM pa_tasks
875 WHERE project_id = l_proj_id;
876
877 BEGIN
878
879 OPEN l_res_dt_minmax_csr(p_project_id);
880 FETCH l_res_dt_minmax_csr INTO l_res_min_date, l_res_max_date;
881 CLOSE l_res_dt_minmax_csr;
882
883 x_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
884 x_start_date_status := 'V';
885 x_end_date_status := 'V';
886
887 IF NVL(x_validate,'N') in ('Y','W') THEN
888 IF p_start_date IS NOT NULL THEN
889 IF p_start_date > l_res_min_date THEN
890 x_start_date_status := 'I';
891 END IF;
892 END IF;
893
894 IF p_end_date IS NOT NULL THEN
895 IF p_end_date < l_res_max_date THEN
896 x_end_date_status := 'I';
897 END IF;
898 END IF;
899 END IF;
900
904 CLOSE l_tsk_dt_minmax_csr;
901 -- Added for bug6866666 Start
902 OPEN l_tsk_dt_minmax_csr(p_project_id);
903 FETCH l_tsk_dt_minmax_csr INTO l_tsk_min_date, l_tsk_max_date;
905
906 IF p_start_date IS NOT NULL THEN
907 IF p_start_date > l_tsk_min_date THEN
908 x_start_date_status := 'J';
909 END IF;
910 END IF;
911
912 IF p_end_date IS NOT NULL THEN
913 IF p_end_date < l_tsk_max_date THEN
914 x_end_date_status := 'J';
915 END IF;
916 END IF;
917 -- Added for bug6866666 End
918
919 EXCEPTION
920
921 when OTHERS then
922 return;
923
924 END Validate_Project_Dates;
925
926 /* ===============================================
927 Bug 6860603: For work plan publish flow:
928 Validates the Project Transaction start and end dates against
929 Resource Assignment/Requirement Least and Most dates.
930 x_start_date_status/x_end_date_status: V-->Valid, I-->Invalid.
931 x_validate: W/Y/N --> Warning/Yes/No.
932 ================================================ */
933
934 PROCEDURE WPP_Validate_Project_Dates
935 (p_project_id IN NUMBER,
936 p_start_date IN DATE,
937 p_end_date IN DATE,
938 p_alwd_start_date OUT NOCOPY DATE,
939 p_alwd_end_date OUT NOCOPY DATE,
940 p_res_min_date OUT NOCOPY DATE,
941 p_res_max_date OUT NOCOPY DATE,
942 x_validate OUT NOCOPY VARCHAR2,
943 x_start_date_status OUT NOCOPY VARCHAR2,
944 x_end_date_status OUT NOCOPY VARCHAR2)
945
946 IS
947 l_res_min_date DATE;
948 l_res_max_date DATE;
949 l_tsk_min_date DATE; -- Added for bug6866666
950 l_tsk_max_date DATE; -- Added for bug6866666
951 l_validate DATE;
952 l_structure_id NUMBER;
953 l_structure_ver_id NUMBER;
954 l_buffer NUMBER;
955
956
957 CURSOR l_res_dt_minmax_csr (l_proj_id NUMBER)
958 IS
959 SELECT min(start_date), max(end_date)
960 FROM pa_project_assignments
961 WHERE project_id = l_proj_id
962 AND assignment_type IN ('OPEN_ASSIGNMENT', 'STAFFED_ASSIGNMENT');
963
964 CURSOR get_structure
965 IS
966 SELECT a.proj_element_id
967 FROM pa_proj_elements a,
968 pa_proj_structure_types b,
969 pa_structure_types c
970 WHERE a.proj_element_id = b.proj_element_id
971 AND a.object_type = 'PA_STRUCTURES'
972 AND a.project_id = p_project_id
973 AND b.structure_type_id = c.structure_type_id
974 AND c.structure_type = 'WORKPLAN';
975
976 CURSOR get_latest_struct_ver(c_structure_id NUMBER)
977 IS
978 select element_version_id
979 from pa_proj_elem_ver_structure
980 where project_id = p_project_id
981 and proj_element_id = c_structure_id
982 and status_code = 'STRUCTURE_PUBLISHED'
983 and latest_eff_published_flag = 'Y';
984
985 CURSOR get_work_struct_ver(c_structure_id NUMBER)
986 IS
987 SELECT element_version_id
988 from pa_proj_elem_ver_structure
989 where project_id = p_project_id
990 and proj_element_id = c_structure_id
991 and status_code <> 'STRUCTURE_PUBLISHED';
992
993 CURSOR get_buffer(c_structure_id NUMBER) IS
994 SELECT NVL(TXN_DATE_SYNC_BUF_DAYS,0)
995 FROM PA_PROJ_WORKPLAN_ATTR
996 WHERE PROJ_ELEMENT_ID = c_structure_id;
997
998 BEGIN
999
1000 /* Getting the Buffer Period*/
1001
1002 OPEN get_structure;
1003 FETCH get_structure into l_structure_id;
1004 CLOSE get_structure;
1005
1006 IF ('Y' = PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(
1007 p_project_id, l_structure_id)) THEN
1008 --Get latest published version id
1009 OPEN get_latest_struct_ver(l_structure_id);
1010 FETCH get_latest_struct_ver into l_structure_ver_id;
1011 CLOSE get_latest_struct_ver;
1012 ELSE
1013 --Get working version id
1014 --this should only return 1 row because this is only called when
1015 -- when structure is of both workplan and financial type
1016 OPEN get_work_struct_ver(l_structure_id);
1017 FETCH get_work_struct_ver into l_structure_ver_id;
1018 CLOSE get_work_struct_ver;
1019 END IF;
1020
1021 --Get buffer from workplan table
1022 OPEN get_buffer(l_structure_id);
1023 FETCH get_buffer into l_buffer;
1024 CLOSE get_buffer;
1025
1026
1027
1028
1029 /* Validation of resource assignment dates against project transaction dates*/
1030
1031 OPEN l_res_dt_minmax_csr(p_project_id);
1032 FETCH l_res_dt_minmax_csr INTO l_res_min_date, l_res_max_date;
1033 CLOSE l_res_dt_minmax_csr;
1034
1035 p_alwd_start_date := l_res_min_date+l_buffer;
1036 p_alwd_end_date := l_res_max_date-l_buffer;
1037 p_res_min_date:=l_res_min_date;
1038 p_res_max_date:=l_res_max_date;
1039
1040
1041
1042 x_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
1043 x_start_date_status := 'V';
1044 x_end_date_status := 'V';
1045
1046 IF NVL(x_validate,'N') in ('Y','W') THEN
1047 IF p_start_date IS NOT NULL THEN
1048 IF p_start_date > l_res_min_date THEN
1049 x_start_date_status := 'I';
1050 END IF;
1051 END IF;
1052
1053 IF p_end_date IS NOT NULL THEN
1054 IF p_end_date < l_res_max_date THEN
1055 x_end_date_status := 'I';
1056 END IF;
1057 END IF;
1058 END IF;
1059
1060
1061
1062 EXCEPTION
1063
1064 when OTHERS then
1065 return;
1066
1067 END WPP_Validate_Project_Dates;
1068
1069
1070 /* =============================================
1071 Validates the Resource Req/Assign start and end dates
1072 against Project Transaction Start and End dates.
1073 x_start_date_status/ x_end_date_status:V-->Valid, I-->Invalid.
1074 x_validate: W/Y/N --> Warning/Yes/No.
1075 ============================================= */
1076
1077 PROCEDURE Validate_Resource_Dates(p_project_id IN NUMBER,
1078 p_start_date IN OUT NOCOPY DATE,
1079 p_end_date IN OUT NOCOPY DATE,
1080 x_validate OUT NOCOPY VARCHAR2,
1081 x_start_date_status OUT NOCOPY VARCHAR2,
1082 x_end_date_status OUT NOCOPY VARCHAR2)
1083
1084 IS
1085 l_prj_min_date DATE;
1086 l_prj_max_date DATE;
1087 l_validate DATE;
1088
1089 CURSOR l_prj_dt_minmax_csr (l_proj_id NUMBER)
1090 IS
1091 SELECT start_date, completion_date
1092 FROM pa_projects_all
1093 WHERE project_id = l_proj_id ;
1094
1095 BEGIN
1096
1097
1098
1099 OPEN l_prj_dt_minmax_csr(p_project_id);
1100 FETCH l_prj_dt_minmax_csr INTO l_prj_min_date, l_prj_max_date;
1101 CLOSE l_prj_dt_minmax_csr;
1102
1103 x_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
1104 x_start_date_status := 'V';
1105 x_end_date_status := 'V';
1106
1107 IF NVL(x_validate,'N') in ('Y','W') THEN
1108
1109 IF p_start_date IS NOT NULL THEN
1110 IF p_start_date < l_prj_min_date THEN
1111 x_start_date_status := 'I';
1112 END IF;
1113 END IF;
1114
1115 IF p_end_date IS NOT NULL THEN
1116 IF p_end_date > l_prj_max_date THEN
1117 x_end_date_status := 'I';
1118 END IF;
1119 END IF;
1120
1121 END IF;
1122
1123 EXCEPTION
1124
1125 when OTHERS then
1126 return;
1127
1128 END;
1129
1130
1131 /* =============================================
1132 Validates the Template Teams start and end dates
1133 against Project Transaction Start and End dates.
1134 x_start_date_status/ x_end_date_status:V-->Valid, I-->Invalid.
1135 x_validate: W/Y/N --> Warning/Yes/No.
1136 ============================================= */
1137
1138
1139 PROCEDURE Validate_Template_Team_Dates
1140 (p_project_id IN NUMBER,
1141 p_template_id IN NUMBER,
1142 x_validate OUT NOCOPY VARCHAR2,
1143 x_start_date_status OUT NOCOPY VARCHAR2,
1144 x_end_date_status OUT NOCOPY VARCHAR2)
1145
1146 IS
1147 l_template_min_date DATE;
1148 l_template_max_date DATE;
1149 l_validate Varchar2(10);
1150
1151 l_prj_min_date DATE;
1152 l_prj_max_date DATE;
1153
1154 /*Fix for Bug 6856082 starts*/
1155 l_team_start_date DATE;
1156 l_number_of_days NUMBER;
1157 /*Fix for Bug 6856082 ends*/
1158
1159 CURSOR l_prj_dt_minmax_csr (l_proj_id NUMBER)
1160 IS
1161 SELECT start_date, completion_date
1162 FROM pa_projects_all
1163 WHERE project_id = l_proj_id ;
1164
1165
1166 CURSOR l_templ_dt_minmax_csr (l_template_id NUMBER)
1167 IS
1168 SELECT min(start_date), max(end_date)
1169 FROM pa_project_assignments
1170 WHERE assignment_template_id = l_template_id ;
1171
1172 /*Fix for Bug 6856082 starts*/
1173 CURSOR get_team_start_date(l_template_id NUMBER) IS
1174 SELECT team_start_date
1175 FROM pa_team_templates
1176 WHERE team_template_id = l_template_id;
1177 /*Fix for Bug 6856082 ends*/
1178 BEGIN
1179
1180 OPEN l_prj_dt_minmax_csr(p_project_id);
1181 FETCH l_prj_dt_minmax_csr INTO l_prj_min_date, l_prj_max_date;
1182 CLOSE l_prj_dt_minmax_csr;
1183
1184 OPEN l_templ_dt_minmax_csr(p_template_id);
1185 FETCH l_templ_dt_minmax_csr INTO l_template_min_date, l_template_max_date;
1186 CLOSE l_templ_dt_minmax_csr;
1187
1188 /*Fix for Bug 6856082 starts*/
1189 OPEN get_team_start_date(p_template_id);
1190 FETCH get_team_start_date INTO l_team_start_date;
1191 CLOSE get_team_start_date ;
1192 /*Fix for Bug 6856082 ends*/
1193
1194 l_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
1195 --l_validate := 'Y';
1196 x_validate := l_validate;
1197 x_start_date_status := 'V';
1198 x_end_date_status := 'V';
1199
1200 /*Fix for Bug 6856082 starts*/
1201 /* Recalc the start and end dates on the template */
1202 l_number_of_days := l_prj_min_date - l_team_start_date;
1203
1204 --determine the requirement's start and end dates by adding l_number_of_days to
1205 --the template requirement's start and end dates.
1206 l_template_min_date := l_template_min_date + l_number_of_days ;
1207 l_template_max_date := l_template_max_date + l_number_of_days ;
1208
1209 /*Fix for Bug 6856082 ends*/
1210
1211 IF NVL(l_validate,'N') in ('Y','W') THEN
1212
1213 IF l_template_min_date IS NOT NULL THEN
1214 IF l_template_min_date < l_prj_min_date THEN
1215 x_start_date_status := 'I';
1216 END IF;
1217 END IF;
1218
1219 IF l_template_max_date IS NOT NULL THEN
1220 IF l_template_max_date > l_prj_max_date THEN
1221 x_end_date_status := 'I';
1222 END IF;
1223 END IF;
1224
1225
1226
1227 END IF;
1228
1229 EXCEPTION
1230
1231 when OTHERS then
1232 return;
1233
1234 END;
1235
1236 /*============Bug 6511907:PJR DATE VALIDATION ENHANCEMENT=======END=======*/
1237 END PA_PROJECT_DATES_UTILS;
[Home] [Help]