USP_DATAFORMTEMPLATE_VIEW_RESERVATION_DEPOSIT

The load procedure used by the view dataform template "Reservation Deposit 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.
@DEPOSITREQUIRED	bit	INOUT	Deposit required
@DEPOSITDUEDATE	date	INOUT	Due date
@PASTDUE	bit	INOUT	Past due
@DEPOSITPAIDDATE	date	INOUT	Received
@DEPOSITPAID	bit	INOUT	DEPOSITPAID
@DEPOSITAMOUNT	money	INOUT	Amount
@BALANCE	money	INOUT	Balance
@DAYSPASTDUE	int	INOUT	Days past due
@RECALCULATEDEPOSIT	money	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESERVATION_DEPOSIT
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @DEPOSITREQUIRED bit = null output,
    @DEPOSITDUEDATE date = null output,
    @PASTDUE bit = null output,
    @DEPOSITPAIDDATE date = null output,
    @DEPOSITPAID bit = null output,
    @DEPOSITAMOUNT money = null output,
    @BALANCE money = null output,
    @DAYSPASTDUE integer = null output,
    @RECALCULATEDEPOSIT money = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

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

    declare @AMOUNTPAID money;
    declare @AMOUNTREFUNDED money;

    select
        @DATALOADED = 1,
        @DEPOSITREQUIRED = RESERVATION.DEPOSITREQUIRED,
        @DEPOSITDUEDATE = RESERVATION.DEPOSITDUEDATE,
        @DEPOSITAMOUNT = RESERVATION.DEPOSITAMOUNT,
        @AMOUNTPAID = TOTALS.AMOUNTPAID,
        @AMOUNTREFUNDED = TOTALS.REFUNDS
    from
        dbo.RESERVATION
    outer apply
        dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
    where
        RESERVATION.ID = @ID;


    set @BALANCE = @DEPOSITAMOUNT - @AMOUNTPAID + @AMOUNTREFUNDED;

    if @BALANCE < 0 begin
        set @BALANCE = 0;
    end

    if @DEPOSITREQUIRED = 1 begin
        set @DEPOSITPAID = dbo.UFN_RESERVATION_ISDEPOSITPAID(@ID);

        if @DEPOSITPAID = 1 begin
            select @DEPOSITPAIDDATE = dbo.UFN_RESERVATION_GETDEPOSITPAIDDATE(@ID);
            set @PASTDUE = 0;
        end
        else if @DEPOSITDUEDATE < @CURRENTDATE begin
            set @PASTDUE = 1;
            set @DAYSPASTDUE = datediff(day,@DEPOSITDUEDATE, @CURRENTDATE);
        end
    end

    declare @DEPOSITTYPE int;
    declare @DEPOSITPERCENT decimal(5,2);
    declare @DEPOSITREQUIREDDEFAULT bit;
    declare @TEMPRECALCULATEDEPOSIT money;

    select top 1
        @DEPOSITPERCENT = DEPOSITPERCENT,
        @DEPOSITTYPE = DEPOSITTYPECODE,
        @DEPOSITREQUIREDDEFAULT = DEPOSITREQUIRED
    from
        dbo.GROUPSALESDEFAULT;

    set @RECALCULATEDEPOSIT = 0;

    if @DEPOSITTYPE = 1 and @DEPOSITREQUIREDDEFAULT = 1 and exists (select 1 from dbo.SALESORDER where SALESORDER.STATUSCODE not in (1,5) and SALESORDER.ID = @ID) begin
        set @TEMPRECALCULATEDEPOSIT = round(dbo.UFN_SALESORDER_TOTAL(@ID) * @DEPOSITPERCENT * .01, 2);

        if @DEPOSITAMOUNT <> @TEMPRECALCULATEDEPOSIT begin
            set @RECALCULATEDEPOSIT = @TEMPRECALCULATEDEPOSIT;
        end
    end

    return 0;