PER New Hire State

➜ Back to Library
Description
Categories: BI Publisher
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
Run PER New Hire State 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.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
Parameter Name SQL text Validation
PER_DATES_STANDARD 
 
 Date
Government Reporting Entity 
 
 LOV Oracle
State 
 
 LOV Oracle
Dependent Health Insurance 
 
 LOV Oracle
Waiting Period (Days) 
 
 Number
Download
Related Blitz Reports
PER Full Assignment Details BEN Participation Audit Log BEN Back-out Life Events Summary