UFN_RESERVATION_GETRESERVATIONSWITHBALANCE
Returns a table with reservations that have outstanding balances.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @LASTDATE | date | IN |
Definition
Copy
CREATE function dbo.UFN_RESERVATION_GETRESERVATIONSWITHBALANCE
(
@LASTDATE date = null
)
returns @RESERVATIONSWITHBALANCE table(ARRIVALDATE date,
DEPOSITDUEDATE date,
FINALDUEDATE date,
DEPOSITDAYSPASTDUE integer,
FINALDAYSPASTDUE integer,
DEPOSITBALANCE money,
FINALBALANCE money,
RESERVATIONID uniqueidentifier,
DEPOSITREQUIRED bit,
SECURITYDEPOSITDAYSPASTDUE integer,
SECURITYDEPOSITBALANCE money,
SECURITYDEPOSITDUEDATE date,
SECURITYDEPOSITREQUIRED bit)
as
begin
insert into @RESERVATIONSWITHBALANCE
select RESERVATION.ARRIVALDATE,
RESERVATION.DEPOSITDUEDATE,
RESERVATION.FINALDUEDATE,
datediff(d, RESERVATION.DEPOSITDUEDATE, @LASTDATE),
datediff(d, RESERVATION.FINALDUEDATE, @LASTDATE),
case when RESERVATION.DEPOSITREQUIRED = 1
then
dbo.UFN_RESERVATION_GETDEPOSITBALANCE(RESERVATION.ID, RESERVATION.DEPOSITAMOUNT)
else
0.0
end,
dbo.UFN_SALESORDER_GETBALANCE(RESERVATION.ID),
RESERVATION.ID,
RESERVATION.DEPOSITREQUIRED,
datediff(d, [RESERVATION].[SECURITYDEPOSITDUEDATE], @LASTDATE),
dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE([RESERVATION].[ID]),
[RESERVATION].[SECURITYDEPOSITDUEDATE],
[RESERVATION].[SECURITYDEPOSITREQUIRED]
from dbo.RESERVATION
where (
(RESERVATION.DEPOSITREQUIRED = 1 and RESERVATION.DEPOSITDUEDATE <= @LASTDATE)
or RESERVATION.FINALDUEDATE <= @LASTDATE
)
return;
end