Rem
Rem
Rem bug_9488925.sql
Rem
Rem
Rem    NAME
Rem      bug_9488925.sql - Report on or fix issues found in bug 9488925 
Rem
Rem    DESCRIPTION
Rem      See description block for each issue below
Rem
Rem    
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bjeffrie    10/03/08 - Created for 10.2.0.5, original script is bug7435518.sql	
Rem    swzhou      09/01/09 - Modified: update condition to check duplicated LocationUsages.
Rem    yuanzha     03/18/01 - Modified: update for 11.2.0.2
Rem
Rem How to apply the script:
Rem 1) If there are any OWB products running, exit from all of them.
Rem 2) Make a backup of the repository
Rem 3) Log in to sqlplus as the repository owner(OWBSYS) and execute bug_9488925.sql script
Rem The script will prompt for execution options (see below)
Rem     SQL> @bug_9488925
Rem     Report Only? (Y/N=Report+Fix):[N]
Rem     Test Mode? (Y=rollback/N=commit):[Y] N
Rem     Warning: Turning show key info on could result in extremely large output volume
Rem          (Tip: Running in report only mode with row info set to FALSE will allow you to determine row counts first.)
Rem    Show detail info for found rows? (Y=show/N=hide):[N]

Rem     Review above entries and press
Rem      <ENTER> to continue or Ctrl-C to cancel
Rem     Start bug_9488925 Customer Critical Issues Repository Cleaning ...
Rem     Note: Script execution could take several hours.

Rem 4) Examine bug_9488925_LOG.TXT generated and save to another copy in case you need to re-run the patch script

Rem 5) [Optional] You can re-run the script selecting report-only mode to verify all found issues were resolved.


set serveroutput on size 999999
set line 300
 
ACCEPT V_REPORTONLY DEFAULT 'N' PROMPT 'Report Only? (Y/N=Report+Fix):[N] '
ACCEPT V_TESTING    DEFAULT 'Y' PROMPT 'Test Mode? (Y=rollback/N=commit):[Y] '
PROMPT Warning: Turning show key info on could result in extremely large output volume
PROMPT .       (Tip: Running in report only mode with row info set to FALSE will allow you to determine row counts first.)
ACCEPT V_SHOWROWS   DEFAULT 'N' PROMPT 'Show detail info for found rows? (Y=show/N=hide):[N] '
PROMPT
PROMPT Review above entries and press
ACCEPT V_DUMMY PROMPT ' <ENTER> to continue or Ctrl-C to cancel'

PROMPT Start bug_9488925 Customer Critical Issues Repository Cleaning ...

SPOOL bug_9488925_LOG.TXT

set role OWB_USER;

CREATE OR REPLACE TYPE tmp_IdSet IS TABLE OF NUMBER(9,0);    -- type for element id sets
/


DECLARE -- NOTE: PL/SQL error message line numbers are offset from here
  SCRIPTID CONSTANT VARCHAR2(30) := 'bug_9488925'; -- For output logging
  EOL      CONSTANT VARCHAR2(01) := CHR(10); -- new line
  OUTINITED         BOOLEAN      := FALSE; -- Flag indicating output has been enabled
  REPORTONLY        BOOLEAN      := FALSE; -- Report Only? (TRUE/FALSE=Report+Fix)
  TESTING           BOOLEAN      := TRUE;  -- Test Mode? (TRUE=rollback/FALSE=commit)
  SHOWROWS          BOOLEAN      := FALSE; -- Show detail info for found rows? (TRUE=show/FALSE=hide)

-- **************************************************************************
-- **************************************************************************
--                      Useful functions/procedures
-- **************************************************************************
-- **************************************************************************

-- ==========================================================================
-- put message to output (uses DBMS_OUTPUT)
  PROCEDURE putMsg(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN

    IF NOT OUTINITED THEN
      OUTINITED := TRUE;
      DBMS_OUTPUT.ENABLE(200000);
      putMsg('DBMS_OUTPUT Enabled.' || EOL);
    END IF;

    DBMS_OUTPUT.put_line(SYSTIMESTAMP || ' ' || SCRIPTID || ': ' || msg); -- adds eol + moves to output
    -- use .put(x); and .new_line; to control seperately
  END putMsg;

-- ==========================================================================
-- put nicely formatted count info indented to level 2 to output (uses putMsg)
  PROCEDURE putCount(aCount NUMBER, aDesc VARCHAR2) IS
-- ==========================================================================
  BEGIN
    putMsg('    ' || LPAD(aCount,7,' ' ) || ' ' || aDesc);
  END putCount;

-- ==========================================================================
-- put detail message indented under putCount to output (uses putMsg)
  PROCEDURE putDetail(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    -- putCount uses 4+7+1 and we want one more level so +2 = 14
    putMsg('              ' || msg);
  END putDetail;

-- ==========================================================================
-- return id of object's parent
 FUNCTION parentIdOf(cId IN NUMBER)
-- ==========================================================================
  RETURN NUMBER IS
  pId NUMBER(9) := 0;
  BEGIN
    BEGIN -- Exception block
      SELECT ATTRIBUTEGROUPOWNER INTO pId
      FROM   CMPMapAttributeGroup_v
      WHERE  ELEMENTID=cId;

      RETURN pId;

    EXCEPTION WHEN OTHERS THEN
      BEGIN -- Exception block
        SELECT ATTRIBUTEGROUP INTO pId
        FROM   CMPMapAttribute_v
        WHERE  ELEMENTID=cId;

        RETURN pId;

      EXCEPTION WHEN OTHERS THEN
        BEGIN
          SELECT PROPERTYOWNER INTO pId
          FROM   CMPPropertyValue_v
          WHERE  ELEMENTID=cId;

          RETURN pId;

        EXCEPTION WHEN OTHERS THEN
          BEGIN
            SELECT OWNINGFOLDER INTO pId
            FROM   FirstClassObject_v
            WHERE  ELEMENTID=cId;

            RETURN pId;

          EXCEPTION WHEN OTHERS THEN
            BEGIN
              SELECT FIRSTCLASSOBJECT INTO pId
              FROM   SecondClassObject_v
              WHERE  ELEMENTID=cId;

              RETURN pId;

            EXCEPTION WHEN OTHERS THEN
              RETURN -1; -- Exception
            END;
          END;
        END;
      END;
    END;
  END parentIdOf;

-- ==========================================================================
-- return fully qualified context string describing the object
 FUNCTION fqContext(cId IN NUMBER)
-- ==========================================================================
  RETURN VARCHAR2 IS
  cClass VARCHAR2(100)  := '';
  cLName VARCHAR2(100)  := '';
  cName  VARCHAR2(30)   := '';
  cCtx   VARCHAR2(1000) := '';
  pId    NUMBER(9)      := -1;
  pCtx   VARCHAR2(4000) := '';
 BEGIN
   BEGIN
     SELECT CLASSNAME, NAME, LOGICALNAME
       INTO cClass, cName, cLName
       FROM CMPElement_v
       WHERE ELEMENTID=cId;
     IF cClass = '' OR cClass IS NULL THEN
       RETURN '';
     END IF;

     cCtx := '(' || cClass || '#' || cId || ')' || NVL(cLName,'null');
     IF NOT NVL(cLName,'null') = NVL(cName,'null') THEN
       cCtx := cCtx || '[' || NVL(cName,'null') || ']';
     END IF;

     pId := parentIdOf(cId);

     IF( pId > 0 ) THEN
       pCtx := fqContext(pId);
     END IF;

     IF( pCtx = '' OR pCtx IS NULL) THEN
       RETURN cCtx;
     ELSE
       RETURN pCtx || '.' || cCtx;
     END IF;

   EXCEPTION WHEN OTHERS THEN
     RETURN '';
   END;
 END fqContext;



-- **************************************************************************
-- **************************************************************************
--                      Start of 'real' processing
-- **************************************************************************
-- **************************************************************************

BEGIN
  IF '&V_REPORTONLY' = 'N' THEN -- DEFAULT 'N' PROMPT 'Report Only? (Y/N=Report+Fix):[N] '
    REPORTONLY := FALSE;
  ELSE
    REPORTONLY := TRUE;
  END IF;
  IF '&V_TESTING' = 'N' THEN -- DEFAULT 'Y' PROMPT 'Test Mode? (Y=rollback/N=commit):[Y] '
    TESTING := FALSE;
  ELSE
    TESTING := TRUE;
  END IF;
  IF '&V_SHOWROWS' = 'Y' THEN -- DEFAULT 'N' PROMPT 'Show detail info for found rows? (Y=show/N=hide):[N] '
    SHOWROWS := TRUE;
  ELSE
    SHOWROWS := FALSE;
  END IF;
  putMsg('Starting (Report Only = ' || '&V_REPORTONLY' || ', Testing = ' || '&V_TESTING' ||
         ', Show Row Detail = ' || '&V_SHOWROWS' || ') ...' || EOL); -- add blank line after
  IF SHOWROWS THEN
    putMsg('Note: Object info in the details sections includes only the primary (at issue) rows.');
    putMsg('     The associated children (if any) do not have detail information produced. The');
    putMsg('     row information is generally of the form:');
    putMsg('     (<className>#<elementId>)<logicalName>[<physicalName>] (physicalName only shown if different)');
    putMsg('     for each object. Where used, addditional attributes will be labelled. E.g., ');
    putMsg('     (<className>#<elementId>)<logicalName>/position=<position>' || EOL);
  END IF;
 
  FOR x in (select workspaceid,name from CMPWorkspace_v where workspaceid != 1) LOOP -- Added by YUANZHA
    owb_workspace_manager.set_workspace(x.workspaceid); -- set current workspace
    putMsg('Begin processing workspace ' || x.name || ' ...');
-- ***************************************************************************
-- Issue: Orphaned PhysicalObjects
--   Delete PhysicalObjects referencing NamedConfigurations that do not exist
--   as well as their 'children', 'grand-children', etc.
--
-- Test Scenario:
--UNKNOWN
-- ***************************************************************************
  DECLARE
    aCount  NUMBER;
    pDesc   VARCHAR2(100) := 'Orphaned PhysicalObjects'; -- parent
    pIds    tmp_IdSet; -- selected set of parent elementIds
    c1Desc  VARCHAR2(100) := 'Owned SecondClassOjbects'; -- child
    c1Ids   tmp_IdSet;  -- all owned sco elementIds
    l_str VARCHAR2(4000);
  BEGIN
    putMsg(' Issue: Orphaned PhysicalObjects...');
    putMsg('   FOUND:');
    aCount := 0;

    SELECT ELEMENTID BULK COLLECT
      INTO pIds
      FROM CMPPhysicalObject_v
      WHERE NAMEDCONFIGURATION NOT IN (
        SELECT ELEMENTID
          FROM CMPNamedConfiguration_v
      ) OR NAMEDCONFIGURATION IS NULL
      OR PLATFORMCONFIGURATION NOT IN (
         SELECT ELEMENTID 
         FROM CMPPLATFORMCONFIGURATION_V 
      ) OR PLATFORMCONFIGURATION IS NULL;
    putCount(pIds.COUNT, pDesc);

    IF pIds.COUNT > 0 AND SHOWROWS THEN
      putDetail('Details (Named Configuration does not exist)');
      putDetail('--------------------------------------------');
      FOR i IN pIds.FIRST .. pIds.LAST
      LOOP
        DECLARE
          details VARCHAR2(100) := '';
        BEGIN
          SELECT '/namedConfiguration=' || NVL2(NAMEDCONFIGURATION,TRIM(TO_CHAR(NAMEDCONFIGURATION)),'null')
            INTO details
            FROM CMPPhysicalObject_V
            WHERE ELEMENTID=pIds(i);
          putDetail(fqContext(pIds(i)) || details);
        END;
      END LOOP;
    END IF;

    SELECT ELEMENTID BULK COLLECT
      INTO c1Ids
      FROM SecondClassObject_v where FIRSTCLASSOBJECT MEMBER OF pIds ;
    putCount(c1Ids.COUNT, c1Desc);

    IF pIds.COUNT > 0 AND NOT REPORTONLY THEN
      BEGIN
        putMsg('   DELETED:');
      
        FOR c in (select distinct CLASSNAME from SecondClassObject_v where FIRSTCLASSOBJECT  MEMBER OF pIds and CLASSNAME is not null) LOOP
          l_str := 'DELETE FROM ' || c.CLASSNAME || '_v WHERE ELEMENTID MEMBER OF :1  RETURNING COUNT(ELEMENTID) INTO :2';
          --putMsg('   l_str:' || l_str);
          EXECUTE IMMEDIATE l_str using c1Ids returning into aCount ;
          putCount(aCount, c.CLASSNAME);
        END LOOP; 
     
        DELETE
          FROM CMPPhysicalObject_v
          WHERE ELEMENTID MEMBER OF pIds
          RETURNING COUNT(ELEMENTID) INTO aCount
        ;
        putCount(aCount, pDesc);

        IF TESTING THEN
          BEGIN
            ROLLBACK;
          putMsg(' *ROLLBACK*');
          END;
        ELSE -- I.E., NOT TESTING
          BEGIN
            COMMIT;
            putMsg(' **COMMIT**');
          END;
        END IF; -- TESTING
      END;
    END IF; -- NOT REPORTONLY
    putMsg(' Issue processing completed.' || EOL); -- blank line after
  END; -- Issue: Orphaned PhysicalObjects

-- ***************************************************************************
-- Issue: Duplicate PhysicalObjects
--   Delete all but most recent duplicate PhysicalObjects
--   as well as their 'children', 'grand-children', etc.
--
--   PhysicalObject as FirstClassObject may have children 
--
-- Test Scenario:
--   UNKNOWN
-- ***************************************************************************
  DECLARE
    aCount  NUMBER;
    pDesc   VARCHAR2(100) := 'Duplicate PhysicalObjects'; -- parent
    pIds    tmp_IdSet; -- selected set of parent elementIds
    c1Desc  VARCHAR2(100) := 'Owned SecondClassOjbects'; -- sco children
    c1Ids   tmp_IdSet;
    l_str VARCHAR2(4000);
  BEGIN
    putMsg(' Issue: Duplicate PhysicalObjects...');
    putMsg('   FOUND:');
    aCount := 0;

    -- Note that AGGREGATESIGNATURE for all of the duplicates is the same (?)
    -- so use element id to determine 'most recent'
    SELECT ELEMENTID BULK COLLECT
      INTO pIds
      FROM CMPPhysicalObject_v po
      WHERE po.ELEMENTID < (
        SELECT max(ELEMENTID)
          FROM CMPPhysicalObject_v poKeep
          WHERE po.LOGICALOBJECT=poKeep.LOGICALOBJECT
            AND NVL(po.namedconfiguration,0)=NVL(poKeep.namedconfiguration,0)
            And NVL(po.platformconfiguration,0)=NVL(poKeep.platformconfiguration,0)
        );
    putCount(pIds.COUNT, pDesc);

    IF pIds.COUNT > 0 AND SHOWROWS THEN
      putDetail('Details (Match on LogicalObject + NamedConfiguration + PlatformConfiguration)');
      putDetail('-----------------------------------------------------');
      FOR i IN pIds.FIRST .. pIds.LAST
      LOOP
        DECLARE
          details VARCHAR2(100) := '';
        BEGIN
          SELECT '/namedConfiguration=' || NVL2(NAMEDCONFIGURATION,TRIM(TO_CHAR(NAMEDCONFIGURATION)),'null')
            INTO details
            FROM CMPPhysicalObject_V
            WHERE ELEMENTID=pIds(i);
          putDetail(fqContext(pIds(i)) || details);
        END;
      END LOOP;
    END IF;

    SELECT ELEMENTID BULK COLLECT
      INTO c1Ids
      FROM SecondClassObject_v where FIRSTCLASSOBJECT MEMBER OF pIds ;
    putCount(c1Ids.COUNT, c1Desc);

    IF pIds.COUNT > 0 AND NOT REPORTONLY THEN
     BEGIN
        putMsg('   DELETED:');
        
        FOR c in (select distinct CLASSNAME from SecondClassObject_v where FIRSTCLASSOBJECT  MEMBER OF pIds and CLASSNAME is not null) LOOP
          l_str := 'DELETE FROM ' || c.CLASSNAME || '_v WHERE ELEMENTID MEMBER OF :1  RETURNING COUNT(ELEMENTID) INTO :2';
          --putMsg('   l_str:' || l_str);
          EXECUTE IMMEDIATE l_str using c1Ids returning into aCount ;
          putCount(aCount, c.CLASSNAME);
        END LOOP;

        DELETE
          FROM CMPPhysicalObject_v
          WHERE ELEMENTID MEMBER OF pIds
          RETURNING COUNT(ELEMENTID) INTO aCount;
        putCount(aCount, pDesc);

        IF TESTING THEN
          BEGIN
            ROLLBACK;
          putMsg(' *ROLLBACK*');
          END;
        ELSE -- I.E., NOT TESTING
          BEGIN
            COMMIT;
            putMsg(' **COMMIT**');
          END;
        END IF; -- TESTING
      END;
    END IF; -- NOT REPORTONLY
    putMsg( ' Issue processing completed.' || EOL); -- blank line after
  END; -- Issue: Duplicate PhysicalObjects

-- ***************************************************************************
-- Issue: Orphaned LocationUsages
--   Delete LocationUsages referencing Locations that do not exist
--   as well as their 'children', 'grand-children', etc.
--
--   No children.
--
-- Test Scenario:
--   UNKNOWN
-- ***************************************************************************
  DECLARE
    aCount NUMBER;
    pDesc  VARCHAR2(100) := 'Orphaned LocationUsages'; -- parent
    pIds   tmp_IdSet; -- selected set of parent elementIds
  BEGIN
    putMsg(' Issue: Orphaned LocationUsages...');
    putMsg('   FOUND:');
    aCount := 0;

    SELECT ELEMENTID BULK COLLECT
      INTO pIds
      FROM CMPLocationUsage_v
      WHERE LOCATION NOT IN (
        SELECT ELEMENTID
          FROM CMPLocation_v
      ) OR LOCATION IS NULL
    ;
    putCount(pIds.COUNT, pDesc);

    IF pIds.COUNT > 0 AND SHOWROWS THEN
      putDetail('Details (Location does not exist)');
      putDetail('---------------------------------');
      FOR i IN pIds.FIRST .. pIds.LAST
      LOOP
        DECLARE
          details VARCHAR2(100) := '';
        BEGIN
          SELECT '/location=' || NVL2(LOCATION,TRIM(TO_CHAR(LOCATION)),'null')
            INTO details
            FROM CMPLocationUsage_V
            WHERE ELEMENTID=pIds(i);
          putDetail(fqContext(pIds(i)) || details);
        END;
      END LOOP;
    END IF;

    IF pIds.COUNT > 0 AND NOT REPORTONLY THEN
      BEGIN
        putMsg('   DELETED:');

        DELETE
          FROM CMPLocationUsage_v
          WHERE ELEMENTID MEMBER OF pIds
          RETURNING COUNT(ELEMENTID) INTO aCount
        ;
        putCount(aCount, pDesc);

        IF TESTING THEN
          BEGIN
            ROLLBACK;
          putMsg(' *ROLLBACK*');
          END;
        ELSE -- I.E., NOT TESTING
          BEGIN
            COMMIT;
            putMsg(' **COMMIT**');
          END;
        END IF; -- TESTING
      END;
    END IF; -- NOT REPORTONLY
    putMsg(' Issue processing completed.' || EOL); -- blank line after
  END; -- Issue: Orphaned LocationUsages

-- ***************************************************************************
-- Issue: Duplicate LocationUsages
--   Delete all but latest duplicate LocationUsages
--   as well as their 'children', 'grand-children', etc.
--
--   No children.
--
-- Test Scenario:
--   UNKNOWN
-- ***************************************************************************
  DECLARE
    aCount NUMBER;
    pDesc  VARCHAR2(100) := 'Duplicate LocationUsages'; -- parent
    pIds   tmp_IdSet; -- selected set of parent elementIds
  BEGIN
    putMsg(' Issue: Duplicate LocationUsages...');
    putMsg('   FOUND:');
    aCount := 0;

    SELECT ELEMENTID BULK COLLECT
      INTO pIds
      FROM CMPLocationUsage_v lu
      WHERE lu.AGGREGATEDSIGNATURE < (
        SELECT MAX(AGGREGATEDSIGNATURE)
          FROM CMPLocationUsage_v luKeep
         WHERE NVL(lu.NAME,'null')=NVL(luKeep.NAME,'null')
           AND lu.DEPLOYMENTDEFAULT=luKeep.DEPLOYMENTDEFAULT
           AND lu.METADATASOURCEFLAG=luKeep.METADATASOURCEFLAG
           AND lu.LOCATION=luKeep.LOCATION  -- make sure LocationUsage refer to same location (swzhou @ 090901 for bug 8478615)
           AND lu.INSTALLEDMODULE=luKeep.INSTALLEDMODULE  -- make sure LocationUsage refer to same InstalledModule (swzhou @ 090901 for bug 8478615)
        )
    ;
    putCount(pIds.COUNT, pDesc);

    IF pIds.COUNT > 0 AND SHOWROWS THEN
      putDetail('Details (Match on name, deploymentDefault, metaDataSourceFlag,location, installedmodule - only "extra" rows shown)');
      putDetail('----------------------------------------------------------------------------------------');
      FOR i IN pIds.FIRST .. pIds.LAST
      LOOP
        DECLARE
          details VARCHAR2(100) := '';
        BEGIN
          SELECT '/deploymentDefault='  || TRIM(TO_CHAR(DEPLOYMENTDEFAULT)) ||
                 '/metaDataSourceFlag=' || TRIM(TO_CHAR(METADATASOURCEFLAG))
            INTO details
            FROM CMPLocationUsage_V
            WHERE ELEMENTID=pIds(i);
          putDetail(fqContext(pIds(i)) || details);
        END;
      END LOOP;
    END IF;

    IF pIds.COUNT > 0 AND NOT REPORTONLY THEN
      BEGIN
        putMsg('  DELETED:');

        DELETE
          FROM CMPLocationUsage_v
          WHERE ELEMENTID MEMBER OF pIds
          RETURNING COUNT(ELEMENTID) INTO aCount
        ;
        putCount(aCount, pDesc);

        IF TESTING THEN
          BEGIN
            ROLLBACK;
          putMsg('*ROLLBACK*');
          END;
        ELSE -- I.E., NOT TESTING
          BEGIN
            COMMIT;
            putMsg('**COMMIT**');
          END;
        END IF; -- TESTING
      END;
    END IF; -- NOT REPORTONLY
    putMsg(' Issue processing completed.' || EOL); -- blank line after
  END; -- Issue: Duplicate LocationUsages

-- ***************************************************************************
-- Issue: Deployment and Metadata location usages have same name
--   Append '_METADATA' to location usage's name where the
--   souceMetaDataLocation flag is true.
--
--   No childern.
--
-- Test Scenario:
--   UNKNOWN
-- ***************************************************************************
  DECLARE
    aCount NUMBER;
    pDesc  VARCHAR2(100) := 'Metadata LocationUsages without "_METADATA" name suffix'; -- parent
    pIds   tmp_IdSet; -- selected set of parent elementIds
  BEGIN
    putMsg(' Issue: Deployment and Metadata location usages have same name...');
    putMsg('   FOUND:');
    aCount := 0;

    SELECT ELEMENTID BULK COLLECT
      INTO pIds
      FROM CMPLocationUsage_v lu
      WHERE METADATASOURCEFLAG='1'
        AND NOT NAME LIKE '%_METADATA'
    ;
    putCount(pIds.COUNT, pDesc);

    IF pIds.COUNT > 0 AND SHOWROWS THEN
      putDetail('Details (NOT NAME LIKE "%_METADATA")');
      putDetail('------------------------------------');
      FOR i IN pIds.FIRST .. pIds.LAST
      LOOP
        DECLARE
          details VARCHAR2(100) := '';
        BEGIN
          SELECT '/deploymentDefault='  || TRIM(TO_CHAR(DEPLOYMENTDEFAULT)) ||
                 '/metaDataSourceFlag=' || TRIM(TO_CHAR(METADATASOURCEFLAG))
            INTO details
            FROM CMPLocationUsage_V
            WHERE ELEMENTID=pIds(i);
          putDetail(fqContext(pIds(i)) || details);
        END;
      END LOOP;
    END IF;

    IF pIds.COUNT > 0 AND NOT REPORTONLY THEN
      BEGIN
        DECLARE
          -- See oracle.wh.repos.sdk.WBRestrictedNameValidator() total max = 30
          suffix  VARCHAR2(9)  := '_METADATA';
          maxLen  NUMBER       := 21; -- (30 - 9)
          newName VARCHAR2(30);
        BEGIN
          putMsg('   UPDATED:');
          aCount := 0;

          FOR i IN pIds.FIRST .. pIds.LAST
          LOOP
            BEGIN
              SELECT NAME
                INTO newName
                FROM CMPLocationUsage_v
                WHERE ELEMENTID=pIds(i);

              IF LENGTH(newName) > maxLen THEN
                newName := SUBSTR(newName, 1, maxLen);
              END IF;

              newName := newName || suffix;

              UPDATE CMPLocationUsage_v
                SET name=newName, logicalName=newName
                WHERE ELEMENTID=pIds(i);

              aCount := aCount + 1;
            END;
          END LOOP;
          putCount(aCount, pDesc);

          IF TESTING THEN
            BEGIN
              ROLLBACK;
              putMsg(' *ROLLBACK*');
            END;
          ELSE -- I.E., NOT TESTING
            BEGIN
              COMMIT;
              putMsg(' **COMMIT**');
            END;
          END IF; -- TESTING
        END;
      END;
    END IF; -- NOT REPORTONLY

    putMsg(' Issue processing completed.' || EOL); -- blank line after
  END; -- Issue  Deployment and Metadata location usages have same name

-- ***************************************************************************
-- Issue: Orphaned ReferencePropertyValues
--   Delete Orphaned ReferencePropertyValues referencing ReferencedElements that do not exist
--   as well as their 'children', 'grand-children', etc.
--
--   ReferencePropertyValues as PROPERTYOWNER may have child PropertyValue
--   PropertyValue as PROPERTYOWNER may have child PropertyValue
--
-- Test Scenario:
--  UNKNOWN
-- ***************************************************************************
  DECLARE
    aCount NUMBER;
    pDesc  VARCHAR2(100) := 'Orphaned ReferencePropertyValues'; -- parent
    pIds   tmp_IdSet; -- selected set of parent elementIds
    c1Desc VARCHAR2(100) := 'Owned PropertyValues'; -- 1st child
    c1Ids  tmp_IdSet;
  BEGIN
    putMsg(' Issue: Orphaned ReferencePropertyValues...');
    putMsg('   FOUND:');
    aCount := 0;

    SELECT ELEMENTID BULK COLLECT
      INTO pIds
      FROM CMPReferencePropertyValue_v
      WHERE REFERENCEDELEMENT NOT IN (
        SELECT ELEMENTID
          FROM CMPElement_v
      ) OR REFERENCEDELEMENT IS NULL
    ;
    putCount(pIds.COUNT, pDesc);

    IF pIds.COUNT > 0 AND SHOWROWS THEN
      putDetail('Details (referencedElement does not exist)');
      putDetail('------------------------------------------');
      FOR i IN pIds.FIRST .. pIds.LAST
      LOOP
        DECLARE
          details  VARCHAR2(100) := '';
          details2 VARCHAR2(100) := '';
        BEGIN
          SELECT '/position=' || TRIM(TO_CHAR(POSITION))
                ,'  /description=' || TRIM(NVL(DESCRIPTION,'null')) ||
                 '/referencedElement=' || NVL2(REFERENCEDELEMENT,TRIM(TO_CHAR(REFERENCEDELEMENT)),'null')
            INTO details, details2
            FROM CMPReferencePropertyValue_V
            WHERE ELEMENTID=pIds(i);
          putDetail(fqContext(pIds(i)) || details);
          putDetail(details2);
        END;
      END LOOP;
    END IF;

    SELECT ELEMENTID BULK COLLECT
      INTO c1Ids
      FROM CMPPropertyValue_v
      START WITH PROPERTYOWNER MEMBER OF pIds
      CONNECT BY PRIOR ELEMENTID = PROPERTYOWNER
    ;
    putCount(c1Ids.COUNT, c1Desc);

    IF pIds.COUNT > 0 AND NOT REPORTONLY THEN
      BEGIN
        putMsg('   DELETED:');

        DELETE
          FROM CMPPropertyValue_v
          WHERE ELEMENTID MEMBER OF c1Ids
          RETURNING COUNT(ELEMENTID) INTO aCount
        ;
        putCount(aCount, c1Desc);

        DELETE
          FROM CMPReferencePropertyValue_v
          WHERE ELEMENTID MEMBER OF pIds
          RETURNING COUNT(ELEMENTID) INTO aCount
        ;
        putCount(aCount, pDesc);

        IF TESTING THEN
          BEGIN
            ROLLBACK;
          putMsg(' *ROLLBACK*');
          END;
        ELSE -- I.E., NOT TESTING
          BEGIN
            COMMIT;
            putMsg(' **COMMIT**');
          END;
        END IF; -- TESTING
      END;
    END IF; -- NOT REPORTONLY
    putMsg(' Issue processing completed.' || EOL); -- blank line after
  END; -- Issue: Orphaned ReferencePropertyValues

-- ***************************************************************************
-- Issue: Duplicate PropertyValues
--   Delete all but most recent duplicate PropertyValues
--   as well as their 'children', 'grand-children', etc.
--
--   PropertyValue as PROPERTYOWNER may have child PropertyValue
--
-- Test Scenario:
-- UNKNOWN
-- ***************************************************************************
  DECLARE
    aCount NUMBER;
    pDesc  VARCHAR2(100) := 'Duplicate PropertyValues'; -- parent
    pIds   tmp_IdSet; -- selected set of parent elementIds
    c1Desc VARCHAR2(100) := 'Owned PropertyValues'; -- 1st child
    c1Ids  tmp_IdSet;
  BEGIN
    putMsg(' Issue: Duplicate PropertyValues...');
    putMsg('   FOUND:');
    aCount := 0;

    SELECT ELEMENTID BULK COLLECT
      INTO pIds
      FROM CMPPropertyValue_v pv
      WHERE pv.AGGREGATEDSIGNATURE < (
        SELECT max(AGGREGATEDSIGNATURE)
          FROM CMPPropertyValue_v pvKeep
          WHERE pv.PROPERTYOWNER=pvKeep.PROPERTYOWNER
            AND pv.LOGICALNAME=pvKeep.LOGICALNAME
            AND pv.POSITION=pvKeep.POSITION
            AND NVL(pv.NAME,'null')=NVL(pvKeep.NAME,'null')
            AND NVL(pv.CLASSNAME,'null')=NVL(pvKeep.CLASSNAME,'null')
        )
    ;
    putCount(pIds.COUNT, pDesc);

    IF pIds.COUNT > 0 AND SHOWROWS THEN
      putDetail('Details (Match on owner, logical name, name, position,className)');
      putDetail('------------------------------------------------------');
      FOR i IN pIds.FIRST .. pIds.LAST
      LOOP
        DECLARE
          details VARCHAR2(100) := '';
        BEGIN
          SELECT '/position=' || TRIM(TO_CHAR(POSITION))
            INTO details
            FROM CMPPropertyValue_V
            WHERE ELEMENTID=pIds(i);
          putDetail(fqContext(pIds(i)) || details);
        END;
      END LOOP;
    END IF;

    SELECT ELEMENTID BULK COLLECT
      INTO c1Ids
      FROM CMPPropertyValue_v
      START WITH PROPERTYOWNER MEMBER OF pIds
      CONNECT BY PRIOR ELEMENTID = PROPERTYOWNER
    ;
    putCount(c1Ids.COUNT, c1Desc);

    IF pIds.COUNT > 0 AND NOT REPORTONLY THEN
      BEGIN
        putMsg('   DELETED:');

        DELETE
          FROM CMPPropertyValue_v
          WHERE ELEMENTID MEMBER OF c1Ids
          RETURNING COUNT(ELEMENTID) INTO aCount
        ;
        putCount(aCount, c1Desc);

        DELETE
          FROM CMPPropertyValue_v
          WHERE ELEMENTID MEMBER OF pIds
          RETURNING COUNT(ELEMENTID) INTO aCount
        ;
        putCount(aCount, pDesc);

        IF TESTING THEN
          BEGIN
            ROLLBACK;
          putMsg(' *ROLLBACK*');
          END;
        ELSE -- I.E., NOT TESTING
          BEGIN
            COMMIT;
            putMsg(' **COMMIT**');
          END;
        END IF; -- TESTING
      END;
    END IF; -- NOT REPORTONLY
    putMsg(' Issue processing completed.' || EOL); -- blank line after
  END; -- Issue: Duplicate PropertyValues
  
  putMsg('Workspace ' || x.name ||  ' processing completed.' || EOL);
 END LOOP; 
 
-- ...........................................................................
  putMsg('Processing completed.');
END;
/

--DROP TYPE tmp_IdSet;
--/

PROMPT End of the cleaning.
SPOOL OFF
--exit -- commented out as most typical case will be to run at least twice

-- ###########################################################################
-- bug_9488925 cleanup
-- ###########################################################################