PER Invalid Address

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: Invalid Address Report (XML)
Short Name: PERRPADD_XML
DB package: PER_PERRPADD_XMLP_PKG
            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
Parameter Name SQL text Validation
Business Id
 
Number
GRE
 
LOV Oracle
End Date
 
Date
Start Date
 
Date