[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
fudb.value_set_id,
fudb.user_dim1_display_code || l_elims_const elim_lob_display_code,
fudb.enabled_flag,
fudb.personal_flag,
fudb.read_only_flag,
fudb.object_version_number
FROM fem_user_dim1_b fudb,
fem_user_dim1_hier fudh
WHERE fudh.hierarchy_obj_def_id = l_hierarchy_obj_def_id
AND fudh.parent_id <> fudh.child_id
AND fudh.parent_value_set_id = fudb.value_set_id
AND fudb.user_dim1_id = fudh.parent_id
AND NOT EXISTS
(SELECT 'X'
FROM fem_user_dim1_attr fuda
WHERE fuda.value_set_id = fudb.value_set_id
AND fuda.user_dim1_id = fudb.user_dim1_id
AND fuda.attribute_id = l_attribute_id
AND fuda.version_id = l_version_id);
SELECT attribute_id
INTO l_attribute_id
FROM fem_dim_attributes_b
WHERE dimension_id = 19
AND attribute_varchar_label = 'ELIMINATION_LOB';
SELECT version_id
INTO l_version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = l_attribute_id
AND default_version_flag = 'Y';
SELECT object_definition_id
INTO l_hierarchy_obj_def_id
FROM fem_object_definition_vl
WHERE object_id = p_hierarchy_obj_id
AND display_name = p_version_name;
SELECT value_set_display_code
INTO l_vs_display_code
FROM fem_value_sets_b fvsb
WHERE fvsb.value_set_id = l_value_set_id;
INSERT INTO fem_user_dim1_b
(user_dim1_id,
value_set_id,
user_dim1_display_code,
enabled_flag,
personal_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
read_only_flag,
object_version_number,
dimension_group_id)
SELECT fnd_flex_values_s.nextval,
v_additional_lobs.value_set_id,
v_additional_lobs.elim_lob_display_code,
v_additional_lobs.enabled_flag,
v_additional_lobs.personal_flag,
sysdate,
g_fnd_user_id,
g_fnd_user_id,
sysdate,
g_fnd_login_id,
v_additional_lobs.read_only_flag,
v_additional_lobs.object_version_number,
NULL
FROM DUAL;
INSERT INTO fem_user_dim1_tl
(user_dim1_id,
value_set_id,
language,
source_lang,
user_dim1_name,
description,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login)
SELECT fudb.user_dim1_id,
fudb.value_set_id,
fl.language_code,
userenv('LANG'),
fudb.user_dim1_display_code,
fudb.user_dim1_display_code,
sysdate,
g_fnd_user_id,
g_fnd_user_id,
sysdate,
g_fnd_login_id
FROM fem_user_dim1_b fudb,
fnd_languages fl
WHERE fl.installed_flag in ('I', 'B')
AND fudb.user_dim1_display_code like '%'||l_elims_const
AND NOT EXISTS
(SELECT 'X'
FROM fem_user_dim1_tl fudt
WHERE fudt.user_dim1_id = fudb.user_dim1_id
AND fudt.language = fl.language_code);
INSERT INTO fem_user_dim1_attr
(attribute_id,
version_id,
user_dim1_id,
value_set_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag,
dim_attribute_numeric_member,
dim_attribute_varchar_member
)
SELECT fdab.attribute_id,
fdavb.version_id,
fudb.user_dim1_id,
fudb.value_set_id,
sysdate,
g_fnd_user_id,
g_fnd_user_id,
sysdate,
g_fnd_login_id,
1,
'N',
DECODE(fdab.attribute_varchar_label, 'SOURCE_SYSTEM_CODE', fdab.default_assignment, NULL),
DECODE(fdab.attribute_varchar_label, 'RECON_LEAF_NODE_FLAG', fdab.default_assignment, NULL)
FROM fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb,
fem_user_dim1_b fudb
WHERE fudb.value_set_id = l_value_set_id
AND fudb.user_dim1_display_code LIKE '%'||l_elims_const
AND fdab.dimension_id = 19
AND fdab.attribute_id = fdavb.attribute_id
AND fdavb.default_version_flag = 'Y'
AND fdab.attribute_varchar_label IN ('SOURCE_SYSTEM_CODE', 'RECON_LEAF_NODE_FLAG')
AND NOT EXISTS (SELECT 'X'
FROM fem_user_dim1_attr fuda
WHERE fuda.attribute_id = fdavb.attribute_id
AND fuda.version_id = fdavb.version_id
AND fuda.user_dim1_id = fudb.user_dim1_id
AND fuda.value_set_id = fudb.value_set_id);
INSERT INTO fem_user_dim1_attr(
attribute_id,
version_id,
user_dim1_id,
value_set_id,
dim_attribute_numeric_member,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
SELECT DISTINCT
l_attribute_id,
l_version_id,
fudb.user_dim1_id,
fudb.value_set_id,
fudb1.user_dim1_id,
sysdate,
g_fnd_user_id,
g_fnd_user_id,
sysdate,
g_fnd_login_id ,
1,
'N'
FROM fem_user_dim1_hier fudh,
fem_user_dim1_b fudb,
fem_user_dim1_b fudb1
WHERE fudh.hierarchy_obj_def_id = l_hierarchy_obj_def_id
AND fudh.parent_id <> fudh.child_id
AND fudh.parent_id = fudb.user_dim1_id
AND fudh.parent_value_set_id = fudb.value_set_id
AND fudb.value_set_id = fudb1.value_set_id
AND fudb.user_dim1_display_code||l_elims_const = fudb1.user_dim1_display_code
AND NOT EXISTS
(SELECT 'X'
FROM fem_user_dim1_attr fuda1
WHERE fuda1.attribute_id = l_attribute_id
AND fuda1.version_id = l_version_id
AND fuda1.user_dim1_id = fudb.user_dim1_id
AND fuda1.value_set_id = fudb.value_set_id);
INSERT INTO fem_hierarchies_t
(hierarchy_object_name,
folder_name,
language,
dimension_varchar_label,
hierarchy_type_code,
group_sequence_enforced_code,
multi_top_flag,
multi_value_set_flag,
hierarchy_usage_code,
flattened_rows_flag,
status,
hier_obj_def_display_name ,
effective_start_date,
effective_end_date)
SELECT p_hierarchy_name,
'Default',
USERENV('LANG'),
'USER_DIM1',
'OPEN',
'NO_GROUPS',
fh.multi_top_flag,
fh.multi_value_set_flag,
'STANDARD',
'Y',
'LOAD',
p_version_name,
fodb.effective_start_date,
fodb.effective_end_date
FROM fem_object_definition_b fodb,
fem_hierarchies fh
WHERE fodb.object_definition_id = l_hierarchy_obj_def_id
AND fodb.object_id = fh.hierarchy_obj_id;
'Insert new hierarchy value set '
||' into FEM_HIER_VALUE_SETS_T ');
INSERT INTO fem_hier_value_sets_t
(hierarchy_object_name,
value_set_display_code,
language,
status)
VALUES (p_hierarchy_name,
l_vs_display_code,
USERENV('LANG'),
'LOAD'
);
'Insert existing hierarchy members '
||' into fem_user_dim1_hier_T ');
INSERT INTO fem_user_dim1_hier_t
(hierarchy_object_name,
hierarchy_obj_def_display_name,
parent_display_code,
parent_value_set_display_code,
child_display_code,
child_value_set_display_code,
display_order_num,
status,
language)
SELECT p_hierarchy_name,
p_version_name,
parent.user_dim1_display_code,
l_vs_display_code,
child.user_dim1_display_code,
l_vs_display_code,
1,
'LOAD',
USERENV('LANG')
FROM fem_user_dim1_hier fudh,
fem_object_definition_tl fodt,
fem_object_catalog_tl foct,
fem_user_dim1_b parent,
fem_user_dim1_b child
WHERE fudh.hierarchy_obj_def_id = l_hierarchy_obj_def_id
AND fudh.hierarchy_obj_def_id = fodt.object_definition_id
AND fodt.language = userenv('LANG')
AND fodt.object_id = foct.object_id
AND foct.language = userenv('LANG')
AND parent_value_set_id = l_value_set_id
AND child_value_set_id = l_value_set_id
AND fudh.parent_id = parent.user_dim1_id
AND fudh.child_id = child.user_dim1_id
AND fudh.single_depth_flag = 'Y';
INSERT INTO fem_user_dim1_hier_t
(hierarchy_object_name,
hierarchy_obj_def_display_name,
parent_display_code,
parent_value_set_display_code,
child_display_code,
child_value_set_display_code,
display_order_num,
status,
language)
SELECT DISTINCT
p_hierarchy_name,
p_version_name,
fudh.parent_display_code,
l_vs_display_code,
fudh.parent_display_code ||l_elims_const,
l_vs_display_code ,
1,
'LOAD',
USERENV('LANG')
FROM fem_user_dim1_hier_t fudh
WHERE fudh.hierarchy_object_name = p_hierarchy_name
AND fudh.hierarchy_obj_def_display_name = p_version_name
AND fudh.parent_display_code <> fudh.child_display_code
AND fudh.parent_value_set_display_code = fudh.child_value_set_display_code;