aonestar.public

Parameters

Name	Type	Mode
issue	text	OUT
booking_id	integer	OUT
booking_item	integer	OUT
rp_status	text	OUT
date_ref_current	date	OUT
date_ref_calc	date	OUT
arrival	date	OUT
departure	date	OUT
room_charge_rp	t_money	OUT
room_charge_calc	t_money	OUT

Definition

WITH
calc AS (
    SELECT * FROM sp_calc_rsv_pickup()
),
known_ids AS (
    SELECT id FROM booking
    UNION
    SELECT id FROM backup.booking
),
missing AS (
    SELECT
        'MISSING'::text  AS issue,
        c.booking_id,
        c.booking_item,
        c.status,
        NULL::date       AS date_ref_current,
        c.date_ref       AS date_ref_calc,
        c.arrival,
        c.departure,
        NULL::t_money    AS room_charge_rp,
        c.room_charge    AS room_charge_calc
    FROM calc c
    WHERE NOT EXISTS (
        SELECT 1 FROM rsv_pickup rp
        WHERE rp.booking_item = c.booking_item AND rp.status = c.status
    )
),
date_ref_mismatch AS (
    SELECT
        'DATE_REF'::text AS issue,
        c.booking_id,
        c.booking_item,
        c.status,
        rp.date_ref      AS date_ref_current,
        c.date_ref       AS date_ref_calc,
        c.arrival,
        c.departure,
        rp.room_charge   AS room_charge_rp,
        c.room_charge    AS room_charge_calc
    FROM calc c
    JOIN rsv_pickup rp ON rp.booking_item = c.booking_item AND rp.status = c.status
    WHERE rp.date_ref <> c.date_ref
),
extra AS (
    SELECT
        CASE WHEN ki.id IS NULL THEN 'EXTRA_PURGED' ELSE 'EXTRA' END::text AS issue,
        rp.booking_id,
        rp.booking_item,
        rp.status,
        rp.date_ref      AS date_ref_current,
        NULL::date       AS date_ref_calc,
        rp.arrival,
        rp.departure,
        rp.room_charge   AS room_charge_rp,
        NULL::t_money    AS room_charge_calc
    FROM rsv_pickup rp
    LEFT JOIN known_ids ki ON ki.id = rp.booking_id
    WHERE rp.status <> 'AMD'
      AND NOT EXISTS (
        SELECT 1 FROM calc c
        WHERE c.booking_item = rp.booking_item AND c.status = rp.status
    )
)
SELECT * FROM missing
UNION ALL
SELECT * FROM date_ref_mismatch
UNION ALL
SELECT * FROM extra
ORDER BY issue, booking_id, booking_item, status

sp_verify_rsv_pickup

Parameters

Definition