[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert CONSTANT VARCHAR2(30) := 'INSERT';
g_update CONSTANT VARCHAR2(30) := 'UPDATE';
PROCEDURE Update_Nbr_Of_Input_Rows(
p_param_rec IN param_record
,p_num_input_rows IN NUMBER
);
PROCEDURE Register_Updated_Column(
p_param_rec IN param_record
,p_table_name IN VARCHAR2
,p_statement_type IN VARCHAR2
,p_column_name IN VARCHAR2);
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_param_rec.obj_type_code
,x_param_rec.local_vs_combo_id
FROM fem_object_catalog_b
WHERE object_id = x_param_rec.obj_id;
SELECT object_id
INTO x_param_rec.dataset_grp_obj_id
FROM fem_object_definition_b
WHERE object_definition_id = x_param_rec.dataset_io_obj_def_id;
SELECT output_dataset_code
INTO x_param_rec.output_dataset_code
FROM fem_ds_input_output_defs
WHERE dataset_io_obj_def_id = x_param_rec.dataset_io_obj_def_id;
SELECT source_system_code
INTO x_param_rec.source_system_code
FROM fem_source_systems_b
WHERE source_system_display_code = G_PFT;
,p_last_update_login => p_param_rec.login_id
,p_program_id => p_param_rec.pgm_id
,p_program_login_id => p_param_rec.login_id
,p_program_application_id => p_param_rec.pgm_app_id
,p_exec_mode_code => NULL
,p_dimension_id => NULL
,p_table_name => NULL
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
,p_last_update_login => p_param_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_param_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
SELECT value_index_formula_id,
condition_obj_id,
region_counting_flag,
proft_percentile_flag,
value_index_flag,
prospect_ident_flag,
customer_level,
output_column
INTO l_value_index_formula_id,
p_param_rec.cond_obj_id,
l_region_counting_flag,
l_proft_percentile_flag,
l_value_index_flag,
l_prospect_ident_flag,
l_customer_level,
l_output_column
FROM pft_pprof_calc_rules
WHERE pprof_calc_obj_def_id = p_param_rec.crnt_proc_child_obj_defn_id;
,p_last_update_login => p_param_rec.login_id
,p_exec_mode_code => NULL
,x_exec_state => l_exec_state
,x_prev_request_id => l_prev_req_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
SELECT COUNT (region_pct_total_cust)
INTO l_region_percent
FROM fem_region_info
WHERE cal_period_id = p_param_rec.output_cal_period_id
AND ledger_id = p_param_rec.ledger_id
AND dataset_code = p_param_rec.output_dataset_code
AND source_system_code = p_param_rec.source_system_code
AND dimension_group_id = l_customer_level;
,p_stmt_type => g_insert);
SELECT COUNT (profit_percentile)
INTO l_profit_percentile
FROM fem_customer_profit
WHERE cal_period_id = p_param_rec.output_cal_period_id
AND ledger_id = p_param_rec.ledger_id
AND dataset_code = p_param_rec.output_dataset_code
AND source_system_code = p_param_rec.source_system_code
AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
SELECT profit_percentile
BULK COLLECT INTO v_percentile
FROM fem_customer_profit
WHERE cal_period_id = p_param_rec.output_cal_period_id
AND ledger_id = p_param_rec.ledger_id
AND dataset_code = p_param_rec.output_dataset_code
AND source_system_code = p_param_rec.source_system_code
AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
,p_stmt_type => g_update);
Register_Updated_Column( p_param_rec => p_param_rec
,p_table_name => g_fem_customer_profit
,p_statement_type => g_update
,p_column_name => 'PROFIT_PERCENTILE');
Register_Updated_Column( p_param_rec => p_param_rec
,p_table_name => g_fem_customer_profit
,p_statement_type => g_update
,p_column_name => 'PROFIT_DECILE');
,p_stmt_type => g_update);
,p_stmt_type => g_update);
Register_Updated_Column( p_param_rec => p_param_rec
,p_table_name => g_fem_customers_attr
,p_statement_type => g_update
,p_column_name => 'VARCHAR_ASSIGN_VALUE');
SELECT COUNT(*)
INTO l_count
FROM fem_ruleset_process_data p,
fem_pl_object_executions x
WHERE p.request_id = p_request_id AND
p.request_id = x.request_id(+) AND
p.rule_set_obj_id = p_rule_set_obj_id
ORDER BY p.engine_execution_sequence;
| Updates the status of the request and object execution in the
| processing locks tables.
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Eng_Master_Post_Proc ( p_param_rec IN param_record
,p_exec_status_code IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Eng_Master_Post_Proc';
,p_msg_text => 'Step 1: 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_param_rec.request_id
,p_object_id => p_param_rec.crnt_proc_child_obj_id
,p_exec_status_code => p_exec_status_code
,p_user_id => p_param_rec.user_id
,p_last_update_login => p_param_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 2: 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_param_rec.request_id
,p_object_id => p_param_rec.crnt_proc_child_obj_id
,p_errors_reported => 1 --todo: verify
,p_errors_reprocessed => 0 --todo: verify
,p_user_id => p_param_rec.user_id
,p_last_update_login => p_param_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 3: Update Request Status');
FEM_PL_PKG.Update_Request_Status (
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_request_id => p_param_rec.request_id
,p_exec_status_code => p_exec_status_code
,p_user_id => p_param_rec.user_id
,p_last_update_login => p_param_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
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';
,p_last_update_login => p_param_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
| Update_Num_Of_Input_Rows
|
| DESCRIPTION
| This procedure logs the total number of rows used as input into
| an object execution
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Update_Nbr_Of_Input_Rows( p_param_rec IN param_record
,p_num_input_rows IN NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Input_Rows';
FEM_PL_PKG.Update_Num_Of_Input_Rows(
p_api_version => 1.0
,p_commit => FND_API.G_TRUE
,p_request_id => p_param_rec.request_id
,p_object_id => p_param_rec.crnt_proc_child_obj_id
,p_num_of_input_rows => p_num_input_rows
,p_user_id => p_param_rec.user_id
,p_last_update_login => p_param_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
,p_msg_text => 'Update Input Rows Exception');
,p_msg_text => 'Update Input Rows Exception');
END Update_Nbr_Of_Input_Rows;
,p_last_update_login => p_param_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
| Register_Updated_Column
|
| DESCRIPTION
| This procedure is used to register a column updated during object execution
|
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Register_Updated_Column( p_param_rec IN param_record
,p_table_name IN VARCHAR2
,p_statement_type IN VARCHAR2
,p_column_name IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Register_Updated_Column';
e_reg_updated_column_error EXCEPTION;
FEM_PL_PKG.register_updated_column(
p_api_version => 1.0
,p_commit => FND_API.G_TRUE
,p_request_id => p_param_rec.request_id
,p_object_id => p_param_rec.crnt_proc_child_obj_id
,p_table_name => p_table_name
,p_statement_type => p_statement_type
,p_column_name => p_column_name
,p_user_id => p_param_rec.user_id
,p_last_update_login => p_param_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
RAISE e_reg_updated_column_error;
WHEN e_reg_updated_column_error THEN
FEM_ENGINES_PKG.Tech_Message (
p_severity => g_log_level_5
,p_module => G_BLOCK||'.'||l_api_name
,p_msg_text => 'Register_Updated_Column_Exception');
,p_msg_text => 'Register_Updated_Column_Exception');
END Register_Updated_Column;