-- In place upgrade for bug 9577909: rebuild view SUB_FQ_PUSAGE_DEF 
 
CREATE OR REPLACE VIEW SUB_FQ_PUSAGE_DEF AS
  /* Set of all usage definitions by class type */
  -- handy headers to stick on top of sqlworkbook results
  -- classTypeName pusageId pdefId propName pusageType storageType datatype typeName hidden publishToPublicApi contributeToSignature dynamicDefault defaultValue psetId psetName psetClassName nlsKey description
  SELECT * FROM (
    SELECT distinct
      cdefs.cdefTypeName classTypeName
      ,puses.id pusageId, puses.pdefId pdefId, puses.name propName, puses.usageType pusageType, puses.storageType
      ,puses.datatype, puses.typeName, puses.hidden 
      ,NVL(puses.publishToPublicApi, DECODE(puses.hidden, 'true', 'false', 'false', 'true', 'true')) publishToPublicApi
      ,puses.contributeToSignature, puses.dynamicDefault
      ,puses.defaultValue, puses.psetId
      ,psets.superPsetName psetName, psets.superPsetClassName psetClassName, puses.puseKey nlsKey, puses.description
      ,puses.groupId AS groupId
      ,groupDef.name AS groupName
      ,groupDef.logicalName AS groupDefinitionKey
      ,puses.createdBy AS createdBy, puses.created as created
      ,puses.updatedBy AS updatedBy, puses.updated AS updated
    FROM
      CMPPropertyGroupDefinition_v groupDef,
   	--2 propdef.cdefs BEG
      ( /* SUB_ALL_RELATED_CDEFS cdefs */
        -- CREATE OR REPLACE VIEW SUB_ALL_RELATED_CDEFS AS -- name: 21 char
        /* Set of classDef ids to process: starting element''s classDef + all superclasses */
        SELECT
          elementId AS cdefId
          ,DECODE(classDef.weaklyTyped, 
            1, classDef.name, 
            SUBSTR(classDef.name, INSTR(classDef.name, '.', -1)+1)) AS cdefTypeName
          ,elementId AS parentCdefId
          ,DECODE(classDef.weaklyTyped, 
            1, classDef.name, 
            SUBSTR(classDef.name, INSTR(classDef.name, '.', -1)+1)) AS parentCdefTypeName
        --  ,0 AS parentCdefLevel
        FROM CMPClassDefinition_v classDef
        --WHERE classDef.name=<something>
        --WHERE classDef.name LIKE ''%.ExternalTable''
        UNION ALL
        SELECT UNIQUE 
          CONNECT_BY_ROOT childClassDef.elementId AS cdefId
          ,CONNECT_BY_ROOT DECODE(childClassDef.weaklyTyped, 
            1, childClassDef.name, 
            SUBSTR(childClassDef.name, INSTR(childClassDef.name, '.', -1)+1)) AS cdefTypeName
          ,parentClassDef.elementId AS parentCdefId
          ,DECODE(parentClassDef.weaklyTyped, 
            1, parentClassDef.name, 
            SUBSTR(parentClassDef.name, INSTR(parentClassDef.name, '.', -1)+1)) AS parentCdefTypeName
        --  ,LEVEL AS parentCdefLevel
        FROM 
		    (select cmpclassusagegroup_v.classusageowner as inheritanceParent, 
					cmpclassusage_v.referencedclassdefinition as inheritanceChild 
					from cmpclassusagegroup_v,cmpclassusage_v
					where cmpclassusage_v.CLASSUSAGEGROUP=cmpclassusagegroup_v.elementid and cmpclassusagegroup_v.name='SUBCLASS'
				union all
				select cmpclassusage_v.referencedclassdefinition as inheritanceParent,
					cmpclassusagegroup_v.classusageowner as inheritanceChild 
					from cmpclassusagegroup_v,cmpclassusage_v
					where cmpclassusage_v.CLASSUSAGEGROUP=cmpclassusagegroup_v.elementid and cmpclassusagegroup_v.name in ('INTERFACE','MULTIPLEINHERITANCE')
				union all
				select superclass as inheritanceParent,
						elementid as inheritanceChild
						from CMPClassDefinition_v) hierDef
            ,CMPClassDefinition_v childClassDef
            ,CMPClassDefinition_v parentClassDef
        WHERE childClassDef.elementId=hierDef.inheritanceChild
          AND parentClassDef.elementId=hierDef.inheritanceParent
        --START WITH childClassdef.elementId=<something>
        --START WITH childClassDef.elementId=(SELECT elementId from Classdefinition_v WHERE name LIKE ''%.ExternalTable'')
        CONNECT BY PRIOR inheritanceParent=inheritanceChild
        /* ORDER BY cdefTypeName, parentCdefId */
      ) /* SUB_ALL_RELATED_CDEFS */ cdefs
   --2 propdef.cdefs END
   --2 propdef.psets BEG
      ,( /* SUB_ALL_RELATED_PSETS psets */
        -- CREATE OR REPLACE VIEW SUB_ALL_RELATED_PSETS AS -- name: 21 char
        /* Set of psetDef ids to process*/
        SELECT UNIQUE
          CONNECT_BY_ROOT psetDef.elementId AS psetId
          ,CONNECT_BY_ROOT decode(psetDef.propertyType, 0, 'CORE', 1, 'LOGICAL', 2, 'CONFIG', 3, 'USERDEF') AS psetType
          ,CONNECT_BY_ROOT psetDef.owningClassDefinition AS classDef
          ,CONNECT_BY_ROOT superPsetDef.owningClassDefinition AS parentClassDef
          ,SUBSTR(classDef.name, INSTR(classDef.name, '.', -1)+1) psetClassName -- for debug
          ,CONNECT_BY_ROOT psetDef.name AS psetName -- for debug
          ,superPsetDef.elementId AS superPsetId
          ,decode(superPsetDef.propertyType, 0, 'CORE', 1, 'LOGICAL', 2, 'CONFIG', 3, 'USERDEF') AS superPsetType
          ,SUBSTR(superClassDef.name, INSTR(superClassDef.name, '.', -1)+1) superPsetClassName -- for debug
          ,superPsetDef.name AS superPsetName -- for debug
        FROM CMPPropertySetDefinition_v psetDef
             ,CMPPropertySetDefinition_v superPsetDef 
             ,CMPClassDefinition_v classDef -- debug
             ,CMPClassDefinition_v superClassDef -- debug
        WHERE psetDef.superPropertySet=superPsetDef.elementId
          AND psetDef.owningClassDefinition=classDef.elementId -- debug
          AND superPsetDef.owningClassDefinition=superClassDef.elementId -- debug
          AND NOT psetDef.propertyType=0
        CONNECT BY PRIOR psetDef.superPropertySet=psetDef.elementId
        UNION ALL
        SELECT
          psetDef.elementId AS psetId
          ,decode(psetDef.propertyType, 0, 'CORE', 1, 'LOGICAL', 2, 'CONFIG', 3, 'USERDEF') AS psetType
          ,psetDef.owningClassDefinition AS classDef
          ,psetDef.owningClassDefinition AS parentClassDef
          ,SUBSTR(classDef.name, INSTR(classDef.name, '.', -1)+1) psetClassName -- for debug
          ,psetDef.name AS psetName -- for debug
          ,psetDef.elementId AS superPsetId
          ,decode(psetDef.propertyType, 0, 'CORE', 1, 'LOGICAL', 2, 'CONFIG', 3, 'USERDEF') AS superPsetType
          ,SUBSTR(classDef.name, INSTR(classDef.name, '.', -1)+1) superPsetClassName -- for debug
          ,psetDef.name AS superPsetName -- for debug
        FROM CMPPropertySetDefinition_v psetDef
             ,CMPClassDefinition_v classDef -- debug
        WHERE psetDef.owningClassDefinition=classDef.elementId -- debug
          AND NOT psetDef.propertyType = 0
        /* ORDER BY psetId, superPsetId */
      ) /* SUB_ALL_RELATED_PSETS */ psets
   --2 propdef.psets END
   --2 propdef.puses BEG
      ,( /* SUB_TYPED_PUSES_W_DFLT puses */
        -- CREATE OR REPLACE VIEW SUB_TYPED_PUSES_W_DFLT -- name: 22 char
        /* property usages with datatype and defaults */
        SELECT UNIQUE
          puse2.id AS id
          ,puse2.pdefId AS pdefId
          ,puse2.name AS name
          ,puse2.description AS description
          ,puse2.usageType AS usageType
          ,puse2.storageType AS storageType
          ,puse2.datatype AS datatype
          ,delem.name AS typeName
          ,DECODE(puse2.hidden,'0', 'false', '1', 'true', puse2.hidden) AS hidden
          ,puse2.contributeToSignature AS contributeToSignature
          ,puse2.dynamicDefault AS dynamicDefault
          ,NVL(puse2.defaultValue, dtype.defaultValue) AS defaultValue
          ,puse2.publishToPublicApi  AS publishToPublicApi
          ,puse2.psetId AS psetId
          ,puse2.puseKey AS puseKey
          ,puse2.groupId AS groupId
          ,puse2.createdBy AS createdBy, puse2.created as created
          ,puse2.updatedBy AS updatedBy, puse2.updated AS updated
        FROM
    --3 propdef.puses.dtype BEG
         ( /* SUB_DEFAULT_FOR_DATATYPE dtype */
          -- CREATE OR REPLACE VIEW SUB_DEFAULT_FOR_DATATYPE AS -- name: 24 char
          /* default value for domain/datatype */
          SELECT 
            'Domain' typeClass -- debug
            ,CONNECT_BY_ROOT domain.elementId typeId
            ,CONNECT_BY_ROOT domain.name typeName
            ,nvl(defaultValueProp.value, 
              ( SELECT value
                FROM CMPPrimitiveModelAttribute_v
                WHERE modelAttributeOwner=domain.datatype
                  AND name='DEFAULTVALUE')
            ) defaultValue
            --,MIN(LEVEL) pathLen -- real - but get error - how to do?
            ,LEVEL pathLen -- debug
            ,SYS_CONNECT_BY_PATH(domain.name, '/') path -- debug
          FROM 
            CMPDomain_v domain LEFT OUTER JOIN CMPPrimitiveModelAttribute_v defaultValueProp ON (defaultValueProp.modelAttributeOwner=domain.elementId)
          WHERE
            defaultValueProp.name='DEFAULTVALUE'
            CONNECT BY PRIOR domain.datatype=domain.elementId
          UNION ALL
          SELECT 
            'Type' -- debug
            ,datatype.elementId typeId
            ,datatype.name typeName
            ,defaultValueProp.value defaultValue
            ,0 pathLen -- debug
            ,'' path -- debug
          FROM 
            CMPAbstractType_v datatype LEFT OUTER JOIN CMPPrimitiveModelAttribute_v defaultValueProp ON (defaultValueProp.modelAttributeOwner=datatype.elementId)
          WHERE
            defaultValueProp.name='DEFAULTVALUE'
         ) /* SUB_DEFAULT_FOR_DATATYPE */ dtype
    --3 propdef.puses.dtype END
    --3 propdef.puses.puse2 BEG
        ,( /* SUB_TYPED_PUSES puse2 */
          -- CREATE OR REPLACE VIEW SUB_TYPED_PUSES
          /* puse def with datatype and maybe defaultValue */
          SELECT
            puse.id AS id
            ,pdef.id AS pdefId
            ,puse.name AS name
            ,puse.description AS description
            ,puse.usageType AS usageType
            ,pdef.storageType AS storageType
            ,NVL(puse.datatype, pdef.datatype) AS datatype
            ,NVL(puse.hidden, NVL(pdef.grpHidden,pdef.hidden)) AS hidden
            ,NVL(pdef.contributeToSignature,  'true') AS contributeToSignature
            ,NVL(puse.dynamicDefault, pdef.dynamicDefault) AS dynamicDefault
            ,NVL(puse.defaultValue, pdef.defaultValue) AS defaultValue
            ,puse.publishToPublicApi AS publishToPublicApi
            ,puse.psetId AS psetId
            ,puse.puseKey AS puseKey
            ,pdef.groupId AS groupId
            ,puse.createdBy AS createdBy, puse.created as created
            ,puse.updatedBy AS updatedBy, puse.updated AS updated
          FROM  
     --4 propdef.puses.puse2.pdef BEG
          ( /* SUB_PDEF_W_META pdef */
            -- CREATE OR REPLACE VIEW SUB_PDEF_W_META
            /* property definition with meta DefaultValue */
            SELECT
              pdef.elementId AS id
              ,pdef.name AS name
              ,pdef.description AS description
              ,decode(pdef.propertyType, 0, 'CORE', 1, 'LOGICAL', 2, 'CONFIG', 3, 'USERDEF') AS storageType
              ,pdef.type AS datatype
              ,pdef.hidden AS hidden
              ,(SELECT hidden FROM CMPPropertyGroupDefinition_v WHERE elementId=pdef.PropertyGroupDefinition AND hidden='1') AS grpHidden
              ,decode(pdef.contributeToSignature, 0, 'false', 1, 'true') AS contributeToSignature
              ,pdef.dynamicDefault AS dynamicDefault
              ,dflt.value AS defaultValue
              ,pdef.propertyGroupDefinition AS groupId
            FROM
              CMPPropertyDefinition_v pdef 
              ,CMPPrimitiveModelAttribute_v dflt
            WHERE
              dflt.modelAttributeOwner(+)=pdef.elementId
              AND dflt.name(+)='DEFAULTVALUE'
          ) /* SUB_PDEF_W_META */ pdef
     --4 propdef.puses.puse2.pdef END
     --4 propdef.puses.puse2.puse BEG
          ,( /* SUB_PUSE_W_META puse */
            -- CREATE OR REPLACE VIEW SUB_PUSE_W_META
            /* property usage definition with meta DefaultValue, Hidden, DynamicDefault,
               USAGE_DATATYPE */
            SELECT
              puse.elementId AS id
              ,puse.name AS name
              ,puse.description AS description
              ,decode(pset.propertyType, 0, 'CORE', 1, 'LOGICAL', 2, 'CONFIG', 3, 'USERDEF') AS usageType
              ,puse.type AS datatype
              ,puse.hidden AS hidden
              ,puse.dynamicDefault AS dynamicDefault
              ,(SELECT value FROM CMPPrimitiveModelAttribute_v WHERE name='DEFAULTVALUE' AND modelAttributeOwner=puse.elementId) AS defaultValue
              ,puse.isPublishedToPublicApi AS publishToPublicApi
              ,puse.definitionKey AS puseKey
              ,pset.elementId AS psetId
              ,puse.elementId AS pdefId
              ,puse.createdBy AS createdBy, puse.creationTimeStamp as created
              ,puse.updatedBy AS updatedBy, puse.updateTimeStamp AS updated
            FROM
              CMPPropertyDefinition_v puse
              ,CMPPropertySetDefinition_v pset
            WHERE
                puse.owningPropertySetDefinition=pset.elementId
          ) /* SUB_PUSE_W_META */ puse
     --4 propdef.puses.puse2.puse END
          WHERE
            puse.pdefId=pdef.id
        ) /* SUB_TYPED_PUSES */ puse2
    --3 propdef.puses.puse2 END
        ,CMPDefinitionElement_v delem
        WHERE puse2.datatype=dtype.typeId(+)
        AND   puse2.datatype=delem.elementId
      )/* SUB_TYPED_PUSES_W_DFLT */ puses
   --2 propdef.puses END
      WHERE
          cdefs.parentCdefId=psets.classDef
      AND psets.superPsetId=puses.psetId
      AND puses.groupId=groupDef.elementId(+)
  ) WITH READ ONLY;