[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
e_cannot_delete_object EXCEPTION;
e_cannot_delete_execution_log EXCEPTION;
PROCEDURE delete_execution_log (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_request_id IN NUMBER,
p_object_id IN NUMBER);
SELECT nvl(display_flag,'Y')
INTO v_display_flag
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
pv_parameter_list.DELETE;
SELECT cal_period_id, ledger_id, output_dataset_code,
creation_date, source_system_code
INTO v_cal_period_id, v_ledger_id, v_output_dataset_code,
v_object_execution_date, v_source_system_code
FROM fem_pl_requests
WHERE request_id = p_request_id;
SELECT object_name
INTO v_object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
SELECT calendar_id, cal_period_name
INTO v_calendar_id, v_cal_period_name
FROM fem_cal_periods_vl
WHERE cal_period_id = v_cal_period_id;
SELECT dataset_display_code, dataset_name
INTO v_dataset_display_code, v_dataset_name
FROM fem_datasets_vl
WHERE dataset_code = v_output_dataset_code;
SELECT ledger_display_code, ledger_name
INTO v_ledger_display_code, v_ledger_name
FROM fem_ledgers_vl
WHERE ledger_id = v_ledger_id;
SELECT source_system_display_code, source_system_name
INTO v_source_system_display_code, v_source_system_name
FROM fem_source_systems_vl
WHERE source_system_code = v_source_system_code;
SELECT calendar_display_code, calendar_name
INTO v_calendar_display_code, v_calendar_name
FROM fem_calendars_vl
WHERE calendar_id = v_calendar_id;
PROCEDURE delete_execution_log (x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_request_id IN NUMBER,
p_object_id IN NUMBER) AS
-- ============================================================================
-- PRIVATE
-- This procedure removes an execution log from the FEM_PL_xxx tables. It
-- drops all temporary objects created by the object execution whose log is
-- being removed, then deletes the execution log from the FEM_PL_xxx tables.
-- ============================================================================
c_api_name CONSTANT VARCHAR2(30) := 'delete_execution_log';
SELECT object_name, object_type
FROM fem_pl_temp_objects
WHERE request_id = p_request_id
AND object_id = p_object_id
ORDER BY object_type, object_name;
SAVEPOINT delete_execution_log_pub;
SELECT object_type_code
INTO v_object_type_code
FROM fem_object_catalog_b
WHERE object_id = p_object_id;
SELECT object_id
INTO v_pb_object_id
FROM fem_objdef_helper_rules
WHERE helper_object_id = p_object_id
AND helper_object_type_code = 'MAPPING_PREVIEW';
p_msg_text => 'STEP 2: Delete object execution from process lock tables (FEM_PL_xxxx).');
DELETE fem_pl_temp_objects
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_object_defs
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';
DELETE fem_pl_obj_exec_steps
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_tab_updated_cols
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_tables
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_chains
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_object_defs
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_pl_requests
WHERE request_id = p_request_id
AND request_id NOT IN
(select request_id FROM fem_pl_object_executions);
ROLLBACK TO delete_execution_log_pub;
ROLLBACK TO delete_execution_log_pub;
END delete_execution_log;
INSERT INTO fem_ud_lists (undo_list_obj_def_id ,include_dependencies_flag ,
ignore_dependency_errs_flag,execution_date,
object_version_number,created_by,creation_date,
last_updated_by,last_update_date,last_update_login)
VALUES(x_undo_list_obj_def_id ,p_include_dependencies_flag ,
p_ignore_dependency_errs_flag,NVL(p_execution_date,sysdate)
,1,pv_apps_user_id,sysdate,pv_apps_user_id,sysdate,pv_login_id);
PROCEDURE delete_undo_list (x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_undo_list_obj_id IN NUMBER) AS
-- ============================================================================
-- PUBLIC
-- This procedure is used to delete an undo list.
-- ============================================================================
c_api_name CONSTANT VARCHAR2(30) := 'delete_undo_list';
SAVEPOINT delete_undo_list_pub;
SELECT object_definition_id
INTO v_undo_list_obj_def_id
FROM fem_object_catalog_b o, fem_object_definition_b d
WHERE o.object_id = p_undo_list_obj_id
AND o.object_type_code = 'UNDO'
AND o.object_id = d.object_id;
p_msg_text => 'V02: Check to see if user list may be deleted.');
SELECT DECODE(count(*),0,'Y','N') INTO v_undo_list_exec_successfully
FROM (select exec_status_code
from fem_ud_list_candidates
where undo_list_obj_def_id = v_undo_list_obj_def_id
and exec_status_code IS NOT NULL
and exec_status_code <> 'SUCCESS'
UNION
select exec_status_code
from fem_ud_list_dependents
where undo_list_obj_def_id = v_undo_list_obj_def_id
and exec_status_code IS NOT NULL
and exec_status_code <> 'SUCCESS'
UNION
select exec_status_code
from fem_ud_lists
where undo_list_obj_def_id = v_undo_list_obj_def_id
and exec_status_code IS NOT NULL
and exec_status_code <> 'SUCCESS');
fem_object_catalog_util_pkg.delete_object(p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_object_id => p_undo_list_obj_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
DELETE fem_ud_list_dependents
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id;
DELETE fem_ud_list_candidates
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id;
DELETE fem_ud_lists
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id;
RAISE e_cannot_delete_object;
ROLLBACK TO delete_undo_list_pub;
WHEN e_cannot_delete_object THEN
ROLLBACK TO delete_undo_list_pub;
p_msg_name => 'FEM_CANNOT_DELETE_OBJ_ERR',p_token1 => 'OBJECT',
p_value1 => p_undo_list_obj_id, p_trans1 => 'N');
ROLLBACK TO delete_undo_list_pub;
ROLLBACK TO delete_undo_list_pub;
END delete_undo_list;
SELECT pl.request_id, pl.object_id
FROM fem_pl_object_executions pl, fem_object_catalog_b o, fem_pl_requests r1, fem_pl_requests r2
WHERE pl.object_id = o.object_id
AND o.object_id = p_object_id
AND o.object_type_code IN ('OGL_INTG_BAL_RULE','XGL_INTEGRATION',
'RCM_PROCESS_RULE','TP_PROCESS_RULE',
'SOURCE_DATA_LOADER','DATAX_LOADER')
AND pl.request_id = r1.request_id
AND r1.request_id <> p_request_id
AND r2.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
OR o.object_type_code IN ('OGL_INTG_BAL_RULE','XGL_INTEGRATION',
'RCM_PROCESS_RULE','TP_PROCESS_RULE'));
SELECT count(*) INTO v_count
FROM fem_user_folders u, fem_object_catalog_b o
WHERE o.object_id = p_object_id
AND o.folder_id = u.folder_id
AND u.user_id = pv_apps_user_id;
SELECT DECODE(count(*),0,'N','Y') INTO v_undo_list_ever_executed
FROM (select exec_status_code
from fem_ud_list_candidates
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL
UNION
select exec_status_code
from fem_ud_list_dependents
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL
UNION
select exec_status_code
from fem_ud_lists
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL);
SELECT include_dependencies_flag, ignore_dependency_errs_flag,
DECODE(include_dependencies_flag,'Y','ALL','UPDATE')
INTO v_include_dependencies_flag, v_ignore_dependency_errs_flag,v_dependency_type
FROM fem_ud_lists
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id;
SELECT count(*) INTO v_count
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;
INSERT INTO fem_ud_list_candidates (undo_list_obj_def_id,
object_id, request_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
VALUES (p_undo_list_obj_def_id, p_object_id, p_request_id,
1, pv_apps_user_id, sysdate, pv_apps_user_id, sysdate, pv_login_id);
INSERT INTO fem_ud_list_candidates (undo_list_obj_def_id,
object_id, request_id, object_version_number, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
VALUES (p_undo_list_obj_def_id, an_integration_load_exec.object_id,
an_integration_load_exec.request_id, 1, pv_apps_user_id, sysdate,
pv_apps_user_id, sysdate, pv_login_id);
SELECT display_name
INTO v_obj_def_name
FROM fem_object_definition_vl
WHERE object_definition_id = p_undo_list_obj_def_id;
SELECT object_name
INTO v_object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
SELECT c.request_id, c.object_id
FROM fem_ud_list_candidates c, fem_object_catalog_b o, fem_pl_requests r1, fem_pl_requests r2
WHERE c.object_id = o.object_id
AND o.object_type_code IN ('OGL_INTG_BAL_RULE','XGL_INTEGRATION',
'RCM_PROCESS_RULE','TP_PROCESS_RULE',
'SOURCE_DATA_LOADER','DATAX_LOADER')
AND c.request_id = r1.request_id
AND r1.request_id <> p_request_id
AND r2.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
OR o.object_type_code IN ('OGL_INTG_BAL_RULE','XGL_INTEGRATION',
'RCM_PROCESS_RULE','TP_PROCESS_RULE'));
SELECT DECODE(count(*),0,'N','Y') INTO v_undo_list_ever_executed
FROM (select exec_status_code
from fem_ud_list_candidates
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL
UNION
select exec_status_code
from fem_ud_list_dependents
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL
UNION
select exec_status_code
from fem_ud_lists
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL);
DELETE fem_ud_list_candidates
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_ud_list_dependents
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_ud_list_candidates
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = an_integration_load_exec.request_id
AND object_id = an_integration_load_exec.object_id;
DELETE fem_ud_list_dependents
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = an_integration_load_exec.request_id
AND object_id = an_integration_load_exec.object_id;
SELECT display_name
INTO v_obj_def_name
FROM fem_object_definition_vl
WHERE object_definition_id = p_undo_list_obj_def_id;
SELECT fem_ud_upd_dep_s.nextval INTO x_upd_dep_calc_id
FROM dual;
SELECT ledger_id, output_dataset_code, cal_period_id
INTO v_ledger_id, v_output_dataset_code, v_cal_period_id
FROM fem_pl_requests
WHERE request_id = p_request_id;
INSERT INTO fem_ud_upd_dep_t (upd_dep_calc_id, dependent_request_id, dependent_object_id)
SELECT DISTINCT x_upd_dep_calc_id, t1.request_id, t1.object_id
FROM fem_pl_tables t1
WHERE t1.request_id IN (
SELECT r.request_id
FROM fem_pl_requests r
WHERE 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 t1.table_name IN (
SELECT t2.table_name
FROM fem_pl_tables t2, fem_table_class_assignmt_v t
WHERE t2.request_id = p_request_id
AND t2.object_id = p_object_id
AND t2.statement_type = 'INSERT'
AND t2.table_name = t.table_name
AND t.table_classification_code IN
('ACCOUNT_PROFITABILITY','FTP_CASH_FLOW',
'FTP_NON_CASH_FLOW','FTP_OPTION_COST') )
AND t1.statement_type = 'UPDATE';
INSERT INTO fem_ud_upd_cols_t (upd_dep_calc_id, table_name, column_name, checked_flag)
SELECT DISTINCT x_upd_dep_calc_id, c.table_name, c.column_name, 'N'
FROM fem_pl_tab_updated_cols c
WHERE request_id = p_request_id
AND object_id = p_object_id
AND c.table_name IN (
SELECT t.table_name
FROM fem_table_class_assignmt_v t
WHERE t.table_classification_code IN
('ACCOUNT_PROFITABILITY','FTP_CASH_FLOW',
'FTP_NON_CASH_FLOW','FTP_OPTION_COST') );
INSERT INTO fem_ud_upd_dep_t (upd_dep_calc_id, dependent_request_id, dependent_object_id)
SELECT DISTINCT x_upd_dep_calc_id, c.request_id, c.object_id
FROM fem_pl_tab_updated_cols c
WHERE c.request_id IN (
SELECT r.request_id
FROM fem_pl_requests r
WHERE 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 (c.table_name, c.column_name) IN (
SELECT table_name, column_name
FROM fem_ud_upd_cols_t
WHERE upd_dep_calc_id = x_upd_dep_calc_id
AND checked_flag = 'N')
AND (c.request_id, c.object_id) NOT IN (
SELECT dependent_request_id, dependent_object_id
FROM fem_ud_upd_dep_t
WHERE upd_dep_calc_id = x_upd_dep_calc_id);
UPDATE fem_ud_upd_cols_t SET checked_flag = 'Y'
WHERE upd_dep_calc_id = x_upd_dep_calc_id
AND checked_flag = 'N';
INSERT INTO fem_ud_upd_dep_t (upd_dep_calc_id, dependent_request_id, dependent_object_id)
SELECT DISTINCT x_upd_dep_calc_id, t1.request_id, t1.object_id
FROM fem_pl_tables t1
WHERE t1.request_id IN (
SELECT r.request_id
FROM fem_pl_requests r
WHERE 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 t1.table_name IN (
SELECT t2.table_name
FROM fem_pl_tables t2, fem_table_class_assignmt_v t
WHERE t2.table_name = t.table_name
AND t.table_classification_code IN
('ACCOUNT_PROFITABILITY','FTP_CASH_FLOW',
'FTP_NON_CASH_FLOW','FTP_OPTION_COST')
AND t2.statement_type = 'INSERT'
AND (t2.request_id, t2.object_id) IN (
SELECT dependent_request_id, dependent_object_id
FROM fem_ud_upd_dep_t
WHERE upd_dep_calc_id = x_upd_dep_calc_id))
AND t1.statement_type = 'UPDATE'
AND (t1.request_id, t1.object_id) NOT IN (
SELECT dependent_request_id, dependent_object_id
FROM fem_ud_upd_dep_t
WHERE upd_dep_calc_id = x_upd_dep_calc_id);
INSERT INTO fem_ud_upd_cols_t (upd_dep_calc_id, table_name, column_name, checked_flag)
SELECT DISTINCT x_upd_dep_calc_id, c.table_name, c.column_name, 'N'
FROM fem_pl_tab_updated_cols c
WHERE (c.request_id, c.object_id) IN (
SELECT d.dependent_request_id, d.dependent_object_id
FROM fem_ud_upd_dep_t d
WHERE d.upd_dep_calc_id = x_upd_dep_calc_id)
AND c.table_name IN (
SELECT t.table_name
FROM fem_table_class_assignmt_v t
WHERE t.table_classification_code IN
('ACCOUNT_PROFITABILITY','FTP_CASH_FLOW',
'FTP_NON_CASH_FLOW','FTP_OPTION_COST') )
AND (c.table_name, c.column_name) NOT IN (
SELECT e.table_name, e.column_name
FROM fem_ud_upd_cols_t e
WHERE e.upd_dep_calc_id = x_upd_dep_calc_id);
SELECT c.upd_dep_calc_id, c.dependent_request_id, c.dependent_object_id
FROM fem_ud_upd_dep_t c
WHERE c.upd_dep_calc_id = v_upd_dep_calc_id
AND NOT (c.dependent_request_id = p_request_id
AND c.dependent_object_id = p_object_id);
SELECT DISTINCT object_id, request_id
FROM fem_pl_chains
START WITH source_created_by_request_id = p_request_id
AND source_created_by_object_id = p_object_id
CONNECT BY
PRIOR object_id=source_created_by_object_id AND
PRIOR request_id=source_created_by_request_id;
IF p_dependency_type IN ('ALL','UPDATE') THEN
fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
p_msg_text => 'STEP 1: Report UPDATE dependents.');
FOR an_update_dependent IN c1(v_upd_dep_calc_id) LOOP
v_count := c1%ROWCOUNT;
p_value1 => an_update_dependent.dependent_request_id,
p_trans1 => 'N',
p_token2 => 'OBJECT_ID',
p_value2 => an_update_dependent.dependent_object_id,
p_trans2 => 'N',
p_token3 => 'DEPENDENT_TYPE',
p_value3 => 'UPDATE',
p_trans3 => 'N');
p_msg_name => 'FEM_UD_NO_UPDATE_DEP_FOUND_TXT');
DELETE fem_ud_upd_dep_t WHERE upd_dep_calc_id = v_upd_dep_calc_id;
DELETE fem_ud_upd_cols_t WHERE upd_dep_calc_id = v_upd_dep_calc_id;
SELECT object_name
INTO v_object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
SELECT c.upd_dep_calc_id, c.dependent_request_id, c.dependent_object_id
FROM fem_ud_upd_dep_t c
WHERE c.upd_dep_calc_id = v_upd_dep_calc_id
AND NOT (c.dependent_request_id = p_request_id
AND c.dependent_object_id = p_object_id);
SELECT DISTINCT object_id, request_id
FROM fem_pl_chains
START WITH source_created_by_request_id = p_request_id
AND source_created_by_object_id = p_object_id
CONNECT BY
PRIOR object_id=source_created_by_object_id AND
PRIOR request_id=source_created_by_request_id;
SELECT pl.request_id, pl.object_id
FROM fem_pl_object_executions pl, fem_object_catalog_b o,
fem_pl_requests r1, fem_pl_requests r2
WHERE pl.object_id = o.object_id
AND o.object_id = p_obj_id
AND o.object_type_code IN ('OGL_INTG_BAL_RULE','XGL_INTEGRATION',
'RCM_PROCESS_RULE','TP_PROCESS_RULE',
'SOURCE_DATA_LOADER','DATAX_LOADER')
AND pl.request_id = r1.request_id
AND r1.request_id <> p_req_id
AND r2.request_id = p_req_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
OR o.object_type_code IN ('OGL_INTG_BAL_RULE','XGL_INTEGRATION',
'RCM_PROCESS_RULE','TP_PROCESS_RULE'));
SELECT d.dependent_request_id, d.dependent_object_id, u.folder_id
FROM fem_ud_list_dependents d, fem_object_catalog_b o, fem_user_folders u
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND d.request_id = p_request_id
AND d.object_id = p_object_id
AND d.dependent_object_id = o.object_id
AND o.folder_id = u.folder_id (+)
AND u.user_id(+) = pv_apps_user_id;
SELECT d.dependent_request_id, d.dependent_object_id, u.folder_id
FROM fem_ud_prview_dependents d, fem_object_catalog_b o, fem_user_folders u
WHERE d.ud_session_id = p_ud_session_id
AND d.request_id = p_request_id
AND d.object_id = p_object_id
AND d.dependent_object_id = o.object_id
AND o.folder_id = u.folder_id (+)
AND u.user_id(+) = pv_apps_user_id;
IF p_dependency_type NOT IN ('ALL','UPDATE') THEN
RAISE e_invalid_dependency_type;
p_msg_text => 'STEP 1: DELETE existing dependents for the candidate.');
SELECT DECODE(count(*),0,'N','Y') INTO v_undo_list_ever_exec
FROM (select exec_status_code
from fem_ud_list_candidates
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL
UNION
select exec_status_code
from fem_ud_list_dependents
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL
UNION
select exec_status_code
from fem_ud_lists
where undo_list_obj_def_id = p_undo_list_obj_def_id
and exec_status_code IS NOT NULL);
DELETE fem_ud_list_dependents
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
DELETE fem_ud_prview_dependents
WHERE ud_session_id = p_ud_session_id
AND request_id = p_request_id
AND object_id = p_object_id;
p_msg_text => 'STEP 2: Generate UPDATE dependents.');
FOR updated_col IN c1 LOOP
v_count := c1%ROWCOUNT;
INSERT INTO fem_ud_list_dependents (undo_list_obj_def_id,
request_id, object_id, dependent_request_id, dependent_object_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_Number)
VALUES (p_undo_list_obj_def_id, p_request_id, p_object_id,
updated_col.dependent_request_id, updated_col.dependent_object_id,
pv_apps_user_id, sysdate, pv_apps_user_id, sysdate, pv_login_id,1);
INSERT INTO fem_ud_prview_dependents (ud_session_id,
request_id, object_id, dependent_request_id, dependent_object_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_Number)
VALUES (p_ud_session_id, p_request_id, p_object_id,
updated_col.dependent_request_id, updated_col.dependent_object_id,
pv_apps_user_id, sysdate, pv_apps_user_id, sysdate, pv_login_id,1);
p_msg_name => 'FEM_UD_NO_UPDATE_DEP_FOUND_TXT');
DELETE fem_ud_upd_dep_t WHERE upd_dep_calc_id = v_upd_dep_calc_id;
DELETE fem_ud_upd_cols_t WHERE upd_dep_calc_id = v_upd_dep_calc_id;
SELECT object_name
INTO v_object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
INSERT INTO fem_ud_list_dependents (undo_list_obj_def_id, request_id,
object_id, dependent_request_id, dependent_object_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, object_version_Number)
VALUES (p_undo_list_obj_def_id, p_request_id, p_object_id, achain.request_id,
achain.object_id, pv_apps_user_id, sysdate, pv_apps_user_id, sysdate,
pv_login_id,1);
INSERT INTO fem_ud_prview_dependents (ud_session_id,
request_id, object_id, dependent_request_id, dependent_object_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number)
VALUES (p_ud_session_id, p_request_id, p_object_id,
achain.request_id, achain.object_id, pv_apps_user_id,
sysdate, pv_apps_user_id, sysdate, pv_login_id,1);
INSERT INTO fem_ud_prview_dependents
(ud_session_id,request_id, object_id, dependent_request_id,
dependent_object_id,created_by, creation_date, last_updated_by,
last_update_date,last_update_login, object_version_Number)
VALUES
(p_ud_session_id, p_request_id, p_object_id, repeat_execs.request_id,
repeat_execs.object_id, pv_apps_user_id, sysdate, pv_apps_user_id,
sysdate, pv_login_id, 1);
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_dep.dependent_request_id
AND object_id = a_dep.dependent_object_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_dep.dependent_request_id
AND object_id = a_dep.dependent_object_id;
UPDATE fem_ud_prview_dependents
SET validation_status_code = 'FEM_UD_CANNOT_READ_OBJECT_ERR',
last_Update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE ud_session_id = p_ud_session_id
AND dependent_request_id = a_prvw_dep.dependent_request_id
AND dependent_object_id = a_prvw_dep.dependent_object_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_prvw_dep.dependent_request_id
AND object_id = a_prvw_dep.dependent_object_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_prvw_dep.dependent_request_id
AND object_id = a_prvw_dep.dependent_object_id;
UPDATE fem_ud_prview_dependents
SET validation_status_code = 'FEM_UD_OBJEXEC_IS_RUNNING_ERR',
last_Update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE ud_session_id = p_ud_session_id
AND dependent_request_id = a_prvw_dep.dependent_request_id
AND dependent_object_id = a_prvw_dep.dependent_object_id;
UPDATE fem_ud_prview_dependents
SET validation_status_code = 'FEM_UD_VALID_TXT',
last_update_date = sysdate, last_updated_By = pv_apps_user_id
WHERE ud_session_id = p_ud_session_id
AND request_id = p_request_id
AND object_id = p_object_id
AND validation_status_code IS NULL;
SELECT request_id, object_id
FROM fem_ud_list_candidates
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND (exec_status_code IS NULL OR
exec_status_code <> 'SUCCESS');
SELECT request_id, object_id
FROM fem_ud_prview_candidates
WHERE ud_session_id = p_ud_session_id;
SELECT count(*) INTO v_count
FROM fem_user_folders u, fem_object_catalog_b o
WHERE o.object_id = a_candidate.object_id
AND o.folder_id = u.folder_id
AND u.user_id = pv_apps_user_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
SELECT count(*) INTO v_count
FROM fem_user_folders u, fem_object_catalog_b o
WHERE o.object_id = a_candidate.object_id
AND o.folder_id = u.folder_id
AND u.user_id = pv_apps_user_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
UPDATE fem_ud_prview_candidates
SET validation_status_code = 'FEM_UD_VALID_TXT',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE ud_session_id = p_ud_session_id
AND request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
UPDATE fem_ud_prview_candidates
SET validation_status_code = 'FEM_UD_CANNOT_READ_OBJECT_ERR',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE ud_session_id = p_ud_session_id
AND request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
UPDATE fem_ud_prview_candidates
SET validation_status_code = 'FEM_UD_OBJEXEC_IS_RUNNING_ERR',
last_update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE ud_session_id = p_ud_session_id
AND request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
UPDATE fem_ud_prview_candidates
SET validation_status_code = 'FEM_UD_CANNOT_UNDO_DEPENDENTS',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE ud_session_id = p_ud_session_id
AND request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
v_col_name FEM_PL_TAB_UPDATED_COLS.column_name%TYPE;
SELECT distinct p.statement_type, p.table_name,
max(t.undo_type) over (partition by p.table_name)
undo_type,
DECODE(p.statement_type, 'INSERT','DELETE','MERGE',
'INSERT and DELETE','UPDATE') undo_statement_type
FROM fem_pl_tables p,
(select decode(table_classification_code,
'TRANSACTION_PROFITABILITY','ZERO_COLUMN_BY_OBJECT',
'CUSTOMER_PROFIT_RESULT','ZERO_COLUMN_BY_OBJECT',
'ACCOUNT_PROFITABILITY','ZERO_COLUMN_BY_PERIOD',
'FTP_CASH_FLOW','ZERO_COLUMN_BY_PERIOD',
'FTP_NON_CASH_FLOW','ZERO_COLUMN_BY_PERIOD',
'FTP_OPTION_COST','ZERO_COLUMN_BY_PERIOD',NULL)
undo_type, table_name
from fem_table_class_assignmt_v) t
WHERE p.request_id = p_request_id
AND p.object_id = p_object_id
AND p.table_name = t.table_name(+)
ORDER BY table_name, statement_type;
SELECT column_name AS colname
FROM fem_pl_tab_updated_cols
WHERE request_id = p_request_id
AND object_id = p_object_id
AND table_name = v_table_name
AND statement_type = v_statement_type
ORDER BY column_name;
SELECT o.object_type_code, t.undo_flag
INTO v_object_type_code, v_undo_flag
FROM fem_object_catalog_b o, fem_object_types t
WHERE o.object_id = p_object_id
AND t.object_type_code = o.object_type_code;
SELECT object_id INTO v_undo_list_obj_id
FROM fem_object_definition_b
WHERE object_definition_id = p_undo_list_obj_def_id;
SELECT object_name
INTO v_object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
UPDATE fem_ud_list_dependents
SET exec_status_code = 'RUNNING',
last_update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND dependent_request_id = p_request_id
AND dependent_object_id = p_object_id;
UPDATE fem_ud_list_candidates
SET exec_status_code = 'RUNNING',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
SELECT max(decode(table_classification_code,'GENERIC_DATA_TABLE',
'GENERIC_DATA_TABLE','LEDGER')) INTO v_mp_step_name
FROM fem_table_class_assignmt_v
WHERE (table_classification_code = 'GENERIC_DATA_TABLE'
OR table_classification_code like '%LEDGER')
AND table_name = atbl.table_name;
IF atbl.statement_type = 'INSERT' THEN
-- ============================================================================
-- S2A: Process statements in FEM_PL_TABLES (INSERT statement section)
-- ============================================================================
-- Bug 5738732: Added {{table_partition}} token
v_sql_stmt := 'DELETE '||atbl.table_name||' {{table_partition}} ';
v_mp_stmt_id := atbl.table_name||'_DELETE_'||v_count_tbls;
SELECT NVL(SUM(rows_processed),0)
INTO v_rows_processed
FROM fem_mp_process_ctl_t
WHERE req_id = pv_request_id;
p_msg_name => 'FEM_UD_DELETE_SUMMARY_TXT',
p_token1 => 'NUM_ROWS',
p_value1 => v_rows_processed,
p_token2 => 'TABLE_NAME',
p_value2 => v_tab_name);
fem_multi_proc_pkg.delete_data_slices(
p_req_id => pv_request_id);
ELSIF atbl.statement_type = 'UPDATE' AND
atbl.undo_type IN ('ZERO_COLUMN_BY_PERIOD',
'ZERO_COLUMN_BY_OBJECT') THEN
-- ============================================================================
-- S2B: Process statements in FEM_PL_TABLES (UPDATE statement section)
-- For the FEM.D release. Update statements are only processed for
-- tables that belong to classifications that allow the undo of updates.
-- The list of classifications is currently hard-coded for FEM.D and
-- will be metadata driven in the future. For now, the list can be
-- determined by looking at the "c1" cursor definition.
-- ============================================================================
v_sql_stmt := 'UPDATE '||atbl.table_name
||' {{table_partition}} SET';
SELECT ' CAL_PERIOD_ID = '||cal_period_id||
' AND LEDGER_ID = '||ledger_id||
' AND DATASET_CODE = '||output_dataset_code
INTO v_where_clause
FROM fem_pl_requests
WHERE request_id = p_request_id;
v_where_clause := ' LAST_UPDATED_BY_REQUEST_ID = '||p_request_id||
' AND LAST_UPDATED_BY_OBJECT_ID = '||p_object_id;
v_mp_stmt_id := atbl.table_name||'_UPDATE_'||v_count_tbls;
SELECT NVL(SUM(rows_processed),0)
INTO v_rows_processed
FROM fem_mp_process_ctl_t
WHERE req_id = pv_request_id;
p_msg_name => 'FEM_UD_UPDATE_SUMMARY_TXT',
p_token1 => 'NUM_ROWS',
p_value1 => v_rows_processed,
p_token2 => 'TABLE_NAME',
p_value2 => v_tab_name);
fem_multi_proc_pkg.delete_data_slices(
p_req_id => pv_request_id);
v_sql_stmt := 'DELETE '||atbl.table_name||' {{table_partition}} ';
SELECT NVL(SUM(rows_processed),0)
INTO v_rows_processed
FROM fem_mp_process_ctl_t
WHERE req_id = pv_request_id;
p_msg_name => 'FEM_UD_DELETE_SUMMARY_TXT',
p_token1 => 'NUM_ROWS',
p_value1 => v_rows_processed,
p_token2 => 'TABLE_NAME',
p_value2 => v_tab_name);
fem_multi_proc_pkg.delete_data_slices(
p_req_id => pv_request_id);
v_sql_stmt := 'UPDATE '||atbl.table_name
||' {{table_partition}} SET';
SELECT ' CAL_PERIOD_ID = '||cal_period_id||
' AND LEDGER_ID = '||ledger_id||
' AND DATASET_CODE = '||output_dataset_code
INTO v_where_clause
FROM fem_pl_requests
WHERE request_id = p_request_id;
v_where_clause := ' LAST_UPDATED_BY_REQUEST_ID = '||p_request_id||
' AND LAST_UPDATED_BY_OBJECT_ID = '||p_object_id;
SELECT NVL(SUM(rows_processed),0)
INTO v_rows_processed
FROM fem_mp_process_ctl_t
WHERE req_id = pv_request_id;
p_msg_name => 'FEM_UD_UPDATE_SUMMARY_TXT',
p_token1 => 'NUM_ROWS',
p_value1 => v_rows_processed,
p_token2 => 'TABLE_NAME',
p_value2 => v_tab_name);
fem_multi_proc_pkg.delete_data_slices(
p_req_id => pv_request_id);
p_msg_text => 'STEP 3: Delete data from the data location tables (FEM_DL_xxxx).');
delete_execution_log (
p_commit => FND_API.G_TRUE,
p_api_version => 1.0,
p_request_id => p_request_id,
p_object_id => p_object_id,
x_return_status => x_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.delete_execution_log');
RAISE e_cannot_delete_execution_log;
p_msg_text => 'STEP 6: Update status of object execution in undo list to SUCCESS.');
UPDATE fem_ud_list_dependents
SET exec_status_code = 'SUCCESS',
last_Update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND dependent_request_id = p_request_id
AND dependent_object_id = p_object_id;
UPDATE fem_ud_list_candidates
SET exec_status_code = 'SUCCESS',
last_update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
SELECT max(table_classification_code)
INTO v_tab_class
FROM fem_table_class_assignmt_v
WHERE table_classification_code NOT IN ('CUSTOMER_PROFIT_RESULT',
'TRANSACTION_PROFITABILITY','ACCOUNT_PROFITABILITY',
'FTP_CASH_FLOW','FTP_NON_CASH_FLOW','FTP_OPTION_COST')
AND table_name = v_tab_name;
p_msg_text => 'Update status of object execution in undo list to ERROR_RERUN');
UPDATE fem_ud_list_dependents
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_User_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND dependent_request_id = p_request_id
AND dependent_object_id = p_object_id;
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND exec_status_code = 'RUNNING';
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
p_msg_text => 'Update status of object execution in undo list to ERROR_RERUN');
UPDATE fem_ud_list_dependents
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_User_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND dependent_request_id = p_request_id
AND dependent_object_id = p_object_id;
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND exec_status_code = 'RUNNING';
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
p_msg_text => 'Update status of object execution in undo list to ERROR_RERUN');
UPDATE fem_ud_list_dependents
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND dependent_request_id = p_request_id
AND dependent_object_id = p_object_id;
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND exec_status_code = 'RUNNING';
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
WHEN e_cannot_delete_execution_log THEN
x_return_status := pc_ret_sts_error;
p_msg_name => 'FEM_UD_CANNOT_DELETE_EXEC_LOG');
p_msg_text => 'Update status of object execution in undo list to ERROR_RERUN');
UPDATE fem_ud_list_dependents
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND dependent_request_id = p_request_id
AND dependent_object_id = p_object_id;
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_Update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND exec_status_code = 'RUNNING';
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
p_msg_text => 'Update status of object execution in undo list to ERROR_RERUN.');
UPDATE fem_ud_list_dependents
SET exec_status_code = 'ERROR_RERUN',
last_Update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND dependent_request_id = p_request_id
AND dependent_object_id = p_object_id;
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND exec_status_code = 'RUNNING';
UPDATE fem_ud_list_candidates
SET exec_status_code = 'ERROR_RERUN',
last_Update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id
AND request_id = p_request_id
AND object_id = p_object_id;
UPDATE fem_ud_lists
SET exec_status_code = p_execution_status,
last_update_date = sysdate, last_Updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = p_undo_list_obj_def_id;
FEM_PL_PKG.Update_Obj_Exec_Status(
P_API_VERSION => 1.0,
P_COMMIT => FND_API.G_TRUE,
P_REQUEST_ID => pv_request_id,
P_OBJECT_ID => p_undo_list_obj_id,
P_EXEC_STATUS_CODE => p_execution_status,
P_USER_ID => pv_apps_user_id,
P_LAST_UPDATE_LOGIN => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
FEM_PL_PKG.Update_Request_Status(
P_API_VERSION => 1.0,
P_COMMIT => FND_API.G_TRUE,
P_REQUEST_ID => pv_request_id,
P_EXEC_STATUS_CODE => p_execution_status,
P_USER_ID => pv_apps_user_id,
P_LAST_UPDATE_LOGIN => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
SELECT meaning INTO v_exec_status
FROM fnd_lookup_values
WHERE lookup_type = 'FEM_EXEC_STATUS_DSC'
AND lookup_code = p_execution_status
AND language = USERENV('LANG');
SELECT request_id, object_id
FROM fem_ud_list_candidates
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id
AND (exec_status_code IS NULL OR
exec_status_code <> 'SUCCESS')
ORDER BY request_id, object_id;
SELECT dependent_request_id, dependent_object_id
FROM fem_ud_list_dependents
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id
AND request_id = v_request_id
AND object_id = v_object_id
AND (exec_status_code IS NULL OR
exec_status_code <> 'SUCCESS')
ORDER BY dependent_request_id, dependent_object_id;
SELECT request_id
FROM fem_pl_object_executions
WHERE object_id = p_undo_list_obj_id;
SELECT u.undo_list_obj_def_id
INTO v_undo_list_obj_def_id
FROM fem_object_catalog_b o, fem_object_definition_b d, fem_ud_lists u
WHERE o.object_id = p_undo_list_obj_id
AND o.object_type_code = 'UNDO'
AND o.object_id = d.object_id
AND d.object_definition_id = u.undo_list_obj_def_id;
SELECT include_dependencies_flag, ignore_dependency_errs_flag
INTO v_include_dependencies_flag, v_ignore_dependency_errs_flag
FROM fem_ud_lists
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id;
SELECT count(*) INTO v_count
FROM fem_pl_chains pl
WHERE (pl.source_created_by_request_id, pl.source_created_by_object_id) IN (
SELECT c.request_id, c.object_id
FROM fem_ud_list_candidates c
WHERE c.undo_list_obj_def_id = v_undo_list_obj_def_id);
v_dependency_type := 'UPDATE';
P_LAST_UPDATE_LOGIN => pv_login_id,
P_PROGRAM_ID => pv_program_id,
P_PROGRAM_LOGIN_ID => pv_login_id,
P_PROGRAM_APPLICATION_ID => pv_program_app_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
P_LAST_UPDATE_LOGIN => pv_login_id,
X_EXEC_STATE => v_exec_state,
X_PREV_REQUEST_ID => v_previous_request_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
UPDATE fem_ud_lists
SET exec_status_code = 'RUNNING',
last_update_date = sysdate, last_updated_by = pv_apps_User_id
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id;
SELECT count(*) INTO v_count
FROM fem_ud_list_candidates
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id
AND (exec_status_code IS NULL OR exec_status_code <> 'SUCCESS');
UPDATE fem_ud_list_candidates
SET exec_status_code = 'RUNNING',
last_Update_date = sysdate, last_updated_by = pv_apps_user_Id
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id
AND request_id = a_candidate.request_id
AND object_id = a_candidate.object_id;
p_msg_text => 'Since the undo run was successful, delete the undo list and its registration information.');
p_msg_text => 'Delete undo run from process lock tables (FEM_PL_xxxx).');
delete_execution_log (
p_commit => FND_API.G_TRUE,
p_api_version => 1.0,
p_request_id => undo_objexec.request_id,
p_object_id => p_undo_list_obj_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.delete_execution_log');
p_msg_name => 'FEM_UD_CANNOT_DELETE_UNDOEXEC');
UPDATE fem_ud_lists
SET exec_status_code = 'SUCCESS',
last_update_date = sysdate, last_updated_by = pv_apps_user_id
WHERE undo_list_obj_def_id = v_undo_list_obj_def_id;
delete_undo_list(
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_undo_list_obj_id => p_undo_list_obj_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
p_msg_name => 'FEM_UD_CANNOT_DELETE_LIST_ERR');
SELECT o.object_id
FROM fem_object_catalog_b o, fem_object_definition_b d, fem_ud_lists u
WHERE o.object_type_code = 'UNDO'
AND o.object_id = d.object_id
AND d.object_definition_id = u.undo_list_obj_def_id
AND (u.execution_date < SYSDATE OR u.execution_date = SYSDATE)
AND (u.exec_status_code IS NULL OR u.exec_status_code NOT IN ('RUNNING','SUCCESS'));
SELECT c.request_id
FROM fnd_concurrent_requests c
WHERE c.parent_request_id = pv_request_id
ORDER BY c.request_id;
SELECT COUNT(*)
INTO v_count
FROM fem_pl_object_executions
WHERE request_id = p_request_id
AND object_id = p_object_id;
SELECT count(*) INTO v_count
FROM fem_user_folders u, fem_object_catalog_b o
WHERE o.object_id = p_object_id
AND o.folder_id = u.folder_id
AND u.user_id = pv_apps_user_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 o.object_id, d.object_definition_id, o.object_name
INTO v_undo_list_obj_id, v_undo_list_obj_def_id, pv_undo_object_name
FROM fem_object_catalog_vl o, fem_object_definition_b d
WHERE o.object_type_code = 'UNDO'
AND o.object_id = d.object_id
AND o.object_name = 'UNDO - REQUEST_ID: '||p_request_id||' OBJECT_ID: '||p_object_id;
SELECT object_id
FROM fem_pl_object_executions
WHERE request_id = p_request_id
ORDER BY event_order;
SELECT COUNT(*)
INTO v_count
FROM fem_pl_requests
WHERE request_id = p_request_id;
SELECT count(*) INTO v_count
FROM fem_pl_object_executions
WHERE request_id = p_request_id;
DELETE fem_pl_temp_objects
WHERE request_id = p_request_id;
DELETE fem_pl_obj_exec_steps
WHERE request_id = p_request_id;
DELETE fem_pl_chains
WHERE request_id = p_request_id;
DELETE fem_pl_object_defs
WHERE request_id = p_request_id;
SELECT count(*) INTO v_count
FROM fem_object_catalog_b o, fem_pl_object_executions p
WHERE p.request_id = p_request_id
AND p.object_id = o.object_id
AND o.folder_id NOT IN
(SELECT folder_id
FROM fem_user_folders
WHERE user_id = pv_apps_user_id);
SELECT o.object_id, d.object_definition_id, o.object_name
INTO v_undo_list_obj_id, v_undo_list_obj_def_id, pv_undo_object_name
FROM fem_object_catalog_vl o, fem_object_definition_b d
WHERE o.object_type_code = 'UNDO'
AND o.object_id = d.object_id
AND o.object_name = 'UNDO - REQUEST_ID: '||p_request_id;
SELECT c.request_id, c.object_id, pl.exec_status_code
FROM fem_ud_prview_candidates c, fem_pl_object_executions pl
WHERE c.ud_session_id = p_ud_session_id
AND c.request_id = pl.request_id (+)
AND c.object_id = pl.object_id (+)
ORDER BY c.request_id, c.object_id;
SELECT exec_status_code INTO v_exec_status_code
FROM fem_pl_object_executions
WHERE request_id = cand_num.request_id
AND object_id = cand_num.object_id;
SELECT o.object_id, d.object_definition_id
INTO v_undo_list_obj_id, v_undo_list_obj_def_id
FROM fem_object_catalog_vl o, fem_object_definition_b d
WHERE o.object_type_code = 'UNDO'
AND o.object_id = d.object_id
AND o.object_name = p_undo_list_name;
DELETE FROM fem_ud_prview_dependents
WHERE ud_session_id = p_ud_session_id;
DELETE FROM fem_ud_prview_candidates
WHERE ud_session_id = p_ud_session_id;
delete_undo_list (
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_undo_list_obj_id => v_undo_list_obj_id);
PROCEDURE insert_preview_candidates (x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_ud_session_id IN NUMBER,
p_request_ids IN FEM_NUMBER_TABLE,
p_object_ids IN FEM_NUMBER_TABLE,
p_commit IN VARCHAR2) AS
-- ============================================================================
-- PUBLIC
-- This procedure inserts the Undo Preview Candidates for
-- a given Undo Session.
-- ============================================================================
c_api_name CONSTANT VARCHAR2(30) := 'insert_preview_candidates';
SAVEPOINT insert_preview_candidates_pub;
INSERT INTO fem_ud_prview_candidates(ud_session_id,
request_id, object_id, validation_status_code,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_Number)
VALUES (p_ud_session_id, p_request_ids(i), p_object_ids(i),
null,pv_apps_user_id, sysdate, pv_apps_user_id, sysdate, pv_login_id,1);
ROLLBACK TO insert_preview_candidates_pub;
ROLLBACK TO insert_preview_candidates_pub;
ROLLBACK TO insert_preview_candidates_pub;
END insert_preview_candidates;
PROCEDURE Delete_Balances (
p_api_version IN NUMBER DEFAULT 1.0,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit 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_current_request_id IN NUMBER,
p_object_id IN NUMBER,
p_cal_period_id IN NUMBER,
p_ledger_id IN NUMBER,
p_dataset_code IN NUMBER
) IS
-- =========================================================================
-- Purpose
-- Deletes all FEM Balances data of a given ledger, calendar period,
-- and dataset that was created by previous executions of a given rule.
-- Data can only be deleted if the data is not being used by another
-- rule as a data source (i.e. chained) and if the object executions
-- that created the data have finished running.
--
-- Note that this API only works against FEM Balances and can only be
-- called for balances created by the TP_PROCESS_RULE rules.
-- History
-- 01-30-06 G Cheng Bug 4596447. Created.
-- 06-28-06 G Cheng Bug 5360424. Added p_current_request_id param.
-- Arguments
-- p_current_request_id Request ID of execution currently running
-- p_object_id Object ID
-- p_cal_period_id Calendar Period ID
-- p_ledger_id Ledger ID
-- p_dataset_code Dataset Code
-- Return Logic
-- Set x_return_status to 'U' (Unexpected Error) if object type
-- is not TP_PROCESS_RULE.
-- Set x_return_status to 'E' (Error) if the object executions that
-- created the existing FEM Balances data are chained or are still running.
-- Otherwise, set x_return_status to 'S' (Success) after deleting the
-- balances and all related Process Locks and Data Locations related data.
-- =========================================================================
C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
'fem.plsql.fem_ud_pkg.delete_balances';
C_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Balances';
SELECT R.request_id, T.object_id, T.table_name
FROM fem_pl_requests R, fem_pl_tables T
WHERE R.request_id = T.request_id
AND R.request_id <> p_current_request_id
AND T.object_id = p_object_id
AND T.table_name = p_table_name
AND R.cal_period_id = p_cal_period_id
AND R.ledger_id = p_ledger_id
AND R.output_dataset_code = p_dataset_code;
SELECT object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
SAVEPOINT delete_balances_pub;
SELECT object_type_code
INTO v_object_type
FROM fem_object_catalog_b
WHERE object_id = p_object_id;
v_sql := 'DELETE FROM ' ||C_FEM_BALANCES
||' WHERE CREATED_BY_REQUEST_ID = '||obj_execs.request_id
||' AND CREATED_BY_OBJECT_ID = '||obj_execs.object_id;
DELETE FROM fem_pl_tables
WHERE object_id = obj_execs.object_id
AND request_id = obj_execs.request_id
AND table_name = obj_execs.table_name;
SELECT count(*)
INTO v_count
FROM fem_pl_tables
WHERE object_id = obj_execs.object_id
AND request_id = obj_execs.request_id;
delete_execution_log (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_request_id => obj_execs.request_id,
p_object_id => obj_execs.object_id);
ROLLBACK TO delete_balances_pub;
ROLLBACK TO delete_balances_pub;
ROLLBACK TO delete_balances_pub;
ROLLBACK TO delete_balances_pub;
END Delete_Balances;
SELECT object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
SELECT undo_flag
INTO v_undo_flag
FROM fem_object_catalog_b oc, fem_object_types_b ot
WHERE oc.object_id = p_object_id
AND oc.object_type_code = ot.object_type_code;
SELECT count(*)
INTO v_count
FROM fem_pl_tables
WHERE object_id = p_object_id
AND request_id = p_request_id;
delete_execution_log (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_request_id => p_request_id,
p_object_id => p_object_id);
select row_count, bal.created_by_object_id, bal.created_by_request_id, bal.ledger_id bal_ledger_id,
bal.cal_period_id bal_cal_period_id, bal.source_system_code bal_source_system_code,
bal.dataset_code bal_dataset_code, exe.request_id execution_request_id,
requests.request_id request_request_id, tab.request_id tab_request_id,
requests.effective_date request_effective_date, requests.cal_period_id request_cal_period_id, requests.ledger_id request_ledger_id,
requests.dataset_io_obj_def_id request_dataset_io_obj_def_id, requests.output_dataset_code request_output_dataset_code,
requests.source_system_code request_source_system_code, requests.program_id, requests.program_application_id,
requests.exec_status_code, requests.last_updated_by, requests.program_login_id, obj.object_name
from (select count(*) row_count, created_by_object_id, created_by_request_id, ledger_id, cal_period_id, source_system_code,
dataset_code
from fem_balances
WHERE (p_request_id IS NULL OR created_by_request_id = p_request_id)
AND (p_object_id IS NULL OR created_by_object_id = p_object_id)
AND source_system_code = (SELECT source_system_code
FROM fem_source_systems_b src_system
WHERE src_system.source_system_display_code = 'PFT'
and src_system.personal_flag = 'N')
GROUP BY created_by_object_id, created_by_request_id, ledger_id, cal_period_id, source_system_code,
dataset_code) bal,
fem_pl_object_executions exe,
fem_pl_requests requests,
fem_pl_tables tab,
fem_object_catalog_vl obj
where bal.created_by_object_id = obj.object_id
and obj.object_type_code = 'MAPPING_RULE'
and exe.object_id(+) = bal.created_by_object_id
and exe.request_id(+) = bal.created_by_request_id
and requests.request_id(+) = bal.created_by_request_id
and tab.object_id(+) = bal.created_by_object_id
and tab.request_id(+) = bal.created_by_request_id
and tab.table_name(+) = 'FEM_BALANCES'
and (exe.object_id is null or exe.request_id is null or requests.request_id is null
or tab.object_id is null or tab.request_id is null) ;
,p_last_update_login => l_reg_rec.login_id
,p_program_id => l_reg_rec.program_id
,p_program_login_id => l_reg_rec.login_id
,p_program_application_id => l_reg_rec.program_application_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
);
l_reg_rec.user_id := missing_entry.last_updated_by;
,p_last_update_login => l_reg_rec.login_id
,p_exec_mode_code => null
,x_exec_state => l_exec_state
,x_prev_request_id => l_prev_request_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
FEM_PL_PKG.Update_Obj_Exec_Status(
P_API_VERSION => 1.0,
P_COMMIT => FND_API.G_FALSE,
P_REQUEST_ID => l_reg_rec.request_id,
P_OBJECT_ID => l_reg_rec.object_id,
P_EXEC_STATUS_CODE => 'SUCCESS',
P_USER_ID => l_reg_rec.user_id,
P_LAST_UPDATE_LOGIN => l_reg_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 => l_reg_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_statement_type => 'INSERT'
,p_num_of_output_rows => missing_entry.row_count
,p_user_id => l_reg_rec.user_id
,p_last_update_login => l_reg_rec.login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
SELECT STATUS_CODE, ARGUMENT1, ARGUMENT2, ARGUMENT3, ARGUMENT4,
ARGUMENT5, ARGUMENT6, ARGUMENT7, ARGUMENT8, requested_by,
conc_login_id, CONCURRENT_PROGRAM_ID, PROGRAM_APPLICATION_ID
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = p_request_id;
select dataset_io_obj_def_id
from fem_ds_input_output_defs
where output_dataset_code = x_pl_register_record.output_dataset_code
and rownum = 1;
p_msg_data => ' select dim_attribute_varchar_member'||
' ,date_assign_value'||
' from '||l_dimension_rec.attr_table||
' where attribute_id = :b_attribute_id'||
' and version_id = :b_attr_version_id'||
' and '||l_dimension_rec.member_col||' = :b_member_id',
p_user_msg => 'N',
p_module => 'fem.plsql.'||pc_pkg_name||'.'|| l_api_name
);
' select dim_attribute_varchar_member'||
' ,date_assign_value'||
' from '||l_dimension_rec.attr_table||
' where attribute_id = :b_attribute_id'||
' and version_id = :b_attr_version_id'||
' and '||l_dimension_rec.member_col||' = :b_member_id'
into x_dim_attribute_varchar_member
,x_date_assign_value
using l_attribute_id
,l_attr_version_id
,p_member_id;
select att.attribute_id
,ver.version_id
,dim.member_col
,dim.attribute_table_name
into x_attribute_id
,x_attr_version_id
,x_dimension_rec.member_col
,x_dimension_rec.attr_table
from fem_dim_attributes_b att
,fem_dim_attr_versions_b ver
,fem_xdim_dimensions_vl dim
where att.attribute_varchar_label = p_attribute_varchar_label
AND dim.dimension_varchar_label = p_dimension_varchar_label
AND dim.dimension_id = att.dimension_id
and att.attribute_id = ver.attribute_id
and ver.default_version_flag = 'Y';
select d.object_definition_id
into x_obj_def_id
from fem_object_definition_b d
where d.object_id = p_object_id
and p_effective_date between d.effective_start_date and d.effective_end_date
and d.old_approved_copy_flag = 'N';
select d.object_definition_id
into x_obj_def_id
from fem_object_definition_b d
where d.object_id = p_object_id
AND ROWNUM = 1;