PAY Payroll Report (Check list for Unemployment Ins)

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,' ')