USP_DATAFORMTEMPLATE_EDITLOAD_BATCHMEMBERSHIPDUESBATCHROW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_BATCHMEMBERSHIPDUESBATCHROW
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SEQUENCE int = null output,
  @BILLTOCONSTITUENTID uniqueidentifier = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @BASEEXCHANGERATEID uniqueidentifier = null output,
  @EXCHANGERATE decimal(20,8) = null output,
  @BASEAMOUNT money = null output,
  @PDACCOUNTSYSTEMID uniqueidentifier = null output,
  @POSTSTATUSCODE tinyint = null output,
  @POSTDATE date = null output,
  @DUESTYPECODE tinyint = null output,
  @MEMBERSHIPRECIPIENT uniqueidentifier = null output,
  @RENEWALRECIPIENT tinyint = null output,
  @EFFORTID uniqueidentifier = null output,
  @FINDERNUMBER nvarchar(19) = null output,
  @APPEALID uniqueidentifier = null output,
  @CHANNELCODEID uniqueidentifier = null output,
  @DATE date = null output,
  @WHATPAYINGFOR tinyint = null output,                       -- Read only field, included to make batch happy...

  @MEMBERSHIPTRANSACTIONTYPECODE tinyint = null output,       --0 = Add, 1 = Renew, 2 = Pay

  @REVENUETYPECODE tinyint = null output,
  @PAYADDITIONALTONEXTINSTALLMENT bit = null output,
  @PAYADDITIONALMONEYAMOUNT money = null output,
  @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
  @MEMBERSHIPLEVELID uniqueidentifier = null output,
  @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
  @MEMBERSHIPEXPIRESONDATE date = null output,
  @MEMBERSHIPAMOUNT money = null output,
  @MEMBERSHIPTRANSACTIONAMOUNT money = null output,
  @CONTRIBUTORYDESIGNATIONID uniqueidentifier = null output,
  @EXISTINGMEMBERSHIPID uniqueidentifier = null output,
  @MEMBERSHIPPLEDGEAMOUNT money = null output,
  @USEDISCOUNTRADIO tinyint = null output,
  @DISCOUNTTYPE uniqueidentifier = null output,
  @PROMOTIONCODE nvarchar(50) = null output,
  @APPLIEDDISCOUNTID uniqueidentifier = null output,

  @ADDDONATION bit = null output,
  @DONATIONAMOUNT money = null output,
  @GIVENANONYMOUSLY bit = null output,
  @DONATIONOPPORTUNITYID uniqueidentifier = null output,
  @DONATIONDESIGNATIONID uniqueidentifier = null output,
  @DONATIONCATEGORYCODEID uniqueidentifier = null output,
  @DECLINESGIFTAID bit = null output,

  @PLEDGEFREQUENCYCODE tinyint = null output,
  @PLEDGENUMBEROFINSTALLMENTS int = null output,
  @PLEDGESTARTDATE datetime = null output,
  @AUTOPAY bit = null output,
  @SENDPLEDGEREMINDER bit = null output,

  @CHECKDATE dbo.UDT_FUZZYDATE = null output,
  @CHECKNUMBER nvarchar(20) = null output,
  @REFERENCENUMBER nvarchar(20) = null output,
  @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
  @PAYMENTMETHODCODE tinyint = null output,

  @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
  @DIRECTDEBITISREJECTED bit = null output,

  @CONSTITUENTACCOUNTID uniqueidentifier = null output,
  @REFERENCE nvarchar(255) = null output,
  @AUTOMATICALLYRENEWMEMBERSHIP bit = null output,
  @CREDITCARDID uniqueidentifier = null output,
  @CREDITCARDNUMBER nvarchar(20) = null output,
  @CREDITCARDTOKEN uniqueidentifier = null output,
  @CARDHOLDERNAME nvarchar(255) = null output,
  @EXPIRESON dbo.UDT_FUZZYDATE = null output,
  @AUTHORIZATIONCODE nvarchar(20) = null output,
  @REJECTIONMESSAGE nvarchar(500) = null output,
  @CREDITTYPECODEID uniqueidentifier = null output,
  @TRANSACTIONID uniqueidentifier = null output,
  @PARTIALCREDITCARDNUMBER nvarchar(4) = null output,
  @DONOTACKNOWLEDGE bit = null output,
  @TAXDEDUCTIBLEAMOUNT money = null output,
  @LETTERCODEID uniqueidentifier = null output,
  @TRIBUTEID uniqueidentifier = null output,
  --@NEWEVENTREGISTRATION bit = null output,

  @DONOTRECEIPT bit = null output,
  @COMMENTS nvarchar(255) = null output,
  @TOTALAMOUNT money = null output,

  @EXISTINGMEMBERS xml = null output,
  @EXISTINGCHILDREN xml = null output,
  @MEMBERSHIPCARDS xml = null output,

  @MEMBERSHIPRECOGNITION xml = null output,
  @MEMBERSHIPPROGRAMADDON xml = null output,
  @IMPORTADDON xml = null output,

  @CAMPAIGNS xml = null output,
  @SOLICITORS xml = null output,
  @RECOGNITIONS xml = null output,

  @BENEFITS xml = null output,
  @PERCENTAGEBENEFITS xml = null output,

  @INSTALLMENTS xml = null output,
  @IMPORT bit = null output,
  @NUMBEROFCHILDREN smallint = null output, -- Temporary workaround for children not being implemented in 2012 Q1

  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
  @SOURCECODE nvarchar(50) = null output,
  @MEMBERSHIPDECLINESGIFTAID bit = null output,
  @DDISOURCECODEID uniqueidentifier = null output,
  @DDISOURCEDATE date = null output,
  @VENDORID nvarchar(50) = null output,
  @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null output,
  @CREDITCARDATTEMPTCOUNT tinyint = null output,
  @CURRENTBATCHROWID uniqueidentifier = null output,
  @NEWCONSTITUENT xml = null output,
  @BBNCTRANID int = null output,
  @ORIGINPAGEID int = null output,
  @ORIGINPAGE nvarchar(100) = null output,
  @BBNCID int = null output,
  @BBISPROCESSORID uniqueidentifier = null output,
  @EDITCONSTITUENTCONTEXT nvarchar(110) = null output,
  @NAMECODE tinyint = null output,
  @SIMILARADDRESSCODE tinyint = null output,
  @UNSIMILARADDRESSCODE tinyint = null output,
  @NEWADDRESSENDDATECODE tinyint = null output,
  @NEWADDRESSPRIMARYCODE tinyint = null output,
  @BIRTHDATERULECODE  tinyint = null output,
  @DIFFERENTPHONECODE tinyint = null output,
  @NEWPHONEENDDATECODE tinyint = null output,
  @NEWPHONEPRIMARYCODE tinyint = null output,
  @DIFFERENTEMAILCODE tinyint = null output,
  @NEWEMAILENDDATECODE tinyint = null output,
  @NEWEMAILPRIMARYCODE tinyint = null output,
  @USEGLOBALSETTINGS bit = null output,
  @DOMANUALREVIEWFORAUTOMATCH bit = null output,
  @CREATEHISTORICALNAMECODE tinyint = null output,
  @CONSTITUENTHASEDITS bit = null output,
  @ISEXISTINGCONSTITUENT bit = null output,
  @SEPAMANDATEID uniqueidentifier = null output,
  @ADDSEPAMANDATE bit = null output,
  @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35) = null output,
  @SEPAMANDATESIGNATUREDATE date = null output,
  @SEPAMANDATETYPECODE tinyint = null output,
  @REQUIRECREDITCARDPROCESSING bit = null output,
  @PAYOTHERAMOUNT money = null output,
  @ISGENERATEDPAYMENT bit = null output,
  @GLREVENUECATEGORYMAPPINGID uniqueidentifier = null output,
  @SOLICITCODES xml = null output
)
as
begin
  set nocount on;

  -- be sure to set these, in case the select returns no rows

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @ID = BATCHMEMBERSHIPDUES.ID,
    @DATALOADED = 1,
    @SEQUENCE = SEQUENCE,
    @BILLTOCONSTITUENTID = BILLTOCONSTITUENTID,

    @DUESTYPECODE = DUESTYPECODE,     -- Paying, giving, comping

    @MEMBERSHIPRECIPIENT = MEMBERSHIPRECIPIENTID,
    @RENEWALRECIPIENT = RENEWALRECIPIENTCODE,
    @FINDERNUMBER = FINDERNUMBER,
    @APPEALID = APPEALID,
    @EFFORTID = EFFORTID,
    @CHANNELCODEID = CHANNELCODEID,
    @DATE = DATE,
    @WHATPAYINGFOR = 0,       -- Set to default value

    @MEMBERSHIPTRANSACTIONTYPECODE = MEMBERSHIPTRANSACTIONTYPECODE,
    @REVENUETYPECODE = REVENUETYPECODE,
    @PAYADDITIONALTONEXTINSTALLMENT = PAYADDITIONALTONEXTINSTALLMENT,
    @PAYADDITIONALMONEYAMOUNT = PAYADDITIONALMONEYAMOUNT,
    @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID,
    @MEMBERSHIPLEVELID = MEMBERSHIPLEVELID,
    @MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERMID,
    @MEMBERSHIPEXPIRESONDATE = MEMBERSHIPEXPIRESONDATE,
    @MEMBERSHIPAMOUNT = MEMBERSHIPAMOUNT,
    @MEMBERSHIPTRANSACTIONAMOUNT = MEMBERSHIPTRANSACTIONAMOUNT,
    @EXISTINGMEMBERSHIPID = EXISTINGMEMBERSHIPID,
    @MEMBERSHIPPLEDGEAMOUNT = MEMBERSHIPPLEDGEAMOUNT,
    @CONTRIBUTORYDESIGNATIONID = CONTRIBUTORYDESIGNATIONID,
    @USEDISCOUNTRADIO = USEDISCOUNT,
 @DISCOUNTTYPE = MEMBERSHIPPROMOID,
    @PROMOTIONCODE = PROMOTIONCODE,
    @APPLIEDDISCOUNTID = APPLIEDDISCOUNTID,
    @ADDDONATION = ADDDONATION,
    @DONATIONAMOUNT = DONATIONAMOUNT,
    @GIVENANONYMOUSLY = DONATIONGIVENANONYMOUSLY,
    @DONATIONOPPORTUNITYID = DONATIONOPPORTUNITYID,
    @DONATIONDESIGNATIONID = DONATIONSINGLEDESIGNATIONID,
    @DONATIONCATEGORYCODEID = DONATIONCATEGORYCODEID,
    @DECLINESGIFTAID = DONATIONDECLINESGIFTAID,
    @PLEDGEFREQUENCYCODE = PLEDGEFREQUENCYCODE,
    @PLEDGENUMBEROFINSTALLMENTS = PLEDGENUMBEROFINSTALLMENTS,
    @PLEDGESTARTDATE = PLEDGESTARTDATE,
    @AUTOPAY = AUTOPAY,
    @SENDPLEDGEREMINDER = SENDPLEDGEREMINDER,
    @CHECKDATE = CHECKDATE,
    @CHECKNUMBER = CHECKNUMBER,
    @REFERENCENUMBER = REFERENCENUMBER,
    @REFERENCEDATE = REFERENCEDATE,

    @DIRECTDEBITRESULTCODE = DIRECTDEBITRESULTCODE,
    @DIRECTDEBITISREJECTED = DIRECTDEBITISREJECTED,

    @REFERENCE = REFERENCE,
    @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID,
    @PAYMENTMETHODCODE = PAYMENTMETHODCODE,
    @AUTOMATICALLYRENEWMEMBERSHIP = AUTOMATICALLYRENEWMEMBERSHIP,

    @CREDITCARDID = BATCHMEMBERSHIPDUES.CREDITCARDID,
    @CARDHOLDERNAME = CREDITCARD.CARDHOLDERNAME,
    @CREDITCARDNUMBER = CREDITCARD.CREDITCARDPARTIALNUMBER,
    @CREDITCARDTOKEN = CREDITCARD.CREDITCARDTOKEN,
    @CREDITTYPECODEID = CREDITCARD.CREDITTYPECODEID,
    @TRANSACTIONID = BATCHMEMBERSHIPDUES.TRANSACTIONID,
    @AUTHORIZATIONCODE = BATCHMEMBERSHIPDUES.AUTHORIZATIONCODE,
    @REJECTIONMESSAGE = BATCHMEMBERSHIPDUES.REJECTIONMESSAGE,
    @EXPIRESON = CREDITCARD.EXPIRESON,

    @DONOTACKNOWLEDGE = DONOTACKNOWLEDGE,
    @TAXDEDUCTIBLEAMOUNT = TAXDEDUCTIBLEAMOUNT,
    @LETTERCODEID = LETTERCODEID,
    @TRIBUTEID = TRIBUTEID,
    @DONOTRECEIPT = DONOTRECEIPT,
    @COMMENTS = COMMENTS,
    @TOTALAMOUNT = TOTALAMOUNT,
    @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID,
    @BASECURRENCYID = BASECURRENCYID,
    @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
    @BASEEXCHANGERATEID = BASEEXCHANGERATEID,
    @EXCHANGERATE = EXCHANGERATE,

    @EXISTINGMEMBERS = dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERS_TOITEMLISTXML(@ID, case BATCHMEMBERSHIPDUES.DUESTYPECODE when 1 then BATCHMEMBERSHIPDUES.MEMBERSHIPRECIPIENTID else BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID end),
    @EXISTINGCHILDREN = dbo.UFN_MEMBERSHIPDUESBATCH_GETCHILDREN_TOITEMLISTXML(@ID, case BATCHMEMBERSHIPDUES.DUESTYPECODE when 1 then BATCHMEMBERSHIPDUES.MEMBERSHIPRECIPIENTID else BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID end),

    @MEMBERSHIPPROGRAMADDON = dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERSHIPPROGRAMADDONS_TOITEMLISTXML(@ID, MEMBERSHIPPROGRAMID, TRANSACTIONCURRENCYID, DATE),

    @IMPORT = 0,
    @NUMBEROFCHILDREN = NUMBEROFCHILDREN,
    @OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODEID,
    @POSTSTATUSCODE = POSTSTATUSCODE,
    @POSTDATE = POSTDATE,
    @SOURCECODE = SOURCECODE,
    @MEMBERSHIPDECLINESGIFTAID = MEMBERSHIPDECLINESGIFTAID,
    @DDISOURCECODEID = DDISOURCECODEID,
    @DDISOURCEDATE = DDISOURCEDATE,
    @VENDORID = VENDORID,
    @MEMBERSHIPLEVELTYPECODEID = MEMBERSHIPLEVELTYPECODEID,
    @CREDITCARDATTEMPTCOUNT = CREDITCARDATTEMPTCOUNT,
    @CURRENTBATCHROWID = BATCHMEMBERSHIPDUES.ID,
    @BBNCTRANID = BMBI.BBNCTRANID,
    @ORIGINPAGEID = BMBI.PAGEID,
    @ORIGINPAGE = BMBI.PAGENAME,
    @BBNCID = BMBI.BBNCID,
    @BBISPROCESSORID = BMBI.NETCOMMUNITYTRANSACTIONPROCESSORID,

    @NAMECODE = BATCHMEMBERSHIPDUES.NAMECODE,
    @SIMILARADDRESSCODE = BATCHMEMBERSHIPDUES.SIMILARADDRESSCODE,
    @UNSIMILARADDRESSCODE = BATCHMEMBERSHIPDUES.UNSIMILARADDRESSCODE,
    @NEWADDRESSENDDATECODE = BATCHMEMBERSHIPDUES.NEWADDRESSENDDATECODE,
    @NEWADDRESSPRIMARYCODE = BATCHMEMBERSHIPDUES.NEWADDRESSPRIMARYCODE,
    @BIRTHDATERULECODE  = BATCHMEMBERSHIPDUES.BIRTHDATERULECODE,
    @DIFFERENTPHONECODE = BATCHMEMBERSHIPDUES.DIFFERENTPHONECODE,
    @NEWPHONEENDDATECODE = BATCHMEMBERSHIPDUES.NEWPHONEENDDATECODE,
    @NEWPHONEPRIMARYCODE = BATCHMEMBERSHIPDUES.NEWPHONEPRIMARYCODE,
    @DIFFERENTEMAILCODE = BATCHMEMBERSHIPDUES.DIFFERENTEMAILCODE,
    @NEWEMAILENDDATECODE = BATCHMEMBERSHIPDUES.NEWEMAILENDDATECODE,
    @NEWEMAILPRIMARYCODE = BATCHMEMBERSHIPDUES.NEWEMAILPRIMARYCODE,
    @USEGLOBALSETTINGS = BATCHMEMBERSHIPDUES.USEGLOBALSETTINGS,
    @CREATEHISTORICALNAMECODE = BATCHMEMBERSHIPDUES.CREATEHISTORICALNAMECODE,
    @EDITCONSTITUENTCONTEXT = cast(BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID as nvarchar(36)) + '|' +cast(BATCHMEMBERSHIPDUES.BATCHID as nvarchar(36)) +  '|' + cast(BATCHMEMBERSHIPDUES.ID as nvarchar(36)),
    @SEPAMANDATEID = BATCHMEMBERSHIPDUES.SEPAMANDATEID,
    @REQUIRECREDITCARDPROCESSING = BATCHMEMBERSHIPDUES.REQUIRECREDITCARDPROCESSING,
    @PAYOTHERAMOUNT = BATCHMEMBERSHIPDUES.MEMBERSHIPTRANSACTIONAMOUNT,
    @ISGENERATEDPAYMENT = BATCHMEMBERSHIPDUES.ISGENERATEDPAYMENT,
    @GLREVENUECATEGORYMAPPINGID = BATCHMEMBERSHIPDUES.GLREVENUECATEGORYMAPPINGID
  from dbo.BATCHMEMBERSHIPDUES
  left join dbo.CREDITCARD on BATCHMEMBERSHIPDUES.CREDITCARDID = CREDITCARD.ID
  left join dbo.BATCHMEMBERSHIPDUESBBNCINFO BMBI on BATCHMEMBERSHIPDUES.ID = BMBI.BATCHMEMBERSHIPDUESID
  where BATCHMEMBERSHIPDUES.ID = @ID;

  set @MEMBERSHIPCARDS = dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERSHIPCARDS_TOITEMLISTXML(@ID);
  set @MEMBERSHIPRECOGNITION = dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERSHIPRECOGNITIONS_TOITEMLISTXML(@ID);
  set @CAMPAIGNS = dbo.UFN_MEMBERSHIPDUESBATCH_GETDONATIONCAMPAIGNS_TOITEMLISTXML(@ID);
  set @SOLICITORS = dbo.UFN_MEMBERSHIPDUESBATCH_GETDONATIONSOLICITORS_TOITEMLISTXML(@ID);
  set @RECOGNITIONS = dbo.UFN_MEMBERSHIPDUESBATCH_GETDONATIONRECOGNITIONS_TOITEMLISTXML(@ID);
  set @BENEFITS = dbo.UFN_MEMBERSHIPDUESBATCH_GETBENEFITS_TOITEMLISTXML(@ID);
  set @PERCENTAGEBENEFITS = dbo.UFN_MEMBERSHIPDUESBATCH_GETPERCENTAGEBENEFITS_TOITEMLISTXML(@ID);
  set @INSTALLMENTS = dbo.UFN_MEMBERSHIPDUESBATCH_GETPLEDGEINSTALLMENTS_TOITEMLISTXML(@ID);
  set @SOLICITCODES = dbo.UFN_MEMBERSHIPDUESBATCH_GETSOLICITCODES_TOITEMLISTXML(@ID);

  select @DOMANUALREVIEWFORAUTOMATCH = DOMANUALREVIEWFORAUTOMATCH 
  from dbo.BATCHCONSTITUENTUPDATE 
  where BATCHCONSTITUENTUPDATE.ID = @ID;

  /*Because of the way this batch in particular works, the findernumber parameter is a string instead of bigint, so we need to do some extra validation and casting.*/
  if cast(@FINDERNUMBER as bigint) = 0
    set @FINDERNUMBER = null;

  set @ISEXISTINGCONSTITUENT = 0
  select @ISEXISTINGCONSTITUENT = 1
  from dbo.CONSTITUENT
  where ID = @BILLTOCONSTITUENTID;

  -- return this true if the selected constituent has edits

  select @CONSTITUENTHASEDITS = 1 from dbo.BATCHCONSTITUENTUPDATE where ID= @ID and PRIMARYRECORDID = @BILLTOCONSTITUENTID

  --Set default values for SEPA mandate fields only used in import

  set @ADDSEPAMANDATE = 0;
  set @SEPAMANDATECUSTOMIDENTIFIER = '';
  set @SEPAMANDATESIGNATUREDATE = null;
  set @SEPAMANDATETYPECODE = 0;

  return 0;
end