[Home] [Help]
PACKAGE BODY: APPS.PA_STRUCT_TASK_ROLLUP_PUB
Source
1 Package Body PA_STRUCT_TASK_ROLLUP_PUB as
2 /* $Header: PATKRUPB.pls 120.12.12010000.4 2008/09/21 04:46:45 kmaddi ship $ */
3
4 -- API name : Rollup_From_Subproject
5 -- Type : Public procedure
6 -- Pre-reqs : None
7 -- Return Value : N/A
8 -- Prameters
9 -- p_api_version IN NUMBER := 1.0
10 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
11 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
12 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
13 -- p_validation_level IN VARCHAR2 := 100
14 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
15 -- p_debug_mode IN VARCHAR2 := 'N'
16 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
17 -- p_element_versions IN PA_NUM_1000_NUM
18 -- x_return_status OUT VARCHAR2
19 -- x_msg_count OUT NUMBER
20 -- x_msg_data OUT VARCHAR2
21
22 -- This procedure is created as a to rollup subproject association,
23 /* Bug 6854670: Renamed procedure, changed type for input parameter p_element_versions
24 * This procedure as of now is called only directly from the procedure Program_Schedule_dates_rollup
25 * in the same package.
26 * All other flows still calls Rollup_From_Subproject
27 */
28
29 PROCEDURE Rollup_From_Subproject_Unltd(
30 p_api_version IN NUMBER := 1.0
31 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
32 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
33 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
34 ,p_validation_level IN VARCHAR2 := 100
35 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
36 ,p_debug_mode IN VARCHAR2 := 'N'
37 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
38 ,p_element_versions IN SYSTEM.PA_NUM_TBL_TYPE
39 ,p_published_str_ver_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM --bug5861729
40 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
41 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
42 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
43 )
44 IS
45 --
46 l_rollup_table PA_SCHEDULE_OBJECTS_PVT.PA_SCHEDULE_OBJECTS_TBL_TYPE;
47 l_process_number NUMBER;
48 --
49 TYPE t_proj_id_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
50 l_proj_id_tbl t_proj_id_table;
51 --
52 --added for bulk update
53 TYPE t_schDate_Tbl IS TABLE Of DATE INDEX BY BINARY_INTEGER;
54 l_old_sch_st_date_tbl t_schDate_Tbl;
55 l_old_sch_fn_date_tbl t_schDate_Tbl;
56 l_sch_start_date_tbl t_schDate_Tbl;
57 l_sch_finish_date_tbl t_schDate_Tbl;
58 l_effort_tbl t_proj_id_table;
59 l_elem_ver_id_tbl t_proj_id_table;
60 --
61 l_res_asgmt_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
62 l_planning_start_tbl SYSTEM.pa_date_tbl_type;
63 l_planning_end_tbl SYSTEM.pa_date_tbl_type;
64 --
65 TYPE DYNAMIC_CUR IS REF CURSOR;
66 l_cur DYNAMIC_CUR;
67 l_sql VARCHAR2(32767);
68 l_sql1 VARCHAR2(32767);
69 l_predicate VARCHAR2(32767);
70 --
71 l_cnt NUMBER :=0; -- for 5660584, pqe base bug5638103
72 --
73 l_project_id NUMBER;
74 l_element_version_id NUMBER;
75 l_object_type VARCHAR2(30);
76 l_wbs_level NUMBER;
77 l_start_date DATE;
78 l_finish_date DATE;
79 l_parent_id NUMBER;
80 l_parent_object_type VARCHAR2(30);
81 --
82 l_structure_version_id NUMBER;
83 l_baseline_proj_id NUMBER;
84 l_versioning VARCHAR2(1);
85 l_planned_effort PA_PROJ_ELEM_VER_SCHEDULE.PLANNED_EFFORT%TYPE;
86 --
87 -- Dates changes
88 l_template_flag VARCHAR2(1);
89 l_record_version_number NUMBER;
90 --
91 cursor get_proj_attr_csr(c_project_id NUMBER)
92 IS
93 SELECT template_flag, record_version_number
94 FROM pa_projects_all
95 WHERE project_id = c_project_id;
96 --
97 l_structure_id NUMBER;
98 l_struc_project_id NUMBER;
99 --
100 --status control is not used anymore
101 CURSOR get_system_status_code(c_element_version_id NUMBER)
102 IS
103 SELECT decode(project_system_status_code, 'CANCELLED', 'N', 'Y')
104 FROM pa_proj_elements a, pa_proj_element_versions b,
105 pa_project_statuses c
106 where a.proj_element_id = b.proj_element_id
107 and a.project_id = b.project_id
108 and b.element_version_id = c_element_version_id
109 and a.status_code = c.project_status_code
110 and c.status_type = 'TASK';
111 --
112 l_rollup_flag VARCHAR2(1);
113 --
114 --Getting the linking tasks
115 CURSOR get_lnk_task(cp_task_version_id NUMBER) IS
116 SELECT ppe.proj_element_id,
117 ppev.element_version_id lnk_task_ver_id,
118 ppev.project_id lnk_task_project_id
119 FROM pa_proj_element_versions ppev,
120 pa_proj_elements ppe,
121 pa_proj_elem_ver_schedule pevs,
122 --bug 4541039
123 pa_object_relationships por,
124 pa_proj_elem_ver_structure pevst
125 --bug 4541039
126 WHERE ppe.project_id = ppev.project_id
127 AND ppe.proj_element_id = ppev.proj_element_id
128 AND ppev.object_type = 'PA_TASKS'
129 AND ppe.object_type = 'PA_TASKS'
130 AND ppe.link_Task_flag = 'Y'
131 AND ppev.project_id = pevs.project_id
132 AND ppev.proj_element_id = pevs.proj_element_id
133 AND ppev.element_version_id = pevs.element_version_id
134 --bug 4541039 rollup only from the published versions
135 and por.object_id_from1 = ppev.element_version_id
136 and pevst.element_version_id = por.object_id_to1
137 and pevst.status_code = 'STRUCTURE_PUBLISHED'
138 and pevst.project_id = por.object_id_to2
139 --bug 4541039
140 AND ppev.element_version_id IN (
141 SELECT object_id_to1
142 FROM pa_object_relationships
143 WHERE relationship_type = 'S'
144 START WITH object_id_from1 = cp_task_version_id
145 AND object_type_from IN ('PA_TASKS','PA_STRUCTURES')
146 AND relationship_type = 'S'
147 CONNECT BY object_id_from1 = prior object_id_to1
148 AND RELATIONSHIP_TYPE = prior relationship_type
149 AND object_type_from = prior object_type_to);
150 get_lnk_task_rec get_lnk_task%ROWTYPE;
151 --
152 --Getting linking relationship details.
153 CURSOR get_lnk_task_rel_det(cp_lnk_task_version_id NUMBER) IS
154 Select object_id_from1 lnk_task_ver_id,object_id_to1 struct_version_id,
155 object_id_from2 lnk_proj_id_from,object_id_to2 lnk_proj_id_to --Bug 3634389
156 FROM pa_object_relationships
157 WHERE object_id_from1 = cp_lnk_task_version_id
158 AND relationship_type = 'LW'
159 AND object_id_from2 <> object_id_to2
160 AND object_type_from = 'PA_TASKS' --Bug 3634389
161 AND object_type_to = 'PA_STRUCTURES'; --Bug 3634389
162 -- AND object_type_to = 'PA_TASKS' --Bug 3634389
163 -- AND object_type_from = 'PA_STRUCTURES'; --Bug 3634389
164 get_lnk_task_rel_det_rec get_lnk_task_rel_det%ROWTYPE;
165 --
166 CURSOR get_lnk_task_start_dt(cp_lnk_task_version_id NUMBER,
167 cp_lnk_proj_id_from NUMBER,
168 cp_lnk_proj_id_to NUMBER) IS
169 SELECT min(scheduled_start_date) lnk_task_sch_start_Dt
170 FROM (SELECT min(b.scheduled_start_date) scheduled_start_date --bug 3967855
171 FROM pa_proj_element_versions a,
172 pa_proj_elem_ver_schedule b
173 WHERE a.project_id = b.project_id
174 AND a.element_version_id = b.element_version_id
175 AND b.project_id = cp_lnk_proj_id_from --Bug 3634389 Added for performance
176 AND b.element_version_id IN (SELECT object_id_from1
177 FROM pa_object_relationships pora
178 WHERE object_id_from1 = cp_lnk_task_version_id
179 AND relationship_type = 'S'
180 AND object_id_from2 <> object_id_to2
181 AND object_type_from IN ('PA_STRUCTURES','PA_TASKS') --Bug 3634389
182 AND object_type_to = 'PA_TASKS')
183 -- AND object_type_from = 'PA_STRUCTURES') --Bug 3634389
184 UNION ALL
185 SELECT min(d.scheduled_start_date) scheduled_start_date --bug 3967855
186 FROM pa_proj_element_versions c,
187 pa_proj_elem_ver_schedule d
188 ,pa_proj_elem_ver_structure e --bug 4541039
189 WHERE c.project_id = d.project_id
190 AND d.project_id =e.project_id --Bug#6277752 Added for performance
191 AND d.element_version_id = e.element_version_id --Bug#6277752 Added for performance
192 AND d.project_id = cp_lnk_proj_id_to --Bug 3634389 Added for performance
193 --make sure that the rollup is happeningonly from published version of the sub-project bug 4541039
194 AND e.element_version_id = c.element_version_id
195 AND e.project_id = c.project_id -- Bug # 4868867.
196 AND e.status_code = 'STRUCTURE_PUBLISHED'
197 --end bug 4541039
198 AND c.element_version_id IN (SELECT object_id_to1
199 FROM pa_object_relationships
200 WHERE object_id_from1 = cp_lnk_task_version_id
201 AND relationship_type = 'LW'
202 AND object_id_from2 <> object_id_to2
203 AND object_type_from = 'PA_TASKS' --Bug 3634389
204 AND object_type_to = 'PA_STRUCTURES')); --Bug 3634389
205 -- AND object_type_to = 'PA_TASKS' --Bug 3634389
206 -- AND object_type_from = 'PA_STRUCTURES')); --Bug 3634389
207 get_lnk_task_start_dt_rec get_lnk_task_start_dt%ROWTYPE;
208 --
209 CURSOR get_lnk_task_finish_dt(cp_lnk_task_version_id NUMBER,
210 cp_lnk_proj_id_from NUMBER,
211 cp_lnk_proj_id_to NUMBER) IS
212 SELECT max(scheduled_finish_date) lnk_task_sch_finish_Dt
213 FROM (SELECT max(b.scheduled_finish_date) scheduled_finish_date --bug 3967855
214 FROM pa_proj_element_versions a,
215 pa_proj_elem_ver_schedule b
216 WHERE a.project_id = b.project_id
217 AND a.element_version_id = b.element_version_id
218 AND b.project_id = cp_lnk_proj_id_from --Bug 3634389 Added for performance
219 AND b.element_version_id IN (SELECT object_id_from1
220 FROM pa_object_relationships pora
221 WHERE object_id_from1 = cp_lnk_task_version_id
222 AND relationship_type = 'S'
223 AND object_id_from2 <> object_id_to2
224 AND object_type_from IN ('PA_STRUCTURES','PA_TASKS') --Bug 3634389
225 AND object_type_to = 'PA_TASKS')
226 -- AND object_type_from = 'PA_STRUCTURES') --Bug 3634389
227 UNION ALL
228 SELECT max(d.scheduled_finish_date) scheduled_finish_date --bug 3967855
229 FROM pa_proj_element_versions c,
230 pa_proj_elem_ver_schedule d
231 ,pa_proj_elem_ver_structure e --bug 4541039
232 WHERE c.project_id = d.project_id
233 AND d.project_id =e.project_id --Bug#6277752 Added for performance
234 AND d.element_version_id = e.element_version_id --Bug#6277752 Added for performance
235 AND d.project_id = cp_lnk_proj_id_to --Bug 3634389 Added for performance
236 --make sure that the rollup is happeningonly from published version of the sub-project bug 4541039
237 AND e.element_version_id = c.element_version_id
238 and e.project_id = c.project_id -- Bug # 4868867.
239 AND e.status_code = 'STRUCTURE_PUBLISHED'
240 --end bug 4541039
241 AND c.element_version_id IN (SELECT object_id_to1
242 FROM pa_object_relationships
243 WHERE object_id_from1 = cp_lnk_task_version_id
244 AND relationship_type = 'LW'
245 AND object_id_from2 <> object_id_to2
246 AND object_type_from = 'PA_TASKS' --Bug 3634389
247 AND object_type_to = 'PA_STRUCTURES')); --Bug 3634389
248 -- AND object_type_to = 'PA_TASKS' --Bug 3634389
249 -- AND object_type_from = 'PA_STRUCTURES')); --Bug 3634389
250 get_lnk_task_finish_dt_rec get_lnk_task_finish_dt%ROWTYPE;
251
252 --bug 4238036
253 Cursor get_sch_dates(c_element_version_id NUMBER) IS
254 SELECT a.scheduled_start_date, a.scheduled_finish_date
255 from pa_proj_elem_ver_schedule a
256 where a.element_version_id = c_element_version_id;
257 l_parent_start_date DATE;
258 l_parent_finish_date DATE;
259 --bug 4238036
260
261 l_lnk_task_project_id NUMBER;
262 l_assgn_context VARCHAR2(30); --bug 4153366
263
264
265 --bug 4416432 issue # 5 and 6
266 CURSOR get_scheduled_dates(c_project_Id NUMBER, c_element_version_id NUMBER)
267 IS
268 select scheduled_start_date, scheduled_finish_date
269 from pa_proj_elem_ver_schedule
270 where project_id = c_project_id
271 and element_version_id = c_element_version_id;
272 l_get_sch_dates_cur get_scheduled_dates%ROWTYPE;
273
274 cursor get_structure_id(c_structure_ver_id NUMBER)
275 IS
276 SELECT project_id, proj_element_id
277 FROM pa_proj_element_versions
278 where element_version_id = c_structure_ver_id;
279 --end bug 4416432 issue # 5 and 6
280
281 l_debug_mode VARCHAR2(1);
282
283 -- 5660584, pqe base bug 5638103
284 l_predicate1 VARCHAR2(32767);
285 l_predicate_cnt NUMBER;
286 l_tot_predicate_cnt NUMBER;
287 dont_close_cursor VARCHAR2(1):='N';
288 processing_completed VARCHAR2(1):='N';
289 i number;
290
291
292 BEGIN
293 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
294
295 IF (l_debug_mode = 'Y') THEN
296 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Entered...', 3);
297 END IF;
298
299 IF (p_commit = FND_API.G_TRUE) THEN
300 savepoint Rollup_From_Subproject_PVT;
301 END IF;
302
303 IF (l_debug_mode = 'Y') THEN
304 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Performing validations...', 3);
305 END IF;
306
307 IF (p_element_versions.count = 0) THEN
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309 return;
310 END IF;
311
312 i:=1;
313 loop -- loop1
314
315 -- 5660584, pqe base bug 5638103
316 l_predicate := null;
317 l_tot_predicate_cnt:=0;
318
319 --Loop thru the element version table and get the linking tasks for each element version if
320 --any. For each linking task get the start date, finish date and then update the schedules
321 --table for that linking task with the start date and finish date.
322
323 -- FOR i IN p_element_versions.first..p_element_versions.last LOOP
324 loop -- loop2
325 l_predicate1 := null;
326 l_predicate_cnt:=0;
327
328 IF NOT get_lnk_task%ISOPEN THEN -- don't open the cursor if still there are some more records in it.
329 IF i > p_element_versions.last THEN
330 processing_completed:='Y';
331 exit; --from loop2 we are done with all the element version ids
332 END IF;
333 OPEN get_lnk_task(p_element_versions(i));
334 i:=i+1;
335 END IF;
336
337 IF (l_debug_mode = 'Y') THEN
338 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Inside Loop p_element_versions(i)='||p_element_versions(i-1), 3);
339 END IF;
340 LOOP -- bug 5638103 loop3
341
342 FETCH get_lnk_task INTO get_lnk_task_rec;
343 --bug 4541039
344 IF get_lnk_task%FOUND
345 THEN
346 IF l_predicate1 IS NOT NULL
347 THEN
348 l_predicate1 := l_predicate1 ||',';
349 END IF;
350 l_predicate1 := l_predicate1||to_char(get_lnk_task_rec.lnk_task_ver_id);
351 l_predicate_cnt:=l_predicate_cnt+1;
352 ELSE
353 exit; -- exit from loop3 no records exists
354 END IF;
355 --l_predicate := l_predicate||to_char(get_lnk_task_rec.lnk_task_ver_id);
356 --EXIT WHEN get_lnk_task%NOTFOUND;
357 --l_predicate := l_predicate||',';
358 --bug 4541039
359 l_lnk_task_project_id := get_lnk_task_rec.lnk_task_project_id;
360 --
361 --Getting linking task relationship details(target str version id
362 OPEN get_lnk_task_rel_det(get_lnk_task_rec.lnk_task_ver_id);
363 FETCH get_lnk_task_rel_det INTO get_lnk_task_rel_det_rec;
364 CLOSE get_lnk_task_rel_det;
365 --
366 --Getting the start date for the linking task
367 OPEN get_lnk_task_start_dt(get_lnk_task_rec.lnk_task_ver_id,
368 get_lnk_task_rel_det_rec.lnk_proj_id_from, --Bug 3634389
369 get_lnk_task_rel_det_rec.lnk_proj_id_to); --Bug 3634389
370
371 FETCH get_lnk_task_start_dt INTO get_lnk_task_start_dt_rec;
372 IF get_lnk_task_start_dt%NOTFOUND THEN
373 RAISE FND_API.G_EXC_ERROR;
374 END IF;
375 CLOSE get_lnk_task_start_dt;
376 --
377 --Getting the finish date for the linking task
378 OPEN get_lnk_task_finish_dt(get_lnk_task_rec.lnk_task_ver_id,
379 get_lnk_task_rel_det_rec.lnk_proj_id_from, --Bug 3634389
380 get_lnk_task_rel_det_rec.lnk_proj_id_to); --Bug 3634389
381 FETCH get_lnk_task_finish_dt INTO get_lnk_task_finish_dt_rec;
382 IF get_lnk_task_finish_dt%NOTFOUND THEN
383 RAISE FND_API.G_EXC_ERROR;
384 END IF;
385 CLOSE get_lnk_task_finish_dt;
386 --
387 --bug 4238036
388 OPEN get_sch_dates(p_element_versions(i-1)); -- 5660584, pqe base bug 5638103
389 FETCH get_sch_dates INTO l_parent_start_date, l_parent_finish_date;
390 CLOSE get_sch_dates;
391
392 -- IF (l_parent_start_date > get_lnk_task_start_dt_rec.lnk_task_sch_start_Dt ) THEN
393 -- Fix for Bug # 4385027.
394
395 l_parent_start_date := get_lnk_task_start_dt_rec.lnk_task_sch_start_Dt;
396
397 -- END IF;
398 -- Fix for Bug # 4385027.
399
400 -- IF (l_parent_finish_date < get_lnk_task_finish_dt_rec.lnk_task_sch_finish_Dt) THEN
401 -- Fix for Bug # 4385027.
402
403 l_parent_finish_date := get_lnk_task_finish_dt_rec.lnk_task_sch_finish_Dt;
404
405 -- END IF;
406 -- Fix for Bug # 4385027.
407
408 --end bug 4238036
409 IF (l_debug_mode = 'Y') THEN
410 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Updating task ver:'||get_lnk_task_rec.lnk_task_ver_id, 3);
411 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Sch start date='||l_parent_start_date, 3);
412 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Sch finish date='||l_parent_finish_date, 3);
413 END IF;
414
415 UPDATE pa_proj_elem_ver_schedule
416 SET scheduled_start_date = l_parent_start_date
417 ,scheduled_finish_date = l_parent_finish_date
418 ,record_version_number = NVL( record_version_number, 0 ) + 1
419 WHERE element_version_id = get_lnk_task_rec.lnk_task_ver_id
420 AND project_id = l_lnk_task_project_id --Bug 3634389
421 ;
422 IF SQL%NOTFOUND THEN
423 RAISE FND_API.G_EXC_ERROR;
424 END IF;
425
426 if l_predicate_cnt+l_tot_predicate_cnt = 1000 then
427 dont_close_cursor:='Y';
428 exit; -- exit from loop3 still there exists some records
429 end if;
430 --
431 END LOOP; --end loop3
432
433 if dont_close_cursor <> 'Y' then
434 CLOSE get_lnk_task;
435 else
436 dont_close_cursor:='N';
437 end if;
438 l_tot_predicate_cnt := l_predicate_cnt+l_tot_predicate_cnt;
439
440 IF l_predicate1 IS NOT NULL THEN
441 IF l_predicate IS NOT NULL
442 THEN
443 l_predicate := l_predicate||',';
444 END IF;
445 l_predicate := l_predicate||l_predicate1;
446 END IF;
447
448 IF l_tot_predicate_cnt = 1000 THEN
449 exit; -- exit from loop2
450 END IF;
451
452 END LOOP; -- end loop2
453
454 --kchaitan by the time we come out of loop2
455 -- we will have predicate with not more than 1000
456 --params
457 --
458 IF (l_debug_mode = 'Y') THEN
459 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Predicate = '||l_predicate, 3);
460 END IF;
461 --
462 -- 5660584, pqe base bug 5638103
463 /* bug 5638103
464 --bug: 3696446
465 IF (length(l_predicate) IS NULL) THEN
466 x_return_status := FND_API.G_RET_STS_SUCCESS;
467 return;
468 END IF;
469 --end bug 3696446
470 */
471
472 -- 5660584, pqe base bug 5638103
473 -- below part is applicable only if l_predicate is not null
474
475 IF l_predicate is not null THEN
476
477 --first select stmt - get all parents along the branch of the element
478 --second select stmt - get direct child of all parents
479 --third select stmt - get input element
480 if l_sql1 is not null then
481 l_sql1 := l_sql1||' or ';
482 end if;
483
484 l_sql1 :='object_id_to1 IN ('||
485 l_predicate||
486 ') ';
487 END IF; -- IF l_predicate is not null THEN
488 IF processing_completed = 'Y' THEN
489 exit; -- exit from loop1
490 END IF;
491 end loop; -- by kchaitan end loop1
492 IF l_sql1 is not null THEN
493 l_sql :=
494 ' SELECT a.project_id, a.element_version_id,'||
495 ' a.object_type, b.PLANNED_EFFORT, '||
496 ' NVL(a.wbs_level,0), b.scheduled_start_date+NVL(b.scheduled_start_date_rollup,0), '||
497 ' b.scheduled_finish_date+NVL(b.scheduled_finish_date_rollup,0), c.object_id_from1, '||
498 ' c.object_type_from FROM '||
499 ' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
500 ' pa_object_relationships c, pa_proj_elements d WHERE '||
501 ' a.element_version_id = c.object_id_to1(+) AND '||
502 ' c.relationship_type(+)= '||''''||'S'||''''||' AND '||
503 ' a.project_id = b.project_id AND '||
504 ' a.element_version_id = b.element_version_id AND '||
505 ' a.proj_element_id = d.proj_element_id AND '||
506 -- ' d.link_task_flag = '||''''||'N'||''''||' and '||
507 ' a.element_version_id IN ('||
508 ' SELECT object_id_from1 FROM '||
509 ' pa_object_relationships CONNECT BY '||
510 ' PRIOR object_id_from1 = object_id_to1 '||
511 ' AND RELATIONSHIP_TYPE = prior relationship_type '||
512 ' AND relationship_type = '||''''||'S'||''''||
513 ' START WITH ('||
514 l_sql1||
515 ') ' ||
516 ' and relationship_type = '||''''||'S'||''''||
517 ') UNION '||
518 ' SELECT distinct a.project_id, a.element_version_id, '||
519 ' a.object_type, b.PLANNED_EFFORT, '||
520 ' nvl(a.wbs_level,0), b.scheduled_start_date+NVL(b.scheduled_start_date_rollup,0), '||
521 ' b.scheduled_finish_date+NVL(b.scheduled_finish_date_rollup,0), c.object_id_from1, '||
522 ' c.object_type_from FROM '||
523 ' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
524 ' pa_object_relationships c, pa_proj_elements d WHERE '||
525 ' a.element_version_id = c.object_id_to1 AND '||
526 ' c.relationship_type = '||''''||'S'||''''||' AND '||
527 ' a.project_id = b.project_id AND '||
528 ' a.element_version_id = b.element_version_id AND '||
529 ' a.proj_element_id = d.proj_element_id AND '|| -- 3305199
530 -- ' a.element_version_id = d.proj_element_id AND '||
531 --' d.link_task_flag = '||''''||'N'||''''||' AND '||
532 ' c.object_id_from1 IN ('||
533 ' select object_id_from1 FROM '||
534 ' pa_object_relationships CONNECT BY '||
535 ' PRIOR object_id_from1 = object_id_to1 '||
536 ' AND RELATIONSHIP_TYPE = prior relationship_type '||
537 ' AND relationship_type = '||''''||'S'||''''||
538 ' START WITH ('||
539 l_sql1||
540 ')' ||
541 ' AND relationship_type = '||''''||'S'||''''||
542 ')';
543 --
544 --
545 --dbms_output.put_line('after');
546 --
547 -- l_cnt := 0; -- 5660584, pqe base bug 5638103
548 OPEN l_cur FOR l_sql;
549 LOOP
550
551 FETCH l_cur INTO l_project_id,
552 l_element_version_id,
553 l_object_type,
554 l_planned_effort,
555 l_wbs_level,
556 l_start_date,
557 l_finish_date,
558 l_parent_id,
559 l_parent_object_type;
560 EXIT WHEN l_cur%NOTFOUND;
561 l_cnt := l_cnt + 1; -- 5660584, pqe base bug 5638103
562 --
563 l_proj_id_tbl(l_cnt) := l_project_id;
564 l_rollup_table(l_cnt).object_id := l_element_version_id;
565 l_rollup_table(l_cnt).object_type := l_object_type;
566 l_rollup_table(l_cnt).REMAINING_EFFORT1 := l_planned_effort;
567 l_rollup_table(l_cnt).wbs_level := l_wbs_level;
568 l_rollup_table(l_cnt).start_date1 := l_start_date;
569 l_rollup_table(l_cnt).finish_date1 := l_finish_date;
570 l_rollup_table(l_cnt).parent_object_id := l_parent_id;
571 l_rollup_table(l_cnt).parent_object_type := l_parent_object_type;
572 l_rollup_table(l_cnt).dirty_flag1 := 'N';
573 --
574 OPEN get_system_status_code(l_element_version_id);
575 FETCH get_system_status_code into l_rollup_flag;
576 CLOSE get_system_status_code;
577 --
578 l_rollup_table(l_cnt).ROLLUP_NODE1 := l_rollup_flag;
579 --
580 --dbms_output.put_line('....count = '||l_cnt||'....');
581 --dbms_output.put_line('pid ='||l_proj_id_tbl(l_cnt));
582 --dbms_output.put_line('elem ='||l_rollup_table(l_cnt).object_id||' , parent='||l_rollup_table(l_cnt).parent_object_id);
583 --dbms_output.put_line('sd ='||l_rollup_table(l_cnt).start_date1||', fd ='||l_rollup_table(l_cnt).finish_date1);
584 --dbms_output.put_line('id = '||l_element_version_id);
585 --
586 FOR j IN p_element_versions.FIRST..p_element_versions.LAST LOOP
587 IF l_element_version_id = p_element_versions(j) THEN
588 l_rollup_table(l_cnt).dirty_flag1 := 'Y';
589 END IF;
590 END LOOP;
591 --
592 --get the structure version id
593 IF (l_rollup_table(l_cnt).object_type = 'PA_STRUCTURES') THEN
594 --save structure id
595 l_structure_version_id := l_rollup_table(l_cnt).object_id;
596 l_baseline_proj_id := l_proj_id_tbl(l_cnt);
597 --
598 --get template flag; will need to change when incorporating linking
599 OPEN get_proj_attr_csr(l_baseline_proj_id);
600 FETCH get_proj_attr_csr INTO l_template_flag, l_record_version_number;
601 CLOSE get_proj_attr_csr;
602 END IF;
603 --
604 END LOOP; --end loop for dynamic cursor
605 --dbms_output.put_line('fetched = '||l_cur%ROWCOUNT);
606 --
607 CLOSE l_cur;
608 END IF; -- IF l_sql1 is not null THEN
609 --Moved for the final fix
610 /*
611 END IF; -- IF l_predicate is not null THEN
612 IF processing_completed = 'Y' THEN
613 exit; -- exit from loop1
614 END IF;
615 end loop; -- by kchaitan end loop1
616 */
617 --Moved for final fix
618 IF (l_rollup_table.count = 0) THEN
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620 return;
621 END IF;
622 -- end changes for bug 5638103
623
624 PA_SCHEDULE_OBJECTS_PVT.GENERATE_SCHEDULE(
625 p_debug_mode => 'N',
626 p_data_structure => l_rollup_table,
627 x_return_status => x_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data,
630 x_process_number => l_process_number,
631 p_process_flag1 => 'Y',
632 p_partial_process_flag1 => 'N', --bug 4020077
633 p_partial_dates_flag1 => 'Y',
634 p_partial_effort_flag1 => 'Y',
635 p_process_rollup_flag1 => 'Y',
636 p_process_effort_flag1 => 'Y');
637 --
638 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
639 RAISE FND_API.G_EXC_ERROR;
640 ELSE
641 IF ((PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_lnk_task_project_id) ='N') AND
642 (PA_RELATIONSHIP_UTILS.IS_AUTO_ROLLUP(l_lnk_task_project_id) = 'Y')) OR
643 ((PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_lnk_task_project_id) ='Y') AND
644 (PA_RELATIONSHIP_UTILS.IS_AUTO_ROLLUP(l_lnk_task_project_id) = 'Y') AND
645 ((PA_PROJECT_STRUCTURE_UTILS.get_structrue_version_status(l_lnk_task_project_id, l_structure_version_id) <> 'STRUCTURE_PUBLISHED')
646 OR ( p_published_str_ver_id = l_structure_version_id)) --bug5861729
647 )THEN
648 FOR i IN l_rollup_table.FIRST..l_rollup_table.LAST LOOP
649 l_sch_start_date_tbl(i) := l_rollup_table(i).start_date1;
650 l_sch_finish_date_tbl(i) := l_rollup_table(i).finish_date1;
651 l_effort_tbl(i) := l_rollup_table(i).remaining_effort1;
652 l_elem_ver_id_tbl(i) := l_rollup_table(i).object_id;
653 END LOOP;
654 --
655 FOR i IN l_rollup_table.FIRST..l_rollup_table.LAST LOOP
656 SELECT scheduled_start_date, scheduled_finish_date
657 into l_old_sch_st_date_tbl(i), l_old_sch_fn_date_tbl(i)
658 FROM pa_proj_elem_ver_schedule
659 WHERE project_id = l_proj_id_tbl(i)
660 AND element_version_id = l_elem_ver_id_tbl(i);
661 END LOOP;
662 --
663 FORALL i IN l_rollup_table.FIRST..l_rollup_table.LAST
664 UPDATE pa_proj_elem_ver_schedule
665 SET scheduled_start_date = l_sch_start_date_tbl(i),
666 scheduled_finish_date = l_sch_finish_date_tbl(i),
667 planned_effort = l_effort_tbl(i),
668 duration = l_sch_finish_date_tbl(i) - l_sch_start_date_tbl(i) + 1,
669 last_update_date = sysdate,
670 last_updated_by = FND_GLOBAL.USER_ID,
671 last_update_login = FND_GLOBAL.LOGIN_ID
672 WHERE project_id = l_proj_id_tbl(i)
673 AND element_version_id = l_elem_ver_id_tbl(i);
674 --
675 -- Upon changes on a Task's Scheduled Dates.
676 FOR i IN l_rollup_table.first..l_rollup_table.last LOOP
677
678 --bug 4153366
679 IF i = l_rollup_table.last
680 THEN
681 l_assgn_context := 'UPDATE';
682 ELSE
683 l_assgn_context := 'INSERT_VALUES';
684 END IF;
685 --bug 4153366
686
687 IF (l_debug_mode = 'Y') THEN
688 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Before calling PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates', 3);
689 END IF;
690
691 PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates(
692 p_element_version_id => l_elem_ver_id_tbl(i),
693 p_old_task_sch_start => l_old_sch_st_date_tbl(i),
694 p_old_task_sch_finish => l_old_sch_fn_date_tbl(i),
695 p_new_task_sch_start => l_sch_start_date_tbl(i),
696 p_new_task_sch_finish => l_sch_finish_date_tbl(i),
697 p_context => l_assgn_context, --4153366
698 x_res_assignment_id_tbl => l_res_asgmt_id_tbl,
699 x_planning_start_tbl => l_planning_start_tbl,
700 x_planning_end_tbl => l_planning_end_tbl,
701 x_return_status => x_return_status);
702
703 IF (l_debug_mode = 'Y') THEN
704 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'After calling PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates x_return_status='||x_return_status, 3);
705 END IF;
706
707 IF x_return_status = FND_API.G_RET_STS_ERROR then
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710 END LOOP;
711 ELSE
712 FOR i IN l_rollup_table.FIRST..l_rollup_table.LAST LOOP
713 l_sch_start_date_tbl(i) := l_rollup_table(i).start_date1;
714 l_sch_finish_date_tbl(i) := l_rollup_table(i).finish_date1;
715 l_effort_tbl(i) := l_rollup_table(i).remaining_effort1;
716 l_elem_ver_id_tbl(i) := l_rollup_table(i).object_id;
717 END LOOP;
718 --
719 FOR i IN l_rollup_table.FIRST..l_rollup_table.LAST LOOP
720 SELECT scheduled_start_date into l_old_sch_st_date_tbl(i)
721 FROM pa_proj_elem_ver_schedule
722 WHERE project_id = l_proj_id_tbl(i)
723 AND element_version_id = l_elem_ver_id_tbl(i);
724 END LOOP;
725 --
726 FORALL i IN l_rollup_table.FIRST..l_rollup_table.LAST
727 UPDATE pa_proj_elem_ver_schedule
728 SET scheduled_start_date_rollup = l_sch_start_date_tbl(i) - scheduled_start_date,
729 scheduled_finish_date_rollup = l_sch_finish_date_tbl(i) - scheduled_finish_date,
730 last_update_date = sysdate,
731 last_updated_by = FND_GLOBAL.USER_ID,
732 last_update_login = FND_GLOBAL.LOGIN_ID
733 WHERE project_id = l_proj_id_tbl(i)
734 AND element_version_id = l_elem_ver_id_tbl(i);
735 --
736 END IF; --end if for checking versions enabled and checking auto_roolup
737
738 --bug 4416432 issue # 5 and 6
739 --call baseline dates, update project dates and auto-copy txn dates apis
740 --for the parent project.
741 FOR i IN l_rollup_table.FIRST..l_rollup_table.LAST LOOP
742 IF l_rollup_table(i).object_type = 'PA_STRUCTURES'
743 THEN
744 l_versioning := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_proj_id_tbl(i));
745 IF (l_versioning = 'N') THEN
746 OPEN get_scheduled_dates(l_proj_id_tbl(i), l_rollup_table(i).object_id);
747 FETCH get_scheduled_dates into l_get_sch_dates_cur;
748 CLOSE get_scheduled_dates;
749
750 IF (l_debug_mode = 'Y') THEN
751 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Before calling PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES', 3);
752 END IF;
753
754 PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
755 p_validate_only => FND_API.G_FALSE
756 ,p_project_id => l_proj_id_tbl(i)
757 ,p_date_type => 'SCHEDULED'
758 ,p_start_date => l_get_sch_dates_cur.scheduled_start_date
759 ,p_finish_date => l_get_sch_dates_cur.scheduled_finish_date
760 ,p_record_version_number => l_record_version_number
761 ,x_return_status => x_return_status
762 ,x_msg_count => x_msg_count
763 ,x_msg_data => x_msg_data );
764
765 IF (l_debug_mode = 'Y') THEN
766 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'After calling PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES x_return_status='||x_return_status, 3);
767 END IF;
768
769 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
770 RAISE FND_API.G_EXC_ERROR;
771 END IF;
772 END IF;
773
774 IF (l_versioning = 'N') THEN
775 --baseline
776 IF (l_debug_mode = 'Y') THEN
777 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Before calling PA_PROJECT_STRUCTURE_PVT1.BASELINE_STRUCTURE_VERSION', 3);
778 END IF;
779
780 PA_PROJECT_STRUCTURE_PVT1.BASELINE_STRUCTURE_VERSION(
781 p_commit => FND_API.G_FALSE,
782 p_structure_version_id => l_rollup_table(i).object_id,
783 x_return_status => x_return_status,
784 x_msg_count => x_msg_count,
785 x_msg_data => x_msg_data);
786
787 IF (l_debug_mode = 'Y') THEN
788 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'After calling PA_PROJECT_STRUCTURE_PVT1.BASELINE_STRUCTURE_VERSION x_return_status='||x_return_status, 3);
789 END IF;
790
791
792 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
793 x_msg_count := FND_MSG_PUB.count_msg;
794 if x_msg_count = 1 then
795 x_msg_data := x_msg_data;
796 end if;
797 raise FND_API.G_EXC_ERROR;
798 end if;
799 END IF;
800 --end baseline changes
801
802 --auto sync changes
803 OPEN get_structure_id(l_rollup_table(i).object_id);
804 FETCH get_structure_id into l_struc_project_id, l_structure_id;
805 CLOSE get_structure_id;
806
807 --auto sync changes
808 IF ((PA_WORKPLAN_ATTR_UTILS.CHECK_AUTO_DATE_SYNC_ENABLED(l_structure_id) = 'Y') AND
809 (PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(l_struc_project_id) = 'SHARE_FULL')) AND
810 (l_versioning = 'N') THEN
811 --copy to transaction dates
812 IF (l_debug_mode = 'Y') THEN
813 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Before calling PA_PROJECT_DATES_PUB.COPY_PROJECT_DATES', 3);
814 END IF;
815
816 PA_PROJECT_DATES_PUB.COPY_PROJECT_DATES(
817 p_validate_only => FND_API.G_FALSE
818 ,p_project_id => l_struc_project_id
819 ,x_return_status => x_return_status
820 ,x_msg_count => x_msg_count
821 ,x_msg_data => x_msg_data
822 );
823
824 IF (l_debug_mode = 'Y') THEN
825 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'After calling PA_PROJECT_DATES_PUB.COPY_PROJECT_DATESx_return_status='||x_return_status, 3);
826 END IF;
827
828 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
829 x_msg_count := FND_MSG_PUB.count_msg;
830 if x_msg_count = 1 then
831 x_msg_data := x_msg_data;
832 end if;
833 raise FND_API.G_EXC_ERROR;
834 end if;
835 END IF;
836 END IF; --<<l_rollup_table(i).object_type = 'PA_STRUCTURES'>>
837 END LOOP;
838 --end bug 4416432 issue # 5 and 6
839
840 END IF; --end if for return status after calling pa_schedule_objects_pvt.generate_schedule
841 --
842 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
843 --
844 EXCEPTION
845 WHEN FND_API.G_EXC_ERROR THEN
846 IF (p_commit = FND_API.G_TRUE) THEN
847 ROLLBACK to Rollup_From_Subproject_PVT;
848 END IF;
849 x_msg_count := FND_MSG_PUB.count_msg;
850 x_return_status := FND_API.G_RET_STS_ERROR;
851 WHEN OTHERS THEN
852 IF (p_commit = FND_API.G_TRUE) THEN
853 ROLLBACK to Rollup_From_Subproject_PVT;
854 END IF;
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
856 x_msg_count := FND_MSG_PUB.count_msg;
857 --put message
858 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STRUCT_TASK_ROLLUP_PUB',
859 p_procedure_name => 'Rollup_From_Subproject_Unltd',
860 p_error_text => SUBSTRB(SQLERRM,1,240));
861 RAISE;
862 END Rollup_From_Subproject_Unltd;
863
864 /* Bug 6854670
865 * This is a wrapper procedure which simply converts the parameter from type PA_NUM_1000_NUM
866 * to SYSTEM.PA_NUM_TBL_TYPE and delegates the processing to Rollup_From_Subproject_Unltd
867 * This procedure should be there to maintain compatibility with calls from online flow
868 */
869 PROCEDURE Rollup_From_Subproject(
870 p_api_version IN NUMBER := 1.0
871 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
872 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
873 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
874 ,p_validation_level IN VARCHAR2 := 100
875 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
876 ,p_debug_mode IN VARCHAR2 := 'N'
877 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
878 ,p_element_versions IN PA_NUM_1000_NUM
879 ,p_published_str_ver_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM --bug5861729
880 ,x_return_status OUT NOCOPY VARCHAR2
881 ,x_msg_count OUT NOCOPY NUMBER
882 ,x_msg_data OUT NOCOPY VARCHAR2
883 )
884 IS
885
886 l_element_versions SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
887 l_debug_mode VARCHAR2(1);
888
889 BEGIN
890
891 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
892
893 IF (l_debug_mode = 'Y') THEN
894 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.ROLLUP_FROM_SUBPROJECT', 'BEGIN', 3);
895 END IF;
896
897 IF (p_element_versions.count = 0) THEN
898 x_return_status := FND_API.G_RET_STS_SUCCESS;
899
900 IF (l_debug_mode = 'Y') THEN
901 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.ROLLUP_FROM_SUBPROJECT', 'p_element_versions.COUNT is 0', 3);
902 END IF;
903
904 return;
905 END IF;
906
907 FOR i IN p_element_versions.FIRST .. p_element_versions.LAST LOOP
908 l_element_versions.extend;
909 l_element_versions(i) := p_element_versions(i);
910 END LOOP;
911
912 -- Call Rollup_From_Subproject_Unltd
913 Rollup_From_Subproject_Unltd(p_api_version => p_api_version,
914 p_init_msg_list => p_init_msg_list,
915 p_commit => p_commit,
916 p_validate_only => p_validate_only,
917 p_validation_level => p_validation_level,
918 p_calling_module => p_calling_module,
919 p_debug_mode => p_debug_mode,
920 p_max_msg_count => p_max_msg_count,
921 p_element_versions => l_element_versions,
922 x_return_status => x_return_status,
923 x_msg_count => x_msg_count,
924 x_msg_data => x_msg_data);
925
926 IF (l_debug_mode = 'Y') THEN
927 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.ROLLUP_FROM_SUBPROJECT', 'END', 3);
928 END IF;
929
930 END Rollup_From_Subproject;
931
932 -- API name : Tasks_Rollup_Unlimited
933 -- Type : Public procedure
934 -- Pre-reqs : None
935 -- Return Value : N/A
936 -- Prameters
937 -- p_api_version IN NUMBER := 1.0
938 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
939 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
940 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
941 -- p_validation_level IN VARCHAR2 := 100
942 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
943 -- p_debug_mode IN VARCHAR2 := 'N'
944 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
945 -- p_element_versions IN pa_element_version_id_tbl_typ
946 -- x_return_status OUT VARCHAR2
947 -- x_msg_count OUT NUMBER
948 -- x_msg_data OUT VARCHAR2
949
950 -- This procedure is created as a wrapper api for tasks_rollup,
951 -- to overcome the limitations of Tasks_Rollup api,
952 -- which works only for 1000 tasks.
953 -- The reason for not changing the Tasks_Roolup api is that it is also
954 -- being called directly from Self-Service (update tasks page), and we
955 -- cannot use PL/SQL table as an input/output parameter
956
957 PROCEDURE TASKS_ROLLUP_UNLIMITED(
958 p_api_version IN NUMBER := 1.0
959 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
960 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
961 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
962 ,p_validation_level IN VARCHAR2 := 100
963 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
964 ,p_debug_mode IN VARCHAR2 := 'N'
965 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
966 ,p_element_versions IN pa_element_version_id_tbl_typ
967 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
968 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
969 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
970
971 IS
972 l_element_ver_ids PA_NUM_1000_NUM := PA_NUM_1000_NUM();
973 l_count number :=0;
974 BEGIN
975
976 IF (p_debug_mode = 'Y') THEN
977 pa_debug.debug('PA_STRUCT_TASK_ROLLUP_PUB.Tasks_Rollup_Unlimited BEGIN');
978 END IF;
979
980 IF (p_commit = FND_API.G_TRUE) THEN
981 savepoint TASKS_ROLLUP_UNLIMITED_PUB;
982 END IF;
983
984 If (p_element_versions.count = 0) THEN
985 x_return_status := FND_API.G_RET_STS_SUCCESS;
986 return;
987 END IF;
988
989 FOR i in 1..p_element_versions.count LOOP
990 l_count := l_count +1;
991
992 IF (l_count mod 1000) = 0 then
993
994 -- 1000 th record from table is reached
995 l_element_ver_ids.extend;
996 l_element_ver_ids(l_element_ver_ids.count) := p_element_versions(i);
997 -- Call api which takes array of 1000
998 Tasks_Rollup(
999 p_api_version => p_api_version
1000 ,p_init_msg_list => p_init_msg_list
1001 ,p_commit => p_commit
1002 ,p_validate_only => p_validate_only
1003 ,p_validation_level => p_validation_level
1004 ,p_calling_module => p_calling_module
1005 ,p_debug_mode => p_debug_mode
1006 ,p_max_msg_count => p_max_msg_count
1007 ,p_element_versions => l_element_ver_ids
1008 ,x_return_status => x_return_status
1009 ,x_msg_count => x_msg_count
1010 ,x_msg_data => x_msg_data
1011 );
1012 --Added by rtarway for BUG 4349474
1013 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1014 RAISE FND_API.G_EXC_ERROR;
1015 END IF;
1016 -- delete the array 100 records are reached
1017 l_element_ver_ids.delete;
1018 ELSE
1019 l_element_ver_ids.extend;
1020 l_element_ver_ids(l_element_ver_ids.count) := p_element_versions(i);
1021 END IF;
1022 END LOOP;
1023
1024 IF l_element_ver_ids.count > 0 THEN
1025 -- Call Tasks_Rollup for remaining records in array if any
1026 Tasks_Rollup(
1027 p_api_version => p_api_version
1028 ,p_init_msg_list => p_init_msg_list
1029 ,p_commit => p_commit
1030 ,p_validate_only => p_validate_only
1031 ,p_validation_level => p_validation_level
1032 ,p_calling_module => p_calling_module
1033 ,p_debug_mode => p_debug_mode
1034 ,p_max_msg_count => p_max_msg_count
1035 ,p_element_versions => l_element_ver_ids
1036 ,x_return_status => x_return_status
1037 ,x_msg_count => x_msg_count
1038 ,x_msg_data => x_msg_data
1039 );
1040 --Added by rtarway for BUG 4349474
1041 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1042 RAISE FND_API.G_EXC_ERROR;
1043 END IF;
1044 END IF;
1045
1046
1047 IF (p_commit = FND_API.G_TRUE) THEN
1048 commit;
1049 END IF;
1050
1051 x_return_status := FND_API.G_RET_STS_SUCCESS;
1052
1053 EXCEPTION
1054 WHEN FND_API.G_EXC_ERROR THEN
1055 IF (p_commit = FND_API.G_TRUE) THEN
1056 ROLLBACK to TASKS_ROLLUP_UNLIMITED_PUB;
1057 END IF;
1058 x_msg_count := FND_MSG_PUB.count_msg;
1059 x_return_status := FND_API.G_RET_STS_ERROR;
1060 WHEN OTHERS THEN
1061 IF (p_commit = FND_API.G_TRUE) THEN
1062 ROLLBACK to TASKS_ROLLUP_UNLIMITED_PUB;
1063 END IF;
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 x_msg_count := FND_MSG_PUB.count_msg;
1066 --put message
1067 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STRUCT_TASK_ROLLUP_PUB',
1068 p_procedure_name => 'Tasks_Rollup_Unlimited',
1069 p_error_text => SUBSTRB(SQLERRM,1,240));
1070 RAISE;
1071
1072 END TASKS_ROLLUP_UNLIMITED;
1073
1074
1075 -- API name : Tasks_Rollup
1076 -- Type : Public procedure
1077 -- Pre-reqs : None
1078 -- Return Value : N/A
1079 -- Prameters
1080 -- p_api_version IN NUMBER := 1.0
1081 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1082 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1083 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1084 -- p_validation_level IN VARCHAR2 := 100
1085 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1086 -- p_debug_mode IN VARCHAR2 := 'N'
1087 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1088 -- p_element_versions IN PA_NUM_1000_NUM
1089 -- x_return_status OUT VARCHAR2
1090 -- x_msg_count OUT NUMBER
1091 -- x_msg_data OUT VARCHAR2
1092
1093
1094 Procedure Tasks_Rollup(
1095 p_api_version IN NUMBER := 1.0
1096 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1097 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1098 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1099 ,p_validation_level IN VARCHAR2 := 100
1100 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1101 ,p_debug_mode IN VARCHAR2 := 'N'
1102 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1103 ,p_element_versions IN PA_NUM_1000_NUM
1104 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1105 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1106 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1107 )
1108 IS
1109
1110 l_rollup_table PA_SCHEDULE_OBJECTS_PVT.PA_SCHEDULE_OBJECTS_TBL_TYPE;
1111 l_process_number NUMBER;
1112
1113 TYPE t_proj_id_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1114 l_proj_id_tbl t_proj_id_table;
1115
1116 --added for bulk update
1117 TYPE t_schDate_Tbl IS TABLE Of DATE INDEX BY BINARY_INTEGER;
1118 l_old_sch_st_date_tbl t_schDate_Tbl;
1119 l_old_sch_fn_date_tbl t_schDate_Tbl;
1120 l_sch_start_date_tbl t_schDate_Tbl;
1121 l_sch_finish_date_tbl t_schDate_Tbl;
1122 l_effort_tbl t_proj_id_table;
1123 l_elem_ver_id_tbl t_proj_id_table;
1124
1125 l_res_asgmt_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
1126 /* Bug #: 3305199 SMukka */
1127 /* Changing data type from PA_PLSQL_DATATYPES.IdTabTyp to SYSTEM.pa_num_tbl_type */
1128 /*l_planning_start_tbl PA_PLSQL_DATATYPES.NumTabTyp; */
1129 /*l_planning_end_tbl PA_PLSQL_DATATYPES.NumTabTyp; */
1130 l_planning_start_tbl SYSTEM.pa_date_tbl_type;
1131 l_planning_end_tbl SYSTEM.pa_date_tbl_type;
1132
1133 TYPE DYNAMIC_CUR IS REF CURSOR;
1134 l_cur DYNAMIC_CUR;
1135 l_sql VARCHAR2(32767);
1136 l_predicate VARCHAR2(32767);
1137 l_predicate2 VARCHAR2(32767);
1138 l_index NUMBER;
1139
1140 l_cnt NUMBER;
1141
1142 l_CursorId Integer;
1143 l_update_stmt VARCHAR2(32767);
1144 l_RowsUpdated NUMBER;
1145
1146 l_project_id NUMBER;
1147 l_element_version_id NUMBER;
1148 l_object_type VARCHAR2(30);
1149 l_wbs_level NUMBER;
1150 l_start_date DATE;
1151 l_finish_date DATE;
1152 l_parent_id NUMBER;
1153 l_parent_object_type VARCHAR2(30);
1154
1155 l_duration NUMBER;
1156 l_duration_days NUMBER;
1157 l_calendar_id NUMBER;
1158
1159 l_structure_version_id NUMBER;
1160 l_baseline_proj_id NUMBER;
1161 l_versioning VARCHAR2(1);
1162 l_planned_effort PA_PROJ_ELEM_VER_SCHEDULE.PLANNED_EFFORT%TYPE;
1163
1164 --bug 4290472, rtarway
1165 str_start_date DATE;
1166 str_end_date DATE;
1167
1168
1169 CURSOR c_get_project_dates (l_project_id NUMBER) IS
1170 SELECT START_DATE, COMPLETION_DATE
1171 FROM PA_PROJECTS_ALL
1172 WHERE PROJECT_ID = l_project_id;
1173
1174 --bug 4290472, rtarway
1175
1176 -- anlee
1177 -- Dates changes
1178 l_template_flag VARCHAR2(1);
1179 l_record_version_number NUMBER;
1180
1181 cursor get_proj_attr_csr(c_project_id NUMBER)
1182 IS
1183 SELECT template_flag, record_version_number
1184 FROM pa_projects_all
1185 WHERE project_id = c_project_id;
1186 -- End of changes
1187
1188 cursor c1(c_child_element_id NUMBER) IS
1189 select b.project_id, b.element_version_id
1190 from pa_object_relationships a,
1191 pa_proj_element_versions b
1192 where a.relationship_type = 'L'
1193 and a.object_id_to1 = c_child_element_id
1194 and a.object_type_from = 'PA_TASKS'
1195 and a.object_id_from1 = b.element_version_id
1196 and a.object_type_from = b.object_type;
1197 c1_rec c1%ROWTYPE;
1198
1199 -- hyau get calendar id to calculate duration
1200 cursor get_calendar_id_csr(c_project_id NUMBER, c_element_version_id NUMBER)
1201 IS
1202 SELECT calendar_id
1203 FROM pa_proj_elem_ver_schedule
1204 WHERE element_version_id = c_element_version_id
1205 AND project_id = c_project_id;
1206
1207 -- hsiu added for bulk update
1208 CURSOR get_scheduled_dates(c_project_Id NUMBER, c_element_version_id NUMBER)
1209 IS
1210 select scheduled_start_date, scheduled_finish_date
1211 from pa_proj_elem_ver_schedule
1212 where project_id = c_project_id
1213 and element_version_id = c_element_version_id;
1214 l_get_sch_dates_cur get_scheduled_dates%ROWTYPE;
1215
1216 -- hsiu auto sync changes
1217 cursor get_structure_id(c_structure_ver_id NUMBER)
1218 IS
1219 SELECT project_id, proj_element_id
1220 FROM pa_proj_element_versions
1221 where element_version_id = c_structure_ver_id;
1222
1223 l_structure_id NUMBER;
1224 l_struc_project_id NUMBER;
1225
1226 -- hsiu added for task partial rollup
1227 CURSOR get_rollup_flag(c_element_version_id NUMBER)
1228 IS
1229 SELECT PA_PROJ_ELEMENTS_UTILS.CHECK_TASK_STUS_ACTION_ALLOWED(
1230 a.STATUS_CODE, 'PLAN_ROLLUP')
1231 FROM pa_proj_elements a, pa_proj_element_versions b
1232 WHERE a.proj_element_id = b.proj_element_id
1233 AND a.project_id = b.project_id
1234 AND b.element_version_id = c_element_version_id;
1235
1236 --hsiu: bug 2660330
1237 --status control is not used anymore
1238 CURSOR get_system_status_code(c_element_version_id NUMBER)
1239 IS
1240 SELECT decode(project_system_status_code, 'CANCELLED', 'N', 'Y')
1241 FROM pa_proj_elements a, pa_proj_element_versions b,
1242 pa_project_statuses c
1243 where a.proj_element_id = b.proj_element_id
1244 and a.project_id = b.project_id
1245 and b.element_version_id = c_element_version_id
1246 and a.status_code = c.project_status_code
1247 and c.status_type = 'TASK';
1248
1249 l_rollup_flag VARCHAR2(1);
1250 --
1251 --Bug No 3450684
1252 CURSOR get_str_ver_ic_lnk_tasks(cp_structure_Version_id NUMBER) IS
1253 SELECT pora.object_id_from1 parent_lnk_task_ver_id,
1254 pora.object_id_to1 struct_version_id,
1255 pora.object_id_from2 parent_proj_id,
1256 porb.object_id_from1 parent_task_ver_id
1257 FROM pa_object_relationships pora,
1258 pa_object_relationships porb
1259 WHERE pora.object_id_to1 = cp_structure_Version_id
1260 AND pora.RELATIONSHIP_TYPE = 'LW'
1261 AND pora.object_id_from2 <> pora.object_id_to2
1262 AND pora.OBJECT_TYPE_TO = 'PA_STRUCTURES'
1263 AND pora.OBJECT_TYPE_FROM = 'PA_TASKS'
1264 AND pora.object_id_from1 = porb.object_id_to1
1265 AND porb.RELATIONSHIP_TYPE = 'S'
1266 AND porb.OBJECT_TYPE_TO = 'PA_TASKS'
1267 AND porb.OBJECT_TYPE_FROM = 'PA_TASKS';
1268 get_str_ver_ic_lnk_tasks_rec get_str_ver_ic_lnk_tasks%ROWTYPE;
1269
1270 CURSOR chk_str_working_ver(cp_proj_id NUMBER, cp_str_ver_id NUMBER) IS
1271 SELECT 1
1272 FROM pa_proj_elem_ver_structure
1273 WHERE project_id = cp_proj_id
1274 -- AND proj_element_id = p_structure_id
1275 AND element_version_id = ( select parent_structure_version_id
1276 from pa_proj_element_versions where element_version_id=cp_str_ver_id) --bug 4287813
1277 AND status_code <> 'STRUCTURE_PUBLISHED';
1278 chk_str_working_ver_rec chk_str_working_ver%ROWTYPE;
1279
1280 CURSOR get_parent_task_str_ver(cp_parent_task_ver_id NUMBER) IS
1281 SELECT PARENT_STRUCTURE_VERSION_ID,project_id
1282 FROM pa_proj_element_versions
1283 WHERE element_version_id = cp_parent_task_ver_id;
1284 parent_task_str_ver_id NUMBER;
1285 parent_task_proj_id NUMBER;
1286 --
1287 l_parent_task_ver_id_tbl PA_NUM_1000_NUM := PA_NUM_1000_NUM();
1288 l_dummy NUMBER;
1289
1290 l_assgn_context VARCHAR2(30); --bug 4153366
1291 l_debug_mode varchar2(1) := 'N';--added by rtarway, 4218977
1292 g_module_name varchar2(200) := 'PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP';--added by rtarway, 4218977
1293 --
1294
1295 --bug 4296915
1296 CURSOR check_pub_str(cp_project_id NUMBER, cp_str_ver_id NUMBER)
1297 IS
1298 SELECT 'x'
1299 FROM pa_proj_elem_ver_structure
1300 WHERE project_id=cp_project_id
1301 AND element_version_id = cp_str_ver_id
1302 AND status_code = 'STRUCTURE_PUBLISHED'
1303 ;
1304 l_dummy_char VARCHAR2(1);
1305 --end bug 4296915
1306
1307
1308 --bug 4541039
1309 --select all the programs up in the hierarchy and pass it to rollup_from_subprojects
1310 --in order to propagate the schedule dates upto the top of the hierarchy.
1311 CURSOR cur_select_hier(c_project_id NUMBER, c_structure_version_id NUMBER)
1312 IS
1313 SELECT object_id_from1, object_id_from2, object_id_to1, object_id_to2
1314 FROM pa_object_relationships
1315 START with object_id_to2 = c_project_id and relationship_type = 'LW' and object_id_to1 = c_structure_version_id
1316 CONNECT by object_id_to2 = prior object_id_from2
1317 AND relationship_type = prior relationship_type;
1318 l_rollup_from_sub_project VARCHAR2(1);
1319 --end bug 4541039
1320
1321 begin
1322
1323 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');--added by rtarway, 4218977
1324
1325 IF (l_debug_mode = 'Y') THEN
1326 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP', 'Entered...', 3);
1327 END IF;
1328
1329 IF (p_commit = FND_API.G_TRUE) THEN
1330 savepoint TASKS_ROLLUP_PVT;
1331 END IF;
1332
1333 IF (l_debug_mode = 'Y') THEN
1334 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP', 'Performing validations',3);
1335 END IF;
1336
1337 If (p_element_versions.count = 0) THEN
1338 x_return_status := FND_API.G_RET_STS_SUCCESS;
1339 return;
1340 END IF;
1341
1342 l_index := 1;
1343 l_predicate := '';
1344 l_predicate2 := '';
1345 LOOP
1346 l_predicate := l_predicate||to_char(p_element_versions(l_index));
1347 l_predicate2 := l_predicate2||to_char(p_element_versions(l_index));
1348 exit when l_index = p_element_versions.count;
1349 l_predicate := l_predicate||',';
1350 l_predicate2 := l_predicate2||',';
1351 l_index := l_index + 1;
1352 END LOOP;
1353
1354 --dbms_output.put_line('Predicate => '||l_predicate);
1355 --dbms_output.put_line('Predicate2 => '||l_predicate2);
1356
1357 IF (l_debug_mode = 'Y') THEN
1358 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Predicate = '||l_predicate,3);
1359 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Predicate2 = '||l_predicate2,3);
1360 END IF;
1361
1362 --first select stmt - get all parents along the branch of the element
1363 --second select stmt - get direct child of all parents
1364 --third select stmt - get input element
1365 -- hsiu: removing third one because the second statment should cover it
1366 -- FPM bug 3301192 : Added pa_proj_elements join and link_task_flag condition
1367 l_sql :=
1368 ' select a.project_id, a.element_version_id,'||
1369 ' a.object_type, b.PLANNED_EFFORT, '||
1370 ' nvl(a.wbs_level,0), b.scheduled_start_date, '||
1371 ' b.scheduled_finish_date, c.object_id_from1, '||
1372 ' c.object_type_from from '||
1373 ' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
1374 ' pa_object_relationships c, pa_proj_elements d where '||
1375 ' a.element_version_id = c.object_id_to1(+) and '||
1376 ' c.relationship_type(+)= '||''''||'S'||''''||' and '||
1377 ' a.project_id = b.project_id and '||
1378 ' a.element_version_id = b.element_version_id and '||
1379 ' a.proj_element_id = d.proj_element_id and '|| -- 3305199
1380 ' d.link_task_flag = '||''''||'N'||''''||' and '||
1381 ' a.element_version_id IN ('||
1382 ' select object_id_from1 from '||
1383 ' pa_object_relationships connect by '||
1384 ' prior object_id_from1 = object_id_to1 '||
1385 ' AND RELATIONSHIP_TYPE = prior relationship_type '||
1386 ' and relationship_type = '||''''||'S'||''''||
1387 ' start with object_id_to1 IN ('||
1388 l_predicate||
1389 ') ' ||
1390 ' and relationship_type = '||''''||'S'||''''||
1391 ') UNION '||
1392 ' select distinct a.project_id, a.element_version_id, '||
1393 ' a.object_type, b.PLANNED_EFFORT, '||
1394 ' nvl(a.wbs_level,0), b.scheduled_start_date, '||
1395 ' b.scheduled_finish_date, c.object_id_from1, '||
1396 ' c.object_type_from from '||
1397 ' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
1398 ' pa_object_relationships c, pa_proj_elements d where '||
1399 ' a.element_version_id = c.object_id_to1 and '||
1400 ' c.relationship_type = '||''''||'S'||''''||' and '||
1401 ' a.project_id = b.project_id and '||
1402 ' a.element_version_id = b.element_version_id and '||
1403 ' a.proj_element_id = d.proj_element_id and '|| -- 3305199
1404 -- ' a.element_version_id = d.proj_element_id and '||
1405 ' d.link_task_flag = '||''''||'N'||''''||' and '||
1406 ' c.object_id_from1 IN ('||
1407 ' select object_id_from1 from '||
1408 ' pa_object_relationships connect by '||
1409 ' prior object_id_from1 = object_id_to1 '||
1410 ' and relationship_type = '||''''||'S'||''''||
1411 ' AND RELATIONSHIP_TYPE = prior relationship_type '||
1412 ' start with object_id_to1 IN ('||
1413 l_predicate||
1414 ')' ||
1415 ' and relationship_type = '||''''||'S'||''''||
1416 ')';
1417
1418 -- ') UNION '||
1419 -- ' select distinct a.project_id, a.element_version_id, '||
1420 -- ' a.object_type, b.PLANNED_EFFORT, '||
1421 -- ' nvl(a.wbs_level,0), b.scheduled_start_date, '||
1422 -- ' b.scheduled_finish_date, c.object_id_from1, '||
1423 -- ' c.object_type_from from '||
1424 -- ' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
1425 -- ' pa_object_relationships c where '||
1426 -- ' a.element_version_id = c.object_id_to1(+) and '||
1427 -- ' c.relationship_type(+) = '||''''||'S'||''''||' and '||
1428 -- ' a.project_id = b.project_id and '||
1429 -- ' a.element_version_id = b.element_version_id and '||
1430 -- ' a.element_version_id IN ('||l_predicate2||
1431 -- ')';
1432
1433 --dbms_output.put_line('after');
1434
1435
1436 l_cnt := 0;
1437 open l_cur for l_sql;
1438 LOOP
1439 l_cnt := l_cnt + 1;
1440 --Cannot fetch into PL/SQL table because it will create a null row when %NOTFOUND.
1441 FETCH l_cur into l_project_id,
1442 l_element_version_id,
1443 l_object_type,
1444 l_planned_effort,
1445 l_wbs_level,
1446 l_start_date,
1447 l_finish_date,
1448 l_parent_id,
1449 l_parent_object_type;
1450 exit when l_cur%NOTFOUND;
1451
1452 l_proj_id_tbl(l_cnt) := l_project_id;
1453 l_rollup_table(l_cnt).object_id := l_element_version_id;
1454 l_rollup_table(l_cnt).object_type := l_object_type;
1455 l_rollup_table(l_cnt).REMAINING_EFFORT1 := l_planned_effort;
1456 l_rollup_table(l_cnt).wbs_level := l_wbs_level;
1457 l_rollup_table(l_cnt).start_date1 := l_start_date;
1458 l_rollup_table(l_cnt).finish_date1 := l_finish_date;
1459 l_rollup_table(l_cnt).parent_object_id := l_parent_id;
1460 l_rollup_table(l_cnt).parent_object_type := l_parent_object_type;
1461 l_rollup_table(l_cnt).dirty_flag1 := 'N';
1462
1463 --hsiu added for task partial rollup
1464 --commented out for bug 2660330
1465 -- OPEN get_rollup_flag(l_element_version_id);
1466 -- FETCH get_rollup_flag into l_rollup_flag;
1467 -- CLOSE get_rollup_flag;
1468 OPEN get_system_status_code(l_element_version_id);
1469 FETCH get_system_status_code into l_rollup_flag;
1470 CLOSE get_system_status_code;
1471 --done changes for bug 2660330
1472
1473 l_rollup_table(l_cnt).ROLLUP_NODE1 := l_rollup_flag;
1474
1475 --dbms_output.put_line('....count = '||l_cnt||'....');
1476 --dbms_output.put_line('pid ='||l_proj_id_tbl(l_cnt));
1477 --dbms_output.put_line('elem ='||l_rollup_table(l_cnt).object_id||' , parent='||l_rollup_table(l_cnt).parent_object_id);
1478 --dbms_output.put_line('sd ='||l_rollup_table(l_cnt).start_date1||', fd ='||l_rollup_table(l_cnt).finish_date1);
1479 --dbms_output.put_line('id = '||l_element_version_id);
1480
1481 l_index := 1;
1482 LOOP
1483 if l_element_version_id = p_element_versions(l_index) then
1484 l_rollup_table(l_cnt).dirty_flag1 := 'Y';
1485 end if;
1486 exit when l_index = p_element_versions.count;
1487 l_index := l_index + 1;
1488 END LOOP;
1489
1490 --get the structure version id
1491 IF (l_rollup_table(l_cnt).object_type = 'PA_STRUCTURES') THEN
1492 --save structure id
1493 l_structure_version_id := l_rollup_table(l_cnt).object_id;
1494 l_baseline_proj_id := l_proj_id_tbl(l_cnt);
1495
1496 --get template flag; will need to change when incorporating linking
1497 OPEN get_proj_attr_csr(l_baseline_proj_id);
1498 FETCH get_proj_attr_csr INTO l_template_flag, l_record_version_number;
1499 CLOSE get_proj_attr_csr;
1500 END IF;
1501
1502 END LOOP;
1503 --dbms_output.put_line('fetched = '||l_cur%ROWCOUNT);
1504
1505
1506 close l_cur;
1507 --Added by rtarway, 4218977
1508 IF l_debug_mode = 'Y' THEN
1509 pa_debug.g_err_stage := 'Value of G_OP_VALIDATE_flag'||PA_PROJECT_PUB.G_OP_VALIDATE_FLAG ;
1510 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1511 END IF;
1512 --If Added by rtarway, BUG 4218977
1513 --Added null condition, for BUG 4226832
1514 if (PA_PROJECT_PUB.G_OP_VALIDATE_FLAG is null OR PA_PROJECT_PUB.G_OP_VALIDATE_FLAG = 'Y' ) then
1515
1516 PA_SCHEDULE_OBJECTS_PVT.GENERATE_SCHEDULE(
1517 p_debug_mode => 'N',
1518 p_data_structure => l_rollup_table,
1519 x_return_status => x_return_status,
1520 x_msg_count => x_msg_count,
1521 x_msg_data => x_msg_data,
1522 x_process_number => l_process_number,
1523 p_process_flag1 => 'Y',
1524 p_partial_process_flag1 => 'Y',
1525 p_partial_dates_flag1 => 'Y',
1526 p_partial_effort_flag1 => 'Y',
1527 p_process_rollup_flag1 => 'Y',
1528 p_process_effort_flag1 => 'Y');
1529 end if;
1530
1531 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1532 RAISE FND_API.G_EXC_ERROR;
1533 ELSE
1534 /* BEGIN
1535 --Update the tasks
1536 l_CursorId := DBMS_SQL.OPEN_CURSOR;
1537 l_update_stmt := 'Update pa_proj_elem_ver_schedule '||
1538 'set SCHEDULED_START_DATE = :sd, '||
1539 'SCHEDULED_FINISH_DATE = :fd, '||
1540 'PLANNED_EFFORT = :pe, '||
1541 'DURATION = :dur, '||
1542 'RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1, '||
1543 'LAST_UPDATE_DATE = SYSDATE, ' ||
1544 'LAST_UPDATED_BY = FND_GLOBAL.USER_ID, ' ||
1545 'LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID ' ||
1546 'where '||
1547 'project_id = :pid and element_version_id = :evid';
1548
1549 --Parse statement
1550 DBMS_SQL.PARSE(l_CursorId, l_update_stmt, DBMS_SQL.V7);
1551
1552 l_index := l_rollup_table.First;
1553 LOOP
1554 IF (p_debug_mode = 'Y') THEN
1555 pa_debug.debug('Binding: '||l_index||', id = '||l_rollup_table(l_index).object_id);
1556 END IF;
1557 --dbms_output.put_line('Binding: '||l_index||', id = '||l_rollup_table(l_index).object_id);
1558
1559 -- hyau l_duration := trunc(l_rollup_table(l_index).finish_date1 - l_rollup_table(l_index).start_date1) + 1;
1560
1561 -- hyau call API to get duration in hours to store to db
1562 OPEN get_calendar_id_csr(l_project_id, l_element_version_id);
1563 FETCH get_calendar_id_csr INTO l_calendar_id;
1564 IF get_calendar_id_csr%NOTFOUND then
1565 x_return_status := FND_API.G_RET_STS_ERROR;
1566 x_msg_count := 1;
1567 x_msg_data := 'PA_PS_NO_SCHEDULE_RECORD';
1568 RAISE FND_API.G_EXC_ERROR;
1569 end if;
1570 CLOSE get_calendar_id_csr;
1571
1572 pa_duration_utils.get_duration(
1573 p_calendar_id => l_calendar_id
1574 ,p_start_date => l_rollup_table(l_index).start_date1
1575 ,p_end_date => l_rollup_table(l_index).finish_date1
1576 ,x_duration_days => l_duration_days
1577 ,x_duration_hours => l_duration
1578 ,x_return_status => x_return_status
1579 ,x_msg_count => x_msg_count
1580 ,x_msg_data => x_msg_data);
1581
1582 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1583 RAISE FND_API.G_EXC_ERROR;
1584 END IF;
1585
1586 --Bind variables
1587 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':sd', l_rollup_table(l_index).start_date1);
1588 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':fd', l_rollup_table(l_index).finish_date1);
1589 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':pe', l_rollup_table(l_index).REMAINING_EFFORT1);
1590 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':dur', l_duration);
1591 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':pid', l_proj_id_tbl(l_index));
1592 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':evid', l_rollup_table(l_index).object_id);
1593
1594 --Update rows
1595 l_RowsUpdated := DBMS_SQL.EXECUTE(l_CursorId);
1596
1597 -- anlee
1598 -- Dates changes
1599 OPEN get_proj_attr_csr(l_proj_id_tbl(l_index));
1600 FETCH get_proj_attr_csr INTO l_template_flag, l_record_version_number;
1601 CLOSE get_proj_attr_csr;
1602
1603 -- Rollup scheduled dates to the project level for templates
1604 -- or project without versioning
1605 if l_template_flag = 'Y' OR
1606 PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_proj_id_tbl(l_index)) = 'N' then
1607 if l_rollup_table(l_index).object_type = 'PA_STRUCTURES' then
1608 PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
1609 p_validate_only => FND_API.G_FALSE
1610 ,p_project_id => l_proj_id_tbl(l_index)
1611 ,p_date_type => 'SCHEDULED'
1612 ,p_start_date => l_rollup_table(l_index).start_date1
1613 ,p_finish_date => l_rollup_table(l_index).finish_date1
1614 ,p_record_version_number => l_record_version_number
1615 ,x_return_status => x_return_status
1616 ,x_msg_count => x_msg_count
1617 ,x_msg_data => x_msg_data );
1618
1619 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1620 RAISE FND_API.G_EXC_ERROR;
1621 END IF;
1622 END IF;
1623 END IF;
1624 -- End of changes
1625
1626 -- baesline changes
1627 IF l_rollup_table(l_index).object_type = 'PA_STRUCTURES' THEN
1628 l_structure_version_id := l_rollup_table(l_index).object_id;
1629 l_baseline_proj_id := l_proj_id_tbl(l_index);
1630 END IF;
1631 -- end changes
1632
1633 --Check for linking tasks
1634 OPEN c1(l_rollup_table(l_index).object_id);
1635 LOOP
1636 FETCH c1 into c1_rec;
1637 EXIT when c1%NOTFOUND;
1638
1639 --update linking task
1640 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':sd', l_rollup_table(l_index).start_date1);
1641 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':fd', l_rollup_table(l_index).finish_date1);
1642 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':dur', l_duration);
1643 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':pid', c1_rec.project_id);
1644 DBMS_SQL.BIND_VARIABLE(l_CursorId, ':evid', c1_rec.element_version_id);
1645
1646 IF (p_debug_mode = 'Y') THEN
1647 pa_debug.debug('Updating linking task '||c1_rec.element_version_id);
1648 END IF;
1649 --dbms_output.put_line('Updating linking task '||c1_rec.element_version_id);
1650 --Update rows
1651 l_RowsUpdated := DBMS_SQL.EXECUTE(l_CursorId);
1652
1653 END LOOP;
1654 CLOSE c1;
1655
1656 EXIT when l_index = l_rollup_table.LAST;
1657 l_index := l_rollup_table.NEXT(l_index);
1658 END LOOP;
1659 DBMS_SQL.CLOSE_CURSOR(l_CursorId);
1660 EXCEPTION
1661 WHEN OTHERS THEN
1662 DBMS_SQL.CLOSE_CURSOR(l_CursorId);
1663 RAISE;
1664 END;
1665 */
1666 if (PA_PROJECT_PUB.G_OP_VALIDATE_FLAG is null OR PA_PROJECT_PUB.G_OP_VALIDATE_FLAG = 'Y' ) then
1667
1668 FOR i IN l_rollup_table.first..l_rollup_table.last LOOP
1669 l_sch_start_date_tbl(i) := l_rollup_table(i).start_date1;
1670 l_sch_finish_date_tbl(i) := l_rollup_table(i).finish_date1;
1671 l_effort_tbl(i) := l_rollup_table(i).remaining_effort1;
1672 l_elem_ver_id_tbl(i) := l_rollup_table(i).object_id;
1673 END LOOP;
1674
1675 --bug 3305199 hsiu
1676 FOR i IN l_rollup_table.first..l_rollup_table.last LOOP
1677 select scheduled_start_date, scheduled_finish_date
1678 into l_old_sch_st_date_tbl(i), l_old_sch_fn_date_tbl(i)
1679 from pa_proj_elem_ver_schedule
1680 where project_id = l_proj_id_tbl(i)
1681 and element_version_id = l_elem_ver_id_tbl(i);
1682 END LOOP;
1683 --end 3305199 hsiu
1684
1685 /* bug 3305199
1686 remove duration calculation
1687 duration = pa_duration_utils.get_total_hours(
1688 calendar_id,
1689 l_sch_start_date_tbl(i),
1690 l_sch_finish_date_tbl(i)),
1691 */
1692 FORALL i IN l_rollup_table.first..l_rollup_table.last
1693 UPDATE pa_proj_elem_ver_schedule
1694 set scheduled_start_date = l_sch_start_date_tbl(i),
1695 scheduled_finish_date = l_sch_finish_date_tbl(i),
1696 planned_effort = l_effort_tbl(i),
1697 duration = l_sch_finish_date_tbl(i) - l_sch_start_date_tbl(i) + 1,
1698 last_update_date = sysdate,
1699 last_updated_by = FND_GLOBAL.USER_ID,
1700 last_update_login = FND_GLOBAL.LOGIN_ID
1701 where project_id = l_proj_id_tbl(i)
1702 and element_version_id = l_elem_ver_id_tbl(i);
1703
1704 --BUG 4290472, rtarway
1705 else
1706 --Added by rtarway for BUG 4349474
1707 FOR i IN l_rollup_table.first..l_rollup_table.last LOOP
1708 l_sch_start_date_tbl(i) := l_rollup_table(i).start_date1;
1709 l_sch_finish_date_tbl(i) := l_rollup_table(i).finish_date1;
1710 l_effort_tbl(i) := l_rollup_table(i).remaining_effort1;
1711 l_elem_ver_id_tbl(i) := l_rollup_table(i).object_id;
1712 END LOOP;
1713
1714
1715 FOR i IN l_rollup_table.first..l_rollup_table.last LOOP
1716 select scheduled_start_date, scheduled_finish_date
1717 into l_old_sch_st_date_tbl(i), l_old_sch_fn_date_tbl(i)
1718 from pa_proj_elem_ver_schedule
1719 where project_id = l_proj_id_tbl(i)
1720 and element_version_id = l_elem_ver_id_tbl(i);
1721
1722 ---- Start of addition for bug 6393979
1723 If l_structure_version_id <> l_elem_ver_id_tbl(i) Then
1724 if nvl(str_start_date,l_sch_start_date_tbl(i)) >= l_sch_start_date_tbl(i) then
1725 str_start_date := l_sch_start_date_tbl(i);
1726 end if;
1727
1728 if nvl(str_end_date,l_sch_finish_date_tbl(i)) <= l_sch_finish_date_tbl(i) then
1729 str_end_date := l_sch_finish_date_tbl(i);
1730 end if;
1731
1732 end if;
1733 --- End of addition for bug 6393979
1734 END LOOP;
1735 --Added by rtarway for BUG 4349474
1736
1737 /* Commented for bug 6393979
1738 OPEN c_get_project_dates(l_baseline_proj_id);
1739 FETCH c_get_project_dates INTO str_start_date, str_end_date;
1740 CLOSE c_get_project_dates;*/
1741
1742 UPDATE pa_proj_elem_ver_schedule
1743 set scheduled_start_date =str_start_date,
1744 scheduled_finish_date = str_end_date,
1745 duration = str_end_date - str_start_date + 1,
1746 last_update_date = sysdate,
1747 last_updated_by = FND_GLOBAL.USER_ID,
1748 last_update_login = FND_GLOBAL.LOGIN_ID
1749 where project_id = l_baseline_proj_id
1750 and element_version_id = l_structure_version_id;
1751 end if;
1752 --BUG 4290472, rtarway
1753
1754
1755
1756 -- Added by skannoji
1757 -- Upon changes on a Task's Scheduled Dates.
1758 FOR i IN l_rollup_table.first..l_rollup_table.last LOOP
1759 /*Smukka Bug No. 3474141 Date 03/01/2004 */
1760 /*moved PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates into plsql block */
1761 BEGIN
1762
1763 --bug 4153366
1764 IF i = l_rollup_table.last
1765 THEN
1766 l_assgn_context := 'UPDATE';
1767 ELSE
1768 l_assgn_context := 'INSERT_VALUES';
1769 END IF;
1770 --bug 4153366
1771
1772 IF (l_debug_mode = 'Y') THEN
1773 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates l_assgn_context = '||l_assgn_context,3);
1774 END IF;
1775
1776 PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates(
1777 p_element_version_id => l_elem_ver_id_tbl(i),
1778 p_old_task_sch_start => l_old_sch_st_date_tbl(i),
1779 p_old_task_sch_finish => l_old_sch_fn_date_tbl(i),
1780 p_new_task_sch_start => l_sch_start_date_tbl(i),
1781 p_new_task_sch_finish => l_sch_finish_date_tbl(i),
1782 p_context => l_assgn_context, --4153366
1783 x_res_assignment_id_tbl => l_res_asgmt_id_tbl,
1784 x_planning_start_tbl => l_planning_start_tbl,
1785 x_planning_end_tbl => l_planning_end_tbl,
1786 x_return_status => x_return_status);
1787 IF (l_debug_mode = 'Y') THEN
1788 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','After calling PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates x_return_status = '||x_return_status,3);
1789 END IF;
1790
1791 EXCEPTION
1792 WHEN OTHERS THEN
1793 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STRUCT_TASK_ROLLUP_PUB',
1794 p_procedure_name => 'Tasks_Rollup',
1795 p_error_text => SUBSTRB('PA_TASK_ASSIGNMENT_UTILS.Adjust_Asgmt_Dates:'||SQLERRM,1,240));
1796 RAISE FND_API.G_EXC_ERROR;
1797 END;
1798
1799 IF x_return_status = FND_API.G_RET_STS_ERROR then
1800 RAISE FND_API.G_EXC_ERROR;
1801 End If;
1802 END LOOP;
1803 -- till here by skannoji
1804
1805 --baseline changes
1806 l_versioning := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(
1807 l_baseline_proj_id);
1808
1809 --added for rolling up to project level
1810 IF (l_template_flag = 'Y' OR l_versioning = 'N') THEN
1811
1812 OPEN get_scheduled_dates(l_baseline_proj_id, l_structure_version_id);
1813 FETCH get_scheduled_dates into l_get_sch_dates_cur;
1814 CLOSE get_scheduled_dates;
1815
1816 IF (l_debug_mode = 'Y') THEN
1817 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES',3);
1818 END IF;
1819
1820
1821 PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
1822 p_validate_only => FND_API.G_FALSE
1823 ,p_project_id => l_baseline_proj_id
1824 ,p_date_type => 'SCHEDULED'
1825 ,p_start_date => l_get_sch_dates_cur.scheduled_start_date
1826 ,p_finish_date => l_get_sch_dates_cur.scheduled_finish_date
1827 ,p_record_version_number => l_record_version_number
1828 ,x_return_status => x_return_status
1829 ,x_msg_count => x_msg_count
1830 ,x_msg_data => x_msg_data );
1831
1832 IF (l_debug_mode = 'Y') THEN
1833 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','After calling PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES x_return_status='||x_return_status,3);
1834 END IF;
1835
1836
1837 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1838 RAISE FND_API.G_EXC_ERROR;
1839 END IF;
1840 END IF;
1841
1842
1843 IF (l_template_flag = 'N' AND l_versioning = 'N') THEN
1844 --baseline
1845 IF (l_debug_mode = 'Y') THEN
1846 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling PA_PROJECT_STRUCTURE_PVT1.BASELINE_STRUCTURE_VERSION l_structure_version_id='||l_structure_version_id,3);
1847 END IF;
1848
1849 PA_PROJECT_STRUCTURE_PVT1.BASELINE_STRUCTURE_VERSION(
1850 p_commit => FND_API.G_FALSE,
1851 p_structure_version_id => l_structure_version_id,
1852 x_return_status => x_return_status,
1853 x_msg_count => x_msg_count,
1854 x_msg_data => x_msg_data);
1855
1856 IF (l_debug_mode = 'Y') THEN
1857 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','After calling PA_PROJECT_STRUCTURE_PVT1.BASELINE_STRUCTURE_VERSION x_return_status='||x_return_status,3);
1858 END IF;
1859
1860
1861 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1862 x_msg_count := FND_MSG_PUB.count_msg;
1863 if x_msg_count = 1 then
1864 x_msg_data := x_msg_data;
1865 end if;
1866 raise FND_API.G_EXC_ERROR;
1867 end if;
1868 END IF;
1869 --end baseline changes
1870
1871 END IF;
1872
1873 --auto sync changes
1874 OPEN get_structure_id(l_structure_version_id);
1875 FETCH get_structure_id into l_struc_project_id, l_structure_id;
1876 CLOSE get_structure_id;
1877
1878 /* bug 3676078
1879 IF ((PA_WORKPLAN_ATTR_UTILS.CHECK_AUTO_DATE_SYNC_ENABLED(l_structure_id) = 'Y') AND
1880 (PA_PROJECT_STRUCTURE_UTILS.CHECK_SHARING_ENABLED(l_struc_project_id) = 'Y')) AND
1881 (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_struc_project_id) = 'N') THEN
1882 */
1883 IF ((PA_WORKPLAN_ATTR_UTILS.CHECK_AUTO_DATE_SYNC_ENABLED(l_structure_id) = 'Y') AND
1884 (PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(l_struc_project_id) = 'SHARE_FULL')) AND
1885 (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_struc_project_id) = 'N') THEN
1886 --copy to transaction dates
1887
1888 IF (l_debug_mode = 'Y') THEN
1889 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling PA_PROJECT_DATES_PUB.COPY_PROJECT_DATES',3);
1890 END IF;
1891
1892 PA_PROJECT_DATES_PUB.COPY_PROJECT_DATES(
1893 p_validate_only => FND_API.G_FALSE
1894 ,p_project_id => l_struc_project_id
1895 ,x_return_status => x_return_status
1896 ,x_msg_count => x_msg_count
1897 ,x_msg_data => x_msg_data
1898 );
1899
1900 IF (l_debug_mode = 'Y') THEN
1901 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','After calling PA_PROJECT_DATES_PUB.COPY_PROJECT_DATES x_return_status='||x_return_status,3);
1902 END IF;
1903
1904
1905 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1906 x_msg_count := FND_MSG_PUB.count_msg;
1907 if x_msg_count = 1 then
1908 x_msg_data := x_msg_data;
1909 end if;
1910 raise FND_API.G_EXC_ERROR;
1911 end if;
1912
1913 --copy to project level
1914
1915 END IF;
1916 --end auto sync changes
1917 --
1918 --
1919 IF PA_PROJECT_STRUCTURE_UTILS.Check_Subproject_Exists(l_struc_project_id,l_structure_version_id, 'WORKPLAN') = 'Y' THEN
1920 /* 4541039 Always rollup to the parent. This will fulfill the following cases:
1921 1) Update Task flow
1922 + schedule dates are getting updated for a non-versioned project. If the sub-project is
1923 + schedule dates are getting updated on a working verison for a versioned project. The dates from the sub-project
1924 will be rolled up into the parent project.
1925 2) Publish flow
1926 In this flow, the data from sub-project published verison will be rolled up into the new published verison.
1927 The api will also be called for working version if the process WBS updates is already not run.
1928 3) Running Process Updates
1929 In this flow, data from the sub-projects rolls up to this project.
1930
1931 --bug 4296915
1932 OPEN check_pub_str(l_struc_project_id,l_structure_version_id);
1933 FETCH check_pub_str INTO l_dummy_char;
1934 IF check_pub_str%FOUND
1935 THEN
1936 bug 4541039 */
1937 ----end bug 4296915
1938 --Bug No.3450684
1939 --Start rolling up from subproject associations
1940
1941 IF (l_debug_mode = 'Y') THEN
1942 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling Rollup_From_Subproject to rollup from the current project''s sub-project',3);
1943 END IF;
1944
1945 Rollup_From_Subproject(
1946 p_api_version => p_api_version
1947 ,p_init_msg_list => p_init_msg_list
1948 ,p_commit => p_commit
1949 ,p_validate_only => p_validate_only
1950 ,p_validation_level => p_validation_level
1951 ,p_calling_module => p_calling_module
1952 ,p_debug_mode => p_debug_mode
1953 ,p_max_msg_count => p_max_msg_count
1954 ,p_element_versions => p_element_versions --Check with hubert
1955 ,x_return_status => x_return_status
1956 ,x_msg_count => x_msg_count
1957 ,x_msg_data => x_msg_data);
1958
1959 IF (l_debug_mode = 'Y') THEN
1960 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','After calling Rollup_From_Subproject to rollup from the current project''s sub-project x_return_status='||x_return_status,3);
1961 END IF;
1962
1963 --
1964 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1965 x_msg_count := FND_MSG_PUB.count_msg;
1966 if x_msg_count = 1 then
1967 x_msg_data := x_msg_data;
1968 end if;
1969 raise FND_API.G_EXC_ERROR;
1970 end if;
1971 /* bug 4541039
1972 --bug 4296915
1973 END IF;
1974 CLOSE check_pub_str;
1975 --end bug 4296915
1976 bug 4541039 */
1977 END IF; --end if for checking existence of subproject association
1978 --
1979 --The following code is added to rollup schedule dates to the top of the program hierarchy. Currently there is an issue
1980 --the schedules dates rollup. The dates are not rollin up to the top if the hierarchy has more than 2 levels.
1981 --
1982 --The cursor cur_select_hier will select all the programs in the hierarchy.
1983 --object_id_from2 is the parent project
1984 --object_id_to2 is the sub project of the parent sub-project.
1985 --
1986 --Here l_struc_project_id is the current sub-project. This sub-project may be getting updated or getting published if its versioend.
1987 -- Rollup happnes from the following:
1988 --published version of the sub-project to the working version of the program.
1989 --published verion of the sub-project to the published verison of the program if program is versioned disabled.
1990 --no rollup happens from a published verison of versioned program to its program(that is to third level).
1991
1992 ----bug 4541039
1993
1994 IF (l_debug_mode = 'Y') THEN
1995 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','l_struc_project_id='||l_struc_project_id,3);
1996 END IF;
1997
1998 FOR cur_select_hier_rec in cur_select_hier( l_struc_project_id, l_structure_version_id ) LOOP ----bug 4541039
1999
2000 --do not rollup from working version except when the current project is getting published.
2001 IF l_struc_project_id <> cur_select_hier_rec.object_id_to2
2002 AND PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(cur_select_hier_rec.object_id_to2) = 'Y'
2003 THEN
2004 l_rollup_from_sub_project := 'N';
2005 ELSE
2006 l_rollup_from_sub_project := 'Y';
2007 END IF;
2008
2009
2010 IF (l_debug_mode = 'Y') THEN
2011 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','l_rollup_from_sub_project='||l_struc_project_id,3);
2012 END IF;
2013
2014 IF l_rollup_from_sub_project = 'Y'
2015 THEN
2016 ----bug 4541039
2017
2018 --Getting all the linking information from linking task
2019 --provide the sub-project structure verison id.
2020 OPEN get_str_ver_ic_lnk_tasks(cur_select_hier_rec.object_id_to1); -- bug 4541039. Provide the sub-project structure version.
2021 LOOP
2022 FETCH get_str_ver_ic_lnk_tasks INTO get_str_ver_ic_lnk_tasks_rec;
2023 EXIT WHEN get_str_ver_ic_lnk_tasks%NOTFOUND;
2024 --
2025 /*
2026 --Getting the parent_structure_version_id from the given task version id
2027 --Trying to execute the following if block only when the project id changes
2028 IF parent_task_proj_id IS NULL OR (parent_task_proj_id = get_str_ver_ic_lnk_tasks_rec.parent_proj_id) THEN
2029 OPEN get_parent_task_str_ver(get_str_ver_ic_lnk_tasks_rec.parent_task_ver_id);
2030 FETCH get_parent_task_str_ver INTO parent_task_str_ver_id,
2031 parent_task_proj_id;
2032 EXIT WHEN get_parent_task_str_ver%NOTFOUND;
2033 CLOSE get_parent_task_str_ver;
2034 END IF;
2035 */
2036 --
2037 IF PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(get_str_ver_ic_lnk_tasks_rec.parent_proj_id) ='Y' THEN
2038
2039 --Checking to see if the parent structure version is working version.
2040 OPEN chk_str_working_ver(get_str_ver_ic_lnk_tasks_rec.parent_proj_id,
2041 get_str_ver_ic_lnk_tasks_rec.parent_task_ver_id); --bug 4287813
2042 FETCH chk_str_working_ver into l_dummy;
2043 IF chk_str_working_ver%NOTFOUND THEN
2044 CLOSE chk_str_working_ver;
2045 ELSE
2046 --bug 4296915
2047 ---check if the current structure version is a published or working ( this api is called from update task as well as publishing)
2048 --check the if the sub-project from where the rollup happens is a publsihed version or not.
2049 OPEN check_pub_str(cur_select_hier_rec.object_id_to2,cur_select_hier_rec.object_id_to1); --bug 4541039 Provide the sub-project structure verison.
2050 FETCH check_pub_str INTO l_dummy_char;
2051 IF check_pub_str%FOUND
2052 THEN
2053 --end bug 4296915
2054 l_index := 1;
2055 l_parent_task_ver_id_tbl.extend;
2056 l_parent_task_ver_id_tbl(l_index):=get_str_ver_ic_lnk_tasks_rec.parent_task_ver_id;
2057 IF (l_debug_mode = 'Y') THEN
2058 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling Rollup_From_Subproject to rollup from the current project id='||
2059 cur_select_hier_rec.object_id_to2||' to its parent project id='||get_str_ver_ic_lnk_tasks_rec.parent_proj_id,3);
2060 END IF;
2061
2062 PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject(
2063 p_api_version => p_api_version
2064 ,p_init_msg_list => p_init_msg_list
2065 ,p_commit => p_commit
2066 ,p_validate_only => p_validate_only
2067 ,p_validation_level => p_validation_level
2068 ,p_calling_module => p_calling_module
2069 ,p_debug_mode => p_debug_mode
2070 ,p_max_msg_count => p_max_msg_count
2071 ,p_element_versions => l_parent_task_ver_id_tbl
2072 ,x_return_status => x_return_status
2073 ,x_msg_count => x_msg_count
2074 ,x_msg_data => x_msg_data);
2075
2076 IF (l_debug_mode = 'Y') THEN
2077 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','After calling Rollup_From_Subproject to rollup from the current project id='||
2078 cur_select_hier_rec.object_id_to2||' to its parent project id='||get_str_ver_ic_lnk_tasks_rec.parent_proj_id||' x_return_status='||x_return_status,3);
2079 END If;
2080 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2081 x_msg_count := FND_MSG_PUB.count_msg;
2082 if x_msg_count = 1 then
2083 x_msg_data := x_msg_data;
2084 end if;
2085 raise FND_API.G_EXC_ERROR;
2086 end if;
2087 END IF; ----check_pub_str bug 4296915
2088 CLOSE check_pub_str;
2089 CLOSE chk_str_working_ver; --this shold be inside ELSE otherwise its giving invalid cursor. --maansari 4293726
2090 END IF; --chk_str_working_ver
2091 ELSE
2092 -- IF PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(get_str_ver_ic_lnk_tasks_rec.parent_proj_id,
2093 -- parent_task_str_ver_id) = 'Y' THEN
2094 l_index := 1;
2095 l_parent_task_ver_id_tbl.extend;
2096 l_parent_task_ver_id_tbl(l_index):=get_str_ver_ic_lnk_tasks_rec.parent_task_ver_id;
2097
2098 IF (l_debug_mode = 'Y') THEN
2099 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling Rollup_From_Subproject to rollup from the current project id='||
2100 cur_select_hier_rec.object_id_to2||' to its parent project id='||get_str_ver_ic_lnk_tasks_rec.parent_proj_id,3);
2101 END IF;
2102
2103 PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject(
2104 p_api_version => p_api_version
2105 ,p_init_msg_list => p_init_msg_list
2106 ,p_commit => p_commit
2107 ,p_validate_only => p_validate_only
2108 ,p_validation_level => p_validation_level
2109 ,p_calling_module => p_calling_module
2110 ,p_debug_mode => p_debug_mode
2111 ,p_max_msg_count => p_max_msg_count
2112 ,p_element_versions => l_parent_task_ver_id_tbl
2113 ,x_return_status => x_return_status
2114 ,x_msg_count => x_msg_count
2115 ,x_msg_data => x_msg_data);
2116 IF (l_debug_mode = 'Y') THEN
2117 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','After calling Rollup_From_Subproject to rollup from the current project id='||
2118 cur_select_hier_rec.object_id_to2||' to its parent project id='||get_str_ver_ic_lnk_tasks_rec.parent_proj_id||' x_return_status='||x_return_status,3);
2119 END IF;
2120
2121 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2122 x_msg_count := FND_MSG_PUB.count_msg;
2123 if x_msg_count = 1 then
2124 x_msg_data := x_msg_data;
2125 end if;
2126 raise FND_API.G_EXC_ERROR;
2127 end if;
2128
2129 -- END IF;--end if for checking published ver exists or not
2130 END IF;--end if for checking veriosning enabled or not
2131 END LOOP; --End loop for get_str_ver_ic_lnk_tasks cursor
2132 CLOSE get_str_ver_ic_lnk_tasks;
2133 ----bug 4541039
2134 END IF; --l_rollup_from_sub_project = 'Y'
2135 END LOOP;
2136 ----bug 4541039
2137
2138 x_return_status := FND_API.G_RET_STS_SUCCESS;
2139 --
2140 EXCEPTION
2141 WHEN FND_API.G_EXC_ERROR THEN
2142 IF (p_commit = FND_API.G_TRUE) THEN
2143 ROLLBACK to TASKS_ROLLUP_PVT;
2144 END IF;
2145 x_msg_count := FND_MSG_PUB.count_msg;
2146 x_return_status := FND_API.G_RET_STS_ERROR;
2147 WHEN OTHERS THEN
2148 IF (p_commit = FND_API.G_TRUE) THEN
2149 ROLLBACK to TASKS_ROLLUP_PVT;
2150 END IF;
2151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2152 x_msg_count := FND_MSG_PUB.count_msg;
2153 --put message
2154 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STRUCT_TASK_ROLLUP_PUB',
2155 p_procedure_name => 'Tasks_Rollup',
2156 p_error_text => SUBSTRB(SQLERRM,1,240));
2157 RAISE;
2158
2159 end Tasks_Rollup;
2160
2161 Procedure Task_Status_Rollup(
2162 p_api_version IN NUMBER := 1.0
2163 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2164 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2165 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2166 ,p_validation_level IN VARCHAR2 := 100
2167 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2168 ,p_debug_mode IN VARCHAR2 := 'N'
2169 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2170 ,p_structure_version_id IN NUMBER
2171 ,p_element_version_id IN NUMBER := NULL
2172 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2173 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2174 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2175 )
2176 IS
2177 --1 for whole structure; 2 for a branch
2178 CURSOR get_task_version_id(c_input NUMBER) IS
2179 select a.proj_element_id, a.object_type, b.wbs_level,
2180 decode(d.object_type, 'PA_STRUCTURES', NULL, d.proj_element_id) object_id_from1, decode(d.object_type, 'PA_STRUCTURES', NULL, d.object_type) object_type_from, c.PROJECT_STATUS_WEIGHT,
2181 a.status_code
2182 from pa_proj_elements a,
2183 pa_proj_element_versions b,
2184 pa_project_statuses c,
2185 pa_proj_element_versions d,
2186 pa_object_relationships e
2187 where a.project_id = b.project_id
2188 and a.proj_element_id = b.proj_element_id
2189 and a.status_code = c.project_status_code
2190 and b.element_version_id = e.object_id_to1
2191 and b.object_type = e.object_type_to
2192 and d.element_version_id = e.object_id_from1
2193 and d.object_type = e.object_type_from
2194 and e.relationship_type = 'S'
2195 and b.element_version_id IN
2196 ( select object_id_to1
2197 from pa_object_relationships
2198 where relationship_type = 'S'
2199 and 1 = c_input
2200 start with object_id_from1 = p_structure_version_id
2201 and object_type_from = 'PA_STRUCTURES'
2202 and relationship_type = 'S'
2203 connect by prior object_id_to1 = object_id_from1
2204 AND RELATIONSHIP_TYPE = prior relationship_type
2205 and prior object_type_to = object_type_from
2206 UNION
2207 select object_id_to1
2208 from pa_object_relationships
2209 where relationship_type = 'S'
2210 and 2 = c_input
2211 and object_id_from1 IN
2212 ( select object_id_from1
2213 from pa_object_relationships
2214 where relationship_type = 'S'
2215 start with object_id_to1 = p_element_version_id
2216 and object_type_to = 'PA_TASKS'
2217 and relationship_type = 'S'
2218 connect by object_id_to1 = prior object_id_from1
2219 AND RELATIONSHIP_TYPE = prior relationship_type
2220 and object_type_to = prior object_type_from
2221 )
2222 );
2223
2224 CURSOR get_task_status IS
2225 select a.project_status_code, a.project_status_weight
2226 from pa_project_statuses a
2227 where a.predefined_flag = 'Y'
2228 and a.STATUS_TYPE = 'TASK';
2229 l_temp_status_code pa_project_statuses.project_status_code%TYPE;
2230 l_temp_status_weight pa_project_statuses.project_status_weight%TYPE;
2231 l_final_status_code pa_project_statuses.project_status_code%TYPE;
2232 l_final_status_weight pa_project_statuses.project_status_weight%TYPE;
2233
2234 CURSOR check_completed_ok(c_element_id NUMBER) IS
2235 select 1
2236 from pa_object_relationships rel,
2237 pa_proj_element_versions a,
2238 pa_proj_element_versions b,
2239 pa_proj_elements ppe,
2240 pa_project_statuses pps
2241 where a.proj_element_id = c_element_id
2242 and a.parent_structure_version_id = p_structure_version_id
2243 and a.element_version_id = rel.object_id_from1
2244 and a.object_type = rel.object_type_from
2245 and b.element_version_id = rel.object_id_to1
2246 and b.object_type = rel.object_type_to
2247 and rel.relationship_type = 'S'
2248 and ppe.proj_element_id = b.proj_element_id
2249 and b.project_id = ppe.project_id
2250 and ppe.status_code = pps.project_status_code
2251 and pps.project_system_status_code NOT IN ('COMPLETED', 'CANCELLED', 'ON_HOLD');
2252
2253 l_process_number NUMBER;
2254 l_temp NUMBER;
2255
2256 l_option NUMBER;
2257 l_get_task_ver_id_rec get_task_version_id%ROWTYPE;
2258 l_rollup_table PA_SCHEDULE_OBJECTS_PVT.PA_SCHEDULE_OBJECTS_TBL_TYPE;
2259 l_cnt NUMBER;
2260
2261 TYPE t_status_code IS TABLE OF pa_proj_elements.status_code%TYPE
2262 INDEX BY BINARY_INTEGER;
2263 l_status_codes t_status_code;
2264
2265 --hsiu: bug 2800553: added for performance improvement
2266 l_partial_flag VARCHAR2(1);
2267 BEGIN
2268 IF (p_debug_mode = 'Y') THEN
2269 pa_debug.debug('PVT.TASK_STATUS_ROLLUP BEGIN');
2270 END IF;
2271
2272 IF (p_commit = FND_API.G_TRUE) THEN
2273 savepoint TASK_STATUS_ROLLUP_PUB;
2274 END IF;
2275
2276 IF (p_debug_mode = 'Y') THEN
2277 pa_debug.debug('Performing validations');
2278 END IF;
2279
2280 --begin rollup
2281 --check if task id is available. If not, rollup the whole structure
2282 IF (p_element_version_id IS NULL) THEN
2283 l_option := 1;
2284 ELSE
2285 l_option := 2;
2286 END IF;
2287
2288 l_cnt := 0;
2289 OPEN get_task_version_id(l_option);
2290 LOOP
2291 l_cnt := l_cnt + 1;
2292 FETCH get_task_version_id into l_get_task_ver_id_rec;
2293 EXIT WHEN get_task_version_id%NOTFOUND;
2294
2295 --populate rollup table
2296 l_rollup_table(l_cnt).object_id := l_get_task_ver_id_rec.proj_element_id;
2297 l_rollup_table(l_cnt).object_type := l_get_task_ver_id_rec.object_type;
2298 l_rollup_table(l_cnt).wbs_level := l_get_task_ver_id_rec.wbs_level;
2299 l_rollup_table(l_cnt).parent_object_id := l_get_task_ver_id_rec.object_id_from1;
2300 l_rollup_table(l_cnt).parent_object_type := l_get_task_ver_id_rec.object_type_from;
2301 -- hsiu: bug 2800553: commented out for performance improvement
2302 -- l_rollup_table(l_cnt).dirty_flag1 := 'Y';
2303 -- hsiu: bug 2800553: added for performance improvement
2304 IF (p_element_version_id IS NULL) THEN
2305 l_rollup_table(l_cnt).dirty_flag1 := 'Y';
2306 ELSIF (p_element_version_id = l_rollup_table(l_cnt).object_id) THEN
2307 l_rollup_table(l_cnt).dirty_flag1 := 'Y';
2308 ELSE
2309 l_rollup_table(l_cnt).dirty_flag1 := 'N';
2310 END IF;
2311 -- end performance changes
2312 l_rollup_table(l_cnt).TASK_STATUS1 := l_get_task_ver_id_rec.PROJECT_STATUS_WEIGHT;
2313 l_rollup_table(l_cnt).TASK_STATUS2 := l_get_task_ver_id_rec.PROJECT_STATUS_WEIGHT;
2314 END LOOP;
2315 CLOSE get_task_version_id;
2316
2317 -- hsiu: bug 2800553: added for performance improvement
2318 IF (p_element_version_id IS NULL) THEN
2319 l_partial_flag := 'N';
2320 ELSE
2321 l_partial_flag := 'Y';
2322 END IF;
2323
2324 PA_SCHEDULE_OBJECTS_PVT.GENERATE_SCHEDULE(
2325 p_debug_mode => 'N',
2326 p_data_structure => l_rollup_table,
2327 x_return_status => x_return_status,
2328 x_msg_count => x_msg_count,
2329 x_msg_data => x_msg_data,
2330 x_process_number => l_process_number,
2331 p_process_flag1 => 'Y',
2332 p_process_rollup_flag1 => 'Y',
2333 p_process_task_status_flag1 => 'Y',
2334 p_partial_progress_flag1 => l_partial_flag,
2335 p_process_flag2 => 'N',
2336 p_process_rollup_flag2 => 'N',
2337 p_process_progress_flag2 => 'N');
2338
2339 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2340 RAISE FND_API.G_EXC_ERROR;
2341 END IF;
2342
2343 --getting task status
2344 OPEN get_task_status;
2345 LOOP
2346 FETCH get_task_status into l_temp_status_code, l_temp_status_weight;
2347 EXIT WHEN get_task_status%NOTFOUND;
2348 l_status_codes(l_temp_status_weight) := l_temp_status_code;
2349 END LOOP;
2350 CLOSE get_task_status;
2351
2352 --update tasks if status has been modified
2353 l_cnt := l_rollup_table.First;
2354 IF (l_cnt IS NOT NULL) THEN
2355 LOOP
2356 IF (l_rollup_table(l_cnt).TASK_STATUS1 IS NULL) THEN
2357 l_final_status_weight := l_rollup_table(l_cnt).TASK_STATUS2;
2358 l_final_status_code := l_status_codes(l_rollup_table(l_cnt).TASK_STATUS2);
2359 ELSE
2360 l_final_status_weight := l_rollup_table(l_cnt).TASK_STATUS1;
2361 l_final_status_code := l_status_codes(l_rollup_table(l_cnt).TASK_STATUS1);
2362 END IF;
2363
2364 --hsiu: removed due to rollup api changes
2365 -- IF (l_final_status_weight = 20) THEN
2366 --status is completed; check if all child are completed
2367 --and cancelled
2368 -- OPEN check_completed_ok(l_rollup_table(l_cnt).object_id);
2369 -- FETCH check_completed_ok into l_temp;
2370 -- IF check_completed_ok%FOUND THEN
2371 -- l_final_status_code := l_status_codes(30);
2372 -- l_final_status_weight := 30;
2373 -- END IF;
2374 -- CLOSE check_completed_ok;
2375 -- END IF;
2376
2377 --check if status has been modified
2378 IF (l_final_status_weight <> l_rollup_table(l_cnt).TASK_STATUS2) THEN
2379 UPDATE PA_PROJ_ELEMENTS
2380 set status_code = l_final_status_code,
2381 RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
2382 LAST_UPDATE_DATE = SYSDATE,
2383 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2384 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
2385 where proj_element_id = l_rollup_table(l_cnt).object_id;
2386 END IF;
2387
2388 EXIT when l_cnt = l_rollup_table.LAST;
2389 l_cnt := l_rollup_table.NEXT(l_cnt);
2390
2391 END LOOP;
2392 END IF;
2393
2394 x_return_status := FND_API.G_RET_STS_SUCCESS;
2395 EXCEPTION
2396 WHEN FND_API.G_EXC_ERROR THEN
2397 IF (p_commit = FND_API.G_TRUE) THEN
2398 ROLLBACK to TASK_STATUS_ROLLUP_PUB;
2399 END IF;
2400 x_msg_count := FND_MSG_PUB.count_msg;
2401 x_return_status := FND_API.G_RET_STS_ERROR;
2402 WHEN OTHERS THEN
2403 IF (p_commit = FND_API.G_TRUE) THEN
2404 ROLLBACK to TASK_STATUS_ROLLUP_PUB;
2405 END IF;
2406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2407 x_msg_count := FND_MSG_PUB.count_msg;
2408 --put message
2409 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STRUCT_TASK_ROLLUP_PUB',
2410 p_procedure_name => 'Task_Status_Rollup',
2411 p_error_text => SUBSTRB(SQLERRM,1,240));
2412 RAISE;
2413 END Task_Status_Rollup;
2414
2415
2416 Procedure Task_Stat_Pushdown_Rollup(
2417 p_api_version IN NUMBER := 1.0
2418 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2419 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2420 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2421 ,p_validation_level IN VARCHAR2 := 100
2422 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2423 ,p_debug_mode IN VARCHAR2 := 'N'
2424 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2425 ,p_structure_version_id IN NUMBER
2426 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2427 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2428 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2429 )
2430 IS
2431 CURSOR get_zero_weight_status IS
2432 select a.project_id, a.proj_element_id,
2433 b.element_version_id, a.status_code
2434 from pa_proj_elements a,
2435 pa_proj_element_versions b,
2436 pa_project_statuses c
2437 where a.project_id = b.project_id
2438 and a.proj_element_id = b.proj_element_id
2439 and b.parent_structure_version_id = p_structure_version_id
2440 and c.project_status_code = a.status_code
2441 and c.project_status_weight = 0
2442 and c.status_type = 'TASK';
2443 l_zero_weight_rec get_zero_weight_status%ROWTYPE;
2444 BEGIN
2445 IF (p_debug_mode = 'Y') THEN
2446 pa_debug.debug('PVT.TASK_STATUS_ROLLUP BEGIN');
2447 END IF;
2448
2449 IF (p_commit = FND_API.G_TRUE) THEN
2450 savepoint TASK_STAT_PUSHDOWN_ROLLUP;
2451 END IF;
2452
2453 IF (p_debug_mode = 'Y') THEN
2454 pa_debug.debug('Performing validations');
2455 END IF;
2456
2457 --push down all cancelled and on-hold statuses
2458 OPEN get_zero_weight_status;
2459 LOOP
2460 FETCH get_zero_weight_status into l_zero_weight_rec;
2461 EXIT WHEN get_zero_weight_status%NOTFOUND;
2462
2463 PA_PROGRESS_PUB.PUSH_DOWN_TASK_STATUS(
2464 p_validate_only => FND_API.G_FALSE
2465 ,p_task_status => l_zero_weight_rec.status_code
2466 ,p_project_id => l_zero_weight_rec.project_id
2467 ,p_object_id => l_zero_weight_rec.proj_element_id
2468 ,p_object_version_id => l_zero_weight_rec.element_version_id
2469 ,p_object_type => 'PA_TASKS'
2470 ,x_return_status => x_return_status
2471 ,x_msg_count => x_msg_count
2472 ,x_msg_data => x_msg_data
2473 );
2474
2475 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2476 RAISE FND_API.G_EXC_ERROR;
2477 END IF;
2478
2479 END LOOP;
2480 CLOSE get_zero_weight_status;
2481
2482 --done with push down. Now Rollup
2483 PA_STRUCT_TASK_ROLLUP_PUB.Task_Status_Rollup(
2484 p_structure_version_id => p_structure_version_id
2485 ,x_return_status => x_return_status
2486 ,x_msg_count => x_msg_count
2487 ,x_msg_data => x_msg_data);
2488
2489 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2490 RAISE FND_API.G_EXC_ERROR;
2491 END IF;
2492
2493 x_return_status := FND_API.G_RET_STS_SUCCESS;
2494 EXCEPTION
2495 WHEN FND_API.G_EXC_ERROR THEN
2496 IF (p_commit = FND_API.G_TRUE) THEN
2497 ROLLBACK to TASK_STAT_PUSHDOWN_ROLLUP;
2498 END IF;
2499 x_msg_count := FND_MSG_PUB.count_msg;
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501 WHEN OTHERS THEN
2502 IF (p_commit = FND_API.G_TRUE) THEN
2503 ROLLBACK to TASK_STAT_PUSHDOWN_ROLLUP;
2504 END IF;
2505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2506 x_msg_count := FND_MSG_PUB.count_msg;
2507 --put message
2508 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STRUCT_TASK_ROLLUP_PUB',
2509 p_procedure_name => 'Task_Stat_Pushdown_Rollup',
2510 p_error_text => SUBSTRB(SQLERRM,1,240));
2511 RAISE;
2512 END Task_Stat_Pushdown_Rollup;
2513
2514
2515 -- API name : Program_Schedule_dates_rollup
2516 -- Type : Public procedure
2517 -- Pre-reqs : None
2518 -- Return Value : N/A
2519 -- Prameters
2520 -- p_api_version IN NUMBER := 1.0
2521 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2522 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2523 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2524 -- p_validation_level IN VARCHAR2 := 100
2525 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2526 -- p_debug_mode IN VARCHAR2 := 'N'
2527 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2528 -- p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2529 -- x_return_status OUT VARCHAR2
2530 -- x_msg_count OUT NUMBER
2531 -- x_msg_data OUT VARCHAR2
2532 --
2533 -- DESCRIPTION:
2534 -- This API is created to call rollup_from_subporject api. The API has 2 modes.
2535 --1) If project id is passed and no structure version id is passed then the api
2536 --calls rollup_from_subproject api for every single project in the hierarchy starting
2537 --from projects at the lowest level.
2538 --2) if structure version id and project id both are passed then the api calls rollup_from_subproject
2539 --api only for that project.
2540 --This api is called from publishing flow in both modes.
2541 -- Bug 4541039
2542
2543 PROCEDURE Program_Schedule_dates_rollup(
2544 p_api_version IN NUMBER := 1.0
2545 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2546 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2547 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2548 ,p_validation_level IN VARCHAR2 := 100
2549 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2550 ,p_debug_mode IN VARCHAR2 := 'N'
2551 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2552 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2553 ,p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2554 ,p_published_str_ver_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2555 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2556 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2557 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2558 ) IS
2559 CURSOR cur_top_task(c_structure_version_id NUMBER)
2560 IS
2561 SELECT object_id_to1
2562 FROM pa_object_relationships
2563 WHERE object_id_from1 = c_structure_version_id
2564 AND relationship_type = 'S'
2565 AND object_type_from = 'PA_STRUCTURES'
2566 AND object_type_to = 'PA_TASKS'
2567 ;
2568 -- Bug 6854670 (Changed type from PA_NUM_1000_NUM to SYSTEM.PA_NUM_TBL_TYPE)
2569 l_tasks_ver_ids SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2570 l_top_task_version_id NUMBER;
2571 l_debug_mode VARCHAR2(1);
2572
2573 --select all the projects belonging to the same program group as p_project_id.
2574 CURSOR cur_select_grid
2575 IS
2576 --select working verisons from versioned projects
2577 SELECT a.project_id, a.element_version_id, a.prg_level
2578 FROM pa_proj_element_versions a,
2579 pa_proj_elem_ver_structure b,
2580 pa_proj_workplan_attr ppwa,
2581 pa_proj_structure_types ppst,
2582 pa_proj_element_versions c
2583 WHERE
2584 c.project_id = p_project_id
2585 AND c.prg_group = a.prg_group
2586 AND a.project_id = b.project_id
2587 AND a.element_version_id = b.element_version_id
2588 AND b.status_code = 'STRUCTURE_WORKING'
2589 AND ppwa.wp_enable_version_flag = 'Y'
2590 AND a.project_id = ppwa.project_id
2591 AND a.proj_element_id = ppwa.proj_element_id
2592 AND a.proj_element_id = ppst.proj_element_id
2593 AND ppst.structure_type_id =1
2594 UNION
2595 --select published verisons from non-versioned projects
2596 SELECT a.project_id, a.element_version_id, a.prg_level
2597 FROM pa_proj_element_versions a,
2598 pa_proj_elem_ver_structure b,
2599 pa_proj_workplan_attr ppwa,
2600 pa_proj_structure_types ppst,
2601 pa_proj_element_versions c
2602 WHERE
2603 c.project_id = p_project_id
2604 AND c.prg_group = a.prg_group
2605 AND a.project_id = b.project_id
2606 AND a.element_version_id = b.element_version_id
2607 AND b.status_code = 'STRUCTURE_PUBLISHED'
2608 AND ppwa.wp_enable_version_flag = 'N'
2609 AND a.project_id = ppwa.project_id
2610 AND a.proj_element_id = ppwa.proj_element_id
2611 AND a.proj_element_id = ppst.proj_element_id
2612 AND ppst.structure_type_id =1
2613 order by 3 desc; --select the lowest level of projects first.
2614
2615 BEGIN
2616
2617 x_msg_count := 0;
2618 x_return_status := FND_API.G_RET_STS_SUCCESS;
2619 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2620
2621 IF (p_debug_mode = 'Y') THEN
2622 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDULE_DATES_ROLLUP','Entered in PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP p_structure_version_id='||p_structure_version_id, 3);
2623 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDULE_DATES_ROLLUP','Entered in PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP p_project_id='||p_project_id, 3);
2624 END IF;
2625
2626 IF (p_commit = FND_API.G_TRUE) THEN
2627 savepoint Program_Schedule_dates_rollup;
2628 END IF;
2629
2630 IF (p_structure_version_id IS NULL OR p_structure_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2631 AND (p_project_id IS NOT NULL AND p_project_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2632 THEN
2633 IF l_debug_mode = 'Y' THEN
2634 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'Before Opening cursor cur_select_grid', 3);
2635 END IF;
2636
2637 FOR cur_select_grid_rec IN cur_select_grid LOOP
2638
2639 --when rollinup to a working verison which is being published, first rollup to the published version then continue with the working
2640 --verison and up in the hierarchy.
2641 IF p_published_str_ver_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_published_str_ver_id IS NOT NULL
2642 AND p_project_id= cur_select_grid_rec.project_id
2643 THEN
2644
2645 IF l_debug_mode = 'Y' THEN
2646 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'Before calling PA_STRUCT_TASK_ROLLUP_PUB.Program_Schedule_dates_rollup for published version of the project:'||cur_select_grid_rec.project_id, 3);
2647 END IF;
2648
2649 PA_STRUCT_TASK_ROLLUP_PUB.Program_Schedule_dates_rollup(
2650 p_project_id => p_project_id,
2651 p_structure_version_id => p_published_str_ver_id,
2652 p_published_str_ver_id => p_published_str_ver_id, --bug5861729
2653 x_return_status => x_return_status,
2654 x_msg_count => x_msg_count,
2655 x_msg_data => x_msg_data);
2656
2657 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2658 RAISE FND_API.G_EXC_ERROR;
2659 END IF;
2660
2661
2662 IF l_debug_mode = 'Y' THEN
2663 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'After calling PA_STRUCT_TASK_ROLLUP_PUB.Program_Schedule_dates_rollup for published version of the project:'||
2664 cur_select_grid_rec.project_id||' x_return_status='||x_return_status, 3);
2665 END IF;
2666
2667 END IF;
2668
2669 OPEN cur_top_task(cur_select_grid_rec.element_version_id);
2670 FETCH cur_top_task BULK COLLECT INTO l_tasks_ver_ids;
2671 CLOSE cur_top_task;
2672
2673 IF l_tasks_ver_ids.count > 0 THEN
2674
2675 IF l_debug_mode = 'Y' THEN
2676 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'Before calling PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd for project'||cur_select_grid_rec.project_id, 3);
2677 END IF;
2678
2679 -- Bug 6854670: Changed call to Rollup_From_Subproject_Unltd
2680 PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd(
2681 p_api_version => p_api_version
2682 ,p_init_msg_list => p_init_msg_list
2683 ,p_commit => p_commit
2684 ,p_validate_only => p_validate_only
2685 ,p_validation_level => p_validation_level
2686 ,p_calling_module => p_calling_module
2687 ,p_debug_mode => p_debug_mode
2688 ,p_max_msg_count => p_max_msg_count
2689 ,p_element_versions => l_tasks_ver_ids
2690 ,p_published_str_ver_id => p_published_str_ver_id --bug5861729
2691 ,x_return_status => x_return_status
2692 ,x_msg_count => x_msg_count
2693 ,x_msg_data => x_msg_data);
2694
2695
2696 IF l_debug_mode = 'Y' THEN
2697 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'After calling PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd x_return_status='||x_return_status, 3);
2698 END IF;
2699
2700 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2701 RAISE FND_API.G_EXC_ERROR;
2702 END IF;
2703 END IF;
2704 END LOOP;
2705 ELSIF (p_structure_version_id IS NOT NULL AND p_structure_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) --bug5861729
2706 THEN
2707
2708 OPEN cur_top_task(p_structure_version_id);
2709 FETCH cur_top_task BULK COLLECT INTO l_tasks_ver_ids;
2710 CLOSE cur_top_task;
2711
2712 IF l_tasks_ver_ids.count > 0 THEN
2713
2714 IF l_debug_mode = 'Y' THEN
2715 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'Before calling PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 3);
2716 END IF;
2717
2718 -- Bug 6854670: Changed call to Rollup_From_Subproject_Unltd
2719 PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd(
2720 p_api_version => p_api_version
2721 ,p_init_msg_list => p_init_msg_list
2722 ,p_commit => p_commit
2723 ,p_validate_only => p_validate_only
2724 ,p_validation_level => p_validation_level
2725 ,p_calling_module => p_calling_module
2726 ,p_debug_mode => p_debug_mode
2727 ,p_max_msg_count => p_max_msg_count
2728 ,p_element_versions => l_tasks_ver_ids
2729 ,p_published_str_ver_id => p_published_str_ver_id --bug5861729
2730 ,x_return_status => x_return_status
2731 ,x_msg_count => x_msg_count
2732 ,x_msg_data => x_msg_data);
2733
2734
2735 IF l_debug_mode = 'Y' THEN
2736 pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'After calling PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd x_return_status='||x_return_status, 3);
2737 END IF;
2738
2739 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2740 RAISE FND_API.G_EXC_ERROR;
2741 END IF;
2742 END IF;
2743
2744 END IF; --if p_structure_verison is not null
2745
2746 x_return_status := FND_API.G_RET_STS_SUCCESS;
2747 EXCEPTION
2748 WHEN FND_API.G_EXC_ERROR THEN
2749 IF (p_commit = FND_API.G_TRUE) THEN
2750 ROLLBACK to Program_Schedule_dates_rollup;
2751 END IF;
2752 x_msg_count := FND_MSG_PUB.count_msg;
2753 x_return_status := FND_API.G_RET_STS_ERROR;
2754 WHEN OTHERS THEN
2755 IF (p_commit = FND_API.G_TRUE) THEN
2756 ROLLBACK to Program_Schedule_dates_rollup;
2757 END IF;
2758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2759 x_msg_count := FND_MSG_PUB.count_msg;
2760 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STRUCT_TASK_ROLLUP_PUB',
2761 p_procedure_name => 'Program_Schedule_dates_rollup',
2762 p_error_text => SUBSTRB(SQLERRM,1,240));
2763 RAISE;
2764
2765 END Program_Schedule_dates_rollup;
2766
2767 END PA_STRUCT_TASK_ROLLUP_PUB;