UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_3

    CREATE function dbo.UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_3
    (
      @GIVENANONYMOUSLY bit,
        @CONSTITUENTID uniqueidentifier,
        @AMOUNT money,
        @DATE datetime,
        @SOURCERECURRINGGIFTREVENUEID uniqueidentifier,
        @APPLICATIONTYPE tinyint = null,
        @APPLICATIONID uniqueidentifier = null,
      @BATCHROWID uniqueidentifier = null
    )
    returns @RECOGNITIONS table
        (
            CONSTITUENTID uniqueidentifier,
            NAME nvarchar(500),
            AMOUNT money,
            REVENUERECOGNITIONTYPECODEID uniqueidentifier
        )
        as
        begin
            declare @DEFAULTANONYMOUSRECOGNITION bit = 0;

            if @APPLICATIONTYPE is null
                set @APPLICATIONTYPE = 0;

            --Select the value for default anonymous recognition setting

            select @DEFAULTANONYMOUSRECOGNITION = DEFAULTANONYMOUSRECOGNITION
            from dbo.RECOGNITIONDEFAULT

            declare @R table
            (
                CONSTITUENTID uniqueidentifier,
                AMOUNT money,
                REVENUERECOGNITIONTYPECODEID uniqueidentifier
            );

            if @APPLICATIONTYPE = 3 --Matching gift claim

            begin

              if @GIVENANONYMOUSLY = 0 or @DEFAULTANONYMOUSRECOGNITION = 1
              begin

                  declare @AUTOADDMGPAYMENTCREDITTODONOR bit;
                  declare @AUTOADDMGPAYMENTCREDITTOMATCHINGORG bit;
                  declare @MGPAYMENTAPPLYRECOGNITIONTYPECODE bit;
                  declare @PAYMENTDEFAULTCREDITTYPEID uniqueidentifier;
                  declare @MATCHINGORGPAYMENTREVENUERECOGNITIONTYPECODEID uniqueidentifier;

                  select
                      @AUTOADDMGPAYMENTCREDITTODONOR = ADDRECOGNITIONCREDITSONMGPAYMENTAPPLY,
                      @AUTOADDMGPAYMENTCREDITTOMATCHINGORG = ADDRECOGNITIONCREDITSTOMATCHINGORGONMGPAYMENTAPPLY,
                      @MGPAYMENTAPPLYRECOGNITIONTYPECODE = MGPAYMENTAPPLYRECOGNITIONTYPECODE,
                      @PAYMENTDEFAULTCREDITTYPEID =    PAYMENTREVENUERECOGNITIONTYPECODEID,
                      @MATCHINGORGPAYMENTREVENUERECOGNITIONTYPECODEID = MATCHINGORGPAYMENTREVENUERECOGNITIONTYPECODEID
                  from
                      dbo.MATCHINGGIFTPREFERENCEINFO;

                  if @AUTOADDMGPAYMENTCREDITTODONOR = 1
                  begin
                      declare @ORIGINALDONORID uniqueidentifier;
                      declare @ORIGINALGIFTID uniqueidentifier;

                      select
                          @ORIGINALDONORID = FINANCIALTRANSACTION.CONSTITUENTID,
                          @ORIGINALGIFTID = FINANCIALTRANSACTION.ID
                      from
                          dbo.FINANCIALTRANSACTION
                          inner join REVENUEMATCHINGGIFT on REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = FINANCIALTRANSACTION.ID
                          inner join REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                      where
                          REVENUEMATCHINGGIFT.ID = @APPLICATIONID

                      if @ORIGINALDONORID is not null
                      begin
                          if @MGPAYMENTAPPLYRECOGNITIONTYPECODE = 0 --Create recognition credit for donor only when applying a payment to a matching gift claim

                          begin
                              insert into @R  
                              select
                                  @ORIGINALDONORID as [CONSTITUENTID],
                                  @AMOUNT as [AMOUNT],
                                  @PAYMENTDEFAULTCREDITTYPEID as [REVENUERECOGNITIONTYPECODEID];
                          end
                          else if @MGPAYMENTAPPLYRECOGNITIONTYPECODE = 1 --Create recognition credits based on recognition from original revenue when applying a payment to a matching gift claim

                          begin
                              declare @GIFTSPLITS xml;
                              set @GIFTSPLITS = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@ORIGINALGIFTID);
                              insert into @R
                              select
                                  REVENUERECOGNITION.CONSTITUENTID as [CONSTITUENTID],
                                  sum(coalesce((@AMOUNT * REVENUERECOGNITION.AMOUNT / Nullif(FINANCIALTRANSACTION.BASEAMOUNT, 0.00)),0.00)) as [AMOUNT],
                              REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID as [REVENUERECOGNITIONTYPECODEID]
                              from
                                  dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@GIFTSPLITS) as SPT
                                  inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = SPT.ID
                  inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID=SPT.ID
                  inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID=FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                group by FINANCIALTRANSACTION.ID,
                          REVENUERECOGNITION.CONSTITUENTID,
                          REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID,
                  FINANCIALTRANSACTION.BASEAMOUNT;    
              end
                      end
                  end

                  if @AUTOADDMGPAYMENTCREDITTOMATCHINGORG = 1 --Add recognition for matching organization when applying a payment to a matching gift claim

                  begin

                      if exists (select 1
                              from dbo.INSTALLMENTSPLITPAYMENT 
                                  inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                  inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                  inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                  inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID and REVENUERECOGNITION.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
                              where
                                  INSTALLMENTSPLITPAYMENT.PLEDGEID = @APPLICATIONID)
                      begin
                        insert into @R
                        select
                            @CONSTITUENTID,
                            @AMOUNT as [AMOUNT],
                            @MATCHINGORGPAYMENTREVENUERECOGNITIONTYPECODEID as [REVENUERECOGNITIONTYPECODEID]
                        from 
                            dbo.INSTALLMENTSPLITPAYMENT 
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID and REVENUERECOGNITION.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
                        where
                            INSTALLMENTSPLITPAYMENT.PLEDGEID = @APPLICATIONID
                            group by FINANCIALTRANSACTION.CONSTITUENTID

                      end
                      else
                      begin
                          insert into @R
                          select
                              @CONSTITUENTID,
                              (@AMOUNT * coalesce(sum(RR.AMOUNT), sum(LT.ORGAMOUNT)) / sum(LT.ORGAMOUNT)) as [AMOUNT],
                              @MATCHINGORGPAYMENTREVENUERECOGNITIONTYPECODEID as [REVENUERECOGNITIONTYPECODEID]
                          from FINANCIALTRANSACTION FT 
                              inner join FINANCIALTRANSACTIONLINEITEM LT on LT.FINANCIALTRANSACTIONID = FT.ID
                              inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FT.ID
                              left join REVENUERECOGNITION RR on RR.REVENUESPLITID = LT.ID and RR.CONSTITUENTID = FT.CONSTITUENTID
                          where FT.ID = @APPLICATIONID
                      end

                  end

                  insert into @RECOGNITIONS 
                  select
                      [RESULTS].CONSTITUENTID,
                      NF.NAME,
                      [RESULTS].AMOUNT,
                      [RESULTS].REVENUERECOGNITIONTYPECODEID
                  from
                      @R as [RESULTS]
                      cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME([RESULTS].CONSTITUENTID) NF
                  order by
                      NF.NAME;
              end
            end
            else if @APPLICATIONTYPE <> 2             -- @APPLICATIONTYPE = 2 is for Unapplied Matching gift claim. For Unapplied matching gift recognition credits should not create.

                    Or @APPLICATIONID is not null     -- @APPLICATIONTYPE = 2 is for Recurring gifts too but revenue stream for recurring gifts have @APPLICATIONID.

            begin

                -- Default source recurring gift ID if not provided

                if @SOURCERECURRINGGIFTREVENUEID is null and 
                    @APPLICATIONID is not null and
                    @APPLICATIONTYPE = 2
                begin
                    set @SOURCERECURRINGGIFTREVENUEID = @APPLICATIONID
                end

                if @GIVENANONYMOUSLY = 0 or @DEFAULTANONYMOUSRECOGNITION = 1
                begin
                    if exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID) and @SOURCERECURRINGGIFTREVENUEID is null
                    begin
                        insert into @R(CONSTITUENTID, REVENUERECOGNITIONTYPECODEID, AMOUNT)
                            select CONSTITUENTID, REVENUERECOGNITIONTYPECODEID, AMOUNT
                            from dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(@GIVENANONYMOUSLY, @CONSTITUENTID, @AMOUNT, @DATE, null);

            -- BUG#584814

            -- Handle recognition credits for spouse added through 'Edit Constituent'

            declare @ISINDIVIDUAL bit = (select (case when ISORGANIZATION = 1 or ISGROUP = 1 then 0 else 1 end)
                                          from dbo.CONSTITUENT
                                          where ID = @CONSTITUENTID);

            if @ISINDIVIDUAL = 1 and @BATCHROWID is not null
            begin
              declare @ISUPDATED bit = 0,
                        @NEWSPOUSEID uniqueidentifier,
                        @NEWSPOUSENAME nvarchar(50),
                        @NEWRELATIONSHIPTYPECODEID uniqueidentifier;

              -- check if the constituent has been updated or not

              select @ISUPDATED = 1,
                         @NEWSPOUSEID = SPOUSE_ID,
                         @NEWSPOUSENAME = (case SPOUSE_FIRSTNAME when '' then '' else SPOUSE_FIRSTNAME end) + 
                                                 (case SPOUSE_MIDDLENAME when '' then '' else LEFT(SPOUSE_MIDDLENAME,1) + '.' end) + 
                                                (case SPOUSE_LASTNAME when '' then '' else SPOUSE_LASTNAME end),
                       @NEWRELATIONSHIPTYPECODEID = SPOUSE_RELATIONSHIPTYPECODEID
              from BATCHCONSTITUENTUPDATE
              where ID = @BATCHROWID
                and PRIMARYRECORDID = @CONSTITUENTID;

              if @ISUPDATED = 1
              begin
                declare @OLDSPOUSEID uniqueidentifier;      

                -- check if the constituent already has a spouse

                  select @OLDSPOUSEID = RECIPROCALCONSTITUENTID
                  from RELATIONSHIP
                  where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                    and ISSPOUSE = 1;

                -- if a spouse has been added, then the credits need to be added

                  if (@NEWSPOUSEID is null and len(coalesce(@NEWSPOUSENAME, '')) > 0) or (@NEWSPOUSEID is not null and (@OLDSPOUSEID is null or @NEWSPOUSEID <> @OLDSPOUSEID))
                  begin
                      -- add recognition-credits using 'default relationship recognition options' for the added spouse

                      declare @MATCHFACTOR decimal,
                                  @REVENUERECOGNITIONTYPECODEID uniqueidentifier;
                      select @MATCHFACTOR = MATCHFACTOR,
                               @REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
                      from RECOGNITIONRELATIONSHIPDEFAULT
                      where RELATIONSHIPTYPECODEID = @NEWRELATIONSHIPTYPECODEID
                      and CONSTITUENTTYPECODE = 0;    -- individual


                  if @NEWSPOUSEID is null
                    set @NEWSPOUSEID = @BATCHROWID;

                      if @MATCHFACTOR is not null and @MATCHFACTOR > 0
                      begin
                          insert into @RECOGNITIONS (CONSTITUENTID, NAME, AMOUNT, REVENUERECOGNITIONTYPECODEID)
                          values (@NEWSPOUSEID, @NEWSPOUSENAME, (@MATCHFACTOR * 0.01 * @AMOUNT), @REVENUERECOGNITIONTYPECODEID)
                      end
                  end  
              end
            end
                    end    
                    else
                    begin
                        --Basically, reimplementing UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS for imaginary batch constituents

                        -- Recurring gift payments copy constituents from recurring gift recognitions

                        -- and calculate amounts by getting the ratio of the payment amount to the recurring gift amount

                        -- and then multiplying by the previous recognition amount for that constituent.

                        if @SOURCERECURRINGGIFTREVENUEID is not null
                        begin
                            -- Calculate ratio if this is a recurring gift payment


                            -- Default ratio to 1 in case it isn't calculated

                            declare @RATIO decimal(20, 10)
                            select @RATIO = 1.0

                            declare @RECURRINGGIFTAMOUNT money                    
                            select @RECURRINGGIFTAMOUNT = AMOUNT from dbo.REVENUE where ID = @SOURCERECURRINGGIFTREVENUEID

                            -- If the recurring giftamount is 0, then the payment amount will be used since 

                            -- @RATIO will remain 1.

                            if @RECURRINGGIFTAMOUNT <> 0
                            begin
                                -- Cast @AMOUNT to a decimal(20, 10) so @RATIO has its full precision.

                                select @RATIO = cast(@AMOUNT as decimal(20, 10)) / @RECURRINGGIFTAMOUNT
                            end

                            insert into @R
                            (
                                CONSTITUENTID,
                                REVENUERECOGNITIONTYPECODEID,
                                AMOUNT
                            )
                            (
                                -- Create recognitions for constituents that were on the recurring gift recognitions.

                                select 
                                    RR.CONSTITUENTID,
                                    RR.REVENUERECOGNITIONTYPECODEID,
                                    AMOUNT = sum(RR.AMOUNT) * @RATIO
                                from dbo.REVENUERECOGNITION RR
                                inner join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RR.REVENUESPLITID 
                                where FTLI.FINANCIALTRANSACTIONID = @SOURCERECURRINGGIFTREVENUEID
                                group by RR.CONSTITUENTID, RR.REVENUERECOGNITIONTYPECODEID
                            )
                        end
                        else
                        begin
                            declare @DATEEARLIESTTIME datetime
                            set @DATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@DATE)

                            insert into @R
                            (
                                CONSTITUENTID,
                                REVENUERECOGNITIONTYPECODEID,
                                AMOUNT
                            )
                            select 
                                RECIPIENTCONSTITUENTID, 
                                REVENUERECOGNITIONTYPECODEID,
                                (MATCHFACTOR * 0.01 * @AMOUNT)
                            from 
                            dbo.UFN_BATCHCONSTITUENT_GETRECOGNITIONDEFAULTSBYSOURCE(@CONSTITUENTID, 1, 1) 
                            where 
                                (STARTDATE is null or STARTDATE <= @DATEEARLIESTTIME) and 
                                (ENDDATE is null or ENDDATE >= @DATEEARLIESTTIME)
                        end
                    end
                end

          insert into @RECOGNITIONS 
                  select
                      [RESULTS].CONSTITUENTID,
                      NF.NAME,
                      [RESULTS].AMOUNT,
                      [RESULTS].REVENUERECOGNITIONTYPECODEID
                  from
                      @R as [RESULTS]
                      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME([RESULTS].CONSTITUENTID) NF
                      left join dbo.BATCHREVENUECONSTITUENT on [RESULTS].CONSTITUENTID = BATCHREVENUECONSTITUENT.ID and BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID is null
              order by
                      coalesce(NF.NAME, BATCHREVENUECONSTITUENT.NAME);
      end

        return;
        end