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)
i_meal_date	date	IN (DEFAULT NULL)
status	text	IN (DEFAULT 'INH')
abf_flag	boolean	IN (DEFAULT true)
lunch_flag	boolean	IN (DEFAULT true)
dinner_flag	boolean	IN (DEFAULT true)
post_flag	boolean	IN (DEFAULT NULL)
register_id	integer	OUT
booking_id	integer	OUT
booking_item	integer	OUT
charge_date	date	OUT
meal_date	date	OUT
mealtype_id	integer	OUT
meal	text	OUT
adult_qty	smallint	OUT
child_qty	smallint	OUT
total_qty	smallint	OUT
amount	t_money	OUT
charge_dept	integer	OUT
posted	boolean	OUT

Definition

declare 
    v_abf_dept int;
    v_lunch_dept int;
    v_dinner_dept int;
BEGIN
    /* Status: RSV, INH, ALL */ 

    v_abf_dept    := fn_sys_param('AUTO_POST', 'ABF', null::int);
    v_lunch_dept  := fn_sys_param('AUTO_POST', 'LUNCH', null::int);
    v_dinner_dept := fn_sys_param('AUTO_POST', 'DINNER', null::int);

    RETURN QUERY
    with meals as (
        select *
        from sp_meal_plan(i_register_id, i_booking_id, i_charge_date, status, post_flag) mp
    )

    Select m.register_id, m.booking_id, m.booking_item, m.charge_date, m.charge_date+1, m.mealtype_id, 'ABF' as meal, 
        m.abf_adult_qty, m.abf_child_qty, m.abf_qty, m.abf_amt, v_abf_dept, m.posted
    from meals m
    where (abf_flag = true) and (m.abf_qty > 0) and (i_meal_date is null or i_meal_date = m.charge_date+1) 

    union all
    Select m.register_id, m.booking_id, m.booking_item, m.charge_date, m.charge_date+1, m.mealtype_id, 'Lunch' as meal, 
        m.lunch_adult_qty, m.lunch_child_qty, m.lunch_qty, m.lunch_amt, v_lunch_dept, m.posted
    from meals m
    where (lunch_flag = true) and (m.lunch_qty > 0) and (i_meal_date is null or i_meal_date = m.charge_date+1) 

    union all
    Select m.register_id, m.booking_id, m.booking_item, m.charge_date, m.charge_date, m.mealtype_id, 'Dinner' as meal, 
        m.dinner_adult_qty, m.dinner_child_qty, m.dinner_qty, m.dinner_amt, v_dinner_dept, m.posted
    from meals m
    where (dinner_flag = true) and (m.dinner_qty > 0) and (i_meal_date is null or i_meal_date = m.charge_date);
END

sp_meal_charges

Parameters

Definition