PAY NI Class Validation

Description

Imported from BI Publisher
Description: NI Class Validation Report
Application: Payroll
Source: NI Class Validation Report (XML)
Short Name: PYGBNICV_XML
DB package: PAY_PYGBNICV_XMLP_PKG

PAY NI Class Validation and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

select asg.assignment_number,
       papf.full_name,
       pet.element_name pension_element,
       peev2.screen_entry_value ni_class
from   per_assignments_f asg,
       per_all_people_f papf,
       pay_all_payrolls_f pp,
       pay_element_entries_f pee,
       pay_element_links_f pel,
       pay_element_types_f pet,
       pay_element_classifications pec,
       pay_element_classifications pec2,
       pay_element_entries_f pee2,
       pay_element_links_f pel2,
       pay_element_types_f pet2,
       pay_input_values_f piv2,
       pay_element_entry_values_f peev2,
       pay_sub_classification_rules_f pscr
where  (:p_payroll_name is null or :p_payroll_name = pp.payroll_id)
and    (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id)
and    pp.business_group_id = :p_business_group_id
and    asg.payroll_id = pp.payroll_id
and    :p_effective_date between asg.effective_start_date and asg.effective_end_date
and    :p_effective_date between pp.effective_start_date and pp.effective_end_date
and    asg.person_id = papf.person_id
and    :p_effective_date between papf.effective_start_date and papf.effective_end_date
and    (:p_assignment_set is null
            or exists (select 1
                              from hr_assignment_set_amendments hasa,
                                       hr_assignment_sets aset
                              where :p_assignment_set is not null
                              and aset.assignment_set_id = :p_assignment_set
                              and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id
                              and hasa.assignment_set_id(+) = aset.assignment_set_id
                              and ((hasa.include_or_exclude is not null
                                       and ((hasa.include_or_exclude = 'I'
                                                and hasa.assignment_id = asg.assignment_id)
                                       or   ((hasa.include_or_exclude = 'E'
                                                and hasa.assignment_id <> asg.assignment_id)))
                              or hasa.include_or_exclude is null))))
and   asg.assignment_id = pee.assignment_id
and  :p_effective_date between pee.effective_start_date and pee.effective_end_date
and   pee.element_link_id = pel.element_link_id
and  :p_effective_date between pel.effective_start_date and pel.effective_end_date
and   pel.element_type_id = pet.element_type_id
and  :p_effective_date between pet.effective_start_date and pet.effective_end_date
and   pet.element_type_id = pscr.element_type_id
and  :p_effective_date between pscr.effective_start_date and pscr.effective_end_date
and   pscr.classification_id = pec.classification_id
and   pec.classification_name = 'Pre Tax Employee Pension COMP'
and   pec.parent_classification_id = pec2.classification_id
and   pec2.classification_name = 'Pre Tax Deductions'
and   pee.assignment_id = pee2.assignment_id
and  :p_effective_date between pee2.effective_start_date and pee2.effective_end_date
and   pee2.element_link_id = pel2.element_link_id
and  :p_effective_date between pel2.effective_start_date and pel2.effective_end_date
and   pel2.element_type_id = pet2.element_type_id
and  :p_effective_date between pet2.effective_start_date and pet2.effective_end_date
and   pet2.element_name = 'NI'
and   piv2.element_type_id = pet2.element_type_id
and  :p_effective_date between piv2.effective_start_date and piv2.effective_end_date
and   piv2.name = 'Category'
and   pee2.element_entry_id = peev2.element_entry_id
and  :p_effective_date between peev2.effective_start_date and peev2.effective_end_date
and   peev2.input_value_id = piv2.input_value_id
and   peev2.screen_entry_value not in ('D', 'E', 'L', 'F','G','S')
UNION
select asg.assignment_number,
       papf.full_name,
       'Pre Tax Employee Pension COMP Element Not Found.' pension_element,
       peev.screen_entry_value ni_class
from   per_assignments_f asg,
       per_all_people_f papf,
       pay_all_payrolls_f pp,
       pay_element_entries_f pee,
       pay_element_links_f pel,
       pay_element_types_f pet,
       pay_input_values_f piv,
       pay_element_entry_values_f peev
where  (:p_payroll_name is null or :p_payroll_name = pp.payroll_id)
and    (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id)
and    pp.business_group_id = :p_business_group_id
and    asg.payroll_id = pp.payroll_id
and    :p_effective_date between asg.effective_start_date and asg.effective_end_date
and    :p_effective_date between pp.effective_start_date and pp.effective_end_date
and    asg.person_id = papf.person_id
and    :p_effective_date between papf.effective_start_date and papf.effective_end_date
and    (:p_assignment_set is null
            or exists (select 1
                              from hr_assignment_set_amendments hasa,
                                       hr_assignment_sets aset
                              where :p_assignment_set is not null
                              and aset.assignment_set_id = :p_assignment_set
                              and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id
                              and hasa.assignment_set_id(+) = aset.assignment_set_id
                              and ((hasa.include_or_exclude is not null
                                       and ((hasa.include_or_exclude = 'I'
                                                and hasa.assignment_id = asg.assignment_id)
                                       or   ((hasa.include_or_exclude = 'E'
                                                and hasa.assignment_id <> asg.assignment_id)))
                              or hasa.include_or_exclude is null))))
and   asg.assignment_id = pee.assignment_id
and  :p_effective_date between pee.effective_start_date and pee.effective_end_date
and   pee.element_link_id = pel.element_link_id
and  :p_effective_date between pel.effective_start_date and pel.effective_end_date
and   pel.element_type_id = pet.element_type_id
and  :p_effective_date between pet.effective_start_date and pet.effective_end_date
and   pet.element_name = 'NI'
and   piv.element_type_id = pet.element_type_id
and  :p_effective_date between piv.effective_start_date and piv.effective_end_date
and   piv.name = 'Category'
and   pee.element_entry_id = peev.element_entry_id
and  :p_effective_date between peev.effective_start_date and peev.effective_end_date
and   peev.input_value_id = piv.input_value_id
and   peev.screen_entry_value in ('F','G','S')
and   NOT EXISTS (select 1
                  from    pay_element_entries_f pee2,
	                  pay_element_links_f pel2,
	                  pay_element_types_f pet2,
	                  pay_element_classifications pec,
	                  pay_element_classifications pec2,
			  pay_sub_classification_rules_f pscr
		  where pee2.assignment_id = asg.assignment_id
		  and   :p_effective_date between pee2.effective_start_date and pee2.effective_end_date
		  and   pee2.element_link_id = pel2.element_link_id
		  and   :p_effective_date between pel2.effective_start_date and pel2.effective_end_date
		  and   pel2.element_type_id = pet2.element_type_id
		  and   :p_effective_date between pet2.effective_start_date and pet2.effective_end_date
		  and   pet2.element_type_id = pscr.element_type_id
		  and   pscr.classification_id = pec.classification_id
		  and   :p_effective_date between pscr.effective_start_date and pscr.effective_end_date
		  and   pec.classification_name = 'Pre Tax Employee Pension COMP'
		  and   pec.parent_classification_id = pec2.classification_id
		  and   pec2.classification_name = 'Pre Tax Deductions')
UNION
select asg.assignment_number,
       papf.full_name,
       pet.element_name pension_element,
       peev2.screen_entry_value ni_class
from   per_assignments_f asg,
       per_all_people_f papf,
       pay_all_payrolls_f pp,
       pay_element_entries_f pee,
       pay_element_links_f pel,
       pay_element_types_f pet,
       pay_element_classifications pec,
       pay_element_classifications pec2,
       pay_element_entries_f pee2,
       pay_element_links_f pel2,
       pay_element_types_f pet2,
       pay_input_values_f piv2,
       pay_element_entry_values_f peev2,
       pay_sub_classification_rules_f pscr
where  (:p_payroll_name is null or :p_payroll_name = pp.payroll_id)
and    (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id)
and    asg.payroll_id = pp.payroll_id
and    pp.business_group_id = :p_business_group_id
and    :p_effective_date between asg.effective_start_date and asg.effective_end_date
and    :p_effective_date between pp.effective_start_date and pp.effective_end_date
and    asg.person_id = papf.person_id
and    :p_effective_date between papf.effective_start_date and papf.effective_end_date
and    (:p_assignment_set is null
            or exists (select 1
                              from hr_assignment_set_amendments hasa,
                                       hr_assignment_sets aset
                              where :p_assignment_set is not null
                              and aset.assignment_set_id = :p_assignment_set
                              and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id
                              and hasa.assignment_set_id(+) = aset.assignment_set_id
                              and ((hasa.include_or_exclude is not null
                                       and ((hasa.include_or_exclude = 'I'
                                                and hasa.assignment_id = asg.assignment_id)
                                       or   ((hasa.include_or_exclude = 'E'
                                                and hasa.assignment_id <> asg.assignment_id)))
                              or hasa.include_or_exclude is null))))
and   asg.assignment_id = pee.assignment_id
and  :p_effective_date between pee.effective_start_date and pee.effective_end_date
and   pee.element_link_id = pel.element_link_id
and  :p_effective_date between pel.effective_start_date and pel.effective_end_date
and   pel.element_type_id = pet.element_type_id
and  :p_effective_date between pet.effective_start_date and pet.effective_end_date
and   pet.element_type_id = pscr.element_type_id
and   :p_effective_date between pscr.effective_start_date and pscr.effective_end_date
and   pscr.classification_id = pec.classification_id
and   pec.classification_name = 'Pre Tax Employee Pension COSR'
and   pec.parent_classification_id = pec2.classification_id
and   pec2.classification_name = 'Pre Tax Deductions'
and   pee.assignment_id = pee2.assignment_id
and  :p_effective_date between pee2.effective_start_date and pee2.effective_end_date
and   pee2.element_link_id = pel2.element_link_id
and  :p_effective_date between pel2.effective_start_date and pel2.effective_end_date
and   pel2.element_type_id = pet2.element_type_id
and  :p_effective_date between pet2.effective_start_date and pet2.effective_end_date
and   pet2.element_name = 'NI'
and   piv2.element_type_id = pet2.element_type_id
and   :p_effective_date between piv2.effective_start_date and piv2.effective_end_date
and   piv2.name = 'Category'
and   pee2.element_entry_id = peev2.element_entry_id
and  :p_effective_date between peev2.effective_start_date and peev2.effective_end_date
and   peev2.input_value_id = piv2.input_value_id
and   peev2.screen_entry_value not in ('D','E','L', 'F', 'G', 'S') 
UNION
select asg.assignment_number,
       papf.full_name,
       'Pre Tax Employee Pension COSR Element Not Found.' pension_element,
       peev.screen_entry_value ni_class
from   per_assignments_f asg,
       per_all_people_f papf,
       pay_all_payrolls_f pp,
       pay_element_entries_f pee,
       pay_element_links_f pel,
       pay_element_types_f pet,
       pay_input_values_f piv,
       pay_element_entry_values_f peev
where  (:p_payroll_name is null or :p_payroll_name = pp.payroll_id)
and    (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id)
and    asg.payroll_id = pp.payroll_id
and    pp.business_group_id = :p_business_group_id
and    :p_effective_date between asg.effective_start_date and asg.effective_end_date
and    :p_effective_date between pp.effective_start_date and pp.effective_end_date
and    asg.person_id = papf.person_id
and    :p_effective_date between papf.effective_start_date and papf.effective_end_date
and    (:p_assignment_set is null
            or exists (select 1
                              from hr_assignment_set_amendments hasa,
                                       hr_assignment_sets aset
                              where :p_assignment_set is not null
                              and aset.assignment_set_id = :p_assignment_set
                              and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id
                              and hasa.assignment_set_id(+) = aset.assignment_set_id
                              and ((hasa.include_or_exclude is not null
                                       and ((hasa.include_or_exclude = 'I'
                                                and hasa.assignment_id = asg.assignment_id)
                                       or   ((hasa.include_or_exclude = 'E'
                                                and hasa.assignment_id <> asg.assignment_id)))
                              or hasa.include_or_exclude is null))))
and   asg.assignment_id = pee.assignment_id
and  :p_effective_date between pee.effective_start_date and pee.effective_end_date
and   pee.element_link_id = pel.element_link_id
and  :p_effective_date between pel.effective_start_date and pel.effective_end_date
and   pel.element_type_id = pet.element_type_id
and  :p_effective_date between pet.effective_start_date and pet.effective_end_date
and   pet.element_name = 'NI'
and   piv.element_type_id = pet.element_type_id
and  :p_effective_date between piv.effective_start_date and piv.effective_end_date
and   piv.name = 'Category'
and   pee.element_entry_id = peev.element_entry_id
and  :p_effective_date between peev.effective_start_date and peev.effective_end_date
and   peev.input_value_id = piv.input_value_id
and   peev.screen_entry_value in ('D','E','L') 
and   NOT EXISTS (select 1
                  from    pay_element_entries_f pee2,
	                  pay_element_links_f pel2,
	                  pay_element_types_f pet2,
	                  pay_element_classifications pec,
	                  pay_element_classifications pec2,
			  pay_sub_classification_rules_f pscr
		  where pee2.assignment_id = asg.assignment_id
		  and   :p_effective_date between pee2.effective_start_date and pee2.effective_end_date
		  and   pee2.element_link_id = pel2.element_link_id
		  and   :p_effective_date between pel2.effective_start_date and pel2.effective_end_date
		  and   pel2.element_type_id = pet2.element_type_id
		  and   :p_effective_date between pet2.effective_start_date and pet2.effective_end_date
		  and   pet2.element_type_id = pscr.element_type_id
		  and   pscr.classification_id = pec.classification_id
		  and   :p_effective_date between pscr.effective_start_date and pscr.effective_end_date
		  and   pec.classification_name = 'Pre Tax Employee Pension COSR'
		  and   pec.parent_classification_id = pec2.classification_id
		  and   pec2.classification_name = 'Pre Tax Deductions')
&P_SORT

select asg.assignment_number, papf.full_name, pet.element_name pension_element, peev2.screen_entry_value ni_class from per_assignments_f asg, per_all_people_f papf, pay_all_payrolls_f pp, pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet, pay_element_classifications pec, pay_element_classifications pec2, pay_element_entries_f pee2, pay_element_links_f pel2, pay_element_types_f pet2, pay_input_values_f piv2, pay_element_entry_values_f peev2, pay_sub_classification_rules_f pscr where (:p_payroll_name is null or :p_payroll_name = pp.payroll_id) and (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id) and pp.business_group_id = :p_business_group_id and asg.payroll_id = pp.payroll_id and :p_effective_date between asg.effective_start_date and asg.effective_end_date and :p_effective_date between pp.effective_start_date and pp.effective_end_date and asg.person_id = papf.person_id and :p_effective_date between papf.effective_start_date and papf.effective_end_date and (:p_assignment_set is null or exists (select 1 from hr_assignment_set_amendments hasa, hr_assignment_sets aset where :p_assignment_set is not null and aset.assignment_set_id = :p_assignment_set and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id and hasa.assignment_set_id(+) = aset.assignment_set_id and ((hasa.include_or_exclude is not null and ((hasa.include_or_exclude = 'I' and hasa.assignment_id = asg.assignment_id) or ((hasa.include_or_exclude = 'E' and hasa.assignment_id <> asg.assignment_id))) or hasa.include_or_exclude is null)))) and asg.assignment_id = pee.assignment_id and :p_effective_date between pee.effective_start_date and pee.effective_end_date and pee.element_link_id = pel.element_link_id and :p_effective_date between pel.effective_start_date and pel.effective_end_date and pel.element_type_id = pet.element_type_id and :p_effective_date between pet.effective_start_date and pet.effective_end_date and pet.element_type_id = pscr.element_type_id and :p_effective_date between pscr.effective_start_date and pscr.effective_end_date and pscr.classification_id = pec.classification_id and pec.classification_name = 'Pre Tax Employee Pension COMP' and pec.parent_classification_id = pec2.classification_id and pec2.classification_name = 'Pre Tax Deductions' and pee.assignment_id = pee2.assignment_id and :p_effective_date between pee2.effective_start_date and pee2.effective_end_date and pee2.element_link_id = pel2.element_link_id and :p_effective_date between pel2.effective_start_date and pel2.effective_end_date and pel2.element_type_id = pet2.element_type_id and :p_effective_date between pet2.effective_start_date and pet2.effective_end_date and pet2.element_name = 'NI' and piv2.element_type_id = pet2.element_type_id and :p_effective_date between piv2.effective_start_date and piv2.effective_end_date and piv2.name = 'Category' and pee2.element_entry_id = peev2.element_entry_id and :p_effective_date between peev2.effective_start_date and peev2.effective_end_date and peev2.input_value_id = piv2.input_value_id and peev2.screen_entry_value not in ('D', 'E', 'L', 'F','G','S') UNION select asg.assignment_number, papf.full_name, 'Pre Tax Employee Pension COMP Element Not Found.' pension_element, peev.screen_entry_value ni_class from per_assignments_f asg, per_all_people_f papf, pay_all_payrolls_f pp, pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet, pay_input_values_f piv, pay_element_entry_values_f peev where (:p_payroll_name is null or :p_payroll_name = pp.payroll_id) and (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id) and pp.business_group_id = :p_business_group_id and asg.payroll_id = pp.payroll_id and :p_effective_date between asg.effective_start_date and asg.effective_end_date and :p_effective_date between pp.effective_start_date and pp.effective_end_date and asg.person_id = papf.person_id and :p_effective_date between papf.effective_start_date and papf.effective_end_date and (:p_assignment_set is null or exists (select 1 from hr_assignment_set_amendments hasa, hr_assignment_sets aset where :p_assignment_set is not null and aset.assignment_set_id = :p_assignment_set and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id and hasa.assignment_set_id(+) = aset.assignment_set_id and ((hasa.include_or_exclude is not null and ((hasa.include_or_exclude = 'I' and hasa.assignment_id = asg.assignment_id) or ((hasa.include_or_exclude = 'E' and hasa.assignment_id <> asg.assignment_id))) or hasa.include_or_exclude is null)))) and asg.assignment_id = pee.assignment_id and :p_effective_date between pee.effective_start_date and pee.effective_end_date and pee.element_link_id = pel.element_link_id and :p_effective_date between pel.effective_start_date and pel.effective_end_date and pel.element_type_id = pet.element_type_id and :p_effective_date between pet.effective_start_date and pet.effective_end_date and pet.element_name = 'NI' and piv.element_type_id = pet.element_type_id and :p_effective_date between piv.effective_start_date and piv.effective_end_date and piv.name = 'Category' and pee.element_entry_id = peev.element_entry_id and :p_effective_date between peev.effective_start_date and peev.effective_end_date and peev.input_value_id = piv.input_value_id and peev.screen_entry_value in ('F','G','S') and NOT EXISTS (select 1 from pay_element_entries_f pee2, pay_element_links_f pel2, pay_element_types_f pet2, pay_element_classifications pec, pay_element_classifications pec2, pay_sub_classification_rules_f pscr where pee2.assignment_id = asg.assignment_id and :p_effective_date between pee2.effective_start_date and pee2.effective_end_date and pee2.element_link_id = pel2.element_link_id and :p_effective_date between pel2.effective_start_date and pel2.effective_end_date and pel2.element_type_id = pet2.element_type_id and :p_effective_date between pet2.effective_start_date and pet2.effective_end_date and pet2.element_type_id = pscr.element_type_id and pscr.classification_id = pec.classification_id and :p_effective_date between pscr.effective_start_date and pscr.effective_end_date and pec.classification_name = 'Pre Tax Employee Pension COMP' and pec.parent_classification_id = pec2.classification_id and pec2.classification_name = 'Pre Tax Deductions') UNION select asg.assignment_number, papf.full_name, pet.element_name pension_element, peev2.screen_entry_value ni_class from per_assignments_f asg, per_all_people_f papf, pay_all_payrolls_f pp, pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet, pay_element_classifications pec, pay_element_classifications pec2, pay_element_entries_f pee2, pay_element_links_f pel2, pay_element_types_f pet2, pay_input_values_f piv2, pay_element_entry_values_f peev2, pay_sub_classification_rules_f pscr where (:p_payroll_name is null or :p_payroll_name = pp.payroll_id) and (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id) and asg.payroll_id = pp.payroll_id and pp.business_group_id = :p_business_group_id and :p_effective_date between asg.effective_start_date and asg.effective_end_date and :p_effective_date between pp.effective_start_date and pp.effective_end_date and asg.person_id = papf.person_id and :p_effective_date between papf.effective_start_date and papf.effective_end_date and (:p_assignment_set is null or exists (select 1 from hr_assignment_set_amendments hasa, hr_assignment_sets aset where :p_assignment_set is not null and aset.assignment_set_id = :p_assignment_set and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id and hasa.assignment_set_id(+) = aset.assignment_set_id and ((hasa.include_or_exclude is not null and ((hasa.include_or_exclude = 'I' and hasa.assignment_id = asg.assignment_id) or ((hasa.include_or_exclude = 'E' and hasa.assignment_id <> asg.assignment_id))) or hasa.include_or_exclude is null)))) and asg.assignment_id = pee.assignment_id and :p_effective_date between pee.effective_start_date and pee.effective_end_date and pee.element_link_id = pel.element_link_id and :p_effective_date between pel.effective_start_date and pel.effective_end_date and pel.element_type_id = pet.element_type_id and :p_effective_date between pet.effective_start_date and pet.effective_end_date and pet.element_type_id = pscr.element_type_id and :p_effective_date between pscr.effective_start_date and pscr.effective_end_date and pscr.classification_id = pec.classification_id and pec.classification_name = 'Pre Tax Employee Pension COSR' and pec.parent_classification_id = pec2.classification_id and pec2.classification_name = 'Pre Tax Deductions' and pee.assignment_id = pee2.assignment_id and :p_effective_date between pee2.effective_start_date and pee2.effective_end_date and pee2.element_link_id = pel2.element_link_id and :p_effective_date between pel2.effective_start_date and pel2.effective_end_date and pel2.element_type_id = pet2.element_type_id and :p_effective_date between pet2.effective_start_date and pet2.effective_end_date and pet2.element_name = 'NI' and piv2.element_type_id = pet2.element_type_id and :p_effective_date between piv2.effective_start_date and piv2.effective_end_date and piv2.name = 'Category' and pee2.element_entry_id = peev2.element_entry_id and :p_effective_date between peev2.effective_start_date and peev2.effective_end_date and peev2.input_value_id = piv2.input_value_id and peev2.screen_entry_value not in ('D','E','L', 'F', 'G', 'S') UNION select asg.assignment_number, papf.full_name, 'Pre Tax Employee Pension COSR Element Not Found.' pension_element, peev.screen_entry_value ni_class from per_assignments_f asg, per_all_people_f papf, pay_all_payrolls_f pp, pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet, pay_input_values_f piv, pay_element_entry_values_f peev where (:p_payroll_name is null or :p_payroll_name = pp.payroll_id) and (:p_consolidation_set is null or :p_consolidation_set = pp.consolidation_set_id) and asg.payroll_id = pp.payroll_id and pp.business_group_id = :p_business_group_id and :p_effective_date between asg.effective_start_date and asg.effective_end_date and :p_effective_date between pp.effective_start_date and pp.effective_end_date and asg.person_id = papf.person_id and :p_effective_date between papf.effective_start_date and papf.effective_end_date and (:p_assignment_set is null or exists (select 1 from hr_assignment_set_amendments hasa, hr_assignment_sets aset where :p_assignment_set is not null and aset.assignment_set_id = :p_assignment_set and nvl(aset.payroll_id,pp.payroll_id) = pp.payroll_id and hasa.assignment_set_id(+) = aset.assignment_set_id and ((hasa.include_or_exclude is not null and ((hasa.include_or_exclude = 'I' and hasa.assignment_id = asg.assignment_id) or ((hasa.include_or_exclude = 'E' and hasa.assignment_id <> asg.assignment_id))) or hasa.include_or_exclude is null)))) and asg.assignment_id = pee.assignment_id and :p_effective_date between pee.effective_start_date and pee.effective_end_date and pee.element_link_id = pel.element_link_id and :p_effective_date between pel.effective_start_date and pel.effective_end_date and pel.element_type_id = pet.element_type_id and :p_effective_date between pet.effective_start_date and pet.effective_end_date and pet.element_name = 'NI' and piv.element_type_id = pet.element_type_id and :p_effective_date between piv.effective_start_date and piv.effective_end_date and piv.name = 'Category' and pee.element_entry_id = peev.element_entry_id and :p_effective_date between peev.effective_start_date and peev.effective_end_date and peev.input_value_id = piv.input_value_id and peev.screen_entry_value in ('D','E','L') and NOT EXISTS (select 1 from pay_element_entries_f pee2, pay_element_links_f pel2, pay_element_types_f pet2, pay_element_classifications pec, pay_element_classifications pec2, pay_sub_classification_rules_f pscr where pee2.assignment_id = asg.assignment_id and :p_effective_date between pee2.effective_start_date and pee2.effective_end_date and pee2.element_link_id = pel2.element_link_id and :p_effective_date between pel2.effective_start_date and pel2.effective_end_date and pel2.element_type_id = pet2.element_type_id and :p_effective_date between pet2.effective_start_date and pet2.effective_end_date and pet2.element_type_id = pscr.element_type_id and pscr.classification_id = pec.classification_id and :p_effective_date between pscr.effective_start_date and pscr.effective_end_date and pec.classification_name = 'Pre Tax Employee Pension COSR' and pec.parent_classification_id = pec2.classification_id and pec2.classification_name = 'Pre Tax Deductions') &P_SORT

Parameter Name

SQL text

Validation

Effective Date

Date

Payroll Name

LOV Oracle

Assignment Set

LOV Oracle

Consolidation Set

LOV Oracle

Sort Order

LOV Oracle

Download

Related Blitz Reports

PAY Fringe Benefits PAY Advance Pay Listing (Australasia) PAY Payroll Reconciliation Detail PAY Payroll Reconciliation (New Zealand) RKD Employee Element History SSP ShPP Adoption Element Results SSP ShPP Birth Element Results PER Payroll Element Entries Upload PAY Employee Run Results Report (CA)

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption