aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_create_booking_folio
Parameters
Name
Type
Mode
booking_id
integer
IN
folio_pattern_id
integer
IN (DEFAULT NULL)
user_name
text
IN (DEFAULT NULL)
Definition
DECLARE _folio_id int; BEGIN _folio_id := (SELECT folio_id FROM booking WHERE id = $1); IF _folio_id IS NULL THEN INSERT INTO folio(folio_type, booking_id, folio_pattern_id, open_user, close_date) SELECT 'B', $1, COALESCE($2, b.folio_pattern_id), COALESCE($3, fn_current_user()), b.departure FROM booking b WHERE id = $1 AND folio_id IS NULL RETURNING id INTO _folio_id; --RAISE NOTICE 'New booking''s folio = %', _folio_id; UPDATE booking b SET folio_id = _folio_id WHERE b.id = $1; END IF; RETURN _folio_id; -- WITH fol AS ( -- INSERT INTO folio(folio_type, booking_id, folio_pattern_id, open_user, close_date) -- SELECT 'B', $1, COALESCE($2, b.folio_pattern_id), COALESCE($3, fn_current_user()), b.departure -- FROM booking b -- WHERE id = $1 AND folio_id IS NULL -- RETURNING id -- ) -- UPDATE booking b SET folio_id = fol.id -- FROM fol -- WHERE b.id = $1 -- RETURNING folio_id END