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
| with address_data
as (select P.INSS as INSS
, nvl(A.STREET_ZIPCODE,A.ZIPCODE) AS ZIP
, PA.ADDRESS_TYPE
, PA.START_DATE
from
PERSONS P
, PERIODIC_ADDRESSES PA
, ADDRESSES A
where
P.PERSON_ID = case when PA.ADDRESS_TYPE = 'OFFICIAL' then PA.OFF_ADDR_PERSON_ID else PA.FUN_ADDR_PERSON_ID end
and PA.ADDRESS_TYPE in ('OFFICIAL', 'FUNCTIONAL')
and CURRENT_DATE between PA.START_DATE and nvl(PA.END_DATE,to_date('31/12/9999', 'dd/mm/yyyy'))
and PA.ADDRESS_ID = A.ADDRESS_ID
and A.type = 'STRUCTURED'
),
benef
as(
SELECT DISTINCT
--per.inss AS child_2020
--,
per_al.inss AS benef_2020
FROM
files fil
INNER JOIN children chi ON chi.file_number = fil.file_number
INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (leg.start_year * 100 + leg.start_month) AND NVL((leg.end_year * 100 + leg.end_month),999999))
AND leg.status = 'ACTIVE'
INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (cbl.start_year * 100 + cbl.start_month) AND NVL((cbl.end_year * 100 + cbl.end_month),999999))
INNER JOIN actors ac ON ac.actor_id = chi.actor_id
INNER JOIN persons per ON ac.person_id = per.person_id
INNER JOIN actors ac_al ON ac_al.actor_id = cbl.beneficiary_id
INNER JOIN persons per_al ON ac_al.person_id = per_al.person_id
left JOIN periodic_addresses pad on pad.FUN_ADDR_PERSON_ID = per_al.person_id
left join contactpersons cpe on cpe.ADDRESS_ID = pad.address_id
)
select inss, nvl(zip_off, zip_func) zip
from address_data
pivot (max(zip) keep (dense_rank first order by start_date desc) for address_type in ('OFFICIAL' as zip_off, 'FUNCTIONAL' as zip_func))
right join benef on benef_2020=inss
; |
