USP_DONORCHALLENGEENCUMBEREDFUND_MATCHPLEDGEPAYMENT

CREATE procedure dbo.USP_DONORCHALLENGEENCUMBEREDFUND_MATCHPLEDGEPAYMENT
(
  @ID nvarchar(72),
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on

  begin try

    declare @CURRENTDATE datetime = getdate();

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

    declare @DONORCHALLENGEID uniqueidentifier;
    declare @PAYMENTSPLITID uniqueidentifier;
    declare @PLEDGESPLITID uniqueidentifier;

    declare @REVID uniqueidentifier;
    declare @REVSPLITID uniqueidentifier;
    declare @RECOGNITIONCREDITID uniqueidentifier;
    declare @SPONSORID uniqueidentifier;
    declare @DONORCHALLENGETYPECODE int;
    declare @DONORID uniqueidentifier;
    declare @DESIGNATIONID uniqueidentifier;
    declare @REVENUERECOGNITIONTYPECODEID uniqueidentifier;
    declare @AMOUNT money;
    declare @MATCHTYPECODE tinyint;
    declare @ENCUMBEREDAMOUNT money;
    declare @DATE datetime;
    declare @MATCHTHRESHOLD money;
    declare @MATCHINGFACTOR money;
    declare @DONORCHALLENGEENCUMBEREDID uniqueidentifier;

    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @BASECURRENCYDECIMALDIGITS tinyint;
    declare @BASECURRENCYROUNDINGTYPECODE tinyint;

    set @DATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    set @DONORCHALLENGEID = cast(left(@ID, 36) as uniqueidentifier);
    set @PAYMENTSPLITID = cast(right(@ID, 36) as uniqueidentifier);

    --Get the Challenge Info

    select 
      @SPONSORID = DONORCHALLENGE.EXTERNALSPONSORID,
      --@REVENUERECOGNITIONTYPECODEID = DONORCHALLENGE.REVENUERECOGNITIONTYPECODEID,

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

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

    --Find the matching pledge

    select @PLEDGESPLITID = REVENUESPLIT.ID,
      @DONORID = PAYMENT.CONSTITUENTID,
      @AMOUNT = dbo.UFN_CURRENCY_ROUND(
    case 
      when @MATCHTYPECODE = 1 
        then 
          case 
            when PAYMENT.TRANSACTIONAMOUNT = 0 
              then 0 
            else PAYMENT.RECEIPTAMOUNT/PAYMENT.TRANSACTIONAMOUNT 
              * case
                when @BASECURRENCYID = PAYMENTSPLIT.TRANSACTIONCURRENCYID
                  then PAYMENTSPLIT.TRANSACTIONAMOUNT
                else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
              end
          end
      else 
        case
          when @BASECURRENCYID = PAYMENTSPLIT.TRANSACTIONCURRENCYID
            then PAYMENTSPLIT.TRANSACTIONAMOUNT
          else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
        end
    end * @MATCHINGFACTOR, 
        @BASECURRENCYDECIMALDIGITS,
        @BASECURRENCYROUNDINGTYPECODE)
    from dbo.INSTALLMENTSPLITPAYMENT
    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
    inner join dbo.REVENUESPLIT on INSTALLMENTSPLIT.PLEDGEID = REVENUESPLIT.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
    inner join dbo.REVENUESPLIT PAYMENTSPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = PAYMENTSPLIT.ID
    inner join dbo.REVENUE PAYMENT on PAYMENTSPLIT.REVENUEID = PAYMENT.ID
 where INSTALLMENTSPLITPAYMENT.PAYMENTID = @PAYMENTSPLITID;

    --Get the Encumbered Info

    select 
      @ENCUMBEREDAMOUNT = dbo.UFN_DONORCHALLENGEENCUMBERED_PLEDGEENCUMBEREDAMOUNT(DONORCHALLENGEENCUMBERED.ID),
      @DESIGNATIONID = DONORCHALLENGEENCUMBERED.DESIGNATIONID,
      @DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID,
      @REVENUERECOGNITIONTYPECODEID = DONORCHALLENGEENCUMBERED.REVENUERECOGNITIONTYPECODEID
    from dbo.DONORCHALLENGEENCUMBERED
    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    where DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DONORCHALLENGEID 
            and DONORCHALLENGEENCUMBERED.REVENUESPLITID = @PLEDGESPLITID;

    --make sure we don't exceed the overall encumbered amount for the pledge

    if @AMOUNT >= @ENCUMBEREDAMOUNT
    begin
      set @AMOUNT = @ENCUMBEREDAMOUNT;
      --Update the pledge as fully approved

      update dbo.DONORCHALLENGEENCUMBERED 
      set 
        STATUSTYPECODE = 1,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where 
        DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DONORCHALLENGEID 
        and DONORCHALLENGEENCUMBERED.REVENUESPLITID = @PLEDGESPLITID;
    end

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

    if @SPONSORID is not null and @DONORCHALLENGETYPECODE = 0
    begin
      exec dbo.USP_DONORCHALLENGE_ADDREVENUE
        @REVID output,
        @REVSPLITID output,
        @SPONSORID,
        @DESIGNATIONID,
        @AMOUNT,
        @DATE,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @BASECURRENCYID;

      --Add recognition credits for externally sponsored challenge

      set @RECOGNITIONCREDITID = newid();
      insert into dbo.REVENUERECOGNITION
      (
        ID,
        REVENUESPLITID,
        CONSTITUENTID,
        AMOUNT,
        EFFECTIVEDATE,
        REVENUERECOGNITIONTYPECODEID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
        BASECURRENCYID,
        ORGANIZATIONAMOUNT,
        ORGANIZATIONEXCHANGERATEID
      )
      select
        @RECOGNITIONCREDITID,
        @REVSPLITID,
        REVENUE.CONSTITUENTID,
        @AMOUNT,
        @DATE,
        @REVENUERECOGNITIONTYPECODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE, 
        @CURRENTDATE,
        @BASECURRENCYID,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID      
      from dbo.REVENUESPLIT
      inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
      where REVENUESPLIT.ID = @PAYMENTSPLITID;
    end

    declare @PAYMENTENCUMBEREDID uniqueidentifier
    set @PAYMENTENCUMBEREDID = newid();

    --ADD NEW ROW IN ENCUMBERED FOR PLEDGE PAYMENT

    insert into dbo.DONORCHALLENGEENCUMBERED(
      ID,
      DONORCHALLENGEID,
      REVENUESPLITID,
      DESIGNATIONID,
      METHODTYPECODE,
      STATUSTYPECODE,
      AMOUNT,
      REVENUERECOGNITIONTYPECODEID,
      MATCHEDREVENUEID,
      MATCHEDREVENUERECOGNITIONID,
      BASECURRENCYID,
      ORGANIZATIONAMOUNT,
      ORGANIZATIONEXCHANGERATEID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    ) values (
      @PAYMENTENCUMBEREDID,
      @DONORCHALLENGEID,
      @PAYMENTSPLITID,
      @DESIGNATIONID,
      0,
      1,
      @AMOUNT,
      @REVENUERECOGNITIONTYPECODEID,
      @REVID,
      @RECOGNITIONCREDITID,
      @BASECURRENCYID,
      @ORGANIZATIONAMOUNT,
      @ORGANIZATIONEXCHANGERATEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    )

    --add recognition credit to internal sponsors

    if @DONORCHALLENGETYPECODE = 0 AND @SPONSORID is null
    begin     
      insert into dbo.RECOGNITIONCREDIT(ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, USERRECOGNITIONTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, DESIGNATIONID, RECOGNITIONCREDITTYPECODE, DONORCHALLENGEENCUMBEREDID)
      select
        newid(),
        REVENUE.CONSTITUENTID,
        @AMOUNT,
        @DATE,
        @REVENUERECOGNITIONTYPECODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE, 
        @CURRENTDATE,
        @BASECURRENCYID,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
        @DESIGNATIONID,
        1, --donor challenge

        @PAYMENTENCUMBEREDID
      from dbo.REVENUESPLIT
      inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
      where REVENUESPLIT.ID = @PAYMENTSPLITID;
    end 

    if dbo.UFN_DONORCHALLENGE_MATCHEDAMOUNT(@DONORCHALLENGEID) >= @MATCHTHRESHOLD
    begin
      update dbo.DONORCHALLENGE
      set 
        STATUSTYPECODE = 1,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where ID = @DONORCHALLENGEID
        and STATUSTYPECODE <> 1;

      if @DONORCHALLENGETYPECODE = 1
        exec dbo.USP_DONORCHALLENGE_ADDLUMPSUM @DONORCHALLENGEID, @CHANGEAGENTID;
    end    
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch