SELECT ET.ETATEJ_CODE codeEtat ,
  ET.ETATEJ_LIB libEtat ,
  COUNT(DISTINCT E.ENGJ_CODE) nbEngj ,
  SUM(NVL(DECODE(LD.LGENGJ_MT_HT, 0, LD.LGENGJ_MT, LD.LGENGJ_MT_HT),0)) engjMtDepHT,
  SUM(NVL(DECODE(LR.LGENGJ_MT_HT, 0, LR.LGENGJ_MT, LR.LGENGJ_MT_HT),0)) engjMtRecHT,
  SUM(NVL(DECODE(LD.LGENGJ_MT_TTC, 0, LD.LGENGJ_MT, LD.LGENGJ_MT_TTC),0)) engjMtDepTTC,
  SUM(NVL(DECODE(LR.LGENGJ_MT_TTC, 0, LR.LGENGJ_MT, LR.LGENGJ_MT_TTC),0)) engjMtRecTTC
FROM ENGJ E ,
  ETATEJ ET ,
  LGENGJ LD ,
  LGENGJ LR ,
  CDR_UTILIS CU ,
  CDR C
WHERE E.ETATEJ_CODE    = ET.ETATEJ_CODE
AND E.ENGJ_CODE      = LD.ENGJ_CODE (+)
AND E.ENGJ_CODE      = LR.ENGJ_CODE (+)
AND LD.LGENGJ_NAT(+) = 'D'
AND LR.LGENGJ_NAT(+) = 'R'
AND E.ETATEJ_CODE   IN ('B', 'C', 'E', 'P', 'N', 'A', 'Z')
AND E.CDR_CODE       = CU.CDR_CODE
AND E.CDR_CODE       = C.CDR_CODE
AND CU.UTILIS_CODE   = '1'
AND C.ENTFIN_CODE    = '1'
GROUP BY ET.ETATEJ_CODE,
  ET.ETATEJ_LIB
ORDER BY ET.ETATEJ_LIB;

SELECT ET.ETATEJ_CODE codeEtat ,
  ET.ETATEJ_LIB libEtat ,
  COUNT(DISTINCT E.ENGJ_CODE) nbEngj ,
  NVL(SUM(NVL(LD.LGENGJ_MT_HT,0)),0) engjMtDepHT ,
  NVL(SUM(NVL(LR.LGENGJ_MT_HT,0)),0) engjMtRecHT ,
  NVL(SUM(NVL(LD.LGENGJ_MT_TTC,0)),0) engjMtDepTTC ,
  NVL(SUM(NVL(LR.LGENGJ_MT_TTC,0)),0) engjMtRecTTC
FROM ENGJ E ,
  ETATEJ ET ,
  LGENGJ LD ,
  LGENGJ LR ,
  CDR_UTILIS CU ,
  CDR C
WHERE E.ETATEJ_CODE    = ET.ETATEJ_CODE
AND E.ENGJ_CODE      = LD.ENGJ_CODE (+)
AND E.ENGJ_CODE      = LR.ENGJ_CODE (+)
AND LD.LGENGJ_NAT(+) = 'D'
AND LR.LGENGJ_NAT(+) = 'R'
AND E.ETATEJ_CODE   IN ('B', 'C', 'E', 'P', 'N', 'A', 'Z')
AND E.CDR_CODE       = CU.CDR_CODE
AND E.CDR_CODE       = C.CDR_CODE
AND CU.UTILIS_CODE   = '1'
AND C.ENTFIN_CODE    = '1'
GROUP BY ET.ETATEJ_CODE,
  ET.ETATEJ_LIB
ORDER BY ET.ETATEJ_LIB;