USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHROW

The save procedure used by the add dataform template "Sponsorship Batch Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@BATCHID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@SEQUENCE	int	IN	Sequence
@CONSTITUENTID	uniqueidentifier	IN	Corresponding sponsor
@DONORID	uniqueidentifier	IN	Financial sponsor
@PLANNEDENDDATE	datetime	IN	Expiration date
@SPONSORSHIPPROGRAMID	uniqueidentifier	IN	Sponsorship program
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN	Location
@GENDERCODE	int	IN	Gender
@SPROPPAGERANGEID	uniqueidentifier	IN	Age range
@ISHIVPOSITIVECODE	int	IN	HIV positive
@HASCONDITIONCODE	int	IN	Disability/Illness
@ISORPHANEDCODE	int	IN	Orphaned
@SPROPPPROJECTCATEGORYCODEID	uniqueidentifier	IN	Category
@SPONSORSHIPOPPORTUNITYIDCHILD	uniqueidentifier	IN	Child
@SPONSORSHIPOPPORTUNITYIDPROJECT	uniqueidentifier	IN	Project
@SPONSORSHIPOPPORTUNITYTYPECODE	tinyint	IN	Sponsorship Opportunity Type
@ISSOLESPONSORSHIP	bit	IN	Sole sponsorship
@STARTDATE	datetime	IN	Start Date
@ENDDATE	datetime	IN	End Date
@AMOUNT	money	IN	Amount
@PAYMENTMETHODCODE	tinyint	IN	Payment method
@FREQUENCYCODE	tinyint	IN	Frequency
@AUTOPAY	bit	IN	Pay installments automatically
@REFERENCEDATE	UDT_FUZZYDATE	IN	Reference date
@REFERENCENUMBER	nvarchar(20)	IN	Reference no.
@CARDHOLDERNAME	nvarchar(255)	IN	Name on card
@CREDITCARDNUMBER	nvarchar(20)	IN	Card number
@CREDITTYPECODEID	uniqueidentifier	IN	Card type
@EXPIRESON	UDT_FUZZYDATE	IN	Expires on
@CONSTITUENTACCOUNTID	uniqueidentifier	IN	Account
@REVENUESCHEDULEENDDATE	datetime	IN	Payment end date
@REVENUESCHEDULESTARTDATE	datetime	IN	Payment start date
@CREDITCARDTOKEN	uniqueidentifier	IN	Credit card token
@ISTRANSIENTCARD	bit	IN	Is transient credit card
@RESERVATIONKEYID	uniqueidentifier	IN	Reservation key
@RESERVEDOPPORTUNITYID	uniqueidentifier	IN	Reserved child
@SENDREMINDER	bit	IN	Send reminders
@FINDERNUMBER	bigint	IN	Finder number
@SOURCECODE	nvarchar(50)	IN	Source code
@APPEALID	uniqueidentifier	IN	Appeal
@MAILINGID	uniqueidentifier	IN	Effort
@CHANNELCODEID	uniqueidentifier	IN	Inbound channel
@REFERENCE	nvarchar(255)	IN	Reference
@REVENUECATEGORYCODEID	uniqueidentifier	IN	Revenue category
@ISGIFTSPONSORSHIP	bit	IN	Is gift sponsorship
@EXPIRATIONREASONID	uniqueidentifier	IN	Expiration reason
@BID	uniqueidentifier	IN	Batch helper id
@FINANCIALSPONSORLOOKUPID	uniqueidentifier	IN	Financial sponsor ID
@CORRESPONDINGSPONSORLOOKUPID	uniqueidentifier	IN	Corresponding sponsor ID
@PARTIALCREDITCARDNUMBER	nvarchar(4)	IN	Partial card number
@NEWCONSTITUENT	xml	IN	New corresponding sponsor
@NEWDONORCONSTITUENT	xml	IN	New financial sponsor
@TRANSACTIONCURRENCYID	uniqueidentifier	IN	Transaction currency
@BASECURRENCYID	uniqueidentifier	IN	Base currency
@BASEEXCHANGERATEID	uniqueidentifier	IN	Base exchange rate
@EXCHANGERATE	decimal(20, 8)	IN	Exchange rate
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@COMMITMENTID	nvarchar(100)	IN	Commitment ID
@SEPAMANDATEID	uniqueidentifier	IN
@ADDSEPAMANDATE	bit	IN
@SEPAMANDATECUSTOMIDENTIFIER	nvarchar(35)	IN
@SEPAMANDATESIGNATUREDATE	date	IN
@SEPAMANDATETYPECODE	tinyint	IN
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	IN
@IMPORT	bit	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHROW
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @BATCHID uniqueidentifier,
  @SEQUENCE int,
  @CONSTITUENTID uniqueidentifier = null,
  @DONORID uniqueidentifier = null,
  @PLANNEDENDDATE datetime = null,
  @SPONSORSHIPPROGRAMID uniqueidentifier = null,
  @SPONSORSHIPLOCATIONID uniqueidentifier = null,
  @GENDERCODE int = 0,
  @SPROPPAGERANGEID uniqueidentifier = null,
  @ISHIVPOSITIVECODE int = 0,
  @HASCONDITIONCODE int = 0,
  @ISORPHANEDCODE int = 0,
  @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null,
  @SPONSORSHIPOPPORTUNITYIDCHILD uniqueidentifier = null,
  @SPONSORSHIPOPPORTUNITYIDPROJECT uniqueidentifier = null,
  @SPONSORSHIPOPPORTUNITYTYPECODE tinyint = null,
  @ISSOLESPONSORSHIP bit = 0,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @AMOUNT money = null,
  @PAYMENTMETHODCODE tinyint = 2,
  @FREQUENCYCODE tinyint = 3,
  @AUTOPAY bit = 1,
  @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,
  @REVENUESCHEDULEENDDATE datetime = null,
  @REVENUESCHEDULESTARTDATE datetime = null,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @ISTRANSIENTCARD bit = null,
  @RESERVATIONKEYID uniqueidentifier = null,
  @RESERVEDOPPORTUNITYID uniqueidentifier = null,
  @SENDREMINDER bit = 0,
  @FINDERNUMBER bigint = null,
  @SOURCECODE nvarchar(50) = '',
  @APPEALID uniqueidentifier = null,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @REFERENCE nvarchar(255) = '',
  @REVENUECATEGORYCODEID uniqueidentifier = null,
  @ISGIFTSPONSORSHIP bit = 0,
  @EXPIRATIONREASONID uniqueidentifier = null,
  @BID uniqueidentifier = null,
  @FINANCIALSPONSORLOOKUPID uniqueidentifier = null,
  @CORRESPONDINGSPONSORLOOKUPID uniqueidentifier = null,
  @PARTIALCREDITCARDNUMBER nvarchar(4) = '', 
  @NEWCONSTITUENT xml = null,
  @NEWDONORCONSTITUENT xml = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @COMMITMENTID nvarchar(100) = '',
  @SEPAMANDATEID uniqueidentifier = null,
  @ADDSEPAMANDATE bit = 0,
  @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35) = '',
  @SEPAMANDATESIGNATUREDATE date = null,
  @SEPAMANDATETYPECODE tinyint = 0,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @IMPORT bit = 1
)
as
  set nocount on;

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

  set @BID = @ID

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  begin try
    -- handle inserting the data


    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier;
    declare @TYPECODE tinyint=0;
    declare @CREDITCARDID uniqueidentifier
    declare @NEWCOUNT int = 0;

    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;
    if @SOURCECODE is null
      set @SOURCECODE = '';
    if @ADDSEPAMANDATE is null
      set @ADDSEPAMANDATE = 0;

    -- Corresponding sponsor

    select @NEWCOUNT = count(*) from @NEWCONSTITUENT.nodes('/NEWCONSTITUENT/ITEM') T(c)
    if @NEWCOUNT > 0
    begin
      declare @NEWCONSTITUENTID uniqueidentifier;
      if not exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID)
      begin
        exec USP_SPONSORSHIPBATCH_ADDNEWCONSTITUENTFROMXML @NEWCONSTITUENT, @CHANGEAGENTID, @NEWCONSTITUENTID output;
        set @CONSTITUENTID = @NEWCONSTITUENTID;
      end;
    end;

    if @CONSTITUENTID is null
      set @CONSTITUENTID = @CORRESPONDINGSPONSORLOOKUPID


    /* Lookup and set all possible marketing data (via output params) from the data that was specified. */
    exec dbo.[USP_REVENUEBATCH_GETMARKETINGDATA]
      @FINDERNUMBER = @FINDERNUMBER,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @SOURCECODE = @SOURCECODE output,
      @MAILINGID = @MAILINGID output,
      @APPEALID = @APPEALID output,
      @CONSTITUENTID = @DONORID output,
      @BATCHID = @BATCHID,
      @IMPORT = @IMPORT;


    -- Financial sponsor 

    select @NEWCOUNT = count(*) from @NEWDONORCONSTITUENT.nodes('/NEWDONORCONSTITUENT/ITEM') T(c)
    if @NEWCOUNT > 0
    begin
      declare @NEWDONORCONSTITUENTID uniqueidentifier;
      if not exists(select ID from dbo.CONSTITUENT where ID = @DONORID)
      begin
        exec USP_SPONSORSHIPBATCH_ADDNEWCONSTITUENTFROMXML @NEWDONORCONSTITUENT, @CHANGEAGENTID, @NEWDONORCONSTITUENTID output;
        set @DONORID = @NEWDONORCONSTITUENTID;
      end;
    end;

    if @DONORID is null
      set @DONORID = @FINANCIALSPONSORLOOKUPID

    if @DONORID is null
      raiserror('BBERR_FINANCIALSPONSORREQUIRED',13,1)

    --set the corresponding sponsor id if it's not filled in.

    if @CONSTITUENTID is null and @ISGIFTSPONSORSHIP=0
      set @CONSTITUENTID = @DONORID

    if @CONSTITUENTID is null and @ISGIFTSPONSORSHIP=1
      raiserror('BBERR_CORRESPONDINGSPONSORREQUIRED',13,1)

    if @SPONSORSHIPOPPORTUNITYIDCHILD is not null and @SPONSORSHIPOPPORTUNITYIDPROJECT is not null
      raiserror('BBERR_CHILDANDPROJECTSPECIFIED',13,1)

    if @SPONSORSHIPOPPORTUNITYIDCHILD is not null 
    begin
      set @TYPECODE=1
      set @SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYIDCHILD
    end

    if @SPONSORSHIPOPPORTUNITYIDPROJECT is not null
    begin
      set @TYPECODE=2
      set @SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYIDPROJECT
    end

    if @RESERVEDOPPORTUNITYID is not null
    begin
      set @TYPECODE=1
      set @SPONSORSHIPOPPORTUNITYID = @RESERVEDOPPORTUNITYID
    end

    if @SPONSORSHIPPROGRAMID is not null and @TYPECODE = 0 
      set @TYPECODE = (select SPONSORSHIPOPPORTUNITYTYPECODE 
                       from SPONSORSHIPOPPORTUNITYGROUP SG
                       inner join SPONSORSHIPPROGRAM SP on SP.SPONSORSHIPOPPORTUNITYGROUPID = SG.ID
                       where SP.ID = @SPONSORSHIPPROGRAMID)

    if coalesce(@PARTIALCREDITCARDNUMBER, '') <> ''
      set @CREDITCARDNUMBER = @PARTIALCREDITCARDNUMBER                       

    if coalesce(@CREDITCARDNUMBER, '') <> '' or  coalesce(@CARDHOLDERNAME, '') <> '' or  @CREDITTYPECODEID is not null or coalesce(@EXPIRESON, '00000000') <> '00000000'
    begin
      if coalesce(@CREDITCARDNUMBER, '') = ''
        raiserror('BBERR_CREDITCARDNUMBERREQUIRED', 13, 1)

      if coalesce(@CARDHOLDERNAME, '') = ''
        raiserror('BBERR_CARDHOLDERREQUIRED', 13, 1)

      if coalesce(@EXPIRESON, '00000000') = '00000000'
        raiserror('BBERR_EXPIRESONREQUIRED', 13, 1)

      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,
              @ISTRANSIENT = @ISTRANSIENTCARD
      end
    end

    -- if @amount is not on the batch assign program amount by default

    if @AMOUNT = 0 or @AMOUNT is null
      select @AMOUNT = AMOUNT from dbo.SPONSORSHIPPROGRAM 
       where ID = @SPONSORSHIPPROGRAMID
         and AMOUNT <> 0

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

    if @EXCHANGERATE is null
      set @EXCHANGERATE = 0;

    if @STARTDATE is null
      set @STARTDATE = @CURRENTDATE;

    exec dbo.USP_REVENUEBATCH_SEPAMANDATE_VALIDATEORCREATE
      @SEPAMANDATEID output,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @ADDSEPAMANDATE,
      @SEPAMANDATECUSTOMIDENTIFIER,
      @SEPAMANDATESIGNATUREDATE,
      @SEPAMANDATETYPECODE,
      @PAYMENTMETHODCODE,
      @CONSTITUENTACCOUNTID,
      null,
      @TRANSACTIONCURRENCYID;

    --Clear the credit card fields before inserting them into the batch table if the payment method is not credit card - last 4 digits (98)

    --They only need to be saved to the BATCHSPONSORSHIP table if the payment method is 98 - Credit card - last 4 digits only

    if @PAYMENTMETHODCODE <> 98
    begin
      set @CARDHOLDERNAME = '';
      set @CREDITTYPECODEID = null;
      set @CREDITCARDNUMBER = '';
      set @EXPIRESON = '00000000';
    end

    insert into dbo.BATCHSPONSORSHIP (
      ID,
      BATCHID, 
      SEQUENCE, 
      CONSTITUENTID,
      DONORID,
      PLANNEDENDDATE,
      SPONSORSHIPPROGRAMID, 
      SPONSORSHIPOPPORTUNITYID, 
      SPONSORSHIPOPPORTUNITYTYPECODE, 
      STARTDATE, 
      ENDDATE, 
      SPONSORSHIPLOCATIONID, 
      CHILDGENDERCODE, 
      ISHIVPOSITIVECODE, 
      HASCONDITIONCODE, 
      ISORPHANEDCODE, 
      SPROPPPROJECTCATEGORYCODEID, 
      ISSOLESPONSORSHIP, 
      SPONSORSHIPOPPORTUNITYAGERANGEID,
      AMOUNT, 
      PAYMENTMETHODCODE,
      FREQUENCYCODE, 
      AUTOPAY, 
      REFERENCEDATE,
      REFERENCENUMBER,
      CREDITCARDID,
      CONSTITUENTACCOUNTID,
      ADDEDBYID, 
      CHANGEDBYID, 
      DATEADDED, 
      DATECHANGED,
      RESERVATIONKEYID,
      RESERVEDOPPORTUNITYID,
      SENDREMINDER,
      FINDERNUMBER,
      SOURCECODE,
      APPEALID,
      CHANNELCODEID,
      MAILINGID,
      REFERENCE,
      REVENUECATEGORYID,
      ISGIFTSPONSORSHIP,
      EXPIRATIONREASONID,
      REVENUESCHEDULEENDDATE,
      REVENUESCHEDULESTARTDATE,
      BASECURRENCYID,
      TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID,
      EXCHANGERATE,
      COMMITMENTID,
      SEPAMANDATEID,
      CARDHOLDERNAME, 
      CREDITTYPECODEID, 
      CREDITCARDPARTIALNUMBER, 
      EXPIRESON,
      OTHERPAYMENTMETHODCODEID
    ) values (
      @ID, 
      @BATCHID, 
      @SEQUENCE, 
      @CONSTITUENTID, 
      @DONORID,
      @PLANNEDENDDATE,
      @SPONSORSHIPPROGRAMID, 
      @SPONSORSHIPOPPORTUNITYID,
      @TYPECODE, 
      @STARTDATE, 
      @ENDDATE, 
      @SPONSORSHIPLOCATIONID, 
      @GENDERCODE, 
      @ISHIVPOSITIVECODE, 
      @HASCONDITIONCODE, 
      @ISORPHANEDCODE, 
      @SPROPPPROJECTCATEGORYCODEID, 
      @ISSOLESPONSORSHIP, 
      @SPROPPAGERANGEID, 
      @AMOUNT, 
      @PAYMENTMETHODCODE, 
      @FREQUENCYCODE, 
      @AUTOPAY, 
      @REFERENCEDATE,
      @REFERENCENUMBER,
      @CREDITCARDID,
      @CONSTITUENTACCOUNTID,
      @CHANGEAGENTID, 
      @CHANGEAGENTID, 
      @CURRENTDATE, 
      @CURRENTDATE,
      @RESERVATIONKEYID,
      @RESERVEDOPPORTUNITYID,
      @SENDREMINDER,
      @FINDERNUMBER,
      @SOURCECODE,
      @APPEALID,
      @CHANNELCODEID,
      @MAILINGID,
      @REFERENCE,
      @REVENUECATEGORYCODEID,
      @ISGIFTSPONSORSHIP,
      @EXPIRATIONREASONID,
      @REVENUESCHEDULEENDDATE,
      @REVENUESCHEDULESTARTDATE,
      @BASECURRENCYID,
      coalesce(@TRANSACTIONCURRENCYID,@BASECURRENCYID),
      @BASEEXCHANGERATEID,
      @EXCHANGERATE,
      coalesce(rtrim(ltrim(@COMMITMENTID)),''),
      @SEPAMANDATEID,
      @CARDHOLDERNAME, 
      @CREDITTYPECODEID, 
      @CREDITCARDNUMBER, 
      @EXPIRESON,
      @OTHERPAYMENTMETHODCODEID
    )    
  end try

  begin catch
      exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;