[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select MEMBER_VL_OBJECT_NAME, MEMBER_NAME_COL,
MEMBER_COL, VALUE_SET_REQUIRED_FLAG
into l_dim_vl_table, l_dim_name_col, l_dim_col, l_vs_req
from FEM_XDIM_DIMENSIONS
where DIMENSION_ID = p_dimension_id;
l_command := 'select '||l_dim_name_col||' from '||l_dim_vl_table||
' where to_char('||l_dim_col||') = '''||p_member_id||'''';
select MEMBER_VL_OBJECT_NAME, MEMBER_DESCRIPTION_COL,
MEMBER_COL, VALUE_SET_REQUIRED_FLAG
into l_dim_vl_table, l_dim_desc_col, l_dim_col, l_vs_req
from FEM_XDIM_DIMENSIONS
where DIMENSION_ID = p_dimension_id;
l_command := 'select '||l_dim_desc_col||' from '||l_dim_vl_table||
' where to_char('||l_dim_col||') = '''||p_member_id||'''';
select MEMBER_VL_OBJECT_NAME, MEMBER_NAME_COL, MEMBER_DESCRIPTION_COL,
MEMBER_COL, VALUE_SET_REQUIRED_FLAG
into l_dim_vl_table, l_dim_name_col, l_dim_desc_col, l_dim_col, l_vs_req
from FEM_XDIM_DIMENSIONS
where DIMENSION_ID = p_dimension_id;
l_command := 'select '||l_dim_col||', '||l_dim_name_col||', '||
l_dim_desc_col;
select MEMBER_VL_OBJECT_NAME, MEMBER_NAME_COL, MEMBER_DESCRIPTION_COL,
MEMBER_COL, VALUE_SET_REQUIRED_FLAG
into l_dim_vl_table, l_dim_name_col, l_dim_desc_col, l_dim_col, l_vs_req
from FEM_XDIM_DIMENSIONS
where DIMENSION_ID = p_dimension_id;
l_command := 'select to_char('||l_dim_col||'), '||l_dim_name_col||', '||
l_dim_desc_col;
select A.DIMENSION_ID, A.VALUE_SET_REQUIRED_FLAG, A.HIERARCHY_TABLE_NAME
into l_dimension_id, l_vs_req, l_hier_table
from FEM_XDIM_DIMENSIONS A, FEM_HIERARCHIES B, FEM_OBJECT_DEFINITION_B C
where A.DIMENSION_ID = B.DIMENSION_ID
and B.HIERARCHY_OBJ_ID = C.OBJECT_ID
and C.OBJECT_DEFINITION_ID = p_hier_vers_id;
l_command := 'select PARENT_ID, ZPB_FEM_UTILS_PKG.GET_MEMBER_NAME('||
l_dimension_id||', PARENT_ID, ';
select A.HIERARCHY_ID, C.OBJECT_DEFINITION_ID, A.KEEP_VERSION,
A.NUMBER_OF_VERSIONS, A.VERSION_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_HIERARCHIES A, ZPB_BUSAREA_VERSIONS B,
FEM_OBJECT_DEFINITION_B C
where A.VERSION_ID = B.VERSION_ID
and B.VERSION_TYPE = p_version_type
and B.BUSINESS_AREA_ID = l_business_area
and A.HIERARCHY_ID = C.OBJECT_ID
and C.EFFECTIVE_START_DATE < sysdate
and C.EFFECTIVE_END_DATE > sysdate;
select HIER_VERSION_ID
from ZPB_BUSAREA_HIER_VERSIONS
where VERSION_ID = l_vers
and LOGICAL_DIM_ID = l_logical_dim_id
and HIERARCHY_ID = l_hier;
select OBJECT_DEFINITION_ID
from FEM_OBJECT_DEFINITION_B
where OBJECT_ID = l_hier
and EFFECTIVE_START_DATE < sysdate
order by EFFECTIVE_END_DATE DESC;
select HIERARCHY_ID, VERSION_ID, PARENT_ID, CHILD_ID, PARENT_DEPTH,
CHILD_DEPTH, PARENT_GROUP, CHILD_GROUP, DISPLAY_ORDER,
LOGICAL_DIM_ID
from ZPB_HIER_MEMBERS
where BUSINESS_AREA_ID = p_business_area_id
and LOGICAL_DIM_ID = p_logical_dim_id
and PARENT_INCLUDE_TYPE in ('Y', 'A', 'D')
and CHILD_INCLUDE_TYPE in ('Y', 'A', 'D');
select B.ATTRIBUTE_ID, D.VERSION_ID, B.VALUE, B.VALUE_SET_ID,
B.ATTRIBUTE_VALUE_COLUMN_NAME COL_NAME, B.OPERATION,
B.LOGICAL_DIM_ID, B.DIMENSION_ID
from
ZPB_BUSAREA_CONDITIONS_V B,
ZPB_BUSAREA_VERSIONS C,
FEM_DIM_ATTR_VERSIONS_B D
where
B.ATTRIBUTE_ID = D.ATTRIBUTE_ID
and D.DEFAULT_VERSION_FLAG = 'Y'
and B.VERSION_ID = C.VERSION_ID
and B.LOGICAL_DIM_ID = p_logical_dim_id
and C.VERSION_TYPE = p_version_type
and C.BUSINESS_AREA_ID = l_business_area
and B.DIMENSION_ID = p_dimension_id;
select A.VALUE_SET_REQUIRED_FLAG,
A.MEMBER_VL_OBJECT_NAME, A.MEMBER_COL, A.ATTRIBUTE_TABLE_NAME,
'N', A.MEMBER_DESCRIPTION_COL, A.MEMBER_NAME_COL,
A.HIER_EDITOR_MANAGED_FLAG
into l_vs_req, l_dim_vl_table, l_dim_col, l_attr_table, l_use_cond,
l_dim_desc_col, l_dim_name_col, l_pers_flag
from FEM_XDIM_DIMENSIONS A
where A.DIMENSION_ID = p_dimension_id;
select A.VALUE_SET_REQUIRED_FLAG,
A.MEMBER_VL_OBJECT_NAME, A.MEMBER_COL, A.ATTRIBUTE_TABLE_NAME,
B.USE_MEMBER_CONDITIONS, A.MEMBER_DESCRIPTION_COL, A.MEMBER_NAME_COL,
A.HIER_EDITOR_MANAGED_FLAG
into l_vs_req, l_dim_vl_table, l_dim_col, l_attr_table, l_use_cond,
l_dim_desc_col, l_dim_name_col, l_pers_flag
from FEM_XDIM_DIMENSIONS A,
ZPB_BUSAREA_DIMENSIONS B,
ZPB_BUSAREA_VERSIONS C
where A.DIMENSION_ID = p_dimension_id
and A.DIMENSION_ID = B.DIMENSION_ID
and B.VERSION_ID = C.VERSION_ID
and B.LOGICAL_DIM_ID = p_logical_dim_id
and C.VERSION_TYPE = p_version_type
and C.BUSINESS_AREA_ID = l_business_area;
select distinct (A.VALUE_SET_ID)
into l_vset_id
from FEM_GLOBAL_VS_COMBO_DEFS A, ZPB_BUSAREA_LEDGERS B,
FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
FEM_DIM_ATTR_VERSIONS_B E, ZPB_BUSAREA_VERSIONS F
where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
and E.DEFAULT_VERSION_FLAG = 'Y'
and E.AW_SNAPSHOT_FLAG = 'N'
and C.VERSION_ID = E.VERSION_ID
and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
and B.LEDGER_ID = C.LEDGER_ID
and B.VERSION_ID = F.VERSION_ID
and A.GLOBAL_VS_COMBO_ID = C.DIM_ATTRIBUTE_NUMERIC_MEMBER
and A.DIMENSION_ID = p_dimension_id
and F.BUSINESS_AREA_ID = l_business_area
and F.VERSION_TYPE = p_version_type;
l_sel_command := 'select to_char(A.'||l_dim_col||'), A.'||l_dim_name_col||
', A.'||l_dim_desc_col||' from '||l_dim_vl_table||' A';
l_incl_select VARCHAR2(4000);
l_pincl_select VARCHAR2(4000);
l_cincl_select VARCHAR2(4000);
select A.HIERARCHY_ID, A.VERSION_ID, A.CURRENT_VERSION,
C.INCLUDE_ALL_TOP_MEMBERS, C.INCLUDE_ALL_LEVELS,
A.LOGICAL_DIM_ID
from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES
(l_business_area, p_version_type)) A,
ZPB_BUSAREA_HIERARCHIES C,
ZPB_BUSAREA_VERSIONS D
where
A.LOGICAL_DIM_ID = p_logical_dim_id
and A.HIERARCHY_ID = C.HIERARCHY_ID
and C.VERSION_ID = D.VERSION_ID
and D.BUSINESS_AREA_ID = l_business_area
and D.VERSION_TYPE = p_version_type
order by INCLUDE_ALL_TOP_MEMBERS ASC;
select B.ATTRIBUTE_ID, D.VERSION_ID, B.VALUE, B.VALUE_SET_ID,
B.ATTRIBUTE_VALUE_COLUMN_NAME COL_NAME, B.OPERATION
from
ZPB_BUSAREA_CONDITIONS_V B,
ZPB_BUSAREA_VERSIONS C,
FEM_DIM_ATTR_VERSIONS_B D
where
B.ATTRIBUTE_ID = D.ATTRIBUTE_ID
and D.DEFAULT_VERSION_FLAG = 'Y'
and B.VERSION_ID = C.VERSION_ID
and B.LOGICAL_DIM_ID = p_logical_dim_id
and C.VERSION_TYPE = p_version_type
and C.BUSINESS_AREA_ID = l_business_area
and B.DIMENSION_ID = p_dimension_id;
select decode(p_vset, 'Y', A.VALUE_SET_ID||'_'||A.MEMBER_ID,
A.MEMBER_ID) MEMBER_ID
from ZPB_BUSAREA_HIER_MEMBERS A,
ZPB_BUSAREA_VERSIONS B
where A.HIERARCHY_ID = p_hierarchy
and nvl(A.HIER_VERSION_ID, -1) = nvl(p_hier_vers, -1)
and A.VERSION_ID = B.VERSION_ID
and A.LOGICAL_DIM_ID = p_logical_dim_id
and B.VERSION_TYPE = p_version_type
and B.BUSINESS_AREA_ID = l_business_area;
select distinct PARENT_DEPTH
from ZPB_HIER_MEMBERS
where HIERARCHY_ID = p_hierarchy
and nvl(VERSION_ID,-1) = nvl(p_hier_vers,-1)
and BUSINESS_AREA_ID = l_business_area
and DIMENSION_ID = p_dimension_id
and LOGICAL_DIM_ID = p_logical_dim_id
order by PARENT_DEPTH DESC;
select A.HIERARCHY_TABLE_NAME, A.VALUE_SET_REQUIRED_FLAG,
A.MEMBER_B_TABLE_NAME, A.MEMBER_COL, A.ATTRIBUTE_TABLE_NAME,
B.USE_MEMBER_CONDITIONS, B.CONDITIONS_INCL_ANC, B.CONDITIONS_INCL_DESC,
A.HIER_EDITOR_MANAGED_FLAG
into l_hier_table, l_vs_req, l_dim_b_table, l_dim_col, l_attr_table,
l_use_cond, l_cond_anc, l_cond_desc, l_pers_flag
from FEM_XDIM_DIMENSIONS A,
ZPB_BUSAREA_DIMENSIONS B,
ZPB_BUSAREA_VERSIONS C
where A.DIMENSION_ID = p_dimension_id
and A.DIMENSION_ID = B.DIMENSION_ID
and B.LOGICAL_DIM_ID = p_logical_dim_id
and B.VERSION_ID = C.VERSION_ID
and C.VERSION_TYPE = p_version_type
and C.BUSINESS_AREA_ID = l_business_area;
select distinct (A.VALUE_SET_ID)
into l_vset_id
from FEM_GLOBAL_VS_COMBO_DEFS A, ZPB_BUSAREA_LEDGERS B,
FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
FEM_DIM_ATTR_VERSIONS_B E, ZPB_BUSAREA_VERSIONS F
where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
and E.DEFAULT_VERSION_FLAG = 'Y'
and E.AW_SNAPSHOT_FLAG = 'N'
and C.VERSION_ID = E.VERSION_ID
and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
and B.LEDGER_ID = C.LEDGER_ID
and B.VERSION_ID = F.VERSION_ID
and A.GLOBAL_VS_COMBO_ID = C.DIM_ATTRIBUTE_NUMERIC_MEMBER
and A.DIMENSION_ID = p_dimension_id
and F.BUSINESS_AREA_ID = l_business_area
and F.VERSION_TYPE = p_version_type;
l_pincl_select := l_pincl_select||'CASE WHEN P'||l_count||'.'||
each_cond.COL_NAME||' '||l_operation;
l_cincl_select := l_cincl_select||'CASE WHEN C'||l_count||'.'||
each_cond.COL_NAME||' '||l_operation;
l_pincl_select := l_pincl_select||each_cond.VALUE||' THEN ';
l_cincl_select := l_cincl_select||each_cond.VALUE||' THEN ';
l_pincl_select :=l_pincl_select||''''||each_cond.VALUE||''' THEN ';
l_cincl_select :=l_cincl_select||''''||each_cond.VALUE||''' THEN ';
l_pincl_select := l_pincl_select||'to_date('''||
each_cond.VALUE||''', ''YYYY/MM/DD'') THEN ';
l_cincl_select := l_cincl_select||'to_date('''||
each_cond.VALUE||''', ''YYYY/MM/DD'') THEN ';
l_incl_select :=l_pincl_select||l_incl_sel_cls||
' PARENT_IS_INCLUDED, '||l_cincl_select||l_incl_sel_cls||
' CHILD_IS_INCLUDED, ';
l_incl_select := '''Y'' PARENT_IS_INCLUDED, ''Y'' CHILD_IS_INCLUDED, ';
select A.INCLUDE_ALL_TOP_MEMBERS
into l_top_mbrs
from ZPB_BUSAREA_HIER_VERSIONS A,
ZPB_BUSAREA_VERSIONS B
where A.VERSION_ID = B.VERSION_ID
and B.BUSINESS_AREA_ID = l_business_area
and B.VERSION_TYPE = p_version_type
and A.HIERARCHY_ID = each.HIERARCHY_ID
and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and A.HIER_VERSION_ID = each.VERSION_ID;
'select A.PARENT_DEPTH_NUM,
A.CHILD_DEPTH_NUM,
A.DISPLAY_ORDER_NUM, '||l_incl_select;
(select LEVEL_ID from ZPB_BUSAREA_LEVELS A,
ZPB_BUSAREA_VERSIONS B
where B.VERSION_TYPE = '''||p_version_type||'''
and B.BUSINESS_AREA_ID = '||l_business_area||'
and A.VERSION_ID = B.VERSION_ID
and A.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
and A.HIERARCHY_ID = '||each.HIERARCHY_ID||')
AND C.DIMENSION_GROUP_ID in
(select LEVEL_ID from ZPB_BUSAREA_LEVELS A,
ZPB_BUSAREA_VERSIONS B
where B.VERSION_TYPE = '''||p_version_type||'''
and B.BUSINESS_AREA_ID = '||l_business_area||'
and A.VERSION_ID = B.VERSION_ID
and A.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
and A.HIERARCHY_ID = '||each.HIERARCHY_ID||')';
INSERT INTO ZPB_HIER_MEMBERS
(BUSINESS_AREA_ID,
DIMENSION_ID,
LOGICAL_DIM_ID,
HIERARCHY_ID,
VERSION_ID,
PARENT_ID,
CHILD_ID,
PARENT_DEPTH,
CHILD_DEPTH,
PARENT_GROUP,
CHILD_GROUP,
DISPLAY_ORDER,
PARENT_INCLUDE_TYPE,
CHILD_INCLUDE_TYPE)
values
(l_business_area,
p_dimension_id,
l_ret.LOGICAL_DIM_ID,
l_ret.HIERARCHY_ID,
l_ret.VERSION_ID,
l_ret.PARENT_ID,
l_ret.CHILD_ID,
l_ret.PARENT_DEPTH,
l_ret.CHILD_DEPTH,
l_ret.PARENT_GROUP,
l_ret.CHILD_GROUP,
l_ret.DISPLAY_ORDER,
l_p_is_included,
l_c_is_included);
select min(A.PARENT_DEPTH)
into l_count
from ZPB_HIER_MEMBERS A
where A.BUSINESS_AREA_ID = l_business_area
and A.DIMENSION_ID = p_dimension_id
and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and A.HIERARCHY_ID = each.HIERARCHY_ID
and nvl(VERSION_ID,-1) = nvl(l_count2,-1);
update ZPB_HIER_MEMBERS A
set A.PARENT_DEPTH = A.PARENT_DEPTH+1-l_count,
A.CHILD_DEPTH = A.CHILD_DEPTH+1-l_count
where A.BUSINESS_AREA_ID = l_business_area
and A.DIMENSION_ID = p_dimension_id
and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and A.HIERARCHY_ID = each.HIERARCHY_ID
and nvl(VERSION_ID,-1) = nvl(l_count2,-1);
insert into ZPB_HIER_MEMBERS
(BUSINESS_AREA_ID,
DIMENSION_ID,
LOGICAL_DIM_ID,
HIERARCHY_ID,
VERSION_ID,
PARENT_ID,
CHILD_ID,
PARENT_DEPTH,
CHILD_DEPTH,
PARENT_GROUP,
CHILD_GROUP,
DISPLAY_ORDER,
PARENT_INCLUDE_TYPE,
CHILD_INCLUDE_TYPE)
select distinct
l_business_area,
p_dimension_id,
each.LOGICAL_DIM_ID,
each.HIERARCHY_ID,
l_count2,
PARENT_ID,
PARENT_ID,
1,
1,
PARENT_GROUP,
PARENT_GROUP,
1,
PARENT_INCLUDE_TYPE,
PARENT_INCLUDE_TYPE
from ZPB_HIER_MEMBERS A
where A.BUSINESS_AREA_ID = l_business_area
and A.DIMENSION_ID = p_dimension_id
and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and A.HIERARCHY_ID = each.HIERARCHY_ID
and nvl(A.VERSION_ID,-1) = nvl(l_count2,-1)
and A.PARENT_DEPTH = 1
and A.CHILD_DEPTH <> 1
and A.PARENT_ID not in
(select distinct B.PARENT_ID
from ZPB_HIER_MEMBERS B
where B.BUSINESS_AREA_ID = l_business_area
and B.DIMENSION_ID = p_dimension_id
and B.HIERARCHY_ID = each.HIERARCHY_ID
and B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and nvl(B.VERSION_ID,-1) = nvl(l_count2,-1)
and B.CHILD_DEPTH = 1
and B.PARENT_DEPTH = 1);
update ZPB_HIER_MEMBERS
set PARENT_INCLUDE_TYPE = 'A'
where PARENT_DEPTH = anc.PARENT_DEPTH
and PARENT_INCLUDE_TYPE in ('N', 'T')
and CHILD_INCLUDE_TYPE in ('Y', 'A')
and BUSINESS_AREA_ID = l_business_area
and DIMENSION_ID = p_dimension_id
and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and HIERARCHY_ID = each.HIERARCHY_ID
and nvl(VERSION_ID,-1) = nvl(l_count2,-1);
update ZPB_HIER_MEMBERS A
set A.PARENT_INCLUDE_TYPE = 'A',
A.CHILD_INCLUDE_TYPE = 'A'
where A.BUSINESS_AREA_ID = l_business_area
and A.DIMENSION_ID = p_dimension_id
and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and A.HIERARCHY_ID = each.HIERARCHY_ID
and nvl(A.VERSION_ID,-1) = nvl(l_count2,-1)
and A.PARENT_DEPTH = 1
and A.CHILD_DEPTH = 1
and A.PARENT_ID = A.CHILD_ID
and A.PARENT_INCLUDE_TYPE in ('N', 'T')
and A.PARENT_ID in
(select B.PARENT_ID
from ZPB_HIER_MEMBERS B
where B.BUSINESS_AREA_ID = l_business_area
and B.DIMENSION_ID = p_dimension_id
and B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and B.HIERARCHY_ID = each.HIERARCHY_ID
and nvl(B.VERSION_ID,-1) = nvl(l_count2,-1)
and B.PARENT_DEPTH = 1
and B.PARENT_INCLUDE_TYPE in ('Y', 'A'));
select A.DIMENSION_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_DIMENSIONS A,
ZPB_BUSAREA_VERSIONS B
where A.VERSION_ID = B.VERSION_ID
and B.BUSINESS_AREA_ID = p_business_area
and B.VERSION_TYPE = p_version_type;
delete from ZPB_HIER_MEMBERS
where BUSINESS_AREA_ID = p_business_area;