-- Create sql in-place upgrade patch for some up.xml
-- property changes. The sql will be shown (as much as 
-- possible) in-line with associated segment of up.xml.
-- The xml will be set off from the sql by commenting each
-- line with "-- up.xml ->".
--
-- Bug #'s/Properties updated
--
--  Bug 8517147 - db112-090515:nls:lt:db11.2:9:string in english
-- 

SET SERVEROUTPUT ON
SET LINESIZE 500
-- ##########################################################################
DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util)
-- ##########################################################################

  SCRIPTID CONSTANT VARCHAR2(40) := 'bug 8517147'; -- For output logging
  EOL      CONSTANT VARCHAR2(01) := CHR(10); -- new line
  TRACEON  CONSTANT BOOLEAN      := TRUE; -- Provide function level trace output
  OUTINITED         BOOLEAN      := FALSE; -- Flag indicating output has been enabled

  PROPERTYTYPE_CORE          NUMBER:= 0;
  PROPERTYTYPE_LOGICAL       NUMBER:= 1;
  PROPERTYTYPE_PHYSICAL      NUMBER:= 2;
  PROPERTYTYPE_CONFIGURATION NUMBER:= 2;
  PROPERTYTYPE_USERDEFINED   NUMBER:= 3;


  -- Record definitions for the various views
  -- Handy sql to make record definitions (some hand editing required (in sqlworksheet anyway)

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

-- ==========================================================================
-- 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;
    
    IF (LENGTH(msg) + LENGTH(SCRIPTID) + 2) > 255 THEN
      -- 10.1 database can't handle messages with length > 255
      --DBMS_OUTPUT.put_line('Message length > 255');
      --DBMS_OUTPUT.put_line(SUBSTR(msg, 0, 255));
      -- Break the string up into lines or 255-byte chunks
      DECLARE
        l_msg_line VARCHAR2(255);
        l_eol_index NUMBER;
        l_curr_index NUMBER;
        l_msg_len NUMBER := LENGTH(msg);
        l_loop_index NUMBER := 0;
      BEGIN
        DBMS_OUTPUT.PUT_LINE(SCRIPTID || ':');
        l_eol_index := 1;
        l_curr_index := 1;
        l_loop_index := 0;
        WHILE (l_curr_index < l_msg_len AND l_loop_index < 10) LOOP
          l_eol_index := INSTR(msg, EOL, l_curr_index);
          --DBMS_OUTPUT.put_line('l_eol_index = ' || TO_CHAR(l_eol_index));
          IF (l_eol_index <= 0) OR (l_eol_index - l_curr_index) > 255 THEN
            l_msg_line := SUBSTR(msg, l_curr_index, 255);
            l_curr_index := l_curr_index + 255;
          ELSE 
            l_msg_line := SUBSTR(msg, l_curr_index, l_eol_index - l_curr_index);
            l_curr_index := l_eol_index + 1;
          END IF;
          DBMS_OUTPUT.PUT_LINE(l_msg_line);        
          l_loop_index := l_loop_index + 1;
        END LOOP;
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
    ELSE 
      DBMS_OUTPUT.put_line(SCRIPTID || ': ' || msg);
    END IF;  

  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;  

-- ==========================================================================
  FUNCTION getPlatformIdByName(
    p_platformName VARCHAR2
  )
  RETURN NUMBER
-- ==========================================================================
  IS
    p_id NUMBER(9,0);
  BEGIN
     SELECT ELEMENTID INTO p_id FROM CMPPlatform_r WHERE WORKSPACEID=1 AND NAME=p_platformName;
     put( 'Found Platform [name: ' || p_platformName || '; elementid: ' || p_id || ']' || EOL);
    RETURN p_id;
  END;

-- ==========================================================================
  FUNCTION getClassIdByName(
    p_platformId NUMBER,
    p_className VARCHAR2
  )
  RETURN NUMBER
-- ==========================================================================
  IS
    p_classId NUMBER(9,0);
  BEGIN
     SELECT ELEMENTID INTO p_classId FROM CMPClassDefinition_r WHERE NAME=p_className AND OWNINGFOLDER=p_platformId;
     put( 'Found ClassDefinition [name: ' || p_className || '; elementid: ' || p_classId || '; owningPlatform: ' || p_platformId || ']' || EOL); 
    RETURN p_classId;
  END;


-- ==========================================================================
  FUNCTION getPropertySetId(
    p_classId NUMBER,
    p_propertyType NUMBER,
    p_name VARCHAR2
  )
  RETURN NUMBER
-- ==========================================================================
  IS
    p_propertySetId NUMBER(9,0);
  BEGIN
     SELECT ELEMENTID INTO p_propertySetId FROM CMPPropertySetDefinition_r 
         WHERE FIRSTCLASSOBJECT=p_classId AND PROPERTYTYPE=p_propertyType
               AND NAME LIKE '%'||p_name||'%';
     put( 'Found PropertySetDefinition [name: ' || p_name || '; elementid: ' || p_propertySetId || '; owningClassDef: ' || p_classId || ']' || EOL);          
    RETURN p_propertySetId;
  END;
  

-- ==========================================================================
  FUNCTION getPropertyGroupId(
    p_propertySetId NUMBER,
    p_definitionKey VARCHAR2
  )
  RETURN NUMBER
-- ==========================================================================
  IS
    p_propertyGroupId NUMBER(9,0);
  BEGIN
     SELECT ELEMENTID INTO p_propertyGroupId FROM CMPPropertyGroupDefinition_r
         WHERE PROPERTYGROUPOWNER=p_propertySetId AND LOGICALNAME=p_definitionKey;
    put( 'Found PropertyGroupDefinition [definitionKey: ' || p_definitionKey || '; propertySetId: ' || p_propertySetId || '; elementId: ' || p_propertyGroupId || ']' || EOL);          
    RETURN p_propertyGroupId;
  END;
  
-- ==========================================================================
  FUNCTION getPropertyDefinitionId(
    p_propertySetId NUMBER,
    p_propertyGroupId NUMBER,
    p_definitionKey VARCHAR2
  )
  RETURN NUMBER
-- ==========================================================================
  IS
    p_propertyDefinitionId NUMBER(9,0);
  BEGIN
     SELECT ELEMENTID INTO p_propertyDefinitionId FROM CMPPropertyDefinition_r
         WHERE OWNINGPROPERTYSETDEFINITION=p_propertySetId AND PROPERTYGROUPDEFINITION=p_propertyGroupId AND DEFINITIONKEY=p_definitionKey;
    put( 'Found PropertyDefinition [definitionKey: ' || p_definitionKey || '; propertySetId: ' || p_propertySetId || '; propertyGroupId: ' || p_propertyGroupId || '; elementId: ' || p_propertyDefinitionId || ']' || EOL);          
  RETURN p_propertyDefinitionId;
  END;  

-- ==========================================================================

  PROCEDURE updateTranslatable(
     p_value NUMBER,
     p_propertyDefinitionId NUMBER) 
  IS
  old_value NUMBER(9,0);
  BEGIN
    -- SELECT translatable into old_value FROM CMPPropertyDefinition_r  WHERE elementId=p_propertyDefinitionId;
    -- put( 'Found old value of translatable of PropertyDefinition [elementId: ' || p_propertyDefinitionId || '; translatable: ' || old_value  || EOL);          
     UPDATE CMPPropertyDefinition_r  SET translatable=p_value WHERE elementId=p_propertyDefinitionId;
     put('Update translatable for PropertyDefinition [elementId:' || p_propertyDefinitionId || '; translatable: ' || p_value  || EOL);
  END updateTranslatable;  

-- ##########################################################################
-- ##########################################################################
BEGIN --    Outer block - utility functions/variables now defined
-- ##########################################################################
-- ##########################################################################  

  -- =======================================================
  DECLARE -- Inner block for utility function/variable usage
          -- aka 'user' declarations
  -- =======================================================   
   l_platformId NUMBER;
   l_classDefinitionId NUMBER;
   l_propertySetDefinitionId NUMBER;
   l_propertyGroupDefinitionId NUMBER;
   l_propertyDefinitionId NUMBER;
  -- =======================================================
  BEGIN -- start handling data types defined in up.xml
  -- =======================================================   
 --up.xml->  <Platform name='GENERIC'>
    l_platformId := getPlatformIdByName('GENERIC'); 
--CMPAppsConcurrentManagerLocation.up.xml-> <Class
--CMPAppsConcurrentManagerLocation.up.xml->   name='oracle.wh.repos.impl.environment.CMPAppsConcurrentManagerLocation'
--CMPAppsConcurrentManagerLocation.up.xml->   extends='oracle.wh.repos.impl.environment.CMPLogicalLocation'>
    l_classDefinitionId := getClassIdByName(l_platformId, 'oracle.wh.repos.impl.environment.CMPAppsConcurrentManagerLocation');
      
--CMPAppsConcurrentManagerLocation.up.xml->   <PropertySet name='Default' propertyType='logical' >
    l_propertySetDefinitionId := getPropertySetId(l_classDefinitionId,PROPERTYTYPE_LOGICAL, 'LOGICAL:DEFAULT');
      
 --CMPAppsConcurrentManagerLocation.up.xml->   <PropertyGroup definitionKey="AppsLocationInfo" name="AppsLocationInfo">
    l_propertyGroupDefinitionId := getPropertyGroupId(l_propertySetDefinitionId, 'AppsLocationInfo');
  --CMPAppsConcurrentManagerLocation.up.xml->  <Attribute
  --CMPAppsConcurrentManagerLocation.up.xml->          name='CONNECTION_TYPE' dataType='String'
  --CMPAppsConcurrentManagerLocation.up.xml->          choices='HOST:PORT:SERVICE,SQL*NET Connection'
  --CMPAppsConcurrentManagerLocation.up.xml->          initValue='HOST:PORT:SERVICE'
  --CMPAppsConcurrentManagerLocation.up.xml->          definitionKey='CMPLocation_ConnectionType'
  --CMPAppsConcurrentManagerLocation.up.xml->          description='Type of the connection.'
  --CMPAppsConcurrentManagerLocation.up.xml->          exportControl='public'
  --CMPAppsConcurrentManagerLocation.up.xml->          isPassword='false'
  --CMPAppsConcurrentManagerLocation.up.xml->          isTranslatable='false'
  --CMPAppsConcurrentManagerLocation.up.xml->          isReadOnly='true'
  --CMPAppsConcurrentManagerLocation.up.xml->          isHidden='true'
  --CMPAppsConcurrentManagerLocation.up.xml->   />     
    l_propertyDefinitionId := getPropertyDefinitionId(l_propertySetDefinitionId,l_propertyGroupDefinitionId, 'CMPLocation_ConnectionType');
    updateTranslatable(1,l_propertyDefinitionId);

--CMPDiscoLocation.up.xml->    <Class
--CMPDiscoLocation.up.xml->      name='oracle.wh.repos.impl.environment.CMPDiscoLocation'
--CMPDiscoLocation.up.xml->      extends='oracle.wh.repos.impl.environment.CMPFileTransferLocation'
--CMPDiscoLocation.up.xml->   > 
    l_classDefinitionId := getClassIdByName(l_platformId, 'oracle.wh.repos.impl.environment.CMPDiscoLocation');
--CMPDiscoLocation.up.xml->    <PropertySet   name='Default' propertyType='logical'>
    l_propertySetDefinitionId := getPropertySetId(l_classDefinitionId,PROPERTYTYPE_LOGICAL,'LOGICAL:DEFAULT');
--CMPDiscoLocation.up.xml->    <PropertyGroup definitionKey="LocationInfo" name="LocationInfo">
    l_propertyGroupDefinitionId := getPropertyGroupId(l_propertySetDefinitionId, 'LocationInfo');
--CMPDiscoLocation.up.xml->    <Attribute
--CMPDiscoLocation.up.xml->          name='CONNECTION_TYPE' dataType='String'
--CMPDiscoLocation.up.xml->         choices='HOST:PORT:SERVICE,SQL*NET Connection'
--CMPDiscoLocation.up.xml->          initValue='HOST:PORT:SERVICE'
--CMPDiscoLocation.up.xml->          definitionKey='CMPLocation_ConnectionType'
--CMPDiscoLocation.up.xml->          description='Type of the connection.'
--CMPDiscoLocation.up.xml->          exportControl='public'
--CMPDiscoLocation.up.xml->          isPassword='false'
--CMPDiscoLocation.up.xml->          isTranslatable='false'
--CMPDiscoLocation.up.xml->          isReadOnly='true'
--CMPDiscoLocation.up.xml->          isHidden='true'
--CMPDiscoLocation.up.xml->     />    
    l_propertyDefinitionId := getPropertyDefinitionId(l_propertySetDefinitionId,l_propertyGroupDefinitionId, 'CMPLocation_ConnectionType');
    updateTranslatable(1,l_propertyDefinitionId);
    
--CMPOWFLocation.up.xml->         <Class
--CMPOWFLocation.up.xml->          name='oracle.wh.repos.impl.environment.CMPOWFLocation'
--CMPOWFLocation.up.xml->          extends='oracle.wh.repos.impl.environment.CMPLogicalLocation'
--CMPOWFLocation.up.xml->       >
   l_classDefinitionId := getClassIdByName(l_platformId, 'oracle.wh.repos.impl.environment.CMPOWFLocation');
--CMPOWFLocation.up.xml->    <PropertySet name='Default' propertyType='logical' >
   l_propertySetDefinitionId := getPropertySetId(l_classDefinitionId,PROPERTYTYPE_LOGICAL,'LOGICAL:DEFAULT');
--CMPOWFLocation.up.xml->    <PropertyGroup definitionKey="LocationInfo" name="LocationInfo">
   l_propertyGroupDefinitionId := getPropertyGroupId(l_propertySetDefinitionId, 'LocationInfo');
--CMPOWFLocation.up.xml->   <Attribute
--CMPOWFLocation.up.xml->          name='CONNECTION_TYPE' dataType='String'
--CMPOWFLocation.up.xml->          choices='HOST:PORT:SERVICE,SQL*NET Connection'
--CMPOWFLocation.up.xml->          initValue='HOST:PORT:SERVICE'
--CMPOWFLocation.up.xml->          definitionKey='CMPLocation_ConnectionType'
--CMPOWFLocation.up.xml->          description='Type of the connection.'
--CMPOWFLocation.up.xml->          exportControl='public'
--CMPOWFLocation.up.xml->          isPassword='false'
--CMPOWFLocation.up.xml->          isTranslatable='false'
--CMPOWFLocation.up.xml->          isReadOnly='true'
--CMPOWFLocation.up.xml->          isHidden='true'
--CMPOWFLocation.up.xml->        />
    l_propertyDefinitionId := getPropertyDefinitionId(l_propertySetDefinitionId,l_propertyGroupDefinitionId, 'CMPLocation_ConnectionType');
    updateTranslatable(1,l_propertyDefinitionId);
    
--CMPOracleDatabaseLocation.up.xml->    <Class
--CMPOracleDatabaseLocation.up.xml->       name='oracle.wh.repos.impl.environment.CMPOracleDatabaseLocation'
--CMPOracleDatabaseLocation.up.xml->       extends='oracle.wh.repos.impl.environment.CMPGenericJDBCLocation'
--CMPOracleDatabaseLocation.up.xml->    >   
    l_classDefinitionId := getClassIdByName(l_platformId, 'oracle.wh.repos.impl.environment.CMPOracleDatabaseLocation');
--CMPOracleDatabaseLocation.up.xml->      <PropertySet name='Default' propertyType='logical' >
    l_propertySetDefinitionId := getPropertySetId(l_classDefinitionId, PROPERTYTYPE_LOGICAL, 'LOGICAL:DEFAULT');
--CMPOracleDatabaseLocation.up.xml->     <PropertyGroup definitionKey="LocationInfo" name="LocationInfo">
    l_propertyGroupDefinitionId := getPropertyGroupId(l_propertySetDefinitionId, 'LocationInfo');
--CMPOracleDatabaseLocation.up.xml->      <Attribute
--CMPOracleDatabaseLocation.up.xml->           name='CONNECTION_TYPE' dataType='String'
--CMPOracleDatabaseLocation.up.xml->           choices='HOST:PORT:SERVICE,SQL*NET Connection,OCI,Database Link'
--CMPOracleDatabaseLocation.up.xml->           initValue='HOST:PORT:SERVICE'
--CMPOracleDatabaseLocation.up.xml->           definitionKey='CMPLocation_ConnectionType'
--CMPOracleDatabaseLocation.up.xml->           description='Type of the connection.'
--CMPOracleDatabaseLocation.up.xml->           exportControl='public'
--CMPOracleDatabaseLocation.up.xml->           isPassword='false'
--CMPOracleDatabaseLocation.up.xml->           isTranslatable='false'
--CMPOracleDatabaseLocation.up.xml->          isReadOnly='true'
--CMPOracleDatabaseLocation.up.xml->           isHidden='true'
--CMPOracleDatabaseLocation.up.xml->           valueInterpreter='oracle.wh.repos.impl.environment.ConnectionTypeValueInterpreterDecoder'
--CMPOracleDatabaseLocation.up.xml->         />
    l_propertyDefinitionId := getPropertyDefinitionId(l_propertySetDefinitionId,l_propertyGroupDefinitionId, 'CMPLocation_ConnectionType');
    updateTranslatable(1,l_propertyDefinitionId);
 
    
--CMPOracleGatewayLocation.up.xml->  <Class
--CMPOracleGatewayLocation.up.xml->     name='oracle.wh.repos.impl.environment.CMPOracleGatewayLocation'
--CMPOracleGatewayLocation.up.xml->     extends='oracle.wh.repos.impl.environment.CMPLogicalLocation'
--CMPOracleGatewayLocation.up.xml->   >   
    l_classDefinitionId := getClassIdByName(l_platformId, 'oracle.wh.repos.impl.environment.CMPOracleGatewayLocation');
--CMPOracleGatewayLocation.up.xml->     <PropertySet name='Default' propertyType='logical'>
    l_propertySetDefinitionId := getPropertySetId(l_classDefinitionId, PROPERTYTYPE_LOGICAL,'LOGICAL:DEFAULT');
--CMPOracleGatewayLocation.up.xml->     <PropertyGroup definitionKey="LocationInfo" name="LocationInfo">
    l_propertyGroupDefinitionId := getPropertyGroupId(l_propertySetDefinitionId, 'LocationInfo');
    
--CMPOracleGatewayLocation.up.xml->     <Attribute
--CMPOracleGatewayLocation.up.xml->           name='CONNECTION_TYPE' dataType='String'
--CMPOracleGatewayLocation.up.xml->           choices='HOST:PORT:SERVICE,SQL*NET Connection,Database Link'
--CMPOracleGatewayLocation.up.xml->           initValue='HOST:PORT:SERVICE'
--CMPOracleGatewayLocation.up.xml->           definitionKey='CMPLocation_ConnectionType'
--CMPOracleGatewayLocation.up.xml->           description='Type of the connection.'
--CMPOracleGatewayLocation.up.xml->           exportControl='public'
--CMPOracleGatewayLocation.up.xml->           isPassword='false'
--CMPOracleGatewayLocation.up.xml->           isTranslatable='false'
--CMPOracleGatewayLocation.up.xml->           isReadOnly='true'
--CMPOracleGatewayLocation.up.xml->          isHidden='true'
--CMPOracleGatewayLocation.up.xml->          isHidden='true'
--CMPOracleGatewayLocation.up.xml->        />
    l_propertyDefinitionId := getPropertyDefinitionId(l_propertySetDefinitionId,l_propertyGroupDefinitionId, 'CMPLocation_ConnectionType');
    updateTranslatable(1,l_propertyDefinitionId);
    
--CMPRuntimeLocation.up.xml->     <Class
--CMPRuntimeLocation.up.xml->         name='oracle.wh.repos.impl.environment.CMPRuntimeLocation'
--CMPRuntimeLocation.up.xml->         extends='oracle.wh.repos.impl.environment.CMPLocation'
--CMPRuntimeLocation.up.xml->     >
   l_classDefinitionId := getClassIdByName(l_platformId, 'oracle.wh.repos.impl.environment.CMPRuntimeLocation');
--CMPRuntimeLocation.up.xml->      <PropertySet name='Default' propertyType='logical' >
   l_propertySetDefinitionId := getPropertySetId(l_classDefinitionId,PROPERTYTYPE_LOGICAL, 'LOGICAL:DEFAULT');
--CMPRuntimeLocation.up.xml->     <PropertyGroup definitionKey="LocationInfo" name="LocationInfo">
   l_propertyGroupDefinitionId := getPropertyGroupId(l_propertySetDefinitionId, 'LocationInfo');
--CMPRuntimeLocation.up.xml->     <Attribute
--CMPRuntimeLocation.up.xml->           name='CONNECTION_TYPE' dataType='String'
--CMPRuntimeLocation.up.xml->           choices='HOST:PORT:SERVICE,SQL*NET Connection'
--CMPRuntimeLocation.up.xml->           initValue='HOST:PORT:SERVICE'
--CMPRuntimeLocation.up.xml->           definitionKey='CMPLocation_J2EE_ConnectionType'
--CMPRuntimeLocation.up.xml->           description='Type of the connection.'
--CMPRuntimeLocation.up.xml->           exportControl='public'
--CMPRuntimeLocation.up.xml->           isPassword='false'
 --CMPRuntimeLocation.up.xml->          isTranslatable='false'
 --CMPRuntimeLocation.up.xml->          isReadOnly='true'
--CMPRuntimeLocation.up.xml->           isHidden='false'
--CMPRuntimeLocation.up.xml->        />
  l_propertyDefinitionId := getPropertyDefinitionId(l_propertySetDefinitionId,l_propertyGroupDefinitionId, 'CMPLocation_J2EE_ConnectionType');
  updateTranslatable(1,l_propertyDefinitionId);
  -- =======================================================
  END; -- Inner block for utility function/variable usage
  -- =======================================================    
  
-- ##########################################################################
END; -- Outer block for utility functions/variables
-- ##########################################################################
/