aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_create_folio_item
Parameters
Name
Type
Mode
folio_id
integer
IN
folio_seq
integer
IN
folio_name
text
IN (DEFAULT NULL)
Definition
DECLARE _folio_item_id int; _pattern_id int; BEGIN SELECT f.folio_pattern_id, COALESCE($3, fp."name") FROM folio f left JOIN folio_pattern_seq fp ON fp.pattern_id = f.folio_pattern_id AND fp.folio_seq = $2 WHERE f.id = $1 INTO _pattern_id, $3; INSERT INTO folio_items(folio_id, folio_seq, folio_name) VALUES ($1, $2, $3) RETURNING item_id INTO _folio_item_id; IF NOT EXISTS(SELECT * FROM folio_details d WHERE d.folio_id = $1 AND d.folio_seq = $2) THEN INSERT INTO folio_details(folio_id, folio_seq, dept_id) SELECT $1, $2, d.dept_id FROM folio_pattern_details d WHERE d.pattern_id = _pattern_id AND d.folio_seq = $2; END IF; RETURN _folio_item_id; END