[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_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 relative_dimension_group_seq
INTO l_dimension_grp_id
FROM fem_hier_dimension_grps
WHERE dimension_group_id = p_customer_level
AND ROWNUM = 1;
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 COUNT(*)
INTO l_total_customers
FROM fem_customers_b
WHERE value_set_id = l_value_set_id
AND dimension_group_id = p_customer_level;
SELECT dim_attr.attribute_id
INTO l_attribute_id
FROM fem_dim_attributes_b dim_attr,fem_dimensions_b xdim
WHERE dim_attr.dimension_id = xdim.dimension_id
AND dim_attr.attribute_varchar_label = 'REGION_CODE'
AND xdim.dimension_varchar_label = 'CUSTOMER';
SELECT COUNT(customer_id)
INTO l_cust_wo_rgn_code
FROM fem_customers_b
WHERE dimension_group_id = p_customer_level
AND value_set_id = l_value_set_id
AND customer_id NOT IN(SELECT customer_id
FROM fem_customers_attr
WHERE attribute_id = l_attribute_id);
SELECT COUNT( dimension_group_id )
INTO l_dim_grp_id
FROM fem_region_info
WHERE dimension_group_id = p_customer_level
AND ledger_id = p_ledger_id
AND cal_period_id = p_cal_period_id
AND dataset_code = p_output_dataset_code;
| 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 => 'No Rows returned by the Insert Statement');
,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 => 'RGN_CNT'
,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 => g_fem_region_info
,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 => p_num_rows);
l_insert_head_stmt LONG;
l_select_stmt LONG;
l_insert_head_stmt := ' INSERT INTO FEM_REGION_INFO ( ' ||
' CAL_PERIOD_ID, ' ||
' DATASET_CODE, ' ||
' DIMENSION_GROUP_ID, ' ||
' SOURCE_SYSTEM_CODE, ' ||
' REGION_CODE, ' ||
' LEDGER_ID, ' ||
' REGION_PCT_TOTAL_CUST, ' ||
' NUMBER_OF_CUSTOMERS, ' ||
' CREATED_BY_OBJECT_ID, ' ||
' CREATED_BY_REQUEST_ID, ' ||
' LAST_UPDATED_BY_OBJECT_ID, ' ||
' LAST_UPDATED_BY_REQUEST_ID ';
l_select_stmt := ' ) SELECT '||
p_cal_period_id || ' , ' ||
p_dataset_code || ' , ' ||
p_customer_level || ' , ' ||
p_source_system_code || ' , ' ||
'fca.number_assign_value, ' ||
p_ledger_id || ' , ' ||
' 100 * (COUNT(fca.number_assign_value)/'
|| p_total_customers || ') , ' ||
'COUNT(fca.number_assign_value)' || ' , ' ||
p_rule_obj_id || ' , ' ||
l_request_id || ' , ' ||
l_user_id ||' , ' ||
l_request_id;
' (' || ' SELECT dim_attr.attribute_id ' ||
' FROM fem_dim_attributes_b dim_attr, ' ||
' fem_dimensions_b xdim ' ||
' WHERE dim_attr.dimension_id = xdim.dimension_id'||
' AND dim_attr.attribute_varchar_label = ' ||
'''REGION_CODE''' ||
' AND xdim.dimension_varchar_label = ''CUSTOMER'''||
' )' || 'T1 ';
' SELECT customer_id ' ||
' FROM fem_customers_b ' ||
' WHERE dimension_group_id = ' ||
p_customer_level ||
' AND value_set_id = ' ||
p_value_set_id || ' ) ';
RETURN l_insert_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt
|| ' ' || l_where_stmt || ' ' || l_group_by_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;
l_insert_head_stmt LONG;
l_select_stmt LONG;
l_insert_head_stmt := ' INSERT INTO FEM_REGION_INFO ( ' ||
' CAL_PERIOD_ID, ' ||
' DATASET_CODE, ' ||
' DIMENSION_GROUP_ID, ' ||
' SOURCE_SYSTEM_CODE, ' ||
' REGION_CODE, ' ||
' LEDGER_ID, ' ||
' REGION_PCT_TOTAL_CUST, ' ||
' NUMBER_OF_CUSTOMERS, ' ||
' CREATED_BY_OBJECT_ID, ' ||
' CREATED_BY_REQUEST_ID, ' ||
' LAST_UPDATED_BY_OBJECT_ID, ' ||
' LAST_UPDATED_BY_REQUEST_ID ';
l_select_stmt := ' ) SELECT '||
p_cal_period_id || ' , ' ||
p_dataset_code || ' , ' ||
p_customer_level || ' , ' ||
p_source_system_code || ' , ' ||
' NULL, ' ||
p_ledger_id || ' , ' ||
' 100 * (' ||p_cust_wo_rgn_code || '/'
|| p_total_customers || ') , ' ||
p_cust_wo_rgn_code || ' , ' ||
p_rule_obj_id || ' , ' ||
l_request_id || ' , ' ||
l_user_id ||' , ' ||
l_request_id;
RETURN l_insert_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt;