[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_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
,p_order_by_clause OUT NOCOPY VARCHAR2 );
PROCEDURE Update_Nbr_Of_Input_Rows (
p_param_rec IN param_record
,p_num_input_rows IN NUMBER);
,p_select_col IN OUT NOCOPY LONG
,p_from_clause IN OUT NOCOPY LONG
,p_where_clause IN OUT NOCOPY LONG
,p_order_by_clause OUT NOCOPY VARCHAR2
);
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_FALSE
,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_FALSE
,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;
Update_Nbr_Of_Output_Rows(p_param_rec => p_param_rec
,p_num_output_rows => p_num_rows
,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_num_rows);
,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 => 'ALL'
,p_exe_status_code => p_exe_status_code );
l_update_flag BOOLEAN := FALSE;
SELECT condition_obj_id
,col_tmplt_obj_id
,hierarchy_obj_id
,aggregate_customer
,dimension_grp_id_from
,dimension_grp_id_to
INTO p_param_rec.cond_obj_id
,l_col_tmplt_obj_id
,l_hierarchy_obj_id
,p_param_rec.aggregate_customer
,l_dimension_grp_id_from
,l_dimension_grp_id_to
FROM pft_pprof_agg_rules
WHERE pprof_agg_obj_def_id = p_param_rec.crnt_proc_child_obj_defn_id;
,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_msg_text => 'Aggregate to customer: INSERT');
,p_stmt_type => g_insert);
,p_msg_text => 'Hierarchy Rollup: UPDATE');
,p_stmt_type => g_insert);
SELECT relative_dimension_group_seq
INTO l_rel_dim_group_seq_to
FROM fem_hier_dimension_grps
WHERE hierarchy_obj_id = l_hierarchy_obj_id
AND dimension_group_id = l_dimension_grp_id_to;
SELECT relative_dimension_group_seq
INTO l_rel_dim_group_seq_from
FROM fem_hier_dimension_grps
WHERE hierarchy_obj_id = l_hierarchy_obj_id
AND dimension_group_id = l_dimension_grp_id_from;
SELECT customer_id
BULK COLLECT INTO v_rollup_cust
FROM fem_customer_profit fcp,
((SELECT child_id
FROM fem_customers_hier a
WHERE hierarchy_obj_def_id = l_hier_object_def_id
AND parent_depth_num = l_rel_dim_group_seq_to
AND child_depth_num BETWEEN
l_rel_dim_group_seq_to AND (l_rel_dim_group_seq_from-1))
UNION (
SELECT child_id
FROM fem_customers_hier a
WHERE hierarchy_obj_def_id = l_hier_object_def_id
AND child_depth_num = l_rel_dim_group_seq_to)) hier_cust
WHERE hier_cust.child_id = fcp.customer_id
AND fcp.cal_period_id = p_param_rec.output_cal_period_id
AND fcp.ledger_id = p_param_rec.ledger_id
AND fcp.dataset_code = p_param_rec.output_dataset_code
AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
,p_msg_text => 'Update the Table Id of the customer records
which has to be updated');
l_update_flag := TRUE;
UPDATE fem_customer_profit
SET table_id = -999999
WHERE cal_period_id = p_param_rec.output_cal_period_id
AND ledger_id = p_param_rec.ledger_id
AND dataset_code = p_param_rec.output_dataset_code
AND source_system_code = p_param_rec.source_system_code
AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION'
AND customer_id = v_rollup_cust(i);
l_update_flag := FALSE;
SELECT customer_id
BULK COLLECT INTO v_rollup_cust
FROM fem_customer_profit fcp,
((SELECT child_id
FROM fem_customers_hier a
WHERE hierarchy_obj_def_id = l_hier_object_def_id
AND parent_depth_num = l_rel_dim_group_seq_to
AND child_depth_num = l_rel_dim_group_seq_from)
UNION (SELECT child_id
FROM fem_customers_hier a
WHERE hierarchy_obj_def_id = l_hier_object_def_id
AND child_depth_num = l_rel_dim_group_seq_from)) hier_cust
WHERE hier_cust.child_id = fcp.customer_id
AND fcp.cal_period_id = p_param_rec.output_cal_period_id
AND fcp.ledger_id = p_param_rec.ledger_id
AND fcp.dataset_code = p_param_rec.output_dataset_code
AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
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 1
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'
AND ROWNUM = 1;
IF (l_update_flag AND p_param_rec.aggregate_customer = 'N' ) THEN
fem_engines_pkg.tech_message(
p_severity => g_log_level_3
,p_module => G_BLOCK||'.'||l_api_name
,p_msg_text => 'Update the Created By Object Id and Request Id');
UPDATE fem_customer_profit fcp
SET (FCP.created_by_object_id
,FCP.created_by_request_id
,FCP.record_count) =
(SELECT created_by_object_id,created_by_request_id,record_count
FROM fem_customer_profit FCP
WHERE FCP.ledger_id = p_param_rec.ledger_id
AND FCP.source_system_code = p_param_rec.source_system_code
AND FCP.cal_period_id = p_param_rec.output_cal_period_id
AND FCP.dataset_code = p_param_rec.output_dataset_code
AND FCP.data_aggregation_type_code = 'CUSTOMER_AGGREGATION'
AND FCP.table_id = -999999
AND FCP.CUSTOMER_ID = v_rollup_cust(i))
WHERE FCP.ledger_id = p_param_rec.ledger_id
AND FCP.source_system_code = p_param_rec.source_system_code
AND FCP.cal_period_id = p_param_rec.output_cal_period_id
AND FCP.dataset_code = p_param_rec.output_dataset_code
AND FCP.data_aggregation_type_code = 'CUSTOMER_AGGREGATION'
AND FCP.table_id <> -999999
AND FCP.customer_id = v_rollup_cust(i);
DELETE FROM fem_customer_profit
WHERE table_id = -999999;
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 => NULL
,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);
,p_select_col => l_select_stmt
,p_from_clause => l_from_stmt
,p_where_clause => l_where_stmt
,p_order_by_clause => l_order_by_clause );
add_hierarchy_details(p_select_col => l_select_stmt
,p_from_clause => l_from_stmt
,p_where_clause => l_where_stmt
,p_order_by_clause => l_order_by_clause);
SELECT REPLACE(l_select_stmt,'TO_NUMBER(''-987654321'')','COUNT(1)')
INTO l_select_stmt
FROM dual;
RETURN l_insert_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt
|| ' ' || l_where_stmt || ' ' || l_order_by_clause;
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_FALSE
,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_FALSE
,p_request_id => p_param_rec.request_id
,p_object_id => p_param_rec.crnt_proc_child_obj_id
,p_errors_reported => 1
,p_errors_reprocessed => 0
,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_FALSE
,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_hierarchy_details(p_select_col IN OUT NOCOPY LONG,
p_from_clause IN OUT NOCOPY LONG,
p_where_clause IN OUT NOCOPY LONG,
p_order_by_clause OUT NOCOPY VARCHAR2 )
IS
l_api_name CONSTANT VARCHAR2(30) := 'add_hierarchy_details';
SELECT REPLACE(p_select_col,'{{{CUSTOMER_ID}}}',
'cust_hier_dump.customer_id' )
INTO p_select_col
FROM DUAL;
' , ( (SELECT child_id, parent_id customer_id ' ||
' FROM fem_customers_hier a ' ||
' WHERE hierarchy_obj_def_id = :1';
'(SELECT child_id, child_id customer_id ' ||
' FROM fem_customers_hier a ' ||
' WHERE hierarchy_obj_def_id = :5';
,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_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_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;
,p_select_col IN OUT NOCOPY LONG
,p_from_clause IN OUT NOCOPY LONG
,p_where_clause IN OUT NOCOPY LONG
,p_order_by_clause OUT NOCOPY VARCHAR2 )
IS
l_api_name CONSTANT VARCHAR2(30) := 'account_aggregation';
SELECT REPLACE(p_select_col,'{{{CUSTOMER_ID}}}',
p_src_alias||'.customer_id')
INTO p_select_col
FROM DUAL;