USP_DATAFORMTEMPLATE_EDITLOAD_REVENUEUPDATEBATCHROW

The load procedure used by the edit dataform template "Revenue Update Batch Edit Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@REVENUEID	uniqueidentifier	INOUT	Revenue
@REVENUESPLITID	uniqueidentifier	INOUT	Revenue Split
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent
@TYPECODE	tinyint	INOUT	Revenue type
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@CHECKDATE	UDT_FUZZYDATE	INOUT	Check date
@CHECKNUMBER	nvarchar(20)	INOUT	Check number
@REFERENCEDATE	UDT_FUZZYDATE	INOUT	Reference date
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference number
@CARDHOLDERNAME	nvarchar(255)	INOUT	Name on card
@CREDITCARDNUMBER	nvarchar(20)	INOUT	Card number
@CREDITTYPECODEID	uniqueidentifier	INOUT	Card type
@AUTHORIZATIONCODE	nvarchar(20)	INOUT	Authorization code
@EXPIRESON	UDT_FUZZYDATE	INOUT	Expires on
@ISSUER	nvarchar(100)	INOUT	Issuer
@NUMBEROFUNITS	decimal(20, 3)	INOUT	Stock number of units
@SYMBOL	nvarchar(25)	INOUT	Symbol
@MEDIANPRICE	decimal(19, 4)	INOUT	Median price
@PROPERTYSUBTYPECODEID	uniqueidentifier	INOUT	Property subtype
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	INOUT	Gift-in-kind subtype
@RECEIPTAMOUNT	money	INOUT	Receipt amount
@DONOTRECEIPT	bit	INOUT	Do not receipt
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT	Account
@SPLITS	xml	INOUT	Designations
@SINGLEDESIGNATIONID	uniqueidentifier	INOUT	Designation
@REVENUESTREAMS	xml	INOUT	Revenue streams
@APPLYTOSHOWNFORCONSTITUENTID	uniqueidentifier	INOUT	Apply to shown for constituent
@SEQUENCE	int	INOUT	Sequence
@INSTALLMENTFREQUENCYCODE	tinyint	INOUT	Installment frequency
@INSTALLMENTSTARTDATE	datetime	INOUT	Installment start date
@INSTALLMENTENDDATE	datetime	INOUT	Installment end date
@NUMBEROFINSTALLMENTS	int	INOUT	No. installments
@SOLICITORS	xml	INOUT	Solicitors
@BENEFITS	xml	INOUT	Money benefits
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(60)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@FINDERNUMBERISVALID	bit	INOUT	Finder number is valid
@USERMODIFIEDBENEFITS	bit	INOUT	User modified benefits
@BENEFITSWAIVED	bit	INOUT	User waived benefits
@POSTDATE	datetime	INOUT	GL post date
@POSTSTATUSCODE	tinyint	INOUT	GL post status
@SENDPLEDGEREMINDER	bit	INOUT	Send reminders
@SALEDATE	datetime	INOUT	Sale date
@SALEAMOUNT	money	INOUT	Sale amount
@BROKERFEE	money	INOUT	Sale fees
@SALEPOSTSTATUSCODE	tinyint	INOUT	Sale GL post status
@SALEPOSTDATE	datetime	INOUT	Sale GL post date
@NOTETITLE	nvarchar(50)	INOUT	Note Title
@NOTEAUTHORID	uniqueidentifier	INOUT	Note Author
@NOTEDATEENTERED	datetime	INOUT	Note Date
@NOTETYPECODEID	uniqueidentifier	INOUT	Note Type
@NOTETEXTNOTE	nvarchar(max)	INOUT	Notes
@GIFTAMOUNT	nvarchar(100)	INOUT	Gift amount
@GIVENANONYMOUSLY	bit	INOUT	Given anonymously
@GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID	uniqueidentifier	INOUT	Given anonymously defaulted for constituent
@USERMODIFIEDRECEIPTAMOUNT	bit	INOUT	User modified receipt amount
@PLEDGESUBTYPEID	uniqueidentifier	INOUT	Pledge subtype
@REJECTIONCODEID	uniqueidentifier	INOUT	Rejection code
@CONSTITUENTLOOKUPID	uniqueidentifier	INOUT	Lookup ID
@MAILINGID	uniqueidentifier	INOUT	Effort
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@INSTALLMENTS	xml	INOUT	Installments
@PAYMENTFORPLEDGEAMOUNT	money	INOUT	Payment for pledge amount
@RECOGNITIONS	xml	INOUT	Recognition credits
@DIDRECOGNITIONSDEFAULT	bit	INOUT	Did default recognition credits
@TRIBUTES	xml	INOUT	Tributes
@UNAPPLIEDMATCHINGGIFTSPLITS	xml	INOUT	Unapplied MG Split
@UNAPPLIEDMATCHINGGIFTAMOUNT	money	INOUT	Applied
@RECEIPTTYPECODE	tinyint	INOUT	Receipt type
@NEWCONSTITUENT	xml	INOUT	New constituent
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT	Other method
@LETTERCODEID	uniqueidentifier	INOUT	Letter
@ACKNOWLEDGEDATE	datetime	INOUT	Acknowledge date
@REFERENCE	nvarchar(255)	INOUT	Reference
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@ACKNOWLEDGEEID	uniqueidentifier	INOUT	Acknowledgee
@APPLICATIONINFO	nvarchar(60)	INOUT	Application
@OTHERTYPECODEID	uniqueidentifier	INOUT	Other type
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT	Result code
@LOWPRICE	decimal(19, 4)	INOUT	Low price
@HIGHPRICE	decimal(19, 4)	INOUT	High price
@NUMBEROFUNITSSOLD	decimal(20, 3)	INOUT	Stock sale number of units
@USERMODIFIEDNUMBEROFUNITSSOLD	bit	INOUT	User modified stock number of units sold
@CREDITCARDTOKEN	uniqueidentifier	INOUT	Credit card token
@REJECTIONMESSAGE	nvarchar(250)	INOUT	Rejection message
@PARTIALCREDITCARDNUMBER	nvarchar(4)	INOUT	Partial card number
@ISEXISTINGCONSTITUENT	bit	INOUT	Is existing constituent
@TAXDECLARATIONS	xml	INOUT	Declarations
@CONSTITUENTTYPE	tinyint	INOUT	Constituent type
@STANDINGORDERCONSTITUENTACCOUNTID	uniqueidentifier	INOUT	Standing order account
@STANDINGORDERREFERENCEDATE	UDT_FUZZYDATE	INOUT	Standing order reference date
@STANDINGORDERREFERENCENUMBER	nvarchar(20)	INOUT	Standing order reference number
@STANDINGORDERSETUP	bit	INOUT	Standing order has been setup
@STANDINGORDERSETUPDATE	datetime	INOUT	Standing order setup date
@TRANSACTIONID	uniqueidentifier	INOUT	Transaction ID
@ISTRANSIENTCARD	bit	INOUT	Is transient credit card
@DECLINESGIFTAID	bit	INOUT	Declines Gift Aid
@DDISOURCECODEID	uniqueidentifier	INOUT	DDI source
@DDISOURCEDATE	date	INOUT	DDI source date
@ISCOVENANT	bit	INOUT	Is covenant gift
@AMOUNTFORVAT	money	INOUT	Portion subject to VAT
@VATTAXRATEID	uniqueidentifier	INOUT	VAT tax rate
@VATAMOUNT	money	INOUT	VAT amount
@ADDITIONALAPPLICATIONSSTREAM	xml	INOUT	Additional applications stream
@REVENUELOOKUPID	nvarchar(100)	INOUT	Revenue ID
@APPLICATIONSOLICITORS	xml	INOUT	Application solicitors
@APPLICATIONRECOGNITIONS	xml	INOUT	Application recognitions
@MATCHINGGIFTS	xml	INOUT	Matching gifts
@MGGENERATED	bit	INOUT	MG Generated
@MGALTERED	bit	INOUT	MG Altered
@PAYINGPENDINGREVENUEID	uniqueidentifier	INOUT	Was paying pending revenue
@GIFTINKINDITEMNAME	nvarchar(100)	INOUT	Item name
@GIFTINKINDDISPOSITIONCODE	tinyint	INOUT	Disposition
@GIFTINKINDNUMBEROFUNITS	int	INOUT	Gift-in-kind number of units
@GIFTINKINDFAIRMARKETVALUE	money	INOUT	Fair market value per unit
@DIRECTDEBITISREJECTED	bit	INOUT	Direct debit is rejected
@PERCENTAGEBENEFITS	xml	INOUT	Percent benefits
@ISGIFTAIDSPONSORSHIP	bit	INOUT	Gift Aid sponsorship
@OTHERCONSTITUENTAPPLICATIONINFO	nvarchar(60)	INOUT	Application
@LOCKBOXID	uniqueidentifier	INOUT	Lockbox
@LOCKBOXBATCHNUMBER	nvarchar(100)	INOUT	Lockbox batch number
@LOCKBOXBATCHSEQUENCE	int	INOUT	Lockbox batch sequence
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT	Account system
@ISADJUSTMENT	bit	INOUT	ISADJUSTMENT
@ADJUSTMENTDATE	datetime	INOUT	Adjustment date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjustment post date
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment details
@ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Adjustment post status
@REVENUEDEVELOPMENTFUNCTIONCODEID	uniqueidentifier	INOUT	Revenue function
@ORIGINALAPPLICATIONINFO	nvarchar(60)	INOUT	ORIGINALAPPLICATIONINFO
@RELOADRECOGNITION	bit	INOUT	Reload recognition credit
@RELOADSOLICITORS	bit	INOUT	Reload solicitors
@LETTERS	xml	INOUT	Letters
@APPLICATIONBUSINESSUNITS	xml	INOUT	Application business units
@NOTES	xml	INOUT	Notes
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Base exchange rate
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@BASEAMOUNT	money	INOUT	Base amount
@ISSPONSORSHIPRG	bit	INOUT	Is sponsorship RG
@LASTPAYMENTSEQUENCE	int	INOUT	Installment sequence of last payment
@LASTUNPAIDROW	int	INOUT	Sequence of last unpaid installment.
@GENERATEREFERENCENUMBER	bit	INOUT	Automatically generate reference number
@UPDATEMATCHINGGIFTCLAIMS	tinyint	INOUT	Update matching gift claims
@ORIGINALCONSTITUENTID	uniqueidentifier	INOUT	Original constituent
@UPDATEGIFTFEES	bit	INOUT	Update gift fees
@ORIGINALREVENUEAMOUNT	money	INOUT	Original revenue amount
@ORIGINALTRIBUTES	xml	INOUT	Tributes
@UPDATETRIBUTES	bit	INOUT	Update tributes
@INSTALLMENTSCHEDULESEEDDATE	datetime	INOUT	Installment next transaction
@APPLYBYPERCENT	bit	INOUT	Apply by
@HASPOSTEDPAYMENTS	bit	INOUT
@ORIGINALINSTALLMENTS	xml	INOUT
@ADJPAYMENT_DATE	datetime	INOUT
@ADJPAYMENT_POSTDATE	datetime	INOUT
@ADJPAYMENT_REASONCODEID	uniqueidentifier	INOUT
@ADJPAYMENT_DETAILS	nvarchar(255)	INOUT
@ADJPAYMENT_FORMSHOWN	bit	INOUT
@ADJPAYMENT_ORIGINALPOSTDATE	date	INOUT
@DEPOSITID	uniqueidentifier	INOUT
@SALE_LOWPRICE	decimal(19, 4)	INOUT
@SALE_MEDIANPRICE	decimal(19, 4)	INOUT
@SALE_HIGHPRICE	decimal(19, 4)	INOUT
@VENDORID	nvarchar(50)	INOUT
@PLEDGEAMOUNTPAID	money	INOUT
@HASSOLDFIXEDASSETS	bit	INOUT
@GIFTAIDCOMMITTED	bit	INOUT
@RGSTATUSCODE	tinyint	INOUT
@INSTALLMENTAMOUNT	money	INOUT
@ADJPAYMENT_REVENUECATEGORYCHANGED	bit	INOUT
@ADJPAYMENT_POSTDATEDEPOSIT	date	INOUT
@PROMPTONADJUSTMENTDATECHANGE	bit	INOUT
@HASCHANGEDBYGRUB	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REVENUEUPDATEBATCHROW
(
  @ID uniqueidentifier,
  @REVENUEID uniqueidentifier = null output,
  @REVENUESPLITID uniqueidentifier = null output,
  @DATALOADED bit = 0 output,
  @CONSTITUENTID uniqueidentifier = null output,
  @TYPECODE tinyint = null output,
  @DATE datetime = null output,
  @AMOUNT money = null output,
  @PAYMENTMETHODCODE tinyint = null output,
  @DONOTACKNOWLEDGE bit = null output,
  @CHECKDATE dbo.UDT_FUZZYDATE = null output,
  @CHECKNUMBER nvarchar(20) = null output,
  @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
  @REFERENCENUMBER nvarchar(20) = null output,
  @CARDHOLDERNAME nvarchar(255) = null output,
  @CREDITCARDNUMBER nvarchar(20) = null output,
  @CREDITTYPECODEID uniqueidentifier = null output,
  @AUTHORIZATIONCODE nvarchar(20) = null output,
  @EXPIRESON dbo.UDT_FUZZYDATE = null output,
  @ISSUER nvarchar(100) = null output,
  @NUMBEROFUNITS decimal(20,3) = null output,
  @SYMBOL nvarchar(25) = null output,
  @MEDIANPRICE decimal(19,4) = null output,
  @PROPERTYSUBTYPECODEID uniqueidentifier = null output,
  @GIFTINKINDSUBTYPECODEID uniqueidentifier = null output,
  @RECEIPTAMOUNT money = null output,
  @DONOTRECEIPT bit = null output,
  @CONSTITUENTACCOUNTID uniqueidentifier = null output,
  @SPLITS xml = null output,
  @SINGLEDESIGNATIONID uniqueidentifier = null output,
  @REVENUESTREAMS xml = null output,
  @APPLYTOSHOWNFORCONSTITUENTID uniqueidentifier = null output,
  @SEQUENCE int = null output,
  @INSTALLMENTFREQUENCYCODE tinyint = null output,
  @INSTALLMENTSTARTDATE datetime = null output,
  @INSTALLMENTENDDATE datetime = null output,
  @NUMBEROFINSTALLMENTS int = null output,
  --Solicitors field is no longer in use (APPLICATIONSOLICITORS is current)

  @SOLICITORS xml = null output,
  @BENEFITS xml = null output,
  @FINDERNUMBER bigint = null output,
  @SOURCECODE nvarchar(60) = null output,
  @APPEALID uniqueidentifier = null output,
  @FINDERNUMBERISVALID bit = null output,
  @USERMODIFIEDBENEFITS bit = null output,
  @BENEFITSWAIVED bit = null output,
  @POSTDATE datetime = null output,
  @POSTSTATUSCODE tinyint = null output,
  @SENDPLEDGEREMINDER bit = null output,
  @SALEDATE datetime = null output,
  @SALEAMOUNT money = null output,
  @BROKERFEE money = null output,
  @SALEPOSTSTATUSCODE tinyint = null output,
  @SALEPOSTDATE datetime = null output,
  @NOTETITLE nvarchar(50) = null output,
  @NOTEAUTHORID uniqueidentifier = null output,
  @NOTEDATEENTERED datetime = null output ,
  @NOTETYPECODEID uniqueidentifier = null output,
  @NOTETEXTNOTE nvarchar(max) = null output,
  @GIFTAMOUNT nvarchar(100) = null output,
  @GIVENANONYMOUSLY bit = null output,
  @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID uniqueidentifier = null output,
  @USERMODIFIEDRECEIPTAMOUNT bit = null output,
  @PLEDGESUBTYPEID uniqueidentifier = null output,
  @REJECTIONCODEID uniqueidentifier = null output,
  @CONSTITUENTLOOKUPID uniqueidentifier = null output,
  @MAILINGID uniqueidentifier = null output,
  @CHANNELCODEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @INSTALLMENTS xml = null output,
  @PAYMENTFORPLEDGEAMOUNT money = null output,
  --Recognitions field is no longer in use (APPLICATIONRECOGNITIONS is current)

  @RECOGNITIONS xml = null output,
  @DIDRECOGNITIONSDEFAULT bit = null output,
  @TRIBUTES xml = null output,
  @UNAPPLIEDMATCHINGGIFTSPLITS xml = null output,   
  @UNAPPLIEDMATCHINGGIFTAMOUNT money = null output,
  @RECEIPTTYPECODE tinyint = null output,
  @NEWCONSTITUENT xml = null output,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
  @LETTERCODEID uniqueidentifier = null output,
  @ACKNOWLEDGEDATE datetime = null output,
  @REFERENCE nvarchar(255) = null output,
  @CATEGORYCODEID uniqueidentifier = null output,
  @ACKNOWLEDGEEID uniqueidentifier = null output,
  @APPLICATIONINFO nvarchar(60) = null output,
  @OTHERTYPECODEID uniqueidentifier = null output,
  @OPPORTUNITYID uniqueidentifier = null output,
  @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
  @LOWPRICE decimal(19,4) = null output,
  @HIGHPRICE decimal(19,4) = null output,
  @NUMBEROFUNITSSOLD decimal(20,3) = null output,
  @USERMODIFIEDNUMBEROFUNITSSOLD bit = null output,
  @CREDITCARDTOKEN uniqueidentifier = null output,
  @REJECTIONMESSAGE nvarchar(250) = null output,
  -- PARTIALCREDITCARDNUMBER is used to support import and 

  -- credit card - last 4 digits recurring gifts

  @PARTIALCREDITCARDNUMBER nvarchar(4) = null output,
  @ISEXISTINGCONSTITUENT bit = null output,
  @TAXDECLARATIONS xml = null output,
  @CONSTITUENTTYPE tinyint = null output,
  @STANDINGORDERCONSTITUENTACCOUNTID uniqueidentifier = null output,
  @STANDINGORDERREFERENCEDATE dbo.UDT_FUZZYDATE = null output,
  @STANDINGORDERREFERENCENUMBER nvarchar(20) = null output,
  @STANDINGORDERSETUP bit = null output,
  @STANDINGORDERSETUPDATE datetime = null output,
  @TRANSACTIONID uniqueidentifier = null output,
  @ISTRANSIENTCARD bit = null output, -- Isn't set since it's write-only

  @DECLINESGIFTAID bit = null output,
  @DDISOURCECODEID uniqueidentifier = null output,
  @DDISOURCEDATE date = null output,
  @ISCOVENANT bit = null output,
  @AMOUNTFORVAT money = null output,
  @VATTAXRATEID uniqueidentifier = null output,
  @VATAMOUNT money = null output,
  @ADDITIONALAPPLICATIONSSTREAM xml = null output,
  @REVENUELOOKUPID nvarchar(100) = null output,
  @APPLICATIONSOLICITORS xml = null output,
  @APPLICATIONRECOGNITIONS xml = null output,
  @MATCHINGGIFTS xml = null output,
  @MGGENERATED bit = null output,
  @MGALTERED bit = null output,
  @PAYINGPENDINGREVENUEID uniqueidentifier = null output,
  @GIFTINKINDITEMNAME nvarchar(100) = null output,
  @GIFTINKINDDISPOSITIONCODE tinyint = null output,
  @GIFTINKINDNUMBEROFUNITS int = null output,
  @GIFTINKINDFAIRMARKETVALUE money = null output,
  @DIRECTDEBITISREJECTED bit = null output,
  @PERCENTAGEBENEFITS xml = null output,
  @ISGIFTAIDSPONSORSHIP bit = null output,
  @OTHERCONSTITUENTAPPLICATIONINFO nvarchar(60) = null output,
  @LOCKBOXID uniqueidentifier = null output,
  @LOCKBOXBATCHNUMBER nvarchar(100) = null output,
  @LOCKBOXBATCHSEQUENCE int = null output,
  @PDACCOUNTSYSTEMID uniqueidentifier = null output,
  @ISADJUSTMENT bit = null output,
  @ADJUSTMENTDATE datetime = null output,
  @ADJUSTMENTPOSTDATE datetime = null output,
  @ADJUSTMENTREASON nvarchar(300) = null output,
  @ADJUSTMENTREASONCODEID uniqueidentifier = null output,
  @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
  @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier = null output,
  @ORIGINALAPPLICATIONINFO nvarchar(60) = null output,
  @RELOADRECOGNITION bit = null output,
  @RELOADSOLICITORS bit = null output,
  @LETTERS xml = null output,
  @APPLICATIONBUSINESSUNITS xml = null output,
  @NOTES xml = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @BASEEXCHANGERATEID uniqueidentifier = null output,
  @EXCHANGERATE decimal(20,8) = null output,
  @BASEAMOUNT money = null output,
  @ISSPONSORSHIPRG bit = null output,
  @LASTPAYMENTSEQUENCE int = null output,
  @LASTUNPAIDROW int = null output,
  @GENERATEREFERENCENUMBER bit = null output,
  @UPDATEMATCHINGGIFTCLAIMS tinyint = null output,
  @ORIGINALCONSTITUENTID uniqueidentifier = null output,
  @UPDATEGIFTFEES bit = null output,
  @ORIGINALREVENUEAMOUNT money = null output,
  @ORIGINALTRIBUTES xml = null output,
  @UPDATETRIBUTES bit = null output,
  @INSTALLMENTSCHEDULESEEDDATE datetime = null output,
  @APPLYBYPERCENT bit= null output,
  @HASPOSTEDPAYMENTS bit = null output,
  @ORIGINALINSTALLMENTS xml = null output,
  @ADJPAYMENT_DATE datetime = null output,
  @ADJPAYMENT_POSTDATE datetime = null output,
  @ADJPAYMENT_REASONCODEID uniqueidentifier  = null output,
  @ADJPAYMENT_DETAILS nvarchar(255) = null output,
  @ADJPAYMENT_FORMSHOWN bit = null output,
  @ADJPAYMENT_ORIGINALPOSTDATE date = null output,
  @DEPOSITID uniqueidentifier = null output,
  @SALE_LOWPRICE decimal(19,4) = null output,
  @SALE_MEDIANPRICE decimal(19,4) = null output,
  @SALE_HIGHPRICE decimal(19,4) = null output,
  @VENDORID nvarchar(50) = null output,
  @PLEDGEAMOUNTPAID money = null output,
  @HASSOLDFIXEDASSETS bit = null output,
  @GIFTAIDCOMMITTED bit = null output,
  @RGSTATUSCODE tinyint = null output,
  @INSTALLMENTAMOUNT money = null output,
  @ADJPAYMENT_REVENUECATEGORYCHANGED bit = null output,
  @ADJPAYMENT_POSTDATEDEPOSIT date = null output,
  @PROMPTONADJUSTMENTDATECHANGE bit = null output,
  @HASCHANGEDBYGRUB bit = null output
)
as
  set nocount on;

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

  select  @DATALOADED = 1,
    @REVENUESPLITID = BATCHREVENUE.REVENUESPLITID,
    @REVENUEID = BATCHREVENUE.REVENUEID,
    @CONSTITUENTID = BATCHREVENUE.CONSTITUENTID,
    @TYPECODE = BATCHREVENUE.TYPECODE,
    @SINGLEDESIGNATIONID = case when (select count(*) from dbo.BATCHREVENUESPLIT where BATCHREVENUESPLIT.BATCHREVENUEID = BATCHREVENUE.ID) <> 1 then BATCHREVENUE.SINGLEDESIGNATIONID
                           else (select DESIGNATIONID from dbo.BATCHREVENUESPLIT where BATCHREVENUESPLIT.BATCHREVENUEID = BATCHREVENUE.ID)
                           end,
    @SPLITS = dbo.UFN_REVENUEBATCH_GETSPLITS_TOITEMLISTXML(BATCHREVENUE.ID),
    @DATE = BATCHREVENUE.DATE,
    @PAYMENTMETHODCODE = BATCHREVENUE.PAYMENTMETHODCODE,
    @DONOTACKNOWLEDGE = BATCHREVENUE.DONOTACKNOWLEDGE,
    @CHECKDATE = BATCHREVENUE.CHECKDATE,
    @CHECKNUMBER = BATCHREVENUE.CHECKNUMBER,
    @REFERENCEDATE = BATCHREVENUE.REFERENCEDATE,
    @REFERENCENUMBER = BATCHREVENUE.REFERENCENUMBER,
    @CARDHOLDERNAME = case when BATCHREVENUE.PAYMENTMETHODCODE = 98 then BATCHREVENUE.CARDHOLDERNAME else CREDITCARD.CARDHOLDERNAME end,
    @CREDITCARDNUMBER = CREDITCARD.CREDITCARDPARTIALNUMBER,
    @PARTIALCREDITCARDNUMBER = BATCHREVENUE.CREDITCARDPARTIALNUMBER,
    @CREDITCARDTOKEN = CREDITCARD.CREDITCARDTOKEN,
    @CREDITTYPECODEID = case when BATCHREVENUE.PAYMENTMETHODCODE = 98 then BATCHREVENUE.CREDITTYPECODEID else CREDITCARD.CREDITTYPECODEID end,
    @AUTHORIZATIONCODE = BATCHREVENUE.AUTHORIZATIONCODE,
    @EXPIRESON = case when BATCHREVENUE.PAYMENTMETHODCODE = 98 then BATCHREVENUE.EXPIRESON else CREDITCARD.EXPIRESON end,
    @CONSTITUENTACCOUNTID = BATCHREVENUE.CONSTITUENTACCOUNTID, 
    @AMOUNT = BATCHREVENUE.AMOUNT,    
    @RECEIPTAMOUNT = BATCHREVENUE.RECEIPTAMOUNT, 
    @DONOTRECEIPT = BATCHREVENUE.DONOTRECEIPT,
    @REVENUESTREAMS = dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN_TOITEMLISTXML(@ID),
    @APPLYTOSHOWNFORCONSTITUENTID = BATCHREVENUE.APPLYTOSHOWNFORCONSTITUENTID, 
    @PROPERTYSUBTYPECODEID = BATCHREVENUE.PROPERTYSUBTYPECODEID,
    @GIFTINKINDSUBTYPECODEID = BATCHREVENUE.GIFTINKINDSUBTYPECODEID,
    @SEQUENCE = BATCHREVENUE.SEQUENCE,
    @INSTALLMENTFREQUENCYCODE = BATCHREVENUE.INSTALLMENTFREQUENCYCODE,
    @INSTALLMENTSTARTDATE = case
      when BATCHREVENUE.TYPECODE = 1 then coalesce(BATCHREVENUE.INSTALLMENTSTARTDATE, BATCHREVENUE.DATE)
      else BATCHREVENUE.INSTALLMENTSTARTDATE
    end,
    @INSTALLMENTENDDATE = BATCHREVENUE.INSTALLMENTENDDATE,
    @NUMBEROFINSTALLMENTS = case when @TYPECODE = 3 then null else BATCHREVENUE.NUMBEROFINSTALLMENTS end, -- if its recurring gift set it to null

    @INSTALLMENTAMOUNT = BATCHREVENUE.INSTALLMENTAMOUNT,
    @BENEFITS = dbo.UFN_REVENUEBATCH_GETBENEFITS_TOITEMLISTXML(@ID),
    @PERCENTAGEBENEFITS = dbo.UFN_REVENUEBATCH_GETBENEFITSPCT_TOITEMLISTXML(@ID),
    @FINDERNUMBER = BATCHREVENUE.FINDERNUMBER,
    @SOURCECODE = BATCHREVENUE.SOURCECODE,
    @APPEALID = BATCHREVENUE.APPEALID,
    @FINDERNUMBERISVALID = BATCHREVENUE.FINDERNUMBERISVALID,
    @USERMODIFIEDBENEFITS = BATCHREVENUE.USERMODIFIEDBENEFITS,
  @BENEFITSWAIVED = BATCHREVENUE.BENEFITSWAIVED,
    @POSTDATE = BATCHREVENUE.POSTDATE,
    @POSTSTATUSCODE = BATCHREVENUE.POSTSTATUSCODE,
    @SENDPLEDGEREMINDER = BATCHREVENUE.SENDPLEDGEREMINDER,
    @SALEDATE = BATCHREVENUE.SALEDATE,
    @SALEAMOUNT = BATCHREVENUE.SALEAMOUNT,
    @BROKERFEE = BATCHREVENUE.BROKERFEE,
    @SALEPOSTSTATUSCODE = BATCHREVENUE.SALEPOSTSTATUSCODE,
    @SALEPOSTDATE = BATCHREVENUE.SALEPOSTDATE,        
    @ISSUER = BATCHREVENUE.ISSUER,
    @NUMBEROFUNITS = BATCHREVENUE.NUMBEROFUNITS,
    @SYMBOL = BATCHREVENUE.SYMBOL,
    @MEDIANPRICE = BATCHREVENUE.MEDIANPRICE,
    @NOTETITLE = BATCHREVENUE.NOTETITLE,
    @NOTEAUTHORID = BATCHREVENUE.NOTEAUTHORID,
    @NOTEDATEENTERED = BATCHREVENUE.NOTEDATEENTERED,
    @NOTETYPECODEID = BATCHREVENUE.NOTETYPECODEID,
    @NOTETEXTNOTE = BATCHREVENUE.NOTETEXTNOTE,
    @GIFTAMOUNT = 0,
    @GIVENANONYMOUSLY = BATCHREVENUE.GIVENANONYMOUSLY,
    @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHREVENUE.GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID,
    @USERMODIFIEDRECEIPTAMOUNT = BATCHREVENUE.USERMODIFIEDRECEIPTAMOUNT,
    @PLEDGESUBTYPEID = BATCHREVENUE.PLEDGESUBTYPEID,
    @REJECTIONMESSAGE = BATCHREVENUE.REJECTIONMESSAGE,
    @CONSTITUENTLOOKUPID = BATCHREVENUE.CONSTITUENTID,
    @DIDRECOGNITIONSDEFAULT = 1,
    @MAILINGID = MAILINGID,
    @CHANNELCODEID = CHANNELCODEID,
    @INSTALLMENTS = dbo.UFN_REVENUEUPDATEBATCH_GETINSTALLMENTS_TOITEMLISTXML(@ID),
    @ORIGINALINSTALLMENTS = case when BATCHREVENUE.TYPECODE = 1 then dbo.UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS_TOITEMLISTXML(@REVENUEID) else null end,
    @PAYMENTFORPLEDGEAMOUNT = PAYMENTFORPLEDGEAMOUNT,
    @TSLONG = BATCHREVENUE.TSLONG,
    @TRIBUTES = dbo.UFN_REVENUEBATCH_GETTRIBUTES_TOITEMLISTXML(@ID),
    @UNAPPLIEDMATCHINGGIFTSPLITS = dbo.UFN_REVENUEBATCH_GETUNAPPLIEDMATCHINGGIFTSPLITS_TOITEMLISTXML(BATCHREVENUE.ID),
    @RECEIPTTYPECODE = BATCHREVENUE.RECEIPTTYPECODE,
    @OTHERPAYMENTMETHODCODEID = BATCHREVENUE.OTHERPAYMENTMETHODCODEID,
    @LETTERCODEID = BATCHREVENUE.LETTERCODEID, 
    @ACKNOWLEDGEDATE = BATCHREVENUE.ACKNOWLEDGEDATE,
    @REFERENCE = BATCHREVENUE.REFERENCE,
    @CATEGORYCODEID = BATCHREVENUE.GLREVENUECATEGORYMAPPINGID,
    @ACKNOWLEDGEEID = BATCHREVENUE.ACKNOWLEDGEEID,
    @APPLICATIONINFO = BATCHREVENUE.APPLICATIONINFO,
    @OTHERTYPECODEID = BATCHREVENUE.OTHERTYPECODEID,
    @OPPORTUNITYID = BATCHREVENUE.OPPORTUNITYID,
    @DIRECTDEBITRESULTCODE = BATCHREVENUE.DIRECTDEBITRESULTCODE,
    @LOWPRICE = BATCHREVENUE.LOWPRICE,
    @HIGHPRICE = BATCHREVENUE.HIGHPRICE,
    @NUMBEROFUNITSSOLD = BATCHREVENUE.NUMBEROFUNITSSOLD,
    @USERMODIFIEDNUMBEROFUNITSSOLD = BATCHREVENUE.USERMODIFIEDNUMBEROFUNITSSOLD,
    @TAXDECLARATIONS = dbo.UFN_REVENUEBATCH_GETTAXDECLARATIONS_TOITEMLISTXML(BATCHREVENUE.ID),
    @STANDINGORDERSETUP = BATCHREVENUE.STANDINGORDERSETUP,
    @STANDINGORDERSETUPDATE = BATCHREVENUE.STANDINGORDERSETUPDATE,
    @TRANSACTIONID = BATCHREVENUE.TRANSACTIONID,
    @ISEXISTINGCONSTITUENT = case when CONSTITUENT.ID is null then 0 else 1 end,
    @DECLINESGIFTAID =  case  when (select count(*) from dbo.BATCHREVENUESPLIT where BATCHREVENUESPLIT.BATCHREVENUEID = BATCHREVENUE.ID) <> 1 then BATCHREVENUE.DECLINESGIFTAID
                        else coalesce((select DECLINESGIFTAID from dbo.BATCHREVENUESPLIT where BATCHREVENUESPLIT.BATCHREVENUEID = BATCHREVENUE.ID), 0)
                        end,
    @DDISOURCECODEID = DDISOURCECODEID,
    @DDISOURCEDATE = DDISOURCEDATE,
    @ISCOVENANT = ISCOVENANT,
    @AMOUNTFORVAT = AMOUNTFORVAT,
    @VATTAXRATEID = VATTAXRATEID,
    @VATAMOUNT = VATAMOUNT,
    @ADDITIONALAPPLICATIONSSTREAM = dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_TOITEMLISTXML(@ID),
    @REVENUELOOKUPID = BATCHREVENUE.REVENUELOOKUPID,
    @MATCHINGGIFTS = dbo.UFN_REVENUEBATCH_GETENHANCEDMATCHINGGIFTS_2_TOITEMLISTXML(BATCHREVENUE.ID),
    @MGGENERATED = MGGENERATED,
    @MGALTERED = MGALTERED,
    @PAYINGPENDINGREVENUEID = PAYINGPENDINGREVENUEID,
    @GIFTINKINDITEMNAME = GIFTINKINDITEMNAME,
    @GIFTINKINDDISPOSITIONCODE = GIFTINKINDDISPOSITIONCODE,
    @GIFTINKINDNUMBEROFUNITS = GIFTINKINDNUMBEROFUNITS,
    @GIFTINKINDFAIRMARKETVALUE = GIFTINKINDFAIRMARKETVALUE,
    @DIRECTDEBITISREJECTED = coalesce(BATCHREVENUE.DIRECTDEBITISREJECTED,0),
    @ISGIFTAIDSPONSORSHIP = case  when (select count(*) from dbo.BATCHREVENUESPLIT where BATCHREVENUESPLIT.BATCHREVENUEID = BATCHREVENUE.ID) <> 1 then BATCHREVENUE.ISGIFTAIDSPONSORSHIP
                            else coalesce((select ISGIFTAIDSPONSORSHIP from dbo.BATCHREVENUESPLIT where BATCHREVENUESPLIT.BATCHREVENUEID = BATCHREVENUE.ID), 0)
                            end,
    @OTHERCONSTITUENTAPPLICATIONINFO = BATCHREVENUE.APPLICATIONINFO,
    @LOCKBOXID = BATCHREVENUE.LOCKBOXID,
    @LOCKBOXBATCHNUMBER = BATCHREVENUE.LOCKBOXBATCHNUMBER,
    @LOCKBOXBATCHSEQUENCE = BATCHREVENUE.LOCKBOXBATCHSEQUENCE,
    @PDACCOUNTSYSTEMID = BATCHREVENUE.PDACCOUNTSYSTEMID,
    @ISADJUSTMENT = BATCHREVENUE.ISADJUSTMENT,
    @ADJUSTMENTDATE = BATCHREVENUE.ADJUSTMENTDATE,
    @ADJUSTMENTPOSTDATE = BATCHREVENUE.ADJUSTMENTPOSTDATE,
    @ADJUSTMENTREASON = BATCHREVENUE.ADJUSTMENTREASON,
    @ADJUSTMENTREASONCODEID = BATCHREVENUE.ADJUSTMENTREASONCODEID,
    @ADJUSTMENTPOSTSTATUSCODE = case when BATCHREVENUE.ISADJUSTMENT = 1 and BATCHREVENUE.TYPECODE = 0 then BATCHREVENUE.ADJUSTMENTPOSTSTATUSCODE else null end,
    @REVENUEDEVELOPMENTFUNCTIONCODEID = REVENUEDEVELOPMENTFUNCTIONCODEID,
    @ORIGINALAPPLICATIONINFO = ORIGINALAPPLICATIONINFO,
    @RELOADRECOGNITION = RELOADRECOGNITION,
    @RELOADSOLICITORS = RELOADSOLICITORS,
    @LETTERS = dbo.UFN_REVENUEUPDATEBATCH_GETLETTERS_TOITEMLISTXML(BATCHREVENUE.ID),
    @NOTES = dbo.UFN_REVENUEUPDATEBATCH_GETNOTES_TOITEMLISTXML(BATCHREVENUE.ID),
    @TRANSACTIONCURRENCYID = BATCHREVENUE.TRANSACTIONCURRENCYID,
    @BASECURRENCYID = BATCHREVENUE.BASECURRENCYID,
    @BASEEXCHANGERATEID = BATCHREVENUE.BASEEXCHANGERATEID,
    @EXCHANGERATE = BATCHREVENUE.EXCHANGERATE,
    @GENERATEREFERENCENUMBER =  BATCHREVENUE.USESYSTEMGENERATEDREFERENCENUMBER,
    @UPDATEMATCHINGGIFTCLAIMS = BATCHREVENUE.UPDATEMATCHINGGIFTCLAIMSCODE,
    @UPDATEGIFTFEES = UPDATEGIFTFEES,
    @ORIGINALREVENUEAMOUNT = (select AMOUNT from dbo.REVENUE where REVENUE.ID = BATCHREVENUE.REVENUEID),
    @ORIGINALTRIBUTES = dbo.UFN_REVENUEUPDATEBATCH_GETTRIBUTES_TOITEMLISTXML(BATCHREVENUE.REVENUEID),
    @UPDATETRIBUTES = BATCHREVENUE.UPDATETRIBUTES,
    @INSTALLMENTSCHEDULESEEDDATE = case
                                   when BATCHREVENUE.TYPECODE = 3 then coalesce(BATCHREVENUE.INSTALLMENTSCHEDULESEEDDATE, BATCHREVENUE.DATE)
                                   else BATCHREVENUE.INSTALLMENTSCHEDULESEEDDATE
                                   end,
    @APPLYBYPERCENT = APPLYBYPERCENT,
    @ADJPAYMENT_DATE = BATCHREVENUE.ADJPAYMENT_DATE,
    @ADJPAYMENT_POSTDATE = BATCHREVENUE.ADJPAYMENT_POSTDATE,
    @ADJPAYMENT_REASONCODEID = BATCHREVENUE.ADJPAYMENT_REASONCODEID,
    @ADJPAYMENT_DETAILS = BATCHREVENUE.ADJPAYMENT_DETAILS,
    @SALE_LOWPRICE = BATCHREVENUE.SALE_LOWPRICE,
    @SALE_MEDIANPRICE = BATCHREVENUE.SALE_MEDIANPRICE,
    @SALE_HIGHPRICE = BATCHREVENUE.SALE_HIGHPRICE,
    @VENDORID = BATCHREVENUE.VENDORID,
    @PLEDGEAMOUNTPAID = case
                          when BATCHREVENUE.TYPECODE = 1 then ((select TRANSACTIONAMOUNT from FINANCIALTRANSACTION where  id= BATCHREVENUE.REVENUEID) - dbo.UFN_PLEDGE_GETBALANCE(BATCHREVENUE.REVENUEID))
                          else null
                        end,
    @STANDINGORDERREFERENCENUMBER = STANDINGORDERREFERENCENUMBER
  from dbo.BATCHREVENUE
  left join dbo.CREDITCARD ON BATCHREVENUE.CREDITCARDID = CREDITCARD.ID
  left join dbo.CONSTITUENT on CONSTITUENT.ID = BATCHREVENUE.CONSTITUENTID
  where BATCHREVENUE.ID = @ID

  if @PAYMENTMETHODCODE = 255
    set @PAYMENTMETHODCODE = null;
  if @PAYMENTMETHODCODE = 11 --Standing order

    begin
      set @STANDINGORDERCONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID; 
      set @STANDINGORDERREFERENCEDATE = @REFERENCEDATE;
    end

  set @HASSOLDFIXEDASSETS = 0;
  if @PAYMENTMETHODCODE in (4, 6) --stock, gift-in-kind

  begin
    -- sold stock or sold gift-in-kind

    if exists (select 1 from dbo.FINANCIALTRANSACTION
               where FINANCIALTRANSACTION.PARENTID = @REVENUEID
               and FINANCIALTRANSACTION.TYPECODE in (21,27))
      set @HASSOLDFIXEDASSETS = 1;
  end

  if @PAYMENTMETHODCODE = 5 --property

  begin
    -- sold property

    if exists (select 1 from dbo.FINANCIALTRANSACTION
               inner join dbo.PROPERTYDETAIL_EXT on PROPERTYDETAIL_EXT.ID = FINANCIALTRANSACTION.ID
               where FINANCIALTRANSACTION.PARENTID = @REVENUEID
               and PROPERTYDETAIL_EXT.SALEDATE is not null)
      set @HASSOLDFIXEDASSETS = 1;
  end

  if @FINDERNUMBER = 0
    set @FINDERNUMBER = null;

  /* JamesWill CR267402-021907 INSTALLMENTFREQUENCYCODE cannot be null in the database, but it can be null in the defaults */
  if @INSTALLMENTFREQUENCYCODE = 255
    set @INSTALLMENTFREQUENCYCODE = null;

  /* JamesWill CR265838-020507 2007/03/07 */
  if @POSTSTATUSCODE = 255
    set @POSTSTATUSCODE = null;
  if @SALEPOSTSTATUSCODE = 255
    set @SALEPOSTSTATUSCODE = null;

  if @RECEIPTTYPECODE = 255
    set @RECEIPTTYPECODE = null;

  -- Determine the constituent type

  declare @ISORGANIZATION bit, @ISGROUP bit, @ISHOUSEHOLD bit
  select
    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
    @ISGROUP = CONSTITUENT.ISGROUP,
    @ISHOUSEHOLD = case when ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 0 then 1 else 0 end
  from dbo.CONSTITUENT
  left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
  where CONSTITUENT.ID = @CONSTITUENTID

  -- If the fields weren't set, then the constituent must be a batch constituent

  if @ISORGANIZATION is null
    select
      @ISORGANIZATION = BATCHREVENUECONSTITUENT.ISORGANIZATION,
      @ISGROUP = BATCHREVENUECONSTITUENT.ISGROUP,
      @ISHOUSEHOLD = case when ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 0 then 1 else 0 end
    from dbo.BATCHREVENUECONSTITUENT
    left join dbo.GROUPDATA on BATCHREVENUECONSTITUENT.ID = GROUPDATA.ID
    where BATCHREVENUECONSTITUENT.ID = @CONSTITUENTID

  if @ISORGANIZATION = 0 and @ISGROUP = 0
    set @CONSTITUENTTYPE = 0 -- Individual

  else
  begin
    if @ISORGANIZATION = 1
      set @CONSTITUENTTYPE = 1 -- Org

    else
    begin
      if @ISHOUSEHOLD = 1
        set @CONSTITUENTTYPE = 2 -- Household

      else
        set @CONSTITUENTTYPE = 3 -- Group

    end
  end

  declare @APPLICATIONCODE tinyint;
  if @APPLICATIONINFO is not null
    if len(@APPLICATIONINFO) > 0
      if len(@APPLICATIONINFO) = 3
        set @APPLICATIONCODE = cast(substring(@APPLICATIONINFO, 3, 1) as tinyint);

  declare @SPLITCOUNT int
  select @SPLITCOUNT = count(*) from @SPLITS.nodes('/SPLITS/ITEM') T(c)
  declare @USESPLITS bit;

  if @APPLICATIONCODE is not null and @SPLITCOUNT > 1
    set @USESPLITS = 1
  else
    set @USESPLITS = 0

  if @TYPECODE in (1,3)
    set @USESPLITS = 1

  set @APPLICATIONRECOGNITIONS = dbo.UFN_REVENUEUPDATEBATCH_GETPAYMENTRECOGNITIONS(@ID, @USESPLITS)

  set @APPLICATIONSOLICITORS = dbo.UFN_REVENUEUPDATEBATCH_GETPAYMENTSOLICITORS(@ID, @USESPLITS)                       

  /* Load business units */
  declare @APPORSPLIT bit =0;
  if @SPLITCOUNT>1
    set @APPORSPLIT=1
  else
    set @APPORSPLIT=0

  set @APPLICATIONBUSINESSUNITS = dbo.UFN_REVENUEBATCH_GETAPPLICATIONBUSINESSUNITS(@ID,@APPORSPLIT)

  --see if this item is a sponsorship RG

  set @ISSPONSORSHIPRG = case when @TYPECODE = 3 and (select top 1 TYPECODE from dbo.REVENUESPLIT where REVENUEID = @REVENUEID) = 9 then 1 else 0 end;

  --INSTALLMENTS--

  --Find the first and last installment that have a full balance/no payments

  declare @LASTUNPAIDINSTALLMENTSEQUENCE integer;
  declare @FIRSTUNPAIDINSTALLMENTSEQUENCE integer;

  declare @MAXINSTALLMENTSEQUENCE integer;
   -- NUMBEROFINSTALLMENTS is null incase of recurring gift, so set MAXINSTALLMENTSEQUENCE to 0

   -- if set to 1 (default), RG behaves as if installments have been paid because of RevenueBatchHelper.RevenueHasPaidInstallments

  set @MAXINSTALLMENTSEQUENCE = isnull(@NUMBEROFINSTALLMENTS,0);
  select
    @FIRSTUNPAIDINSTALLMENTSEQUENCE = min(SEQUENCE),
    @LASTUNPAIDINSTALLMENTSEQUENCE = max(SEQUENCE)
  from dbo.INSTALLMENT
  left join dbo.INSTALLMENTPAYMENT on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
  where
    INSTALLMENT.REVENUEID = @REVENUEID and
    INSTALLMENTPAYMENT.INSTALLMENTID is null;

  /*
  select
    @RESCHEDULEBALANCE = sum(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID))
  from dbo.INSTALLMENT
  where
    INSTALLMENT.REVENUEID = @ID;
  */
  if @FIRSTUNPAIDINSTALLMENTSEQUENCE is not null
  begin
    set @LASTPAYMENTSEQUENCE = @FIRSTUNPAIDINSTALLMENTSEQUENCE - 1;
    -- set @NUMBEROFINSTALLMENTS to the number of unpaid installments

    --set @NUMBEROFINSTALLMENTS = (@LASTUNPAIDINSTALLMENTSEQUENCE - @FIRSTUNPAIDINSTALLMENTSEQUENCE) + 1;

  end
  else
  begin
    --all installments have a payment towards them

    set @LASTPAYMENTSEQUENCE = (@MAXINSTALLMENTSEQUENCE);
    --set @NUMBEROFINSTALLMENTS = 0;

  end

  select @RGSTATUSCODE = REVENUESCHEDULE.STATUSCODE 
  from dbo.REVENUESCHEDULE 
  where REVENUESCHEDULE.ID = @REVENUEID

  --If there have been no payments towards the balance, then the @MAXINSTALLSEQUENCE

  --and the @LASTUNPAIDINSTALLMENTSEQUENCE will be equal.  If they are equal,

  --we do not care about the last unpaid row.  Set it to 0 as it can never be 0 on its own.

  --Otherwise, set it to the sequence number of the last installment that has no payments

  --applied to it.

  if @LASTUNPAIDINSTALLMENTSEQUENCE = @MAXINSTALLMENTSEQUENCE or @LASTUNPAIDINSTALLMENTSEQUENCE is null
    set @LASTUNPAIDROW = 0;
  else
    set @LASTUNPAIDROW = @LASTUNPAIDINSTALLMENTSEQUENCE;

  --set @INSTALLMENTS = dbo.UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS_TOITEMLISTXML(@ID)        


  select @ORIGINALCONSTITUENTID = CONSTITUENTID from dbo.REVENUE where ID = @REVENUEID

  set @HASPOSTEDPAYMENTS = dbo.UFN_PLEDGEHASPOSTEDPAYMENTS(@REVENUEID);

  if @HASPOSTEDPAYMENTS = 1
  begin
     declare @hasbatchrevenuesplitrows int = 0
     select @hasbatchrevenuesplitrows = count(1) from dbo.BATCHREVENUESPLIT where BATCHREVENUESPLIT.BATCHREVENUEID = @ID 
     if @hasbatchrevenuesplitrows >= 1 and exists(select 1
       from dbo.BATCHREVENUESPLIT 
       where BATCHREVENUEID = @ID and 
       DESIGNATIONID not in (select DESIGNATIONID from dbo.REVENUESPLIT_EXT 
       inner join  dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
       inner join  dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
       where FINANCIALTRANSACTION.ID = @REVENUEID))
           set @HASCHANGEDBYGRUB = 1;
     else if @hasbatchrevenuesplitrows = 0 and exists(select 1
       from dbo.BATCHREVENUE 
       where ID = @ID and
       SINGLEDESIGNATIONID not in (select DESIGNATIONID from dbo.REVENUESPLIT_EXT 
       inner join  dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
       inner join  dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
       where FINANCIALTRANSACTION.ID = @REVENUEID))
           set @HASCHANGEDBYGRUB = 1;
     else
           set @HASCHANGEDBYGRUB = 0;
  end
  else
  begin
    set @HASCHANGEDBYGRUB = 0;
  end

  if exists(select 1 
            from dbo.FINANCIALTRANSACTIONLINEITEM 
      inner join REVENUECATEGORY on REVENUECATEGORY.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID <> @CATEGORYCODEID)
  begin
    set @ADJPAYMENT_REVENUECATEGORYCHANGED = 1 ;
  end

  select @DEPOSITID = DEPOSITID
  from dbo.BANKACCOUNTDEPOSITPAYMENT
  where ID = @REVENUEID;

  /* Determine if the original revenue record was posted to determine its adjustment date. The logic to determine its adjustment status is complicated.
   * This comes from USP_DATAFORMTEMPLATE_VIEW_REVENUEUPDATEBATCH_GETREVENUE.
   */
  declare @ISPAYMENT bit = 0;
  declare @ISPLEDGE bit = 0;
  declare @ISRECURRINGGIFT bit = 0;
  declare @ISPOSTED  bit = 0;
  declare @ISMISCELLANEOUSPAYMENT bit = 0;
  declare @ISORDERAPPLICATION bit = 0;
  declare @HASGIFTAIDSPLITONPENDINGR68PROCESS bit;
  declare @ADJPAYMENT_ORIGINALPOSTSTATUS tinyint; 
  declare @ADJPAYMENT_ORIGINALREASONCODEID uniqueidentifier;

  select
    @ISPAYMENT = case when R.TRANSACTIONTYPECODE = 0 then 1 else 0 end,
    @ISPOSTED = case when P.ID is null then 0 else 1 end,
    @ISPLEDGE = case when R.TRANSACTIONTYPECODE = 1 then 1 else 0 end,
    @ISRECURRINGGIFT = case when R.TRANSACTIONTYPECODE = 2 then 1 else 0 end
  from dbo.REVENUE R
  inner join dbo.REVENUEPAYMENTMETHOD RP on R.ID = RP.REVENUEID  
  left join dbo.REVENUEPOSTED P on P.ID = R.ID
  where R.ID = @REVENUEID;

  if exists(select ID from dbo.REVENUESPLIT where REVENUEID = @REVENUEID and APPLICATIONCODE = 11)
  begin
    set @ISMISCELLANEOUSPAYMENT = 1;
  end

  if @ISPAYMENT = 1
    if exists(select 'x' from dbo.SALESORDERPAYMENT
              inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDERPAYMENT.SALESORDERID
              where SALESORDERPAYMENT.PAYMENTID = @REVENUEID and SALESORDERITEM.TYPECODE <> 12) 
      begin
        select @ISORDERAPPLICATION = 1
        from dbo.SALESORDERPAYMENT
        where PAYMENTID = @REVENUEID;  
      end
  set @HASGIFTAIDSPLITONPENDINGR68PROCESS = dbo.UFN_REVENUE_HASGIFTAIDSPLITONPENDINGR68(@REVENUEID, @ISPAYMENT)

  --payment adjustment

  if @ISPAYMENT = 1 and @ISPOSTED = 1 and @ISMISCELLANEOUSPAYMENT = 0 and @ISORDERAPPLICATION = 0 and @HASGIFTAIDSPLITONPENDINGR68PROCESS = 0
  begin
    set @ISADJUSTMENT = 1;
    set @POSTSTATUSCODE = null
  end;
  --pledge adjustment

  if @ISPLEDGE = 1 and @ISPOSTED = 1
  begin
    set @ISADJUSTMENT = 1;
    set @POSTSTATUSCODE = null
  end;

  if @ISADJUSTMENT = 1
    begin
      select  
        @ADJPAYMENT_ORIGINALPOSTDATE = getdate()

      select top 1
        @ADJPAYMENT_ORIGINALPOSTDATE = ADJUSTMENT.POSTDATE,
        @ADJPAYMENT_ORIGINALPOSTSTATUS = ADJUSTMENT.POSTSTATUSCODE,
        @ADJPAYMENT_ORIGINALREASONCODEID = ADJUSTMENT.REASONCODEID
      from dbo.ADJUSTMENT
      where ADJUSTMENT.REVENUEID = @REVENUEID
      order by dateadded desc;

      if @ADJPAYMENT_ORIGINALREASONCODEID is null
      begin
        select
          @ADJPAYMENT_ORIGINALPOSTDATE = POSTDATE,
          @ADJPAYMENT_ORIGINALPOSTSTATUS = 1,
          @ADJPAYMENT_ORIGINALREASONCODEID = REASONCODEID
        from dbo.BENEFITADJUSTMENT
        where REVENUEID = @REVENUEID and POSTSTATUSCODE = 1
      end

      --if the most recent adjustment has been posted then in essence we have no adjustment so clear out the adjustment fields

      if @ADJPAYMENT_ORIGINALPOSTSTATUS = 0
      begin
        select 
          @ADJPAYMENT_ORIGINALPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
          @ADJPAYMENT_ORIGINALPOSTSTATUS = 1,
          @ADJPAYMENT_ORIGINALREASONCODEID = null
      end

      if @ADJUSTMENTPOSTDATE is null
        select @ADJPAYMENT_ORIGINALPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
    end

    if @ISPLEDGE = 1 and @HASPOSTEDPAYMENTS = 1
      select
      @ADJPAYMENT_POSTDATEDEPOSIT = case when count(distinct Deposit.POSTDATE) = 1 then max(Deposit.POSTDATE) else null end,
      @PROMPTONADJUSTMENTDATECHANGE = case when count(*) > 0 then 1 else 0 end 
      from dbo.FINANCIALTRANSACTIONLINEITEM as Pledge 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM as Payment on Pledge.ID = Payment.SourceLineItemID
      inner join dbo.BANKACCOUNTDEPOSITPAYMENT on Payment.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
      inner join dbo.FINANCIALTRANSACTION as Deposit on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = Deposit.ID
      where Pledge.FINANCIALTRANSACTIONID = @REVENUEID
      and Pledge.DELETEDON is null
      and Pledge.TYPECODE = 0
      and Payment.DELETEDON is null
      and Payment.TYPECODE = 0

  /* End logic to determine original adjustment date for the revenue record 
   *
   */

  return 0;