rsv_pickup

Pickup event type: NEW = new booking, RBK = rebook, RBN = rebook-in, CXL = cancelled, NOS = no-show, AMD = amendment. AMD rows carry signed deltas (room_qty and every revenue column can be negative), so any SUM over this table must include them rather than filtering to NEW.

Arrival-night rate only – sp_calc_rsv_pickup joins booking_rates on br.charge_date = bi.arrival. It therefore misrepresents variable nightly rates, which is why rate_revenue was added (V20260916.10) as the REVENUE_CALC_TYPE = 0 basis. Kept as the display rate and as the legacy fallback when rate_revenue IS NULL.

REVENUE_CALC_TYPE = 1 basis for pickup/pace, copied from booking_items.room_charge. PER SINGLE ROOM: booking_items.room_charge is the total for one room, so readers must multiply by abs(COALESCE(room_qty, 1)) (V20260918.10.1). CAREFUL: despite the name this is NOT the room-only figure that recapitulate.individual_revenue, room_summary.room_charge and guest_history.room_revenue hold – it is the SUM of nightly rates and, for comp/zero-rate items, it also adds the meal amount, so it tracks the type-0 basis rather than type 1. Kept as the frozen figure shown on pickup detail screens and as the last-resort fallback. The real bases are rate_revenue (0), revenue_room_gross (1) and revenue_room_net (2).

REVENUE_CALC_TYPE = 2 basis for this pickup event. Room only and NET of every itemizer on the room-charge department, from SUM(sp_booking_rates_breakdown.room_net) over the booking item’s nights. It is revenue_room_gross with every itemizer stripped, which is not the same as subtracting room_service and room_tax when the department carries more. This is NOT room_charge minus tax: room_charge mirrors booking_items.room_charge, which is the sum of nightly rates and still includes meals, so it is not room-only at all. Negative on status = ‘AMD’ rows, mirroring rate_revenue. NULL = no booking_rates rows left to derive it from (cancelled/no-show history); readers use COALESCE(revenue_room_net, revenue_room_gross, room_charge). PER SINGLE ROOM: readers multiply by abs(COALESCE(room_qty, 1)) (V20260918.10.1).

Frozen SUM(booking_rates.room_rate) for the booking item at pickup time. REVENUE_CALC_TYPE = 0 basis (Gross Rate: room, meals and inclusions); unlike legacy room_rate, this supports variable nightly rates. Negative on status = ‘AMD’ rows. See also revenue_room_gross (type 1) and revenue_room_net (type 2). PER SINGLE ROOM: readers multiply by abs(COALESCE(room_qty, 1)) (V20260918.10.1).

REVENUE_CALC_TYPE = 1 basis for this pickup event: Gross Room – room only, with every itemizer on the room-charge department still included, from SUM(sp_booking_rates_breakdown.room) over the booking item’s nights. It is NOT revenue_room_net + room_service + room_tax: those two columns name only the service and tax slices, and a department may carry more (sample’s room department is {SER,VAT,LOC}). revenue_room_net is the authoritative net figure. Negative on status = ‘AMD’ rows, mirroring rate_revenue. NULL = no booking_rates rows left to derive it from (cancelled/no-show history); readers use COALESCE(revenue_room_gross, room_charge). PER SINGLE ROOM: readers multiply by abs(COALESCE(room_qty, 1)) (V20260918.10.1).