[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hoi.org_information6
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND hoi.org_information_context = 'Representative Body';
SELECT sum(hoi.org_information2)
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND hoi.org_information_context = 'RepBody_Constituencies'
AND rowid <> p_rowid;
SELECT sum(hoi.org_information2)
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND hoi.org_information_context = 'RepBody_Constituencies';
Select posvv.org_structure_version_id version_id
From per_organization_structures_v posv,
per_org_structure_versions_v posvv
Where posvv.organization_structure_id = posv.organization_structure_id
And posv.organization_structure_id = p_organization_structure_id;
select posev.organization_id_parent org_id
from per_org_structure_elements_v posev
where posev.org_Structure_version_id = p_version_id
UNION
select posev.organization_id_child org_id
from per_org_structure_elements_v posev
where posev.org_Structure_version_id = p_version_id;
select 'Y'
into l_temp
from sys.dual
where exists (select null
from per_org_structure_elements ose
where ose.org_structure_version_id =
p_org_structure_version_id
and (ose.organization_id_child = p_organization_id
or ose.organization_id_parent = p_organization_id));
select ose.organization_id_parent
into l_parent_id
from per_org_structure_elements ose
where ose.org_structure_version_id = p_org_structure_version_id
and ose.organization_id_child = p_organization_id;
select 'Y'
from hr_organization_information hoi
where hoi.org_INFORMATION_CONTEXT = 'CLASS'
and hoi.org_information1 = p_type
and hoi.org_information2 = 'Y' -- Bug 3456540
and hoi.organization_id = p_org_id;
SELECT 0
FROM hr_organization_information ori
WHERE ori.org_information_context = 'CLASS'
AND ori.org_information1 = 'HR_BG'
AND ori.org_information2 = 'Y'
AND ori.organization_id = p_organization_id;
SELECT 0
FROM hr_all_organization_units org
,hr_all_organization_units_tl otl
,hr_organization_information ori
WHERE ori.org_information_context = 'CLASS'
AND ori.org_information1 = 'HR_BG'
AND ori.org_information2 = 'Y'
AND ori.organization_id = org.organization_id
AND otl.name = p_name
AND otl.language = userenv('LANG')
AND otl.organization_id = org.organization_id
AND org.organization_id <> p_organization_id;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Organization_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Cost_Allocation_Keyflex_Id NUMBER,
X_Location_Id NUMBER,
X_Soft_Coding_Keyflex_Id NUMBER,
X_Date_From DATE,
X_Name VARCHAR2,
X_Comments VARCHAR2,
X_Date_To DATE,
X_Internal_External_Flag VARCHAR2,
X_Internal_Address_Line VARCHAR2,
X_Type VARCHAR2,
X_Security_Profile_Id NUMBER,
X_View_All_Orgs 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
) IS
CURSOR C IS SELECT rowid FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = X_Organization_Id;
CURSOR C2 IS SELECT hr_organization_units_s.nextval FROM sys.dual;
** Insert the main organization record into the HR_ORGANIZATION_UNITS
** table.
*/
if (X_Organization_Id is NULL) then
OPEN C2;
INSERT INTO HR_ALL_ORGANIZATION_UNITS(
organization_id,
business_group_id,
cost_allocation_keyflex_id,
location_id,
soft_coding_keyflex_id,
date_from,
name,
comments,
date_to,
internal_external_flag,
internal_address_line,
type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20
) VALUES (
X_Organization_Id,
X_Business_Group_Id,
X_Cost_Allocation_Keyflex_Id,
X_Location_Id,
X_Soft_Coding_Keyflex_Id,
X_Date_From,
X_Name,
X_Comments,
X_Date_To,
X_Internal_External_Flag,
X_Internal_Address_Line,
X_Type,
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
);
insert into HR_ALL_ORGANIZATION_UNITS_TL (
-- BUSINESS_GROUP_ID,
ORGANIZATION_ID,
NAME,
LANGUAGE,
SOURCE_LANG
) select
-- X_Business_Group_Id,
X_Organization_Id,
X_Name,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from HR_ALL_ORGANIZATION_UNITS_TL T
where
-- T.BUSINESS_GROUP_ID = X_Business_Group_Id
T.ORGANIZATION_ID = X_Organization_Id
and T.LANGUAGE = L.LANGUAGE_CODE);
hr_utility.set_message_token('PROCEDURE','Insert_row');
END Insert_Row;
SELECT *
FROM HR_ALL_ORGANIZATION_UNITS
WHERE rowid = X_Rowid
FOR UPDATE of Organization_Id NOWAIT;
SELECT oru.*,orutl.name name_tl
FROM HR_ALL_ORGANIZATION_UNITS ORU,
HR_ALL_ORGANIZATION_UNITS_TL ORUTL
WHERE ORU.rowid = X_Rowid
AND ORU.organization_id = ORUTL.organization_id
AND ORUTL.language = userenv('LANG')
FOR UPDATE of ORU.Organization_Id NOWAIT;
cursor c1 is select
NAME,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from HR_ALL_ORGANIZATION_UNITS_TL
where
-- BUSINESS_GROUP_ID = X_Business_Group_Id
ORGANIZATION_ID = X_Organization_Id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of ORGANIZATION_ID nowait;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Organization_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Cost_Allocation_Keyflex_Id NUMBER,
X_Location_Id NUMBER,
X_Soft_Coding_Keyflex_Id NUMBER,
X_Date_From DATE,
X_Name VARCHAR2,
X_Comments VARCHAR2,
X_Date_To DATE,
X_Internal_External_Flag VARCHAR2,
X_Internal_Address_Line VARCHAR2,
X_Type 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
) IS
BEGIN
--
validate_business_group_name
(p_organization_id => X_Organization_Id
,p_name => X_Name
);
UPDATE HR_ALL_ORGANIZATION_UNITS
SET
organization_id = X_Organization_Id,
business_group_id = X_Business_Group_Id,
cost_allocation_keyflex_id = X_Cost_Allocation_Keyflex_Id,
location_id = X_Location_Id,
soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id,
date_from = X_Date_From,
name = X_Name,
comments = X_Comments,
date_to = X_Date_To,
internal_external_flag = X_Internal_External_Flag,
internal_address_line = X_Internal_Address_Line,
type = X_Type,
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
WHERE rowid = X_rowid;
hr_utility.set_message_token('PROCEDURE','Update_Row');
update HR_ALL_ORGANIZATION_UNITS_TL set
NAME = X_Name,
SOURCE_LANG = userenv('LANG')
where
-- BUSINESS_GROUP_ID = X_Business_Group_Id
ORGANIZATION_ID = X_Organization_Id
and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
END Update_Row;
select '1'
from HR_ORGANIZATION_INFORMATION
where organization_id = X_Organization_Id
and org_information1 = X_Organization_Class
and org_information_context = 'CLASS';
PROCEDURE Validate_delete (X_Organization_Id NUMBER,
X_Business_Group_Id Number) IS
cursor csr_employer is
select '1'
from per_collective_agreements_v
where employer_organization_id = X_Organization_Id;
select '1'
from per_collective_agreements_v
where bargaining_organization_id = X_Organization_Id;
select legislation_code
from per_business_groups
where business_group_id = X_Business_Group_id;
select '1'
from user_source
where name = p_pkg_name
and rownum < 2;*/
select '1'
from user_objects
where object_name = p_pkg_name
and object_type = 'PACKAGE';
l_package_name := 'PER_'||l_leg_code||'_VALIDATE_DELETE_PKG';
l_procedure_name := 'VALIDATE_DELETE';
END Validate_delete;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Business_Group_Id NUMBER,
X_Organization_Id NUMBER,
X_View_All_Orgs VARCHAR2) IS
BEGIN
--
-- Delete the Organization from PER_ORGANIZATION_LIST.
--
Validate_delete(X_Organization_Id => X_Organization_Id,
X_Business_Group_Id => X_Business_Group_Id);
delete from HR_ALL_ORGANIZATION_UNITS_TL
where
-- BUSINESS_GROUP_ID = X_Business_Group_Id
ORGANIZATION_ID = X_Organization_Id;
hr_security.delete_org_from_list(X_Organization_Id);
DELETE FROM HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = X_Business_Group_Id
AND ORGANIZATION_ID = X_Organization_Id;
hr_utility.set_message_token('PROCEDURE','Delete_Row');
END Delete_Row;
l_sql_text := 'select hoi.ORG_INFORMATION1 '
||'from hr_organization_information hoi '
||'where hoi.organization_id = '||to_char(X_ORGANIZATION_ID)||' '
||'and hoi.org_information_context = ''Accounting Information''';
l_sql_text := 'select mp.organization_code '
||'from mtl_parameters mp '
||'where mp.organization_id = '|| to_char(X_ORGANIZATION_ID);
l_sql_text := 'select gsb.chart_of_accounts_id '
||' from gl_sets_of_books gsb '
||' where gsb.set_of_books_id = '||to_char(X_SOB_ID);
delete from HR_ALL_ORGANIZATION_UNITS_TL T
where not exists
(select NULL
from HR_ALL_ORGANIZATION_UNITS B
where B.ORGANIZATION_ID = T.ORGANIZATION_ID
);
update HR_ALL_ORGANIZATION_UNITS_TL T set (
NAME
) = (select
B.NAME
from HR_ALL_ORGANIZATION_UNITS_TL B
where B.ORGANIZATION_ID = T.ORGANIZATION_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ORGANIZATION_ID,
T.LANGUAGE
) in (select
SUBT.ORGANIZATION_ID,
SUBT.LANGUAGE
from HR_ALL_ORGANIZATION_UNITS_TL SUBB, HR_ALL_ORGANIZATION_UNITS_TL SUBT
where SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME <> SUBT.NAME
));
insert into HR_ALL_ORGANIZATION_UNITS_TL (
ORGANIZATION_ID,
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.ORGANIZATION_ID,
B.NAME,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from HR_ALL_ORGANIZATION_UNITS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from HR_ALL_ORGANIZATION_UNITS_TL T
where T.ORGANIZATION_ID = B.ORGANIZATION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT 1
FROM hr_all_organization_units_tl orgt,
hr_all_organization_units org
WHERE upper(orgt.name)=upper(p_org_name)
AND orgt.organization_id = org.organization_id
AND orgt.language = p_language
AND (org.organization_id <> p_org_id OR p_org_id IS NULL)
AND (org.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
;