ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4716451A9E05362FB09050512Y 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 PAY PYIESUBREC US PAY IE Payroll Submission Reconciliation Imported from BI Publisher Application: Payroll Source: IE Payroll Submission Reconciliation Report Short Name: PYIESUBREC DB package: BI Publisher :bg_id :payroll_action_id 1 -10 :bg_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') US Business Group ID Business Group ID 2 10 :payroll_action_id LOV Oracle PAY_IE_PSUB_ARCH_REQ_ID Y select ppa.payroll_action_id id, ppa.request_id value, null description from pay_payroll_actions ppa where ppa.action_type='X' and ppa.action_status='C' and ppa.report_qualifier='IE' and ppa.report_type='IEPSUBA' and ppa.report_category='IEPSUBA' and ppa.business_group_id=fnd_global.per_business_group_id order by ppa.request_id desc Y US Payroll Action ID Payroll Action ID