INV_INVESTMENTS.CODE and PAC_MNT_PROJECTS.PROJECT_CODE different for some project

Products

• Clarity PPM On Premise
• Clarity FedRAMP
• Clarity PPM SaaS

Issue/Introduction

An inconsistency has been identified between two core database tables. Specifically, the unique identifier for certain projects—Investment Code and Project Code—does not match across the systems as expected.

Breakdown of the Inconsistency

Table	Column Name	Role
INV_INVESTMENTS	CODE	The primary identifier in the Investment module.
PAC_MNT_PROJECTS	PROJECT_CODE	The primary identifier in the Project module.

Environment

Clarity all release 

Cause

"unknown reason"—this is often caused by manual data entry/update, a failed integration script, or a legacy system migration.

Resolution

1. Data Audit: Run a query to identify the exact number of affected records. 

   SELECT INV.ID INV_ID,PAC.ID PAC_ID,INV.CODE INV_CODE,PAC.PROJECT_CODE PAC_PRJ_CODE,
   INV.NAME PRJ_NAME,DECODE(INV.IS_ACTIVE,1,'Yes','No') INV_IS_ACTIVE,INITCAP(INV.ODF_OBJECT_CODE) INV_TYPE, 
   inv.created_date, pac.created_date, inv.created_by,pac.created_by, inv.last_updated_date, pac.last_updated_date, inv.last_updated_by,
   pac.last_updated_by
   FROM INV_INVESTMENTS INV
   JOIN PAC_MNT_PROJECTS PAC ON INV.ID=PAC.ID
   AND ((UPPER(INV.CODE)<>UPPER(PAC.PROJECT_CODE)))

2. Resolution: 
   1. Stop all the clarity services
   2. Back up the two tables using the below scripts 

      CREATE TABLE BKP_INV_INVESTMENTS
      AS 
      SELECT * FROM INV_INVESTMENTS
      
      CREATE TABLE BKP_PAC_MNT_PROJECTS
      AS
      SELECT * FROM PAC_MNT_PROJECTS

   3. Execute the update statement 

      MERGE INTO PAC_MNT_PROJECTS p
      USING inv_investments i
      ON (i.id = p.id)
      WHEN MATCHED THEN
      UPDATE SET p.project_code = i.code
      WHERE upper(i.CODE) != p.PROJECT_CODE

   4. Restart all the services and verify by running the query in Point 1