aonestar.public

Parameters

Name	Type	Mode
post_date	date	IN (DEFAULT NULL)
charge_type	text	IN (DEFAULT NULL)
posted	boolean	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)
booking_id	integer	IN (DEFAULT NULL)
folio_id	integer	IN (DEFAULT NULL)
include_inhouse	boolean	IN (DEFAULT true)
include_booking	boolean	IN (DEFAULT false)
post_date	date	OUT
register_id	integer	OUT
room_id	integer	OUT
booking_id	integer	OUT
booking_item	integer	OUT
arrival	date	OUT
departure	date	OUT
charge_date	date	OUT
charge_type	text	OUT
inclusion_id	integer	OUT
charge_dept	integer	OUT
charge_qty	integer	OUT
charge_amount	t_money	OUT
total_amount	t_money	OUT
remark	text	OUT
charge_folio_id	integer	OUT
charge_to_booking	boolean	OUT
split_room_charge	boolean	OUT
posted	boolean	OUT

Definition

--    IF include_booking THEN 
--        RETURN query 
        WITH rsv AS (
            SELECT NULL::int AS register_id, bi.booking_id, bi.item_id AS booking_item, bk.folio_id, bk.folio_id AS booking_folio_id, NULL::int AS room_id,
                bi.arrival, bi.departure, bi.rateplan_id, 
                (bi.charge_to = 'B' and bk.book_type = 'G') AS charge_to_booking, 
                false as split_room_charge, bi.qty-bi.inhouse_qty AS qty,
                bi.adult, bi.child, bi.infant, bi.extra_adult, bi.extra_child, bi.extra_infant,
                iif(bi.charge_to = 'B', bk.folio_id, null::int) AS charge_folio_id
            FROM booking_items bi 
                LEFT JOIN booking bk ON bk.id = bi.booking_id 
            WHERE include_booking AND bk.status IN ('R','C','I') AND (bi.qty - bi.inhouse_qty) > 0 
                AND ($5 IS NULL OR bi.booking_id = $5)
        ), rsv_inclusion_charge AS (
            SELECT d.post_date, rsv.register_id, rsv.room_id, rsv.booking_id, rsv.booking_item, rsv.arrival, rsv.departure, inc.charge_dept, inc.frequency,
                 CASE frequency
                    WHEN 1 THEN d.post_date +1
                    WHEN 2 THEN d.post_date
                    WHEN 3 THEN rsv.arrival 
                    WHEN 4 THEN rsv.departure
                 END AS charge_date,
                 fn_calc_inclusion_qty(rsv.qty, adult, child, infant, 1, inc.charge_rate, inc.multiplier, inc.frequency, d.post_date, d.post_date+1) AS qty,
                 inc.charge_rate, 
                 iif(inc.name <> dp.name, inc.name, null) AS remark, 
                 rsv.charge_to_booking, rsv.charge_folio_id, rsv.split_room_charge, inc.inclusion_id
            FROM rsv, 
                LATERAL sp_get_rate_inclusions(rsv.rateplan_id) AS inc
                LEFT JOIN department dp ON dp.id = inc.charge_dept,
                LATERAL fn_date_range(rsv.arrival, rsv.departure) AS d(post_date)   
            WHERE (frequency = 1 AND d.post_date <> rsv.arrival)        -- Daily except arrival
                  OR (frequency = 2 AND d.post_date <> rsv.departure)   -- Daily except departure  
                  OR (frequency = 3 AND d.post_date = rsv.arrival)      -- Once on arrival
                  OR (frequency = 4 AND d.post_date = rsv.departure)    -- Once on departure
        ), rsv_room_charge AS (
            WITH ic AS (
                SELECT ic.booking_item, ic.post_date, sum(ic.qty * ic.charge_rate) AS inclusion_charge_amount 
                FROM rsv_inclusion_charge AS ic
                WHERE ($1 IS NULL OR ic.post_date = $1) AND ic.frequency IN (1,2) --> count daily charges as inclusive, charge once as exclusive
                GROUP BY 1,2
            )
            SELECT  rr.charge_date AS post_date, rr.charge_date, rsv.booking_id, rsv.booking_item, rsv.arrival, rsv.departure,
                COALESCE(rr.room_charge_dept, default_charge_dept) AS charge_dept,
                rsv.qty, 
                
                (rr.room_rate 
                    + (rsv.extra_adult * rr.extrabed_rate) 
                    + (rsv.extra_child * rr.child_rate) 
                    + (rsv.extra_infant * rr.infant_rate)
                    - COALESCE(ic.inclusion_charge_amount, 0)
                )::t_money AS room_charge_amount,
                
                NULL AS remark,
                
                rsv.charge_to_booking, rsv.charge_folio_id, rsv.split_room_charge, FALSE AS posted
            FROM rsv 
                LEFT JOIN booking_rates rr ON rsv.booking_item = rr.booking_item --AND ($1 IS NULL OR rr.charge_date = $1)
                LEFT JOIN ic ON ic.post_date = rr.charge_date and ic.booking_item = rr.booking_item,
                LATERAL fn_default('ROOM_CHARGE_DEPT', 0) AS default_charge_dept 
            WHERE ($1 IS NULL OR rr.charge_date = $1) AND ($3 IS NULL OR $3 = FALSE)
        ), booking_charges AS (
            /* Room charge */ 
            SELECT 'room' AS charge_type, NULL::int AS inclusion_id, r.* 
            FROM rsv_room_charge r
            WHERE ($2 IS NULL OR $2 = 'room') AND ($6 IS NULL OR $6 = charge_folio_id)

            UNION ALL /* Meals */ 
            Select m.meal, null, m.charge_date, m.meal_date, m.booking_id, m.booking_item, rsv.arrival, rsv.departure,
                   m.charge_dept, 1, m.amount, null, rsv.charge_to_booking, rsv.charge_folio_id, rsv.split_room_charge, m.posted    
            from rsv
                inner join sp_meal_charges(status := 'RSV', i_charge_date := $1, i_booking_id := $5, post_flag := $3) m 
                on m.booking_id = rsv.booking_id

            UNION ALL /* Inclusions */ 
            SELECT 'inclusion', inc.inclusion_id, inc.post_date, inc.charge_date, inc.booking_id, inc.booking_item, inc.arrival, inc.departure, 
                    inc.charge_dept, inc.qty, inc.charge_rate, inc.remark, inc.charge_to_booking, inc.charge_folio_id, inc.split_room_charge, FALSE AS posted
            FROM rsv_inclusion_charge inc --LEFT JOIN booking_rates rr ON inc.booking_item = rr.booking_item AND inc.post_date = rr.charge_date 
            WHERE ($2 IS NULL OR $2 = 'inclusion') 
                 AND NOT(inc.qty = 0 OR inc.charge_rate = 0) 
                 AND ($1 IS NULL OR inc.post_date = $1) 
                 AND ($3 IS NULL OR $3 = FALSE)
                 AND ($6 IS NULL OR $6 = charge_folio_id)
        )
/*
    post_date date DEFAULT NULL::date, 
    charge_type text DEFAULT NULL::text, 
    posted boolean DEFAULT NULL::boolean, 
    register_id integer DEFAULT NULL::integer, 
    booking_id integer DEFAULT NULL::integer, 
    folio_id integer DEFAULT NULL::integer, 
    include_inhouse boolean DEFAULT true, 
    include_booking boolean DEFAULT false
*/
        /* In House */   
        , schedule as (
            SELECT rm.room_number, r.booking_id, r.booking_item, f.split_room_charge, f.id as charge_folio_id, 
                coalesce(rp.room_charge_dept, fn_sys_param('AUTO_POST', 'ROOM', null::int)) as room_dept,
                coalesce(fn_sys_param('AUTO_POST', 'EXTRABED', null::int), 
                         rp.room_charge_dept, 
                         fn_sys_param('AUTO_POST', 'ROOM', null::int)) as extrabed_dept,
                S.* 
            FROM sp_rate_schedule_breakdown(post_date := $1, posted := $3, register_id := $4) S
                LEFT JOIN registration r on r.id = s.register_id 
                LEFT JOIN folio f on f.register_id = s.register_id 
                LEFT JOIN rate_plan rp on rp.id = s.rateplan_id
                LEFT JOIN room rm ON rm.id = s.room_id
            WHERE include_inhouse 
                and ($5 is null or r.booking_id = $5)
                and ($6 is null or f.id = $6)
            ORDER BY rm.room_number, s.register_id
        ), inh_charges as (
            /* Room Charge */
            select s.charge_date as post_date, s.register_id, s.room_id, s.booking_id, s.booking_item, s.arrival, s.departure, 
                s.charge_date, 'room' as charge_type, null::int as inclusion_id, room_dept as charge_dept,    
                1 as charge_qty, s.room as charge_amount, s.room as total_amount, null::text as remark, s.charge_folio_id,
                s.charge_to_booking, s.split_room_charge, s.room_posted as posted
            from schedule s
            where (s.room > 0) and ($2 is null or lower($2) = 'room') and ($3 is null or s.room_posted = $3)
            union all
            /* Extra Bed */
            select s.charge_date as post_date, s.register_id, s.room_id, s.booking_id, s.booking_item, s.arrival, s.departure, 
                s.charge_date, 'extrabed' as charge_type, null::int as inclusion_id, 
                s.extrabed_dept as charge_dept,    
                1 as charge_qty, s.extrabed as charge_amount, s.extrabed as total_amount, 
                iif(s.extrabed_dept = s.extrabed_dept, 'Extra Bed', null) as remark, 
                s.charge_folio_id, s.charge_to_booking, s.split_room_charge, s.room_posted as posted
            from schedule s
            where (s.extrabed > 0) and ($2 is null or lower($2) = 'extrabed') and ($3 is null or s.room_posted = $3) 
            union all
            /* ABF */
            select s.charge_date as post_date, s.register_id, s.room_id, s.booking_id, s.booking_item, s.arrival, s.departure, 
                s.charge_date+1 as charge_date, 'ABF' as charge_type, null::int as inclusion_id, 
                fn_sys_param('AUTO_POST', 'ABF', null::int) as charge_dept,    
                1 as charge_qty, s.abf as charge_amount, s.abf as total_amount, null, s.charge_folio_id,
                s.charge_to_booking, s.split_room_charge, s.room_posted as posted
            from schedule s
            where (s.abf > 0) and ($2 is null or lower($2) = 'abf') and ($3 is null or s.room_posted = $3) 
            union all
            /* Lunch */
            select s.charge_date as post_date, s.register_id, s.room_id, s.booking_id, s.booking_item, s.arrival, s.departure, 
                s.charge_date+1 as charge_date, 'Lunch' as charge_type, null::int as inclusion_id, 
                fn_sys_param('AUTO_POST', 'LUNCH', null::int) as charge_dept,    
                1 as charge_qty, s.lunch as charge_amount, s.lunch as total_amount, null, s.charge_folio_id,
                s.charge_to_booking, s.split_room_charge, s.room_posted as posted
            from schedule s
            where (s.lunch > 0) and ($2 is null or lower($2) = 'lunch') and ($3 is null or s.room_posted = $3)
            union all
            /* Dinner */
            select s.charge_date as post_date, s.register_id, s.room_id, s.booking_id, s.booking_item, s.arrival, s.departure, 
                s.charge_date, 'Dinner' as charge_type, null::int as inclusion_id, 
                fn_sys_param('AUTO_POST', 'DINNER', null::int) as charge_dept,    
                1 as charge_qty, s.dinner as charge_amount, s.dinner as total_amount, null, s.charge_folio_id,
                s.charge_to_booking, s.split_room_charge, s.room_posted as posted
            from schedule s
            where (s.dinner > 0) and ($2 is null or lower($2) = 'dinner') and ($3 is null or s.room_posted = $3)
            union all
            /* Inclusive */
            select s.charge_date as post_date, s.register_id, s.room_id, s.booking_id, s.booking_item, s.arrival, s.departure, 
                item.charge_date, 'inclusion' as charge_type, item.inclusion_id, item.charge_dept, item.qty,    
                item.charge_rate as charge_amount, item.qty * item.charge_rate as total_amount, item.name as remark, 
                s.charge_folio_id, s.charge_to_booking, s.split_room_charge, item.posted
            from schedule S, 
                 lateral json_to_recordset(s.inclusive_items)
                 as item(inclusion_id int, name text, charge_date date, charge_dept int, frequency int, 
                         qty int, charge_rate t_money, remark text, posted bool)
            where (s.inclusive_items is not null) 
                and (item.charge_rate > 0) 
                and ($2 is null or lower($2) in ('inclusion','inclusive')) 
                and ($3 is null or item.posted = $3)
            union all
            /* Other requests */
            select s.charge_date as post_date, s.register_id, s.room_id, s.booking_id, s.booking_item, s.arrival, s.departure, 
                item.charge_date, 'requirement' as charge_type, item.inclusion_id, item.charge_dept, item.qty,    
                item.charge_rate as charge_amount, item.qty * item.charge_rate as total_amount, item.name as remark, 
                s.charge_folio_id, s.charge_to_booking, s.split_room_charge, item.posted
            from schedule S, 
                 lateral json_to_recordset(s.other_items)
                 as item(inclusion_id int, name text, charge_date date, charge_dept int, frequency int, 
                         qty int, charge_rate t_money, remark text, posted bool)
            where (s.other_items is not null) 
                and (item.charge_rate > 0) 
                and ($2 is null or lower($2) = 'other') 
                and ($3 is null or item.posted = $3)
        )       
        SELECT rc.post_date, NULL::int AS register_id, NULL::int AS room_id, rc.booking_id, rc.booking_item, rc.arrival, rc.departure, rc.charge_date, rc.charge_type, rc.inclusion_id, 
            rc.charge_dept, rc.qty, rc.room_charge_amount::t_money, (rc.qty*rc.room_charge_amount)::t_money, rc.remark, rc.charge_folio_id, 
            rc.charge_to_booking, rc.split_room_charge, rc.posted 
        FROM booking_charges rc 
        WHERE include_booking
        UNION ALL 
        SELECT * FROM inh_charges WHERE include_inhouse
--        SELECT rc.post_date, rc.register_id, rc.room_id, rc.booking_id, NULL::int, rc.arrival, rc.departure, rc.charge_date, rc.charge_type, rc.inclusion_id, 
--            rc.charge_dept, rc.qty, rc.room_charge_amount::t_money, (rc.qty*rc.room_charge_amount)::t_money, rc.remark, rc.charge_folio_id, 
--            rc.charge_to_booking, rc.split_room_charge, rc.posted 
--        FROM inh_charges rc 
--        WHERE include_inhouse

sp_get_charge_schedule

Parameters

Definition