USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONPROFILETA

The load procedure used by the view dataform template "Revenue Transaction Profile Target Associates 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(50)	INOUT	Pending batch number
@DATE	datetime	INOUT	Date
@PAYMENTMETHOD	nvarchar(13)	INOUT	Payment method
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method code
@CHECKDATE	UDT_FUZZYDATE	INOUT	Check date
@CHECKNUMBER	nvarchar(20)	INOUT	Check #
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference number
@REFERENCEDATE	UDT_FUZZYDATE	INOUT	Reference date
@CARDHOLDERNAME	nvarchar(255)	INOUT	Cardholder name
@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	int	INOUT	Number of units
@SYMBOL	nvarchar(4)	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(50)	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	Mailing
@CHANNEL	nvarchar(100)	INOUT	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	Acknowledgments
@MGSTATUSCODE	tinyint	INOUT	Matching gift status
@RECEIPTTYPE	nvarchar(30)	INOUT	Receipt preference
@OTHERPAYMENTMETHODCODE	nvarchar(100)	INOUT	Other method
@FISCALYEARMONTH	UDT_FUZZYDATE	INOUT	Fiscal month/year
@PLEDGESTATUS	nvarchar(100)	INOUT	Pledge status
@TRANSACTIONTYPE	nvarchar(100)	INOUT	Gift type
@CATEGORYDESCRIPTION	nvarchar(100)	INOUT	Revenue category
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONPROFILETA
                (
                    @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(50) = null output,
                    @DATE datetime = null output,
                    @PAYMENTMETHOD nvarchar(13) = 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 int = null output, 
                    @SYMBOL nvarchar(4) = 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(50) = 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,
                    @FISCALYEARMONTH dbo.UDT_FUZZYDATE = null output,
                    @PLEDGESTATUS nvarchar(100) = null output,
                    @TRANSACTIONTYPE nvarchar(100) = null output,
                    @CATEGORYDESCRIPTION nvarchar(100) = null output
                )
                as

                set nocount on;

                declare @REVENUEID uniqueidentifier;
                declare @MATCHEDREVENUEDETAILID uniqueidentifier;

                set @DATALOADED = 0;
                set @MGSTATUSCODE = 0;

                select 
                    @AMOUNT = sum(AMOUNT),
                    @RECEIPTAMOUNT = sum(RECEIPTAMOUNT)
                from dbo.REVENUE 
                where ID = @ID;

                --Replaces revenue is the PK FK in many tables

                declare @REVENUEPAYMENTMETHODID uniqueidentifier;

                --Get general fields that are valid for all views

                select top 1
                    @DATALOADED = 1,
                    @REVENUEID = REVENUE.ID,
                    @TYPE = REVENUE.TRANSACTIONTYPECODE,
                    @DATE = REVENUE.DATE, 
                    @POSTDATE = REVENUE.POSTDATE,
                    @BATCHNUMBER = REVENUE.BATCHNUMBER,
                    @REVENUEPAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID,
                    @PAYMENTMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                    @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    @CHECKDATE = [CHECK].CHECKDATE,
                    @CHECKNUMBER = [CHECK].CHECKNUMBER,
                    @CARDHOLDERNAME = [CREDIT].CARDHOLDERNAME,
                    @CREDITCARDNUMBER = '***************',    --we should not be showing this information from some general summary

                    @CREDITTYPE = [CREDITTYPE].DESCRIPTION,
                    @AUTHORIZATIONCODE = [CREDIT].AUTHORIZATIONCODE,
                    @EXPIRESON = [CREDIT].EXPIRESON,
                    @ISSUER = [STOCK].ISSUER,
                    @NUMBEROFUNITS = case when [STOCK].NUMBEROFUNITS <> 0 then [STOCK].NUMBEROFUNITS else null end, 
                    @SYMBOL = [STOCK].SYMBOL,
                    @MEDIANPRICE = [STOCK].MEDIANPRICE,
                    @SALEAMOUNT = case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 4 then coalesce((select sum(SALEAMOUNT) from dbo.STOCKSALE where STOCKDETAILID = STOCK.ID), 0)
                                       when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5 and PROPERTY.ISSOLD = 1 then [PROPERTY].SALEAMOUNT
                                       else 0 end,
                    @GIFTINKINDSUBTYPE = [GIK].DESCRIPTION,
                    @PROPERTYSUBTYPE = PROPERTYSUBTYPECODE.DESCRIPTION,
                    @APPEAL = '',
                    @RECEIPTNUMBER = case left(dbo.UFN_REVENUE_GETRECEIPTSTATUS(REVENUE.ID),9)
                        when 'Receipted' then (select top 1 RR.RECEIPTNUMBER from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RR.RECEIPTPROCESSDATE desc)
                        else null end,
                    @RECEIPTDATE = case left(dbo.UFN_REVENUE_GETRECEIPTSTATUS(REVENUE.ID),9)
                        when 'Receipted' then (select top 1 RR.RECEIPTDATE from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RR.RECEIPTPROCESSDATE desc)
                        else null end,
                    @RECEIPTSTATUS = dbo.UFN_REVENUE_GETRECEIPTSTATUS(REVENUE.ID),
                    @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
                    @ACKNOWLEDGEMENTSTATUS = dbo.UFN_REVENUETRANSACTION_GETACKNOWLEDGESTATUS(REVENUE.ID),
                    @RECEIPTTYPE = RECEIPTTYPE,
                    @OTHERPAYMENTMETHODCODE = OTHERTYPE.DESCRIPTION,
                    @CATEGORYDESCRIPTION = GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME
                from dbo.REVENUE 
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                left join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID 
                left join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
                left join dbo.CREDITTYPECODE as [CREDITTYPE] on [CREDITTYPE].ID = [CREDIT].CREDITTYPECODEID
                left join dbo.STOCKDETAIL as [STOCK] on [STOCK].ID = REVENUEPAYMENTMETHOD.ID
                left join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                left join dbo.GIFTINKINDSUBTYPECODE as [GIK] on [GIK].ID = GIFTINKINDPAYMENTMETHODDETAIL.GIFTINKINDSUBTYPECODEID
                left join dbo.PROPERTYDETAIL as [PROPERTY] on [PROPERTY].ID = REVENUEPAYMENTMETHOD.ID 
                left join dbo.PROPERTYSUBTYPECODE on PROPERTYSUBTYPECODE.ID = [PROPERTY].PROPERTYSUBTYPECODEID
                left join dbo.ADJUSTMENT on REVENUE.ID = ADJUSTMENT.REVENUEID
                --left join dbo.REVENUERECEIPT on REVENUE.ID = REVENUERECEIPT.REVENUEID

                left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                left join dbo.OTHERPAYMENTMETHODDETAIL as [OTHER] on [OTHER].ID = REVENUEPAYMENTMETHOD.ID 
                left join dbo.OTHERPAYMENTMETHODCODE as [OTHERTYPE] on [OTHERTYPE].ID = [OTHER].OTHERPAYMENTMETHODCODEID
                left join dbo.REVENUECATEGORY on REVENUE.ID = REVENUECATEGORY.ID
                left join dbo.GLREVENUECATEGORYMAPPING on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
                where REVENUE.ID = @ID

                --TAFIELDS

                select
                    @FISCALYEARMONTH = RT.FISCALYEARMONTH,
                    @PLEDGESTATUS = RT.PLEDGESTATUS,
                    @TRANSACTIONTYPE = RTCODE.DESCRIPTION
                from dbo.REVENUETAINTEGRATION RT
                left outer join dbo.REVENUETRANSACTIONTYPECODE RTCODE
                    on RT.TRANSACTIONTYPECODEID = RTCODE.ID
                where RT.ID = @REVENUEID

                select top 1 @POSTSTATUS = 
                    case 
                        when REVENUEPOSTED.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 1 then 'Posted (adjustment pending)'
                        when REVENUEPOSTED.ID is not null then 'Posted'
                        when REVENUE.DONOTPOST = 1 then 'Do not post'
                        else 'Not posted'
                    end
                from dbo.REVENUE 
                left join dbo.ADJUSTMENT on REVENUE.ID = ADJUSTMENT.REVENUEID
                left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                where REVENUE.ID = @ID
                order by ADJUSTMENT.DATEADDED desc;

                --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;
                    select 
                        @REFERENCENUMBER = REFERENCENUMBER,
                        @REFERENCEDATE = REFERENCEDATE,
                        @ACCOUNT = dbo.UFN_CONSTITUENTACCOUNT_GETDESCRIPTION(CONSTITUENTACCOUNTID)
                    from dbo.DIRECTDEBITPAYMENTMETHODDETAIL
                    where ID = @REVENUEPAYMENTMETHODID;
                    close symmetric key sym_BBInfinity;
                end

                if @PAYMENTMETHODCODE = 10 --Other

                        select 
                            @REFERENCENUMBER = REFERENCENUMBER,
                            @REFERENCEDATE = REFERENCEDATE
                        from dbo.OTHERPAYMENTMETHODDETAIL where ID = @REVENUEPAYMENTMETHODID;

                if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID) --Sold stock

                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

                if @PAYMENTMETHODCODE = 5 and exists (select ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and PROPERTYDETAIL.ISSOLD = 1) --Sold property

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

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

                if @TYPE = 1 -- Pledge

                    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,
                        @SENDPLEDGEREMINDER = REVENUESCHEDULE.SENDPLEDGEREMINDER,
                        @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE.APPEALID),
                        @SOURCECODE = REVENUE.SOURCECODE,
                        @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
                        @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(MAILINGID),
                        @CHANNEL = CHANNELCODE.DESCRIPTION,
                        @PLEDGESUBTYPE = PLEDGESUBTYPE.NAME
                    from dbo.REVENUE                     
                    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                    left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = REVENUESCHEDULE.PLEDGESUBTYPEID
                    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
                        inner join dbo.BATCHREVENUEAPPLICATION as [APP] on [APP].BATCHREVENUEID = BATCHREVENUE.ID 
                        inner join dbo.REVENUE on REVENUE.ID = [APP].REVENUEID
                        where BATCH.STATUSCODE not in (1, 2) and REVENUE.ID = @REVENUEID;

                if @TYPE = 3 -- MG Pledge

                begin
                    declare @ISACTIVE bit;

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

                    select @MATCHEDREVENUE
                         = '$' + Cast(REVENUE.AMOUNT 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.REVENUE on REVENUE.ID = [APP].REVENUEID
                        where BATCH.STATUSCODE not in (1, 2) and REVENUE.ID = @REVENUEID;

                    --MGSTATUSCODE: 0 active

                    --MGSTATUSCODE: 1 paid in full

                    --MGSTATUSCODE: 2 inactive

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

                if @TYPE = 2 -- Recurring Gift

                begin
                    select
                        @DATALOADED = 1,
                        @NEXTTRANSACTION = case when REVENUESCHEDULE.NEXTTRANSACTIONDATE > REVENUESCHEDULE.ENDDATE then null else REVENUESCHEDULE.NEXTTRANSACTIONDATE end,
                        @TOTALPAID = coalesce((select sum(AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = REVENUE.ID and TYPECODE = 0), 0),
                        @FREQUENCY = REVENUESCHEDULE.FREQUENCY,
                        @STARTDATE = REVENUESCHEDULE.STARTDATE,
                        @ENDDATE = REVENUESCHEDULE.ENDDATE,
                        @STATUS = REVENUESCHEDULE.STATUS,
                        @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
                        inner join dbo.BATCHREVENUEAPPLICATION as [APP] on [APP].BATCHREVENUEID = BATCHREVENUE.ID 
                        inner join dbo.REVENUE on REVENUE.ID = [APP].REVENUEID
                        where BATCH.STATUSCODE not in (1, 2) and REVENUE.ID = @REVENUEID;
                end

                return 0;