[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
);
PROCEDURE Register_Updated_Column(
p_request_id IN NUMBER
,p_object_id IN NUMBER
,p_user_id IN NUMBER
,p_last_update_login IN NUMBER
,p_table_name IN VARCHAR2
,p_statement_type IN VARCHAR2
,p_column_name IN VARCHAR2
);
'SELECT distinct r.created_by_request_id'||
',r.created_by_object_id '||
'FROM FEM_REGION_INFO r '||
'where r.ledger_id = :b_ledger_id '||
'and r.cal_period_id = :b_cal_period_id '||
'and r.dataset_code = :b_output_dataset_code '||
'and r.source_system_code = :b_source_system_code '||
'and r.dimension_group_id = :b_customer_level '||
'and not ('||
'r.created_by_request_id = :b_request_id '||
'and r.created_by_object_id = :b_rule_obj_id '||
' )'||
' and not exists ('||
' select 1 '||
' from fem_pl_chains c '||
' where c.request_id = :b_request_id '||
' and c.object_id = :b_rule_obj_id '||
' and c.source_created_by_request_id = r.created_by_request_id '||
' and c.source_created_by_object_id = r.created_by_object_id '||
' )';
'SELECT distinct cp.last_updated_by_request_id '||
',cp.created_by_object_id '||
'FROM FEM_CUSTOMER_PROFIT cp '||
'where cp.ledger_id = :b_ledger_id '||
'and cp.cal_period_id = :b_cal_period_id '||
'and cp.dataset_code = :b_output_dataset_code '||
'and cp.source_system_code = :b_source_system_code '||
'AND (SELECT customer_level FROM pft_pprof_calc_rules '||
'WHERE pprof_calc_obj_def_id = :b_rule_obj_defn_id) = :b_customer_level '||
'and not ( '||
'cp.last_updated_by_request_id = :b_request_id '||
'and cp.created_by_object_id = :b_rule_obj_id '||
' )'||
' and not exists ( '||
' select 1 '||
' from fem_pl_chains c '||
' where c.request_id = :b_request_id '||
' and c.object_id = :b_rule_obj_id '||
' and c.source_created_by_request_id = cp.last_updated_by_request_id '||
' and c.source_created_by_object_id = cp.created_by_object_id '||
' ) ';
SELECT COUNT(measure_type)
INTO l_measure_type
FROM pft_val_index_ranges
WHERE value_index_formula_id = p_value_index_formula_id
AND measure_type = 'REGION_COUNTING';
SELECT COUNT(measure_type)
INTO l_measure_type
FROM pft_val_index_ranges
WHERE value_index_formula_id = p_value_index_formula_id
AND measure_type = 'PROFIT_PERCENTILE';
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(product_id)
INTO l_product_id
FROM pft_val_index_counting
WHERE value_index_formula_id = p_value_index_formula_id;
,p_msg_text => 'Register update colmn:Value Index');
Register_Updated_Column( p_request_id => l_request_id
,p_object_id => p_rule_obj_id
,p_user_id => l_user_id
,p_last_update_login => l_login_id
,p_table_name => g_fem_customer_profit
,p_statement_type => g_update
,p_column_name => p_output_column);
SELECT relative_dimension_group_seq
INTO l_rel_dimension_grp_seq
FROM fem_hier_dimension_grps
WHERE dimension_group_id = p_customer_level
AND ROWNUM = 1;
,p_last_update_login => l_login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
,p_last_update_login => l_login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
l_created_by_request_id_tbl.DELETE;
l_created_by_object_id_tbl.DELETE;
SELECT dim_attr.attribute_id,ver.version_id
INTO l_attribute_id
,l_version_id
FROM fem_dim_attributes_b dim_attr,
fem_dimensions_b xdim,
fem_dim_attr_versions_b ver
WHERE dim_attr.dimension_id = xdim.dimension_id
AND dim_attr.attribute_id = ver.attribute_id
AND dim_attr.attribute_varchar_label = 'REGION_CODE'
AND xdim.dimension_varchar_label = 'CUSTOMER';
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);
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);
SELECT dim_attr.attribute_id
,ver.version_id
INTO l_attribute_id
,l_version_id
FROM fem_dim_attributes_b dim_attr,
fem_dimensions_b xdim,
fem_dim_attr_versions_b ver
WHERE dim_attr.dimension_id = xdim.dimension_id
AND dim_attr.attribute_id = ver.attribute_id
AND dim_attr.attribute_varchar_label = 'PRODUCT_ID'
AND xdim.dimension_varchar_label = 'CUSTOMER';
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 => '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 => 'VAL_IDX'
,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_output_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 => l_nbr_output_rows);
l_update_stmt LONG;
l_select_stmt LONG;
l_update_stmt := ' UPDATE fem_customer_profit fcp' ||
' SET fcp.' || p_output_column ||' = NVL('||
p_output_column || ',0) + ' ||
' ( SELECT NVL(factor_weight,0) ' ||
' FROM pft_val_index_ranges a, ';
l_select_stmt := ' ( SELECT region_pct_total_cust, ' ||
' cust.customer_id ' ||
' FROM fem_customers_b cust, ' ||
' fem_region_info fri, ' ||
' fem_customers_attr fca ';
' fcp.LAST_UPDATED_BY_REQUEST_ID = ' || l_request_id || ', ' ||
' fcp.LAST_UPDATED_BY_OBJECT_ID = ' || p_object_id ||
' WHERE fcp.cal_period_id = ' || p_cal_period_id ||
' AND fcp.dataset_code = ' || p_dataset_code||
' AND fcp.source_system_code = ' || p_source_system_code ||
' AND fcp.ledger_id = ' || p_ledger_id ||
' AND fcp.customer_id IN ( SELECT cust.customer_id '||
' FROM fem_customers_b cust, ' ||
' fem_region_info fri, ' ||
' fem_customers_attr fca ' ||
' WHERE cust.dimension_group_id = fri.dimension_group_id ' ||
' AND cust.value_set_id = ' || p_value_set_id ||
' AND fri.region_code = fca.number_assign_value ' ||
' AND fca.customer_id = cust.customer_id ' ||
' AND fca.attribute_id = ' || p_attribute_id ||
' AND fca.version_id = ' || p_version_id ||
' AND fri.cal_period_id = ' || p_cal_period_id ||
' AND fri.dataset_code = ' || p_dataset_code ||
' AND fri.source_system_code = ' || p_source_system_code ||
' AND fri.ledger_id = ' || p_ledger_id ||
' AND fri.dimension_group_id = ' || p_customer_level ||' ) ' ||
' AND fcp.data_aggregation_type_code = ' || '''CUSTOMER_AGGREGATION''';
RETURN l_update_stmt || ' ' || l_select_stmt || ' ' || l_where_stmt;
l_update_stmt LONG;
l_select_stmt LONG;
l_update_stmt := ' UPDATE fem_customer_profit fcp' ||
' SET fcp.' || p_output_column ||' = NVL('||
p_output_column || ',0) + ' ||
' ( SELECT NVL(factor_weight,0) ' ||
' FROM pft_val_index_ranges a, ';
l_select_stmt := ' (SELECT profit_percentile,cust.customer_id' ||
' FROM fem_customers_b cust, ' ||
' fem_customer_profit fcp ';
' fcp.LAST_UPDATED_BY_REQUEST_ID = ' || l_request_id || ', ' ||
' fcp.LAST_UPDATED_BY_OBJECT_ID = ' || p_object_id ||
' WHERE fcp.cal_period_id = ' || p_cal_period_id ||
' AND fcp.dataset_code = ' || p_dataset_code||
' AND fcp.source_system_code = ' || p_source_system_code ||
' AND fcp.ledger_id = ' || p_ledger_id ||
' AND fcp.customer_id IN ( SELECT cust.customer_id '||
' FROM fem_customers_b cust ' ||
' WHERE cust.dimension_group_id = ' || p_customer_level ||
' AND cust.value_set_id = ' || p_value_set_id || ' ) ' ||
' AND fcp.data_aggregation_type_code = ' || '''CUSTOMER_AGGREGATION''';
RETURN l_update_stmt || ' ' || l_select_stmt || ' ' || l_where_stmt;
l_update_stmt LONG;
l_select_stmt LONG;
l_update_stmt := ' UPDATE fem_customer_profit fcp' ||
' SET fcp.' || p_output_column ||' = NVL('||
p_output_column || ',0) + ' ||
' ( SELECT NVL(factor_weight,0) ' ||
' FROM pft_val_index_counting a, ';
l_select_stmt := ' (SELECT fca.dim_attribute_numeric_member product_id'||
' , cust.customer_id ' ||
' FROM fem_customers_b cust, ' ||
' fem_customers_attr fca';
' fcp.LAST_UPDATED_BY_REQUEST_ID = ' || l_request_id || ', ' ||
' fcp.LAST_UPDATED_BY_OBJECT_ID = ' || p_object_id ||
' WHERE fcp.cal_period_id = ' || p_cal_period_id ||
' AND fcp.dataset_code = ' || p_dataset_code||
' AND fcp.source_system_code = ' || p_source_system_code ||
' AND fcp.ledger_id = ' || p_ledger_id ||
' AND fcp.customer_id IN ( SELECT cust.customer_id '||
' FROM fem_customers_b cust, ' ||
' fem_customers_attr fca ' ||
' WHERE cust.dimension_group_id = ' || p_customer_level ||
' AND cust.value_set_id = ' || p_value_set_id ||
' AND fca.customer_id = cust.customer_id ' ||
' AND fca.attribute_id = ' || p_attribute_id ||
' AND fca.version_id = ' || p_version_id ||' ) ' ||
' AND fcp.data_aggregation_type_code = ' || '''CUSTOMER_AGGREGATION''';
RETURN l_update_stmt || ' ' || l_select_stmt || ' ' || l_where_stmt;
| Register_Updated_Column
|
| DESCRIPTION
| This procedure is used to register a column updated during object execution
|
| SCOPE - PRIVATE
|
+============================================================================*/
PROCEDURE Register_Updated_Column( p_request_id IN NUMBER
,p_object_id IN NUMBER
,p_user_id IN NUMBER
,p_last_update_login IN NUMBER
,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_request_id
,p_object_id => p_object_id
,p_table_name => p_table_name
,p_statement_type => p_statement_type
,p_column_name => p_column_name
,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);
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;
| 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;