ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4715A51A9E05362FB09050512Y SELECT '05-'||to_char (efctv_dt,'MON-YYYY') MONTH, app_employer_pay_bill APP_EMPLOYER_PAY_BILL, app_employer_pay_bill_ytd APP_EMPLOYER_PAY_BILL_YTD, grs_emp_pb_disc GRS_EMP_PB_DISC, grs_emp_pb_ytd GRS_EMP_PB_YTD, effctv_alwnc EFFCTV_ALWNC, effctv_alwnc_ytd EFFCTV_ALWNC_YTD, apprentice_levy_due_ytd_calc APPR_LEVY_DUE_YTD_CALC, apprentice_levy_due_ytd_calc - lag (apprentice_levy_due_ytd_calc,1,0) OVER (ORDER BY efctv_dt ASC) APPR_LEVY_DUE_YTD_CALC_DISC FROM ( SELECT eps.effective_date efctv_dt, eps.app_employer_pay_bill app_employer_pay_bill, eps.app_employer_pay_bill_ytd app_employer_pay_bill_ytd, trunc (eps.app_employer_pay_bill * (to_number (hr_gb_process_p11d_entries_pkg.get_global_value ('APPRENTICE_PERCENTAGE',fnd_date.date_to_canonical (eps.effective_date))))) grs_emp_pb_disc, trunc (eps.app_employer_pay_bill_ytd * (to_number (hr_gb_process_p11d_entries_pkg.get_global_value ('APPRENTICE_PERCENTAGE',fnd_date.date_to_canonical (eps.effective_date))))) grs_emp_pb_ytd, trunc ((nvl (org_information6,(to_number (hr_gb_process_p11d_entries_pkg.get_global_value ('ANNUAL_APP_LEVY_ALLOWANCE',fnd_date.date_to_canonical (eps.effective_date)))))) / 12) effctv_alwnc, trunc ((nvl (to_number (hr.org_information6), (to_number (hr_gb_process_p11d_entries_pkg.get_global_value ('ANNUAL_APP_LEVY_ALLOWANCE',fnd_date.date_to_canonical (eps.effective_date))))) / 12)) * to_number (to_char (add_months (eps.effective_date,- 4),'MM')) effctv_alwnc_ytd, TRUNC(eps.apprentice_levy_due_ytd_calc) apprentice_levy_due_ytd_calc FROM pay_gb_eps_details eps, hr_organization_information hr WHERE eps.emp_paye_reference = :P_PAYE_REF AND eps.business_group_id = :BUSINESS_GROUP_ID AND hr.org_information_context = 'Tax Details References Cont' AND nvl (hr.org_information10,'UK') = 'UK' AND hr.org_information1 = eps.emp_paye_reference AND related_tax_year = :P_TAX_YEAR AND UPPER(eps.status) = 'SENT' ) PAY PYGBAPRCS US PAY GB Apprenticeship Levy Costing Sheet Imported from BI Publisher Description: GB Apprenticeship Levy Costing Report Application: Payroll Source: GB Apprenticeship Levy Costing Sheet Short Name: PYGBAPRCS DB package: BI Publisher :business_group_id :p_paye_ref :p_tax_year 1 -10 :business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') US Business Group Business Group Id 2 10 :p_paye_ref LOV Oracle PAY_GB_PAYROLL_TAX_REF Y select org.org_information1 id, org.org_information1 value, null description from hr_organization_information org,pay_payrolls_f p,hr_soft_coding_keyflex flex,fnd_sessions sess where p.soft_coding_keyflex_id=flex.soft_coding_keyflex_id and sess.effective_date between p.effective_start_date and p.effective_end_date and sess.session_id=userenv('sessionid') and p.business_group_id=fnd_global.per_business_group_id and nvl(to_number(:$flex$.pay_srs_payroll:null), p.payroll_id)=p.payroll_id and org.org_information1=flex.segment1 and nvl(org.org_information10,'UK')='UK' and org.org_information_context='Tax Details References' and org.organization_id=fnd_global.per_business_group_id and p.rowid=(select max(a.rowid) from pay_payrolls_f a, hr_soft_coding_keyflex aflex where a.soft_coding_keyflex_id= aflex.soft_coding_keyflex_id and a.business_group_id+0= fnd_global.per_business_group_id and flex.segment1=aflex.segment1 and sess.effective_date between a.effective_start_date and a.effective_end_date and nvl(to_number(:$flex$.pay_srs_payroll:null), a.payroll_id)=a.payroll_id ) order by value,description select org_information1 from hr_organization_information org, pay_payrolls_f p, hr_soft_coding_keyflex flex, fnd_sessions sess where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id and sess.effective_date between p.effective_start_date and p.effective_end_date and p.business_group_id = fnd_global.PER_BUSINESS_GROUP_ID and org.org_information1 = flex.segment1 and nvl(org.org_information10, 'UK') = 'UK' and org.org_information_context = 'Tax Details References' and org.organization_id = fnd_global.PER_BUSINESS_GROUP_ID and sess.session_id = userenv('sessionid') Y US PAYE Reference Employer PAYE Reference 3 20 :p_tax_year LOV Oracle GB_APP_EPS_COST_YEAR Y select a.related_tax_year id, a.related_tax_year value, null description from (select distinct related_tax_year,emp_paye_reference from pay_gb_eps_details) a where a.emp_paye_reference=:$flex$.pay_gb_payroll_tax_ref order by value,description select MAX(a.related_tax_year) from pay_gb_eps_Details a where a.EMP_PAYE_REFERENCE = :$flex$.paye_reference Y US Tax Year Related Tax Year