Post To WIP fails with Error 'REVMGR-20669:Error inserting matching records'

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

When attempting to post transactions into WIP, the following error is generated: Error 'REVMGR-20669:Error inserting matching records. Because of the Post to WIP failure, transactions do not get posted.

Steps to Reproduce:

Login to PPM as a Financial user
Go to Home->Post to WIP
Click the 'Apply' button

Expected Result: The transactions should be posted without this error message.

Actual Result: Post To WIP fails with Error 'REVMGR-20669:Error inserting matching records'.

Environment

Release: All Supported Releases
Component: PPMFNM

Cause

This issue can be caused by orphaned records in the PPA_WIP or PPA_WIP_VALUES tables or an incorrect ratio of records. This can happen in a Microsoft SQL Server environment if the 'Post Transactions to Financials' job is interrupted due to Database problems such as out of space, transaction logs filling up or the job itself is interrupted. The job creates the header record and may be interrupted during the time that it needs to create the 5 matching records.

This can also happen when xogging in records and can also affect PPA_TRANSCONTROL and PPA_TRANSCONTROL_VALUES.

The error may also be returned if the ppa_transconrol transno is already in WIP. Somehow the sequences got off between these 2 tables.

Resolution

Run the following queries to check for orphans in the tables or incorrect ratio of records (PPM SaaS customers, please contact CA Technical Support for assistance)

Orphan PPA_WIP record: The following query will show any record that is in PPA_WIP table, but no matching records in the PPA_WIP_VALUES table. No results indicate no problem.

select wip.*
from ppa_wip wip
where wip.transno not in (select transno from ppa_wip_values)

Incorrect Ratio of Records: Here is a query that shows there might be records in the PPA_WIP_VALUES table, but not all 5 records in the table.

For the application to function properly, there must be a 1:5 ratio between PPA_WIP and PPA_WIP_VALUES tables. No results indicate no problem.

select w.transno, count(*)
from ppa_wip w, ppa_wip_values v
where w.transno = v.transno
group by w.transno
having count(*) <> 5

Orphan PPA_WIP_VALUES records: The following query will show any records that are in the PPA_WIP_VALUES table, but no matching header record in the PPA_WIP_VALUES table. No results indicate no problem.

select wipval.*
from ppa_wip_values wipval
where wipval.transno not in (select transno from ppa_wip)

PPA_TRANSCONTROL Queries

The same queries can be used however substitue PPA_WIP and PPA_WIP_VALUES with PPA_TRANSCONTROL and PPA_TRANSCONTROL_VALUES

Orphan PPA_TRANSCONTROL record: The following query will show any record that is in PPA_WIP table, but no matching records in the PPA_WIP_VALUES table. No results indicate no problem.

select wip.*
from ppa_transcontrol wip
where wip.transno not in (select transno from ppa_transcontrol_values)

If the issue arises again, review the data and check for the 1:5 record ratio to determine if there is a data corruption issue.

If the above sequences and values all match check for the transno issue.

select * from ppa_wip where externaltransno in

(select transno from ppa_transcontrol)

If this returns values then the sequence on ppa_transcontrol will need to be updated

Contact Broadcom Support for assistance in resolving the data corruption issue.

Additional Information

For more details on WIP (Work-in-Progress) see Manage Work-in-Progress Transactions