PAY NI Class Validation

Description
Categories: BI Publisher
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
Run PAY NI Class Validation and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Blitz Report™