USP_DATAFORMTEMPLATE_VIEW_RESERVATION_SECURITYDEPOSIT

The load procedure used by the view dataform template "Reservation Security 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.
@SECURITYDEPOSITREQUIRED	bit	INOUT	SECURITYDEPOSIT required
@SECURITYDEPOSITDUEDATE	date	INOUT	Due date
@PASTDUE	bit	INOUT	Past due
@SECURITYDEPOSITPAIDDATE	date	INOUT	Received
@SECURITYDEPOSITPAID	bit	INOUT	SECURITYDEPOSITPAID
@SECURITYDEPOSITAMOUNT	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_SECURITYDEPOSIT
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SECURITYDEPOSITREQUIRED bit = null output,
    @SECURITYDEPOSITDUEDATE date = null output,
    @PASTDUE bit = null output,
    @SECURITYDEPOSITPAIDDATE date = null output,
    @SECURITYDEPOSITPAID bit = null output,
    @SECURITYDEPOSITAMOUNT 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;

    select
        @DATALOADED = 1,
        @SECURITYDEPOSITREQUIRED = RESERVATION.SECURITYDEPOSITREQUIRED,
        @SECURITYDEPOSITDUEDATE = RESERVATION.SECURITYDEPOSITDUEDATE,
        @SECURITYDEPOSITAMOUNT = RESERVATION.SECURITYDEPOSITAMOUNT,
        @AMOUNTPAID = TOTALS.SECURITYDEPOSITAMOUNTPAID
    from
        dbo.RESERVATION
    outer apply
        dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
    where
        RESERVATION.ID = @ID

    set @BALANCE = @SECURITYDEPOSITAMOUNT - @AMOUNTPAID
    if @BALANCE < 0
        set @BALANCE = 0

    if @SECURITYDEPOSITREQUIRED = 1 begin
        set @SECURITYDEPOSITPAID =
            case
                when @SECURITYDEPOSITREQUIRED = 1 and dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@ID) <= 0 and @SECURITYDEPOSITAMOUNT > 0 then 1
                else 0
            end;

        if @SECURITYDEPOSITPAID = 1 begin
            select @SECURITYDEPOSITPAIDDATE = max(PAYMENT.DATEADDED) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT PAYMENT where PAYMENT.RESERVATIONID = @ID
            set @PASTDUE = 0
        end
        else if @SECURITYDEPOSITDUEDATE < @CURRENTDATE begin
            set @PASTDUE = 1
            set @DAYSPASTDUE = datediff(day,@SECURITYDEPOSITDUEDATE, @CURRENTDATE)
        end

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

        select top 1
            @DEPOSITPERCENT = SECURITYDEPOSITPERCENT,
            @DEPOSITTYPE = SECURITYDEPOSITTYPECODE,
            @DEPOSITREQUIREDDEFAULT = SECURITYDEPOSITREQUIRED
        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 @RECALCULATEDEPOSIT = round(dbo.UFN_SALESORDER_TOTAL(@ID) * @DEPOSITPERCENT * .01, 2);

            if @SECURITYDEPOSITAMOUNT = @RECALCULATEDEPOSIT
                set @RECALCULATEDEPOSIT = 0;
        end
    end

    return 0;