CREATE PROCEDURE [dbo].[AL_INTERNAL_CONFIGURATION]  
   @topLevelObject nvarchar(max),
   @uapmuser nvarchar(max),
   @config_id int  OUTPUT
AS    
   /*
   *   Generated by SQL Server Migration Assistant for Oracle.
   *   Contact ora2sql@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
   */
   BEGIN

      SET @config_id = NULL

      DECLARE
         @subType_attribute_name nvarchar(255),
         @subType_value nvarchar(255),
         @subTypeValue_id nvarchar(255),
         @subTypeValue_value nvarchar(255),
         @record_count int,
         @return_value varchar(200)/*for getting the config ID creation xml.*/,
         @operation char(1),
         @configName nvarchar(255)

      SELECT @record_count = count_big(1)
      FROM dbo.ARG_ATTRIBUTE_DEF
      WHERE ARG_ATTRIBUTE_DEF.EXTENSIBLE = 1 AND lower(ARG_ATTRIBUTE_DEF.CLASS_NAME) = lower(@topLevelObject)     
      /*
      *   If the top level object has any subtypes then it makes sense
      *   to check further for creating internal configuration
      *   changed from 1 to 0 to support simple field extensions.
      */
      IF (@record_count > 0)
         BEGIN            
            /*
            *   This will be null if the user has selected across all extension types
            *   if the subtype value is provided there is no need of internal configuration creation.
            */
            SELECT @record_count = count_big(1)
            FROM dbo.AL_CONFIGURATION_DEF
            WHERE lower(AL_CONFIGURATION_DEF.TOP_LEVEL_OBJECT) = lower(@topLevelObject) AND AL_CONFIGURATION_DEF.SCHEME_ID IS NULL

            /*if a configuration exists already then find it and update it as internal config*/
            IF (@record_count = 1)
               BEGIN

                  SELECT @config_id = AL_CONFIGURATION_DEF.ID
                  FROM dbo.AL_CONFIGURATION_DEF
                  WHERE lower(AL_CONFIGURATION_DEF.TOP_LEVEL_OBJECT) = lower(@topLevelObject) AND AL_CONFIGURATION_DEF.SCHEME_ID IS NULL

                  UPDATE dbo.AL_CONFIGURATION_DEF
                     SET
                        INTERNAL = 1
                  WHERE AL_CONFIGURATION_DEF.ID = @config_id

                  UPDATE dbo.ARG_CLASS_DEF
                     SET
                        CONFIG_ID = ARG_CLASS_DEF.CONFIG_ID
                  WHERE lower(ARG_CLASS_DEF.CLASS_NAME) = lower(@topLevelObject)

               END
            ELSE
               BEGIN
                  IF (@record_count = 0)
                     BEGIN
                        SET @operation = 'A'
                        SET @configName = N'Internal '+ @topLevelObject
                        EXECUTE dbo.AL_BATCH_SAVE_CONFIG_DEF
                           @OPERATION = @operation  OUTPUT,
                           @CONFIGID = @config_id  OUTPUT,
                           @CONFIGNAME = @configName,
                           @CONFIGGLOBAL = 1,
                           @TOPLEVELOBJECT = @topLevelObject,
                           @SUBTYPE_ATTRIBUTE_NAME = @subType_attribute_name  OUTPUT,
                           @SUBTYPE_VALUE = @subType_value  OUTPUT,
                           @SUBTYPEVALUE_ID = @subTypeValue_id  OUTPUT,
                           @SUBTYPEVALUE_VALUE = @subTypeValue_value  OUTPUT,
                           @PRIMARYKEY = NULL/*primarykey*/,
                           @UAPMUSER = @uapmuser,
                           @RESULT = @return_value  OUTPUT,
                           @FORDEFAULTCONFIG = 0

                        UPDATE dbo.AL_CONFIGURATION_DEF
                           SET
                              INTERNAL = 1
                        WHERE AL_CONFIGURATION_DEF.ID = @config_id

                        UPDATE dbo.ARG_CLASS_DEF
                           SET
                              CONFIG_ID = ARG_CLASS_DEF.CONFIG_ID
                        WHERE lower(ARG_CLASS_DEF.CLASS_NAME) = lower(@topLevelObject)

                     END
               END

            SELECT @record_count = count_big(1)
            FROM dbo.AL_SCHEME_DEF
            WHERE
               lower(AL_SCHEME_DEF.CLASS_NAME) = lower(@topLevelObject) AND
               lower(AL_SCHEME_DEF.ATTRIBUTE_NAME) = lower(@subType_attribute_name) AND
               lower(AL_SCHEME_DEF.ATTRIBUTE_VALUE) = lower(@subTypeValue_id)
         END

   END
GO