CREATE PROCEDURE [dbo].[AL_CONFIGURATION_DEFAULT]  
   @tlo varchar(max),
   @configId int  OUTPUT,
   @configName varchar(max)  OUTPUT,
   @subType_attribute_name nvarchar(max)  OUTPUT,
   @subType_value nvarchar(max)  OUTPUT,
   @subTypeValue_id nvarchar(max)  OUTPUT,
   @subTypeValue_value nvarchar(max)  OUTPUT,
   @primaryKey binary(16),
   @uapmuser varchar(max),
   @schemeId int  OUTPUT,
   @versionNumber int OUTPUT,
   @isGlobal int OUTPUT,    
   @SQL_ERROR_CODE int  OUTPUT,
   @SQL_ERROR_DESCRIPTION varchar(max)  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

      
      /*
      *   error_code  NUMBER;
      *    error_message  long;
      */
      DECLARE
         @result varchar(2000),
         @joinName varchar(200),
         @leftSideTable varchar(30),
         @intermediateprimaryColumnname varchar(30),
         @leftSideField varchar(30),
         @tempPrimaryKey varbinary(32),
         @localCursor CURSOR,
         @record_count int,
         @operation char,
         @value_doesnot_exist$exception nvarchar(1000),
         @subtype_value_notpresent$exception nvarchar(1000),
         @primarykey_deleted$exception nvarchar(1000)

      BEGIN TRY

         SET @configId = NULL
         SET @configName = NULL
         SET @subType_attribute_name = NULL
         SET @subType_value = NULL
         SET @subTypeValue_id = NULL
         SET @subTypeValue_value = NULL
         SET @schemeId = NULL
         SET @SQL_ERROR_CODE = NULL
         SET @SQL_ERROR_DESCRIPTION = NULL
         SET @operation = 'A'
         SET @value_doesnot_exist$exception = -20000
         SET @subtype_value_notpresent$exception = -20015
         SET @primarykey_deleted$exception = -20016

        IF (@primaryKey is not null)
            SET @primaryKey = dbo.unhex(@primaryKey)            

        --defaulting the schemeid to null if it has been passed as -1 from UI.
        IF (@schemeId = -1)
          SET @schemeId = NULL
        
         
         /*
         *   ---PLEASE READ THIS   ERROR MESSAGE CONSTRUCTION FORMAT--------
         *   If the exception message has to be parsed in UI, the table name should be appended with table:(without space),
         *   column name with key: (without space) and value with value: (without space).  There can be multiple key value pairs, which go
         *   immediately one after the other. Eg: key:column1 value:abc... some text.. key:column2 value:bcd.  No data should be appended after xmlstring:
         *   A logic of checking if the primarykey passed is existing in DB
         *   just before doing any thing.
         */
        
      --yadra07 APM-18293 this procedure need not return the last updated configuration
      --yadra07 APM-13332  
      --  if(schemeid is null  /*and primaryKey is  null*/ ) then
      SELECT @record_count = count(1) from al_configuration_def acd where
      lower( acd.top_level_object) = lower(@tlo) and internal=0 -- and scheme_id=schemeId;
--  
--      --yadra07 APM-13332  
      IF (@record_count>0)   
        BEGIN
            select @configId = id, @configName = [name], @versionNumber = version_number, @schemeId = scheme_id, @isGlobal = [global] from al_configuration_def acd
            where  lower( acd.top_level_object) = lower(@tlo) and last_update_date
            in (select max(last_update_date) from  al_configuration_def where lower( top_level_object) = lower(@tlo)  and internal=0)
          
            IF (@schemeId is not null)    
                BEGIN   
                  select  distinct @subType_attribute_name = asd.attribute_name, @subTypeValue_id = asd.attribute_value
                  from al_configuration_def acd inner join al_scheme_def asd on acd.scheme_id=asd.scheme_id
                  where  acd.id = @configId
                  
                 EXECUTE AL_CONFIG_DEF_COVERSION_LOGIC  
                 @topLevelObject = @tlo,
                 @subType_attribute_name = @subType_attribute_name OUTPUT,
                 @subType_value = @subType_value OUTPUT,
                 @subTypeValue_id = @subTypeValue_id OUTPUT,
                 @subTypeValue_value = @subTypeValue_value OUTPUT,
                 @primaryKey = @primaryKey,
                 @uapmuser = @uapmuser
                END    
        END
      ELSE
        BEGIN
    
          EXECUTE AL_CONFIG_DEF_COVERSION_LOGIC  
          @topLevelObject = @tlo,
          @subType_attribute_name = @subType_attribute_name OUTPUT,
          @subType_value = @subType_value OUTPUT,
          @subTypeValue_id = @subTypeValue_id OUTPUT,
          @subTypeValue_value = @subTypeValue_value OUTPUT,
          @primaryKey = @primaryKey,
          @uapmuser = @uapmuser          

          print @tlo
          print @subType_attribute_name
          print @subTypeValue_id
          IF (@subType_attribute_name is not null and @subTypeValue_id is not null)
            BEGIN    
                select  @record_count = count(1)  
                from al_scheme_def asd
                where lower(asd.attribute_name) = lower(@subType_attribute_name)
                and asd.attribute_value = @subTypeValue_id and lower(class_name)=lower(@tlo)
                
                IF(@record_count =0 )
                  BEGIN
                      select @schemeId = max(scheme_id)+1 from al_scheme_def
                      IF(@schemeId is null)
                        SET @schemeId =1

                      insert into al_scheme_def(scheme_id, class_name, attribute_name, attribute_value, creation_user,
                      creation_date, last_update_user, last_update_date, version_number)
                      values(@schemeId, @tlo, @subType_attribute_name, @subTypeValue_id, @uapmuser, dbo.currentutcsecs(), @uapmuser, dbo.currentutcsecs(),0)
                  END
                ELSE            
                  select @schemeId = scheme_id from al_scheme_def  asd
                  where lower(asd.attribute_name) = lower(@subType_attribute_name)
                  and asd.attribute_value = @subTypeValue_id and lower(class_name)=lower(@tlo)
            END    
        END
  END TRY

  BEGIN CATCH
        
        DECLARE @ErrorNumber int
        DECLARE    @ErrorSeverity int
        DECLARE @ErrorState int
        DECLARE    @ErrorMessage nvarchar(max)
        DECLARE    @AppErrorCode int

        EXECUTE al_evaluate_error @ErrorNumber OUTPUT, @ErrorSeverity OUTPUT, @ErrorState OUTPUT, @ErrorMessage OUTPUT, @AppErrorCode OUTPUT
        
        -- Un-handled Exception
        IF (@AppErrorCode IS NULL)
            BEGIN

               SET @SQL_ERROR_CODE = @ErrorNumber
               SET @SQL_ERROR_DESCRIPTION = @ErrorMessage

            END
         ELSE IF (@AppErrorCode = @subtype_value_notpresent$exception)
            BEGIN
                --this is needed temporarily till the subtype deletion can be tied to existing configurations.
                SET @configId = null
                SET @configName = null
                SET @subType_attribute_name = null
                SET @subType_value=null
                SET @subTypeValue_id = null
                SET @subTypeValue_value = null

                EXECUTE AL_CONFIG_DEF_COVERSION_LOGIC  
                 @topLevelObject = @tlo,
                 @subType_attribute_name = @subType_attribute_name OUTPUT,
                 @subType_value = @subType_value OUTPUT,
                 @subTypeValue_id = @subTypeValue_id OUTPUT,
                 @subTypeValue_value = @subTypeValue_value OUTPUT,
                 @primaryKey = @primaryKey,
                 @uapmuser = @uapmuser
            END
         ELSE IF (@AppErrorCode = @value_doesnot_exist$exception) OR (@AppErrorCode = @primarykey_deleted$exception)
            BEGIN

               SET @SQL_ERROR_CODE = @AppErrorCode
               SET @SQL_ERROR_DESCRIPTION = @ErrorMessage

            END
         ELSE
            BEGIN

               SET @SQL_ERROR_CODE = -20500
               SET @SQL_ERROR_DESCRIPTION = @ErrorMessage

            END
    END CATCH
   END
GO