aonestar.public

Parameters

Name	Type	Mode
status	text	INOUT
msg	text	INOUT
details	jsonb	INOUT

Definition

DECLARE system_date date = fn_system_date();
BEGIN
    WITH non_extensions AS (
        SELECT rg.id, rm.room_number, g.full_name AS name, rg.arrival, rg.departure
        FROM registration rg LEFT JOIN room rm ON rm.id = rg.room_id
            LEFT JOIN guest g ON g.id = rg.guest_id
        WHERE rg.status = 'I' AND departure = system_date
        UNION ALL
        SELECT f.id, 'Cash Sale', cs."name", f.open_date, f.close_date
        FROM cash_sale cs INNER JOIN folio f ON f.id = cs.folio_id
        WHERE f.folio_type = 'C' AND f.status = 'A' AND f.close_date = system_date
        UNION ALL
        SELECT bk.id, 'Deposit', g.full_name, bk.arrival, bk.departure
        FROM folio f INNER JOIN booking bk ON f.booking_id = bk.id
            LEFT JOIN guest g ON g.id = bk.guest_id
        WHERE f.folio_type = 'B' AND f.status = 'A' AND f.close_date = system_date
    )
    SELECT jsonb_agg(non_extensions) FROM non_extensions INTO details;

    status := iif(details IS NULL, 'success', 'error');
    IF details IS NOT NULL THEN
        RAISE EXCEPTION SQLSTATE '90002' USING MESSAGE = 'There are non-extension guests or folios', DETAIL = details;
    END IF;
END;

sp_endday_check_non_extensions

Parameters

Definition