[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_hier_def constant varchar2(30) := 'UPDATE_HIER_DEF';
PROCEDURE bld_insert_hier_rels_stmt (
p_dimension_varchar_label in varchar2
,p_value_set_required_flag in varchar2
,p_target_hier_table in varchar2
,p_target_hierval_table in varchar2
,x_insert_hier_rels_stmt out nocopy varchar2
);
PROCEDURE bld_delete_hier_rels_stmt (
p_dimension_varchar_label in varchar2
,p_execution_mode in varchar2
,p_source_hier_table in varchar2 := null
,p_target_hier_table in varchar2 := null
,p_target_hierval_table in varchar2 := null
,x_delete_hier_rels_stmt out nocopy varchar2
);
PROCEDURE bld_insert_hierval_rels_stmt (
p_dimension_varchar_label in varchar2
,p_target_hierval_table in varchar2
,x_insert_hierval_rels_stmt out nocopy varchar2
);
FUNCTION bld_update_status_stmt (
p_dimension_varchar_label in varchar2
,p_execution_mode in varchar2
,p_value_set_required_flag in varchar2 := null
,p_source_hier_table in varchar2
,p_rowid_flag in varchar2 := null
,p_hier_object_name_flag in varchar2 := null
,p_hier_obj_def_name_flag in varchar2 := null
,p_parent_flag in varchar2 := null
,p_child_flag in varchar2 := null
)
RETURN varchar2;
l_insert_hier_rels_stmt varchar2(10000);
l_delete_hier_t_rels_stmt varchar2(10000);
l_delete_hier_rels_stmt varchar2(10000);
l_insert_hierval_rels_stmt varchar2(10000);
select object_id
into l_loader_object_id
from fem_object_definition_b
where object_definition_id = l_loader_obj_def_id
and object_id in (
select object_id
from fem_object_catalog_b
where object_type_code = 'HIERARCHY_LOADER'
);
select count(*)
into l_count
from fem_object_catalog_b o
,fem_user_folders u
where o.object_id = l_loader_object_id
and u.folder_id = o.folder_id
and u.user_id = l_user_id;
select f.folder_name
into l_loader_folder_name
from fem_folders_vl f
,fem_object_catalog_b o
where o.object_id = l_loader_object_id
and f.folder_id = o.folder_id;
bld_insert_hier_rels_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_value_set_required_flag => l_value_set_required_flag
,p_target_hier_table => l_target_hier_table
,p_target_hierval_table => l_target_hierval_table
,x_insert_hier_rels_stmt => l_insert_hier_rels_stmt
);
bld_delete_hier_rels_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,x_delete_hier_rels_stmt => l_delete_hier_t_rels_stmt
);
bld_delete_hier_rels_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_target_hier_table => l_target_hier_table
,x_delete_hier_rels_stmt => l_delete_hier_rels_stmt
);
bld_insert_hierval_rels_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_target_hierval_table => l_target_hierval_table
,x_insert_hierval_rels_stmt => l_insert_hierval_rels_stmt
);
' select count(*)'||
' from fem_hierarchies_t'||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hier_obj_def_display_name = :b_hier_obj_def_display_name'||
' and dimension_varchar_label = :b_dimension_varchar_label'||
l_status_clause||
' and language = userenv(''LANG'')'
into l_count
using p_hierarchy_object_name
,p_hier_obj_def_display_name
,p_dimension_varchar_label;
ld_load_type := g_update_hier_def;
select h.hierarchy_obj_id
,h.hierarchy_type_code
,h.group_sequence_enforced_code
,h.multi_top_flag
,h.multi_value_set_flag
,h.hierarchy_usage_code
,h.flattened_rows_flag
,h.calendar_id
into ld_hierarchy_object_id
,ld_hierarchy_type_code
,ld_group_seq_enforced_code
,ld_multi_top_flag
,ld_multi_value_set_flag
,ld_hierarchy_usage_code
,ld_flattened_rows_flag
,ld_calendar_id
from fem_object_catalog_vl cat
,fem_hierarchies h
where cat.object_name = ld_hierarchy_object_name
and cat.object_type_code = 'HIERARCHY'
and h.hierarchy_obj_id = cat.object_id
and h.dimension_id = l_dimension_id;
select object_definition_id
,effective_start_date
,effective_end_date
into ld_hier_obj_def_id
,ld_effective_start_date
,ld_effective_end_date
from fem_object_definition_vl
where object_id = ld_hierarchy_object_id
and display_name = ld_hier_obj_def_display_name
and old_approved_copy_flag = 'N';
select object_definition_id
,trunc(effective_start_date)
,trunc(effective_end_date)
into l_max_object_definition_id
,l_max_effective_start_date
,l_max_effective_end_date
from fem_object_definition_b b1
where object_id = ld_hierarchy_object_id
and old_approved_copy_flag = 'N'
and effective_end_date = (
select max(effective_end_date)
from fem_object_definition_b b2
where b2.object_id = b1.object_id
and b2.old_approved_copy_flag = b1.old_approved_copy_flag
);
,p_msg_text => 'Step 3.4: Update Hierarchy Definition Validations'
);
if (ld_load_type = g_update_hier_def) then
------------------------------------------------------------------------------
-- STEP 3.4.1: Check for Edit or Approval Lock.
------------------------------------------------------------------------------
FEM_ENGINES_PKG.tech_message (
p_severity => g_log_level_1
,p_module => G_BLOCK||'.'||l_api_name
,p_msg_text => 'Step 3.4.1: Check for Edit or Approval Lock'
);
' select count(*)'||
' from '||l_target_hier_table||
' where hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and read_only_flag = ''Y'''
into l_count
using ld_hier_obj_def_id;
select f.folder_id
into ld_folder_id
from fem_folders_vl f
,fem_user_folders uf
where uf.folder_id = f.folder_id
and uf.user_id = l_user_id
and uf.write_flag = 'Y'
and f.folder_name = ld_folder_name;
select 1
into l_dummy
from fem_lookups
where lookup_type = 'FEM_HIERARCHY_TYPE_DSC'
and lookup_code = ld_hierarchy_type_code;
select 1
into l_dummy
from fem_lookups
where lookup_type = 'FEM_HIERARCHY_USAGE_DSC'
and lookup_code = ld_hierarchy_usage_code;
select 1
into l_dummy
from fem_lookups
where lookup_type = 'FEM_GROUP_SEQ_ENFORCED_DSC'
and lookup_code = ld_group_seq_enforced_code;
select calendar_id
into ld_calendar_id
from fem_calendars_b
where calendar_display_code = ld_calendar_dc;
' select count(*)'||
' from fem_hier_value_sets_t'||
' where hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and language = userenv(''LANG'')'
into l_vs_count
using ld_hierarchy_object_name;
' update fem_hier_value_sets_t'||
' set status = :b_status'||
' where hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and language = userenv(''LANG'')'
using ld_status
,ld_hierarchy_object_name;
' select count(*)'||
' from fem_hier_dim_grps_t'||
' where hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and language = userenv(''LANG'')'
into l_count
using p_hierarchy_object_name;
' update fem_hier_dim_grps_t'||
' set status = :b_status'||
' where rowid = :b_rowid'||
l_status_clause||
' and language = userenv(''LANG'')'
using tg_status(j)
,tg_rowid(j);
tg_rowid.DELETE;
tg_status.DELETE;
' update fem_hier_value_sets_t'||
' set status = :b_status'||
' where rowid = :b_rowid'||
l_status_clause||
' and language = userenv(''LANG'')'
using tv_status(j)
,tv_rowid(j);
tv_rowid.DELETE;
tv_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_hier_object_name_flag => 'Y'
,p_hier_obj_def_name_flag => 'Y'
,p_child_flag => 'Y'
)
using th_status(j)
,ld_hierarchy_object_name
,ld_hier_obj_def_display_name
,ld_calendar_dc
,th_child_dim_grp_dc(j)
,th_child_cal_period_end_date(j)
,th_child_cal_period_number(j);
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_value_set_required_flag => l_value_set_required_flag
,p_source_hier_table => l_source_hier_table
,p_hier_object_name_flag => 'Y'
,p_hier_obj_def_name_flag => 'Y'
,p_child_flag => 'Y'
)
using th_status(j)
,ld_hierarchy_object_name
,ld_hier_obj_def_display_name
,th_child_dc(j)
,th_child_value_set_dc(j);
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_value_set_required_flag => l_value_set_required_flag
,p_source_hier_table => l_source_hier_table
,p_hier_object_name_flag => 'Y'
,p_hier_obj_def_name_flag => 'Y'
,p_child_flag => 'Y'
)
using th_status(j)
,ld_hierarchy_object_name
,ld_hier_obj_def_display_name
,th_child_dc(j);
th_child_dc.DELETE;
th_child_value_set_dc.DELETE;
th_child_dim_grp_dc.DELETE;
th_child_cal_period_end_date.DELETE;
th_child_cal_period_number.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using ld_status
,th_rowid(j);
th_rowid.DELETE;
th_child_id.DELETE;
th_child_value_set_id.DELETE;
th_child_dimension_grp_id.DELETE;
th_display_order_num.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
,p_msg_text => 'Step 7: Inserting Flattened Hierarchy Records in '||l_target_hierval_table
);
,p_msg_text => 'Step 7.1: Inserting Root Nodes Records in '||l_target_hierval_table
);
execute immediate l_insert_hierval_rels_stmt
using l_request_id
,th_rowid(j)
,1
,th_child_id(j)
,th_child_dimension_grp_id(j)
,1
,th_child_id(j)
,th_child_dimension_grp_id(j)
,th_display_order_num(j)
,to_number(null);
execute immediate l_insert_hierval_rels_stmt
using l_request_id
,th_rowid(j)
,1
,th_child_id(j)
,th_child_value_set_id(j)
,th_child_dimension_grp_id(j)
,1
,th_child_id(j)
,th_child_value_set_id(j)
,th_child_dimension_grp_id(j)
,th_display_order_num(j)
,to_number(null);
th_rowid.DELETE;
th_child_id.DELETE;
th_child_value_set_id.DELETE;
th_child_dimension_grp_id.DELETE;
th_display_order_num.DELETE;
,p_msg_text => 'Step 7.2: Inserting Relationship Records in '||l_target_hierval_table
);
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_hier_object_name_flag => 'Y'
,p_hier_obj_def_name_flag => 'Y'
)
using ld_status
,ld_hierarchy_object_name
,ld_hier_obj_def_display_name;
execute immediate l_insert_hierval_rels_stmt
using l_request_id
,th_rowid(j)
,th_parent_depth_num(j)
,th_parent_id(j)
,th_parent_dimension_grp_id(j)
,th_child_depth_num(j)
,th_child_id(j)
,th_child_dimension_grp_id(j)
,th_display_order_num(j)
,th_wt_pct(j);
execute immediate l_insert_hierval_rels_stmt
using l_request_id
,th_rowid(j)
,th_parent_depth_num(j)
,th_parent_id(j)
,th_parent_value_set_id(j)
,th_parent_dimension_grp_id(j)
,th_child_depth_num(j)
,th_child_id(j)
,th_child_value_set_id(j)
,th_child_dimension_grp_id(j)
,th_display_order_num(j)
,th_wt_pct(j);
th_rowid.DELETE;
th_parent_depth_num.DELETE;
th_parent_id.DELETE;
th_parent_value_set_id.DELETE;
th_parent_dimension_grp_id.DELETE;
th_child_depth_num.DELETE;
th_child_id.DELETE;
th_child_value_set_id.DELETE;
th_child_dimension_grp_id.DELETE;
th_display_order_num.DELETE;
th_wt_pct.DELETE;
th_status.DELETE;
select att.attribute_id
,ver.version_id
into l_attribute_id
,l_attr_version_id
from fem_dim_attributes_b att
,fem_dim_attr_versions_b ver
where att.attribute_varchar_label = 'RECON_LEAF_NODE_FLAG'
and att.dimension_id = l_dimension_id
and ver.attribute_id = att.attribute_id
and ver.default_version_flag = 'Y';
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
bld_update_status_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_source_hier_table => l_source_hier_table
,p_rowid_flag => 'Y'
)
using th_status(j)
,th_rowid(j);
th_rowid.DELETE;
th_status.DELETE;
tg_dimension_group_id.DELETE;
tg_depth_num.DELETE;
' update fem_hier_value_sets_t'||
' set status = :b_status'||
' where hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and language = userenv(''LANG'')'
using ld_status
,ld_hierarchy_object_name;
,p_msg_text => 'Step 10.2: Insert into FEM_OBJECT_CATALOG_B/_TL'
);
select fem_object_id_seq.nextval
into ld_hierarchy_object_id
from dual;
FEM_OBJECT_CATALOG_PKG.INSERT_ROW (
x_rowid => l_rowid
,x_object_id => ld_hierarchy_object_id
,x_object_type_code => 'HIERARCHY'
,x_folder_id => ld_folder_id
,x_local_vs_combo_id => null
,x_object_access_code => 'W' --todo
,x_object_origin_code => 'IMPORT' --todo
,x_object_version_number => g_object_version_number
,x_object_name => ld_hierarchy_object_name
,x_description => ld_hierarchy_object_name
,x_creation_date => sysdate
,x_created_by => l_user_id
,x_last_update_date => sysdate
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
,p_msg_text => 'Step 10.3: Insert into FEM_HIERARCHIES'
);
insert into fem_hierarchies (
hierarchy_obj_id
,dimension_id
,hierarchy_type_code
,group_sequence_enforced_code
,multi_top_flag
,financial_category_flag
,value_set_id
,calendar_id
,period_type
,personal_flag
,flattened_rows_flag
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login
,hierarchy_usage_code
,multi_value_set_flag
,object_version_number
) values (
ld_hierarchy_object_id
,l_dimension_id
,ld_hierarchy_type_code
,ld_group_seq_enforced_code
,ld_multi_top_flag
,'N'
,ld_value_set_id
,ld_calendar_id
,null
,'N'
,ld_flattened_rows_flag
,sysdate
,l_user_id
,l_user_id
,sysdate
,l_login_id
,ld_hierarchy_usage_code
,ld_multi_value_set_flag
,g_object_version_number
);
,p_msg_text => 'Step 10.4: Insert into FEM_HIER_DIMENSION_GRPS'
);
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 (
tg_dimension_group_id(j)
,ld_hierarchy_object_id
,tg_relative_dim_group_seq(j)
,sysdate
,l_user_id
,l_user_id
,sysdate
,l_login_id
,g_object_version_number
);
tg_dimension_group_id.DELETE;
tg_relative_dim_group_seq.DELETE;
,p_msg_text => 'Step 10.5: Insert into FEM_HIER_VALUE_SETS'
);
' delete from fem_hier_value_sets_t hvst'||
' where hvst.hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and hvst.language = userenv(''LANG'')'||
' and exists ('||
' select 1'||
' from fem_hier_value_sets hvs'||
' ,fem_value_sets_b vsb'||
' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
' and vsb.value_set_id = hvs.value_set_id'||
' and vsb.value_set_display_code = hvst.value_set_display_code'||
' )'
using ld_hierarchy_object_name
,ld_hierarchy_object_id;
insert into fem_hier_value_sets (
hierarchy_obj_id
,value_set_id
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login
,object_version_number
) values (
ld_hierarchy_object_id
,tv_value_set_id(j)
,sysdate
,l_user_id
,l_user_id
,sysdate
,l_login_id
,g_object_version_number
);
tv_value_set_id.DELETE;
insert into fem_hier_value_sets (
hierarchy_obj_id
,value_set_id
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login
,object_version_number
) values (
ld_hierarchy_object_id
,ld_calendar_id
,sysdate
,l_user_id
,l_user_id
,sysdate
,l_login_id
,g_object_version_number
);
,p_msg_text => 'Step 11: Insert Hierarchy Object Definition Records'
);
update fem_object_definition_b
set effective_end_date = l_new_max_eff_end_date
where object_definition_id = l_new_max_obj_def_id;
select fem_object_definition_id_seq.nextval
into ld_hier_obj_def_id
from dual;
FEM_OBJECT_DEFINITION_PKG.INSERT_ROW (
x_rowid => l_rowid
,x_object_definition_id => ld_hier_obj_def_id
,x_object_id => ld_hierarchy_object_id
,x_effective_start_date => ld_effective_start_date
,x_effective_end_date => ld_effective_end_date
,x_object_origin_code => 'IMPORT' --todo
,x_approval_status_code => 'NOT_APPLICABLE'
,x_old_approved_copy_flag => 'N'
,x_old_approved_copy_obj_def_id => null
,x_approved_by => null
,x_approval_date => null
,x_display_name => ld_hier_obj_def_display_name
,x_description => ld_hier_obj_def_display_name
,x_creation_date => sysdate
,x_created_by => l_user_id
,x_last_update_date => sysdate
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
,x_object_version_number => g_object_version_number
);
insert into fem_hier_definitions (
hierarchy_obj_def_id
,flattened_rows_completion_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login
,object_version_number
) values (
ld_hier_obj_def_id
,decode(ld_flattened_rows_flag,'Y','PENDING','COMPLETED')
,sysdate
,l_user_id
,l_user_id
,sysdate
,l_login_id
,g_object_version_number
);
if (ld_load_type = g_update_hier_def) then
FEM_ENGINES_PKG.tech_message (
p_severity => g_log_level_1
,p_module => G_BLOCK||'.'||l_api_name
,p_msg_text => 'Step 12: Deleting Hierarchy Relationship Records'
);
execute immediate l_delete_hier_rels_stmt
using ld_hier_obj_def_id;
update fem_hier_definitions
set flattened_rows_completion_code = 'PENDING'
,last_updated_by = l_user_id
,last_update_date = sysdate
,last_update_login = l_login_id
where hierarchy_obj_def_id = ld_hier_obj_def_id;
,p_msg_text => 'Step 13: Inserting Relationship Records into '||l_target_hierval_table
);
execute immediate l_insert_hier_rels_stmt
using ld_hier_obj_def_id
,l_user_id
,l_user_id
,l_login_id
,g_object_version_number
,l_request_id;
execute immediate l_delete_hier_t_rels_stmt
using ld_hierarchy_object_name
,ld_hier_obj_def_display_name;
' delete from fem_hier_value_sets_t'||
' where hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause
using ld_hierarchy_object_name;
' delete from fem_hier_dim_grps_t'||
' where hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause
using ld_hierarchy_object_name;
' delete from fem_hierarchies_t'||
' where rowid = :b_rowid'||
l_status_clause
using ld_rowid;
' update fem_hierarchies_t'||
' set status = :b_status'||
' where rowid = :b_rowid'||
l_status_clause
using ld_status
,ld_rowid;
select dimension_id
,hierarchy_table_name
,hierarchy_table_name||'_T'
,member_b_table_name
,attribute_table_name
,member_col
,member_display_code_col
,group_use_code
,value_set_required_flag
,hier_type_allowed_code
,hier_versioning_type_code
into x_dimension_id
,x_target_hier_table
,x_source_hier_table
,x_member_b_table
,x_member_attr_table
,x_member_col
,x_member_dc_col
,x_group_use_code
,x_value_set_required_flag
,x_hier_type_allowed_code
,x_hier_versioning_type_code
from fem_xdim_dimensions_vl
where dimension_varchar_label = p_dimension_varchar_label
and composite_dimension_flag = 'N'
and hierarchy_table_name is not null
and read_only_flag = 'N';
,p_last_update_login => p_login_id
,p_program_id => p_pgm_id
,p_program_login_id => p_login_id
,p_program_application_id => p_pgm_app_id
,p_exec_mode_code => p_execution_mode
,p_hierarchy_name => p_hierarchy_object_name
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_last_update_login => p_login_id
,p_exec_mode_code => p_execution_mode
,x_exec_state => l_exec_state
,x_prev_request_id => l_prev_request_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_last_update_login => p_login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
| Updates the status of the request and object execution in the
| processing locks tables.
|
| SCOPE - PRIVATE
|
+===========================================================================*/
PROCEDURE eng_master_post_proc (
p_request_id in number
,p_object_id in number
,p_exec_status_code in varchar2
,p_user_id in number
,p_login_id in number
,p_dimension_varchar_label in varchar2
,p_execution_mode in varchar2
,p_target_hierval_table in varchar2
)
IS
l_api_name constant varchar2(30) := 'register_process_execution';
l_delete_hierval_rels_stmt varchar2(10000);
bld_delete_hier_rels_stmt (
p_dimension_varchar_label => p_dimension_varchar_label
,p_execution_mode => p_execution_mode
,p_target_hierval_table => p_target_hierval_table
,x_delete_hier_rels_stmt => l_delete_hierval_rels_stmt
);
execute immediate l_delete_hierval_rels_stmt
using p_request_id;
,p_msg_text => 'Step 2: Update Object Execution Errors and the Status'
);
FEM_PL_PKG.update_obj_exec_errors (
p_api_version => 1.0
,p_request_id => p_request_id
,p_object_id => p_object_id
,p_errors_reported => 1
,p_errors_reprocessed => 0
,p_user_id => p_user_id
,p_last_update_login => p_login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
FEM_PL_PKG.update_obj_exec_status (
p_api_version => 1.0
,p_request_id => p_request_id
,p_object_id => p_object_id
,p_exec_status_code => p_exec_status_code
,p_user_id => p_user_id
,p_last_update_login => p_login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
FEM_PL_PKG.update_request_status (
p_api_version => 1.0
,p_request_id => p_request_id
,p_exec_status_code => p_exec_status_code
,p_user_id => p_user_id
,p_last_update_login => p_login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
' select hvst.rowid'||
' ,''INVALID_VALUE_SET'''||
' from fem_hier_value_sets_t hvst'||
' where hvst.hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and hvst.language = userenv(''LANG'')'||
' and not exists ('||
' select 1'||
' from fem_value_sets_b vs'||
' where vs.value_set_display_code = hvst.value_set_display_code'||
' and vs.dimension_id = :b_dimension_id'||
' )';
' select hdgt.rowid'||
' ,''INVALID_DIMENSION_GROUP'''||
' from fem_hier_dim_grps_t hdgt'||
' where hdgt.hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and hdgt.language = userenv(''LANG'')'||
' and not exists ('||
' select 1'||
' from fem_dimension_grps_b dg'||
' where dg.dimension_group_display_code = hdgt.dimension_group_display_code'||
' and dg.dimension_id = :b_dimension_id'||
' )';
' select ht.rowid'||
' ,''INVALID_CALENDAR'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and ht.calendar_display_code <> :b_calendar_display_code'||
l_status_clause||
' and ht.language = userenv(''LANG'')';
' select ht.rowid'||
' ,''INVALID_VALUE_SET'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause_1||
' and ht.language = userenv(''LANG'')'||
' and ('||
' not exists ('||
' select 1'||
' from fem_hier_value_sets_t hvst'||
' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
' and hvst.value_set_display_code = ht.parent_value_set_display_code'||
l_status_clause_2||
' and hvst.language = userenv(''LANG'')'||
' )'||
' or'||
' not exists ('||
' select 1'||
' from fem_hier_value_sets_t hvst'||
' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
' and hvst.value_set_display_code = ht.child_value_set_display_code'||
l_status_clause_2||
' and hvst.language = userenv(''LANG'')'||
' )'||
' )';
' select ht.rowid'||
' ,''INVALID_VALUE_SET'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and ht.language = userenv(''LANG'')'||
' and ('||
' not exists ('||
' select 1'||
' from fem_hier_value_sets hvs'||
' ,fem_value_sets_b vsb'||
' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
' and vsb.value_set_id = hvs.value_set_id'||
' and vsb.value_set_display_code = ht.parent_value_set_display_code'||
' )'||
' or'||
' not exists ('||
' select 1'||
' from fem_hier_value_sets hvs'||
' ,fem_value_sets_b vsb'||
' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
' and vsb.value_set_id = hvs.value_set_id'||
' and vsb.value_set_display_code = ht.child_value_set_display_code'||
' )'||
' )';
' select ht.rowid'||
' ,''INVALID_VALUE_SET'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause_1||
' and ht.language = userenv(''LANG'')'||
' and ('||
' ('||
' not exists ('||
' select 1'||
' from fem_hier_value_sets hvs'||
' ,fem_value_sets_b vsb'||
' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
' and vsb.value_set_id = hvs.value_set_id'||
' and vsb.value_set_display_code = ht.parent_value_set_display_code'||
' )'||
' and'||
' not exists ('||
' select 1'||
' from fem_hier_value_sets_t hvst'||
' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
' and hvst.value_set_display_code = ht.parent_value_set_display_code'||
l_status_clause_2||
' and hvst.language = userenv(''LANG'')'||
' )'||
' )'||
' or ('||
' not exists ('||
' select 1'||
' from fem_hier_value_sets hvs'||
' ,fem_value_sets_b vsb'||
' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
' and vsb.value_set_id = hvs.value_set_id'||
' and vsb.value_set_display_code = ht.child_value_set_display_code'||
' )'||
' and'||
' not exists ('||
' select 1'||
' from fem_hier_value_sets_t hvst'||
' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
' and hvst.value_set_display_code = ht.child_value_set_display_code'||
l_status_clause_2||
' and hvst.language = userenv(''LANG'')'||
' )'||
' )'||
' )';
' select ht.rowid'||
' ,''INVALID_MEMBER'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and ht.calendar_display_code = :b_calendar_display_code'||
l_status_clause||
' and ht.language = userenv(''LANG'')'||
' and ('||
' not exists ('||
' select 1'||
' from fem_dimension_grps_b dg'||
' ,'||p_member_b_table||' b'||
' where dg.dimension_group_display_code = ht.parent_dim_grp_display_code'||
' and dg.dimension_id = :b_dimension_id'||
' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(ht.parent_cal_period_end_date,ht.parent_cal_period_number,:b_calendar_id,dg.dimension_group_id)'||
-- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
-- ' and b.enabled_flag = ''Y'''||
' and b.personal_flag = ''N'''||
' )'||
' or'||
' not exists ('||
' select 1'||
' from fem_dimension_grps_b dg'||
' ,'||p_member_b_table||' b'||
' where dg.dimension_group_display_code = ht.child_dim_grp_display_code'||
' and dg.dimension_id = :b_dimension_id'||
' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(ht.child_cal_period_end_date,ht.child_cal_period_number,:b_calendar_id,dg.dimension_group_id)'||
-- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
--' and b.enabled_flag = ''Y'''||
' and b.personal_flag = ''N'''||
' )'||
' )';
' select ht.rowid'||
' ,''INVALID_MEMBER'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and ht.language = userenv(''LANG'')'||
' and ('||
' not exists ('||
' select 1'||
' from '||p_member_b_table||' b'||
' ,fem_value_sets_b vs'||
' where b.'||p_member_dc_col||' = ht.parent_display_code'||
' and b.value_set_id = vs.value_set_id'||
-- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
-- ' and b.enabled_flag = ''Y'''||
' and b.personal_flag = ''N'''||
' and vs.value_set_display_code = ht.parent_value_set_display_code'||
' and vs.dimension_id = :b_dimension_id'||
' )'||
' or'||
' not exists ('||
' select 1'||
' from '||p_member_b_table||' b'||
' ,fem_value_sets_b vs'||
' where b.'||p_member_dc_col||' = ht.child_display_code'||
' and b.value_set_id = vs.value_set_id'||
-- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
-- ' and b.enabled_flag = ''Y'''||
' and b.personal_flag = ''N'''||
' and vs.value_set_display_code = ht.child_value_set_display_code'||
' and vs.dimension_id = :b_dimension_id'||
' )'||
' )';
' select ht.rowid'||
' ,''INVALID_MEMBER'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and ht.language = userenv(''LANG'')'||
' and ('||
' not exists ('||
' select 1'||
' from '||p_member_b_table||' b'||
' where b.'||p_member_dc_col||' = ht.parent_display_code'||
-- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
-- ' and b.enabled_flag = ''Y'''||
' and b.personal_flag = ''N'''||
' )'||
' or'||
' not exists ('||
' select 1'||
' from '||p_member_b_table||' b'||
' where b.'||p_member_dc_col||' = ht.child_display_code'||
-- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
-- ' and b.enabled_flag = ''Y'''||
' and b.personal_flag = ''N'''||
' )'||
' )';
' select null'||
' ,null'||
' ,ht.child_dim_grp_display_code'||
' ,ht.child_cal_period_end_date'||
' ,ht.child_cal_period_number'||
' ,''CHILD_WITH_MULTIPLE_PARENTS'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and ht.calendar_display_code = :b_calendar_display_code'||
l_status_clause||
' and ht.language = userenv(''LANG'')'||
' and not ('||
' ht.parent_dim_grp_display_code = ht.child_dim_grp_display_code'||
' and ht.parent_cal_period_end_date = ht.child_cal_period_end_date'||
' and ht.parent_cal_period_number = ht.child_cal_period_number'||
' )'||
' group by ht.child_dim_grp_display_code'||
' ,ht.child_cal_period_end_date'||
' ,ht.child_cal_period_number'||
' having count(ht.parent_cal_period_number) > 1';
' select ht.child_display_code'||
' ,ht.child_value_set_display_code'||
' ,null'||
' ,null'||
' ,null'||
' ,''CHILD_WITH_MULTIPLE_PARENTS'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and ht.language = userenv(''LANG'')'||
' and not ('||
' ht.parent_display_code = ht.child_display_code'||
' and ht.parent_value_set_display_code = ht.child_value_set_display_code'||
' )'||
' group by ht.child_display_code'||
' ,ht.child_value_set_display_code'||
' having count(ht.parent_display_code) > 1';
' select ht.child_display_code'||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,''CHILD_WITH_MULTIPLE_PARENTS'''||
' from '||p_source_hier_table||' ht'||
' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and ht.language = userenv(''LANG'')'||
' and ht.parent_display_code <> ht.child_display_code'||
' group by ht.child_display_code'||
' having count(ht.parent_display_code) > 1';
' select leaf.row_id'||
' ,''INVALID_RECONCILIATION_LEAF'''||
' from ('||
' select root.source_hier_t_rowid as row_id'||
' ,root.child_id'||
' from '||p_target_hierval_table||' root'||
' where root.request_id = :b_request_id'||
' and root.child_depth_num <> 1'||
' and not exists ('||
' select 1'||
' from '||p_target_hierval_table||' parent'||
' where parent.request_id = root.request_id'||
' and parent.parent_id = root.child_id'||
' )'||
' ) leaf'||
' where not exists ('||
' select 1'||
' from '||p_member_attr_table||' attv'||
' where attv.'||p_member_col||' = leaf.child_id'||
' and attv.dim_attribute_varchar_member = ''Y'''||
' and attv.attribute_id = :b_attribute_id'||
' and attv.version_id = :b_attr_version_id'||
' )';
' select leaf.row_id'||
' ,''INVALID_RECONCILIATION_LEAF'''||
' from ('||
' select root.source_hier_t_rowid as row_id'||
' ,root.child_id'||
' ,root.child_value_set_id'||
' from '||p_target_hierval_table||' root'||
' where root.request_id = :b_request_id'||
' and root.child_depth_num <> 1'||
' and not exists ('||
' select 1'||
' from '||p_target_hierval_table||' parent'||
' where parent.request_id = root.request_id'||
' and parent.parent_id = root.child_id'||
' and parent.parent_value_set_id = root.child_value_set_id'||
' )'||
' ) leaf'||
' where not exists ('||
' select 1'||
' from '||p_member_attr_table||' attv'||
' where attv.value_set_id = leaf.child_value_set_id'||
' and attv.'||p_member_col||' = leaf.child_id'||
' and attv.dim_attribute_varchar_member = ''Y'''||
' and attv.attribute_id = :b_attribute_id'||
' and attv.version_id = :b_attr_version_id'||
' )';
' select node.row_id'||
' ,''INVALID_RECONCILIATION_NODE'''||
' from ('||
' select min(parent.source_hier_t_rowid) as row_id'||
' ,parent.parent_id'||
' from '||p_target_hierval_table||' parent'||
' where parent.request_id = :b_request_id'||
' and parent.child_depth_num <> 1'||
' group by parent.parent_id'||
' ) node'||
' where not exists ('||
' select 1'||
' from '||p_member_attr_table||' attv'||
' where attv.'||p_member_col||' = node.parent_id'||
' and attv.dim_attribute_varchar_member = ''N'''||
' and attv.attribute_id = :b_attribute_id'||
' and attv.version_id = :b_attr_version_id'||
' )';
' select node.row_id'||
' ,''INVALID_RECONCILIATION_NODE'''||
' from ('||
' select min(parent.source_hier_t_rowid) as row_id'||
' ,parent.parent_id'||
' ,parent.parent_value_set_id'||
' from '||p_target_hierval_table||' parent'||
' where parent.request_id = :b_request_id'||
' and parent.child_depth_num <> 1'||
' group by parent.parent_id'||
' ,parent.parent_value_set_id'||
' ) node'||
' where not exists ('||
' select 1'||
' from '||p_member_attr_table||' attv'||
' where attv.value_set_id = node.parent_value_set_id'||
' and attv.'||p_member_col||' = node.parent_id'||
' and attv.dim_attribute_varchar_member = ''N'''||
' and attv.attribute_id = :b_attribute_id'||
' and attv.version_id = :b_attr_version_id'||
' )';
' select root.rowid'||
' ,''INVALID_ROOT_NODE'''||
' from '||p_source_hier_table||' root'||
' ,'||p_member_b_table||' b'||
' ,fem_dimension_grps_b dg'||
' where root.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
' and root.child_cal_period_end_date = root.parent_cal_period_end_date'||
' and root.child_cal_period_number = root.parent_cal_period_number'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and root.calendar_display_code = :b_calendar_display_code'||
l_status_clause||
' and root.language = userenv(''LANG'')'||
' and dg.dimension_group_display_code = root.parent_dim_grp_display_code'||
' and dg.dimension_id = :b_dimension_id'||
' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(root.parent_cal_period_end_date,root.parent_cal_period_number,:b_calendar_id,dg.dimension_group_id)'||
' and exists ('||
' select 1'||
' from '||p_source_hier_table||' child'||
' where child.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
' and child.child_cal_period_end_date = root.parent_cal_period_end_date'||
' and child.child_cal_period_number = root.parent_cal_period_number'||
' and child.hierarchy_object_name = root.hierarchy_object_name'||
' and child.hierarchy_obj_def_display_name = root.hierarchy_obj_def_display_name'||
' and child.calendar_display_code = root.calendar_display_code'||
' and child.status = root.status'||
' and child.language = root.language'||
' and not ('||
' child.parent_dim_grp_display_code = child.child_dim_grp_display_code'||
' and child.parent_cal_period_end_date = child.child_cal_period_end_date'||
' and child.parent_cal_period_number = child.child_cal_period_number'||
' )'||
' )';
' select root.rowid'||
' ,''INVALID_ROOT_NODE'''||
' from '||p_source_hier_table||' root'||
' ,'||p_member_b_table||' b'||
' ,fem_value_sets_b vs'||
' where root.child_display_code = root.parent_display_code'||
' and root.child_value_set_display_code = root.parent_value_set_display_code'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and root.language = userenv(''LANG'')'||
' and vs.value_set_display_code = root.parent_value_set_display_code'||
' and vs.dimension_id = :b_dimension_id'||
' and b.value_set_id = vs.value_set_id'||
' and b.'||p_member_dc_col||' = root.parent_display_code'||
' and exists ('||
' select 1'||
' from '||p_source_hier_table||' child'||
' where child.child_display_code = root.parent_display_code'||
' and child.child_value_set_display_code = root.parent_value_set_display_code'||
' and child.hierarchy_object_name = root.hierarchy_object_name'||
' and child.hierarchy_obj_def_display_name = root.hierarchy_obj_def_display_name'||
' and child.status = root.status'||
' and child.language = root.language'||
' and not ('||
' child.parent_display_code = child.child_display_code'||
' and child.parent_value_set_display_code = child.child_value_set_display_code'||
' )'||
' )';
' select root.rowid'||
' ,''INVALID_ROOT_NODE'''||
' from '||p_source_hier_table||' root'||
' ,'||p_member_b_table||' b'||
' where root.child_display_code = root.parent_display_code'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and root.language = userenv(''LANG'')'||
' and b.'||p_member_dc_col||' = root.parent_display_code'||
' and exists ('||
' select 1'||
' from '||p_source_hier_table||' child'||
' where child.child_display_code = root.parent_display_code'||
' and child.hierarchy_object_name = root.hierarchy_object_name'||
' and child.hierarchy_obj_def_display_name = root.hierarchy_obj_def_display_name'||
' and child.status = root.status'||
' and child.language = root.language'||
' and child.parent_display_code <> child.child_display_code'||
' )';
' select hv.source_hier_t_rowid'||
' ,''GROUP_SEQ_RULE_VIOLATED'''||
' from '||p_target_hierval_table||' hv'||
' where hv.request_id = :b_request_id'||
' and not exists ('||
' select 1'||
' from fem_hier_dim_grps_t hdgt'||
' ,fem_dimension_grps_b dg'||
' where hdgt.hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and hdgt.language = userenv(''LANG'')'||
' and dg.dimension_group_display_code = hdgt.dimension_group_display_code'||
' and dg.dimension_id = :b_dimension_id'||
' and dg.dimension_group_id = hv.child_dimension_grp_id'||
' )';
' select hv.source_hier_t_rowid'||
' ,''GROUP_SEQ_RULE_VIOLATED'''||
' from '||p_target_hierval_table||' hv'||
' where hv.request_id = :b_request_id'||
' and not exists ('||
' select 1'||
' from fem_hier_dimension_grps hdg'||
' where hdg.hierarchy_obj_id = :b_hierarchy_object_id'||
' and hdg.dimension_group_id = hv.child_dimension_grp_id'||
' )';
' select hv.source_hier_t_rowid'||
' ,''GROUP_SEQ_RULE_VIOLATED'''||
' from '||p_target_hierval_table||' hv'||
' ,fem_dimension_grps_b dgp'||
' ,fem_dimension_grps_b dgc'||
' where hv.request_id = :b_request_id'||
' and not ('||
' hv.parent_id = hv.child_id'||
' )'||
' and dgp.dimension_group_id = hv.parent_dimension_grp_id'||
' and dgc.dimension_group_id = hv.child_dimension_grp_id'||
' and dgp.dimension_group_seq >= dgc.dimension_group_seq';
' select hv.source_hier_t_rowid'||
' ,''GROUP_SEQ_RULE_VIOLATED'''||
' from '||p_target_hierval_table||' hv'||
' ,fem_dimension_grps_b dgp'||
' ,fem_dimension_grps_b dgc'||
' where hv.request_id = :b_request_id'||
' and not ('||
' hv.parent_id = hv.child_id'||
' and hv.parent_value_set_id = hv.child_value_set_id'||
' )'||
' and dgp.dimension_group_id = hv.parent_dimension_grp_id'||
' and dgc.dimension_group_id = hv.child_dimension_grp_id'||
' and dgp.dimension_group_seq >= dgc.dimension_group_seq';
' select source_hier_t_rowid'||
' ,''GROUP_SEQ_RULE_VIOLATED'''||
' from '||p_target_hierval_table||
' where request_id = :b_request_id'||
' and child_depth_num = :b_depth_num'||
' and child_dimension_grp_id <> :b_dimension_group_id';
' select count(*)'||
' from '||p_source_hier_table||' root'||
' where root.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
' and root.child_cal_period_end_date = root.parent_cal_period_end_date'||
' and root.child_cal_period_number = root.parent_cal_period_number'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and root.calendar_display_code = :b_calendar_display_code'||
l_status_clause||
' and root.language = userenv(''LANG'')';
' select count(*)'||
' from '||p_source_hier_table||' root'||
' where root.child_display_code = root.parent_display_code'||
' and root.child_value_set_display_code = root.parent_value_set_display_code'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and root.language = userenv(''LANG'')';
' select count(*)'||
' from '||p_source_hier_table||' root'||
' where root.child_display_code = root.parent_display_code'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and root.language = userenv(''LANG'')';
' select vs.value_set_id'||
' from fem_hier_value_sets_t hvst'||
' ,fem_value_sets_b vs'||
' where hvst.hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and hvst.language = userenv(''LANG'')'||
' and vs.value_set_display_code = hvst.value_set_display_code'||
' and vs.dimension_id = :b_dimension_id';
' select dimension_group_id'||
' ,rownum as depth_num'||
' from ('||
' select dg.dimension_group_id'||
' from fem_hier_dim_grps_t hdgt'||
' ,fem_dimension_grps_b dg'||
' where hdgt.hierarchy_object_name = :b_hierarchy_object_name'||
l_status_clause||
' and hdgt.language = userenv(''LANG'')'||
' and dg.dimension_group_display_code = hdgt.dimension_group_display_code'||
' and dg.dimension_id = :b_dimension_id'||
' order by dg.dimension_group_seq'||
' )';
' select dimension_group_id'||
' ,rownum as depth_num'||
' from ('||
' select hdg.dimension_group_id'||
' from fem_hier_dimension_grps hdg'||
' where hdg.hierarchy_obj_id = :b_hierarchy_object_id'||
' order by hdg.relative_dimension_group_seq'||
' )';
' select ht.rowid'||
' ,ht.folder_name'||
' ,ht.hierarchy_object_name'||
' ,ht.hier_obj_def_display_name'||
' ,ht.effective_start_date'||
' ,ht.effective_end_date'||
' ,ht.calendar_display_code'||
' ,ht.language'||
' ,ht.dimension_varchar_label'||
' ,ht.hierarchy_type_code'||
' ,ht.group_sequence_enforced_code'||
' ,ht.multi_top_flag'||
' ,ht.multi_value_set_flag'||
' ,ht.hierarchy_usage_code'||
' ,ht.flattened_rows_flag'||
' ,ht.status'||
' from fem_hierarchies_t ht'||
' where ht.dimension_varchar_label = :b_dimension_varchar_label'||
' and ht.hierarchy_object_name = :b_hierarchy_object_name'||
' and ht.hier_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and ht.language = userenv(''LANG'')';
' select root.rowid'||
' ,b.'||p_member_col||
' ,null'||
' ,b.dimension_group_id'||
' ,root.display_order_num'||
' from '||p_source_hier_table||' root'||
' ,'||p_member_b_table||' b'||
' ,fem_dimension_grps_b dg'||
' where root.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
' and root.child_cal_period_end_date = root.parent_cal_period_end_date'||
' and root.child_cal_period_number = root.parent_cal_period_number'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and root.calendar_display_code = :b_calendar_display_code'||
l_status_clause||
' and root.language = userenv(''LANG'')'||
' and dg.dimension_group_display_code = root.parent_dim_grp_display_code'||
' and dg.dimension_id = :b_dimension_id'||
' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(root.parent_cal_period_end_date,root.parent_cal_period_number,:b_calendar_id,dg.dimension_group_id)';
' select root.rowid'||
' ,b.'||p_member_col||
' ,vs.value_set_id'||
l_dim_grp_clause||
' ,root.display_order_num'||
' from '||p_source_hier_table||' root'||
' ,'||p_member_b_table||' b'||
' ,fem_value_sets_b vs'||
' where root.child_display_code = root.parent_display_code'||
' and root.child_value_set_display_code = root.parent_value_set_display_code'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and root.language = userenv(''LANG'')'||
' and vs.value_set_display_code = root.parent_value_set_display_code'||
' and vs.dimension_id = :b_dimension_id'||
' and b.value_set_id = vs.value_set_id'||
' and b.'||p_member_dc_col||' = root.parent_display_code';
' select root.rowid'||
' ,b.'||p_member_col||
' ,-1'||
l_dim_grp_clause||
' ,root.display_order_num'||
' from '||p_source_hier_table||' root'||
' ,'||p_member_b_table||' b'||
' where root.child_display_code = root.parent_display_code'||
' and root.hierarchy_object_name = :b_hierarchy_object_name'||
' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and root.language = userenv(''LANG'')'||
' and b.'||p_member_dc_col||' = root.parent_display_code';
' select rel.row_id'||
' ,rel.depth_num'||
' ,bp.'||p_member_col||
' ,null'||
' ,bp.dimension_group_id'||
' ,rel.depth_num+1'||
' ,bc.'||p_member_col||
' ,null'||
' ,bc.dimension_group_id'||
' ,rel.display_order_num'||
' ,rel.weighting_pct'||
' ,rel.status'||
' from ('||
' select row_id'||
' ,level as depth_num'||
' ,display_order_num'||
' ,weighting_pct'||
' ,status'||
' ,parent_dim_grp_display_code'||
' ,parent_cal_period_end_date'||
' ,parent_cal_period_number'||
' ,child_dim_grp_display_code'||
' ,child_cal_period_end_date'||
' ,child_cal_period_number'||
' from ('||
' select rowid as row_id'||
' ,display_order_num'||
' ,weighting_pct'||
' ,status'||
' ,parent_dim_grp_display_code'||
' ,parent_cal_period_end_date'||
' ,parent_cal_period_number'||
' ,child_dim_grp_display_code'||
' ,child_cal_period_end_date'||
' ,child_cal_period_number'||
' from '||p_source_hier_table||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and calendar_display_code = :b_calendar_display_code'||
' and not ('||
' parent_dim_grp_display_code = child_dim_grp_display_code'||
' and parent_cal_period_end_date = child_cal_period_end_date'||
' and parent_cal_period_number = child_cal_period_number'||
' )'||
l_status_clause||
' and language = userenv(''LANG'')'||
' )'||
' start with (parent_dim_grp_display_code,parent_cal_period_end_date,parent_cal_period_number) in ('||
' select parent_dim_grp_display_code'||
' ,parent_cal_period_end_date'||
' ,parent_cal_period_number'||
' from '||p_source_hier_table||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and calendar_display_code = :b_calendar_display_code'||
' and child_dim_grp_display_code = parent_dim_grp_display_code'||
' and child_cal_period_end_date = parent_cal_period_end_date'||
' and child_cal_period_number = parent_cal_period_number'||
l_status_clause||
' and language = userenv(''LANG'')'||
' )'||
' connect by prior child_dim_grp_display_code = parent_dim_grp_display_code'||
' and prior child_cal_period_end_date = parent_cal_period_end_date'||
' and prior child_cal_period_number = parent_cal_period_number'||
' ) rel'||
' ,'||p_member_b_table||' bp'||
' ,'||p_member_b_table||' bc'||
' ,fem_dimension_grps_b dgp'||
' ,fem_dimension_grps_b dgc'||
' where dgp.dimension_group_display_code = rel.parent_dim_grp_display_code'||
' and dgp.dimension_id = :b_dimension_id'||
' and dgc.dimension_group_display_code = rel.child_dim_grp_display_code'||
' and dgc.dimension_id = dgp.dimension_id'||
' and bp.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(rel.parent_cal_period_end_date,rel.parent_cal_period_number,:b_calendar_id,dgp.dimension_group_id)'||
' and bc.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(rel.child_cal_period_end_date,rel.child_cal_period_number,:b_calendar_id,dgc.dimension_group_id)';
' select rel.row_id'||
' ,rel.depth_num'||
' ,bp.'||p_member_col||
' ,vp.value_set_id'||
l_parent_dim_grp_clause||
' ,rel.depth_num+1'||
' ,bc.'||p_member_col||
' ,vc.value_set_id'||
l_child_dim_grp_clause||
' ,rel.display_order_num'||
' ,rel.weighting_pct'||
' ,rel.status'||
' from ('||
' select row_id'||
' ,level as depth_num'||
' ,display_order_num'||
' ,weighting_pct'||
' ,status'||
' ,parent_display_code'||
' ,parent_value_set_display_code'||
' ,child_display_code'||
' ,child_value_set_display_code'||
' from ('||
' select rowid as row_id'||
' ,display_order_num'||
' ,weighting_pct'||
' ,status'||
' ,parent_display_code'||
' ,parent_value_set_display_code'||
' ,child_display_code'||
' ,child_value_set_display_code'||
' from '||p_source_hier_table||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and not ('||
' parent_display_code = child_display_code'||
' and parent_value_set_display_code = child_value_set_display_code'||
' )'||
l_status_clause||
' and language = userenv(''LANG'')'||
' )'||
' start with (parent_display_code, parent_value_set_display_code) in ('||
' select parent_display_code'||
' ,parent_value_set_display_code'||
' from '||p_source_hier_table||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and child_display_code = parent_display_code'||
' and child_value_set_display_code = parent_value_set_display_code'||
l_status_clause||
' and language = userenv(''LANG'')'||
' )'||
' connect by prior child_display_code = parent_display_code'||
' and prior child_value_set_display_code = parent_value_set_display_code'||
' ) rel'||
' ,'||p_member_b_table||' bp'||
' ,fem_value_sets_b vp'||
' ,'||p_member_b_table||' bc'||
' ,fem_value_sets_b vc'||
' where bp.'||p_member_dc_col||' = rel.parent_display_code'||
' and bp.value_set_id = vp.value_set_id'||
' and vp.value_set_display_code = rel.parent_value_set_display_code'||
' and vp.dimension_id = :b_dimension_id'||
' and bc.'||p_member_dc_col||' = rel.child_display_code'||
' and bc.value_set_id = vc.value_set_id'||
' and vc.value_set_display_code = rel.child_value_set_display_code'||
' and vc.dimension_id = vp.dimension_id';
' select rel.row_id'||
' ,rel.depth_num'||
' ,bp.'||p_member_col||
' ,-1'||
l_parent_dim_grp_clause||
' ,rel.depth_num+1'||
' ,bc.'||p_member_col||
' ,-1'||
l_child_dim_grp_clause||
' ,rel.display_order_num'||
' ,rel.weighting_pct'||
' ,rel.status'||
' from ('||
' select row_id'||
' ,level as depth_num'||
' ,display_order_num'||
' ,weighting_pct'||
' ,status'||
' ,parent_display_code'||
' ,child_display_code'||
' from ('||
' select rowid as row_id'||
' ,display_order_num'||
' ,weighting_pct'||
' ,status'||
' ,parent_display_code'||
' ,child_display_code'||
' from '||p_source_hier_table||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and parent_display_code <> child_display_code'||
l_status_clause||
' and language = userenv(''LANG'')'||
' )'||
' start with parent_display_code in ('||
' select parent_display_code'||
' from '||p_source_hier_table||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
' and child_display_code = parent_display_code'||
l_status_clause||
' and language = userenv(''LANG'')'||
' )'||
' connect by prior child_display_code = parent_display_code'||
' ) rel'||
' ,'||p_member_b_table||' bp'||
' ,'||p_member_b_table||' bc'||
' where bp.'||p_member_dc_col||' = rel.parent_display_code'||
' and bc.'||p_member_dc_col||' = rel.child_display_code';
PROCEDURE bld_insert_hier_rels_stmt (
p_dimension_varchar_label in varchar2
,p_value_set_required_flag in varchar2
,p_target_hier_table in varchar2
,p_target_hierval_table in varchar2
,x_insert_hier_rels_stmt out nocopy varchar2
)
IS
l_api_name constant varchar2(30) := 'bld_insert_hier_rels_stmt';
x_insert_hier_rels_stmt :=
' insert into '||p_target_hier_table||' ('||
' hierarchy_obj_def_id'||
' ,parent_depth_num'||
' ,parent_id'||
' ,child_depth_num'||
' ,child_id'||
' ,single_depth_flag'||
' ,display_order_num'||
' ,weighting_pct'||
' ,read_only_flag'||
' ,creation_date'||
' ,created_by'||
' ,last_updated_by'||
' ,last_update_date'||
' ,last_update_login'||
' ,object_version_number'||
' )'||
' select :b_hier_obj_def_id'||
' ,parent_depth_num'||
' ,parent_id'||
' ,child_depth_num'||
' ,child_id'||
' ,''Y'''||
' ,display_order_num'||
' ,weighting_pct'||
' ,''N'''||
' ,sysdate'||
' ,:b_user_id'||
' ,:b_user_id'||
' ,sysdate'||
' ,:b_login_id'||
' ,:b_object_version_number'||
' from '||p_target_hierval_table||
' where request_id = :b_request_id';
x_insert_hier_rels_stmt :=
' insert into '||p_target_hier_table||' ('||
' 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'||
' ,read_only_flag'||
' ,creation_date'||
' ,created_by'||
' ,last_updated_by'||
' ,last_update_date'||
' ,last_update_login'||
' ,object_version_number'||
' )'||
' select :b_hier_obj_def_id'||
' ,parent_depth_num'||
' ,parent_id'||
' ,parent_value_set_id'||
' ,child_depth_num'||
' ,child_id'||
' ,child_value_set_id'||
' ,''Y'''||
' ,display_order_num'||
' ,weighting_pct'||
' ,''N'''||
' ,sysdate'||
' ,:b_user_id'||
' ,:b_user_id'||
' ,sysdate'||
' ,:b_login_id'||
' ,:b_object_version_number'||
' from '||p_target_hierval_table||
' where request_id = :b_request_id';
x_insert_hier_rels_stmt :=
' insert into '||p_target_hier_table||' ('||
' hierarchy_obj_def_id'||
' ,parent_depth_num'||
' ,parent_id'||
' ,child_depth_num'||
' ,child_id'||
' ,single_depth_flag'||
' ,display_order_num'||
' ,weighting_pct'||
' ,read_only_flag'||
' ,creation_date'||
' ,created_by'||
' ,last_updated_by'||
' ,last_update_date'||
' ,last_update_login'||
' ,object_version_number'||
' )'||
' select :b_hier_obj_def_id'||
' ,parent_depth_num'||
' ,parent_id'||
' ,child_depth_num'||
' ,child_id'||
' ,''Y'''||
' ,display_order_num'||
' ,weighting_pct'||
' ,''N'''||
' ,sysdate'||
' ,:b_user_id'||
' ,:b_user_id'||
' ,sysdate'||
' ,:b_login_id'||
' ,:b_object_version_number'||
' from '||p_target_hierval_table||
' where request_id = :b_request_id';
END bld_insert_hier_rels_stmt;
PROCEDURE bld_delete_hier_rels_stmt (
p_dimension_varchar_label in varchar2
,p_execution_mode in varchar2
,p_source_hier_table in varchar2 := null
,p_target_hier_table in varchar2 := null
,p_target_hierval_table in varchar2 := null
,x_delete_hier_rels_stmt out nocopy varchar2
)
IS
l_api_name constant varchar2(30) := 'bld_delete_hier_rels_stmt';
x_delete_hier_rels_stmt := null;
x_delete_hier_rels_stmt :=
' delete from '||p_source_hier_table||
' where hierarchy_object_name = :b_hierarchy_object_name'||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
l_status_clause||
' and language = userenv(''LANG'')';
x_delete_hier_rels_stmt :=
' delete from '||p_target_hier_table||
' where hierarchy_obj_def_id = :b_hier_obj_def_id';
x_delete_hier_rels_stmt :=
' delete from '||p_target_hierval_table||
' where request_id = :b_request_id';
END bld_delete_hier_rels_stmt;
PROCEDURE bld_insert_hierval_rels_stmt (
p_dimension_varchar_label in varchar2
,p_target_hierval_table in varchar2
,x_insert_hierval_rels_stmt out nocopy varchar2
)
IS
l_api_name constant varchar2(30) := 'bld_insert_hierval_rels_stmt';
x_insert_hierval_rels_stmt :=
' insert into '||p_target_hierval_table||' ('||
' request_id'||
' ,source_hier_t_rowid'||
' ,parent_depth_num'||
' ,parent_id'||
' ,parent_dimension_grp_id'||
' ,child_depth_num'||
' ,child_id'||
' ,child_dimension_grp_id'||
' ,display_order_num'||
' ,weighting_pct'||
' ) values ('||
' :request_id'||
' ,:b_rowid'||
' ,:b_parent_depth_num'||
' ,:b_parent_id'||
' ,:b_parent_dimension_grp_id'||
' ,:b_child_depth_num'||
' ,:b_child_id'||
' ,:b_child_dimension_grp_id'||
' ,:b_display_order_num'||
' ,:b_wt_pct'||
' )';
x_insert_hierval_rels_stmt :=
' insert into '||p_target_hierval_table||' ('||
' request_id'||
' ,source_hier_t_rowid'||
' ,parent_depth_num'||
' ,parent_id'||
' ,parent_value_set_id'||
' ,parent_dimension_grp_id'||
' ,child_depth_num'||
' ,child_id'||
' ,child_value_set_id'||
' ,child_dimension_grp_id'||
' ,display_order_num'||
' ,weighting_pct'||
' ) values ('||
' :request_id'||
' ,:b_rowid'||
' ,:b_parent_depth_num'||
' ,:b_parent_id'||
' ,:b_parent_value_set_id'||
' ,:b_parent_dimension_grp_id'||
' ,:b_child_depth_num'||
' ,:b_child_id'||
' ,:b_child_value_set_id'||
' ,:b_child_dimension_grp_id'||
' ,:b_display_order_num'||
' ,:b_wt_pct'||
' )';
END bld_insert_hierval_rels_stmt;
FUNCTION bld_update_status_stmt (
p_dimension_varchar_label in varchar2
,p_execution_mode in varchar2
,p_value_set_required_flag in varchar2 := null
,p_source_hier_table in varchar2
,p_rowid_flag in varchar2 := null
,p_hier_object_name_flag in varchar2 := null
,p_hier_obj_def_name_flag in varchar2 := null
,p_parent_flag in varchar2 := null
,p_child_flag in varchar2 := null
)
RETURN varchar2
IS
l_api_name constant varchar2(30) := 'bld_update_status_stmt';
l_update_status_stmt varchar2(4000);
l_update_status_stmt :=
' update '||p_source_hier_table||
' set status = :b_status'||
' where language = userenv(''LANG'')'||
l_status_clause;
l_update_status_stmt := l_update_status_stmt ||
' and rowid = :b_rowid';
l_update_status_stmt := l_update_status_stmt ||
' and hierarchy_object_name = :b_hierarchy_object_name';
l_update_status_stmt := l_update_status_stmt ||
' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name';
l_update_status_stmt := l_update_status_stmt ||
' and calendar_display_code = :b_calendar_display_code'||
' and parent_dim_grp_display_code = :b_parent_dim_grp_dc'||
' and parent_cal_period_end_date = :b_parent_cal_period_end_date'||
' and parent_cal_period_number = :b_parent_cal_period_number'||
' and not ('||
' parent_dim_grp_display_code = child_dim_grp_display_code'||
' and parent_cal_period_end_date = child_cal_period_end_date'||
' and parent_cal_period_number = child_cal_period_number'||
' )';
l_update_status_stmt := l_update_status_stmt ||
' and calendar_display_code = :b_calendar_display_code'||
' and child_dim_grp_display_code = :b_child_dim_grp_dc'||
' and child_cal_period_end_date = :b_child_cal_period_end_date'||
' and child_cal_period_number = :b_child_cal_period_number'||
' and not ('||
' parent_dim_grp_display_code = child_dim_grp_display_code'||
' and parent_cal_period_end_date = child_cal_period_end_date'||
' and parent_cal_period_number = child_cal_period_number'||
' )';
l_update_status_stmt := l_update_status_stmt ||
' and parent_display_code = :b_parent_dc'||
' and parent_value_set_display_code = :b_parent_value_set_dc'||
' and not ('||
' parent_display_code = child_display_code'||
' and parent_value_set_display_code = child_value_set_display_code'||
' )';
l_update_status_stmt := l_update_status_stmt ||
' and child_display_code = :b_child_dc'||
' and child_value_set_display_code = :b_child_value_set_dc'||
' and not ('||
' parent_display_code = child_display_code'||
' and parent_value_set_display_code = child_value_set_display_code'||
' )';
l_update_status_stmt := l_update_status_stmt ||
' and parent_display_code = :b_parent_dc'||
' and parent_display_code <> child_display_code';
l_update_status_stmt := l_update_status_stmt ||
' and child_display_code = :b_child_dc'||
' and parent_display_code <> child_display_code';
return l_update_status_stmt;
END bld_update_status_stmt;