aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_room     public.t_money;
    v_room_net public.t_money;
BEGIN
    IF NEW.revenue_room_gross IS NULL OR NEW.revenue_room_net IS NULL THEN
        IF NEW.status = 'AMD' AND NEW.room_qty < 0 THEN
            /* An amendment row carries the delta, so mirror the latest positive row. */
            SELECT -ABS(src.revenue_room_gross), -ABS(src.revenue_room_net)
            INTO v_room, v_room_net
            FROM public.rsv_pickup src
            WHERE src.booking_id = NEW.booking_id
              AND src.booking_item = NEW.booking_item
              AND src.room_qty > 0
              AND (src.revenue_room_gross IS NOT NULL OR src.revenue_room_net IS NOT NULL)
            ORDER BY src.id DESC
            LIMIT 1;
        ELSE
            SELECT SUM(b.room)::public.t_money, SUM(b.room_net)::public.t_money
            INTO v_room, v_room_net
            FROM public.booking_items bi
                 CROSS JOIN LATERAL public.sp_booking_rates_breakdown(
                     i_booking_id        := bi.booking_id,
                     i_booking_item      := bi.item_id,
                     only_not_checked_in := false
                 ) b
            WHERE bi.item_id = NEW.booking_item;
        END IF;

/* Left NULL when no booking_rates rows survive (cancelled/no-show history);
           readers fall back with COALESCE(revenue_room_gross, room_charge). */
        NEW.revenue_room_gross := COALESCE(NEW.revenue_room_gross, v_room);
        NEW.revenue_room_net   := COALESCE(NEW.revenue_room_net, v_room_net);
    END IF;

RETURN NEW;
END

sp_tr_rsv_pickup_room_revenue

Parameters

Definition