APPS.PAY_NL_WTS_REPORT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 10

SELECT paa.assignment_action_id assignment_action_id
FROM
	pay_assignment_actions paa,
	pay_payroll_actions ppa,
	per_all_assignments_f pas
WHERE
	pas.person_id = l_person_id
AND 	pas.payroll_id = l_payroll_id
AND 	ppa.date_earned = l_date_earned
AND	pas.assignment_id = paa.assignment_id
AND 	ppa.payroll_id = pas.payroll_id
AND 	paa.payroll_action_id = ppa.payroll_action_id
AND	paa.action_status='C'
AND 	ppa.action_type in ('R','Q','V','B','I')
AND 	ppa.date_earned between pas.effective_start_date and pas.effective_end_date
	and exists(select * from pay_run_results
		   where assignment_action_id=paa.assignment_action_id
		   and (element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'), Get_Element_Type_Id('Wage Tax Subsidy Education')
		       , Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed') , Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))
		   or 	element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')
		       ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))))
		   order by assignment_action_id desc;

Line: 47

Select org_structure_version_id
From per_org_structure_versions posv
Where organization_structure_id=l_org_struct_id
and to_date(l_month_to,'MMYYYY') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time);

Line: 66

select element_type_id
from pay_element_types_F
where element_name = l_element_name
and legislation_code = 'NL';

Line: 85

select round(input_value_id,2)
from pay_input_values_f
where name=l_input_value
and element_type_id=l_element_type_id
and legislation_code='NL';

Line: 105

select pdb.defined_balance_id
from	pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb
where  pbt.balance_type_id = pdb.balance_type_id
and pbt.balance_name =l_balance_name
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.database_item_suffix='_PER_PAY_PTD'
and pbt.legislation_code='NL';

Line: 163

select	DISTINCT
	prr.run_result_id,
	to_number(pay_nl_general.GET_RUN_RESULT_VALUE(paa.assignment_action_id,l_element_type_id,
        decode(l_element_type_id,
        Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id, 'M')) retro_wts
from	pay_assignment_actions	paa,
	pay_assignment_actions	paa1,
	per_all_assignments_f	pas,
	per_all_assignments_f	pas1,
	pay_payroll_actions	ppa,
	pay_payroll_actions	ppa1,
	pay_run_results		prr
where	paa1.assignment_action_id = l_asg_act_id
and	pas1.assignment_id = paa1.assignment_id
and	pas.person_id = pas1.person_id
and	paa.assignment_id = pas.assignment_id
and	ppa1.payroll_action_id = paa1.payroll_action_id
and	ppa.payroll_action_id = paa.payroll_action_id
and	ppa.payroll_id = pas.payroll_id
and	paa.action_status = 'C'
and	ppa.action_type in ('R','Q','V','B','I')
and	ppa.date_earned between pas.effective_start_date and pas.effective_end_date
and	ppa.time_period_id = ppa1.time_period_id
and	prr.assignment_action_id = paa.assignment_action_id
and	prr.element_type_id = l_element_type_id
and	nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) = l_retro_date;

Line: 230

SELECT	DISTINCT
	hou.name employer_name,
	pap.full_name||'('||pap.employee_number||')' employee,
	hoi.org_information3 tax_office_id,
	hoi.org_information4 tax_reg,
	ppa.business_group_id business_group_id,
                hou1.name,
	paa.person_id,
	ppa.date_earned,
	ppa.payroll_id
from
	per_assignments_f paa,
	pay_payroll_actions ppa,
	per_people_f     pap,
	hr_organization_units hou,
        hr_organization_units hou1,
	hr_organization_information hoi,
	pay_assignment_actions asg_act
where
	ppa.business_group_id=p_bg_id
	and paa.assignment_id = asg_act.assignment_id
	and pap.person_id = paa.person_id
	and ppa.payroll_action_id = asg_act.payroll_action_id
	and asg_act.action_status='C'
	and ppa.action_type in ('R','Q','V','B','I')
	and ppa.date_earned between to_date(p_month_from,'MMYYYY')  AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
	and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
	and paa.organization_id in
                ((SELECT pose.organization_id_child
                  FROM   per_org_structure_elements pose
                  WHERE pose.org_structure_version_id = GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to)
                  START with pose.organization_id_parent = nvl(p_top_org_id,p_bg_id)
	          CONNECT BY prior organization_id_child = organization_id_parent )
                  union
                 (select nvl(p_top_org_id,p_bg_id) from dual))
                and pap.person_id=nvl(p_person_id,pap.person_id)
                and   ((p_top_org_id is NULL)  or (nvl(p_inc_sub_emp,'N') = 'N' and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)=p_top_org_id) or (nvl(p_inc_sub_emp,'N') = 'Y'))
	and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id) is
	not null
	and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))  between pap.effective_start_date and pap.effective_end_date
	and hou.business_group_id=p_bg_id
                and hou1.organization_id = hoi.org_information3
	and hou.organization_id=hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)
	and hoi.organization_id=hou.organization_id
                and hoi.org_information3 IS NOT NULL
                and hoi.org_information4 IS NOT NULL
	and hoi.org_information_context='NL_ORG_INFORMATION'
	and exists(select * from pay_run_results
		   where assignment_action_id=asg_act.assignment_action_id
		   and (element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'), Get_Element_Type_Id('Wage Tax Subsidy Education') , Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
		       , Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))
		   or 	element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Element_Type_Id('Retro Wage Tax Subsidy Education')
		       ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))))
	order by employer_name , paa.person_id,ppa.payroll_id,ppa.date_earned;

Line: 285

SELECT	DISTINCT
	pap.full_name||' ('||pap.employee_number||')' employee_name,
        to_char(ppa.date_earned,'MonthYYYY') current_period,
	paa.assignment_id,
	pay.payroll_name payroll_name,
	ppa.date_earned,
	abs(pay_balance_pkg.get_value(decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Defined_Balance_Id('Wage Tax Subsidy Low Wages'),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Defined_Balance_Id('Wage Tax Subsidy Education'),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Defined_Balance_Id('Wage Tax Subsidy Long Term Unemployed'),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Defined_Balance_Id('Wage Tax Subsidy Paid Parental Leave')),asg_act.assignment_action_id)) Wage_Tax_Subsidy,
	paa.assignment_number,
        to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Education')) ,
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Parental Leave Hours',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'N')) Working_Hours
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'), Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Part_Time_Percentage
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Wage_Limit
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Basis_Salary,
	prr.element_type_id Subsidy_Element_Type_ID,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),1,
	Get_Element_Type_Id('Wage Tax Subsidy Education'),2,
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),3,
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),4) Sequence
from
	per_assignments_f paa,
	pay_payroll_actions ppa,
	per_people_f pap,
	pay_assignment_actions asg_act,
	pay_run_results prr,
	pay_all_payrolls_f pay
where
	ppa.business_group_id=p_bg_id
	and asg_act.assignment_action_id = l_asg_act_id
	and paa.assignment_id = asg_act.assignment_id
	and pay.payroll_id = ppa.payroll_id
	and pap.person_id = paa.person_id
	and ppa.payroll_action_id = asg_act.payroll_action_id
	and asg_act.action_status='C'
	and ppa.action_type in ('R','Q','V','B','I')
	and ppa.date_earned between to_date(p_month_from,'MMYYYY')  AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
	and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
	and prr.assignment_action_id=asg_act.assignment_action_id
	and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))  between pap.effective_start_date and pap.effective_end_date
	and prr.element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))
	and exists(select * from pay_run_results where assignment_action_id=asg_act.assignment_action_id and element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Element_Type_Id('Wage Tax Subsidy Education')
	                                                                   ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')))
	and (to_number(pay_nl_general.get_run_result_value(asg_act.assignment_action_id,prr.Element_Type_Id,
	              decode(prr.element_type_id,Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),Get_Element_Type_Id('Wage Tax Subsidy Education')
	              ,Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'))
	              ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M'))) is not null
	order by employee_name, ppa.date_earned , Sequence;

Line: 357

SELECT	DISTINCT
	pap.full_name||' ('||pap.employee_number||')' employee_name,
	get_retro_wts(prr.assignment_action_id, prr.element_type_id, nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned))*(-1) Retro_WTS,
	paa.assignment_number,
	pay.payroll_name payroll_name,
	paa.assignment_id,
        to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
        decode(prr.element_type_id,
        Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_value_Id('Parental Leave Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'N')) Retro_Working_Hours
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Part_Time_Percentage
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Wage_Limit
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Basis_Salary
	                     ,to_char(nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned),'MonthYYYY') Retro_Period
	,nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) RDate,
	nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) Retro_Date,
	prr.element_type_id Retro_Subsidy_Element_Type_ID,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),1,Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),3,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),2,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),4) Sequence
from
	per_assignments_f paa,
	per_people_f     pap,
	pay_assignment_actions asg_act,
	pay_run_results prr,
	pay_payroll_actions ppa,
	pay_all_payrolls_f pay
where
	ppa.business_group_id=p_bg_id
	and paa.assignment_id = asg_act.assignment_id
	and pap.person_id = paa.person_id
	and pay.payroll_id = ppa.payroll_id
	and asg_act.assignment_action_id = l_asg_act_id
	and ppa.payroll_action_id = asg_act.payroll_action_id
	and asg_act.action_status='C'
	and ppa.action_type in ('R','Q','V','B','I')
	and ppa.date_earned between to_date(p_month_from,'MMYYYY')  AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
	and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
	and prr.assignment_action_id=asg_act.assignment_action_id
	and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))  between pap.effective_start_date and pap.effective_end_date
	and prr.element_type_id  in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
	                             Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Element_Type_Id('Retro Wage Tax Subsidy Education')
	                             ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))
	and exists(select * from pay_run_results where assignment_action_id=asg_act.assignment_action_id and 	element_type_id
	                  in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')
	                      ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave')) )
	order by employee_name, RDate , Sequence;

Line: 426

SELECT name FROM per_business_groups
WHERE
BUSINESS_GROUP_ID = l_bg_id;

Line: 763

SELECT	DISTINCT
	hou.name employer_name,
	pap.full_name||'('||pap.employee_number||')' employee,
	hoi.org_information3 tax_office_id,
	hoi.org_information4 tax_reg,
	ppa.business_group_id business_group_id,
                hou1.name,
	paa.person_id,
	ppa.date_earned,
	ppa.payroll_id
from
	per_assignments_f paa,
	pay_payroll_actions ppa,
	per_people_f     pap,
	hr_organization_units hou,
        hr_organization_units hou1,
	hr_organization_information hoi,
	pay_assignment_actions asg_act
where
	ppa.business_group_id=p_bg_id
	and paa.assignment_id = asg_act.assignment_id
	and pap.person_id = paa.person_id
	and ppa.payroll_action_id = asg_act.payroll_action_id
	and asg_act.action_status='C'
	and ppa.action_type in ('R','Q','V','B','I')
	and ppa.date_earned between to_date(p_month_from,'MMYYYY')  AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
	and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
	and paa.organization_id in
                ((SELECT pose.organization_id_child
                  FROM   per_org_structure_elements pose
                  WHERE pose.org_structure_version_id = GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to)
                  START with pose.organization_id_parent = nvl(p_top_org_id,p_bg_id)
	          CONNECT BY prior organization_id_child = organization_id_parent )
                  union
                 (select nvl(p_top_org_id,p_bg_id) from dual))
                and pap.person_id=nvl(p_person_id,pap.person_id)
                and   ((p_top_org_id is NULL)  or (nvl(p_inc_sub_emp,'N') = 'N' and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)=p_top_org_id) or (nvl(p_inc_sub_emp,'N') = 'Y'))
	and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id) is
	not null
	and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))  between pap.effective_start_date and pap.effective_end_date
	and hou.business_group_id=p_bg_id
        and hou1.organization_id = hoi.org_information3
	and hou.organization_id=hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)
	and hoi.organization_id=hou.organization_id
	and hoi.org_information3 IS NOT NULL
	and hoi.org_information4 IS NOT NULL
	and hoi.org_information_context='NL_ORG_INFORMATION'
	and exists(select *
		   from pay_run_results
		   where assignment_action_id=asg_act.assignment_action_id
		   and (element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages')
		   			   ,Get_Element_Type_Id('Wage Tax Subsidy Education')
		   			   ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
		   			   ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
		   			   --RSS
		   			   ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
		   			   --RSS
		   			   )
		   or 	element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')
		   			   ,Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')
		   			   ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education')
		       			   ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave')
		       			   --RSS
		       			   ,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
		       			   --RSS
		       			   )
		       )
		 )
	order by employer_name , paa.person_id,ppa.payroll_id,ppa.date_earned;

Line: 834

SELECT	DISTINCT
	pap.full_name||' ('||pap.employee_number||')' employee_name,
        to_char(ppa.date_earned,'MonthYYYY') current_period,
	paa.assignment_id,
	pay.payroll_name payroll_name,
	ppa.date_earned,
	abs(pay_balance_pkg.get_value(decode(prr.element_type_id,
	--RSS
	Get_Element_Type_Id('Wage Tax Subsidy EVC'),Get_Defined_Balance_Id('Wage Tax Subsidy EVC'),
	--RSS
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Defined_Balance_Id('Wage Tax Subsidy Low Wages'),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Defined_Balance_Id('Wage Tax Subsidy Education'),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Defined_Balance_Id('Wage Tax Subsidy Long Term Unemployed'),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Defined_Balance_Id('Wage Tax Subsidy Paid Parental Leave')),asg_act.assignment_action_id)) Wage_Tax_Subsidy,
	paa.assignment_number,
        to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Education')) ,
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Parental Leave Hours',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'N')) Working_Hours
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'), Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Part_Time_Percentage
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Wage_Limit
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
	prr.Element_Type_Id,decode(prr.element_type_id,
	Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Basis_Salary,
	prr.element_type_id Subsidy_Element_Type_ID,
	decode(prr.element_type_id
	,Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),1
	,Get_Element_Type_Id('Wage Tax Subsidy Education'),2
	,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),3
	,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),4
	--RSS
	,Get_Element_Type_Id('Wage Tax Subsidy EVC'),5
	--RSS
	) Sequence
from
	per_assignments_f paa,
	pay_payroll_actions ppa,
	per_people_f pap,
	pay_assignment_actions asg_act,
	pay_run_results prr,
	pay_all_payrolls_f pay
where
	ppa.business_group_id=p_bg_id
	and asg_act.assignment_action_id = l_asg_act_id
	and paa.assignment_id = asg_act.assignment_id
	and pay.payroll_id = ppa.payroll_id
	and pap.person_id = paa.person_id
	and ppa.payroll_action_id = asg_act.payroll_action_id
	and asg_act.action_status='C'
	and ppa.action_type in ('R','Q','V','B','I')
	and ppa.date_earned between to_date(p_month_from,'MMYYYY')  AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
	and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
	and prr.assignment_action_id=asg_act.assignment_action_id
	and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))  between pap.effective_start_date and pap.effective_end_date
	and prr.element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages')
				   ,Get_Element_Type_Id('Wage Tax Subsidy Education')
				   ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
				   ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
				   --RSS
				   ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
				   --RSS
				   )
	and exists(select *
		   from pay_run_results
		   where assignment_action_id=asg_act.assignment_action_id
		   and element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages')
		   			  ,Get_Element_Type_Id('Wage Tax Subsidy Education')
	                                  ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
	                                  ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
	                                   --RSS
	                                  ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
	                                   --RSS
	                                  )
	          )
	and (to_number(pay_nl_general.get_run_result_value(asg_act.assignment_action_id
							   ,prr.Element_Type_Id
							   ,decode(prr.element_type_id,
							   	   Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
							   	   Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),
							   	   Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
	              						   Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')),
	              						   --RSS
	              						   Get_Element_Type_Id('Wage Tax Subsidy EVC'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Wage Tax Subsidy EVC'))
	              						   --RSS
	              						   )
	              					   ,prr.run_result_id
	              					   ,'M'
	              					  )
	     	      )
	     ) is not null
	order by employee_name, ppa.date_earned , Sequence;

Line: 942

SELECT	DISTINCT
	pap.full_name||' ('||pap.employee_number||')' employee_name,
	get_retro_wts(prr.assignment_action_id, prr.element_type_id, nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned))*(-1) Retro_WTS,
	paa.assignment_number,
	pay.payroll_name payroll_name,
	paa.assignment_id,
        to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
        decode(prr.element_type_id,
        Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
        Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_value_Id('Parental Leave Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'N')) Retro_Working_Hours
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Part_Time_Percentage
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Wage_Limit
	,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
	decode(prr.element_type_id,
	Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
	Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Basis_Salary
	,to_char(nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned),'MonthYYYY') Retro_Period
	,nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) RDate,
	nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) Retro_Date,
	prr.element_type_id Retro_Subsidy_Element_Type_ID,
	decode(prr.element_type_id
	,Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),1
	,Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),3
	,Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),2
	,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),4
	--RSS
	,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC'),5
	--RSS
	) Sequence
from
	per_assignments_f paa,
	per_people_f     pap,
	pay_assignment_actions asg_act,
	pay_run_results prr,
	pay_payroll_actions ppa,
	pay_all_payrolls_f pay
where
	ppa.business_group_id=p_bg_id
	and paa.assignment_id = asg_act.assignment_id
	and pap.person_id = paa.person_id
	and pay.payroll_id = ppa.payroll_id
	and asg_act.assignment_action_id = l_asg_act_id
	and ppa.payroll_action_id = asg_act.payroll_action_id
	and asg_act.action_status='C'
	and ppa.action_type in ('R','Q','V','B','I')
	and ppa.date_earned between to_date(p_month_from,'MMYYYY')  AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
	and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
	and prr.assignment_action_id=asg_act.assignment_action_id
	and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))  between pap.effective_start_date and pap.effective_end_date
	and prr.element_type_id  in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
	                             Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),
	                             Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),
	                             Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),
	                             --RSS
	                             Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
	                             --RSS
	                            )
	and exists(select *
			from pay_run_results
			where assignment_action_id=asg_act.assignment_action_id
			and element_type_id
	                in 	(Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
	                	 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),
	                      	 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),
	                      	 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),
	                      	 --RSS
	                      	 Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
	                      	 --RSS
	                      	)
	          )
	order by employee_name, RDate , Sequence;

Line: 1032

SELECT name FROM per_business_groups
WHERE
BUSINESS_GROUP_ID = l_bg_id;

Line: 1362

	SELECT program_name
	INTO l_file_name
	FROM fnd_lobs
	WHERE file_id = p_file_id;

Line: 1366

	-- the delete will ensure that the patch is rerunnable
	DELETE FROM per_gb_xdo_templates
	WHERE file_name = l_file_name AND
	effective_start_date = l_start_date AND
	effective_end_date = l_end_date;

Line: 1371

	INSERT INTO per_gb_xdo_templates
	(file_id,
	file_name,
	file_description,
	effective_start_date,
	effective_end_date)
	SELECT p_file_id, l_file_name, 'Template for year 0001-4712',
	l_start_date, l_end_date
	FROM fnd_lobs
	WHERE file_id = p_file_id;