[Home] [Help]
6621: p_dataset_code IN NUMBER
6622: ) IS
6623: -- =========================================================================
6624: -- Purpose
6625: -- Deletes all FEM Balances data of a given ledger, calendar period,
6626: -- and dataset that was created by previous executions of a given rule.
6627: -- Data can only be deleted if the data is not being used by another
6628: -- rule as a data source (i.e. chained) and if the object executions
6629: -- that created the data have finished running.
6627: -- Data can only be deleted if the data is not being used by another
6628: -- rule as a data source (i.e. chained) and if the object executions
6629: -- that created the data have finished running.
6630: --
6631: -- Note that this API only works against FEM Balances and can only be
6632: -- called for balances created by the TP_PROCESS_RULE rules.
6633: -- History
6634: -- 01-30-06 G Cheng Bug 4596447. Created.
6635: -- 06-28-06 G Cheng Bug 5360424. Added p_current_request_id param.
6642: -- Return Logic
6643: -- Set x_return_status to 'U' (Unexpected Error) if object type
6644: -- is not TP_PROCESS_RULE.
6645: -- Set x_return_status to 'E' (Error) if the object executions that
6646: -- created the existing FEM Balances data are chained or are still running.
6647: -- Otherwise, set x_return_status to 'S' (Success) after deleting the
6648: -- balances and all related Process Locks and Data Locations related data.
6649: -- =========================================================================
6650: C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
6650: C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
6651: 'fem.plsql.fem_ud_pkg.delete_balances';
6652: C_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Balances';
6653: C_API_VERSION CONSTANT NUMBER := 1.0;
6654: C_FEM_BALANCES CONSTANT VARCHAR2(30) := 'FEM_BALANCES';
6655: --
6656: e_api_error EXCEPTION;
6657: e_objexec_running EXCEPTION;
6658: e_chain_exists EXCEPTION;
6763: IF p_init_msg_list = FND_API.G_TRUE then
6764: FND_MSG_PUB.Initialize;
6765: END IF;
6766:
6767: -- For now, only restrict to FEM_BALANCES since that is the
6768: -- table that TP Process Rules wants to remove data from.
6769: -- Loop through all object executions to make sure they can be removed.
6770: -- Checks being made are:
6771: -- 1. No chaining on the object executions
6771: -- 1. No chaining on the object executions
6772: -- 2. Object executions are not in the RUNNING state
6773: -- If data from object execution can be removed, delete data from table.
6774: FOR obj_execs IN c_pl_obj_execs (p_current_request_id, p_object_id,
6775: C_FEM_BALANCES, p_cal_period_id,
6776: p_ledger_id, p_dataset_code) LOOP
6777:
6778: -- Check to make sure object execution is not chained.
6779: FEM_PL_PKG.check_chaining (
6809: RAISE e_objexec_running;
6810: END IF;
6811:
6812: -- Delete data from the table
6813: v_sql := 'DELETE FROM ' ||C_FEM_BALANCES
6814: ||' WHERE CREATED_BY_REQUEST_ID = '||obj_execs.request_id
6815: ||' AND CREATED_BY_OBJECT_ID = '||obj_execs.object_id;
6816: BEGIN
6817: EXECUTE IMMEDIATE v_sql;
7194: |
7195: | DESCRIPTION
7196: | Main concurrent program to repair the Process Log/Process Lock entries based on
7197: | the Created_By_Request_ID and Created_By_Object_ID columns, etc. from
7198: | FEM_BALANCES, such as to restore the entries sufficiently in the executed
7199: | Rules UI for Undo to be invoked again to finish the job. See bug 7260263.
7200: |
7201: | SCOPE - PUBLIC
7202: |
7246: requests.source_system_code request_source_system_code, requests.program_id, requests.program_application_id,
7247: requests.exec_status_code, requests.last_updated_by, requests.program_login_id, obj.object_name
7248: from (select count(*) row_count, created_by_object_id, created_by_request_id, ledger_id, cal_period_id, source_system_code,
7249: dataset_code
7250: from fem_balances
7251: WHERE (p_request_id IS NULL OR created_by_request_id = p_request_id)
7252: AND (p_object_id IS NULL OR created_by_object_id = p_object_id)
7253: AND source_system_code = (SELECT source_system_code
7254: FROM fem_source_systems_b src_system
7266: and exe.request_id(+) = bal.created_by_request_id
7267: and requests.request_id(+) = bal.created_by_request_id
7268: and tab.object_id(+) = bal.created_by_object_id
7269: and tab.request_id(+) = bal.created_by_request_id
7270: and tab.table_name(+) = 'FEM_BALANCES'
7271: and (exe.object_id is null or exe.request_id is null or requests.request_id is null
7272: or tab.object_id is null or tab.request_id is null) ;
7273:
7274: /******************************************************************************
7584: END IF;
7585: END IF;
7586: END IF;--l_exe_ok AND missing_entry.execution_request_id IS NULL
7587:
7588: --Call the process lock API FEM_PL_PKG.Register_Table to register the FEM_BALANCES
7589: --table if the corresponding entry is missing from the fem_pl_tables
7590: IF (l_exe_ok AND missing_entry.tab_request_id IS NULL) THEN
7591: --write_debug (' Repair_PL_Request 13, before Register_Table');
7592: write_debug(
7599: p_api_version => 1.0
7600: ,p_commit => FND_API.G_FALSE
7601: ,p_request_id => l_reg_rec.request_id
7602: ,p_object_id => l_reg_rec.object_id
7603: ,p_table_name => 'FEM_BALANCES'
7604: ,p_statement_type => 'INSERT'
7605: ,p_num_of_output_rows => missing_entry.row_count
7606: ,p_user_id => l_reg_rec.user_id
7607: ,p_last_update_login => l_reg_rec.login_id
7652:
7653: FEM_DIMENSION_UTIL_PKG.Register_Data_Location (
7654: p_request_id => l_reg_rec.request_id
7655: ,p_object_id => l_reg_rec.object_id
7656: ,p_table_name => 'FEM_BALANCES'
7657: ,p_ledger_id => l_reg_rec.ledger_id
7658: ,p_cal_per_id => l_reg_rec.cal_period_id
7659: ,p_dataset_cd => l_reg_rec.output_dataset_code
7660: ,p_source_cd => l_reg_rec.source_system_code