aonestar.public

Parameters

Name	Type	Mode
pattern_id	integer	IN (DEFAULT NULL)
only_enabled	boolean	IN (DEFAULT false)

Definition

SELECT json_agg(J0) 
	FROM (
		SELECT p.id, p.name, p.split_room_charge, p.enabled,
		
			 (SELECT json_build_object('name' ,s.name, 
			 		'departments', (SELECT json_agg(OBJ) 
						 			FROM (SELECT f.dept_id id, d.name 
						 			      FROM folio_pattern_details F 
						 			      		left join department D on D.id = F.dept_id
						 			      where F.pattern_id = S.pattern_id and F.folio_seq = S.folio_seq		
						 			      )OBJ
						 			) 
					)
		 	  FROM folio_pattern_seq S 
		 	  where S.pattern_id = P.id AND s.folio_seq = 1
			 ) AS folio1,
			 
			 (SELECT json_build_object('name' ,s.name, 
			 		'departments', (SELECT json_agg(OBJ) 
						 			from (SELECT f.dept_id id, d.name 
						 			      from folio_pattern_details F 
						 			      		left join department D on D.id = F.dept_id
						 			      where F.pattern_id = S.pattern_id and F.folio_seq = S.folio_seq		
						 			      )OBJ
						 			) 
					)
		 	  from folio_pattern_seq S 
		 	  where S.pattern_id = P.id AND s.folio_seq = 2
			 ) AS folio2,
			 
			 (SELECT json_build_object('name' ,s.name, 
			 		'departments', (select json_agg(OBJ) 
						 			from (select f.dept_id id, d.name 
						 			      from folio_pattern_details F 
						 			      		left join department D on D.id = F.dept_id
						 			      where F.pattern_id = S.pattern_id and F.folio_seq = S.folio_seq		
						 			      )OBJ
						 			) 
					)
		 	  from folio_pattern_seq S 
		 	  where S.pattern_id = P.id AND s.folio_seq = 3
			 ) AS folio3,
			 
			 (SELECT json_build_object('name' ,s.name, 
			 		'departments', (select json_agg(OBJ) 
						 			from (select f.dept_id id, d.name 
						 			      from folio_pattern_details F 
						 			      		left join department D on D.id = F.dept_id
						 			      where F.pattern_id = S.pattern_id and F.folio_seq = S.folio_seq		
						 			      )OBJ
						 			) 
					)
		 	  from folio_pattern_seq S 
		 	  where S.pattern_id = P.id AND s.folio_seq = 4
			 ) AS folio4
		from folio_pattern P 
		where ($1 is null or P.id = $1) and (not only_enabled or P.enabled)
		order by 1
	) J0

sp_get_folio_patterns_js

Parameters

Definition