-- fix5988620.sql.
-- This script is used with the Paris patch 10.2.0.3 release to update existing 10.2 repositories.
-- It adds the ROWID and UROWID data types to the CMPDATATYPE_V view.
-- It also adds a new property for attributes, COLUMN_USAGE, which determines whether a column is
-- used as a normal table column or a ROWID/ROWNUM pseudo-column
-- dwenberg, 4/20/2007

SET SERVEROUTPUT ON
SET LINESIZE 500

DECLARE

  SCRIPTID CONSTANT VARCHAR2(30) := 'ROWID/ROWNUM upgrade'; -- For output logging
  EOL      CONSTANT VARCHAR2(01) := CHR(10); -- new line
  TRACEON  CONSTANT BOOLEAN      := FALSE; -- Provide function level trace output
  OUTINITED         BOOLEAN      := FALSE; -- Flag indicating output has been enabled

  -- useful 'constants'
  PropertyTypeCORE    NUMBER := 0;
  PropertyTypeLOGICAL NUMBER := 1;
  PropertyTypeCONFIG  NUMBER := 2;
  PropertyTypeUSERDEF NUMBER := 3;

  -- types for lookup lists
  TYPE NameList  IS TABLE OF VARCHAR2(32);
  TYPE IdForName IS TABLE OF NUMBER INDEX BY VARCHAR2(32);

  -- some useful lookups/'translators' from xml type names to coded ids
  -- will work like functions i.e., id := propertytypeIdOf('logical');
  propertytypeIdOf IdForName; -- initialized below
  datatypeIdOf IdForName; -- initialized below

  -- will use original install user as create/update id for new records so
  -- make record to hold info
-- ==========================================================================
  TYPE AuditInfo_rec IS RECORD (
-- ==========================================================================
     CREATEDBY               CMPInstallation_v.CREATEDBY%TYPE               DEFAULT null
    ,CREATIONTIMESTAMP       CMPInstallation_v.CREATIONTIMESTAMP%TYPE       DEFAULT null
    ,UPDATEDBY               CMPInstallation_v.UPDATEDBY%TYPE               DEFAULT null
    ,UPDATETIMESTAMP         CMPInstallation_v.UPDATETIMESTAMP%TYPE         DEFAULT null
  );
  g_auditInfo AuditInfo_rec;

-- ==========================================================================
  TYPE PropertyDefinition IS RECORD (
-- ==========================================================================
     LOADINGPARENT                  PropertyDefinition_v.LOADINGPARENT%TYPE                  DEFAULT    0                 -- NUMBER(9,0)
    ,DELETIONPARENT                 PropertyDefinition_v.DELETIONPARENT%TYPE                 DEFAULT    0                 -- NUMBER(9,0)
    ,AGGREGATEDSIGNATURE            PropertyDefinition_v.AGGREGATEDSIGNATURE%TYPE            DEFAULT null                 -- VARCHAR2(255)
    ,CLASSNAME                      PropertyDefinition_v.CLASSNAME%TYPE                      DEFAULT 'PropertyDefinition' -- VARCHAR2(255)
    ,COMPLETED                      PropertyDefinition_v.COMPLETED%TYPE                      DEFAULT null                 -- CHAR(1)
    ,CREATEDBY                      PropertyDefinition_v.CREATEDBY%TYPE                      DEFAULT null                 -- VARCHAR2(40)
    ,CREATIONTIMESTAMP              PropertyDefinition_v.CREATIONTIMESTAMP%TYPE              DEFAULT null                 -- DATE
    ,CUSTOMERDELETABLE              PropertyDefinition_v.CUSTOMERDELETABLE%TYPE              DEFAULT null                 -- CHAR(1)
    ,CUSTOMEREDITABLE               PropertyDefinition_v.CUSTOMEREDITABLE%TYPE               DEFAULT null                 -- CHAR(1)
    ,CUSTOMERRENAMABLE              PropertyDefinition_v.CUSTOMERRENAMABLE%TYPE              DEFAULT null                 -- CHAR(1)
    ,DESCRIPTION                    PropertyDefinition_v.DESCRIPTION%TYPE                    DEFAULT null                 -- VARCHAR2(4000)
    ,DISABLED                       PropertyDefinition_v.DISABLED%TYPE                       DEFAULT null                 -- CHAR(1)
    ,EDITABLE                       PropertyDefinition_v.EDITABLE%TYPE                       DEFAULT null                 -- CHAR(1)
    ,ELEMENTID                      PropertyDefinition_v.ELEMENTID%TYPE                      DEFAULT    0                 -- NUMBER(9,0)
    ,FROZEN                         PropertyDefinition_v.FROZEN%TYPE                         DEFAULT null                 -- CHAR(1)
    ,GENERATED                      PropertyDefinition_v.GENERATED%TYPE                      DEFAULT null                 -- CHAR(1)
    ,IMPORTED                       PropertyDefinition_v.IMPORTED%TYPE                       DEFAULT null                 -- CHAR(1)
    ,LOGICALNAME                    PropertyDefinition_v.LOGICALNAME%TYPE                    DEFAULT null                 -- VARCHAR2(1000)
    ,METADATASIGNATURE              PropertyDefinition_v.METADATASIGNATURE%TYPE              DEFAULT null                 -- VARCHAR2(255)
    ,NAME                           PropertyDefinition_v.NAME%TYPE                           DEFAULT null                 -- VARCHAR2(255)
    ,NOTE                           PropertyDefinition_v.NOTE%TYPE                           DEFAULT null                 -- VARCHAR2(4000)
    ,NOTM                           PropertyDefinition_v.NOTM%TYPE                           DEFAULT    0                 -- NUMBER(9,0)
    ,PERSISTENT                     PropertyDefinition_v.PERSISTENT%TYPE                     DEFAULT null                 -- CHAR(1)
    ,SEEDED                         PropertyDefinition_v.SEEDED%TYPE                         DEFAULT    1                 -- CHAR(1)
    ,UOID                           PropertyDefinition_v.UOID%TYPE                           DEFAULT null                 -- VARCHAR2(255)
    ,UPDATEDBY                      PropertyDefinition_v.UPDATEDBY%TYPE                      DEFAULT null                 -- VARCHAR2(40)
    ,UPDATETIMESTAMP                PropertyDefinition_v.UPDATETIMESTAMP%TYPE                DEFAULT null                 -- DATE
    ,VERSION                        PropertyDefinition_v.VERSION%TYPE                        DEFAULT null                 -- VARCHAR2(40)
    ,ISLOADINGPARENT                PropertyDefinition_v.ISLOADINGPARENT%TYPE                DEFAULT null                 -- CHAR(1)
    ,PROPERTYTYPE                   PropertyDefinition_v.PROPERTYTYPE%TYPE                   DEFAULT    0                 -- NUMBER(9,0)
    ,DEFINITIONKEY                  PropertyDefinition_v.DEFINITIONKEY%TYPE                  DEFAULT null                 -- VARCHAR2(255)
    ,EXPORTCONTROL                  PropertyDefinition_v.EXPORTCONTROL%TYPE                  DEFAULT    0                 -- NUMBER(9,0)
    ,OVERRIDE                       PropertyDefinition_v.OVERRIDE%TYPE                       DEFAULT null                 -- CHAR(1)
    ,POSITION                       PropertyDefinition_v.POSITION%TYPE                       DEFAULT    0                 -- NUMBER(9,0)
    ,CLASSDEFINITION                PropertyDefinition_v.CLASSDEFINITION%TYPE                DEFAULT    0                 -- NUMBER(9,0)
    ,DATATYPE                       PropertyDefinition_v.DATATYPE%TYPE                       DEFAULT    0                 -- NUMBER(9,0)
    ,ISROLE                         PropertyDefinition_v.ISROLE%TYPE                         DEFAULT null                 -- CHAR(1)
    ,ROLEDEFINITION                 PropertyDefinition_v.ROLEDEFINITION%TYPE                 DEFAULT    0                 -- NUMBER(9,0)
    ,TRANSLATABLE                   PropertyDefinition_v.TRANSLATABLE%TYPE                   DEFAULT null                 -- CHAR(1)
    ,ISPASSWORD                     PropertyDefinition_v.ISPASSWORD%TYPE                     DEFAULT null                 -- CHAR(1)
    ,HIDDEN                         PropertyDefinition_v.HIDDEN%TYPE                         DEFAULT null                 -- CHAR(1)
    ,PROPERTYREADONLY               PropertyDefinition_v.PROPERTYREADONLY%TYPE               DEFAULT null                 -- CHAR(1)
    ,NAMECHECKED                    PropertyDefinition_v.NAMECHECKED%TYPE                    DEFAULT null                 -- CHAR(1)
    ,USERCHOICEALLOWED              PropertyDefinition_v.USERCHOICEALLOWED%TYPE              DEFAULT null                 -- CHAR(1)
    ,DYNAMICDEFAULT                 PropertyDefinition_v.DYNAMICDEFAULT%TYPE                 DEFAULT null                 -- VARCHAR2(255)
    ,INPLACEEDITOR                  PropertyDefinition_v.INPLACEEDITOR%TYPE                  DEFAULT null                 -- VARCHAR2(255)
    ,CUSTOMEDITOR                   PropertyDefinition_v.CUSTOMEDITOR%TYPE                   DEFAULT null                 -- VARCHAR2(255)
    ,PROPERTYGROUPDEFINITION        PropertyDefinition_v.PROPERTYGROUPDEFINITION%TYPE        DEFAULT    0                 -- NUMBER(9,0)
  );

-- ==========================================================================
  TYPE PropertyUsageDefinition IS RECORD (
-- ==========================================================================
     LOADINGPARENT                  PropertyUsageDefinition_v.LOADINGPARENT%TYPE                  DEFAULT    0                      -- NUMBER(9,0)
    ,DELETIONPARENT                 PropertyUsageDefinition_v.DELETIONPARENT%TYPE                 DEFAULT    0                      -- NUMBER(9,0)
    ,AGGREGATEDSIGNATURE            PropertyUsageDefinition_v.AGGREGATEDSIGNATURE%TYPE            DEFAULT null                      -- VARCHAR2(255)
    ,CLASSNAME                      PropertyUsageDefinition_v.CLASSNAME%TYPE                      DEFAULT 'PropertyUsageDefinition' -- VARCHAR2(255)
    ,COMPLETED                      PropertyUsageDefinition_v.COMPLETED%TYPE                      DEFAULT null                      -- CHAR(1)
    ,CREATEDBY                      PropertyUsageDefinition_v.CREATEDBY%TYPE                      DEFAULT null                      -- VARCHAR2(40)
    ,CREATIONTIMESTAMP              PropertyUsageDefinition_v.CREATIONTIMESTAMP%TYPE              DEFAULT null                      -- DATE
    ,CUSTOMERDELETABLE              PropertyUsageDefinition_v.CUSTOMERDELETABLE%TYPE              DEFAULT null                      -- CHAR(1)
    ,CUSTOMEREDITABLE               PropertyUsageDefinition_v.CUSTOMEREDITABLE%TYPE               DEFAULT null                      -- CHAR(1)
    ,CUSTOMERRENAMABLE              PropertyUsageDefinition_v.CUSTOMERRENAMABLE%TYPE              DEFAULT null                      -- CHAR(1)
    ,DESCRIPTION                    PropertyUsageDefinition_v.DESCRIPTION%TYPE                    DEFAULT null                      -- VARCHAR2(4000)
    ,DISABLED                       PropertyUsageDefinition_v.DISABLED%TYPE                       DEFAULT null                      -- CHAR(1)
    ,EDITABLE                       PropertyUsageDefinition_v.EDITABLE%TYPE                       DEFAULT null                      -- CHAR(1)
    ,ELEMENTID                      PropertyUsageDefinition_v.ELEMENTID%TYPE                      DEFAULT    0                      -- NUMBER(9,0)
    ,FROZEN                         PropertyUsageDefinition_v.FROZEN%TYPE                         DEFAULT null                      -- CHAR(1)
    ,GENERATED                      PropertyUsageDefinition_v.GENERATED%TYPE                      DEFAULT null                      -- CHAR(1)
    ,IMPORTED                       PropertyUsageDefinition_v.IMPORTED%TYPE                       DEFAULT null                      -- CHAR(1)
    ,LOGICALNAME                    PropertyUsageDefinition_v.LOGICALNAME%TYPE                    DEFAULT null                      -- VARCHAR2(1000)
    ,METADATASIGNATURE              PropertyUsageDefinition_v.METADATASIGNATURE%TYPE              DEFAULT null                      -- VARCHAR2(255)
    ,NAME                           PropertyUsageDefinition_v.NAME%TYPE                           DEFAULT null                      -- VARCHAR2(255)
    ,NOTE                           PropertyUsageDefinition_v.NOTE%TYPE                           DEFAULT null                      -- VARCHAR2(4000)
    ,NOTM                           PropertyUsageDefinition_v.NOTM%TYPE                           DEFAULT    0                      -- NUMBER(9,0)
    ,PERSISTENT                     PropertyUsageDefinition_v.PERSISTENT%TYPE                     DEFAULT null                      -- CHAR(1)
    ,SEEDED                         PropertyUsageDefinition_v.SEEDED%TYPE                         DEFAULT    1                      -- CHAR(1)
    ,UOID                           PropertyUsageDefinition_v.UOID%TYPE                           DEFAULT null                      -- VARCHAR2(255)
    ,UPDATEDBY                      PropertyUsageDefinition_v.UPDATEDBY%TYPE                      DEFAULT null                      -- VARCHAR2(40)
    ,UPDATETIMESTAMP                PropertyUsageDefinition_v.UPDATETIMESTAMP%TYPE                DEFAULT null                      -- DATE
    ,VERSION                        PropertyUsageDefinition_v.VERSION%TYPE                        DEFAULT null                      -- VARCHAR2(40)
    ,ISLOADINGPARENT                PropertyUsageDefinition_v.ISLOADINGPARENT%TYPE                DEFAULT null                      -- CHAR(1)
    ,PROPERTYSETDEFINITION          PropertyUsageDefinition_v.PROPERTYSETDEFINITION%TYPE          DEFAULT    0                      -- NUMBER(9,0)
    ,PROPERTYDEFINITION             PropertyUsageDefinition_v.PROPERTYDEFINITION%TYPE             DEFAULT    0                      -- NUMBER(9,0)
    ,DEFINITIONKEY                  PropertyUsageDefinition_v.DEFINITIONKEY%TYPE                  DEFAULT null                      -- VARCHAR2(255)
  );

-- ==========================================================================
  TYPE WBPrimitiveProperty IS RECORD (
-- ==========================================================================
     LOADINGPARENT                  WBPrimitiveProperty_v.LOADINGPARENT%TYPE                  DEFAULT    0                  -- NUMBER(9,0)
    ,DELETIONPARENT                 WBPrimitiveProperty_v.DELETIONPARENT%TYPE                 DEFAULT    0                  -- NUMBER(9,0)
    ,AGGREGATEDSIGNATURE            WBPrimitiveProperty_v.AGGREGATEDSIGNATURE%TYPE            DEFAULT null                  -- VARCHAR2(255)
    ,CLASSNAME                      WBPrimitiveProperty_v.CLASSNAME%TYPE                      DEFAULT 'WBPrimitiveProperty' -- VARCHAR2(255)
    ,COMPLETED                      WBPrimitiveProperty_v.COMPLETED%TYPE                      DEFAULT null                  -- CHAR(1)
    ,CREATEDBY                      WBPrimitiveProperty_v.CREATEDBY%TYPE                      DEFAULT null                  -- VARCHAR2(40)
    ,CREATIONTIMESTAMP              WBPrimitiveProperty_v.CREATIONTIMESTAMP%TYPE              DEFAULT null                  -- DATE
    ,CUSTOMERDELETABLE              WBPrimitiveProperty_v.CUSTOMERDELETABLE%TYPE              DEFAULT null                  -- CHAR(1)
    ,CUSTOMEREDITABLE               WBPrimitiveProperty_v.CUSTOMEREDITABLE%TYPE               DEFAULT null                  -- CHAR(1)
    ,CUSTOMERRENAMABLE              WBPrimitiveProperty_v.CUSTOMERRENAMABLE%TYPE              DEFAULT null                  -- CHAR(1)
    ,DESCRIPTION                    WBPrimitiveProperty_v.DESCRIPTION%TYPE                    DEFAULT null                  -- VARCHAR2(4000)
    ,DISABLED                       WBPrimitiveProperty_v.DISABLED%TYPE                       DEFAULT null                  -- CHAR(1)
    ,EDITABLE                       WBPrimitiveProperty_v.EDITABLE%TYPE                       DEFAULT null                  -- CHAR(1)
    ,ELEMENTID                      WBPrimitiveProperty_v.ELEMENTID%TYPE                      DEFAULT    0                  -- NUMBER(9,0)
    ,FROZEN                         WBPrimitiveProperty_v.FROZEN%TYPE                         DEFAULT null                  -- CHAR(1)
    ,GENERATED                      WBPrimitiveProperty_v.GENERATED%TYPE                      DEFAULT null                  -- CHAR(1)
    ,IMPORTED                       WBPrimitiveProperty_v.IMPORTED%TYPE                       DEFAULT null                  -- CHAR(1)
    ,LOGICALNAME                    WBPrimitiveProperty_v.LOGICALNAME%TYPE                    DEFAULT null                  -- VARCHAR2(1000)
    ,METADATASIGNATURE              WBPrimitiveProperty_v.METADATASIGNATURE%TYPE              DEFAULT null                  -- VARCHAR2(255)
    ,NAME                           WBPrimitiveProperty_v.NAME%TYPE                           DEFAULT null                  -- VARCHAR2(255)
    ,NOTE                           WBPrimitiveProperty_v.NOTE%TYPE                           DEFAULT null                  -- VARCHAR2(4000)
    ,NOTM                           WBPrimitiveProperty_v.NOTM%TYPE                           DEFAULT    0                  -- NUMBER(9,0)
    ,PERSISTENT                     WBPrimitiveProperty_v.PERSISTENT%TYPE                     DEFAULT null                  -- CHAR(1)
    ,SEEDED                         WBPrimitiveProperty_v.SEEDED%TYPE                         DEFAULT    1                  -- CHAR(1)
    ,UOID                           WBPrimitiveProperty_v.UOID%TYPE                           DEFAULT null                  -- VARCHAR2(255)
    ,UPDATEDBY                      WBPrimitiveProperty_v.UPDATEDBY%TYPE                      DEFAULT null                  -- VARCHAR2(40)
    ,UPDATETIMESTAMP                WBPrimitiveProperty_v.UPDATETIMESTAMP%TYPE                DEFAULT null                  -- DATE
    ,VERSION                        WBPrimitiveProperty_v.VERSION%TYPE                        DEFAULT null                  -- VARCHAR2(40)
    ,ISLOADINGPARENT                WBPrimitiveProperty_v.ISLOADINGPARENT%TYPE                DEFAULT null                  -- CHAR(1)
    ,OWNER                          WBPrimitiveProperty_v.OWNER%TYPE                          DEFAULT    0                  -- NUMBER(9,0)
    ,DATATYPE                       WBPrimitiveProperty_v.DATATYPE%TYPE                       DEFAULT    0                  -- NUMBER(9,0)
    ,POSITION                       WBPrimitiveProperty_v.POSITION%TYPE                       DEFAULT    0                  -- NUMBER(9,0)
    ,HASNAME                        WBPrimitiveProperty_v.HASNAME%TYPE                        DEFAULT null                  -- CHAR(1)
    ,HASPOSITION                    WBPrimitiveProperty_v.HASPOSITION%TYPE                    DEFAULT null                  -- CHAR(1)
    ,VALUE                          WBPrimitiveProperty_v.VALUE%TYPE                          DEFAULT null                  -- VARCHAR2(4000)
  );

-- ==========================================================================
  TYPE WBDomain IS RECORD (
-- ==========================================================================
     LOADINGPARENT                  WBDomain_v.LOADINGPARENT%TYPE                  DEFAULT    0       -- NUMBER(9,0)
    ,DELETIONPARENT                 WBDomain_v.DELETIONPARENT%TYPE                 DEFAULT    0       -- NUMBER(9,0)
    ,AGGREGATEDSIGNATURE            WBDomain_v.AGGREGATEDSIGNATURE%TYPE            DEFAULT null       -- VARCHAR2(255)
    ,CLASSNAME                      WBDomain_v.CLASSNAME%TYPE                      DEFAULT 'WBDomain' -- VARCHAR2(255)
    ,COMPLETED                      WBDomain_v.COMPLETED%TYPE                      DEFAULT null       -- CHAR(1)
    ,CREATEDBY                      WBDomain_v.CREATEDBY%TYPE                      DEFAULT null       -- VARCHAR2(40)
    ,CREATIONTIMESTAMP              WBDomain_v.CREATIONTIMESTAMP%TYPE              DEFAULT null       -- DATE
    ,CUSTOMERDELETABLE              WBDomain_v.CUSTOMERDELETABLE%TYPE              DEFAULT null       -- CHAR(1)
    ,CUSTOMEREDITABLE               WBDomain_v.CUSTOMEREDITABLE%TYPE               DEFAULT null       -- CHAR(1)
    ,CUSTOMERRENAMABLE              WBDomain_v.CUSTOMERRENAMABLE%TYPE              DEFAULT null       -- CHAR(1)
    ,DESCRIPTION                    WBDomain_v.DESCRIPTION%TYPE                    DEFAULT null       -- VARCHAR2(4000)
    ,DISABLED                       WBDomain_v.DISABLED%TYPE                       DEFAULT null       -- CHAR(1)
    ,EDITABLE                       WBDomain_v.EDITABLE%TYPE                       DEFAULT null       -- CHAR(1)
    ,ELEMENTID                      WBDomain_v.ELEMENTID%TYPE                      DEFAULT    0       -- NUMBER(9,0)
    ,FROZEN                         WBDomain_v.FROZEN%TYPE                         DEFAULT null       -- CHAR(1)
    ,GENERATED                      WBDomain_v.GENERATED%TYPE                      DEFAULT null       -- CHAR(1)
    ,IMPORTED                       WBDomain_v.IMPORTED%TYPE                       DEFAULT null       -- CHAR(1)
    ,LOGICALNAME                    WBDomain_v.LOGICALNAME%TYPE                    DEFAULT null       -- VARCHAR2(1000)
    ,METADATASIGNATURE              WBDomain_v.METADATASIGNATURE%TYPE              DEFAULT null       -- VARCHAR2(255)
    ,NAME                           WBDomain_v.NAME%TYPE                           DEFAULT null       -- VARCHAR2(255)
    ,NOTE                           WBDomain_v.NOTE%TYPE                           DEFAULT null       -- VARCHAR2(4000)
    ,NOTM                           WBDomain_v.NOTM%TYPE                           DEFAULT    0       -- NUMBER(9,0)
    ,PERSISTENT                     WBDomain_v.PERSISTENT%TYPE                     DEFAULT null       -- CHAR(1)
    ,SEEDED                         WBDomain_v.SEEDED%TYPE                         DEFAULT    1       -- CHAR(1)
    ,UOID                           WBDomain_v.UOID%TYPE                           DEFAULT null       -- VARCHAR2(255)
    ,UPDATEDBY                      WBDomain_v.UPDATEDBY%TYPE                      DEFAULT null       -- VARCHAR2(40)
    ,UPDATETIMESTAMP                WBDomain_v.UPDATETIMESTAMP%TYPE                DEFAULT null       -- DATE
    ,VERSION                        WBDomain_v.VERSION%TYPE                        DEFAULT null       -- VARCHAR2(40)
    ,ISLOADINGPARENT                WBDomain_v.ISLOADINGPARENT%TYPE                DEFAULT null       -- CHAR(1)
    ,COMPARATOR                     WBDomain_v.COMPARATOR%TYPE                     DEFAULT 'PropertyNameComparator'       -- VARCHAR2(255)
    ,DATATYPE                       WBDomain_v.DATATYPE%TYPE                       DEFAULT    0       -- NUMBER(9,0)
  );

-- ==========================================================================
-- put message to output (uses DBMS_OUTPUT)
  PROCEDURE put(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF NOT OUTINITED THEN
      OUTINITED := TRUE;
      DBMS_OUTPUT.ENABLE(200000);
      put('DBMS_OUTPUT Enabled.');
    END IF;

    DBMS_OUTPUT.put_line(SCRIPTID || ': ' || msg);

  END;

-- ==========================================================================
-- put message to output controlled by TRACEON variable (uses put)
  PROCEDURE putTrace(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF TRACEON THEN
      put(msg);
    END IF;
  END;

-- ==========================================================================
-- similar to Object.toString() in Java - return a string representation of the record
  FUNCTION toString(
    dt CMPDATATYPE_V%ROWTYPE
  )
  RETURN VARCHAR2
-- ==========================================================================
  IS
    l_str VARCHAR2(255) := '(CMPDatatype)' || dt.NAME || '/elementid=' ||
                          dt.ELEMENTID || '/uoid=' || dt.UOID;
  BEGIN
    RETURN l_str;
  END;


-- ==========================================================================
-- return next element id for new objects
  FUNCTION getNextElementId RETURN NUMBER
-- ==========================================================================
  IS
    l_id NUMBER;
  BEGIN
      SELECT cwmseq.nextval
        INTO l_id
        FROM DUAL;
      RETURN l_id;
  END;

-- ==========================================================================
-- return the id (elementid) for the (fully qualified) class name passed in
  FUNCTION classIdOf(
     p_className IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_className VARCHAR2(255) := p_className;
    l_classId NUMBER;
  BEGIN

      putTrace('classIdOf(' || l_className || ')');
      SELECT ELEMENTID
        INTO l_classId
        FROM ClassDefinition_v
        WHERE NAME=l_className;
      putTrace('  RETURN ' || l_classId || EOL);

    RETURN l_classId;
  END classIdOf;

-- ==========================================================================
-- return the id (elementid) for the property set defined by the input parameters
  FUNCTION psetIdOf(
      p_classId  IN NUMBER
     ,p_psetType IN NUMBER
     ,p_psetName IN VARCHAR2 DEFAULT 'DEFAULT'
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_classId  NUMBER := p_classId;
    l_psetType NUMBER := p_psetType;
    l_psetName VARCHAR2(255) := p_psetName;
    l_psetId   NUMBER;
  BEGIN

      putTrace('psetIdOf(' || l_classId || ', ' || l_psetType || ', ' || l_psetName || ')');
      SELECT ELEMENTID
        INTO l_psetId
        FROM PropertySetDefinition_v
        WHERE CLASSDEFINITION=l_classId
          AND NAME=l_psetName
          AND PROPERTYTYPE=l_psetType;
      putTrace('  RETURN ' || l_psetId || EOL);

    RETURN l_psetId;
  END psetIdOf;

-- ==========================================================================
-- return the id (elementid) for the property group defined by the input parameters
  FUNCTION groupIdOf(
      p_ownerId   IN NUMBER
     ,p_groupName IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_ownerId  NUMBER := p_ownerId;
    l_groupName VARCHAR2(255) := p_groupName;
    l_groupId   NUMBER;
  BEGIN

      putTrace('groupIdOf(' || l_ownerId || ', ' || l_groupName || ')');
      SELECT ELEMENTID
        INTO l_groupId
        FROM PropertyGroupDefinition_v
        WHERE GROUPOWNER=l_ownerId
          AND NAME=l_groupName;
      putTrace('  RETURN ' || l_groupId || EOL);

    RETURN l_groupId;
  END groupIdOf;

-- ==========================================================================
-- return the id (elementid) for the CMPWBValidDataTypeList defined by the given uoid
  FUNCTION validDataTypeListIdOf(
     p_uoid IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_validDataTypeListId   NUMBER;
  BEGIN
      --putTrace('validDataTypeListIdOf(' || p_uoid || ')');
      SELECT ELEMENTID
        INTO l_validDataTypeListId
        FROM CMPWBValidDataTypeList_v
        WHERE UOID=p_uoid;
      --putTrace('  RETURN ' || l_validDataTypeListId || EOL);

    RETURN l_validDataTypeListId;
  END validDataTypeListIdOf;

-- ==========================================================================
-- return the id (elementid) for the CMPLanguage_v rec defined by the given uoid
  FUNCTION languageIdOf(
     p_uoid IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_Id   NUMBER;
  BEGIN
      --putTrace('LanguageIdOf(' || p_uoid || ')');
      SELECT ELEMENTID
        INTO l_Id
        FROM CMPLanguage_v
        WHERE UOID=p_uoid;
      --putTrace('  RETURN ' || l_Id || EOL);

    RETURN l_Id;
  END languageIdOf;


-- **************************************************************************
-- **************************************************************************
--                      Record handling functions/procedures
-- new<Recordtype> - create initialized record instance
-- toString        - debug 'pretty print' similar to java
-- getInitFor      - print pl/sql initialization for columns thqat need to be
--                  carried over from a live installation (METADATASIGNATURE,
--                  UOID, ...)
-- add<RecordType> - find or create row represented by this record in the OWB
--                  repository. Calls getInitFor if row already exists.
-- **************************************************************************
-- **************************************************************************


-- ==========================================================================
--                         PropertyDefinition
-- ==========================================================================

-- ==========================================================================
-- return PropertyDefinition record with given info and audit block initialized
  FUNCTION newPropertyDefinition(
     propertyName IN VARCHAR2 DEFAULT null
    ,classId      IN NUMBER   DEFAULT 0
  ) RETURN PropertyDefinition
-- ==========================================================================
  IS
    pdef PropertyDefinition;
  BEGIN
    pdef.NAME              := propertyName;
    pdef.LOGICALNAME       := propertyName;
    pdef.CLASSDEFINITION   := classId;
    pdef.LOADINGPARENT     := classId;

    pdef.CREATEDBY         := g_auditInfo.CREATEDBY;
    pdef.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP;
    pdef.UPDATEDBY         := g_auditInfo.UPDATEDBY;
    pdef.UPDATETIMESTAMP   := g_auditInfo.UPDATETIMESTAMP;

    RETURN pdef;
  END newPropertyDefinition;

-- ==========================================================================
-- similar to Object.toString() in Java - return a string representation of the record
  FUNCTION toString(
    p_pdef PropertyDefinition
  )
  RETURN VARCHAR2
-- ==========================================================================
  IS
    l_str VARCHAR2(255) := '(PropertyDefinition)' || p_pdef.NAME || '/id=' ||
                          p_pdef.ELEMENTID;
  BEGIN
    RETURN l_str;
  END;

-- ==========================================================================
-- return pl/sql required to initialize additional PropertyDefinition fields for
-- insert (Used when running against new repos to get attributes created by the
-- OWB framework during during the install.)
  FUNCTION getInitFor(
     pdef IN PropertyDefinition
  ) RETURN VARCHAR2
-- ==========================================================================
  IS
    initsql VARCHAR2(2000);
  BEGIN

    initsql := '' || EOL ||
      '     -- getInitFor(' || toString(pdef) || ') =' || EOL ||
      '        pdef.METADATASIGNATURE := ''' || pdef.METADATASIGNATURE || ''';' || EOL ||
      '        pdef.UOID              := ''' || pdef.UOID              || ''';' || EOL ||
      '        pdef.POSITION          := '   || pdef.POSITION          || ';'   || EOL ;
--let caller decide    putTrace('  RETURN ' || initsql);

    RETURN initsql;
  END getInitFor;

-- ==========================================================================
-- add record to repository (or say existing one was found)
  PROCEDURE addPropertyDefinition(
      pdef IN OUT PropertyDefinition
  )
-- ==========================================================================
  IS
  BEGIN
    SELECT *
      INTO pdef
      FROM PropertyDefinition_v
     WHERE CLASSDEFINITION=pdef.CLASSDEFINITION
       AND NAME=pdef.NAME;
    put('Found (' || toString(pdef) || ')' || getInitFor(pdef));

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
      -- new def so need new element id
      pdef.ELEMENTID := getNextElementId();

      put('Creating (' || toString(pdef) || ')');
      INSERT
        INTO PropertyDefinition_v
      VALUES pdef;

      put('Requery after create...');
      SELECT *
        INTO pdef
        FROM PropertyDefinition_v
       WHERE CLASSDEFINITION=pdef.CLASSDEFINITION
         AND NAME=pdef.NAME;
      put('Created (' || toString(pdef) || ')');
    END; -- NO_DATA_FOUND
  END addPropertyDefinition;

-- ==========================================================================
--                     PropertyUsageDefinition
-- ==========================================================================

-- ==========================================================================
-- return PropertyUsageDefinition record with given info and audit block initialized
  FUNCTION newPropertyUsageDefinition(
     psetId   IN NUMBER
    ,pdef     IN PropertyDefinition
  ) RETURN PropertyUsageDefinition
-- ==========================================================================
  IS
    puse PropertyUsageDefinition;
  BEGIN
    puse.LOADINGPARENT         := pdef.LOADINGPARENT;
    puse.NAME                  := pdef.NAME;
    puse.LOGICALNAME           := pdef.LOGICALNAME;
    puse.PROPERTYSETDEFINITION := psetId;
    puse.PROPERTYDEFINITION    := pdef.ELEMENTID;
    puse.DESCRIPTION           := pdef.DESCRIPTION;

    puse.CREATEDBY         := g_auditInfo.CREATEDBY;
    puse.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP;
    puse.UPDATEDBY         := g_auditInfo.UPDATEDBY;
    puse.UPDATETIMESTAMP   := g_auditInfo.UPDATETIMESTAMP;

    RETURN puse;
  END newPropertyUsageDefinition;

-- ==========================================================================
-- similar to Object.toString() in Java - return a string representation of the record
  FUNCTION toString(
    p_puse PropertyUsageDefinition
  )
  RETURN VARCHAR2
-- ==========================================================================
  IS
    l_str VARCHAR2(255) := '(PropertyUsageDefinition)' || p_puse.NAME || '/id=' ||
                          p_puse.ELEMENTID || '/pdef=' || p_puse.PROPERTYDEFINITION;
  BEGIN
    RETURN l_str;
  END;

-- ==========================================================================
-- return pl/sql required to initialize additional PropertyDefinition fields for
-- insert (Used when running against new repos to get attributes created by the
-- OWB framework during during the install.)
  FUNCTION getInitFor(
     puse IN PropertyUsageDefinition
  ) RETURN VARCHAR2
-- ==========================================================================
  IS
    initsql VARCHAR2(2000);
  BEGIN

    initsql := '' || EOL ||
      '     -- getInitFor(' || toString(puse) || ') =' || EOL ||
      '        puse.METADATASIGNATURE := ''' || puse.METADATASIGNATURE || ''';' || EOL ||
      '        puse.UOID              := ''' || puse.UOID              || ''';' || EOL ;
--let caller decide    putTrace('  RETURN ' || initsql);

    RETURN initsql;
  END getInitFor;

-- ==========================================================================
-- return pl/sql required to initialize additional CMPDataType fields for
-- insert (Used when running against new repos to get attributes created by the
-- OWB framework during during the install.)
  FUNCTION getInitFor(
     dt CMPDATATYPE_V%ROWTYPE
  ) RETURN VARCHAR2
-- ==========================================================================
  IS
    initsql VARCHAR2(2000);
    l_validDataTypeListUOID VARCHAR2(32);
    l_owningLanguageUOID VARCHAR2(32);
    l_name VARCHAR2(50);
  BEGIN
    -- the uoid or name from cmpwbvalidDataTypeList_v is needed to find the elementid
    -- to assign to the membershipList
    BEGIN
    SELECT uoid, name into l_validDataTypeListUOID, l_name FROM CMPWBValidDataTypeList_v
      WHERE elementid = dt.membershiplist;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_validDataTypeListUOID := null;
      put('Error: the membershiplist of ' || dt.membershiplist || ' was not found in CMPWBValidDataTypeList_v');
    END;

    BEGIN
    IF dt.owningLanguage is not null THEN
      SELECT uoid into l_owningLanguageUOID FROM CMPLanguage_v
        WHERE elementid = dt.owningLanguage;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_owningLanguageUOID := 'null';
      put('Error: the owningLanguage of ' || dt.owningLanguage || ' was not found in CMPLanguage_v');
    END;

    initsql := '' || EOL ||
      '     -- getInitFor(' || toString(dt) || ') =' || EOL ||
      '        addcmpdatatype( cmpdatatype_rec, '|| dt.UOID || ', '|| dt.METADATASIGNATURE ||
      ',  ' || l_owningLanguageUOID || ', ' || l_validDataTypeListUOID || ' );' || EOL ;
    RETURN initsql;
  END getInitFor;


-- ==========================================================================
-- add record to repository (or say existing one was found)
  PROCEDURE addPropertyUsageDefinition(
      puse IN OUT PropertyUsageDefinition
  )
-- ==========================================================================
  IS
  BEGIN
    -- This one will trigger NO_DATA_FOUND if the property does not yet exist
    SELECT *
      INTO puse
      FROM PropertyUsageDefinition_v
     WHERE PROPERTYSETDEFINITION=puse.PROPERTYSETDEFINITION
       AND PROPERTYDEFINITION=puse.PROPERTYDEFINITION;
    put('Found (' || toString(puse) || ')' || getInitFor(puse));

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
      -- new def so need new element id
      puse.ELEMENTID := getNextElementId();

      put('Creating (' || toString(puse) || ')');
      INSERT
        INTO PropertyUsageDefinition_v
      VALUES puse;

      put('Requery after create...');
    SELECT *
      INTO puse
      FROM PropertyUsageDefinition_v
     WHERE PROPERTYSETDEFINITION=puse.PROPERTYSETDEFINITION
       AND PROPERTYDEFINITION=puse.PROPERTYDEFINITION;
      put('Created (' || toString(puse) || ')');
    END; -- NO_DATA_FOUND
  END addPropertyUsageDefinition;

-- ==========================================================================
--                        WBPrimitiveProperty
-- ==========================================================================

-- ==========================================================================
-- return WBPrimitiveProperty record with given info and audit block initialized
  FUNCTION newWBPrimitiveProperty(
     classId       IN NUMBER
    ,ownerId       IN NUMBER
    ,datatype      IN NUMBER
    ,propertyName  IN VARCHAR2 DEFAULT NULL
    ,propertyValue IN VARCHAR2 DEFAULT NULL
  ) RETURN WBPrimitiveProperty
-- ==========================================================================
  IS
    prim WBPrimitiveProperty;
  BEGIN
    prim.NAME          := propertyName;
    prim.LOGICALNAME   := propertyName;
    prim.OWNER         := ownerId;
    prim.LOADINGPARENT := classId;
    prim.DATATYPE      := datatype;
    prim.VALUE         := propertyValue;

    prim.CREATEDBY         := g_auditInfo.CREATEDBY;
    prim.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP;
    prim.UPDATEDBY         := g_auditInfo.UPDATEDBY;
    prim.UPDATETIMESTAMP   := g_auditInfo.UPDATETIMESTAMP;

    RETURN prim;
  END newWBPrimitiveProperty;

-- similar to Object.toString() in Java - return a string representation of the record
  FUNCTION toString(
    p_meta WBPrimitiveProperty
  )
  RETURN VARCHAR2
  IS
    l_str VARCHAR2(255) := '(WBPrimitiveProperty)' || NVL(p_meta.NAME,'NULL') ||
                           '/id=' || p_meta.ELEMENTID || '/owner=' ||
                           p_meta.OWNER || '/value=''' || p_meta.VALUE || '''';
  BEGIN
    RETURN l_str;
  END;

-- ==========================================================================
-- return pl/sql required to initialize additional WBPrimitiveProperty fields for
-- insert (Used when running against new repos to get attributes created by the
-- OWB framework during during the install.)
  FUNCTION getInitFor(
     prim IN WBPrimitiveProperty
  ) RETURN VARCHAR2
-- ==========================================================================
  IS
    initsql VARCHAR2(2000);
  BEGIN

    initsql := '' || EOL ||
      '     -- getInitFor(' || toString(prim) || ') =' || EOL ||
      '        prim.METADATASIGNATURE := ''' || prim.METADATASIGNATURE || ''';' || EOL ||
      '        prim.UOID              := ''' || prim.UOID              || ''';' || EOL ;
--      '        prim.HASNAME           := ''' || prim.HASNAME           || ''';' || EOL ||
--      '        prim.HASPOSITION       := ''' || prim.HASPOSITION       || ''';' || EOL ||
--      '        prim.POSITION          := '   || prim.POSITION          || ';'   || EOL ;
--let caller decide    putTrace('  RETURN ' || initsql);

    RETURN initsql;
  END getInitFor;

-- ==========================================================================
-- add record to repository (or say existing one was found)
  PROCEDURE addWBPrimitiveProperty(
      prim IN OUT WBPrimitiveProperty
  )
-- ==========================================================================
  IS
  BEGIN
    IF prim.NAME IS NULL THEN
      SELECT *
        INTO prim
        FROM WBPrimitiveProperty_v
       WHERE OWNER=prim.OWNER
         AND VALUE=prim.VALUE
         AND NAME IS NULL;
      -- indent values ...
      put('...Found (' || toString(prim) || ')');
    ELSE
      SELECT *
        INTO prim
        FROM WBPrimitiveProperty_v
       WHERE OWNER=prim.OWNER
         AND NAME=prim.NAME;
      put('Found (' || toString(prim) || ')' || getInitFor(prim));
    END IF;


  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
      -- new def so need new element id
      prim.ELEMENTID := getNextElementId();

      put('Creating (' || toString(prim) || ')');
      INSERT
        INTO WBPrimitiveProperty_v
      VALUES prim;

      put('Requery after create...');
      IF prim.NAME IS NULL THEN
        SELECT *
          INTO prim
          FROM WBPrimitiveProperty_v
         WHERE OWNER=prim.OWNER
           AND VALUE=prim.VALUE
           AND NAME IS NULL;
        -- indent values ...
        put('...Created (' || toString(prim) || ')');
      ELSE
        SELECT *
          INTO prim
          FROM WBPrimitiveProperty_v
         WHERE OWNER=prim.OWNER
           AND NAME=prim.NAME;
        put('Created (' || toString(prim) || ')');
      END IF;
    END; -- NO_DATA_FOUND
  END addWBPrimitiveProperty;

-- ==========================================================================
--                        WBDomain
-- ==========================================================================

-- ==========================================================================
-- return WBDomain record with given info and audit block initialized
  FUNCTION newWBDomain(
     name     IN VARCHAR2
    ,datatype IN NUMBER
  ) RETURN WBDomain
-- ==========================================================================
  IS
    domn WBDomain;
  BEGIN
    domn.NAME          := name;
    domn.LOGICALNAME   := name;
    domn.DATATYPE      := datatype;

    domn.CREATEDBY         := g_auditInfo.CREATEDBY;
    domn.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP;
    domn.UPDATEDBY         := g_auditInfo.UPDATEDBY;
    domn.UPDATETIMESTAMP   := g_auditInfo.UPDATETIMESTAMP;

    RETURN domn;
  END newWBDomain;

-- similar to Object.toString() in Java - return a string representation of the record
  FUNCTION toString(
    domn WBDomain
  )
  RETURN VARCHAR2
  IS
    l_str VARCHAR2(255) := '(WBDomain)' || domn.NAME || '/id=' ||
                          domn.ELEMENTID;
  BEGIN
    RETURN l_str;
  END;

-- ==========================================================================
-- return pl/sql required to initialize additional WBDomain fields for
-- insert (Used when running against new repos to get attributes created by the
-- OWB framework during during the install.)
  FUNCTION getInitFor(
     domn IN WBDomain
  ) RETURN VARCHAR2
-- ==========================================================================
  IS
    initsql VARCHAR2(2000);
  BEGIN

    initsql := '' || EOL ||
      '     -- getInitFor(' || toString(domn) || ') =' || EOL ||
      '        domn.METADATASIGNATURE := ''' || domn.METADATASIGNATURE || ''';' || EOL ||
      '        domn.UOID              := ''' || domn.UOID              || ''';' || EOL ;
--      '        domn.HASNAME           := ''' || domn.HASNAME           || ''';' || EOL ||
--      '        domn.HASPOSITION       := ''' || domn.HASPOSITION       || ''';' || EOL ||
--      '        domn.POSITION          := '   || domn.POSITION          || ';'   || EOL ;
--let caller decide    putTrace('  RETURN ' || initsql);

    RETURN initsql;
  END getInitFor;

-- ==========================================================================
-- add record to repository (or say existing one was found)
  PROCEDURE addWBDomain(
     domn IN OUT WBDomain
  )
-- ==========================================================================
  IS
    prim WBPrimitiveProperty;
  BEGIN
    BEGIN -- need to wrap exception catching block
      SELECT *
        INTO domn
        FROM WBDomain_v
       WHERE NAME=domn.NAME;
      put('Found (' || toString(domn) || ')' || getInitFor(domn));

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
        -- new def so need new element id
        domn.ELEMENTID := getNextElementId();

        put('Creating (' || toString(domn) || ')');
        INSERT
          INTO WBDomain_v
        VALUES domn;

        put('Requery after create...');
      SELECT *
        INTO domn
        FROM WBDomain_v
       WHERE NAME=domn.NAME;
        put('Created (' || toString(domn) || ')');
      END; -- NO_DATA_FOUND
    END;
  END addWBDomain;

-- ==========================================================================
-- initialize the cmpdatatype_v record with default values for ROWID or UROWID
  PROCEDURE initRowidDatatypeRec (
      dt IN OUT CMPDATATYPE_V%ROWTYPE
  )
-- ==========================================================================
  IS
  BEGIN
    --DELETIONPARENT not set
    --AGGREGATEDSIGNATURE not set
    dt.CLASSNAME := 'CMPDatatype';
    dt.CREATEDBY := g_auditInfo.CREATEDBY ;
    dt.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP ;
    dt.CUSTOMERDELETABLE := 0;
    dt.CUSTOMEREDITABLE := 0;
    dt.CUSTOMERRENAMABLE := 0;
    dt.DESCRIPTION := null;
    dt.DISABLED := 0;
    dt.EDITABLE := 0;
    dt.FROZEN := 0;
    dt.GENERATED := 0;
    dt.IMPORTED := 0;
    dt.NOTE := null;
    dt.NOTM := 1;
    dt.PERSISTENT := 1;
    dt.SEEDED := 1;
    --STRONGTYPENAME not set
    --UOID not set
    dt.UPDATEDBY := g_auditInfo.UPDATEDBY ;
    dt.UPDATETIMESTAMP := g_auditInfo.UPDATETIMESTAMP ;
    --VERSION not set
    --ICONOBJECT not set
    --SOFTWAREMODVERSION not set
    --TYPENAME not set
    --DATATYPE reset in addcmpdatatype(
    dt.DAYPRECISIONALLOWED := 0;
    dt.DEFAULTDAYPRECISION := 0;
    dt.DEFAULTFRACTIONALSECPRECISION := 0;
    dt.DEFAULTLENGTH := 0;
    dt.DEFAULTPRECISION := 0;
    dt.DEFAULTSCALE := 0;
    dt.DEFAULTYEARPRECISION := 0;
    dt.FRACTIONALSECPRECISIONALLOWED := 0;
    dt.ICON := null;
    dt.LENGTHALLOWED := 0;
    dt.MAXIMUMDAYPRECISION := 0;
    dt.MAXIMUMFRACTIONALSECPRECISION := 0;
    dt.MAXIMUMLENGTH := 0;
    dt.MAXIMUMPRECISION := 0;
    dt.MAXIMUMSCALE := 0;
    dt.MAXIMUMYEARPRECISION := 0;
    dt.MINIMUMDAYPRECISION := 0;
    dt.MINIMUMFRACTIONALSECPRECISION := 0;
    dt.MINIMUMLENGTH := 0;
    dt.MINIMUMPRECISION := 0;
    dt.MINIMUMSCALE := 0;
    dt.MINIMUMYEARPRECISION := 0;
    dt.PRECISIONALLOWED := 0;
    dt.PREDEFINED := 0;
    dt.SCALEALLOWED := 0;
    dt.YEARPRECISIONALLOWED := 0;
    --OWNINGLANGUAGE reset in addcmpdatatype
    --MEMBERSHIPLIST reset in addcmpdatatype
  END;

-- ==========================================================================
-- add record to repository if a record having the specified uoid does not exist
  PROCEDURE addcmpdatatype(
      dt IN OUT CMPDATATYPE_V%ROWTYPE, p_uoid VARCHAR2, metasig VARCHAR2, name VARCHAR2,
      owningLanguage VARCHAR2, membershiplist VARCHAR2
  )
-- ==========================================================================
  IS
  BEGIN
    dt.uoid := p_uoid;
    SELECT * INTO dt FROM CMPDatatype_v WHERE uoid = p_uoid;
    put('Found (' || toString(dt) || ')' || getInitFor(dt));

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
      dt.ELEMENTID := getNextElementId();
      dt.METADATASIGNATURE := metasig;
      dt.LOGICALNAME := name;
      dt.NAME := name;
      dt.DATATYPE := name;
      IF owningLanguage IS NULL THEN
        dt.OWNINGLANGUAGE := null;
      ELSE
        dt.OWNINGLANGUAGE := languageIdOf(owningLanguage);
      END IF;
      dt.MEMBERSHIPLIST := validDataTypeListIdOf(membershiplist);
      put('Creating (' || toString(dt) || ')');
      INSERT
        INTO cmpdatatype_v
      VALUES dt;

    END; -- NO_DATA_FOUND
  END addcmpdatatype;

BEGIN
    putTrace('-- propertytypeIdOf');
    --------------------------------
    propertytypeIdOf('core')          := 0;
    propertytypeIdOf('configuration') := 2;
    propertytypeIdOf('logical')       := 1;
    propertytypeIdOf('physical')      := 2;
    propertytypeIdOf('userdefined')   := 3;

    putTrace('-- datatypeIdOf');
    ----------------------------
    DECLARE
      datatypenames NameList := NameList('String','Integer','Long','Float','Double','Timestamp','Date','Boolean');
      -- Note: String[] was stringArray in 9.2.0.4
    BEGIN
      FOR i IN datatypenames.FIRST .. datatypenames.LAST
      LOOP
        SELECT ELEMENTID INTO datatypeIdOf(datatypenames(i)) FROM WBPrimitiveType_v
        WHERE NAME=datatypenames(i);
      END LOOP;
      -- add special cases
      datatypeIdOf('int') := datatypeIdOf('Integer');
      SELECT ELEMENTID INTO datatypeIdOf('String[]') FROM WBArrayType_v
      WHERE NAME='String[]';
    END;

    putTrace('-- g_auditInfo');
    ---------------------------
    DECLARE
      CURSOR c1 IS
        SELECT CREATEDBY, CREATIONTIMESTAMP, UPDATEDBY, UPDATETIMESTAMP
        FROM CMPInstallation_v;
   -- info and g_auditInfo have different types. But because info is based on a
   -- %ROWTYPE, we can assign it to g_auditInfo as long as they have the right
   -- number of fields and the fields have the right datatypes.
      info c1%ROWTYPE;
    BEGIN
      -- audit info (just set update timestamp once for all updates.)
      SELECT CREATEDBY, CREATIONTIMESTAMP, UPDATEDBY, UPDATETIMESTAMP
      INTO info
      FROM CMPInstallation_v;
      g_auditInfo := info;
      -- Replace installations update timestamp with current sysdate
      SELECT SYSDATE INTO g_auditInfo.UPDATETIMESTAMP FROM DUAL;
    END;
    put('Initialize constants and lookup tables END.' || EOL);

      --***************************************************
      -- ROWID, UROWID datatypes
      --***************************************************
    DECLARE
       cmpdatatype_rec CMPDATATYPE_V%ROWTYPE;
    BEGIN
        -- add values to CMPDataType_v.  ROWID and UROWID recs are added for each membership value: OWB_INTERNAL, 73, 80, 8i, 8i_9i, PL_SQL
        -- the membershiplist param is the uoid of the record in CMPValidDataTypeList_v - it is used to look up the elementid for CMPDatatype_v.membershiplist.
        -- the owninglanguage param is the uoid of the record in CMPLanguage_v - it is used to look up the elementid for CMPDatatype.owningLanguage. It
        -- only has a value for PL/SQL.
      initRowidDatatypeRec( cmpdatatype_rec );  --initialize common values for ROWID/UROWID
        -- up.xml--> <owb:properties UOID="'3392CBB7F7F144D6B68B18642B41FDDC'" LogicalName="'OWB_INTERNAL'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE537649C0CABE040578CAD0A20B4', 'cf8df2ea368861c99de590ba75432f25', 'ROWID', null,'3392CBB7F7F144D6B68B18642B41FDDC' );
        -- up.xml--> <owb:properties UOID="'9E011F48D16211D48D7100B0D02A59E8'" LogicalName="'73'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A70CABE040578CAD0A20B4', '24201009767e4f0715eccffadaa7646a', 'UROWID', null, '9E011F48D16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'9E011F41D16211D48D7100B0D02A59E8'" LogicalName="'80'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A50CABE040578CAD0A20B4', '24201009767e4f0715eccffadaa7646a', 'UROWID', null, '9E011F41D16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'82E322B741492E5BE034080020F013C0'" LogicalName="'8i'">
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A20CABE040578CAD0A20B4', 'cf8df2ea368861c99de590ba75432f25', 'ROWID', null, '82E322B741492E5BE034080020F013C0');
        -- up.xml--> <owb:properties UOID="'9E011F3AD16211D48D7100B0D02A59E8'" LogicalName="'8i_9i'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A10CABE040578CAD0A20B4', '24201009767e4f0715eccffadaa7646a', 'UROWID', null, '9E011F3AD16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'9E011F2FD16211D48D7100B0D02A59E8'" LogicalName="'PL_SQL'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE537649F0CABE040578CAD0A20B4', '67c2aece9a5ae508aa25b073241ed9df', 'UROWID', '9E011EFCD16211D48D7100B0D02A59E8', '9E011F2FD16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'9E011F48D16211D48D7100B0D02A59E8'" LogicalName="'73'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A60CABE040578CAD0A20B4', 'cf8df2ea368861c99de590ba75432f25', 'ROWID', null, '9E011F48D16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'9E011F41D16211D48D7100B0D02A59E8'" LogicalName="'80'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A40CABE040578CAD0A20B4', 'cf8df2ea368861c99de590ba75432f25', 'ROWID', null, '9E011F41D16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'82E322B741492E5BE034080020F013C0'" LogicalName="'8i'">
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A30CABE040578CAD0A20B4', '24201009767e4f0715eccffadaa7646a', 'UROWID', null, '82E322B741492E5BE034080020F013C0');
        -- up.xml--> <owb:properties UOID="'9E011F3AD16211D48D7100B0D02A59E8'" LogicalName="'8i_9i'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE53764A00CABE040578CAD0A20B4', 'cf8df2ea368861c99de590ba75432f25', 'ROWID', null, '9E011F3AD16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'9E011F2FD16211D48D7100B0D02A59E8'" LogicalName="'PL_SQL'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE537649E0CABE040578CAD0A20B4', 'cf8df2ea368861c99de590ba75432f25', 'ROWID', '9E011EFCD16211D48D7100B0D02A59E8', '9E011F2FD16211D48D7100B0D02A59E8');
        -- up.xml--> <owb:properties UOID="'3392CBB7F7F144D6B68B18642B41FDDC'" LogicalName="'OWB_INTERNAL'"/>
      addcmpdatatype( cmpdatatype_rec, '2DEEE537649D0CABE040578CAD0A20B4', '24201009767e4f0715eccffadaa7646a', 'UROWID', null, '3392CBB7F7F144D6B68B18642B41FDDC');
   END;

      --***************************************************
      -- COLUMN_USAGE Property, used in MV, view, and table operators
      --    Valid values: ROWID, ROWNUM, TABLE_COL
      --***************************************************
    DECLARE
      className VARCHAR2(255);
      classId   NUMBER(9,0);
  
      propertySetName VARCHAR2(255);
      propertySetType NUMBER(9,0);
      propertySetId   NUMBER(9,0);
  
      domn WBDOMAIN_V%ROWTYPE;
      dlst NameList;
      pdef PROPERTYDEFINITION_V%ROWTYPE;
      puse PROPERTYUSAGEDEFINITION_V%ROWTYPE;
      dflt WBPRIMITIVEPROPERTY_V%ROWTYPE;
    BEGIN
      -- MaterializedView.up.xml shown below
      -- Table.up.xml varies by one line: name='#Table.inout'
      -- View.up.xml varies by one line: name='#View.inout'
      --up.xml-><Class
      --up.xml->  name='oracle.wh.repos.impl.mapping.CMPMapAttribute'
      --up.xml->>  
      --up.xml->  <PropertySet
      --up.xml->    Position='0'
      --up.xml->    extends='#EntityAttributeLoadParams'
      --up.xml->    definitionKey='MAPPING.TABLE.INOUTGRP'
      --up.xml->    name='#MaterializedView.inout'
      --up.xml->    propertyType='logical'
      --up.xml->  >
      --up.xml->  <Attribute
      --up.xml->    Position='0'
      --up.xml->    choices='TABLE_COL,ROWID,ROWNUM'
      --up.xml->    dataType='String'
      --up.xml->    definitionKey='TABLE.INPUTOUTPUTPARAM.COLUMN_USAGE'
      --up.xml->    description='Indicates usage as a pseudo-column or normal table column'
      --up.xml->    exportControl='public'
      --up.xml->    initValue='TABLE_COL'
      --up.xml->   isHidden='true'
      --up.xml->   isPassword='false'
      --up.xml->   isPublishedToPublicApi='true'
      --up.xml->    isReadOnly='false'
      --up.xml->    isTranslatable='false'
      --up.xml->    name='COLUMN_USAGE'
      --up.xml->  >
      --up.xml->  </Attribute>
      --up.xml->   </PropertySet>
      --up.xml-> </Class>    
      className := 'oracle.wh.repos.impl.mapping.CMPMapAttribute';
      classId   := classIdOf(className);
      propertySetType := propertytypeIdOf('logical');
 
      --***************************************************
      -- WBDOMAIN
      --***************************************************
      domn := newWBDomain('COLUMN_USAGE', datatypeIdOf('String'));

      domn.METADATASIGNATURE := 'fe60461a121657ffe0e1062dcfa27f5e';
      domn.UOID              := '2E420CA4E7C7F922E040578CAD0A4E79';
      addWBDomain(domn);

      --***************************************************
      -- TABLE_COL, WBPRIMITIVEPROPERTY
      --***************************************************
      dflt := newWBPrimitiveProperty(
                 classId       => domn.ELEMENTID
                ,ownerId       => domn.ELEMENTID
                ,datatype      => domn.DATATYPE
                ,propertyName  => NULL
                ,propertyValue => 'TABLE_COL'
              );

      dflt.METADATASIGNATURE := '5a4e4a29462ec6f789d91252303a9091';
      dflt.UOID              := '2EE48E16E6D4F521E040578CAD0A15EE';
      addWBPrimitiveProperty(dflt);

      --***************************************************
      -- ROWID, WBPRIMITIVEPROPERTY
      --***************************************************
      dflt := newWBPrimitiveProperty(
                 classId       => domn.ELEMENTID
                ,ownerId       => domn.ELEMENTID
                ,datatype      => domn.DATATYPE
                ,propertyName  => NULL
                ,propertyValue => 'ROWID'
              );

      dflt.METADATASIGNATURE := '5a4e4a29462ec6f789d91252303a9091';
      dflt.UOID              := '2EE48E16E6D5F521E040578CAD0A15EE';
      addWBPrimitiveProperty(dflt);

      --***************************************************
      -- ROWNUM, WBPRIMITIVEPROPERTY
      --***************************************************
      dflt := newWBPrimitiveProperty(
                 classId       => domn.ELEMENTID
                ,ownerId       => domn.ELEMENTID
                ,datatype      => domn.DATATYPE
                ,propertyName  => NULL
                ,propertyValue => 'ROWNUM'
              );

      dflt.METADATASIGNATURE := '5a4e4a29462ec6f789d91252303a9091';
      dflt.UOID              := '2EE48E16E6D6F521E040578CAD0A15EE';
      addWBPrimitiveProperty(dflt);


      --***************************************************
      -- Default value, WBPRIMITIVEPROPERTY
      --***************************************************
      dflt := newWBPrimitiveProperty(
                 classId       => domn.ELEMENTID
                ,ownerId       => domn.ELEMENTID
                ,datatype      => domn.DATATYPE
                ,propertyName  => 'DefaultValue'
                ,propertyValue => 'TABLE_COL'
              );

      dflt.METADATASIGNATURE := 'a1554f1d2c506c4c817ab7f456f7a684';
      dflt.UOID              := '2E8FEAFDC7419009E040578CAD0A39EF';
      addWBPrimitiveProperty(dflt);

      --***************************************************
      -- Property definition
      --***************************************************
      pdef := newPropertyDefinition(domn.NAME, classId);
      pdef.PROPERTYGROUPDEFINITION := 0;
      pdef.PROPERTYTYPE            := propertySetType; -- and what kind of property

      pdef.DATATYPE      := domn.ELEMENTID;
      pdef.DEFINITIONKEY := 'columnUsage';
      pdef.DESCRIPTION   := 'Indicates usage as a pseudo-column or normal table column';
      pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function
      pdef.ISPASSWORD    := 0; -- is making function booleanIdOf() worth it?
      pdef.HIDDEN        := 1; -- nls deadline was missed for this patch release

      -- get real values for these by running against new install
      -- then come back and put values here
      pdef.METADATASIGNATURE := 'cefea10bac7dc700cd429a25cce83003';
      pdef.UOID              := '2E420CA4FA94F922E040578CAD0A4E79';
      pdef.POSITION          := 62;

      addPropertyDefinition(pdef);
      
      --***************************************************
      -- Property Usage: Materialized view, table, and view operators
      --***************************************************
      propertySetName := 'MaterializedView.inout';
      propertySetType := propertytypeIdOf('logical');
      propertySetId   := psetIdOf(classId, propertySetType, propertySetName);
      puse := newPropertyUsageDefinition(propertySetId, pdef);
      puse.DEFINITIONKEY := 'TABLE.INPUTOUTPUTPARAM.COLUMN_USAGE';
      puse.METADATASIGNATURE := 'b0d90268ba95ad62dfb437bcecad6fee';
      puse.UOID              := '2E6DA4B800C85242E040578CAD0A305F';

      addPropertyUsageDefinition(puse);
      
      propertySetName := 'Table.inout';
      propertySetType := propertytypeIdOf('logical');
      propertySetId   := psetIdOf(classId, propertySetType, propertySetName);
      puse := newPropertyUsageDefinition(propertySetId, pdef);
      puse.DEFINITIONKEY := 'TABLE.INPUTOUTPUTPARAM.COLUMN_USAGE';
      puse.METADATASIGNATURE := 'b0d90268ba95ad62dfb437bcecad6fee';
      puse.UOID              := '2E6DA4B800A25242E040578CAD0A305F';

      addPropertyUsageDefinition(puse);
      
      propertySetName := 'View.inout';
      propertySetType := propertytypeIdOf('logical');
      propertySetId   := psetIdOf(classId, propertySetType, propertySetName);
      puse := newPropertyUsageDefinition(propertySetId, pdef);
      puse.DEFINITIONKEY := 'TABLE.INPUTOUTPUTPARAM.COLUMN_USAGE';
      puse.METADATASIGNATURE := 'b0d90268ba95ad62dfb437bcecad6fee';
      puse.UOID              := '2E6DA4B8007D5242E040578CAD0A305F';

      addPropertyUsageDefinition(puse);

   END;
END;
/