CREATE VIEW ana_view_accerta AS
SELECT distinct
(
   CASE
      WHEN
         (
            select
               param_value
            from
               aut_global_params
            where
               app_prefix = 'ana'
               and param_key like '%gestione_numero_individuale%'
         )
         = '0'
      then
         per.pkid
      else
         per.numero_individuo
   end
) AS COD_PERSONA, an1.codice_fiscale as COD_FISCALE, an1.flg_codice_fiscale AS validaz_codfis, an1.cognome as cognome, an1.nome as nome, to_char(an1.data_nascita, 'dd/MM/yyyy') AS data_nascita,
   case
      when
         an1.sesso = 1
      then
         'M'
      when
         an1.sesso = 2
      then
         'F'
      else
         null
   end
   as sesso, coalesce(accres.cap, areres.cap, comres.cap) as cap, comres.des as COMUNE_RESID , stares.des as NAZIONE_RESID , '' as luogo_residenza, cast(
   case
      when
         comnas.st_provincia is null
      then
         null
      else
         TO_CHAR(comres.cod, 'fm000000')
   end
   as varchar(6)) as COD_COMUNE_RESID , cast( TO_CHAR(pronas.cod, 'fm000') as varchar(3)) as COD_PROV_RESID, cast( TO_CHAR(stares.cod, 'fm000') as varchar(3)) as COD_NAZIONE_RESID , cast(substr (
   CASE
      WHEN
         areres.pkid IS NOT NULL
      THEN
(
         CASE
            WHEN
               areres.specie IS NULL
            THEN
               areres.des
            ELSE
               rtrim(ltrim(rtrim(ltrim(areres.specie)) || ' ' || rtrim(ltrim(areres.des))))
         END
)
         ELSE
            accres.des_gen
   END
, 1, 40) as varchar(100)) AS via, accres.civico as NUM_CIVICO, accres.lettera as BIS, accres.piano as piano, accres.scala as scala, accres.interno as interno, cast(rtrim(ltrim(an1con.cognome || ' ' || an1con.nome)) as varchar(255)) as cognomeNomeConiuge,
   (
      select
         CASE
            WHEN
               (
                  select
                     param_value
                  from
                     aut_global_params
                  where
                     app_prefix = 'ana'
                     and param_key like '%gestione_numero_individuale%'
               )
               = '0'
            then
               perpd.pkid
            else
               perpd.numero_individuo
         end
      from
         ANA_PERSONE perpd
         inner join
            AN1_ANAGRAFE_UNICA an2
            on perpd.id_anagrafe_unica = an2.pkid
      where
         an2.pkid = an1con.pkid
   )
   AS COD_CONIUGE,
   case
      when
         an1.sesso = 1
      then
         stc.des_m
      else
         stc.des_f
   end
   as STATO_CIVILE, stc.cod_istat as COD_STATO_CIVILE_INTERNO, stc.cod_anpr as COD_STATO_CIVILE,
   case
      when
         fam.flg_con = 0
      then
         cast( rel.cod as varchar(5))
      else
         rel.cod_anpr_c
   end
   as COD_RELPAR,
   case
      when
         an1.sesso = 1
      then
         rel.des_m
      else
         rel.des_f
   end
   as RAPP_PARENTELA,
   case
      when
         rel.cod = 1
      then
         'S'
      else
         'N'
   end
   as CAPOFAMIGLIA_SI_NO, cast(
   case
      when
         stacit.cod = 1
      then
         1
      else
         stacit.cod
   end
   as smallint) as COD_CITTADINANZA , stacit.des_cittadinanza_generica as CITTADINANZA , stacit.flg_ue as COMUNITARIO_SI_NO , percit.data_ini as data_cittadinanza, cast(
   case
      when
         comnas.st_provincia is null
      then
         null
      else
         TO_CHAR(comnas.cod, 'fm000000')
   end
   as varchar(6)) as cod_comune_nascita, cast( TO_CHAR(pronas.cod, 'fm000') as varchar(3)) as cod_provincia_nascita, cast( TO_CHAR(stanas.cod, 'fm000') as varchar(3)) as cod_stato_estero_nascita,
   case
      when
         comnas.st_provincia is null
      then
         comnas.des
      else
         null
   end
   as des_comune_estero_nascita,
   case
      when
         comnas.st_provincia is null
      then
         stanas.des
      else
         null
   end
   as stato_estero_nascita, pronas.des as provincia_nascita, comattnas.des as comune_atto_nascita,
   case
      when
         pernas.st_comune_atto = cominst.static_id
      then
         pernas.numero
      else
         pernas.numero_t
   end
   as numero_atto_nascita, cast(
   case
      when
         pernas.st_comune_atto = cominst.static_id
      then
         pernas.parte
      else
         pernas.parte_t
   end
   as varchar(255)) as parte_nascita,
   case
      when
         pernas.st_comune_atto = cominst.static_id
      then
         pernas.serie
      else
         pernas.serie_t
   end
   as serie_nascita, cast(
   case
      when
         pernas.st_comune_atto = cominst.static_id
      then
         pernas.anno
      else
         pernas.anno_t
   end
   as smallint) as anno_atto_nascita,
   case
      when
         pernas.st_comune_atto = cominst.static_id
      then
         pernas.ufficio
      else
         pernas.ufficio_t
   end
   as ufficio_nascita, cast(an1pd.nominativo as varchar(250)) as PADRE, an1pd.codice_fiscale as COD_FISCALE_PADRE ,
   (
      select
         CASE
            WHEN
               (
                  select
                     param_value
                  from
                     aut_global_params
                  where
                     app_prefix = 'ana'
                     and param_key like '%gestione_numero_individuale%'
               )
               = '0'
            then
               perpd.pkid
            else
               perpd.numero_individuo
         end
      from
         ANA_PERSONE perpd
         inner join
            AN1_ANAGRAFE_UNICA an2
            on perpd.id_anagrafe_unica = an2.pkid
      where
         an2.pkid = an1pd.pkid
   )
   AS COD_PADRE, an1md.codice_fiscale as COD_FISCALE_MADRE , cast(an1md.nominativo as varchar(250)) as MADRE,
   (
      select
         CASE
            WHEN
               (
                  select
                     param_value
                  from
                     aut_global_params
                  where
                     app_prefix = 'ana'
                     and param_key like '%gestione_numero_individuale%'
               )
               = '0'
            then
               perpd.pkid
            else
               perpd.numero_individuo
         end
      from
         ANA_PERSONE perpd
         inner join
            AN1_ANAGRAFE_UNICA an2
            on perpd.id_anagrafe_unica = an2.pkid
      where
         an2.pkid = an1md.pkid
   )
   AS COD_MADRE, cast(
   CASE
      WHEN
         per.flg_cessato = 0
         and fam.flg_aire = 0
      THEN
         'RESIDENTE'
      WHEN
         per.flg_cessato = 0
         and fam.flg_aire = 1
      THEN
         'AIRE'
      WHEN
         per.flg_cessato = 1
         AND
         (
((mot_apr_fin.gruppo * 100 + mot_apr_fin.tipo * 10 + mot_apr_fin.cod) IN
            (
               123, 123
            )
)
            OR
            (
(mot_aire_fin.gruppo * 100 + mot_aire_fin.tipo * 10 + mot_aire_fin.cod) IN
               (
                  123, 123
               )
            )
         )
      THEN
         'EMIGRATO'
      WHEN
         per.flg_cessato = 1
         AND
         (
((mot_apr_fin.gruppo * 100 + mot_apr_fin.tipo * 10 + mot_apr_fin.cod) IN
            (
               125, 127, 144
            )
)
            OR
            (
(mot_aire_fin.gruppo * 100 + mot_aire_fin.tipo * 10 + mot_aire_fin.cod) IN
               (
                  125, 127, 144
               )
            )
         )
      THEN
         'IRREPERIBILE'
      WHEN
         per.flg_cessato = 1
         AND
         (
((mot_apr_fin.gruppo * 100 + mot_apr_fin.tipo * 10 + mot_apr_fin.cod) IN
            (
               121, 142
            )
)
            OR
            (
(mot_aire_fin.gruppo * 100 + mot_aire_fin.tipo * 10 + mot_aire_fin.cod) IN
               (
                  121, 142
               )
            )
         )
      THEN
         'DECEDUTO'
      ELSE
         'NON ISCRITTO'
   END
   as varchar(215)) AS DESC_POSIZIONE_ANAGá ,
   case
      when
         permat.st_comune_atto = cominst.static_id
      then
         permat.numero
      else
         permat.numero_t
   end
   as numero_atto_matrimonio, cast(
   case
      when
         permat.st_comune_atto = cominst.static_id
      then
         permat.parte
      else
         permat.parte_t
   end
   as varchar(255)) as parte_matrimonio,
   case
      when
         permat.st_comune_atto = cominst.static_id
      then
         permat.serie
      else
         permat.serie_t
   end
   as serie_matrimonio, cast(
   case
      when
         permat.st_comune_atto = cominst.static_id
      then
         permat.anno
      else
         permat.anno_t
   end
   as smallint) as anno_matrimonio,
   case
      when
         permat.st_comune_atto = cominst.static_id
      then
         permat.ufficio
      else
         permat.ufficio_t
   end
   as ufficio_matrimonio, cast(
   case
      when
         commat.st_provincia is not null
      then
         TO_CHAR(commat.cod, 'fm000000')
      else
         null
   end
   as varchar(6)) as cod_comune_matrimonio, commat.des as luogo_matrimonio, cast(permat.data_eve as date) as data_matrimonio, cast(perdiv.data_eve as date) as data_decorrenza_divorzio, cast(perdiv.data_sentenza as date) as data_sentenza_divorzio, perdiv.num_sentenza as numero_sentenza_divorzio, cast(TO_CHAR(comtrbdiv.cod, 'fm000000') as varchar(6)) as cod_com_tribunale_divor, cast(null as varchar(255)) as tipo_divorzio, cast(null as date) as data_atto_divorzio,
   case
      when
         perdiv.st_comune_atto = cominst.static_id
      then
         perdiv.numero
      else
         null
   end
   as numero_atto_divorzio, cast(
   case
      when
         perdiv.st_comune_atto = cominst.static_id
      then
         perdiv.parte
      else
         null
   end
   as varchar(255)) as parte_divorzio,
   case
      when
         perdiv.st_comune_atto = cominst.static_id
      then
         perdiv.serie
      else
         null
   end
   as serie_divorzio,
   case
      when
         perdiv.st_comune_atto = cominst.static_id
      then
         perdiv.volume
      else
         null
   end
   as volume_divorzio,
   case
      when
         perdiv.st_comune_atto = cominst.static_id
      then
         perdiv.ufficio
      else
         null
   end
   as ufficio_divorzio, cast(null as date) as data_atto_vedovanza,
   case
      when
         perved.st_comune_atto = cominst.static_id
      then
         perved.numero
      else
         null
   end
   as numero_atto_vedovanza, cast(
   case
      when
         perved.st_comune_atto = cominst.static_id
      then
         perved.parte
      else
         null
   end
   as varchar(255)) as parte_vedovanza,
   case
      when
         perved.st_comune_atto = cominst.static_id
      then
         perved.serie
      else
         null
   end
   as serie_vedovanza,
   case
      when
         perved.st_comune_atto = cominst.static_id
      then
         perved.volume
      else
         null
   end
   as volume_vedovanza,
   case
      when
         perved.st_comune_atto = cominst.static_id
      then
         perved.ufficio
      else
         null
   end
   as ufficio_vedovanza, cast(
   case
      when
         commor.st_provincia is not null
      then
         TO_CHAR(commor.cod, 'fm000000')
      else
         null
   end
   as varchar(6)) as codice_istat_comune_morte, cast(permor.data_eve as date) as data_morte, cast(permor.numero as integer) as numero_atto_morte, cast(permor.parte as varchar(255)) as parte_morte, cast(permor.serie as varchar(255)) as serie_morte, cast(permor.ufficio as varchar(255)) as ufficio_morte, cast(permor.anno as smallint) as anno_morte, prof.des as professione, studio.cod_anpr as cod_studio, cast(
   case
      when
         perci.numero_cie is null
      then
         perci.sig_ci || cast(perci.num_ci as varchar(11))
      else
         perci.numero_cie
   end
   as varchar(21)) as numero_ci,
   case
      when
         perci.numero_cie is null
      then
         'CI'
      else
         'CIE'
   end
   as tipo_ci, cast(perci.data_eve as date) as data_ci, cast(perci.data_scadenza as date) as data_scadenza_ci, perci.flg_espatrio as ci_valida_espatrio, fam.numero_famiglia as cod_famiglia, TO_CHAR(comaprini.cod, 'fm000000') as COD_COMUNE_IMMIGR_APR, cast(comaprini.des as varchar(50)) as COMUNE_IMMIGR_APR, aprini.data_ini as DATA_IMMIGR,
   CASE
      WHEN
         mot_apr_fin.cod > 1
      THEN
         TO_CHAR(comaprfin.cod, 'fm000000')
      ELSE
         cast ( null as varchar(6))
   END
   AS COD_COMUNE_EMIGR_APR , cast(
   CASE
      WHEN
         mot_apr_fin.cod > 1
      THEN
         comaprfin.des
      ELSE
         null
   END
   as varchar(50)) AS COMUNE_EMIGR_APR ,
   CASE
      WHEN
         mot_apr_fin.cod > 1
      THEN
         aprfin.data_ini
      ELSE
         cast ( null as date)
   END
   AS data_EMIGR_APR ,
   CASE
      WHEN
         mot_apr_fin.cod != 2
      then
         TO_CHAR(comairefin.cod, 'fm000000')
      ELSE
         cast ( null as varchar(6))
   END
   AS COD_COMUNE_EMIGR_AIRE , cast(
   CASE
      WHEN
         mot_apr_fin.cod != 2
      then
         comairefin.des
      ELSE
         null
   END
   as varchar(50)) AS comune_EMIGR_AIRE,
   CASE
      WHEN
         mot_apr_fin.cod != 2
      then
         airefin.data_ini
      ELSE
         cast ( null as date)
   END
   AS data_EMIGR_aire , TO_CHAR(comaireini.cod, 'fm000000') AS COD_COMUNE_IMMIGR_AIRE , cast( comaireini.des as varchar(50)) AS comune_IMMIGR_AIRE , aireini.data_ini AS data_IMMIGR_AIRE ,
   CASE
      WHEN
         fam.flg_aire = 0
      THEN
         aprini.data_ini
      ELSE
         aireini.data_ini
   END
   AS DTA_ISCRIZ_ANAG, sogg.numero as numero_soggiorno, sogg.data_rilascio as data_rilascio_soggiorno ,
   case
      when
         sogg.tipo = 1
      then
         'Permesso di soggiorno'
      when
         sogg.tipo = 2
      then
         'Carta di soggiorno'
      else
         cast(null as varchar(20))
   end
   as tipo_soggiorno, TO_CHAR(comsog.cod, 'fm000000') AS COD_COMUNE_soggiorno , comsog.des AS COMUNE_soggiorno
FROM
   AN1_RECAPITI rec
   left outer join
      TER_ACCESSI accres
      on rec.id_accesso = accres.pkid
   left outer join
      TER_AREE areres
      on accres.st_area = areres.static_id
   left outer join
      TER_COMUNI comres
      on accres.st_comune = comres.static_id
   left outer join
      TER_STATI stares
      on comres.st_stato = stares.static_id, ANA_FAMIGLIE_PERSONE fp
   inner join
      ANA_PERSONE per
      on fp.id_persona = per.pkid
   inner join
      AN1_ANAGRAFE_UNICA an1
      on per.id_anagrafe_unica = an1.pkid
   left outer join
      TER_COMUNI comnas
      on an1.st_comune_nascita = comnas.static_id
   left outer join
      TER_PROVINCE pronas
      on comnas.st_provincia = pronas.static_id
   left outer join
      TER_STATI stanas
      on comnas.st_stato = stanas.static_id
   inner join
      ANA_FAMIGLIE fam
      on fp.id_famiglia = fam.pkid
   left outer join
      ANA_PERSONE_STATO_CIVILE perstc
      on perstc.id_persona = per.pkid
      and perstc.flg_cessato = 0
      and perstc.data_fin is null
   left outer join
      ANA_STATO_CIVILE stc
      on stc.pkid = perstc.id_stato_civile
   left outer join
      ANA_RELAZIONI rel
      on rel.pkid = fp.id_relazione
   left outer join
      ANA_PERSONE_CITTADINANZA percit
      on percit.id_persona = per.pkid
      and percit.flg_cessato = 0
      and percit.data_fin is null
   left outer join
      TER_STATI stacit
      on stacit.static_id = percit.st_stato_cittadinanza
   left outer join
      ANA_PERSONE_NASCITA pernas
      on pernas.id_persona = per.pkid
      and pernas.flg_Cessato = 0
      and pernas.data_fin is null
   left outer join
      TER_COMUNI comattnas
      on pernas.st_comune_atto = comattnas.static_id
   left join
      an1_anagrafe_unica an1md
      on an1md.pkid = pernas.id_an1_madre
   left join
      an1_anagrafe_unica an1pd
      on an1pd.pkid = pernas.id_an1_padre
   left outer join
      ANA_PERSONE_MATRIMONIO permat
      on permat.id_persona = per.pkid
      and permat.flg_Cessato = 0
      and permat.data_fin is null
      and permat.tipo = 0
   left outer join
      AN1_ANAGRAFE_UNICA an1con
      on an1con.pkid = permat.id_an1_coniuge
   left outer join
      TER_COMUNI comattmat
      on permat.st_comune_atto = comattmat.static_id
   left outer join
      TER_COMUNI commat
      on permat.st_comune = commat.static_id
   left outer join
      ANA_PERSONE_DIVORZIO perdiv
      on perdiv.id_persona = per.pkid
      and perdiv.flg_cessato = 0
      and perdiv.data_fin is null
      and perdiv.tipo = 0
   left outer join
      TER_COMUNI comattdiv
      on perdiv.st_comune_atto = comattdiv.static_id
   left outer join
      TER_TRIBUNALI trbdiv
      on perdiv.id_tribunale = trbdiv.pkid
   left outer join
      TER_COMUNI comtrbdiv
      on comtrbdiv.pkid = trbdiv.id_comune
   left outer join
      ANA_PERSONE_VEDOVANZA perved
      on perved.id_persona = per.pkid
      and perved.flg_cessato = 0
      and perved.data_fin is null
      and perved.tipo = 0
   left outer join
      ANA_PERSONE_PROFESSIONI prof
      on prof.id_persona = per.pkid
      and prof.flg_cessato = 0
      and prof.data_fin is null
   left join
      ana_cond_prof cprof
      on cprof.pkid = prof.id_cond_prof
   left outer join
      ANA_PERSONE_STUDIO titst
      on titst.id_persona = per.pkid
      and titst.flg_cessato = 0
      and titst.data_fin is null
   left outer join
      ana_studio studio
      on titst.id_studio = studio.pkid
   left outer join
      ANA_PERSONE_CI perci
      on perci.id_persona = per.pkid
      and perci.flg_cessato = 0
      and perci.data_fin is null
   left outer join
      ANA_PERSONE_MORTE permor
      on permor.id_persona = per.pkid
      and permor.flg_cessato = 0
      and permor.data_fin is null
   left outer join
      TER_COMUNI commor
      on permor.st_comune = commor.static_id
   LEFT JOIN
      ana_persone_apr_ini aprini
      ON aprini.id_persona = per.pkid
      AND aprini.data_reg is not null
      AND aprini.data_ini <= sysdate
      AND
      (
         aprini.data_fin > sysdate
         OR aprini.data_fin IS NULL
      )
   left join
      ter_Comuni comaprini
      on comaprini.static_id = aprini.st_comune_prov
   left join
      ter_Stati staprini
      on staprini.static_id = aprini.st_stato_prov
   LEFT JOIN
      ana_persone_apr_fin aprfin
      ON aprfin.id_persona = per.pkid
      AND aprfin.data_ini <= sysdate
      AND
      (
         aprfin.data_fin > sysdate
         OR aprfin.data_fin IS NULL
      )
   left join
      ter_Comuni comaprfin
      on comaprfin.static_id = aprfin.st_comune_dest
   left join
      ter_Stati staaprfin
      on staaprfin.static_id = comaprfin.st_stato
   left outer join
      ana_motivi mot_apr_fin
      on mot_apr_fin.pkid = aprfin.id_motivo
   LEFT JOIN
      ana_persone_aire_ini aireini
      ON aireini.id_persona = per.pkid
      AND aireini.data_reg is not null
      AND aireini.data_ini <= sysdate
      AND
      (
         aireini.data_fin > sysdate
         OR aireini.data_fin IS NULL
      )
   left join
      ter_Comuni comaireini
      on comaireini.static_id = aireini.st_comune_prov
   left outer join
      ana_motivi mot_aire_ini
      on mot_aire_ini.pkid = aireini.id_motivo
   LEFT JOIN
      ana_persone_aire_fin airefin
      ON airefin.id_persona = per.pkid
      AND airefin.data_ini <= sysdate
      AND
      (
         airefin.data_fin > sysdate
         OR airefin.data_fin IS NULL
      )
   left join
      Ter_Comuni comairefin
      on comairefin.static_id = airefin.st_comune_dest
   left join
      Ter_Stati staairefin
      on staairefin.static_id = comairefin.st_stato
   left outer join
      ana_motivi mot_aire_fin
      on mot_aire_fin.pkid = airefin.id_motivo
   left outer join
      ANA_PERSONE_SOGGIORNO persog
      on persog.id_persona = per.pkid
      and persog.flg_cessato = 0
      and persog.data_fin is null
   left outer join
      ANA_SOGGIORNO sogg
      on persog.st_soggiorno = sogg.static_id
   left outer join
      TER_COMUNI comsog
      on sogg.st_comune_questura = comsog.static_id , aut_global_params parm
   inner join
      ter_comuni cominst
      on cominst.pkid = cast(param_value as integer)
where
   per.flg_con = 0
   and per.id_anagrafe_unica = rec.id_anag_unica
   and rec.flg_residenza = 1
   and fp.id_persona = fp.id_persona
   and rec.flg_residenza = 1
   and
   (
      fp.flg_cessato = 0
      or
      (
         fp.flg_cessato = 1
         and not exists
         (
            select
               *
            from
               Ana_Famiglie_Persone fp1
            where
               fp1.id_persona = per.pkid
               and fp1.data_ini >= fp.data_ini
               and fp1.flg_cessato = 0
         )
      )
   )
   and
   (
(fp.flg_cessato = 0
      and rec.data_fin is null)
      or
      (
         fp.flg_cessato = 1
         and rec.data_ini < fp.data_ini
         and
         (
            rec.data_fin >= fp.data_ini
            or rec.data_fin is null
         )
      )
   )
   and an1.pkid not in
   (
      select
         n.id_anagrafe_unica
      from
         Ana_Non_Iscrivere n
      where
         n.flg_annullato = 0
   )
   and fp.data_fin is null
   and stacit.cod =
   (
(
      SELECT
         min(stacit2.cod) AS min
      FROM
         ana_persone_cittadinanza cit2
         LEFT JOIN
            ter_stati stacit2
            ON stacit2.static_id = cit2.st_stato_cittadinanza
      WHERE
         cit2.id_persona = percit.id_persona
         AND
         (
            cit2.pkid IS NULL
            OR cit2.data_fin IS NULL
            AND cit2.flg_cessato = 0
         )
         AND
         (
            stacit2.pkid IS NULL
            OR stacit2.data_fin IS NULL
            AND stacit2.flg_cessato = 0
         )
)
   )
   and parm.param_key = 'id_comune_installazione' 	--     and ROWNUM <= 2