PAY P45(3) & P46 Exception

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: P45(3) & P46 Exception Report (XML)
Short Name: PYGBNSEX_XML
DB package: PAY_PYGBNSEX_XMLP_PKG
/* distinct keyword added to the query for Bug: 10366795 */
select distinct peo.last_name||' '||peo.first_name||' '||peo.middle_names emp_name,
       peo.start_date,
       peo.national_identifier,
       paf.assignment_number,
       paf.business_group_id,
       pay.payroll_name
from   per_assignments_f paf,
       per_all_people_f  peo,
       per_periods_of_service serv,
       fnd_sessions fs,
       hr_organization_information hoi,
       pay_all_payrolls_f pay,
       hr_soft_coding_keyflex sck
where  upper(:p_tax_ref) = upper(hoi.org_information1)
and    (:p_payroll_id is null or :p_payroll_id = pay.payroll_id)
and    hoi.org_information1 = sck.segment1
and    sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
and    pay.payroll_id = paf.payroll_id
and    pay.business_group_id = paf.business_group_id
and    paf.person_id = peo.person_id
and    fs.session_id = userenv('sessionid')
and    serv.person_id = peo.person_id
and    serv.date_start = (select max(s.date_start)
                          from   per_periods_of_service s
                          where  s.person_id = peo.person_id
                          and    fs.effective_date >= s.date_start
                          and    s.date_start >= :p_starters_from)
and    peo.current_employee_flag = 'Y'
and    fs.effective_date between paf.effective_start_date and paf.effective_end_date
and    fs.effective_date between peo.effective_start_date and peo.effective_end_date
and    fs.effective_date between pay.effective_start_date and pay.effective_end_date
and    paf.assignment_id not in(select  paa.assignment_id
                                from    pay_assignment_actions paa,
                                        pay_payroll_actions    ppa
                                where   ppa.report_type in ('P45_3','P46','P45PT_3','P46_5','P45PT_3_VER6','P46_VER6','P46EXP_VER6')
                                and     ppa.payroll_action_id = paa.payroll_action_id)
and    paf.assignment_id in (select paa.assignment_id
                             from   pay_assignment_actions paa,
                                    pay_payroll_actions ppa
                             where  paa.payroll_action_id in ppa.payroll_action_id
                             and    ppa.action_type in ('Q','R')
                             --and    ppa.effective_date < :p_effective_date
                             and    ppa.effective_date < :p_effective_date_t
                             and    ppa.effective_date > peo.start_date)
--ORDER BY 6,1,4,3,5,2
ORDER BY 6
--ORDER BY 6,4,3,1
--/
Parameter Name SQL text Validation
Business Group ID
 
Number
Effective Date
 
Date
Starters From
 
Date
Employers PAYE Reference
 
LOV Oracle
Payroll ID
 
LOV Oracle