PAY GB Apprenticeship Levy Costing Sheet

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