PER New Hire State

Description
Categories: BI Publisher
Columns: Person Id, Last Name, Middle Name, First Name, National Identifier, Date Of Birth, Date Start, Title, Gender, Assignment Id ...
Application: Human Resources
Source: New Hire State Report (XML)
Short Name: PERPHIRE_XML
DB package: PER_PERUSHIR_XMLP_PKG
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