USP_REVENUE_DISQUALIFIED_GIFT_AID_ATTRIBUTE_2

            CREATE procedure USP_REVENUE_DISQUALIFIED_GIFT_AID_ATTRIBUTE_2
      (
      @REVENUEID as uniqueidentifier
      )

      as
      begin

        if exists (select id from GIFTAIDDISQUALIFIEDBYATTRIBUTE)
          begin

            declare @revenueIDs xml;
            declare @sql nvarchar(max);
            declare @parameters nvarchar(500);
                declare @tempTable table([ID] uniqueidentifier);
            declare @valueColumnName nvarchar(128);
            declare @attributeCategoryID uniqueidentifier;
            declare @dataType smallint;
            declare @giftAidValue nvarchar(max);
            declare @attributeCategoryIDString nvarchar(36);
            declare @onlyallowoneperrecord bit;

            declare cur_GiftAidAttributes cursor local fast_forward for
                select  ATTRIBUTECATEGORYID, 
                case when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 0
                        then TEXTVALUE
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 1
                        then convert(nvarchar(100),NUMBERVALUE)
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 2
                        then convert(nvarchar(100),DATEVALUE, 121)
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 3
                        then Convert(nvarchar(100),CURRENCYVALUE)
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 4
                        then convert(nvarchar(100),BOOLEANVALUE)
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 5
                        then convert(nvarchar(100),CODETABLEVALUE)
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 6
                        then Convert(nvarchar(max),CONSTITUENTID)
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 7
                        then FUZZYDATEVALUE 
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 8
                        then HOURMINUTEVALUE
                    when GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE = 9
                        then MEMOVALUE
                end as GIFTAIDVALUE,
                GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE,
                VALUECOLUMNNAME,
              ONLYALLOWONEPERRECORD
                from GIFTAIDDISQUALIFIEDBYATTRIBUTE
                inner join ATTRIBUTECATEGORY 
                    on ATTRIBUTECATEGORY.ID = GIFTAIDDISQUALIFIEDBYATTRIBUTE.ATTRIBUTECATEGORYID
                inner join ATTRIBUTERECORDTYPE 
                    on ATTRIBUTERECORDTYPE.ID = ATTRIBUTECATEGORY.ATTRIBUTERECORDTYPEID
                inner join RECORDTYPE on RECORDTYPE.ID = ATTRIBUTERECORDTYPE.RECORDTYPEID
                where RECORDTYPE.NAME = 'REVENUE'
                order by ATTRIBUTECATEGORY.ID;

                open cur_GiftAidAttributes;
                fetch next from cur_GiftAidAttributes into @attributeCategoryID,@giftAidValue,@dataType,@valueColumnName,@onlyallowoneperrecord;

                while @@FETCH_STATUS = 0
                    begin
                      if @dataType > -1
                          begin
                              set @attributeCategoryIDString = REPLACE(convert(nvarchar(36),@attributeCategoryID),'-','')
                      if @onlyallowoneperrecord = 1
                        begin
                                      set @sql = 'select id as revenueid from attribute' + @attributeCategoryIDString + ' where ID = @REVENUEID '
                        end
                      else
                        begin
                                      set @sql = 'select revenueid from attribute' + @attributeCategoryIDString + ' where REVENUEID = @REVENUEID '
                        end
                              set @sql = @sql + ' and attribute' + @attributeCategoryIDString + '.' + @valueColumnName + ' = ' + ''''+ @giftAidValue + ''''
                      end

                  set @parameters = N'@REVENUEID uniqueidentifier, @revenueIDs_out xml OUTPUT';

                  set @sql = 'set @revenueIDs_out = (select revenueid as ID from (' + @sql + ') as temptable for XML raw (' 
                    + '''' + 'ITEM' + '''' + '), type, elements, root(' + '''' + 'REVENUEID' + '''' 
                    + '), binary base64);'

                  exec sp_executesql @sql, @parameters, @REVENUEID, @revenueIDs_out = @revenueIDs output;

                  insert into @tempTable select T.c.value('(ID)[1]', 'uniqueidentifier') as [ID] from @revenueIDs.nodes('/REVENUEID/ITEM') T(c);
                  fetch next from cur_GiftAidAttributes into @attributeCategoryID,@giftAidValue,@dataType,@valueColumnName,@onlyallowoneperrecord;
                end

                 close cur_GiftAidAttributes;
                deallocate cur_GiftAidAttributes;

              if exists (select ID from @tempTable where ID = @REVENUEID)
                begin
                  update REVENUESPLITGIFTAID set ATTRIBUTES_STATUS = 0 --disqualified

                    where ID in
                      (select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
               end
              else
                begin
                  update REVENUESPLITGIFTAID set ATTRIBUTES_STATUS = 1 --qualified

                    where ID in
                      (select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                end
          end
        else
          begin
            update REVENUESPLITGIFTAID set ATTRIBUTES_STATUS = 1 --qualified

                where ID in
                (select id from REVENUESPLIT where REVENUEID = @REVENUEID)
          end
      end