[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_DATES_PUB
Source
1 PACKAGE BODY PA_PROJECT_DATES_PUB AS
2 /* $Header: PARMPDPB.pls 120.5 2007/02/06 09:51:55 dthakker ship $ */
3
4 -- Global constant
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJECT_DATES_PUB';
6
7
8 -- API name : Copy_Project_Dates
9 -- Type : Public
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- p_api_version IN NUMBER Required Default = 1.0
13 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
14 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
15 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
16 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
17 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
18 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
19 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
20 -- p_project_id IN NUMBER Required
21 -- p_buffer IN NUMBER Optional Default = FND_API.G_MISS_NUM
22 -- x_return_status OUT VARCHAR2 Required
23 -- x_msg_count OUT NUMBER Required
24 -- x_msg_data OUT VARCHAR2 Optional
25
26 PROCEDURE COPY_PROJECT_DATES
27 ( p_api_version IN NUMBER := 1.0
28 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
29 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
30 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
31 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
32 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
33 ,p_debug_mode IN VARCHAR2 := 'N'
34 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
35 ,p_project_id IN NUMBER
36 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
37 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
38 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
39 )
40 IS
41 l_api_name CONSTANT VARCHAR(30) := 'Copy_Project_Dates';
42 l_api_version CONSTANT NUMBER := 1.0;
43
44 l_return_status VARCHAR2(1);
45 l_error_msg_code VARCHAR2(250);
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(250);
48 l_data VARCHAR2(250);
49 l_msg_index_out NUMBER;
50
51 CURSOR task_csr
52 IS
53 SELECT task_id
54 FROM PA_TASKS
55 WHERE project_id = p_project_id
56 ORDER BY wbs_level;
57
58 CURSOR latest_published_ver_csr
59 IS
60 SELECT element_version_id
61 FROM PA_PROJ_ELEM_VER_STRUCTURE
62 WHERE project_id = p_project_id
63 AND latest_eff_published_flag = 'Y';
64
65 CURSOR get_task_sch_dates_csr(c_structure_version_id NUMBER, c_task_id NUMBER)
66 IS
67 SELECT a.scheduled_start_date, a.scheduled_finish_date
68 FROM PA_PROJ_ELEM_VER_SCHEDULE a,
69 PA_PROJ_ELEMENT_VERSIONS b
70 WHERE b.parent_structure_version_id = c_structure_version_id
71 AND b.project_id = p_project_id
72 AND b.proj_element_id = c_task_id
73 -- Bug Fix 4868867
74 -- Ram Namburi
75 -- Added the following AND condition to eliminate Full table scan and Merge Join Cartesian.
76 AND b.element_version_id = a.element_version_id;
77
78
79 CURSOR get_task_est_dates_csr(c_task_id NUMBER)
80 IS
81 SELECT estimated_start_date, estimated_finish_date
82 FROM PA_PROGRESS_ROLLUP
83 WHERE project_id = p_project_id
84 AND object_id = c_task_id
85 AND object_type = 'PA_TASKS'
86 AND as_of_date = (
87 select max(as_of_date) from pa_progress_rollup
88 where project_id = p_project_id
89 and object_id = c_task_id
90 and object_type = 'PA_TASKS'
91 );
92
93 CURSOR get_task_act_dates_csr(c_task_id NUMBER)
94 IS
95 SELECT actual_start_date, actual_finish_date
96 FROM PA_PROGRESS_ROLLUP
97 WHERE project_id = p_project_id
98 AND object_id = c_task_id
99 AND object_type = 'PA_TASKS'
100 AND as_of_date = (
101 select max(as_of_date) from pa_progress_rollup
102 where project_id = p_project_id
103 and object_id = c_task_id
104 and object_type = 'PA_TASKS'
105 );
106
107 CURSOR get_task_bas_dates_csr(c_task_id NUMBER)
108 IS
109 SELECT baseline_start_date, baseline_finish_date
110 FROM PA_PROJ_ELEMENTS
111 WHERE proj_element_id = c_task_id;
112
113 CURSOR get_proj_sch_dates_csr
114 IS
115 SELECT scheduled_start_date, scheduled_finish_date
116 FROM PA_PROJECTS_ALL
117 WHERE project_id = p_project_id;
118
119 CURSOR get_proj_act_dates_csr
120 IS
121 SELECT actual_start_date, actual_finish_date
122 FROM PA_PROJECTS_ALL
123 WHERE project_id = p_project_id;
124
125 /*
126 CURSOR get_proj_est_dates_csr
127 IS
128 SELECT estimated_start_date, estimated_finish_date
129 FROM PA_PROJECTS_ALL
130 WHERE project_id = p_project_id;
131 */
132
133 CURSOR get_proj_bas_dates_csr
134 IS
135 SELECT baseline_start_date, baseline_finish_date
136 FROM PA_PROJECTS_ALL
137 WHERE project_id = p_project_id;
138
139 CURSOR get_proj_record_ver_number
140 IS
141 SELECT record_version_number
142 FROM PA_PROJECTS_ALL
143 WHERE project_id = p_project_id;
144
145 CURSOR get_task_record_ver_number(c_task_id NUMBER)
146 IS
147 SELECT record_version_number
148 FROM PA_TASKS
149 WHERE task_id = c_task_id;
150
151 CURSOR get_structure
152 IS
153 SELECT a.proj_element_id
154 FROM pa_proj_elements a,
155 pa_proj_structure_types b,
156 pa_structure_types c
157 WHERE a.proj_element_id = b.proj_element_id
158 AND a.object_type = 'PA_STRUCTURES'
159 AND a.project_id = p_project_id
160 AND b.structure_type_id = c.structure_type_id
161 AND c.structure_type = 'WORKPLAN';
162
163 CURSOR get_latest_struct_ver(c_structure_id NUMBER)
164 IS
165 select element_version_id
166 from pa_proj_elem_ver_structure
167 where project_id = p_project_id
168 and proj_element_id = c_structure_id
169 and status_code = 'STRUCTURE_PUBLISHED'
170 and latest_eff_published_flag = 'Y';
171
172 CURSOR get_work_struct_ver(c_structure_id NUMBER)
173 IS
174 SELECT element_version_id
175 from pa_proj_elem_ver_structure
176 where project_id = p_project_id
177 and proj_element_id = c_structure_id
178 and status_code <> 'STRUCTURE_PUBLISHED';
179
180 CURSOR get_tasks(c_structure_version_id NUMBER)
181 IS
182 SELECT a.proj_element_id,
183 a.element_version_id,
184 a.object_type,
185 a.wbs_level,
186 b.object_id_from1,
187 b.object_type_from
188 FROM pa_proj_element_versions a,
189 pa_object_relationships b
190 ,pa_proj_elements c --bug 4606475
191 WHERE a.parent_structure_version_id = c_structure_version_id
192 AND a.project_id = p_project_id
193 AND a.element_version_id = b.object_id_to1
194 AND b.object_type_to = 'PA_TASKS'
195 AND b.relationship_type(+) = 'S'
196 --bug 4606475
197 AND c.proj_element_id = a.proj_element_id
198 AND c.project_id=a.project_id
199 AND c.object_type = a.object_type
200 AND c.link_task_flag = 'N'
201 --bug 4606475
202 UNION
203 SELECT c.proj_element_id,
204 c.element_version_id,
205 c.object_type,
206 0,
207 to_number(NULL),
208 NULL
209 FROM pa_proj_element_versions c
210 WHERE c.element_version_id = c_structure_version_id;
211
212 CURSOR get_buffer(c_structure_id NUMBER) IS
213 SELECT NVL(TXN_DATE_SYNC_BUF_DAYS,0)
214 FROM PA_PROJ_WORKPLAN_ATTR
215 WHERE PROJ_ELEMENT_ID = c_structure_id;
216
217 l_process_number NUMBER;
218 l_cnt NUMBER;
219 l_rollup_table PA_SCHEDULE_OBJECTS_PVT.PA_SCHEDULE_OBJECTS_TBL_TYPE;
220 TYPE proj_elem_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
221 l_proj_elem_tbl proj_elem_tbl_type;
222
223 l_proj_element_id NUMBER;
224 l_element_version_id NUMBER;
225 l_object_type VARCHAR2(30);
226 l_wbs_level NUMBER;
227 l_parent_id NUMBER;
228 l_parent_object_type VARCHAR2(30);
229
230 l_structure_id NUMBER;
231 l_structure_ver_id NUMBER;
232 l_buffer NUMBER;
233 l_task_start_date DATE;
234 l_task_finish_date DATE;
235 l_proj_start_date DATE;
236 l_proj_finish_date DATE;
237 l_structure_version_id NUMBER;
238 l_task_id NUMBER;
239 l_task_record_version_number NUMBER;
240 l_proj_record_version_number NUMBER;
241
242 --bug 2831656
243 --modified the cursor for bug 3043580
244 CURSOR cur_proj_elem_id( c_task_version_id NUMBER )
245 IS
246 SELECT ppev.proj_element_id,
247 ppe.element_number
248 FROM pa_proj_element_versions ppev,
249 pa_proj_elements ppe
250 WHERE ppev.element_version_id = c_task_version_id
251 AND ppev.proj_element_id = ppe.proj_element_id
252 AND ppe.link_task_flag = 'N' --bug 4606475
253 ;
254
255 l_proj_element_id2 NUMBER;
256 l_element_number VARCHAR2(240); --bug 3043580
257 --bug 2831656
258
259 l_act_fin_date_flag VARCHAR2(1) := 'Y'; --bug 4229865
260 -- Start of addition for bug 5665772
261 l_parent_task_id NUMBER;
262 l_tstart_date DATE;
263 l_tend_date DATE;
264 l_tcnt NUMBER;
265
266 TYPE TASK_DATES_REC_TYPE IS RECORD (
267 TASK_ID NUMBER := NULL,
268 PARENT_TASK_ID NUMBER := NULL,
269 OLD_START_DATE DATE := NULL,
270 OLD_END_DATE DATE := NULL,
271 NEW_START_DATE DATE := NULL,
272 NEW_END_DATE DATE := NULL
273 );
274
275 TYPE TASK_DATES_TBL_TYPE IS TABLE OF TASK_DATES_REC_TYPE
276 INDEX BY BINARY_INTEGER;
277
278 l_task_dates TASK_DATES_TBL_TYPE;
279 -- End of addition for bug 5665772
280
281 BEGIN
282 pa_debug.init_err_stack('PA_PROJECT_DATES_PUB.Copy_Project_Dates');
283
284 if (p_debug_mode = 'Y') then
285 pa_debug.debug('PA_PROJECT_DATES_PUB.Copy_Project_Dates BEGIN');
286 end if;
287
288 if p_commit = FND_API.G_TRUE then
289 savepoint copy_project_dates;
290 end if;
291
292 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
293 raise FND_API.G_EXC_UNEXPECTED_ERROR;
294 end if;
295
296 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
297 FND_MSG_PUB.initialize;
298 end if;
299
300 --select structure version id
301 OPEN get_structure;
302 FETCH get_structure into l_structure_id;
303 CLOSE get_structure;
304
305 IF ('Y' = PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(
306 p_project_id, l_structure_id)) THEN
307 --Get latest published version id
308 OPEN get_latest_struct_ver(l_structure_id);
309 FETCH get_latest_struct_ver into l_structure_ver_id;
310 CLOSE get_latest_struct_ver;
311 ELSE
312 --Get working version id
313 --this should only return 1 row because this is only called when
314 -- when structure is of both workplan and financial type
315 OPEN get_work_struct_ver(l_structure_id);
316 FETCH get_work_struct_ver into l_structure_ver_id;
317 CLOSE get_work_struct_ver;
318 END IF;
319
320 --Get buffer from workplan table
321 OPEN get_buffer(l_structure_id);
322 FETCH get_buffer into l_buffer;
323 CLOSE get_buffer;
324
325 --bug 4229865
326 --do not consider actual finish dates if any of the task does not have act finish date.
327 --The API returns 'N' if any of the tasks in the structure version does not have actual finish date.
328 -- chek_one_task_has_act_st_date will make sure that it rollsup schedule finish date if there is no task
329 -- with act start date.
330 /* Commented for bug 5338208
331 IF PA_PROJECT_DATES_UTILS.chek_one_task_has_act_st_date(p_project_id,l_structure_ver_id) = 'Y'
332 THEN
333 l_act_fin_date_flag := PA_PROJECT_DATES_UTILS.chek_all_tsk_have_act_fin_dt(p_project_id,l_structure_ver_id);
334 END IF;
335
336 --bug 4241863
337 IF l_act_fin_date_flag = 'Y'
338 THEN
339 UPDATE pa_tasks
340 set completion_date = NULL
341 WHERE project_id=p_project_id;
342 END IF;
343 --end bug 4241863
344 Commented for bug 5338208*/
345 --Get dates from all tasks
346 OPEN get_tasks(l_structure_ver_id);
347 l_cnt := 0;
348 LOOP
349 l_cnt := l_cnt + 1;
350 FETCH get_tasks into l_proj_element_id,
351 l_element_version_id,
352 l_object_type,
353 l_wbs_level,
354 l_parent_id,
355 l_parent_object_type;
356 EXIT WHEN get_tasks%NOTFOUND;
357
358 --For each task, get the start and finish date
359 PA_PROJECT_DATES_UTILS.GET_TASK_COPY_DATES(p_project_id => p_project_id,
360 p_proj_element_id => l_proj_element_id,
361 p_parent_structure_version_id => l_structure_ver_id,
362 x_task_start_date => l_task_start_date,
363 x_task_finish_date => l_task_finish_date,
364 p_act_fin_date_flag => l_act_fin_date_flag --bug 4229865
365 );
366
367 --Add buffer
368 l_task_start_date := l_task_start_date - l_buffer;
369 l_task_finish_date := l_task_finish_date + l_buffer;
370
371 -- Start of addition for bug 5338208
372 -- changed IS_LOWEST_TASK() to IS_LOWEST_PROJ_TASK for bug 5698103
373 -- If PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(l_element_version_id) <> 'Y' then
374 If PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_PROJ_TASK(l_element_version_id, p_project_id) <> 'Y' then
375 l_task_start_date := Null;
376 l_task_finish_date:= Null;
377 END if;
378 -- End of addition for bug 5338208
379 --Add to the rollup table
380 l_proj_elem_tbl(l_cnt) := l_proj_element_id;
381 l_rollup_table(l_cnt).object_id := l_element_version_id;
382 l_rollup_table(l_cnt).object_type := l_object_type;
383 l_rollup_table(l_cnt).wbs_level := l_wbs_level;
384 l_rollup_table(l_cnt).start_date1 := trunc(l_task_start_date); --3961867, rtarway, added trunc
385 l_rollup_table(l_cnt).finish_date1 := trunc(l_task_finish_date); --3961867, rtarway, added trunc
386 l_rollup_table(l_cnt).parent_object_id := l_parent_id;
387 l_rollup_table(l_cnt).parent_object_type := l_parent_object_type;
388 --bug 2868685: Removing because when actual dates at the project level
389 -- are different from the rollup values, the changes
390 -- will not be reflected
391 -- IF (l_buffer <> 0) OR (l_object_type = 'PA_STRUCTURES') THEN
392 -- l_rollup_table(l_cnt).dirty_flag1 := 'Y'; --all dates are modified with buffer
393 -- ELSE
394 -- l_rollup_table(l_cnt).dirty_flag1 := 'N'; --no modification
395 -- END IF;
396 END LOOP;
397
398 CLOSE get_tasks; --Bug 3867426
399 --Call rollup api
400 --bug 2868685: Changed p_partial_process_flag1 to 'N' because
401 -- when actual dates at the project level are
402 -- different from the rollup values, the changes
403 -- will not be reflected
404 PA_SCHEDULE_OBJECTS_PVT.GENERATE_SCHEDULE(
405 p_debug_mode => 'N',
406 p_data_structure => l_rollup_table,
407 x_return_status => l_return_status,
408 x_msg_count => l_msg_count,
409 x_msg_data => l_msg_data,
410 x_process_number => l_process_number,
411 p_process_flag1 => 'Y',
412 p_partial_process_flag1 => 'N',
413 p_process_rollup_flag1 => 'Y');
414
415 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
416 x_msg_count := FND_MSG_PUB.count_msg;
417 if x_msg_count = 1 then
418 pa_interface_utils_pub.get_messages
419 (p_encoded => FND_API.G_TRUE,
420 p_msg_index => 1,
421 p_msg_count => l_msg_count,
422 p_msg_data => l_msg_data,
423 p_data => l_data,
424 p_msg_index_out => l_msg_index_out);
425 x_msg_data := l_data;
426 end if;
427 raise FND_API.G_EXC_ERROR;
428 end if;
429
430 --Bug 3919138 : Sort the rollup table by wbs_level in ascending order.
431 --This is necessary because update_task api below must be called with the parent tasks
432 --first. Else, PA_TASKS_MAINT_UTILS.CHECK_START_DATE results in an error when a buffer value
433 --exists for adjusting task start dates
434 DECLARE
435 l_temp_object_id NUMBER;
436 l_temp_object_type VARCHAR2(30);
437 l_temp_wbs_level NUMBER;
438 l_temp_start_date1 DATE;
439 l_temp_finish_date1 DATE;
440 l_temp_parent_object_id NUMBER;
441 l_temp_parent_object_type VARCHAR2(30);
442 BEGIN
443 --Sort only if there are 2 or more records in the table
444 IF nvl(l_rollup_table.LAST,0) > 1 THEN
445 FOR i IN 1..(l_rollup_table.count-1) LOOP
446 FOR j IN 1..(l_rollup_table.LAST-i) LOOP
447 --Sort in ascending order
448 IF l_rollup_table(j).wbs_level > l_rollup_table(j+1).wbs_level THEN
449 --Swap the two table records
450 l_temp_object_id := l_rollup_table(j).object_id ;
451 l_temp_object_type := l_rollup_table(j).object_type ;
452 l_temp_wbs_level := l_rollup_table(j).wbs_level ;
453 l_temp_start_date1 := l_rollup_table(j).start_date1 ;
454 l_temp_finish_date1 := l_rollup_table(j).finish_date1 ;
455 l_temp_parent_object_id := l_rollup_table(j).parent_object_id ;
456 l_temp_parent_object_type := l_rollup_table(j).parent_object_type;
457
458 l_rollup_table(j).object_id := l_rollup_table(j+1).object_id ;
459 l_rollup_table(j).object_type := l_rollup_table(j+1).object_type ;
460 l_rollup_table(j).wbs_level := l_rollup_table(j+1).wbs_level ;
461 l_rollup_table(j).start_date1 := l_rollup_table(j+1).start_date1 ;
462 l_rollup_table(j).finish_date1 := l_rollup_table(j+1).finish_date1 ;
463 l_rollup_table(j).parent_object_id := l_rollup_table(j+1).parent_object_id ;
464 l_rollup_table(j).parent_object_type := l_rollup_table(j+1).parent_object_type;
465
466 l_rollup_table(j+1).object_id := l_temp_object_id ;
467 l_rollup_table(j+1).object_type := l_temp_object_type ;
468 l_rollup_table(j+1).wbs_level := l_temp_wbs_level ;
469 l_rollup_table(j+1).start_date1 := l_temp_start_date1 ;
470 l_rollup_table(j+1).finish_date1 := l_temp_finish_date1 ;
471 l_rollup_table(j+1).parent_object_id := l_temp_parent_object_id ;
472 l_rollup_table(j+1).parent_object_type := l_temp_parent_object_type;
473 END IF;
474 END LOOP;
475 END LOOP;
476 END IF;
477 END;
478 --Bug 3919138
479
480 l_cnt := l_rollup_table.First;
481 LOOP
482 --bug 3716805
483 exit when l_rollup_table.count = 0;
484 --end bug 3716805
485 IF (l_rollup_table(l_cnt).object_type = 'PA_STRUCTURES') THEN
486 --Update project first; otherwise task dates might exceed project dates
487 -- Now copy over project level dates
488 OPEN get_proj_record_ver_number;
489 FETCH get_proj_record_ver_number INTO l_proj_record_version_number;
490 CLOSE get_proj_record_ver_number;
491
492 PA_PROJECT_DATES_PUB.Update_Project_Dates (
493 p_init_msg_list => p_init_msg_list
494 ,p_commit => FND_API.G_FALSE
495 ,p_validate_only => FND_API.G_FALSE -- Bug 2786525
496 ,p_calling_module => p_calling_module
497 ,p_debug_mode => p_debug_mode
498 ,p_project_id => p_project_id
499 ,p_date_type => 'TRANSACTION'
500 ,p_start_date => l_rollup_table(l_cnt).start_date1
501 ,p_finish_date => l_rollup_table(l_cnt).finish_date1
502 ,p_record_version_number => l_proj_record_version_number
503 ,x_return_status => l_return_status
504 ,x_msg_count => l_msg_count
505 ,x_msg_data => l_msg_data );
506
507 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
508 x_msg_count := FND_MSG_PUB.count_msg;
509 if x_msg_count = 1 then
510 pa_interface_utils_pub.get_messages
511 (p_encoded => FND_API.G_TRUE,
512 p_msg_index => 1,
513 p_msg_count => l_msg_count,
514 p_msg_data => l_msg_data,
515 p_data => l_data,
516 p_msg_index_out => l_msg_index_out);
517 x_msg_data := l_data;
518 end if;
519 raise FND_API.G_EXC_ERROR;
520 end if;
521
522 EXIT;
523 END IF;
524 EXIT when l_cnt = l_rollup_table.Last;
525 l_cnt := l_rollup_table.NEXT(l_cnt);
526 END LOOP;
527
528 --Update tasks
529 l_cnt := l_rollup_table.First;
530 l_tcnt := 0; -- added for bug 5665772
531
532 LOOP
533 --bug 3716805
534 exit when l_rollup_table.count = 0;
535 --end bug 3716805
536 IF (l_rollup_table(l_cnt).object_type = 'PA_TASKS') THEN
537 /* Not doing anything. Also I checked PA_TASK_MAINT_PUB and PVT.update_task. Its not using record version number
538 so its useless here.
539 OPEN get_task_record_ver_number(l_task_id);
540 FETCH get_task_record_ver_number INTO l_task_record_version_number;
541 CLOSE get_task_record_ver_number;
542 */
543
544 --bug 2831656
545 OPEN cur_proj_elem_id( l_rollup_table(l_cnt).object_id);
546 FETCH cur_proj_elem_id INTO l_proj_element_id2,
547 l_element_number; --bug 3043580;
548 CLOSE cur_proj_elem_id;
549 --bug 2831656
550
551 --bug 3974958
552 IF ('Y' = PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id2)) THEN
553 --end bug 3974958
554 /* Start of changes for bug 5665772 */
555
556 SELECT pt.parent_task_id,
557 pt.start_date,
558 pt.completion_date
559 INTO l_parent_task_id,
560 l_tstart_date,
561 l_tend_date
562 FROM pa_tasks pt
563 WHERE pt.task_id = l_proj_element_id2;
564
565 IF ((nvl(l_rollup_table(l_cnt).start_date1,sysdate) <> nvl(l_tstart_date,sysdate))
566 OR (nvl(l_rollup_table(l_cnt).finish_date1,sysdate) <> nvl(l_tend_date,sysdate))) THEN
567
568 l_tcnt := l_tcnt + 1;
569
570 l_task_dates(l_tcnt).task_id := l_proj_element_id2;
571 l_task_dates(l_tcnt).parent_task_id := l_parent_task_id;
572 l_task_dates(l_tcnt).old_start_date := l_tstart_date;
573 l_task_dates(l_tcnt).old_end_date := l_tend_date;
574 l_task_dates(l_tcnt).new_start_date := l_rollup_table(l_cnt).start_date1;
575 l_task_dates(l_tcnt).new_end_date := l_rollup_table(l_cnt).finish_date1;
576
577 END IF;
578
579 /* End of changes for bug 5665772 */
580 PA_TASKS_MAINT_PUB.Update_Task (
581 p_init_msg_list => p_init_msg_list
582 ,p_commit => FND_API.G_FALSE
583 ,p_validate_only => p_validate_only
584 ,p_calling_module => p_calling_module
585 ,p_debug_mode => p_debug_mode
586 ,p_project_id => p_project_id
587 -- ,p_task_id => l_proj_elem_tbl(l_cnt) --bug 2831656
588 ,p_task_id => l_proj_element_id2 --bug 2831656
589 ,p_task_number => l_element_number --bug 3043580
590 ,p_task_start_date => l_rollup_table(l_cnt).start_date1
591 ,p_task_completion_date => l_rollup_table(l_cnt).finish_date1
592 ,p_record_version_number => l_task_record_version_number
593 ,p_update_subtasks_end_dt => 'N' --bug 4241863
594 ,p_dates_check => 'N' --bug 5665772
595 ,x_return_status => l_return_status
596 ,x_msg_count => l_msg_count
597 ,x_msg_data => l_msg_data );
598 --bug 3974958
599 END IF;
600 --end bug 3974958
601
602 END IF;
603
604 --bug 2868685
605 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
606 x_msg_count := FND_MSG_PUB.count_msg;
607 if x_msg_count = 1 then
608 pa_interface_utils_pub.get_messages
609 (p_encoded => FND_API.G_TRUE,
610 p_msg_index => 1,
611 p_msg_count => l_msg_count,
612 p_msg_data => l_msg_data,
613 p_data => l_data,
614 p_msg_index_out => l_msg_index_out);
615 x_msg_data := l_data;
616 end if;
617 raise FND_API.G_EXC_ERROR;
618 end if;
619 --bug 2868685
620
621 EXIT when l_cnt = l_rollup_table.Last;
622 l_cnt := l_rollup_table.NEXT(l_cnt);
623 END LOOP;
624
625 /* Start of changes for bug 5665772 */
626
627 -- Validate transaction dates
628
629 IF l_task_dates.COUNT <> 0 THEN
630
631 l_tcnt := l_task_dates.First;
632 LOOP
633
634 IF (nvl(l_task_dates(l_tcnt).old_start_date,sysdate) <>
635 nvl(l_task_dates(l_tcnt).new_start_date,sysdate)) THEN
636
637 PA_TASKS_MAINT_UTILS.Check_Start_Date(
638 p_project_id => p_project_id,
639 p_parent_task_id => l_task_dates(l_tcnt).parent_task_id,
640 p_task_id => NULL,
641 p_start_date => l_task_dates(l_tcnt).new_start_date,
642 x_return_status => l_return_status,
643 x_msg_count => l_msg_count,
644 x_msg_data => l_msg_data);
645
646 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
647 PA_UTILS.ADD_MESSAGE('PA', l_msg_data);
648 END IF;
649
650 END IF;
651
652 IF (nvl(l_task_dates(l_tcnt).old_end_date,sysdate) <>
653 nvl(l_task_dates(l_tcnt).new_end_date,sysdate)) THEN
654
655 PA_TASKS_MAINT_UTILS.Check_End_Date(
656 p_project_id => p_project_id,
657 p_parent_task_id => l_task_dates(l_tcnt).parent_task_id,
658 p_task_id => l_task_dates(l_tcnt).task_id,
659 p_end_date => l_task_dates(l_tcnt).new_end_date,
660 x_return_status => l_return_status,
661 x_msg_count => l_msg_count,
662 x_msg_data => l_msg_data);
663
664 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
665 PA_UTILS.ADD_MESSAGE('PA', l_msg_data);
666 END IF;
667
668 END IF;
669
670 l_msg_count := FND_MSG_PUB.count_msg;
671 IF (l_msg_count > 0) THEN
672 x_msg_count := l_msg_count;
673 IF (x_msg_count = 1) THEN
674 pa_interface_utils_pub.get_messages(
675 p_encoded => FND_API.G_TRUE,
676 p_msg_index => 1,
677 p_data => l_data,
678 p_msg_index_out => l_msg_index_out);
679 x_msg_data := l_data;
680 END IF;
681 RAISE FND_API.G_EXC_ERROR;
682 END IF;
683
684 EXIT when l_tcnt = l_task_dates.Last;
685 l_tcnt := l_task_dates.NEXT(l_tcnt);
686
687 END LOOP;
688
689 END IF;
690
691 /* End of changes for bug 5665772 */
692
693 -- Commented out by hsiu
694 -- date_type is no longer an input
695 -- if p_date_type NOT IN ('ESTIMATED', 'ACTUAL', 'BASELINE', 'SCHEDULED') THEN
696 -- PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
697 -- p_msg_name => 'PA_INVALID_DATE_TYPE');
698 -- end if;
699
700 -- l_msg_count := FND_MSG_PUB.count_msg;
701 -- if l_msg_count > 0 then
702 -- x_msg_count := l_msg_count;
703 -- if x_msg_count = 1 then
704 -- pa_interface_utils_pub.get_messages
705 -- (p_encoded => FND_API.G_TRUE,
706 -- p_msg_index => 1,
707 -- p_msg_count => l_msg_count,
708 -- p_msg_data => l_msg_data,
709 -- p_data => l_data,
710 -- p_msg_index_out => l_msg_index_out);
711 -- x_msg_data := l_data;
712 -- end if;
713 -- raise FND_API.G_EXC_ERROR;
714 -- end if;
715
716 -- Loop through all of the tasks in this project and copy the appropriate dates
717 -- OPEN task_csr;
718 -- LOOP
719 -- FETCH task_csr INTO l_task_id;
720 -- EXIT WHEN task_csr%NOTFOUND;
721
722 -- OPEN get_task_record_ver_number(l_task_id);
723 -- FETCH get_task_record_ver_number INTO l_task_record_version_number;
724 -- CLOSE get_task_record_ver_number;
725
726 -- Removed for new copy strategy
727 --
728 -- if p_date_type = 'SCHEDULED' then
729 -- OPEN latest_published_ver_csr;
730 -- FETCH latest_published_ver_csr INTO l_structure_version_id;
731 -- if latest_published_ver_csr%NOTFOUND then
732 -- PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
733 -- p_msg_name => 'PA_NO_PUBLISHED_VERSION');
734 -- CLOSE latest_published_ver_csr;
735 --
736 -- x_msg_count := FND_MSG_PUB.count_msg;
737 --
738 -- pa_interface_utils_pub.get_messages
739 -- (p_encoded => FND_API.G_TRUE,
740 -- p_msg_index => 1,
741 -- p_msg_count => l_msg_count,
742 -- p_msg_data => l_msg_data,
743 -- p_data => l_data,
744 -- p_msg_index_out => l_msg_index_out);
745 -- x_msg_data := l_data;
746 --
747 -- raise FND_API.G_EXC_ERROR;
748 -- else
749 -- CLOSE latest_published_ver_csr;
750 --
751 -- OPEN get_task_sch_dates_csr(l_structure_version_id, l_task_id);
752 -- FETCH get_task_sch_dates_csr INTO l_task_start_date, l_task_finish_date;
753 -- CLOSE get_task_sch_dates_csr;
754 --
755 -- end if;
756 -- elsif p_date_type = 'ACTUAL' then
757 -- OPEN get_task_act_dates_csr(l_task_id);
758 -- FETCH get_task_act_dates_csr INTO l_task_start_date, l_task_finish_date;
759 -- CLOSE get_task_act_dates_csr;
760 -- elsif p_date_type = 'ESTIMATED' then
761 -- OPEN get_task_est_dates_csr(l_task_id);
762 -- FETCH get_task_est_dates_csr INTO l_task_start_date, l_task_finish_date;
763 -- CLOSE get_task_est_dates_csr;
764 -- elsif p_date_type = 'BASELINE' then
765 -- OPEN get_task_bas_dates_csr(l_task_id);
766 -- FETCH get_task_bas_dates_csr INTO l_task_start_date, l_task_finish_date;
767 -- CLOSE get_task_bas_dates_csr;
768 -- end if;
769 --
770 -- if(p_buffer <> FND_API.G_MISS_NUM) AND (p_buffer IS NOT NULL) then
771 -- l_task_start_date := l_task_start_date + p_buffer;
772 -- l_task_finish_date := l_task_finish_date + p_buffer;
773 -- end if;
774 --
775 -- PA_TASKS_MAINT_PUB.Update_Task (
776 -- p_init_msg_list => p_init_msg_list
777 -- ,p_commit => FND_API.G_FALSE
778 -- ,p_validate_only => p_validate_only
779 -- ,p_calling_module => p_calling_module
780 -- ,p_debug_mode => p_debug_mode
781 -- ,p_project_id => p_project_id
782 -- ,p_task_id => l_task_id
783 -- ,p_task_start_date => l_task_start_date
784 -- ,p_task_completion_date => l_task_finish_date
785 -- ,p_record_version_number => l_task_record_version_number
786 -- ,x_return_status => l_return_status
787 -- ,x_msg_count => l_msg_count
788 -- ,x_msg_data => l_msg_data );
789 --
790 -- if l_return_status <> FND_API.G_RET_STS_SUCCESS then
791 -- x_msg_count := FND_MSG_PUB.count_msg;
792 -- if x_msg_count = 1 then
793 -- pa_interface_utils_pub.get_messages
794 -- (p_encoded => FND_API.G_TRUE,
795 -- p_msg_index => 1,
796 -- p_msg_count => l_msg_count,
797 -- p_msg_data => l_msg_data,
798 -- p_data => l_data,
799 -- p_msg_index_out => l_msg_index_out);
800 -- x_msg_data := l_data;
801 -- end if;
802 -- raise FND_API.G_EXC_ERROR;
803 -- end if;
804 -- END LOOP;
805
806 -- Now copy over project level dates
807 -- OPEN get_proj_record_ver_number;
808 -- FETCH get_proj_record_ver_number INTO l_proj_record_version_number;
809 -- CLOSE get_proj_record_ver_number;
810
811 -- if p_date_type = 'SCHEDULED' then
812 -- OPEN get_proj_sch_dates_csr;
813 -- FETCH get_proj_sch_dates_csr INTO l_proj_start_date, l_proj_finish_date;
814 -- CLOSE get_proj_sch_dates_csr;
815 -- elsif p_date_type = 'ACTUAL' then
816 -- OPEN get_proj_act_dates_csr;
817 -- FETCH get_proj_act_dates_csr INTO l_proj_start_date, l_proj_finish_date;
818 -- CLOSE get_proj_act_dates_csr;
819 -- elsif p_date_type = 'ESTIMATED' then
820 -- OPEN get_proj_est_dates_csr;
821 -- FETCH get_proj_est_dates_csr INTO l_proj_start_date, l_proj_finish_date;
822 -- CLOSE get_proj_est_dates_csr;
823 -- elsif p_date_type = 'BASELINE' then
824 -- OPEN get_proj_bas_dates_csr;
825 -- FETCH get_proj_bas_dates_csr INTO l_proj_start_date, l_proj_finish_date;
826 -- CLOSE get_proj_bas_dates_csr;
827 -- end if;
828
829 -- if(p_buffer <> FND_API.G_MISS_NUM) AND (p_buffer IS NOT NULL) then
830 -- l_proj_start_date := l_proj_start_date + p_buffer;
831 -- l_proj_finish_date := l_proj_finish_date + p_buffer;
832 -- end if;
833
834 -- PA_PROJECT_DATES_PUB.Update_Project_Dates (
835 -- p_init_msg_list => p_init_msg_list
836 -- ,p_commit => FND_API.G_FALSE
837 -- ,p_validate_only => p_validate_only
838 -- ,p_calling_module => p_calling_module
839 -- ,p_debug_mode => p_debug_mode
840 -- ,p_project_id => p_project_id
841 -- ,p_date_type => p_date_type
842 -- ,p_start_date => l_proj_start_date
843 -- ,p_finish_date => l_proj_finish_date
844 -- ,p_record_version_number => l_proj_record_version_number
845 -- ,x_return_status => l_return_status
846 -- ,x_msg_count => l_msg_count
847 -- ,x_msg_data => l_msg_data );
848
849 -- if l_return_status <> FND_API.G_RET_STS_SUCCESS then
850 -- x_msg_count := FND_MSG_PUB.count_msg;
851 -- if x_msg_count = 1 then
852 -- pa_interface_utils_pub.get_messages
853 -- (p_encoded => FND_API.G_TRUE,
854 -- p_msg_index => 1,
855 -- p_msg_count => l_msg_count,
856 -- p_msg_data => l_msg_data,
857 -- p_data => l_data,
858 -- p_msg_index_out => l_msg_index_out);
859 -- x_msg_data := l_data;
860 -- end if;
861 -- raise FND_API.G_EXC_ERROR;
862 -- end if;
863
864 x_return_status := FND_API.G_RET_STS_SUCCESS;
865
866 if p_commit = FND_API.G_TRUE then
867 commit work;
868 end if;
869
870 if (p_debug_mode = 'Y') then
871 pa_debug.debug('PA_PROJECT_DATES_PUB.Copy_Project_Dates END');
872 end if;
873
874 EXCEPTION
875 when FND_API.G_EXC_ERROR then
876 if p_commit = FND_API.G_TRUE then
877 rollback to copy_project_dates;
878 end if;
879 x_return_status := FND_API.G_RET_STS_ERROR;
880 when FND_API.G_EXC_UNEXPECTED_ERROR then
881 if p_commit = FND_API.G_TRUE then
882 rollback to copy_project_dates;
883 end if;
884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
885 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_DATES_PUB',
886 p_procedure_name => 'Copy_Project_Dates',
887 p_error_text => SUBSTRB(SQLERRM,1,240));
888 when OTHERS then
889 if p_commit = FND_API.G_TRUE then
890 rollback to copy_project_dates;
891 end if;
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
893 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_DATES_PUB',
894 p_procedure_name => 'Copy_Project_Dates',
895 p_error_text => SUBSTRB(SQLERRM,1,240));
896 raise;
897 END COPY_PROJECT_DATES;
898
899
900 -- API name : Update_Project_Dates
901 -- Type : Public
902 -- Pre-reqs : None.
903 -- Parameters :
904 -- p_api_version IN NUMBER Required Default = 1.0
905 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
906 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
907 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
908 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
909 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
910 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
911 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
912 -- p_project_id IN NUMBER Required
913 -- p_date_type IN VARCHAR2 Required
914 -- p_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
915 -- p_finish_date IN DATE Optional Default = FND_API.G_MISS_DATE
916 -- p_record_version_number IN NUMBER Optional Default = FND_API.G_MISS_NUM
917 -- x_return_status OUT VARCHAR2 Required
918 -- x_msg_count OUT NUMBER Required
919 -- x_msg_data OUT VARCHAR2 Optional
920
921 PROCEDURE UPDATE_PROJECT_DATES
922 ( p_api_version IN NUMBER := 1.0
923 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
924 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
925 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
926 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
927 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
928 ,p_debug_mode IN VARCHAR2 := 'N'
929 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
930 ,p_project_id IN NUMBER
931 ,p_date_type IN VARCHAR2
932 ,p_start_date IN DATE := FND_API.G_MISS_DATE
933 ,p_finish_date IN DATE := FND_API.G_MISS_DATE
934 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
935 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
936 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
937 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
938 )
939 IS
940 l_api_name CONSTANT VARCHAR(30) := 'Update_Project_Dates';
941 l_api_version CONSTANT NUMBER := 1.0;
942
943 l_return_status VARCHAR2(1);
944 l_error_msg_code VARCHAR2(250);
945 l_msg_count NUMBER;
946 l_msg_data VARCHAR2(250);
947 l_data VARCHAR2(250);
948 l_msg_index_out NUMBER;
949
950 BEGIN
951 pa_debug.init_err_stack('PA_PROJECT_DATES_PUB.Update_Project_Dates');
952
953 if (p_debug_mode = 'Y') then
954 pa_debug.debug('PA_PROJECT_DATES_PUB.Update_Project_Dates BEGIN');
955 end if;
956
957 if p_commit = FND_API.G_TRUE then
958 savepoint update_project_dates;
959 end if;
960
961 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
962 raise FND_API.G_EXC_UNEXPECTED_ERROR;
963 end if;
964
965 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
966 FND_MSG_PUB.initialize;
967 end if;
968
969
970 if p_date_type NOT IN ('PROJECT', 'TRANSACTION', 'ESTIMATED', 'ACTUAL', 'BASELINE', 'SCHEDULED') THEN
971 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
972 p_msg_name => 'PA_INVALID_DATE_TYPE');
973 end if;
974
975 l_msg_count := FND_MSG_PUB.count_msg;
976 if l_msg_count > 0 then
977 x_msg_count := l_msg_count;
978 if x_msg_count = 1 then
979 pa_interface_utils_pub.get_messages
980 (p_encoded => FND_API.G_TRUE,
981 p_msg_index => 1,
982 p_msg_count => l_msg_count,
983 p_msg_data => l_msg_data,
984 p_data => l_data,
985 p_msg_index_out => l_msg_index_out);
986 x_msg_data := l_data;
987 end if;
988 raise FND_API.G_EXC_ERROR;
989 end if;
990
991 PA_PROJECT_DATES_PVT.UPDATE_PROJECT_DATES
992 ( p_commit => FND_API.G_FALSE
993 ,p_validate_only => p_validate_only
994 ,p_validation_level => p_validation_level
995 ,p_calling_module => p_calling_module
996 ,p_debug_mode => p_debug_mode
997 ,p_max_msg_count => p_max_msg_count
998 ,p_project_id => p_project_id
999 ,p_date_type => p_date_type
1000 ,p_start_date => p_start_date
1001 ,p_finish_date => p_finish_date
1002 ,p_record_version_number => p_record_version_number
1003 ,x_return_status => l_return_status
1004 ,x_msg_count => l_msg_count
1005 ,x_msg_data => l_msg_data );
1006
1007 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1008 x_msg_count := FND_MSG_PUB.count_msg;
1009 if x_msg_count = 1 then
1010 pa_interface_utils_pub.get_messages
1011 (p_encoded => FND_API.G_TRUE,
1012 p_msg_index => 1,
1013 p_msg_count => l_msg_count,
1014 p_msg_data => l_msg_data,
1015 p_data => l_data,
1016 p_msg_index_out => l_msg_index_out);
1017 x_msg_data := l_data;
1018 end if;
1019 raise FND_API.G_EXC_ERROR;
1020 end if;
1021
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023
1024 if p_commit = FND_API.G_TRUE then
1025 commit work;
1026 end if;
1027
1028 if (p_debug_mode = 'Y') then
1029 pa_debug.debug('PA_PROJECT_DATES_PUB.Update_Project_Dates END');
1030 end if;
1031
1032 EXCEPTION
1033 when FND_API.G_EXC_ERROR then
1034 if p_commit = FND_API.G_TRUE then
1035 rollback to update_project_dates;
1036 end if;
1037 x_return_status := FND_API.G_RET_STS_ERROR;
1038 when FND_API.G_EXC_UNEXPECTED_ERROR then
1039 if p_commit = FND_API.G_TRUE then
1040 rollback to update_project_dates;
1041 end if;
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_DATES_PUB',
1044 p_procedure_name => 'Update_Project_Dates',
1045 p_error_text => SUBSTRB(SQLERRM,1,240));
1046 when OTHERS then
1047 if p_commit = FND_API.G_TRUE then
1048 rollback to update_project_dates;
1049 end if;
1050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1051 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_DATES_PUB',
1052 p_procedure_name => 'Update_Project_Dates',
1053 p_error_text => SUBSTRB(SQLERRM,1,240));
1054 raise;
1055 END UPDATE_PROJECT_DATES;
1056
1057
1058 END PA_PROJECT_DATES_PUB;