USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFT (
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,                    
  @DATE datetime,
  @AMOUNT money = 0,
  @PAYMENTMETHODCODE tinyint = 2,
  @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @REFERENCENUMBER nvarchar(20) = '',
  @CARDHOLDERNAME nvarchar(255) = '',
  @CREDITCARDNUMBER nvarchar(20) = '',
  @CREDITTYPECODEID uniqueidentifier = null,
  @EXPIRESON dbo.UDT_FUZZYDATE = null,
  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @SPLITS xml,
  @FREQUENCYCODE tinyint = 3,
  @ENDDATE datetime = null,
  @STARTDATE datetime = null,
  @FINDERNUMBER bigint = null,
  @SOURCECODE nvarchar(50) = null,
  @APPEALID uniqueidentifier = null,
  @GIVENANONYMOUSLY bit = 0,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @DONOTACKNOWLEDGE bit = 0,
  @AUTOPAY bit = 1,
  @REFERENCE nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @STANDINGORDERSETUP bit = 0,
  @STANDINGORDERSETUPDATE datetime = null,
  @DDISOURCECODEID uniqueidentifier = null,
  @DDISOURCEDATE date = null,
  @SENDREMINDER bit = 1,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @BUSINESSUNITSAPPLIED bit=0,
  @GENERATEREFERENCENUMBER bit = 1,
  @STANDINGORDERREFERENCENUMBER nvarchar(18) = null,
  @EVENTID uniqueidentifier = null,
  @LOCALCORPID uniqueidentifier = null,
  @ISMEMBERSHIPRECURRINGGIFT bit = 0,
  @BATCHNUMBER nvarchar(100) = '',
  @BASECURRENCYID uniqueidentifier = null,
  @SEPAMANDATEID uniqueidentifier = null,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null
)

as

set nocount on;

declare @CURRENTDATE datetime
declare @NEXTTRANSACTIONDATE datetime                
declare @TRANSACTIONTYPECODE tinyint
declare @PAYMENTMETHODID uniqueidentifier;

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

set @TRANSACTIONTYPECODE = 2; --Recurring Gift                    


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

set @CURRENTDATE = getdate();

--Multicurrency - RSC 4/14/10 - Use the appuser's base currency since there is no account system and we do not post recurring gifts.

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

--Multicurrency - RSC 4/14/10 - Add a spot exchange rate if an existing rate hasn't

--    been selected, the base and transaction currencies are different, and the rate

--    entered isn't zero (which indicates that the user wants to enter the record without a rate).

if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
  set @BASEEXCHANGERATEID = newid()

  --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

  /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
    and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
  begin
    raiserror('User does not have the right to add a new spot rate.', 13, 1);
    return 1;
  end*/

  insert into dbo.CURRENCYEXCHANGERATE(
    ID, 
    FROMCURRENCYID,
    TOCURRENCYID,
    RATE,
    ASOFDATE,
    TYPECODE,
    SOURCECODEID,
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED
  )
  values(
    @BASEEXCHANGERATEID,
    @TRANSACTIONCURRENCYID,
    @BASECURRENCYID,
    @EXCHANGERATE,
    @DATE,
    2,
    null,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
  );
end

declare @SUM money 

begin try
  if @FINDERNUMBER is null
    set @FINDERNUMBER = 0;

  --if we have a finder number make sure the constituent has been added to the tables

 if @FINDERNUMBER > 0
      exec dbo.USP_DATAFORMTEMPLATE_ADD_ACQUISITIONLISTMEMBER @CONSTITUENTID, @CHANGEAGENTID, @FINDERNUMBER;

  if @AMOUNT < 0 
    raiserror('The amount cannot be negative.', 13, 1);

  if @STARTDATE < @DATE
    raiserror('The schedule cannot start before the gift date.', 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);

    -- if this is a recurring gift created via membership and the @PAYMENTMETHODCODE is other (10)

    -- or the PAYMENTMETHODCODE is not one of the values recurring gifts allow then set it to None (9)

    -- this was previously handled by the @AUTOPAY = 0 flag

  if (@ISMEMBERSHIPRECURRINGGIFT = 1 and @PAYMENTMETHODCODE = 10) or @PAYMENTMETHODCODE not in (0,1,2,3,9,10,11,98,101,102)
    set @PAYMENTMETHODCODE = 9;

  -- clear the OTHERPAYMENTMETHODCODEID if the payment method is different than Other

  if @PAYMENTMETHODCODE <> 10
    set @OTHERPAYMENTMETHODCODEID = null;

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


  --Multicurrency - SlyyMu 8/12/10 replaced the previous multicurrency section done by (RSC 4/14/10) with the UFN_CURRENCY_GETCURRENCYVALUES()                 

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

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

  insert into dbo.REVENUE ([ID],[CONSTITUENTID],[DATE],[POSTDATE],[DONOTRECEIPT], [AMOUNT], [TRANSACTIONTYPECODE], [RECEIPTAMOUNT], [FINDERNUMBER], [SOURCECODE], [APPEALID], [GIVENANONYMOUSLY], [MAILINGID], [CHANNELCODEID], [DONOTACKNOWLEDGE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED],[BASECURRENCYID],[ORGANIZATIONAMOUNT],[ORGANIZATIONEXCHANGERATEID],[TRANSACTIONAMOUNT],[TRANSACTIONCURRENCYID],[BASEEXCHANGERATEID], [BATCHNUMBER])
    values (@ID,@CONSTITUENTID,@DATE,@DATE, 0, @BASEAMOUNT, @TRANSACTIONTYPECODE, @AMOUNT, @FINDERNUMBER, @SOURCECODE, @APPEALID, @GIVENANONYMOUSLY, @MAILINGID, @CHANNELCODEID, @DONOTACKNOWLEDGE, @CHANGEAGENTID, @CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID, isnull(@BATCHNUMBER, ''));

  --Add origination source

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

  set @PAYMENTMETHODID = newid();

  insert into dbo.REVENUEPAYMENTMETHOD (ID, REVENUEID, AMOUNT, PAYMENTMETHODCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values (@PAYMENTMETHODID, @ID, @BASEAMOUNT, case when @PAYMENTMETHODCODE = 98 then 2 else @PAYMENTMETHODCODE end, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE ,@CURRENTDATE);

  exec dbo.USP_REVENUEREFERENCE_ADD @ID, @REFERENCE, @CHANGEAGENTID;

  --if semi-monthly ensure nexttransaction date starts on 1st or 15th

  if @FREQUENCYCODE = 7 and not (day(@STARTDATE) = 1 or day(@STARTDATE) = 15)
  begin
    --Use the old version of UFN_REVENUE_GETNEXTTRANSACTIONDATE for this purpose because it doesn't take a revenue ID and does exactly what is needed.

    set @NEXTTRANSACTIONDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE(@FREQUENCYCODE, @STARTDATE)
  end
  else
    set @NEXTTRANSACTIONDATE = @STARTDATE

  declare @CREDITCARDID uniqueidentifier
  if @PAYMENTMETHODCODE = 2 -- Credit Card

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

  insert into dbo.REVENUESCHEDULE ([ID], [STARTDATE], [SCHEDULESEEDDATE], [ENDDATE], [FREQUENCYCODE], [NEXTTRANSACTIONDATE], CREDITCARDID, SENDPLEDGEREMINDER, [EVENTID], [LOCALCORPID], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
    values (@ID, @STARTDATE, @NEXTTRANSACTIONDATE, @ENDDATE, @FREQUENCYCODE, @NEXTTRANSACTIONDATE, @CREDITCARDID, @SENDREMINDER, @EVENTID, @LOCALCORPID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

  -- add first installment

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

  -- add any additional installments to get us current

  exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS
    @ID = @ID,
    @CHANGEAGENTID = @CHANGEAGENTID,
    @CREATIONDATE = @CURRENTDATE,
    @AMOUNT = @BASEAMOUNT,
    @BASECURRENCYID = @BASECURRENCYID,
    @ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
    @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
    @TRANSACTIONAMOUNT = @AMOUNT,
    @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
    @BASEEXCHANGERATEID = @BASEEXCHANGERATEID;

  declare @SENDNEWINSTRUCTION bit;
  declare @NEWINSTRUCTIONTOSEND tinyint;

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

  if @PAYMENTMETHODCODE = 3 --Direct debit

  begin
    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 0
    begin
      set @DDISOURCECODEID = null;
      set @DDISOURCEDATE = null;
    end

    if not @DDISOURCECODEID is null
    begin                                    
      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 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);

      if @STANDINGORDERSETUP = 0 set @STANDINGORDERSETUPDATE = null

      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

  else if @PAYMENTMETHODCODE = 10 --Other

  begin
    insert into dbo.OTHERPAYMENTMETHODDETAIL (ID, REFERENCEDATE, REFERENCENUMBER, OTHERPAYMENTMETHODCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select ID, @REFERENCEDATE, @REFERENCENUMBER, @OTHERPAYMENTMETHODCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from dbo.REVENUEPAYMENTMETHOD 
    where REVENUEID = @ID; 
  end

  else if @PAYMENTMETHODCODE = 98 --Credit card 4 digits only

  begin
    insert into dbo.CREDITCARDPAYMENTMETHODDETAIL (ID, CARDHOLDERNAME, CREDITCARDPARTIALNUMBER, CREDITTYPECODEID, EXPIRESON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select ID, @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @EXPIRESON, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from dbo.REVENUEPAYMENTMETHOD 
    where REVENUEID = @ID; 
  end

  -- 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)

  --Multicurrency - RSC 4/14/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;

  exec dbo.USP_REVENUE_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CURRENTDATE;

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

  exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID, 
    @CHANGEAGENTID = @CHANGEAGENTID,
    @CHANGEDATE = @CURRENTDATE

  declare @SPLITSDECLININGGIFTAID xml
  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)

        exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, @TRANSACTIONTYPECODE, @SPLITSDECLININGGIFTAID;


  if @PAYMENTMETHODCODE = 3 
    and @DDISOURCECODEID is not null
    and 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

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

  insert into dbo.RECURRINGGIFTAMENDMENT(ID, FINANCIALTRANSACTIONID, AMENDMENTTYPECODE, DATE, STATUSCODE,
            TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEAMOUNT, BASECURRENCYID, BASEEXCHANGERATEID,
            ORGANIZATIONAMOUNT,  ORGANIZATIONEXCHANGERATEID, FREQUENCYCODE,
            DESIGNATIONS, SOURCECODE, FINDERNUMBER, APPEALID, MAILINGID, CHANNELCODEID, PAYMENTMETHODCODE, CREDITTYPECODEID,
            CREDITCARDPARTIALNUMBER, EXPIRESON, CARDHOLDERNAME, CONSTITUENTACCOUNTID, REFERENCEDATE, 
            REFERENCENUMBER, CONSTITUENTID, OTHERPAYMENTMETHODCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
            DDISOURCECODEID, DDISOURCEDATE, SENDPMINSTRUCTION, PMINSTRUCTIONTOSENDCODE, SEPAMANDATEID, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, USESYSTEMGENERATEDREFERENCENUMBER)

  values(newid(), @ID, 0, @CURRENTDATE, 0, @AMOUNT, @TRANSACTIONCURRENCYID, @BASEAMOUNT, @BASECURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID, @FREQUENCYCODE, @SPLITS, isnull(@SOURCECODE,''), @FINDERNUMBER, @APPEALID, @MAILINGID, @CHANNELCODEID, @PAYMENTMETHODCODE, @CREDITTYPECODEID,
        isnull(@CREDITCARDNUMBER, ''), isnull(nullif(@EXPIRESON,''), '00000000'), isnull(@CARDHOLDERNAME, ''), @CONSTITUENTACCOUNTID, @REFERENCEDATE, 
        case when @PAYMENTMETHODCODE = 11 then case when @GENERATEREFERENCENUMBER = 1 then dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(@ID) else @STANDINGORDERREFERENCENUMBER end else @REFERENCENUMBER end, 
        @CONSTITUENTID, @OTHERPAYMENTMETHODCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @DDISOURCECODEID, @DDISOURCEDATE, @SENDNEWINSTRUCTION, @NEWINSTRUCTIONTOSEND, 
        @SEPAMANDATEID, isnull(@STANDINGORDERSETUP,0), @STANDINGORDERSETUPDATE, case when @PAYMENTMETHODCODE = 11 then @GENERATEREFERENCENUMBER else 0 end)

end try

begin catch
  exec dbo.USP_RAISE_ERROR;

  close symmetric key sym_BBInfinity;
  return 1;
end catch

return 0;