Root Cause

IMPORTANT: Back up the database before executing these queries. Ensure you enter the correct IDs when performing deletions.

#1: Check for Duplicate Entries

Run the following SQL query to identify duplicates:

SELECT PLM.ID_, PLM.VERSION_ID_, PLM.REPO_ITEM_ID_, CV.VERSION_NAME_, CVC.VERSION_NAME_, PLM.SCOPE_, RI.ITEM_TYPE_, CV.COMPONENT_ID_, CVC.COMPONENT_ID_, RI.COMPONENT_ID_, RI.COMPONENT_VERSION_ID_, CVC.ID_
FROM PAS_USER_PREFERRED_LICENSE_MAPPING AS PLM,
     PDL_COMPONENT_VERSION AS CV,
     PDL_COMPONENT_VERSION_CUSTOM AS CVC,
     PAS_REPOSITORY_ITEM RI
WHERE RI.ITEM_TYPE_ = 'CVC'
  AND RI.ID_ = PLM.REPO_ITEM_ID_
  AND CV.COMPONENT_ID_ = CVC.COMPONENT_ID_
  AND CV.VERSION_NAME_ = CVC.VERSION_NAME_
  AND RI.COMPONENT_ID_ = CVC.COMPONENT_ID_
  AND RI.COMPONENT_VERSION_ID_ = CVC.ID_
  AND EXISTS (
    SELECT 1
    FROM PAS_USER_PREFERRED_LICENSE_MAPPING AS DUP
    WHERE DUP.VERSION_ID_ = CV.ID_
      AND DUP.LICENSE_ID_ = PLM.LICENSE_ID_
      AND DUP.SCOPE_ = PLM.SCOPE_
  );

If this query returns rows, it confirms the presence of duplicate entries.

#2: Delete Duplicate Entries

Extract the PLM.ID_ values from the first column of the result and run the following:

DELETE FROM PAS_USER_PREFERRED_LICENSE_MAPPING
WHERE ID_ IN (IDs collected from PLM.ID_ column from previous query);

IMPORTANT: Ensure the IDs are correct before executing the delete query.

#3: Update License Mapping

1. Update VERSION_ID_ in PAS_USER_PREFERRED_LICENSE_MAPPING:
   

   UPDATE PAS_USER_PREFERRED_LICENSE_MAPPING AS PLM,
          PDL_COMPONENT_VERSION AS CV,
          PDL_COMPONENT_VERSION_CUSTOM AS CVC,
          PAS_REPOSITORY_ITEM RI
   SET PLM.VERSION_ID_ = CV.ID_
   WHERE RI.ID_ = PLM.REPO_ITEM_ID_
     AND RI.ITEM_TYPE_ = 'CVC'
     AND CV.COMPONENT_ID_ = CVC.COMPONENT_ID_
     AND CV.VERSION_NAME_ = CVC.VERSION_NAME_
     AND RI.COMPONENT_ID_ = CVC.COMPONENT_ID_
     AND RI.COMPONENT_VERSION_ID_ = CVC.ID_;

2. Update COMPONENT_VERSION_ID_ and ITEM_TYPE_ in PAS_REPOSITORY_ITEM:
   

   UPDATE PAS_REPOSITORY_ITEM AS R,
          PDL_COMPONENT_VERSION AS CV,
          PDL_COMPONENT_VERSION_CUSTOM AS CVC
   SET R.COMPONENT_VERSION_ID_ = CV.ID_,
       R.ITEM_TYPE_ = 'CV'
   WHERE R.ITEM_TYPE_ = 'CVC'
     AND CV.COMPONENT_ID_ = CVC.COMPONENT_ID_
     AND CV.VERSION_NAME_ = CVC.VERSION_NAME_
     AND R.COMPONENT_ID_ = CVC.COMPONENT_ID_
     AND R.COMPONENT_VERSION_ID_ = CVC.ID_;

3. Clean up unused custom versions:
   

   DELETE FROM PDL_COMPONENT_VERSION_CUSTOM
   WHERE NOT EXISTS (
     SELECT 1
     FROM PAS_REPOSITORY_ITEM
     WHERE PAS_REPOSITORY_ITEM.ITEM_TYPE_ = 'CVC'
       AND PDL_COMPONENT_VERSION_CUSTOM.ID_ = PAS_REPOSITORY_ITEM.COMPONENT_VERSION_ID_
   );

IMPORTANT: These queries may take time to execute. Please wait for completion before proceeding.

After these steps, the subsequent Library Refresh job should pass.