PAY GB Apprenticeship Levy Costing Sheet

Description

Categories: BI Publisher
Columns: Month, App Employer Pay Bill, App Employer Pay Bill Ytd, Grs Emp Pb Disc, Grs Emp Pb Ytd, Effctv Alwnc, Effctv Alwnc Ytd, Appr Levy Due Ytd Calc, Appr Levy Due Ytd Calc Disc ...

Application: Payroll
Source: GB Apprenticeship Levy Costing Sheet
Short Name: PYGBAPRCS
DB package:

Copy

					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'
					)