USP_ADDPLANNEDGIFTADDITIONREVENUE

CREATE procedure dbo.USP_ADDPLANNEDGIFTADDITIONREVENUE
(
  @ID uniqueidentifier,
  @AMOUNT money,
  @DATE datetime,
  @RECEIPTAMOUNT money,
  @CHANGEAGENTID uniqueidentifier,
  @ANONYMOUS bit,
  @NEWREVENUEID uniqueidentifier = null,
  @GENERATEGLDISTRIBUTIONS bit,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @DONOTRECEIPT bit = null
)
as begin
  set nocount on;

  begin try

    if exists(select top 1 ID from dbo.PLANNEDGIFTADDITIONREVENUE where ID = @ID)
      raiserror('A planned gift addition can only be linked to one revenue record.', 13, 1);

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

    if @PDACCOUNTSYSTEMID is null
      set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';

    if @DONOTRECEIPT is null
      set @DONOTRECEIPT = 1

    declare @CURRENTDATE datetime = getdate();

    declare @CONSTITUENTID uniqueidentifier;
    declare @GIFTAMOUNT money;
    declare @RECOGNITIONAMOUNT money;

    select 
      @CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID,
      @GIFTAMOUNT = PLANNEDGIFTADDITION.TRANSACTIONGIFTAMOUNT,
      @RECOGNITIONAMOUNT = PLANNEDGIFTADDITION.RECOGNITIONAMOUNT
    from
      dbo.PLANNEDGIFTADDITION
    inner join
      dbo.PLANNEDGIFT on PLANNEDGIFT.ID = PLANNEDGIFTADDITION.PLANNEDGIFTID
    where
      PLANNEDGIFTADDITION.ID = @ID;

    --Get current designations

    declare @SPLITS xml = dbo.UFN_PLANNEDGIFTADDITION_GETDESIGNATIONS_TOITEMLISTXML(@ID);

    --Combine duplicate designations and prorate the splits

    set @SPLITS = (
      select sum(TRANSACTIONAMOUNT) as AMOUNT, DESIGNATIONID as ID
      from dbo.UFN_PLANNEDGIFTADDITION_GETDESIGNATIONS_FROMITEMLISTXML(@SPLITS)
      group by DESIGNATIONID
      for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64)

    declare @DECIMALDIGITS tinyint = 2;
    select @DECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where ID = @TRANSACTIONCURRENCYID;

    set @SPLITS = (
      select AMOUNT, ID as DESIGNATIONID
      from dbo.UFN_SPLITS_PRORATEAMOUNTS(@GIFTAMOUNT, @AMOUNT, @DECIMALDIGITS, @SPLITS)
      for xml raw('ITEM'),type,elements,root('DESIGNATION'),binary base64)

    exec dbo.USP_PLANNEDGIFTADDITION_VALIDATESPLITS @SPLITS, @AMOUNT;

    declare @REVENUEID uniqueidentifier = coalesce(@NEWREVENUEID, newid());

    --Multicurrency - AdamBu 5/19/10 - Retrieve and calculate the necessary multicurrency values.    

    declare @BASEAMOUNT money;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;

    -- Determine Receipt Type Preference

    declare @RECEIPTTYPECODE bit;
    set @RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID, null)

    if @GENERATEGLDISTRIBUTIONS = 1
    begin
      insert into dbo.FINANCIALTRANSACTION
      (
        ID,
        CONSTITUENTID,
        DATE,
        POSTSTATUSCODE,
        POSTDATE,
        BASEAMOUNT,
        TYPECODE,
        PDACCOUNTSYSTEMID,
        ORGAMOUNT,
        ORGEXCHANGERATEID,
        TRANSACTIONAMOUNT,
        TRANSACTIONCURRENCYID,
        BASEEXCHANGERATEID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      values
      (
        @REVENUEID,
        @CONSTITUENTID,
        @DATE,
        1,
        @DATE,
        @BASEAMOUNT,
        4, --planned gift

        @PDACCOUNTSYSTEMID,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
      @AMOUNT,
        @TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      );

      insert into dbo.REVENUE_EXT
      (
        ID,
        DONOTRECEIPT,
        RECEIPTAMOUNT,
        GIVENANONYMOUSLY,
        RECEIPTTYPECODE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      values
      (
        @REVENUEID,
        @DONOTRECEIPT,
        @RECEIPTAMOUNT,
        @ANONYMOUS,
        @RECEIPTTYPECODE,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      );
    end
    else
    begin
      insert into dbo.FINANCIALTRANSACTION
      (
        ID,
        CONSTITUENTID,
        DATE,
        POSTSTATUSCODE,
        POSTDATE,
        BASEAMOUNT,
        TYPECODE,
        PDACCOUNTSYSTEMID,
        ORGAMOUNT,
        ORGEXCHANGERATEID,
        TRANSACTIONAMOUNT,
        TRANSACTIONCURRENCYID,
        BASEEXCHANGERATEID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      values
      (
        @REVENUEID,
        @CONSTITUENTID,
        @DATE,
        3,
        null,
        @BASEAMOUNT,
        4, -- planned gift

        @PDACCOUNTSYSTEMID,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
        @AMOUNT,
        @TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      );

      insert into dbo.REVENUE_EXT
      (
        ID,
        DONOTRECEIPT,
        RECEIPTAMOUNT,
        GIVENANONYMOUSLY,
        RECEIPTTYPECODE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      values
      (
        @REVENUEID,
        @DONOTRECEIPT,
        @RECEIPTAMOUNT,
        @ANONYMOUS,
        @RECEIPTTYPECODE,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      );
    end

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

    insert into dbo.PLANNEDGIFTADDITIONREVENUE
    (
      ID,
      REVENUEID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    values
    (
      @ID,
      @REVENUEID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    );

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

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

    --Multicurrency - AdamBu 5/19/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

    set @SPLITS = dbo.UFN_PLANNEDGIFTDESIGNATION_CONVERTAMOUNTSINXML(@SPLITS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID);
    exec dbo.USP_PLANNEDGIFTADDITION_GETSPLITS_ADDFROMXML @REVENUEID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

    -- create recognitions

    exec dbo.USP_REVENUE_CREATERECOGNITIONS_WITHAMOUNT @REVENUEID, @RECOGNITIONAMOUNT, @CHANGEAGENTID, @CURRENTDATE;

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

    if dbo.UFN_PLEDGE_INSTALLMENTSPLITSBALANCE(@ID) = 0
      raiserror('INSTALLMENTSPLITSBALANCE', 13, 2);

    -- Link revenue to Account System

    exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE @REVENUEID, @PDACCOUNTSYSTEMID, @CURRENTDATE, @CHANGEAGENTID;

    --Save the GL distributions

    if @GENERATEGLDISTRIBUTIONS = 1
      exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

    -- Copy campaigns from planned gift

    insert into dbo.REVENUESPLITCAMPAIGN
    (
      REVENUESPLITID,
      CAMPAIGNID,
      CAMPAIGNSUBPRIORITYID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select distinct
      REVENUESPLIT.ID,
      PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.CAMPAIGNID,
      PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from
      dbo.PLANNEDGIFTADDITIONDESIGNATION
    inner join
      dbo.PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN on PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.PLANNEDGIFTADDITIONDESIGNATIONID = PLANNEDGIFTADDITIONDESIGNATION.ID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = @REVENUEID and REVENUESPLIT.DESIGNATIONID = PLANNEDGIFTADDITIONDESIGNATION.DESIGNATIONID
    where
      PLANNEDGIFTADDITIONDESIGNATION.PLANNEDGIFTADDITIONID = @ID;

    exec dbo.USP_PLANNEDGIFTREVENUE_DEFAULTSOLICITORS @ID, @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

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