PAY IE BIK Arrearage

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: IE BIK Arrearage Report (XML)
Short Name: PYIEBIKA_XML
DB package: PAY_PYIEBIKA_XMLP_PKG
Select 
papf.first_name first_name,
nvl(&P_DERIVED_SORT_ORDER, papf.employee_number ) break_order,
papf.last_name last_name,
papf.employee_number employee_number,
papf.national_identifier pps_number,
ppa.payroll_id,
pac.assignment_id, 
	PAY_PYIEBIKA_XMLP_PKG.cf_payroll_nameformula(ppa.payroll_id) CF_Payroll_Name, 
	PAY_PYIEBIKA_XMLP_PKG.cf_1formula(pac.assignment_id) bal1, 
	PAY_PYIEBIKA_XMLP_PKG.cf_2formula(pac.assignment_id) bal2, 
	PAY_PYIEBIKA_XMLP_PKG.cf_3formula(pac.assignment_id) bal3, 
	PAY_PYIEBIKA_XMLP_PKG.cf_4formula(pac.assignment_id) bal4, 
	PAY_PYIEBIKA_XMLP_PKG.cf_5formula(pac.assignment_id) bal5
from per_all_people_f  papf,
       per_assignments_f  paf,
       pay_assignment_actions pac,
       pay_payroll_actions ppa
where papf.person_id=paf.person_id
and pac.assignment_id=paf.assignment_id
and papf.business_group_id=paf.business_group_id
and papf.business_group_id=:P_BUSINESS_GROUP_ID
and ppa.payroll_id=nvl(:P_PAYROLL_ID,ppa.payroll_id)
and ppa.consolidation_set_id=:P_CONSOLIDATION_SET_ID
and ppa.payroll_action_id=pac.payroll_action_id
and pac.action_status='C'
and ppa.action_type in ('R','Q','V','B','I')
and ppa.date_earned=(select max(ppp.date_earned) 
                                  from pay_payroll_actions ppp,
                                          pay_assignment_actions paas
                                  where ppp.payroll_id=ppa.payroll_id 
                                  and ppp.date_earned<=:p_effective_date
                                  and ppp.action_type in ('R','Q','V','B','I')
                                  and ppp.effective_date between papf.effective_start_date and papf.effective_end_date
                                  and ppp.payroll_action_id=paas.payroll_action_id
                                  and paas.assignment_id=paf.assignment_id
                                   )
and to_number(to_char(ppa.date_earned,'YYYY')) between  
to_number(to_char(:p_effective_date,'YYYY'))-1  and to_number(to_char(:p_effective_date,'YYYY'))
and ppa.date_earned between paf.effective_start_date and paf.effective_end_date
and pac.source_action_id is null
and  (:p_assignment_set_id IS NULL
 or EXISTS (
        SELECT 1 
        FROM  hr_assignment_sets has,
                    hr_assignment_set_amendments hasa
        where has.assignment_set_id=:p_assignment_set_id
                   and nvl(has.payroll_id, ppa.payroll_id) =ppa.payroll_id
                   and  hasa.assignment_set_id = has.assignment_set_id(+)
                   and nvl(hasa.include_or_exclude, 'I')  = 'I' 
                   and nvl(hasa.assignment_id,pac.assignment_id) = pac.assignment_id 
                 )
        )
Parameter Name SQL text Validation
Assignment Set Name
 
Consolidation Set Name
 
Business Group ID
 
Number
Effective Date
 
Date
Sort Order
 
LOV Oracle
Assignment Set
 
LOV Oracle
Consolidation Set
 
LOV Oracle
Payroll
 
LOV Oracle