[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT template_flag, record_version_number
FROM pa_projects_all
WHERE project_id = c_project_id;
SELECT decode(project_system_status_code, 'CANCELLED', 'N', 'Y')
FROM pa_proj_elements a, pa_proj_element_versions b,
pa_project_statuses c
where a.proj_element_id = b.proj_element_id
and a.project_id = b.project_id
and b.element_version_id = c_element_version_id
and a.status_code = c.project_status_code
and c.status_type = 'TASK';
SELECT ppe.proj_element_id,
ppev.element_version_id lnk_task_ver_id,
ppev.project_id lnk_task_project_id
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe,
pa_proj_elem_ver_schedule pevs,
--bug 4541039
pa_object_relationships por,
pa_proj_elem_ver_structure pevst
--bug 4541039
WHERE ppe.project_id = ppev.project_id
AND ppe.proj_element_id = ppev.proj_element_id
AND ppev.object_type = 'PA_TASKS'
AND ppe.object_type = 'PA_TASKS'
AND ppe.link_Task_flag = 'Y'
AND ppev.project_id = pevs.project_id
AND ppev.proj_element_id = pevs.proj_element_id
AND ppev.element_version_id = pevs.element_version_id
--bug 4541039 rollup only from the published versions
and por.object_id_from1 = ppev.element_version_id
and pevst.element_version_id = por.object_id_to1
and pevst.status_code = 'STRUCTURE_PUBLISHED'
and pevst.project_id = por.object_id_to2
--bug 4541039
AND ppev.element_version_id IN (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_from1 = cp_task_version_id
AND object_type_from IN ('PA_TASKS','PA_STRUCTURES')
AND relationship_type = 'S'
CONNECT BY object_id_from1 = prior object_id_to1
AND RELATIONSHIP_TYPE = prior relationship_type
AND object_type_from = prior object_type_to);
Select object_id_from1 lnk_task_ver_id,object_id_to1 struct_version_id,
object_id_from2 lnk_proj_id_from,object_id_to2 lnk_proj_id_to --Bug 3634389
FROM pa_object_relationships
WHERE object_id_from1 = cp_lnk_task_version_id
AND relationship_type = 'LW'
AND object_id_from2 <> object_id_to2
AND object_type_from = 'PA_TASKS' --Bug 3634389
AND object_type_to = 'PA_STRUCTURES'; --Bug 3634389
SELECT min(scheduled_start_date) lnk_task_sch_start_Dt
FROM (SELECT min(b.scheduled_start_date) scheduled_start_date --bug 3967855
FROM pa_proj_element_versions a,
pa_proj_elem_ver_schedule b
WHERE a.project_id = b.project_id
AND a.element_version_id = b.element_version_id
AND b.project_id = cp_lnk_proj_id_from --Bug 3634389 Added for performance
AND b.element_version_id IN (SELECT object_id_from1
FROM pa_object_relationships pora
WHERE object_id_from1 = cp_lnk_task_version_id
AND relationship_type = 'S'
AND object_id_from2 <> object_id_to2
AND object_type_from IN ('PA_STRUCTURES','PA_TASKS') --Bug 3634389
AND object_type_to = 'PA_TASKS')
-- AND object_type_from = 'PA_STRUCTURES') --Bug 3634389
UNION ALL
SELECT min(d.scheduled_start_date) scheduled_start_date --bug 3967855
FROM pa_proj_element_versions c,
pa_proj_elem_ver_schedule d
,pa_proj_elem_ver_structure e --bug 4541039
WHERE c.project_id = d.project_id
AND d.project_id =e.project_id --Bug#6277752 Added for performance
AND d.element_version_id = e.element_version_id --Bug#6277752 Added for performance
AND d.project_id = cp_lnk_proj_id_to --Bug 3634389 Added for performance
--make sure that the rollup is happeningonly from published version of the sub-project bug 4541039
AND e.element_version_id = c.element_version_id
AND e.project_id = c.project_id -- Bug # 4868867.
AND e.status_code = 'STRUCTURE_PUBLISHED'
--end bug 4541039
AND c.element_version_id IN (SELECT object_id_to1
FROM pa_object_relationships
WHERE object_id_from1 = cp_lnk_task_version_id
AND relationship_type = 'LW'
AND object_id_from2 <> object_id_to2
AND object_type_from = 'PA_TASKS' --Bug 3634389
AND object_type_to = 'PA_STRUCTURES')); --Bug 3634389
SELECT max(scheduled_finish_date) lnk_task_sch_finish_Dt
FROM (SELECT max(b.scheduled_finish_date) scheduled_finish_date --bug 3967855
FROM pa_proj_element_versions a,
pa_proj_elem_ver_schedule b
WHERE a.project_id = b.project_id
AND a.element_version_id = b.element_version_id
AND b.project_id = cp_lnk_proj_id_from --Bug 3634389 Added for performance
AND b.element_version_id IN (SELECT object_id_from1
FROM pa_object_relationships pora
WHERE object_id_from1 = cp_lnk_task_version_id
AND relationship_type = 'S'
AND object_id_from2 <> object_id_to2
AND object_type_from IN ('PA_STRUCTURES','PA_TASKS') --Bug 3634389
AND object_type_to = 'PA_TASKS')
-- AND object_type_from = 'PA_STRUCTURES') --Bug 3634389
UNION ALL
SELECT max(d.scheduled_finish_date) scheduled_finish_date --bug 3967855
FROM pa_proj_element_versions c,
pa_proj_elem_ver_schedule d
,pa_proj_elem_ver_structure e --bug 4541039
WHERE c.project_id = d.project_id
AND d.project_id =e.project_id --Bug#6277752 Added for performance
AND d.element_version_id = e.element_version_id --Bug#6277752 Added for performance
AND d.project_id = cp_lnk_proj_id_to --Bug 3634389 Added for performance
--make sure that the rollup is happeningonly from published version of the sub-project bug 4541039
AND e.element_version_id = c.element_version_id
and e.project_id = c.project_id -- Bug # 4868867.
AND e.status_code = 'STRUCTURE_PUBLISHED'
--end bug 4541039
AND c.element_version_id IN (SELECT object_id_to1
FROM pa_object_relationships
WHERE object_id_from1 = cp_lnk_task_version_id
AND relationship_type = 'LW'
AND object_id_from2 <> object_id_to2
AND object_type_from = 'PA_TASKS' --Bug 3634389
AND object_type_to = 'PA_STRUCTURES')); --Bug 3634389
SELECT a.scheduled_start_date, a.scheduled_finish_date
from pa_proj_elem_ver_schedule a
where a.element_version_id = c_element_version_id;
select scheduled_start_date, scheduled_finish_date
from pa_proj_elem_ver_schedule
where project_id = c_project_id
and element_version_id = c_element_version_id;
SELECT project_id, proj_element_id
FROM pa_proj_element_versions
where element_version_id = c_structure_ver_id;
UPDATE pa_proj_elem_ver_schedule
SET scheduled_start_date = l_parent_start_date
,scheduled_finish_date = l_parent_finish_date
,record_version_number = NVL( record_version_number, 0 ) + 1
WHERE element_version_id = get_lnk_task_rec.lnk_task_ver_id
AND project_id = l_lnk_task_project_id --Bug 3634389
;
' SELECT a.project_id, a.element_version_id,'||
' a.object_type, b.PLANNED_EFFORT, '||
' NVL(a.wbs_level,0), b.scheduled_start_date+NVL(b.scheduled_start_date_rollup,0), '||
' b.scheduled_finish_date+NVL(b.scheduled_finish_date_rollup,0), c.object_id_from1, '||
' c.object_type_from FROM '||
' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
' pa_object_relationships c, pa_proj_elements d WHERE '||
' a.element_version_id = c.object_id_to1(+) AND '||
' c.relationship_type(+)= '||''''||'S'||''''||' AND '||
' a.project_id = b.project_id AND '||
' a.element_version_id = b.element_version_id AND '||
' a.proj_element_id = d.proj_element_id AND '||
-- ' d.link_task_flag = '||''''||'N'||''''||' and '||
' a.element_version_id IN ('||
' SELECT object_id_from1 FROM '||
' pa_object_relationships CONNECT BY '||
' PRIOR object_id_from1 = object_id_to1 '||
' AND RELATIONSHIP_TYPE = prior relationship_type '||
' AND relationship_type = '||''''||'S'||''''||
' START WITH ('||
l_sql1||
') ' ||
' and relationship_type = '||''''||'S'||''''||
') UNION '||
' SELECT distinct a.project_id, a.element_version_id, '||
' a.object_type, b.PLANNED_EFFORT, '||
' nvl(a.wbs_level,0), b.scheduled_start_date+NVL(b.scheduled_start_date_rollup,0), '||
' b.scheduled_finish_date+NVL(b.scheduled_finish_date_rollup,0), c.object_id_from1, '||
' c.object_type_from FROM '||
' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
' pa_object_relationships c, pa_proj_elements d WHERE '||
' a.element_version_id = c.object_id_to1 AND '||
' c.relationship_type = '||''''||'S'||''''||' AND '||
' a.project_id = b.project_id AND '||
' a.element_version_id = b.element_version_id AND '||
' a.proj_element_id = d.proj_element_id AND '|| -- 3305199
-- ' a.element_version_id = d.proj_element_id AND '||
--' d.link_task_flag = '||''''||'N'||''''||' AND '||
' c.object_id_from1 IN ('||
' select object_id_from1 FROM '||
' pa_object_relationships CONNECT BY '||
' PRIOR object_id_from1 = object_id_to1 '||
' AND RELATIONSHIP_TYPE = prior relationship_type '||
' AND relationship_type = '||''''||'S'||''''||
' START WITH ('||
l_sql1||
')' ||
' AND relationship_type = '||''''||'S'||''''||
')';
SELECT scheduled_start_date, scheduled_finish_date
into l_old_sch_st_date_tbl(i), l_old_sch_fn_date_tbl(i)
FROM pa_proj_elem_ver_schedule
WHERE project_id = l_proj_id_tbl(i)
AND element_version_id = l_elem_ver_id_tbl(i);
UPDATE pa_proj_elem_ver_schedule
SET scheduled_start_date = l_sch_start_date_tbl(i),
scheduled_finish_date = l_sch_finish_date_tbl(i),
planned_effort = l_effort_tbl(i),
duration = l_sch_finish_date_tbl(i) - l_sch_start_date_tbl(i) + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_proj_id_tbl(i)
AND element_version_id = l_elem_ver_id_tbl(i);
l_assgn_context := 'UPDATE';
l_assgn_context := 'INSERT_VALUES';
SELECT scheduled_start_date into l_old_sch_st_date_tbl(i)
FROM pa_proj_elem_ver_schedule
WHERE project_id = l_proj_id_tbl(i)
AND element_version_id = l_elem_ver_id_tbl(i);
UPDATE pa_proj_elem_ver_schedule
SET scheduled_start_date_rollup = l_sch_start_date_tbl(i) - scheduled_start_date,
scheduled_finish_date_rollup = l_sch_finish_date_tbl(i) - scheduled_finish_date,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_proj_id_tbl(i)
AND element_version_id = l_elem_ver_id_tbl(i);
pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject_Unltd', 'Before calling PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES', 3);
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => l_proj_id_tbl(i)
,p_date_type => 'SCHEDULED'
,p_start_date => l_get_sch_dates_cur.scheduled_start_date
,p_finish_date => l_get_sch_dates_cur.scheduled_finish_date
,p_record_version_number => l_record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
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);
l_element_ver_ids.delete;
l_update_stmt VARCHAR2(32767);
l_RowsUpdated NUMBER;
SELECT START_DATE, COMPLETION_DATE
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = l_project_id;
SELECT template_flag, record_version_number
FROM pa_projects_all
WHERE project_id = c_project_id;
select b.project_id, b.element_version_id
from pa_object_relationships a,
pa_proj_element_versions b
where a.relationship_type = 'L'
and a.object_id_to1 = c_child_element_id
and a.object_type_from = 'PA_TASKS'
and a.object_id_from1 = b.element_version_id
and a.object_type_from = b.object_type;
SELECT calendar_id
FROM pa_proj_elem_ver_schedule
WHERE element_version_id = c_element_version_id
AND project_id = c_project_id;
select scheduled_start_date, scheduled_finish_date
from pa_proj_elem_ver_schedule
where project_id = c_project_id
and element_version_id = c_element_version_id;
SELECT project_id, proj_element_id
FROM pa_proj_element_versions
where element_version_id = c_structure_ver_id;
SELECT PA_PROJ_ELEMENTS_UTILS.CHECK_TASK_STUS_ACTION_ALLOWED(
a.STATUS_CODE, 'PLAN_ROLLUP')
FROM pa_proj_elements a, pa_proj_element_versions b
WHERE a.proj_element_id = b.proj_element_id
AND a.project_id = b.project_id
AND b.element_version_id = c_element_version_id;
SELECT decode(project_system_status_code, 'CANCELLED', 'N', 'Y')
FROM pa_proj_elements a, pa_proj_element_versions b,
pa_project_statuses c
where a.proj_element_id = b.proj_element_id
and a.project_id = b.project_id
and b.element_version_id = c_element_version_id
and a.status_code = c.project_status_code
and c.status_type = 'TASK';
SELECT pora.object_id_from1 parent_lnk_task_ver_id,
pora.object_id_to1 struct_version_id,
pora.object_id_from2 parent_proj_id,
porb.object_id_from1 parent_task_ver_id
FROM pa_object_relationships pora,
pa_object_relationships porb
WHERE pora.object_id_to1 = cp_structure_Version_id
AND pora.RELATIONSHIP_TYPE = 'LW'
AND pora.object_id_from2 <> pora.object_id_to2
AND pora.OBJECT_TYPE_TO = 'PA_STRUCTURES'
AND pora.OBJECT_TYPE_FROM = 'PA_TASKS'
AND pora.object_id_from1 = porb.object_id_to1
AND porb.RELATIONSHIP_TYPE = 'S'
AND porb.OBJECT_TYPE_TO = 'PA_TASKS'
AND porb.OBJECT_TYPE_FROM = 'PA_TASKS';
SELECT 1
FROM pa_proj_elem_ver_structure
WHERE project_id = cp_proj_id
-- AND proj_element_id = p_structure_id
AND element_version_id = ( select parent_structure_version_id
from pa_proj_element_versions where element_version_id=cp_str_ver_id) --bug 4287813
AND status_code <> 'STRUCTURE_PUBLISHED';
SELECT PARENT_STRUCTURE_VERSION_ID,project_id
FROM pa_proj_element_versions
WHERE element_version_id = cp_parent_task_ver_id;
SELECT 'x'
FROM pa_proj_elem_ver_structure
WHERE project_id=cp_project_id
AND element_version_id = cp_str_ver_id
AND status_code = 'STRUCTURE_PUBLISHED'
;
CURSOR cur_select_hier(c_project_id NUMBER, c_structure_version_id NUMBER)
IS
SELECT object_id_from1, object_id_from2, object_id_to1, object_id_to2
FROM pa_object_relationships
START with object_id_to2 = c_project_id and relationship_type = 'LW' and object_id_to1 = c_structure_version_id
CONNECT by object_id_to2 = prior object_id_from2
AND relationship_type = prior relationship_type;
' select a.project_id, a.element_version_id,'||
' a.object_type, b.PLANNED_EFFORT, '||
' nvl(a.wbs_level,0), b.scheduled_start_date, '||
' b.scheduled_finish_date, c.object_id_from1, '||
' c.object_type_from from '||
' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
' pa_object_relationships c, pa_proj_elements d where '||
' a.element_version_id = c.object_id_to1(+) and '||
' c.relationship_type(+)= '||''''||'S'||''''||' and '||
' a.project_id = b.project_id and '||
' a.element_version_id = b.element_version_id and '||
' a.proj_element_id = d.proj_element_id and '|| -- 3305199
' d.link_task_flag = '||''''||'N'||''''||' and '||
' a.element_version_id IN ('||
' select object_id_from1 from '||
' pa_object_relationships connect by '||
' prior object_id_from1 = object_id_to1 '||
' AND RELATIONSHIP_TYPE = prior relationship_type '||
' and relationship_type = '||''''||'S'||''''||
' start with object_id_to1 IN ('||
l_predicate||
') ' ||
' and relationship_type = '||''''||'S'||''''||
') UNION '||
' select distinct a.project_id, a.element_version_id, '||
' a.object_type, b.PLANNED_EFFORT, '||
' nvl(a.wbs_level,0), b.scheduled_start_date, '||
' b.scheduled_finish_date, c.object_id_from1, '||
' c.object_type_from from '||
' pa_proj_element_versions a, pa_proj_elem_ver_schedule b, '||
' pa_object_relationships c, pa_proj_elements d where '||
' a.element_version_id = c.object_id_to1 and '||
' c.relationship_type = '||''''||'S'||''''||' and '||
' a.project_id = b.project_id and '||
' a.element_version_id = b.element_version_id and '||
' a.proj_element_id = d.proj_element_id and '|| -- 3305199
-- ' a.element_version_id = d.proj_element_id and '||
' d.link_task_flag = '||''''||'N'||''''||' and '||
' c.object_id_from1 IN ('||
' select object_id_from1 from '||
' pa_object_relationships connect by '||
' prior object_id_from1 = object_id_to1 '||
' and relationship_type = '||''''||'S'||''''||
' AND RELATIONSHIP_TYPE = prior relationship_type '||
' start with object_id_to1 IN ('||
l_predicate||
')' ||
' and relationship_type = '||''''||'S'||''''||
')';
l_update_stmt := 'Update pa_proj_elem_ver_schedule '||
'set SCHEDULED_START_DATE = :sd, '||
'SCHEDULED_FINISH_DATE = :fd, '||
'PLANNED_EFFORT = :pe, '||
'DURATION = :dur, '||
'RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1, '||
'LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATED_BY = FND_GLOBAL.USER_ID, ' ||
'LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID ' ||
'where '||
'project_id = :pid and element_version_id = :evid';
DBMS_SQL.PARSE(l_CursorId, l_update_stmt, DBMS_SQL.V7);
l_RowsUpdated := DBMS_SQL.EXECUTE(l_CursorId);
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => l_proj_id_tbl(l_index)
,p_date_type => 'SCHEDULED'
,p_start_date => l_rollup_table(l_index).start_date1
,p_finish_date => l_rollup_table(l_index).finish_date1
,p_record_version_number => l_record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
l_RowsUpdated := DBMS_SQL.EXECUTE(l_CursorId);
select scheduled_start_date, scheduled_finish_date
into l_old_sch_st_date_tbl(i), l_old_sch_fn_date_tbl(i)
from pa_proj_elem_ver_schedule
where project_id = l_proj_id_tbl(i)
and element_version_id = l_elem_ver_id_tbl(i);
UPDATE pa_proj_elem_ver_schedule
set scheduled_start_date = l_sch_start_date_tbl(i),
scheduled_finish_date = l_sch_finish_date_tbl(i),
planned_effort = l_effort_tbl(i),
duration = l_sch_finish_date_tbl(i) - l_sch_start_date_tbl(i) + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where project_id = l_proj_id_tbl(i)
and element_version_id = l_elem_ver_id_tbl(i);
select scheduled_start_date, scheduled_finish_date
into l_old_sch_st_date_tbl(i), l_old_sch_fn_date_tbl(i)
from pa_proj_elem_ver_schedule
where project_id = l_proj_id_tbl(i)
and element_version_id = l_elem_ver_id_tbl(i);
UPDATE pa_proj_elem_ver_schedule
set scheduled_start_date =str_start_date,
scheduled_finish_date = str_end_date,
duration = str_end_date - str_start_date + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where project_id = l_baseline_proj_id
and element_version_id = l_structure_version_id;
l_assgn_context := 'UPDATE';
l_assgn_context := 'INSERT_VALUES';
pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.TASKS_ROLLUP','Before calling PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES',3);
PA_PROJECT_DATES_PUB.UPDATE_PROJECT_DATES (
p_validate_only => FND_API.G_FALSE
,p_project_id => l_baseline_proj_id
,p_date_type => 'SCHEDULED'
,p_start_date => l_get_sch_dates_cur.scheduled_start_date
,p_finish_date => l_get_sch_dates_cur.scheduled_finish_date
,p_record_version_number => l_record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
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);
1) Update Task flow
+ schedule dates are getting updated for a non-versioned project. If the sub-project is
+ schedule dates are getting updated on a working verison for a versioned project. The dates from the sub-project
will be rolled up into the parent project.
2) Publish flow
In this flow, the data from sub-project published verison will be rolled up into the new published verison.
The api will also be called for working version if the process WBS updates is already not run.
3) Running Process Updates
In this flow, data from the sub-projects rolls up to this project.
--bug 4296915
OPEN check_pub_str(l_struc_project_id,l_structure_version_id);
FOR cur_select_hier_rec in cur_select_hier( l_struc_project_id, l_structure_version_id ) LOOP ----bug 4541039
--do not rollup from working version except when the current project is getting published.
IF l_struc_project_id <> cur_select_hier_rec.object_id_to2
AND PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(cur_select_hier_rec.object_id_to2) = 'Y'
THEN
l_rollup_from_sub_project := 'N';
OPEN get_str_ver_ic_lnk_tasks(cur_select_hier_rec.object_id_to1); -- bug 4541039. Provide the sub-project structure version.
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.
cur_select_hier_rec.object_id_to2||' to its parent project id='||get_str_ver_ic_lnk_tasks_rec.parent_proj_id,3);
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);
cur_select_hier_rec.object_id_to2||' to its parent project id='||get_str_ver_ic_lnk_tasks_rec.parent_proj_id,3);
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);
select a.proj_element_id, a.object_type, b.wbs_level,
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,
a.status_code
from pa_proj_elements a,
pa_proj_element_versions b,
pa_project_statuses c,
pa_proj_element_versions d,
pa_object_relationships e
where a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and a.status_code = c.project_status_code
and b.element_version_id = e.object_id_to1
and b.object_type = e.object_type_to
and d.element_version_id = e.object_id_from1
and d.object_type = e.object_type_from
and e.relationship_type = 'S'
and b.element_version_id IN
( select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
and 1 = c_input
start with object_id_from1 = p_structure_version_id
and object_type_from = 'PA_STRUCTURES'
and relationship_type = 'S'
connect by prior object_id_to1 = object_id_from1
AND RELATIONSHIP_TYPE = prior relationship_type
and prior object_type_to = object_type_from
UNION
select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
and 2 = c_input
and object_id_from1 IN
( select object_id_from1
from pa_object_relationships
where relationship_type = 'S'
start with object_id_to1 = p_element_version_id
and object_type_to = 'PA_TASKS'
and relationship_type = 'S'
connect by object_id_to1 = prior object_id_from1
AND RELATIONSHIP_TYPE = prior relationship_type
and object_type_to = prior object_type_from
)
);
select a.project_status_code, a.project_status_weight
from pa_project_statuses a
where a.predefined_flag = 'Y'
and a.STATUS_TYPE = 'TASK';
select 1
from pa_object_relationships rel,
pa_proj_element_versions a,
pa_proj_element_versions b,
pa_proj_elements ppe,
pa_project_statuses pps
where a.proj_element_id = c_element_id
and a.parent_structure_version_id = p_structure_version_id
and a.element_version_id = rel.object_id_from1
and a.object_type = rel.object_type_from
and b.element_version_id = rel.object_id_to1
and b.object_type = rel.object_type_to
and rel.relationship_type = 'S'
and ppe.proj_element_id = b.proj_element_id
and b.project_id = ppe.project_id
and ppe.status_code = pps.project_status_code
and pps.project_system_status_code NOT IN ('COMPLETED', 'CANCELLED', 'ON_HOLD');
UPDATE PA_PROJ_ELEMENTS
set status_code = l_final_status_code,
RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where proj_element_id = l_rollup_table(l_cnt).object_id;
select a.project_id, a.proj_element_id,
b.element_version_id, a.status_code
from pa_proj_elements a,
pa_proj_element_versions b,
pa_project_statuses c
where a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.parent_structure_version_id = p_structure_version_id
and c.project_status_code = a.status_code
and c.project_status_weight = 0
and c.status_type = 'TASK';
SELECT object_id_to1
FROM pa_object_relationships
WHERE object_id_from1 = c_structure_version_id
AND relationship_type = 'S'
AND object_type_from = 'PA_STRUCTURES'
AND object_type_to = 'PA_TASKS'
;
CURSOR cur_select_grid
IS
--select working verisons from versioned projects
SELECT a.project_id, a.element_version_id, a.prg_level
FROM pa_proj_element_versions a,
pa_proj_elem_ver_structure b,
pa_proj_workplan_attr ppwa,
pa_proj_structure_types ppst,
pa_proj_element_versions c
WHERE
c.project_id = p_project_id
AND c.prg_group = a.prg_group
AND a.project_id = b.project_id
AND a.element_version_id = b.element_version_id
AND b.status_code = 'STRUCTURE_WORKING'
AND ppwa.wp_enable_version_flag = 'Y'
AND a.project_id = ppwa.project_id
AND a.proj_element_id = ppwa.proj_element_id
AND a.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id =1
UNION
--select published verisons from non-versioned projects
SELECT a.project_id, a.element_version_id, a.prg_level
FROM pa_proj_element_versions a,
pa_proj_elem_ver_structure b,
pa_proj_workplan_attr ppwa,
pa_proj_structure_types ppst,
pa_proj_element_versions c
WHERE
c.project_id = p_project_id
AND c.prg_group = a.prg_group
AND a.project_id = b.project_id
AND a.element_version_id = b.element_version_id
AND b.status_code = 'STRUCTURE_PUBLISHED'
AND ppwa.wp_enable_version_flag = 'N'
AND a.project_id = ppwa.project_id
AND a.proj_element_id = ppwa.proj_element_id
AND a.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id =1
order by 3 desc; --select the lowest level of projects first.
pa_debug.write('PA_STRUCT_TASK_ROLLUP_PUB.PROGRAM_SCHEDUlE_DATES_ROLLUP', 'Before Opening cursor cur_select_grid', 3);
FOR cur_select_grid_rec IN cur_select_grid LOOP
--when rollinup to a working verison which is being published, first rollup to the published version then continue with the working
--verison and up in the hierarchy.
IF p_published_str_ver_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_published_str_ver_id IS NOT NULL
AND p_project_id= cur_select_grid_rec.project_id
THEN
IF l_debug_mode = 'Y' THEN
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);
cur_select_grid_rec.project_id||' x_return_status='||x_return_status, 3);
OPEN cur_top_task(cur_select_grid_rec.element_version_id);
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);