WITH ate
        AS (SELECT code_un
              FROM p06_atenei
             WHERE ateneo_id = esse3.f_get_ate_id_parconf)
SELECT ROWNUM progressivo_quest,
       ate.code_un codice_ateneo,
       a.cognome,
       a.nome,
       a.cod_fis codice_fiscale,
       decode(a.sesso, 'M',1, 2) sesso,
       TO_CHAR (a.data_nascita, 'YYYY') data_nascita_anno,
       decode(a.citt1_cod, 200, 100, a.citt1_cod) cittadinanza_individuo,
       nvl(decode(c.cod_stat_miur, '0', null, c.cod_stat_miur), c.cod) cod_tit_dott,
       c.des corso_dottorato,
       c.perga_tipo_tit_des specifica_titolo,
       aree.cod cod_area_1,
       NULL cod_area_2,
       TO_CHAR (d.data_ct, 'DD/MM/YYYY') data_conseguimento,
       o.num_ciclo ciclo,
       o.num_ciclo specifica_ciclo,
       DECODE (nazi_res.cod, '200', 1, 2) ind_res_ita_est,
       DECODE (nazi_res.cod, '200', a.via_res || ', ' || a.num_civ_res, NULL)
          ind_res_ita,
       DECODE (nazi_res.cod, '200', a.cap_res, NULL) cap_res_ita,
       DECODE (nazi_res.cod, '200', substr(comu_res.cod_istat, 1, 3), NULL) prov_res_ita,
       DECODE (nazi_res.cod, '200', substr(comu_res.cod_istat, 4, 3), NULL) com_res_ita,
       DECODE (nazi_res.cod, '200', NULL, a.via_res || ', ' || a.num_civ_res)
          ind_res_est,
       DECODE (nazi_res.cod, '200', NULL, a.citstra_res) citta_res_est,
       DECODE (nazi_res.cod, '200', NULL, nazi_res.cod) stato_res_est,
       DECODE (nazi_dom.cod, '200', 1, 2) ind_dom_ita_est,
       DECODE (nazi_dom.cod, '200', a.via_dom || ', ' || a.num_civ_dom, NULL)
          ind_dom_ita,
       DECODE (nazi_dom.cod, '200', a.cap_dom, NULL) cap_dom_ita,
       DECODE (nazi_dom.cod, '200', substr(comu_dom.cod_istat, 1, 3), NULL) prov_dom_ita,
       DECODE (nazi_dom.cod, '200', substr(comu_dom.cod_istat, 4, 3), NULL) com_dom_ita,
       DECODE (nazi_dom.cod, '200', NULL, a.via_dom || ', ' || a.num_civ_dom)
          ind_dom_est,
       DECODE (nazi_dom.cod, '200', NULL, a.citstra_dom) citta_dom_est,
       DECODE (nazi_dom.cod, '200', NULL, nazi_dom.cod) stato_dom_est,
       a.cellulare tel_1,
       a.tel_dom tel_2,
       a.tel_res tel_3,
       a.email_ate email_1,
       a.email email_2,
       a.email_certificata email_3
  FROM p04_mat m
       JOIN p01_stu s ON m.stu_id = s.stu_id
       JOIN p12_dom_ct d ON d.stu_id = s.stu_id
       JOIN p06_cds c ON m.cds_id = c.cds_id
       JOIN p06_cdsord o ON o.cds_id = m.cds_id AND o.aa_ord_id = m.aa_ord_id
       JOIN p01_anaper a ON s.pers_id = a.pers_id
       LEFT JOIN p01_nazi nazi_dom ON a.nazione_dom_id = nazi_dom.nazione_id
       LEFT JOIN p01_comu comu_dom ON a.comune_dom_id = comu_dom.comune_id
       LEFT JOIN p01_prov prov_dom ON comu_dom.sigla = prov_dom.sigla
       LEFT JOIN p01_nazi nazi_res ON a.naz_res_id = nazi_res.nazione_id
       LEFT JOIN p01_comu comu_res ON a.com_res_id = comu_res.comune_id
       LEFT JOIN p01_prov prov_res ON comu_res.sigla = prov_res.sigla
       LEFT JOIN p06_sett_dott settdott
          ON     settdott.cds_id = o.cds_id
             AND settdott.aa_ord_id = o.aa_ord_id
             AND settdott.prevalente_flg = 1
       LEFT JOIN p07_sett sett ON sett.sett_cod = settdott.sett_cod
       LEFT JOIN p07_aree aree ON aree.area_id = sett.area_id
       CROSS JOIN ate
 WHERE     m.sta_mat_cod = 'A'
       AND d.stato = 'CHI'
       AND c.tipo_corso_cod IN ('D1', 'D2')
       AND (   (d.data_ct BETWEEN TO_DATE ('01/01/2012', 'DD/MM/YYYY')
                              AND TO_DATE ('31/12/2012', 'DD/MM/YYYY'))
            OR (d.data_ct BETWEEN TO_DATE ('01/01/2014', 'DD/MM/YYYY')
                              AND TO_DATE ('31/12/2014', 'DD/MM/YYYY')))