USP_DATAFORMTEMPLATE_VIEW_REVENUEAPPLICATIONPROFILE

The load procedure used by the view dataform template "Revenue Application 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.
@AMOUNT	money	INOUT	Amount
@DATE	datetime	INOUT	Date
@PAYMENTMETHOD	nvarchar(15)	INOUT	Payment method
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method code
@POSTDATE	datetime	INOUT	Post date
@POSTSTATUS	nvarchar(50)	INOUT	Post status
@BATCHNUMBER	nvarchar(100)	INOUT	Batch number
@RECEIPTSTATUS	nvarchar(50)	INOUT	Receipt status
@ACKNOWLEDGEMENTSTATUS	nvarchar(50)	INOUT	Acknowledgements
@GIFTFEE_ENABLED	bit	INOUT	Gift fee enabled
@GIFTFEE_AMOUNT	money	INOUT	Gift fees
@GIFTFEE_WAIVED	bit	INOUT	Gift fee waived
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@BASEAMOUNT	money	INOUT	Base amount
@ACKNOWLEDGEMENTDATE	datetime	INOUT	Acknowledged date

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUEAPPLICATIONPROFILE
                (
                    @ID uniqueidentifier,    
                    @DATALOADED bit = 0 output,
                    @AMOUNT money = null output,
                    @DATE datetime = null output,
                    @PAYMENTMETHOD nvarchar(15) = null output,
                    @PAYMENTMETHODCODE tinyint = null output,
                    @POSTDATE datetime = null output,
                    @POSTSTATUS nvarchar(50) = null output,
                    @BATCHNUMBER nvarchar(100) = null output,
                    @RECEIPTSTATUS nvarchar(50) = null output,
                    @ACKNOWLEDGEMENTSTATUS nvarchar(50) = null output,
                    @GIFTFEE_ENABLED bit = null output,
                    @GIFTFEE_AMOUNT money = null output,
                    @GIFTFEE_WAIVED bit = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @BASEAMOUNT money = null output,
                    @ACKNOWLEDGEMENTDATE datetime = null output
                )
                as

                set nocount on;

                declare @REVENUEID uniqueidentifier;

                set @DATALOADED = 0;

                --Get general fields that are valid for all views
                select top 1
                    @DATALOADED = 1,
                    @REVENUEID = REVENUE.ID,
                    @AMOUNT = REVENUESPLIT.TRANSACTIONAMOUNT,
                    @DATE = REVENUE.DATE, 
                    @POSTDATE = REVENUE.POSTDATE,
                    @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,
                    @BATCHNUMBER = REVENUE.BATCHNUMBER,
                    @PAYMENTMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                    @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    @RECEIPTSTATUS = dbo.UFN_REVENUE_GETRECEIPTSTATUS(REVENUE.ID),
                    @ACKNOWLEDGEMENTSTATUS = dbo.UFN_REVENUETRANSACTION_GETACKNOWLEDGESTATUS(REVENUE.ID),
                    @GIFTFEE_ENABLED = dbo.UFN_GIFTFEE_ENABLED(),
                    @GIFTFEE_AMOUNT = coalesce(REVENUESPLITGIFTFEE.TRANSACTIONAMOUNT, 0),
                    @GIFTFEE_WAIVED = coalesce(REVENUESPLITGIFTFEE.WAIVED, 0),
                    @BASEAMOUNT = REVENUESPLIT.AMOUNT,
                    @BASECURRENCYID = REVENUESPLIT.BASECURRENCYID,
                    @TRANSACTIONCURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID,
                    @ACKNOWLEDGEMENTDATE = (select top 1 ACKNOWLEDGEDATE from dbo.REVENUELETTER where REVENUELETTER.REVENUEID = REVENUE.ID)
                from 
                    REVENUESPLIT
                    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                    left join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                    left join dbo.ADJUSTMENT on REVENUE.ID = ADJUSTMENT.REVENUEID
                    left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                    left join dbo.REVENUESPLITGIFTFEE on REVENUESPLITGIFTFEE.ID = REVENUESPLIT.ID
                where 
                    REVENUESPLIT.ID = @ID;            

                return 0;