v_rate_details

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

rate_id

int4

√

null

rateplan_id

int4

√

null

roomtype_id

int4

√

null

origin_id

int4

√

null

market_id

int4

√

null

link_plan_id

int4

√

null

link_path

_int4

√

null

name

text

2147483647

√

null

description

text

2147483647

√

null

link_description

text

2147483647

√

null

order_seq

int4

√

null

editable

bool

√

null

enabled

bool

√

null

no_amend

bool

√

null

no_cancel

bool

√

null

no_refund

bool

√

null

rate_amount

t_money

15,2

√

null

rate_extra_adult

t_money

15,2

√

null

rate_extra_child

t_money

15,2

√

null

rate_extra_infant

t_money

15,2

√

null

base_rate_amt

t_money

15,2

√

null

affected_days

_bool

√

null

include_adult

int2

√

null

include_child

int2

√

null

include_infant

int2

√

null

max_adult

int2

√

null

max_child

int2

√

null

max_infant

int2

√

null

max_pax

int2

√

null

min_stay

int2

√

null

max_stay

int2

√

null

min_adv_book

int2

√

null

max_adv_book

int2

√

null

book_start_date

date

√

null

book_end_date

date

√

null

stay_start_date

date

√

null

stay_end_date

date

√

null

room_charge_dept

int4

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH RECURSIVE plan AS (
         SELECT p.id AS rateplan_id,
            d.rate_id,
            d.roomtype_id,
            p.link_plan_id,
            NULL::integer[] AS link_path,
            true AS editable,
            p.order_seq,
            p.enabled,
            p.name,
            (p.name || COALESCE(((' ('::text || NULLIF(d.description, ''::text)) || ')'::text), ''::text)) AS description,
            (iif((p.plan_type = 1), (COALESCE(d.rate_amount, rt_1.room_rate))::numeric, 0.0))::t_money AS rate_amount,
            COALESCE(d.rate_extrabed, p.rate_extrabed) AS rate_extra_adult,
            COALESCE(d.rate_child, p.rate_child) AS rate_extra_child,
            COALESCE(d.rate_infant, p.rate_infant) AS rate_extra_infant,
            (NULL::numeric)::t_money AS base_rate_amt,
            ''::text AS link_description,
            p.days AS affected_days,
            p.origin_id,
            p.market_id,
            p.no_amend,
            p.no_cancel,
            p.no_refund,
            d.include_adult,
            d.include_child,
            d.include_infant,
            d.max_adult,
            d.max_child,
            d.max_infant,
            d.max_pax,
            p.min_stay,
            p.max_stay,
            p.min_adv_book,
            p.max_adv_book,
            p.book_start_date,
            p.book_end_date,
            p.stay_start_date,
            p.stay_end_date,
            p.room_charge_dept
           FROM ((rate_plan p
             LEFT JOIN rate_details d ON ((d.rateplan_id = p.id)))
             LEFT JOIN room_type rt_1 ON ((rt_1.id = d.roomtype_id)))
          WHERE (p.link_plan_id IS NULL)
        UNION
         SELECT d.rateplan_id,
            d.rate_id,
            d.roomtype_id,
            p.link_plan_id,
            (link.link_path || p.link_plan_id) AS link_path,
            false AS editable,
            p.order_seq,
            p.enabled,
            p.name,
            (p.name || COALESCE(((' ('::text || NULLIF(d.description, ''::text)) || ')'::text), ''::text)),
            fn_calc_link_rate(link.rate_amount, (p.link_adjust_type)::integer, (p.link_adjust_percent)::t_percent, p.link_adjust_amt) AS rate_amount,
            COALESCE(d.rate_extrabed, p.rate_extrabed) AS rate_extra_adult,
            COALESCE(d.rate_child, p.rate_child) AS rate_extra_child,
            COALESCE(d.rate_infant, p.rate_infant) AS rate_extra_infant,
            link.rate_amount AS base_rate_amt,
            (((('derived from '::text || link.name) || ' ('::text) ||
                CASE p.link_adjust_type
                    WHEN 1 THEN 'same rate'::text
                    WHEN 2 THEN ((iif(((p.link_adjust_percent)::numeric < 0.0), '-'::text, '+'::text) || abs((p.link_adjust_percent)::numeric)) || '%'::text)
                    WHEN 3 THEN (iif(((p.link_adjust_percent)::numeric < 0.0), '-'::text, '+'::text) || abs((p.link_adjust_amt)::numeric))
                    WHEN 5 THEN (((iif(((p.link_adjust_percent)::numeric < 0.0), '-'::text, '+'::text) || abs((p.link_adjust_percent)::numeric)) || '% and '::text) || abs((p.link_adjust_amt)::numeric))
                    WHEN 4 THEN ((((iif(((p.link_adjust_percent)::numeric < 0.0), '-'::text, '+'::text) || abs((p.link_adjust_amt)::numeric)) || ' and '::text) || abs((p.link_adjust_percent)::numeric)) || '%'::text)
                    ELSE NULL::text
                END) || ')'::text) AS link_description,
            p.days AS affected_days,
            p.origin_id,
            p.market_id,
            p.no_amend,
            p.no_cancel,
            p.no_refund,
            d.include_adult,
            d.include_child,
            d.include_infant,
            d.max_adult,
            d.max_child,
            d.max_infant,
            d.max_pax,
            p.min_stay,
            p.max_stay,
            p.min_adv_book,
            p.max_adv_book,
            p.book_start_date,
            p.book_end_date,
            p.stay_start_date,
            p.stay_end_date,
            p.room_charge_dept
           FROM ((rate_details d
             LEFT JOIN rate_plan p ON ((d.rateplan_id = p.id)))
             JOIN plan link ON (((link.rateplan_id = p.link_plan_id) AND (link.roomtype_id = d.roomtype_id))))
          WHERE (p.link_plan_id IS NOT NULL)
        )
 SELECT plan.rate_id,
    plan.rateplan_id,
    plan.roomtype_id,
    plan.origin_id,
    plan.market_id,
    plan.link_plan_id,
    plan.link_path,
    plan.name,
    plan.description,
    plan.link_description,
    plan.order_seq,
    plan.editable,
    plan.enabled,
    plan.no_amend,
    plan.no_cancel,
    plan.no_refund,
    plan.rate_amount,
    (COALESCE((plan.rate_extra_adult)::numeric, (rt.extrabed_rate)::numeric, (def.extra_adult)::numeric, 0.0))::t_money AS rate_extra_adult,
    (COALESCE((plan.rate_extra_child)::numeric, (rt.child_rate)::numeric, (def.extra_child)::numeric, 0.0))::t_money AS rate_extra_child,
    (COALESCE((plan.rate_extra_infant)::numeric, (rt.infant_rate)::numeric, (def.extra_infant)::numeric, 0.0))::t_money AS rate_extra_infant,
    plan.base_rate_amt,
    plan.affected_days,
    (COALESCE((NULLIF(plan.include_adult, 0))::integer, def.include_adult))::smallint AS include_adult,
    NULLIF(plan.include_child, 0) AS include_child,
    NULLIF(plan.include_infant, 0) AS include_infant,
    rt.max_adult,
    rt.max_child,
    rt.max_infant,
    rt.max_pax,
    plan.min_stay,
    plan.max_stay,
    plan.min_adv_book,
    plan.max_adv_book,
    plan.book_start_date,
    plan.book_end_date,
    plan.stay_start_date,
    plan.stay_end_date,
    plan.room_charge_dept
   FROM (plan
     LEFT JOIN room_type rt ON ((rt.id = plan.roomtype_id))),
    LATERAL ( SELECT fn_sys_param('ROOM_RATE'::text, 'INCLUDE_ADULT'::text, 2) AS include_adult,
            (fn_sys_param('ROOM_RATE'::text, 'EXTRA_ADULT'::text, 0.0))::t_money AS extra_adult,
            (fn_sys_param('ROOM_RATE'::text, 'EXTRA_CHILD'::text, 0.0))::t_money AS extra_child,
            (fn_sys_param('ROOM_RATE'::text, 'EXTRA_INFANT'::text, 0.0))::t_money AS extra_infant) def;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views