PAY Payroll Report (Check List of Bonus Payment Notification for Health Ins/Welfare Pension Ins)

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Payroll Report (Check List of Bonus Payment Notification for Health Ins/Welfare Pension Ins)
Application: Payroll
Source: Payroll Report (Check List of Bonus Payment Notification for Health Ins/Welfare Pension Ins) (XML)
Short Name: PAYJPBON_XML
DB package: PAY_PAYJPBON_XMLP_PKG
Run PAY Payroll Report (Check List of Bonus Payment Notification for Health Ins/Welfare Pension Ins) and other Oracle EBS reports with Blitz Report™ on our demo environment
					select	/*+ ORDERED NO_MERGE(pjsbp) */
	pjsbp.si_org_id								loc_id,
--	decode(:p_organization_context,'JP_WP_FUND_INFO',
--		wp.org_information1,hoi.org_information1)			loc_code1,
	decode(:p_organization_context,'JP_WP_FUND_INFO',
		wp.org_information14,hoi.org_information14)			loc_code1_sign,
	decode(:p_organization_context,'JP_WP_FUND_INFO',
		wp.org_information2,hoi.org_information2)			loc_code2,
	hoi.org_information3							loc_number,
	hoi.org_information4							loc_postal_code,
	hoi.org_information5||hoi.org_information6||hoi.org_information7	loc_address,
	hoi.org_information8							loc_name,
	hoi.org_information9							loc_er_name,
	hoi.org_information10							loc_er_phone,
	pjsbp.assignment_action_id	assignment_action_id,
	pjsbp.assignment_id							assignment_id,
	pp.employee_number							employee_number,
	pjsbp.effective_date							effective_date,
	pjsbp.date_earned							date_earned,
	pjsbp.person_id								person_id,
	decode(:p_sort_order,
		'HI_NUMBER',decode(:p_si_submit_type,
			3,decode(pjsbp.si_type,
					2,pjsbp.wp_number,
					6,pjsbp.wp_number,
				pjsbp.hi_number),
			5,decode(pjsbp.si_type,
					4,pjsbp.wp_number,
					6,pjsbp.wp_number,
				pjsbp.hi_number),
			7,decode(pjsbp.si_type,
					2,pjsbp.wp_number,
					4,pjsbp.wp_number,
					6,pjsbp.wp_number,
				pjsbp.hi_number),
			pjsbp.hi_number),
		decode(:p_si_submit_type,
			4,decode(pjsbp.si_type,
					5,pjsbp.hi_number,
					pjsbp.wp_number),
			6,decode(pjsbp.si_type,
					5,pjsbp.hi_number,
					pjsbp.wp_number),
			pjsbp.wp_number))					ins_number,
	decode(:p_sort_order,
		'HI_NUMBER',decode(:p_si_submit_type,
			3,decode(pjsbp.si_type,
					2,'WP_NUMBER',
					6,'WP_NUMBER',
				'HI_NUMBER'),
			5,decode(pjsbp.si_type,
					4,'WP_NUMBER',
					6,'WP_NUMBER',
				'HI_NUMBER'),
			7,decode(pjsbp.si_type,
					2,'WP_NUMBER',
					4,'WP_NUMBER',
					6,'WP_NUMBER',
					'HI_NUMBER'),
			'HI_NUMBER'),
		decode(:p_si_submit_type,
			4,decode(pjsbp.si_type,
					5,'HI_NUMBER',
					'WP_NUMBER'),
			6,decode(pjsbp.si_type,
					5,'HI_NUMBER',
					'WP_NUMBER'),
			'WP_NUMBER'))						sort_order,
	pjsbp.si_type								si_type,
	pp.last_name								last_name_kana,
	pp.first_name								first_name_kana,
	pp.per_information18							last_name,
	pp.per_information19							first_name,
	pp.date_of_birth							date_of_birth,
	PAY_PAYJPBON_XMLP_PKG.CF_DATAFormula(
	decode(:p_sort_order,
			'HI_NUMBER',decode(:p_si_submit_type,
				3,decode(pjsbp.si_type,
						2,'WP_NUMBER',
						6,'WP_NUMBER',
					'HI_NUMBER'),
				5,decode(pjsbp.si_type,
						4,'WP_NUMBER',
						6,'WP_NUMBER',
					'HI_NUMBER'),
				7,decode(pjsbp.si_type,
						2,'WP_NUMBER',
						4,'WP_NUMBER',
						6,'WP_NUMBER',
						'HI_NUMBER'),
				'HI_NUMBER'),
			decode(:p_si_submit_type,
				4,decode(pjsbp.si_type,
						5,'HI_NUMBER',
						'WP_NUMBER'),
				6,decode(pjsbp.si_type,
						5,'HI_NUMBER',
						'WP_NUMBER'),
			'WP_NUMBER')),
	pjsbp.si_type,
	pp.date_of_birth,
	pjsbp.effective_date,
	pjsbp.assignment_action_id,
	pjsbp.assignment_id,
	pp.per_information18,
	pp.per_information19,
	decode(:p_sort_order,
			'HI_NUMBER',decode(:p_si_submit_type,
				3,decode(pjsbp.si_type,
						2,pjsbp.wp_number,
						6,pjsbp.wp_number,
					pjsbp.hi_number),
				5,decode(pjsbp.si_type,
						4,pjsbp.wp_number,
						6,pjsbp.wp_number,
					pjsbp.hi_number),
				7,decode(pjsbp.si_type,
						2,pjsbp.wp_number,
						4,pjsbp.wp_number,
						6,pjsbp.wp_number,
					pjsbp.hi_number),
				pjsbp.hi_number),
			decode(:p_si_submit_type,
				4,decode(pjsbp.si_type,
						5,pjsbp.hi_number,
						pjsbp.wp_number),
				6,decode(pjsbp.si_type,
						5,pjsbp.hi_number,
						pjsbp.wp_number),
			pjsbp.wp_number)),
	pp.last_name,
	pp.first_name
	) CF_DATA,
	PAY_PAYJPBON_XMLP_PKG.cp_birth_date_dd_p() cp_birth_date_dd,
	PAY_PAYJPBON_XMLP_PKG.cp_birth_date_era_p() cp_birth_date_era,
	PAY_PAYJPBON_XMLP_PKG.cp_birth_date_erayymmdd_p() cp_birth_date_erayymmdd,
	PAY_PAYJPBON_XMLP_PKG.cp_birth_date_mm_p() cp_birth_date_mm,
	PAY_PAYJPBON_XMLP_PKG.cp_birth_date_yy_p() cp_birth_date_yy,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_comp_p() cp_bon_comp,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_comp_total_p() cp_bon_comp_total,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_mtr_comp_p() cp_bon_mtr_comp,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_payment_date_p() cp_bon_payment_date,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_payment_date_dd_p() cp_bon_payment_date_dd,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_payment_date_mm_p() cp_bon_payment_date_mm,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_payment_date_yy_p() cp_bon_payment_date_yy,
	PAY_PAYJPBON_XMLP_PKG.cp_bon_payment_date_yymmdd_p() cp_bon_payment_date_yymmdd,
	PAY_PAYJPBON_XMLP_PKG.cp_d_birth_date_era_p() cp_d_birth_date_era,
	PAY_PAYJPBON_XMLP_PKG.cp_d_bon_comp_total_p() cp_d_bon_comp_total,
	PAY_PAYJPBON_XMLP_PKG.cp_emp_failure_item_p() cp_emp_failure_item,
	PAY_PAYJPBON_XMLP_PKG.cp_error_message_p() cp_error_message,
	PAY_PAYJPBON_XMLP_PKG.cp_exclude_p() cp_exclude,
	PAY_PAYJPBON_XMLP_PKG.cp_full_name_p() cp_full_name,
	PAY_PAYJPBON_XMLP_PKG.cp_hi_only_p() cp_hi_only,
	PAY_PAYJPBON_XMLP_PKG.cp_si_sex_code_p() cp_si_sex_code,
	PAY_PAYJPBON_XMLP_PKG.cp_wp_only_p() cp_wp_only
from	pay_payroll_actions		ppa,
	pay_jp_si_bon_payment_v		pjsbp,
	hr_organization_information	wp,
	hr_organization_information	hoi,
	per_all_assignments_f		pa,
	per_all_people_f		pp
where	ppa.payroll_action_id = :p_payroll_action_id
and	ppa.business_group_id = :p_business_group_id
and	pjsbp.business_group_id = ppa.business_group_id
&p_where_clause_for_assid
and	pjsbp.si_org_id = :p_organization_id
and	((:p_si_submit_type = 1
		and	pjsbp.si_type in (1,3,5,7))
		or
		(:p_si_submit_type = 2
		and	pjsbp.si_type in (2,3,6,7))
		or
		(:p_si_submit_type = 3
		and	pjsbp.si_type in (1,2,3,5,6,7))
		or
		(:p_si_submit_type = 4
		and	pjsbp.si_type in (4,5,6,7))
		or
		(:p_si_submit_type = 5
		and	pjsbp.si_type in (1,3,4,5,6,7))
		or
		(:p_si_submit_type = 6
		and	pjsbp.si_type in (2,3,4,5,6,7))
		or
		(:p_si_submit_type = 7
		and	pjsbp.si_type in (1,2,3,4,5,6,7)))
and	((		pay_core_utils.get_parameter('SI_SUM_MTD_BON', ppa.legislative_parameters) = 'N'
		and pjsbp.payroll_action_id = ppa.payroll_action_id)
		or
		(		pay_core_utils.get_parameter('SI_SUM_MTD_BON', ppa.legislative_parameters) = 'Y'
		and	pjsbp.effective_date
				between trunc(ppa.effective_date,'MM') and ppa.effective_date
        and pjsbp.si_sum_mtd_bon = 'Y'))
and	nvl(substrb(pay_jp_balance_pkg.get_entry_value_char(
			hr_jp_id_pkg.input_value_id(
				'COM_SI_REPORT_INFO',
				'OUTPUT_FLAG',
				NULL,'JP','FALSE'),
			pjsbp.assignment_id,
			pjsbp.effective_date),1,1),'N')
	 = decode(:p_output_flag,
			1,'N',
			2,'Y',
			nvl(substrb(pay_jp_balance_pkg.get_entry_value_char(
				hr_jp_id_pkg.input_value_id(
					'COM_SI_REPORT_INFO',
					'OUTPUT_FLAG',
					NULL,'JP','FALSE'),
				pjsbp.assignment_id,
				pjsbp.effective_date),1,1),'N'))
and	not exists(
		select	/*+ ORDERED
								INDEX(PPA2 PAY_PAYROLL_ACTIONS_FK6) */
						null
		from		pay_payroll_actions			ppa2,
						pay_assignment_actions	paa2
		where		ppa2.element_set_id = ppa.element_set_id
		and			ppa2.effective_date
						between trunc(ppa.effective_date,'MM') and ppa.effective_date
		and			ppa2.business_group_id = ppa.business_group_id
		and			ppa2.action_type = 'R'
		and			paa2.payroll_action_id = ppa2.payroll_action_id
		and			paa2.action_status = 'C'
		and			paa2.assignment_id = pjsbp.assignment_id
		and			pay_core_utils.get_parameter('SI_SUM_MTD_BON', ppa2.legislative_parameters) = pay_core_utils.get_parameter('SI_SUM_MTD_BON', ppa.legislative_parameters)
		and			paa2.action_sequence > pjsbp.action_sequence)
and	wp.organization_id (+) = pjsbp.si_org_id
and	wp.org_information_context (+) = 'JP_WP_UNION_INFO'
and	hoi.organization_id (+) = pjsbp.si_org_id
and	hoi.org_information_context (+) = :p_organization_context
and	pa.assignment_id = pjsbp.assignment_id
--and	pa.primary_flag = 'Y'/*commented by JSAJJA as per Bug no 8435426*/
and	pjsbp.effective_date /* Bonus Process Date */
		between pa.effective_start_date and pa.effective_end_date
and	pp.person_id = pa.person_id
and	pjsbp.effective_date /* Bonus Process Date */
	between pp.effective_start_date and pp.effective_end_date
order by
	lpad(decode(:p_sort_order,
		'HI_NUMBER',decode(:p_si_submit_type,
			3,decode(pjsbp.si_type,
					2,pjsbp.wp_number,
					6,pjsbp.wp_number,
				pjsbp.hi_number),
			5,decode(pjsbp.si_type,
					4,pjsbp.wp_number,
					6,pjsbp.wp_number,
				pjsbp.hi_number),
			7,decode(pjsbp.si_type,
					2,pjsbp.wp_number,
					4,pjsbp.wp_number,
					6,pjsbp.wp_number,
				pjsbp.hi_number),
			pjsbp.hi_number),
		decode(:p_si_submit_type,
			4,decode(pjsbp.si_type,
					5,pjsbp.hi_number,
					pjsbp.wp_number),
			6,decode(pjsbp.si_type,
					5,pjsbp.hi_number,
					pjsbp.wp_number),
			pjsbp.wp_number)),10,' ')
Parameter Name SQL text Validation
Reported Date 
 
 Date
PAY_JP_SRS_SI_LOC_BON_EFFECTIVE_DATE 
 
 Date
Bonus Process (Latest) 
 
 LOV Oracle
Scheduled Payment Year/Month 
 
 Char
Location 
 
 LOV Oracle
Submit to 
 
 LOV Oracle
Output Flag 
 
 LOV Oracle
Employee1 
 
 LOV Oracle
Employee2 
 
 LOV Oracle
Employee3 
 
 LOV Oracle
Employee4 
 
 LOV Oracle
Employee5 
 
 LOV Oracle
Employee6 
 
 LOV Oracle
Employee7 
 
 LOV Oracle
Employee8 
 
 LOV Oracle
Employee9 
 
 LOV Oracle
Employee10 
 
 LOV Oracle
Download
Related Blitz Reports
BEN Reconciliation of Premium Contributions to Element Entries GL Account Analysis PAY Inland Revenue Remittance Certificate PAY Void Payments PAY Business Payrolls Survey PAY RTI - Year End P60 Report Process PAY RTI - Year End P60 Report Process (Multi Threaded) PAY Payroll Reconciliation (New Zealand) PAY Employee Payroll History
Blitz Report™

Blitz Report™ provides multiple benefits: