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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
| SELECT DISTINCT
RDWH_EDS_AGNT_POST_FONC2.REFR_EXTR_AGNT,
count(distinct DCLI_PROFIL_PERS_TOT.NUMR_PERS),
DCLI_PORTEFEUILLE.IDNT_PORTF_COML,
RDWH1_EDS_HIERARCHIE21.IDNT_EXTR_EDS_NIV4,
RDWH1_EDS_HIERARCHIE21.DESG_COUR_EDS_NIV4,
RDWH1_EDS_HIERARCHIE21.IDNT_EXTR_EDS_NIV7,
RDWH1_EDS_HIERARCHIE21.DESG_COUR_EDS_NIV7,
trim(RDWH_CODE_CIVILITE2a.LIBL_CIVL)||' '|| nvl(RDWH_EDS_AGNT_POST_FONC2.NOM_MART,RDWH_EDS_AGNT_POST_FONC2.NOM_PATR)||' '|| RDWH_EDS_AGNT_POST_FONC2.PRNM,
RDWH_EDS_AGNT_POST_FONC2.POST_FONC_AFFC,
Table__15335938.LIBL_POST_FONC,
DCLI_PORTEFEUILLE.LIBL_PORTF_COML
FROM
DCLI_ADRESSES,
DCLI_ADRESSES DCLI_ADRESSES_CORR,
DCLI_ADRESSES DCLI_ADRESSES_FISC,
DCLI_ADRESSES DCLI_ADRESSES_FISC_PRO,
DCLI_ADRESSES DCLI_ADRESSES_CORR_PRO,
DCLI.DCLI_PROFIL_PARTIC,
DCLI_SYNT_PARTIC,
DCLI.DCLI_PERS_DME,
DCLI_DME,
DCLI_LIEU_ACTIV,
RDWH.RDWH_EDS_AGNT_POST_FONC RDWH_EDS_AGNT_POST_FONC2,
RDWH.RDWH_EDS_HIERARCHIE RDWH1_EDS_HIERARCHIE21,
RDWH_EDS_AGENCE,
DCLI.DCLI_PROFIL_PROF,
DCLI_PROFIL_PERS_TOT,
DCLI_PORTEFEUILLE,
RDWH.RDWH_CODE_TYPE_POST_FONC Table__15335938,
RDWH_CODE_CIVILITE RDWH_CODE_CIVILITE2a
WHERE
( DCLI.DCLI_PROFIL_PARTIC.IDNT_ADRS_COMM=DCLI_ADRESSES.IDNT_ADRS(+) and DCLI.DCLI_PROFIL_PARTIC.CODE_ORGN_FINN=DCLI_ADRESSES.CODE_ORGN_FINN(+) )
AND ( DCLI.DCLI_PROFIL_PARTIC.IDNT_ADRS_CORR=DCLI_ADRESSES_CORR.IDNT_ADRS(+) and DCLI.DCLI_PROFIL_PARTIC.CODE_ORGN_FINN=DCLI_ADRESSES_CORR.CODE_ORGN_FINN(+) )
AND ( DCLI.DCLI_PROFIL_PARTIC.IDNT_ADRS_LEGL_FISC=DCLI_ADRESSES_FISC.IDNT_ADRS(+) and DCLI.DCLI_PROFIL_PARTIC.CODE_ORGN_FINN=DCLI_ADRESSES_FISC.CODE_ORGN_FINN(+) )
AND ( DCLI_LIEU_ACTIV.NUMR_PERS(+)=DCLI.DCLI_PROFIL_PROF.NUMR_PERS and DCLI_LIEU_ACTIV.CODE_ORGN_FINN(+)=DCLI.DCLI_PROFIL_PROF.CODE_ORGN_FINN )
AND ( DCLI_LIEU_ACTIV.NUMR_CHRN_PRFS(+) =DCLI.DCLI_PROFIL_PROF.NUMR_CHRN_PRFS and DCLI_LIEU_ACTIV.CODE_ORGN_FINN(+)=DCLI.DCLI_PROFIL_PROF.CODE_ORGN_FINN )
AND ( DCLI_PROFIL_PERS_TOT.NUMR_PERS=DCLI.DCLI_PROFIL_PARTIC.NUMR_PERS(+) and DCLI_PROFIL_PERS_TOT.CODE_ORGN_FINN=DCLI.DCLI_PROFIL_PARTIC.CODE_ORGN_FINN(+) )
AND ( DCLI_ADRESSES_FISC_PRO.IDNT_ADRS(+)=DCLI_LIEU_ACTIV.IDNT_ADRS_LEGL_FISC and DCLI_ADRESSES_FISC_PRO.CODE_ORGN_FINN(+)=DCLI_LIEU_ACTIV.CODE_ORGN_FINN )
AND ( DCLI_LIEU_ACTIV.IDNT_ADRS_CORR=DCLI_ADRESSES_CORR_PRO.IDNT_ADRS(+) and DCLI_LIEU_ACTIV.CODE_ORGN_FINN=DCLI_ADRESSES_CORR_PRO.CODE_ORGN_FINN(+) )
AND ( DCLI_SYNT_PARTIC.NUMR_PERS=DCLI.DCLI_PROFIL_PARTIC.NUMR_PERS and DCLI_SYNT_PARTIC.CODE_ORGN_FINN=DCLI.DCLI_PROFIL_PARTIC.CODE_ORGN_FINN )
AND ( DCLI_PORTEFEUILLE.IDNT_PORTF_COML(+)=DCLI_DME.IDNT_PORTF_COML and DCLI_PORTEFEUILLE.CODE_ORGN_FINN(+)=DCLI_DME.CODE_ORGN_FINN )
AND ( RDWH_EDS_AGNT_POST_FONC2.REFR_INTE_POST_FONC(+)=DCLI_PORTEFEUILLE.EDS_INTR_SUIV_PORTF
AND RDWH_EDS_AGNT_POST_FONC2.DATE_DEBT_VALD_REFR(+) <= sysdate
AND RDWH_EDS_AGNT_POST_FONC2.DATE_FIN_VALD_REFR(+) >= sysdate and RDWH_EDS_AGNT_POST_FONC2.CODE_ORGN_FINN(+)=DCLI_PORTEFEUILLE.CODE_ORGN_FINN )
AND ( Table__15335938.TYPE_FONC_POST_FONC(+)=RDWH_EDS_AGNT_POST_FONC2.TYPE_FONC_POST_FONC and Table__15335938.CODE_ORGN_FINN(+)=RDWH_EDS_AGNT_POST_FONC2.CODE_ORGN_FINN )
AND ( DCLI_DME.NUMR_DOSS_ECNM(+)=DCLI.DCLI_PERS_DME.NUMR_DOSS_ECNM and
DCLI_DME.CODE_ORGN_FINN(+)=DCLI.DCLI_PERS_DME.CODE_ORGN_FINN )
AND ( DCLI.DCLI_PERS_DME.NUMR_PERS(+)=DCLI_PROFIL_PERS_TOT.NUMR_PERS and
DCLI.DCLI_PERS_DME.CODE_ORGN_FINN(+)=DCLI_PROFIL_PERS_TOT.CODE_ORGN_FINN )
AND ( DCLI_PROFIL_PERS_TOT.EDS_EXTR_PERS=RDWH_EDS_AGENCE.IDNT_EXTR_EDS(+) and DCLI_PROFIL_PERS_TOT.TYPE_EDS_EXTR_PERS=RDWH_EDS_AGENCE.TYPE_ELMT_STRC(+) and
DCLI_PROFIL_PERS_TOT.CODE_ORGN_FINN=RDWH_EDS_AGENCE.CODE_ORGN_FINN(+) )
AND ( RDWH_CODE_CIVILITE2a.CODE_CIVL(+)=RDWH_EDS_AGNT_POST_FONC2.CODE_CIVL )
AND ( RDWH1_EDS_HIERARCHIE21.IDNT_INTR_EDS(+)=RDWH_EDS_AGENCE.IDNT_INTR_EDS AND RDWH1_EDS_HIERARCHIE21.TYPE_ORGN(+) = '002' and
RDWH1_EDS_HIERARCHIE21.CODE_ORGN_FINN(+)=RDWH_EDS_AGENCE.CODE_ORGN_FINN )
AND ( RDWH_EDS_AGENCE.TYPE_ELMT_STRC=RDWH1_EDS_HIERARCHIE21.TYPE_ELMT_STRC(+) and
RDWH_EDS_AGENCE.CODE_ORGN_FINN=RDWH1_EDS_HIERARCHIE21.CODE_ORGN_FINN(+) )
AND ( DCLI.DCLI_PROFIL_PROF.NUMR_PERS(+)=DCLI_PROFIL_PERS_TOT.NUMR_PERS and DCLI.DCLI_PROFIL_PROF.CODE_ORGN_FINN(+)=DCLI_PROFIL_PERS_TOT.CODE_ORGN_FINN )
AND (
DCLI_PROFIL_PERS_TOT.CODE_ETAT_PERS = 1
AND CASE WHEN ( DCLI_SYNT_PARTIC.CODE_FAML_OPPS_COUR_PLUS_ELV = 43 ) THEN 1 ELSE 0 END
= 0
AND (DCLI.DCLI_PROFIL_PARTIC.NUMR_TELP_CORR_CLNT LIKE '06%'
OR DCLI.DCLI_PROFIL_PARTIC.NUMR_TELP_TRVL LIKE '06%'
OR DCLI.DCLI_PROFIL_PARTIC.ADRS_E_MAIL_CLNT LIKE '06%'
OR DCLI_ADRESSES_FISC.NUMR_TELP_DOMC LIKE '06%'
OR DCLI_ADRESSES_FISC_PRO.NUMR_TELP_DOMC LIKE '06%'
OR DCLI_ADRESSES_CORR_PRO.NUMR_TELP_DOMC LIKE '06%'
OR DCLI.DCLI_PROFIL_PARTIC.NUMR_TELP_CORR_CLNT LIKE '07%'
OR DCLI.DCLI_PROFIL_PARTIC.NUMR_TELP_TRVL LIKE '07%'
OR DCLI_ADRESSES_CORR.NUMR_TELP_DOMC LIKE '07%'
OR DCLI_ADRESSES_FISC.NUMR_TELP_DOMC LIKE '07%'
OR DCLI_ADRESSES.NUMR_TELP_DOMC LIKE '07%')
)
GROUP BY
RDWH_EDS_AGNT_POST_FONC2.REFR_EXTR_AGNT,
DCLI_PORTEFEUILLE.IDNT_PORTF_COML,
RDWH1_EDS_HIERARCHIE21.IDNT_EXTR_EDS_NIV4,
RDWH1_EDS_HIERARCHIE21.DESG_COUR_EDS_NIV4,
RDWH1_EDS_HIERARCHIE21.IDNT_EXTR_EDS_NIV7,
RDWH1_EDS_HIERARCHIE21.DESG_COUR_EDS_NIV7,
trim(RDWH_CODE_CIVILITE2a.LIBL_CIVL)||' '|| nvl(RDWH_EDS_AGNT_POST_FONC2.NOM_MART,RDWH_EDS_AGNT_POST_FONC2.NOM_PATR)||' '|| RDWH_EDS_AGNT_POST_FONC2.PRNM,
RDWH_EDS_AGNT_POST_FONC2.POST_FONC_AFFC,
Table__15335938.LIBL_POST_FONC,
DCLI_PORTEFEUILLE.LIBL_PORTF_COML |
Partager