API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Nov 20th, 2017
79
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.79 KB | None | 0 0
raw download clone embed print report
  1.  
  2. CREATE VIEW ana_view_accerta AS
  3. SELECT distinct
  4. (CASE WHEN (select param_value from aut_global_params
  5. where app_prefix='ana'
  6. and
  7. param_key like '%gestione_numero_individuale%' ) ='0' then per.pkid else
  8. per.numero_individuo end) AS COD_PERSONA,
  9. an1.codice_fiscale as COD_FISCALE,
  10. an1.flg_codice_fiscale AS validaz_codfis,
  11. an1.cognome as cognome,
  12. an1.nome as nome,
  13. to_char(an1.data_nascita,'dd/MM/yyyy') AS data_nascita,
  14. case
  15. when an1.sesso=1 then 'M'
  16. when an1.sesso=2 then 'F'
  17. else null
  18. end as sesso,
  19. coalesce(accres.cap,areres.cap,comres.cap) as cap,
  20. comres.des as COMUNE_RESID ,
  21. stares.des as NAZIONE_RESID ,
  22. '' as luogo_residenza,
  23. cast( case
  24. when comnas.st_provincia is null then null
  25. else TO_CHAR(comres.cod,'fm000000') end as varchar(6)) as COD_COMUNE_RESID ,
  26. cast( TO_CHAR(pronas.cod,'fm000') as varchar(3)) as COD_PROV_RESID,
  27. cast( TO_CHAR(stares.cod,'fm000') as varchar(3)) as COD_NAZIONE_RESID ,
  28. cast(substr (
  29. CASE
  30. WHEN areres.pkid IS NOT NULL THEN (CASE
  31. WHEN areres.specie IS NULL THEN areres.des
  32. ELSE rtrim(ltrim(rtrim(ltrim(areres.specie)) || ' ' || rtrim(ltrim(areres.des))))
  33. END)
  34. ELSE accres.des_gen
  35. END,1,40) as varchar(100)) AS via,
  36. 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,
  37. (select CASE WHEN (select param_value from aut_global_params
  38. where app_prefix='ana'
  39. and
  40. param_key like '%gestione_numero_individuale%' ) ='0' then perpd.pkid else
  41. perpd.numero_individuo end from ANA_PERSONE perpd inner join AN1_ANAGRAFE_UNICA an2 on perpd.id_anagrafe_unica=an2.pkid
  42. where an2.pkid = an1con.pkid ) AS COD_CONIUGE,
  43. case
  44. when an1.sesso=1 then stc.des_m
  45. else stc.des_f
  46. end as STATO_CIVILE,
  47. stc.cod_istat as COD_STATO_CIVILE_INTERNO,
  48. stc.cod_anpr as COD_STATO_CIVILE,
  49. case when fam.flg_con = 0 then cast( rel.cod as varchar(5))
  50. else rel.cod_anpr_c end
  51. as COD_RELPAR,
  52. case
  53. when an1.sesso=1 then rel.des_m
  54. else rel.des_f
  55. end as RAPP_PARENTELA,
  56. case
  57. when rel.cod=1 then 'S'
  58. else 'N'
  59. end as CAPOFAMIGLIA_SI_NO,
  60. cast(case
  61. when stacit.cod=1 then 1
  62. else stacit.cod
  63. end as smallint) as COD_CITTADINANZA ,
  64. stacit.des_cittadinanza_generica as CITTADINANZA ,
  65. stacit.flg_ue as COMUNITARIO_SI_NO ,
  66. percit.data_ini as data_cittadinanza,
  67. cast( case
  68. when comnas.st_provincia is null then null
  69. else TO_CHAR(comnas.cod,'fm000000') end as varchar(6)) as cod_comune_nascita,
  70. cast( TO_CHAR(pronas.cod,'fm000') as varchar(3)) as cod_provincia_nascita,
  71. cast( TO_CHAR(stanas.cod,'fm000') as varchar(3)) as cod_stato_estero_nascita,
  72. case
  73. when comnas.st_provincia is null then comnas.des
  74. else null
  75. end as des_comune_estero_nascita,
  76. case
  77. when comnas.st_provincia is null then stanas.des
  78. else null
  79. end as stato_estero_nascita,
  80. pronas.des as provincia_nascita,
  81. comattnas.des as comune_atto_nascita,
  82. case
  83. when pernas.st_comune_atto=cominst.static_id then pernas.numero
  84. else pernas.numero_t
  85. end as numero_atto_nascita,
  86. cast( case
  87. when pernas.st_comune_atto=cominst.static_id then pernas.parte
  88. else pernas.parte_t
  89. end as varchar(255)) as parte_nascita,
  90. case
  91. when pernas.st_comune_atto=cominst.static_id then pernas.serie
  92. else pernas.serie_t
  93. end as serie_nascita,
  94. cast(case
  95. when pernas.st_comune_atto=cominst.static_id then pernas.anno
  96. else pernas.anno_t
  97. end as smallint) as anno_atto_nascita,
  98. case
  99. when pernas.st_comune_atto=cominst.static_id then pernas.ufficio
  100. else pernas.ufficio_t
  101. end as ufficio_nascita,
  102. cast(an1pd.nominativo as varchar(250)) as PADRE,
  103. an1pd.codice_fiscale as COD_FISCALE_PADRE ,
  104. (select CASE WHEN (select param_value from aut_global_params
  105. where app_prefix='ana'
  106. and
  107. param_key like '%gestione_numero_individuale%' ) ='0' then perpd.pkid else
  108. perpd.numero_individuo end from ANA_PERSONE perpd inner join AN1_ANAGRAFE_UNICA an2 on perpd.id_anagrafe_unica=an2.pkid
  109. where an2.pkid = an1pd.pkid ) AS COD_PADRE,
  110. an1md.codice_fiscale as COD_FISCALE_MADRE ,
  111. cast(an1md.nominativo as varchar(250)) as MADRE,
  112. (select CASE WHEN (select param_value from aut_global_params
  113. where app_prefix='ana'
  114. and
  115. param_key like '%gestione_numero_individuale%' ) ='0' then perpd.pkid else
  116. perpd.numero_individuo end from ANA_PERSONE perpd inner join AN1_ANAGRAFE_UNICA an2 on perpd.id_anagrafe_unica=an2.pkid
  117. where an2.pkid = an1md.pkid ) AS COD_MADRE,
  118. cast( CASE
  119. WHEN per.flg_cessato = 0 and fam.flg_aire = 0 THEN 'RESIDENTE'
  120. WHEN per.flg_cessato = 0 and fam.flg_aire = 1 THEN 'AIRE'
  121. WHEN per.flg_cessato = 1 AND (((mot_apr_fin.gruppo * 100 + mot_apr_fin.tipo * 10 + mot_apr_fin.cod) IN (123,123)) OR
  122. ((mot_aire_fin.gruppo * 100 + mot_aire_fin.tipo * 10 + mot_aire_fin.cod) IN (123,123))) THEN 'EMIGRATO'
  123. 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
  124. ((mot_aire_fin.gruppo * 100 + mot_aire_fin.tipo * 10 + mot_aire_fin.cod) IN (125,127,144))) THEN 'IRREPERIBILE'
  125. WHEN per.flg_cessato = 1 AND (((mot_apr_fin.gruppo * 100 + mot_apr_fin.tipo * 10 + mot_apr_fin.cod) IN (121,142)) OR
  126. ((mot_aire_fin.gruppo * 100 + mot_aire_fin.tipo * 10 + mot_aire_fin.cod) IN (121,142))) THEN 'DECEDUTO'
  127. ELSE 'NON ISCRITTO'
  128. END as varchar(215)) AS DESC_POSIZIONE_ANAGá ,
  129. case
  130. when permat.st_comune_atto=cominst.static_id then permat.numero
  131. else permat.numero_t
  132. end as numero_atto_matrimonio,
  133. cast(case
  134. when permat.st_comune_atto=cominst.static_id then permat.parte
  135. else permat.parte_t
  136. end as varchar(255)) as parte_matrimonio,
  137. case
  138. when permat.st_comune_atto=cominst.static_id then permat.serie
  139. else permat.serie_t
  140. end as serie_matrimonio,
  141. cast(case
  142. when permat.st_comune_atto=cominst.static_id then permat.anno
  143. else permat.anno_t
  144. end as smallint) as anno_matrimonio,
  145. case
  146. when permat.st_comune_atto=cominst.static_id then permat.ufficio
  147. else permat.ufficio_t
  148. end as ufficio_matrimonio,
  149. cast(case
  150. when commat.st_provincia is not null then TO_CHAR(commat.cod,'fm000000')
  151. else null
  152. end as varchar(6)) as cod_comune_matrimonio,
  153. commat.des as luogo_matrimonio,
  154. cast(permat.data_eve as date) as data_matrimonio,
  155. cast(perdiv.data_eve as date) as data_decorrenza_divorzio,
  156. cast(perdiv.data_sentenza as date) as data_sentenza_divorzio,
  157. perdiv.num_sentenza as numero_sentenza_divorzio,
  158. cast(TO_CHAR(comtrbdiv.cod,'fm000000') as varchar(6)) as cod_com_tribunale_divor,
  159. cast(null as varchar(255)) as tipo_divorzio,
  160. cast(null as date) as data_atto_divorzio,
  161. case
  162. when perdiv.st_comune_atto=cominst.static_id then perdiv.numero
  163. else null
  164. end as numero_atto_divorzio,
  165. cast(case
  166. when perdiv.st_comune_atto=cominst.static_id then perdiv.parte
  167. else null
  168. end as varchar(255)) as parte_divorzio,
  169. case
  170. when perdiv.st_comune_atto=cominst.static_id then perdiv.serie
  171. else null
  172. end as serie_divorzio,
  173. case
  174. when perdiv.st_comune_atto=cominst.static_id then perdiv.volume
  175. else null
  176. end as volume_divorzio,
  177. case
  178. when perdiv.st_comune_atto=cominst.static_id then perdiv.ufficio
  179. else null
  180. end as ufficio_divorzio,
  181. cast(null as date) as data_atto_vedovanza,
  182. case
  183. when perved.st_comune_atto=cominst.static_id then perved.numero
  184. else null
  185. end as numero_atto_vedovanza,
  186. cast(case
  187. when perved.st_comune_atto=cominst.static_id then perved.parte
  188. else null
  189. end as varchar(255)) as parte_vedovanza,
  190. case
  191. when perved.st_comune_atto=cominst.static_id then perved.serie
  192. else null
  193. end as serie_vedovanza,
  194. case
  195. when perved.st_comune_atto=cominst.static_id then perved.volume
  196. else null
  197. end as volume_vedovanza,
  198. case
  199. when perved.st_comune_atto=cominst.static_id then perved.ufficio
  200. else null
  201. end as ufficio_vedovanza,
  202.  
  203. cast( case
  204. when commor.st_provincia is not null then TO_CHAR(commor.cod,'fm000000')
  205. else null
  206. end as varchar(6)) as codice_istat_comune_morte,
  207. cast(permor.data_eve as date) as data_morte,
  208. cast(permor.numero as integer) as numero_atto_morte,
  209. cast(permor.parte as varchar(255)) as parte_morte,
  210. cast(permor.serie as varchar(255)) as serie_morte,
  211. cast(permor.ufficio as varchar(255)) as ufficio_morte,
  212. cast(permor.anno as smallint) as anno_morte,
  213.  
  214. prof.des as professione,
  215. studio.cod_anpr as cod_studio,
  216. 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,
  217. case when perci.numero_cie is null then 'CI' else 'CIE' end as tipo_ci,
  218. cast(perci.data_eve as date) as data_ci,
  219. cast(perci.data_scadenza as date) as data_scadenza_ci,
  220. perci.flg_espatrio as ci_valida_espatrio,
  221. fam.numero_famiglia as cod_famiglia,
  222.  
  223. TO_CHAR(comaprini.cod,'fm000000')
  224. as COD_COMUNE_IMMIGR_APR, cast(comaprini.des as varchar(50)) as COMUNE_IMMIGR_APR, aprini.data_ini as DATA_IMMIGR,
  225. CASE
  226. WHEN mot_apr_fin.cod > 1 THEN TO_CHAR(comaprfin.cod,'fm000000') ELSE cast ( null as varchar(6)) END AS COD_COMUNE_EMIGR_APR ,
  227. cast( CASE
  228. WHEN mot_apr_fin.cod > 1 THEN comaprfin.des ELSE null END as varchar(50)) AS COMUNE_EMIGR_APR ,
  229. CASE
  230. WHEN mot_apr_fin.cod > 1 THEN aprfin.data_ini ELSE cast ( null as date) END AS data_EMIGR_APR ,
  231. CASE
  232. WHEN mot_apr_fin.cod != 2 then TO_CHAR(comairefin.cod,'fm000000') ELSE cast ( null as varchar(6)) END AS COD_COMUNE_EMIGR_AIRE ,
  233. cast( CASE
  234. WHEN mot_apr_fin.cod != 2 then comairefin.des ELSE null END as varchar(50)) AS comune_EMIGR_AIRE,
  235. CASE
  236. WHEN mot_apr_fin.cod != 2 then airefin.data_ini ELSE cast ( null as date) END AS data_EMIGR_aire ,
  237. TO_CHAR(comaireini.cod,'fm000000') AS COD_COMUNE_IMMIGR_AIRE ,
  238. cast(
  239. comaireini.des as varchar(50)) AS comune_IMMIGR_AIRE ,
  240. aireini.data_ini AS data_IMMIGR_AIRE ,
  241. CASE
  242. WHEN fam.flg_aire = 0 THEN aprini.data_ini ELSE aireini.data_ini END AS DTA_ISCRIZ_ANAG,
  243. sogg.numero as numero_soggiorno,
  244. sogg.data_rilascio as data_rilascio_soggiorno ,
  245. 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,
  246. TO_CHAR(comsog.cod,'fm000000') AS COD_COMUNE_soggiorno ,
  247. comsog.des AS COMUNE_soggiorno
  248. FROM AN1_RECAPITI rec
  249. left outer join TER_ACCESSI accres on rec.id_accesso=accres.pkid
  250. left outer join TER_AREE areres on accres.st_area=areres.static_id
  251. left outer join TER_COMUNI comres on accres.st_comune=comres.static_id
  252. left outer join TER_STATI stares on comres.st_stato=stares.static_id,
  253. ANA_FAMIGLIE_PERSONE fp
  254. inner join ANA_PERSONE per on fp.id_persona=per.pkid
  255. inner join AN1_ANAGRAFE_UNICA an1 on per.id_anagrafe_unica=an1.pkid
  256. left outer join TER_COMUNI comnas on an1.st_comune_nascita=comnas.static_id
  257. left outer join TER_PROVINCE pronas on comnas.st_provincia=pronas.static_id
  258. left outer join TER_STATI stanas on comnas.st_stato=stanas.static_id
  259. inner join ANA_FAMIGLIE fam on fp.id_famiglia=fam.pkid
  260. 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
  261. left outer join ANA_STATO_CIVILE stc on stc.pkid=perstc.id_stato_civile
  262. left outer join ANA_RELAZIONI rel on rel.pkid=fp.id_relazione
  263. left outer join ANA_PERSONE_CITTADINANZA percit on percit.id_persona=per.pkid and percit.flg_cessato=0 and percit.data_fin is null
  264. left outer join TER_STATI stacit on stacit.static_id=percit.st_stato_cittadinanza
  265. left outer join ANA_PERSONE_NASCITA pernas on pernas.id_persona=per.pkid and pernas.flg_Cessato=0 and pernas.data_fin is null
  266. left outer join TER_COMUNI comattnas on pernas.st_comune_atto=comattnas.static_id
  267. 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
  268. 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
  269. left outer join TER_COMUNI commat on permat.st_comune=commat.static_id
  270. 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
  271. left outer join TER_COMUNI comattdiv on perdiv.st_comune_atto=comattdiv.static_id
  272. left outer join TER_TRIBUNALI trbdiv on perdiv.id_tribunale=trbdiv.pkid
  273. left outer join TER_COMUNI comtrbdiv on comtrbdiv.pkid=trbdiv.id_comune
  274. 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
  275. left outer join ANA_PERSONE_PROFESSIONI prof on prof.id_persona=per.pkid and prof.flg_cessato=0 and prof.data_fin is null
  276. left join ana_cond_prof cprof on cprof.pkid = prof.id_cond_prof
  277. left outer join ANA_PERSONE_STUDIO titst on titst.id_persona=per.pkid and titst.flg_cessato=0 and titst.data_fin is null
  278. 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
  279. left outer join ANA_PERSONE_MORTE permor on permor.id_persona=per.pkid and permor.flg_cessato=0 and permor.data_fin is null
  280. left outer join TER_COMUNI commor on permor.st_comune=commor.static_id
  281. 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)
  282. left join ter_Comuni comaprini on comaprini.static_id = aprini.st_comune_prov
  283. left join ter_Stati staprini on staprini.static_id = aprini.st_stato_prov
  284. 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)
  285. left join ter_Comuni comaprfin on comaprfin.static_id = aprfin.st_comune_dest
  286. left join ter_Stati staaprfin on staaprfin.static_id = comaprfin.st_stato
  287. left outer join ana_motivi mot_apr_fin on mot_apr_fin.pkid=aprfin.id_motivo
  288. 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)
  289. left join ter_Comuni comaireini on comaireini.static_id = aireini.st_comune_prov
  290. left outer join ana_motivi mot_aire_ini on mot_aire_ini.pkid=aireini.id_motivo
  291. 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)
  292. left join Ter_Comuni comairefin on comairefin.static_id = airefin.st_comune_dest
  293. left join Ter_Stati staairefin on staairefin.static_id = comairefin.st_stato
  294. left outer join ana_motivi mot_aire_fin on mot_aire_fin.pkid=airefin.id_motivo
  295. left outer join ANA_PERSONE_SOGGIORNO persog on persog.id_persona=per.pkid and persog.flg_cessato=0 and persog.data_fin is null
  296. left outer join ANA_SOGGIORNO sogg on persog.st_soggiorno=sogg.static_id
  297. left outer join TER_COMUNI comsog on sogg.st_comune_questura=comsog.static_id
  298. , aut_global_params parm
  299. inner join ter_comuni cominst on cominst.pkid=cast(param_value as integer)
  300. where per.flg_con=0
  301. and per.id_anagrafe_unica=rec.id_anag_unica
  302. and rec.flg_residenza=1
  303. and fp.id_persona=fp.id_persona
  304. and rec.flg_residenza=1
  305. and (fp.flg_cessato=0
  306. or (fp.flg_cessato=1
  307. and not exists (select *
  308. from Ana_Famiglie_Persone fp1
  309. where fp1.id_persona=per.pkid
  310. and fp1.data_ini>=fp.data_ini
  311. and fp1.flg_cessato=0)))
  312. and ((fp.flg_cessato=0
  313. and rec.data_fin is null)
  314. or (fp.flg_cessato=1
  315. and rec.data_ini<fp.data_ini
  316. and (rec.data_fin>=fp.data_ini
  317. or rec.data_fin is null)))
  318. and an1.pkid not in (select n.id_anagrafe_unica
  319. from Ana_Non_Iscrivere n
  320. where n.flg_annullato=0)
  321. and fp.data_fin is null
  322. and stacit.cod = (( SELECT min(stacit2.cod) AS min
  323. FROM ana_persone_cittadinanza cit2
  324. LEFT JOIN ter_stati stacit2 ON stacit2.static_id = cit2.st_stato_cittadinanza
  325. WHERE cit2.id_persona = percit.id_persona
  326. AND (cit2.pkid IS NULL OR cit2.data_fin IS NULL AND cit2.flg_cessato = 0)
  327. AND (stacit2.pkid IS NULL OR stacit2.data_fin IS NULL AND stacit2.flg_cessato = 0)))
  328. and parm.param_key = 'id_comune_installazione'
  329. -- and ROWNUM <= 2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!