USP_DATAFORMTEMPLATE_VIEW_GroupSalesCheckOutDetail

The load procedure used by the view dataform template "Group Sales Check Out Detail 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.
@CREDITID	uniqueidentifier	INOUT	Credit ID
@NAME	nvarchar(100)	INOUT	NAME
@AMOUNTCHARGED	money	INOUT	Amount charged
@AMOUNTPAID	money	INOUT	Amount paid
@STATUSCODE	int	INOUT	Status code
@REFUNDHISTORY	xml	INOUT	Refund history
@WITHHOLDAMOUNT	money	INOUT	Withhold amount
@WITHHOLDREASON	nvarchar(100)	INOUT	Withhold reason

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GroupSalesCheckOutDetail
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CREDITID uniqueidentifier = null output,
    @NAME nvarchar(100) = null output,
    @AMOUNTCHARGED money = null output,
    @AMOUNTPAID money = null output,
    @STATUSCODE int = null output,
    @REFUNDHISTORY xml = null output,
    @WITHHOLDAMOUNT money = null output,
    @WITHHOLDREASON nvarchar(100) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select @DATALOADED = 1,
           @NAME = RESERVATION.NAME,
           @AMOUNTCHARGED = SECURITYDEPOSITAMOUNT,
           @AMOUNTPAID = (select sum(AMOUNT) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where RESERVATIONID = RESERVATION.ID),
           @STATUSCODE = RESERVATION.SECURITYDEPOSITSTATUSCODE,
           @WITHHOLDAMOUNT = case SECURITYDEPOSITSTATUSCODE 
                                when 0 then 0
                                when 1 then 0
                                when 2 then 0
                                when 3 then (select sum(AMOUNT) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where RESERVATIONID = RESERVATION.ID)
                                when 4 then (select sum(AMOUNT) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where RESERVATIONID = RESERVATION.ID) -
                                            (select sum(CREDITPAYMENT.AMOUNT) from CREDITPAYMENT
                                                inner join dbo.REVENUESPLIT 
                                                on CREDITPAYMENT.REVENUESPLITID = REVENUESPLIT.ID 
                                                inner join dbo.CREDIT 
                                                on CREDITPAYMENT.CREDITID = CREDIT.ID
                                                where REVENUESPLIT.TYPECODE = 13
                                                and CREDIT.SALESORDERID = @ID
                                            )
                                end,
           @WITHHOLDREASON = case SECURITYDEPOSITSTATUSCODE 
                                when 0 then null
                                when 1 then null
                                when 2 then null
                                when 3 then RESERVATIONSECURITYDEPOSITWITHHOLDREASONCODE.DESCRIPTION
                                when 4 then RESERVATIONSECURITYDEPOSITWITHHOLDREASONCODE.DESCRIPTION
                                end
    from dbo.RESERVATION
    left join dbo.RESERVATIONSECURITYDEPOSITWITHHOLDREASONCODE on RESERVATION.SECURITYDEPOSITWITHHOLDREASONCODEID = RESERVATIONSECURITYDEPOSITWITHHOLDREASONCODE.ID
    where RESERVATION.ID = @ID

    set @REFUNDHISTORY = (select CREDIT.TRANSACTIONDATE as REFUNDDATE,
                                    CREDITPAYMENT.AMOUNT as REFUNDAMOUNT,
                                    CREDITPAYMENT.PAYMENTMETHOD as REFUNDMETHOD
                            from dbo.CREDITPAYMENT 
                            inner join dbo.REVENUESPLIT on CREDITPAYMENT.REVENUESPLITID = REVENUESPLIT.ID
                            inner join dbo.CREDIT on CREDITPAYMENT.CREDITID = CREDIT.ID 
                            inner join dbo.RESERVATION on CREDIT.SALESORDERID = RESERVATION.ID 
                            where REVENUESPLIT.TYPECODE = 13 and RESERVATION.ID = @ID 
                            order by CREDIT.TRANSACTIONDATE 
                            for xml raw('ITEM'), type, elements, root('REFUNDHISTORY'), binary base64
                        ) 

    select @CREDITID = CREDIT.ID
    from dbo.RESERVATION
    inner join dbo.RESERVATIONSECURITYDEPOSITPAYMENT on RESERVATIONSECURITYDEPOSITPAYMENT.RESERVATIONID = RESERVATION.ID
    left join dbo.CREDIT ON RESERVATION.ID = CREDIT.SALESORDERID
    where RESERVATION.ID = @ID;

    return 0;