USP_DATAFORMTEMPLATE_ADD_DONORCHALLENGEENCUMBEREDFUND

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DONORCHALLENGEENCUMBEREDFUND
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier,
  @DONORCHALLENGEID uniqueidentifier,
  @REVENUEID uniqueidentifier
)
as
begin
  set nocount on;

  begin try

    declare @CURRENTDATE datetime = getdate();

    if @CHANGEAGENTID is null  
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CONTEXTCACHE varbinary(128) = CONTEXT_INFO();
      set CONTEXT_INFO @CHANGEAGENTID;

    --Remove any removed funds for this revenue associated with the donor challenge

    delete DCE from
      dbo.DONORCHALLENGEENCUMBERED DCE
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = DCE.REVENUESPLITID
    where
      DCE.DONORCHALLENGEID = @DONORCHALLENGEID
      and DCE.STATUSTYPECODE = 2
      and REVENUESPLIT.REVENUEID = @REVENUEID

    --Delete Encumbered pledge payments if the Pledge is being added

    delete DCE from
      dbo.DONORCHALLENGEENCUMBERED DCE
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = DCE.REVENUESPLITID
    inner join
      dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
    inner join
      dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
    inner join
      dbo.REVENUE on INSTALLMENTSPLIT.PLEDGEID = REVENUE.ID
    inner join
      dbo.REVENUESPLIT PLEDGESPLIT on REVENUE.ID = PLEDGESPLIT.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = PLEDGESPLIT.DESIGNATIONID
    where
      REVENUE.ID = @REVENUEID
      and REVENUE.TRANSACTIONTYPECODE = 1 --Pledge

      and DCE.DONORCHALLENGEID = @DONORCHALLENGEID
      and DCE.STATUSTYPECODE = 0 --Pledge payment is encumbered

      and REVENUESPLIT.APPLICATIONCODE = 2 --Pledge payment


    if not @CONTEXTCACHE is null
      set CONTEXT_INFO @CONTEXTCACHE;

    declare @TOTALFUNDS money;
    declare @MATCHINGFACTOR decimal(5,2);
    declare @MATCHTYPECODE tinyint;
    declare @REVENUERECOGNITIONTYPECODEID uniqueidentifier;
    declare @TYPECODE tinyint;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @BASECURRENCYDECIMALDIGITS tinyint;
    declare @BASECURRENCYROUNDINGTYPECODE tinyint;
    declare @MAXMATCHPERGIFT money;

    select
      @TOTALFUNDS = case when TYPECODE = 1 then MATCHTHRESHOLD else TOTALFUNDS end,
      @MATCHINGFACTOR = case when TYPECODE = 1 then 1 else MATCHINGFACTOR end,
      @MATCHTYPECODE = MATCHTYPECODE,
      @REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID,
      @TYPECODE = TYPECODE,
      @BASECURRENCYID = BASECURRENCYID,
      @MAXMATCHPERGIFT = DONORCHALLENGE.MAXMATCHPERGIFT
    from
      dbo.DONORCHALLENGE
    where
      ID = @DONORCHALLENGEID;

    select
      @BASECURRENCYDECIMALDIGITS = DECIMALDIGITS,
      @BASECURRENCYROUNDINGTYPECODE = ROUNDINGTYPECODE
    from
      dbo.CURRENCY
    where
      ID = @BASECURRENCYID;

    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    declare @ENCUMBERED money;
    select @ENCUMBERED = coalesce(dbo.UFN_DONORCHALLENGE_ENCUMBEREDAMOUNT(@DONORCHALLENGEID), 0) + coalesce(dbo.UFN_DONORCHALLENGE_MATCHEDAMOUNT(@DONORCHALLENGEID), 0);

    declare @REVENUESPLITID uniqueidentifier;
    declare @AMOUNT money = 0;
    declare @DESIGNATIONID uniqueidentifier;

    declare @DATEADDED datetime;
    select @DATEADDED = REVENUE.DATEADDED from dbo.REVENUE where REVENUE.ID = @REVENUEID;

    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);
    declare @ORGANIZATIONAMOUNT money;

    declare AVAILABLEREVENUE cursor local fast_forward for  
      select
        REVENUESPLIT.ID,
        case 
          when @MATCHTYPECODE = 1 and REVENUE.TRANSACTIONTYPECODE <> 1 then 
            case 
              when REVENUE.AMOUNT = 0 then 
                0 
                else dbo.UFN_CURRENCY_ROUND(REVENUE.RECEIPTAMOUNT/cast(REVENUE.TRANSACTIONAMOUNT As decimal(20,10))
                    * case
                        when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
                            then REVENUESPLIT.TRANSACTIONAMOUNT
                        else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
                    end, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE)
            end
          else 
            case
        when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
          then REVENUESPLIT.TRANSACTIONAMOUNT
        else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
      end
        end,
        DESIGNATIONID
      from
        dbo.REVENUESPLIT
      inner join
        dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
      where
        REVENUEID = @REVENUEID

      open AVAILABLEREVENUE
      fetch next from AVAILABLEREVENUE into @REVENUESPLITID, @AMOUNT, @DESIGNATIONID;

    while (@@FETCH_STATUS = 0 and @ENCUMBERED < @TOTALFUNDS)
    begin
      set @AMOUNT = dbo.UFN_CURRENCY_ROUND(@AMOUNT * @MATCHINGFACTOR, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE);

      if @MAXMATCHPERGIFT > 0 and @AMOUNT > @MAXMATCHPERGIFT
        set @AMOUNT = @MAXMATCHPERGIFT;

      if @AMOUNT > (@TOTALFUNDS - @ENCUMBERED)
        set @AMOUNT = (@TOTALFUNDS - @ENCUMBERED);      

        set @ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @ORGANIZATIONEXCHANGERATEID);

          insert into dbo.DONORCHALLENGEENCUMBERED
          (
            ID,
            DONORCHALLENGEID,
            REVENUESPLITID,
            DESIGNATIONID,
            METHODTYPECODE,
            AMOUNT,
            REVENUERECOGNITIONTYPECODEID,
            BASECURRENCYID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
      select
            newid(),
            @DONORCHALLENGEID,
            REVENUESPLIT.ID,
            case when @TYPECODE = 1 then null else coalesce(DCDM.MATCHINGDESIGNATIONID, REVENUESPLIT.DESIGNATIONID, DCMM.MATCHINGDESIGNATIONID) end,
            1,
            @AMOUNT,
            @REVENUERECOGNITIONTYPECODEID,
            @BASECURRENCYID,
            @ORGANIZATIONAMOUNT,
            @ORGANIZATIONEXCHANGERATEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          from
            dbo.REVENUESPLIT
          left join
            dbo.DONORCHALLENGEDESIGNATIONMAP DCDM on DCDM.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID and DCDM.DONORCHALLENGEID = @DONORCHALLENGEID
          left join
            dbo.MEMBERSHIPTRANSACTION MT on MT.REVENUESPLITID = REVENUESPLIT.ID
          left join
            dbo.DONORCHALLENGEMEMBERSHIPLEVELMAP DCMM on DCMM.MEMBERSHIPLEVELID = MT.MEMBERSHIPLEVELID and DCMM.DONORCHALLENGEID = @DONORCHALLENGEID
          left join
            dbo.DONORCHALLENGEENCUMBERED DCE on DCE.REVENUESPLITID = REVENUESPLIT.ID and DCE.DONORCHALLENGEID = @DONORCHALLENGEID
          where
            REVENUESPLIT.ID = @REVENUESPLITID
            and DCE.REVENUESPLITID is null
            and ((REVENUESPLIT.APPLICATIONCODE <> 2) or 
                --allow individual pledge payments to be added unless the associated pledge is already added and does not have Removed status

                ((REVENUESPLIT.APPLICATIONCODE = 2) and 
                  (not exists (select DCE2.REVENUESPLITID 
                              from dbo.DONORCHALLENGEENCUMBERED DCE2
                              inner join dbo.REVENUESPLIT RS on DCE2.REVENUESPLITID = RS.ID
                              inner join dbo.REVENUE R on R.ID = RS.REVENUEID
                              inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.PLEDGEID = R.ID and INSTALLMENTSPLIT.DESIGNATIONID = RS.DESIGNATIONID
                              inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                              where INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID and DCE2.DONORCHALLENGEID = @DONORCHALLENGEID and DCE2.STATUSTYPECODE <> 2))));

      set @ENCUMBERED = @ENCUMBERED + @AMOUNT;

      fetch next from AVAILABLEREVENUE into @REVENUESPLITID, @AMOUNT, @DESIGNATIONID;
    end

    --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

    close AVAILABLEREVENUE;
    deallocate AVAILABLEREVENUE;

    end try
  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

  return 0

end