USP_DATAFORMTEMPLATE_ADD_PLEDGE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PLEDGE 
(
  @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 = null,
  @AUTOPAY bit = 0,
  @PAYMENTMETHODCODE tinyint = 2,
  @CARDHOLDERNAME nvarchar(255) = '',
  @CREDITCARDNUMBER nvarchar(20) = '',
  @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,
  @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,
  @PERCENTAGEBENEFITS xml = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @PDACCOUNTSYSTEMID uniqueidentifier  = null,
  @GENERATEREFERENCENUMBER bit = 1,
  @STANDINGORDERREFERENCENUMBER nvarchar(18) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @EVENTID uniqueidentifier = null,
  @LOCALCORPID uniqueidentifier = null,
  @BATCHNUMBER nvarchar(100) = '',
  @INSTALLMENTAMOUNT money = 0,
  @INSTALLMENTSPLITSCHEDULEOPTIONCODE tinyint = 0,
  @SEPAMANDATEID uniqueidentifier = null
)

as
begin
  set nocount on;

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

  declare @CURRENTDATE datetime = getdate();

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

  -- Check GL business rule for this account system and set to 'Do not post' if needed.

  -- ****

  declare @ALLOWGLDISTRIBUTIONS bit;
  set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
  if @ALLOWGLDISTRIBUTIONS = 0 
    begin
      set @POSTSTATUSCODE = 2        -- Do not post

      set @POSTDATE = null
    end
  -- ****                                            


  --Multicurrency - AdamBu 3/30/10 - Retrieve base currency from the account system's currency set.

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

  declare @BASECURRENCYID uniqueidentifier;
  select 
    @BASECURRENCYID = BASECURRENCYID
  from 
    dbo.CURRENCYSET
  where 
    ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET())

  --Multicurrency - AdamBu 4/1/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

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

  if @PAYMENTMETHODCODE = 11 and @STANDINGORDERSETUP = 0 --Standing order

    set @STANDINGORDERSETUPDATE = null;

  if @NUMBEROFINSTALLMENTS > 150
    raiserror('BBERR_NUMINSTALLMENTS',13,1);

  begin try
    declare @SPLITSDECLININGGIFTAID xml;

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

    if isnull(@FINDERNUMBER, 0) > 0
        exec dbo.[USP_DATAFORMTEMPLATE_ADD_ACQUISITIONLISTMEMBER] @CONSTITUENTID, @CHANGEAGENTID, @FINDERNUMBER;

if @BATCHNUMBER is null
      set @BATCHNUMBER = ''

    exec dbo.USP_PLEDGE_ADD
      @ID output,
      @CHANGEAGENTID,
      @CONSTITUENTID,
      @DATE,
      @AMOUNT,
      @POSTSTATUSCODE,
      @POSTDATE,
      @SENDPLEDGEREMINDER,
      @SPLITS,
      @FREQUENCYCODE,
      @NUMBEROFINSTALLMENTS,
      @STARTDATE,
      @INSTALLMENTS,
      @AUTOPAY,
      @PAYMENTMETHODCODE,
      @CARDHOLDERNAME,
      @CREDITCARDNUMBER,
      @CREDITTYPECODEID,
      @EXPIRESON,
      @REFERENCEDATE,
      @REFERENCENUMBER,
      @CONSTITUENTACCOUNTID,
      @FINDERNUMBER,
      @SOURCECODE,
      @APPEALID,
      @BENEFITS,
      @BENEFITSWAIVED,
      @GIVENANONYMOUSLY,
      @MAILINGID,
      @CHANNELCODEID,
      @DONOTACKNOWLEDGE,
      @PLEDGESUBTYPEID,
      @BATCHNUMBER,
      @OPPORTUNITYID,
      @REFERENCE,
      @CATEGORYCODEID,
      @CREDITCARDTOKEN,
      @STANDINGORDERSETUP,
      @STANDINGORDERSETUPDATE,
      @DDISOURCECODEID,
      @DDISOURCEDATE,
      @SPLITSDECLININGGIFTAID output,
      @PERCENTAGEBENEFITS,
      @TRANSACTIONCURRENCYID,
      @BASECURRENCYID,
      @BASEEXCHANGERATEID,
      @PDACCOUNTSYSTEMID,
      0, -- business units applied

      @GENERATEREFERENCENUMBER,
      @STANDINGORDERREFERENCENUMBER,
      @EVENTID,
      @LOCALCORPID,
      0, --ISMEMBERSHIPPLEDGE

      @SEPAMANDATEID

    exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 1, @SPLITSDECLININGGIFTAID; --revenue transaction type code of pledge is 1


    insert into dbo.PLEDGEINSTALLMENTOPTION
        (ID
        ,INSTALLMENTAMOUNT
        ,SPLITSCHEDULEOPTIONCODE
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED)
    values (@ID
        ,case @FREQUENCYCODE when 4 then null else @INSTALLMENTAMOUNT end
        ,@INSTALLMENTSPLITSCHEDULEOPTIONCODE
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE);

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

  return 0;
end