/* Shows unpaid bookings with deposit required */
SELECT S.salesperson_name AS [Salesperson],
o.loginname AS [Original Operator],
B.booking_no AS [Booking #],
B.organizationv6 AS [Company],
CASE
WHEN B.booking_type_v32 = 0 THEN 'Rental'
WHEN B.booking_type_v32 = 1 THEN 'Sub Hire'
WHEN B.booking_type_v32 = 2 THEN 'Quote'
WHEN B.booking_type_v32 = 3 THEN 'Rental Quote Monthly'
WHEN B.booking_type_v32 = 4 THEN 'Rental Monthly'
WHEN B.booking_type_v32 = 5 THEN 'Sub Rental'
WHEN B.booking_type_v32 = 6 THEN 'Sale'
WHEN B.booking_type_v32 = 7 THEN 'Sales Quote'
WHEN B.booking_type_v32 = 11 THEN 'Location Transfer'
WHEN B.booking_type_v32 = 13 THEN 'Sundry Transfer'
WHEN B.booking_type_v32 = 14 THEN 'Rental 7'
WHEN B.booking_type_v32 = 15 THEN 'Rental 14'
WHEN B.booking_type_v32 = 16 THEN 'Rental 28'
WHEN B.booking_type_v32 = 17 THEN 'Rental Quarterly'
WHEN B.booking_type_v32 = 18 THEN 'Rental Quote 7'
WHEN B.booking_type_v32 = 19 THEN 'rental_quote_14'
WHEN B.booking_type_v32 = 20 THEN 'Rental Quote 28'
WHEN B.booking_type_v32 = 21 THEN 'Rental Quote Quarterly'
ELSE ''
END AS Type,
CASE
WHEN B.status = 0 THEN 'Booked'
WHEN B.status = 1 THEN 'Checked Out'
WHEN B.status = 2 THEN 'Returned'
ELSE ''
END AS State,
B.showname AS [Showname],
B.invoiced AS [Invoiced],
B.invoice_no AS [Invoice #],
I.invoice_amount,
Cast(b.price_quoted AS DECIMAL(9, 2)) AS [Price Quoted],
Cast(b.deposit_quoted_v50 AS DECIMAL(9, 2)) AS [Deposit Required],
CASE
WHEN i.Inv_cred_note = 0 THEN CAST(
(SELECT SUM(P.amount)
FROM tblPayment P
WHERE P.invoice_no = i.invoice_cred_no) AS DECIMAL(9,2))
ELSE 0
END AS [Payment Amount],
B.contact_namev6 AS [Contact Name],
B.ddate AS [Out Date],
B.rdate AS [Return Date],
V.venuename AS [Venue],
CASE
WHEN B.bookingprogressstatus = 0 THEN 'Quote'
WHEN B.bookingprogressstatus = 1 THEN 'Light Pencil'
WHEN B.bookingprogressstatus = 2 THEN 'Heavy Pencil'
WHEN B.bookingprogressstatus = 3 THEN 'Confirmed'
ELSE 'Cancelled'
END AS [Progress Status]
FROM tblbookings B
LEFT OUTER JOIN tblvenues V
ON B.venueid = V.id
LEFT OUTER JOIN tblsalesper S
ON B.salesperson = S.salesperson_code
LEFT OUTER JOIN tbloperators O
ON o.id = b.operatorsid
LEFT OUTER JOIN tblinvhead I on I.Invoice_cred_no = b.invoice_no
WHERE ISNULL(B.deposit_quoted_v50, 0) > 0
AND (
I.invoice_cred_no IS NULL
OR
ISNULL(I.invoice_amount, 0) > ISNULL((
SELECT SUM(P.amount)
FROM tblPayment P
WHERE P.invoice_no = I.invoice_cred_no
), 0)
);