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

Description
Categories: BI Publisher, Human Resources
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
					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
Sort Order
 
LOV Oracle
Location Address
 
LOV Oracle
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
Output Flag
 
LOV Oracle
Submit to
 
LOV Oracle
Location
 
LOV Oracle
Scheduled Payment Year/Month
 
Bonus Process (Latest)
 
LOV Oracle
PAY_JP_SRS_SI_LOC_BON_EFFECTIVE_DATE
 
Date
Reported Date
 
Date