USP_DONORCHALLENGEENCUMBEREDFUND_MATCH

CREATE procedure dbo.USP_DONORCHALLENGEENCUMBEREDFUND_MATCH
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on

  begin try

    declare @CURRENTDATE datetime = getdate();

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

    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 @DATE datetime;
    declare @DONORCHALLENGEID uniqueidentifier;
    declare @MATCHTHRESHOLD money;
    declare @BASECURRENCYID uniqueidentifier;

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

    select 
      @SPONSORID = DONORCHALLENGE.EXTERNALSPONSORID,
      @AMOUNT = DONORCHALLENGEENCUMBERED.AMOUNT,
      @DESIGNATIONID = DONORCHALLENGEENCUMBERED.DESIGNATIONID,
      @REVENUERECOGNITIONTYPECODEID = DONORCHALLENGEENCUMBERED.REVENUERECOGNITIONTYPECODEID,
      @DONORID = REVENUE.CONSTITUENTID,
      @DONORCHALLENGETYPECODE = DONORCHALLENGE.TYPECODE,
      @DONORCHALLENGEID = DONORCHALLENGE.ID,
      @MATCHTHRESHOLD = case when DONORCHALLENGE.TYPECODE = 1 then DONORCHALLENGE.MATCHTHRESHOLD else DONORCHALLENGE.TOTALFUNDS end,
      @BASECURRENCYID = DONORCHALLENGEENCUMBERED.BASECURRENCYID
    from
      dbo.DONORCHALLENGEENCUMBERED
    inner join
      dbo.DONORCHALLENGE on DONORCHALLENGE.ID = DONORCHALLENGEENCUMBERED.DONORCHALLENGEID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
    inner join
      dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    where
      DONORCHALLENGEENCUMBERED.ID = @ID and
      DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 0;

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);

    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,
        DONORCHALLENGEENCUMBERED.AMOUNT,
        @DATE,
        @REVENUERECOGNITIONTYPECODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE, 
        @CURRENTDATE,
        DONORCHALLENGEENCUMBERED.BASECURRENCYID,
        dbo.UFN_CURRENCY_CONVERT(DONORCHALLENGEENCUMBERED.AMOUNT, @ORGANIZATIONEXCHANGERATEID),
        @ORGANIZATIONEXCHANGERATEID 
      from
        dbo.REVENUESPLIT
      inner join
        dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
      inner join
        dbo.DONORCHALLENGEENCUMBERED on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
      where
        DONORCHALLENGEENCUMBERED.ID = @ID;        
    end

    --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,
        DONORCHALLENGEENCUMBERED.AMOUNT,
        @DATE,
        @REVENUERECOGNITIONTYPECODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE, 
        @CURRENTDATE,
        DONORCHALLENGEENCUMBERED.BASECURRENCYID,
        dbo.UFN_CURRENCY_CONVERT(DONORCHALLENGEENCUMBERED.AMOUNT, @ORGANIZATIONEXCHANGERATEID),
        @ORGANIZATIONEXCHANGERATEID,
        DONORCHALLENGEENCUMBERED.DESIGNATIONID,
        1, --donor challenge

        DONORCHALLENGEENCUMBERED.ID
      from
        dbo.REVENUESPLIT
      inner join
        dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
      inner join
        dbo.DONORCHALLENGEENCUMBERED on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
      where
        DONORCHALLENGEENCUMBERED.ID = @ID;
    end

    update
      dbo.DONORCHALLENGEENCUMBERED
    set
      STATUSTYPECODE = 1,
      MATCHEDREVENUEID = @REVID,
      MATCHEDREVENUERECOGNITIONID = @RECOGNITIONCREDITID,
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    where
      ID = @ID;

    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