v_auto_post_result

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

folio_id

int4

√

null

book_type

bpchar

2147483647

√

null

folio_type

t_folio_type

√

null

booking_id

int4

√

null

group_name

text

2147483647

√

null

room_number

t_room_number

2147483647

√

null

room_posted

bool

√

null

room_and_inclusive

numeric

√

null

other_charge

numeric

√

null

total_post

numeric

√

null

posted_items

json

2147483647

√

null

plan_room_and_inclusive

numeric

√

null

plan_other

numeric

√

null

plan_total

numeric

√

null

match

bool

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH _js AS (
         SELECT sp_get_auto_post_rooms_js() AS data
        ), plan AS (
         SELECT ((elem.value ->> 'folio_id'::text))::integer AS folio_id,
            (((((((elem.value ->> 'room_charge'::text))::numeric + ((elem.value ->> 'extrabed'::text))::numeric) + ((elem.value ->> 'abf'::text))::numeric) + ((elem.value ->> 'lunch'::text))::numeric) + ((elem.value ->> 'dinner'::text))::numeric) + ((elem.value ->> 'inclusive_charge'::text))::numeric) AS plan_r,
            ((elem.value ->> 'other_charge'::text))::numeric AS plan_a,
            ((elem.value ->> 'total'::text))::numeric AS plan_total
           FROM _js,
            LATERAL json_array_elements((_js.data -> 'Individual'::text)) elem(value)
          WHERE ((elem.value ->> 'folio_id'::text) IS NOT NULL)
        UNION ALL
         SELECT ((elem.value ->> 'folio_id'::text))::integer AS folio_id,
            (((((((elem.value ->> 'room_charge'::text))::numeric + ((elem.value ->> 'extrabed'::text))::numeric) + ((elem.value ->> 'abf'::text))::numeric) + ((elem.value ->> 'lunch'::text))::numeric) + ((elem.value ->> 'dinner'::text))::numeric) + ((elem.value ->> 'inclusive_charge'::text))::numeric) AS plan_r,
            ((elem.value ->> 'other_charge'::text))::numeric AS plan_a,
            ((elem.value ->> 'total'::text))::numeric AS plan_total
           FROM _js,
            LATERAL json_array_elements((_js.data -> 'Group'::text)) elem(value)
          WHERE ((elem.value ->> 'folio_id'::text) IS NOT NULL)
        UNION ALL
         SELECT ((m.value ->> 'folio_id'::text))::integer AS folio_id,
                CASE
                    WHEN (NOT ((m.value ->> 'charge_to_booking'::text))::boolean) THEN (((((((m.value ->> 'room_charge'::text))::numeric + ((m.value ->> 'extrabed'::text))::numeric) + ((m.value ->> 'abf'::text))::numeric) + ((m.value ->> 'lunch'::text))::numeric) + ((m.value ->> 'dinner'::text))::numeric) + ((m.value ->> 'inclusive_charge'::text))::numeric)
                    ELSE (0)::numeric
                END AS plan_r,
            ((m.value ->> 'other_charge_own'::text))::numeric AS plan_a,
            ((m.value ->> 'total'::text))::numeric AS plan_total
           FROM _js,
            LATERAL json_array_elements((_js.data -> 'Group'::text)) elem(value),
            LATERAL json_array_elements((elem.value -> 'Members'::text)) m(value)
          WHERE ((m.value ->> 'folio_id'::text) IS NOT NULL)
        ), inh AS (
         SELECT COALESCE(f.booking_id, rg.booking_id) AS booking_id,
            COALESCE(b.book_type, (iif(rg.is_group, 'G'::text, 'I'::text))::bpchar) AS book_type,
            f.id AS folio_id,
            f.folio_type,
            rm.room_number,
            COALESCE(rg.group_id, b.group_id) AS group_id,
            g.name AS group_name,
            rg.room_posted,
            f.split_room_charge AS split_charge,
            (rg.is_group OR (b.book_type = 'G'::bpchar)) AS is_group
           FROM ((((folio f
             LEFT JOIN registration rg ON ((rg.id = f.register_id)))
             LEFT JOIN room rm ON ((rm.id = rg.room_id)))
             LEFT JOIN booking b ON ((b.id = f.booking_id)))
             LEFT JOIN group_info g ON ((g.id = COALESCE(rg.group_id, b.group_id))))
          WHERE (((f.folio_type)::bpchar = ANY (ARRAY['R'::bpchar, 'G'::bpchar])) AND (f.status = 'A'::bpchar) AND (((f.folio_type)::bpchar = 'G'::bpchar) OR (rg.departure > fn_system_date()) OR (rg.arrival = rg.departure)))
        ), posted AS (
         SELECT trn.folio_id,
            sum(iif((trn.post_flag = 'R'::bpchar), (trn.amount)::numeric, (0)::numeric)) AS room_and_inclusive,
            sum(iif((trn.post_flag = 'A'::bpchar), (trn.amount)::numeric, (0)::numeric)) AS other_charge,
            sum((trn.amount)::numeric) AS total_post,
            json_agg(trn.* ORDER BY trn.id) AS posted_items
           FROM ( SELECT t.folio_id,
                    t.id,
                    t.dept_id,
                    t.description,
                    t.remark,
                    t.amount,
                    t.post_flag
                   FROM transactions t
                  WHERE ((t.folio_id IN ( SELECT inh_1.folio_id
                           FROM inh inh_1)) AND t.active AND (t.post_flag = ANY (ARRAY['R'::bpchar, 'A'::bpchar])) AND (t.post_date = fn_system_date()))) trn
          GROUP BY trn.folio_id
        )
 SELECT inh.folio_id,
    inh.book_type,
    inh.folio_type,
    inh.booking_id,
    inh.group_name,
    inh.room_number,
    inh.room_posted,
    posted.room_and_inclusive,
    posted.other_charge,
    posted.total_post,
    posted.posted_items,
    p.plan_r AS plan_room_and_inclusive,
    p.plan_a AS plan_other,
    p.plan_total,
    (abs((COALESCE(p.plan_total, (0)::numeric) - COALESCE(posted.total_post, (0)::numeric))) < 0.01) AS match
   FROM ((inh
     LEFT JOIN posted ON ((posted.folio_id = inh.folio_id)))
     LEFT JOIN plan p ON ((p.folio_id = inh.folio_id)))
  ORDER BY (((inh.book_type)::text || iif(inh.is_group, lpad((inh.booking_id)::text, 6, '0'::text), ''::text)) || lpad((COALESCE((inh.room_number)::character varying, ''::character varying))::text, 5, '0'::text));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views