USP_DATAFORMTEMPLATE_VIEW_CREDITPAGEDATA

The load procedure used by the view dataform template "Credit Refund Page Expression 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.
@TRANSACTIONDATE	date	INOUT	Credit date
@CREDITTOTAL	money	INOUT	Credit total
@CONSTITUENTNAME	nvarchar(100)	INOUT	Constituent name
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@CREDITHASTICKET	bit	INOUT	Has tickets
@CREDITTICKETTOTAL	money	INOUT	Ticket total
@CREDITHASEVENTREGISTRATION	bit	INOUT	Has event registrations
@CREDITEVENTREGISTRATIONTOTAL	money	INOUT	Event registration total
@CREDITHASMEMBERSHIP	bit	INOUT	Has memberships
@CREDITMEMBERSHIPTOTAL	money	INOUT	Membership total
@CREDITHASADJUSTMENT	bit	INOUT	Has adjustments
@CREDITADJUSTMENTTOTAL	money	INOUT	Adjustment total
@HASINCOMPLETEREFUND	bit	INOUT	Has a credit payment that has not been processed
@SALESORDERID	uniqueidentifier	INOUT	Credit sales order ID
@CREDITHASSECURITYDEPOSIT	bit	INOUT	Has security deposit
@CREDITHASMERCHANDISE	bit	INOUT	Has merchandise
@CREDITMERCHANDISETOTAL	money	INOUT	Merchandise total
@USERGRANTEDCREDITCARDRETRYOPERATION	bit	INOUT	USERGRANTEDCREDITCARDRETRYOPERATION
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CREDITHASDONATION	bit	INOUT	Has donation
@CREDITDONATIONTOTAL	money	INOUT	Donation total
@CREDITHASMEMBERSHIPADDON	bit	INOUT
@POSTSTATUSCODE	tinyint	INOUT
@CREDITHASUNEARNEDREVENUE	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITPAGEDATA
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TRANSACTIONDATE date = null output,
    @CREDITTOTAL money = null output,
    @CONSTITUENTNAME nvarchar(100) = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CREDITHASTICKET bit = null output,
    @CREDITTICKETTOTAL money = null output,
    @CREDITHASEVENTREGISTRATION bit = null output,
    @CREDITEVENTREGISTRATIONTOTAL money = null output,
    @CREDITHASMEMBERSHIP bit = null output,
    @CREDITMEMBERSHIPTOTAL money = null output,
    @CREDITHASADJUSTMENT bit = null output,
    @CREDITADJUSTMENTTOTAL money = null output,
    @HASINCOMPLETEREFUND bit = null output,
    @SALESORDERID uniqueidentifier = null output,
    @CREDITHASSECURITYDEPOSIT bit = null output,
    @CREDITHASMERCHANDISE bit = null output,
    @CREDITMERCHANDISETOTAL money = null output,
    @USERGRANTEDCREDITCARDRETRYOPERATION bit = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CREDITHASDONATION bit = null output,
    @CREDITDONATIONTOTAL money = null output,
    @CREDITHASMEMBERSHIPADDON bit = null output,
    @POSTSTATUSCODE tinyint = null output,
    @CREDITHASUNEARNEDREVENUE bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    -- Refund record data and totals by item type
    select 
        @DATALOADED = 1,
        @TRANSACTIONDATE = FT.DATE,
        @CREDITTOTAL = FT.TRANSACTIONAMOUNT,
        @POSTSTATUSCODE = FT.POSTSTATUSCODE,
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(FT.CONSTITUENTID),
        @CONSTITUENTID = FT.CONSTITUENTID,
        @SALESORDERID = CREDIT_EXT.SALESORDERID,
        @CREDITTICKETTOTAL = dbo.UFN_CREDIT_REFUND_GETTICKETTOTAL2(FT.ID),
        @CREDITEVENTREGISTRATIONTOTAL = dbo.UFN_CREDIT_REFUND_GETEVENTREGISTRATIONTOTAL(FT.ID),
        @CREDITADJUSTMENTTOTAL = dbo.UFN_CREDIT_REFUND_GETADJUSTMENTTOTAL2(FT.ID),
        @CREDITMERCHANDISETOTAL = dbo.UFN_CREDIT_REFUND_GETMERCHANDISETOTAL2(FT.ID),
        @CREDITDONATIONTOTAL = dbo.UFN_CREDIT_REFUND_GETDONATIONTOTAL(FT.ID),
        @CREDITMEMBERSHIPTOTAL = dbo.UFN_CREDIT_REFUND_GETMEMBERSHIPANDADDONTOTAL(FT.ID)
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
    where FT.ID = @ID;

    -- Existences by item type (don't check TOTAL>$0 because there may be an item refunded for $0)
    -- sorry about the pivot table; it's just rotating a list of counts by typecode into a single row.
    select
        @CREDITHASTICKET = case when [0] > 0 then 1 else 0 end,
        @CREDITHASMEMBERSHIP = case when [1] > 0 then 1 else 0 end,
        @CREDITHASDONATION = case when [2] > 0 then 1 else 0 end,
        @CREDITHASADJUSTMENT = case when [3]+[4] > 0 then 1 else 0 end,  -- fees and taxes
        @CREDITHASEVENTREGISTRATION = case when [6] > 0 then 1 else 0 end,
        @CREDITHASSECURITYDEPOSIT = case when [12] > 0 then 1 else 0 end,
        @CREDITHASMERCHANDISE = case when [14] > 0 then 1 else 0 end,
        @CREDITHASMEMBERSHIPADDON = case when [16] > 0 then 1 else 0 end,
        @CREDITHASUNEARNEDREVENUE = case when [255] > 0 then 1 else 0 end
    from
    (
        select EXT.ID, EXT.TYPECODE
        from dbo.CREDITITEM_EXT EXT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on EXT.ID = LI.ID
        where LI.FINANCIALTRANSACTIONID = @ID
            and LI.DELETEDON is null
    ) REFUNDEDITEMS
    pivot (
        count(REFUNDEDITEMS.ID)
        for TYPECODE in ([0], [1], [2], [3], [4], [6], [14], [12], [16], [255])
    ) REFUNDEDITEMSBYTYPE;

    if @DATALOADED = 1 begin
        set @USERGRANTEDCREDITCARDRETRYOPERATION = 0;

        if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
            or exists (
                select * 
                from dbo.SYSTEMROLEPERM_RECORDOPERATION
                inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLEPERM_RECORDOPERATION.SYSTEMROLEID
                where SYSTEMROLEPERM_RECORDOPERATION.RECORDOPERATIONCATALOGID = '7f1092b1-f365-4a65-8359-4c3959d83451'
                    and SYSTEMROLEAPPUSER.APPUSERID = @CURRENTAPPUSERID
            )
            set @USERGRANTEDCREDITCARDRETRYOPERATION = 1;
    end

    return 0;