aonestar.public

Parameters

Name	Type	Mode
link_plan_id	integer	IN

Definition

WITH def AS (
        SELECT fn_sys_param('ROOM_RATE', 'INCLUDE_ADULT', 2) AS include_adult,
               fn_sys_param('ROOM_RATE', 'EXTRA_ADULT', 0.0) AS extra_adult,
               fn_sys_param('ROOM_RATE', 'EXTRA_CHILD', 0.0) AS extra_child,
               fn_sys_param('ROOM_RATE', 'EXTRA_INFANT', 0.0) AS extra_infant
    ), details AS (
        SELECT NULL AS rate_id, --link.rate_id AS link_rate_id, 
              t.id roomtype_id, t.name roomtype_name, 
              NULL AS description, NULL AS rate_amount, 
              NULL AS rate_extrabed, NULL AS rate_child, NULL AS rate_infant, 
              NULL AS include_adult, NULL AS include_child, NULL AS include_infant, 
              t.max_adult, t.max_child, t.max_infant, t.max_pax,
              IIF(COALESCE(link_plan_id,-1) = -1, t.room_rate, NULL) AS default_rate_amount, 
              COALESCE(t.extrabed_rate, def.extra_adult)  AS default_rate_extrabed, 
              COALESCE(t.child_rate  , def.extra_child)   AS default_rate_child, 
              COALESCE(t.infant_rate , def.extra_infant)  AS default_rate_infant, 
              COALESCE(t.include_adult, def.include_adult)AS default_include_adult, 
              t.include_child AS default_include_child, 
              t.include_infant AS default_include_infant,
              TRUE AS active 
        FROM room_type t 
            LEFT JOIN rate_details link ON (t.id = link.roomtype_id AND link.rateplan_id = $1), def
        WHERE t.enabled and ((COALESCE($1,-1) = -1) OR (link.rate_id IS NOT NULL AND link.active)) 
        ORDER BY t.order_seq, t.id
    )
    SELECT json_agg(details) FROM details

sp_get_rateplan_link_details_js

Parameters

Definition