aonestar.public

Parameters

Name	Type	Mode
i_booking_id	integer	IN (DEFAULT NULL)
i_scope	text	IN (DEFAULT 'all')
i_item_id	integer	IN (DEFAULT NULL)
i_register_id	integer	IN (DEFAULT NULL)
i_rate_date	date	IN (DEFAULT NULL)
i_charge_to_booking	boolean	IN (DEFAULT NULL)

Definition

SELECT (
        -- booking side
        CASE WHEN i_scope IN ('booking','all') AND i_booking_id IS NOT NULL THEN (
            SELECT COALESCE(jsonb_agg(jsonb_build_object(
                        'id',              bi.item_id,
                        'source',          'booking',
                        'description',     fn_accommodation(rt.code, bt.code::text, bi.qty, rp.name, bi.arrival, bi.departure),
                        'extra_adult_qty', bi.extra_adult,
                        'extra_child_qty', bi.extra_child,
                        'extra_infant_qty',bi.extra_infant,
                        'rate_schedule',   COALESCE((
                            SELECT jsonb_agg(jsonb_build_object(
                                        'key',             bi.item_id::text || '-' || to_char(b.charge_date,'YYYYMMDD'),
                                        'date',            b.charge_date,
                                        'rateplan_id',     b.rateplan_id,
                                        'mealtype_id',     b.mealtype_id,
                                        'room_qty',        b.room_qty,
                                        'adult',           b.adult,
                                        'child',           b.child,
                                        'infant',          b.infant,
                                        'extra_adult',     b.extra_adult,
                                        'extra_child',     b.extra_child,
                                        'rate_amount',     b.rate_amount,
                                        'rate_extra_adult',b.rate_extra_adult,
                                        'room',            b.room,
                                        'extrabed',        b.extrabed,
                                        'abf',             b.abf,
                                        'lunch',           b.lunch,
                                        'dinner',          b.dinner,
                                        'inclusive',       b.inclusive,
                                        'other',           b.other,
                                        'inclusive_items', b.inclusive_items,
                                        'other_items',     b.other_items,
                                        'charge_to_booking',b.charge_to_booking,
                                        'abf_included',    b.abf_included,
                                        'lunch_included',  b.lunch_included,
                                        'dinner_included', b.dinner_included,
                                        'abf_extra',       b.abf_extra,
                                        'lunch_extra',     b.lunch_extra,
                                        'dinner_extra',    b.dinner_extra
                                   ) ORDER BY b.charge_date)
                            FROM sp_booking_rates_breakdown(
                                     i_rate_date         := i_rate_date,
                                     i_booking_id        := i_booking_id,
                                     i_booking_item      := bi.item_id,
                                     i_charge_to_booking := i_charge_to_booking,
                                     only_not_checked_in := true
                                 ) b
                        ), '[]'::jsonb)
                   ) ORDER BY bi.item_id), '[]'::jsonb)
            FROM booking_items bi
                LEFT JOIN rate_plan  rp ON rp.id = bi.rateplan_id
                LEFT JOIN room_type  rt ON rt.id = bi.roomtype_id
                LEFT JOIN bed_type   bt ON bt.id = bi.bedtype_id
            WHERE bi.booking_id = i_booking_id
              AND (i_item_id IS NULL OR bi.item_id = i_item_id)
              AND (bi.qty - bi.inhouse_qty) > 0
        ) ELSE '[]'::jsonb END
        ||
        -- in-house side
        CASE WHEN i_scope IN ('inhouse','all') THEN (
            SELECT COALESCE(jsonb_agg(jsonb_build_object(
                        'id',              rg.id,
                        'source',          'inhouse',
                        'description',     fn_accommodation('Room '||rm.room_number, null, null, rp.name, rg.arrival, rg.departure),
                        'extra_adult_qty', rg.extra_adult,
                        'extra_child_qty', rg.extra_child,
                        'extra_infant_qty',rg.extra_infant,
                        'rate_schedule',   COALESCE((
                            SELECT jsonb_agg(jsonb_build_object(
                                        'key',             rg.id::text || '-' || to_char(s.charge_date,'YYYYMMDD'),
                                        'date',            s.charge_date,
                                        'rateplan_id',     s.rateplan_id,
                                        'mealtype_id',     s.mealtype_id,
                                        'adult',           s.adult,
                                        'child',           s.child,
                                        'infant',          s.infant,
                                        'extra_adult',     s.extra_adult,
                                        'extra_child',     s.extra_child,
                                        'rate_amount',     s.rate_amount,
                                        'room',            s.room,
                                        'extrabed',        s.extrabed,
                                        'abf',             s.abf,
                                        'lunch',           s.lunch,
                                        'dinner',          s.dinner,
                                        'inclusive',       s.inclusive,
                                        'other',           s.other,
                                        'inclusive_items', s.inclusive_items,
                                        'other_items',     s.other_items,
                                        'charge_to_booking',s.charge_to_booking,
                                        'room_posted',     s.room_posted,
                                        'other_posted',    s.other_posted,
                                        'approved',        s.approved,
                                        'abf_included',    s.abf_included,
                                        'lunch_included',  s.lunch_included,
                                        'dinner_included', s.dinner_included,
                                        'abf_extra',       s.abf_extra,
                                        'lunch_extra',     s.lunch_extra,
                                        'dinner_extra',    s.dinner_extra
                                   ) ORDER BY s.charge_date)
                            FROM sp_rate_schedule_breakdown(
                                     post_date         := i_rate_date,
                                     posted            := NULL,
                                     register_id       := rg.id,
                                     status            := NULL,
                                     charge_to_booking := i_charge_to_booking
                                 ) s
                        ), '[]'::jsonb)
                   ) ORDER BY rg.id), '[]'::jsonb)
            FROM registration rg
                LEFT JOIN rate_plan rp ON rp.id = rg.rateplan_id
                LEFT JOIN room      rm ON rm.id = rg.room_id
            WHERE (i_register_id IS NOT NULL AND rg.id = i_register_id)
               OR (i_register_id IS NULL AND rg.booking_id = i_booking_id AND rg.status IN ('I','O'))
        ) ELSE '[]'::jsonb END
    )::json

sp_get_rate_breakdown_js

Parameters

Definition