CSTPAVCP.calc_average_cost ORA-1426 Numeric Overflow - Error in Actual Cost worker

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 :

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

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.

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.