USP_DONORCHALLENGE_ADDLUMPSUM

CREATE procedure dbo.USP_DONORCHALLENGE_ADDLUMPSUM
(
    @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 @SPONSORID uniqueidentifier;
    declare @REVENUERECOGNITIONTYPECODEID uniqueidentifier;
    declare @DONORCHALLENGETYPECODE int;
    declare @DATE datetime;
    declare @AMOUNT money;
    declare @REVENUESPLITID uniqueidentifier;

    declare @BASECURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @BASEAMOUNT money;
    declare @ORGANIZATIONAMOUNT money;

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

    select 
      @SPONSORID = DONORCHALLENGE.EXTERNALSPONSORID,
      @REVENUERECOGNITIONTYPECODEID = DONORCHALLENGE.REVENUERECOGNITIONTYPECODEID,
      @DONORCHALLENGETYPECODE = DONORCHALLENGE.TYPECODE,
      @AMOUNT = DONORCHALLENGE.TOTALFUNDS,
      @REVID = DONORCHALLENGE.LUMPSUMMATCHREVENUEID,
      @BASECURRENCYID = DONORCHALLENGE.BASECURRENCYID
    from dbo.DONORCHALLENGE 
      where DONORCHALLENGE.ID = @ID;

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, 
      @CURRENTDATE, 
      @BASECURRENCYID, 
      @BASEEXCHANGERATEID output,
      @BASECURRENCYID,
      @BASEAMOUNT output,
      @ORGANIZATIONCURRENCYID,
      @ORGANIZATIONAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID output;

    if @DONORCHALLENGETYPECODE <> 1
    begin
      raiserror('BBERR_DONORCHALLENGE_NOTLUMPSUM', 13, 1);
      return 0;
    end

    if @REVID is not null
    begin
      raiserror('BBERR_DONORCHALLENGE_LUMPSUMREVENUEEXISTS', 13, 1);
      return 0;
    end

    if @SPONSORID is not null 
    begin

      set @REVID = newid();

      --Add Revenue

      insert into dbo.REVENUE (
        ID, 
        CONSTITUENTID, 
        DATE, 
        DONOTPOST, 
        POSTDATE, 
        DONOTRECEIPT, 
        AMOUNT, 
        TRANSACTIONTYPECODE, 
        RECEIPTAMOUNT, 
        BASECURRENCYID,
        TRANSACTIONCURRENCYID,
        TRANSACTIONAMOUNT,
        ORGANIZATIONAMOUNT,
        ORGANIZATIONEXCHANGERATEID,
        BASEEXCHANGERATEID,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
      ) values (
        @REVID, 
        @SPONSORID, 
        @DATE, 
        1, 
        null, 
        1, 
        @AMOUNT, 
        8, 
        0, 
        @BASECURRENCYID,
        @BASECURRENCYID,
        @AMOUNT,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
        @BASEEXCHANGERATEID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE,
        @CURRENTDATE
      )

      --Add origination source

      exec dbo.USP_REVENUE_ADDORIGIN @REVID, @SPONSORID, @CHANGEAGENTID, @CURRENTDATE;

      --Add applications

      insert into dbo.REVENUESPLIT(
        REVENUEID, 
        TYPECODE, 
        APPLICATIONCODE, 
        AMOUNT, 
        DESIGNATIONID, 
        BASECURRENCYID,
        TRANSACTIONCURRENCYID,
        TRANSACTIONAMOUNT,
        ORGANIZATIONAMOUNT,
        ORGANIZATIONEXCHANGERATEID,
        BASEEXCHANGERATEID,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
      ) 
      select 
        @REVID,
        0,
        0,
        DONORCHALLENGESPLIT.AMOUNT,
        DONORCHALLENGESPLIT.DESIGNATIONID,
        @BASECURRENCYID,
        @BASECURRENCYID,
        @AMOUNT,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
        @BASEEXCHANGERATEID,
        @CHANGEAGENTID, 
      @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
      from 
        dbo.DONORCHALLENGESPLIT
      where 
        DONORCHALLENGEID = @ID;

      exec dbo.USP_REVENUE_ADDCAMPAIGNS @REVID, @CHANGEAGENTID, @CURRENTDATE;

      --Add Payment method

      insert into dbo.REVENUEPAYMENTMETHOD (REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (@REVID, 9, @AMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE ,@CURRENTDATE);

      insert into dbo.REVENUESCHEDULE (ID, STARTDATE, FREQUENCYCODE, NUMBEROFINSTALLMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (@REVID, @DATE, 5, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

      insert into dbo.INSTALLMENT (
        ID, 
        REVENUEID, 
        AMOUNT, 
        DATE, 
        SEQUENCE, 
        BASECURRENCYID,
        TRANSACTIONCURRENCYID,
        TRANSACTIONAMOUNT,
        ORGANIZATIONAMOUNT,
        ORGANIZATIONEXCHANGERATEID,
        BASEEXCHANGERATEID,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
      ) values (
        newid(), 
        @REVID, 
        @AMOUNT, 
        @DATE, 
        1, 
        @BASECURRENCYID,
        @BASECURRENCYID,
        @AMOUNT,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
        @BASEEXCHANGERATEID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
      );

      exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @REVID, @CHANGEAGENTID, @CURRENTDATE;

      --Add default Recognition Credits

      declare CSR_REVENUESPLITS cursor local fast_forward for  
      select ID
      from dbo.REVENUESPLIT
      where REVENUEID = @REVID;

      open CSR_REVENUESPLITS

      fetch next from CSR_REVENUESPLITS into @REVENUESPLITID;

      while (@@FETCH_STATUS = 0)
      begin
        exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @REVENUESPLITID, @CHANGEAGENTID, @CURRENTDATE, null;

        fetch next from CSR_REVENUESPLITS into @REVENUESPLITID;
      end

      close CSR_REVENUESPLITS
      deallocate CSR_REVENUESPLITS          

      update dbo.DONORCHALLENGE set
        LUMPSUMMATCHREVENUEID = @REVID,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      from dbo.DONORCHALLENGE
      where ID = @ID;          
    end

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