[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT display_order_num,
application_column_name,
top_parent_value
FROM fem_intg_hier_def_segs
WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
ORDER BY display_order_num;
SELECT nvl(h.hierarchy_obj_id, -1), o.object_name
INTO pv_hier_obj_id,
pv_hier_rule_obj_name
FROM fem_object_definition_b b,
fem_object_catalog_vl o,
fem_intg_hier_rules h
WHERE b.object_definition_id = pv_hier_rule_obj_def_id
AND b.object_id = o.object_id
AND o.object_id = h.hier_rule_obj_id;
SELECT nvl(hier_obj_def_id,-1)
INTO pv_hier_obj_def_id
FROM fem_intg_hier_def_segs
WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
AND display_order_num = 1;
SELECT DIM_MAPPING_OPTION_CODE
INTO pv_dim_mapping_option_code
FROM fem_intg_hier_rules h,
fem_intg_dim_rule_defs d
WHERE HIER_RULE_OBJ_ID = pv_hier_rule_obj_id
AND h.DIM_RULE_OBJ_DEF_ID = d.DIM_RULE_OBJ_DEF_ID;
SELECT dr.chart_of_accounts_id,
dr.dimension_id,
drf.fem_value_set_id,
seg.top_parent_value,
v.flex_value_id,
d.member_vl_object_name,
d.member_tl_table_name,
d.member_b_table_name,
d.member_col,
d.member_display_code_col,
d.member_name_col,
d.member_description_col,
d.hierarchy_table_name,
d.attribute_table_name,
drf.segment_count,
hir.dim_rule_obj_def_id,
dr.dim_rule_obj_id,
v.flex_value_set_id,
nvl(hir.flatten_hier_flag,'N'),
nvl(hir.sequence_enforced_flag,'N')
INTO pv_coa_id,
pv_dim_id,
pv_dim_vs_id,
pv_top_parent_disp_code,
pv_top_parent_id,
pv_dim_memb_vl_obj,
pv_dim_memb_tl_tab,
pv_dim_memb_b_tab,
pv_dim_memb_col,
pv_dim_memb_disp_col,
pv_dim_memb_name_col,
pv_dim_memb_desc_col,
pv_dim_hier_tab,
pv_dim_attr_tab,
pv_segment_count,
pv_dim_rule_obj_def_id,
pv_dim_rule_obj_id,
pv_aol_vs_id,
pv_flatten_hier_flag,
pv_sequence_enforced_flag
FROM fem_intg_hier_rules hir,
fem_object_definition_b b1,
fem_intg_dim_rules dr,
fem_intg_dim_rule_defs drf,
fem_intg_hier_def_segs seg,
fnd_flex_values v,
fem_xdim_dimensions d,
fem_intg_aol_valset_map m
WHERE hir.hier_rule_obj_id = pv_hier_rule_obj_id
AND b1.object_definition_id = hir.dim_rule_obj_def_id
AND dr.dim_rule_obj_id = b1.object_id
AND drf.dim_rule_obj_def_id = hir.dim_rule_obj_def_id
AND seg.hier_rule_obj_def_id = pv_hier_rule_obj_def_id
AND drf.fem_value_set_id = m.fem_value_set_id
AND v.flex_value_set_id = m.segment1_value_set_id
AND v.flex_value = seg.top_parent_value
AND d.dimension_id = dr.dimension_id;
SELECT distinct dr.chart_of_accounts_id,
dr.dimension_id,
drf.fem_value_set_id,
d.member_vl_object_name,
d.member_tl_table_name,
d.member_b_table_name,
d.member_col,
d.member_display_code_col,
d.member_name_col,
d.member_description_col,
d.hierarchy_table_name,
d.attribute_table_name,
drf.segment_count,
hir.dim_rule_obj_def_id,
dr.dim_rule_obj_id,
m.segment1_value_set_id,
m.segment2_value_set_id,
m.segment3_value_set_id,
m.segment4_value_set_id,
m.segment5_value_set_id,
nvl(hir.flatten_hier_flag,'N'),
nvl(hir.sequence_enforced_flag,'N'),
drf.application_column_name1,
drf.application_column_name2,
drf.application_column_name3,
drf.application_column_name4,
drf.application_column_name5
INTO pv_coa_id,
pv_dim_id,
pv_dim_vs_id,
pv_dim_memb_vl_obj,
pv_dim_memb_tl_tab,
pv_dim_memb_b_tab,
pv_dim_memb_col,
pv_dim_memb_disp_col,
pv_dim_memb_name_col,
pv_dim_memb_desc_col,
pv_dim_hier_tab,
pv_dim_attr_tab,
pv_segment_count,
pv_dim_rule_obj_def_id,
pv_dim_rule_obj_id,
v_aol_vs_id1,
v_aol_vs_id2,
v_aol_vs_id3,
v_aol_vs_id4,
v_aol_vs_id5,
pv_flatten_hier_flag,
pv_sequence_enforced_flag,
v_app_col_name1,
v_app_col_name2,
v_app_col_name3,
v_app_col_name4,
v_app_col_name5
FROM fem_intg_hier_rules hir,
fem_object_definition_b b1,
fem_intg_dim_rules dr,
fem_intg_dim_rule_defs drf,
fem_intg_hier_def_segs seg,
fnd_flex_values v,
fem_xdim_dimensions d,
fem_intg_aol_valset_map m
WHERE hir.hier_rule_obj_id = pv_hier_rule_obj_id
AND b1.object_definition_id = hir.dim_rule_obj_def_id
AND dr.dim_rule_obj_id = b1.object_id
AND drf.dim_rule_obj_def_id = hir.dim_rule_obj_def_id
AND seg.hier_rule_obj_def_id = pv_hier_rule_obj_def_id
AND drf.fem_value_set_id = m.fem_value_set_id
AND v.flex_value_set_id = m.segment1_value_set_id
AND v.flex_value = seg.top_parent_value
AND d.dimension_id = dr.dimension_id;
pv_traversal_rarray.DELETE;
SELECT decode(v_traversal_info.application_column_name,
v_app_col_name1, v_aol_vs_id1,
v_app_col_name2, v_aol_vs_id2,
v_app_col_name3, v_aol_vs_id3,
v_app_col_name4, v_aol_vs_id4,
v_app_col_name5, v_aol_vs_id5,
null) INTO l_rec.aol_vs_id FROM DUAL;
SELECT decode(v_traversal_info.application_column_name,
v_app_col_name1, 1,
v_app_col_name2, 2,
v_app_col_name3, 3,
v_app_col_name4, 4,
v_app_col_name5, 5,
null) INTO l_rec.concat_segment FROM DUAL;
select flex_value_id into l_rec.top_parent_id
from fnd_flex_values
where flex_value_set_id=l_rec.aol_vs_id
and flex_value= l_rec.top_parent_value ;
SELECT global_vs_combo_id
INTO pv_gvsc_id
FROM fem_intg_coa_gvsc_map
WHERE chart_of_accounts_id = pv_coa_id
AND effective_start_date <= pv_hier_rule_start_date
AND effective_end_date >= pv_hier_rule_end_date;
INSERT INTO fem_intg_dim_hier_gt
(hierarchy_obj_def_id,
parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
dimension_group_id)
VALUES
(pv_hier_obj_def_id,
1,
pv_top_parent_id,
pv_top_parent_disp_code,
1,
pv_top_parent_id,
pv_top_parent_disp_code,
'Y',
1,
pv_top_dimension_group_id);
'INSERT INTO fem_intg_dim_hier_gt
(hierarchy_obj_def_id,
parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
dimension_group_id)
SELECT DISTINCT :pv_hier_obj_def_id,
gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
''Y'',
-1,
:v_dimension_group_id
FROM fem_intg_dim_hier_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE gt.child_depth_num = :v_parent_level
AND vh.flex_value_set_id = :pv_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = ''P''
AND ff.flex_value_set_id = :pv_aol_vs_id
AND ff.summary_flag = ''Y''
AND ff.flex_value
BETWEEN vh.child_flex_value_low
AND vh.child_flex_value_high
ORDER BY ff.flex_value';
'UPDATE fem_intg_dim_hier_gt
SET display_order_num = '||v_seq_name||'.nextval
WHERE rowid in
(select rowid
from fem_intg_dim_hier_gt)
AND display_order_num = -1';
p_module => 'fem.plsql.'||'insert into fem_hier_dimension_grps',
p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id ||
' v_rel_dim_group_seq:' || v_rel_dim_group_seq);
INSERT INTO fem_hier_dimension_grps
(dimension_group_id,
hierarchy_obj_id,
relative_dimension_group_seq,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(v_dimension_group_id,
pv_hier_obj_id,
v_rel_dim_group_seq,
sysdate,
pv_user_id,
pv_user_id,
sysdate,
pv_login_id,
1);
p_module => 'fem.plsql.'||'insert into fem_hier_dimension_grps',
p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id ||
' v_rel_dim_group_seq:' || v_rel_dim_group_seq);
INSERT INTO fem_hier_dimension_grps
(dimension_group_id,
hierarchy_obj_id,
relative_dimension_group_seq,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(v_dimension_group_id,
pv_hier_obj_id,
v_rel_dim_group_seq,
sysdate,
pv_user_id,
pv_user_id,
sysdate,
pv_login_id,
1);
'INSERT INTO fem_intg_dim_hier_gt
(hierarchy_obj_def_id,
parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
dimension_group_id)
SELECT DISTINCT '
||pv_hier_obj_def_id||',
gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
m.'||pv_dim_memb_col||',
m.'||pv_dim_memb_disp_col||',
''Y'',
-1,
:dgid
FROM fem_intg_dim_hier_gt gt,
fnd_flex_value_norm_hierarchy vh,
'||pv_dim_memb_b_tab||' m
WHERE vh.flex_value_set_id = '||pv_aol_vs_id||'
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = ''C''
AND m.value_set_id = '||pv_dim_vs_id||'
AND m.'||pv_dim_memb_disp_col||'
BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
AND m.'||pv_dim_memb_col||' NOT IN
(SELECT inner_gt.child_id
FROM fem_intg_dim_hier_gt inner_gt)
AND m.'||pv_dim_memb_col||' NOT IN
(SELECT mh.parent_id
FROM '||pv_dim_hier_tab||' mh,
fem_object_definition_b odb,
fem_intg_hier_rules ihr
WHERE mh.hierarchy_obj_def_id = odb.object_definition_id
AND odb.object_id = ihr.hierarchy_obj_id
AND ihr.dim_rule_obj_def_id = '||pv_dim_rule_obj_def_id||'
AND mh.parent_value_set_id = '||pv_dim_vs_id||'
AND mh.child_value_set_id = '||pv_dim_vs_id||'
AND mh.child_id <> mh.parent_id)
ORDER BY m.'||pv_dim_memb_disp_col;
'UPDATE fem_intg_dim_hier_gt
SET display_order_num = '||v_seq_name||'.nextval
WHERE rowid in
(select rowid
from fem_intg_dim_hier_gt)
AND display_order_num = -1';
SELECT dimension_id
INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_com_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'COMPANY';
SELECT dimension_id
INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'COST_CENTER';
SELECT value_set_id
INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_com_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = pv_gvsc_id
AND dimension_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_com_dim_id;
SELECT value_set_id
INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = pv_gvsc_id
AND dimension_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_dim_id;
INSERT INTO fem_intg_dim_hier_c1_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
VALUES
(1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
'Y',
1,
'N');
INSERT INTO fem_intg_dim_hier_c2_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
VALUES
(1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
'Y',
1,
'N');
INSERT INTO fem_intg_dim_hier_c3_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
VALUES
(1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
'Y',
1,
'N');
INSERT INTO fem_intg_dim_hier_c4_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
VALUES
(1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
'Y',
1,
'N');
INSERT INTO fem_intg_dim_hier_c5_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
VALUES
(1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
1,
pv_traversal_rarray(i_hier_ctr).top_parent_id,
pv_traversal_rarray(i_hier_ctr).top_parent_value,
'Y',
1,
'N');
END CASE; -- End initial insert
INSERT INTO fem_intg_dim_hier_c1_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'N'
FROM fem_intg_dim_hier_c1_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE gt.child_depth_num = v_parent_level
AND vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'P'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'Y'
AND ff.flex_value
BETWEEN vh.child_flex_value_low
AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c2_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'N'
FROM fem_intg_dim_hier_c2_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE gt.child_depth_num = v_parent_level
AND vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'P'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'Y'
AND ff.flex_value
BETWEEN vh.child_flex_value_low
AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c3_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'N'
FROM fem_intg_dim_hier_c3_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE gt.child_depth_num = v_parent_level
AND vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'P'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'Y'
AND ff.flex_value
BETWEEN vh.child_flex_value_low
AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c4_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'N'
FROM fem_intg_dim_hier_c4_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE gt.child_depth_num = v_parent_level
AND vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'P'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'Y'
AND ff.flex_value
BETWEEN vh.child_flex_value_low
AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c5_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'N'
FROM fem_intg_dim_hier_c5_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE gt.child_depth_num = v_parent_level
AND vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'P'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'Y'
AND ff.flex_value
BETWEEN vh.child_flex_value_low
AND vh.child_flex_value_high
ORDER BY ff.flex_value;
p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Component_Hier.Intermediate_Inserted.',
p_msg_text => ' v_row_count2:' || v_row_count2 ||
' v_parent_level :' || v_parent_level );
'UPDATE fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt
SET display_order_num = '||v_seq_name||'.nextval
WHERE display_order_num = -1';
INSERT INTO fem_intg_dim_hier_c1_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'Y'
FROM fem_intg_dim_hier_c1_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'C'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'N'
AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c2_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'Y'
FROM fem_intg_dim_hier_c2_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'C'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'N'
AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c3_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'Y'
FROM fem_intg_dim_hier_c3_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'C'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'N'
AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c4_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'Y'
FROM fem_intg_dim_hier_c4_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'C'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'N'
AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_c5_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
ff.flex_value_id,
ff.flex_value,
'Y',
-1,
'Y'
FROM fem_intg_dim_hier_c5_gt gt,
fnd_flex_value_norm_hierarchy vh,
fnd_flex_values ff
WHERE vh.flex_value_set_id = v_aol_vs_id
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'C'
AND ff.flex_value_set_id = v_aol_vs_id
AND ff.summary_flag = 'N'
AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
ORDER BY ff.flex_value;
INSERT INTO fem_intg_dim_hier_gt
(hierarchy_obj_def_id,
parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT 1,
gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
-1,
MEANING,
'Y',
-1,
'Y'
FROM fem_intg_dim_hier_gt gt,
FND_LOOKUPS,
fnd_flex_value_norm_hierarchy vh
WHERE FND_LOOKUPS.LOOKUP_TYPE = 'YES_NO'
AND vh.flex_value_set_id = 1002723
AND gt.hierarchy_obj_def_id = 1
AND vh.parent_flex_value = gt.child_display_code
AND vh.range_attribute = 'C'
AND MEANING
BETWEEN vh.child_flex_value_low
AND vh.child_flex_value_high
ORDER BY MEANING
*/
FEM_ENGINES_PKG.Tech_Message
(p_severity => pc_log_level_procedure,
p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.Bottom_User_table.',
p_msg_text => 'pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
' traversal hierarchy_obj_def_id:' || pv_traversal_rarray(i_hier_ctr).display_order);
Select ADDITIONAL_WHERE_CLAUSE
into v_add_where
from fnd_flex_validation_tables
where pv_traversal_rarray(i_hier_ctr).aol_vs_id = FLEX_VALUE_SET_ID;
V_sql_stmt_start := 'INSERT INTO fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt
(parent_depth_num,
parent_id,
parent_display_code,
child_depth_num,
child_id,
child_display_code,
single_depth_flag,
display_order_num,
child_leaf_flag)
SELECT DISTINCT gt.child_depth_num,
gt.child_id,
gt.child_display_code,
(gt.child_depth_num + 1),
-1,
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).val_col_name || ',
''Y'',
-1,
''Y''
FROM fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt gt,
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).table_name || ',
fnd_flex_value_norm_hierarchy vh
';
END CASE; -- end bottom leaf inserts
'UPDATE fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt
SET display_order_num = '||v_seq_name||'.nextval
WHERE display_order_num = -1';
SELECT 'Duplicate'
INTO v_duplicate_parent
FROM dual
WHERE EXISTS
(SELECT gt.child_display_code
FROM fem_intg_dim_hier_c1_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
GROUP BY gt.child_display_code
HAVING count(gt.child_display_code) > 1);
SELECT 'Duplicate'
INTO v_duplicate_parent
FROM dual
WHERE EXISTS
(SELECT gt.child_display_code
FROM fem_intg_dim_hier_c2_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
GROUP BY gt.child_display_code
HAVING count(gt.child_display_code) > 1);
SELECT 'Duplicate'
INTO v_duplicate_parent
FROM dual
WHERE EXISTS
(SELECT gt.child_display_code
FROM fem_intg_dim_hier_c3_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
GROUP BY gt.child_display_code
HAVING count(gt.child_display_code) > 1);
SELECT 'Duplicate'
INTO v_duplicate_parent
FROM dual
WHERE EXISTS
(SELECT gt.child_display_code
FROM fem_intg_dim_hier_c4_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
GROUP BY gt.child_display_code
HAVING count(gt.child_display_code) > 1);
SELECT 'Duplicate'
INTO v_duplicate_parent
FROM dual
WHERE EXISTS
(SELECT gt.child_display_code
FROM fem_intg_dim_hier_c5_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
GROUP BY gt.child_display_code
HAVING count(gt.child_display_code) > 1);
'SELECT gt.child_display_code
FROM fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
GROUP BY gt.child_display_code
HAVING count(gt.child_display_code) > 1';
'SELECT DISTINCT gt.parent_display_code
FROM fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
and gt.child_display_code = :child_display_code' USING v_display_code;
update FEM_INTG_DIM_HIER_GT
set child_display_code = substr(child_display_code, 1, pc_max_disp_len)
, parent_display_code = substr(parent_display_code, 1, pc_max_disp_len);
INSERT INTO FEM_INTG_DIM_HIER_GT
(HIERARCHY_OBJ_DEF_ID
, child_display_code
, child_id
, parent_display_code
, parent_id
, child_depth_num
, parent_depth_num
, child_leaf_flag
, single_depth_flag
, display_order_num
)
(SELECT 20861
, gt1.child_display_code || '-' || gt2.child_display_code || '-' || gt3.child_display_code || '-' ||
gt4.child_display_code || '-' || gt5.child_display_code, b.CUSTOMER_ID ,gt1.parent_display_code || '-' ||
gt2.child_display_code || '-' || gt3.child_display_code || '-' || gt4.child_display_code || '-' ||
gt5.child_display_code
, -1
, (gt1.child_depth_num + gt2.child_depth_num + gt3.child_depth_num + gt4.child_depth_num + gt5.child_depth_num- 5 + 1)
, (gt1.parent_depth_num + gt2.parent_depth_num + gt3.parent_depth_num + gt4.parent_depth_num + gt5.parent_depth_num)
, 'Y'
, 'N'
, -1
from
FEM_INTG_DIM_HIER_GT gt1
, FEM_INTG_DIM_HIER_GT gt2
, FEM_INTG_DIM_HIER_GT gt3
, FEM_INTG_DIM_HIER_GT gt4
, FEM_INTG_DIM_HIER_GT gt5
, FEM_CUSTOMERS_B b
WHERE gt1.hierarchy_obj_def_id = 1
and gt1.child_leaf_flag = 'Y'
and gt2.hierarchy_obj_def_id = 2
and gt2.child_leaf_flag = 'Y'
and gt3.hierarchy_obj_def_id = 3
and gt3.child_leaf_flag = 'Y'
and gt4.hierarchy_obj_def_id = 4
and gt4.child_leaf_flag = 'Y'
and gt5.hierarchy_obj_def_id = 5
and gt5.child_leaf_flag = 'Y'
and b.CUSTOMER_DISPLAY_CODE = gt1.child_display_code || '-' || gt2.child_display_code || '-' || gt3.child_display_code || '-' || gt4.child_display_code || '-' || gt5.child_display_code
and b.value_set_id = :pv_aol_vs_id)
*/
v_sql := 'INSERT INTO FEM_INTG_DIM_HIER_GT
(HIERARCHY_OBJ_DEF_ID
, child_display_code
, child_id
, parent_display_code
, parent_id
, child_depth_num
, parent_depth_num
, child_leaf_flag
, single_depth_flag
, display_order_num
)
(SELECT ' || pv_hier_obj_def_id || '
, ';
select count(*) into v_debug from FEM_INTG_DIM_HIER_GT;
INSERT INTO FEM_INTG_DIM_HIER_GT
(HIERARCHY_OBJ_DEF_ID
, child_display_code
, child_id
, parent_display_code
, parent_id
, child_depth_num
, parent_depth_num
, child_leaf_flag
, single_depth_flag
, display_order_num
--, counter_num
)'
(SELECT distinct 20861
, gtm.parent_display_code
, gtm.parent_id
------------ before hierarchy rule performance fix
, decode(1,
1, gts.parent_display_code || '-' || substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-')+1),
5, substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 5-1)-1) || '-' || gts.parent_display_code,
substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 0)-1) || '-' ||
gts.parent_display_code || '-' ||
substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 1)+1))
------------ before hierarchy rule performance fix
------------ after (one of the three)
1. , gts.parent_display_code || '-' || substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-')+1)
2. , substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 5-1)-1) || '-' || gts.parent_display_code
3. , substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 0)-1) || '-' ||
gts.parent_display_code || '-' ||
substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 1)+1))
------------ after (one of the three)
, -1
, gtm.parent_depth_num
, gtm.parent_depth_num-1
, 'N'
, 'N'
, -1
--, 1
FROM
FEM_INTG_DIM_HIER_GT gtm
, FEM_INTG_DIM_HIER_GT gts
------------ WHERE gtm.HIERARCHY_OBJ_DEF_ID = :pv_hier_obj_def_id
------------ AND gts.hierarchy_obj_def_id = :display_order
------------ AND gts.child_display_code =
WHERE gts.child_display_code =
------------ before hierarchy rule performance fix
decode(1,
1, substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-')-1),
5, substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1),
substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1,
instr(gtm.parent_display_code, '-', 1, 1)-instr(gtm.parent_display_code, '-', 1, 0)-1))
------------ before hierarchy rule performance fix
------------ after (one of the three)
1. substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-')-1)
2. substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1)
3. substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1,
instr(gtm.parent_display_code, '-', 1, 1)-instr(gtm.parent_display_code, '-', 1, 0)-1))
------------ after (one of the three)
AND gts.child_display_code <> gts.parent_display_code
and not exists (select 1 from FEM_INTG_DIM_HIER_GT gte where gte.child_display_code = gtm.parent_display_code)
*/
v_sql := 'INSERT INTO FEM_INTG_DIM_HIER_GT
(HIERARCHY_OBJ_DEF_ID
, child_display_code
, child_id
, parent_display_code
, parent_id
, child_depth_num
, parent_depth_num
, child_leaf_flag
, single_depth_flag
, display_order_num
)
(SELECT distinct ' || pv_hier_obj_def_id || '
, gtm.parent_display_code
, gtm.parent_id
, ';
and not exists (select 1 from FEM_INTG_DIM_HIER_GT gte where gte.child_display_code = gtm.parent_display_code))';
select count(*) into v_debug from FEM_INTG_DIM_HIER_GT;
select count(*) into v_debug from FEM_INTG_DIM_HIER_GT;
INSERT INTO FEM_INTG_DIM_HIER_GT
(HIERARCHY_OBJ_DEF_ID
, child_display_code
, child_id
, parent_display_code
, parent_id
, child_depth_num
, parent_depth_num
, child_leaf_flag
, single_depth_flag
, display_order_num
)
(SELECT distinct pv_hier_obj_def_id
, gtm.parent_display_code
, gtm.parent_id
, gtm.parent_display_code
, gtm.parent_id
, gtm.parent_depth_num
, gtm.parent_depth_num
, 'N'
, 'N'
, -1
FROM FEM_INTG_DIM_HIER_GT gtm
WHERE not exists (select 1 from FEM_INTG_DIM_HIER_GT gte where gte.child_display_code = gtm.parent_display_code));
update fem_intg_dim_hier_gt
set dimension_group_id = v_dimension_group_id
where child_depth_num = v_counter;
'UPDATE fem_intg_dim_hier_gt
SET display_order_num = '||v_seq_name||'.nextval
WHERE child_depth_num = '||v_counter||' AND display_order_num = -1';
p_module => 'fem.plsql.'||'insert into fem_hier_dimension_grps',
p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id || ' v_rel_dim_group_seq:' || v_counter);
INSERT INTO fem_hier_dimension_grps
(dimension_group_id,
hierarchy_obj_id,
relative_dimension_group_seq,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(v_dimension_group_id,
pv_hier_obj_id,
v_counter,
sysdate,
pv_user_id,
pv_user_id,
sysdate,
pv_login_id,
1);
v_sql := 'update FEM_INTG_DIM_HIER_GT gt
set gt.parent_id = (select distinct b1.' || fem_intg_new_dim_member_pkg.pv_local_member_col || ' from ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name || ' b1 ';
v_sql := v_sql || ', gt.child_id = (select distinct b2.' || fem_intg_new_dim_member_pkg.pv_local_member_col || ' from ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name || ' b2 ';
SELECT gt.child_display_code
FROM fem_intg_dim_hier_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
and gt.hierarchy_obj_def_id = pv_hier_obj_def_id
GROUP BY gt.child_display_code
HAVING count(gt.child_display_code) > 1;
SELECT DISTINCT gt.parent_display_code
FROM fem_intg_dim_hier_gt gt
WHERE gt.parent_display_code <> gt.child_display_code
and gt.hierarchy_obj_def_id = pv_hier_obj_def_id
and gt.child_display_code = p_child_display_code;
SELECT o.object_id, o.folder_id, b.object_definition_id,
b.effective_start_date, b.effective_end_date
INTO pv_hier_rule_obj_id,
pv_folder_id,
pv_hier_rule_obj_def_id,
pv_hier_rule_start_date,
pv_hier_rule_end_date
FROM fem_object_definition_b b,
fem_object_catalog_b o
WHERE b.object_definition_id = p_hier_rule_obj_def_id
AND o.object_id = b.object_id
AND o.object_type_code='OGL_INTG_HIER_RULE';
UPDATE fem_intg_hier_rules
SET hierarchy_obj_id = pv_hier_obj_id,
last_updated_by = pv_user_id,
last_update_date = sysdate,
last_update_login = pv_login_id
WHERE hier_rule_obj_id = pv_hier_rule_obj_id;
UPDATE fem_hierarchies
SET value_set_id = pv_dim_vs_id
WHERE hierarchy_obj_id = pv_hier_obj_id;
UPDATE fem_intg_hier_def_segs
SET hier_obj_def_id = pv_hier_obj_def_id,
last_updated_by = pv_user_id,
last_update_date = sysdate,
last_update_login = pv_login_id
WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
AND display_order_num = 1;
UPDATE fem_intg_hier_def_segs
SET hier_obj_def_id = pv_hier_obj_def_id,
last_updated_by = pv_user_id,
last_update_date = sysdate,
last_update_login = pv_login_id
WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
AND display_order_num = 1;
SELECT 'Duplicate'
INTO v_duplicate_parent
FROM dual
WHERE EXISTS
(SELECT gt.child_id
FROM fem_intg_dim_hier_gt gt
WHERE gt.parent_id <> gt.child_id
GROUP BY gt.child_id
HAVING count(gt.child_id) > 1);
'SELECT gt.child_display_code
FROM '||pv_dim_memb_b_tab||' b,
fem_intg_dim_hier_gt gt
WHERE b.'||pv_dim_memb_disp_col ||'= gt.child_display_code
AND b.value_set_id = '||pv_dim_vs_id||'
AND b.dimension_group_id <> gt.dimension_group_id';
'UPDATE ' ||pv_dim_memb_b_tab||'
SET dimension_group_id =
(SELECT dimension_group_id
FROM fem_intg_dim_hier_gt
WHERE child_display_code = '||pv_dim_memb_disp_col||'),
last_updated_by = :pv_user_id,
last_update_date = sysdate,
last_update_login = :pv_login_id
WHERE value_set_id = :pv_dim_vs_id
AND '||pv_dim_memb_col||' IN
(SELECT child_id
FROM fem_intg_dim_hier_gt)';
'INSERT INTO '||pv_dim_hier_tab||'
(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, weighting_pct,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, object_version_number)
SELECT
:pv_hier_obj_def_id,
gt.parent_depth_num,
gt.parent_id,
:pv_dim_vs_id,
gt.child_depth_num,
gt.child_id,
:pv_dim_vs_id,
''Y'',
gt.display_order_num, NULL,
SYSDATE,
:pv_user_id,
SYSDATE,
:pv_user_id,
:pv_login_id,
1
FROM fem_intg_dim_hier_gt gt
WHERE hierarchy_obj_def_id = :pv_hier_obj_def_id';
v_sql_stmt := 'DELETE FROM '||pv_dim_hier_tab||'
WHERE hierarchy_obj_def_id = :pv_hier_obj_def_id';
'INSERT INTO '||pv_dim_hier_tab||'
(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, weighting_pct,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, object_version_number)
SELECT
:pv_hier_obj_def_id,
gt.parent_depth_num,
gt.parent_id,
:pv_dim_vs_id,
gt.child_depth_num,
gt.child_id,
:pv_dim_vs_id,
''Y'',
gt.display_order_num, NULL,
SYSDATE,
:pv_user_id,
SYSDATE,
:pv_user_id,
:pv_login_id,
1
FROM fem_intg_dim_hier_gt gt
WHERE gt.hierarchy_obj_def_id = :pv_hier_obj_def_id';