PER New Hire State Magnetic Media

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