aonestar.public

Parameters

Name	Type	Mode
post_date	date	IN (DEFAULT NULL)
posted	boolean	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)
status	character	IN (DEFAULT 'I')
charge_to_booking	boolean	IN (DEFAULT false)
register_id	integer	OUT
charge_date	date	OUT
arrival	date	OUT
departure	date	OUT
adult	smallint	OUT
child	smallint	OUT
infant	smallint	OUT
extra_adult	smallint	OUT
extra_child	smallint	OUT
room_id	integer	OUT
rateplan_id	integer	OUT
mealtype_id	integer	OUT
rate_amount	t_money	OUT
room	t_money	OUT
extrabed	t_money	OUT
abf	t_money	OUT
lunch	t_money	OUT
dinner	t_money	OUT
inclusive	t_money	OUT
other	t_money	OUT
inclusive_items	json	OUT
other_items	json	OUT
charge_to_booking	boolean	OUT
room_posted	boolean	OUT
other_posted	boolean	OUT
approved	boolean	OUT
abf_included	t_money	OUT
lunch_included	t_money	OUT
dinner_included	t_money	OUT
abf_extra	t_money	OUT
lunch_extra	t_money	OUT
dinner_extra	t_money	OUT
room_net_dept	integer	OUT
room_net	numeric	OUT
room_service	numeric	OUT
room_tax	numeric	OUT

Definition

WITH INH AS (
        SELECT rg.id AS register_id, rg.booking_id, rg.folio_id, rg.room_id,
            rg.arrival, rg.departure, rg.rateplan_id, rg.mealtype_id,
            (rg.charge_to_booking and rg.is_group) as charge_to_booking,
            false as split_room_charge,
            rg.adult, rg.child, rg.infant, rg.extra_adult, rg.extra_child, rg.extra_infant,
            rg.included_adult, rg.included_child,
            iif(rg.charge_to_booking and rg.is_group, bk.folio_id, rg.folio_id) AS charge_folio_id,
            rg.room_posted, rg.approved
        FROM registration rg
            LEFT JOIN booking bk ON bk.id = rg.booking_id
        WHERE ($1 is null or $1 between rg.arrival and rg.departure)
            AND ($4 is null or rg.status = $4)
            AND ($3 is NULL OR rg.id = $3)
            AND ($5 is null OR ($5 = (rg.charge_to_booking and rg.is_group)))
    ), schedule as (
        SELECT inh.register_id, inh.booking_id, folio_id, room_id, arrival, departure, inh.rateplan_id,
            coalesce(rs.mealtype_id, inh.mealtype_id) as mealtype_id,
            inh.charge_to_booking, inh.adult, inh.child, inh.infant,
            inh.extra_adult, inh.extra_child, inh.included_adult, inh.included_child,
            charge_date, room_rate, extrabed_rate as extra_adult_rate, child_rate as extra_child_rate,
            (inh.extra_adult * rs.extrabed_rate) + (inh.extra_child * rs.child_rate) as extra_amt,
            room_charge_dept, posted, inclusive_posted, inh.approved
        FROM INH inner join registration_rates rs on rs.register_id = inh.register_id
        WHERE ($1 is null or rs.charge_date = $1)
    ), inclusive as (
        select inc.register_id, post_date,
            s.posted,
            coalesce(sum(qty * charge_rate),0) as charge_amt,
            json_agg(json_build_object(
                'inclusion_id', inclusion_id,
                'name', inc.name,
                'charge_date', inc.charge_date,
                'charge_dept', charge_dept,
                'frequency', frequency,
                'qty', qty,
                'charge_rate', charge_rate,
                'remark', remark,
                'charge_to_booking', inc.charge_to_booking,
                'posted', s.posted
            )) as items
        from (
            SELECT inh.register_id, d.post_date, inc.name,
                 CASE frequency
                    WHEN 1 THEN d.post_date +1
                    WHEN 2 THEN d.post_date
                    WHEN 3 THEN inh.arrival
                    WHEN 4 THEN inh.departure
                 END AS charge_date,
                 inc.inclusion_id, inc.charge_dept, inc.frequency,
                 fn_calc_inclusion_qty(1, adult, child, infant, 1, inc.charge_rate,
                                       inc.multiplier, inc.frequency, d.post_date, d.post_date+1) AS qty,
                 inc.charge_rate,
                 inh.charge_to_booking,
                 iif(inc.name <> dp.name, inc.name, null) AS remark
            FROM INH,
                LATERAL sp_get_rate_inclusions(inh.rateplan_id) AS inc
                LEFT JOIN department dp ON dp.id = inc.charge_dept,
                LATERAL fn_date_range(inh.arrival, inh.departure) AS d(post_date)
            WHERE ($1 is null or d.post_date = $1)
                  AND ((frequency = 1 AND d.post_date <> inh.arrival)
                      OR (frequency = 2 AND d.post_date <> inh.departure)
                      OR (frequency = 3 AND d.post_date = inh.arrival)
                      OR (frequency = 4 AND d.post_date = inh.departure))
        ) as INC
          LEFT JOIN schedule s on s.register_id = inc.register_id and s.charge_date = inc.post_date
        where qty > 0 and charge_rate > 0 AND ($2 is null or s.posted = $2)
        group by 1,2,3
    ), other as (
        SELECT register_id, post_date,
            coalesce(sum(total_amount),0) as charge_amt,
            bool_and(posted) as posted,
            json_agg(json_build_object(
                'charge_id', charge_id,
                'inclusion_id', inclusion_id,
                'name', req.name,
                'charge_date', charge_date,
                'charge_dept', charge_dept,
                'frequency', frequency,
                'qty', qty,
                'charge_rate', charge_rate,
                'remark', remark,
                'charge_to_booking', charge_to_booking,
                'posted', posted
            )) as items
        FROM (
            SELECT cs.id as charge_id, cs.register_id, rq.inclusion_id, inc.name,
                CASE coalesce(rq.frequency, inc.frequency)
                    WHEN 1 THEN IIF(inh.arrival=inh.departure, cs.charge_date, cs.charge_date-1)
                    WHEN 2 THEN cs.charge_date
                    WHEN 3 THEN cs.charge_date
                    WHEN 4 THEN IIF(inh.arrival=inh.departure, cs.charge_date, cs.charge_date-1)
                    ELSE cs.charge_date
                END AS post_date,
                cs.charge_date, rq.remark,
                coalesce(rq.frequency, inc.frequency) as frequency,
                coalesce(rq.charge_dept, inc.charge_dept) as charge_dept,
                cs.charge_amount as charge_rate, cs.charge_qty as qty, total_amount, cs.charge_to_booking, cs.posted
            FROM charge_schedule cs
                INNER JOIN INH ON inh.register_id = cs.register_id
                LEFT JOIN guest_request rq ON rq.id = cs.request_id
                LEFT JOIN inclusion inc ON inc.id = rq.inclusion_id
            WHERE (cs.charge_type = 'requirement')
                  AND (cs.register_id IS NOT NULL)
                  AND NOT cs.charge_to_booking        -- own-account reqs only; CTB items go to group folio via Phase C2
                  AND ($2 is null or cs.posted = $2)
        ) as REQ
        WHERE ($1 is null or req.post_date = $1)
        GROUP BY 1,2
        ORDER BY register_id desc, post_date
    ), rate_rows as (
        select s.register_id, s.charge_date, s.arrival, s.departure,
            s.adult, s.child, s.infant, s.extra_Adult, s.extra_child, s.room_id,
            s.rateplan_id, s.mealtype_id, s.room_rate as rate_amount,
            CASE WHEN s.room_rate::numeric = 0 THEN 0::t_money ELSE
                (s.room_rate
                 - coalesce(m.abf_included_amt,0)
                 - coalesce(m.lunch_included_amt,0)
                 - coalesce(m.dinner_included_amt,0)
                 - coalesce(inc.charge_amt,0))
            END as room,
            (s.extra_amt
             - coalesce(m.abf_extra_amt,0)
             - coalesce(m.lunch_extra_amt,0)
             - coalesce(m.dinner_extra_amt,0)) as extrabed,
            coalesce(m.abf_amt,0) as abf,
            coalesce(m.lunch_amt,0) as lunch,
            coalesce(m.dinner_amt,0) as dinner,
            coalesce(inc.charge_amt,0) as inclusive,
            coalesce(oth.charge_amt,0) as other,
            inc.items as inclusive_items,
            oth.items as other_items,
            s.charge_to_booking, s.posted as room_posted, oth.posted as other_posted, s.approved,
            coalesce(m.abf_included_amt,0) as abf_included,
            coalesce(m.lunch_included_amt,0) as lunch_included,
            coalesce(m.dinner_included_amt,0) as dinner_included,
            coalesce(m.abf_extra_amt,0) as abf_extra,
            coalesce(m.lunch_extra_amt,0) as lunch_extra,
            coalesce(m.dinner_extra_amt,0) as dinner_extra
        from schedule s
            left join inclusive inc
                on inc.register_id = s.register_id and inc.post_date = s.charge_date
            left join other oth
                on oth.register_id = s.register_id and oth.post_date = s.charge_date
            left join sp_meal_plan(
                            i_charge_date := $1,
                            post_flag := $2,
                            i_register_id := $3,
                            status := IIF($4 = 'I', 'INH', 'ALL')
                      ) m
                on m.register_id = s.register_id and m.charge_date = s.charge_date
        where ($2 is null) or ($2 = s.posted) or (inc.items is not null) or (oth.items is not null)
    )
    select r.register_id, r.charge_date, r.arrival, r.departure,
        r.adult, r.child, r.infant, r.extra_adult, r.extra_child, r.room_id,
        r.rateplan_id, r.mealtype_id, r.rate_amount,
        r.room, r.extrabed, r.abf, r.lunch, r.dinner, r.inclusive, r.other,
        r.inclusive_items, r.other_items,
        r.charge_to_booking, r.room_posted, r.other_posted, r.approved,
        r.abf_included, r.lunch_included, r.dinner_included,
        r.abf_extra, r.lunch_extra, r.dinner_extra,
        /* Room-charge department, resolved exactly the way sp_auto_post does, so
           room_net matches the BASE_PRICE frozen on the posted transaction.
           NOTE: rate_plan.room_charge_dept -- NOT registration_rates.room_charge_dept,
           which is only a "already posted" guard and is mostly NULL. */
        coalesce(rp.room_charge_dept, fn_sys_param('AUTO_POST', 'ROOM', null::int)) as room_net_dept,
        ib.base    as room_net,
        ib.service as room_service,
        ib.tax     as room_tax
    from rate_rows r
        left join rate_plan rp on rp.id = r.rateplan_id
        left join department d
            on d.id = coalesce(rp.room_charge_dept, fn_sys_param('AUTO_POST', 'ROOM', null::int))
        left join lateral fn_itemizer_breakdown(r.room, coalesce(d.itemizers, '{}'::text[])) ib on true
    order by r.register_id desc, r.charge_date;

sp_rate_schedule_breakdown

Description

Parameters

Definition