1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
|
select replace (NVL(ent.liste_entity, 'ENTITY')
||';'|| upper(nvl(o.cd_cat, 'cat'))
||';'|| id_annuaire
||';'|| cd_rep
||';'|| cd_trt
||';'|| b_nat_con
||';'|| decode(cd_month,'00','YEAR','01','JAN','02','FEB','03','MAR','04','APR','05','MAY','06','JUN','07','JUL','08','AUG','09','SEP','10','OCT','11','NOV','12','DEC')
||';'|| decode(b_nat_con,'1','@LEVMBRS(NATURE,0)','2','@REMOVE(@LEVMBRS(NATURE,0),(@LEVMBRS(NATURE,0) AND @DESCENDANTS(LOCALDATA)))','3','@LEVMBRS(NATURE,0)')
||';'|| (select fnt_agg_level('1326673') from dual)
||';'|| cd_cat_src
, ' ', 'SPACE') LIGNE
from
tch_action o,
rep r,
(select MAX(LTRIM(sys_connect_by_path(cd_entity,'|'),'|')) liste_entity
from (
SELECT distinct cd_entity , DENSE_RANK() over(order by cd_entity) rn
FROM entity e
where cd_rep=(select r.cd_rep from rep r where r.dt_end is null)
and cd_hierarchie = '0'
and leaf = 1
connect by prior e.cd_entity = e.ent_cd_entity
and prior cd_rep=cd_rep
and prior cd_hierarchie = '0'
start with e.cd_entity in (
select em.cd_entity from entity_multiple em
where em.num_seq ='1326673'
)
and cd_rep = (select r.cd_rep from rep r where r.dt_end is null)
and cd_hierarchie = '0'
)
connect by prior rn+1 = rn
start with rn = 1
) ent
where o.num_seq = '1326673'
and r.dt_end is null; |
Partager