select f.CODEFAM, f.CODESFA, f.CODESSF, f.LIBFAM, decode(p.fampro, f.codefam, decode(p.sfapro, f.codesfa, decode(p.ssfpro, f.codessf, p.codpro, ' '), ' '), ' ')  
from fml f, pro p
where p.codsoc = 0 and p.codpro in ('1A00473', '1A00032', '1A00065', '1A00178')
and f.codsoc = 0 and f.typtie = 'PRO' and f.codlan = 'FRA'
and ((f.codefam = p.fampro and f.niveau = 1) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.niveau = 2) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.codessf = p.ssfpro and f.niveau = 3))  
union
select f.CODEFAM, f.CODESFA, f.CODESSF, f.LIBFAM, decode(p.fampro, f.codefam, decode(p.sfapro, f.codesfa, decode(p.ssfpro, f.codessf, p.codpro, ' '), ' '), ' ')
from fam f, pro p
where p.codsoc = 0 and p.codpro in ('1A00473', '1A00032', '1A00065', '1A00178')
and f.codsoc = 0 and f.typtie = 'PRO' and not exists (select null  
                               from fml ff  
                 where ff.codsoc = 0 and ff.typtie = 'PRO' and ff.codlan = 'FRA' and ff.codefam = f.codefam and ff.codesfa = f.codesfa and ff.codessf = f.codessf)  
and ((f.codefam = p.fampro and f.niveau = 1) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.niveau = 2) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.codessf = p.ssfpro and f.niveau = 3))
order by 1, 2, 3, 5  

CODEFAM CODESFA CODESSF LIBFAM                  CODPRO
------- ------- ------- ----------------------- -------
1APRCH                  PAPIER ECG ET EEG  
1APRCH  CHART           PAPIER ECG ET EEG  
1APRCH  CHART   ZFOLD   PLIAGE                  1A00032
1APRPR                  SUPPORT D'IMPRESSION  
1APRPR  PRINT           SUPPORT D'IMPRESSION  
1APRPR  PRINT           SUPPORT D'IMPRESSION    1A00178
1APRPR  PRINT   THERMA  PAPIER THERMIQUE        1A00065
1APRPR  PRINT   THERMA  PAPIER THERMIQUE        1A00473

CODEFAM CODESFA CODESSF LIBFAM                  CODPRO
------- ------- ------- ----------------------- -------
1APRCH                  PAPIER ECG ET EEG  
1APRCH  CHART           PAPIER ECG ET EEG  
1APRCH  CHART   ZFOLD   PLIAGE                  1A00032
1APRPR                  SUPPORT D'IMPRESSION  
1APRPR  PRINT           SUPPORT D'IMPRESSION    1A00178
1APRPR  PRINT   THERMA  PAPIER THERMIQUE        1A00065
1APRPR  PRINT   THERMA  PAPIER THERMIQUE        1A00473

select CODEFAM, CODESFA, CODESSF, LIBFAM, CODPRO
from  
(
select f.CODEFAM, f.CODESFA, f.CODESSF, f.LIBFAM, decode(p.fampro, f.codefam, decode(p.sfapro, f.codesfa, decode(p.ssfpro, f.codessf, p.codpro, ' '), ' '), ' ') CODPRO  
from fml f, pro p
where p.codsoc = 0 and p.codpro in ('1A00473', '1A00032', '1A00065', '1A00178')
and f.codsoc = 0 and f.typtie = 'PRO' and f.codlan = 'FRA'
and ((f.codefam = p.fampro and f.niveau = 1) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.niveau = 2) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.codessf = p.ssfpro and f.niveau = 3))  
union
select f.CODEFAM, f.CODESFA, f.CODESSF, f.LIBFAM, decode(p.fampro, f.codefam, decode(p.sfapro, f.codesfa, decode(p.ssfpro, f.codessf, p.codpro, ' '), ' '), ' ') CODPRO
from fam f, pro p
where p.codsoc = 0 and p.codpro in ('1A00473', '1A00032', '1A00065', '1A00178')
and f.codsoc = 0 and f.typtie = 'PRO' and not exists (select null  
                               from fml ff  
                 where ff.codsoc = 0 and ff.typtie = 'PRO' and ff.codlan = 'FRA' and ff.codefam = f.codefam and ff.codesfa = f.codesfa and ff.codessf = f.codessf)  
and ((f.codefam = p.fampro and f.niveau = 1) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.niveau = 2) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.codessf = p.ssfpro and f.niveau = 3))
) p1
where not exists (select null from (select f.CODEFAM, f.CODESFA, f.CODESSF, f.LIBFAM, decode(p.fampro, f.codefam, decode(p.sfapro, f.codesfa, decode(p.ssfpro, f.codessf, p.codpro, ' '), ' '), ' ') CODPRO  
from fml f, pro p
where p.codsoc = 0 and p.codpro in ('1A00473', '1A00032', '1A00065', '1A00178')
and f.codsoc = 0 and f.typtie = 'PRO' and f.codlan = 'FRA'
and ((f.codefam = p.fampro and f.niveau = 1) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.niveau = 2) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.codessf = p.ssfpro and f.niveau = 3))  
union
select f.CODEFAM, f.CODESFA, f.CODESSF, f.LIBFAM, decode(p.fampro, f.codefam, decode(p.sfapro, f.codesfa, decode(p.ssfpro, f.codessf, p.codpro, ' '), ' '), ' ') CODPRO
from fam f, pro p
where p.codsoc = 0 and p.codpro in ('1A00473', '1A00032', '1A00065', '1A00178')
and f.codsoc = 0 and f.typtie = 'PRO' and not exists (select null  
                               from fml ff  
                 where ff.codsoc = 0 and ff.typtie = 'PRO' and ff.codlan = 'FRA' and ff.codefam = f.codefam and ff.codesfa = f.codesfa and ff.codessf = f.codessf)  
and ((f.codefam = p.fampro and f.niveau = 1) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.niveau = 2) or (f.codefam = p.fampro and f.codesfa = p.sfapro and f.codessf = p.ssfpro and f.niveau = 3))
) p2 where p2.CODEFAM = p1.CODEFAM and p2.CODESFA = p1.CODESFA and p2.CODESSF = p1.CODESSF and p2.CODPRO != ' ' and p1.CODPRO = ' ')
order by 1, 2, 3, 5