aonestar.public

Parameters

Name	Type	Mode
i_register_id	integer	IN (DEFAULT NULL)
i_booking_id	integer	IN (DEFAULT NULL)
i_charge_date	date	IN (DEFAULT NULL)
status	character varying	IN (DEFAULT 'ALL')
post_flag	boolean	IN (DEFAULT NULL)
single_room	boolean	IN (DEFAULT false)
register_id	integer	OUT
booking_id	integer	OUT
booking_item	integer	OUT
charge_date	date	OUT
mealtype_id	integer	OUT
mealtype_name	text	OUT
abf_qty	smallint	OUT
lunch_qty	smallint	OUT
dinner_qty	smallint	OUT
abf_adult_qty	smallint	OUT
abf_child_qty	smallint	OUT
lunch_adult_qty	smallint	OUT
lunch_child_qty	smallint	OUT
dinner_adult_qty	smallint	OUT
dinner_child_qty	smallint	OUT
abf_amt	t_money	OUT
lunch_amt	t_money	OUT
dinner_amt	t_money	OUT
abf_included_amt	t_money	OUT
lunch_included_amt	t_money	OUT
dinner_included_amt	t_money	OUT
abf_extra_amt	t_money	OUT
lunch_extra_amt	t_money	OUT
dinner_extra_amt	t_money	OUT
posted	boolean	OUT

Definition

BEGIN 
     /* Status: RSV, INH, ALL */ 

    if STATUS in ('ALL','INH') and (i_booking_id is null) then
        RETURN QUERY
        Select rs.register_id, null::int, null::int, rs.charge_date, 
           coalesce(rs.mealtype_id, rg.mealtype_id) as mealtype_id,
           m.name,
           --rg.included_adult, rg.included_child, rg.extra_adult, rg.extra_child, 1 as qty,
           qty.total_abf as abf_qty, 
           qty.total_lunch as lunch_qty, 
           qty.total_dinner as dinner_qty,
           
           qty.abf_adult + qty.abf_extra_adult as abf_adult_qty,
           qty.abf_child + qty.abf_extra_child as abf_child_qty,
           
           qty.lunch_adult + qty.lunch_extra_adult as lunch_adult_qty,
           qty.lunch_child + qty.lunch_extra_child as lunch_child_qty,
           
           qty.dinner_adult + qty.dinner_extra_adult as dinner_adult_qty, 
           qty.dinner_child + qty.dinner_extra_child as dinner_child_qty,
           
           (coalesce(qty.abf_adult * m.abf, 0.0) 
           + coalesce(qty.abf_child * m.abf_child, 0.0) 
           + coalesce(qty.abf_extra_adult * m.abf_extra_adult, 0.0)  
           + coalesce(qty.abf_extra_child * m.abf_extra_child, 0.0))::t_money as abf_amt,
           
           (coalesce(qty.lunch_adult * m.lunch, 0.0)
           + coalesce(qty.lunch_child * m.lunch_child, 0.0) 
           + coalesce(qty.lunch_extra_adult * m.lunch_extra_adult, 0.0)
           + coalesce(qty.lunch_extra_child * m.lunch_extra_child, 0.0))::t_money as lunch_amt,
           
           (coalesce(qty.dinner_adult * m.dinner, 0.0)
           + coalesce(qty.dinner_child * m.dinner_child, 0.0) 
           + coalesce(qty.dinner_extra_adult * m.dinner_extra_adult, 0.0) 
           + coalesce(qty.dinner_extra_child * m.dinner_extra_child, 0.0))::t_money as dinner_amt,

           -- included amounts 
           (coalesce(qty.abf_adult * m.abf, 0.0) + coalesce(qty.abf_child * m.abf_child, 0.0))::t_money ,  
           (coalesce(qty.lunch_adult * m.lunch, 0.0) + coalesce(qty.lunch_child * m.lunch_child, 0.0))::t_money,
           (coalesce(qty.dinner_adult * m.dinner, 0.0) + coalesce(qty.dinner_child * m.dinner_child, 0.0))::t_money,  
           -- extra amounts  
           (coalesce(qty.abf_extra_adult * m.abf_extra_adult, 0.0) 
            + coalesce(qty.abf_extra_child * m.abf_extra_child, 0.0))::t_money,  
           (coalesce(qty.lunch_extra_adult * m.lunch_extra_adult, 0.0) 
            + coalesce(qty.lunch_extra_child * m.lunch_extra_child, 0.0))::t_money,
           (coalesce(qty.dinner_extra_adult * m.dinner_extra_adult, 0.0) 
            + coalesce(qty.dinner_extra_child * m.dinner_extra_child, 0.0))::t_money,

           rs.posted 
           
    --        qty.abf_adult as abf_include_adult_qty,
    --        qty.abf_child as abf_include_child_qty, 
    --        qty.abf_extra_adult as abf_extra_adult_qty, 
    --        qty.abf_extra_child as abf_extra_child_qty,
    --        
    --        qty.lunch_adult as lunch_include_adult_qty,
    --        qty.lunch_child as lunch_include_child_qty, 
    --        qty.lunch_extra_adult as lunch_extra_adult_qty, 
    --        qty.lunch_extra_child as lunch_extra_child_qty,
    --        
    --        qty.dinner_adult as dinner_include_adult_qty,
    --        qty.dinner_child as dinner_include_child_qty, 
    --        qty.dinner_extra_adult as dinner_extra_adult_qty, 
    --        qty.dinner_extra_child as dinner_extra_child_qty,
    --        
    --        qty.abf_adult * m.abf as abf_include_adult_amt,
    --        qty.abf_child * m.abf_child as abf_include_child_amt, 
    --        qty.abf_extra_adult * m.abf_extra_adult as abf_extra_adult_amt, 
    --        qty.abf_extra_child * m.abf_extra_child as abf_extra_child_amt,
    --        
    --        qty.lunch_adult * m.lunch as lunch_include_adult_amt,
    --        qty.lunch_child * m.lunch_child as lunch_include_child_amt, 
    --        qty.lunch_extra_adult * m.lunch_extra_adult as lunch_extra_adult_amt, 
    --        qty.lunch_extra_child * m.lunch_extra_child as lunch_extra_child_amt,
    --        
    --        qty.dinner_adult * m.dinner as dinner_include_adult_amt,
    --        qty.dinner_child * m.dinner_child as dinner_include_child_amt, 
    --        qty.dinner_extra_adult * m.dinner_extra_adult as dinner_extra_adult_amt, 
    --        qty.dinner_extra_child * m.dinner_extra_child as dinner_extra_child_amt,
    --        
--           , m.abf as abf_adult_rate, 
--            m.abf_child as abf_child_rate, 
--            m.abf_extra_adult as abf_extra_adult_rate, 
--            m.abf_extra_child as abf_extra_child_rate, 
--            m.lunch as lunch_adult_rate, 
--            m.lunch_child as lunch_child_rate, 
--            m.lunch_extra_adult as lunch_extra_adult_rate, 
--            m.lunch_extra_child as lunch_extra_child_rate, 
--            m.dinner as dinner_adult_rate, 
--            m.dinner_child as dinner_child_rate, 
--            m.dinner_extra_adult as dinner_extra_adult_rate, 
--            m.dinner_extra_child as dinner_extra_child_rate       
        from registration rg
                left join registration_rates rs on rs.register_id = rg.id
                left join meal_type m on m.id = coalesce(rs.mealtype_id, rg.mealtype_id),
                lateral sp_get_meal_qty(m.id, rg.included_adult, rg.included_child, rg.extra_adult, rg.extra_child) as qty
        where (qty.total_abf > 0 or qty.total_lunch > 0 or qty.total_dinner > 0)
              and ((i_register_id is not null and rg.id = i_register_id) or (i_register_id is null and rg.status = 'I'))
              and (i_charge_date is null or i_charge_date = rs.charge_date)
              and (post_flag is null or rs.posted = post_flag);
    END IF;

    if STATUS in ('ALL','RSV') and (i_register_id is null) and (post_flag is null or post_flag = false) then
        RETURN QUERY
        Select null::int, b.id, bi.item_id, rs.charge_date, 
           coalesce(rs.mealtype_id, bi.mealtype_id) as mealtype_id,
           m.name,
           --rg.included_adult, rg.included_child, rg.extra_adult, rg.extra_child, 1 as qty,
           qty.total_abf as abf_qty, 
           qty.total_lunch as lunch_qty, 
           qty.total_dinner as dinner_qty,
           
           qty.abf_adult + qty.abf_extra_adult as abf_adult_qty,
           qty.abf_child + qty.abf_extra_child as abf_child_qty,
           
           qty.lunch_adult + qty.lunch_extra_adult as lunch_adult_qty,
           qty.lunch_child + qty.lunch_extra_child as lunch_child_qty,
           
           qty.dinner_adult + qty.dinner_extra_adult as dinner_adult_qty, 
           qty.dinner_child + qty.dinner_extra_child as dinner_child_qty,
           
           (coalesce(qty.abf_adult * m.abf, 0.0) 
           + coalesce(qty.abf_child * m.abf_child, 0.0) 
           + coalesce(qty.abf_extra_adult * m.abf_extra_adult, 0.0)  
           + coalesce(qty.abf_extra_child * m.abf_extra_child, 0.0))::t_money as abf_amt,
           
           (coalesce(qty.lunch_adult * m.lunch, 0.0)
           + coalesce(qty.lunch_child * m.lunch_child, 0.0) 
           + coalesce(qty.lunch_extra_adult * m.lunch_extra_adult, 0.0)
           + coalesce(qty.lunch_extra_child * m.lunch_extra_child, 0.0))::t_money as lunch_amt,
           
           (coalesce(qty.dinner_adult * m.dinner, 0.0)
           + coalesce(qty.dinner_child * m.dinner_child, 0.0) 
           + coalesce(qty.dinner_extra_adult * m.dinner_extra_adult, 0.0) 
           + coalesce(qty.dinner_extra_child * m.dinner_extra_child, 0.0))::t_money as dinner_amt,

           -- included amounts 
           (coalesce(qty.abf_adult * m.abf, 0.0) + coalesce(qty.abf_child * m.abf_child, 0.0))::t_money,  
           (coalesce(qty.lunch_adult * m.lunch, 0.0) + coalesce(qty.lunch_child * m.lunch_child, 0.0))::t_money,
           (coalesce(qty.dinner_adult * m.dinner, 0.0) + coalesce(qty.dinner_child * m.dinner_child, 0.0))::t_money,  
           -- extra amounts  
           (coalesce(qty.abf_extra_adult * m.abf_extra_adult, 0.0) 
            + coalesce(qty.abf_extra_child * m.abf_extra_child, 0.0))::t_money,  
           (coalesce(qty.lunch_extra_adult * m.lunch_extra_adult, 0.0) 
            + coalesce(qty.lunch_extra_child * m.lunch_extra_child, 0.0))::t_money,
           (coalesce(qty.dinner_extra_adult * m.dinner_extra_adult, 0.0) 
            + coalesce(qty.dinner_extra_child * m.dinner_extra_child, 0.0))::t_money,

           false as posted
         
        from booking b 
            left join booking_items bi on bi.booking_id = b.id
            left join booking_rates rs on rs.booking_item = bi.item_id
            left join meal_type m on m.id = coalesce(rs.mealtype_id, bi.mealtype_id),
            lateral sp_get_meal_qty(m.id, bi.included_adult, bi.included_child, bi.extra_adult, bi.extra_child, IIF(SINGLE_ROOM, 1, bi.qty-bi.inhouse_qty)::int2) as qty
        where (qty.total_abf > 0 or qty.total_lunch > 0 or qty.total_dinner > 0)
              and ( (i_booking_id is not null and b.id = i_booking_id) 
                    or (i_booking_id is null and b.status in ('R','C','I') and (bi.qty-bi.inhouse_qty) > 0) )
              and (i_charge_date is null or i_charge_date = rs.charge_date); 
    END IF;
END;

sp_meal_plan

Parameters

Definition