[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'I'
into l_hr_installed
from sys.dual
where exists (select 'I'
from fnd_product_installations
where application_id between 800 and 899
and status = 'I');
select psp.view_all_organizations_flag,
psp.security_profile_id
into p_view_all_orgs,
p_security_profile_id
from per_security_profiles psp
where psp.security_profile_id = fnd_profile.value('PER_SECURITY_PROFILE_ID')
and ( psp.business_group_id + 0 = p_business_group_id
or psp.business_group_id is null);
select 'Y'
into l_exists
from sys.dual
where exists(
select 'Name exists'
from per_organization_structures os
where ( os.business_group_id + 0 = p_business_group_id
or ( os.business_group_id is null
and p_business_group_id is null))
and upper(os.name) = upper(p_name)
and (os.rowid <> p_rowid
or p_rowid is null));
select 'Y'
into l_exists
from sys.dual
where exists(
select 'Primary exists'
from per_organization_structures os
where ( os.business_group_id + 0 = p_business_group_id
or ( os.business_group_id is null
and p_business_group_id is null))
and os.primary_structure_flag = p_primary_flag
and (os.rowid <> p_rowid
or p_rowid is null));
select position_control_structure_flg
from per_organization_structures
where position_control_structure_flg = 'Y'
and organization_structure_id <> nvl(p_organization_structure_id, -1)
-- BUG 1694549
-- add predicate to check for other pos control structures
-- within current business_group only, to allow 1 pos control org hierarchy
-- per business_group.
and business_group_id = p_business_group_id;
select 1 from dual
where exists (
select 1
from per_org_structure_elements ose,
per_org_structure_versions osv
where osv.org_structure_version_id = ose.org_structure_version_id(+)
and (ose.position_control_enabled_flag = 'Y' or
osv.topnode_pos_ctrl_enabled_flag = 'Y'));
select *
from per_organization_structures
where organization_structure_id = nvl(p_organization_structure_id, -1);
PROCEDURE delete_check(p_organization_structure_id NUMBER
,p_business_group_id NUMBER
,p_pa_installed VARCHAR2
) is
--
l_temp VARCHAR2(1);
select 1
into l_temp
from sys.dual
where exists( select 1
from per_org_structure_versions osv
where ( osv.business_group_id + 0 = p_business_group_id
or ( osv.business_group_id is null
and p_business_group_id is null))
and osv.organization_structure_id =
p_organization_structure_id
);
end delete_check;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Organization_Structure_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Name VARCHAR2,
X_Comments VARCHAR2,
X_Primary_Structure_Flag VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Pos_Control_Structure_Flag VARCHAR2
) IS
--
CURSOR C IS SELECT rowid FROM PER_ORGANIZATION_STRUCTURES
WHERE organization_structure_id = X_Organization_Structure_Id;
CURSOR C2 IS SELECT PER_ORGANIZATION_STRUCTURES_S.nextval FROM sys.dual;
INSERT INTO PER_ORGANIZATION_STRUCTURES(
organization_structure_id,
business_group_id,
name,
comments,
primary_structure_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
position_control_structure_flg
) VALUES (
X_Organization_Structure_Id,
X_Business_Group_Id,
X_Name,
X_Comments,
X_Primary_Structure_Flag,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
X_Pos_Control_Structure_Flag
);
END Insert_Row;
SELECT *
FROM PER_ORGANIZATION_STRUCTURES
WHERE rowid = X_Rowid
FOR UPDATE of Organization_Structure_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Organization_Structure_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Name VARCHAR2,
X_Comments VARCHAR2,
X_Primary_Structure_Flag VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Pos_Control_Structure_Flag VARCHAR2
) IS
--
BEGIN
check_position_control_flag(X_Organization_Structure_Id,
X_Pos_Control_Structure_Flag,
X_Business_Group_Id);
UPDATE PER_ORGANIZATION_STRUCTURES
SET
organization_structure_id = X_Organization_Structure_Id,
business_group_id = X_Business_Group_Id,
name = X_Name,
comments = X_Comments,
primary_structure_flag = X_Primary_Structure_Flag,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20,
position_control_structure_flg = X_Pos_Control_Structure_Flag
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2
,p_organization_structure_id NUMBER
,p_business_group_id NUMBER
,p_pa_installed VARCHAR2) IS
BEGIN
-- do pre-delete checks
per_org_structures_pkg.delete_check(
p_organization_structure_id =>p_organization_structure_id
,p_business_group_id =>p_business_group_id
,p_pa_installed => p_pa_installed
);
DELETE FROM PER_ORGANIZATION_STRUCTURES
WHERE rowid = X_Rowid;
END Delete_Row;
select null
into l_dummy
from sys.dual
where exists( select 1
from per_security_profiles sp
where sp.organization_id is not null
and ( sp.business_group_id + 0 = p_business_group_id
or ( sp.business_group_id is null
and p_business_group_id is null))
and exists (select null
from per_org_structure_versions osv
where ( osv.business_group_id = p_business_group_id
or ( osv.business_group_id is null
and p_business_group_id is null))
and sp.organization_structure_id =
osv.organization_structure_id
and osv.org_structure_version_id = p_org_structure_version_id /* 1301741 */
and not exists
(
select null
from per_org_structure_elements ose
where ose.org_structure_version_id =
osv.org_structure_version_id
and (sp.organization_id =
ose.organization_id_child
or sp.organization_id =
ose.organization_id_parent)
)
)
);