aonestar.public

Parameters

Name	Type	Mode
requirements	guest_request[]	IN
accom_format	integer	IN (DEFAULT 0)
description	text	OUT
multiplier	smallint	OUT
frequency	smallint	OUT
qty	integer	OUT
night	integer	OUT
rate	t_money	OUT
amount	t_money	OUT

Definition

WITH requests AS (SELECT * FROM unnest(requirements))
    SELECT fn_accommodation(name, A.qty, A.charge_rate, iif(A.frequency IN (3,4), NULL, A.night), $2::smallint) AS description,
           A.multiplier, A.frequency, 
           A.qty, iif(A.frequency IN (3,4), NULL, A.night) AS night, A.charge_rate, (A.charge_rate * A.qty * A.night)::t_money AS amount 
    FROM (
        SELECT rq.inclusion_id, rq.multiplier, rq.frequency, rq.charge_rate, rq.qty,                   
            fn_get_request_nights(rq.frequency, bk.arrival, bk.departure, rq.start_date, rq.end_date) AS night 
        FROM requests rq LEFT JOIN booking bk ON bk.id = rq.booking_id
        WHERE rq.booking_id IS NOT NULL AND rq.multiplier = 1
        UNION ALL 
        SELECT rq.inclusion_id, rq.multiplier, rq.frequency, rq.charge_rate, 
            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 requests rq 
            LEFT JOIN booking_items bi on (bi.booking_id = rq.booking_id AND (bi.item_id = rq.booking_item OR rq.booking_item IS NULL))
        WHERE rq.booking_id IS NOT NULL AND rq.multiplier <> 1
        GROUP BY rq.qty, rq.multiplier, rq.frequency, rq.inclusion_id, rq.charge_rate, night
    ) A LEFT JOIN inclusion inc ON inc.id = a.inclusion_id

sp_calc_requirement_charges

Parameters

Definition