aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_check_folio
Parameters
Name
Type
Mode
folio_id
integer
IN
Definition
DECLARE _register_status char(1); _folio_status char(1); _folio_type char(1); _folio_locked bool; _booking_status char(1); messages jsonb = $${ "30111" : "Guest already checked out", "30114" : "Check-in is already cancelled", "30115" : "Booking status is not active", "30106" : "Folio %s is already closed", "30113" : "Folio locked", "30116" : "Folio not found" }$$; BEGIN SELECT rg.status, f.status, f.folio_locked, f.folio_type, bk.status FROM folio f LEFT JOIN registration rg ON rg.id = f.register_id LEFT JOIN booking bk ON bk.id = f.booking_id WHERE f.id = $1 INTO _register_status, _folio_status, _folio_locked, _folio_type, _booking_status; CASE WHEN (_folio_type IS NULL) THEN RAISE EXCEPTION SQLSTATE '99999' USING detail = fn_result_error('30116', messages->>'30116'); -- Folio not found WHEN (_folio_type = 'R' and _register_status = 'O') then RAISE EXCEPTION SQLSTATE '99999' USING detail = fn_result_error('30111', messages->>'30111'); -- guest already checked out WHEN (_folio_type = 'R' and _register_status = 'X') then RAISE EXCEPTION SQLSTATE '99999' USING detail = fn_result_error('30114', messages->>'30114'); -- Check-in is already cancelled WHEN (_folio_type = 'B' and _booking_status = 'O') then RAISE EXCEPTION SQLSTATE '99999' USING detail = fn_result_error('30115', messages->>'30115'); -- Booking status is not active WHEN (_folio_status = 'C') THEN RAISE EXCEPTION SQLSTATE '99999' USING detail = fn_result_error('30106', messages->>'30106', folio_id::text); -- Folio already closed WHEN (_folio_locked) THEN RAISE EXCEPTION SQLSTATE '99999' USING detail = fn_result_error('30113', messages->>'30113'); -- Folio is locked ELSE END CASE; END