ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4718651A9E05362FB09050512Y select ppf.PERSON_ID ,ppf.LAST_NAME LAST_NAME ,substr(ppf.MIDDLE_NAMES,1,1) MIDDLE_NAME ,ppf.FIRST_NAME FIRST_NAME ,ppf.NATIONAL_IDENTIFIER ,ppf.DATE_OF_BIRTH ,pps.DATE_START ,ppf.TITLE ,substr(ppf.sex,1,1) GENDER ,paf.ASSIGNMENT_ID ,paf.ASSIGNMENT_STATUS_TYPE_ID ,paf.LOCATION_ID ,paf.SOFT_CODING_KEYFLEX_ID ,paf.ASSIGNMENT_TYPE ,paf.PRIMARY_FLAG ,substr(paf.employment_category,1,1) full_part_time ,paf.NORMAL_HOURS ,decode(ptpt.number_per_fiscal_year ,6 ,'BM' ,26 ,'BW' ,12 ,'M' ,24 ,'SM' ,52 ,'W' ,2 ,'SY' ,1 ,'Y' ,'') FREQUENCY ,round(decode(paf.frequency, 'D', paf.normal_hours*5, 'W', paf.normal_hours, 'M', paf.normal_hours/4, 'Y', paf.normal_hours/52, 0)) hours_per_week ,paf.PAY_BASIS_ID ,to_number(hscf.segment1) tax_unit_id1 ,jbt.name job_name ,decode(ppf.per_information9, 'Y', 'Yes', 'N', 'No', null) child_support_flag /** ,decode(:P_MEDICAL_AVAIL, 'Y', to_char(ppf.start_date + nvl(:P_WAITING_PERIOD,0)) ,'Not Avail') health_ins_date **/ /* ,decode(:P_MEDICAL_AVAIL,'Y',decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)),'F',to_char(last_day(pps.DATE_START)+1),'Not Avail'),'Not Avail') health_ins_date -- bug 13411503 */ /* ,decode(:P_MEDICAL_AVAIL,'Y',decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)),'F',to_char(last_day(pps.DATE_START + nvl(hoi.org_information4,0))+1),'Not Avail'),'Not Avail') health_ins_date --bug 14360558*/ ,decode(:P_MEDICAL_AVAIL ,'Y' ,decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)) ,'F',to_char(last_day(pps.DATE_START + nvl(hoi.org_information4,0))+1) ,'Not Avail') ,'E' ,decode(ppf.per_information7,'INCL',decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)) ,'F',to_char(last_day(pps.DATE_START+ nvl(hoi.org_information4,0))+1) ,'Not Avail') ,'Not Avail') ,'Not Avail') health_ins_date --bug 14532612 ,hl.region_2 STATE, PER_PERUSHIR_XMLP_PKG.c_employee_addressformula(ppf.PERSON_ID) C_EMPLOYEE_ADDRESS, PER_PERUSHIR_XMLP_PKG.c_salaryformula(paf.ASSIGNMENT_ID) C_SALARY From per_all_people_f ppf ,per_jobs job ,per_jobs_tl jbt , hr_locations_all hl ,hr_soft_coding_keyflex hscf ,per_all_assignments_f paf ,per_periods_of_service pps ,pay_all_payrolls_f pay ,per_time_period_types ptpt ,hr_organization_information hoi --BUG 13411503 WHERE (pps.person_id = ppf.person_id AND :p_report_date BETWEEN pps.date_start AND nvl(pps.actual_termination_date, :c_end_of_time) AND :p_report_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND ppf.person_id = paf.person_id AND :p_report_date BETWEEN paf.effective_start_date AND paf.effective_end_date And hscf.segment1 = to_char(:TAX_UNIT_ID) AND paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id AND paf.assignment_type = 'E' AND paf.primary_flag = 'Y' AND paf.payroll_id = pay.payroll_id(+) AND :p_report_date BETWEEN pay.effective_start_date(+) AND pay.effective_end_date(+) AND pay.period_type = ptpt.period_type(+) AND paf.location_id = hl.location_id AND hl.region_2 = nvl(:p_state_code, hl.region_2) AND paf.job_id = job.job_id(+) AND paf.job_id = jbt.job_id(+) AND jbt.LANGUAGE(+) = userenv('LANG') AND :p_report_date BETWEEN nvl(job.date_from, :p_report_date) AND nvl(job.date_to, :c_end_of_time) AND ppf.business_group_id + 0 = :p_business_group_id AND ppf.per_information_category = 'US' AND pps.date_start <= :p_report_date --AND ppf.per_information7 = 'INCL' AND ppf.per_information7 IN ('INCL','INCLWH') --bug 14532612 --Start of Bug 13411503 AND hoi.organization_id(+) = hscf.segment1 AND hoi.org_information_context(+) = 'New Hire Reporting' --End of Bug 13411503 AND((:org_info IS NOT NULL AND hl.region_2 = RTRIM(:org_info)) OR(:org_info = 'ZZ' AND NOT EXISTS (SELECT 1 FROM hr_organization_information WHERE organization_id = :tax_unit_id AND org_information_context = 'State Tax Rules' AND org_information1 = hl.region_2)))) AND(to_char(:tax_unit_id) = hscf.segment1) UNION select ppf.PERSON_ID ,ppf.LAST_NAME LAST_NAME ,substr(ppf.MIDDLE_NAMES,1,1) MIDDLE_NAME ,ppf.FIRST_NAME FIRST_NAME ,ppf.NATIONAL_IDENTIFIER ,ppf.DATE_OF_BIRTH ,pps.DATE_START ,ppf.TITLE ,substr(ppf.sex,1,1) GENDER ,paf.ASSIGNMENT_ID ,paf.ASSIGNMENT_STATUS_TYPE_ID ,paf.LOCATION_ID ,paf.SOFT_CODING_KEYFLEX_ID ,paf.ASSIGNMENT_TYPE ,paf.PRIMARY_FLAG ,substr(paf.employment_category,1,1) full_part_time ,paf.NORMAL_HOURS ,decode(ptpt.number_per_fiscal_year ,6 ,'BM' ,26 ,'BW' ,12 ,'M' ,24 ,'SM' ,52 ,'W' ,2 ,'SY' ,1 ,'Y' ,'') FREQUENCY ,round(decode(paf.frequency, 'D', paf.normal_hours*5, 'W', paf.normal_hours, 'M', paf.normal_hours/4, 'Y', paf.normal_hours/52, 0)) hours_per_week ,paf.PAY_BASIS_ID ,to_number(hscf.segment1) tax_unit_id1 ,jbt.name job_name ,decode(ppf.per_information9, 'Y', 'Yes', 'N', 'No', null) child_support_flag /**,decode(:P_MEDICAL_AVAIL, 'Y', to_char(ppf.start_date + nvl(:P_WAITING_PERIOD,0)) ,'Not Avail') health_ins_date **/ /* ,decode(:P_MEDICAL_AVAIL,'Y',decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)),'F',to_char(last_day(pps.DATE_START)+1),'Not Avail'),'Not Avail') health_ins_date --bug 13411503*/ /* ,decode(:P_MEDICAL_AVAIL,'Y',decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)),'F',to_char(last_day(pps.DATE_START + nvl(hoi.org_information4,0))+1),'Not Avail'),'Not Avail') health_ins_date --bug 14360558*/ ,decode(:P_MEDICAL_AVAIL ,'Y' ,decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)) ,'F',to_char(last_day(pps.DATE_START + nvl(hoi.org_information4,0))+1) ,'Not Avail') ,'E' ,decode(ppf.per_information7,'INCL',decode(hoi.org_information3,'D',to_char(pps.DATE_START + nvl(hoi.org_information4,0)) ,'F',to_char(last_day(pps.DATE_START+ nvl(hoi.org_information4,0))+1) ,'Not Avail') ,'Not Avail') ,'Not Avail') health_ins_date --bug 14532612 ,hl.region_2 STATE, PER_PERUSHIR_XMLP_PKG.c_employee_addressformula(ppf.PERSON_ID) C_EMPLOYEE_ADDRESS, PER_PERUSHIR_XMLP_PKG.c_salaryformula(paf.ASSIGNMENT_ID) C_SALARY From per_all_people_f ppf ,per_jobs job ,per_jobs_tl jbt ,hr_locations_all hl ,hr_soft_coding_keyflex hscf ,per_all_assignments_f paf ,per_periods_of_service pps ,pay_all_payrolls_f pay ,per_time_period_types ptpt ,hr_organization_information hoi --BUG 13411503 Where (pps.person_id = ppf.person_id And :P_REPORT_DATE between ppf.effective_start_date and ppf.effective_end_date And pps.actual_termination_date IS NOT NULL And ppf.person_id = paf.person_id And not exists ( select 1 from per_periods_of_service pps2 where pps2.person_id = ppf.person_id and :P_REPORT_DATE between pps2.date_start and nvl(pps2.actual_termination_date,:C_END_OF_TIME) ) And pps.date_start = paf.effective_start_date And hscf.segment1 = to_char(:TAX_UNIT_ID) And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id And paf.assignment_type = 'E' And paf.primary_flag = 'Y' And paf.payroll_id = pay.payroll_id(+) And :P_REPORT_DATE between pay.effective_start_date(+) and pay.effective_end_date(+) And pay.period_type = ptpt.period_type(+) And paf.location_id = hl.location_id And hl.region_2 = nvl(:P_STATE_CODE,hl.region_2) And paf.job_id = job.job_id(+) And paf.job_id = jbt.job_id(+) And jbt.language(+) = userenv('LANG') And :P_REPORT_DATE between nvl(job.date_from, :P_REPORT_DATE) and nvl(job.date_to, :C_END_OF_TIME) And ppf.business_group_id +0 = :P_BUSINESS_GROUP_ID And ppf.per_information_category = 'US' And pps.date_start <= :P_REPORT_DATE --And ppf.per_information7 = 'INCL' And ppf.per_information7 IN ('INCL','INCLWH') --bug 14532612 --Start of Bug 13411503 And hoi.organization_id(+) = hscf.segment1 And hoi.org_information_context(+) = 'New Hire Reporting' --End of Bug 13411503 and ((:ORG_INFO IS NOT NULL AND hl.region_2 = rtrim(:ORG_INFO)) OR (:ORG_INFO = 'ZZ' AND NOT EXISTS(SELECT 1 FROM HR_ORGANIZATION_INFORMATION WHERE organization_id = :TAX_UNIT_ID AND org_information_context = 'State Tax Rules' AND org_information1 = hl.region_2))) ) and (hscf.segment1 =to_char(:tax_unit_id)) Order by STATE, LAST_NAME, FIRST_NAME PER PERPHIRE_XML US PER New Hire State Imported from BI Publisher Description: New Hire State Report Application: Human Resources Source: New Hire State Report (XML) Short Name: PERPHIRE_XML DB package: PER_PERUSHIR_XMLP_PKG BI Publisher :c_business_group_name :c_end_of_time :c_end_of_time_v :c_medical_avail :c_report_subtitle :c_state_name :c_tax_unit :flag1 :flag2 :org_info :p_business_group_id :p_conc_request_id :p_medical_avail :p_report_date :p_report_date_t :p_report_mode :p_state_code :p_tax_unit_id :p_waiting_period :tax_unit_id 1 -30 :p_business_group_id LOV Oracle HR_US_BUSINESS_GROUP_NAME Y select hou.business_group_id id, hou.name value, null description from hr_organization_units hou where hou.business_group_id= fnd_global.per_business_group_id and hou.organization_id=fnd_global.per_business_group_id order by hou.name SELECT hou.name FROM hr_organization_units hou WHERE hou.business_group_id = fnd_global.per_business_group_id AND hou.organization_id = fnd_global.per_business_group_id Y US Business Group 2 -20 :p_report_mode LOV Oracle HR_US_REPORT_MODE Y select ffvv.flex_value id, ffvv.flex_value value, ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description from fnd_flex_values_vl ffvv where ffvv.flex_value_set_id=1015174 order by ffvv.flex_value D US Report Mode: Final (F) or Draft (D) 3 10 :p_report_date Date select fnd_date.date_to_displaydate(effective_date) from fnd_sessions where session_id = userenv('sessionid') Y US PER_DATES_STANDARD 4 20 :p_tax_unit_id LOV Oracle HR_US_TAX_UNIT_DATE_EFF Y select tax_unit_id id, name value, null description from hr_tax_units_v where business_group_id=fnd_global.per_business_group_id and date_from<=to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') and (date_to is null or (date_to is not null and date_to>= to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') )) order by name Y US Government Reporting Entity 5 30 :p_state_code LOV Oracle PER_US_STATES Y select lookup_code id, lookup_code value, meaning description from fnd_common_lookups where lookup_type='US_STATE' order by lookup_code US State 6 40 :p_medical_avail LOV Oracle HR_US_NEW_HIRE_MEDICAL_YES_NO Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='US_DEP_HEALTH_CRITERIA' order by value,description US Dependent Health Insurance 7 50 :p_waiting_period Number US Waiting Period (Days)