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