1 PACKAGE BODY pa_project_dates_utils AS
2 /* $Header: PARMPDUB.pls 120.24 2011/10/28 10:50:39 djambhek 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 -- Begin fix for Bug # 12940745.
25 l_auto_trans_flag varchar2(10);
26 l_enable_ver_flag varchar2(10);
27 l_struct_code varchar2(50);
28 -- Begin fix for Bug # 12940745.
29
30 CURSOR get_dates_csr
31 IS
32 SELECT target_start_date, scheduled_start_date, actual_start_date, start_date -- Fix for Bug # 4506308.
33 FROM PA_PROJECTS_ALL
34 WHERE project_id = p_project_id;
35
36 -- Begin fix for Bug # 12940745.
37 CURSOR c_auto_flag_enabled
38 IS
39 select ppwa.AUTO_SYNC_TXN_DATE_FLAG, ppwa.wp_enable_version_flag
40 from pa_proj_workplan_attr ppwa, pa_proj_elements ppe, pa_proj_structure_types ppst, pa_structure_types pst
41 WHERE ppe.project_id = p_project_id
42 AND ppe.project_id = ppwa.project_id
43 AND ppe.proj_element_id = ppwa.proj_element_id
44 AND ppe.proj_element_id = ppst.proj_element_id
45 AND ppst.structure_type_id = pst.structure_type_id
46 AND pst.structure_type_class_code = 'WORKPLAN';
47
48 CURSOR c_struct_code
49 IS
50 select STRUCTURE_SHARING_CODE
51 from PA_PROJECTS_ALL
52 where project_id = p_project_id;
53 -- End fix for Bug # 12940745.
54 BEGIN
55
56 OPEN get_dates_csr;
57 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date, l_start_date;
58 -- Fix for Bug # 4506308.
59 CLOSE get_dates_csr;
60
61 -- Begin fix for Bug # 12940745.
62 OPEN c_auto_flag_enabled;
63 FETCH c_auto_flag_enabled INTO l_auto_trans_flag, l_enable_ver_flag;
64 CLOSE c_auto_flag_enabled;
65
66 OPEN c_struct_code;
67 FETCH c_struct_code INTO l_struct_code;
68 CLOSE c_struct_code;
69 -- End fix for Bug # 12940745.
70
71
72 IF l_actual_start_date IS NULL THEN
73 -- Begin fix for Bug # 12940745.
74 IF((l_struct_code='SHARE_FULL' OR l_struct_code='SHARE_PARTIAL') AND (l_auto_trans_flag='Y' and l_enable_ver_flag='Y')) THEN
75 return l_start_date;
76 END IF;
77 -- End fix for Bug # 12940745.
78 IF l_scheduled_start_date IS NULL THEN
79
80 -- Begin fix for Bug # 4506308.
81
82 -- return l_target_start_date;
83
84 if l_target_start_date is null then
85 return l_start_date;
86 else
87 return l_target_start_date;
88 end if;
89
90 -- End fix for Bug # 4506308.
91
92 ELSE
93 return l_scheduled_start_date;
94 END IF;
95 ELSE
96 return l_actual_start_date;
97 END IF;
98
99 EXCEPTION
100 when OTHERS then
101 return NULL;
102 END GET_PROJECT_START_DATE;
103
104
105
106 -- API name : Get_Project_Finish_Date
107 -- Type : Utility
108 -- Pre-reqs : None.
109 -- Parameters :
110 -- p_project_id IN NUMBER
111
112 FUNCTION GET_PROJECT_FINISH_DATE
113 ( p_project_id IN NUMBER
114 ) RETURN DATE
115 IS
116 l_target_finish_date DATE;
117 l_scheduled_finish_date DATE;
118 l_actual_finish_date DATE;
119 l_completion_date DATE; -- Fix for Bug # 4506308.
120
121 CURSOR get_dates_csr
122 IS
123 SELECT target_finish_date, scheduled_finish_date, actual_finish_date, completion_date
124 -- Fix for Bug # 4506308.
128 BEGIN
125 FROM PA_PROJECTS_ALL
126 WHERE project_id = p_project_id;
127
129
130 OPEN get_dates_csr;
131 FETCH get_dates_csr INTO l_target_finish_date, l_scheduled_finish_date, l_actual_finish_date
132 , l_completion_date; -- Fix for Bug # 4506308.
133 CLOSE get_dates_csr;
134
135 IF l_actual_finish_date IS NULL THEN
136 IF l_scheduled_finish_date IS NULL THEN
137
138 -- Begin fix for Bug # 4506308.
139
140 -- return l_target_finish_date;
141
142 if l_target_finish_date is null then
143 return l_completion_date;
144 else
145 return l_target_finish_date;
146 end if;
147
148 -- End fix for Bug # 4506308.
149
150 ELSE
151 return l_scheduled_finish_date;
152 END IF;
153 ELSE
154 return l_actual_finish_date;
155 END IF;
156
157 EXCEPTION
158 when OTHERS then
159 return NULL;
160 END GET_PROJECT_FINISH_DATE;
161
162
163
164 -- API name : Check_Financial_Task_Exists
165 -- Type : Utility
166 -- Pre-reqs : None.
167 -- Parameters :
168 -- p_proj_element_id IN NUMBER
169
170 FUNCTION CHECK_FINANCIAL_TASK_EXISTS
171 ( p_proj_element_id IN NUMBER
172 ) RETURN VARCHAR2
173 IS
174 l_dummy VARCHAR2(1);
175
176 CURSOR C1
177 IS
178 SELECT 'Y'
179 FROM PA_TASKS
180 WHERE task_id = p_proj_element_id;
181
182 BEGIN
183 OPEN C1;
184 FETCH C1 INTO l_dummy;
185
186 if C1%NOTFOUND then
187 CLOSE C1;
188 return 'N';
189 else
190 CLOSE C1;
191 return 'Y';
192 end if;
193
194 EXCEPTION
195 when OTHERS then
196 return 'N';
197 END CHECK_FINANCIAL_TASK_EXISTS;
198
199
200 -- API name : Get_Task_Start_Date
201 -- Type : Utility
202 -- Pre-reqs : None.
203 -- Parameters :
204 -- p_project_id IN NUMBER
205 -- p_proj_element_id IN NUMBER
206 -- p_parent_structure_version_id IN NUMBER
207 -- x_task_start_date OUT DATE
208 -- x_start_as_of_date OUT DATE
209 PROCEDURE GET_TASK_START_DATE
210 ( p_project_id IN NUMBER
211 ,p_proj_element_id IN NUMBER
212 ,p_parent_structure_version_id IN NUMBER
213 ,x_task_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
214 ,x_start_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
215 )
216 IS
217 CURSOR get_task_sch_dates_csr
218 IS
219 SELECT a.scheduled_start_date, a.last_update_date
220 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
221 PA_PROJ_ELEMENT_VERSIONS b
222 WHERE b.parent_structure_version_id = p_parent_structure_version_id
223 AND b.project_id = p_project_id
224 AND b.proj_element_id = p_proj_element_id
225 AND b.element_version_id = a.element_version_id
226 AND b.project_id = a.project_id;
227
228 CURSOR get_task_act_dates_csr
229 IS
230 SELECT a.actual_start_date, a.last_update_date
231 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
232 PA_PROJ_ELEMENT_VERSIONS b
233 WHERE b.parent_structure_version_id = p_parent_structure_version_id
234 AND b.project_id = p_project_id
235 AND b.proj_element_id = p_proj_element_id
236 AND b.element_version_id = a.element_version_id
237 AND b.project_id = a.project_id;
238
239 l_scheduled_start_date DATE;
240 l_actual_start_date DATE;
241 l_scheduled_update_date DATE;
242 l_actual_update_date DATE;
243 BEGIN
244
245 x_task_start_date := NULL;
246 x_start_as_of_date := NULL;
247
248 OPEN get_task_sch_dates_csr;
249 FETCH get_task_sch_dates_csr INTO l_scheduled_start_date, l_scheduled_update_date;
250 CLOSE get_task_sch_dates_csr;
251
252 OPEN get_task_act_dates_csr;
253 FETCH get_task_act_dates_csr INTO l_actual_start_date, l_actual_update_date;
254 CLOSE get_task_act_dates_csr;
255
256 if l_actual_start_date IS NULL then
257 if l_scheduled_start_date IS NULL then
258 NULL;
259 else
260 x_task_start_date := l_scheduled_start_date;
261 x_start_as_of_date := l_scheduled_update_date;
262 end if;
263 else
264 x_task_start_date := l_actual_start_date;
265 x_start_as_of_date := l_actual_update_date;
266 end if;
267
268 EXCEPTION
269 when OTHERS then
270 x_task_start_date := NULL;
271 x_start_as_of_date := NULL;
272 END GET_TASK_START_DATE;
273
274
275
276 -- API name : Get_Task_Finish_Date
277 -- Type : Utility
278 -- Pre-reqs : None.
279 -- Parameters :
280 -- p_project_id IN NUMBER
281 -- p_proj_element_id IN NUMBER
282 -- p_parent_structure_version_id IN NUMBER
283 -- x_task_finish_date OUT DATE
284 -- x_finish_as_of_date OUT DATE
285 PROCEDURE GET_TASK_FINISH_DATE
286 ( p_project_id IN NUMBER
287 ,p_proj_element_id IN NUMBER
288 ,p_parent_structure_version_id IN NUMBER
289 ,x_task_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
290 ,x_finish_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
291 )
292 IS
293 CURSOR get_task_sch_dates_csr
294 IS
295 SELECT a.scheduled_finish_date, a.last_update_date
299 AND b.project_id = p_project_id
296 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
297 PA_PROJ_ELEMENT_VERSIONS b
298 WHERE b.parent_structure_version_id = p_parent_structure_version_id
300 AND b.proj_element_id = p_proj_element_id
301 AND b.element_version_id = a.element_version_id
302 AND b.project_id = a.project_id;
303
304 CURSOR get_task_act_dates_csr
305 IS
306 SELECT a.actual_finish_date, a.last_update_date
307 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
308 PA_PROJ_ELEMENT_VERSIONS b
309 WHERE b.parent_structure_version_id = p_parent_structure_version_id
310 AND b.project_id = p_project_id
311 AND b.proj_element_id = p_proj_element_id
312 AND b.element_version_id = a.element_version_id
313 AND b.project_id = a.project_id;
314
315 l_scheduled_finish_date DATE;
316 l_actual_finish_date DATE;
317 l_scheduled_update_date DATE;
318 l_actual_update_date DATE;
319 l_finish_update_date DATE;
320 BEGIN
321
322 x_task_finish_date := NULL;
323 x_finish_as_of_date := NULL;
324
325 OPEN get_task_sch_dates_csr;
326 FETCH get_task_sch_dates_csr INTO l_scheduled_finish_date, l_scheduled_update_date;
327 CLOSE get_task_sch_dates_csr;
328
329 OPEN get_task_act_dates_csr;
330 FETCH get_task_act_dates_csr INTO l_actual_finish_date, l_actual_update_date;
331 CLOSE get_task_act_dates_csr;
332
333 if l_actual_finish_date IS NULL then
334 if l_scheduled_finish_date IS NULL then
335 NULL;
336 else
337 x_task_finish_date := l_scheduled_finish_date;
338 x_finish_as_of_date := l_scheduled_update_date;
339 end if;
340 else
341 x_task_finish_date := l_actual_finish_date;
342 x_finish_as_of_date := l_actual_update_date;
343 end if;
344
345 EXCEPTION
346 when OTHERS then
347 x_task_finish_date := NULL;
348 x_finish_as_of_date := NULL;
349 END GET_TASK_FINISH_DATE;
350
351
352 -- API name : Get_Task_Derived_Dates
353 -- Type : Utility
354 -- Pre-reqs : None.
355 -- Parameters :
356 -- p_project_id IN NUMBER
357 -- p_proj_element_id IN NUMBER
358 -- p_parent_structure_version_id IN NUMBER
359 -- x_task_start_date OUT DATE
360 -- x_task_finish_date OUT DATE
361 -- x_task_as_of_date OUT DATE
362 PROCEDURE GET_TASK_DERIVED_DATES
363 ( p_project_id IN NUMBER
364 ,p_proj_element_id IN NUMBER
365 ,p_parent_structure_version_id IN NUMBER
366 ,x_task_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
367 ,x_task_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
368 ,x_task_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
369 )
370 IS
371 l_task_start_date DATE;
372 l_task_finish_date DATE;
373 l_task_as_of_date DATE;
374
375 l_start_as_of_date DATE;
376 l_finish_as_of_date DATE;
377 BEGIN
378 PA_PROJECT_DATES_UTILS.GET_TASK_START_DATE (
379 p_project_id => p_project_id
380 ,p_proj_element_id => p_proj_element_id
381 ,p_parent_structure_version_id => p_parent_structure_version_id
382 ,x_task_start_date => l_task_start_date
383 ,x_start_as_of_date => l_start_as_of_date );
384
385 PA_PROJECT_DATES_UTILS.GET_TASK_FINISH_DATE (
386 p_project_id => p_project_id
387 ,p_proj_element_id => p_proj_element_id
388 ,p_parent_structure_version_id => p_parent_structure_version_id
389 ,x_task_finish_date => l_task_finish_date
390 ,x_finish_as_of_date => l_finish_as_of_date );
391
392 x_task_start_date := l_task_start_date;
393 x_task_finish_date := l_task_finish_date;
394 -- Commented out for bug 2635769
395 --if l_start_as_of_date > l_finish_as_of_date then
396 -- x_task_as_of_date := l_start_as_of_date;
397 --else
398 -- x_task_as_of_date := l_finish_as_of_date;
399 --end if;
400 x_task_as_of_date := NULL;
401
402 EXCEPTION
403 when OTHERS then
404 x_task_start_date := NULL;
405 x_task_finish_date := NULL;
406 x_task_as_of_date := NULL;
407 END GET_TASK_DERIVED_DATES;
408
409
410 -- API name : Get_Task_Copy_Dates
411 -- Type : Utility
412 -- Pre-reqs : None.
413 -- Parameters :
414 -- p_project_id IN NUMBER
415 -- p_proj_element_id IN NUMBER
416 -- p_parent_structure_version_id IN NUMBER
417 -- x_task_start_date OUT DATE
418 -- x_task_finish_date OUT DATE
419 PROCEDURE GET_TASK_COPY_DATES
420 ( p_project_id IN NUMBER
421 ,p_proj_element_id IN NUMBER
422 ,p_parent_structure_version_id IN NUMBER
423 ,x_task_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
424 ,x_task_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
425 ,p_act_fin_date_flag IN VARCHAR2 DEFAULT 'Y' --bug 4229865
426 )
427 IS
428 /* Commented for bug 5258713
429 CURSOR get_task_sch_dates_csr
430 IS
431 SELECT a.scheduled_start_date, a.scheduled_finish_date
432 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
433 PA_PROJ_ELEMENT_VERSIONS b
434 WHERE b.parent_structure_version_id = p_parent_structure_version_id
435 AND b.project_id = p_project_id
436 AND b.proj_element_id = p_proj_element_id
437 AND b.element_version_id = a.element_version_id;
438
439 CURSOR get_task_act_dates_csr
440 IS
444 WHERE b.parent_structure_version_id = p_parent_structure_version_id
441 SELECT a.actual_start_date, a.actual_finish_date
442 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
443 PA_PROJ_ELEMENT_VERSIONS b
445 AND b.project_id = p_project_id
446 AND b.proj_element_id = p_proj_element_id
447 AND b.element_version_id = a.element_version_id;
448 */
449
450 /*Added for bug 5258713 - Merged the two cursors*/
451 CURSOR get_task_dates_csr
452 IS
453 SELECT a.actual_start_date, a.actual_finish_date,a.scheduled_start_date, a.scheduled_finish_date
454 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
455 PA_PROJ_ELEMENT_VERSIONS b
456 WHERE b.parent_structure_version_id = p_parent_structure_version_id
457 AND b.project_id = p_project_id
458 AND b.proj_element_id = p_proj_element_id
459 AND b.element_version_id = a.element_version_id
460 AND A.PROJECT_ID = B.PROJECT_ID; -- Also added a new condition for 5258713
461 /* End Added for bug 5258713 */
462
463 l_scheduled_start_date DATE;
464 l_scheduled_finish_date DATE;
465 l_actual_start_date DATE;
466 l_actual_finish_date DATE;
467 BEGIN
468 /*Commented for bug 5258713
469 OPEN get_task_sch_dates_csr;
470 FETCH get_task_sch_dates_csr INTO l_scheduled_start_date, l_scheduled_finish_date;
471 CLOSE get_task_sch_dates_csr;
472
473 OPEN get_task_act_dates_csr;
474 FETCH get_task_act_dates_csr INTO l_actual_start_date, l_actual_finish_date;
475 CLOSE get_task_act_dates_csr;
476 */
477
478 /*Added for bug 5258713 */
479 OPEN get_task_dates_csr;
480 FETCH get_task_dates_csr INTO l_actual_start_date, l_actual_finish_date, l_scheduled_start_date, l_scheduled_finish_date;
481 CLOSE get_task_dates_csr;
482 /* End Added for bug 5258713 */
483
484 --Added condition OR (l_actual_finish_date IS NULL) for bug 5338208
485 if (l_actual_start_date IS NULL) OR (l_actual_finish_date IS NULL) then
486 x_task_start_date := l_scheduled_start_date;
487 x_task_finish_date := l_scheduled_finish_date;
488 else
489 x_task_start_date := l_actual_start_date;
490 x_task_finish_date := l_actual_finish_date;
491 end if;
492
493 -- bug 4229865
494 /* Commented for bug 5338208
495 IF p_act_fin_date_flag = 'N'
496 THEN
497 x_task_finish_date := NULL;
498 END IF;*/
499 -- end bug 4229865
500
501
502 EXCEPTION
503 when OTHERS then
504 x_task_start_date := NULL;
505 x_task_finish_date := NULL;
506 END GET_TASK_COPY_DATES;
507
508
509 -- API name : Get_Default_Proj_Start_Date
510 -- Type : Utility
511 -- Pre-reqs : None.
512 -- Parameters :
513 -- p_project_id IN NUMBER
514
515 FUNCTION GET_DEFAULT_PROJ_START_DATE
516 ( p_project_id IN NUMBER
517 ) RETURN DATE
518 IS
519 l_target_start_date DATE;
520 l_scheduled_start_date DATE;
521 l_actual_start_date DATE;
522
523 CURSOR get_dates_csr
524 IS
525 SELECT target_start_date, scheduled_start_date, actual_start_date
526 FROM PA_PROJECTS_ALL
527 WHERE project_id = p_project_id;
528
529 BEGIN
530
531 OPEN get_dates_csr;
532 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date;
533 CLOSE get_dates_csr;
534
535 IF l_actual_start_date IS NULL THEN
536 IF l_scheduled_start_date IS NULL THEN
537 IF l_target_start_date IS NULL THEN
538 return SYSDATE;
539 ELSE
540 return l_target_start_date;
541 END IF;
542 ELSE
543 return l_scheduled_start_date;
544 END IF;
545 ELSE
546 return l_actual_start_date;
547 END IF;
548
549 EXCEPTION
550 when OTHERS then
551 return SYSDATE;
552 END GET_DEFAULT_PROJ_START_DATE;
553
554
555
556 -- Bug 6335446: Start
557
558 -- API name : Get_Default_Assign_Start_Date
559 -- Type : Utility
560 -- Pre-reqs : None.
561 -- Parameters :
562 -- p_project_id IN NUMBER
563
564 FUNCTION GET_DEFAULT_ASSIGN_START_DATE
565 ( p_project_id IN NUMBER
566 ) RETURN DATE
567 IS
568 l_target_start_date DATE;
569 l_scheduled_start_date DATE;
570 l_actual_start_date DATE;
571 l_return_date DATE;
572 l_util_start_date DATE;
573 l_transaction_date DATE;
574
575 CURSOR get_dates_csr
576 IS
577 SELECT target_start_date, scheduled_start_date, actual_start_date,start_date
578 FROM PA_PROJECTS_ALL
579 WHERE project_id = p_project_id;
580
581 BEGIN
582
583 OPEN get_dates_csr;
584 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date, l_transaction_date;
585 CLOSE get_dates_csr;
586
587 IF l_transaction_date IS NULL THEN -- 6853114
588 IF l_actual_start_date IS NULL THEN
589 IF l_scheduled_start_date IS NULL THEN
590 IF l_target_start_date IS NULL THEN
591 l_return_date := SYSDATE;
592 ELSE
593 l_return_date := l_target_start_date;
594 END IF;
595 ELSE
596 l_return_date := l_scheduled_start_date;
597 END IF;
598 ELSE
599 l_return_date := l_actual_start_date;
600 END IF;
601 ELSE
605 l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151 */
602 l_return_date := l_transaction_date;
603 END IF;
604 -- l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY'); /* commenting for For Bug 7304151 */
606 IF( l_util_start_date IS NOT NULL ) THEN
607 IF ( l_util_start_date >= l_return_date ) THEN
608 l_return_date := l_util_start_date ;
609 END IF;
610 END IF;
611 return l_return_date ;
612 EXCEPTION
613 when OTHERS then
614 return SYSDATE;
615 END GET_DEFAULT_ASSIGN_START_DATE;
616
617
618 -- API name : Is_Valid_Assign_Start_Date
619 -- Type : Utility
620 -- Pre-reqs : None.
621 -- Parameters :
622 -- p_project_id IN NUMBER
623 -- p_assign_start_date IN DATE
624
625 FUNCTION IS_VALID_ASSIGN_START_DATE
626 ( p_project_id IN NUMBER,
627 p_assign_start_date IN DATE
628 ) RETURN VARCHAR2
629 IS
630 l_result_date DATE;
631 l_util_start_date DATE;
632
633 BEGIN
634
635 -- l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY'); /* commenting for For Bug 7304151 */
636 l_util_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151 */
637 IF( l_util_start_date IS NOT NULL ) THEN
638 l_result_date := l_util_start_date ;
639 END IF;
640 IF ( l_util_start_date IS NOT NULL ) THEN
641 IF ( p_assign_start_date < l_result_date ) THEN
642 RETURN 'N';
643 ELSE
644 RETURN 'Y';
645 END IF;
646 ELSE
647 RETURN 'Y';
648 END IF;
649 EXCEPTION
650 WHEN OTHERS THEN
651 RETURN 'N' ;
652 END IS_VALID_ASSIGN_START_DATE;
653
654 -- Bug 6335446: End
655
656
657
658 -- API name : Get_Struct_Schedule_Dates
659 -- Type : Utility
660 -- Pre-reqs : None.
661 -- Parameters :
662 -- p_structure_version_id IN NUMBER
663 -- x_schedule_start_date OUT DATE
664 -- x_schedule_finish_date OUT DATE
665 -- x_schedule_as_of_date OUT DATE
666 -- x_schedule_duration OUT NUMBER
667 PROCEDURE GET_STRUCT_SCHEDULE_DATES
668 ( p_structure_version_id IN NUMBER
669 ,x_schedule_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
670 ,x_schedule_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
671 ,x_schedule_as_of_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
672 ,x_schedule_duration OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
673 )
674 IS
675 CURSOR get_sch_dates_csr
676 IS
677 SELECT scheduled_start_date,
678 scheduled_finish_date,
679 last_update_date,
680 pa_proj_elements_utils.convert_hr_to_days(duration)
681 FROM PA_PROJ_ELEM_VER_SCHEDULE
682 WHERE element_version_id = p_structure_version_id;
683
684 l_scheduled_start_date DATE;
685 l_scheduled_finish_date DATE;
686 l_schedule_as_of_date DATE;
687 l_schedule_duration NUMBER;
688
689 BEGIN
690 OPEN get_sch_dates_csr;
691 FETCH get_sch_dates_csr
692 INTO l_scheduled_start_date,
693 l_scheduled_finish_date,
694 l_schedule_as_of_date,
695 l_schedule_duration;
696 IF get_sch_dates_csr%NOTFOUND then
697 CLOSE get_sch_dates_csr;
698 x_schedule_start_date := NULL;
699 x_schedule_finish_date := NULL;
700 x_schedule_as_of_date := NULL;
701 x_schedule_duration := NULL;
702 return;
703 ELSE
704 CLOSE get_sch_dates_csr;
705 x_schedule_start_date := l_scheduled_start_date;
706 x_schedule_finish_date := l_scheduled_finish_date;
707 x_schedule_as_of_date := l_schedule_as_of_date;
708 x_schedule_duration := l_schedule_duration;
709 END IF;
710
711 EXCEPTION
712 when OTHERS then
713 x_schedule_start_date := NULL;
714 x_schedule_finish_date := NULL;
715 x_schedule_as_of_date := NULL;
716 x_schedule_duration := NULL;
717 END GET_STRUCT_SCHEDULE_DATES;
718
719 -- API name : Get_Project_Start_Date_Src
720 -- Type : Utility
721 -- Pre-reqs : None.
722 -- Parameters :
723 -- p_project_id IN NUMBER
724
725 FUNCTION GET_PROJECT_START_DATE_SRC
726 ( p_project_id IN NUMBER
727 ) RETURN VARCHAR2
728 IS
729 l_target_start_date DATE;
730 l_scheduled_start_date DATE;
731 l_actual_start_date DATE;
732
733 CURSOR get_dates_csr
734 IS
735 SELECT target_start_date, scheduled_start_date, actual_start_date
736 FROM PA_PROJECTS_ALL
737 WHERE project_id = p_project_id;
738
739 BEGIN
740
741 OPEN get_dates_csr;
742 FETCH get_dates_csr INTO l_target_start_date, l_scheduled_start_date, l_actual_start_date;
743 CLOSE get_dates_csr;
744
745 IF l_actual_start_date IS NULL THEN
746 IF l_scheduled_start_date IS NULL THEN
747 return 'T';
748 ELSE
749 return 'S';
750 END IF;
751 ELSE
752 return 'A';
753 END IF;
754
755 EXCEPTION
759
756 when OTHERS then
757 return NULL;
758 END GET_PROJECT_START_DATE_SRC;
760
761
762 -- API name : Get_Project_Finish_Date_Src
763 -- Type : Utility
764 -- Pre-reqs : None.
765 -- Parameters :
766 -- p_project_id IN NUMBER
767
768 FUNCTION GET_PROJECT_FINISH_DATE_SRC
769 ( p_project_id IN NUMBER
770 ) RETURN VARCHAR2
771 IS
772 l_target_finish_date DATE;
773 l_scheduled_finish_date DATE;
774 l_actual_finish_date DATE;
775
776 CURSOR get_dates_csr
777 IS
778 SELECT target_finish_date, scheduled_finish_date, actual_finish_date
779 FROM PA_PROJECTS_ALL
780 WHERE project_id = p_project_id;
781
782 BEGIN
783
784 OPEN get_dates_csr;
785 FETCH get_dates_csr INTO l_target_finish_date, l_scheduled_finish_date, l_actual_finish_date;
786 CLOSE get_dates_csr;
787
788 IF l_actual_finish_date IS NULL THEN
789 IF l_scheduled_finish_date IS NULL THEN
790 return 'T';
791 ELSE
792 return 'S';
793 END IF;
794 ELSE
795 return 'A';
796 END IF;
797
798 EXCEPTION
799 when OTHERS then
800 return NULL;
801 END GET_PROJECT_FINISH_DATE_SRC;
802
803 -- API name : chek_all_tsk_have_act_fin_dt
804 -- Type : Utility
805 -- Pre-reqs : None.
806 -- Parameters :
807 -- p_project_id IN NUMBER
808 -- Description : This API returns 'N' if any of the tasks in the structure version does not have actual finish date.
809
810 FUNCTION chek_all_tsk_have_act_fin_dt
811 ( p_project_id IN NUMBER,
812 p_parent_structure_version_id IN NUMBER
813 ) RETURN VARCHAR2 AS
814
815 CURSOR cur_chk_act_fin_dt
816 IS
817 SELECT 'x'
818 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
819 PA_PROJ_ELEMENT_VERSIONS b
820 WHERE b.parent_structure_version_id = p_parent_structure_version_id
821 AND b.project_id = p_project_id
822 AND b.proj_element_id = a.proj_element_id
823 AND b.element_version_id = a.element_version_id
824 AND a.actual_finish_date IS NULL
825 ;
826 l_dummy VARCHAR2(1);
827 BEGIN
828 OPEN cur_chk_act_fin_dt;
829 FETCH cur_chk_act_fin_dt INTO l_dummy;
830 IF cur_chk_act_fin_dt%FOUND
831 THEN
832 return 'N';
833 ELSE
834 return 'Y';
835 END IF;
836
837 EXCEPTION
838 when OTHERS then
839 return NULL;
840 end chek_all_tsk_have_act_fin_dt;
841
842 -- API name : chek_one_task_has_act_st_date
843 -- Type : Utility
844 -- Pre-reqs : None.
845 -- Parameters :
846 -- p_project_id IN NUMBER
847 -- Description : This API checks and returns 'Y' if any of the task has actual start date.
848
849 FUNCTION chek_one_task_has_act_st_date
850 ( p_project_id IN NUMBER,
851 p_parent_structure_version_id IN NUMBER
852 ) RETURN VARCHAR2 AS
853
854 CURSOR cur_chk_act_fin_dt
855 IS
856 SELECT 'x'
857 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
858 PA_PROJ_ELEMENT_VERSIONS b
859 WHERE b.parent_structure_version_id = p_parent_structure_version_id
860 AND b.project_id = p_project_id
861 AND b.proj_element_id = a.proj_element_id
862 AND b.element_version_id = a.element_version_id
863 AND a.actual_start_date IS NOT NULL
864 ;
865 l_dummy VARCHAR2(1);
866 BEGIN
867 OPEN cur_chk_act_fin_dt;
868 FETCH cur_chk_act_fin_dt INTO l_dummy;
869 IF cur_chk_act_fin_dt%FOUND
870 THEN
871 return 'Y';
872 ELSE
873 return 'N';
874 END IF;
875
876 EXCEPTION
877 when OTHERS then
878 return NULL;
879 end chek_one_task_has_act_st_date;
880
881 /*============Bug 6511907:PJR DATE VALIDATION ENHANCEMENT=======START=======*/
882
883 /* ===============================================
884 Validates the Project Transaction start and end dates against
885 Resource Assignment/Requirement Least and Most dates.
886 x_start_date_status/x_end_date_status: V-->Valid, I-->Invalid.
887 x_validate: W/Y/N --> Warning/Yes/No.
888 ================================================ */
889
890 PROCEDURE Validate_Project_Dates(p_project_id IN NUMBER,
891 p_start_date IN DATE,
892 p_end_date IN DATE,
893 x_validate OUT NOCOPY VARCHAR2,
894 x_start_date_status OUT NOCOPY VARCHAR2,
895 x_end_date_status OUT NOCOPY VARCHAR2)
896
897 IS
898 l_res_min_date DATE;
899 l_res_max_date DATE;
900 l_tsk_min_date DATE; -- Added for bug6866666
901 l_tsk_max_date DATE; -- Added for bug6866666
902 l_validate DATE;
903
904 CURSOR l_res_dt_minmax_csr (l_proj_id NUMBER)
905 IS
906 SELECT min(start_date), max(end_date)
907 FROM pa_project_assignments
908 WHERE project_id = l_proj_id
909 AND assignment_type IN ('OPEN_ASSIGNMENT', 'STAFFED_ASSIGNMENT');
910
911 CURSOR l_tsk_dt_minmax_csr (l_proj_id NUMBER) -- Added for bug6866666
912 IS
913 SELECT min(start_date), max(completion_date)
914 FROM pa_tasks
918
915 WHERE project_id = l_proj_id;
916
917 BEGIN
919 OPEN l_res_dt_minmax_csr(p_project_id);
920 FETCH l_res_dt_minmax_csr INTO l_res_min_date, l_res_max_date;
921 CLOSE l_res_dt_minmax_csr;
922
923 x_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
924 x_start_date_status := 'V';
925 x_end_date_status := 'V';
926
927 IF NVL(x_validate,'N') in ('Y','W') THEN
928 IF p_start_date IS NOT NULL THEN
929 IF p_start_date > l_res_min_date THEN
930 x_start_date_status := 'I';
931 END IF;
932 END IF;
933
934 IF p_end_date IS NOT NULL THEN
935 IF p_end_date < l_res_max_date THEN
936 x_end_date_status := 'I';
937 END IF;
938 END IF;
939 END IF;
940
941 -- Added for bug6866666 Start
942 OPEN l_tsk_dt_minmax_csr(p_project_id);
943 FETCH l_tsk_dt_minmax_csr INTO l_tsk_min_date, l_tsk_max_date;
944 CLOSE l_tsk_dt_minmax_csr;
945
946 IF p_start_date IS NOT NULL THEN
947 IF p_start_date > l_tsk_min_date THEN
948 x_start_date_status := 'J';
949 END IF;
950 END IF;
951
952 IF p_end_date IS NOT NULL THEN
953 IF p_end_date < l_tsk_max_date THEN
954 x_end_date_status := 'J';
955 END IF;
956 END IF;
957 -- Added for bug6866666 End
958
959 EXCEPTION
960
961 when OTHERS then
962 return;
963
964 END Validate_Project_Dates;
965
966 /* ===============================================
967 Bug 6860603: For work plan publish flow:
968 Validates the Project Transaction start and end dates against
969 Resource Assignment/Requirement Least and Most dates.
970 x_start_date_status/x_end_date_status: V-->Valid, I-->Invalid.
971 x_validate: W/Y/N --> Warning/Yes/No.
972 ================================================ */
973
974 PROCEDURE WPP_Validate_Project_Dates
975 (p_project_id IN NUMBER,
976 p_start_date IN DATE,
977 p_end_date IN DATE,
978 p_alwd_start_date OUT NOCOPY DATE,
979 p_alwd_end_date OUT NOCOPY DATE,
980 p_res_min_date OUT NOCOPY DATE,
981 p_res_max_date OUT NOCOPY DATE,
982 x_validate OUT NOCOPY VARCHAR2,
983 x_start_date_status OUT NOCOPY VARCHAR2,
984 x_end_date_status OUT NOCOPY VARCHAR2)
985
986 IS
987 l_res_min_date DATE;
988 l_res_max_date DATE;
989 l_tsk_min_date DATE; -- Added for bug6866666
990 l_tsk_max_date DATE; -- Added for bug6866666
991 l_validate DATE;
992 l_structure_id NUMBER;
993 l_structure_ver_id NUMBER;
994 l_buffer NUMBER;
995
996
997 CURSOR l_res_dt_minmax_csr (l_proj_id NUMBER)
998 IS
999 SELECT min(start_date), max(end_date)
1000 FROM pa_project_assignments
1001 WHERE project_id = l_proj_id
1002 AND assignment_type IN ('OPEN_ASSIGNMENT', 'STAFFED_ASSIGNMENT');
1003
1004 CURSOR get_structure
1005 IS
1006 SELECT a.proj_element_id
1007 FROM pa_proj_elements a,
1008 pa_proj_structure_types b,
1009 pa_structure_types c
1010 WHERE a.proj_element_id = b.proj_element_id
1011 AND a.object_type = 'PA_STRUCTURES'
1012 AND a.project_id = p_project_id
1013 AND b.structure_type_id = c.structure_type_id
1014 AND c.structure_type = 'WORKPLAN';
1015
1016 CURSOR get_latest_struct_ver(c_structure_id NUMBER)
1017 IS
1018 select element_version_id
1019 from pa_proj_elem_ver_structure
1020 where project_id = p_project_id
1021 and proj_element_id = c_structure_id
1022 and status_code = 'STRUCTURE_PUBLISHED'
1023 and latest_eff_published_flag = 'Y';
1024
1025 CURSOR get_work_struct_ver(c_structure_id NUMBER)
1026 IS
1027 SELECT element_version_id
1028 from pa_proj_elem_ver_structure
1029 where project_id = p_project_id
1030 and proj_element_id = c_structure_id
1031 and status_code <> 'STRUCTURE_PUBLISHED';
1032
1033 CURSOR get_buffer(c_structure_id NUMBER) IS
1034 SELECT NVL(TXN_DATE_SYNC_BUF_DAYS,0)
1035 FROM PA_PROJ_WORKPLAN_ATTR
1036 WHERE PROJ_ELEMENT_ID = c_structure_id;
1037
1038 BEGIN
1039
1040 /* Getting the Buffer Period*/
1041
1042 OPEN get_structure;
1043 FETCH get_structure into l_structure_id;
1044 CLOSE get_structure;
1045
1046 IF ('Y' = PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(
1047 p_project_id, l_structure_id)) THEN
1048 --Get latest published version id
1049 OPEN get_latest_struct_ver(l_structure_id);
1050 FETCH get_latest_struct_ver into l_structure_ver_id;
1051 CLOSE get_latest_struct_ver;
1052 ELSE
1053 --Get working version id
1054 --this should only return 1 row because this is only called when
1055 -- when structure is of both workplan and financial type
1056 OPEN get_work_struct_ver(l_structure_id);
1057 FETCH get_work_struct_ver into l_structure_ver_id;
1058 CLOSE get_work_struct_ver;
1059 END IF;
1060
1061 --Get buffer from workplan table
1062 OPEN get_buffer(l_structure_id);
1063 FETCH get_buffer into l_buffer;
1064 CLOSE get_buffer;
1065
1066
1067
1068
1069 /* Validation of resource assignment dates against project transaction dates*/
1070
1071 OPEN l_res_dt_minmax_csr(p_project_id);
1072 FETCH l_res_dt_minmax_csr INTO l_res_min_date, l_res_max_date;
1073 CLOSE l_res_dt_minmax_csr;
1074
1078 p_res_max_date:=l_res_max_date;
1075 p_alwd_start_date := l_res_min_date+l_buffer;
1076 p_alwd_end_date := l_res_max_date-l_buffer;
1077 p_res_min_date:=l_res_min_date;
1079
1080
1081
1082 x_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
1083 x_start_date_status := 'V';
1084 x_end_date_status := 'V';
1085
1086 IF NVL(x_validate,'N') in ('Y','W') THEN
1087 IF p_start_date IS NOT NULL THEN
1088 IF p_start_date > l_res_min_date THEN
1089 x_start_date_status := 'I';
1090 END IF;
1091 END IF;
1092
1093 IF p_end_date IS NOT NULL THEN
1094 IF p_end_date < l_res_max_date THEN
1095 x_end_date_status := 'I';
1096 END IF;
1097 END IF;
1098 END IF;
1099
1100
1101
1102 EXCEPTION
1103
1104 when OTHERS then
1105 return;
1106
1107 END WPP_Validate_Project_Dates;
1108
1109
1110 /* =============================================
1111 Validates the Resource Req/Assign start and end dates
1112 against Project Transaction Start and End dates.
1113 x_start_date_status/ x_end_date_status:V-->Valid, I-->Invalid.
1114 x_validate: W/Y/N --> Warning/Yes/No.
1115 ============================================= */
1116
1117 PROCEDURE Validate_Resource_Dates(p_project_id IN NUMBER,
1118 p_start_date IN OUT NOCOPY DATE,
1119 p_end_date IN OUT NOCOPY DATE,
1120 x_validate OUT NOCOPY VARCHAR2,
1121 x_start_date_status OUT NOCOPY VARCHAR2,
1122 x_end_date_status OUT NOCOPY VARCHAR2)
1123
1124 IS
1125 l_prj_min_date DATE;
1126 l_prj_max_date DATE;
1127 l_validate DATE;
1128
1129 CURSOR l_prj_dt_minmax_csr (l_proj_id NUMBER)
1130 IS
1131 SELECT start_date, completion_date
1132 FROM pa_projects_all
1133 WHERE project_id = l_proj_id ;
1134
1135 BEGIN
1136
1137
1138
1139 OPEN l_prj_dt_minmax_csr(p_project_id);
1140 FETCH l_prj_dt_minmax_csr INTO l_prj_min_date, l_prj_max_date;
1141 CLOSE l_prj_dt_minmax_csr;
1142
1143 x_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
1144 x_start_date_status := 'V';
1145 x_end_date_status := 'V';
1146
1147 IF NVL(x_validate,'N') in ('Y','W') THEN
1148
1149 IF p_start_date IS NOT NULL THEN
1150 IF p_start_date < l_prj_min_date THEN
1151 x_start_date_status := 'I';
1152 END IF;
1153 END IF;
1154
1155 IF p_end_date IS NOT NULL THEN
1156 IF p_end_date > l_prj_max_date THEN
1157 x_end_date_status := 'I';
1158 END IF;
1159 END IF;
1160
1161 END IF;
1162
1163 EXCEPTION
1164
1165 when OTHERS then
1166 return;
1167
1168 END;
1169
1170
1171 /**
1172 @author NISINHA
1173 Procedure VALIDATE_ASSIGNMENT_DATES_BULK
1174 Description : This procedure is used to validate the dates of Assignments when updated from the Updatable Scheduled People Page
1175 */
1176
1177 PROCEDURE VALIDATE_ASSIGNMENT_DATES_BULK
1178 (p_project_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1179 p_start_date_tbl IN OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE,
1180 p_end_date_tbl IN OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE,
1181 x_validate_tbl IN OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE,
1182 x_start_date_status_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE,
1183 x_end_date_status_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE,
1184 x_msg_data_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE)
1185
1186 IS
1187 l_prj_min_date DATE;
1188 l_prj_max_date DATE;
1189 l_start_date Date;
1190 l_completion_date Date;
1191
1192 CURSOR l_prj_dt_minmax_csr (l_proj_id NUMBER)
1193 IS
1194 SELECT start_date, completion_date
1195 FROM pa_projects_all
1196 WHERE project_id = l_proj_id ;
1197
1198 l_validate VARCHAR2 (2000);
1199 l_validate_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1200 l_start_date_status_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1201 l_end_date_status_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1202 l_msg_data_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1203
1204 l_valid_assign_start_flag VARCHAR2(1) := 'Y';
1205
1206 BEGIN
1207
1208
1209 fnd_msg_pub.initialize;
1210 l_validate := FND_PROFILE.Value('PA_VALIDATE_ASSIGN_DATES'); -- This profile option is for only validating the assignment dates against project dates
1211
1212 FOR i IN p_project_id_tbl.first .. p_project_id_tbl.last LOOP
1213
1214 l_validate_tbl(i) := NULL ;
1215 l_start_date_status_tbl(i) := NULL ;
1216 l_end_date_status_tbl(i) := NULL ;
1217 l_msg_data_tbl(i) := NULL ;
1218
1219
1220
1221 IF p_project_id_tbl(i) IS NOT NULL THEN
1222
1223 OPEN l_prj_dt_minmax_csr(p_project_id_tbl(i));
1224 FETCH l_prj_dt_minmax_csr INTO l_prj_min_date, l_prj_max_date;
1225 CLOSE l_prj_dt_minmax_csr;
1226
1227
1228 l_validate_tbl(i) := l_validate;
1232 -- This new condition sets the status as G indicating that Start date is greater than End date.
1229 l_start_date_status_tbl(i) := 'V';
1230 l_end_date_status_tbl(i) := 'V';
1231
1233 IF p_start_date_tbl(i) IS NOT NULL AND p_end_date_tbl(i) IS NOT NULL THEN
1234 IF p_start_date_tbl(i) > p_end_date_tbl(i) THEN
1235
1236 l_start_date_status_tbl(i) := 'G';
1237 l_msg_data_tbl(i) := 'PA_INVALID_START_DATE';
1238 PA_UTILS.Add_Message( p_app_short_name => 'PA',
1239 p_msg_name => 'PA_INVALID_START_DATE');
1240
1241
1242 END IF;
1243 END IF;
1244
1245 IF NVL(l_validate_tbl(i),'N') in ('Y','W') THEN
1246 IF p_start_date_tbl(i) IS NOT NULL THEN
1247 IF p_start_date_tbl(i) < l_prj_min_date THEN
1248 l_start_date_status_tbl(i) := 'I';
1249
1250
1251 END IF;
1252 END IF;
1253
1254 IF p_end_date_tbl(i) IS NOT NULL THEN
1255 IF p_end_date_tbl(i) > l_prj_max_date THEN
1256 l_end_date_status_tbl(i) := 'I';
1257
1258
1259 END IF;
1260 END IF;
1261
1262
1263
1264 l_valid_assign_start_flag := PA_PROJECT_DATES_UTILS.IS_VALID_ASSIGN_START_DATE
1265 ( p_project_id => p_project_id_tbl(i),
1266 p_assign_start_date => p_start_date_tbl(i) ) ;
1267 IF ( l_valid_assign_start_flag <> 'Y' ) THEN
1268 l_start_date_status_tbl(i) := 'I';
1269 END IF;
1270
1271 END IF;
1272
1273 IF l_validate_tbl(i) = 'Y' THEN
1274 SELECT start_date, completion_date
1275 INTO l_start_date, l_completion_date
1276 FROM pa_projects_prm_v proj
1277 WHERE project_id = p_project_id_tbl(i);
1278
1279 IF (l_start_date_status_tbl(i) = 'I' AND l_start_date IS NOT NULL ) THEN
1280 l_msg_data_tbl(i) := 'PA_PJR_DATE_START_ERROR';
1281 PA_UTILS.Add_Message( p_app_short_name => 'PA',
1282 p_msg_name => 'PA_PJR_DATE_START_ERROR',
1283 p_token1 => 'PROJ_TXN_START_DATE',
1284 p_value1 => ' ');
1285
1286 END IF ;
1287
1288 IF (l_end_date_status_tbl(i) = 'I' AND l_completion_date IS NOT NULL ) THEN
1289 l_msg_data_tbl(i) := 'PA_PJR_DATE_FINISH_ERROR';
1290 PA_UTILS.Add_Message( p_app_short_name => 'PA',
1291 p_msg_name => 'PA_PJR_DATE_FINISH_ERROR',
1292 p_token1 => 'PROJ_TXN_END_DATE',
1293 p_value1 => ' ');
1294
1295 END IF ;
1296
1297
1298 ELSIF l_validate_tbl(i) = 'W' THEN
1299 IF (l_start_date_status_tbl(i) = 'I') THEN
1300 l_msg_data_tbl(i) := 'StartDateWarning';
1301 END IF ;
1302
1303 IF (l_end_date_status_tbl(i) = 'I') THEN
1304 l_msg_data_tbl(i) := 'EndDateWarning';
1305 END IF ;
1306 END IF ;
1307
1308 END IF ; -- p_project_id_tbl(i) is not null
1309
1310 END LOOP ;
1311
1312 x_validate_tbl := l_validate_tbl;
1313 x_start_date_status_tbl := l_start_date_status_tbl;
1314 x_end_date_status_tbl := l_end_date_status_tbl;
1315 x_msg_data_tbl := l_msg_data_tbl;
1316
1317
1318 EXCEPTION
1319
1320 WHEN OTHERS THEN
1321
1322 RAISE ;
1323
1324 END VALIDATE_ASSIGNMENT_DATES_BULK;
1325
1326
1327 /* END OF PROCEDURE VALIDATE_ASSIGNMENT_DATES_BULK */
1328
1329 /* Bug 7693634 start */
1330
1331 PROCEDURE Validate_Resource_Dates_Bulk
1332 (p_project_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1333 p_start_date_tbl IN OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE,
1334 p_end_date_tbl IN OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE,
1335 x_validate_tbl IN OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE,
1336 x_start_date_status_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE,
1337 x_end_date_status_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE,
1338 x_msg_data_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE)
1339
1340 IS
1341 l_prj_min_date DATE;
1342 l_prj_max_date DATE;
1343 l_start_date Date;
1344 l_completion_date Date;
1345
1346 CURSOR l_prj_dt_minmax_csr (l_proj_id NUMBER)
1347 IS
1348 SELECT start_date, completion_date
1349 FROM pa_projects_all
1350 WHERE project_id = l_proj_id ;
1351
1352 l_validate varchar2(2000);
1353 l_validate_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1354 l_start_date_status_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1355 l_end_date_status_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1356 l_msg_data_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := x_validate_tbl;
1357
1361
1358 l_valid_assign_start_flag VARCHAR2(1) := 'Y';
1359
1360 BEGIN
1362
1363 fnd_msg_pub.initialize; -- bug 8233045
1364 l_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
1365
1366 for i in p_project_id_tbl.first .. p_project_id_tbl.last loop
1367 l_validate_tbl(i) := null;
1368 l_start_date_status_tbl(i) := null;
1369 l_end_date_status_tbl(i) := null;
1370 l_msg_data_tbl(i) := null;
1371
1372
1373
1374 if p_project_id_tbl(i) is not null THEN
1375
1376 OPEN l_prj_dt_minmax_csr(p_project_id_tbl(i));
1377 FETCH l_prj_dt_minmax_csr INTO l_prj_min_date, l_prj_max_date;
1378 CLOSE l_prj_dt_minmax_csr;
1379
1380
1381 l_validate_tbl(i) := l_validate;
1382 l_start_date_status_tbl(i) := 'V';
1383 l_end_date_status_tbl(i) := 'V';
1384
1385 IF NVL(l_validate_tbl(i),'N') in ('Y','W') THEN
1386
1387 IF p_start_date_tbl(i) IS NOT NULL THEN
1388 IF p_start_date_tbl(i) < l_prj_min_date THEN
1389 l_start_date_status_tbl(i) := 'I';
1390 END IF;
1391 END IF;
1392
1393 IF p_end_date_tbl(i) IS NOT NULL THEN
1394 IF p_end_date_tbl(i) > l_prj_max_date THEN
1395 l_end_date_status_tbl(i) := 'I';
1396 END IF;
1397 END IF;
1398
1399 l_valid_assign_start_flag := PA_PROJECT_DATES_UTILS.IS_VALID_ASSIGN_START_DATE
1400 ( p_project_id => p_project_id_tbl(i),
1401 p_assign_start_date => p_start_date_tbl(i) ) ;
1402
1403 IF ( l_valid_assign_start_flag <> 'Y' ) THEN
1404 l_start_date_status_tbl(i) := 'I';
1405 END IF;
1406
1407 END IF;
1408
1409 if l_validate_tbl(i) = 'Y' then
1410
1411
1412 select start_date, completion_date
1413 into l_start_date, l_completion_date
1414 from pa_projects_prm_v proj
1415 where project_id = p_project_id_tbl(i);
1416
1417 if (l_start_date_status_tbl(i) = 'I' and l_start_date is not null) then
1418 l_msg_data_tbl(i) := 'PA_PJR_DATE_START_ERROR';
1419
1420 PA_UTILS.Add_Message( p_app_short_name => 'PA',
1421 p_msg_name => 'PA_PJR_DATE_START_ERROR',
1422 p_token1 => 'PROJ_TXN_START_DATE',
1423 p_value1 => ' ');
1424 end if;
1425
1426 if (l_end_date_status_tbl(i) = 'I' and l_completion_date is not null) then
1427 l_msg_data_tbl(i) := 'PA_PJR_DATE_FINISH_ERROR';
1428
1429 PA_UTILS.Add_Message( p_app_short_name => 'PA',
1430 p_msg_name => 'PA_PJR_DATE_FINISH_ERROR',
1431 p_token1 => 'PROJ_TXN_END_DATE',
1432 p_value1 => ' ');
1433 end if;
1434
1435 elsif l_validate_tbl(i) = 'W' then
1436
1437
1438 if (l_start_date_status_tbl(i) = 'I') then
1439 l_msg_data_tbl(i) := 'StartDateWarning';
1440
1441
1442 end if;
1443
1444 if (l_end_date_status_tbl(i) = 'I') then
1445 l_msg_data_tbl(i) := 'EndDateWarning';
1446
1447 end if;
1448
1449
1450 end if;
1451
1452 end if; -- p_project_id_tbl(i) is not null
1453
1454 end loop;
1455
1456 x_validate_tbl := l_validate_tbl;
1457 x_start_date_status_tbl := l_start_date_status_tbl;
1458 x_end_date_status_tbl := l_end_date_status_tbl;
1459 x_msg_data_tbl := l_msg_data_tbl;
1460
1461
1462 EXCEPTION
1463
1464 when OTHERS THEN
1465
1466 raise;
1467
1468 END;
1469
1470 /* Bug 7693634 end */
1471
1472
1473 /* =============================================
1474 Validates the Template Teams start and end dates
1475 against Project Transaction Start and End dates.
1476 x_start_date_status/ x_end_date_status:V-->Valid, I-->Invalid.
1477 x_validate: W/Y/N --> Warning/Yes/No.
1478 ============================================= */
1479
1480
1481 PROCEDURE Validate_Template_Team_Dates
1482 (p_project_id IN NUMBER,
1483 p_template_id IN NUMBER,
1484 x_validate OUT NOCOPY VARCHAR2,
1485 x_start_date_status OUT NOCOPY VARCHAR2,
1486 x_end_date_status OUT NOCOPY VARCHAR2)
1487
1488 IS
1489 l_template_min_date DATE;
1490 l_template_max_date DATE;
1491 l_validate Varchar2(10);
1492
1493 l_prj_min_date DATE;
1494 l_prj_max_date DATE;
1495
1496 /*Fix for Bug 6856082 starts*/
1497 l_team_start_date DATE;
1498 l_number_of_days NUMBER;
1499 /*Fix for Bug 6856082 ends*/
1500
1501 CURSOR l_prj_dt_minmax_csr (l_proj_id NUMBER)
1502 IS
1503 SELECT start_date, completion_date
1504 FROM pa_projects_all
1505 WHERE project_id = l_proj_id ;
1506
1507
1508 CURSOR l_templ_dt_minmax_csr (l_template_id NUMBER)
1509 IS
1510 SELECT min(start_date), max(end_date)
1511 FROM pa_project_assignments
1512 WHERE assignment_template_id = l_template_id
1513 AND project_id IS NULL ; -- added this for bug 7638712
1514
1515 /*Fix for Bug 6856082 starts*/
1516 CURSOR get_team_start_date(l_template_id NUMBER) IS
1517 SELECT team_start_date
1518 FROM pa_team_templates
1519 WHERE team_template_id = l_template_id;
1520 /*Fix for Bug 6856082 ends*/
1521 BEGIN
1522
1523 OPEN l_prj_dt_minmax_csr(p_project_id);
1524 FETCH l_prj_dt_minmax_csr INTO l_prj_min_date, l_prj_max_date;
1525 CLOSE l_prj_dt_minmax_csr;
1529 CLOSE l_templ_dt_minmax_csr;
1526
1527 OPEN l_templ_dt_minmax_csr(p_template_id);
1528 FETCH l_templ_dt_minmax_csr INTO l_template_min_date, l_template_max_date;
1530
1531 /*Fix for Bug 6856082 starts*/
1532 OPEN get_team_start_date(p_template_id);
1533 FETCH get_team_start_date INTO l_team_start_date;
1534 CLOSE get_team_start_date ;
1535 /*Fix for Bug 6856082 ends*/
1536
1537 l_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
1538 --l_validate := 'Y';
1539 x_validate := l_validate;
1540 x_start_date_status := 'V';
1541 x_end_date_status := 'V';
1542
1543 /*Fix for Bug 6856082 starts*/
1544 /* Recalc the start and end dates on the template */
1545 l_number_of_days := l_prj_min_date - l_team_start_date;
1546
1547 --determine the requirement's start and end dates by adding l_number_of_days to
1548 --the template requirement's start and end dates.
1549 l_template_min_date := l_template_min_date + l_number_of_days ;
1550 l_template_max_date := l_template_max_date + l_number_of_days ;
1551
1552 /*Fix for Bug 6856082 ends*/
1553
1554 IF NVL(l_validate,'N') in ('Y','W') THEN
1555
1556 IF l_template_min_date IS NOT NULL THEN
1557 IF l_template_min_date < l_prj_min_date THEN
1558 x_start_date_status := 'I';
1559 END IF;
1560 END IF;
1561
1562 IF l_template_max_date IS NOT NULL THEN
1563 IF l_template_max_date > l_prj_max_date THEN
1564 x_end_date_status := 'I';
1565 END IF;
1566 END IF;
1567
1568
1569
1570 END IF;
1571
1572 EXCEPTION
1573
1574 when OTHERS then
1575 return;
1576
1577 END;
1578
1579 /*============Bug 6511907:PJR DATE VALIDATION ENHANCEMENT=======END=======*/
1580 END PA_PROJECT_DATES_UTILS;
[Home] [Help]