[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(max(osv.version_number), 0) + 1
into l_next_no
from per_org_structure_versions osv
where osv.organization_structure_id = p_Org_Structure_Version_Id;
select max(osv.date_to)
into l_max_end
from per_org_structure_versions osv
where osv.organization_structure_id = p_org_structure_id
and osv.date_from < p_date_from
and (osv.rowid <> p_rowid
or p_rowid is null);
select min(osv.date_from)
into l_min_start
from per_org_structure_versions osv
where osv.organization_structure_id = p_org_structure_id
and osv.date_from > p_date_from
and (osv.rowid <> p_rowid
or p_rowid is null);
select null
into l_dummy
from sys.dual
where exists (select 1
from per_org_structure_versions osv
where osv.organization_structure_id = p_org_structure_id
and osv.version_number = p_version_number
and (osv.rowid <> p_rowid
or p_rowid is null));
select null
into l_dummy
from sys.dual
where exists
(select 1
from per_org_structure_versions osv
where osv.organization_structure_id = p_org_structure_id
and p_date_from > osv.date_from
and osv.date_to is null);
update per_org_structure_versions osv
set osv.date_to = (p_date_from - 1)
where osv.organization_structure_id = p_org_structure_id
and osv.date_to is null
and (osv.rowid <> p_rowid
or p_rowid is null);
select null
into l_dummy
from sys.dual
where exists
(select 1
from per_org_structure_versions osv
where osv.date_from <= nvl(p_date_to,
p_end_of_time)
and nvl(osv.date_to, p_end_of_time)
>= p_date_from
and osv.organization_structure_id = p_org_structure_id
and (osv.rowid <> p_rowid
or p_rowid is null));
select str.position_control_structure_flg
from per_organization_structures str
where str.organization_structure_id = p_organization_structure_id;
cursor struct_element is select *
from per_org_structure_elements ose
where ose.org_structure_version_id = p_copy_structure_version_id;
INSERT INTO PER_ORG_STRUCTURE_ELEMENTS(
org_structure_element_id,
business_group_id,
organization_id_parent,
org_structure_version_id,
organization_id_child
) VALUES (
PER_ORG_STRUCTURE_ELEMENTS_S.NEXTVAL,
ele_record.Business_Group_Id,
ele_record.Organization_Id_Parent,
p_org_structure_version_id,
ele_record.Organization_Id_Child
);
PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
p_Org_Structure_Version_Id IN OUT NOCOPY NUMBER,
p_Business_Group_Id NUMBER,
p_Organization_Structure_Id NUMBER,
p_Date_From DATE,
p_Version_Number NUMBER,
p_Copy_Structure_Version_Id NUMBER,
p_Date_To DATE,
p_Pos_Ctrl_Enabled_Flag VARCHAR2,
p_end_of_time DATE,
p_Next_no_free IN OUT NOCOPY NUMBER,
p_closedown_warning IN OUT NOCOPY VARCHAR2,
p_gap_warning IN OUT NOCOPY VARCHAR2
) IS
--
CURSOR C IS SELECT rowid
FROM PER_ORG_STRUCTURE_VERSIONS
WHERE org_structure_version_id = p_Org_Structure_Version_Id;
CURSOR C2 IS SELECT per_org_structure_versions_s.nextval
FROM sys.dual;
INSERT INTO PER_ORG_STRUCTURE_VERSIONS(
org_structure_version_id,
business_group_id,
organization_structure_id,
date_from,
version_number,
copy_structure_version_id,
date_to,
topnode_pos_ctrl_enabled_flag
) VALUES (
p_Org_Structure_Version_Id,
p_Business_Group_Id,
p_Organization_Structure_Id,
p_Date_From,
p_Version_Number,
p_Copy_Structure_Version_Id,
p_Date_To,
p_Pos_Ctrl_Enabled_Flag
);
END Insert_Row;
SELECT *
FROM PER_ORG_STRUCTURE_VERSIONS
WHERE rowid = p_Rowid
FOR UPDATE of Org_Structure_Version_Id NOWAIT;
PROCEDURE Update_Row(p_Rowid VARCHAR2,
p_Org_Structure_Version_Id NUMBER,
p_Business_Group_Id NUMBER,
p_Organization_Structure_Id NUMBER,
p_Date_From DATE,
p_Version_Number NUMBER,
p_Copy_Structure_Version_Id NUMBER,
p_Date_To DATE,
p_Pos_Ctrl_Enabled_Flag VARCHAR2,
p_end_of_time DATE,
p_Next_no_free IN OUT NOCOPY NUMBER,
p_closedown_warning IN OUT NOCOPY VARCHAR2,
p_gap_warning IN OUT NOCOPY VARCHAR2
) IS
BEGIN
--
PER_ORG_STRUCTURE_VERSIONS_PKG.check_version_number(
p_org_structure_id => p_Organization_Structure_Id
,p_version_number => p_version_number
,p_rowid => p_rowid);
UPDATE PER_ORG_STRUCTURE_VERSIONS
SET
org_structure_version_id = p_Org_Structure_Version_Id,
business_group_id = p_Business_Group_Id,
organization_structure_id = p_Organization_Structure_Id,
date_from = p_Date_From,
version_number = p_Version_Number,
copy_structure_version_id = p_Copy_Structure_Version_Id,
date_to = p_Date_To,
topnode_pos_ctrl_enabled_flag = p_Pos_Ctrl_Enabled_Flag
WHERE rowid = p_rowid;
END Update_Row;
PROCEDURE pre_delete_checks(p_org_Structure_Version_Id NUMBER,
p_Pa_Installed VARCHAR2) is
--
-- Ensure that there are no child records existing for this
-- version. Oracle 7's constraints will handle this but it is rather
-- unfriendly to the user.
--
l_dummy VARCHAR2(1);
select null
into l_dummy
from sys.dual
where exists ( select 1
from per_org_structure_elements ose
where ose.org_structure_version_id = p_org_Structure_Version_Id
);
end pre_delete_checks;
PROCEDURe update_copied_versions(p_org_Structure_Version_Id NUMBER) is
--
-- If a version has been deleted and its structure has been copied by
-- another version then null this reference.
--
cursor update_osv is
select rowid
from per_org_structure_versions osv
where osv.copy_structure_version_id = p_org_Structure_Version_Id
for update of osv.copy_structure_version_id nowait;
open update_osv;
fetch update_osv into l_copied_rowid;
exit when update_osv%NOTFOUND;
update per_org_structure_versions osv
set osv.copy_structure_version_id = ''
where rowid = l_copied_rowid;
close update_osv;
PROCEDURE Delete_Row(p_Rowid VARCHAR2,
p_Organization_Structure_Id NUMBER,
p_org_Structure_Version_Id NUMBER,
p_Pa_Installed VARCHAR2,
p_Date_From DATE,
p_Date_To DATE,
p_gap_warning IN OUT NOCOPY VARCHAR2,
p_Next_no_free IN OUT NOCOPY NUMBER) IS
BEGIN
pre_delete_checks(p_org_Structure_Version_Id => p_org_Structure_Version_Id,
p_Pa_Installed => p_Pa_Installed);
DELETE FROM PER_ORG_STRUCTURE_VERSIONS
WHERE rowid = p_Rowid;
update_copied_versions(
p_org_Structure_Version_Id =>p_org_Structure_Version_Id);
END Delete_Row;