USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONPROFILE

The load procedure used by the view dataform template "Revenue Transaction Profile View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TYPE	tinyint	INOUT	Type
@AMOUNT	money	INOUT	Amount
@BALANCE	money	INOUT	Balance
@TOTALPAID	money	INOUT	Total paid
@PASTDUE	money	INOUT	Past due
@MATCHEDREVENUE	nvarchar(255)	INOUT	Matched revenue
@MATCHEDREVENUEID	uniqueidentifier	INOUT	Matched revenue ID
@PAYMENTID	uniqueidentifier	INOUT	Payment ID
@NEXTTRANSACTION	datetime	INOUT	Next transaction
@ISPENDING	bit	INOUT	Is pending
@PENDINGBATCHNUMBER	nvarchar(100)	INOUT	Pending batch number
@DATE	datetime	INOUT	Date
@PAYMENTMETHOD	nvarchar(15)	INOUT	Payment method
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method code
@CHECKDATE	UDT_FUZZYDATE	INOUT	Check date
@CHECKNUMBER	nvarchar(20)	INOUT	Check number
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference number
@REFERENCEDATE	UDT_FUZZYDATE	INOUT	Reference date
@CARDHOLDERNAME	nvarchar(255)	INOUT	Name on card
@CREDITCARDNUMBER	nvarchar(20)	INOUT	Card number
@CREDITTYPE	nvarchar(100)	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	Number of units
@SYMBOL	nvarchar(25)	INOUT	Symbol
@MEDIANPRICE	decimal(19, 4)	INOUT	Median price
@SALEAMOUNT	money	INOUT	Sale price
@GIFTINKINDSUBTYPE	nvarchar(100)	INOUT	Subtype
@PROPERTYSUBTYPE	nvarchar(100)	INOUT	Subtype
@ACCOUNT	nvarchar(255)	INOUT	Account
@POSTDATE	datetime	INOUT	Post date
@POSTSTATUS	nvarchar(50)	INOUT	Post status
@BATCHNUMBER	nvarchar(100)	INOUT	Batch number
@ISSOLDSTOCK	bit	INOUT	Is sold stock
@ISSOLDPROPERTY	bit	INOUT	Is sold property
@ISPOSTEDSOLDSTOCK	bit	INOUT	Is posted sold stock
@ISPOSTEDSOLDPROPERTY	bit	INOUT	Is posted sold property
@APPEAL	nvarchar(100)	INOUT	Appeal
@SENDPLEDGEREMINDER	bit	INOUT	Send reminders
@FREQUENCY	nvarchar(100)	INOUT	Frequency
@ENDDATE	datetime	INOUT	End date
@STARTDATE	datetime	INOUT	Start date
@STATUS	nvarchar(255)	INOUT	Status
@SOURCECODE	nvarchar(50)	INOUT	Source code
@RECEIPTAMOUNT	money	INOUT	Receipt amount
@GIVENANONYMOUSLY	bit	INOUT	Given anonymously
@MAILING	nvarchar(100)	INOUT	Effort
@CHANNEL	nvarchar(100)	INOUT	Inbound channel
@PLEDGESUBTYPE	nvarchar(100)	INOUT	Subtype
@RECEIPTNUMBER	int	INOUT	Receipt number
@RECEIPTSTATUS	nvarchar(50)	INOUT	Receipt status
@RECEIPTDATE	datetime	INOUT	Receipt date
@ACKNOWLEDGEMENTSTATUS	nvarchar(50)	INOUT	Acknowledgements
@MGSTATUSCODE	tinyint	INOUT	Matching gift status
@RECEIPTTYPE	nvarchar(30)	INOUT	Receipt preference
@OTHERPAYMENTMETHODCODE	nvarchar(100)	INOUT	Other method
@CATEGORYDESCRIPTION	nvarchar(100)	INOUT	Revenue category
@GIFTFEEAMOUNT	money	INOUT	Gift fees
@GIFTFEE_ENABLED	bit	INOUT	Gift fee enabled
@ELIGIBLEFORMATCHINGGIFTCLAIM	bit	INOUT	Eligible for matching gift claims
@ORDERNUMBER	nvarchar(30)	INOUT	Order number
@TAXCLAIMNUMBER	nvarchar(10)	INOUT	Tax claim number
@TAXCLAIMAMOUNT	money	INOUT	Tax claim amount
@GROSSAMOUNT	money	INOUT	Total payment amount
@SHOWPOTENTIAL	bit	INOUT	Show gift aid as potential
@RECURRINGGIFTTOTALWITHGIFTAID	money	INOUT	Total paid including Gift Aid
@PLEDGETOTALWITHGIFTAID	money	INOUT	Total paid including Gift Aid
@PLEDGERECURRINGGIFTGROSSAMOUNT	money	INOUT	Gross amount for pledge or recurring gift
@CONSTITUENTISGROUP	bit	INOUT	Constituent is a group
@VATAMOUNT	money	INOUT	VAT amount
@ISMISCELLANEOUSPAYMENT	bit	INOUT	Is miscellaneous payment
@DEPOSITID	uniqueidentifier	INOUT	Deposit ID
@DEPOSITNAME	nvarchar(100)	INOUT	Deposit
@REFERENCE	nvarchar(255)	INOUT	Reference
@ISREIMBURSABLE	bit	INOUT	Reimbursable
@LOOKUPID	nvarchar(100)	INOUT	Revenue ID
@RGHASINSTALLMENTS	bit	INOUT	RGHASINSTALLMENTS
@GIFTINKINDITEMNAME	nvarchar(100)	INOUT	Item name
@GIFTINKINDDISPOSITION	nvarchar(50)	INOUT	Disposition
@GIFTINKINDNUMBEROFUNITS	int	INOUT	Number of units
@GIFTINKINDFAIRMARKETVALUE	money	INOUT	Fair market value per unit
@GIFTINKINDAPPRAISALREQUIRED	bit	INOUT	GIFTINKINDAPPRAISALREQUIRED
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT	Result code
@DIRECTDEBITISREJECTED	bit	INOUT	Direct debit is rejected
@OPPORTUNITYNAME	nvarchar(250)	INOUT	Opportunity
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity ID
@FUNDINGREQUESTNAME	nvarchar(150)	INOUT	Funding request
@FUNDINGREQUESTID	uniqueidentifier	INOUT	Funding request ID
@SHOWRGPASTDUE	bit	INOUT	SHOWRGPASTDUE
@HASGIFTAIDSPLITONPENDINGR68PROCESS	bit	INOUT	HASGIFTAIDSPLITONPENDINGR68PROCESS
@GLBATCHLOOKUPID	nvarchar(100)	INOUT	GLBATCHLOOKUPID
@GLBATCHLINKID	uniqueidentifier	INOUT	GLBATCHLINKID
@REVENUEID	uniqueidentifier	INOUT	REVENUEID
@AUCTIONITEMNAME	nvarchar(100)	INOUT	Item name
@AUCTIONITEMID	uniqueidentifier	INOUT	Auction item ID
@AUCTIONITEMCATEGORY	nvarchar(100)	INOUT	Category
@AUCTIONITEMSUBCATEGORY	nvarchar(100)	INOUT	Subcategory
@AUCTIONEXPIRATIONDATE	datetime	INOUT	Expiration date
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@BASEAMOUNT	money	INOUT	Base amount
@CONSTITUENTNAME	nvarchar(255)	INOUT	Constituent
@TRANSACTIONTYPE	nvarchar(100)	INOUT	Transaction type
@GIFTFEESANDGIFTFEEDISTRIBUTIONAMOUNTSNOTEQUAL	bit	INOUT	Gift fees and gift fee distribution amounts are not equal
@PLANNEDGIFTVEHICLECODE	int	INOUT	Planned gift vehicle
@PLANNEDGIFTISREVOCABLE	bit	INOUT	Revocable
@ISPLANNEDGIFTADDITION	bit	INOUT	Planned gift addition
@UNIQUERECEIPTNUMBER	nvarchar(20)	INOUT	Receipt number
@HASINVALIDPAPERLESSMANDATES	bit	INOUT	HASINVALIDPAPERLESSMANDATES
@PLANNEDGIFT_TRUSTHELDOUTSIDE	bit	INOUT	Trust held outside
@ISSOLDGIFTINKIND	bit	INOUT	Is sold gift-in-kind
@ISPOSTEDSOLDGIFTINKIND	bit	INOUT	Is posted sold gift-in-kind
@ACKNOWLEDGEMENTDATE	datetime	INOUT	Acknowledged date
@POSTSTATUSCODE	tinyint	INOUT
@SALESMETHODTYPECODE	tinyint	INOUT
@TOTALREFUNDED	money	INOUT
@OVERAGEKEPT	money	INOUT
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT
@SEPAMANDATELOOKUPID	nvarchar(35)	INOUT
@SEPAMANDATESIGNATUREDATE	datetime	INOUT
@SEPAMANDATESTATUSCODE	tinyint	INOUT
@SALESORDERID	uniqueidentifier	INOUT
@ISTAXEXEMPT	bit	INOUT
@TAXEXEMPTREASON	nvarchar(100)	INOUT
@TAXEXEMPTCOMMENTS	nvarchar(255)	INOUT
@DESIGNATIONS	xml	INOUT
@OTHERRECURRINGGIFTS	xml	INOUT
@LASTPAYMENTDATE	date	INOUT
@FINANCIALINSTITUTION	nvarchar(50)	INOUT
@DIRECTDEBITACCOUNT	nvarchar(4)	INOUT
@NEXTTRANSACTIONSEQUENCE	int	INOUT
@LASTPAYMENTSEQUENCE	int	INOUT
@ISPARTIALCC	bit	INOUT
@SPONSORSHIPID	uniqueidentifier	INOUT
@RECURRINGGIFTHASCONTRIBUTEDPORTION	bit	INOUT
@STANDINGORDERSETUP	bit	INOUT
@STANDINGORDERSETUPDATE	datetime	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CREDITCARDUPDATEDATE	date	INOUT
@UNMASKEDACCOUNT	nvarchar(255)	INOUT
@CANVIEWSENSITIVEINFORMATION	bit	INOUT
@ACCOUNTNUMBERLEN	int	INOUT
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONPROFILE
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TYPE tinyint = null output,
  @AMOUNT money = null output,
  @BALANCE money = null output,
  @TOTALPAID money = null output,
  @PASTDUE money = null output,
  @MATCHEDREVENUE nvarchar(255) = null output,
  @MATCHEDREVENUEID uniqueidentifier = null output,
  @PAYMENTID uniqueidentifier = null output,
  @NEXTTRANSACTION datetime = null output,
  @ISPENDING bit = null output,
  @PENDINGBATCHNUMBER nvarchar(100) = null output,
  @DATE datetime = null output,
  @PAYMENTMETHOD nvarchar(15) = null output,
  @PAYMENTMETHODCODE tinyint = null output,
  @CHECKDATE dbo.UDT_FUZZYDATE = null output,
  @CHECKNUMBER nvarchar(20) = null output,
  @REFERENCENUMBER nvarchar(20) = null output,
  @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
  @CARDHOLDERNAME nvarchar(255) = null output,
  @CREDITCARDNUMBER nvarchar(20) = null output,
  @CREDITTYPE nvarchar(100) = 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,
  @SALEAMOUNT money = null output,
  @GIFTINKINDSUBTYPE nvarchar(100) = null output,
  @PROPERTYSUBTYPE nvarchar(100) = null output,
  @ACCOUNT nvarchar(255) = null output,
  @POSTDATE datetime = null output,
  @POSTSTATUS nvarchar(50) = null output,
  @BATCHNUMBER nvarchar(100) = null output,
  @ISSOLDSTOCK bit = null output,
  @ISSOLDPROPERTY bit = null output,
  @ISPOSTEDSOLDSTOCK bit = null output,
  @ISPOSTEDSOLDPROPERTY bit = null output,
  @APPEAL nvarchar(100) = null output,
  @SENDPLEDGEREMINDER bit = null output,
  @FREQUENCY nvarchar(100) = null output,
  @ENDDATE datetime = null output,
  @STARTDATE datetime = null output,
  @STATUS nvarchar(255) = null output,
  @SOURCECODE nvarchar(50) = null output,
  @RECEIPTAMOUNT money = null output,
  @GIVENANONYMOUSLY bit = null output,
  @MAILING nvarchar(100) = null output,
  @CHANNEL nvarchar(100) = null output,
  @PLEDGESUBTYPE nvarchar(100) = null output,
  @RECEIPTNUMBER int = null output,
  @RECEIPTSTATUS nvarchar(50) = null output,
  @RECEIPTDATE datetime = null output,
  @ACKNOWLEDGEMENTSTATUS nvarchar(50) = null output,
  @MGSTATUSCODE tinyint = null output,
  @RECEIPTTYPE nvarchar(30) = null output,
  @OTHERPAYMENTMETHODCODE nvarchar(100) = null output,
  @CATEGORYDESCRIPTION nvarchar(100) = null output,
  @GIFTFEEAMOUNT money = null output,
  @GIFTFEE_ENABLED bit = null output,
  @ELIGIBLEFORMATCHINGGIFTCLAIM bit = null output,
  @ORDERNUMBER nvarchar(30) = null output,
  @TAXCLAIMNUMBER nvarchar(10) = null output,
  @TAXCLAIMAMOUNT money = null output,
  @GROSSAMOUNT money = null output,
  @SHOWPOTENTIAL bit = null output,
  @RECURRINGGIFTTOTALWITHGIFTAID money = null output,
  @PLEDGETOTALWITHGIFTAID money = null output,
  @PLEDGERECURRINGGIFTGROSSAMOUNT money = null output,
  @CONSTITUENTISGROUP bit = null output,
  @VATAMOUNT money = null output,
  @ISMISCELLANEOUSPAYMENT bit = null output,
  @DEPOSITID uniqueidentifier = null output,
  @DEPOSITNAME nvarchar(100) = null output,
  @REFERENCE nvarchar(255) = null output,
  @ISREIMBURSABLE bit = null output,
  @LOOKUPID nvarchar(100) = null output,
  @RGHASINSTALLMENTS bit = null output,
  @GIFTINKINDITEMNAME nvarchar(100) = null output,
  @GIFTINKINDDISPOSITION nvarchar(50) = null output,
  @GIFTINKINDNUMBEROFUNITS int = null output,
  @GIFTINKINDFAIRMARKETVALUE money = null output,
  @GIFTINKINDAPPRAISALREQUIRED bit = null output,
  @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
  @DIRECTDEBITISREJECTED bit = null output,
  @OPPORTUNITYNAME nvarchar(250) = null output,
  @OPPORTUNITYID uniqueidentifier = null output,
  @FUNDINGREQUESTNAME nvarchar(150) = null output,
  @FUNDINGREQUESTID uniqueidentifier = null output,
  @SHOWRGPASTDUE bit = null output,
  @HASGIFTAIDSPLITONPENDINGR68PROCESS bit = null output,
  @GLBATCHLOOKUPID nvarchar(100) = null output,
  @GLBATCHLINKID uniqueidentifier = null output,
  @REVENUEID uniqueidentifier = null output,
  @AUCTIONITEMNAME nvarchar(100) = null output,
  @AUCTIONITEMID uniqueidentifier = null output,
  @AUCTIONITEMCATEGORY nvarchar(100) = null output,
  @AUCTIONITEMSUBCATEGORY nvarchar(100) = null output,
  @AUCTIONEXPIRATIONDATE datetime = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
  @BASEAMOUNT money = null output,
  @CONSTITUENTNAME nvarchar(255) = null output,
  @TRANSACTIONTYPE nvarchar(100) = null output,
  @GIFTFEESANDGIFTFEEDISTRIBUTIONAMOUNTSNOTEQUAL bit = null output,
  @PLANNEDGIFTVEHICLECODE int = null output,
  @PLANNEDGIFTISREVOCABLE bit = null output,
  @ISPLANNEDGIFTADDITION bit = null output,
  @UNIQUERECEIPTNUMBER nvarchar(20) = null output,
  @HASINVALIDPAPERLESSMANDATES bit = null output,
  @PLANNEDGIFT_TRUSTHELDOUTSIDE bit = null output,
  @ISSOLDGIFTINKIND bit = null output,
  @ISPOSTEDSOLDGIFTINKIND bit = null output,
  @ACKNOWLEDGEMENTDATE datetime = null output,
  @POSTSTATUSCODE tinyint = null output,
  @SALESMETHODTYPECODE tinyint = null output,
  @TOTALREFUNDED money = null output,
  @OVERAGEKEPT money = null output,
  @CONSTITUENTACCOUNTID uniqueidentifier = null output,
  @SEPAMANDATELOOKUPID nvarchar(35) = null output,
  @SEPAMANDATESIGNATUREDATE datetime = null output,
  @SEPAMANDATESTATUSCODE tinyint = null output,
  @SALESORDERID uniqueidentifier = null output,
  @ISTAXEXEMPT bit = null output,
  @TAXEXEMPTREASON nvarchar(100) = null output,
  @TAXEXEMPTCOMMENTS nvarchar(255) = null output,
  @DESIGNATIONS xml = null output,
  @OTHERRECURRINGGIFTS xml = null output,
  @LASTPAYMENTDATE date = null output,
  @FINANCIALINSTITUTION nvarchar(50) = null output,
  @DIRECTDEBITACCOUNT nvarchar(4) = null output,
  @NEXTTRANSACTIONSEQUENCE int = null output,
  @LASTPAYMENTSEQUENCE int = null output,
  @ISPARTIALCC bit = null output,
  @SPONSORSHIPID uniqueidentifier = null output,
  @RECURRINGGIFTHASCONTRIBUTEDPORTION bit = null output,
  @STANDINGORDERSETUP bit = null output,
  @STANDINGORDERSETUPDATE datetime = null output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CREDITCARDUPDATEDATE date = null output,
  @UNMASKEDACCOUNT nvarchar(255) = null output,
  @CANVIEWSENSITIVEINFORMATION bit = null output,
  @ACCOUNTNUMBERLEN int = null output
)
as
--set @CURRENTAPPUSERID = 'BB053AF1-905F-4A08-844B-7AD505D98210' 

  set nocount on;

  declare @MATCHEDREVENUEDETAILID uniqueidentifier;
  declare @GIFTINKINDORGANIZATIONFAIRMARKETVALUE money;
  declare @USER_GRANTED_DESIGNATIONS_DATALIST bit;
  declare @USER_GRANTED_PAYMENTINFORMATION_VIEW bit;

  set @DATALOADED = 0;
  set @MGSTATUSCODE = 0;
  set @ISMISCELLANEOUSPAYMENT = 0;
  set @RGHASINSTALLMENTS = 0;
  set @GIFTINKINDAPPRAISALREQUIRED = 0;
  set @GIFTFEESANDGIFTFEEDISTRIBUTIONAMOUNTSNOTEQUAL = 0;
  set @GIFTINKINDORGANIZATIONFAIRMARKETVALUE = 0;
  set @TOTALREFUNDED = 0;
  set @OVERAGEKEPT = 0;

  declare @ORGANIZATIONAMOUNT money = null;

  select
    @CONSTITUENTISGROUP = (ISGROUP|ISORGANIZATION),
    @CONSTITUENTNAME = CONSTITUENT.NAME
  from dbo.CONSTITUENT
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
  where FINANCIALTRANSACTION.ID = @ID
  and FINANCIALTRANSACTION.DELETEDON is null;

  --Replaces revenue is the PK FK in many tables

  declare @REVENUEPAYMENTMETHODID uniqueidentifier;

  declare @ISMEMBERSHIPPLEDGE bit = 0;    

  --Get general fields that are valid for all views

  select top 1
    @DATALOADED = 1,
    @AMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
    @RECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT,
    @BASEAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT,
    @ORGANIZATIONAMOUNT = FINANCIALTRANSACTION.ORGAMOUNT,
    @REVENUEID = FINANCIALTRANSACTION.ID,
    @TYPE = FINANCIALTRANSACTION.TYPECODE,
    @TRANSACTIONTYPE = FINANCIALTRANSACTION.TYPE,
    @DATE = FINANCIALTRANSACTION.DATE, 
    @POSTDATE = FINANCIALTRANSACTION.POSTDATE,
    @BATCHNUMBER = REVENUE_EXT.BATCHNUMBER,
    @REVENUEPAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID,
    @PAYMENTMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
    @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
    @SALEAMOUNT = 0,
    @APPEAL = '',
    @ELIGIBLEFORMATCHINGGIFTCLAIM = REVENUE_EXT.ELIGIBLEFORMATCHINGGIFTCLAIM,
    @LOOKUPID = FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID,
    @BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    @HASINVALIDPAPERLESSMANDATES = 0,
    @RECEIPTTYPE = REVENUE_EXT.RECEIPTTYPE,
    @GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY
  from 
    dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
  where 
    FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) 
    and FINANCIALTRANSACTION.DELETEDON is null;
  -- Set up security on elements that were moved out of there previous form such  as the designations datalist, payment information and sepa information.

  if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
      begin
        set @USER_GRANTED_DESIGNATIONS_DATALIST = 1;
        set @USER_GRANTED_PAYMENTINFORMATION_VIEW = 1;
      end
  else 
      begin
        set @USER_GRANTED_DESIGNATIONS_DATALIST = dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'38FE7974-263F-4C47-ACEE-5577A6BC2DBC');
        set @USER_GRANTED_PAYMENTINFORMATION_VIEW = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'bafca6f5-4cdb-4173-aece-113713d38bfb');
      end    
  if @USER_GRANTED_PAYMENTINFORMATION_VIEW = 1
  begin
      if @PAYMENTMETHODCODE = 1
        select 
          @CHECKDATE = [CHECK].CHECKDATE,
          @CHECKNUMBER = [CHECK].CHECKNUMBER
        from
          dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          inner join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID 
        where FINANCIALTRANSACTION.ID = @ID;
      else if @PAYMENTMETHODCODE = 2 --credit card

      begin
        if @TYPE = 2 -- cc recurring gift

        begin
            select
                @CARDHOLDERNAME = case when REVENUESCHEDULE.CREDITCARDID is null then CCD.CARDHOLDERNAME else [CREDITCARD].CARDHOLDERNAME end,
                @CREDITCARDNUMBER = case when REVENUESCHEDULE.CREDITCARDID is null then CCD.CREDITCARDPARTIALNUMBER else (case when len(coalesce([CREDITCARD].CREDITCARDPARTIALNUMBER, '')) = 0 then ''
                        else replicate('*', 16 - len([CREDITCARD].CREDITCARDPARTIALNUMBER)) + [CREDITCARD].CREDITCARDPARTIALNUMBER end) end,
                @CREDITTYPE = dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(case when REVENUESCHEDULE.CREDITCARDID is null then CCD.CREDITTYPECODEID else CREDITCARD.CREDITTYPECODEID end),
                @EXPIRESON = case when REVENUESCHEDULE.CREDITCARDID is null then CCD.EXPIRESON else [CREDITCARD].EXPIRESON end,
                @ISPARTIALCC = case when REVENUESCHEDULE.CREDITCARDID is null then 1 else 0 end
            from dbo.FINANCIALTRANSACTION
                inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
                left join dbo.REVENUESCHEDULE on FINANCIALTRANSACTION.ID = REVENUESCHEDULE.ID
                left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
                left join dbo.CREDITCARDPAYMENTMETHODDETAIL CCD on REVENUEPAYMENTMETHOD.ID = CCD.ID
            where FINANCIALTRANSACTION.ID = @ID

            -- if there are credit card updates, get the latest one

            select top 1
                            @CREDITCARDUPDATEDATE = CCU.DATEPROCESSED
            from
              dbo.FINANCIALTRANSACTION FT
              inner join dbo.REVENUESCHEDULE RS on RS.ID = FT.ID
              inner join dbo.CREDITCARDUPDATE CCU on CCU.CREDITCARDID = RS.CREDITCARDID
            where
              FT.ID = @ID
              and FT.DELETEDON is null
                            and CCU.STATUSCODE = 1 --Updated

            order by
              CCU.DATEPROCESSED desc,
              CCU.DATEADDED desc

        end
        else --cc payment

            select
                @CARDHOLDERNAME = [CREDIT].CARDHOLDERNAME,
                @CREDITCARDNUMBER = case when len(coalesce([CREDIT].CREDITCARDPARTIALNUMBER, '')) = 0 then ''
                        else replicate('*', 16 - len([CREDIT].CREDITCARDPARTIALNUMBER)) + [CREDIT].CREDITCARDPARTIALNUMBER end,
                @CREDITTYPE = [CREDITTYPE].DESCRIPTION,
                @AUTHORIZATIONCODE = [CREDIT].AUTHORIZATIONCODE,
                @EXPIRESON = [CREDIT].EXPIRESON
            from
                dbo.FINANCIALTRANSACTION
                inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
                inner join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
                left join dbo.CREDITTYPECODE as [CREDITTYPE] on [CREDITTYPE].ID = [CREDIT].CREDITTYPECODEID
            where FINANCIALTRANSACTION.ID = @ID;
      end
      else if @PAYMENTMETHODCODE = 4
        select
          @ISSUER = [STOCK].ISSUER,
          @NUMBEROFUNITS = case when [STOCK].NUMBEROFUNITS <> 0 then [STOCK].NUMBEROFUNITS else null end, 
          @SYMBOL = [STOCK].SYMBOL,
          @MEDIANPRICE = [STOCK].TRANSACTIONMEDIANPRICE,
          @SALEAMOUNT = coalesce((select sum(TRANSACTIONSALEAMOUNT) from dbo.STOCKSALE where STOCKDETAILID = REVENUEPAYMENTMETHOD.ID), 0)
        from
          dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          inner join dbo.STOCKDETAIL as [STOCK] on [STOCK].ID = REVENUEPAYMENTMETHOD.ID
        where FINANCIALTRANSACTION.ID = @ID;
      else if @PAYMENTMETHODCODE = 5
        select
          @PROPERTYSUBTYPE = PROPERTYSUBTYPECODE.DESCRIPTION,
          @SALEAMOUNT = case when PROPERTY.ISSOLD = 1 then [PROPERTY].TRANSACTIONSALEAMOUNT else 0 end
        from
          dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          inner join dbo.PROPERTYDETAIL as [PROPERTY] on [PROPERTY].ID = REVENUEPAYMENTMETHOD.ID 
          left join dbo.PROPERTYSUBTYPECODE on PROPERTYSUBTYPECODE.ID = [PROPERTY].PROPERTYSUBTYPECODEID
        where FINANCIALTRANSACTION.ID = @ID;
      else if @PAYMENTMETHODCODE = 6
        select
          @GIFTINKINDSUBTYPE = [GIK].DESCRIPTION,
          @GIFTINKINDITEMNAME = GIFTINKINDPAYMENTMETHODDETAIL.ITEMNAME,
          @GIFTINKINDDISPOSITION = GIFTINKINDPAYMENTMETHODDETAIL.DISPOSITION,
          @GIFTINKINDNUMBEROFUNITS = GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS,
          @GIFTINKINDFAIRMARKETVALUE = GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONFAIRMARKETVALUE,
          @SALEAMOUNT = coalesce((select sum(TRANSACTIONSALEAMOUNT) from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID), 0),
          @GIFTINKINDORGANIZATIONFAIRMARKETVALUE = GIFTINKINDPAYMENTMETHODDETAIL.ORGANIZATIONFAIRMARKETVALUE
        from
          dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
          left join dbo.GIFTINKINDSUBTYPECODE as [GIK] on [GIK].ID = GIFTINKINDPAYMENTMETHODDETAIL.GIFTINKINDSUBTYPECODEID
        where FINANCIALTRANSACTION.ID = @ID;
      else if @PAYMENTMETHODCODE = 10
        select
          @OTHERPAYMENTMETHODCODE = OTHERTYPE.DESCRIPTION,
          @REFERENCENUMBER = OTHER.REFERENCENUMBER,
          @REFERENCEDATE = OTHER.REFERENCEDATE
        from
          dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          inner join dbo.OTHERPAYMENTMETHODDETAIL as [OTHER] on [OTHER].ID = REVENUEPAYMENTMETHOD.ID 
          inner join dbo.OTHERPAYMENTMETHODCODE as [OTHERTYPE] on [OTHERTYPE].ID = [OTHER].OTHERPAYMENTMETHODCODEID
        where FINANCIALTRANSACTION.ID = @ID;
      else if @PAYMENTMETHODCODE = 101
        select
          @REFERENCENUMBER = PAYPAL.REFERENCENUMBER,
          @REFERENCEDATE = PAYPAL.REFERENCEDATE
        from
          dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          inner join dbo.PAYPALPAYMENTMETHODDETAIL as [PAYPAL] on [PAYPAL].ID = REVENUEPAYMENTMETHOD.ID
        where FINANCIALTRANSACTION.ID = @ID;
      else if @PAYMENTMETHODCODE = 102
        select
          @REFERENCENUMBER = VENMO.REFERENCENUMBER,
          @REFERENCEDATE = VENMO.REFERENCEDATE
        from
          dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          inner join dbo.VENMOPAYMENTMETHODDETAIL as [VENMO] on [VENMO].ID = REVENUEPAYMENTMETHOD.ID
        where FINANCIALTRANSACTION.ID = @ID;
    end --@USER_GRANTED_PAYMENTINFORMATION_VIEW

    else
      SET @PAYMENTMETHODCODE = 99;

  set @RECEIPTSTATUS = dbo.UFN_REVENUE_GETRECEIPTSTATUS(@ID);
  set @ACKNOWLEDGEMENTSTATUS = dbo.UFN_REVENUETRANSACTION_GETACKNOWLEDGESTATUS(@ID)

  select top 1 
    @RECEIPTNUMBER = case left(@RECEIPTSTATUS,9) when 'Receipted' then RR.RECEIPTNUMBER else null end, 
    @RECEIPTDATE = case left(@RECEIPTSTATUS,9) when 'Receipted' then RR.RECEIPTDATE else null end,
    @UNIQUERECEIPTNUMBER = case left(@RECEIPTSTATUS,9) when 'Receipted' then RR.UNIQUERECEIPTNUMBER else null end
  from dbo.REVENUERECEIPT RR 
  where RR.REVENUEID = @ID 
  order by RR.RECEIPTPROCESSDATE desc;

  select top 1 @ACKNOWLEDGEMENTDATE = ACKNOWLEDGEDATE 
  from dbo.REVENUELETTER 
  where REVENUELETTER.REVENUEID = @ID

  set @VATAMOUNT = isnull((select REVENUEVAT.TRANSACTIONVATAMOUNT from dbo.REVENUEVAT where dbo.REVENUEVAT.ID = @ID), 0);

  select top 1 @GLBATCHLINKID = GLBATCH.ID, @GLBATCHLOOKUPID = GLBATCH.LOOKUPID 
  from dbo.REVENUEPOSTEDDETAIL
  inner join dbo.FINANCIALTRANSACTION REVENUEPOSTED on REVENUEPOSTED.ID = REVENUEPOSTEDDETAIL.REVENUEPOSTEDID 
  inner join dbo.GLBATCH on GLBATCH.ID = REVENUEPOSTEDDETAIL.GLBATCHID
  where REVENUEPOSTED.ID = @ID 
    and REVENUEPOSTED.POSTSTATUSCODE = 2 and REVENUEPOSTED.DELETEDON is null;

  select @CATEGORYDESCRIPTION = GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME
  from dbo.REVENUECATEGORY
  inner join dbo.GLREVENUECATEGORYMAPPING on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
  where REVENUECATEGORY.ID = @ID;

  if @TYPE = 15
    set @ISMEMBERSHIPPLEDGE = 1

  declare @COUNTFTPS as int = 0

    select
        @POSTSTATUSCODE = case
            when COUNT(T.FTPS) = 0 then 3
            when MIN(T.FTPS) <> 2 then MIN(T.FTPS)
            else
                case
                    when MIN(T.LIPS) = 1 then 4
                    when MAX(T.LIPS) = 3 then 5
                    else 2
                end
            end,
        @COUNTFTPS = COUNT(T.FTPS)
    from
        (
        --Below select statement will list line items that are not equal to "not posted"

        select FT.POSTSTATUSCODE FTPS, LI.POSTSTATUSCODE LIPS
        from dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
            inner join dbo.JOURNALENTRY on LI.ID = FINANCIALTRANSACTIONLINEITEMID
        where FT.ID = @ID and LI.DELETEDON is null

        union all

        select FT.POSTSTATUSCODE FTPS, LI.POSTSTATUSCODE LIPS
        from dbo.FINANCIALTRANSACTION CHILD
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CHILD.PARENTID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = CHILD.ID
            inner join dbo.JOURNALENTRY on LI.ID = FINANCIALTRANSACTIONLINEITEMID
        where CHILD.PARENTID = @ID and LI.DELETEDON is null

        union all

        -- Below is specifically for Benefit adjustments, they do not satisfy the first select since they are adjustments (typecode = 1) and they do not satisfy the second select because they do not have their own financial transaction row

        -- When the only action is removing the posted benefit there is no new FTLI to enable 'Posted (adjustment pending)' therefore the below select will catch the benefit adjustment

        select FT.POSTSTATUSCODE FTPS, LI.POSTSTATUSCODE LIPS
        from dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
            inner join dbo.BENEFITADJUSTMENT BA on BA.REVENUEID = FT.ID
            inner join dbo.JOURNALENTRY on LI.ID = FINANCIALTRANSACTIONLINEITEMID
        where FT.ID = @ID and LI.DELETEDON is null and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = BA.ID) T;

    if (@COUNTFTPS = 0)
    begin
        select 
            @POSTSTATUSCODE = case
                when COUNT(T.FTPS) = 0 then 3
                when MIN(T.FTPS) <> 2 then MIN(T.FTPS)
                else
                    case
                        when MIN(T.LIPS) = 1 then 4
                        when MAX(T.LIPS) = 3 then 5
                        else 2
                    end
                end
        from
            (
            --Below select statement will list line items that are not equal to "not posted"

            select FT.POSTSTATUSCODE FTPS, LI.POSTSTATUSCODE LIPS
            from dbo.FINANCIALTRANSACTION FT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
            where FT.ID = @ID and LI.DELETEDON is null and LI.TYPECODE != 1

            union all

            select FT.POSTSTATUSCODE FTPS, LI.POSTSTATUSCODE LIPS
            from dbo.FINANCIALTRANSACTION CHILD
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CHILD.PARENTID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = CHILD.ID
            where CHILD.PARENTID = @ID and LI.DELETEDON is null

            union all

            -- Below is specifically for Benefit adjustments, they do not satisfy the first select since they are adjustments (typecode = 1) and they do not satisfy the second select because they do not have their own financial transaction row

            -- When the only action is removing the posted benefit there is no new FTLI to enable 'Posted (adjustment pending)' therefore the below select will catch the benefit adjustment

            select FT.POSTSTATUSCODE FTPS, LI.POSTSTATUSCODE LIPS
            from dbo.FINANCIALTRANSACTION FT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
                inner join dbo.BENEFITADJUSTMENT BA on BA.REVENUEID = FT.ID
            where FT.ID = @ID and LI.DELETEDON is null and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = BA.ID) T;

    end


  set @POSTSTATUS = case
    when @POSTSTATUSCODE = 1 then 'Not posted'
      when @POSTSTATUSCODE = 2 then 'Posted'
      when @POSTSTATUSCODE = 3 then 'Do not post'
      when @POSTSTATUSCODE = 4 then 'Posted (adjustment pending)'
      when @POSTSTATUSCODE = 5 then 'Posted (adjustment set to do not post)'
    end

  declare @ISPAYMENT bit
  select @ISPAYMENT = case when @TYPE = 0 then 1 else 0 end
  set @HASGIFTAIDSPLITONPENDINGR68PROCESS = dbo.UFN_REVENUE_HASGIFTAIDSPLITONPENDINGR68(@REVENUEID, @ISPAYMENT)

  --Assuming all payment information matches across the transaction

  if @PAYMENTMETHODCODE = 0 --Cash

    select 
      @REFERENCENUMBER = REFERENCENUMBER,
      @REFERENCEDATE = REFERENCEDATE
    from dbo.CASHPAYMENTMETHODDETAIL 
    where ID = @REVENUEPAYMENTMETHODID;
  else if @PAYMENTMETHODCODE = 1 --Check

  begin
    -- Open the symmetric key for decryption

    exec dbo.USP_GET_KEY_ACCESS;
    select 
      @ACCOUNT = dbo.UFN_CONSTITUENTACCOUNT_GETDESCRIPTION(ACCOUNT.ID) 
    from dbo.CONSTITUENTACCOUNT as [ACCOUNT]
    inner join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].CONSTITUENTACCOUNTID = [ACCOUNT].ID
    where [CHECK].ID = @REVENUEPAYMENTMETHODID;
    close symmetric key sym_BBInfinity;
  end
  else if @PAYMENTMETHODCODE = 3 --Direct Debit

  begin
    -- Open the symmetric key for decryption

    exec dbo.USP_GET_KEY_ACCESS;

    -- Can View Sensitive Information

    set @CANVIEWSENSITIVEINFORMATION = 0;
    declare @ISSYSADMIN bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);

    if @ISSYSADMIN = 0
    begin
         set @CANVIEWSENSITIVEINFORMATION = dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'B01B8750-8BC3-4FA8-881E-BE2E605339BF'); 
    end
    else if @ISSYSADMIN = 1
    begin
        set @CANVIEWSENSITIVEINFORMATION = 1;
    end

    if @TYPE = 2 --recurring gift

      select 
        @FINANCIALINSTITUTION = FI.description,  
        @DIRECTDEBITACCOUNT = coalesce(SUBSTRING(convert(nvarchar(50), DecryptByKey(CA.ACCOUNTNUMBER)),LEN(convert(nvarchar(50), DecryptByKey(CA.ACCOUNTNUMBER)))-3,4),''),
        @REFERENCENUMBER = RS.REFERENCENUMBER,
        @REFERENCEDATE = RS.REFERENCEDATE
      from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT RS
      inner join dbo.CONSTITUENTACCOUNT CA ON CA.ID = RS.CONSTITUENTACCOUNTID
      inner join dbo.FINANCIALINSTITUTION FI ON FI.ID = CA.FINANCIALINSTITUTIONID
      where RS.ID = @ID;
    else
      select 
        @REFERENCENUMBER = DIRECTDEBITPAYMENTMETHODDETAIL.REFERENCENUMBER,
        @REFERENCEDATE = DIRECTDEBITPAYMENTMETHODDETAIL.REFERENCEDATE,
        @CONSTITUENTACCOUNTID = DIRECTDEBITPAYMENTMETHODDETAIL.CONSTITUENTACCOUNTID,
        @ACCOUNT = dbo.UFN_CONSTITUENTMASKEDACCOUNT_GETDESCRIPTION(DIRECTDEBITPAYMENTMETHODDETAIL.CONSTITUENTACCOUNTID),
        @DIRECTDEBITRESULTCODE = DIRECTDEBITPAYMENTMETHODDETAIL.DIRECTDEBITRESULTCODE,
        @DIRECTDEBITISREJECTED = DIRECTDEBITPAYMENTMETHODDETAIL.ISREJECTED,
        @SEPAMANDATELOOKUPID = SEPAMANDATE.LOOKUPID,
        @SEPAMANDATESIGNATUREDATE = SEPAMANDATE.SIGNATUREDATE,
        @SEPAMANDATESTATUSCODE = SEPAMANDATE.STATUSCODE,
        @UNMASKEDACCOUNT =(
        case 
        when @CANVIEWSENSITIVEINFORMATION = 1 then
        dbo.UFN_CONSTITUENTACCOUNT_GETDESCRIPTION(DIRECTDEBITPAYMENTMETHODDETAIL.CONSTITUENTACCOUNTID)
        when @CANVIEWSENSITIVEINFORMATION = 0 then
        dbo.UFN_CONSTITUENTMASKEDACCOUNT_GETDESCRIPTION(DIRECTDEBITPAYMENTMETHODDETAIL.CONSTITUENTACCOUNTID) 
        end)
      from 
        dbo.DIRECTDEBITPAYMENTMETHODDETAIL
      left join dbo.SEPAMANDATE on SEPAMANDATE.ID = DIRECTDEBITPAYMENTMETHODDETAIL.SEPAMANDATEID
      where DIRECTDEBITPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID;

  select @ACCOUNTNUMBERLEN = LEN(coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),''))
  from
  dbo.CONSTITUENTACCOUNT
  where CONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID;

    close symmetric key sym_BBInfinity;
  end
  else if @PAYMENTMETHODCODE = 11 --Standing order

  begin
    exec dbo.USP_GET_KEY_ACCESS;

    if @TYPE = 2 --Only payments have a record in the STANDINGORDERPAYMENTMETHODDETAIL so gather it this way

      select
          @FINANCIALINSTITUTION = FI.DESCRIPTION,  
          @ACCOUNT = coalesce(SUBSTRING(convert(nvarchar(50), DecryptByKey(CA.ACCOUNTNUMBER)),LEN(convert(nvarchar(50), DecryptByKey(CA.ACCOUNTNUMBER)))-3,4),''),
                  @REFERENCEDATE = REFERENCEDATE,
     @REFERENCENUMBER = dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(RP.ID),
          @STANDINGORDERSETUP = STANDINGORDERSETUP,
          @STANDINGORDERSETUPDATE = STANDINGORDERSETUPDATE
      from dbo.REVENUESCHEDULESTANDINGORDERPAYMENT RP
      inner join dbo.CONSTITUENTACCOUNT CA ON CA.ID = RP.CONSTITUENTACCOUNTID
      inner join dbo.FINANCIALINSTITUTION FI ON FI.ID = CA.FINANCIALINSTITUTIONID
      where RP.ID = @ID;
    else
      select 
        --JamesWill 2009-06-03 Only pledges or recurring gift standing orders have a reference number

        @REFERENCENUMBER = case when @TYPE = 1 then dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(@ID) else N'' end ,
        @REFERENCEDATE = REFERENCEDATE,
        @ACCOUNT = dbo.UFN_CONSTITUENTACCOUNT_GETDESCRIPTION(CONSTITUENTACCOUNTID)
      from dbo.STANDINGORDERPAYMENTMETHODDETAIL
      where ID = @REVENUEPAYMENTMETHODID;

    close symmetric key sym_BBInfinity;
  end

  if @PAYMENTMETHODCODE = 4 --Sold stock

  begin
    if exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)
    begin
      set @ISSOLDSTOCK = 1

      if exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0)
        set @ISPOSTEDSOLDSTOCK = 1
      else
        set @ISPOSTEDSOLDSTOCK = 0
    end
  end

  if @PAYMENTMETHODCODE = 5  --Sold property

  begin
    if exists(select ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1)
    begin
      set @ISSOLDPROPERTY = 1

      if (select SALEPOSTSTATUSCODE from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID) = 0
        set @ISPOSTEDSOLDPROPERTY = 1
      else
        set @ISPOSTEDSOLDPROPERTY = 0
    end
  end

  if @PAYMENTMETHODCODE = 6 --Gift-in-kind

  begin
    declare @USEAPPRAISALTHRESHOLD bit;
    declare @APPRAISALTHRESHOLD money;

    select top 1
      @USEAPPRAISALTHRESHOLD = USEAPPRAISALTHRESHOLD,
      @APPRAISALTHRESHOLD = APPRAISALTHRESHOLD
    from
      dbo.GIFTINKINDAPPRAISALSETTINGS

    if (
        (@USEAPPRAISALTHRESHOLD = 1) and 
        (@GIFTINKINDDISPOSITION = 'To sell') and 
        (@GIFTINKINDORGANIZATIONFAIRMARKETVALUE > @APPRAISALTHRESHOLD) and 
        not exists (select top(1) 1 from dbo.GIFTINKINDAPPRAISAL where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID)
    )
      set @GIFTINKINDAPPRAISALREQUIRED = 1;

    if exists(select ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID) --Sold GIFTINKIND

    begin
      set @ISSOLDGIFTINKIND = 1

      if exists(select ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0)
        set @ISPOSTEDSOLDGIFTINKIND = 1
      else
        set @ISPOSTEDSOLDGIFTINKIND = 0

    end
  end

  --Assuming only one revenue per transaction for types other than payment

  if @TYPE = 1 -- Pledge

  begin
    select 
      @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
      @TOTALPAID = dbo.UFN_PLEDGE_GETAMOUNTPAID(FINANCIALTRANSACTION.ID),
      @PASTDUE = dbo.UFN_PLEDGE_GETPASTDUEAMOUNT(FINANCIALTRANSACTION.ID),
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @SENDPLEDGEREMINDER = REVENUESCHEDULE.SENDPLEDGEREMINDER,
      @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
      @SOURCECODE = REVENUE_EXT.SOURCECODE,
      @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
      @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(MAILINGID),
      @CHANNEL = CHANNELCODE.DESCRIPTION,
      @PLEDGESUBTYPE = PLEDGESUBTYPE.NAME
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
    left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = REVENUESCHEDULE.PLEDGESUBTYPEID
    left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
    where 
      FINANCIALTRANSACTION.ID = @REVENUEID
      and FINANCIALTRANSACTION.DELETEDON is null;

    if @ISPENDING = 1
    begin
        select top 1
                  @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
                from 
                  dbo.BATCH
                  inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
                  left join dbo.BATCHREVENUEAPPLICATION as [BATCHAPP] on [BATCHAPP].BATCHREVENUEID = BATCHREVENUE.ID 
                where 
                  BATCH.STATUSCODE not in (1, 2) 
                  and (
                      ([BATCHAPP].REVENUEID = @REVENUEID and BATCHREVENUE.PAYINGPENDINGREVENUEID is null and [BATCHAPP].WASGENERATED = 1) 
                      or
                      ([BATCHAPP].REVENUEID is null and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID)
                      or
                      ([BATCHAPP].REVENUEID = @REVENUEID and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID)
                      or
                      ([BATCHAPP].REVENUEID <> BATCHREVENUE.PAYINGPENDINGREVENUEID and [BATCHAPP].REVENUEID = @REVENUEID)                                               
                    )
    end

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
      set @PLEDGETOTALWITHGIFTAID = dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETOTALPAID(@ID);
      set @PLEDGERECURRINGGIFTGROSSAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(@ID, 1);

    if @PAYMENTMETHODCODE = 3 and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
    begin
      --Bug 138736 - AdamBu 2/2/11 - Check for invalid paperless mandate setup.

      set @HASINVALIDPAPERLESSMANDATES = 1
    end

    end
  end

  if @TYPE = 3 -- MG Pledge

  begin
    declare @ISACTIVE bit;

    select 
      @DATALOADED = 1,
      @TOTALPAID = coalesce((select sum(INSTALLMENTPAYMENT.AMOUNT) from dbo.INSTALLMENTPAYMENT where INSTALLMENTPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID), 0),
      @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
      @MATCHEDREVENUEID = MGREVENUE.ID,
      @MATCHEDREVENUEDETAILID = MGREVENUE.ID,
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
      @SOURCECODE = REVENUE_EXT.SOURCECODE,
      @ISACTIVE = RMG.ISACTIVE
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.REVENUEMATCHINGGIFT RMG on FINANCIALTRANSACTION.ID = RMG.ID
    inner join dbo.REVENUE MGREVENUE on RMG.MGSOURCEREVENUEID = MGREVENUE.ID
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
    where FINANCIALTRANSACTION.ID = @REVENUEID
        and FINANCIALTRANSACTION.DELETEDON is null;


    --INPROGRESS

    select @MATCHEDREVENUE
       = Cast(REVENUE.TRANSACTIONAMOUNT as nvarchar(20)) + ' ' + REVENUE.TRANSACTIONTYPE + ' for ' + CONSTITUENT.NAME
    from dbo.REVENUE 
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
    where REVENUE.ID = @MATCHEDREVENUEDETAILID;

    select
      top 1 
      @PAYMENTID = REVENUE.ID 
    from dbo.INSTALLMENTPAYMENT
    inner join dbo.REVENUE on REVENUE.ID = INSTALLMENTPAYMENT.PAYMENTID                    
    where REVENUE.ID = @REVENUEID
    order by REVENUE.DATE desc;

    if @ISPENDING = 1
      select top 1
        @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
      from dbo.BATCH
      inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
      inner join dbo.BATCHREVENUEAPPLICATION as [APP] on [APP].BATCHREVENUEID = BATCHREVENUE.ID 
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = [APP].REVENUEID
      where BATCH.STATUSCODE not in (1, 2) and FINANCIALTRANSACTION.ID = @REVENUEID;

    --MGSTATUSCODE: 0 active

    --MGSTATUSCODE: 1 paid in full

    --MGSTATUSCODE: 2 inactive

    --MGSTATUSCODE: 3 inactive (Partially paid)

    if @ISACTIVE = 1 and @BALANCE <> 0 
      set @MGSTATUSCODE = 0;
    else if @ISACTIVE = 1 and @BALANCE = 0
      set @MGSTATUSCODE = 1;
    else if @ISACTIVE = 0 and @TOTALPAID = 0
      set @MGSTATUSCODE = 2;
    else if @ISACTIVE = 0
      set @MGSTATUSCODE = 3;
  end

  if @TYPE = 2 -- Recurring Gift

  begin
    -- Next transaction and Last payment as well as sequence numbers

    declare @NEXTTRANSACTIONID uniqueidentifier = null
    declare @LASTPAYMENTID uniqueidentifier = null

    select top 1 @LASTPAYMENTDATE = RGI.DATE, 
                 @LASTPAYMENTID = RGI.ID 
    from dbo.RECURRINGGIFTINSTALLMENTPAYMENT RGP
    inner join dbo.RECURRINGGIFTINSTALLMENT RGI on RGI.ID = RGP.RECURRINGGIFTINSTALLMENTID
    where RGI.REVENUEID = @REVENUEID
    order by RGI.DATE desc

    select @NEXTTRANSACTION = DATE,
           @NEXTTRANSACTIONID = ID
    from dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(@REVENUEID,null);

    --Find the row numbers that correspond to next transaction and last payment

    with RG_CTE(ROWNUM,TID) as
      (select row_number() over (order by RGI.DATE) ROWNUM, RGI.ID TID
       from dbo.RECURRINGGIFTINSTALLMENT RGI
       where RGI.REVENUEID = @REVENUEID) 
    select 
      @NEXTTRANSACTIONSEQUENCE = (select RG_CTE.ROWNUM from RG_CTE where @NEXTTRANSACTIONID = TID),
      @LASTPAYMENTSEQUENCE = (select RG_CTE.ROWNUM from RG_CTE where @LASTPAYMENTID = TID);

    -- Recurring gifts fields

    select
      @DATALOADED = 1,
      @TOTALPAID = coalesce((select sum(RECURRINGGIFTACTIVITY.AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = REVENUE.ID and TYPECODE = 0), 0),
      @FREQUENCY = REVENUESCHEDULE.FREQUENCY,
      @STARTDATE = REVENUESCHEDULE.STARTDATE,
      @ENDDATE = REVENUESCHEDULE.ENDDATE,
      @STATUS = case REVENUESCHEDULE.STATUSCODE when 3 then 'Canceled' else REVENUESCHEDULE.STATUS end,
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE.APPEALID),
      @SOURCECODE = REVENUE.SOURCECODE,
      @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
      @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(MAILINGID),
      @CHANNEL = CHANNELCODE.DESCRIPTION
    from dbo.REVENUE
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
    left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE.CHANNELCODEID
    where REVENUE.ID = @REVENUEID;

    if @ISPENDING = 1
      select top 1
                    @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
                  from 
                    dbo.BATCH
                    inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
                    left join dbo.BATCHREVENUEAPPLICATION as [BATCHAPP] on [BATCHAPP].BATCHREVENUEID = BATCHREVENUE.ID 
                  where 
                    BATCH.STATUSCODE not in (1, 2) 
                    and (
                        ([BATCHAPP].REVENUEID = @REVENUEID and BATCHREVENUE.PAYINGPENDINGREVENUEID is null and [BATCHAPP].WASGENERATED = 1) 
                        or
                        ([BATCHAPP].REVENUEID is null and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID)
                        or
                        ([BATCHAPP].REVENUEID = @REVENUEID and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID)
                        or
                        ([BATCHAPP].REVENUEID <> BATCHREVENUE.PAYINGPENDINGREVENUEID and [BATCHAPP].REVENUEID = @REVENUEID)                       
                      )

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1 --UK

    begin
      set @RECURRINGGIFTTOTALWITHGIFTAID = dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATERECURRINGGIFTTOTALPAID(@ID);
      set @PLEDGERECURRINGGIFTGROSSAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(@ID, 1, 1);

      if @PAYMENTMETHODCODE = 3 and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
      begin        
        --Bug 138736 - AdamBu 2/2/11 - Check for invalid paperless mandate setup.

        set @HASINVALIDPAPERLESSMANDATES = 1
      end
    end

    --Past Due

    if exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @REVENUEID)
    begin
      set @RGHASINSTALLMENTS = 1;
      set @PASTDUE = dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_2(@REVENUEID,null, 0);

      -- always show past due regardless of RG setting.

      set @SHOWRGPASTDUE = 1;
    end

    --Designation collection

    if @USER_GRANTED_DESIGNATIONS_DATALIST = 1
        set @DESIGNATIONS = (
                    select
                        [SPLITS].ID,
                        dbo.UFN_DESIGNATION_BUILDNAME([SPLITS].DESIGNATIONID) as 'DESIGNATION',
                        [SPLITS].TRANSACTIONAMOUNT as 'AMOUNT',
                        [SPLITS].TRANSACTIONCURRENCYID
                    from dbo.REVENUESPLIT as [SPLITS] 
                    where [SPLITS].REVENUEID = @REVENUEID and [SPLITS].DESIGNATIONID is not null
                    order by Designation, [SPLITS].DATEADDED
        for xml raw('ITEM'),
                    type,
                    elements,
                    root('DESIGNATIONS'),
                    binary BASE64);

    -- Other recurring gifts collection                    

    set @OTHERRECURRINGGIFTS = (
                select
                  R.ID,
                  case RS.STATUSCODE when 3 then 'Canceled' else RS.STATUS end STATUS,
                  R.TRANSACTIONAMOUNT AMOUNT,
                  RS.FREQUENCY,
                  (select TOP 1  RGI.DATE 
                   from dbo.RECURRINGGIFTINSTALLMENTPAYMENT RGP
                   inner join dbo.RECURRINGGIFTINSTALLMENT RGI on RGI.ID = RGP.RECURRINGGIFTINSTALLMENTID
                   where RGI.REVENUEID = R.ID
                   order by RGI.DATE desc) LASTPAYMENTDATE,
                   R.TRANSACTIONCURRENCYID
                from REVENUE R
                inner join dbo.REVENUESCHEDULE RS on RS.ID=R.ID
                where R.CONSTITUENTID = (SELECT CONSTITUENTID FROM REVENUE WHERE ID=@REVENUEID)  
                and R.TRANSACTIONTYPECODE = 2
                and R.ID <> @REVENUEID
                order by case RS.STATUSCODE
                          when 0 then 0
                          when 5 then 1
                          when 1 then 2
                          when 3 then 3
                          when 2 then 4 end,
                        LASTPAYMENTDATE DESC
                for xml raw('ITEM'),
                            type,
                            elements,
                            root('OTHERRECURRINGGIFTS'),
                            binary BASE64);

    select 
        @SPONSORSHIPID = SPONSORSHIP.ID
    from 
        dbo.FINANCIALTRANSACTIONLINEITEM LI
    inner join 
        dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = LI.ID
    inner join 
        dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
    where 
        LI.FINANCIALTRANSACTIONID = @REVENUEID
        and REVENUESPLIT.TYPECODE = 9
        and SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1
        and LI.DELETEDON is null
        and LI.TYPECODE != 1;

    set @RECURRINGGIFTHASCONTRIBUTEDPORTION = 0;
    if exists
    (
        select 
            top 1 1
        from 
            dbo.FINANCIALTRANSACTIONLINEITEM
        inner join 
            dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        where 
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and REVENUESPLIT_EXT.TYPECODE in(0,9)
    )
        set @RECURRINGGIFTHASCONTRIBUTEDPORTION = 1
  end

  if @TYPE = 0 --payment

  begin
    --Gift Aid is for UK only

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
      select @TAXCLAIMNUMBER = dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMNUMBER(@ID);
      select @SHOWPOTENTIAL = dbo.UFN_GIFTAIDREVENUESPLIT_SHOWTOTALSASPOTENTIAL(@ID);

      -- If the tax claim amount shows as potential because there isn't a valid declaration, 

      -- only include splits that don't have a valid declaration so that covenant gifts are excluded

      if @SHOWPOTENTIAL = 1

        select @TAXCLAIMAMOUNT = coalesce(sum(TRANSACTIONTAXCLAIMAMOUNT), 0)
        from dbo.REVENUESPLITGIFTAID
        inner join dbo.REVENUESPLIT on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
        where
          REVENUESPLIT.REVENUEID = @ID and
          (REVENUESPLITGIFTAID.RULES_STATUS & REVENUESPLITGIFTAID.ATTRIBUTES_STATUS) = 1 and
          dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITY(REVENUESPLIT.ID) = 'No valid declaration';
      else
        set @TAXCLAIMAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMAMOUNT_2(@ID, 0, 1);

      select @GROSSAMOUNT = @AMOUNT + @TAXCLAIMAMOUNT;
    end

    select @GIFTFEE_ENABLED = dbo.UFN_GIFTFEE_ENABLED();

    if @GIFTFEE_ENABLED = 1
      select @GIFTFEEAMOUNT = coalesce(sum(REVENUESPLITGIFTFEE.TRANSACTIONAMOUNT), 0.00) 
      from dbo.REVENUESPLITGIFTFEE 
      inner join dbo.REVENUESPLIT on REVENUESPLITGIFTFEE.ID = REVENUESPLIT.ID
      where REVENUESPLIT.REVENUEID = @ID;

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

      select @REFERENCE = REFERENCE
      from dbo.REVENUEREFERENCE
      where ID = @ID;
    end

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

    if @DEPOSITID is not null
      set @DEPOSITNAME = dbo.UFN_BANKACCOUNT_GETDEPOSITNAME(@DEPOSITID);

    -- If the revenue isn't going to post, there isn't a need for the distributions amount

    -- to match the gift fees amount.  Also verify that BasicGL is installed since gift 

    -- distributions are only valid for that GL type.

    if (select DONOTPOST from dbo.REVENUE where ID = @ID) = 0 and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1
    begin
      declare @DISTRIBUTIONSUM money;
      select @DISTRIBUTIONSUM = sum(AMOUNT)
      from dbo.GIFTFEEGLDISTRIBUTION
      where
        REVENUEID = @ID and
        TRANSACTIONTYPECODE = 0 and
        OUTDATED = 0;

      declare @TOTALGIFTFEES money;
      select @TOTALGIFTFEES = sum(FEE)
      from dbo.REVENUESPLITGIFTFEE
      inner join dbo.REVENUESPLIT on REVENUESPLITGIFTFEE.ID = REVENUESPLIT.ID
      where
        REVENUESPLIT.REVENUEID = @ID and
        -- Exclude splits with an application of Order and type of Fee or Taxes since they aren't mapped

        not (REVENUESPLIT.APPLICATIONCODE = 10 and (REVENUESPLIT.TYPECODE = 6 or REVENUESPLIT.TYPECODE = 7));

      if coalesce(@DISTRIBUTIONSUM, 0) <> coalesce(@TOTALGIFTFEES, 0)
        set @GIFTFEESANDGIFTFEEDISTRIBUTIONAMOUNTSNOTEQUAL = 1;
    end
  end

  if @TYPE = 4 -- Planned gift

  begin
    select
      @PLANNEDGIFTVEHICLECODE = VEHICLECODE,
      @PLANNEDGIFTISREVOCABLE = ISREVOCABLE,
      @ISPLANNEDGIFTADDITION = ISADDITION,
      @PLANNEDGIFT_TRUSTHELDOUTSIDE = TRUSTHELDOUTSIDE
    from
        dbo.PLANNEDGIFT
    inner join
        dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PLANNEDGIFTREVENUE on PLANNEDGIFTREVENUE.PLANNEDGIFTID = PLANNEDGIFT.ID
    inner join
        dbo.REVENUE on REVENUE.ID = PLANNEDGIFTREVENUE.REVENUEID
    where
        REVENUE.ID = @REVENUEID;
  end

  if @TYPE = 5 -- Order

  begin
    -- NEEDS TO BE UPDATED FOR MULTICURRENCY

    select
      @SALESORDERID = SALESORDER.ID,
      @TOTALPAID = TOTALS.AMOUNTPAID,
      @BALANCE = TOTALS.BALANCE,
      @TOTALREFUNDED = TOTALS.REFUNDS,
      @OVERAGEKEPT = TOTALS.OVERAGEKEPT,
      @ORDERNUMBER = cast(SALESORDER.SEQUENCEID as nvarchar(30)),
      @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE,
      @ISTAXEXEMPT = case when SALESORDERTAXEXEMPTINFO.ID is null then 0 else 1 end,
      @TAXEXEMPTREASON = SALESORDERTAXEXEMPTREASONCODE.[DESCRIPTION],
      @TAXEXEMPTCOMMENTS = SALESORDERTAXEXEMPTINFO.COMMENTS
    from
      dbo.SALESORDER
    left join
      dbo.SALESORDERTAXEXEMPTINFO on SALESORDERTAXEXEMPTINFO.ID = SALESORDER.ID
    left join
      dbo.SALESORDERTAXEXEMPTREASONCODE on SALESORDERTAXEXEMPTREASONCODE.ID = SALESORDERTAXEXEMPTINFO.SALESORDERTAXEXEMPTREASONCODEID
    outer apply
      dbo.UFN_SALESORDER_TOTALS(SALESORDER.ID) as TOTALS
    where
      SALESORDER.REVENUEID = @REVENUEID;

    --If there are order credits tied to that revenue id, we need

    --to account for those too

    select
      @AMOUNT = @AMOUNT - coalesce(sum([CREDITGLDISTRIBUTION].[AMOUNT]), 0)
    from
      dbo.[CREDITGLDISTRIBUTION]
    where
      [CREDITGLDISTRIBUTION].[REVENUEID] = @REVENUEID and
      [CREDITGLDISTRIBUTION].[OUTDATED] = 0 and
      [CREDITGLDISTRIBUTION].[TRANSACTIONTYPECODE] = 0
  end

  if @TYPE = 6 -- Grant award

  begin
    select 
      @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID),
      @TOTALPAID = dbo.UFN_PLEDGE_GETAMOUNTPAID(REVENUE.ID),
      @PASTDUE = dbo.UFN_PLEDGE_GETPASTDUEAMOUNT(REVENUE.ID),
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @ISREIMBURSABLE = REVENUE.ISREIMBURSABLE
    from dbo.REVENUE                     
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
    where 
      REVENUE.ID = @REVENUEID;

    select top 1
      @OPPORTUNITYNAME = dbo.UFN_OPPORTUNITY_GETLONGDESCRIPTION(OPPORTUNITY.ID),
      @OPPORTUNITYID = OPPORTUNITY.ID
    from
      dbo.OPPORTUNITY
      inner join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITY.ID
    where
      REVENUEOPPORTUNITY.ID in (select ID from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = @ID);

    select top 1
      @FUNDINGREQUESTNAME = GRANTS.TITLE,
      @FUNDINGREQUESTID = FUNDINGREQUEST.ID
    from
      dbo.FUNDINGREQUEST
      inner join dbo.REVENUEFUNDINGREQUEST on REVENUEFUNDINGREQUEST.FUNDINGREQUESTID = FUNDINGREQUEST.ID
      inner join dbo.GRANTS on FUNDINGREQUEST.GRANTSID = GRANTS.ID
    where
      REVENUEFUNDINGREQUEST.ID = @ID;

    if @ISPENDING = 1
      select top 1
        @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
      from dbo.BATCH
      inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
      inner join dbo.BATCHREVENUEAPPLICATION as [APP] on [APP].BATCHREVENUEID = BATCHREVENUE.ID 
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = [APP].REVENUEID
      where BATCH.STATUSCODE not in (1, 2) and FINANCIALTRANSACTION.ID = @REVENUEID;
  end

  if @TYPE = 7 --Auction donation

  begin
    select
      @AUCTIONITEMID = AI.ID,
      @AUCTIONITEMNAME = AI.NAME,
      @AUCTIONITEMCATEGORY = AIC.NAME,
      @AUCTIONITEMSUBCATEGORY = AISC.NAME,
      @AUCTIONEXPIRATIONDATE = AI.EXPIRATIONDATE
    from
      dbo.AUCTIONITEM AI
      left join dbo.AUCTIONITEMCATEGORY AIC on AIC.ID = AI.AUCTIONITEMCATEGORYID
      left join dbo.AUCTIONITEMSUBCATEGORY AISC on AISC.ID = AI.AUCTIONITEMSUBCATEGORYID
    where
      REVENUEAUCTIONDONATIONID = @ID;
  end

  if @TYPE = 15 -- Membership installment plan

  begin
    select 
      @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
      @TOTALPAID = dbo.UFN_PLEDGE_GETAMOUNTPAID(FINANCIALTRANSACTION.ID),
      @PASTDUE = dbo.UFN_PLEDGE_GETPASTDUEAMOUNT(FINANCIALTRANSACTION.ID),
      @ISPENDING = REVENUESCHEDULE.ISPENDING,
      @SENDPLEDGEREMINDER = REVENUESCHEDULE.SENDPLEDGEREMINDER,
      @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
   @SOURCECODE = REVENUE_EXT.SOURCECODE,
      @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
      @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(MAILINGID),
      @CHANNEL = CHANNELCODE.DESCRIPTION
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
    left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = REVENUESCHEDULE.PLEDGESUBTYPEID
    left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
    where 
      FINANCIALTRANSACTION.ID = @REVENUEID
      and FINANCIALTRANSACTION.DELETEDON is null;

    if @ISPENDING = 1
    begin
        select top 1 
          @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER
        from dbo.BATCH
        inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.BATCHID = BATCH.ID and dbo.UFN_MEMBERSHIP_GETPLEDGE(BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID) = @REVENUEID
        where 
          BATCH.STATUSCODE not in (1, 2) 
          and BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID is not null
          and BATCHMEMBERSHIPDUES.REVENUETYPECODE = 1
    end

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
      set @PLEDGETOTALWITHGIFTAID = dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETOTALPAID(@ID);
      set @PLEDGERECURRINGGIFTGROSSAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(@ID, 1);

      if @PAYMENTMETHODCODE = 3 and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
      begin
        --Bug 138736 - AdamBu 2/2/11 - Check for invalid paperless mandate setup.

        set @HASINVALIDPAPERLESSMANDATES = 1
      end
    end
  end
  return 0;