aonestar.public

Parameters

Name	Type	Mode
mealtype_id	integer	IN
included_adult	smallint	IN
included_child	smallint	IN
extra_adult	smallint	IN
extra_child	smallint	IN
room_qty	smallint	IN (DEFAULT 1)
abf_adult	smallint	OUT
abf_child	smallint	OUT
abf_extra_adult	smallint	OUT
abf_extra_child	smallint	OUT
lunch_adult	smallint	OUT
lunch_child	smallint	OUT
lunch_extra_adult	smallint	OUT
lunch_extra_child	smallint	OUT
dinner_adult	smallint	OUT
dinner_child	smallint	OUT
dinner_extra_adult	smallint	OUT
dinner_extra_child	smallint	OUT
total_abf	smallint	OUT
total_lunch	smallint	OUT
total_dinner	smallint	OUT

Definition

BEGIN
    SELECT
        ROOM_QTY * INCLUDED_ADULT * CASE WHEN m.abf IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * INCLUDED_CHILD * CASE WHEN m.abf_child IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * EXTRA_ADULT * CASE WHEN m.abf_extra_adult IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * EXTRA_CHILD * CASE WHEN m.abf_extra_child IS NULL THEN 0 ELSE 1 END,
        
        ROOM_QTY * INCLUDED_ADULT * CASE WHEN m.lunch IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * INCLUDED_CHILD * CASE WHEN m.lunch_child IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * EXTRA_ADULT * CASE WHEN m.lunch_extra_adult IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * EXTRA_CHILD * CASE WHEN m.lunch_extra_child IS NULL THEN 0 ELSE 1 END,
        
        ROOM_QTY * INCLUDED_ADULT * CASE WHEN m.dinner IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * INCLUDED_CHILD * CASE WHEN m.dinner_child IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * EXTRA_ADULT * CASE WHEN m.dinner_extra_adult IS NULL THEN 0 ELSE 1 END,
        ROOM_QTY * EXTRA_CHILD * CASE WHEN m.dinner_extra_child IS NULL THEN 0 ELSE 1 END
    INTO
        ABF_ADULT, ABF_CHILD, ABF_EXTRA_ADULT, ABF_EXTRA_CHILD,
        LUNCH_ADULT, LUNCH_CHILD, LUNCH_EXTRA_ADULT, LUNCH_EXTRA_CHILD,
        DINNER_ADULT, DINNER_CHILD, DINNER_EXTRA_ADULT, DINNER_EXTRA_CHILD
    FROM meal_type m
    WHERE m.id = $1;
    
    TOTAL_ABF := ABF_ADULT + ABF_CHILD + ABF_EXTRA_ADULT + ABF_EXTRA_CHILD;
    TOTAL_LUNCH := LUNCH_ADULT + LUNCH_CHILD + LUNCH_EXTRA_ADULT + LUNCH_EXTRA_CHILD;
    TOTAL_DINNER := DINNER_ADULT + DINNER_CHILD + DINNER_EXTRA_ADULT + DINNER_EXTRA_CHILD;
END;

sp_get_meal_qty

Parameters

Definition