PAY Payroll Report (Check list for Unemployment Ins)

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: Payroll Report (Check list for Unemployment Ins) (XML)
Short Name: PAYJPNUI_XML
DB package: PAY_PAYJPNUI_XMLP_PKG
select	uiv.organization_id									ORGANIZATION_ID,
	substrb(hoi.org_information1,1,4) || '-' || substrb(hoi.org_information1,5,6) || '-' || substrb(hoi.org_information1,11,1)
												LOCATION_NUMBER,
	hoi.org_information5 || hoi.org_information6 || hoi.org_information7			EMPLOYER_ADDRESS,
	hoi.org_information9									EMPLOYER_NAME,
	hoi.org_information10									EMPLOYER_FULL_NAME,
	hoi.org_information11									EMPLOYER_TELEPHONE_NUMBER,
	uiv.assignment_id									ASSIGNMENT_ID,
	uiv.person_id										PERSON_ID,
	uiv.business_group_id									BUSINESS_GROUP_ID,
	uiv.effective_date									EFFECTIVE_DATE,
	uiv.report_type										REPORT_TYPE,
--	substrb(uiv.ui_number,1,4) || '-' || substrb(uiv.ui_number,5,6) || '-' || substrb(uiv.ui_number,11,1)
--												UI_NUMBER,
	uiv.target_date										TARGET_DATE,
	pp.employee_number									EMPLOYEE_NUMBER,
	pp.per_information18 || ' ' || pp.per_information19					FULL_NAME,
	pp.last_name || ' ' || pp.first_name							FULL_NAME_KANA,
	substrb(decode(pp.sex,'F','2','1'),1,1)							SEX,
	pp.date_of_birth									DATE_OF_BIRTH,
	uiv.qualified_status									QUALIFIED_STATUS,
	uiv.disqualified_status									DISQUALIFIED_STATUS,
	uiv.unemployed_issue									UNEMPLOYED_ISSUE,
	substrb(decode(uiv.report_type,
		'2',pad.address_line1 || pad.address_line2 || pad.address_line3,
		'3',substrb(hoi2.org_information1,1,4) || '-' || substrb(hoi2.org_information1,5,6) || '-' || substrb(hoi2.org_information1,11,1),
		'4',hoi.org_information8,
		'5',pp2.per_information18 || ' ' || pp2.per_information19 || ' (' || pp2.last_name || ' ' || pp2.first_name || ')'),1,255)
												NOTE,
	PAY_PAYJPNUI_XMLP_PKG.cf_details_dummyformula(uiv.assignment_id, uiv.effective_date, uiv.target_date, pp.date_of_birth)   CF_DETAILS_DUMMY,
	PAY_PAYJPNUI_XMLP_PKG.CP_UI_NUMBER_p CP_UI_NUMBER,
	PAY_PAYJPNUI_XMLP_PKG.CP_TARGET_DATE_p CP_TARGET_DATE,
	PAY_PAYJPNUI_XMLP_PKG.CP_DATE_OF_BIRTH_p CP_DATE_OF_BIRTH,
	PAY_PAYJPNUI_XMLP_PKG.CP_QUALIFIED_DATE_p CP_QUALIFIED_DATE
from	per_all_people_f		pp2,
	per_all_people_f		pp,
	hr_organization_information	hoi2,
	hr_organization_information	hoi,
	per_addresses			pad,
	pay_jp_ui_v			uiv
where	uiv.report_type=:P_REPORT_TYPE
and	uiv.business_group_id=:P_BUSINESS_GROUP_ID
and	uiv.target_date
	between :P_TARGET_DATE_FROM and :P_TARGET_DATE_TO
and	uiv.organization_id=:P_ORGANIZATION_ID
&P_WHERE_CLAUSE_FOR_ASSID
-- For Disqualified
and	pad.person_id(+)=uiv.person_id
and	pad.address_type(+)='JP_C'
and	uiv.effective_date
	between pad.date_from(+) and nvl(pad.date_to(+),uiv.effective_date)
and	hoi.organization_id=uiv.organization_id
and	hoi.org_information_context='JP_LI_UNION_INFO'
-- For Transfer
and	hoi2.organization_id(+)=uiv.organization_id_from
and	hoi2.org_information_context(+)='JP_LI_UNION_INFO'
and	pp.person_id=uiv.person_id
and	uiv.target_date
	between pp.effective_start_date and pp.effective_end_date
-- For Name Changed
and	pp2.person_id(+)=uiv.person_id
and	uiv.effective_date-1
	between pp2.effective_start_date(+) and pp2.effective_end_date(+)
order by target_date, lpad(pp.employee_number,30,' ')
Parameter Name SQL text Validation
PAY_JP_SRS_BUSINESS_GROUP_ID
 
LOV Oracle
Employee10
 
LOV Oracle
Employee9
 
LOV Oracle
Employee8
 
LOV Oracle
Employee7
 
LOV Oracle
Employee6
 
LOV Oracle
Employee5
 
LOV Oracle
Employee4
 
LOV Oracle
Employee3
 
LOV Oracle
Employee2
 
LOV Oracle
Employee1
 
LOV Oracle
Employer
 
LOV Oracle
PAY_JP_SRS_DATE_HIGH
 
Date
PAY_JP_SRS_DATE_LOW
 
Date
Checklist
 
LOV Oracle