aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN
allow_zero	boolean	IN (DEFAULT false)
accom_format	integer	IN (DEFAULT 0)
seq	integer	OUT
charge_type	text	OUT
description	text	OUT
amount	t_money	OUT

Definition

WITH BI AS (
        SELECT bi.booking_id, bi.booking_seq, bi.item_id, bi.roomtype_id, bi.qty, bi.block_qty, bi.inhouse_qty, bi.arrival, bi.departure,
            bi.adult + bi.extra_adult AS adult, bi.child + bi.extra_child AS child, bi.infant + bi.extra_infant AS infant
        FROM booking_items bi 
        WHERE booking_id = $1
    ), BR AS (
        SELECT bi.roomtype_id, rt.code::text AS roomtype, bi.qty, br.room_rate, sum(br.end_date -br.start_date +1)::int night
        FROM BI 
            LEFT JOIN booking_rates br ON br.booking_item = bi.item_id 
            LEFT JOIN room_type rt ON rt.id = bi.roomtype_id 
        GROUP BY bi.roomtype_id, rt.code, bi.qty, br.room_rate
    ), ACCOM AS (
        SELECT fn_accommodation(roomtype, qty, room_rate, night, accom_format::smallint) AS description, amount
        FROM (SELECT roomtype, room_rate, night, 
                     sum(qty)::int AS qty , 
                     sum(qty) * room_rate * night AS amount
              FROM BR
              GROUP BY roomtype, room_rate, night
              ORDER BY roomtype, night DESC
              ) AS R 
    ), REQ AS (
        SELECT fn_accommodation(name, qty, charge_rate, iif(frequency IN (3,4), NULL, night), accom_format::smallint) AS description,
              charge_rate * qty * night AS amount
        FROM (
            SELECT inc.name, rq.charge_rate, rq.frequency, 
                sum(fn_get_request_qty(bi.qty, bi.adult, bi.child, bi.infant, rq.qty, rq.multiplier))::int AS qty,                   
                fn_get_request_nights(rq.frequency, bi.arrival, bi.departure, rq.start_date, rq.end_date) AS night 
            FROM guest_request rq 
                LEFT JOIN inclusion inc ON inc.id = rq.inclusion_id 
                LEFT JOIN BI on (bi.item_id = rq.booking_item OR rq.booking_item IS NULL)
            WHERE rq.booking_id = $1 AND NOT rq.inclusive 
            GROUP BY rq.qty, rq.multiplier, rq.frequency, inc.name, rq.charge_rate, night
            ORDER BY inc.name 
        ) A 
    ), items AS (
        SELECT 'room' AS charge_type, description, amount FROM ACCOM 
        UNION ALL 
        SELECT 'other', description, amount FROM REQ WHRE 
        UNION ALL 
        SELECT 'deposit', 'Deposit', -COALESCE(sum(amount),0.0)::t_money 
        FROM deposit 
        WHERE booking_id = $1 AND status <> 'X' 
    )
    SELECT (ROW_NUMBER() OVER())::int AS seq, charge_type, description, amount::t_money 
    FROM items 
    WHERE not (charge_type = 'deposit' and amount = 0)
         and (allow_zero or amount <> 0)

sp_get_booking_charges

Parameters

Definition