USP_CONSTITUENTSMARTFIELD_AGGREGATEREVENUEAMOUNTS

CREATE procedure dbo.USP_CONSTITUENTSMARTFIELD_AGGREGATEREVENUEAMOUNTS
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @TYPECODES xml,
    @DESIGNATIONS xml,                
    @INCLUDESOFTCREDITS bit = 0,
    @CALCULATIONTYPE tinyint = 0,
    @ASOF datetime,
    @CAMPAIGNS xml,
    @DATETYPECODE tinyint = null,
    @STARTDATERECENTDATEUNITCODE tinyint = null,
    @STARTDATERECENTDATEINTERVAL int = null,
    @CURRENCYID uniqueidentifier = null,
    @SMARTFIELDID uniqueidentifier = null
)
with execute as owner
as
/*
This smart field will return an aggregation of revenue values for ALL constituents in the database
*/
    set nocount on;    

    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    if @CURRENCYID is null
        set @CURRENCYID = @ORGANIZATIONCURRENCYID;

    select
        @DECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
        @ROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
    from dbo.[CURRENCY]
    where [CURRENCY].[ID] = @CURRENCYID;

    -- JLM 2/24/12 : Account for refunds : WI 160346

    declare @REFUNDSEXIST bit = 0;

    /*  
    Find all valid constituents based on the date that the smart field was last updated.
    */  
    declare @CONSTITUENTFILTERVIEW as nvarchar(50) = '#TMP_CONSTITUENTS_AGGREGATEREVENUEAMOUNTS'

    if @ASOF is null 
    begin  
        set @CONSTITUENTFILTERVIEW = 'dbo.CONSTITUENT'

        if exists(
            select 1
            from dbo.FINANCIALTRANSACTION 
            where FINANCIALTRANSACTION.TYPECODE = 23
            and CONSTITUENTID is not null
        ) set @REFUNDSEXIST = 1;
    end  
    else
    begin
        if object_id('tempdb..#TMP_CONSTITUENTS_AGGREGATEREVENUEAMOUNTS') is not null  
            drop table #TMP_CONSTITUENTS_AGGREGATEREVENUEAMOUNTS  

        create table #TMP_CONSTITUENTS_AGGREGATEREVENUEAMOUNTS  
        (  
            ID uniqueidentifier primary key
        )

        insert into #TMP_CONSTITUENTS_AGGREGATEREVENUEAMOUNTS(ID) 
            -- Selecting from FT instead of REVENUE to include refund transactions

            select CONSTITUENTID from dbo.FINANCIALTRANSACTION where FINANCIALTRANSACTION.DATECHANGED > @ASOF and CONSTITUENTID is not null
            union select CONSTITUENTID from dbo.FINANCIALTRANSACTIONAUDIT inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTIONAUDIT.CONSTITUENTID where FINANCIALTRANSACTIONAUDIT.AUDITDATE > @ASOF and CONSTITUENTID is not null
            union select CONSTITUENTID from dbo.FINANCIALTRANSACTION inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
      where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
            union select ID from dbo.CONSTITUENT where DATEADDED > @ASOF;

        /* 
        Pay the price for the 'exists' inner join here on #TMP_CONSTITUENTS_AGGREGATEREVENUEAMOUNTS
        here so that we only join on the refund table valued function if there were refunds for the 
        constituents in the working set.  
        */
        if exists(
            select 1 
            from dbo.FINANCIALTRANSACTION 
            inner join #TMP_CONSTITUENTS_AGGREGATEREVENUEAMOUNTS CONSTITUENT
                on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
            where FINANCIALTRANSACTION.TYPECODE = 23
        ) set @REFUNDSEXIST = 1;
    end  

    /*
    Calculate start and end dates
    */
    declare @CURRENTDATEEARLIESTTIME datetime;
    set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    declare @COMPUTEDSTARTDATE datetime;
    declare @COMPUTEDENDDATE datetime;

    if @DATETYPECODE = 1
    begin
        set @COMPUTEDSTARTDATE = 
            case @STARTDATERECENTDATEUNITCODE
                when 0 then dateadd(year, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                when 1 then dateadd(quarter, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                when 2 then dateadd(month, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                when 3 then dateadd(week, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                when 4 then dateadd(day, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
            end;
        set @COMPUTEDENDDATE = null;
    end
    else
    begin
        -- Use "Specific dates" behavior for @DATETYPECODE = 1 and @DATETYPECODE is null for backwards compatibility

        set @COMPUTEDSTARTDATE = @STARTDATE;
        set @COMPUTEDENDDATE = @ENDDATE;
    end

    set @COMPUTEDSTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@COMPUTEDSTARTDATE);
    set @COMPUTEDENDDATE = dbo.UFN_DATE_GETLATESTTIME(@COMPUTEDENDDATE);

    declare @sql as nvarchar(max);
    set @sql = '    
        declare @GIFTTYPESFILTER table(TRANSACTIONTYPECODE tinyint, APPLICATIONCODE tinyint primary key (TRANSACTIONTYPECODE, APPLICATIONCODE));
        declare @DESIGNATIONSFILTER table(ID uniqueidentifier primary key);
        declare @CAMPAIGNSFILTER table(ID uniqueidentifier primary key);

        /*@TYPECODES will not be null if this SP is driven from the UI.  Just in case, apply the default value.*/
        if @TYPECODES is null
        begin
            insert into @GIFTTYPESFILTER(TRANSACTIONTYPECODE,APPLICATIONCODE) values (0,0)
        end
        else
        begin
            insert into @GIFTTYPESFILTER(TRANSACTIONTYPECODE,APPLICATIONCODE) 
            select distinct
                case TYPECODEID 
                        when 1 then 1 /*Pledge*/
                        when 3 then 3 /*Matching gift claim*/
                        when 9 then 4 /*Planned gift*/
                        when 14 then 8 /*Donor challenge*/
                        when 17 then 9 /*Pending gift*/
                        when 7 then 7 /*Auction donation*/
                        else 0
                end as TRANSACTIONTYPECODE,
                case TYPECODEID 
                        when 4 then 2 /*Pledge payment*/
                        when 5 then 3 /*RG payment*/
                        when 6 then 1 /*Event registration fee*/
                        when 8 then 7 /*Matching gift payment*/
                        when 10 then 6 /*Planned gift payment*/
                        when 11 then 5 /*Membership*/
                        when 12 then 4 /*Other*/
                        when 13 then 9 /*Admission*/
                        when 15 then 13 /*Donor challenge payment*/
                        when 16 then 15 /* Event sponsorship payment */
                        when 18 then 17 /* Pending gift payment */
                        when 19 then 12 /* Auction purchase payment */
                        when 20 then 19 /* Membership Installment Plan */
                        else 0          /*Gift, pledge, planned gift, and matching gift claim*/
                end as APPLICATIONCODE
            from (
                select
                        T.c.value(''(TYPECODEID)[1]'',''tinyint'') as TYPECODEID
                from
                        @TYPECODES.nodes(''/TYPECODES/ITEM'') T(c)
                ) TYPECODES


            if exists (select 1 from @GIFTTYPESFILTER where TRANSACTIONTYPECODE = 1)
                insert into @GIFTTYPESFILTER(TRANSACTIONTYPECODE,APPLICATIONCODE)
                select 1,5 union all select 1,4 /*Add pledges applied to memberships and applied to other*/

            /* Allow for donations made from sales orders */
            if exists (select 1 from @GIFTTYPESFILTER where TRANSACTIONTYPECODE = 0)
                insert into @GIFTTYPESFILTER(TRANSACTIONTYPECODE, APPLICATIONCODE)
                select 5,0
        end 

        if @DESIGNATIONS is not null
        begin
            insert into @DESIGNATIONSFILTER(ID) select distinct
                T.c.value(''(ID)[1]'',''uniqueidentifier'')
                FROM   @DESIGNATIONS.nodes(''/DESIGNATIONS/ITEM'') T(c);
        end;

        if @CAMPAIGNS is not null
        begin
            insert into @CAMPAIGNSFILTER(ID) select distinct
                T.c.value(''(ID)[1]'',''uniqueidentifier'')
                FROM  @CAMPAIGNS.nodes(''/CAMPAIGNS/ITEM'') T(c);
        end;'

    set @sql = @sql + char(10) + 'with REVENUE_CTE (ID, CONSTITUENTID, AMOUNT, DATE, ROW) as
        (select    
            [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID],
            [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID],
            case when [REVENUESPLITAMOUNTINCURRENCY].[TRANSACTIONTYPECODE] in(1,3) then 
                sum([REVENUESPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY]) - coalesce(
                    (select 
                        sum([WRITEOFFSPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY]) as [AMOUNT]
                        from dbo.[UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [WRITEOFFSPLITAMOUNTINCURRENCY]
                        where [WRITEOFFSPLITAMOUNTINCURRENCY].[REVENUEID] = [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID]) ,0)
            else
                sum([REVENUESPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY]) 
                -- Subtract refunds if any exist

                ' if @REFUNDSEXIST = 1
                    set @sql = @sql + ' - coalesce(sum(REFUNDSPLIT.REFUNDTOTAL), 0) '
                set @sql = @sql + ' 
            end as [AMOUNT],
            [REVENUESPLITAMOUNTINCURRENCY].[DATE],'

    -- Include a row number if needed for calculating an extreme.

    if @CALCULATIONTYPE = 11
        set @sql = @sql + char(10) + 'ROW_NUMBER() over (partition by [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] order by [REVENUESPLITAMOUNTINCURRENCY].[DATE] asc, [REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED] asc) as ROW'
    else if @CALCULATIONTYPE = 12
        set @sql = @sql + char(10) + 'ROW_NUMBER() over (partition by [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] order by [REVENUESPLITAMOUNTINCURRENCY].[DATE] desc, [REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED] desc) as ROW'
    else
        set @sql = @sql + char(10) + '0 as ROW'

    set @sql = @sql + char(10) + '
        from dbo.[UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [REVENUESPLITAMOUNTINCURRENCY]
        inner join @GIFTTYPESFILTER as [GIFTTYPESFILTER] on [GIFTTYPESFILTER].[TRANSACTIONTYPECODE] = [REVENUESPLITAMOUNTINCURRENCY].[TRANSACTIONTYPECODE] and [GIFTTYPESFILTER].[APPLICATIONCODE] = [REVENUESPLITAMOUNTINCURRENCY].[APPLICATIONCODE]'
        -- Subtract refunds if any exist

        if @REFUNDSEXIST = 1
            set @sql = @sql + ' left outer join dbo.UFN_CREDIT_GETSPLITREFUNDAMOUNT_BULK() REFUNDSPLIT on REFUNDSPLIT.SOURCELINEITEMID = [REVENUESPLITAMOUNTINCURRENCY].ID '

    -- If the constituent filter is all constituents, then simply filtering out null is faster than joining

    if @ASOF is not null
        set @sql = @sql + char(10) + 'inner join ' + @CONSTITUENTFILTERVIEW + ' as [CONSTITUENTFILTER] on [CONSTITUENTFILTER].[ID] = [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID]'

  declare @whereClause as nvarchar(max);
  set @whereClause = ''

  if @ASOF is null
      set @whereClause = @whereClause + char(10) + '([REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] is not null)'

 if not @COMPUTEDSTARTDATE is null 
  begin
      if @whereClause <> ''
          set @whereClause = @whereClause + char(10) + 'and';

      set @whereClause = @whereClause + ' ([REVENUESPLITAMOUNTINCURRENCY].[DATE] >= @COMPUTEDSTARTDATE)';
  end

  if not @COMPUTEDENDDATE is null 
  begin
      if @whereClause <> ''
          set @whereClause = @whereClause + char(10) + 'and';

      set @whereClause = @whereClause + ' ([REVENUESPLITAMOUNTINCURRENCY].[DATE] <= @COMPUTEDENDDATE)';
  end

  if not @DESIGNATIONS is null     
  begin
      if @whereClause <> ''
          set @whereClause = @whereClause + char(10) + 'and';

      set @whereClause = @whereClause + ' (                    
                    (
                        (
                            [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID] in (select FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID 
              inner join @DESIGNATIONSFILTER as DESIGNATIONSFILTER on DESIGNATIONSFILTER.ID = REVENUESPLIT_EXT.DESIGNATIONID
                                                            where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1)
                        )
                    )
                )';
  end  

  if not @CAMPAIGNS is null 
    begin
      if @whereClause <> ''
          set @whereClause = @whereClause + char(10) + 'and';

      set @whereClause = @whereClause + ' (
                    (
                        (
                            [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID] in (
                                select [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID]
                                from dbo.[FINANCIALTRANSACTIONLINEITEM] 
                                inner join dbo.[REVENUESPLITCAMPAIGN] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLITCAMPAIGN].[REVENUESPLITID] 
                                inner join @CAMPAIGNSFILTER as [CAMPAIGNSFILTER] on [REVENUESPLITCAMPAIGN].[CAMPAIGNID] = [CAMPAIGNSFILTER].[ID]
                where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
                            )
                        )
                    )
                ) ';
    end

  if len(@whereClause) <> 0
    set @sql = @sql + ' where ' + CHAR(10) + @whereClause;

    set @sql = @sql + char(10) + 'group by [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID], [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID], [REVENUESPLITAMOUNTINCURRENCY].[TRANSACTIONTYPECODE], [REVENUESPLITAMOUNTINCURRENCY].[DATE], [REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED])';

    -- Backwards compatibility

    if @SMARTFIELDID is not null
        set @sql = @sql + ', SF_CTE (ID, VALUE) as ('

    /*
    Calculate totals based on calculation type.  
    */
    if @CALCULATIONTYPE = 0 --Max(amount)

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                coalesce(max(REVENUERECORDS.AMOUNT),0) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';
    else if @CALCULATIONTYPE = 1 --Min(amount)

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                coalesce(min(REVENUERECORDS.AMOUNT),0) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';
    else if @CALCULATIONTYPE = 2 --Sum(amount)

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                coalesce(sum(REVENUERECORDS.AMOUNT),0) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';
    else if @CALCULATIONTYPE = 3 --Avg(amount) 

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                coalesce(avg(REVENUERECORDS.AMOUNT),0) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';
    else if @CALCULATIONTYPE = 4 --Min(date)

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                min(REVENUERECORDS.DATE) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';
    else if @CALCULATIONTYPE = 5 --Max(date)

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                max(REVENUERECORDS.DATE) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';
    else if @CALCULATIONTYPE = 6 --Largest revenue date

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                REVENUERECORDS.DATE as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join (
                    select
                        ID,
                        CONSTITUENTID,
                        DATE,
                        ROW_NUMBER() over (partition by CONSTITUENTID order by AMOUNT desc, DATE desc) ROW
                    from REVENUE_CTE
                ) as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            where 
                REVENUERECORDS.ID is null or REVENUERECORDS.ROW = 1';
    else if @CALCULATIONTYPE = 7 --Giving years

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                coalesce(datediff(year,min(REVENUERECORDS.DATE), max(REVENUERECORDS.DATE)), 0) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';        
    else if @CALCULATIONTYPE = 8 --Distinct giving years

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                count(REVENUEDATES.YEAR) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join (
                    select distinct
                        REVENUERECORDS.CONSTITUENTID,
                        datepart(year, REVENUERECORDS.DATE) as YEAR
                    from REVENUE_CTE as REVENUERECORDS
                ) as REVENUEDATES on REVENUEDATES.CONSTITUENTID = CONSTITUENTFILTER.ID
            group by
                CONSTITUENTFILTER.ID';                
    else if @CALCULATIONTYPE = 9 --Is annual giver

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                case                             
                when 
                    coalesce(datediff(year,min(REVENUERECORDS.DATE), max(REVENUERECORDS.DATE)), 0) = (count(distinct datepart(year, REVENUERECORDS.DATE)) - 1)
                        and count(distinct datepart(year, REVENUERECORDS.DATE)) > 1
                then
                    cast(1 as bit)
                else
                    cast(0 as bit)
                end as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';
    else if @CALCULATIONTYPE = 10 --Count(revenue)

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                count(REVENUERECORDS.ID) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            group by
                CONSTITUENTFILTER.ID';            
    else if @CALCULATIONTYPE in (11, 12) --First revenue amount, Latest revenue amount

        set @sql = @sql + char(10) + '
            select 
                CONSTITUENTFILTER.ID,
                coalesce(REVENUERECORDS.AMOUNT, 0) as VALUE
            from 
                ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
                left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
            where 
                REVENUERECORDS.ID is null or REVENUERECORDS.ROW = 1';

    -- Backwards compatibility

    if @SMARTFIELDID is null      
        exec sp_executesql @sql, N'@ASOF datetime,
            @CURRENCYID uniqueidentifier,
            @ORGANIZATIONCURRENCYID uniqueidentifier,
            @DECIMALDIGITS tinyint,
            @ROUNDINGTYPECODE tinyint,
            @COMPUTEDSTARTDATE datetime,
            @COMPUTEDENDDATE datetime,
            @TYPECODES xml,
            @DESIGNATIONS xml,
            @CAMPAIGNS xml
            ',
            @ASOF,
            @CURRENCYID,
            @ORGANIZATIONCURRENCYID,
            @DECIMALDIGITS,
            @ROUNDINGTYPECODE,
            @COMPUTEDSTARTDATE,
            @COMPUTEDENDDATE,
            @TYPECODES,
            @DESIGNATIONS,
            @CAMPAIGNS;
    else
    begin
        declare @TARGET nvarchar(100)
        declare @TARGET_FIELD nvarchar(128)
        select
            @TARGET = TABLECATALOG.TABLENAME,
            @TARGET_FIELD = SMARTFIELD.VALUECOLUMNNAME
        from dbo.SMARTFIELD
            left join dbo.TABLECATALOG on SMARTFIELD.TABLECATALOGID = TABLECATALOG.ID
        where SMARTFIELD.ID = @SMARTFIELDID

        set @sql = @sql + ')
            merge dbo.' + @TARGET + ' as target
            using SF_CTE as source
            on target.ID = source.ID
            when matched then
                update set ' + @TARGET_FIELD + ' = source.VALUE
            when not matched by target then
                insert (ID, ' + @TARGET_FIELD + ')
                values (source.ID, source.VALUE)
            option (recompile);';

        exec sp_executesql @sql, N'@ASOF datetime,
            @CURRENCYID uniqueidentifier,
            @ORGANIZATIONCURRENCYID uniqueidentifier,
            @DECIMALDIGITS tinyint,
            @ROUNDINGTYPECODE tinyint,
            @COMPUTEDSTARTDATE datetime,
            @COMPUTEDENDDATE datetime,
            @TYPECODES xml,
            @DESIGNATIONS xml,
            @CAMPAIGNS xml
            ',
            @ASOF,
            @CURRENCYID,
            @ORGANIZATIONCURRENCYID,
            @DECIMALDIGITS,
            @ROUNDINGTYPECODE,
            @COMPUTEDSTARTDATE,
            @COMPUTEDENDDATE,
            @TYPECODES,
            @DESIGNATIONS,
            @CAMPAIGNS;

        select @@ROWCOUNT;
    end