PER New Hire State Magnetic Media
Description
Categories: BI Publisher
Imported from BI Publisher
Description: New Hire State Magnetic Media Report
Application: Human Resources
Source: New Hire State Magnetic Media (XML)
Short Name: PERPHIREM_XML
DB package: PER_PERUSHRM_XMLP_PKG
Description: New Hire State Magnetic Media Report
Application: Human Resources
Source: New Hire State Magnetic Media (XML)
Short Name: PERPHIREM_XML
DB package: PER_PERUSHRM_XMLP_PKG
Run
PER New Hire State Magnetic Media and other Oracle EBS reports with Blitz Report™ on our demo environment
select ppf.PERSON_ID ,ppf.LAST_NAME LAST_NAME ,substr(ppf.MIDDLE_NAMES,1,1) MIDDLE_NAME ,ppf.MIDDLE_NAMES FULL_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 NORMAL_HOURS ,paf.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_id ,decode(ppf.per_information9, 'Y', 'Yes', 'N', 'No', null) child_support_flag ,hl.region_2 STATE ,hou.name tax_unit_name ,hoi4.org_information3 sit_company_state_id ,hoi4.org_information2 sui_company_state_id ,hoi3.org_information1 new_hire_contact_id ,hoi4.org_information1 org_info ,replace(hoi2.org_information1,'-',null) federal_id ,hou.location_id gre_location_id ,hl.region_2 hire_state, /* PER_PERUSHRM_XMLP_PKG.cf_greformula(hou.location_id, to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ),to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ),hou.location_id,to_number ( hscf.segment1 ),hou.location_id, to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information3, hou.name,hou.location_id, to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information3,hou.location_id, to_number ( hscf.segment1 )) CF_GRE, */ PER_PERUSHRM_XMLP_PKG.cf_greformula(replace(hoi2.org_information1,'-',null), hou.location_id, to_number(hscf.segment1), hou.name, hoi4.org_information3) CF_GRE, PER_PERUSHRM_XMLP_PKG.c_tax_unit_addressformula(paf.LOCATION_ID) C_tax_unit_address, PER_PERUSHRM_XMLP_PKG.c_contact_titleformula(hoi3.org_information1) C_contact_title, PER_PERUSHRM_XMLP_PKG.c_contact_nameformula(hoi3.org_information1) C_contact_name, PER_PERUSHRM_XMLP_PKG.c_contact_phoneformula(hoi3.org_information1) C_contact_phone, PER_PERUSHRM_XMLP_PKG.CP_pre_tax_unit_id_p CP_pre_tax_unit_id, /*PER_PERUSHRM_XMLP_PKG.cf_new_hireformula(ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ),ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ),ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2,ppf.PERSON_ID,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name, :C_contact_phone, :C_contact_name, hoi4.org_information3,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name, :C_contact_phone, :C_contact_name,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name, :C_contact_phone) CF_new_hire, */ PER_PERUSHRM_XMLP_PKG.cf_new_hireformula(hoi4.org_information2, pps.DATE_START, replace(hoi2.org_information1,'-',null), ppf.NATIONAL_IDENTIFIER, substr(ppf.MIDDLE_NAMES,1,1), hou.location_id, hl.region_2, ppf.PERSON_ID, ppf.LAST_NAME, ppf.FIRST_NAME, ppf.DATE_OF_BIRTH, hou.name, ppf.MIDDLE_NAMES, hoi4.org_information3, PER_PERUSHRM_XMLP_PKG.c_contact_nameformula(hoi3.org_information1), PER_PERUSHRM_XMLP_PKG.c_contact_phoneformula(hoi3.org_information1)) CF_new_hire, PER_PERUSHRM_XMLP_PKG.c_employee_addressformula(ppf.PERSON_ID) C_EMPLOYEE_ADDRESS, PER_PERUSHRM_XMLP_PKG.c_salaryformula(paf.ASSIGNMENT_ID) C_SALARY From per_all_people_f ppf ,per_jobs job ,hr_locations_all hl ,hr_soft_coding_keyflex hscf ,per_all_assignments_f paf ,per_periods_of_service pps ,hr_organization_information hoi4 ,hr_organization_information hoi3 ,hr_organization_information hoi2 ,hr_organization_information hoi1 ,hr_organization_units hou ,hr_locations_all hl2 ,per_all_assignments_f paf2 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 pps.date_start = paf2.effective_start_date and ppf.person_id = paf2.person_id and paf2.location_id = hl2.location_id And :P_REPORT_DATE between paf.effective_start_date and paf.effective_end_date and hscf.segment1 = to_char(hou.organization_id) and hou.business_group_id = :P_BUSINESS_GROUP_ID and hou.organization_id = NVL(:p_tax_unit_id,hou.organization_id) and hl.region_2 = DECODE(:P_MULTI_STATE,'N',:P_STATE_CODE,hl.region_2) And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id And paf.assignment_type = 'E' And paf.primary_flag = 'Y' And paf.location_id = hl.location_id 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 hou.business_group_id = ppf.business_group_id and hoi1.organization_id = hou.organization_id and hoi1.org_information_context = 'CLASS' and hoi1.org_information1 = 'HR_LEGAL' and hoi1.org_information2 ='Y' and hoi2.organization_id(+) = hou.organization_id and hoi2.org_information_context ='Employer Identification' and hoi3.organization_id(+) = hou.organization_id and hoi3.org_information_context(+) = 'New Hire Reporting' and hoi4.organization_id(+) = hou.organization_id and hoi4.org_information_context(+) = 'State Tax Rules' and hoi4.org_information1(+) = nvl(:P_STATE_CODE,hoi4.org_information1(+)) UNION select ppf.PERSON_ID ,ppf.LAST_NAME LAST_NAME ,substr(ppf.MIDDLE_NAMES,1,1) MIDDLE_NAME ,ppf.MIDDLE_NAMES FULL_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 NORMAL_HOURS ,paf.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_id ,decode(ppf.per_information9, 'Y', 'Yes', 'N', 'No', null) child_support_flag ,hl.region_2 STATE ,hou.name tax_unit_name ,hoi4.org_information3 sit_company_state_id ,hoi4.org_information2 sui_company_state_id ,hoi3.org_information1 new_hire_contact_id ,hoi4.org_information1 org_info ,replace(hoi2.org_information1,'-',null) federal_id ,hou.location_id gre_location_id ,hl.region_2 hire_state, /*PER_PERUSHRM_XMLP_PKG.cf_greformula(hou.location_id, to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ),to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ),hou.location_id,to_number ( hscf.segment1 ),hou.location_id, to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information3, hou.name,hou.location_id, to_number ( hscf.segment1 ), replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information3,hou.location_id, to_number ( hscf.segment1 )) CF_GRE, */ PER_PERUSHRM_XMLP_PKG.cf_greformula(replace(hoi2.org_information1,'-',null), hou.location_id, to_number(hscf.segment1), hou.name, hoi4.org_information3) CF_GRE, PER_PERUSHRM_XMLP_PKG.c_tax_unit_addressformula(paf.LOCATION_ID) C_tax_unit_address, PER_PERUSHRM_XMLP_PKG.c_contact_titleformula(hoi3.org_information1) C_contact_title, PER_PERUSHRM_XMLP_PKG.c_contact_nameformula(hoi3.org_information1) C_contact_name, PER_PERUSHRM_XMLP_PKG.c_contact_phoneformula(hoi3.org_information1) C_contact_phone, PER_PERUSHRM_XMLP_PKG.CP_pre_tax_unit_id_p CP_pre_tax_unit_id, /*PER_PERUSHRM_XMLP_PKG.cf_new_hireformula(ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ),ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ),ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2,ppf.PERSON_ID,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name, :C_contact_phone, :C_contact_name, hoi4.org_information3,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name, :C_contact_phone, :C_contact_name,ppf.PERSON_ID, ppf.NATIONAL_IDENTIFIER, ppf.FIRST_NAME, substr ( ppf.MIDDLE_NAMES , 1 , 1 ), ppf.LAST_NAME, pps.DATE_START, ppf.MIDDLE_NAMES, hou.location_id, ppf.DATE_OF_BIRTH, hl.region_2, replace ( hoi2.org_information1 , '-' , null ), hoi4.org_information2, hou.name, :C_contact_phone) CF_new_hire, */ PER_PERUSHRM_XMLP_PKG.cf_new_hireformula(hoi4.org_information2, pps.DATE_START, replace(hoi2.org_information1,'-',null), ppf.NATIONAL_IDENTIFIER, substr(ppf.MIDDLE_NAMES,1,1), hou.location_id, hl.region_2, ppf.PERSON_ID, ppf.LAST_NAME, ppf.FIRST_NAME, ppf.DATE_OF_BIRTH, hou.name, ppf.MIDDLE_NAMES, hoi4.org_information3, PER_PERUSHRM_XMLP_PKG.c_contact_nameformula(hoi3.org_information1), PER_PERUSHRM_XMLP_PKG.c_contact_phoneformula(hoi3.org_information1)) CF_new_hire, PER_PERUSHRM_XMLP_PKG.c_employee_addressformula(ppf.PERSON_ID) C_EMPLOYEE_ADDRESS, PER_PERUSHRM_XMLP_PKG.c_salaryformula(paf.ASSIGNMENT_ID) C_SALARY From per_all_people_f ppf ,hr_locations_all hl ,hr_soft_coding_keyflex hscf ,per_all_assignments_f paf ,per_periods_of_service pps ,hr_organization_information hoi4 ,hr_organization_information hoi3 ,hr_organization_information hoi2 ,hr_organization_information hoi1 ,hr_organization_units hou 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_all_assignments_f paf2 where ppf.person_id = paf2.person_id and :P_REPORT_DATE between paf2.effective_start_date and paf2.effective_end_date ) And pps.date_start = paf.effective_start_date and hscf.segment1 = to_char(hou.organization_id) and hou.business_group_id = :P_BUSINESS_GROUP_ID and hou.organization_id = NVL(:p_tax_unit_id,hou.organization_id) and hl.region_2 = DECODE(:P_MULTI_STATE,'N',:P_STATE_CODE,hl.region_2) And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id And paf.assignment_type = 'E' And paf.primary_flag = 'Y' And paf.location_id = hl.location_id 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 hou.business_group_id = ppf.business_group_id and hoi1.organization_id = hou.organization_id and hoi1.org_information_context = 'CLASS' and hoi1.org_information1 = 'HR_LEGAL' and hoi1.org_information2 ='Y' and hoi2.organization_id(+) = hou.organization_id and hoi2.org_information_context ='Employer Identification' and hoi3.organization_id(+) = hou.organization_id and hoi3.org_information_context(+) = 'New Hire Reporting' and hoi4.organization_id(+) = hou.organization_id and hoi4.org_information_context(+) = 'State Tax Rules' and hoi4.org_information1(+) = nvl(:P_STATE_CODE,hoi4.org_information1(+)) order by tax_unit_id, STATE, LAST_NAME, FIRST_NAME |