OPM Cost Update Fails With ORA-1 On GL_ITEM_CST_U1

OPM Cost Update concurrent program (GMCCUPD) is failing with:

ORA-00001: unique constraint (GMF.GL_ITEM_CST_U1) violated
Failed while inserting rows into gl_item_cst table.

Cause

When you run a 'Final' Cost Update, this selects rows from CM_CMPT_DTL
where ROLLOVER_IND is zero, sets this value to '1', and inserts rows
into GL_ITEM_CST with FINAL_FLAG also set to '1'.

In this case there were rows in CM_CMPT_DTL for the relevant Calendar
and Period where ROLLOVER_IND was still set to zero, but where the
corresponding GL_ITEM_CST rows had FINAL_FLAG = '1'.

When Cost Update tried to re-process the CM_CMPT_DTL rows, it needed to
insert another 'FINAL_FLAG = 1' row into GL_ITEM_CST, and this was the
duplicate.

This situation should not arise - I can only think that a 'Final' Cost
Update was run in error, then someone went in via a back-end tool and
updated CM_CMPT_DTL.ROLLOVER_IND to zero without realising that they
would also need to do something with GL_ITEM_CST (and GL_ITEM_DTL).

OPM Cost Update Fails With ORA-1 On GL_ITEM_CST_U1

Solution

If this data situation arises, then it will be necessary to update the
OPM Financial data via SQL*Plus or similar.

Please engage the assistance of Oracle Applications Support before
attempting this.

The following script will show if you have any occurrences of this
problem:

select distinct c.rollover_ind, g.final_flag
from cm_cmpt_dtl c, gl_item_cst g, gl_item_dtl d
where g.itemcost_id = d.itemcost_id
and d.cost_cmpntcls_id = c.cost_cmpntcls_id
and c.item_id = g.item_id
and c.whse_code = g.whse_code
and c.calendar_code = g.calendar_code
and c.period_code = g.period_code
and c.cost_mthd_code = g.cost_mthd_code;

Note that the only problem combination is zero in 'ROLLOVER_IND'
and '1' in 'FINAL_FLAG'.