PAY IE Payroll Submission Reconciliation

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payroll
Source: IE Payroll Submission Reconciliation Report
Short Name: PYIESUBREC
DB package:
Run PAY IE Payroll Submission Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  paaf.person_id person_id
       ,paaf.assignment_number assignment_id
       ,pai.action_information1 ppsn
       ,pai.action_information2 first_name
       ,pai.action_information3 surname
       ,pai.action_information4
        || nvl2(pai.action_information5,' ','')
        || pai.action_information5
	|| nvl2(pai.action_information6,' ','') 
        || pai.action_information6
        || nvl2(pai.action_information13,' ','')
        || upper (pai.action_information13)
        || nvl2(pai.action_information12,' ','')
        || pai.action_information12
        || nvl2(pai.action_information14,' ','')
        || pai.action_information14 address_line
       ,pai.action_information8 emplyoment_id
       ,fnd_date.date_to_displaydt (fnd_date.canonical_to_date(pai.action_information7)) date_of_birth
       ,fnd_date.date_to_displaydt (fnd_date.canonical_to_date(pai.action_information9)) hire_date
	   ,decode (to_char (fnd_date.canonical_to_date (pai.action_information10)
                ,'YYYY-MM-DD')
                     ,'4712-12-31'
                     ,NULL
                     ,fnd_date.date_to_displaydt  (fnd_date.canonical_to_date (pai.action_information10)))term_date
       ,pai.action_information11 rpn_number
       ,pai.action_information12 country
       ,upper (pai.action_information13) county
       ,pai2.action_information1 paye_reference
       ,fnd_date.date_to_displaydt (fnd_date.canonical_to_date(pai2.action_information2)) pay_date
       ,decode (pai2.action_information3
               ,'0'
               ,'WEEKLY'
               ,'1'
               ,'BI_WEEKLY'
               ,'2'
               ,'MONTHLY'
               ,'3'
               ,'FOUR_WEEKLY'
               ,'4'
               ,'OTHER') pay_frequency
       ,pai3.action_information1 std_rate_cutoff
       ,pai3.action_information2 annuatax_credit
       ,pai3.action_information3
       ,decode (pai3.action_information3
               ,'1'
               ,'WEEK_1'
               ,'2'
               ,'EMERGENCY'
               ,'0'
               ,'CUMULATIVE'
               ,NULL
               ,'2') tax_deduction_basis
       ,decode (pai3.action_information4
               ,'0'
               ,'false'
               ,1
               ,'true') exclusion_order
       ,pai3.action_information5 gross_pay
       ,pai3.action_information6 pay_for_income_tax
       ,pai3.action_information7 income_tax_paid
       ,pai3.action_information8 gross_pay_prsi
       ,pai3.action_information9 gross_pay_prsi_dup
       ,decode (pai3.action_information10
               ,'0'
               ,'false'
               ,1
               ,'true') prsi_exempt_flag
       ,decode (pai3.action_information11
               ,'01'
               ,'A_1_PORTABLE_DOCUMENT'
               ,'02'
               ,'CERTIFICATE_OF_COVERAGE'
               ,'03'
               ,'POSTED_WORKER_OTHER_NOTIFICATION'
               ,'04'
               ,'FAMILY_MEMBER'
               ,'05'
               ,'UNDER_16'
               ,'06'
               ,'SOCIAL_WELFARE_SCHEME'
               ,'07'
               ,'OTHER') exempt_reason
       ,pay_ie_payroll_sub_report.get_initial_class (paa.assignment_action_id,'IE CONT CLASS DETAILS') initial_class
       ,pay_ie_payroll_sub_report.get_insurable_weeks(paa.assignment_action_id,'IE CONT CLASS DETAILS') insurable_weeks
       ,pai3.action_information14 emp_prsi_cont
       ,pai3.action_information15 employer_prsi_cont
       ,pai3.action_information16 gross_pay_usc
       ,decode (pai3.action_information17
               ,'1'
               ,'EXEMPT'
               ,'ORDINARY') usc_status
       ,pai3.action_information18 usc
       ,pai3.action_information19 medical_insurance
       ,pay_ie_payroll_sub_report.get_initial_class (paa.assignment_action_id,'IE PENSION TRACING DETAILS') pension_tracing_number
       ,pai3.action_information21 pen_empr_rbs
       ,pai3.action_information22 pen_emp_rbs
       ,pai3.action_information23 rch_pen_empr_prsa
       ,pai3.action_information24 rch_pen_emp_prsa
       ,pai3.action_information25 rch_pen_emp_rac
       ,pai3.action_information26 pen_emp_avc
       ,pai3.action_information27 share_based_rem
       ,pai3.action_information28 taxable_benefits
       ,pai3.action_information29 taxable_lump_sum
       ,pai3.action_information30 nontax_lumpsum
       ,pai4.action_information1 lpt
       ,decode (nvl (pai4.action_information2
                    ,'')
               ,'NP'
               ,'NON-PROPRIETARY'
               ,'P'
               ,'PROPRIETARY') dir_flg
       ,decode (nvl (pai4.action_information3
                    ,'N')
               ,'N'
               ,'false'
               ,'Y'
               ,'true') shadow_payment
       ,pai4.action_information4 pay_periods
       ,pai4.action_information5 employer_reference
FROM    pay_action_information pai3
       ,pay_action_information pai
       ,pay_action_information pai4
       ,pay_action_information pai2
       ,pay_assignment_actions paa
       ,per_all_assignments_f paaf
       ,pay_payroll_actions ppa
WHERE   paaf.business_group_id = :BG_ID
AND     paaf.assignment_id = pai.assignment_id
AND     ppa.payroll_action_id = paa.payroll_action_id
AND     ppa.effective_date BETWEEN paaf.effective_start_date
                           AND     paaf.effective_end_date
AND     paaf.assignment_type = 'E'
AND     paa.payroll_action_id = :PAYROLL_ACTION_ID
AND     paa.action_status = 'C'
AND     paa.assignment_id = paaf.assignment_id
AND     pai.action_context_id = paa.assignment_action_id
AND     pai.action_context_type = 'AAP'
AND     pai.action_information_category = 'IE PSUB EMPLOYEE DETAILS'
AND     pai3.action_information_category = 'IE PSUB ASG DETAILS'
AND     pai3.action_context_type = 'AAP'
AND     pai3.action_context_id = paa.assignment_action_id
AND     pai4.action_information_category = 'IE PSUB ASG ADDITIONAL DETAIL'
AND     pai4.action_context_type = 'AAP'
AND     pai4.action_context_id = paa.assignment_action_id
AND     pai2.action_information_category = 'IE PSUB PAYROLL DETAILS'
AND     pai2.action_context_type = 'PA'
AND     pai2.action_context_id = paa.payroll_action_id
Parameter Name SQL text Validation
Payroll Action ID 
 
 LOV Oracle
Download
Related Blitz Reports
PAY Employee Run Results PAY Payroll Report (Check List of Bonus Payment Notification for Health Ins/Welfare Pension Ins) PAY IE P30 PAY Employee Run Results Report (CA) PAY Business Payrolls Survey PAY Inland Revenue Remittance Certificate PAY RTI - Year End P60 Report Process PAY RTI - Year End P60 Report Process (Multi Threaded) PAY RTI FPS Amendments Run Reconciliation
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™