aonestar.public

Parameters

Name	Type	Mode
p_from	date	IN (DEFAULT NULL)
p_to	date	IN (DEFAULT NULL)

Definition

DECLARE
    _room_dept_default int := public.fn_sys_param('AUTO_POST', 'ROOM', NULL::int);
    _rows              int;
BEGIN
    /* ---- 1. room_summary --------------------------------------------------
       Source precedence: the BASE_PRICE frozen on the posted room-charge transaction
       first, then a recompute with today's department itemizers, then leave NULL.
       The frozen value wins because department.itemizers is NOT versioned -- on aonestar
       the 2019 postings froze {SER,VAT} while department 1 now carries only {VAT}, so
       recomputing old nights with today's set would invent numbers. */
    UPDATE public.room_summary rs
    SET revenue_room_net = src.room_net
    FROM (
        SELECT rs2.id,
               coalesce(
                   (SELECT x.amount FROM jsonb_to_recordset(pt.itemizers) x(code text, amount numeric)
                     WHERE x.code = 'BASE_PRICE'),
                   (SELECT x.amount FROM jsonb_to_recordset(bt.itemizers) x(code text, amount numeric)
                     WHERE x.code = 'BASE_PRICE'),
                   public.fn_itemizer_base_amount(
                       coalesce(rs2.room_charge, rs2.room_rate),
                       coalesce(rp.room_charge_dept, _room_dept_default))
               )::public.t_money AS room_net
        FROM public.room_summary rs2
             LEFT JOIN public.registration_rates rr
                    ON rr.register_id = rs2.register_id AND rr.charge_date = rs2.date_ref
             LEFT JOIN public.rate_plan rp ON rp.id = rs2.rateplan_id
             LEFT JOIN public.transactions pt
                    ON pt.id = rr.tran_id AND pt.active AND pt.itemizers IS NOT NULL
             LEFT JOIN backup.transactions bt
                    ON bt.id = rr.tran_id AND bt.active AND bt.itemizers IS NOT NULL
        WHERE rs2.revenue_room_net IS NULL
          AND rs2.register_id IS NOT NULL
          AND coalesce(rs2.room_charge, rs2.room_rate) IS NOT NULL
          AND (p_from IS NULL OR rs2.date_ref >= p_from)
          AND (p_to   IS NULL OR rs2.date_ref <= p_to)
    ) src
    WHERE rs.id = src.id AND src.room_net IS NOT NULL;

GET DIAGNOSTICS _rows = ROW_COUNT;
    RAISE NOTICE 'room_summary.revenue_room_net: % row(s) filled', _rows;

/* ---- 2. recapitulate, derived from room_summary ------------------------
       Buckets the day the way sp_get_recapitulate does. Dates with no room_summary
       coverage stay NULL -- on aonestar recapitulate reaches back to 2007 while
       room_summary starts in 2017. */
    UPDATE public.recapitulate rc
    SET individual_revenue_room_net = d.ind,
        group_revenue_room_net      = d.grp,
        day_used_revenue_room_net   = d.dayuse
    FROM (
        SELECT rs.date_ref,
               coalesce(sum(rs.revenue_room_net)
                        FILTER (WHERE NOT rs.day_used AND coalesce(rs.book_type,'I') <> 'G'), 0)::public.t_money AS ind,
               coalesce(sum(rs.revenue_room_net)
                        FILTER (WHERE NOT rs.day_used AND rs.book_type = 'G'), 0)::public.t_money AS grp,
               coalesce(sum(rs.revenue_room_net)
                        FILTER (WHERE rs.day_used), 0)::public.t_money AS dayuse
        FROM public.room_summary rs
        WHERE rs.room_status = 'OCC'
          AND NOT rs.share_room
          AND rs.revenue_room_net IS NOT NULL
          AND (p_from IS NULL OR rs.date_ref >= p_from)
          AND (p_to   IS NULL OR rs.date_ref <= p_to)
        GROUP BY rs.date_ref
    ) d
    WHERE rc.date_ref = d.date_ref
      AND rc.individual_revenue_room_net IS NULL;

GET DIAGNOSTICS _rows = ROW_COUNT;
    RAISE NOTICE 'recapitulate.*_revenue_room_net: % day(s) filled', _rows;

/* ---- 3. guest_history -------------------------------------------------- */
    UPDATE public.guest_history gh
    SET revenue_room_net = src.room_net
    FROM (
        SELECT gh2.id,
               (SELECT sum(coalesce(
                           (SELECT x.amount FROM jsonb_to_recordset(pt.itemizers) x(code text, amount numeric)
                             WHERE x.code = 'BASE_PRICE'),
                           (SELECT x.amount FROM jsonb_to_recordset(bt.itemizers) x(code text, amount numeric)
                             WHERE x.code = 'BASE_PRICE'),
                           public.fn_itemizer_base_amount(
                               coalesce(nullif(r.room, 0), r.room_rate),
                               coalesce(rp.room_charge_dept, _room_dept_default))
                       ))::public.t_money
                  FROM (
                        SELECT rr.room, rr.room_rate, rr.tran_id, rr.rateplan_id
                        FROM public.registration_rates rr
                        WHERE rr.register_id = gh2.register_id
                        UNION ALL
                        SELECT brr.room, brr.room_rate, brr.tran_id, brr.rateplan_id
                        FROM backup.registration_rates brr
                        WHERE brr.register_id = gh2.register_id
                          AND NOT EXISTS (SELECT 1 FROM public.registration_rates p
                                           WHERE p.register_id = brr.register_id
                                             AND p.charge_date = brr.charge_date)
                       ) r
                       LEFT JOIN public.rate_plan rp ON rp.id = r.rateplan_id
                       LEFT JOIN public.transactions pt
                              ON pt.id = r.tran_id AND pt.active AND pt.itemizers IS NOT NULL
                       LEFT JOIN backup.transactions bt
                              ON bt.id = r.tran_id AND bt.active AND bt.itemizers IS NOT NULL
               ) AS room_net
        FROM public.guest_history gh2
        WHERE gh2.revenue_room_net IS NULL
          AND (p_from IS NULL OR gh2.arrival >= p_from)
          AND (p_to   IS NULL OR gh2.arrival <= p_to)
    ) src
    WHERE gh.id = src.id AND src.room_net IS NOT NULL;

GET DIAGNOSTICS _rows = ROW_COUNT;
    RAISE NOTICE 'guest_history.revenue_room_net: % stay row(s) filled', _rows;

/* ---- 4. rsv_pickup -----------------------------------------------------
       Both EXISTS checks matter: booking_rates rows can outlive their booking_items row,
       and without the item sp_booking_rates_breakdown returns nothing. Rows with no
       surviving booking item stay NULL and readers fall back to room_charge. */
    UPDATE public.rsv_pickup rp
    SET revenue_room_net = public.fn_booking_room_net_revenue(rp.booking_item)
    WHERE rp.revenue_room_net IS NULL
      AND rp.status <> 'AMD'
      AND EXISTS (SELECT 1 FROM public.booking_items bi WHERE bi.item_id = rp.booking_item)
      AND EXISTS (SELECT 1 FROM public.booking_rates br WHERE br.booking_item = rp.booking_item)
      AND (p_from IS NULL OR rp.date_ref >= p_from)
      AND (p_to   IS NULL OR rp.date_ref <= p_to);

GET DIAGNOSTICS _rows = ROW_COUNT;
    RAISE NOTICE 'rsv_pickup.revenue_room_net: % row(s) processed', _rows;

/* ---- 5. AMD rows mirror the latest positive row, as the trigger does ---- */
    UPDATE public.rsv_pickup rp
    SET revenue_room_net = -ABS(src.revenue_room_net)
    FROM public.rsv_pickup src
    WHERE rp.revenue_room_net IS NULL
      AND rp.status = 'AMD'
      AND rp.room_qty < 0
      AND src.booking_id = rp.booking_id
      AND src.booking_item = rp.booking_item
      AND src.room_qty > 0
      AND src.revenue_room_net IS NOT NULL
      AND (p_from IS NULL OR rp.date_ref >= p_from)
      AND (p_to   IS NULL OR rp.date_ref <= p_to);

GET DIAGNOSTICS _rows = ROW_COUNT;
    RAISE NOTICE 'rsv_pickup AMD mirror: % row(s) filled', _rows;
END

sp_backfill_revenue_room_net

Description

Parameters

Definition