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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
| create view PERCON_INTERMEDIATE as
with FLAT_EMPLOYEE as
(select ee.person_id, ee.EMP_NAT_REG_NUMBER, pp.birthdate, pp.LAST_NAME, pp.first_name,
EE.EMP_ADMIN_MATRICULE, EE.EMP_WORKING_PERCENT as TAUX_ACTIV,
EE.EMP_MEDICAL_NUMBER as MEDNR, EE.EMP_IN_DATE as DATIN,
EE.EMP_SALARY_IN_DATE as ANCPEC,
home.address_number as home_number, home.street as home_street, home.zip as home_zip, home.city as home_city,
office.phone as office_phone, EE.EMP_EMAIL as email,
pp.language, ee.emp_status_2, ee.emp_admin_state, pp.nationality, PP.GENDER as gender_id, ee.NOK_ID as nok_id,
home.country as home_country_id
from RH_EMPLOYEE ee,RH_PERSON pp, RH_ADDRESS home, RH_ADDRESS office
where ee.PERSON_ID = pp.PERSON_ID
and pp.home_address = home.address_id
and ee.emp_office_address = office.address_id
and ee.EMP_ADMIN_STATE in (13160,13161,13162,13163)
and ee.emp_status_1 = 17265
),
CURRENT_CAREER as
(SELECT r.emp_id,
max(r.career_id) keep (dense_rank last order by rank_start DESC nulls last) AS career_id,
max(r.rank_start) keep (dense_rank last order by rank_start DESC nulls last) as rand_start,
max(r.rank_end) keep (dense_rank last order by rank_start DESC nulls last) as rank_end ,
max(g.pcode) keep (dense_rank last order by rank_start DESC nulls last) AS grade_code,
max(g.glevel) keep (dense_rank last order by rank_start DESC nulls last) AS grade_level,
max(path.junior) keep (dense_rank last order by rank_start DESC nulls last) AS path_jrcode
FROM RH_CAREER_RANK r
left outer join RH_GRADE g
on g.GRADE_ID = r.GRADE_ID
left outer join RH_SELECT_LIST path
on path.entry_id = r.career_path
where (rank_start < sysdate OR rank_start IS NULL)
AND (rank_end > sysdate OR rank_end IS NULL)
GROUP BY emp_id
),
CURRENT_SALARY as
(SELECT r.emp_id,
max(r.salary_start) keep (dense_rank last order by salary_start DESC nulls last) as salary_start,
max(r.salary_end) keep (dense_rank last order by salary_start DESC nulls last) as salary_end ,
max(r.salary_scale) keep (dense_rank last order by salary_start DESC nulls last) AS salary_scale
FROM RH_SALARY_RANK r
where (salary_start < sysdate OR salary_start IS NULL)
AND (salary_end > sysdate OR salary_end IS NULL)
GROUP BY emp_id
),
CURRENT_CONTRACT as
(SELECT c.emp_id,
max(c.CT_START_DATE) keep (dense_rank last order by CT_START_DATE DESC nulls last) as contract_start,
max(c.CT_END_DATE) keep (dense_rank last order by CT_START_DATE DESC nulls last) as contract_end ,
max(compt.entry_name_nl) keep (dense_rank last order by CT_START_DATE DESC nulls last) AS contract_comptability
FROM RH_CONTRACT c
left outer join RH_SELECT_LIST compt
on compt.ENTRY_ID = c.CT_COMPT_SECTION
where (CT_START_DATE < sysdate OR CT_START_DATE IS NULL)
AND (CT_END_DATE > sysdate OR CT_END_DATE IS NULL)
GROUP BY emp_id
)
select
'11' as TRENSFP, contract.contract_end as DRENSFP, e.EMP_ADMIN_MATRICULE as MATRICNR, e.EMP_NAT_REG_NUMBER as NAT_NUM,
extract(YEAR from e.birthdate) as DATENAISS, e.last_name as NOM, e.first_name as prenom, e.home_street ||' ' || e.home_number as RUE,
e.home_zip as PCOD, nat.alpha3 as nat, gender.pcode as gender,
lang.pcode as RLING,
case
when e.emp_status_2 is null then '4'
else statsit.junior
end as statsit,
substr(c.grade_code,-1) as GRORG,
case
when c.grade_level = 's' or c.grade_level = 'S' then 'W'
else c.grade_level
end as NIVEAU,
substr(adminstate.pcode,0,1) as posadmin,
e.TAUX_ACTIV, e.MEDNR, e.DATIN, e.ANCPEC,
salary.salary_scale as BAREM,
CASE
when nok.nok_haardtoelage = 'Y' THEN 1
when nok.nok_standplaatstoelage = 'Y' THEN 2
else 0
END as FOYER_RES,
contract.contract_comptability as NUMART, 'K' as ORGANISME,
e.home_city as COMMUNE, e.office_phone as PHONE, e.email,
c.path_jrcode as FAMILLEFONCTION_NR,
home_country.alpha3 as PAYS_ADRESSE
from FLAT_EMPLOYEE e
left outer join RH_COUNTRY nat
on e.nationality = nat.alpha2
left outer join RH_COUNTRY home_country
on e.home_country_id = home_country.alpha2
left outer join RH_SELECT_LIST gender
on gender.entry_id = e.GENDER_ID
left outer join RH_SELECT_LIST lang
on lang.entry_id = e.language
left outer join RH_SELECT_LIST statsit
on statsit.entry_id = e.emp_status_2
left outer join CURRENT_CAREER c
on c.emp_id = e.person_id
left outer join RH_SELECT_LIST adminstate
on adminstate.entry_id = e.emp_admin_state
left outer join CURRENT_SALARY salary
on salary.emp_id = e.person_id
left outer join RH_NEXTOFKIN nok
on nok.NOK_ID = e.NOK_ID
left outer join CURRENT_CONTRACT contract
on contract.emp_id = e.person_id
order by e.LAST_NAME, E.FIRST_NAME; |
