[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select '1' from pa_projects_all
where project_id = p_project_id
and template_flag = 'Y';
p_require_lock_flag = 'Y' -- 5/13/05: DHI ER: Allowing multiple user to update task assignment
-- If structure version is NOT locked by another user,
-- also return 'Y';
p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
IF (PA_SECURITY_PVT.check_user_privilege('PA_TASKS_UPDATE_DETAILS', 'PA_PROJECTS', p_project_id
, 'N') -- Fix for Bug # 4319137.
<> FND_API.G_TRUE) THEN
-- Bug 4533152
IF p_add_error_flag = 'Y' THEN
PA_UTILS.ADD_MESSAGE
(p_app_short_name => 'PA',
p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
SELECT ppe.name, ppe.element_number, ppev.parent_structure_version_id, ppe.project_id
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
WHERE ppe.proj_element_id = ppev.proj_element_id
AND ppev.element_version_id = p_element_version_id
AND ppev.object_type = 'PA_TASKS';
SELECT ppevs.version_number, ppevs.name, ppe.element_number, ppe.name, ppe.proj_element_id, ppe.project_id
FROM pa_proj_elem_ver_structure ppevs,
pa_proj_elements ppe
WHERE ppevs.element_version_id = p_version_id
AND ppe.proj_element_id = ppevs.proj_element_id
AND ppevs.project_id = (select project_id
from pa_proj_element_versions
where element_version_id = p_version_id) ;
SELECT meaning
FROM pa_lookups
WHERE lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
AND lookup_code = p_structure_type_code;
SELECT element_version_id
FROM pa_proj_elem_ver_structure ppevs,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppevs.project_id = p_project_id
AND latest_eff_published_flag = 'Y'
AND ppst.proj_element_id = ppevs.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = p_structure_type_code;
SELECT ppevs.last_update_date,
ppevs.pev_structure_id,
ppevs.element_version_id,
ppevs.name,
ppevs.record_version_number
FROM pa_proj_elem_ver_structure ppevs,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppevs.project_id = p_project_id
AND ppevs.status_code = 'STRUCTURE_WORKING'
AND ppst.proj_element_id = ppevs.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = 'WORKPLAN'
ORDER BY ppevs.last_update_date desc ;
SELECT MAX(a.last_update_date),
c.pev_structure_id,
c.element_version_id,
c.name,
c.record_version_number
FROM pa_proj_element_versions b,
pa_proj_elem_ver_schedule a,
pa_proj_elem_ver_structure c,
pa_structure_types d ,
pa_proj_structure_types e
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 b.project_id = p_project_id
AND c.status_code = 'STRUCTURE_WORKING'
AND e.proj_element_id = c.proj_element_id
AND d.structure_type_id = e.structure_type_id
AND d.structure_type_class_code = 'WORKPLAN'
GROUP BY c.pev_structure_id
,c.element_version_id
,c.name
,c.record_version_number
ORDER BY MAX(a.last_update_date) desc ;
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.object_type = 'PA_STRUCTURES'
-- AND ppe.status_code <> 'STRUCTURE_PUBLISHED'
AND ppst.proj_element_id = ppe.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = 'WORKPLAN';
/* pa_proj_structure_types, pa_structure_types tables are not used in select list and where clause. */
/* In case these two tables are required please add the following two join conditions to avoid full */
/* table scan on the structure_type tables */
/* AND ppst.proj_element_id = ppevs.proj_element_id */
/* AND ppst.structure_type_id = pst.structure_type_id */
CURSOR cur_elem_ver_stru4(c_struc_id NUMBER, c_struc_ver_id NUMBER)
IS
SELECT ppevs.last_update_date,
ppevs.pev_structure_id,
ppevs.element_version_id,
ppevs.name,
ppevs.record_version_number
FROM pa_proj_elem_ver_structure ppevs
WHERE ppevs.project_id = p_project_id
AND ppevs.proj_element_id = c_struc_id
AND ppevs.element_version_id = c_struc_ver_id;
/* SELECT ppevs.last_update_date,
ppevs.pev_structure_id,
ppevs.element_version_id,
ppevs.name,
ppevs.record_version_number
FROM pa_proj_elem_ver_structure ppevs,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppevs.project_id = p_project_id
AND ppevs.proj_element_id = c_struc_id
AND ppevs.element_version_id = c_struc_ver_id;*/
x_element_version_id := PA_PROJECT_STRUCTURE_UTILS.get_last_updated_working_ver(l_structure_id);
SELECT 'x'
FROM pa_object_relationships por, pa_proj_elements ppe, pa_proj_element_versions ppev
WHERE object_id_from1 = p_structure_version_id
AND relationship_type = 'S'
AND por.object_id_to1 = ppev.element_version_id
AND ppe.proj_element_id = ppev.proj_element_id
AND ppe.link_task_flag = 'N';
select 1
from pa_object_relationships
where object_type_from = 'PA_TASKS'
and object_id_from1 = p_task_version_id
and relationship_type = 'S';
select 1 from pa_proj_elements
where project_id = p_project_id
and object_type = p_object_type
and element_number = p_element_number
and PARENT_STRUCTURE_ID = p_structure_id
and (p_element_id is NULL or proj_element_id <> p_element_id);
select '1'
from pa_proj_elem_ver_structure
where project_id = p_project_id
and proj_element_id = p_structure_id
and published_date IS NULL;
procedure Check_Delete_task_Ver_Ok
(
p_project_id IN NUMBER
,p_task_version_id IN NUMBER
,p_parent_structure_ver_id IN NUMBER
,p_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
,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_parent_structure_ver_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_task_version_id
or object_id_to1 = p_task_version_id )
and relationship_type = 'L';
SELECT 'x'
FROM pa_proj_element_versions
WHERE proj_element_id = ( SELECT proj_element_id
FROM pa_proj_element_versions
WHERE element_version_id = p_task_version_id )
AND element_version_id <> p_task_version_id;
SELECT ppev.proj_element_id
FROM pa_proj_element_versions ppev, pa_tasks pt
WHERE ppev.element_version_id = p_task_version_id
and ppev.proj_element_id = pt.task_id;
SELECT ppev.proj_element_id
FROM pa_proj_element_versions ppev
WHERE ppev.element_version_id = p_task_version_id
;
l_delete_ok VARCHAR2(250);
PA_EGO_WRAPPER_PUB.check_delete_task_ok_eng(
p_api_version => 1.0 ,
p_task_id => l_proj_element_id ,
p_init_msg_list => NULL ,
x_delete_ok => l_delete_ok ,
x_return_status => l_return_status ,
x_errorcode => l_err_code ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data );
PA_DELIVERABLE_PUB.delete_dlv_task_asscn_in_bulk
(
p_task_element_id => l_proj_element_id
,p_project_id => p_project_id
,p_task_version_id => p_task_version_id
,p_delete_or_validate => 'V'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
x_error_message_code := 'PA_PS_CANT_DELETE_TASK_VER';
PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_CANT_DELETE_TASK_VER');
PA_TASK_UTILS.CHECK_DELETE_TASK_OK( x_task_id => l_proj_element_id,
x_validation_mode => p_validation_mode, --bug 2947492
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
x_error_message_code := 'PA_CHECK_DELETE_TASK_FAILED';
p_procedure_name => 'Check_Delete_task_Ver_Ok',
p_error_text => SUBSTRB(SQLERRM,1,240)); -- 4537865
END Check_Delete_task_Ver_Ok;
SELECT parent_structure_version_id
FROM pa_proj_element_versions
WHERE element_version_id = p_task_version_id
AND object_type = 'PA_TASKS';
SELECT 'Y'
FROM pa_proj_element_versions ppev
,pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppev.element_version_id = x_structure_version_id
AND ppev.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = p_structure_type;
SELECT 'x'
FROM pa_object_relationships
WHERE object_id_from1 = p_element_version_id
--hsiu: bug 2800553: performance
and rownum < 2
--start with object_id_from1 = p_element_version_id
AND object_type_from = 'PA_TASKS'
AND relationship_type = 'S'
And object_id_to1 NOT IN (
select b.object_id_from1
from pa_object_relationships a,
pa_object_relationships b
where a.object_id_from1 = p_element_version_id
and a.object_id_to1 = b.object_id_from1
and a.relationship_type = 'S'
and b.relationship_type IN ('LW', 'LF'))
;*/
SELECT NULL
FROM DUAL
WHERE EXISTS
(SELECT NULL
FROM pa_object_relationships por,
pa_proj_element_versions pev,
pa_proj_elements pe
WHERE por.object_id_from1 = p_element_version_id
AND por.object_type_from ='PA_TASKS'
AND por.relationship_type = 'S'
AND por.object_id_to1=pev.element_version_id
AND pe.proj_element_id=pev.proj_element_id
AND (((NVL(pe.link_task_flag,'N') <> 'Y'))
-- AND pev.financial_task_flag = 'Y'))
OR (pe.task_status is not null))); -- Added AND Condition for Bug 7210236
SELECT NULL
FROM DUAL
WHERE EXISTS
(SELECT NULL
FROM pa_object_relationships por,
pa_proj_element_versions pev,
pa_proj_elements pe
WHERE por.object_id_from1 = p_element_version_id
AND por.object_type_from ='PA_TASKS'
AND por.relationship_type = 'S'
AND por.object_id_to1=pev.element_version_id
AND pe.proj_element_id=pev.proj_element_id
AND NVL(pe.link_task_flag,'N') <> 'Y'
AND pev.financial_task_flag = 'Y'); -- checks for financial subtask for current task
select project_id
into x_project_id
from pa_projects_all
where project_id = p_project_id;
select project_id
into x_project_id
from pa_projects_all
where segment1 = p_project_name;
select element_version_id
into x_task_version_id
from pa_proj_element_versions
where element_version_id = p_task_version_id;
select element_version_id
into x_task_version_id
from pa_proj_elements ppe, pa_proj_element_versions ppev
where ppe.proj_element_id = ppev.proj_element_id
AND ppe.name = p_task_name
AND ppev.parent_structure_version_id = p_structure_version_id;
|| Procedure Name: UPDATE_WBS_NUMBERS
||
|| Author : Andrew Lee
|| Description:
|| This procedure is called update the wbs numbers in the task
|| hierarchy whenever any of the following actions occur:
|| INSERT
|| INDENT
|| OUTDENT
|| COPY
|| DELETE
|| ---------------------------------------------------------------------
*/
PROCEDURE UPDATE_WBS_NUMBERS ( p_commit IN VARCHAR2
,p_debug_mode IN VARCHAR2
,p_parent_structure_ver_id IN NUMBER
,p_task_id IN NUMBER
,p_display_seq IN NUMBER
,p_action IN VARCHAR2
,p_parent_task_id IN NUMBER
,p_task_version_id IN NUMBER DEFAULT -1 --bug 13895419
,p_ref_task_version_id IN NUMBER DEFAULT -1 --bug 13895419
,p_peer_or_sub IN VARCHAR2 DEFAULT 'xxx' --bug 13895419
,x_return_status OUT NOCOPY VARCHAR2) -- 4537865
IS
CURSOR TASK_INFO_CSR(c_task_id NUMBER)
IS
SELECT rel.object_id_from1 parent_task_id, pev.wbs_number
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE pev.element_version_id = c_task_id
AND pev.object_type = 'PA_TASKS'
AND rel.object_id_to1 = pev.element_version_id
AND rel.relationship_type = 'S'
AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
CURSOR UPDATE_MASKED_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER, c_mask VARCHAR2)
IS
SELECT element_version_id task_id, wbs_number, display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = c_parent_structure_ver_id
AND object_type = 'PA_TASKS'
AND abs(display_sequence) >= abs(c_display_seq)
AND display_sequence <> c_display_seq
-- AND substr(wbs_number, 1, length(c_mask)) = c_mask Commented for bug 3581030
AND substr(wbs_number, 1, length(c_mask)+1) = c_mask||'.' -- Added for bug 3581030
ORDER BY abs(display_sequence);
CURSOR UPDATE_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
IS
SELECT element_version_id task_id, wbs_number, display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = c_parent_structure_ver_id
AND object_type = 'PA_TASKS'
AND abs(display_sequence) >= abs(c_display_seq)
AND display_sequence <> c_display_seq
ORDER BY abs(display_sequence);
SELECT element_version_id task_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = c_parent_structure_ver_id
AND object_type = 'PA_TASKS'
AND display_sequence = c_display_seq;
SELECT element_version_id task_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = c_parent_structure_ver_id
AND object_type = 'PA_TASKS'
AND display_sequence =
(SELECT max(pev.display_sequence)
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE rel.object_type_from = 'PA_TASKS'
AND rel.object_id_from1 = c_parent_task_id
AND rel.relationship_type = 'S'
AND rel.object_type_to = 'PA_TASKS'
AND rel.object_id_to1 = pev.element_version_id
AND pev.parent_structure_version_id = c_parent_structure_ver_id
AND pev.display_sequence < c_display_seq);
SELECT t2.child_ver_id task_id
FROM (SELECT parent_ver_id, MAX (display_sequence) max_ds
FROM PA_PROJ_ELEM_RELATIONSHIP_T
where parent_ver_id = c_parent_task_id
and display_sequence < c_display_seq
GROUP BY parent_ver_id) t1,
PA_PROJ_ELEM_RELATIONSHIP_T t2
WHERE t1.parent_ver_id = t2.parent_ver_id AND t1.max_ds = t2.display_sequence;
SELECT element_version_id task_id
FROM PA_PROJ_ELEMENT_VERSIONS
WHERE parent_structure_version_id = c_parent_structure_ver_id
AND object_type = 'PA_TASKS'
AND display_sequence =
(SELECT max(pev.display_sequence)
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE rel.object_type_from = 'PA_STRUCTURES'
AND rel.object_id_from1 = c_parent_structure_ver_id
AND rel.relationship_type = 'S'
AND rel.object_type_to = 'PA_TASKS'
AND rel.object_id_to1 = pev.element_version_id
AND pev.parent_structure_version_id = c_parent_structure_ver_id
AND display_sequence < c_display_seq);
l_update_task_rec UPDATE_TASKS_CSR%ROWTYPE;
savepoint update_wbs_numbers;
if(p_action <> 'DELETE') then
OPEN TASK_INFO_CSR(p_task_id);
if((p_action = 'INSERT') OR (p_action = 'COPY')) then
if(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
-- Inserted task is a top task
-- Added the leading hint below for bug 3416314
-- Smukka Merging branch 40 as of now with main branch
SELECT /*+ LEADING (rel) */ count(pev.element_version_id)
INTO l_count
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE pev.parent_structure_version_id = p_parent_structure_ver_id
AND pev.object_type = 'PA_TASKS'
AND abs(pev.display_sequence) <= abs(p_display_seq)
AND rel.object_id_to1 = pev.element_version_id
AND rel.relationship_type = 'S'
AND rel.object_type_from = 'PA_STRUCTURES'
AND rel.object_id_from1 = p_parent_structure_ver_id
AND rel.object_type_to = 'PA_TASKS'; --Added for Bug 6430953
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET wbs_number = l_wbs_number
WHERE element_version_id = p_task_id;
OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
SELECT rel.object_id_from1 INTO l_parent_task_id
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE pev.element_version_id = p_task_version_id
AND pev.object_type = 'PA_TASKS'
AND rel.object_id_to1 = pev.element_version_id
AND rel.relationship_type = 'S'
AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
SELECT rel.object_id_from1 INTO l_ref_parent_task_id
FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
WHERE pev.element_version_id = p_ref_task_version_id
AND pev.object_type = 'PA_TASKS'
AND rel.object_id_to1 = pev.element_version_id
AND rel.relationship_type = 'S'
AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
l_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) + 1);
FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET wbs_number = l_wbs_number
WHERE element_version_id = l_update_task_rec.task_id;
CLOSE UPDATE_TASKS_CSR;
CLOSE UPDATE_MASKED_TASKS_CSR;
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET wbs_number = l_wbs_number
WHERE element_version_id = p_task_id;
OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
-- Task is under the indented branch
-- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
--l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
l_loop_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) - 1);
FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
-- Task is under the indented branch
-- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
--l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET wbs_number = l_loop_wbs_number
WHERE element_version_id = l_update_task_rec.task_id;
CLOSE UPDATE_TASKS_CSR;
CLOSE UPDATE_MASKED_TASKS_CSR;
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET wbs_number = l_wbs_number
WHERE element_version_id = p_task_id;
OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
-- Task is under the outdented branch
-- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
--l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
elsif(substr(l_update_task_rec.wbs_number, 1, length(l_mask2)) = l_mask2) then
-- Task used to be a peer of the outdented task
OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, p_task_id, l_update_task_rec.display_sequence);
if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
l_loop_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) + 1);
FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
if(substr(l_update_task_rec.wbs_number, 1, length(l_branch_mask)) = l_branch_mask) then
-- Task is under the indented branch
-- Bug 2786662 Commented the replace and used substr to get the l_loop_wbs_number
--l_loop_wbs_number := replace(l_update_task_rec.wbs_number, l_branch_mask, l_wbs_number);
l_number := instr(l_update_task_rec.wbs_number, l_branch_mask, 1, 1);
l_str1 := substr(l_update_task_rec.wbs_number, 1, l_number -1);
l_str2 := substr(l_update_task_rec.wbs_number, length(l_branch_mask)+l_number);
elsif(substr(l_update_task_rec.wbs_number, 1, length(l_mask2)) = l_mask2) then
-- Task used to be a peer of the outdented task
OPEN GET_PREV_PEER_TASK_CSR(p_parent_structure_ver_id, p_task_id, l_update_task_rec.display_sequence);
l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET wbs_number = l_loop_wbs_number
WHERE element_version_id = l_update_task_rec.task_id;
CLOSE UPDATE_TASKS_CSR;
CLOSE UPDATE_MASKED_TASKS_CSR;
elsif(p_action = 'DELETE') then
if(p_parent_task_id = p_parent_structure_ver_id ) then
-- Deleted task is a top task
--dbms_output.put_line('Is parent task');
OPEN UPDATE_TASKS_CSR(p_parent_structure_ver_id, p_display_seq);
OPEN UPDATE_MASKED_TASKS_CSR(p_parent_structure_ver_id, p_display_seq, l_mask);
FETCH UPDATE_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_TASKS_CSR%NOTFOUND;
if(instr(l_update_task_rec.wbs_number, '.') <> 0) then
l_str1 := substr(l_update_task_rec.wbs_number, 1, instr(l_update_task_rec.wbs_number, '.') - 1);
l_str2 := substr(l_update_task_rec.wbs_number, instr(l_update_task_rec.wbs_number, '.'));
l_wbs_number := to_char(to_number(l_update_task_rec.wbs_number) - 1);
FETCH UPDATE_MASKED_TASKS_CSR INTO l_update_task_rec;
EXIT WHEN UPDATE_MASKED_TASKS_CSR%NOTFOUND;
l_str1 := substr(l_update_task_rec.wbs_number, length(l_mask) + 2);
UPDATE PA_PROJ_ELEMENT_VERSIONS
SET wbs_number = l_wbs_number
WHERE element_version_id = l_update_task_rec.task_id;
CLOSE UPDATE_TASKS_CSR;
CLOSE UPDATE_MASKED_TASKS_CSR;
rollback to update_wbs_numbers;
rollback to update_wbs_numbers;
p_procedure_name => 'UPDATE_WBS_NUMBERS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_WBS_NUMBERS;
SELECT 'x'
FROM pa_proj_element_versions
WHERE element_version_id = p_task_version_id
AND parent_structure_version_id = p_structure_version_id;
SELECT object_id_to1
FROM pa_object_relationships
WHERE object_id_from1 = p_cur_element_version_id
AND relationship_type = 'L';
SELECT link_task_flag
FROM pa_proj_elements
WHERE proj_element_id = p_element_id;
select '1'
from pa_proj_element_versions
where p_task_version_id = element_version_id
and p_structure_version_id = parent_structure_version_id;
select object_id_from1
from pa_object_relationships
where relationship_type = 'S'
and object_id_to1 = p_parent_element_version_id;
select scheduled_start_date
from pa_proj_elem_ver_schedule sch,
pa_proj_element_versions ev
where p_element_version_id = ev.element_version_id
and ev.element_version_id = sch.element_version_id
and ev.project_id = sch.project_id;
select scheduled_finish_date
from pa_proj_elem_ver_schedule sch,
pa_proj_element_versions ev
where p_element_version_id = ev.element_version_id
and ev.element_version_id = sch.element_version_id
and ev.project_id = sch.project_id;
select sch.scheduled_start_date
from pa_proj_elem_ver_schedule sch,
pa_proj_element_versions pev,
pa_proj_element_versions pev2
where pev.project_id = c_project_id
and pev.parent_structure_version_id = c_structure_version_id
and pev.element_version_id = sch.element_version_id
and pev.project_id = sch.project_id
and pev.proj_element_id = pev2.proj_element_id
and pev.project_id = pev2.project_id
and pev2.element_version_id = p_element_version_id;
select str.project_id, str.element_version_id
from pa_proj_elem_ver_structure str,
pa_proj_element_versions pev
where pev.element_version_id = p_parent_structure_version_id
and pev.project_id = str.project_id
and pev.proj_element_id = str.proj_element_id
and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
select sch.scheduled_finish_date
from pa_proj_elem_ver_schedule sch,
pa_proj_element_versions pev,
pa_proj_element_versions pev2
where pev.project_id = c_project_id
and pev.parent_structure_version_id = c_structure_version_id
and pev.element_version_id = sch.element_version_id
and pev.project_id = sch.project_id
and pev.proj_element_id = pev2.proj_element_id
and pev.project_id = pev2.project_id
and pev2.element_version_id = p_element_version_id;
select str.project_id, str.element_version_id
from pa_proj_elem_ver_structure str,
pa_proj_element_versions pev
where pev.element_version_id = p_parent_structure_version_id
and pev.project_id = str.project_id
and pev.proj_element_id = str.proj_element_id
and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
SELECT 1
FROM PA_TASKS
WHERE task_id = p_proj_element_id;
SELECT fte_day
FROM pa_implementations;
select meaning
from fnd_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select meaning
from fnd_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select meaning
from pa_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select 'Y'
from PA_PROJ_ELEMENTS
where type_id = p_task_type_id;
select 'Y'
from dual
where exists (
select 'xyz'
from PA_PROJ_ELEMENTS
where type_id = p_task_type_id
AND project_id > -1
AND object_type = 'PA_TASKS'
);
select ppev.element_version_id
from pa_proj_element_versions ppev
where parent_structure_version_id = c_structure_version_id
and project_id = p_project_id
and proj_element_id = p_task_id;
select b.project_system_status_code
from pa_proj_elements a, pa_project_statuses b
where a.proj_element_id = p_task_id
and a.status_code = b.project_status_code
and b.status_type = 'TASK';
SELECT project_id
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT element_version_id
FROM pa_proj_element_versions
WHERE proj_element_id = p_proj_element_id;
SELECT display_sequence
FROM pa_proj_element_versions
WHERE element_version_id = p_element_version_id;
select object_Id_to1
from pa_object_relationships
where object_type_to = 'PA_TASKS'
and relationship_type = 'S'
start with object_id_from1 = p_task_version_id
and object_type_from = 'PA_TASKS'
and relationship_type = 'S'
connect by prior object_id_to1 = object_id_from1
and prior object_type_to = object_type_from
and relationship_type = prior relationship_type; --Bug 3792616
PA_PROJ_ELEMENTS_UTILS.CHECK_DELETE_TASK_VER_OK(
p_project_id => p_project_id
,p_task_version_id => p_task_version_id
,p_parent_structure_ver_id => p_parent_structure_ver_id
,x_return_status => x_return_status
,x_error_message_code => x_error_message_code
);
PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
p_project_id => p_project_id
,p_task_version_id => l_task_ver_id
,p_parent_structure_ver_id => p_parent_structure_ver_id
,x_return_status => x_return_status
,x_error_message_code => x_error_message_code
);
PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
p_project_id => p_project_id
,p_task_version_id => l_task_ver_id_tbl(i)
,p_parent_structure_ver_id => p_parent_structure_ver_id
,x_return_status => x_return_status
,x_error_message_code => x_error_message_code
);
select ppe.project_id, ppe.proj_element_id
from pa_project_statuses pps,
pa_proj_elements ppe,
pa_proj_element_versions ppev
where ppev.element_version_id = p_parent_task_ver_id
and ppe.project_id = ppev.project_id
and ppe.proj_element_id = ppev.proj_element_id
and ppe.status_code = pps.project_status_code
and pps.project_system_status_code IN ('ON_HOLD', 'CANCELLED')
and pps.status_type = 'TASK';
select ppev.project_id, ppev.proj_element_id
from pa_proj_element_versions ppev
where ppev.element_version_id = p_parent_task_ver_id;
select 1
from pa_proj_elem_ver_schedule ppvsch,
pa_proj_element_versions ppev
where ppev.element_version_id = p_parent_task_ver_id
and ppev.project_id = ppvsch.project_id
and ppev.proj_element_id = ppvsch.proj_element_id
and ppev.element_version_id = ppvsch.element_version_id
and ( ppvsch.wq_planned_quantity IS NOT NULL AND ppvsch.wq_planned_quantity <> 0 );
SELECT enabled_flag, project_system_status_code
FROM pa_project_status_controls
WHERE project_status_code = p_task_status_code
AND action_code = p_action_code;
SELECT 'Y'
FROM pa_proj_elements
WHERE proj_element_id = p_proj_element_id
AND phase_version_id is not null;
select 'N'
from pa_proj_elements ppe,
pa_proj_element_versions ppev
where ppe.proj_element_id = p_proj_element_id
and ppe.proj_element_id = ppev.proj_element_id
and nvl(ppev.wbs_level, 0) <> 1;
select 'Y'
from pa_proj_elements ppe,
pa_proj_elements ppe2
where ppe.proj_element_id = p_task_id
and ppe.parent_structure_id = ppe2.parent_structure_id
and ppe2.phase_version_id = p_phase_version_id
and ppe2.proj_element_id <> p_task_id
and ppe2.project_id = ppe.project_id;
select 1 from
pa_proj_elements a,
pa_proj_element_versions b,
pa_proj_elem_ver_structure c
where a.proj_element_id = p_task_id
and a.project_id = p_project_id
and a.project_id = b.project_id
and a.proj_element_id = b.proj_element_id
and b.project_Id = c.project_id
and b.parent_structure_version_id = c.element_version_id
and c.status_code = 'STRUCTURE_PUBLISHED';
select 1 from
pa_tasks a
where a.task_id = p_task_id;
select '1'
from pa_project_statuses pps,
pa_proj_elements ppe,
pa_proj_element_versions ppev
where ppev.element_version_id = p_task_ver_id
and ppe.project_id = ppev.project_id
and ppe.proj_element_id = ppev.proj_element_id
and ppe.status_code = pps.project_status_code
and pps.project_system_status_code IN ('ON_HOLD', 'CANCELLED', 'COMPLETED')
and pps.status_type = 'TASK';
select b.project_id, b.proj_element_id
from pa_proj_element_versions a,
pa_proj_elem_ver_structure b
where a.element_version_id = p_task_version_id
and a.parent_structure_version_id = b.element_version_id
and a.project_id = b.project_id;
select distinct ppe.proj_element_id
from pa_proj_element_versions ppe
where ppe.element_version_id IN (
select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
start with object_id_from1 IN (
select a.element_version_id
from pa_proj_element_versions a,
pa_proj_elem_ver_structure b
where b.project_id = c_project_id
and b.proj_element_id = c_structure_id
and b.status_code <> 'STRUCTURE_PUBLISHED'
and b.element_version_id = a.parent_structure_version_id
and a.proj_element_id = p_task_id)
and relationship_type = 'S'
connect by object_id_from1 = prior object_id_to1
and object_type_from = prior object_type_to
and relationship_type = prior relationship_type)
minus
select ppe.proj_element_id
from pa_proj_element_versions ppe
where ppe.element_version_id IN (
select object_id_to1
from pa_object_relationships
where relationship_type = 'S'
start with object_id_from1 = p_task_version_id
and object_type_from = 'PA_TASKS'
and relationship_type = 'S'
connect by object_id_from1 = prior object_id_to1
and object_type_from = prior object_type_to
and relationship_type = prior relationship_type);
select ppev.element_version_id, ppev.TASK_UNPUB_VER_STATUS_CODE
from pa_proj_element_Versions ppev,
pa_proj_elem_ver_structure ppevs
where ppev.proj_element_id = c_xtra_task_id
and ppev.project_id = c_project_id
and ppev.parent_structure_version_id = ppevs.element_version_id
and ppevs.project_id = c_project_id
and ppevs.proj_element_id = c_structure_id
and ppevs.status_code <> 'STRUCTURE_PUBLISHED';
SELECT name
INTO l_ret
FROM pa_proj_elements
WHERE proj_element_id = p_proj_element_id;
SELECT element_number
INTO l_ret
FROM pa_proj_elements
WHERE proj_element_id = p_proj_element_id;
SELECT name||'('||element_number||')'
INTO l_ret
FROM pa_proj_elements
WHERE proj_element_id = p_proj_element_id;
select null
from dual where exists
(select null from pa_object_relationships
where object_id_from1 = p_element_version_id
and relationship_type = 'S');
select project_system_status_code into l_task_stat_sys_code
from pa_project_statuses
where p_task_status_code = project_status_code
and status_type = 'TASK';
select element_version_id
from pa_proj_element_versions
where project_id = c_project_id
and parent_structure_version_id = c_struct_version_id
and display_sequence = (
select max(display_sequence)
from pa_proj_element_versions
where project_id = c_project_id
and parent_structure_version_id = c_struct_version_id
and display_sequence < c_display_seq
);
select element_version_id
from pa_proj_element_versions
where project_id = c_project_id
and parent_structure_version_id = c_struct_version_id
and display_sequence = (
select min(display_sequence)
from pa_proj_element_versions
where project_id = c_project_id
and parent_structure_version_id = c_struct_version_id
and display_sequence > c_display_seq
);
SELECT c.element_version_id
FROM pa_proj_element_versions c,
pa_structure_types a,
pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE c.project_id = c_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = c_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = c_status_code;
SELECT c.element_version_id
FROM pa_proj_element_versions c,
pa_structure_types a,
pa_proj_structure_types b,
pa_proj_elem_ver_structure d
WHERE c.project_id = c_project_id
AND a.structure_type_id = b.structure_type_id
AND b.proj_element_id = c.proj_element_id
AND a.structure_type = c_structure_type
AND d.project_id = c.project_id
AND d.element_version_id = c.element_version_id
AND d.status_code = 'STRUCTURE_PUBLISHED'
AND d.latest_eff_published_flag = 'Y';
PA_PROJECT_STRUCTURE_UTILS.get_last_updated_working_ver(p_structure_id);
Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK'|| p_task_id;
PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK(
p_task_id => p_task_id
,p_validation_mode => 'U'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
select object_id_from1
from pa_object_relationships
where relationship_type='S'
and object_type_from='PA_TASKS'
connect by prior object_id_from1 = object_id_to1
and prior relationship_type = relationship_type
start with object_id_to1 = p_element_version_id
and relationship_type = 'S'
union
select p_element_version_id from dual ; --bug 3429648*/
select object_id_to1
from pa_object_relationships
where relationship_type='S'
and object_type_from='PA_STRUCTURES'
and object_type_to='PA_TASKS'
connect by prior object_id_from1 = object_id_to1
start with object_id_to1 = p_element_version_id
and relationship_type = 'S';
select a.object_id_to1
from pa_object_relationships a, pa_proj_element_versions b
where b.element_version_id = p_element_version_id
and b.parent_structure_version_id = a.object_id_from1
and a.relationship_type = 'S';
select proj_element_id from pa_proj_element_versions
where element_version_id IN (
/*select object_id_from1 --bug 4043647
from pa_object_relationships
where relationship_type='S'
and object_type_from='PA_TASKS'
connect by prior object_id_from1 = object_id_to1
and prior relationship_type = relationship_type
start with object_id_to1 = p_element_version_id
and relationship_type = 'S'
union
select p_element_version_id from dual ); --bug 3429648*/
select object_id_to1
from pa_object_relationships
where relationship_type='S'
and object_type_from='PA_STRUCTURES'
and object_type_to='PA_TASKS'
connect by prior object_id_from1 = object_id_to1
start with object_id_to1 = p_element_version_id
and relationship_type = 'S');
select a.object_id_to1
from pa_object_relationships a, pa_proj_element_versions b
where b.element_version_id = p_element_version_id
and b.parent_structure_version_id = a.object_id_from1
and a.relationship_type = 'S');
select 1
from pa_object_relationships
where object_id_to1 = evid
and relationship_type='S'
and object_type_from='PA_STRUCTURES';
select 1
from pa_object_relationships
where object_id_from1 = evid
and relationship_type='S';
select 1 from pa_proj_element_versions
where element_version_id = evid
and object_type = 'PA_STRUCTURES';
select 1
from pa_tasks
where task_id = p_proj_element_id
and project_id = p_project_id
;
select 1
from dual
where exists
(select 1
from pa_tasks
where nvl(parent_task_id,-9999) = p_proj_element_id
and project_id = p_project_id
) ;
select 1 from pa_tasks
where parent_task_id is not null
and task_id = p_proj_element_id ;
SELECT structure_sharing_code INTO str_sharing_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT proj_element_id
FROM pa_object_relationships, pa_proj_element_versions
WHERE object_id_to1 = p_element_version_id
AND object_type_from='PA_TASKS'
AND object_id_from1 = element_version_id
AND relationship_type = 'S'; -- added for bug 16170622 to eliminate 'D' dependency records
SELECT object_id_from1
FROM pa_object_relationships
WHERE object_id_to1 = p_element_version_id
AND object_type_from='PA_TASKS'
AND relationship_type = 'S';
select b.element_version_id into l_task_version_id from pa_proj_elements a, pa_proj_element_versions b
where a.proj_element_id = b.proj_element_id
and a.proj_element_id = p_task_id
and b.parent_structure_version_id = p_structure_version_id;
select object_relationship_id into l_relationship_id from pa_object_relationships
where object_id_from1 = p_object_id_from1
and object_id_to1 = p_object_id_to1
and relationship_type = 'D';
SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
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 ppev.element_version_id IN (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_to1 = cp_task_version_id --24628
AND object_type_to = 'PA_TASKS'
and relationship_type = 'S'
CONNECT BY PRIOR object_id_from1 = object_id_to1
AND PRIOR object_type_from = object_type_to
AND PRIOR relationship_type = relationship_type);
SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
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 ppev.element_version_id IN (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_to1 = cp_target_task_version_id
AND object_type_to = 'PA_TASKS'
and relationship_type = 'S'
CONNECT BY PRIOR object_id_from1 = object_id_to1
AND PRIOR object_type_from = object_type_to
AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
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 ppev.element_version_id IN (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_to1 = cp_task_version_id
AND object_type_to = 'PA_TASKS'
and relationship_type = 'S'
CONNECT BY object_id_from1 = prior object_id_to1
AND object_type_from = prior object_type_to
AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
SELECT object_id_to1
FROM pa_proj_element_versions ppev,
pa_object_relationships rel1
WHERE ppev.parent_structure_version_id = cp_structure_elem_id --19671
AND rel1.relationship_type = 'S'
AND ppev.element_version_id = rel1.object_id_to1
AND NOT EXISTS (SELECT 'XYZ'
FROM pa_object_relationships rel2
WHERE rel2.object_id_from1 = rel1.object_id_to1);
SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
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 ppev.element_version_id IN (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_to1 = cp_task_version_id --24628
AND object_type_to = 'PA_TASKS'
and relationship_type = 'S'
CONNECT BY PRIOR object_id_from1 = object_id_to1
AND PRIOR object_type_from = object_type_to
AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
SELECT 'Y'
FROM pa_wf_processes pwp, pa_proj_elements ppe -- Bug #3967939
WHERE pwp.ENTITY_KEY2 = to_char(p_proj_element_id) -- Bug#3619754 : Added to_char
AND ppe.PROJ_ELEMENT_ID = p_proj_element_id -- Bug #3967939
AND pwp.ITEM_TYPE = ppe.WF_ITEM_TYPE; -- Bug #3967939
select max(item_key)
from pa_wf_processes wp
, pa_proj_elements pe
where wp.item_type = pe.wf_item_type
and wp.wf_type_code = p_wf_type_code
and to_char(pe.proj_element_id) = wp.entity_key2 --Bug 3619754 Added By avaithia
and to_char(pe.project_id) = wp.entity_key1 --Bug 3619754 Added By avaithia
and pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
select wf_item_type
from pa_proj_elements pe
where
pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
SELECT 'Y'
FROM pa_proj_elements ppe
,pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppe.proj_element_id = p_proj_element_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type IN ('WORKPLAN','FINANCIAL') ;
SELECT object_id_to1
FROM pa_object_relationships
WHERE object_id_from1 = p_task_version_id
AND object_type_to='PA_TASKS'
AND relationship_type = 'S';
SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
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 ppev.element_version_id IN (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_to1 = cp_target_task_version_id
AND object_type_to = 'PA_TASKS'
and relationship_type = 'S'
CONNECT BY PRIOR object_id_from1 = object_id_to1
AND PRIOR object_type_from = object_type_to
AND PRIOR relationship_type = RELATIONSHIP_TYPE);
SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elements ppe
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 ppev.element_version_id IN (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
START WITH object_id_to1 = cp_task_version_id
AND object_type_to = 'PA_TASKS'
and relationship_type = 'S'
CONNECT BY object_id_from1 = prior object_id_to1
AND object_type_from = prior object_type_to
AND PRIOR relationship_type = RELATIONSHIP_TYPE);
SELECT 'Y'
FROM pa_lookups
WHERE lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code;
select b.proj_element_id into l_task_id
from pa_proj_element_versions b
where b.element_version_id = p_task_version_id
and b.project_id = p_project_id
and b.parent_structure_version_id = p_structure_version_id;
select ppev.financial_task_flag
from pa_proj_element_versions ppev
where ppev.project_id = c_project_id
and ppev.element_version_id = c_task_version_id;
select 'N'
from pa_object_relationships por1, pa_proj_element_versions ppev1
where por1.object_id_to1 = ppev1.element_version_id
and por1.relationship_type = 'S'
and ppev1.project_id = c_project_id
and por1.object_id_from1 = c_task_version_id
and ppev1.financial_task_flag = 'Y'
union all
-- This query checks if the task version has a linking sub-task that has a financial link to
-- a sub-project if the input p_include_sub_proj_flag = 'Y'.
select 'N'
from pa_object_relationships por2, pa_proj_element_versions ppev2
where por2.object_id_to1 = ppev2.element_version_id
and por2.relationship_type = 'S'
and ppev2.project_id = c_project_id
and por2.object_id_from1 = c_task_version_id
and exists (select 'Y'
from pa_object_relationships por3
where por3.object_id_from1 = ppev2.element_version_id
and por3.object_id_from2 = ppev2.project_id
and por3.relationship_type = 'LF')
and c_include_sub_proj_flag = 'Y'; -- Fix for Bug # 4290042.
SELECT 'x'
FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE ppe.project_id = p_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = 1; --'WORKPLAN'
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 TASK_ID
FROM PA_TASKS
CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
AND PROJECT_ID = p_project_id
START WITH TASK_ID = p_TASK_ID
AND PROJECT_ID = p_project_id; */
select alt_task_id from pa_alternate_tasks,table(cast(l_task_tbl as sub_task)) st
where proj_element_id = st.task_id ;
IF l_valid_status_tbl(i) <> 'V' THEN -- Delete records which are in-valid
x_error_code :=49;
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM PA_EXPENDITURE_ITEMS_all pei,table(cast(l_alt_task_tbl as sub_task)) st --Changed the query for Bug#4964992
WHERE pei.TASK_ID = st.task_id)
or exists (SELECT NULL
FROM PA_EI_DENORM ped, table(cast(l_task_tbl as sub_task)) st
WHERE ped.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM PA_EXPENDITURE_ITEMS_all pei,table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
WHERE pei.TASK_ID = st.task_id)
or exists (SELECT NULL
FROM PA_EI_DENORM ped, table(cast(l_task_tbl as sub_task)) st
WHERE ped.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM po_distributions_all poa, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where poa.project_id = p_project_id
AND poa.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM po_req_distributions_all prd, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where prd.project_id = p_project_id
AND prd.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM ap_invoices_all aia, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where aia.project_id = p_project_id
AND aia.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM ap_invoice_distributions_all aid, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where aid.project_id = p_project_id
AND aid.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM pa_commitment_txns pct, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where pct.project_id = p_project_id
AND pct.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM pa_comp_rule_ot_defaults_all pcr, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where pcr.project_id = p_project_id
AND pcr.TASK_ID = st.task_id)
or exists (SELECT NULL
FROM pa_org_labor_sch_rule pol, table(cast(l_task_tbl as sub_task)) st
where overtime_project_id = p_project_id
AND pol.overtime_TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM pa_draft_invoice_details_all pdi, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where pdi.CC_TAX_TASK_ID =st.task_id
and pdi.project_id = p_project_id); -- Added for bug 8530541
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM pa_project_customers pc, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where /*pc.project_id = p_project_id and */ -- commented for bug 8485835 */
pc.receiver_task_id =st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM ap_exp_report_dists_all er, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where er.project_id = p_project_id
AND er.TASK_ID = st.task_id);
SELECT
1 into l_dummy
FROM
sys.dual
WHERE
exists (SELECT NULL
FROM mtl_material_transactions mtl, table(cast(l_task_tbl as sub_task)) st --Changed the query for Bug#4964992
where mtl.project_id = p_project_id
AND mtl.TASK_ID = st.task_id);
SELECT
task_rec(task_name,task_id)
FROM
pa_tasks
CONNECT BY PRIOR
task_id = parent_task_id AND
project_id = p_project_id
START WITH
task_id = p_task_id AND
project_id = p_project_id;
SELECT
task_rec(null,alt_task_id)
FROM
pa_alternate_tasks where
proj_element_id in (select task_id from
pa_tasks
CONNECT BY PRIOR
task_id = parent_task_id AND
project_id = p_project_id
START WITH
task_id = p_task_id AND
project_id = p_project_id)
;
select 1 from dual where exists(
select 1
from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elements ppe
where por.object_type_from = 'PA_TASKS'
and por.object_id_from1 = p_task_version_id
and por.relationship_type = 'S'
and por.object_id_to1 = ppev.element_version_id
and ppe.PROJ_ELEMENT_ID = ppev.PROJ_ELEMENT_ID
and nvl(ppe.LINK_TASK_FLAG,'N') <> 'Y');