問題記錄_FA_Create Accounting ERROR MESSAGE NUMBER:95937

Symptoms
----------------------------------------------------------------------------------------------------------------
於固定資產模組下，執行創建會計請求(Create Accounting)後，檢視輸出的報表(Subledger Accounting Program Report)，內容顯示錯誤訊息：

Message Number:
95937
Message:
There are either non-accountable events existing in the system, or events which could not be processed because no data could be found on transaction objects. Please enable the profile option SLA: Enable Diagnostics,create accounting again, and run the report Transaction Objects Disagnostics to identify the problem.


Cause
----------------------------------------------------------------------------------------------------------------
造成此錯誤的原因主要分為兩項：
1.Oracle Patch Needed.
2.Unprocessed Records Exists(Orphan Records).


其中Patch是在Oracle Support查詢時，幾乎所有的資料都顯示要打這個Patch:8738842。另外在查詢會計事件的時候，也發現了有未處理並沒有產生分錄的事件(Accounting Events)。
經過Reproduce後，基本上確認是用戶手動新增固定資產後，又手動將其資產刪除，造成Orphan Records所引起的錯誤。


Solution
----------------------------------------------------------------------------------------------------------------
1)先執行Apps Check(可略過)：
Please run the Diagnostics: Apps Check program for Oracle Assets

If the program doesn't exist, please add the program as follows
Sysadmin > Security > Responsibility > Request

Query the request as follows
Group - All reports
Application - Assets

And add the Diagnostics: Apps Check as follows
Type - Program
Name - from the list of value select Diagnostics: Apps Check
Application - Oracle Order Management

Save the changes. Now you can run the Diagnostics: Apps Check with
Assets as Parameter


依自身系統環境進行參數輸入，並檢查輸出的報表。


2)檢查下列Query：
Select bug_number, creation_date from ad_bugs where bug_number IN('8738842');


Select owner,object_name,object_type, status From all_objects Where status = 'INVALID' And object_name LIKE 'FA%';


3)在固定資產模組下查詢到未處理並沒有分錄的事件(Inquiry->Subledger Accounting->Accounting Events)。


4)執行下列Query檢查分錄事件表單(Table)，可依情況自行修改一些ID參數：
SELECT xlt.ledger_id,
xlt.valuation_method,
xe.event_id,
xlt.entity_id,
xe.event_type_code,
xe.event_status_code,
xlt.transaction_number,
xe.event_date
FROM xla_events xe,
xla.xla_transaction_entities xlt
WHERE xe.entity_id = xlt.entity_id
AND xe.application_id = xlt.application_id
AND xlt.source_application_id = 140
AND xlt.entity_code = 'TRANSACTIONS'
AND xe.event_status_code <> 'P'
AND NOT EXISTS (SELECT fth.transaction_header_id
FROM fa_transaction_headers fth
WHERE fth.transaction_header_id = xlt.source_id_int_1);


5)備份及刪除表單中的Orphan Events
To implement the solution, please execute the following steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:

a. Create table to list down the orphan records.

CREATE TABLE fa_orphan_events AS
SELECT xlt.ledger_id,
xlt.valuation_method,
xe.event_id,
xlt.entity_id,
xe.event_type_code,
xe.event_status_code,
xlt.transaction_number,
xe.event_date
FROM xla_events xe,
xla.xla_transaction_entities xlt
WHERE xe.entity_id = xlt.entity_id
AND xe.application_id = xlt.application_id
AND xlt.source_application_id = 140
AND xlt.entity_code = 'TRANSACTIONS'
AND xe.event_status_code <> 'P'
AND NOT EXISTS (SELECT fth.transaction_header_id
FROM fa_transaction_headers fth
WHERE fth.transaction_header_id = xlt.source_id_int_1);

b. Create Backup tables

CREATE TABLE xla_transaction_entities_bk
AS SELECT * FROM xla_transaction_entities xte
WHERE xte.application_id = 140
AND EXISTS (
SELECT xe.entity_id
FROM xla_events xe,fa_orphan_events oe
WHERE xe.event_id = oe.event_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = 140);

CREATE TABLE xla_events_bk
AS SELECT * FROM xla_events xe
WHERE EXISTS (
SELECT oe.event_id
FROM fa_orphan_events oe
WHERE xe.event_id = oe.event_id)
AND xe.application_id = 140;


c. Delete the unwanted orphan records



DELETE FROM xla_transaction_entities xte
WHERE xte.application_id = 140
AND EXISTS (
SELECT oe.entity_id
FROM fa_orphan_events oe
WHERE oe.entity_id = xte.entity_id);

DELETE FROM xla_events xe
WHERE xe.application_id = 140
AND EXISTS (
SELECT oe.event_id
FROM fa_orphan_events oe
WHERE xe.event_id = oe.event_id);

COMMIT;

3. If you are satisfied with the results, issue a commit.

4. Confirm that the data is corrected when viewed in the Oracle Applications.

You can do this by running Sub-ledger Period Close Exception Report.

5. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.


6)再次執行Create Accounting請求，並確認錯誤訊息解除。