aonestar.public

Parameters

Name	Type	Mode
rateplan_id	integer	IN (DEFAULT NULL)

Definition

declare result_data json;
begin
	
	Select json_agg(R)
	from (
		select p.id, p.code, p.name, p.plan_type, p.start_date, p.end_date, p.description, p.link_plan_id, 
			p.link_adjust_type, p.link_adjust_amt, p.origin_id, o."name" origin_name, p.market_id, m."name" market_name, 
			p.min_stay, p.max_stay, p.min_adv_book, p.max_adv_book, p.rate_extrabed, p.rate_child, p.rate_infant,
			json_build_object('sun',day_sun,'mon',day_mon,'tue',day_tue,'wed',day_wed,'thu',day_thu,'fri',day_fri,'sat',day_sat) days, 
			p.enabled, 
			-- Inclusions
			(select json_agg(Z) 
			 from (select i.id, i."name", r.charge_rate, r.charge_dept--, (r.rateplan_id is not null) active 
				   from inclusion i right join rate_inclusion r on i.id = r.inclusion_id and r.rateplan_id = p.id
				   where i.frequency in (1,2)
			 	   order by i.id) as Z
			) as inclusions,
			-- Rate Details
			(select json_agg(Z) 
			 from (select t.id roomtype_id, t.name roomtype_name, d.description, 
			 			  rate_id, rate_amount, rate_extrabed, rate_child, rate_infant, 
			 			  (select count(*) from rate_channels c where c.rate_id = d.rate_id) channel_count,
						  (rate_id is not null) active 
			       from room_type t left join rate_details d on t.id = d.roomtype_id and d.rateplan_id = p.id
			       order by t.order_seq, t.id) as Z	
			) as details	
		from rate_plan p 
			left join origin o on o.id = p.origin_id
			left join market m on m.id = p.market_id 
		where $1 is null or p.id = $1	
		order by p.order_seq, p.id
	) R	
	into result_data;
    return result_data;
end

sp_get_rate_plans

Parameters

Definition