[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO l_user_assigned_flag
FROM fem_user_folders fuf
WHERE fuf.folder_id = 1100
AND fuf.user_id = FND_GLOBAL.USER_ID;
SELECT 'Y'
INTO l_user_assigned_flag
FROM fem_user_folders fuf
WHERE fuf.folder_id = 1000
AND fuf.user_id = FND_GLOBAL.USER_ID;
SELECT folder_name
INTO l_folder_name
FROM fem_folders_vl
WHERE folder_id = 1100;
SELECT hierarchy_name,
ghv.start_date,
feb.entity_display_code
INTO l_hierarchy_name,
l_start_date,
l_top_entity_display_code
FROM gcs_hierarchies_vl ghv,
gcs_hierarchies_b ghb,
fem_entities_b feb
WHERE ghb.hierarchy_id = p_hierarchy_id
AND ghb.top_entity_id = feb.entity_id
AND ghv.hierarchy_id = p_hierarchy_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
)
VALUES
( l_hierarchy_name,
l_folder_name,
USERENV('LANG'),
'ENTITY',
'OPEN',
'NO_GROUPS',
'N',
'N',
'STANDARD',
'N',
'LOAD',
l_hierarchy_name,
l_start_date
);
INSERT INTO fem_hier_value_sets_t
( hierarchy_object_name,
value_set_display_code,
language,
status
)
VALUES
( l_hierarchy_name,
'ENTITY',
USERENV('LANG'),
'LOAD'
);
INSERT INTO fem_entities_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,
weighting_pct,
status,
language)
SELECT
l_hierarchy_name,
l_hierarchy_name,
fev_p.entity_display_code,
'ENTITY',
fev_c.entity_display_code,
'ENTITY',
1,
NULL,
'LOAD',
USERENV('LANG')
FROM fem_entities_b fev_p,
fem_entities_b fev_c,
gcs_cons_relationships gcr
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = fev_p.entity_id
AND l_start_date BETWEEN gcr.start_date AND NVL(gcr.end_date, l_start_date)
AND gcr.dominant_parent_flag= 'Y'
AND gcr.child_entity_id = fev_c.entity_id;
INSERT INTO fem_entities_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,
weighting_pct,
status,
language
)
VALUES
(
l_hierarchy_name,
l_hierarchy_name,
l_top_entity_display_code,
'ENTITY',
l_top_entity_display_code,
'ENTITY',
1,
NULL,
'LOAD',
USERENV('LANG')
);
SELECT gcr.parent_entity_id,
gcr.child_entity_id,
gcr.start_date,
gcr.end_date
FROM gcs_cons_relationships gcr
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.child_entity_id = p_child_entity_id;
SELECT gcr.parent_entity_id,
gcr.child_entity_id,
gcr.start_date,
fea.dim_attribute_varchar_member,
substr(ghv.hierarchy_name,1,110) || ' effective since'
INTO l_parent_entity_id,
l_child_entity_id,
l_start_date,
l_entity_type,
l_hierarchy_name
FROM gcs_cons_relationships gcr,
fem_entities_attr fea,
gcs_hierarchies_vl ghv
WHERE gcr.cons_relationship_id = p_cons_relationship_id
AND gcr.hierarchy_id = ghv.hierarchy_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.child_entity_id = fea.entity_id
AND fea.attribute_id = l_entity_type_attr
AND fea.version_id = l_entity_type_version;
SELECT fodb.object_definition_id
INTO l_object_definition_id
FROM fem_object_definition_vl fodb,
fem_hierarchies fh
WHERE fh.hierarchy_obj_id = fodb.object_id
AND fh.dimension_id = 18
AND fodb.display_name like l_hierarchy_name || '%'
AND l_start_date between fodb.effective_start_date and fodb.effective_end_date
AND effective_end_date = TO_DATE('01-01-2500','DD-MM-YYYY');
UPDATE fem_object_definition_b
SET effective_end_date = l_start_date
WHERE object_definition_id = l_object_definition_id;
SELECT feh.parent_depth_num
INTO l_parent_depth_num
FROM fem_entities_hier feh
WHERE feh.hierarchy_obj_def_id = l_object_definition_id
AND feh.parent_id = l_parent_entity_id
AND feh.single_depth_flag = 'Y'
AND ROWNUM < 2;
SELECT feh.child_depth_num
INTO l_parent_depth_num
FROM fem_entities_hier feh
WHERE feh.hierarchy_obj_def_id = l_object_definition_id
AND feh.child_id = l_parent_entity_id
AND feh.single_depth_flag = 'Y'
AND ROWNUM < 2;
INSERT INTO fem_entities_hier
(hierarchy_obj_def_id,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
read_only_flag
)
VALUES
(l_object_definition_id,
l_parent_depth_num,
l_parent_entity_id,
18,
l_parent_depth_num + 1,
l_child_entity_id,
18,
'Y',
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
1,
'N'
);
INSERT INTO fem_entities_hier
(hierarchy_obj_def_id,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
read_only_flag
)
SELECT l_object_definition_id,
l_parent_depth_num + 1,
l_child_entity_id,
18,
l_parent_depth_num + 2,
fea.dim_attribute_numeric_member,
18,
'Y',
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
1,
'N'
FROM fem_entities_attr fea
WHERE fea.entity_id = l_child_entity_id
AND fea.attribute_id IN (l_oper_entity_attr, l_elim_entity_attr)
AND fea.version_id IN (l_oper_entity_version, l_elim_entity_version);