USP_DATAFORMTEMPLATE_VIEW_RESERVATION_CONTRACT

The load procedure used by the view dataform template "Reservation Contract 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.
@CONTRACTREQUIRED	bit	INOUT	Contract required
@CONTRACTDUEDATE	date	INOUT	Due date
@CONTRACTRECEIVED	bit	INOUT	Contract received
@CONTRACTSENT	bit	INOUT	Contract sent
@CONTRACTSENTDATE	date	INOUT	Sent
@CONTRACTRECEIVEDDATE	date	INOUT	Received
@CONTRACTPASTDUE	bit	INOUT	CONTRACTPASTDUE
@DAYSPASTDUE	int	INOUT	Days past due
@FILE	varbinary	INOUT	File
@FILENAME	nvarchar(255)	INOUT	Contract
@RESERVATIONID	uniqueidentifier	INOUT	RESERVATIONID

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESERVATION_CONTRACT
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CONTRACTREQUIRED bit = null output,
                    @CONTRACTDUEDATE date = null output,
                    @CONTRACTRECEIVED bit = null output,
                    @CONTRACTSENT bit = null output,
                    @CONTRACTSENTDATE date = null output,
                    @CONTRACTRECEIVEDDATE date = null output,
                    @CONTRACTPASTDUE bit = null output,
                    @DAYSPASTDUE integer = null output,
                    @FILE varbinary(max) = null output,
                    @FILENAME nvarchar(255) = null output,
                    @RESERVATIONID uniqueidentifier = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

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

                    set @CONTRACTPASTDUE = 0;

                    select top 1 @CONTRACTSENTDATE = STATUSDATE
                    from dbo.RESERVATIONSTATUSHISTORY 
                    where RESERVATIONID = @ID and
                        STATUSCODE = 6
                    order by STATUSDATE desc


                    select top 1 @CONTRACTRECEIVEDDATE = STATUSDATE
                    from dbo.RESERVATIONSTATUSHISTORY 
                    where RESERVATIONID = @ID and
                        STATUSCODE = 7
                    order by STATUSDATE desc

                    select @DATALOADED = 1,
                           @CONTRACTREQUIRED = CONTRACTREQUIRED,
                           @CONTRACTDUEDATE = CONTRACTDUEDATE,
                           @CONTRACTRECEIVED = CONTRACTRECEIVED,
                           @CONTRACTSENT = CONTRACTSENT,
                           @FILENAME = RESERVATIONATTACHMENT.FILENAME
                    from dbo.RESERVATION
                    left outer join
                        dbo.RESERVATIONATTACHMENT on RESERVATION.ID = RESERVATIONATTACHMENT.RESERVATIONID and RESERVATIONATTACHMENT.ISCONTRACT = 1
                    where RESERVATION.ID = @ID;

                    if @CONTRACTREQUIRED = 1 and @CONTRACTDUEDATE < @CURRENTDATE and @CONTRACTRECEIVED = 0
                    begin
                        set @CONTRACTPASTDUE = 1;
                        set @DAYSPASTDUE = datediff(day, @CONTRACTDUEDATE, @CURRENTDATE)
                    end

                    set @RESERVATIONID = @ID;
                    return 0;