select a.*,
PER_PERRPADD_XMLP_PKG.cf_addr_chkformula(gre_name, person_id, cnt, address_id, add_date_to, effective_start_date, effective_end_date, date_from, address_line1, location_address, town_or_city, address_line2, address_line3) CF_addr_chk,
PER_PERRPADD_XMLP_PKG.CP_print_gre_p CP_print_gre,
PER_PERRPADD_XMLP_PKG.CP_missing_flag_p CP_missing_flag,
PER_PERRPADD_XMLP_PKG.CP_old_address_id_p CP_old_address_id,
PER_PERRPADD_XMLP_PKG.CP_prev_person_id_p CP_prev_person_id,
PER_PERRPADD_XMLP_PKG.CP_prev_name_p CP_prev_name,
PER_PERRPADD_XMLP_PKG.CP_temp_id_p CP_temp_id,
PER_PERRPADD_XMLP_PKG.CP_prev_gre_name_p CP_prev_gre_name,
PER_PERRPADD_XMLP_PKG.CP_addr_count_p CP_addr_count,
PER_PERRPADD_XMLP_PKG.CP_missing_st_p CP_missing_st,
PER_PERRPADD_XMLP_PKG.CP_missing_end_p CP_missing_end,
PER_PERRPADD_XMLP_PKG.CP_reason_p CP_reason,
PER_PERRPADD_XMLP_PKG.CP_reason1_p CP_reason1,
PER_PERRPADD_XMLP_PKG.CP_missing_st1_p CP_missing_st1,
PER_PERRPADD_XMLP_PKG.CP_missing_end1_p CP_missing_end1,
PER_PERRPADD_XMLP_PKG.CP_keep_count_p CP_keep_count,
PER_PERRPADD_XMLP_PKG.CP_prev_date_to_p CP_prev_date_to
from
(select person_id, effective_start_date, effective_end_date,
address_id, address_line1, location_address, address_line2,
address_line3, town_or_city, add_date_to, date_from, employee_number,
full_name, gre_name,
count(address_id) over( partition by person_id,effective_start_date,effective_end_date,employee_number,full_name) cnt
from (select
people.person_id,
people.effective_start_date,
people.effective_end_date,
addr.address_id,
addr.address_line1,
addr.address_line2 || address_line3 location_address,
addr.address_line2,
addr.address_line3,
addr.town_or_city,
nvl(addr.date_to, TO_DATE('31/12/4712','dd/mm/yyyy')) add_date_to ,
addr.date_from,
people.employee_number,
full_name,
hou.name gre_name
from
hr_soft_coding_keyflex hr1,
hr_organization_units hou,
pay_us_zip_codes zip,
pay_us_city_names city,
pay_us_counties county,
pay_us_states state,
per_assignments_f asg,
per_addresses addr,
per_people_f people
where
to_char(hou.organization_id) = hr1.segment1
and hou.organization_id = nvl(:gre_id, hou.organization_id)
and addr.region_2 = state.state_abbrev
and addr.region_1 = county.county_name
and addr.town_or_city = city.city_name
and substr(addr.postal_code,1,5) between zip.zip_start and zip.zip_end
and
((addr.add_information17 is null and
addr.add_information18 is null and
addr.add_information19 is null and
addr.add_information20 is null) or
exists
(select null
from
pay_us_states st,
pay_us_counties cou,
pay_us_zip_codes zip,
pay_us_city_names cty
where
cou.state_code = st.state_code and
st.state_code = cty.state_code and
zip.state_code = cty.state_code and
zip.county_code = cty.county_code and
zip.city_code = cty.city_code and
substr(addr.add_information20,1,5) between zip.zip_start and zip.zip_end and
st.state_abbrev = addr.add_information17 and
cou.county_name = addr.add_information19 and
cty.city_name = addr.add_information18))
and state.state_code = county.state_code
and county.state_code = city.state_code
and county.county_code = city.county_code
and city.state_code = zip.state_code
and city.county_code = zip.county_code
and city.city_code = zip.city_code
and hr1.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and asg.person_id = people.person_id
and addr.date_from <= :input_end_date
and nvl(addr.date_to, TO_DATE('31/12/4712','dd/mm/yyyy'))
>= :input_start_date
and addr.date_from <= people.effective_end_date
and nvl(addr.date_to, TO_DATE('31/12/4712','dd/mm/yyyy'))
>= people.effective_start_date
and addr.primary_flag = 'Y'
and people.person_id = addr.person_id
and people.effective_start_date <= :input_end_date
and people.effective_end_date >= :input_start_date
and people.business_group_id + 0 = :business_id
and people.current_employee_flag = 'Y'
UNION
select
people.person_id,
people.effective_start_date,
people.effective_end_date,
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_DATE(NULL),
TO_DATE(NULL),
people.employee_number,
full_name,
hou.name gre_name
from
hr_soft_coding_keyflex hr1,
hr_organization_units hou,
per_assignments_f asg,
per_people_f people
where
to_char(hou.organization_id) = hr1.segment1
and hou.organization_id = nvl(:gre_id,hou.organization_id)
and hr1.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and asg.person_id = people.person_id
and people.effective_start_date <= :input_end_date
and people.effective_end_date >= :input_start_date
and people.business_group_id + 0 = :business_id
and people.current_employee_flag = 'Y'
and NOT EXISTS (
select addr_null.address_id
from per_addresses addr_null
where addr_null.person_id = people.person_id
and addr_null.primary_flag = 'Y'
)
)) a order by 14,1,2,3,11 |