[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_Output_Rows (
p_request_id IN NUMBER
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,p_rule_obj_id IN NUMBER
,p_num_output_rows IN NUMBER
,p_tbl_name IN VARCHAR2
,p_stmt_type IN VARCHAR2
);
PROCEDURE Update_Obj_Exec_Step_Status (
p_request_id IN NUMBER
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,p_rule_obj_id IN NUMBER
,p_exe_step IN VARCHAR2
,p_exe_status_code IN VARCHAR2
);
PROCEDURE Update_Nbr_Of_Input_Rows (
p_request_id IN NUMBER
,p_user_id IN NUMBER
,p_last_update_login IN NUMBER
,p_rule_obj_id IN NUMBER
,p_num_of_input_rows IN NUMBER
);
SELECT gvsc.value_set_id
INTO l_value_set_id
FROM fem_global_vs_combo_defs gvsc,fem_dimensions_b dim
WHERE gvsc.dimension_id = dim.dimension_id
AND dim.dimension_varchar_label = 'CUSTOMER'
AND gvsc.global_vs_combo_id = l_gvsc_id;
SELECT DISTINCT(message)
BULK COLLECT INTO v_msg_list
FROM fem_mp_process_ctl_t
WHERE req_id = l_request_id
AND status = 2;
FEM_MULTI_PROC_PKG.Delete_Data_Slices (
p_req_id => l_request_id);
| 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_request_id IN NUMBER
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,p_rule_obj_id IN NUMBER
,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_request_id
,p_object_id => p_rule_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_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);
,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_request_id IN NUMBER
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,p_rule_obj_id IN NUMBER
,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_request_id
,p_object_id => p_rule_obj_id
,p_exec_step => p_exe_step
,p_exec_status_code => p_exe_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);
,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 => 'Update the status of the step with execution status :'
||p_exe_status_code);
Update_Obj_Exec_Step_Status( p_request_id => p_request_id
,p_user_id => p_user_id
,p_login_id => p_login_id
,p_rule_obj_id => p_rule_obj_id
,p_exe_step => 'PROSP_IDENT'
,p_exe_status_code => p_exe_status_code );
Update_Nbr_Of_Output_Rows(p_request_id => p_request_id
,p_user_id => p_user_id
,p_login_id => p_login_id
,p_rule_obj_id => p_rule_obj_id
,p_num_output_rows => l_nbr_loaded_rows
,p_tbl_name => p_tbl_name
,p_stmt_type => g_insert );
Update_Nbr_Of_Input_Rows( p_request_id => p_request_id
,p_user_id => p_user_id
,p_last_update_login => p_login_id
,p_rule_obj_id => p_rule_obj_id
,p_num_of_input_rows => l_nbr_input_rows);
l_update_head_stmt LONG;
l_select_stmt LONG;
l_update_head_stmt := ' UPDATE FEM_CUSTOMERS_ATTR attr' ||
' SET ' ||
' attr.VARCHAR_ASSIGN_VALUE '||
' = ' ||
'fem_prospect_ident_s.NEXTVAL';
l_select_stmt := ' SELECT ' ||
' CUSTOMER_ID ' ||
' FROM FEM_CUSTOMERS_B FCB'||
' WHERE FCB.CUSTOMER_ID NOT IN ( '||
' SELECT DISTINCT CUSTOMER_ID '||
' FROM FEM_CUSTOMER_PROFIT FCP'||
' WHERE LEDGER_ID =' || p_ledger_id ||
' AND SOURCE_SYSTEM_CODE ='|| p_source_system_code ||
' AND cal_period_id = ' || p_cal_period_id ||
' AND dataset_code = ' || p_dataset_code ||
' AND DATA_AGGREGATION_TYPE_CODE =
''CUSTOMER_AGGREGATION''';
' SELECT att.ATTRIBUTE_ID '||
' FROM FEM_CUSTOMERS_ATTR att, '||
' FEM_DIM_ATTRIBUTES_B dim, '||
' FEM_DIMENSIONS_B xdim '||
' WHERE att.ATTRIBUTE_ID = dim.ATTRIBUTE_ID '||
' AND dim.dimension_id = xdim.dimension_id '||
' AND dim.ATTRIBUTE_VARCHAR_LABEL =
''FEM_PROSPECT_IDENT''' ||
' AND xdim.dimension_varchar_label = ''CUSTOMER'''||
') ';
l_select_stmt := l_select_stmt || ' AND {{data_slice}} ';
RETURN l_update_head_stmt || ' ' || l_where_stmt || ' ' || l_select_stmt || ' ' ||
l_sub_query_stmt;
| 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_request_id IN NUMBER
,p_user_id IN NUMBER
,p_last_update_login IN NUMBER
,p_rule_obj_id IN NUMBER
,p_num_of_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_request_id
,p_object_id => p_rule_obj_id
,p_num_of_input_rows => p_num_of_input_rows
,p_user_id => p_user_id
,p_last_update_login => p_last_update_login
,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;