[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rs.child_obj_id
,rs.child_obj_def_id
,x.exec_status_code
from fem_ruleset_process_data rs
,fem_pl_object_executions x
where rs.request_id = p_request_id
and rs.rule_set_obj_id = p_ruleset_obj_id
and x.request_id (+) = rs.request_id
and x.object_id (+) = rs.child_obj_id
and x.exec_object_definition_id (+) = rs.child_obj_def_id
order by rs.engine_execution_sequence;
g_xledger_tbl.DELETE;
select object_type_code
,local_vs_combo_id
into x_request_rec.submit_obj_type_code
,x_request_rec.local_vs_combo_id
from fem_object_catalog_b
where object_id = x_request_rec.submit_obj_id;
select object_name
into x_request_rec.ruleset_obj_name
from fem_object_catalog_vl
where object_id = x_request_rec.ruleset_obj_id;
select rule_set_object_type_code
into x_request_rec.rollup_obj_type_code
from fem_rule_sets
where rule_set_obj_def_id = x_request_rec.ruleset_obj_def_id;
' select cost_object_hier_obj_id'||
' ,condition_obj_id'||
' ,currency_code'||
' ,null'||
' from pft_couc_rollup_rules'||
' where couc_rollup_obj_def_id = :b_rollup_obj_def_id';
' select activity_hier_obj_id'||
' ,condition_obj_id'||
' ,currency_code'||
' ,null'||
' from pft_activity_cost_ru'||
' where cost_rollup_obj_def_id = :b_rollup_obj_def_id';
' select activity_hier_obj_id'||
' ,condition_obj_id'||
' ,''STAT'''||
' ,statistic_basis_id'||
' from pft_activity_stat_ru'||
' where stat_rollup_obj_def_id = :b_rollup_obj_def_id';
select 'Y'
into l_dummy_varchar
from fem_table_class_assignmt_v
where table_classification_code = 'ABM_LEDGER'
and table_name = 'FEM_BALANCES';
select source_system_code
into x_request_rec.source_system_code
from fem_source_systems_b
where source_system_display_code = G_PFT_SOURCE_SYSTEM_DC;
select object_id
into x_request_rec.dataset_grp_obj_id
from fem_object_definition_b
where object_definition_id = x_request_rec.dataset_grp_obj_def_id;
select output_dataset_code
into x_request_rec.output_dataset_code
from fem_ds_input_output_defs
where dataset_io_obj_def_id = x_request_rec.dataset_grp_obj_def_id;
select obj.object_name
,f.folder_name
into l_object_name
,l_folder_name
from fem_object_catalog_vl obj
,fem_folders_vl f
where obj.object_id = x_request_rec.dataset_grp_obj_id
and f.folder_id = obj.folder_id;
select d.object_definition_id
into x_obj_def_id
from fem_object_definition_b d
,fem_object_catalog_b o
where o.object_id = p_object_id
and o.object_type_code = p_object_type_code
and d.object_id = o.object_id
and p_effective_date between d.effective_start_date and d.effective_end_date
and d.old_approved_copy_flag = 'N';
select object_name
,object_type_code
into l_object_name
,l_object_type_code
from fem_object_catalog_vl
where object_id = p_object_id;
select dimension_id
,dimension_varchar_label
,composite_dimension_flag
,member_col
,member_b_table_name
,attribute_table_name as attr_table
,hierarchy_table_name as hier_table
,null as hier_rollup_table
,hier_versioning_type_code
into x_dimension_rec
from fem_xdim_dimensions_vl
where dimension_varchar_label = p_dimension_varchar_label;
' select dim_attribute_varchar_member'||
' ,date_assign_value'||
' from '||l_dimension_rec.attr_table||
' where attribute_id = :b_attribute_id'||
' and version_id = :b_attr_version_id'||
' and '||l_dimension_rec.member_col||' = :b_member_id'
into x_dim_attribute_varchar_member
,x_date_assign_value
using l_attribute_id
,l_attr_version_id
,p_member_id;
select dim_attribute_varchar_member
into x_currency_code
from fem_ledgers_attr
where attribute_id = g_ledger_curr_attr_id
and version_id = g_ledger_curr_attr_version_id
and ledger_id = p_ledger_id;
select att.attribute_id
,ver.version_id
into x_attribute_id
,x_attr_version_id
from fem_dim_attributes_b att
,fem_dim_attr_versions_b ver
where att.dimension_id = x_dimension_rec.dimension_id
and att.attribute_varchar_label = p_attribute_varchar_label
and ver.attribute_id = att.attribute_id
and ver.default_version_flag = 'Y';
l_comp_dim_comp_cols_insert long;
l_comp_dim_data_cols_insert long;
' select reqs.column_name'||
' from fem_column_requiremnt_b reqs'||
' ,fem_tab_columns_v cols'||
' where reqs.'||l_comp_dim_req_col||'_dim_requirement_code is not null'||
' and reqs.'||l_comp_dim_req_col||'_dim_component_flag = ''Y'''||
' and reqs.dimension_id is not null'||
' and cols.table_name = ''FEM_BALANCES'''||
' and cols.column_name = reqs.column_name'||
' and cols.dimension_id = reqs.dimension_id';
l_comp_dim_comp_cols_insert := l_comp_dim_comp_cols_insert ||
' ,bp.'||l_column_name;
' select reqs.column_name'||
' ,decode(cols.column_name,props.column_name,''Y'',''N'') as proc_key_flag'||
' from fem_column_requiremnt_b reqs'||
' ,fem_tab_columns_v cols'||
' ,fem_tab_column_prop props'||
' where reqs.'||l_comp_dim_req_col||'_dim_component_flag = ''N'''||
' and reqs.dimension_id is not null'||
' and cols.table_name = ''FEM_BALANCES'''||
' and cols.column_name = reqs.column_name'||
' and cols.dimension_id is not null'||
' and cols.fem_data_type_code = ''DIMENSION'''||
' and props.table_name (+) = cols.table_name'||
' and props.column_name (+) = cols.column_name'||
' and props.column_property_code (+) = ''PROCESSING_KEY'''||
' and ('||
' reqs.'||l_comp_dim_req_col||'_dim_requirement_code is not null'||
' or ('||
' reqs.'||l_comp_dim_req_col||'_dim_requirement_code is null'||
' and cols.column_name not in ('||
' ''ACTIVITY_ID'''||
' ,''COST_OBJECT_ID'''||
' ,''CREATED_BY_OBJECT_ID'''||
' ,''LAST_UPDATED_BY_OBJECT_ID'''||
' ,''CURRENCY_TYPE_CODE'''||
' ,''CURRENCY_CODE'''||
' ,''DATASET_CODE'''||
' ,''CAL_PERIOD_ID'''||
' ,''LEDGER_ID'''||
' ,''SOURCE_SYSTEM_CODE'''||
' )'||
' )'||
' )';
l_comp_dim_data_cols_insert := l_comp_dim_data_cols_insert ||
' ,bp.'||l_column_name;
x_sql_rec.comp_dim_comp_cols_insert := l_comp_dim_comp_cols_insert;
x_sql_rec.comp_dim_data_cols_insert := l_comp_dim_data_cols_insert;
,p_last_update_login => p_request_rec.login_id
,p_program_id => p_request_rec.pgm_id
,p_program_login_id => p_request_rec.login_id
,p_program_application_id => p_request_rec.pgm_app_id
,p_exec_mode_code => null
,p_dimension_id => null
,p_table_name => null
,p_hierarchy_name => null
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
select node_id
from fem_ru_nodes_t
where created_by_request_id = p_request_id
and created_by_object_id = p_object_id
and root_flag = 'Y'
and costed_flag = 'N';
select node_id
from fem_ru_nodes_t
where created_by_request_id = p_request_id
and created_by_object_id = p_object_id
and condition_flag = 'Y'
and costed_flag = 'N';
l_top_node_id_tbl.DELETE;
l_top_node_id_tbl.DELETE;
select count(*)
into l_uncosted_node_count
from fem_ru_nodes_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = l_rule_rec.rollup_obj_id
and costed_flag = 'N';
select object_type_code
,object_name
,local_vs_combo_id
into x_rule_rec.rollup_obj_type_code
,x_rule_rec.rollup_obj_name
,x_rule_rec.local_vs_combo_id
from fem_object_catalog_vl
where object_id = x_rule_rec.rollup_obj_id;
select h.dimension_id
into l_dimension_id
from fem_hierarchies h
where h.hierarchy_obj_id = x_rule_rec.hier_obj_id;
' select h.child_id'||
' ,sum(nvl(q.child_qty/decode(q.parent_qty,0,null,q.parent_qty)/decode(q.yield_percentage,0,null,q.yield_percentage),0.00)) as weighting_pct'||
' ,h.child_ledger_id'||
' from '||p_rule_rec.hier_rollup_table||' h'||
' ,fem_cost_obj_hier_qty q'||
' where h.created_by_request_id = :b_request_id'||
' and h.created_by_object_id = :b_rollup_obj_id'||
' and h.parent_id = :b_parent_id'||
' and h.parent_depth_num = :b_parent_depth_num'||
' and q.relationship_id = h.relationship_id'||
' and '||p_input_ds_q_where_clause||
' group by h.child_ledger_id'||
' ,h.child_id';
' select :b_source_system_code as source_system_code'||--new
' ,:b_currency_code as currency_code'||--new
' ,b.currency_type_code'||
' ,parent.cost_object_id'||
p_sql_rec.comp_dim_comp_cols_using||
p_sql_rec.comp_dim_data_cols_using||
' ,sum(b.xtd_balance_f) xtd_balance_f'||
' from fem_balances b'||
' ,fem_cost_objects parent'||
' where b.cost_object_id = :b_child_id'||
' and parent.cost_object_id = :b_parent_id'||
' and b.currency_type_code = ''ENTERED'''||
' and '||p_input_ds_b_where_clause||
' group by b.currency_type_code'||
' ,parent.cost_object_id'||
p_sql_rec.comp_dim_comp_cols_using||
p_sql_rec.comp_dim_data_cols_using||
' ) bc'||
' on ('||
' bp.source_system_code = bc.source_system_code'||
' and bp.currency_code = bc.currency_code'||
' and bp.currency_type_code = bc.currency_type_code'||
' and bp.cost_object_id = bc.cost_object_id'||
p_sql_rec.comp_dim_data_cols_on||
' and bp.dataset_code = :b_output_dataset_code'||
' and bp.cal_period_id = :b_output_cal_period_id'||
' and bp.created_by_request_id = :b_request_id'||
' and bp.created_by_object_id = :b_rollup_obj_id'||
' )'||
' when matched then'||
' update set'||
' bp.xtd_balance_e = bp.xtd_balance_e + ( round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num / :b_entered_exch_rate_den * :b_entered_exch_rate_num)'||
' ,bp.xtd_balance_f = bp.xtd_balance_f + ( round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num )'||
' ,bp.last_updated_by_request_id = :b_request_id'||
' ,bp.last_updated_by_object_id = :b_rollup_obj_id'||
' when not matched then'||
' insert ('||
' bp.dataset_code'||
' ,bp.cal_period_id'||
' ,bp.creation_row_sequence'||
' ,bp.source_system_code'||
' ,bp.currency_code'||
' ,bp.currency_type_code'||
' ,bp.cost_object_id'||
p_sql_rec.comp_dim_comp_cols_insert||
p_sql_rec.comp_dim_data_cols_insert||
' ,bp.created_by_request_id'||
' ,bp.created_by_object_id'||
' ,bp.last_updated_by_request_id'||
' ,bp.last_updated_by_object_id'||
' ,bp.xtd_balance_e'||
' ,bp.xtd_balance_f'||
' )'||
' values'||
' ('||
' :b_output_dataset_code'||
' ,:b_output_cal_period_id'||
' ,'||p_rule_rec.sequence_name||'.NEXTVAL'||
' ,bc.source_system_code'||
' ,bc.currency_code'||
' ,bc.currency_type_code'||
' ,bc.cost_object_id'||
p_sql_rec.comp_dim_comp_cols_values||
p_sql_rec.comp_dim_data_cols_values||
' ,:b_request_id'||
' ,:b_rollup_obj_id'||
' ,:b_request_id'||
' ,:b_rollup_obj_id'||
' ,round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num / :b_entered_exch_rate_den * :b_entered_exch_rate_num'||
' ,round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num'||
' )';
' select distinct created_by_request_id'||
' ,created_by_object_id'||
' from fem_balances b'||
' where b.currency_type_code = ''ENTERED'''||
' and '||p_input_ds_b_where_clause||
' and b.cost_object_id = :b_child_id'||
' and not ('||
' b.created_by_request_id = :b_request_id'||
' and b.created_by_object_id = :b_rollup_obj_id'||
' )'||
' and not exists ('||
' select 1'||
' from fem_pl_chains c'||
' where c.request_id = :b_request_id'||
' and c.object_id = :b_rollup_obj_id'||
' and c.source_created_by_request_id = b.created_by_request_id'||
' and c.source_created_by_object_id = b.created_by_object_id'||
' )';
' select count(*)'||
' from fem_balances b'||
' where b.currency_type_code = ''ENTERED'''||
' and '||p_input_ds_b_where_clause||
' and not ('||
' created_by_request_id = :b_request_id'||
' and created_by_object_id = :b_rollup_obj_id'||
' )'||
' and exists ('||
' select 1'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.node_id = b.cost_object_id'||
' and n.costed_flag = ''Y'''||
' and n.root_flag = ''N'''||
' and n.condition_flag = ''N'''||
' )';
' select h.child_id'||
' ,nvl(h.weighting_pct,1.00) as weighting_pct'||
' ,null as child_ledger_id'||
' from '||p_rule_rec.hier_rollup_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.child_id <> h.parent_id'||
' and h.single_depth_flag = ''Y'''||
' and h.parent_id = :b_parent_id'||
' and h.parent_depth_num = :b_parent_depth_num';
' select :b_source_system_code as source_system_code'||--new
' ,:b_currency_code as currency_code'||--new
' ,b.currency_type_code'||
' ,b.ledger_id'||
' ,parent.activity_id'||
p_sql_rec.comp_dim_comp_cols_using||
p_sql_rec.comp_dim_data_cols_using||
' ,sum(b.xtd_balance_f) xtd_balance_f'||
' from fem_balances b'||
' ,fem_activities parent'||
' where b.activity_id = :b_child_id'||
' and parent.activity_id = :b_parent_id'||
' and b.ledger_id = :b_ledger_id'||
' and b.currency_type_code = ''ENTERED'''||
' and '||l_financial_elem_id_clause||
' and '||l_line_item_id_clause||
' and '||p_input_ds_b_where_clause||
' group by b.currency_type_code'||
' ,b.ledger_id'||
' ,parent.activity_id'||
p_sql_rec.comp_dim_comp_cols_using||
p_sql_rec.comp_dim_data_cols_using||
' ) bc'||
' on ('||
' bp.source_system_code = bc.source_system_code'||
' and bp.currency_code = bc.currency_code'||
' and bp.currency_type_code = bc.currency_type_code'||
' and bp.ledger_id = bc.ledger_id'||
' and bp.activity_id = bc.activity_id'||
p_sql_rec.comp_dim_data_cols_on||
' and bp.dataset_code = :b_output_dataset_code'||
' and bp.cal_period_id = :b_output_cal_period_id'||
' and bp.created_by_request_id = :b_request_id'||
' and bp.created_by_object_id = :b_rollup_obj_id'||
' )'||
' when matched then'||
' update set'||
' bp.xtd_balance_e = bp.xtd_balance_e + ( bc.xtd_balance_f * :b_weighting_pct / :b_entered_exch_rate_den * :b_entered_exch_rate_num)'||
' ,bp.xtd_balance_f = bp.xtd_balance_f + ( bc.xtd_balance_f * :b_weighting_pct)'||
' ,bp.last_updated_by_request_id = :b_request_id'||
' ,bp.last_updated_by_object_id = :b_rollup_obj_id'||
' when not matched then'||
' insert ('||
' bp.dataset_code'||
' ,bp.cal_period_id'||
' ,bp.creation_row_sequence'||
' ,bp.source_system_code'||
' ,bp.currency_code'||
' ,bp.currency_type_code'||
' ,bp.ledger_id'||
' ,bp.activity_id'||
p_sql_rec.comp_dim_comp_cols_insert||
p_sql_rec.comp_dim_data_cols_insert||
' ,bp.created_by_request_id'||
' ,bp.created_by_object_id'||
' ,bp.last_updated_by_request_id'||
' ,bp.last_updated_by_object_id'||
' ,bp.xtd_balance_e'||
' ,bp.xtd_balance_f'||
' )'||
' values'||
' ('||
' :b_output_dataset_code'||
' ,:b_output_cal_period_id'||
' ,'||p_rule_rec.sequence_name||'.NEXTVAL'||
' ,bc.source_system_code'||
' ,bc.currency_code'||
' ,bc.currency_type_code'||
' ,bc.ledger_id'||
' ,bc.activity_id'||
p_sql_rec.comp_dim_comp_cols_values||
p_sql_rec.comp_dim_data_cols_values||
' ,:b_request_id'||
' ,:b_rollup_obj_id'||
' ,:b_request_id'||
' ,:b_rollup_obj_id'||
' ,bc.xtd_balance_f * :b_weighting_pct / :b_entered_exch_rate_den * :b_entered_exch_rate_num'||
' ,bc.xtd_balance_f * :b_weighting_pct'||
' )';
' select distinct created_by_request_id'||
' ,created_by_object_id'||
' from fem_balances b'||
' where b.ledger_id = :b_ledger_id'||
' and b.currency_type_code = ''ENTERED'''||
' and '||l_financial_elem_id_clause||
' and '||l_line_item_id_clause||
' and '||p_input_ds_b_where_clause||
' and b.activity_id = :b_child_id'||
' and not ('||
' b.created_by_request_id = :b_request_id'||
' and b.created_by_object_id = :b_rollup_obj_id'||
' )'||
' and not exists ('||
' select 1'||
' from fem_pl_chains c'||
' where c.request_id = :b_request_id'||
' and c.object_id = :b_rollup_obj_id'||
' and c.source_created_by_request_id = b.created_by_request_id'||
' and c.source_created_by_object_id = b.created_by_object_id'||
' )';
' select count(*)'||
' from fem_balances b'||
' where b.ledger_id = :b_ledger_id'||
' and b.currency_type_code = ''ENTERED'''||
' and '||l_financial_elem_id_clause||
' and '||l_line_item_id_clause||
' and '||p_input_ds_b_where_clause||
' and not ('||
' created_by_request_id = :b_request_id'||
' and created_by_object_id = :b_rollup_obj_id'||
' )'||
' and exists ('||
' select 1'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.node_id = b.activity_id'||
' and n.costed_flag = ''Y'''||
' and n.root_flag = ''N'''||
' and n.condition_flag = ''N'''||
' )';
,p_last_update_login => p_request_rec.login_id
,p_exec_mode_code => null
,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_request_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_statement_type => 'INSERT'
);
,p_statement_type => 'INSERT'
);
,p_statement_type => 'INSERT'
);
,p_statement_type => 'INSERT'
);
,p_last_update_login => p_request_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_last_update_login => p_request_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_last_update_login => p_request_rec.login_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
select 'Y'
into l_object_exists_flag
from fem_pl_temp_objects
where request_id = p_request_rec.request_id
and object_id = p_rule_rec.rollup_obj_id
and object_type = 'SEQUENCE'
and object_name = p_rule_rec.sequence_name;
delete from fem_pl_temp_objects
where request_id = p_request_rec.request_id
and object_id = p_rule_rec.rollup_obj_id
and object_type = 'SEQUENCE'
and object_name = p_rule_rec.sequence_name;
' insert into fem_ru_nodes_t ('||
' created_by_request_id'||
' ,created_by_object_id'||
' ,node_id'||
' ,costed_flag'||
' ,root_flag'||
' ,condition_flag'||
' )'||
' select :b_request_id'||
' ,:b_rollup_obj_id'||
' ,m.'||l_dimension_rec.member_col||
' ,''N'''||
' ,''N'''||
' ,''Y'''||
' from '||l_dimension_rec.member_b_table||' m'||
' where m.local_vs_combo_id = :b_local_vs_combo_id'||
-- ' and {{data_slice}}'||
' and '||l_cond_where_clause||
' and not exists ('||
' select 1'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.node_id = m.'||l_dimension_rec.member_col||
' )';
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' where h.hierarchy_obj_id = :b_hier_obj_id'||
' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
' and h.parent_id = m.'||l_dimension_rec.member_col||
' )';
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.single_depth_flag = ''Y'''||
' and h.parent_id = m.'||l_dimension_rec.member_col||
' )';
select count(*)
into l_node_count
from fem_ru_nodes_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id;
' select count(*)'||
' from '||l_dimension_rec.member_b_table||' m'||
' where m.local_vs_combo_id = :b_local_vs_combo_id'||
' and '||l_cond_where_clause;
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' where h.hierarchy_obj_id = :b_hier_obj_id'||
' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
' and h.child_id = m.'||l_dimension_rec.member_col||
' )';
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.single_depth_flag = ''Y'''||
' and h.child_id = m.'||l_dimension_rec.member_col||
' )';
' update fem_ru_nodes_t n'||
' set root_flag = ''Y'''||
' where created_by_request_id = :b_request_id'||
' and created_by_object_id = :b_rollup_obj_id';
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' ,'||l_dimension_rec.member_b_table||' parent'||
' where h.hierarchy_obj_id = :b_hier_obj_id'||
' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
' and h.child_id = n.node_id'||
' and parent.'||l_dimension_rec.member_col||' = h.parent_id'||
' and parent.ledger_id = :b_ledger_id'|| --must restrict by ledger for x-ledger
' )';
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.child_id = h.parent_id'||
' and h.single_depth_flag = ''Y'''||
' and h.parent_id = n.node_id'||
' )';
' insert into fem_ru_nodes_t ('||
' created_by_request_id'||
' ,created_by_object_id'||
' ,node_id'||
' ,costed_flag'||
' ,root_flag'||
' ,condition_flag'||
' )'||
' select :b_request_id'||
' ,:b_rollup_obj_id'||
' ,m.'||l_dimension_rec.member_col||
' ,''N'''||
' ,''Y'''||
' ,''N'''||
' from '||l_dimension_rec.member_b_table||' m'||
' where m.local_vs_combo_id = :b_local_vs_combo_id'||
-- ' and {{data_slice}}'||
' and not exists ('||
' select 1'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.node_id = m.'||l_dimension_rec.member_col||
' )';
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' where h.hierarchy_obj_id = :b_hier_obj_id'||
' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
' and h.parent_id = m.'||l_dimension_rec.member_col||
' )'||
' and not exists ('||
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' ,'||l_dimension_rec.member_b_table||' parent'||
' where h.hierarchy_obj_id = :b_hier_obj_id'||
' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
' and h.child_id = m.'||l_dimension_rec.member_col||
' and parent.'||l_dimension_rec.member_col||' = h.parent_id'||
' and parent.ledger_id = m.ledger_id'|| --must restrict by ledger for x-ledger
' )';
' select 1'||
' from '||l_dimension_rec.hier_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.child_id = h.parent_id'||
' and h.single_depth_flag = ''Y'''||
' and h.parent_id = m.'||l_dimension_rec.member_col||
' )';
select count(*)
into l_node_count
from fem_ru_nodes_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id
and root_flag = 'Y';
select costed_flag
into l_costed_flag
from fem_ru_nodes_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id
and node_id = p_top_node_id;
insert into fem_ru_cost_obj_hier_t (
created_by_request_id
,created_by_object_id
,relationship_id
,parent_id
,parent_depth_num
,child_id
,child_sequence_num
,child_depth_num
,child_ledger_id
)
select ru.created_by_request_id
,ru.created_by_object_id
,ru.relationship_id
,ru.parent_id
,ru.parent_depth_num
,ru.child_id
,ru.child_sequence_num
,ru.child_depth_num
,decode(child.ledger_id,p_request_rec.ledger_id,null,child.ledger_id)
from (
select created_by_request_id
,created_by_object_id
,relationship_id
,parent_id
,level as parent_depth_num
,child_id
,child_sequence_num
,(level + 1) as child_depth_num
from (
select nvl(n.created_by_request_id,p_request_rec.request_id) as created_by_request_id
,nvl(n.created_by_object_id,p_rule_rec.rollup_obj_id) as created_by_object_id
,h.relationship_id
,h.parent_id
,h.child_id
,h.child_sequence_num
,nvl(n.costed_flag,'N') as costed_flag
from fem_cost_objects_hier h
,fem_cost_objects parent
,fem_ru_nodes_t n
where h.hierarchy_obj_id = p_rule_rec.hier_obj_id
and p_request_rec.effective_date between h.effective_start_date and h.effective_end_date
and parent.cost_object_id = h.parent_id
and parent.ledger_id = p_request_rec.ledger_id
and n.created_by_request_id (+) = p_request_rec.request_id
and n.created_by_object_id (+) = p_rule_rec.rollup_obj_id
and n.node_id (+) = h.parent_id
)
start with parent_id = p_top_node_id
connect by prior child_id = parent_id
and prior costed_flag = 'N'
) ru
,fem_cost_objects child
where child.cost_object_id = ru.child_id;
insert into fem_ru_activities_hier_t (
created_by_request_id
,created_by_object_id
,hierarchy_obj_def_id
,parent_id
,parent_depth_num
,child_id
,child_depth_num
,single_depth_flag
,weighting_pct
)
select created_by_request_id
,created_by_object_id
,hierarchy_obj_def_id
,parent_id
,level
,child_id
,level + 1
,single_depth_flag
,weighting_pct
from (
select nvl(n.created_by_request_id,p_request_rec.request_id) as created_by_request_id
,nvl(n.created_by_object_id,p_rule_rec.rollup_obj_id) as created_by_object_id
,h.hierarchy_obj_def_id
,h.parent_id
,h.child_id
,h.single_depth_flag
,h.weighting_pct
,nvl(n.costed_flag,'N') as costed_flag
from fem_activities_hier h
,fem_ru_nodes_t n
where h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
and h.child_id <> h.parent_id
and h.single_depth_flag = 'Y'
and n.created_by_request_id (+) = p_request_rec.request_id
and n.created_by_object_id (+) = p_rule_rec.rollup_obj_id
and n.node_id (+) = h.parent_id
)
start with parent_id = p_top_node_id
connect by prior child_id = parent_id
and prior costed_flag = 'N';
' insert into fem_ru_nodes_t ('||
' created_by_request_id'||
' ,created_by_object_id'||
' ,node_id'||
' ,costed_flag'||
' ,root_flag'||
' ,condition_flag'||
' )';
' select distinct h.created_by_request_id'||
' ,h.created_by_object_id'||
' ,h.child_id'||
' ,''N'''||
' ,''N'''||
' ,''N'''||
' from '||p_rule_rec.hier_rollup_table||' h'||
' where h.created_by_request_id = :b_request_id'||
' and h.created_by_object_id = :b_rollup_obj_id'||
' and not exists ('||
' select 1'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = h.created_by_request_id'||
' and n.created_by_object_id = h.created_by_object_id'||
' and n.node_id = h.child_id'||
' )';
' select h.created_by_request_id'||
' ,h.created_by_object_id'||
' ,h.child_id'||
' ,''N'''||
' ,''N'''||
' ,''N'''||
' from '||p_rule_rec.hier_rollup_table||' h'||
' where h.created_by_request_id = :b_request_id'||
' and h.created_by_object_id = :b_rollup_obj_id'||
' and h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.child_id <> h.parent_id'||
' and h.single_depth_flag = ''Y'''||
' and not exists ('||
' select 1'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = h.created_by_request_id'||
' and n.created_by_object_id = h.created_by_object_id'||
' and n.node_id = h.child_id'||
' )';
' select :b_request_id'||
' ,:b_rollup_obj_id'||
' ,h.child_id'||
' ,''N'''||
' ,''N'''||
' ,''N'''||
' from '||p_rule_rec.hier_rollup_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.child_id <> h.parent_id'||
' and h.single_depth_flag = ''Y'''||
' and not exists ('||
' select 1'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.node_id = h.child_id'||
' )';
' select co.cost_object_id'||
' from fem_cost_objects co'||
' ,fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.costed_flag = ''N'''||
' and n.node_id = co.cost_object_id';
' select act.activity_id'||
' from fem_activities act'||
' ,fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.costed_flag = ''N'''||
' and n.node_id = act.activity_id';
' select act.activity_id'||
' from fem_activities act'||
' ,'||p_rule_rec.hier_rollup_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.child_id = act.activity_id'||
' and h.single_depth_flag = ''Y''';
select min(h.parent_depth_num)
into l_min_parent_depth_num
from fem_ru_cost_obj_hier_t h
where h.created_by_request_id = p_request_rec.request_id
and h.created_by_object_id = p_rule_rec.rollup_obj_id
and h.parent_id = p_top_node_id;
select max(h.parent_depth_num)
into l_max_parent_depth_num
from fem_ru_cost_obj_hier_t h
where h.created_by_request_id = p_request_rec.request_id
and h.created_by_object_id = p_rule_rec.rollup_obj_id;
select min(h.parent_depth_num)
into l_min_parent_depth_num
from fem_ru_activities_hier_t h
where h.created_by_request_id = p_request_rec.request_id
and h.created_by_object_id = p_rule_rec.rollup_obj_id
and h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
and h.child_id <> h.parent_id
and h.single_depth_flag = 'Y'
and h.parent_id = p_top_node_id;
select max(h.parent_depth_num)
into l_max_parent_depth_num
from fem_ru_activities_hier_t h
where h.created_by_request_id = p_request_rec.request_id
and h.created_by_object_id = p_rule_rec.rollup_obj_id
and h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
and h.child_id <> h.parent_id
and h.single_depth_flag = 'Y';
select min(h.parent_depth_num)
into l_min_parent_depth_num
from fem_activities_hier h
where h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
and h.child_id <> h.parent_id
and h.single_depth_flag = 'Y'
and h.parent_id = p_top_node_id;
select max(h.parent_depth_num)
into l_max_parent_depth_num
from fem_activities_hier h
where h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
and h.child_id <> h.parent_id
and h.single_depth_flag = 'Y';
' select n.node_id'||
' from fem_ru_nodes_t n'||
' where n.created_by_request_id = :b_request_id'||
' and n.created_by_object_id = :b_rollup_obj_id'||
' and n.costed_flag = ''N''';
' select 1'||
' from '||p_rule_rec.hier_rollup_table||' h'||
' where h.created_by_request_id = n.created_by_request_id'||
' and h.created_by_object_id = n.created_by_object_id'||
' and h.parent_id = n.node_id'||
' and h.parent_depth_num = :b_parent_depth_num'||
' )';
' select 1'||
' from '||p_rule_rec.hier_rollup_table||' h'||
' where h.created_by_request_id = n.created_by_request_id'||
' and h.created_by_object_id = n.created_by_object_id'||
' and h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.single_depth_flag = ''Y'''||
' and h.parent_id = n.node_id'||
' and h.parent_depth_num = :b_parent_depth_num'||
' )';
' select 1'||
' from '||p_rule_rec.hier_rollup_table||' h'||
' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
' and h.single_depth_flag = ''Y'''||
' and h.parent_id = n.node_id'||
' and h.parent_depth_num = :b_parent_depth_num'||
' )';
l_parent_id_tbl.DELETE;
delete from fem_ru_cost_obj_hier_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id;
delete from fem_ru_activities_hier_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id;
select costed_flag
into l_costed_flag
from fem_ru_nodes_t
where created_by_request_id = p_request_id
and created_by_object_id = p_rollup_obj_id
and node_id = p_parent_id;
update fem_ru_nodes_t
set costed_flag = 'Y'
where created_by_request_id = p_request_id
and created_by_object_id = p_rollup_obj_id
and node_id = l_child_id_tbl(i)
and costed_flag = 'N';
l_child_id_tbl.DELETE;
l_weighting_pct_tbl.DELETE;
l_xledger_id_tbl.DELETE;
update fem_ru_nodes_t
set costed_flag = 'Y'
where created_by_request_id = p_request_id
and created_by_object_id = p_rollup_obj_id
and node_id = p_parent_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
);
l_created_by_request_id_tbl.DELETE;
l_created_by_object_id_tbl.DELETE;
| Updates the status of the object execution in the
| processing locks tables.
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Rule_Post_Proc (
p_request_rec in request_record
,p_rule_rec in rule_record
,p_num_of_input_rows_stmt in long
,p_exec_status_code in varchar2
)
IS
l_api_name constant varchar2(30) := 'Rule_Post_Proc';
,p_msg_text => 'Step 2: Update Number of Input Rows'
);
FEM_PL_PKG.Update_Num_Of_Input_Rows (
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_request_id => p_request_rec.request_id
,p_object_id => p_rule_rec.rollup_obj_id
,p_num_of_input_rows => l_num_of_input_rows
,p_user_id => p_request_rec.user_id
,p_last_update_login => p_request_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
delete from fem_ru_nodes_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id;
delete from fem_ru_cost_obj_hier_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id;
delete from fem_ru_activities_hier_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id;
,p_msg_text => 'Step 5: Update Number of Output Rows'
);
select count(*)
into l_num_of_output_rows
from fem_balances
where dataset_code = p_request_rec.output_dataset_code
and cal_period_id = p_request_rec.output_cal_period_id
and created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.rollup_obj_id
and ledger_id = p_request_rec.ledger_id;
FEM_PL_PKG.Update_Num_Of_Output_Rows (
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_request_id => p_request_rec.request_id
,p_object_id => p_rule_rec.rollup_obj_id
,p_table_name => 'FEM_BALANCES'
,p_statement_type => 'INSERT'
,p_num_of_output_rows => l_num_of_output_rows
,p_user_id => p_request_rec.user_id
,p_last_update_login => p_request_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_msg_text => 'Step 6: Update Object Execution Status'
);
FEM_PL_PKG.Update_Obj_Exec_Status (
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_request_id => p_request_rec.request_id
,p_object_id => p_rule_rec.rollup_obj_id
,p_exec_status_code => p_exec_status_code
,p_user_id => p_request_rec.user_id
,p_last_update_login => p_request_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_msg_text => 'Step 7: Update Object Execution Errors'
);
FEM_PL_PKG.Update_Obj_Exec_Errors (
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_request_id => p_request_rec.request_id
,p_object_id => p_rule_rec.rollup_obj_id
,p_errors_reported => 1
,p_errors_reprocessed => 0
,p_user_id => p_request_rec.user_id
,p_last_update_login => p_request_rec.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 in the processing locks tables.
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Request_Post_Proc (
p_request_rec in request_record
,p_exec_status_code in varchar2
)
IS
l_api_name constant varchar2(30) := 'Request_Post_Proc';
FEM_RULE_SET_MANAGER.FEM_DeleteFlatRuleList_PVT (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_TRUE
,p_encoded => FND_API.G_TRUE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_ruleset_object_id => p_request_rec.ruleset_obj_id
);
,p_msg_text => 'Step 2: Update Request Status'
);
FEM_PL_PKG.Update_Request_Status (
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_request_id => p_request_rec.request_id
,p_exec_status_code => p_exec_status_code
,p_user_id => p_request_rec.user_id
,p_last_update_login => p_request_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
select meaning
into l_meaning
from fnd_lookup_values_vl
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and view_application_id = 274;
select object_type_name
into l_object_type_name
from fem_object_types_vl
where object_type_code = p_object_type_code;