[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select per_gen_hier_node_types_s.nextval
from sys.dual;
select 'x' from HR_LOOKUPS
where lookup_type = 'HIERARCHY_NODE_TYPE'
and lookup_code = p_child_node_type;
fnd_lookup_values_pkg.INSERT_ROW(X_ROWID => l_rowid,
X_SECURITY_GROUP_ID => 0,
X_LOOKUP_TYPE => 'HIERARCHY_NODE_TYPE',
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => l_lookup_code,
X_TAG => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => null,
X_END_DATE_ACTIVE => null,
X_TERRITORY_CODE => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_MEANING => p_child_node_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => SYSDATE ,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
procedure update_node_type
(p_validate in boolean default false
,p_effective_date in date
,p_hier_node_type_id in number
,p_object_version_number in out nocopy number
,p_child_node_name in varchar2 default hr_api.g_varchar2
,p_child_value_set in varchar2 default hr_api.g_varchar2
,p_parent_node_type in varchar2 default hr_api.g_varchar2
,p_description in varchar2 default hr_api.g_varchar2
) is
--
-- Declare cursors and local variables
--
-- return the current lookup value for the current lang, etc
-- for the child node type
CURSOR csr_lookup IS
SELECT lookup_code, created_by, meaning, description
FROM hr_lookups
WHERE lookup_type = 'HIERARCHY_NODE_TYPE'
AND lookup_code = (SELECT child_node_type
FROM per_gen_hier_node_types
WHERE hier_node_type_id = p_hier_node_type_id);
l_update_attempted boolean := false;
l_proc varchar2(80) := g_package||'update_node_type';
savepoint update_node_type;
l_update_attempted := TRUE;
If l_created_by = 1 and l_update_attempted then
-- cannot update a seeded lookup code (auto-install)
l_update_attempted := false;
fnd_message.set_name('PER','HR_289895_PGT_LOOKUP_NO_UPDATE');
If l_update_attempted then
BEGIN
-- lock the fnd_lookup_values record first....
-- Now try the update of lookup meaning and desc
-- for the child_node_type
fnd_lookup_values_pkg.UPDATE_ROW(X_LOOKUP_TYPE => 'HIERARCHY_NODE_TYPE',
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => l_lookup_code,
X_TAG => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => null,
X_END_DATE_ACTIVE => null,
X_TERRITORY_CODE => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_MEANING => p_child_node_name,
X_DESCRIPTION => p_description,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
else -- cannot update p_child_node_name to null
fnd_message.set_name('PER','HR_289897_PGT_LOOKUP_NULL');
HR_CALENDAR_NODE_TYPE_BK2.update_node_type_b
(p_effective_date => l_effective_date
,p_hier_node_type_id => p_hier_node_type_id
,p_child_node_name => p_child_node_name
,p_object_version_number => l_object_version_number
,p_child_value_set => p_child_value_set
,p_parent_node_type => p_parent_node_type
,p_description => p_description
);
(p_module_name => 'update_node_type_b'
,p_hook_type => 'BP'
);
HR_CALENDAR_NODE_TYPE_BK2.update_node_type_a
(p_effective_date => l_effective_date
,p_hier_node_type_id => p_hier_node_type_id
,p_child_node_name => p_child_node_name
,p_object_version_number => l_object_version_number
,p_child_value_set => p_child_value_set
,p_parent_node_type => p_parent_node_type
,p_description => p_description
);
(p_module_name => 'update_node_type_a'
,p_hook_type => 'AP'
);
rollback to update_node_type;
rollback to update_node_type;
end update_node_type;
procedure delete_node_type
(p_validate in boolean default false
,p_hier_node_type_id in number
,p_object_version_number in number
) is
--
-- Declare cursors and local variables
--
-- Fetch the lookup to be deleted
CURSOR csr_get_child_code IS
SELECT child_node_type
FROM per_gen_hier_node_types
WHERE hier_node_type_id = p_hier_node_type_id;
l_proc varchar2(72) := g_package||'delete_node_type';
savepoint delete_node_type;
HR_CALENDAR_NODE_TYPE_BK3.delete_node_type_b
(p_hier_node_type_id => p_hier_node_type_id,
p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_node_type_b',
p_hook_type => 'BP'
);
fnd_lookup_values_pkg.DELETE_ROW
(X_LOOKUP_TYPE => 'HIERARCHY_NODE_TYPE'
,X_SECURITY_GROUP_ID => 0
,X_VIEW_APPLICATION_ID => 3
,X_LOOKUP_CODE => l_child_node_type);
HR_CALENDAR_NODE_TYPE_BK3.delete_node_type_a
(p_hier_node_type_id => p_hier_node_type_id,
p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_node_type_a',
p_hook_type => 'AP'
);
ROLLBACK TO delete_node_type;
ROLLBACK TO delete_node_type;
end delete_node_type;
select max(level) from per_gen_hier_node_types
where hierarchy_type = p_hierarchy_type
connect by child_node_type = prior parent_node_type
start with child_node_type = p_child_node_type;
select 'Y'
from per_gen_hier_node_types
where hierarchy_type = p_hierarchy_type
and parent_node_type = p_child_node_type
and rownum = 1;
l_DeleteCode Varchar2(1) := null;
fetch csr_exist into l_DeleteCode;
RETURN nvl(l_DeleteCode,'N');
select 'Y'
from per_gen_hierarchy
where type = p_hierarchy_type
and rownum = 1;