Error "ORA-00001: unique constraint (SYSADM.PS_CM_DFLT_AVG_T4) violated" at Step CM_BUCST_UPD.ALLITMS.STEP03 when Running the 'Default Actual Cost Update' Process (CM_BUCST_UPD) After a Change a the Item Default Cost Element (Doc ID 1511292.1)

Last updated on MARCH 10, 2015

Applies to:

PeopleSoft Enterprise SCM Manufacturing - Version 9.1 to 9.1 [Release 9]
PeopleSoft Enterprise SCM Inventory - Version 9.1 to 9.1 [Release 9]
PeopleSoft Enterprise SCM Cost Management - Version 8.4 SP2 and later
Information in this document applies to any platform.
This Knowledge Document applies to Cost Management release 9.1 only.

Symptoms

Running the 'Default Actual Cost Update' process (CM_BUCST_UPD) (navigation: Cost Accounting, Item Costs, Update Costs, Update Default Actual Cost) abends with the below error when there is a change to the Business Unit Item Default Cost Element and if there was a prior Cost with the previous Default Cost Element.

SQL error. Function: SQLExec
  Error Position: 0
  Return: 805 - ORA-00001: unique constraint (SYSADM.PS_CM_DFLT_AVG_T4) violated
 
  Statement: INSERT INTO PS_CM_DFLT_AVG_T4 (PROCESS_INSTANCE, BUSINESS_UNIT, INV_ITEM_ID, COST_ELEMENT, CM_BOOK, CM_UNIT_COST, QTY_BASE, EXTENDED_AMOUNT) SELECT A.PROCESS_INSTANCE, A.BUSINESS_UNIT, A.INV_ITEM_ID, A.COST_ELEMENT, A.CM_BOOK, ((SUM (((A.CM_UNIT_COST) * (B.QTY_BASE)))) / (SUM(B.QTY_BASE))), SUM(B.QTY_BASE), ((((SUM (((A.CM_UNIT_COST) * (B.QTY_BASE)))) / (SUM(B.QTY_BASE))) ) * ( SUM(B.QTY_BASE))) FROM PS_CM_ACT_COST_T4 A, PS_TRANSACTION_INV B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND
  A.INV_ITEM_ID = B.INV_ITEM_ID AND A.DT_TIMESTAMP = B.DT_TIMESTAMP AND A.SEQ_NBR = B.SEQ_NBR AND (A.RECEIVER_ID = B.RECEIVER_ID OR A.PRODUCTION_ID = B.PRODUCTION_ID) AND A.PROCESS_INSTANCE = :1 AND A.BUSINESS_UNIT = :2 GROUP BY A.PROCESS_INSTANCE, A.BUSINESS_UNIT, A.INV_ITEM_ID, A.COST_ELEMENT, A.CM_BOOK
 
Original Statement: %InsertSelect(CM_DFLT_AVG_T, CM_ACT_COST_T A, TRANSACTION_INV B, BUSINESS_UNIT=A.BUSINESS_UNIT, INV_ITEM_ID=A.INV_ITEM_ID, COST_ELEMENT=A.COST_ELEMENT, CM_BOOK=A.CM_BOOK, CM_UNIT_COST = %DecDiv(SUM (%DECMULT(A.CM_UNIT_COST,B.QTY_BASE)),SUM(B.QTY_BASE)), QTY_BASE = SUM(B.QTY_BASE), EXTENDED_AMOUNT = %DecMult(%DECDIV(SUM
  (%DECMULT(A.CM_UNIT_COST,B.QTY_BASE)),SUM(B.QTY_BASE)) , SUM(B.QTY_BASE) ), PROCESS_INSTANCE=A.PROCESS_INSTANCE) FROM %Table(CM_ACT_COST_T) A, %Table(TRANSACTION_INV) B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.DT_TIMESTAMP = B.DT_TIMESTAMP AND A.SEQ_NBR = B.SEQ_NBR AND (A.RECEIVER_ID = B.RECEIVER_ID OR A.PRODUCTION_ID = B.PRODUCTION_ID) AND A.PROCESS_INSTANCE = :1 AND A.BUSINESS_UNIT = :2 GROUP BY A.PROCESS_INSTANCE, A.BUSINESS_UNIT, A.INV_ITEM_ID, A.COST_ELEMENT, A.CM_BOOK
   
  SQL error in Exec. (2,280)
  CM_BUCST_UPD.AllItms.GBL.default.1900-01-01.Step03.OnExecute PCPC:487
  Statement:17
   
  Process 6844598 ABENDED at Step CM_BUCST_UPD.AllItms.Step03 (PeopleCode) --
  RC = 8 (108,524)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms