[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
);
FUNCTION Create_Pptile_Update_Stmt (
p_rule_obj_id IN NUMBER
,p_table_name IN VARCHAR2
,p_cal_period_id IN NUMBER
,p_effective_date IN VARCHAR2
,p_dataset_code IN NUMBER
,p_ledger_id IN NUMBER
,p_source_system_code IN NUMBER
,p_ds_where_clause IN LONG)
RETURN LONG;
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
);
l_insert_sql LONG;
l_update_sql LONG;
,p_msg_text => 'Building Update SQL');
l_update_sql := Create_Pptile_Update_Stmt(
p_rule_obj_id => p_rule_obj_id
,p_table_name => l_process_table
,p_cal_period_id => p_cal_period_id
,p_effective_date => p_effective_date
,p_dataset_code => p_output_dataset_code
,p_ledger_id => p_ledger_id
,p_source_system_code => p_source_system_code
,p_ds_where_clause => l_ds_where_clause);
,p_msg_text => 'Update Sql'|| l_update_sql);
EXECUTE IMMEDIATE l_update_sql;
,p_msg_text => 'UPDATE STATEMENT ERROR');
| 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;
,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 => 'CUST_PPTILE'
,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 => p_num_rows
,p_tbl_name => p_tbl_name
,p_stmt_type => g_update );
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 => p_num_rows);
| Create Profit Percentile Update Statement
|
| DESCRIPTION
| Creates the Bulk SQL for Profit Percentile
| (To Update Fem_Customer_Profit Table).
|
| SCOPE - PRIVATE
|
+============================================================================*/
FUNCTION Create_Pptile_Update_Stmt ( p_rule_obj_id IN NUMBER
,p_table_name IN VARCHAR2
,p_cal_period_id IN NUMBER
,p_effective_date IN VARCHAR2
,p_dataset_code IN NUMBER
,p_ledger_id IN NUMBER
,p_source_system_code IN NUMBER
,p_ds_where_clause IN LONG)
RETURN LONG IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_Pptile_Update_Stmt';
l_update_head_stmt LONG;
l_select_stmt LONG;
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;
l_update_head_stmt := ' UPDATE FEM_CUSTOMER_PROFIT FCP' ||
' SET (' ||
' FCP.PROFIT_PERCENTILE, '||
' FCP.PROFIT_DECILE, '||
' FCP.LAST_UPDATED_BY_OBJECT_ID, ' ||
' FCP.LAST_UPDATED_BY_REQUEST_ID ' ||
' ) = ';
l_select_stmt := ' ( SELECT '||
' PROFIT_PERCENTILE,' ||
' PROFIT_DECILE, ' ||
p_rule_obj_id || ' , ' ||
l_request_id ;
' ( SELECT BUS_REL_ID, CUSTOMER_ID, ' ||
' NTILE(100) OVER ' ||
' (PARTITION BY dimension_group_id ' ||
' ORDER BY PROFIT_CONTRIB ASC NULLS FIRST) ' ||
' AS PROFIT_PERCENTILE, ' ||
' NTILE(10) OVER ' ||
' (PARTITION BY dimension_group_id ' ||
' ORDER BY PROFIT_CONTRIB ASC NULLS FIRST) ' ||
' AS PROFIT_DECILE ' ||
' FROM (SELECT FCP.BUS_REL_ID,FCP.CUSTOMER_ID, ' ||
' DIMENSION_GROUP_ID, ' ||
' PROFIT_CONTRIB FROM FEM_CUSTOMERS_B FCB, ' ||
' FEM_CUSTOMER_PROFIT fcp ' ||
' WHERE FCP.LEDGER_ID = ' || p_ledger_id ||
' AND FCP.SOURCE_SYSTEM_CODE = ' || p_source_system_code ||
' AND DATA_AGGREGATION_TYPE_CODE = ' || '''CUSTOMER_AGGREGATION''' ||
' AND FCP.CUSTOMER_ID = FCB.CUSTOMER_ID' ||
' AND FCB.VALUE_SET_ID = ' || l_value_set_id;
RETURN l_update_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt
|| ' ' || l_where_stmt ;
END Create_Pptile_Update_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;