PAY IE Payroll Submission Reconciliation
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payroll
Source: IE Payroll Submission Reconciliation Report
Short Name: PYIESUBREC
DB package:
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 |