[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AR_INSERT_ACT_DRIV_ERR constant varchar2(30) := 'PFT_AR_INSERT_ACT_DRIV_ERR';
type g_last_update_date_table is table of PFT_AR_DRIVERS_T.LAST_UPDATE_DATE%TYPE
index by BINARY_INTEGER;
PROCEDURE Update_Obj_Exec_Step_Status (
p_request_rec in request_record
,p_rule_rec in rule_record
,p_exec_step in varchar2
,p_exec_status_code in varchar2
);
,p_insert_count out nocopy number
);
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;
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.act_rate_obj_type_code
from fem_rule_sets
where rule_set_obj_def_id = x_request_rec.ruleset_obj_def_id;
select object_name
,object_type_code
into l_object_name
,l_object_type_code
from fem_object_catalog_vl
where object_id = p_obj_id;
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 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 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 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
into l_object_name
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
,hier_versioning_type_code
into x_dimension_rec
from fem_xdim_dimensions_vl
where dimension_varchar_label = p_dimension_varchar_label;
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.dimension_id = l_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';
' 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;
,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
);
l_insert_count number;
,p_insert_count => l_insert_count
);
,p_msg_text => 'Step 1: Update Activity Id in FEM_BALANCES Table'
);
' select act.activity_id'||
' from fem_activities act'||
' ,pft_ar_driver_values_t drv'||
' where drv.CREATED_BY_REQUEST_ID = :b_request_id'||
' and drv.CREATED_BY_OBJECT_ID = :b_act_rate_obj_id'||
' and drv.activity_id = act.activity_id';
,p_msg_text => 'Step 2: Bulk Insert into FEM_BALANCES Table'
);
' insert into FEM_BALANCES ('||
' dataset_code'||
' ,cal_period_id'||
' ,creation_row_sequence'||
' ,source_system_code'||
' ,currency_code'||
' ,currency_type_code'||
' ,ledger_id'||
' ,financial_elem_id'||
' ,activity_id'||
' ,task_id'||
' ,product_id'||
' ,company_cost_center_org_id'||
' ,customer_id'||
' ,channel_id'||
' ,project_id'||
' ,user_dim1_id'||
' ,user_dim2_id'||
' ,user_dim3_id'||
' ,user_dim4_id'||
' ,user_dim5_id'||
' ,user_dim6_id'||
' ,user_dim7_id'||
' ,user_dim8_id'||
' ,user_dim9_id'||
' ,user_dim10_id'||
' ,natural_account_id'||
' ,line_item_id'||
' ,entity_id'||
' ,intercompany_id'||
' ,created_by_request_id'||
' ,created_by_object_id'||
' ,last_updated_by_request_id'||
' ,last_updated_by_object_id'||
' ,xtd_balance_e'||
' ,xtd_balance_f'||
' )'||
' select '||p_request_rec.output_dataset_code||
' ,'||p_request_rec.output_cal_period_id||
' ,'||p_rule_rec.rate_sequence_name||'.NEXTVAL'||
' ,'||p_request_rec.source_system_code||
' ,'''||p_rule_rec.entered_currency_code||''''||
' ,currency_type_code'||
' ,ledger_id'||
' ,'||G_FIN_ELEM_ID_ACTIVITY_RATE||
' ,activity_id'||
' ,task_id'||
' ,product_id'||
' ,company_cost_center_org_id'||
' ,customer_id'||
' ,channel_id'||
' ,project_id'||
' ,user_dim1_id'||
' ,user_dim2_id'||
' ,user_dim3_id'||
' ,user_dim4_id'||
' ,user_dim5_id'||
' ,user_dim6_id'||
' ,user_dim7_id'||
' ,user_dim8_id'||
' ,user_dim9_id'||
' ,user_dim10_id'||
' ,natural_account_id'||
' ,statistic_basis_id'||
' ,entity_id'||
' ,intercompany_id'||
' ,'||p_request_rec.request_id||
' ,'||p_rule_rec.act_rate_obj_id||
' ,'||p_request_rec.request_id||
' ,'||p_rule_rec.act_rate_obj_id||
' ,act_rate_value / '||p_rule_rec.entered_exch_rate_den||' * '||p_rule_rec.entered_exch_rate_num||
' ,act_rate_value'||
' from ('||
' select b.currency_type_code'||
' ,b.ledger_id'||
' ,b.activity_id'||
' ,b.task_id'||
' ,b.product_id'||
' ,b.company_cost_center_org_id'||
' ,b.customer_id'||
' ,b.channel_id'||
' ,b.project_id'||
' ,b.user_dim1_id'||
' ,b.user_dim2_id'||
' ,b.user_dim3_id'||
' ,b.user_dim4_id'||
' ,b.user_dim5_id'||
' ,b.user_dim6_id'||
' ,b.user_dim7_id'||
' ,b.user_dim8_id'||
' ,b.user_dim9_id'||
' ,b.user_dim10_id'||
' ,b.natural_account_id'||
' ,max(drv.statistic_basis_id) as statistic_basis_id'||
' ,b.entity_id'||
' ,b.intercompany_id'||
' ,sum(b.xtd_balance_f) / max(abs(drv.driver_value)) as act_rate_value'||
' from fem_balances b'||
' ,pft_ar_driver_values_t {{table_partition}} drv'||
' where b.ledger_id = '||p_request_rec.ledger_id||
' and b.financial_elem_id not in ('||G_FIN_ELEM_ID_STATISTIC||','||G_FIN_ELEM_ID_ACTIVITY_RATE||')'||
' and b.currency_type_code = ''ENTERED'''||
' and '||p_input_ds_b_where_clause||
' and not ('||
' b.created_by_request_id = '||p_request_rec.request_id||
' and b.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
' )'||
' and drv.activity_id = b.activity_id'||
-- l_drv_vals_tbl_where_clause with aliases (start)
' and drv.created_by_request_id = '||p_request_rec.request_id||
' and drv.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
-- l_drv_vals_tbl_where_clause with aliases (end)
' and {{data_slice}} '||
' group by b.currency_type_code'||
' ,b.currency_type_code'||
' ,b.ledger_id'||
' ,b.activity_id'||
' ,b.task_id'||
' ,b.product_id'||
' ,b.company_cost_center_org_id'||
' ,b.customer_id'||
' ,b.channel_id'||
' ,b.project_id'||
' ,b.user_dim1_id'||
' ,b.user_dim2_id'||
' ,b.user_dim3_id'||
' ,b.user_dim4_id'||
' ,b.user_dim5_id'||
' ,b.user_dim6_id'||
' ,b.user_dim7_id'||
' ,b.user_dim8_id'||
' ,b.user_dim9_id'||
' ,b.user_dim10_id'||
' ,b.natural_account_id'||
-- Bug fix 4619775 - ammittal 10/07/05 - The code has been commented
-- to avoid grouping by line item id as the statistic basis id from
-- drivers table is always used in the select statement
-- ' ,b.line_item_id'||
' ,b.entity_id'||
' ,b.intercompany_id'||
' )';
' insert into FEM_BALANCES ('||
' dataset_code'||
' ,cal_period_id'||
' ,creation_row_sequence'||
' ,source_system_code'||
' ,currency_code'||
' ,currency_type_code'||
' ,ledger_id'||
' ,financial_elem_id'||
' ,activity_id'||
' ,task_id'||
' ,product_id'||
' ,company_cost_center_org_id'||
' ,customer_id'||
' ,channel_id'||
' ,project_id'||
' ,user_dim1_id'||
' ,user_dim2_id'||
' ,user_dim3_id'||
' ,user_dim4_id'||
' ,user_dim5_id'||
' ,user_dim6_id'||
' ,user_dim7_id'||
' ,user_dim8_id'||
' ,user_dim9_id'||
' ,user_dim10_id'||
' ,natural_account_id'||
' ,line_item_id'||
' ,entity_id'||
' ,intercompany_id'||
' ,created_by_request_id'||
' ,created_by_object_id'||
' ,last_updated_by_request_id'||
' ,last_updated_by_object_id'||
' ,xtd_balance_e'||
' ,xtd_balance_f'||
' )'||
' select '||p_request_rec.output_dataset_code||
' ,'||p_request_rec.output_cal_period_id||
' ,'||p_rule_rec.rate_sequence_name||'.NEXTVAL'||
' ,'||p_request_rec.source_system_code||
' ,'''||p_rule_rec.entered_currency_code||''''||
' ,currency_type_code'||
' ,ledger_id'||
' ,'||G_FIN_ELEM_ID_ACTIVITY_RATE||
' ,activity_id'||
' ,task_id'||
' ,product_id'||
' ,company_cost_center_org_id'||
' ,customer_id'||
' ,channel_id'||
' ,project_id'||
' ,user_dim1_id'||
' ,user_dim2_id'||
' ,user_dim3_id'||
' ,user_dim4_id'||
' ,user_dim5_id'||
' ,user_dim6_id'||
' ,user_dim7_id'||
' ,user_dim8_id'||
' ,user_dim9_id'||
' ,user_dim10_id'||
' ,natural_account_id'||
' ,statistic_basis_id'||
' ,entity_id'||
' ,intercompany_id'||
' ,'||p_request_rec.request_id||
' ,'||p_rule_rec.act_rate_obj_id||
' ,'||p_request_rec.request_id||
' ,'||p_rule_rec.act_rate_obj_id||
' ,null'||
' ,act_rate_value'||
' from ('||
' select b.currency_type_code'||
' ,b.ledger_id'||
' ,b.activity_id'||
' ,b.task_id'||
' ,b.product_id'||
' ,b.company_cost_center_org_id'||
' ,b.customer_id'||
' ,b.channel_id'||
' ,b.project_id'||
' ,b.user_dim1_id'||
' ,b.user_dim2_id'||
' ,b.user_dim3_id'||
' ,b.user_dim4_id'||
' ,b.user_dim5_id'||
' ,b.user_dim6_id'||
' ,b.user_dim7_id'||
' ,b.user_dim8_id'||
' ,b.user_dim9_id'||
' ,b.user_dim10_id'||
' ,b.natural_account_id'||
' ,max(drv.statistic_basis_id) as statistic_basis_id'||
' ,b.entity_id'||
' ,b.intercompany_id'||
' ,sum(b.xtd_balance_f) / max(abs(drv.driver_value)) as act_rate_value'||
' from fem_balances b'||
' ,pft_ar_driver_values_t {{table_partition}} drv'||
' where b.ledger_id = '||p_request_rec.ledger_id||
' and b.financial_elem_id not in ('||G_FIN_ELEM_ID_STATISTIC||','||G_FIN_ELEM_ID_ACTIVITY_RATE||')'||
' and b.currency_type_code = ''ENTERED'''||
' and '||p_input_ds_b_where_clause||
' and not ('||
' b.created_by_request_id = '||p_request_rec.request_id||
' and b.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
' )'||
' and drv.activity_id = b.activity_id'||
-- l_drv_vals_tbl_where_clause with aliases (start)
' and drv.created_by_request_id = '||p_request_rec.request_id||
' and drv.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
-- l_drv_vals_tbl_where_clause with aliases (end)
' and {{data_slice}} '||
' group by b.currency_type_code'||
' ,b.currency_type_code'||
' ,b.ledger_id'||
' ,b.activity_id'||
' ,b.task_id'||
' ,b.product_id'||
' ,b.company_cost_center_org_id'||
' ,b.customer_id'||
' ,b.channel_id'||
' ,b.project_id'||
' ,b.user_dim1_id'||
' ,b.user_dim2_id'||
' ,b.user_dim3_id'||
' ,b.user_dim4_id'||
' ,b.user_dim5_id'||
' ,b.user_dim6_id'||
' ,b.user_dim7_id'||
' ,b.user_dim8_id'||
' ,b.user_dim9_id'||
' ,b.user_dim10_id'||
' ,b.natural_account_id'||
-- Bug fix 4619775 - ammittal 10/07/05 - The code has been commented
-- to avoid grouping by line item id as the statistic basis id from
-- drivers table is always used in the select statement
-- ' ,b.line_item_id'||
' ,b.entity_id'||
' ,b.intercompany_id'||
' )';
Update_Obj_Exec_Step_Status(
p_request_rec => p_request_rec
,p_rule_rec => p_rule_rec
,p_exec_step => L_CALC_ACT_RATE_VALUES
,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
);
Update_Obj_Exec_Step_Status(
p_request_rec => p_request_rec
,p_rule_rec => p_rule_rec
,p_exec_step => L_CALC_ACT_RATE_VALUES
,p_exec_status_code => G_EXEC_STATUS_SUCCESS
);
FEM_MULTI_PROC_PKG.Delete_Data_Slices (
p_req_id => p_request_rec.request_id
);
,p_msg_text => 'Step 3: Bulk Insert into FEM_BALANCES_CALC_FCTRS Table'
);
' insert into FEM_BALANCES_CALC_FCTRS ('||
' created_by_request_id'||
' ,created_by_object_id'||
' ,creation_row_sequence'||
' ,factor'||
' ,output_type'||
' ,last_updated_by_object_id'||
' ,last_updated_by_request_id'||
' )'||
' select b.created_by_request_id'||
' ,b.created_by_object_id'||
' ,b.creation_row_sequence'||
' ,1/drv.driver_value'||
' ,''N/A'''||
' ,b.last_updated_by_object_id'||
' ,b.last_updated_by_request_id'||
' from fem_balances b'||
' ,pft_ar_driver_values_t {{table_partition}} drv'||
' where b.ledger_id = '||p_request_rec.ledger_id||
' and b.dataset_code = '||p_request_rec.output_dataset_code||
' and b.cal_period_id = '||p_request_rec.output_cal_period_id||
' and b.created_by_request_id = drv.created_by_request_id'||
' and b.created_by_object_id = drv.created_by_object_id'||
' and drv.activity_id = b.activity_id'||
-- l_drv_vals_tbl_where_clause with aliases (start)
' and drv.created_by_request_id = '||p_request_rec.request_id||
' and drv.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
-- l_drv_vals_tbl_where_clause with aliases (end)
' and {{data_slice}} ';
Update_Obj_Exec_Step_Status(
p_request_rec => p_request_rec
,p_rule_rec => p_rule_rec
,p_exec_step => L_CALC_ACT_RATE_FACTORS
,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
);
Update_Obj_Exec_Step_Status(
p_request_rec => p_request_rec
,p_rule_rec => p_rule_rec
,p_exec_step => L_CALC_ACT_RATE_FACTORS
,p_exec_status_code => G_EXEC_STATUS_SUCCESS
);
FEM_MULTI_PROC_PKG.Delete_Data_Slices (
p_req_id => p_request_rec.request_id
);
select object_type_code
,object_name
,local_vs_combo_id
into x_rule_rec.act_rate_obj_type_code
,x_rule_rec.act_rate_obj_name
,x_rule_rec.local_vs_combo_id
from fem_object_catalog_vl
where object_id = x_rule_rec.act_rate_obj_id;
select activity_hier_obj_id,
currency_code,
condition_obj_id,
top_nodes_flag,
output_to_rate_stat_flag
into x_rule_rec.hier_obj_id
,x_rule_rec.entered_currency_code
,x_rule_rec.cond_obj_id
,x_rule_rec.top_node_flag
,x_rule_rec.output_to_rate_stat_flag
from PFT_ACTIVITY_RATES
where activity_rate_obj_def_id = x_rule_rec.act_rate_obj_def_id;
select h.dimension_id
into l_dimension_id
from fem_hierarchies h
where h.hierarchy_obj_id = x_rule_rec.hier_obj_id;
,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_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_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
| Update_Obj_Exec_Step_Status
|
| DESCRIPTION
| Update Object Execution Step Status
|
| SCOPE - PRIVATE
|
+===========================================================================*/
PROCEDURE Update_Obj_Exec_Step_Status (
p_request_rec in request_record
,p_rule_rec in rule_record
,p_exec_step in varchar2
,p_exec_status_code in varchar2
)
IS
L_API_NAME constant varchar2(30) := 'Update_Obj_Exec_Step_Status';
FEM_PL_PKG.Update_Obj_Exec_Step_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.act_rate_obj_id
,p_exec_step => p_exec_step
,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
);
END Update_Obj_Exec_Step_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
);
,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.act_rate_obj_id
and object_type = 'SEQUENCE'
and object_name = p_rule_rec.rate_sequence_name;
delete from fem_pl_temp_objects
where request_id = p_request_rec.request_id
and object_id = p_rule_rec.act_rate_obj_id
and object_type = 'SEQUENCE'
and object_name = p_rule_rec.rate_sequence_name;
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.act_rate_obj_id
and object_type = 'SEQUENCE'
and object_name = p_rule_rec.drv_sequence_name;
delete from fem_pl_temp_objects
where request_id = p_request_rec.request_id
and object_id = p_rule_rec.act_rate_obj_id
and object_type = 'SEQUENCE'
and object_name = p_rule_rec.drv_sequence_name;
,p_insert_count out nocopy number
)
IS
L_API_NAME constant varchar2(30) := 'Process_Drivers';
' SELECT 1'||
' FROM FEM_ACTIVITIES_HIER H'||
' WHERE H.HIERARCHY_OBJ_DEF_ID = :b_hier_obj_def_id'||
' AND H.CHILD_ID = H.PARENT_ID'||
' AND H.PARENT_ID = ACTS.ACTIVITY_ID'||
' AND H.SINGLE_DEPTH_FLAG = ''Y'''||
' )';
' SELECT 1'||
' FROM FEM_ACTIVITIES_HIER H'||
' WHERE H.HIERARCHY_OBJ_DEF_ID = :b_hier_obj_def_id'||
' AND H.CHILD_ID = ACTS.ACTIVITY_ID'||
' AND H.SINGLE_DEPTH_FLAG = ''Y'''||
' )';
' INSERT INTO PFT_AR_DRIVERS_T('||
' CREATED_BY_REQUEST_ID'||
' ,CREATED_BY_OBJECT_ID'||
' ,SEQ_ID'||
' ,SOURCE_TABLE_NAME'||
' ,COLUMN_NAME'||
' ,STATISTIC_BASIS_ID'||
' ,CONDITION_OBJ_ID'||
' ,LAST_UPDATE_DATE'||
' )'||
' SELECT'||
' :b_request_id'||
' ,:b_act_rate_obj_id'||
' ,' || p_rule_rec.drv_sequence_name||'.nextval'||
' ,SOURCE_TABLE_NAME'||
' ,COLUMN_NAME'||
' ,STATISTIC_BASIS_ID'||
' ,CONDITION_OBJ_ID'||
' ,sysdate'||
' FROM ('||
' SELECT distinct assgn.SOURCE_TABLE_NAME'||
' ,assgn.COLUMN_NAME'||
' ,assgn.STATISTIC_BASIS_ID'||
' ,assgn.CONDITION_OBJ_ID'||
' FROM PFT_ACTIVITY_DRIVER_ASGN assgn'||
' WHERE EXISTS ('||
' SELECT activity_id'||
' FROM fem_activities acts'||
' WHERE acts.local_vs_combo_id = :b_local_vs_combo_id'||
' AND acts.activity_id = assgn.ACTIVITY_ID'||
' AND '||l_act_hier_where_clause||
l_act_cond_where_clause||
' )'||
' AND ACTIVITY_RATE_OBJ_DEF_ID = :b_act_rate_obj_def_id'||
' )'
USING
p_request_rec.request_id
, p_rule_rec.act_rate_obj_id
, p_request_rec.local_vs_combo_id
, p_rule_rec.hier_obj_def_id
, p_rule_rec.act_rate_obj_def_id;
p_insert_count := SQL%ROWCOUNT;
if (p_insert_count > 0) then
-- Register Object Execution Step
Register_Obj_Exec_Step (
p_request_rec => p_request_rec
,p_rule_rec => p_rule_rec
,p_exec_step => L_CALC_DRIVER_VALUES
,p_exec_status_code => G_EXEC_STATUS_RUNNING
);
Update_Obj_Exec_Step_Status (
p_request_rec => p_request_rec
,p_rule_rec => p_rule_rec
,p_exec_step => L_CALC_DRIVER_VALUES
,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
);
Update_Obj_Exec_Step_Status (
p_request_rec => p_request_rec
,p_rule_rec => p_rule_rec
,p_exec_step => L_CALC_DRIVER_VALUES
,p_exec_status_code => G_EXEC_STATUS_SUCCESS
);
FEM_MULTI_PROC_PKG.Delete_Data_Slices (
p_req_id => p_request_rec.request_id
);
SELECT count(*)
INTO l_valid_drv_count
FROM PFT_AR_DRIVERS_T
WHERE CREATED_BY_REQUEST_ID = p_request_rec.request_id
AND CREATED_BY_OBJECT_ID = p_rule_rec.act_rate_obj_id
AND VALID_FLAG = 'Y';
l_last_update_date_tbl g_last_update_date_table;
' select rowid'||
' ,source_table_name'||
' ,column_name'||
' ,statistic_basis_id'||
' ,condition_obj_id'||
' ,valid_flag'||
' ,driver_value'||
' ,last_update_date'||
' ,invalid_reason'||
' from pft_ar_drivers_t {{table_partition}} drv'||
' where created_by_request_id = '||p_request_id||
' and created_by_object_id = '||p_act_rate_obj_id||
' and {{data_slice}} ';
,l_last_update_date_tbl
,l_invalid_reason_tbl
limit l_fetch_limit;
select 1
into l_dummy
from fem_table_class_assignmt_v
where table_classification_code in ('STATISTIC', 'PFT_LEDGER')
and table_name = l_drv_table_name_tbl(i);
' select sum(d.'||l_column_name_tbl(i)||')'||
' from '||l_drv_table_name_tbl(i)|| ' d' ||
' where d.line_item_id = :b_statistic_basis_id'||
' and '|| l_input_ds_d_where_clause||
' and d.ledger_id = :b_ledger_id';
,p_msg_name => G_AR_INSERT_ACT_DRIV_ERR
,p_token1 => 'TABLE_NAME'
,p_value1 => l_statistic_basis_id_tbl(i)
,p_token2 => 'OBJECT_ID'
,p_value2 => p_act_rate_obj_id
);
,p_msg_text => 'Step 4: Bulk Update PFT_AR_DRIVERS_T Table'
);
update pft_ar_drivers_t
set driver_value = l_driver_value_tbl(rec_num)
,valid_flag = l_valid_flag_tbl(rec_num)
,invalid_reason = l_invalid_reason_tbl(rec_num)
where rowid = l_rowid_tbl(rec_num);
,p_msg_text => 'Step 5: Build MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T'
);
' insert into pft_ar_driver_values_t ('||
' created_by_request_id'||
' ,created_by_object_id'||
' ,activity_id'||
' ,driver_value'||
' ,statistic_basis_id'||
' )'||
' select '||p_request_id||
' ,'||p_act_rate_obj_id||
' ,assgn.activity_id'||
' ,drv.driver_value'||
' ,assgn.statistic_basis_id'||
' from pft_ar_drivers_t {{table_partition}} drv'||
' ,pft_activity_driver_asgn assgn'||
' where drv.created_by_request_id = '||p_request_id||
' and drv.created_by_object_id = '||p_act_rate_obj_id||
' and drv.valid_flag = ''Y'''||
' and assgn.activity_rate_obj_def_id = '||p_act_rate_obj_def_id||
' and assgn.source_table_name = drv.source_table_name'||
' and assgn.column_name = drv.column_name'||
' and assgn.statistic_basis_id = drv.statistic_basis_id'||
' and exists ('||
' select activity_id'||
' from fem_activities acts'||
' where acts.local_vs_combo_id = '||p_local_vs_combo_id||
' and acts.activity_id = assgn.activity_id'||
' and '||p_act_hier_where_clause||
p_act_cond_where_clause||
' )'||
' and nvl(assgn.condition_obj_id, -1) = nvl(drv.condition_obj_id, -1)'||
' and {{data_slice}} ';
,p_msg_text => 'Step 6: Execute MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T'
);
l_rowid_tbl.DELETE;
l_drv_table_name_tbl.DELETE;
l_column_name_tbl.DELETE;
l_statistic_basis_id_tbl.DELETE;
l_drv_condition_obj_id_tbl.DELETE;
l_valid_flag_tbl.DELETE;
l_driver_value_tbl.DELETE;
l_last_update_date_tbl.DELETE;
l_invalid_reason_tbl.DELETE;
' select distinct d.created_by_request_id'||
' ,d.created_by_object_id'||
' from ' || p_drv_table_name || ' d'||
' where d.line_item_id = :b_statistic_basis_id'||
' and '|| p_input_ds_d_where_clause||
' and d.ledger_id = :b_ledger_id'||
' and not ('||
' d.created_by_request_id = :b_request_id'||
' and d.created_by_object_id = :b_act_rate_obj_id'||
' )'||
' and not exists ('||
' select 1'||
' from fem_pl_chains c'||
' where c.request_id = :b_request_id'||
' and c.object_id = :b_act_rate_obj_id'||
' and c.source_created_by_request_id = d.created_by_request_id'||
' and c.source_created_by_object_id = d.created_by_object_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;
' select distinct created_by_request_id'||
' ,created_by_object_id'||
' from fem_balances b'||
' where b.ledger_id = :b_ledger_id'||
' and b.financial_elem_id not in (:b_stat_fin_elem_id, :b_act_rate_fin_elem_id)'||
' and b.currency_type_code = ''ENTERED'''||
' and '||p_input_ds_b_where_clause||
' and not ('||
' b.created_by_request_id = :b_request_id'||
' and b.created_by_object_id = :b_act_rate_obj_id'||
' )'||
' and exists ('||
' select 1'||
' from pft_ar_driver_values_t drv'||
' where drv.created_by_request_id = :b_request_id'||
' and drv.created_by_object_id = :b_act_rate_obj_id'||
' and drv.activity_id = b.activity_id'||
' )'||
' and not exists ('||
' select 1'||
' from fem_pl_chains c'||
' where c.request_id = :b_request_id'||
' and c.object_id = :b_act_rate_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'||
' )';
,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
);
t_created_by_request_id.DELETE;
t_created_by_object_id.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_input_ds_b_where_clause 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'
);
' select count(*)'||
' from fem_balances b'||
' where b.ledger_id = :b_ledger_id'||
' and b.financial_elem_id not in (:b_stat_fin_elem_id,:b_act_rate_fin_elem_id)'||
' and b.currency_type_code = ''ENTERED'''||
' and '||p_input_ds_b_where_clause||
' and not ('||
' b.created_by_request_id = :b_request_id'||
' and b.created_by_object_id = :b_act_rate_obj_id'||
' )'||
' and exists ('||
' select 1'||
' from pft_ar_driver_values_t drv'||
' where drv.created_by_request_id = :b_request_id'||
' and drv.created_by_object_id = :b_act_rate_obj_id'||
' and drv.activity_id = b.activity_id'||
' )';
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.act_rate_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 pft_ar_driver_values_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.act_rate_obj_id;
delete from pft_ar_drivers_t
where created_by_request_id = p_request_rec.request_id
and created_by_object_id = p_rule_rec.act_rate_obj_id;
,p_msg_text => 'Step 3: 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.act_rate_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.act_rate_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 4: 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.act_rate_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 5: 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.act_rate_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
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and view_application_id = 274
and language = userenv('LANG');