[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_sql := 'SELECT '||p_trans_col_name
||' FROM '||p_vl_view_name
||' WHERE '||p_id_col_name||' = :id';
SELECT object_id
INTO v_object_id
FROM fem_object_definition_b
WHERE object_definition_id = p_object_definition_id;
SELECT t.data_edit_lock_flag
INTO v_data_edit_lock_flag
FROM fem_object_definition_b d,
fem_object_catalog_b o,
fem_object_types t
WHERE d.object_definition_id = p_object_definition_id
AND d.object_id = o.object_id
AND o.object_type_code = t.object_type_code;
SELECT COUNT(*)
INTO v_count
FROM fem_pl_object_defs
WHERE object_definition_id = p_object_definition_id;
SELECT min(r.effective_date), max(r.effective_date)
INTO v_rslts_start_date, v_rslts_end_date
FROM fem_pl_requests r, fem_pl_object_defs d
WHERE r.request_id = d.request_id
AND d.object_definition_id = p_object_definition_id;
SELECT COUNT(*) INTO v_count
FROM fem_object_definition_b
WHERE object_definition_id = p_object_definition_id
AND approval_status_code IN ('SUBMIT_APPROVAL','SUBMIT_DELETE');
PROCEDURE can_delete_object_def (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_object_definition_id IN NUMBER,
p_process_type IN NUMBER DEFAULT NULL,
p_calling_program IN VARCHAR2 DEFAULT NULL,
x_can_delete_obj_def OUT NOCOPY VARCHAR2) IS
-- ==========================================================================
-- Purpose
-- Checks to see if a rule (object) can be deleted or not.
-- Arguments
-- p_object_definition_id ID of the rule version being checked
-- p_process_type 1 indicates procedure called from a workflow
-- process. This parameter should only be
-- used by workflow code.
-- p_calling_program This parameter should only be used if this API is
-- being called by the "can_delete_object" API.
-- x_can_delete_obj_def Returns 'T' if the rule version can be deleted;
C_API_NAME CONSTANT VARCHAR2(30) := 'can_delete_object_def';
SELECT object_id, approval_status_code
INTO v_object_id, v_approval_status_code
FROM fem_object_definition_b
WHERE object_definition_id = p_object_definition_Id;
x_can_delete_obj_def := 'T';
IF (nvl(p_calling_program,'X') <> 'can_delete_object') THEN
SELECT COUNT(*)
INTO v_num_of_definitions
FROM fem_object_definition_b
WHERE object_id = v_object_id
AND old_approved_copy_flag = 'N';
(v_approval_status_code = 'SUBMIT_DELETE')) THEN
null;
x_can_delete_obj_def := 'T';
p_msg_text => 'End. X_CAN_DELETE_OBJ_DEF:'||x_can_delete_obj_def);
x_can_delete_obj_def := 'F';
x_can_delete_obj_def := 'F';
x_can_delete_obj_def := 'F';
x_can_delete_obj_def := 'F';
x_can_delete_obj_def := 'F';
END can_delete_object_def;
PROCEDURE can_delete_object_def (
p_object_definition_id IN NUMBER,
x_can_delete_obj_def OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_process_type IN NUMBER DEFAULT NULL,
p_calling_program IN VARCHAR2 DEFAULT NULL) IS
-- ==========================================================================
-- API signature kept for backward compatibility. It simply calls the
-- can_delete_object_def that follows the FND API standards.
-- ==========================================================================
v_return_status VARCHAR2(1);
can_delete_object_def (
p_api_version => 1.0,
x_return_status => v_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_definition_id => p_object_definition_id,
p_process_type => p_process_type,
p_calling_program => p_calling_program,
x_can_delete_obj_def => x_can_delete_obj_def);
END can_delete_object_def;
PROCEDURE can_delete_object (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_object_id IN NUMBER,
p_process_type IN NUMBER DEFAULT NULL,
x_can_delete_obj OUT NOCOPY VARCHAR2) IS
-- ==========================================================================
-- Purpose
-- Checks to see if a rule (object) can be deleted or not.
-- Arguments
-- p_object_id ID of the rule being checked
-- p_process_type 1 indicates procedure called from a workflow process.
-- This parameter should only be used by workflow code.
-- x_can_delete_obj Returns 'T' if rule can be deleted; 'F' otherwise.
SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = p_object_id
AND old_approved_copy_flag = 'N';
SELECT object_definition_id
FROM fem_object_dependencies
WHERE required_object_id = p_object_id;
C_API_NAME CONSTANT VARCHAR2(30) := 'can_delete_object';
v_can_delete_object_def VARCHAR2(1);
can_delete_object_def(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_encoded => p_encoded,
x_return_status => v_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_definition_id => adef.object_definition_id,
p_process_type => p_process_type,
p_calling_program => 'can_delete_object',
x_can_delete_obj_def => v_can_delete_object_def);
IF (v_can_delete_object_def = 'F') THEN
v_obj_def_id := adef.object_definition_id;
x_can_delete_obj := 'T';
p_msg_text => 'End. X_CAN_DELETE_OBJ:'||x_can_delete_obj);
p_msg_name => 'FEM_PL_CANNOT_DELETE_DEF_ERR',
p_token1 => 'OBJECT_NAME',
p_value1 => v_obj_name,
p_token2 => 'OBJECT_DEF_NAME',
p_value2 => v_obj_def_name);
x_can_delete_obj := 'F';
x_can_delete_obj := 'F';
SELECT c.object_id, c.object_name
INTO v_dep_obj_id, v_dep_obj_name
FROM fem_object_catalog_vl c, fem_object_definition_b d
WHERE c.object_id = d.object_id
AND d.object_definition_id = v_obj_def_id;
x_can_delete_obj := 'F';
x_can_delete_obj := 'd';
END can_delete_object;
PROCEDURE can_delete_object (
p_object_id IN NUMBER,
p_process_type IN NUMBER DEFAULT NULL,
x_can_delete_obj OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
-- ==========================================================================
-- API signature kept for backward compatibility. It simply calls the
-- can_delete_object that follows the FND API standards.
-- ==========================================================================
v_return_status VARCHAR2(1);
can_delete_object (
p_api_version => 1.0,
x_return_status => v_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_id => p_object_id,
p_process_type => p_process_type,
x_can_delete_obj => x_can_delete_obj);
END can_delete_object;
SELECT t.executable_lock_flag, o.object_type_code
INTO v_executable_lock_flag, v_object_type_code
FROM fem_object_types t, fem_object_catalog_b o
WHERE o.object_id = p_object_id
AND o.object_type_code = t.object_type_code;
SELECT DECODE(COUNT(*),1,'RESTART','NORMAL') INTO x_exec_state
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = v_request_id
AND r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code = 'RUNNING';
p_last_update_login IN NUMBER,
p_exec_mode_code IN VARCHAR2 DEFAULT NULL,
x_exec_state OUT NOCOPY VARCHAR2,
x_prev_request_id OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
-- ==========================================================================
-- NOTE: The p_commit flag is currently ignored to ensure that the
-- exclusive lock on FEM_PL_OBJECT_EXECUTIONS is always released at
-- the end of the procedure. Bug# 3981986
-- ==========================================================================
-- x_return_status returns: S=successful, E=error, U=unexpected error.
-- ==========================================================================
--BEGIN register_object_execution
-- Acquire table lock (Wait until lock available)
-- IF execution lock exists THEN
-- Rollback to release exclusive table lock.
-- post message CANNOT_EXECUTE_LOCKED_OBJ;
SELECT r.request_id
FROM fem_pl_requests r, fem_pl_object_executions pl
WHERE pl.object_id = p_object_id
AND pl.request_id = r.request_id
AND r.ledger_id = v_ledger_id
AND r.cal_period_Id = v_cal_period_id
AND r.output_dataset_code = v_output_dataset_code
AND r.source_system_code = v_source_system_code
AND r.dimension_id = v_dimension_id
AND r.table_name = v_table_name
AND r.hierarchy_name = v_hierarchy_name;
SELECT ledger_id, cal_period_id, output_dataset_code, source_system_code,
dimension_id, table_Name, exec_mode_code, hierarchy_name INTO
v_ledger_id, v_cal_period_id, v_output_dataset_code,
v_source_system_code, v_dimension_id, v_table_Name,
v_exec_mode_code, v_hierarchy_name
FROM fem_pl_requests
WHERE request_id = p_request_id;
SELECT undo_flag INTO v_undo_flag
FROM fem_object_catalog_b o, fem_object_types t
WHERE o.object_id = p_object_id
AND o.object_type_code = t.object_type_code;
SELECT object_type_code
INTO v_object_type_code
FROM fem_object_catalog_b
WHERE object_id = p_object_id;
UPDATE fem_pl_object_executions SET display_flag = 'N'
WHERE object_id = p_object_id
AND display_flag = 'Y'
AND request_id IN
(SELECT r.request_id FROM fem_pl_requests r, fem_pl_requests a
WHERE a.request_id = p_request_id
AND r.request_id <> p_request_id
AND a.cal_period_id = r.cal_period_id
AND a.ledger_id = r.ledger_id
AND a.output_dataset_code = r.output_dataset_code);
UPDATE fem_pl_object_executions SET display_flag = 'N'
WHERE object_id = p_object_id
AND request_id <> p_request_id;
INSERT INTO fem_pl_object_executions (request_id, object_id,
exec_object_definition_id, event_order, display_flag, exec_status_code,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
VALUES (p_request_id, p_object_id, p_exec_object_definition_id,
fem_event_order_seq.NEXTVAL, v_display_flag,'RUNNING',p_user_id, SYSDATE,
p_user_id, SYSDATE, p_last_update_login);
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
p_last_update_login IN NUMBER,
p_program_id IN NUMBER,
p_program_login_id IN NUMBER,
p_program_application_id IN NUMBER,
p_exec_mode_code IN VARCHAR2 DEFAULT NULL,
p_dimension_id IN NUMBER DEFAULT NULL,
p_table_name IN VARCHAR2 DEFAULT NULL,
p_hierarchy_name IN VARCHAR2 DEFAULT NULL,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
-- ==========================================================================
-- x_return_status returns: S=successful, E=error, U=unexpected error.
-- ==========================================================================
--BEGIN register request
-- Insert row into FEM_PL_REQUESTS.
-- IF request already exists in fem_pl_requests THEN
-- Set p_request_id = existing request ID;
INSERT INTO fem_pl_requests (request_id, exec_status_code,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, program_id, program_login_id,
program_application_id, rule_set_obj_def_id, rule_set_name,
effective_date, cal_period_id, ledger_id, dataset_io_obj_def_id,
output_dataset_code, source_system_code,exec_mode_code, dimension_id,
table_name, hierarchy_name)
VALUES (p_request_id, 'RUNNING',
p_user_id, sysdate, p_user_id, sysdate,
p_last_update_login, p_program_id, p_program_login_id,
p_program_application_id, p_rule_set_obj_def_id, p_rule_set_name,
p_effective_date, p_cal_period_id, p_ledger_id, p_dataset_io_obj_def_id,
p_output_dataset_code, p_source_system_code,p_exec_mode_code, p_dimension_id,
p_table_name, p_hierarchy_name);
SELECT count(*) INTO v_count
FROM fem_pl_object_executions
WHERE request_id = p_request_id;
DELETE fem_pl_requests WHERE request_id = p_request_id;
PROCEDURE update_request_status (p_api_version IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_request_id IN NUMBER,
p_exec_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_request_status';
SAVEPOINT update_request_status_pub;
UPDATE fem_pl_requests SET exec_status_code = p_exec_status_code,
last_updated_by = p_user_id, last_update_date = sysdate,
last_update_login = p_last_update_login
WHERE request_id = p_request_id;
ROLLBACK TO update_request_status_pub;
END update_request_status;
PROCEDURE update_obj_exec_status (p_api_version IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_request_id IN NUMBER,
p_object_id IN NUMBER,
p_exec_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_obj_exec_status';
SAVEPOINT update_obj_exec_status_pub;
UPDATE fem_pl_object_executions SET exec_status_code = p_exec_status_code,
last_updated_by = p_user_id, last_update_date = sysdate,
last_update_login = p_last_update_login
WHERE request_id = p_request_id
AND object_id = p_object_id;
ROLLBACK TO update_obj_exec_status_pub;
END update_obj_exec_status;
PROCEDURE update_obj_exec_errors (p_api_version IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_request_id IN NUMBER,
p_object_id IN NUMBER,
p_errors_reported IN NUMBER,
p_errors_reprocessed IN NUMBER,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_obj_exec_errors';
SAVEPOINT update_obj_exec_errors_pub;
UPDATE fem_pl_object_executions
SET errors_reported = p_errors_reported,
errors_reprocessed=p_errors_reprocessed,
last_updated_by = p_user_id, last_update_date = sysdate,
last_update_login = p_last_update_login
WHERE request_id = p_request_id
AND object_id = p_object_id;
ROLLBACK TO update_obj_exec_errors_pub;
END update_obj_exec_errors;
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'register_object_def';
INSERT INTO fem_pl_object_defs (request_id, object_id, object_definition_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT
request_id, object_id, p_object_definition_id,
p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'register_dependent_objdefs';
SELECT D.required_object_id
FROM fem_object_dependencies D,
fem_object_definition_b B
WHERE D.required_object_id = B.object_id
AND B.effective_start_date <= p_effective_date
AND B.effective_end_date >= p_effective_date
AND B.old_approved_copy_flag = 'N'
AND B.approval_status_code NOT IN ('SUBMIT_APPROVAL','SUBMIT_DELETE')
START WITH D.object_definition_id = p_exec_object_definition_id
CONNECT BY PRIOR B.object_definition_id = D.object_definition_id;
SELECT object_definition_id INTO v_object_definition_id
FROM fem_object_definition_b
WHERE object_id = a_dependent_objdef.required_object_id
AND effective_start_date <= p_effective_date
AND effective_end_date >= p_effective_date
AND old_approved_copy_flag = 'N'
AND approval_status_code NOT IN ('SUBMIT_APPROVAL','SUBMIT_DELETE');
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'register_table';
INSERT INTO fem_pl_tables (request_id, object_id, table_name,
statement_type, num_of_output_rows,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT
request_id, object_id, p_table_name,
p_statement_type, p_num_of_output_rows,
p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
PROCEDURE update_num_of_output_rows (p_api_version IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_request_id IN NUMBER,
p_object_id IN NUMBER,
p_table_name IN VARCHAR2,
p_statement_type IN VARCHAR2,
p_num_of_output_rows IN NUMBER,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_num_of_output_rows';
SAVEPOINT update_num_of_output_rows_pub;
UPDATE fem_pl_tables SET num_of_output_rows = p_num_of_output_rows,
last_updated_by = p_user_id, last_update_date = sysdate,
last_update_login = p_last_update_login
WHERE request_id = p_request_id
AND object_id = p_object_id
AND table_name = p_table_name
AND statement_type = p_statement_type;
SELECT object_type_code
INTO v_object_type_code
FROM fem_object_catalog_b
WHERE object_id = p_object_id;
UPDATE fem_pl_object_executions
SET display_flag = 'Y'
WHERE request_id = p_request_id
AND object_id = p_object_id
AND display_flag <> 'Y';
UPDATE fem_pl_object_executions
SET display_flag = 'N'
WHERE object_id = p_object_id
AND display_flag = 'Y'
AND request_id IN
(SELECT r1.request_id FROM fem_pl_requests r1, fem_pl_requests r2
WHERE r2.request_id = p_request_id
AND r1.request_id <> p_request_id
AND r2.cal_period_id = r1.cal_period_id
AND r2.ledger_id = r1.ledger_id
AND r2.output_dataset_code = r1.output_dataset_code);
UPDATE fem_pl_object_executions
SET display_flag = 'N'
WHERE object_id = p_object_id
AND display_flag = 'Y'
AND request_id IN
(SELECT r1.request_id FROM fem_pl_requests r1, fem_pl_requests r2
WHERE r2.request_id = p_request_id
AND r1.request_id <> p_request_id
AND r2.cal_period_id = r1.cal_period_id
AND r2.ledger_id = r1.ledger_id
AND r2.output_dataset_code = r1.output_dataset_code
AND r2.source_system_code = r1.source_system_code);
ROLLBACK TO update_num_of_output_rows_pub;
END update_num_of_output_rows;
PROCEDURE register_updated_column (p_api_version IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_request_id IN NUMBER,
p_object_id IN NUMBER,
p_table_name IN VARCHAR2,
p_statement_type IN VARCHAR2,
p_column_name IN VARCHAR2,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'register_updated_column';
SAVEPOINT register_updated_column_pub;
INSERT INTO fem_pl_tab_updated_cols (request_id, object_id, table_name,
statement_type, column_name,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT
request_id, object_id, table_name,
statement_type, p_column_name,
p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
FROM fem_pl_tables
WHERE request_id = p_request_id
AND object_id = p_object_id
AND table_name = p_table_name
AND statement_type = p_statement_type;
ROLLBACK TO register_updated_column_pub;
p_msg_text => 'End. Updated column already registered. X_RETURN_STATUS:'||x_return_status);
ROLLBACK TO register_updated_column_pub;
END register_updated_column;
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'register_chain';
INSERT INTO fem_pl_chains (request_id, object_id,
source_created_by_request_id, source_created_by_object_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT
request_id, object_id,
p_source_created_by_request_id, p_source_created_by_object_id,
p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'register_temp_object';
INSERT INTO fem_pl_temp_objects (request_id, object_id, object_type,
object_name,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT
request_id, object_id, p_object_type,p_object_name,
p_user_id, sysdate, p_user_id, sysdate,p_last_update_login
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
PROCEDURE update_num_of_input_rows (p_api_version IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_request_id IN NUMBER,
p_object_id IN NUMBER,
p_num_of_input_rows IN NUMBER,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_num_of_input_rows';
SAVEPOINT update_num_of_input_rows_pub;
UPDATE fem_pl_object_executions SET num_of_input_rows = p_num_of_input_rows,
last_updated_by = p_user_id, last_update_date = sysdate,
last_update_login = p_last_update_login
WHERE request_id = p_request_id
AND object_id = p_object_id;
ROLLBACK TO update_num_of_input_rows_pub;
END update_num_of_input_rows;
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'register_obj_exec_step';
INSERT INTO fem_pl_obj_exec_steps (request_id, object_id, exec_step,
exec_status_code, created_by, creation_date, last_updated_by,
last_update_date, last_update_login)
SELECT
request_id, object_id, p_exec_step, p_exec_status_code,
p_user_id, sysdate, p_user_id, sysdate, p_last_update_login
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_obj_exec_steps
WHERE request_id = p_request_id
AND object_id = p_object_id
AND exec_step = p_exec_step;
DELETE fem_pl_obj_exec_steps
WHERE request_id = p_request_id
AND object_id = p_object_id;
PROCEDURE update_obj_exec_step_status (p_api_version IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_request_id IN NUMBER,
p_object_id IN NUMBER,
p_exec_step IN VARCHAR2,
p_exec_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_obj_exec_step_status';
SAVEPOINT update_obj_exec_step_statu_pub;
UPDATE fem_pl_obj_exec_steps SET exec_status_code = p_exec_status_code,
last_updated_by = p_user_id, last_update_date = sysdate,
last_update_login = p_last_update_login
WHERE request_id = p_request_id
AND object_id = p_object_id
AND exec_step = p_exec_step;
ROLLBACK TO update_obj_exec_step_statu_pub;
END update_obj_exec_step_status;
v_last_update_login NUMBER := FND_GLOBAL.Login_Id;
SELECT always_rerunnable_flag INTO v_always_rerunnable_flag
FROM fem_object_types t, fem_object_catalog_b o
WHERE o.object_id = p_object_id
AND o.object_type_code = t.object_type_code;
UPDATE fem_pl_requests SET exec_status_code = v_error_status,
last_updated_by = v_user_id, last_update_date = sysdate,
last_update_login = v_last_update_login
WHERE request_id = p_request_id
AND exec_status_code = 'RUNNING';
UPDATE fem_pl_object_executions SET exec_status_code = v_error_status,
last_updated_by = v_user_id, last_update_date = sysdate,
last_update_login = v_last_update_login
WHERE request_id = p_request_id
AND exec_status_code = 'RUNNING';
UPDATE fem_pl_requests SET exec_status_code = 'SUCCESS',
last_updated_by = v_user_id, last_update_date = sysdate,
last_update_login = v_last_update_login
WHERE request_id = p_request_id;
UPDATE fem_pl_object_executions SET exec_status_code = 'SUCCESS',
last_updated_by = v_user_id, last_update_date = sysdate,
last_update_login = v_last_update_login
WHERE request_id = p_request_id
AND exec_status_code = 'RUNNING';
ELSIF v_drphase = 'COMPLETE' AND v_dstatus IN ('ERROR','CANCELLED','TERMINATED','DELETED') THEN
UPDATE fem_pl_requests SET exec_status_code =
DECODE(v_dstatus,'ERROR',v_error_status,v_cancelled_status),
last_updated_by = v_user_id, last_update_date = sysdate,
last_update_login = v_last_update_login
WHERE request_id = p_request_id;
UPDATE fem_pl_object_executions SET exec_status_code =
DECODE(v_dstatus,'ERROR',v_error_status,v_cancelled_status),
last_updated_by = v_user_id, last_update_date = sysdate,
last_update_login = v_last_update_login
WHERE request_id = p_request_id
AND exec_status_code = 'RUNNING';
SELECT r.request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code;
SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = v_request_id
AND r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND o.exec_status_code NOT IN ('CANCELLED_RERUN','ERROR_RERUN');
SELECT MAX(r.request_id) INTO x_prev_request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND o.exec_status_code IN ('CANCELLED_RERUN','ERROR_RERUN');
SELECT r.request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = v_request_id
AND r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code NOT IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
SELECT MAX(r.request_id) INTO x_prev_request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
SELECT r.request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND r.source_system_code = p_source_system_code
AND r.table_name = p_table_name
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.table_name = p_table_name
AND r.output_dataset_code = p_output_dataset_code
AND r.source_system_code = p_source_system_code;
SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = v_request_id
AND r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.table_name = p_table_name
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND r.source_system_code = p_source_system_code
AND r.table_name = p_table_name
AND o.exec_status_code NOT IN ('CANCELLED_RERUN','ERROR_RERUN');
SELECT MAX(r.request_id) INTO x_prev_request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND r.source_system_code = p_source_system_code
AND r.table_name = p_table_name
AND o.exec_status_code IN ('CANCELLED_RERUN','ERROR_RERUN');
SELECT r.request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.hierarchy_name = p_hierarchy_name
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.hierarchy_name = p_hierarchy_name
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = v_request_id
AND r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.hierarchy_name = p_hierarchy_name
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.hierarchy_name = p_hierarchy_name
AND o.exec_status_code NOT IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
SELECT MAX(r.request_id) INTO x_prev_request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.hierarchy_name = p_hierarchy_name
AND o.exec_status_code IN ('SUCCESS','CANCELLED_RERUN','ERROR_RERUN');
SELECT r.request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = v_request_id
AND r.request_id = o.request_id
AND o.object_id = p_object_id
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_rerun
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND o.exec_status_code NOT IN ('CANCELLED_RERUN','ERROR_RERUN','SUCCESS');
SELECT MAX(r.request_id) INTO x_prev_request_id
FROM fem_pl_object_executions o, fem_pl_requests r
WHERE r.request_id = o.request_id
AND o.object_id = p_object_id
AND r.cal_period_id = p_cal_period_id
AND r.ledger_id = p_ledger_id
AND r.output_dataset_code = p_output_dataset_code
AND o.exec_status_code IN ('CANCELLED_RERUN','ERROR_RERUN','SUCCESS');
SELECT o.request_id
FROM fem_pl_object_executions o
WHERE o.exec_object_definition_id = p_exec_object_definition_id
AND o.exec_status_code = 'RUNNING';
SELECT DECODE(COUNT(*),0,'T','F') INTO v_normal_run
FROM fem_pl_object_executions o
WHERE o.exec_object_definition_id = p_exec_object_definition_id;
SELECT DECODE(COUNT(*),1,'T','F') INTO v_restart
FROM fem_pl_object_executions o
WHERE o.request_id = v_request_id
AND o.exec_object_definition_id = p_exec_object_definition_id
AND o.exec_status_code = 'RUNNING';
SELECT request_id, object_id
FROM fem_pl_chains
WHERE source_created_by_request_id = p_request_id
AND source_created_by_object_id = p_object_id;
SELECT exec_status_code
INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
SELECT exec_status_code
INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
SELECT object_type_code
INTO v_object_type_code
FROM fem_object_catalog_b
WHERE object_id = p_object_id;