create table PATH_ICG_SCG_LL_DATA
as
with path_port_data as
(
	select *
	from 
	(
		select a.*
		,count(port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr) as PORT_COUNT
		,lag(a.port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr order by ELEMENT_ORDER) as A_PORT_ID
		,a.port_inst_id as Z_PORT_ID
		from 
		(
			select cpi.*, cpe.element_inst_id, cpe.port_inst_id
			,row_number() over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr order by cpe.sequence) as ELEMENT_ORDER
			,count(1) over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr) as ELEMENT_COUNT
			from 
				(
					select * 
					from circ_path_inst@dbl.granite
					where circ_path_hum_id like 'LL%'
					and path_class = 'P'
				) cpi, 
				circ_path_element@dbl.granite cpe
			where cpi.circ_path_inst_id = cpe.circ_path_inst_id (+)
		) a
		where (ELEMENT_ORDER = 1 or ELEMENT_ORDER = ELEMENT_COUNT)
	)
	where ELEMENT_ORDER = ELEMENT_COUNT
)
select 
	ppd.*
	,aei.type as A_EQ_TYPE
	,zei.type as Z_EQ_TYPE
from 
	equip_inst@dbl.granite aei
	,epa@dbl.granite api
	,path_port_data ppd
	,epa@dbl.granite zpi
	,equip_inst@dbl.granite zei
where aei.equip_inst_id (+) = api.equip_inst_id
and api.port_inst_id (+) = ppd.A_PORT_ID
and ppd.Z_PORT_ID = zpi.port_inst_id (+)
and zpi.equip_inst_id = zei.equip_inst_id (+);

select * from PATH_ICG_SCG_LL_DATA where circ_path_inst_id = '131296';

create view PATH_ICG_SCG_LL_DATA
as
with path_port_data as
(
	select *
	from 
	(
		select a.*
		,count(port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr) as PORT_COUNT
		,lag(a.port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr order by ELEMENT_ORDER) as A_PORT_ID
		,a.port_inst_id as Z_PORT_ID
		from 
		(
			select cpi.*, cpe.element_inst_id, cpe.port_inst_id
			,row_number() over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr order by cpe.sequence) as ELEMENT_ORDER
			,count(1) over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr) as ELEMENT_COUNT
			from 
				(
					select * 
					from circ_path_inst@dbl.granite
					where circ_path_hum_id like 'LL%'
					and path_class = 'P'
				) cpi, 
				circ_path_element@dbl.granite cpe
			where cpi.circ_path_inst_id = cpe.circ_path_inst_id (+)
		) a
		where (ELEMENT_ORDER = 1 or ELEMENT_ORDER = ELEMENT_COUNT)
	)
	where ELEMENT_ORDER = ELEMENT_COUNT
)
select 
	ppd.*
	,aei.type as A_EQ_TYPE
	,zei.type as Z_EQ_TYPE
from 
	equip_inst@dbl.granite aei
	,epa@dbl.granite api
	,path_port_data ppd
	,epa@dbl.granite zpi
	,equip_inst@dbl.granite zei
where aei.equip_inst_id (+) = api.equip_inst_id
and api.port_inst_id (+) = ppd.A_PORT_ID
and ppd.Z_PORT_ID = zpi.port_inst_id (+)
and zpi.equip_inst_id = zei.equip_inst_id (+);

select * from PATH_ICG_SCG_LL_DATA where circ_path_inst_id = '131296';

,row_number() over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr order by [b]cpe.sequence[/b]) as ELEMENT_ORDER