The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hri_org_params_s.nextval
from dual;
select osv.organization_structure_id
from per_org_structure_versions osv
where osv.org_structure_version_id = cp_osv_id
and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate);
select osv.org_structure_version_id
from per_organization_structures ost
, per_org_structure_versions osv
where ost.organization_structure_id = cp_ost_id
and ost.organization_structure_id = osv.organization_structure_id
and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate);
select ose.organization_id_parent
from per_organization_structures ost
, per_org_structure_versions osv
, per_org_structure_elements ose
where ost.organization_structure_id = cp_ost_id
and ost.organization_structure_id = osv.organization_structure_id
and osv.org_structure_version_id = ose.org_structure_version_id
and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
and not exists
( select null
from per_org_structure_elements ose2
where ose2.org_structure_version_id = osv.org_structure_version_id
and ose.organization_id_parent = ose2.organization_id_child );
select bop.org_param_id
from hri_org_params bop
where bop.organization_structure_id = cp_ost_id
and bop.organization_id = cp_org_id
and bop.organization_process = cp_orgprc;
select TREE.organization_id_start
from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
, (select ele.organization_id_parent organization_id_start
from per_org_structure_elements ele
where ele.org_structure_version_id = cp_org_structure_version_id
and cp_organization_process in ('ISNR', 'ISRO')
connect by prior ele.organization_id_child = ele.organization_id_parent
and ele.org_structure_version_id = cp_org_structure_version_id
start with ele.organization_id_parent = cp_organization_id
and ele.org_structure_version_id = cp_org_structure_version_id) TREE
where TREE.organization_id_start = org.organization_id
UNION
select TREE.organization_id_start
from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
, (select ele.organization_id_child organization_id_start
from per_org_structure_elements ele
where ele.org_structure_version_id = cp_org_structure_version_id
and cp_organization_process in ('ISNR', 'ISRO')
connect by prior ele.organization_id_child = ele.organization_id_parent
and ele.org_structure_version_id = cp_org_structure_version_id
start with ele.organization_id_parent = cp_organization_id
and ele.org_structure_version_id = cp_org_structure_version_id) TREE
where TREE.organization_id_start = org.organization_id
UNION
select org.organization_id organization_id_start
from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
where org.organization_id = cp_organization_id
order by 1;
select TREE.organization_id_group
, TREE.organization_id_child
from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
, (select cp_organization_id_start organization_id_group
, ele.organization_id_child organization_id_child
from per_org_structure_elements ele
where ele.org_structure_version_id = cp_org_structure_version_id
and cp_organization_process in ('SIRO', 'ISRO')
connect by prior ele.organization_id_child = ele.organization_id_parent
-- JTitmas Bug# 1296567 Altered line below to keep query in same hierarchy
and prior ele.org_structure_version_id = ele.org_structure_version_id
start with ele.organization_id_parent = cp_organization_id_start
and ele.org_structure_version_id = cp_org_structure_version_id) TREE
where TREE.organization_id_child = org.organization_id
UNION
select org.organization_id organization_id_group
, org.organization_id organization_id_child
from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
where org.organization_id = cp_organization_id_start
order by 1,2;
insert into hri_org_params
( org_param_id
, organization_structure_id
, organization_id
, organization_process )
values
( l_org_param_id
, p_organization_structure_id
, p_organization_id
, p_organization_process );
'HrBisOrgParams - Failed on insert into hri_org_params table: ');
insert into hri_org_param_list
( org_param_id
, organization_id_group
, organization_id_child )
values
( l_org_param_id
, r_child.organization_id_group
, r_child.organization_id_child );
'HrBisOrgParams - Failed on insert into hri_org_param_list table: ');
select distinct
ost.organization_structure_id ost_id
, ose.organization_id_parent top_org_id
, ose.org_structure_version_id ver_id
from per_organization_structures ost
, per_org_structure_versions osv
, per_org_structure_elements ose
where ost.organization_structure_id = osv.organization_structure_id
and osv.org_structure_version_id = ose.org_structure_version_id
and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
and not exists
( select null
from per_org_structure_elements ose2
where ose2.org_structure_version_id = osv.org_structure_version_id
and ose.organization_id_parent = ose2.organization_id_child );
select organization_id_child organization_id
from per_org_structure_elements
where org_structure_version_id = cp_ost_id
UNION
select organization_id
from hr_all_organization_units -- S.Bhattal, 18-AUG-99, 115.2 -- cbridge 05-DEC-2000 115.6
where organization_id = cp_org_id;
delete from hri_org_param_list;
delete from hri_org_params;
select 1
from hri_org_param_list opl
where opl.org_param_id = cp_org_param_id
and opl.organization_id_group = cp_organization_id_group
and opl.organization_id_child = cp_organization_id_child;