問題記錄_CST_原物料異動(錯誤)

1)於「成本管理用戶」職責->檢視異動->原物料異動。

2)彈出「搜尋原物料異動」視窗->搜尋條件：異動日期(依期間)、來源型態(例：Job or Schedule)、來源(例：工單編號)、計算成本(錯誤)->搜尋。

3)彈出「原物料異動」視窗->列出搜尋條件內的資料->頁籤「原因，參考」->錯誤代碼、錯誤說明欄位。

4)欄位Error Explanation：CSTPACIN.COST_INV_TXN:CSTPAVCP.compute_actual_cost (40): ORA-00001: 違反必須為唯一的限制條件 (INV.MTL_CST_ACTUAL_COST_DETAILS_U1)。

5)以下是oracle support所提供的解決方案：

Comments

--------

Please perform the below action plan in TEST instance only:

Action 1. Backup mtl_cst_actual_cost_details table.

Sql : Create table mcacd_backup as (Select * from mtl_cst_actual_cost_details
where transaction_id in (select transaction_id from mtl_material_transactions where costed_flag in ('N','E')
and transaction_id in (select transaction_id from mtl_cst_actual_cost_details)
and transaction_id not in (select transaction_id from mtl_transaction_accounts)))


Action 2. Backup mtl_material_transactions table.

Sql. : Create table MMT as (Select * from mtl_material_transactions where costed_flag in ('N','E')
and transaction_id in (select transaction_id from mtl_cst_actual_cost_details)
and transaction_id not in (select transaction_id from mtl_transaction_accounts));


Action 3. Delete the records from mtl_cst_actual_cost_details which have corresponding records in mtl_material_transactions table with costed_flag in ('N',' E') and no corresponding records in mtl_transaction_accounts

Sql : Delete from mtl_cst_actual_cost_details
Where transaction_id IN
(Select transaction_id from mtl_material_transactions where costed_flag in ('N','E')
and transaction_id in (select transaction_id from mtl_cst_actual_cost_details)
and transaction_id not in (select transaction_id from mtl_transaction_accounts));

Action 4 : Cancel the Cost Manager:
System Administrator --> Requests --> View --> Go to Specific Request and in the Name enter 'Cost Manager'.
From here cancel the Cost Manager that is Pending Scheduled.


Action 5. Re-submitt the transactions:
Update mtl_material_transactions
Set costed_flag = 'N',
transaction_group_id = null,
error_code = null,
error_explanation = null
where costed_flag in ('N','E');

Commit ;


Action 6 . Launch the Cost Manager
Inventory --> Setup --> Transactions --> Interface Managers --> Tools (Menu Bar) --> Launch Manager-->Submit
(No scheduling for the Cost Manager should be done in the Request Form)

6)以下是處理後的feedback：

Action 1. Backup mtl_cst_actual_cost_details table.

Sql : Create table mcacd_backup as (Select * from mtl_cst_actual_cost_details
where transaction_id in (select transaction_id from mtl_material_transactions where costed_flag in ('N','E')
and transaction_id in (select transaction_id from mtl_cst_actual_cost_details)
and transaction_id not in (select transaction_id from mtl_transaction_accounts)))

>>OK

Action 2. Backup mtl_material_transactions table.

Sql. : Create table MMT_BK as (Select * from mtl_material_transactions where costed_flag in ('N','E')
and transaction_id in (select transaction_id from mtl_cst_actual_cost_details)
and transaction_id not in (select transaction_id from mtl_transaction_accounts))

>>OK


Action 3. Delete the records from mtl_cst_actual_cost_details which have corresponding records in mtl_material_transactions table with costed_flag in ('N',' E') and no corresponding records in mtl_transaction_accounts

Sql : Delete from mtl_cst_actual_cost_details
Where transaction_id IN
(Select transaction_id from mtl_material_transactions where costed_flag in ('N','E')
and transaction_id in (select transaction_id from mtl_cst_actual_cost_details)
and transaction_id not in (select transaction_id from mtl_transaction_accounts));

>>OK


Action 4 : Cancel the Cost Manager:
System Administrator --> Requests --> View --> Go to Specific Request and in the Name enter 'Cost Manager'.
From here cancel the Cost Manager that is Pending Scheduled.

>>OK

Action 5. Re-submitt the transactions:
Update mtl_material_transactions
Set costed_flag = 'N',
transaction_group_id = null,
error_code = null,
error_explanation = null
where costed_flag in ('N','E');

Commit ;

>>OK

Action 6 . Launch the Cost Manager
Inventory --> Setup --> Transactions --> Interface Managers --> Tools (Menu Bar) --> Launch Manager-->Submit
(No scheduling for the Cost Manager should be done in the Request Form)

>>Please check logfile output0315.txt and 01.jpg