USP_PLEDGE_ADD

CREATE procedure dbo.USP_PLEDGE_ADD 
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @DATE datetime,
  @AMOUNT money,
  @POSTSTATUSCODE tinyint = 1,
  @POSTDATE datetime = null,
  @SENDPLEDGEREMINDER bit = 1,
  @SPLITS xml,
  @FREQUENCYCODE tinyint = 5,
  @NUMBEROFINSTALLMENTS int = 1,
  @STARTDATE datetime = null,
  @INSTALLMENTS xml,
  @AUTOPAY bit = 0,
  @PAYMENTMETHODCODE tinyint = 2,
  @CARDHOLDERNAME nvarchar(255) = '',
  @CREDITCARDNUMBER nvarchar(4) = '',
  @CREDITTYPECODEID uniqueidentifier = null,
  @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
  @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @REFERENCENUMBER nvarchar(20) = '',
  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @FINDERNUMBER bigint = null,
  @SOURCECODE nvarchar(50) = null,
  @APPEALID uniqueidentifier = null,
  @BENEFITS xml = null,
  @BENEFITSWAIVED bit = 0,
  @GIVENANONYMOUSLY bit = null,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @DONOTACKNOWLEDGE bit = 0,
  @PLEDGESUBTYPEID uniqueidentifier = null,
  @BATCHNUMBER nvarchar(100) = '',
  @OPPORTUNITYID uniqueidentifier = null,
  @REFERENCE nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @STANDINGORDERSETUP bit = 0,
  @STANDINGORDERSETUPDATE datetime = null,
  @DDISOURCECODEID uniqueidentifier = null,
  @DDISOURCEDATE date = null,
  @SPLITSDECLININGGIFTAID xml = null output,
  @PERCENTAGEBENEFITS xml = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @PDACCOUNTSYSTEMID uniqueidentifier  = null,
  @BUSINESSUNITSAPPLIED bit = 0,
  @GENERATEREFERENCENUMBER bit = null,
  @STANDINGORDERREFERENCENUMBER nvarchar(18) = null,
  @EVENTID uniqueidentifier = null,
  @LOCALCORPID uniqueidentifier = null,
  @ISMEMBERSHIPPLEDGE bit = 0,
  @SEPAMANDATEID uniqueidentifier = null
)
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @REVENUEPAYMENTID uniqueidentifier;

  declare @INSTALLMENTSTABLE table
  (
    AMOUNT money,
    APPLIED money,
    BALANCE money,
    BASECURRENCYID uniqueidentifier,
    BASEEXCHANGERATEID uniqueidentifier,
    DATE datetime,
    ID uniqueidentifier,
    INSTALLMENTSPLITS xml,
    ORGANIZATIONAMOUNT money,
    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
    SEQUENCE int,
    TRANSACTIONAMOUNT money,
    TRANSACTIONCURRENCYID uniqueidentifier,
    RECEIPTAMOUNT money,
    ORGANIZATIONRECEIPTAMOUNT money
  )

  insert into @INSTALLMENTSTABLE (AMOUNT,APPLIED,BALANCE,BASECURRENCYID,BASEEXCHANGERATEID,DATE,ID,INSTALLMENTSPLITS,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,SEQUENCE,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID, RECEIPTAMOUNT, ORGANIZATIONRECEIPTAMOUNT)
    select AMOUNT,APPLIED,BALANCE,BASECURRENCYID,BASEEXCHANGERATEID,DATE,ID,INSTALLMENTSPLITS,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,SEQUENCE,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID, RECEIPTAMOUNT, ORGANIZATIONRECEIPTAMOUNT
    from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS)

  if @FREQUENCYCODE = 4 
    exec dbo.USP_REVENUEINSTALLMENT_VALIDATEINSTALLMENTS @INSTALLMENTS, @DATE, @AMOUNT,@ISMEMBERSHIPPLEDGE;

  set @REVENUEPAYMENTID = newid();

  if @ID is null
    set @ID = NewID();

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

  set @CURRENTDATE = GetDate();          

  --Set currency parameters for backwards compatibility

  if @TRANSACTIONCURRENCYID is null
    set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  if @BASECURRENCYID is null
    set @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  declare @SUM money; 
  declare @COUNT int;

  begin try
    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;
    else if @FINDERNUMBER <> 0
      begin
        if dbo.[UFN_MKTFINDERNUMBER_VALIDATE_MOD10](@FINDERNUMBER) = 0
          raiserror('Finder number failed check digit.', 13, 1);

        if dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER) is null
          raiserror('Invalid finder number.', 13, 1);
      end

    if @AMOUNT <= 0 and @ISMEMBERSHIPPLEDGE = 1
      raiserror('BBERR_PLEDGEADD_ZEROMEMBERSHIPPLEDGEAMOUNT', 13, 1);

    if @ISMEMBERSHIPPLEDGE = 1
    begin
      exec dbo.USP_MEMBERSHIPPLEDGE_VALIDATESPLITS @SPLITS, @AMOUNT, 1, default, @TRANSACTIONCURRENCYID;
    end
    else
    begin
      exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, 1, default, @TRANSACTIONCURRENCYID;
    end

    -- Multicurrency - AdamBu 3/17/10 - Ensure that the installment's transaction currency is the same as its revenue.

    if exists(
      select 1
      from @INSTALLMENTSTABLE
      where TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID
    )
    begin
      raiserror('An installment''s transaction currency must match that of its revenue.',13,1); 
    end

    select 
      @SUM = sum(AMOUNT),
      @COUNT = count(AMOUNT)
    from 
      @INSTALLMENTSTABLE;

    if @COUNT = 0 
      raiserror('Please enter at least one installment.',13,1);

    if @SUM <> @AMOUNT
      raiserror('The sum of the installment amounts must equal the pledge amount.',13,1);

    if @AUTOPAY = 0
      set @PAYMENTMETHODCODE = 9;

    declare @OVERLAP bit;

    set @OVERLAP = 0;

    select
      @OVERLAP = 1
    from
      @INSTALLMENTSTABLE [CURRENTINSTALLMENT]
    inner join @INSTALLMENTSTABLE [PREVIOUSINSTALLMENT] on PREVIOUSINSTALLMENT.SEQUENCE = CURRENTINSTALLMENT.SEQUENCE - 1
    where
      CURRENTINSTALLMENT.DATE < PREVIOUSINSTALLMENT.DATE;

    if @OVERLAP = 1
    raiserror('Installment dates are out of sequence.',13,1);

    -- If the system has set that households can't be donors, verify that constituent isn't a household

    if dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS() = 0 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
      raiserror('HOUSEHOLDSCANNOTBEDONORS', 13, 1);

    -- if the group type can't be a donor, raise an error

    if exists(select GD.ID from dbo.GROUPDATA GD inner join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID where GD.ID = @CONSTITUENTID and GT.CANBEDONOR = 0)
      raiserror('GROUPCANNOTBEDONOR', 13, 1);

    --Multicurrency - AdamBu 3/30/10 - Retrieve and calculate the necessary multicurrency values.

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

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

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

    declare @CURRENCYSETID uniqueidentifier;
    select @CURRENCYSETID = CURRENCYSETID from dbo.PDACCOUNTSYSTEM where ID = @PDACCOUNTSYSTEMID;

    if @BASECURRENCYID <> (select BASECURRENCYID from dbo.CURRENCYSET where ID = @CURRENCYSETID)
      raiserror('The base currency of the pledge is not valid for its account system.',13,1);

    if @TRANSACTIONCURRENCYID not in (select CURRENCYID from dbo.CURRENCYSETTRANSACTIONCURRENCY where CURRENCYSETID = @CURRENCYSETID)
      raiserror('The transaction currency of the pledge is not valid for its account system.',13,1);

    insert into dbo.FINANCIALTRANSACTION (
     ID,
      CONSTITUENTID,
      DATE,
      POSTDATE,
      POSTSTATUSCODE,
      BASEAMOUNT,
      TYPECODE,
      PDACCOUNTSYSTEMID,
      TRANSACTIONAMOUNT,
      TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID,
      ORGAMOUNT,
      ORGEXCHANGERATEID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values (
      @ID,
      @CONSTITUENTID,
      @DATE,
      @POSTDATE,
      case @POSTSTATUSCODE when 2 then 3 when 0 then 2 else 1 end,
      @BASEAMOUNT,
      case when @ISMEMBERSHIPPLEDGE = 1 then 15 else 1 end,
      @PDACCOUNTSYSTEMID,
      @AMOUNT,
      @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID,
      @ORGANIZATIONAMOUNT,
      @ORGANIZATIONEXCHANGERATEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    insert into dbo.REVENUE_EXT (
      ID,
      REFERENCE,
      BATCHNUMBER,
      DONOTRECEIPT,
      RECEIPTAMOUNT,
      FINDERNUMBER,
      SOURCECODE,
      APPEALID,
      GIVENANONYMOUSLY,
      DONOTACKNOWLEDGE,
      BENEFITSWAIVED,
      MAILINGID,
      CHANNELCODEID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values (
      @ID,
      isnull(@REFERENCE, ''),
      isnull(@BATCHNUMBER, ''),
      1,
      0,
      @FINDERNUMBER,
      isnull(@SOURCECODE,''),
      @APPEALID,
      isnull(@GIVENANONYMOUSLY,0),
      @DONOTACKNOWLEDGE, 
      @BENEFITSWAIVED,
      @MAILINGID,
      @CHANNELCODEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    --Add origination source

    exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

    if @ISMEMBERSHIPPLEDGE = 1 
      begin
        --Add membership installment plan original amount

        exec dbo.USP_MEMBERSHIPINSTALLMENTPLAN_ADDORIGINALAMOUNT @ID, @CHANGEAGENTID, @CURRENTDATE;                    
      end
    else
      begin
        --Add pledge original amount

        exec dbo.USP_PLEDGE_ADDORIGINALAMOUNT @ID, null, @CHANGEAGENTID, @CURRENTDATE;
      end

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

    --This is a view and already accomplished by POSTSTATUSCODE in FinancialTransaction

    --if @POSTSTATUSCODE = 0

    --  insert into dbo.REVENUEPOSTED(ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)

    --    values(@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);


    insert into dbo.REVENUESCHEDULE (ID,STARTDATE,FREQUENCYCODE,NUMBEROFINSTALLMENTS,PLEDGESUBTYPEID,SENDPLEDGEREMINDER, EVENTID, LOCALCORPID, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
      values (@ID, @STARTDATE, @FREQUENCYCODE, @NUMBEROFINSTALLMENTS, @PLEDGESUBTYPEID, @SENDPLEDGEREMINDER, @EVENTID, @LOCALCORPID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    -- Set the IDs for any blank entries in the splits XML so that it can associate the declines with gift aid

    -- flag with the generated splits

    set @SPLITS = (select 
                     case when [ID] is null or ID = '00000000-0000-0000-0000-000000000000' then newid() else [ID] end [ID],
                     [AMOUNT],
                     [APPLICATIONCODE],
                     [DESIGNATIONID],
                     [TYPECODE],
                     [DECLINESGIFTAID]
                   from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
                   for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64)

    set @SPLITSDECLININGGIFTAID = (select
                                     ID as REVENUESPLITID
                                   from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
     where DECLINESGIFTAID = 1
                                   for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64)

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

    set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
    exec dbo.USP_REVENUE_GETSPLITS_2_ADDFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;


    if (@BENEFITSWAIVED = 0)
      begin
        -- add benefits

        declare @TOTALBENEFITS xml;
        set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);
        --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.

        set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS,@TRANSACTIONCURRENCYID,@BASECURRENCYID);
      end
    else
      begin
        set @TOTALBENEFITS = null;
      end
    if @TOTALBENEFITS is not null
        exec dbo.USP_REVENUE_GETBENEFITS_4_UPDATEFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE;

    exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @REVENUEID = @ID, 
      @CATEGORYCODEID = @CATEGORYCODEID, 
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CURRENTDATE = @CURRENTDATE

    if @ISMEMBERSHIPPLEDGE = 1
      begin
        --Multicurrency - AdamBu 3/30/10 - Process the installments xml to calculate the base and organization amounts and place them in proper nodes.

        set @INSTALLMENTS = dbo.UFN_INSTALLMENT_CONVERTAMOUNTSINXML(@INSTALLMENTS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
        exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_ADDFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;
        exec dbo.USP_REVENUE_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CURRENTDATE;
        exec dbo.USP_MEMBERSHIPPLEDGE_GENERATEINSTALLMENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;
      end
    else
      begin
        exec dbo.USP_REVENUE_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CURRENTDATE;
        if @INSTALLMENTS is null or not exists(select 1 from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T1(c) cross apply T1.c.nodes('./INSTALLMENTSPLITS/ITEM') as T2(split) where T2.split.value('(DESIGNATIONID)[1]', 'uniqueidentifier') is not null)
        begin
            --Multicurrency - AdamBu 3/30/10 - Process the installments xml to calculate the base and organization amounts and place them in proper nodes.

            set @INSTALLMENTS = dbo.UFN_INSTALLMENT_CONVERTAMOUNTSINXML(@INSTALLMENTS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
            exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_ADDFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;
            exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;
        end
        else
            exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID
              ,@INSTALLMENTS
              ,@CHANGEAGENTID
              ,@CURRENTDATE
              ,null
              ,null
              ,null
              ,null
              ,@BASECURRENCYID
              ,@ORGANIZATIONEXCHANGERATEID
              ,@TRANSACTIONCURRENCYID
              ,@BASEEXCHANGERATEID;
      end

    exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE

    if (@OPPORTUNITYID is not null) and (@OPPORTUNITYID <> '00000000-0000-0000-0000-000000000000')
    begin
      insert into dbo.REVENUEOPPORTUNITY (ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.REVENUESPLIT where REVENUEID = @ID;

      exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @DATE, @CHANGEAGENTID, @CURRENTDATE
    end

    exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID,
      @OPPORTUNITYID = @OPPORTUNITYID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CHANGEDATE = @CURRENTDATE

    --Add payment info if applicable

    if @AUTOPAY = 1
    begin
      if @PAYMENTMETHODCODE = 2 --Credit card

      begin
        declare @CREDITCARDID uniqueidentifier
        exec dbo.USP_CREDITCARD_SAVE
          @ID = @CREDITCARDID output,
          @CREDITCARDTOKEN = @CREDITCARDTOKEN,
          @CARDHOLDERNAME = @CARDHOLDERNAME,
          @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
          @CREDITTYPECODEID = @CREDITTYPECODEID,
          @EXPIRESON = @EXPIRESON,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @CURRENTDATE = @CURRENTDATE

        update dbo.REVENUESCHEDULE set
          CREDITCARDID = @CREDITCARDID,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where ID = @ID
      end
      if @PAYMENTMETHODCODE = 3 --Direct debit

      begin
        --Direct Debit w/ Paperless mandate fields is UK only

        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
        begin
          declare @SENDNEWINSTRUCTION bit;
          declare @NEWINSTRUCTIONTOSEND tinyint;

          set @SENDNEWINSTRUCTION = 0;
          set @NEWINSTRUCTIONTOSEND = 0;

          if not @DDISOURCECODEID is null
          begin
            if dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
            begin
              --Bug 138736 - AdamBu 2/2/11 - Error on invalid paperless mandate setup.

              raiserror('BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS', 13, 1);
              return 1;
            end

            set @SENDNEWINSTRUCTION = 1;
            set @NEWINSTRUCTIONTOSEND = 1;
          end

          insert into dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, DDISOURCECODEID, DDISOURCEDATE, SENDPMINSTRUCTION, PMINSTRUCTIONTOSENDCODE, SEPAMANDATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values(@ID, @REFERENCEDATE, @REFERENCENUMBER, @CONSTITUENTACCOUNTID, @DDISOURCECODEID, @DDISOURCEDATE, @SENDNEWINSTRUCTION, @NEWINSTRUCTIONTOSEND, @SEPAMANDATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
        end
        else
        begin
          insert into dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, SEPAMANDATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values(@ID, @REFERENCEDATE, @REFERENCENUMBER, @CONSTITUENTACCOUNTID, @SEPAMANDATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
        end
      end
                  if @PAYMENTMETHODCODE = 11 --Standing order

      begin
        if @GENERATEREFERENCENUMBER is null or @GENERATEREFERENCENUMBER = 1
          select 
            @STANDINGORDERREFERENCENUMBER = '',
            @GENERATEREFERENCENUMBER = 1

        if @REFERENCEDATE is null or @REFERENCEDATE = '' or @REFERENCEDATE = '00000000'
            select @REFERENCEDATE = convert(nvarchar,DATEPART(yyyy,@CURRENTDATE)) + right('00' + convert(nvarchar,DATEPART(mm,@CURRENTDATE)),2) + right('00' + convert(nvarchar,DATEPART(dd,@CURRENTDATE)),2);

        insert into dbo.REVENUESCHEDULESTANDINGORDERPAYMENT(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values(@ID, @REFERENCEDATE, @STANDINGORDERREFERENCENUMBER, @CONSTITUENTACCOUNTID, @STANDINGORDERSETUP, @STANDINGORDERSETUPDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        insert into dbo.REVENUESTANDINGORDER(ID, CUSTOMREFERENCENUMBER, USESYSTEMGENERATEDREFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values(@ID, @STANDINGORDERREFERENCENUMBER, @GENERATEREFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
      end
    end

    if (@ISMEMBERSHIPPLEDGE=1)
      begin
        if (dbo.UFN_MEMBERSHIPRG_INSTALLMENTSPLITSBALANCE(@ID) = 0) raiserror('INSTALLMENTSPLITSBALANCE', 13, 10);
      end
    else
      begin
        if (dbo.UFN_PLEDGE_INSTALLMENTSPLITSBALANCE(@ID) = 0) raiserror('INSTALLMENTSPLITSBALANCE', 13, 10);
      end

    --Save the GL distributions

    --Membership installment plans are excluded. The revenue created here needs to be linked to the membership (as a membership transaction) for GL to be correctly generated

    if @POSTSTATUSCODE <> 2 and @ISMEMBERSHIPPLEDGE = 0
      begin
        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

        if @TOTALBENEFITS is not null and (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1) --BasicGL

          exec dbo.USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID, @CHANGEAGENTID, @CURRENTDATE
      end

    /* Apply business units */
    if @BUSINESSUNITSAPPLIED = 0
      exec dbo.USP_REVENUESPLIT_APPLYBUSINESSUNITS @ID, @CHANGEAGENTID, @CURRENTDATE;

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

  return 0;
end