[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_attr_vl_query_select in long
,p_member_col in varchar2
,p_member_display_code_col in varchar2
,p_member_name_col in varchar2
,p_value_set_select in varchar2
,p_data_tablespace in varchar2
,p_index_tablespace in varchar2
,p_storage in varchar2
,p_build_mode in varchar2
,p_refresh_mode in varchar2
,p_enable_qrewrite in varchar2
);
,x_attrd_attr_select out nocopy long
,x_attrn_attr_select out nocopy long
,x_attrn_vl_attr_select out nocopy long
);
,x_attrd_attr_select out nocopy long
,x_attrn_attr_select out nocopy long
,x_attrn_vl_attr_select out nocopy long
);
select *
from fem_bi_attr_dimensions_v
where dimension_varchar_label <> 'ALL'
order by dimension_varchar_label;
select *
from fnd_concurrent_requests
where parent_request_id = p_parent_request_id;
select 1 into l_dummy_number
from fem_bi_attr_dimensions_v
where dimension_varchar_label = p_dimension_varchar_label;
select cp2.cal_period_id
from fem_cal_periods_b cp
,fem_cal_periods_attr cpa_adj
,fem_cal_periods_b cp2
,fem_cal_periods_attr cpa2_adj
,fem_dimensions_b dim
,fem_dim_attributes_b a
,fem_dim_attr_versions_b v
where cp.cal_period_id = p_cal_period_id
and dim.dimension_varchar_label = 'CAL_PERIOD'
and a.dimension_id = dim.dimension_id
and a.attribute_varchar_label = 'ADJ_PERIOD_FLAG'
and v.attribute_id = a.attribute_id
and v.default_version_flag = 'Y'
and cpa_adj.attribute_id = a.attribute_id
and cpa_adj.version_id = v.version_id
and cpa_adj.cal_period_id = cp.cal_period_id
and cpa2_adj.attribute_id = a.attribute_id
and cpa2_adj.version_id = v.version_id
and cpa2_adj.cal_period_id = cp2.cal_period_id
and cp2.calendar_id = cp.calendar_id
and cp2.dimension_group_id = cp.dimension_group_id
and cpa2_adj.dim_attribute_varchar_member = cpa_adj.dim_attribute_varchar_member
and cp2.cal_period_id < cp.cal_period_id
order by cp2.cal_period_id desc;
select cp2.cal_period_id
from fem_cal_periods_b cp
,fem_cal_periods_attr cpa_adj
,fem_cal_periods_attr cpa_start
,fem_cal_periods_attr cpa_end
,fem_cal_periods_b cp2
,fem_cal_periods_attr cpa2_adj
,fem_cal_periods_attr cpa2_start
,fem_cal_periods_attr cpa2_end
,fem_dimensions_b dim
,fem_dim_attributes_b a_adj
,fem_dim_attr_versions_b v_adj
,fem_dim_attributes_b a_start
,fem_dim_attr_versions_b v_start
,fem_dim_attributes_b a_end
,fem_dim_attr_versions_b v_end
where cp.cal_period_id = p_cal_period_id
and dim.dimension_varchar_label = 'CAL_PERIOD'
and a_adj.dimension_id = dim.dimension_id
and a_adj.attribute_varchar_label = 'ADJ_PERIOD_FLAG'
and v_adj.attribute_id = a_adj.attribute_id
and v_adj.default_version_flag = 'Y'
and a_start.dimension_id = dim.dimension_id
and a_start.attribute_varchar_label = 'CAL_PERIOD_START_DATE'
and v_start.attribute_id = a_start.attribute_id
and v_start.default_version_flag = 'Y'
and a_end.dimension_id = dim.dimension_id
and a_end.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
and v_end.attribute_id = a_end.attribute_id
and v_end.default_version_flag = 'Y'
and cpa_adj.attribute_id = a_adj.attribute_id
and cpa_adj.version_id = v_adj.version_id
and cpa_adj.cal_period_id = cp.cal_period_id
and cpa_adj.dim_attribute_varchar_member = 'N'
and cpa_start.attribute_id = a_start.attribute_id
and cpa_start.version_id = v_start.version_id
and cpa_start.cal_period_id = cp.cal_period_id
and cpa_end.attribute_id = a_end.attribute_id
and cpa_end.version_id = v_end.version_id
and cpa_end.cal_period_id = cp.cal_period_id
and cpa2_adj.attribute_id = a_adj.attribute_id
and cpa2_adj.version_id = v_adj.version_id
and cpa2_adj.cal_period_id = cp2.cal_period_id
and cpa2_adj.dim_attribute_varchar_member = 'N'
and cpa2_start.attribute_id = a_start.attribute_id
and cpa2_start.version_id = v_start.version_id
and cpa2_start.cal_period_id = cp2.cal_period_id
and cpa2_end.attribute_id = a_end.attribute_id
and cpa2_end.version_id = v_end.version_id
and cpa2_end.cal_period_id = cp2.cal_period_id
and cp2.calendar_id = cp.calendar_id
and cp2.dimension_group_id = cp.dimension_group_id
and add_months(cpa_end.date_assign_value,-12) between cpa2_start.date_assign_value and cpa2_end.date_assign_value
and add_months(cpa_start.date_assign_value,-12) between cpa2_start.date_assign_value and cpa2_end.date_assign_value;
select *
from fem_bi_attr_dimensions_v
where dimension_varchar_label <> 'ALL'
order by dimension_varchar_label;
select 1 into l_dummy_number
from fem_bi_attr_dimensions_v
where dimension_varchar_label = p_dimension_varchar_label;
l_value_set_select varchar2(200);
l_attrd_query_select long;
l_attrd_attr_select long;
l_attrn_query_select long;
l_attrn_attr_select long;
l_attrn_vl_query_select long;
l_attrn_vl_attr_select long;
select dimension_id
,dimension_name
,member_b_table_name
,member_tl_table_name
,attribute_table_name
,member_col
,decode(member_display_code_col
,member_col,null
,member_display_code_col)
,decode(member_name_col
,member_display_code_col,null
,member_col,null
,member_name_col)
,decode(member_description_col
,member_name_col,null
,member_display_code_col,null
,member_col,null
,member_description_col)
,group_use_code
,value_set_required_flag
,logical_delete_applicable_flag
,read_only_applicable_flag
into l_dimension_id
,l_dimension_name
,l_member_b_table_name
,l_member_tl_table_name
,l_attribute_table_name
,l_member_col
,l_member_display_code_col
,l_member_name_col
,l_member_description_col
,l_group_use_code
,l_value_set_required_flag
,l_enabled_applicable_flag
,l_read_only_applicable_flag
from fem_xdim_dimensions_vl
where dimension_varchar_label = p_dimension_varchar_label;
l_attrd_query_select :=
' select b.'||l_member_col;
l_attrn_query_select :=
' select b.'||l_member_col;
l_attrn_vl_query_select :=
' select '||l_member_col;
l_value_set_select :=
' ,value_set_id';
l_attrd_query_select := l_attrd_query_select||
' ,b.value_set_id';
l_attrn_query_select := l_attrn_query_select||
' ,b.value_set_id';
l_attrn_vl_query_select := l_attrn_vl_query_select||
' ,value_set_id';
l_attrn_query_select := l_attrn_query_select||
' ,tl.language'||
' ,tl.source_lang';
l_attrd_query_select := l_attrd_query_select||
' ,b.'||l_member_display_code_col;
l_attrn_query_select := l_attrn_query_select||
' ,tl.'||l_member_name_col;
l_attrn_vl_query_select := l_attrn_vl_query_select||
' ,'||l_member_name_col;
l_attrn_query_select := l_attrn_query_select||
' ,tl.'||l_member_description_col;
l_attrn_vl_query_select := l_attrn_vl_query_select||
' ,'||l_member_description_col;
l_attrd_query_select := l_attrd_query_select||
' ,b.dimension_group_id'||
' ,dgb.dimension_group_display_code';
l_attrn_query_select := l_attrn_query_select||
' ,b.dimension_group_id'||
' ,(select dgtl.dimension_group_name'||
' from fem_dimension_grps_tl dgtl'||
' where dgtl.dimension_group_id = b.dimension_group_id'||
' and dgtl.language = tl.language'||
' ) as dimension_group_name'||
' ,(select dgtl.description'||
' from fem_dimension_grps_tl dgtl'||
' where dgtl.dimension_group_id = b.dimension_group_id'||
' and dgtl.language = tl.language'||
' ) as dimension_group_desc';
l_attrn_vl_query_select := l_attrn_vl_query_select||
' ,dimension_group_id'||
' ,dimension_group_name'||
' ,dimension_group_desc';
l_attrd_query_select := l_attrd_query_select||
' ,b.calendar_id'||
' ,cal.calendar_display_code'||
' ,FEM_BI_DIMENSION_UTILS_PKG.Get_Pago_Cal_Period_ID(b.'||l_member_col||') pago_cal_period_id'||
' ,FEM_BI_DIMENSION_UTILS_PKG.Get_Yago_Cal_Period_ID(b.'||l_member_col||') yago_cal_period_id';
l_attrn_query_select := l_attrn_query_select||
' ,b.calendar_id'||
' ,cal.calendar_name'||
' ,cal.description as calendar_desc';
l_attrn_vl_query_select := l_attrn_vl_query_select||
' ,calendar_id'||
' ,calendar_name'||
' ,calendar_desc';
l_attrd_query_select := l_attrd_query_select||
' ,b.enabled_flag';
l_attrd_query_select := l_attrd_query_select||
' ,b.read_only_flag';
l_attrd_query_select := l_attrd_query_select||
' ,b.personal_flag';
,x_attrd_attr_select => l_attrd_attr_select
,x_attrn_attr_select => l_attrn_attr_select
,x_attrn_vl_attr_select => l_attrn_vl_attr_select
);
,x_attrd_attr_select => l_attrd_attr_select
,x_attrn_attr_select => l_attrn_attr_select
,x_attrn_vl_attr_select => l_attrn_vl_attr_select
);
if (l_attrd_attr_select is null) then
FEM_ENGINES_PKG.User_Message (
p_app_name => G_FEM
,p_msg_name => G_BI_ATTR_NO_ATTRIBUTES_WRN
,p_token1 => 'DIMENSION'
,p_value1 => l_function_name
);
l_attrd_query_select :=
l_attrd_query_select||
l_attrd_attr_select||
' ,b.created_by'||
' ,b.creation_date'||
' ,b.last_updated_by'||
' ,b.last_update_date'||
' ,b.last_update_login';
l_attrn_query_select :=
l_attrn_query_select||
l_attrn_attr_select||
' ,tl.created_by'||
' ,tl.creation_date'||
' ,tl.last_updated_by'||
' ,tl.last_update_date'||
' ,tl.last_update_login';
l_attrn_vl_query_select :=
l_attrn_vl_query_select||
l_attrn_vl_attr_select||
' ,created_by'||
' ,creation_date'||
' ,last_updated_by'||
' ,last_update_date'||
' ,last_update_login';
l_attrd_query_select||
l_attrd_query_from||
l_attrd_query_where;
l_attrn_query_select||
l_attrn_query_from||
l_attrn_query_where;
,p_attr_vl_query_select => null
,p_member_col => l_member_col
,p_member_display_code_col => l_member_display_code_col
,p_member_name_col => null
,p_value_set_select => l_value_set_select
,p_data_tablespace => l_data_tablespace
,p_index_tablespace => l_index_tablespace
,p_storage => l_storage
,p_build_mode => p_build_mode
,p_refresh_mode => p_refresh_mode
,p_enable_qrewrite => l_enable_qrewrite
);
,p_attr_vl_query_select => l_attrn_vl_query_select
,p_member_col => l_member_col
,p_member_display_code_col => null
,p_member_name_col => l_member_name_col
,p_value_set_select => l_value_set_select
,p_data_tablespace => l_data_tablespace
,p_index_tablespace => l_index_tablespace
,p_storage => l_storage
,p_build_mode => p_build_mode
,p_refresh_mode => p_refresh_mode
,p_enable_qrewrite => l_enable_qrewrite
);
| p_attr_vl_query_select Attribute VL View Select Statement
| p_member_col Dimension Member ID Column Name
| p_member_display_code_col Dimension Member Display Code Column Name
| p_member_name_col Dimension Member Name Column Name
| p_value_set_select Value Set Select Statement
| p_data_tablespace MV Data Tablespace Name
| p_index_tablespace MV Index Tablespace Name
| p_storage MV Storage Clause
| p_build_mode MV Build Mode
| p_refresh_mode MV Refresh Mode
| p_enable_qrewrite MV Enable Query Rewrite Clause
+===========================================================================*/
PROCEDURE Create_MV_Objects (
p_attr_mv_name_prefix in varchar2
,p_attr_query in long
,p_attr_vl_query_select in long
,p_member_col in varchar2
,p_member_display_code_col in varchar2
,p_member_name_col in varchar2
,p_value_set_select in varchar2
,p_data_tablespace in varchar2
,p_index_tablespace in varchar2
,p_storage in varchar2
,p_build_mode in varchar2
,p_refresh_mode in varchar2
,p_enable_qrewrite in varchar2
)
IS
-----------------------
-- Declare constants --
-----------------------
l_api_name constant varchar2(100) := G_PACKAGE_NAME||'.Create_MV_Objects';
' SELECT *'||
' FROM '||l_attr_v_name;
if (p_attr_vl_query_select is not null) then
l_dynamic_sql :=
' CREATE OR REPLACE VIEW '||l_attr_vl_name||
' AS'||
p_attr_vl_query_select||
' FROM '||l_attr_mv_name||
' WHERE LANGUAGE = USERENV(''LANG'')';
p_value_set_select;
p_value_set_select;
p_value_set_select||
' ,language';
p_value_set_select||
' ,language';
| Gets dynamic SQL statement for selecting dimension attribute values.
|
| SCOPE - PRIVATE
|
| ARGUMENTS
| p_dimension_id Dimension ID
| p_attribute_table_name Attribute Table Name
| p_member_col Member Column Name
| p_value_set_required_flag Value Set Required Flag
+===========================================================================*/
PROCEDURE Get_Dim_Attribute_Sql (
p_dimension_id in number
,p_attribute_table_name in varchar2
,p_member_col in varchar2
,p_value_set_required_flag in varchar2
,x_attrd_attr_select out nocopy long
,x_attrn_attr_select out nocopy long
,x_attrn_vl_attr_select out nocopy long
)
IS
-----------------------
-- Declare constants --
-----------------------
l_api_name constant varchar2(100) := G_PACKAGE_NAME||'.Get_Dim_Attribute_Sql';
x_attrd_attr_select := null;
x_attrn_attr_select := null;
x_attrn_vl_attr_select := null;
select a.attribute_id
,v.version_id
,a.attribute_varchar_label
,a.attribute_data_type_code
,a.attribute_value_column_name
,a.attribute_dimension_id
,dim.dimension_varchar_label as attr_dimension_varchar_label
from fem_dim_attributes_b a
,fem_dim_attr_versions_b v
,fem_dimensions_b dim
where a.dimension_id = p_dimension_id
and a.personal_flag = 'N'
and a.queryable_for_reporting_flag = 'Y'
and a.allow_multiple_assignment_flag = 'N'
and v.attribute_id = a.attribute_id
and v.default_version_flag = 'Y'
and dim.dimension_id (+) = a.attribute_dimension_id
order by a.attribute_required_flag desc
,a.attribute_varchar_label
) loop
-- If Attribute Definition points to a Dimension, then must denormalize
-- the attribute value by querying the appropriate dimension member
-- TL table to get the member Name.
if (attr_rec.attribute_data_type_code = 'DIMENSION') then
-----------------------------------------
-- Get the Dimension metadata
-----------------------------------------
select member_b_table_name
,member_tl_table_name
,member_col
,member_display_code_col
,member_name_col
,decode(value_set_required_flag
,'Y',' and adb.value_set_id = attr.dim_attribute_value_set_id'
,null)
,decode(value_set_required_flag
,'Y',' and adtl.value_set_id = attr.dim_attribute_value_set_id'
,null)
,decode(member_tl_table_name
,member_vl_object_name,null
,' and adtl.language = tl.language')
into l_attr_mem_b_tab
,l_attr_mem_tl_tab
,l_attr_mem_col
,l_attr_mem_display_code_col
,l_attr_mem_name_col
,l_attrd_value_set_where
,l_attrn_value_set_where
,l_attrn_language_where
from fem_xdim_dimensions
where dimension_id = attr_rec.attribute_dimension_id;
' select adb.'||l_attr_mem_display_code_col||
' from '||l_attr_mem_b_tab||' adb'||
' ,'||p_attribute_table_name||' attr'||
' where adb.'||l_attr_mem_col||' = attr.'||attr_rec.attribute_value_column_name||
l_attrd_value_set_where||
' and attr.attribute_id = '||attr_rec.attribute_id||
' and attr.version_id = '||attr_rec.version_id||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where;
' select adtl.'||l_attr_mem_name_col||
' from '||l_attr_mem_tl_tab||' adtl'||
' ,'||p_attribute_table_name||' attr'||
' where adtl.'||l_attr_mem_col||' = attr.'||attr_rec.attribute_value_column_name||
l_attrn_value_set_where||
l_attrn_language_where||
' and attr.attribute_id = '||attr_rec.attribute_id||
' and attr.version_id = '||attr_rec.version_id||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where;
select a.attribute_id
,v.version_id
into l_sign_attribute_id
,l_sign_attr_version_id
from fem_dim_attributes_b a
,fem_dim_attr_versions_b v
where a.dimension_id = attr_rec.attribute_dimension_id
and a.attribute_varchar_label = 'SIGN'
and v.attribute_id = a.attribute_id
and v.default_version_flag = 'Y';
' select ext_attr.number_assign_value'||
' from '||p_attribute_table_name||' attr'||
' ,fem_ext_acct_types_attr ext_attr'||
' where attr.attribute_id = '||attr_rec.attribute_id||
' and attr.version_id = '||attr_rec.version_id||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where||
' and ext_attr.attribute_id = '||l_sign_attribute_id||
' and ext_attr.version_id = '||l_sign_attr_version_id||
' and ext_attr.ext_account_type_code = attr.'||attr_rec.attribute_value_column_name;
select a.attribute_id
,v.version_id
into l_bsc_attribute_id
,l_bsc_attr_version_id
from fem_dim_attributes_b a
,fem_dim_attr_versions_b v
where a.dimension_id = attr_rec.attribute_dimension_id
and a.attribute_varchar_label = 'BASIC_ACCOUNT_TYPE_CODE'
and v.attribute_id = a.attribute_id
and v.default_version_flag = 'Y';
' select ext_attr.dim_attribute_varchar_member'||
' from '||p_attribute_table_name||' attr'||
' ,fem_ext_acct_types_attr ext_attr'||
' where attr.attribute_id = '||attr_rec.attribute_id||
' and attr.version_id = '||attr_rec.version_id||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where||
' and ext_attr.attribute_id = '||l_bsc_attribute_id||
' and ext_attr.version_id = '||l_bsc_attr_version_id||
' and ext_attr.ext_account_type_code = attr.'||attr_rec.attribute_value_column_name;
' select bsc_tl.basic_account_type_name'||
' from '||p_attribute_table_name||' attr'||
' ,fem_ext_acct_types_attr ext_attr'||
' ,fem_basic_acct_types_tl bsc_tl'||
' where attr.attribute_id = '||attr_rec.attribute_id||
' and attr.version_id = '||attr_rec.version_id||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where||
' and ext_attr.attribute_id = '||l_bsc_attribute_id||
' and ext_attr.version_id = '||l_bsc_attr_version_id||
' and ext_attr.ext_account_type_code = attr.'||attr_rec.attribute_value_column_name||
' and bsc_tl.basic_account_type_code = ext_attr.dim_attribute_varchar_member'||
' and bsc_tl.language = tl.language';
' select attr.'||attr_rec.attribute_value_column_name||
' from '||p_attribute_table_name||' attr'||
' where attr.attribute_id = '||attr_rec.attribute_id||
' and attr.version_id = '||attr_rec.version_id||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where;
x_attrd_attr_select := x_attrd_attr_select||
' ,('||l_attrd_sql_stmt||') '||l_attr_col;
x_attrn_attr_select := x_attrn_attr_select||
' ,('||l_attrn_sql_stmt||') '||l_attr_col;
x_attrn_vl_attr_select := x_attrn_vl_attr_select||
' ,'||l_attr_col;
x_attrd_attr_select := x_attrd_attr_select||
' ,('||l_sign_attrd_sql_stmt||') EXTENDED_ACCOUNT_SIGN'||
' ,('||l_bsc_attrd_sql_stmt||') BASIC_ACCOUNT_TYPE';
x_attrn_attr_select := x_attrn_attr_select||
' ,('||l_bsc_attrn_sql_stmt||') BASIC_ACCOUNT_TYPE';
x_attrn_vl_attr_select := x_attrn_vl_attr_select||
' ,BASIC_ACCOUNT_TYPE';
x_attrd_attr_select := null;
x_attrn_attr_select := null;
x_attrn_vl_attr_select := null;
,x_attrd_attr_select out nocopy long
,x_attrn_attr_select out nocopy long
,x_attrn_vl_attr_select out nocopy long
)
IS
-----------------------
-- Declare constants --
-----------------------
l_api_name constant varchar2(100) := G_PACKAGE_NAME||'.Get_Seed_Dim_Attribute_Sql';
x_attrd_attr_select := null;
x_attrn_attr_select := null;
x_attrn_vl_attr_select := null;
' select a.attribute_varchar_label'||
' ,v.version_display_code'||
' ,a.attribute_data_type_code'||
' ,a.attribute_value_column_name'||
' ,a.attribute_dimension_id'||
' ,dim.dimension_varchar_label as attr_dimension_varchar_label'||
' from fem_dim_attributes_b@'||p_seed_db_link||' a'||
' ,fem_dim_attr_versions_b@'||p_seed_db_link||' v'||
' ,fem_dimensions_b@'||p_seed_db_link||' dim'||
' where a.dimension_id = :b_dimension_id'||
' and a.personal_flag = ''N'''||
' and a.queryable_for_reporting_flag = ''Y'''||
' and a.allow_multiple_assignment_flag = ''N'''||
' and v.attribute_id = a.attribute_id'||
' and v.default_version_flag = ''Y'''||
' and dim.dimension_id (+) = a.attribute_dimension_id'||
' order by a.attribute_required_flag desc'||
' ,a.attribute_varchar_label';
' select member_b_table_name'||
' ,member_tl_table_name'||
' ,member_col'||
' ,member_display_code_col'||
' ,member_name_col'||
' ,decode(value_set_required_flag'||
' ,''Y'','' and adb.value_set_id = attr.dim_attribute_value_set_id'''||
' ,null)'||
' ,decode(value_set_required_flag'||
' ,''Y'','' and adtl.value_set_id = attr.dim_attribute_value_set_id'''||
' ,null)'||
' ,decode(member_tl_table_name'||
' ,member_vl_object_name,null'||
' ,'' and adtl.language = tl.language'')'||
' from fem_xdim_dimensions@'||p_seed_db_link||
' where dimension_id = :b_attribute_dimension_id'
into l_attr_mem_b_tab
,l_attr_mem_tl_tab
,l_attr_mem_col
,l_attr_mem_display_code_col
,l_attr_mem_name_col
,l_attrd_value_set_where
,l_attrn_value_set_where
,l_attrn_language_where
using l_attr_tbl(i).attribute_dimension_id;
' select adb.'||l_attr_mem_display_code_col||
' from '||l_attr_mem_b_tab||' adb'||
' ,'||p_attribute_table_name||' attr'||
' ,fem_dim_attributes_b a'||
' ,fem_dim_attr_versions_b v'||
' where adb.'||l_attr_mem_col||' = attr.'||l_attr_tbl(i).attribute_value_column_name||
l_attrd_value_set_where||
' and a.dimension_id = '||p_dimension_id||
' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
' and v.attribute_id = a.attribute_id'||
' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
' and attr.attribute_id = a.attribute_id'||
' and attr.version_id = v.version_id'||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where;
' select adtl.'||l_attr_mem_name_col||
' from '||l_attr_mem_tl_tab||' adtl'||
' ,'||p_attribute_table_name||' attr'||
' ,fem_dim_attributes_b a'||
' ,fem_dim_attr_versions_b v'||
' where adtl.'||l_attr_mem_col||' = attr.'||l_attr_tbl(i).attribute_value_column_name||
l_attrn_value_set_where||
l_attrn_language_where||
' and a.dimension_id = '||p_dimension_id||
' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
' and v.attribute_id = a.attribute_id'||
' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
' and attr.attribute_id = a.attribute_id'||
' and attr.version_id = v.version_id'||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where;
' select ext_attr.number_assign_value'||
' from '||p_attribute_table_name||' attr'||
' ,fem_dim_attributes_b a'||
' ,fem_dim_attr_versions_b v'||
' ,fem_ext_acct_types_attr ext_attr'||
' ,fem_dim_attributes_b ext_a'||
' ,fem_dim_attr_versions_b ext_v'||
' where a.dimension_id = '||p_dimension_id||
' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
' and v.attribute_id = a.attribute_id'||
' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
' and attr.attribute_id = a.attribute_id'||
' and attr.version_id = v.version_id'||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where||
' and ext_a.dimension_id = '||l_attr_tbl(i).attribute_dimension_id||
' and ext_a.attribute_varchar_label = ''SIGN'''||
' and ext_v.attribute_id = ext_a.attribute_id'||
' and ext_v.version_display_code = ''Default'''||
' and ext_attr.attribute_id = ext_a.attribute_id'||
' and ext_attr.version_id = ext_v.version_id'||
' and ext_attr.ext_account_type_code = attr.'||l_attr_tbl(i).attribute_value_column_name;
' select ext_attr.dim_attribute_varchar_member'||
' from '||p_attribute_table_name||' attr'||
' ,fem_dim_attributes_b a'||
' ,fem_dim_attr_versions_b v'||
' ,fem_ext_acct_types_attr ext_attr'||
' ,fem_dim_attributes_b ext_a'||
' ,fem_dim_attr_versions_b ext_v'||
' where a.dimension_id = '||p_dimension_id||
' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
' and v.attribute_id = a.attribute_id'||
' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
' and attr.attribute_id = a.attribute_id'||
' and attr.version_id = v.version_id'||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where||
' and ext_a.dimension_id = '||l_attr_tbl(i).attribute_dimension_id||
' and ext_a.attribute_varchar_label = ''BASIC_ACCOUNT_TYPE_CODE'''||
' and ext_v.attribute_id = ext_a.attribute_id'||
' and ext_v.version_display_code = ''Default'''||
' and ext_attr.attribute_id = ext_a.attribute_id'||
' and ext_attr.version_id = ext_v.version_id'||
' and ext_attr.ext_account_type_code = attr.'||l_attr_tbl(i).attribute_value_column_name;
' select bsc_tl.basic_account_type_name'||
' from '||p_attribute_table_name||' attr'||
' ,fem_dim_attributes_b a'||
' ,fem_dim_attr_versions_b v'||
' ,fem_ext_acct_types_attr ext_attr'||
' ,fem_dim_attributes_b ext_a'||
' ,fem_dim_attr_versions_b ext_v'||
' ,fem_basic_acct_types_tl bsc_tl'||
' where a.dimension_id = '||p_dimension_id||
' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
' and v.attribute_id = a.attribute_id'||
' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
' and attr.attribute_id = a.attribute_id'||
' and attr.version_id = v.version_id'||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where||
' and ext_a.dimension_id = '||l_attr_tbl(i).attribute_dimension_id||
' and ext_a.attribute_varchar_label = ''BASIC_ACCOUNT_TYPE_CODE'''||
' and ext_v.attribute_id = ext_a.attribute_id'||
' and ext_v.version_display_code = ''Default'''||
' and ext_attr.attribute_id = ext_a.attribute_id'||
' and ext_attr.version_id = ext_v.version_id'||
' and ext_attr.ext_account_type_code = attr.'||l_attr_tbl(i).attribute_value_column_name||
' and bsc_tl.basic_account_type_code = ext_attr.dim_attribute_varchar_member'||
' and bsc_tl.language = tl.language';
' select attr.'||l_attr_tbl(i).attribute_value_column_name||
' from '||p_attribute_table_name||' attr'||
' ,fem_dim_attributes_b a'||
' ,fem_dim_attr_versions_b v'||
' where a.dimension_id = '||p_dimension_id||
' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
' and v.attribute_id = a.attribute_id'||
' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
' and attr.attribute_id = a.attribute_id'||
' and attr.version_id = v.version_id'||
' and attr.'||p_member_col||' = b.'||p_member_col||
l_value_set_where;
x_attrd_attr_select := x_attrd_attr_select||
' ,('||l_attrd_sql_stmt||') '||l_attr_col;
x_attrn_attr_select := x_attrn_attr_select||
' ,('||l_attrn_sql_stmt||') '||l_attr_col;
x_attrn_vl_attr_select := x_attrn_vl_attr_select||
' ,'||l_attr_col;
x_attrd_attr_select := x_attrd_attr_select||
' ,('||l_sign_attrd_sql_stmt||') EXTENDED_ACCOUNT_SIGN'||
' ,('||l_bsc_attrd_sql_stmt||') BASIC_ACCOUNT_TYPE';
x_attrn_attr_select := x_attrn_attr_select||
' ,('||l_bsc_attrn_sql_stmt||') BASIC_ACCOUNT_TYPE';
x_attrn_vl_attr_select := x_attrn_vl_attr_select||
' ,BASIC_ACCOUNT_TYPE';
x_attrd_attr_select := null;
x_attrn_attr_select := null;
x_attrn_vl_attr_select := null;