USP_DATAFORMTEMPLATE_VIEW_RESERVATION_FINALPAYMENT

The load procedure used by the view dataform template "Reservation Final Payment Due Date 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.
@FINALPAYMENTDUEDATE	date	INOUT	Due date
@FINALPAYMENTPASTDUE	bit	INOUT	FINALPAYMENTPASTDUE
@FINALPAYMENTRECEIVED	bit	INOUT	FINALPAYMENTRECEIVED
@FINALPAYMENTDATE	date	INOUT	Received
@RESERVATIONTOTAL	money	INOUT	Total
@AMOUNTDUE	money	INOUT	Balance
@DAYSPASTDUE	int	INOUT	Days past due
@ORDERBALANCESENT	bit	INOUT	ORDERBALANCESENT
@ORDERBALANCESENTDATE	date	INOUT	Sent

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESERVATION_FINALPAYMENT
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @FINALPAYMENTDUEDATE date = null output,
    @FINALPAYMENTPASTDUE bit = null output,
    @FINALPAYMENTRECEIVED bit = null output,
    @FINALPAYMENTDATE date = null output,
    @RESERVATIONTOTAL money = null output,
    @AMOUNTDUE money = null output,
    @DAYSPASTDUE integer = null output,
    @ORDERBALANCESENT bit = null output,
    @ORDERBALANCESENTDATE date = null output
)
as
    set nocount on;

    declare @DEPOSIT money;
    declare @CURRENTDATE date;
    set @CURRENTDATE = getdate();

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @FINALPAYMENTDUEDATE = RESERVATION.FINALDUEDATE,
        @DEPOSIT = case RESERVATION.DEPOSITREQUIRED when 1 then RESERVATION.DEPOSITAMOUNT else 0 end,
        @RESERVATIONTOTAL = TOTALS.TOTAL,
        @AMOUNTDUE = TOTALS.BALANCE,
        @ORDERBALANCESENT = [RESERVATION].[ORDERBALANCESENT]
    from
        dbo.[RESERVATION]
    outer apply
        dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
    where
        RESERVATION.ID = @ID

    select top 1 @ORDERBALANCESENTDATE = [STATUSDATE]
    from dbo.[RESERVATIONSTATUSHISTORY] 
    where 
        [RESERVATIONID] = @ID and
        [STATUSCODE] = 12
    order by [STATUSDATE] desc

    set @FINALPAYMENTPASTDUE = dbo.UFN_RESERVATION_FINALPAYMENTPASTDUE(@ID, @CURRENTDATE)

    set @DAYSPASTDUE = datediff(day, @FINALPAYMENTDUEDATE, @CURRENTDATE)

    select top 1 @FINALPAYMENTDATE = [REVENUE].[DATE]
    from dbo.[REVENUE]
    inner join dbo.[SALESORDERPAYMENT]
        on [REVENUE].[ID] = [SALESORDERPAYMENT].[PAYMENTID]
    where [SALESORDERPAYMENT].[SALESORDERID] = @ID
    order by [REVENUE].[DATE] desc

    set @FINALPAYMENTRECEIVED = 0
    if @AMOUNTDUE <= 0 and @FINALPAYMENTDATE is not null
        set @FINALPAYMENTRECEIVED = 1

    return 0;