CSTPAVCP.calc_average_cost ORA-1426 Numeric Overflow - Error in
Actual Cost worker
|
After "Completion Transaction of the Discrete
job. "Actual Cost worker" Completed with error
|
|
Error log file attached. mtl_material_transactions
table have the error line as"CSTPAVCP.calc_average_cost (30): ORA-01426:
numeric overflow"
|
Applies to:
Oracle Cost Management - Version 12.1.1 and
later
Symptoms
On R12.x in Production:
Within an organization that is using average costing method, one transaction is stuck with an error in mtl_material_transactions (MMT)
with the ERROR below. This same error MSG is showing
in the log of the Actual Cost Worker (CMCACW), such as :
Within an organization that is using average costing method, one transaction is stuck with an error in mtl_material_transactions (MMT)
with the ERROR below. This same error MSG is showing
in the log of the Actual Cost Worker (CMCACW), such as :
ERROR
COSTED_FLAG=E
ERROR_CODE=No Error
ERROR_EXPLANATION= CSTPAVCP.calc_average_cost (30): ORA-01426: numeric overflow
+
Logfile of CMCACW shows:
CSTPAVCP.calc_average_cost (30): ORA-01426: numeric overflow
Failing Transaction ID is xxxxx
ERROR_CODE=No Error
ERROR_EXPLANATION= CSTPAVCP.calc_average_cost (30): ORA-01426: numeric overflow
+
Logfile of CMCACW shows:
CSTPAVCP.calc_average_cost (30): ORA-01426: numeric overflow
Failing Transaction ID is xxxxx
STEPS
The issue can be reproduced at will with the following steps:
1. /INV/Transactions / Material Transactions
for org=VPM
TAB Reason_Reference :
Costed=Error
error_code=No Error
error_explanation=CSTPAVCP.calc_average_cost (30): ORA-1426: numeric overflow
Cause
The cause of the issue is invalid data in tables
CST_LAYER_COST_DETAILS and CST_QUANTITY_LAYERS
The "costs fields" are rounded to more than 30 characters
Bug 5855020 was logged for this specific customer to receive a datafix from Oracle
Development:
The following SQL identified the invalid data:
a) First identify the transaction in ERROR in mtl_material_transactions, with :
COSTED_FLAG=E
ERROR_CODE=No Error
ERROR_EXPLANATION=
CSTPAVCP.calc_average_cost (30): ORA-01426: numeric overflow
b) Second, for this transaction in error, find out the value of layer_id :
SELECT layer_id, inventory_item_id, organization_id,
cost_group_id
FROM cst_quantity_layers
WHERE organization_id = &org_id
AND inventory_item_id = &item_id
AND cost_group_id = &cost_group_id
.
For a Non WMS, Non project Enabled Org, the Cost Group id would be the
default cost group id of the Organization. The Item_id and Org_id values
correspond to the transaction that errs with the reported issue.
CST_LAYER_COST_DETAILS and CST_QUANTITY_LAYERS
The "costs fields" are rounded to more than 30 characters
Bug 5855020 was logged for this specific customer to receive a datafix from Oracle
Development:
The following SQL identified the invalid data:
a) First identify the transaction in ERROR in mtl_material_transactions, with :
COSTED_FLAG=E
ERROR_CODE=No Error
ERROR_EXPLANATION=
CSTPAVCP.calc_average_cost (30): ORA-01426: numeric overflow
b) Second, for this transaction in error, find out the value of layer_id :
SELECT layer_id, inventory_item_id, organization_id,
cost_group_id
FROM cst_quantity_layers
WHERE organization_id = &org_id
AND inventory_item_id = &item_id
AND cost_group_id = &cost_group_id
.
For a Non WMS, Non project Enabled Org, the Cost Group id would be the
default cost group id of the Organization. The Item_id and Org_id values
correspond to the transaction that errs with the reported issue.
Solution
To implement the solution, please execute the following steps:DATA FIX
-----------
1. Ensure to take a backup of the system before applying the recommended solution.
Such as : make backup of following tables :
- CST_QUANTITY_LAYERS and
- CST_LAYER_COST_DETAILS and
- MTL_MATERIAL_TRANSACTIONS for the errored transaction
create table mtl_material_txn_bkup
as select * from mtl_material_transactions
Where costed_flag='E';
2. Run the following scripts in a TEST environment first:
a- Cancel the Cost Manager (cf Note 373207.1)
b- Run the update scripts for the layer_id identified above :
UPDATE CST_LAYER_COST_DETAILS
SET item_cost = ROUND(item_cost, 40)
WHERE layer_id = &layer_id;
commit;
UPDATE CST_QUANTITY_LAYERS
SET item_cost = ROUND(item_cost, 40),
PL_MATERIAL = ROUND(PL_MATERIAL, 40),
TL_MATERIAL = ROUND(TL_MATERIAL, 40),
MATERIAL_COST = ROUND(MATERIAL_COST, 40),
PL_ITEM_COST = ROUND(PL_ITEM_COST, 40),
TL_ITEM_COST = ROUND(TL_ITEM_COST, 40),
UNBURDENED_COST = ROUND(UNBURDENED_COST, 40)
WHERE layer_id = &layer_id;
COMMIT;
c- Resubmit the errored transactions
update mtl_material_transactions MMT
set MMT.costed_flag = 'N',
MMT.error_code = NULL,
MMT.error_explanation = NULL,
MMT.transaction_group_id = NULL,
MMT.transaction_set_id = NULL
where MMT.organization_id = ‘&organization_id’
and MMT.costed_flag in ('N','E')
and NOT EXISTS ( SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MMT.TRANSACTION_ID = MTA.TRANSACTION_ID);
commit;
d- Re-Start the cost manager
e- wait for the worker to complete, and check the result.
f- check that the transaction is now costed with success.
3. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.
4. Root Cause
a. Download and review the readme and pre-requisites forPatch.18791666:R12.BOM.C (or higher)
b. Ensure that you have taken a backup of your system before applying the recommended patch.
c. Apply the patch in a test environment.
d. Confirm the following file versions:
CSTAVCPB.pls 120.36.12010000.23 (or higher)
cmlmcw.lpc 120.3.12010000.10 (or higher
You can use the commands like the following:
Strings -a $BOM_TOP/patch/115/sql/CSTAVCPB.pls | grep '$Header'
e. Retest the issue.
f. Migrate the solution as appropriate to other environments.
