Orphaned records into CMN_SCH_JOB_RUNS table

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Orphan record in CMN_SCH_JOB_RUNS tables. The orphans are not getting created recently but due to old defect DE56070 which is fixed is 15.9.2 

Steps to Reproduce: 

1. Connect to Clarity supported version 
2. Run the below query to find the orphan 

   SELECT
       *
   FROM
       CMN_SCH_JOB_RUNS CSJR
   WHERE
       CSJR.JOB_ID NOT IN (
       SELECT
               J.ID
       FROM
               CMN_SCH_JOBS J,
               CMN_SCH_JOB_DEFINITIONS JD,
               CMN_LOOKUPS L
       WHERE
               JOB_DEFINITION_ID = JD.ID
           AND JD.JOB_TYPE = L.ID
           AND L.LOOKUP_CODE != 'REPORT'
           AND L.LOOKUP_TYPE = 'SCH_JOB_TYPE' )

Expected Results: No orphan should be found at table CMN_SCH_JOB_RUNS 

Actual Results: Table CMN_SCH_JOB_RUNS  contains Orphans 

Environment

Clarity Release 16.x 

Cause

DE154527

Resolution

In Order to fix DE154527

• Execute the below clean up script after taking backup of table CMN_SCH_JOB_RUNS

• DELETE 
  FROM
      CMN_SCH_JOB_RUNS CSJR
  WHERE
      CSJR.JOB_ID NOT IN (
      SELECT
              J.ID
      FROM
              CMN_SCH_JOBS J,
              CMN_SCH_JOB_DEFINITIONS JD,
              CMN_LOOKUPS L
      WHERE
              JOB_DEFINITION_ID = JD.ID
          AND JD.JOB_TYPE = L.ID
          AND L.LOOKUP_CODE != 'REPORT'
          AND L.LOOKUP_TYPE = 'SCH_JOB_TYPE' )