[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure Check_Delete_Structure_Ver_Ok
(
p_project_id IN NUMBER
,p_structure_version_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
)
IS
l_user_id NUMBER;
select p.person_id
from per_all_people_f p, fnd_user f
where f.employee_id = p.person_id
and sysdate between p.effective_start_date and p.effective_end_date
and f.user_id = p_user_id;
select '1'
from pa_proj_element_versions v, pa_proj_elem_ver_structure s
where v.element_version_id = p_structure_version_id
and v.project_id = s.project_id
and v.element_version_id = s.element_version_id
and (locked_by_person_id IS NULL
or locked_by_person_id = p_person_id);
select '1'
from pa_object_relationships
where (object_id_from1 = p_structure_version_id
or object_id_to1 = p_structure_version_id)
and relationship_type = 'L';
p_procedure_name => 'Check_Delete_Structure_Ver_Ok');
END Check_Delete_Structure_Ver_Ok;
select 1 from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_STRUCTURES'
and name = p_structure_name;
select 1 from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_STRUCTURES'
and name = p_structure_name
and proj_element_id <> p_structure_id;
select name from pa_proj_elem_ver_structure
where project_id = p_project_id
and proj_element_id = p_structure_id
and name = p_structure_version_name;
select name from pa_proj_elem_ver_structure
where project_id = p_project_id
and proj_element_id = p_structure_id
and name = p_structure_version_name
and pev_structure_id <> p_pev_structure_id;
select '1'
from pa_proj_structure_types p, pa_proj_elements e, pa_structure_types t
where e.project_id = p_project_id
and e.proj_element_id = p.proj_element_id
and p.structure_type_id = t.structure_type_id
and t.structure_type_class_code = p_structure_type
and t.structure_type_class_code IN ('WORKPLAN', 'FINANCIAL','DELIVERABLE');
select '1'
from pa_proj_structure_types p,
pa_structure_types t
where p.proj_element_id = p_structure_id
and p.structure_type_id = t.structure_type_id
and t.structure_type_class_code = p_structure_type
and t.structure_type_class_code IN ('WORKPLAN', 'FINANCIAL');
select '1'
from pa_proj_structure_types p, pa_proj_elements e,
pa_structure_types t, pa_proj_element_versions v
where v.element_version_id = p_structure_version_id
and v.proj_element_id = e.proj_element_id
and e.proj_element_id = p.proj_element_id
and p.structure_type_id = t.structure_type_id
and t.structure_type_class_code = p_structure_type
and t.structure_type_class_code IN ('WORKPLAN', 'FINANCIAL');
select v1.project_id, v1.parent_structure_version_id
from pa_object_relationships r,
pa_proj_element_versions v1,
pa_proj_element_versions v2
where r.relationship_type = 'L'
and r.object_type_from IN ('PA_TASKS','PA_STRUCTURES')
and r.object_id_from1 = v2.element_version_id
and r.object_id_to1 = v1.element_version_id
and v2.parent_structure_version_id = p_structure_version_id;
select b.element_version_id
from pa_proj_element_versions a,
pa_proj_element_versions b,
pa_proj_elem_ver_structure c
where a.element_version_id = p_structure_version_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.element_version_id <> p_structure_version_id
and b.object_type = 'PA_STRUCTURES'
and b.project_id = c.project_id
and b.element_version_id = c.element_version_id
and c.status_code <> 'STRUCTURE_PUBLISHED';
select '1'
from pa_proj_elem_ver_structure
where project_id = p_project_id
and element_version_id = p_structure_version_id
and status_code = 'STRUCTURE_PUBLISHED';
select s.name, v.project_id, v.proj_element_id
from pa_proj_element_versions v, pa_proj_elem_ver_structure s
where v.element_version_id = p_structure_version_id and
s.project_id = v.project_id and
s.element_version_id = v.element_version_id;
select '1'
from pa_proj_elem_ver_structure
where project_id = p_project_id
and proj_element_id = p_structure_id
and name = p_new_name;
select pev.element_version_id
into x_structure_version_id
from pa_proj_element_versions pev
-- pa_proj_elem_ver_structure pevs,
-- pa_proj_elements pe
where
-- pe.proj_element_id = p_structure_id
pev.element_version_id = p_structure_version_id;
select pevs.element_version_id
into x_structure_version_id
from pa_proj_elem_ver_structure pevs,
pa_proj_elements pe
where pe.proj_element_id = p_structure_id
and pevs.project_id = pe.project_id
and pevs.proj_element_id = pe.proj_element_id
and pevs.name = p_structure_version_name;
select proj_element_id
into x_structure_id
from pa_proj_elements
where proj_element_id = p_structure_id;
select proj_element_id
into x_structure_id
from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_STRUCTURES'
and name = p_structure_name;
select p.person_id
from fnd_user f,
per_all_people_f p
where p_user_id = f.user_id
and f.employee_id = p.person_id
and sysdate between p.effective_start_date and p.effective_end_date;
select s.locked_by_person_id
from pa_proj_element_versions v,
pa_proj_elem_ver_structure s
where v.element_version_id = p_structure_version_id
and v.project_id = s.project_id
and v.element_version_id = s.element_version_id;
select p.person_id
from fnd_user f,
per_all_people_f p
where p_user_id = f.user_id
and f.employee_id = p.person_id
and sysdate between p.effective_start_date and p.effective_end_date;
select s.locked_by_person_id
from pa_proj_element_versions v,
pa_proj_elem_ver_structure s
where v.element_version_id = p_structure_version_id
and v.project_id = s.project_id
and v.element_version_id = s.element_version_id;
select wp_approval_reqd_flag, wp_auto_publish_flag
from pa_proj_workplan_attr
where project_id = p_project_id; */
select wp_approval_reqd_flag, wp_auto_publish_flag
from pa_proj_workplan_attr ppw
,pa_proj_structure_types ppst
,pa_structure_types pst
where ppw.project_id = p_project_id
and ppw.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type = 'WORKPLAN' ;
select '1'
from pa_proj_elem_ver_structure
where project_id = p_project_id
and proj_element_id = p_structure_id
and status_code = 'STRUCTURE_PUBLISHED';
select proj_element_id from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_STRUCTURES';
,p_rollup_last_update_date OUT NOCOPY DATE -- 4537865
)
IS
CURSOR c1 IS
select min(a.scheduled_start_date)
from pa_proj_elem_ver_schedule a,
pa_object_relationships b,
pa_proj_element_versions c
where relationship_type IN ('S', 'L')
and b.object_id_from1 = p_element_version_id
and b.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
and b.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
and b.object_id_to1 = c.element_version_id
and c.project_id = a.project_id
and c.element_version_id = a.element_version_id;
select max(a.scheduled_finish_date)
from pa_proj_elem_ver_schedule a,
pa_object_relationships b,
pa_proj_element_versions c
where relationship_type IN ('S', 'L')
and b.object_id_from1 = p_element_version_id
and b.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
and b.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
and b.object_id_to1 = c.element_version_id
and c.project_id = a.project_id
and c.element_version_id = a.element_version_id;
select '1'
from pa_object_relationships a,
pa_proj_elements b,
pa_proj_element_versions c
where a.object_id_from1 = p_element_version_id
and a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
and a.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
and a.object_id_to1 = c.element_version_id
and c.proj_element_id = b.proj_element_id
and c.project_id = b.project_id
and b.link_task_flag = 'Y';
select max(a.last_update_date)
from pa_proj_elem_ver_schedule a,
pa_object_relationships b,
pa_proj_element_versions c
where a.element_version_id = c.element_version_id
and a.project_id = c.project_id
and c.element_version_id = b.object_id_to1
and b.object_type_to = 'PA_TASKS'
and b.object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
and b.relationship_type = 'S'
and b.object_id_from1 = p_element_version_id;
l_last_update_date DATE;
FETCH c4 INTO l_last_update_date;
p_rollup_last_update_date := l_last_update_date;
p_rollup_last_update_date := NULL ;
select c.element_version_id
from pa_proj_element_versions c,
pa_proj_elements b,
pa_proj_structure_types a,
pa_structure_types d
where d.structure_type_class_code = 'WORKPLAN'
and d.structure_type_id = a.structure_type_id
and a.proj_element_id = b.proj_element_id
and b.project_id = p_project_id
and b.proj_element_id = c.proj_element_id
and b.project_id = c.project_id
and c.object_type = 'PA_STRUCTURES';
select c.element_version_id /*New Query with improved Performance : 3968091 */
from pa_proj_element_versions c,
pa_proj_structure_types a,
pa_structure_types d
where d.structure_type_class_code = 'WORKPLAN'
and d.structure_type_id = a.structure_type_id
and a.proj_element_id = c.proj_element_id
and c.project_id = p_project_id
and c.object_type = 'PA_STRUCTURES';
select c.element_version_id
from pa_proj_element_versions c,
pa_proj_elements b,
pa_proj_structure_types a,
pa_structure_types d
where d.structure_type_class_code = 'FINANCIAL'
and d.structure_type_id = a.structure_type_id
and a.proj_element_id = b.proj_element_id
and b.project_id = p_project_id
and b.proj_element_id = c.proj_element_id
and b.project_id = c.project_id
and c.object_type = 'PA_STRUCTURES';
select c.element_version_id /*New Query with improved Performance : 3968091 */
from pa_proj_element_versions c,
pa_proj_structure_types a,
pa_structure_types d
where d.structure_type_class_code = 'FINANCIAL'
and d.structure_type_id = a.structure_type_id
and a.proj_element_id = c.proj_element_id
and c.project_id = p_project_id
and c.object_type = 'PA_STRUCTURES';
SELECT project_id, proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = c_element_version_id;
select b.element_version_id
from pa_proj_element_versions a,
pa_proj_elem_ver_structure b
where a.project_id = b.project_id
and a.element_version_id = p_structure_version_id
and a.proj_element_id = b.proj_element_id
and b.LATEST_EFF_PUBLISHED_FLAG = 'Y'
and b.status_code = 'STRUCTURE_PUBLISHED';
select project_id, element_version_id
from pa_proj_element_versions
where parent_structure_version_id = c_latest_struc_ver_id
and proj_element_id IN (
select proj_element_id
from pa_proj_element_versions
where parent_structure_version_id = c_latest_struc_ver_id
minus
select proj_element_id
from pa_proj_element_versions
where parent_structure_version_id = c_struc_ver_id);
select a.project_id, a.element_version_id
from pa_proj_element_versions a
where a.parent_structure_version_id = c_latest_struc_ver_id
and EXISTS (
select b.proj_element_id
from pa_proj_element_versions b
where b.parent_structure_version_id = c_latest_struc_ver_id
and b.proj_element_id = a.proj_element_id
minus
select c.proj_element_id
from pa_proj_element_versions c
where c.parent_structure_version_id = c_struc_ver_id
and c.proj_element_id = a.proj_element_id);
select name, element_number
from pa_proj_elements
where project_id = c_project_id
and proj_element_id = c_proj_element_id;
PA_TASK_UTILS.CHECK_DELETE_TASK_OK(
x_task_id => l_proj_element_id,
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
SELECT 'Y'
FROM pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
WHERE c.structure_type_class_code = 'WORKPLAN'
AND c.structure_type_id = b.structure_type_id
AND b.proj_element_id = a.proj_element_id
AND a.project_id = p_project_id;
SELECT 'Y'
FROM pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
WHERE c.structure_type_class_code = 'FINANCIAL'
AND c.structure_type_id = b.structure_type_id
AND b.proj_element_id = a.proj_element_id
AND a.project_id = p_project_id;
SELECT 'Y'
FROM pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c,
pa_proj_structure_types d,
pa_structure_types e
WHERE c.structure_type_class_code = 'WORKPLAN'
AND e.structure_type_class_code = 'FINANCIAL'
AND c.structure_type_id = b.structure_type_id
AND e.structure_type_id = d.structure_type_id
AND b.proj_element_id = a.proj_element_id
AND d.proj_element_id = a.proj_element_id
AND a.project_id = p_project_id;
select CALENDAR_ID
from pa_projects_all
where project_id = p_project_id;
select FTE_DAY
from pa_implementations;
SELECT proj_element_id
from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_TASKS'
and proj_element_id IN (
select distinct ppev1.proj_element_id
from pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
where ppev2.object_type = 'PA_STRUCTURES'
and ppev2.project_id = p_project_id
and ppev2.proj_element_id = l_structure_id
and ppev1.parent_structure_version_id = ppev2.element_version_id);*/
select distinct ppev1.proj_element_id
from pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
where ppev2.object_type = 'PA_STRUCTURES'
and ppev2.project_id = p_project_id
and ppev2.proj_element_id = l_structure_id
and ppev1.object_type = 'PA_TASKS'
and ppev1.parent_structure_version_id = ppev2.element_version_id;
SELECT ppe.proj_element_id
from pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppe.project_id = p_project_id
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'WORKPLAN';
SELECT proj_element_id
from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_TASKS'
and proj_element_id NOT IN (
select task_id
from PA_TASKS
where project_id = p_project_id);
SELECT ppe.proj_element_id
from pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppe.project_id = p_project_id
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'WORKPLAN';
SELECT proj_element_id
from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_TASKS';
SELECT project_id
FROM pa_proj_elements
where proj_element_id = p_task_id;
FUNCTION GET_LAST_UPDATED_WORKING_VER
( p_structure_id IN NUMBER
) return NUMBER
IS
CURSOR c1 IS
select str.element_version_id
from pa_proj_elem_ver_structure str,
pa_proj_elements ppe
where ppe.proj_element_id = p_structure_id
and ppe.project_id = str.project_id
and ppe.proj_element_id = str.proj_element_id
and str.CURRENT_WORKING_FLAG = 'Y';
SELECT MAX(a.last_update_date), b.parent_structure_version_id
FROM pa_proj_element_versions b,
pa_proj_elem_ver_schedule a,
pa_proj_elem_ver_structure c
WHERE a.element_version_id (+)= b.element_version_id
AND a.project_id (+) = b.project_id
AND a.proj_element_id (+) = b.proj_element_id
AND b.parent_structure_version_id = c.element_version_id
AND b.project_id = c.project_id
AND c.status_code <> 'STRUCTURE_PUBLISHED'
AND b.proj_element_id = p_structure_id
GROUP BY b.parent_structure_version_id
ORDER BY MAX(a.last_update_date) desc;
END GET_LAST_UPDATED_WORKING_VER;
SELECT project_id
FROM PA_PROJ_ELEMENTS
WHERE proj_element_id = p_proj_element_id;
select 1
from pa_tasks a,
pa_percent_completes b
where b.project_id = a.project_id
and b.task_id = a.task_id
and a.project_id = p_project_id;
select 1
from dual
where exists
( select 1
from pa_tasks a,
pa_percent_completes b
where b.project_id = a.project_id
and b.task_id = a.task_id
and a.project_id = p_project_id
);
select 1
from pa_proj_elements a,
pa_proj_elem_ver_structure b
where a.proj_element_id = p_proj_element_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.status_code <> 'STRUCTURE_PUBLISHED';
select ppe.proj_element_id
from pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppe.project_id = p_project_id
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'FINANCIAL';
select ppevs.element_version_id
from pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
where ppe.project_id = p_project_id
and ppe.proj_element_id = ppst.proj_element_id
and ppe.project_id = ppevs.project_id
and ppe.proj_element_id = ppevs.proj_element_id
and ppevs.status_code = 'STRUCTURE_PUBLISHED'
/* Commented for Bug 4998101
and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'*/
and ppst.structure_type_id = pst.structure_type_id
-- and ppevs.process_code is null -- Added for Bug Bug 4998101
and ( ppevs.process_code is null or ppevs.process_code = 'PRE') -- Added for Bug #5659575
and pst.structure_type_class_code = 'FINANCIAL'
order by ppevs.published_date desc; -- Added for Bug Bug 4998101
select ppevs.element_version_id
from pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
where ppe.project_id = p_project_id
and ppe.proj_element_id = ppst.proj_element_id
and ppe.project_id = ppevs.project_id
and ppe.proj_element_id = ppevs.proj_element_id
and ppevs.status_code <> 'STRUCTURE_PUBLISHED'
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'FINANCIAL';
select a.proj_element_id
from pa_proj_element_versions a,
pa_proj_element_versions b
where b.proj_element_id = p_proj_element_id
and b.project_id = p_project_id
and b.parent_structure_version_id = a.element_version_id
and a.project_id = p_project_id;
select ppevs.element_version_id
from pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
where ppe.project_id = p_project_id
and ppe.proj_element_id = ppst.proj_element_id
and ppe.project_id = ppevs.project_id
and ppe.proj_element_id = ppevs.proj_element_id
and ppevs.status_code = 'STRUCTURE_PUBLISHED'
and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'WORKPLAN';
select project_id, proj_element_id
from pa_proj_element_versions
where element_version_id = p_structure_version_id;
select count(1)
from pa_proj_elem_ver_structure
where project_Id = l_project_id
and proj_element_id = l_structure_id
and status_code <> 'STRUCTURE_PUBLISHED';
select ppev.proj_element_id, ppev.element_version_id
from pa_proj_element_versions ppev,
pa_object_relationships por
where ppev.parent_structure_version_id = p_structure_version_id
and ppev.object_type = 'PA_TASKS'
and ppev.element_version_id = por.object_id_from1
and ppev.object_type = por.object_type_from
and por.relationship_type = 'S';
select 1
from pa_proj_element_versions ppev,
pa_object_relationships por
where por.object_id_from1 = c_task_ver_id
and por.relationship_type = 'S'
and por.object_id_to1 = ppev.element_version_id
and ppev.financial_task_flag = 'Y'
and rownum = 1;
select name, element_number
from pa_proj_elements
where proj_element_id = c_proj_element_id;
select 1 into l_dummy
from pa_tasks where task_id = l_proj_element_id;
select ppev.proj_element_id, ppev.element_version_id
from pa_proj_element_versions ppev,
pa_object_relationships por
where ppev.parent_structure_version_id = p_structure_version_id
and ppev.object_type = 'PA_TASKS'
and ppev.element_version_id = por.object_id_from1
and ppev.object_type = por.object_type_from
and por.relationship_type = 'S';
select pps.project_system_status_code
from pa_project_statuses pps,
pa_proj_elements ppe
where ppe.proj_element_id = c_proj_element_id
and ppe.status_code = pps.project_status_code
and pps.status_type = 'TASK'
and pps.project_system_status_code = 'CANCELLED';
select pps.project_system_status_code
from pa_project_statuses pps,
pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_object_relationships por
where por.object_id_from1 = c_parent_task_ver_id
and por.object_type_from = 'PA_TASKS'
and por.relationship_type = 'S'
and por.object_type_to = 'PA_TASKS'
and por.object_id_to1 = ppev.element_version_id
and ppev.project_id = ppe.project_id
and ppev.proj_element_id = ppe.proj_element_id
and ppev.object_type = ppe.object_type
and ppe.status_code = pps.project_status_code
and pps.status_type = 'TASK'
and pps.project_system_status_code NOT IN ('CANCELLED','COMPLETED')
and ppev.TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED';
select name, element_number
from pa_proj_elements
where proj_element_id = c_proj_element_id;
select count(1)
from pa_proj_elem_ver_structure
where project_id = p_project_id
and proj_element_id =
(select proj_element_id
from pa_proj_element_versions
where element_version_id = p_structure_ver_id)
and status_code <> 'STRUCTURE_PUBLISHED';
SELECT status_code
FROM pa_proj_elem_ver_structure
WHERE project_id = p_project_id
AND element_version_id = p_structure_version_id;
select ppa.project_id, ppa.template_flag
from pa_projects_all ppa,
pa_proj_element_versions ppev
where ppev.element_version_id = p_structure_version_id
and ppev.project_id = ppa.project_id;
select 1
from pa_proj_elem_ver_structure
where project_id = l_project_id
and element_version_id = p_structure_version_id
and status_code = 'STRUCTURE_WORKING';
FUNCTION GET_UPDATE_WBS_FLAG(
p_project_id IN pa_projects_all.project_id%TYPE
,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
)
return VARCHAR2 IS
-- This cursor gets the process_update_wbs_flag for the structure version.
cursor cur_update_wbs_flag(c_project_id pa_projects_all.project_id%TYPE,
c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
IS
select process_update_wbs_flag
from pa_proj_elem_ver_structure
where project_id = c_project_id
and element_version_id = c_structure_version_id;
l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
OPEN cur_update_wbs_flag(p_project_id,p_structure_version_id);
fetch cur_update_wbs_flag into l_update_wbs_flag;
close cur_update_wbs_flag;
return nvl(l_update_wbs_flag,'N');
END GET_UPDATE_WBS_FLAG;
select process_code
from pa_proj_elem_ver_structure
where project_id = c_project_id
and element_version_id = c_structure_version_id;
FUNCTION GET_PROCESS_WBS_UPDATES_OPTION(
p_task_count IN NUMBER
,p_project_id IN NUMBER default null --bug 4370533
)
return VARCHAR2 IS
l_wbs_update_option fnd_lookups.meaning%TYPE;
Select 'x' from pa_object_relationships
where relationship_type = 'LW'
and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
SELECT 1
FROM dual
WHERE EXISTS (SELECT LOG.event_id
FROM pji_pa_proj_events_log LOG,
pa_proj_element_versions ver
WHERE LOG.event_type='PRG_CHANGE'
AND LOG.event_object =TO_CHAR(ver.prg_group)
AND ver.project_id=p_project_id
UNION ALL
SELECT LOG.event_id
FROM pa_pji_proj_events_log LOG,
pa_proj_element_versions ver
WHERE LOG.event_type='PRG_CHANGE'
AND LOG.event_object =TO_CHAR(ver.prg_group)
AND ver.project_id=p_project_id);
l_wbs_update_option := nvl(FND_PROFILE.value('PA_PROCESS_WBS_UPDATES'),'STANDARD');
IF l_wbs_update_option = 'ONLINE' THEN
return 'ONLINE';
END GET_PROCESS_WBS_UPDATES_OPTION;
select vs.process_code
from pa_proj_elem_ver_structure vs,
pa_proj_structure_types pst,
pa_structure_types st
where
vs.project_id = c_project_id
and vs.process_code in ('WUP','WUE')
and pst.PROJ_ELEMENT_ID = vs.proj_element_id
and pst.structure_type_id = st.structure_type_id
and st.structure_type = c_structure_type;
select TEMPLATE_FLAG from pa_projects_all where project_id = p_project_id;
select pev.element_version_id
from pa_proj_element_versions pev, pa_proj_elements pe,
pa_proj_structure_types pst
where pe.project_id = c_project_id
and pe.project_id = pev.project_id
and pe.proj_element_id = pev.proj_element_id
and pe.object_type = 'PA_STRUCTURES'
and pe.proj_element_id = pst.proj_element_id
and pst.structure_type_id = 6;
select pev.element_version_id /*New Query with improved Performance : 3968091 */
from pa_proj_element_versions pev,
pa_proj_structure_types pst
where pev.project_id = c_project_id
and pev.object_type = 'PA_STRUCTURES'
and pev.proj_element_id = pst.proj_element_id
and pst.structure_type_id = 6;
select vs.conc_request_id, vs.process_code, vs.element_version_id
from pa_proj_elem_ver_structure vs,
pa_proj_structure_types pst,
pa_structure_types st
where vs.project_id = c_project_id
and vs.process_code in ('WUP','WUE')
and pst.PROJ_ELEMENT_ID = vs.proj_element_id
and pst.structure_type_id = st.structure_type_id
and st.structure_type = c_structure_type;
select vs.conc_request_id
from pa_proj_elem_ver_structure vs,
pa_proj_structure_types pst,
pa_structure_types st
where vs.project_id = c_project_id
and vs.process_code in ('WUP','WUE')
and pst.PROJ_ELEMENT_ID = vs.proj_element_id
and pst.structure_type_id = st.structure_type_id
and st.structure_type = c_structure_type;
select vs.conc_request_id
from pa_proj_elem_ver_structure vs
where vs.element_version_id = c_structure_version_id
and vs.project_id = p_project_id ; -- Included for Performance Fix : 3968091
SELECT 'Y'
FROM pa_proj_elem_ver_structure a, -- Bug 16325665
pa_proj_structure_types b,
pa_structure_types c
WHERE c.structure_type_class_code = 'DELIVERABLE'
AND c.structure_type_id = b.structure_type_id
AND b.proj_element_id = a.proj_element_id
AND a.project_id = p_project_id;
SELECT ppevs.element_version_id
FROM pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
WHERE ppe.project_id = p_project_id
AND ppe.proj_element_id = ppst.proj_element_id
AND ppe.project_id = ppevs.project_id
AND ppe.proj_element_id = ppevs.proj_element_id
AND ppevs.status_code = 'STRUCTURE_WORKING'
AND ppevs.CURRENT_WORKING_FLAG = 'Y'
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = 'WORKPLAN';
SELECT pev.element_version_id
FROM pa_proj_element_versions pev ,
pa_proj_structure_types pst
WHERE pev.project_id = p_project_id
AND pev.object_type = 'PA_STRUCTURES'
AND pev.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = 1; -- WORKPLAN
For Rec IN ( select a.ELEMENT_VERSION_ID
from pa_proj_element_versions a
-- where a.project_id = 1027
-- This line is not reqd. Its used only for testing
where a.Parent_Structure_Version_ID = P_Version_ID
and a.Element_Version_ID IN (
Select b.OBJECT_ID_FROM1
from pa_object_relationships b
where b.Relationship_Type = 'S'
and b.Object_Type_From = 'PA_TASKS'
) )
Loop
Begin
Select 'Y'
Into l_Exist_Flag
from pa_object_relationships b
where Rec.Element_Version_ID IN ( b.Object_ID_From1, b.Object_ID_To1 )
and b.Object_Type_From = 'PA_TASKS'
and b.Object_Type_To = 'PA_TASKS'
and b.Relationship_Type = 'D';
select STRUCTURE_SHARING_CODE
from pa_projects_all
where project_id = p_project_id;
Select ppwa.SCHEDULE_THIRD_PARTY_FLAG
from pa_proj_workplan_attr ppwa,
pa_proj_structure_types ppst,
pa_structure_types pst
where p_project_id = ppwa.project_id
and ppwa.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type = 'WORKPLAN';
Select ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
from pa_proj_workplan_attr ppwa,
pa_proj_structure_types ppst,
pa_structure_types pst
where p_project_id = ppwa.project_id
and ppwa.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type = 'WORKPLAN';
SELECT MAX(a.last_update_date), b.parent_structure_version_id
FROM pa_proj_element_versions b,
pa_proj_elem_ver_schedule a,
pa_proj_elem_ver_structure c
WHERE a.element_version_id (+)= b.element_version_id
AND a.project_id (+) = b.project_id
AND a.proj_element_id (+) = b.proj_element_id
AND b.parent_structure_version_id = c.element_version_id
AND b.project_id = c.project_id
AND c.status_code <> 'STRUCTURE_PUBLISHED'
AND b.proj_element_id = p_structure_id
GROUP BY b.parent_structure_version_id
ORDER BY MAX(a.last_update_date) desc;
FUNCTION GET_STRUCT_VER_UPDATE_FLAG(
p_structure_version_id NUMBER
) return VARCHAR2
IS
CURSOR c1 IS
select project_id from pa_proj_element_versions
where element_version_id = p_structure_version_id;
select process_update_wbs_flag from pa_proj_elem_ver_structure
where project_id = c_project_id and element_version_id = p_structure_Version_id;
END GET_STRUCT_VER_UPDATE_FLAG;
SELECT ppev.element_version_id
FROM pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppe.proj_element_id = ppev.proj_element_id
AND ppe.project_id = ppev.project_id
AND ppe.project_id = cp_proj_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppev.element_version_id = ppevs.ELEMENT_VERSION_ID
AND ppevs.CURRENT_FLAG = 'Y'
AND pst.structure_type_id = ppst.structure_type_id
AND ppst.proj_element_id = ppe.proj_element_id
AND pst.structure_type_class_code = 'WORKPLAN';*/
SELECT ppev.element_version_id
FROM pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppe.proj_element_id = ppev.proj_element_id
AND ppe.project_id = ppev.project_id
AND ppe.project_id = cp_proj_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppev.element_version_id = ppevs.ELEMENT_VERSION_ID
AND ppev.project_id = ppevs.project_id
AND ((ppevs.CURRENT_FLAG = 'Y') OR ((ppevs.CURRENT_FLAG = 'N') AND (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(cp_proj_id) = 'N') ) )
--Added for the fix of 7658505
AND pst.structure_type_id = ppst.structure_type_id
AND ppst.proj_element_id = ppe.proj_element_id
AND pst.structure_type_class_code = 'WORKPLAN';
SELECT schedule_dirty_flag
FROM pa_proj_elem_ver_structure
WHERE project_id = cp_project_id
AND element_version_id = cp_structure_version_id;
SELECT '1'
FROM pa_proj_element_Versions ppev,
pa_object_relationships por
where ppev.parent_structure_version_id = p_structure_ver_id
and ppev.element_version_id = por.object_id_from1
and ppev.project_id = p_project_id
and por.relationship_type IN ('LW', 'LF');
SELECT '1'
FROM pa_proj_element_Versions ppev,
pa_object_relationships por
where ppev.parent_structure_version_id = p_structure_ver_id
and ppev.element_version_id = por.object_id_from1
and ppev.project_id = p_project_id
and por.relationship_type = 'LW';
SELECT '1'
FROM pa_proj_element_Versions ppev,
pa_object_relationships por
where ppev.parent_structure_version_id = p_structure_ver_id
and ppev.element_version_id = por.object_id_from1
and ppev.project_id = p_project_id
and por.relationship_type = 'LF';
SELECT '1'
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
WHERE ppev.element_version_id = cp_structure_version_id
AND ppev.project_id = cp_project_id
AND ppev.project_id = ppe.project_id
AND ppev.proj_element_id = ppe.parent_structure_id
AND ppe.link_task_flag = 'Y';
SELECT 'Y'
FROM pa_proj_element_versions
WHERE project_id = cp_project_id
AND element_version_id = cp_struct_ver_id
AND object_type = 'PA_STRUCTURES';
SELECT 'Y'
FROM pa_projects_all
WHERE project_id = cp_project_id;
select vs.process_code, vs.element_version_id, vs.conc_request_id
from pa_proj_elem_ver_structure vs
, pa_proj_structure_types pst
, pa_structure_types st
where vs.project_id = c_project_id
and pst.PROJ_ELEMENT_ID = vs.proj_element_id
and pst.structure_type_id = st.structure_type_id
and st.structure_type = c_structure_type
and vs.process_code is not null; -- Fix for Bug # 4373055.
select process_code, element_version_id, conc_request_id
from pa_proj_elem_ver_structure
where project_id = c_project_id
and element_version_id = c_structure_version_id;
select meaning message_name, 'PROCESS' message_type
from pa_lookups
where lookup_code = c_process_code
and c_process_code like '%P'
union all
select meaning message_name, 'ERROR_EDITABLE' message_type
from pa_lookups
where lookup_code = c_process_code
and c_process_code like '%E'
and c_process_code <> 'PUE'
union all
select meaning message_name, 'ERROR_NOT_EDITABLE' message_type
from pa_lookups
where lookup_code = c_process_code
and c_process_code = 'PUE'
;
if ((p_context = 'UPDATE_AMG') and (l_message_name is not null)) then -- Fix for Bug # 4373055.
l_message_name := l_message_name||'_AMG';
update pa_proj_elem_ver_structure
set process_code = decode(p_calling_context, 'APPLY_PROGRESS', 'APP'
, 'CONC_PUBLISH', 'PUP'
, 'CONC_UPDATE', 'WUP'
, 'ASGMT_PLAN_CHANGE', 'WUP'
,null) ---4492493
, conc_request_id = p_conc_request_id
where element_version_id = p_structure_version_id
and project_id = p_project_id;
update pa_proj_elem_ver_structure
set process_code = decode(p_calling_context, 'APPLY_PROGRESS', 'APE'
, 'CONC_PUBLISH', 'PUE'
, 'CONC_UPDATE', 'WUE', null)
, conc_request_id = p_conc_request_id
where element_version_id = p_structure_version_id
and project_id = p_project_id;
update pa_proj_elem_ver_structure
set process_code = p_process_code
, conc_request_id = p_conc_request_id
where element_version_id = p_structure_version_id
and project_id = p_project_id
and process_code is null;
update pa_proj_elem_ver_structure
set process_code = NULL
where project_id = p_project_id
and process_code = 'PRE';
select ppa.structure_sharing_code
from pa_projects_all ppa
where ppa.project_id = c_project_id;
select ppevs.process_update_wbs_flag, ppevs.process_code
from pa_proj_elem_ver_structure ppevs, pa_proj_structure_types ppst, pa_structure_types pst
where ppevs.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and ppevs.project_id = c_project_id
and ppevs.element_version_id = c_structure_version_id;
select ppevs.name, ppevs.pev_structure_id, ppevs.record_version_number
from pa_proj_elem_ver_structure ppevs
where ppevs.project_id = c_project_id
and ppevs.element_version_id = c_str_ver_id;
pa_debug.write(l_module_name,'Before Calling pa_project_structure_pub1.update_structure_version_attr',3);
pa_project_structure_pub1.update_structure_version_attr
(p_pev_structure_id => l_rec_ppevs.pev_structure_id
, p_locked_status_code => p_lock_status_code
, p_structure_version_name => l_rec_ppevs.name
, p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130360
, p_record_version_number => l_rec_ppevs.record_version_number
, p_calling_module => p_calling_module
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
pa_debug.write(l_module_name,'After calling pa_project_structure_pub1.update_structure_version_attr x_return_status='||x_return_status,3);
select ppevs.name, ppevs.pev_structure_id, ppevs.record_version_number
from pa_proj_elem_ver_structure ppevs
where ppevs.project_id = c_project_id
and ppevs.element_version_id = c_str_ver_id;
pa_debug.write(l_module_name,'Before Calling pa_project_structure_pub1.update_structure_version_attr',3);
pa_project_structure_pub1.update_structure_version_attr
(p_pev_structure_id => l_rec_ppevs.pev_structure_id
, p_locked_status_code => p_lock_status_code
, p_structure_version_name => l_rec_ppevs.name
, p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130360
, p_record_version_number => l_rec_ppevs.record_version_number
, p_calling_module => p_calling_module
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
pa_debug.write(l_module_name,'After calling pa_project_structure_pub1.update_structure_version_attr x_return_status='||x_return_status,3);
SELECT sys_program_flag
FROM pa_projects_all
WHERE project_id=p_project_id
;
SELECT 1 FROM DUAL WHERE EXISTS (
SELECT por.object_relationship_id
FROM pa_object_relationships por,
pa_proj_element_versions ppev
WHERE por.object_id_from1 = ppev.element_version_id
AND por.relationship_type in ('LW', 'LF')
AND ppev.parent_structure_version_id = p_structure_version_id
UNION ALL
SELECT por. object_relationship_id
FROM pa_object_relationships por,
pa_proj_element_versions ppev
WHERE por.object_id_to1 = ppev.element_version_id
AND por.relationship_type in ('LW', 'LF')
AND ppev.parent_structure_version_id = p_structure_version_id);
SELECT ppevs.current_flag,
ppevs.LATEST_EFF_PUBLISHED_FLAG,
ppevs.name,
ppevs.version_number
FROM pa_proj_elem_ver_structure ppevs
WHERE ppevs.element_version_id = p_structure_version_id
AND ppevs.project_id = p_project_id;
select 1
from dual
where exists (select log.event_id
from pji_pa_proj_events_log log,pa_proj_element_versions ver
where log.event_type='PRG_CHANGE'
and log.event_object =to_char(ver.prg_group)
and ver.project_id=p_project_id
and ver.parent_structure_version_id = nvl(p_version_id,ver.parent_structure_version_id) --Bug#8889029.added nvl
union all
select log.event_id
from pa_pji_proj_events_log log,pa_proj_element_versions ver
where log.event_type='PRG_CHANGE'
and log.event_object =to_char(ver.prg_group)
and ver.project_id=p_project_id
and ver.parent_structure_version_id = nvl(p_version_id,ver.parent_structure_version_id) --Bug#8889029.added nvl
);
select distinct worker_id
from pji_pjp_proj_batch_map
where project_id = p_project_id;
select 1
from dual
where exists (select log.event_id
from pji_pa_proj_events_log log
where log.event_type='PRG_CHANGE'
union all
select log.event_id
from pa_pji_proj_events_log log
where log.event_type='PRG_CHANGE'
);
SELECT a.proj_element_id
FROM pa_proj_elements a,
pa_proj_structure_types b,
pa_structure_types c
WHERE a.proj_element_id = b.proj_element_id
AND a.object_type = 'PA_STRUCTURES'
AND a.project_id = p_project_id
AND b.structure_type_id = c.structure_type_id
AND c.structure_type = 'WORKPLAN';
SELECT NVL(txn_date_sync_buf_days, 0)
FROM pa_proj_workplan_attr
WHERE proj_element_id = c_structure_id;
SELECT ppe.proj_element_id,
ppe.element_number,
ppev.element_version_id
FROM pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_proj_elem_ver_schedule ppevs
WHERE ppe.project_id = p_project_id
AND ppe.proj_element_id = ppev.proj_element_id
AND ppev.parent_structure_version_id = p_structure_version_id
AND ppev.object_type = 'PA_TASKS'
AND ppev.element_version_id = ppevs.element_version_id
AND NVL(ppe.link_task_flag, 'N') = 'N';
SELECT pt.start_date,
pt.completion_date
FROM pa_tasks pt
WHERE pt.task_id = c_proj_element_id;
SELECT nvl(prla.resource_class_flag, 'Y') INTO l_resource_class_flag FROM pa_projects_all ppa,
pa_resource_lists_all_bg prla, pa_resource_lists_tl prl, pa_proj_fp_options ppfo
where ppa.project_id = p_project_id and prla.resource_list_id = ppfo.cost_resource_list_id
AND ppfo.fin_plan_option_level_code = 'PLAN_TYPE'
AND ppfo.project_id = ppa.project_id AND prl.resource_list_id = prla.resource_list_id
AND prl.language = userenv('LANG') AND ppfo.fin_plan_type_id = 10;