aonestar.public

Parameters

Name	Type	Mode
i_rate_date	date	IN (DEFAULT NULL)
i_start_date	date	IN (DEFAULT NULL)
i_end_date	date	IN (DEFAULT NULL)
i_roomtype_id	integer	IN (DEFAULT NULL)
i_rateplan_id	integer	IN (DEFAULT NULL)
id	bigint	OUT
log_time	timestamp with time zone	OUT
log_date	date	OUT
log_type	text	OUT
user_name	text	OUT
rate_date	date	OUT
rate_id	integer	OUT
roomtype_id	integer	OUT
roomtype_code	text	OUT
rateplan_id	integer	OUT
rateplan_code	text	OUT
log_group	uuid	OUT
action_type	character	OUT
new_value	text	OUT
old_value	text	OUT
log_text	text	OUT

Definition

SELECT
        l.id,
        l.log_time,
        l.log_date,
        l.log_type,
        l.user_name,
        l.rate_date,
        l.rate_id,
        l.roomtype_id,
        rt.code::text AS roomtype_code,
        l.rateplan_id,
        rp.code::text AS rateplan_code,
        l.log_group,
        l.action_type,
        l.new_value,
        l.old_value,
        CASE
            WHEN l.log_type = 'Adjust' THEN 'Adjust rate to ' || COALESCE(l.new_value, '')
            WHEN l.log_type = 'Override' AND l.new_value IS NULL THEN 'Clear override' || COALESCE(' (' || l.old_value || ')', '')
            WHEN l.log_type = 'Override' THEN 'Override by ' || COALESCE(l.old_value || ' -> ', 'to ') || COALESCE(l.new_value, '')
            WHEN l.log_type IN ('Close Sell', 'CTA', 'CTD') THEN 'Set ' || l.log_type || ' to ' || COALESCE(l.new_value, '')
            ELSE 'Set ' || l.log_type || ' ' || COALESCE(l.old_value || ' -> ', 'to ') || COALESCE(l.new_value, '')
        END AS log_text
    FROM public.rate_logs l
        LEFT JOIN public.rate_plan rp ON rp.id = l.rateplan_id
        LEFT JOIN public.room_type rt ON rt.id = l.roomtype_id
    WHERE (i_rate_date IS NULL OR l.rate_date = i_rate_date)
      AND (i_rate_date IS NOT NULL OR i_start_date IS NULL OR l.rate_date >= i_start_date)
      AND (i_rate_date IS NOT NULL OR i_end_date IS NULL OR l.rate_date <= i_end_date)
      AND (i_roomtype_id IS NULL OR l.roomtype_id = i_roomtype_id)
      AND (i_rateplan_id IS NULL OR l.rateplan_id = i_rateplan_id)
    ORDER BY l.id DESC

sp_get_inventory_logs

Parameters

Definition