aonestar.public

Parameters

Name	Type	Mode
inclusion_name	text	IN
qty	integer	IN
rate	t_money	IN
multiplier	integer	IN
frequency	integer	IN (DEFAULT NULL)
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
inclusion_name	text	IN
rooms	integer	IN
adult	integer	IN
child	integer	IN
infant	integer	IN
qty	integer	IN
rate	t_money	IN
multiplier	integer	IN
frequency	integer	IN
start_date	date	IN
end_date	date	IN
inclusion_name	text	IN
rooms	integer	IN
adult	integer	IN
child	integer	IN
infant	integer	IN
qty	integer	IN
rate	t_money	IN
multiplier	integer	IN
frequency	integer	IN
start_date	date	IN
end_date	date	IN
full_format	boolean	IN (DEFAULT false)

Definition

SELECT
      CASE 
        WHEN full_format THEN    
--           coalesce(qty,1) ||' '|| inclusion_name ||' @'||rate
--           ||CASE COALESCE(multiplier,1)
--                WHEN 1 THEN ''          -- per booking
--                WHEN 2 THEN ' x '||fn_qty(rooms, 'room')   -- per room
--                WHEN 3 THEN ' x '||fn_qty(rooms, 'room')||' x '||fn_qty(adult+child, 'person') -- per person
--                WHEN 4 THEN ' x '||fn_qty(rooms, 'room')||' x '||fn_qty(adult, 'adult')  -- per adult
--                WHEN 5 THEN ' x '||fn_qty(rooms, 'room')||' x '||fn_qty(child, 'child', 'children')  -- per child
--                WHEN 6 THEN ' x '||fn_qty(rooms, 'room')||' x '||fn_qty(infant, 'infant') -- per infant
--                ELSE ''
--             END 
           (coalesce(qty,1)  
               * CASE COALESCE(multiplier,1)
                    WHEN 1 THEN 1             -- per booking
                    WHEN 2 THEN rooms         -- per room
                    WHEN 3 THEN rooms * (adult + child) -- per person
                    WHEN 4 THEN rooms * adult         -- per adult
                    WHEN 5 THEN rooms * child         -- per child
                    WHEN 6 THEN rooms * infant        -- per infant
                    ELSE 1
                 END) 
           ||' '|| inclusion_name ||' @'||rate 
           ||CASE frequency 
                WHEN 1 THEN ' x '||fn_qty(end_date - start_date, 'night')    -- Daily except arrival
                WHEN 2 THEN ' x '||fn_qty(end_date - start_date, 'night')     -- Daily except departure
                WHEN 3 THEN ''                        -- Once on arrival
                WHEN 4 THEN ''                        -- Once on departure
                WHEN 9 THEN ' x '||fn_qty(end_date -start_date +1, 'night')  -- custom period
             END
        ELSE 
           (coalesce(qty,1)  
           * CASE COALESCE(multiplier,1)
                WHEN 1 THEN 1             -- per booking
                WHEN 2 THEN rooms         -- per room
                WHEN 3 THEN rooms * (adult + child) -- per person
                WHEN 4 THEN rooms * adult         -- per adult
                WHEN 5 THEN rooms * child         -- per child
                WHEN 6 THEN rooms * infant        -- per infant
                ELSE 1
             END 
           * CASE frequency 
                WHEN 1 THEN end_date - start_date    -- Daily except arrival
                WHEN 2 THEN end_date - start_date    -- Daily except departure
                WHEN 3 THEN 1                        -- Once on arrival
                WHEN 4 THEN 1                        -- Once on departure
                WHEN 9 THEN end_date - start_date +1 -- custom period
             END
           )||' '|| inclusion_name ||' @'||rate 
      END

fn_get_inclusion_desc

Parameters

Definition