[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(level)
INTO l_tmp_level
FROM per_org_structure_elements
WHERE org_structure_version_id = p_org_version_id
AND organization_id_parent = p_child_parent_org_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = p_org_version_id
START WITH organization_id_parent = p_start_org_id
AND org_structure_version_id = p_org_version_id;
SELECT max(level)
INTO l_tmp_level
FROM per_org_structure_elements
WHERE org_structure_version_id = p_org_version_id
AND organization_id_child = p_child_parent_org_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = p_org_version_id
START WITH organization_id_parent = p_start_org_id
AND org_structure_version_id = p_org_version_id;
select parent_level into l_org_level
from pa_org_hierarchy_denorm
where org_hierarchy_version_id = p_org_version_id
and child_organization_id = p_child_parent_org_id
and parent_organization_id = p_child_parent_org_id
and pa_org_use_type = 'TP_SCHEDULE';
SELECT max(level) into l_max_org_level
FROM per_org_structure_elements
WHERE org_structure_version_id = p_org_version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
and org_structure_version_id = p_org_version_id
START WITH organization_id_parent = p_start_org_id
and org_structure_version_id = p_org_version_id;
SELECT a.organization_id_parent
INTO l_start_org_id
FROM per_org_structure_elements a
WHERE org_structure_version_id = p_org_version_id
AND not exists ( select 'a' from per_org_structure_elements b
WHERE a.organization_id_parent = b.organization_id_child and
a.org_structure_version_id = b.org_structure_version_id)
AND rownum = 1;
select decode(x_usage,'PROJECTS',PROJ_ORG_STRUCTURE_VERSION_ID
,'EXPENDITURES',EXP_ORG_STRUCTURE_VERSION_ID
,'REPORTING',ORG_STRUCTURE_VERSION_ID)
into x_org_version_id
from pa_implementations;
select decode(x_usage,'PROJECTS',PROJ_ORG_STRUCTURE_VERSION_ID
,'EXPENDITURES',EXP_ORG_STRUCTURE_VERSION_ID
,'REPORTING',ORG_STRUCTURE_VERSION_ID
,'BURDENING',to_number(org_information2))
into x_org_version_id
from pa_implementations imp,hr_organization_information hr
where imp.business_group_id = hr.organization_id
and hr.org_information_context = 'Project Burdening Hierarchy';
select decode(x_usage,'PROJECTS',PROJ_START_ORG_ID
,'EXPENDITURES',EXP_START_ORG_ID
,'REPORTING',START_ORGANIZATION_ID)
into x_start_org_id
from pa_implementations;
select decode(x_usage,'PROJECTS',PROJ_START_ORG_ID
,'EXPENDITURES',EXP_START_ORG_ID
,'REPORTING',START_ORGANIZATION_ID)
into x_start_org_id
from pa_implementations;
select distinct organization_id_parent into x_start_org_id
from per_org_structure_elements a
,pa_implementations b
,hr_organization_information c
where organization_id_parent not in
( select d.ORGANIZATION_ID_CHILD from per_org_structure_elements d
where d.org_structure_version_id = to_number(c.org_information2)
)
and a.org_structure_version_id = to_number(c.org_information2)
and b.business_group_id = c.organization_id
and c.org_information_context = 'Project Burdening Hierarchy' ;
SELECT l_new_start_org_id
FROM dual
UNION ALL
SELECT organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = l_new_org_version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = l_new_org_version_id
START WITH organization_id_parent = l_new_start_org_id
AND org_structure_version_id = l_new_org_version_id;
SELECT l_org_id
FROM dual
UNION ALL
SELECT organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = l_new_org_version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = l_new_org_version_id
START WITH organization_id_parent = l_org_id
AND org_structure_version_id = l_new_org_version_id;
(select se.organization_id_child
from per_org_structure_elements se
where se.org_structure_version_id = l_old_org_version_id
connect by prior se.organization_id_child = se.organization_id_parent
and org_structure_version_id = l_old_org_version_id
start with se.organization_id_parent = l_old_org_start_id
and org_structure_version_id = l_old_org_version_id
union
select l_old_org_start_id from sys.dual)
intersect
(select l_new_start_org_id from sys.dual );
SELECT 'Y' FROM pa_implementations_all
WHERE ( proj_org_structure_version_id = c_org_struct_version_id
OR exp_org_structure_version_id = c_org_struct_version_id )
and ORG_ID <> l_org_id;
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type )
( (select se.organization_id_child
,x_org_id
,'PROJECTS'
from per_org_structure_elements se
where org_structure_version_id = x_new_proj_org_version_id
AND EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = se.organization_id_child
and info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
connect by prior se.organization_id_child =
se.organization_id_parent
and org_structure_version_id = x_new_proj_org_version_id
start with se.organization_id_parent = x_new_proj_start_org_id
and org_structure_version_id = x_new_proj_org_version_id
union
select x_new_proj_start_org_id
,x_org_id
,'PROJECTS'
from sys.duaL /* Exists clause added for bug#2591146 */
where EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = x_new_proj_start_org_id
and info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
)
minus
(select organization_id,
org_id,
pa_org_use_type
from pa_all_organizations
where pa_org_use_type = 'PROJECTS'
and org_id = x_org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
)
);
delete from pa_cc_tp_schedule_line_lkp;
select 'YES' into proj_org_true
from hr_organization_information
where organization_id = l_child_org_id
and ORG_INFORMATION_CONTEXT = 'CLASS'
and ORG_INFORMATION1 = 'PA_PROJECT_ORG'
and ORG_INFORMATION2 = 'Y';
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type)
( (select se.organization_id_child
,x_org_id
,'EXPENDITURES'
from per_org_structure_elements se
where org_structure_version_id = x_new_exp_org_version_id
AND EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = se.organization_id_child
and info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
connect by prior se.organization_id_child =
se.organization_id_parent
and org_structure_version_id = x_new_exp_org_version_id
start with se.organization_id_parent = x_new_exp_start_org_id
and org_structure_version_id = x_new_exp_org_version_id
union
select x_new_exp_start_org_id
,x_org_id
,'EXPENDITURES'
from sys.duaL /* Exists clause added for bug#2591146 */
where EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = x_new_exp_start_org_id
and info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
)
minus
(select organization_id,
org_id,
pa_org_use_type
from pa_all_organizations
where pa_org_use_type = 'EXPENDITURES'
and org_id = x_org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
)
);
select 'YES' into exp_org_true
from hr_organization_information
where organization_id = l_child_org_id
and ORG_INFORMATION_CONTEXT = 'CLASS'
and ORG_INFORMATION1 = 'PA_EXPENDITURE_ORG'
and ORG_INFORMATION2 = 'Y';
SELECT l_dummy_level,lp_start_org_id
FROM dual
UNION ALL
SELECT level,organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = lp_org_version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = lp_org_version_id
START WITH organization_id_parent = lp_start_org_id
AND org_structure_version_id = lp_org_version_id;
SELECT l_dummy_level,lp_org_id
FROM dual
UNION ALL
SELECT level,organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = lp_org_version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = lp_org_version_id
START WITH organization_id_parent = lp_org_id
AND org_structure_version_id = lp_org_version_id;
SELECT 'x'
FROM dual
WHERE not exists
( SELECT 'x' from pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = nvl(lp_org_version_id,org_hierarchy_version_id)
and pa_org_use_type = 'TP_SCHEDULE' );
DELETE from pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = p_org_version_id
AND pa_org_use_type = 'TP_SCHEDULE';
select se.organization_id_parent
from per_org_structure_elements se
where se.org_structure_version_id = x_org_version_id
connect by prior se.organization_id_parent =
se.organization_id_child
and org_structure_version_id = x_org_version_id
start with se.organization_id_child =
x_organization_id_parent
and org_structure_version_id = x_org_version_id
union
select x_organization_id_parent from sys.dual
)
intersect
(
select v_start_org_id from sys.dual
);
(select se.organization_id_child
from per_org_structure_elements se
where se.org_structure_version_id =
x_org_version_id
connect by prior se.organization_id_child =
se.organization_id_parent
and org_structure_version_id = x_org_version_id
start with se.organization_id_parent =
v_start_org_id
and org_structure_version_id = x_org_version_id
union
select v_start_org_id
from sys.dual)
intersect
(select x_organization_id_parent
from sys.dual );
(SELECT x_organization_id_parent
FROM dual
UNION
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id = x_org_version_id
CONNECT BY PRIOR organization_id_parent= organization_id_child
AND org_structure_version_id = x_org_version_id
START WITH organization_id_child = x_organization_id_parent
AND org_structure_version_id = x_org_version_id) /* Bug#2643047, Added this condition as we have to query only for this org_structure_version_id */
union ALL
(SELECT x_organization_id_child from dual);
(SELECT lp_organization_id_parent
FROM dual
UNION
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id = lp_org_version_id
CONNECT BY PRIOR organization_id_parent= organization_id_child
AND org_structure_version_id = lp_org_version_id
START WITH organization_id_child = lp_organization_id_parent
AND org_structure_version_id = lp_org_version_id)
union ALL
(SELECT lp_organization_id_child from dual);
for imp_rec in ( select org_id,proj_start_org_id
from pa_implementations_all imp
where proj_org_structure_version_id = x_org_version_id
)
loop
/*Bug# 2247737*/
l_process_schedule_hier := 'Y';
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type)
(select
x_organization_id_child,
imp_rec.org_id,
'PROJECTS'
from sys.dual
where exists (select 'x'
from hr_organization_information info
where info.organization_id = x_organization_id_child
and info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
and not exists
(select 'X'
from pa_all_organizations
where organization_id = x_organization_id_child
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS')
) ;
Update pa_all_organizations
set inactive_date = NULL
where organization_id = x_organization_id_child
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS'
and exists (select 'x' -- EXISTS condition added for bug 2890516
from hr_organization_information info
where info.organization_id = x_organization_id_child
and info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y');
delete from pa_cc_tp_schedule_line_lkp;
select 'Y' into l_proj_org_true
from hr_organization_information info
where info.organization_id = x_organization_id_child
and info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y';
for imp_rec in ( select org_id, exp_start_org_id
from pa_implementations_all imp
where exp_org_structure_version_id = x_org_version_id
)
loop
/*Bug# 2247737*/
l_process_schedule_hier := 'Y';
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type)
(select
x_organization_id_child,
imp_rec.org_id,
'EXPENDITURES'
from sys.dual
where exists (select 'x'
from hr_organization_information info
where info.organization_id = x_organization_id_child
and info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
and not exists
(select 'X'
from pa_all_organizations
where organization_id = x_organization_id_child
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES')
) ;
Update pa_all_organizations
set inactive_date=NULL
where organization_id =x_organization_id_child
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES'
and exists (select 'x' -- EXISTS condition added for bug 2890516
from hr_organization_information info
where info.organization_id = x_organization_id_child
and info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y');
select 'Y' into l_exp_org_true
from hr_organization_information info
where info.organization_id = x_organization_id_child
and info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y';
SELECT child_organization_id
from pa_org_hierarchy_denorm
where org_hierarchy_version_id = x_org_version_id
and parent_organization_id = x_organization_id_child;
for imp_rec in ( select org_id,proj_start_org_id
from pa_implementations_all imp
where proj_org_structure_version_id = x_org_version_id
)
loop
update pa_all_organizations
set inactive_date = trunc(sysdate)
where organization_id = x_organization_id_child
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS'
and inactive_date is null ; -- Bug Ref # 6367868
for imp_rec in ( select org_id, exp_start_org_id
from pa_implementations_all imp
where exp_org_structure_version_id = x_org_version_id
)
loop
update pa_all_organizations
set inactive_date = trunc(sysdate)
where organization_id = x_organization_id_child
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES'
and inactive_date is null ; -- Bug Ref # 6367868
delete from pa_cc_tp_schedule_line_lkp;
delete from pa_org_hierarchy_denorm
where org_hierarchy_version_id = x_org_version_id
and (child_organization_id = l_parent_org_id
or parent_organization_id = l_parent_org_id);
select se.organization_id_parent
from per_org_structure_elements se
where se.org_structure_version_id = v_org_structure_version_id
and se.organization_id_parent =
v_start_org_id
connect by prior se.organization_id_parent =
se.organization_id_child
and org_structure_version_id = v_org_structure_version_id
start with se.organization_id_child =
x_organization_id
and org_structure_version_id = v_org_structure_version_id
);
(select se.organization_id_child
from per_org_structure_elements se
where se.org_structure_version_id = v_org_structure_version_id
and se.organization_id_child = x_organization_id ---made changes as Suggested
connect by prior se.organization_id_child =
se.organization_id_parent
and org_structure_version_id = v_org_structure_version_id
start with se.organization_id_parent =
v_start_org_id
and org_structure_version_id = v_org_structure_version_id );
(SELECT x_organization_id
FROM dual
UNION ALL
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id = v_org_structure_version_id
CONNECT BY PRIOR organization_id_parent= organization_id_child
AND org_structure_version_id = v_org_structure_version_id
START WITH organization_id_child = x_organization_id);
delete from pa_cc_tp_schedule_line_lkp;
for imp_rec in (select proj_start_org_id, proj_org_structure_version_id,
org_id
from pa_implementations_all )
loop
v_start_org_id := imp_rec.proj_start_org_id;
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type)
(select x_organization_id,
imp_rec.org_id, 'PROJECTS'
from sys.dual
where not exists ( select 'X'
from pa_all_organizations
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS' ));
if sql%rowcount = 0 then --- Means Row was not inserted as it was there
---- Earlier, IN This case set inactive_date to
--- NULL.
update pa_all_organizations
set inactive_date = null
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS' ;
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type)
(select x_organization_id,
imp_rec.org_id, 'PROJECTS'
from sys.dual
where not exists ( select 'X'
from pa_all_organizations
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS' ));
if sql%rowcount = 0 then --- Means Row was not inserted as it was there
---- Earlier, IN This case set inactive_date to
--- NULL.
update pa_all_organizations
set inactive_date = null
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS' ;
for imp_rec in (select proj_start_org_id, proj_org_structure_version_id,
org_id
from pa_implementations_all )
loop
update pa_all_organizations
set inactive_date = trunc(sysdate)
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'PROJECTS'
and inactive_date is null; -- Bug Ref # 6367868
delete from pa_org_hierarchy_denorm
where child_organization_id = x_organization_id
and org_hierarchy_version_id = imp_rec.proj_org_structure_version_id
and pa_org_use_type = 'PROJECTS'; /* Bug#2643047 - Only PROJECTS records need to be deleted as we are checking
for imp_rec in (select exp_start_org_id, exp_org_structure_version_id,
org_id
from pa_implementations_all )
loop
v_start_org_id := imp_rec.exp_start_org_id;
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type)
(select x_organization_id,
imp_rec.org_id, 'EXPENDITURES'
from sys.dual
where not exists ( select 'X'
from pa_all_organizations
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES' ));
if sql%rowcount = 0 then --- Means Row was not inserted as it was there
---- Earlier, IN This case set inactive_date to
--- NULL.
update pa_all_organizations
set inactive_date = null
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES' ;
insert into pa_all_organizations
(organization_id,
org_id,
pa_org_use_type)
(select x_organization_id,
imp_rec.org_id, 'EXPENDITURES'
from sys.dual
where not exists ( select 'X'
from pa_all_organizations
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES' ));
if sql%rowcount = 0 then --- Means Row was not inserted as it was there
---- Earlier, IN This case set inactive_date to
--- NULL.
update pa_all_organizations
set inactive_date = null
where organization_id = x_organization_id
and org_id = imp_rec.org_id--MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES' ;
for imp_rec in (select exp_start_org_id, exp_org_structure_version_id,
org_id
from pa_implementations_all )
loop
update pa_all_organizations
set inactive_date = trunc(sysdate)
where organization_id = x_organization_id
and org_id = imp_rec.org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
and pa_org_use_type = 'EXPENDITURES'
and inactive_date is null ; -- Bug Ref # 6367868
delete from pa_org_hierarchy_denorm
where child_organization_id = x_organization_id
and org_hierarchy_version_id = imp_rec.exp_org_structure_version_id
and pa_org_use_type = 'EXPENDITURES'; /* Bug#2643047 - Only EXPENDITURES records need to be deleted as we
SELECT se.organization_id_child organization_id
FROM per_org_structure_elements se
WHERE org_structure_version_id = x_old_org_version_id
CONNECT BY PRIOR se.organization_id_child
= se.organization_id_parent
AND org_structure_version_id = x_old_org_version_id
START WITH se.organization_id_parent = x_old_start_org_id
AND org_structure_version_id = x_old_org_version_id
UNION
SELECT x_old_start_org_id FROM Sys.dual ;
SELECT se.organization_id_child organization_id
FROM per_org_structure_elements se
WHERE org_structure_version_id = x_new_org_version_id
AND EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = se.organization_id_child
and info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
CONNECT BY PRIOR se.organization_id_child
= se.organization_id_parent
AND org_structure_version_id = x_new_org_version_id
START WITH se.organization_id_parent = x_new_start_org_id
AND org_structure_version_id = x_new_org_version_id
UNION
SELECT x_new_start_org_id FROM Sys.dual -- Added exists clause for Bug# 1650520
WHERE EXISTS (select 'X'
from hr_organization_information info
where info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y'
and info.organization_id = x_new_start_org_id);
SELECT se.organization_id_child organization_id
FROM per_org_structure_elements se
WHERE org_structure_version_id = x_new_org_version_id
AND EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = se.organization_id_child
and info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
CONNECT BY PRIOR se.organization_id_child
= se.organization_id_parent
AND org_structure_version_id = x_new_org_version_id
START WITH se.organization_id_parent = x_new_start_org_id
AND org_structure_version_id = x_new_org_version_id
UNION
SELECT x_new_start_org_id FROM Sys.dual
where EXISTS (select 'X' /* Made changes for BUG 1180635*/
from hr_organization_information info
where info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y'
and info.organization_id = x_new_start_org_id);
/* Update all Organizations (Projects or Expenditures)
in the old hierarchy with inactive date as Sysdate.
Union is to include the start organization id in the update */
UPDATE pa_all_organizations
SET Inactive_Date = TRUNC(SYSDATE)
WHERE Pa_Org_Use_Type = x_org_use_type
AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
AND Organization_id = rec_all_old_org.organization_id
and inactive_date is null ; -- Bug Ref # 6367868
INSERT INTO Pa_All_Organizations
(organization_id,
org_id,
pa_org_use_type,
inactive_date)
VALUES
(rec_all_old_org.organization_id,
x_org_id,
x_org_use_type,
TRUNC(SYSDATE));
/* Update all Organizations (Expenditures)
in the new hierarchy with inactive date as NULL.
Union is to include the start organization id in the update */
UPDATE pa_all_organizations
SET Inactive_Date = NULL
WHERE Pa_Org_Use_Type = x_org_use_type
AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
AND Organization_id = rec_exp_new_org.organization_id;
INSERT INTO Pa_All_Organizations
(organization_id,
org_id,
pa_org_use_type,
inactive_date)
VALUES
(rec_exp_new_org.organization_id,
x_org_id,
x_org_use_type,
NULL);
/* Update all Organizations (Projects)
in the new hierarchy with inactive date as NULL.
Union is to include the start organization id in the update */
UPDATE pa_all_organizations
SET Inactive_Date = NULL
WHERE Pa_Org_Use_Type = x_org_use_type
AND Org_id = x_Org_id --MOAC Changes: Bug 4363092: Removed nvl usage with org_id
AND Organization_id = rec_proj_new_org.organization_id;
INSERT INTO Pa_All_Organizations
(organization_id,
org_id,
pa_org_use_type,
inactive_date)
VALUES
(rec_proj_new_org.organization_id,
x_org_id,
x_org_use_type,
NULL);
INSERT INTO PA_ORG_HIERARCHY_DENORM ( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT p_parent_organization_id
, p_child_organization_id
, p_org_hierarchy_version_id
, p_pa_org_use_type
, sysdate
,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
, sysdate
,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
from dual
where not exists
(select 'Y'
from pa_org_hierarchy_denorm
where pa_org_use_type = p_pa_org_use_type
and parent_organization_id = p_parent_organization_id
and child_organization_id = p_child_organization_id
and org_hierarchy_version_id = p_org_hierarchy_version_id);
INSERT INTO pa_org_hierarchy_denorm ( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
)
SELECT p_parent_organization_id
, p_child_organization_id
, p_org_hierarchy_version_id
, p_pa_org_use_type
, sysdate
,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
, sysdate
,1-- , TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
,1-- , TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
, p_parent_level
, p_child_level
from dual
where not exists
(select 'Y'
from pa_org_hierarchy_denorm
where pa_org_use_type = p_pa_org_use_type
and parent_organization_id = p_parent_organization_id
and child_organization_id = p_child_organization_id
and org_hierarchy_version_id = p_org_hierarchy_version_id);
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
)
SELECT p_parent_organization_id
, p_child_organization_id
, p_org_hierarchy_version_id
, p_pa_org_use_type
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
, p_parent_org_level
, p_child_org_level
, p_org_id
from dual
where not exists
(select 'Y'
from pa_org_hierarchy_denorm
where pa_org_use_type = p_pa_org_use_type
and parent_organization_id = p_parent_organization_id
and child_organization_id = p_child_organization_id
and org_hierarchy_version_id = p_org_hierarchy_version_id
and nvl(org_id, -99) = nvl(p_org_id, -99)); /* 2976953 Added nvl condition for org_id */
SELECT P_organization_id_parent
FROM dual
UNION
(SELECT distinct organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id = P_org_version_id
CONNECT BY PRIOR organization_id_parent= organization_id_child
AND org_structure_version_id = P_org_version_id
START WITH organization_id_child = P_organization_id_parent
AND org_structure_version_id = P_org_version_id
/* ---- The following query is added to fix the bug : 1654453 ---
---- since the start organization id defined in per_org_structure_elements
---- may be different from the start_organization_id set up in pa_implementations
---- so always the reporting hierarchy is formed based on pa_implementations
---- start_organization_id */
MINUS
SELECT distinct organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id = P_org_version_id
CONNECT BY PRIOR organization_id_parent= organization_id_child
AND org_structure_version_id = P_org_version_id
START WITH organization_id_child = P_start_org_id
AND org_structure_version_id = P_org_version_id)
UNION
(SELECT P_organization_id_child from dual);
SELECT max(parent_level) into v_plevel
FROM pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = P_org_version_id
and pa_org_use_type = 'REPORTING'
and parent_organization_id = v_parent_org_id
and org_id = p_org_id;
SELECT max(child_level) into v_plevel
FROM pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = P_org_version_id
and pa_org_use_type = 'REPORTING'
and child_organization_id = v_parent_org_id
and org_id = p_org_id;
SELECT max(level)
INTO v_parent_org_level
FROM per_org_structure_elements
WHERE org_structure_version_id = P_org_version_id
AND organization_id_parent = v_parent_org_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = P_org_version_id
START WITH organization_id_parent = P_start_org_id
AND org_structure_version_id = P_org_version_id;
SELECT max(level)
INTO v_parent_org_level
FROM per_org_structure_elements
WHERE org_structure_version_id = P_org_version_id
AND organization_id_child = v_parent_org_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = P_org_version_id
START WITH organization_id_parent = P_start_org_id
AND org_structure_version_id = P_org_version_id;
SELECT dummy_level plevel --- This query is added to fix bug : 1619922
,start_org_id organization_id_parent
,start_org_id organization_id_child
FROM dual
UNION
SELECT distinct
level plevel
,organization_id_parent
,organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
and org_structure_version_id = version_id
START WITH organization_id_parent = start_org_id
and org_structure_version_id = version_id
ORDER by 1;
(SELECT organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
and org_structure_version_id = version_id
START WITH organization_id_parent = start_org_id
and org_structure_version_id = version_id
UNION
SELECT v_start_org_id
FROM sys.dual)
INTERSECT
(SELECT p_organization_id_parent
FROM sys.dual );
delete from pa_cc_tp_schedule_line_lkp;
for imp_rec in ( select org_id, start_organization_id
from pa_implementations_all imp
where org_structure_version_id = p_org_version_id
)
LOOP
v_start_org_id := imp_rec.start_organization_id;
DELETE from pa_org_hierarchy_denorm
WHERE pa_org_use_type = 'REPORTING'
and org_hierarchy_version_id = p_org_version_id
and nvl(org_id, -99) = nvl(v_org_id, -99); /* 2976953-Added the nvl condition for org_id */
SELECT max(level) into v_maximumlevel
FROM per_org_structure_elements
WHERE org_structure_version_id = p_org_version_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
and org_structure_version_id = p_org_version_id
START WITH organization_id_parent = v_start_org_id
and org_structure_version_id = p_org_version_id;
select 'X' into l_exist
from pa_org_hierarchy_denorm
where parent_organization_id = p_organization_id
and org_hierarchy_version_id = p_org_structure_version_id
and pa_org_use_type = p_org_structure_type
and rownum = 1;
select 'X' into l_exist
from pa_org_hierarchy_denorm
where parent_organization_id = p_organization_id
and pa_org_use_type = p_org_structure_type
and rownum = 1;
select 'X' into l_exist
from pa_org_hierarchy_denorm
where org_hierarchy_version_id = p_org_structure_version_id
and pa_org_use_type = p_org_structure_type
and rownum = 1;
SELECT distinct
imp.org_structure_version_id version_id
,imp.start_organization_id start_org_id
FROM pa_implementations_all imp,
per_org_structure_elements posg
WHERE posg.org_structure_version_id = imp.org_structure_version_id;
/* this cursor will select all parents in the hierarchy below p_organization_id_parent
including p_organization_id_parent. This cursor will not select leaf nodes
for p_organization_id_parent = org3 it will return org3, org5,org6, org9 */
CURSOR all_parents (max_level number) IS
SELECT distinct organization_id_parent
,(max_level - level + 1) rev_level
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = p_org_version_id
CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
AND a.org_structure_version_id = p_org_version_id
START WITH a.organization_id_parent = p_organization_id_parent
AND a.org_structure_version_id = p_org_version_id
ORDER by rev_level desc;
delete from pa_cc_tp_schedule_line_lkp;
/* delete from pa_org_hierarchy_denorm */
DELETE from pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = p_org_version_id
AND nvl(org_id, -99) = nvl(p_org_id, -99)
AND pa_org_use_type = 'REPORTING'; /* 2976953-Added nvl condition for org id and check for
SELECT MAX(level+1)
INTO v_max_level
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = p_org_version_id
CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
AND a.org_structure_version_id = p_org_version_id
START WITH a.organization_id_parent = p_organization_id_parent
AND a.org_structure_version_id = p_org_version_id;
for each parent insert all childs
this will be called for each parent i.e. org3, org5, org6, org9
For each parent it will insert all of its child at any level. Like
for org3 it will insert org5-10 with their appropriate level
*/
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
)
(SELECT c1rec.organization_id_parent
, organization_id_child
, org_structure_version_id
, 'REPORTING'
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
, c1rec.rev_level
, c1rec.rev_level - level
, p_org_id
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = p_org_version_id
CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
AND a.org_structure_version_id = p_org_version_id
START WITH a.organization_id_parent = c1rec.organization_id_parent
AND a.org_structure_version_id = p_org_version_id
);
/* now insert all organizations in the heirarchy under p_organization_id_parent
into this table with parent and child organization id same. As all organizations
in the heirarchy has to be a child of start org so select all childs from denorm
table for this parent.
*/
--mano_msg('now inserting for each child ');
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
)
(SELECT child_organization_id
, child_organization_id
, org_hierarchy_version_id
, 'REPORTING'
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
, child_level
, child_level
, p_org_id
FROM pa_org_hierarchy_denorm
where org_hierarchy_version_id = p_org_version_id -- org_hierarchy_version_id Changed via bug 2890156
and nvl(org_id, -99) = nvl(p_org_id, -99) /* 2976953-Added nvl to the org_id condition */
and pa_org_use_type = 'REPORTING' /* 2976953- Added this condition */
and parent_organization_id = p_organization_id_parent
UNION ALL
SELECT p_organization_id_parent
, p_organization_id_parent
, p_org_version_id
, 'REPORTING'
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
, v_max_level
, v_max_level
, p_org_id
FROM dual
);
select parent_organization_id, parent_level
from pa_org_hierarchy_denorm
where org_hierarchy_version_id = x_org_version_id
and child_organization_id = x_organization_id_parent
and pa_org_use_type = 'REPORTING'
and nvl(org_id, -99) = nvl(p_org_id, -99);
for imp_rec in ( select org_id
from pa_implementations_all imp
where org_structure_version_id = x_org_version_id
)
loop
/* Get the level for the organization x_organization_id_parent */
/* The variable x_exists_in_denorm is to check whether the parent organization is
existing in pa_org_hierarchy_denorm table, if it is not there it means that the parent
organization is not in the reporting hierarchy below the reporting start organization */
x_exists_in_denorm := 'Y';
select unique parent_level into x_parent_level from pa_org_hierarchy_denorm
where org_hierarchy_version_id = x_org_version_id
and parent_organization_id = x_organization_id_parent
and child_organization_id = x_organization_id_parent
and pa_org_use_type = 'REPORTING'
and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953 - Added nvl for the org id condition */
update pa_org_hierarchy_denorm
set parent_level = parent_level + 1,
child_level = child_level + 1
where org_hierarchy_version_id = x_org_version_id
and pa_org_use_type = 'REPORTING'
and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953-Added nvl condition for org_id */
pa_og_hierarchy_denorm for REPORTING pa_org_use_type when a organization is deleted from the hierarchy.
The call to this procedure will be made from maintain_org_hist_brd.
The deleted organization is x_organiation_id_child */
procedure restructure_rpt_orgs_denorm(
x_org_version_id in number,
x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
)
IS
old_stack VARCHAR2(2000);
Select 1
from dual
where exists
(select 1 from pa_org_hierarchy_denorm
where org_hierarchy_version_id = x_org_version_id and
pa_org_use_type = 'REPORTING' and
nvl(org_id, -99) = nvl(p_org_id, -99) and /* 2976953 Added nvl for org_id condition */
parent_level = 1
);
for imp_rec in ( select org_id
from pa_implementations_all imp
where org_structure_version_id = x_org_version_id
)
loop
/* check_lower_org_exists is the cursor to find if there is any other organization also at the leaf level
as x_organization_id_child which was deleted, if yes no need to restructure the records else we need to decrement
all the levels so that other organization (s) which are actually now leaf nodes in the hierarchy have their levels as 1
and other organizations in the hierarchy have levels changed accordingly */
open check_lower_org_exists(imp_rec.org_id);
update pa_org_hierarchy_denorm
set parent_level = parent_level - 1,
child_level = child_level - 1
where org_hierarchy_version_id = x_org_version_id
and pa_org_use_type = 'REPORTING'
and nvl(org_id, -99) = nvl(imp_rec.org_id, -99); /* 2976953 Added nvl for org_id condition */
/* Bug 3649799 - This procedure will be called by statement level trigger for update on
per_org_structure_elements. */
procedure maintain_org_hist_update(x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895
/* This cursor is to get all the parents for the organization l_start_org_id in hierarchy l_org_struct_ver_id */
CURSOR all_parents (max_level number,
l_org_struct_ver_id IN per_org_structure_elements.org_structure_version_id%TYPE,
l_start_org_id IN per_org_structure_elements.organization_id_child%TYPE) IS
SELECT distinct organization_id_parent
,(max_level - level + 1) rev_level
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = l_org_struct_ver_id
CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
AND a.org_structure_version_id = l_org_struct_ver_id
START WITH a.organization_id_parent = l_start_org_id
AND a.org_structure_version_id = l_org_struct_ver_id
ORDER by rev_level desc;
SELECT 'Y' FROM pa_implementations_all
WHERE proj_org_structure_version_id = c_org_struct_version_id
OR exp_org_structure_version_id = c_org_struct_version_id;
SELECT organization_id_child
, l_level - level
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = l_version_id
CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
AND a.org_structure_version_id = l_version_id
START WITH a.organization_id_parent = l_org_id_parent
AND a.org_structure_version_id = l_version_id;
SELECT child_organization_id
, child_level
FROM pa_org_hierarchy_denorm
where org_hierarchy_version_id = l_version_id
and nvl(org_id, -99) = nvl(l_org_id, -99)
and pa_org_use_type = 'REPORTING'
and parent_organization_id = l_start_org_id;
SELECT org_id FROM pa_implementations_all
WHERE org_structure_version_id = l_version_id
and start_organization_id = l_start_org_id;
select distinct org_structure_version_id
into L_STR_VERSION_ID_TMP(L_STR_VERSION_ID_TMP.count+1)
from per_org_structure_elements
where
ROWID = pa_org_utils.newRows(k);
SELECT org_structure_version_id, organization_id_parent, organization_id_child
INTO l_version_id, l_new_parent_org_id, l_new_child_org_id
FROM per_org_structure_elements
WHERE ROWID = pa_org_utils.newRows(i); */
maintain_projexp_org_update( p_version_id => l_version_id,
p_org_use_type => 'PROJECTS',
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack => x_err_stack);
maintain_projexp_org_update( p_version_id => l_version_id,
p_org_use_type => 'EXPENDITURES',
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack => x_err_stack);
For each org id which uses the hierarchy updated, we first delete the data from denorm table
Then get the maximum level in that hierarchy.
Then insert the appropriate combinations in the denorm table.
The code is similar to the populate_hierarchy_denorm2 procedure added in 115.25 version of this file */
/* Commented and added for bug#5952671
FOR imp_rec IN (SELECT org_id, start_organization_id FROM
pa_implementations_all
WHERE org_structure_version_id = l_version_id) */
FOR imp_rec IN (SELECT distinct start_organization_id FROM
pa_implementations_all
WHERE org_structure_version_id = l_version_id)
LOOP
/* Commented for bug#5952671
DELETE from pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = l_version_id
AND org_id = imp_rec.org_id
AND pa_org_use_type = 'REPORTING'; */
l_org_id_tbl.delete;
DELETE from pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = l_version_id
AND nvl(org_id, -99) = nvl(l_org_id_tbl(i), -99)
AND pa_org_use_type = 'REPORTING';
SELECT MAX(level+1)
INTO v_max_level
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = l_version_id
CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
AND a.org_structure_version_id = l_version_id
START WITH a.organization_id_parent = imp_rec.start_organization_id
AND a.org_structure_version_id = l_version_id;
l_child_organization_id_tbl.delete;
l_child_level_tbl.delete;
FOR imp1_rec IN (SELECT org_id FROM
pa_implementations_all
WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
LOOP
forall i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
) values
(
c1rec.organization_id_parent
,l_child_organization_id_tbl(i)
,l_version_id
,'REPORTING'
,sysdate
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
,sysdate
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
,c1rec.rev_level
,l_child_level_tbl(i)
,imp1_rec.org_id
);
l_child_organization_id_tbl.delete;
l_child_level_tbl.delete;
FOR imp1_rec IN (SELECT org_id FROM
pa_implementations_all
WHERE org_structure_version_id = l_version_id and start_organization_id = imp_rec.start_organization_id)
LOOP
FORALL i in l_child_organization_id_tbl.first..l_child_organization_id_tbl.last
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
) values
(
l_child_organization_id_tbl(i)
,l_child_organization_id_tbl(i)
,l_version_id
,'REPORTING'
,sysdate
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
,sysdate
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
,l_child_level_tbl(i)
,l_child_level_tbl(i)
,imp1_rec.org_id
);
FOR imp1_rec IN (SELECT org_id FROM
pa_implementations_all
WHERE org_structure_version_id = l_version_id AND start_organization_id = imp_rec.start_organization_id)
LOOP
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
) VALUES
(
imp_rec.start_organization_id
,imp_rec.start_organization_id
,l_version_id
,'REPORTING'
,sysdate
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
,sysdate
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
,v_max_level
,v_max_level
,imp1_rec.org_id
);
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
)
(SELECT c1rec.organization_id_parent
, organization_id_child
, l_version_id
, 'REPORTING'
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
, c1rec.rev_level
, c1rec.rev_level - level
, imp_rec.org_id
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = l_version_id
CONNECT BY PRIOR a.organization_id_child = a.organization_id_parent
AND a.org_structure_version_id = l_version_id
START WITH a.organization_id_parent = c1rec.organization_id_parent
AND a.org_structure_version_id = l_version_id
);
INSERT INTO pa_org_hierarchy_denorm
( parent_organization_id
, child_organization_id
, org_hierarchy_version_id
, pa_org_use_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, parent_level
, child_level
, org_id
)
(SELECT child_organization_id
, child_organization_id
, org_hierarchy_version_id
, 'REPORTING'
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
, child_level
, child_level
, imp_rec.org_id
FROM pa_org_hierarchy_denorm
where org_hierarchy_version_id = l_version_id
and org_id = imp_rec.org_id
and pa_org_use_type = 'REPORTING' -- Added for bug#5361709
and parent_organization_id = imp_rec.start_organization_id
UNION ALL
SELECT imp_rec.start_organization_id
, imp_rec.start_organization_id
, l_version_id
, 'REPORTING'
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, sysdate
, nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),1)
, nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),1)
, v_max_level
, v_max_level
, imp_rec.org_id
FROM dual
); */
END maintain_org_hist_update;
procedure maintain_projexp_org_update(p_version_id in number,
p_org_use_type in varchar2,
x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
x_err_stack in out NOCOPY varchar2) is --File.Sql.39 bug 4440895
l_imp_proj_exp VARCHAR2(1);
SELECT decode(p_org_use_type, 'PROJECTS', proj_start_org_id, 'EXPENDITURES', exp_start_org_id) start_organization_id
FROM pa_implementations_all imp
WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
'EXPENDITURES', exp_org_structure_version_id) = c_version_id;
SELECT c_start_org_id FROM dual
UNION ALL
SELECT organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = c_org_struct_ver_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = c_org_struct_ver_id
START WITH organization_id_parent = c_start_org_id
AND org_structure_version_id = c_org_struct_ver_id;
SELECT c_org_id
FROM dual
UNION ALL
SELECT organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = c_org_struct_ver_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = c_org_struct_ver_id
START WITH organization_id_parent = c_org_id
AND org_structure_version_id = c_org_struct_ver_id ;
SELECT 'Y' INTO l_imp_proj_exp
FROM pa_implementations_all
WHERE decode(p_org_use_type, 'PROJECTS', proj_org_structure_version_id,
'EXPENDITURES', exp_org_structure_version_id) = p_version_id
AND ROWNUM = 1;
DELETE FROM pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = p_version_id
AND pa_org_use_type = p_org_use_type;
SELECT 'Y' INTO l_exist_recs
FROM pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = p_version_id
AND pa_org_use_type = p_org_use_type
AND parent_organization_id = imp_rec.start_organization_id
AND ROWNUM = 1;
SELECT 'YES' INTO class_org_true
FROM hr_organization_information
WHERE organization_id = l_child_org_id
AND ORG_INFORMATION_CONTEXT = 'CLASS'
AND ORG_INFORMATION1 = decode(p_org_use_type, 'PROJECTS', 'PA_PROJECT_ORG',
'EXPENDITURES', 'PA_EXPENDITURE_ORG')
AND ORG_INFORMATION2 = 'Y';
END maintain_projexp_org_update;
SELECT se.organization_id_child organization_id
FROM per_org_structure_elements se
WHERE org_structure_version_id = x_org_version_id
AND EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = se.organization_id_child
and info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
CONNECT BY PRIOR se.organization_id_child
= se.organization_id_parent
AND org_structure_version_id = x_org_version_id
START WITH se.organization_id_parent = v_start_org_id
AND org_structure_version_id = x_org_version_id
UNION
SELECT v_start_org_id FROM Sys.dual -- Added exists clause for Bug# 1650520
WHERE EXISTS (select 'X'
from hr_organization_information info
where info.org_information1 = 'PA_PROJECT_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y'
and info.organization_id = v_start_org_id);
SELECT se.organization_id_child organization_id
FROM per_org_structure_elements se
WHERE org_structure_version_id = x_org_version_id
AND EXISTS (select 'X'
from hr_organization_information info
where info.organization_id = se.organization_id_child
and info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y')
CONNECT BY PRIOR se.organization_id_child
= se.organization_id_parent
AND org_structure_version_id = x_org_version_id
START WITH se.organization_id_parent = v_start_org_id
AND org_structure_version_id = x_org_version_id
UNION
SELECT v_start_org_id FROM Sys.dual
where EXISTS (select 'X' /* Made changes for BUG 1180635*/
from hr_organization_information info
where info.org_information1 = 'PA_EXPENDITURE_ORG'
and info.org_information_context||'' = 'CLASS'
and info.org_information2 = 'Y'
and info.organization_id = v_start_org_id);
for imp_rec in ( select org_id,proj_start_org_id
from pa_implementations_all imp
where proj_org_structure_version_id = x_org_version_id
)
loop
v_start_org_id := imp_rec.proj_start_org_id;
UPDATE pa_all_organizations
SET Inactive_Date = TRUNC(SYSDATE)
WHERE Pa_Org_Use_Type = 'PROJECTS'
AND NVL(Org_id, -99) = NVL(v_org_id, -99);
/* Update all Organizations (Projects)
in the hierarchy with inactive date as NULL.
Union is to include the start organization id in the update */
UPDATE pa_all_organizations
SET Inactive_Date = NULL
WHERE Pa_Org_Use_Type = 'PROJECTS'
AND NVL(Org_id, -99) = NVL(v_org_id, -99)
AND Organization_id = rec_proj_new_org.organization_id;
INSERT INTO Pa_All_Organizations
(organization_id,
org_id,
pa_org_use_type,
inactive_date)
VALUES
(rec_proj_new_org.organization_id,
v_org_id,
'PROJECTS',
NULL);
for imp_rec in ( select org_id, exp_start_org_id
from pa_implementations_all imp
where exp_org_structure_version_id = x_org_version_id
)
loop
v_start_org_id := imp_rec.exp_start_org_id;
UPDATE pa_all_organizations
SET Inactive_Date = TRUNC(SYSDATE)
WHERE Pa_Org_Use_Type = 'EXPENDITURES'
AND NVL(Org_id, -99) = NVL(v_org_id, -99);
/* Update all Organizations (Projects)
in the hierarchy with inactive date as NULL.
Union is to include the start organization id in the update */
UPDATE pa_all_organizations
SET Inactive_Date = NULL
WHERE Pa_Org_Use_Type = 'EXPENDITURES'
AND NVL(Org_id, -99) = NVL(v_org_id, -99)
AND Organization_id = rec_exp_new_org.organization_id;
INSERT INTO Pa_All_Organizations
(organization_id,
org_id,
pa_org_use_type,
inactive_date)
VALUES
(rec_exp_new_org.organization_id,
v_org_id,
'EXPENDITURES',
NULL);