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

Definition

--declare SYS_DATE date = fn_system_date();
--BEGIN
    --RETURN QUERY
    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)))
           -- AND ($2 is null or s.posted = $2)  
    ), 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) --and ($2 is null or rs.posted = $2)
    ), inclusive as (
        select inc.register_id, post_date, 
            s.posted,
            coalesce(sum(qty * charge_rate),0) as charge_amt,
           -- bool_and(s.inclusive_posted),
            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, --false as posted, --s.inclusive_posted as posted, 
                 inh.charge_to_booking,
                 iif(inc.name <> dp.name, inc.name, null) AS remark --inc.name 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 ($2 is null or s.inclusive_posted = $2)  
                  AND ((frequency = 1 AND d.post_date <> inh.arrival)       -- Daily except arrival
                      OR (frequency = 2 AND d.post_date <> inh.departure)   -- Daily except departure  
                      OR (frequency = 3 AND d.post_date = inh.arrival)      -- Once on arrival
                      OR (frequency = 4 AND d.post_date = inh.departure))   -- Once on 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 ($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    
    
    )
    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, --s.extra_adult_rate, s.extra_child_rate,
        (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)) as room, 
        (s.extra_amt 
         - coalesce(m.abf_extra_amt,0) 
         - coalesce(m.lunch_extra_amt,0) 
         - coalesce(m.dinner_extra_amt,0)) as extra_bed, 
        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, oth.posted, s.approved,
        coalesce(m.abf_included_amt,0), coalesce(m.lunch_included_amt,0), coalesce(m.dinner_included_amt,0),
        coalesce(m.abf_extra_amt,0), coalesce(m.lunch_extra_amt,0), coalesce(m.dinner_extra_amt,0)
    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)
    order by register_id desc, charge_date--;
--END

sp_rate_schedule_breakdown

Parameters

Definition