with recursive QW AS ( 
with tabledata as (
SELECT u.sn_index, 
(CASE
WHEN coalesce(100,0)=0 then u.hi_sn_index
ELSE
(CASE WHEN u.sn_type=2 THEN null 
ELSE u.hi_sn_index 
END)
END) as hi_sn_index,
u.name,
u.nn,
a.cl_rezult
from
		(SELECT sum(coalesce(a.cl_rezult,0)) as cl_rezult, 
						         u.sn_index
		from
		((with recursive fg_tree as (
				select sn_index, 
				sn_index as objindex 
				from n.usn
				where coalesce(d,0) = 0 
				union all
				select c.sn_index, 
					p.objindex
				from n.usn as c 
				join fg_tree p
				on p.sn_index = hi_sn_index 
		) 
		select *from fg_tree )as u
		LEFT JOIN(select c.cl_objindex,sum(round(cast(c.cl_rezult as numeric),3)) as cl_rezult
		from d.dc_calculate as c, D.dc_typecalculate as t
		where coalesce(Cl_validate,0) =1 and t.tc_index = c.tc_index
		and c.cl_date = timestamp '2015-08-25' and c.ta_index =1 and c.tp_index
		and ((c.lpu_index = 100 ) or (coalesce(100,0) =0)) --iLPU_INDEX = 100
		group by  cl_objindex) as a ON (u.objindex =a.cl_objindex))
		

	group by u.sn_index) as A, n.usn as u
	where a.sn_index = u.sn_index and coalesce(u.d,0)=0
	and ((u.lpu_index = 100 ) or (coalesce(100,0) =0)) 
	
union all

	select t.tc_property*10000000000000+c.cl_objindex ,c.cl_objindex, g.gp_name,g.gp_nn,
          sum(round(coalesce(cast (cl_rezult as numeric),0),3))  as cl_rezult
	from d.dc_calculate as c, D.dc_typecalculate as t , n.gts_property as g
        where coalesce(Cl_validate,0) =1 and t.tc_index = c.tc_index and g.gp_index = t.tc_property
          and c.cl_date = timestamp '2015-08-25'
          and c.ta_index =1 and c.tp_index =20
        and ((c.lpu_index = 100 ) or (coalesce(100,0) =0))
         group by  cl_objindex,t.tc_property, g.gp_name,g.gp_nn
union all
select 2000000000000000*c.tc_index+t.tc_property*10000000000+c.cl_objindex,t.tc_property*10000000000000+c.cl_objindex  , t.tc_name, tc_nn
     , sum(round(coalesce(cast(cl_rezult as numeric),0),3)) as cl_rezult
from d.dc_calculate as c, D.dc_typecalculate as t
        where coalesce(Cl_validate,0) =1 and t.tc_index = c.tc_index
       and c.cl_date = timestamp '2015-08-25'
        and  0 >=1 and c.ta_index =1 and c.tp_index =20
        and ((c.lpu_index = 100 ) or (coalesce(100,0) =0))
         group by  cl_objindex,c.tc_index ,t.tc_property,t.tc_name, tc_nn)
(select  				1 as level,
                                        sn_index, 
	                                hi_sn_index,
	                                WWW_REPORT.html_table(WWW_REPORT.html_trtd(to_char(cl_rezult,'9999999999999990.999'))) as q,
	                                ARRAY[nn] as path

from tabledata
where cl_rezult <>0 and coalesce(hi_sn_index::text, '') = ''
order by nn)

UNION ALL 

(select qw.level+1 as level,
        Z.sn_index, 
	Z.hi_sn_index,
	WWW_REPORT.html_table(WWW_REPORT.html_trtd(to_char(Z.cl_rezult,'9999999999999990.999'))) as q,
	qw.path||z.nn
from tabledata as Z
JOIN QW ON QW.sn_index = Z.hi_sn_index
ORDER BY NN))

select row_number () over () as N_num, f.*
from (select WWW_REPORT.html_space((level-1)*5)||name as name,
	     qw.*
from qw, n.usn
order by path) as f;


END;

$BODY$
LANGUAGE plpgsql;