[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert CONSTANT VARCHAR2(30) := 'INSERT';
PROCEDURE Update_Nbr_Of_Output_Rows(
p_param_rec IN param_record
,p_num_output_rows IN NUMBER
,p_tbl_name IN VARCHAR2
,p_stmt_type IN VARCHAR2
);
PROCEDURE Update_Obj_Exec_Step_Status(
p_param_rec IN param_record
,p_exe_step IN VARCHAR2
,p_exe_status_code IN VARCHAR2
);
p_select_col IN OUT NOCOPY LONG
,p_from_clause IN OUT NOCOPY LONG
,p_where_clause IN OUT NOCOPY LONG
);
PROCEDURE Update_Nbr_Of_Input_Rows(
p_param_rec IN param_record
,p_num_input_rows IN NUMBER
);
/* SELECT p.child_obj_def_id,
p.engine_execution_sequence,
p.child_obj_id,
x.exec_status_code
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*/
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
,p_hierarchy_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);
,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_Output_Rows
|
| DESCRIPTION
| Updates the rows successfully processed by calling
| fem_pl_pkg.Update_Num_Of_Output_Rows in fem_pl_tables.
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Update_Nbr_Of_Output_Rows( p_param_rec IN param_record
,p_num_output_rows IN NUMBER
,p_tbl_name IN VARCHAR2
,p_stmt_type IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Output_Rows';
FEM_PL_PKG.Update_Num_Of_Output_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_table_name => p_tbl_name
,p_statement_type => p_stmt_type
,p_num_of_output_rows => p_num_output_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 Rows Exception');
END Update_Nbr_Of_Output_Rows;
| Update_Obj_Exec_Step_Status
|
| DESCRIPTION
| Updates the status of the executuon of the object by calling
| fem_pl_pkg.Update_obj_exec_step_status in fem_pl_obj_steps.
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Update_Obj_Exec_Step_Status( p_param_rec IN param_record
,p_exe_step IN VARCHAR2
,p_exe_status_code IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Obj_Exe_Step_Status';
FEM_PL_PKG.Update_Obj_Exec_Step_Status(
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_exec_step => p_exe_step
,p_exec_status_code => p_exe_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 => 'Update Obj Exec Step API Exception');
END Update_Obj_Exec_Step_Status;
SELECT NVL(SUM(rows_processed),0), NVL(SUM(rows_rejected),0), NVL(SUM(rows_loaded),0)
INTO x_rows_processed, x_rows_rejected, x_rows_loaded
FROM fem_mp_process_ctl_t t
WHERE t.req_id = p_request_id
AND t.process_num > 0;
,p_msg_text => 'No Rows returned by the Insert Statement');
Update_Nbr_Of_Output_Rows(p_param_rec => p_param_rec
,p_num_output_rows => p_param_rec.rows_processed
,p_tbl_name => p_tbl_name
,p_stmt_type => g_insert );
Update_Nbr_Of_Input_Rows(p_param_rec => p_param_rec
,p_num_input_rows => p_param_rec.rows_processed);
,p_msg_text => 'Update the status of the step with execution status :'
||p_exe_status_code);
Update_Obj_Exec_Step_Status( p_param_rec => p_param_rec
,p_exe_step => 'ACCT_REL_CONS'
,p_exe_status_code => p_exe_status_code );
SELECT processing_table
,load_secondary_rel_flag
,condition_obj_id
,col_tmplt_obj_id
INTO l_process_table
,p_param_rec.sec_relns_flag
,p_param_rec.cond_obj_id
,l_col_tmplt_obj_id
FROM pft_acct_rel_cons_rules
WHERE acct_rel_cons_obj_def_id =
p_param_rec.crnt_proc_child_obj_defn_id;
SELECT source_table_name
INTO l_src_tab_name
FROM fem_col_population_tmplt_b
WHERE col_pop_templt_obj_def_id = l_col_obj_def_id
AND ROWNUM = 1;
SELECT COUNT(aggregation_method)
INTO l_aggregation_method
FROM fem_col_population_tmplt_b
WHERE col_pop_templt_obj_def_id = l_col_obj_def_id
AND aggregation_method <> 'NOAGG';
,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);
,p_stmt_type => g_insert);
SELECT DISTINCT(message)
BULK COLLECT INTO v_msg_list
FROM fem_mp_process_ctl_t
WHERE req_id = p_param_rec.request_id
AND status = 2;
FEM_MULTI_PROC_PKG.Delete_Data_Slices (
p_req_id => p_param_rec.request_id);
l_insert_head_stmt LONG;
l_select_stmt LONG;
l_selection_param NUMBER;
l_selection_param := 1;
l_selection_param := 0;
,p_selection_param => l_selection_param
,p_effective_date => p_effective_date
,p_condition_obj_id => p_condition_obj_id
,p_condition_sel_param => l_condition_sel_param
,p_load_sec_relns => p_secondary_flag
,p_dataset_grp_obj_def_id => p_dataset_io_obj_def_id
,p_cal_period_id => p_cal_period_id
,p_ledger_id => p_ledger_id
,p_source_system_code => p_source_system_code
,p_created_by_object_id => p_rule_obj_id
,p_created_by_request_id => l_request_id
,p_insert_list => l_insert_head_stmt
,p_select_list => l_select_stmt
,p_from_clause => l_from_stmt
,p_where_clause => l_where_stmt
,p_con_where_clause => l_cond_where_stmt
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
Add_Secondary_Relation( p_select_col => l_select_stmt
,p_from_clause => l_from_stmt
,p_where_clause => l_where_stmt);
RETURN l_insert_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt
|| ' ' || l_where_stmt;
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_TRUE
,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_TRUE
,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_TRUE
,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';
PROCEDURE add_secondary_relation(p_select_col IN OUT NOCOPY LONG,
p_from_clause IN OUT NOCOPY LONG,
p_where_clause IN OUT NOCOPY LONG)
IS
l_api_name CONSTANT VARCHAR2(30) := 'add_secondary_relation';
SELECT REPLACE(p_select_col, '{{{CUSTOMER_ID}}}',
l_sec_alias || '.' || 'CUSTOMER_ID' )
INTO p_select_col
FROM DUAL;
,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_Intput_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;