USP_DATAFORMTEMPLATE_VIEW_CREDIT

The load procedure used by the view dataform template "Credit 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.
@REFUNDDATE	datetime	INOUT	Refund date
@USERID	nvarchar(100)	INOUT	Refunded by
@REASONCODE	nvarchar(500)	INOUT	Reason code
@COMMENT	nvarchar(500)	INOUT	Comment
@AMOUNT	money	INOUT	Refund amount
@REFUNDMETHODS	nvarchar(500)	INOUT	Refund methods
@RECONCILIATIONSTATUSTEXT	nvarchar(10)	INOUT	Reconciliation Status

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDIT (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @REFUNDDATE datetime = null output,
    @USERID nvarchar(100) = null output,
    @REASONCODE nvarchar(500) = null output,
    @COMMENT nvarchar(500) = null output,
    @AMOUNT money = null output,
    @REFUNDMETHODS nvarchar(500) = null output,
    @RECONCILIATIONSTATUSTEXT nvarchar(10) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @AMOUNT = FT.TRANSACTIONAMOUNT,
        @REFUNDDATE = cast(FT.DATE as datetime),
        @USERID = dbo.UFN_APPUSER_GETNAME(FT.APPUSERID),
        @REASONCODE = [CREDITREASONCODE].[DESCRIPTION],
        @COMMENT = FT.DESCRIPTION,
        @REFUNDMETHODS = (
            select
                dbo.UDA_BUILDLIST(DESCRIPTIONS.DESCRIPTION)
            from (    
                select
                    case
                        when PAYMENTMETHODCODE = 10 then  -- Other

                            dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODCODEID]) + ' - $' + convert(nvarchar(20),[CREDITPAYMENT].[AMOUNT])
                        when PAYMENTMETHODCODE in (0, 1) then  -- Cash, Check

                            [CREDITPAYMENT].[PAYMENTMETHOD] + ' - $' + convert(nvarchar(20),[CREDITPAYMENT].[AMOUNT])
                    end as DESCRIPTION
                from dbo.[CREDITPAYMENT] where [CREDITID] = @ID and [PAYMENTMETHODCODE] in (0, 1, 10)  -- Cash, Check, Other


                union all
                select
                    dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER] + ' - $' + convert(nvarchar(20),[CREDITPAYMENT].[AMOUNT]) as [DESCRIPTION]
                from dbo.[CREDITPAYMENT] 
                    left join dbo.FINANCIALTRANSACTIONLINEITEM as LI on [CREDITPAYMENT].[REVENUESPLITID] = LI.ID and LI.DELETEDON is null and LI.TYPECODE <> 1  -- Reversal

                    inner join dbo.[REVENUEPAYMENTMETHOD] on REVENUEPAYMENTMETHOD.REVENUEID in (CREDITPAYMENT.REVENUEID, LI.FINANCIALTRANSACTIONID)
                    inner join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
                where [CREDITID] = @ID and [CREDITPAYMENT].[PAYMENTMETHODCODE] = 2
            ) as [DESCRIPTIONS]
        )
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    left join
        dbo.[CREDITREASONCODE] on [CREDITREASONCODE].[ID] = EXT.CREDITREASONCODEID
    where
        FT.ID = @ID

    if exists (select * from dbo.CREDITPAYMENT where CREDITID = @ID) begin
        set @RECONCILIATIONSTATUSTEXT = dbo.UFN_RECONCILIATION_STATUSCODE_GETDESCRIPTION_FORREFUND(@ID);
    end

    return 0;