PER Invalid Address
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Human Resources
Source: Invalid Address Report (XML)
Short Name: PERRPADD_XML
DB package: PER_PERRPADD_XMLP_PKG
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 | |
---|---|---|---|
Start Date |
|
Date | |
End Date |
|
Date | |
GRE |
|
LOV Oracle |