ER Achievement Funding Data Definition 2009/2010

A_ACH_PAYMENT_P1 to A_ACH_PAYMENT_P12, A_ACH_PAYMENT_Y2D, A_ACH_PAYMENT_EFY, A_ACH_SLN_P1 to A_ACH_SLN_P12, A_ACH_SLN_Y2D, A_ACH_SLN_EFY, L_ACH_PAYMENT_P1 to L_ACH_PAYMENT_P12, L_ACH_PAYMENT_Y2D, L_ACH_PAYMENT_EFY, L_ACH_SLN_P1 to L_ACH_SLN_P12, L_ACH_SLN_Y2D, L_ACH_SLN_EFY

PURPOSE

1. To enable achievement funding information to be used from the funding calculation outputs.

2. These variables were defined in the MI views specification in 2008/09. New variables have been added to display the expected payments and SLN for the full year.

3. Note that these variables should still be calculated for the entitlement aims produced by the funding calculation, which are identified by A05=99.

RELEVANT COLLECTIONS

ILR (ER)

SOURCE DATA

Field Name	Label	Dataset
ER_ACH_CASH	Employer Achievement Payment	ER AIM_PERIOD
ACH_SLN_INST	Achievement SLN Instalment	ER AIM_PERIOD
PERIOD	PERIOD	ER AIM_PERIOD

DERIVED VARIABLES AND DATASETS

4. The definitions produce the following derived variable(s)

Field Name	Label	Dataset
A_ACH_PAYMENT_P1 to A_ACH_PAYMENT_P12	A_ACH_PAYMENT_P1 Achievement Payment in period 1 to A_ACH_PAYMENT_P12 Achievement Payment in period 12.	ER_AIMS_DLF
A_ACH_PAYMENT_Y2D	A_ACH_PAYMENT_Y2D Achievement Payment for year to date.	ER_AIMS_DLF
A_ACH_PAYMENT_EFY	A_ACH_PAYMENT_EFY Achievement Payment Expected for Full Year.	ER_AIMS_DLF
A_ACH_SLN_P1 to A_ACH_SLN_P12	A_ACH_SLN_P1 Achievement SLN in period 1 to A_ACH_SLN_P12 Achievement SLN in period 12	ER_AIMS_DLF
A_ACH_SLN_Y2D	A_ACH_SLN_Y2D Achievement SLN for year to date.	ER_AIMS_DLF
A_ACH_SLN_EFY	A_ACH_SLN_EFY Achievement SLN Expected for Full Year.	ER_AIMS_DLF
L_ACH_PAYMENT_P1 to L_ACH_PAYMENT_P12	L_ACH_PAYMENT_P1 Sum of Achievement Payments in period 1 for all aims of the learner to L_ACH_PAYMENT_P12 Sum of Achievement Payments in period 12 for all aims of the learner.	ER_Learner_DLF
L_ACH_PAYMENT_Y2D	L_ACH_PAYMENT_Y2D Sum of Achievement Payment for year to date for all aims of the learner.	ER_Learner_DLF
L_ACH_PAYMENT_EFY	L_ACH_PAYMENT_EFY Achievement Payment Expected for Full Year for all aims of the learner.	ER_Learner_DLF
L_ACH_SLN_P1 to L_ACH_SLN_P12	L_ACH_SLN_P1 Sum of Achievement SLN in period 1 for all aims of the learner to L_ACH_SLN_P12 Sum of Achievement SLN in period 12 for all aims of the learner.	ER_Learner_DLF
L_ACH_SLN_Y2D	L_ACH_SLN_P12 Sum of Achievement SLN for year to date for all aims of the learner.	ER_Learner_DLF
L_ACH_SLN_EFY	A_ACH_SLN_EFY Achievement SLN Expected for Full Year for all aims of the learner.	ER_Learner_DLF

DETAILED DEFINATION

5. These variables are dependant on output from the funding calculation. The funding calculation must have completed before these variables are derived.

6. These DVs make the ER_ACH_CASH and ER_ACH_SLN values from the funding calculation’s Employer Responsive AIM_PERIOD data structure available to users. Learner level versions are also defined.

7. Current period is defined by the collection and is as follows:

Collection	Current Period
W01	1
W02	2
W03	3
W04	4
W05	5
W06	6
W07	7
W08	8
W09	9
W10	10
W11	11
W12	12
W13	12

8. The following illustrates how to calculate sum of periodic Achievement Payments at the Aim Level:

A_ACH_PAYMENT_P1 = ER_ACH_CASH where PERIOD = 1.
A_ACH_PAYMENT_P2 = ER_ACH_CASH where PERIOD = 2.
A_ACH_PAYMENT_P3 = ER_ACH_CASH where PERIOD = 3.
A_ACH_PAYMENT_P4 = ER_ACH_CASH where PERIOD = 4.
A_ACH_PAYMENT_P5 = ER_ACH_CASH where PERIOD = 5.
A_ACH_PAYMENT_P6 = ER_ACH_CASH where PERIOD = 6.
A_ACH_PAYMENT_P7 = ER_ACH_CASH where PERIOD = 7.
A_ACH_PAYMENT_P8 = ER_ACH_CASH where PERIOD = 8.
A_ACH_PAYMENT_P9 = ER_ACH_CASH where PERIOD = 9.
A_ACH_PAYMENT_P10 = ER_ACH_CASH where PERIOD = 10.
A_ACH_PAYMENT_P11 = ER_ACH_CASH where PERIOD = 11.
A_ACH_PAYMENT_P12 = ER_ACH_CASH where PERIOD = 12.
A_ACH_PAYMENT_Y2D = sum of ER_ACH_CASH for PERIOD from 1 to current period.
A_ACH_PAYMENT_EFY = sum of ER_ACH_CASH for all PERIODs.

9. The following SQL code illustrates how to calculate periodic Achievement Payments at the Aim Level:

select L01 ,L03, A05 , case when B.PERIOD = 1 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P1 , case when B.PERIOD = 2 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P2 , case when B.PERIOD = 3 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P3 , case when B.PERIOD = 4 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P4 , case when B.PERIOD = 5 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P5 , case when B.PERIOD = 6 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P6 , case when B.PERIOD = 7 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P7 , case when B.PERIOD = 8 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P8 , case when B.PERIOD = 9 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P9 , case when B.PERIOD = 10 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P10 , case when B.PERIOD = 11 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P11 , case when B.PERIOD = 12 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P12 , case when B.PERIOD between 1 and A.PERIOD then sum(ER_ACH_CASH) else 0 end as A_ACH_PAYMENT_Y2D , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(ER_ACH_CASH) else 0 end as A_ACH_PAYMENT_EFY from ILR0910_E_AIMS_DLF A left outer join ER_AIM_PERIOD B on A.L01=B.L01 and A.L03=B.L03 and A.A05=B.A05

select

     L01 ,L03, A05
     , case when B.PERIOD = 1 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P1
     , case when B.PERIOD = 2 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P2
     , case when B.PERIOD = 3 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P3
     , case when B.PERIOD = 4 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P4
     , case when B.PERIOD = 5 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P5
     , case when B.PERIOD = 6 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P6
     , case when B.PERIOD = 7 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P7
     , case when B.PERIOD = 8 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P8
     , case when B.PERIOD = 9 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P9
     , case when B.PERIOD = 10 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P10
     , case when B.PERIOD = 11 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P11
     , case when B.PERIOD = 12 then ER_ACH_CASH else 0 end as A_ACH_PAYMENT_P12
     , case when B.PERIOD between 1 and A.PERIOD then sum(ER_ACH_CASH) else 0 end as A_ACH_PAYMENT_Y2D
     , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(ER_ACH_CASH) else 0 end as A_ACH_PAYMENT_EFY

from

ILR0910_E_AIMS_DLF A

left outer join

ER_AIM_PERIOD B

on A.L01=B.L01 and A.L03=B.L03 and A.A05=B.A05

10. The following illustrates how to calculate sum of periodic Achievement Payments at the Learner Level:

L_ACH_PAYMENT_P1 = sum of A_ACH_PAYMENT_P1 for all aims of the learner.
L_ACH_PAYMENT_P2 = sum of A_ACH_PAYMENT_P2 for all aims of the learner.
L_ACH_PAYMENT_P3 = sum of A_ACH_PAYMENT_P3 for all aims of the learner.
L_ACH_PAYMENT_P4 = sum of A_ACH_PAYMENT_P4 for all aims of the learner.
L_ACH_PAYMENT_P5 = sum of A_ACH_PAYMENT_P5 for all aims of the learner.
L_ACH_PAYMENT_P6 = sum of A_ACH_PAYMENT_P6 for all aims of the learner.
L_ACH_PAYMENT_P7 = sum of A_ACH_PAYMENT_P7 for all aims of the learner.
L_ACH_PAYMENT_P8 = sum of A_ACH_PAYMENT_P8 for all aims of the learner.
L_ACH_PAYMENT_P9 = sum of A_ACH_PAYMENT_P9 for all aims of the learner.
L_ACH_PAYMENT_P10 = sum of A_ACH_PAYMENT_P10 for all aims of the learner.
L_ACH_PAYMENT_P11 = sum of A_ACH_PAYMENT_P11 for all aims of the learner.
L_ACH_PAYMENT_P12 = sum of A_ACH_PAYMENT_P12 for all aims of the learner.
L_ACH_PAYMENT_Y2D = sum of A_ACH_PAYMENT_Y2D for all aims of the learner.
L_ACH_PAYMENT_EFY = sum of A_ACH_PAYMENT_EFY for all aims of the learner.

11. The following SQL code illustrates how to calculate sum of periodic Achievement Payments at the Learner Level:


select --use of count() is to aggregate to learner level L01, L03, count(a05) as aims , sum(A_ACH_PAYMENT_P1) as L_ACH_PAYMENT_P1 , sum(A_ACH_PAYMENT_P2) as L_ACH_PAYMENT_P2 , sum(A_ACH_PAYMENT_P3) as L_ACH_PAYMENT_P3 , sum(A_ACH_PAYMENT_P4) as L_ACH_PAYMENT_P4 , sum(A_ACH_PAYMENT_P5) as L_ACH_PAYMENT_P5 , sum(A_ACH_PAYMENT_P6) as L_ACH_PAYMENT_P6 , sum(A_ACH_PAYMENT_P7) as L_ACH_PAYMENT_P7 , sum(A_ACH_PAYMENT_P8) as L_ACH_PAYMENT_P8 , sum(A_ACH_PAYMENT_P9) as L_ACH_PAYMENT_P9 , sum(A_ACH_PAYMENT_P10) as L_ACH_PAYMENT_P10 , sum(A_ACH_PAYMENT_P11) as L_ACH_PAYMENT_P11 , sum(A_ACH_PAYMENT_P12) as L_ACH_PAYMENT_P12 , sum(A_ACH_PAYMENT_Y2D) as L_ACH_PAYMENT_Y2D , sum(A_ACH_PAYMENT_EFY) as L_ACH_PAYMENT_EFY from ILR0910_E_AIMS_DLF group by L01, L03 order by L01, L03

12. The following illustrates how to calculate sum of periodic Achievement SLNs at the Aim Level:

A_ACH_SLN_P1 = ACH_SLN_INST where PERIOD = 1.
A_ACH_SLN_P2 = ACH_SLN_INST where PERIOD = 2.
A_ACH_SLN_P3 = ACH_SLN_INST where PERIOD = 3.
A_ACH_SLN_P4 = ACH_SLN_INST where PERIOD = 4.
A_ACH_SLN_P5 = ACH_SLN_INST where PERIOD = 5.
A_ACH_SLN_P6 = ACH_SLN_INST where PERIOD = 6.
A_ACH_SLN_P7 = ACH_SLN_INST where PERIOD = 7.
A_ACH_SLN_P8 = ACH_SLN_INST where PERIOD = 8.
A_ACH_SLN_P9 = ACH_SLN_INST where PERIOD = 9.
A_ACH_SLN_P10 = ACH_SLN_INST where PERIOD = 10.
A_ACH_SLN_P11 = ACH_SLN_INST where PERIOD = 11.
A_ACH_SLN_P12 = ACH_SLN_INST where PERIOD = 12.
A_ACH_SLN_Y2D = sum of ACH_SLN_INST for PERIOD from 1 to current period.
A_ACH_SLN_EFY = sum of ACH_SLN_INST for all PERIODs.

13. The following illustrates how to calculate sum of periodic Achievement SLNs at the Aim Level:

select

     L01 ,L03, A05
     , case when B.PERIOD = 1 then ACH_SLN_INST else 0 end as A_ACH_SLN_P1
     , case when B.PERIOD = 2 then ACH_SLN_INST else 0 end as A_ACH_SLN_P2
     , case when B.PERIOD = 3 then ACH_SLN_INST else 0 end as A_ACH_SLN_P3
     , case when B.PERIOD = 4 then ACH_SLN_INST else 0 end as A_ACH_SLN_P4
     , case when B.PERIOD = 5 then ACH_SLN_INST else 0 end as A_ACH_SLN_P5
     , case when B.PERIOD = 6 then ACH_SLN_INST else 0 end as A_ACH_SLN_P6
     , case when B.PERIOD = 7 then ACH_SLN_INST else 0 end as A_ACH_SLN_P7
     , case when B.PERIOD = 8 then ACH_SLN_INST else 0 end as A_ACH_SLN_P8
     , case when B.PERIOD = 9 then ACH_SLN_INST else 0 end as A_ACH_SLN_P9
     , case when B.PERIOD = 10 then ACH_SLN_INST else 0 end as A_ACH_SLN_P10
     , case when B.PERIOD = 11 then ACH_SLN_INST else 0 end as A_ACH_SLN_P11
     , case when B.PERIOD = 12 then ACH_SLN_INST else 0 end as A_ACH_SLN_P12
     , case when B.PERIOD between 1 and A.PERIOD then sum(ACH_SLN_INST) else 0 end as A_ACH_SLN_Y2D
     , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(ACH_SLN_INST) else 0 end as A_ACH_SLN_EFY

from

ILR0910_E_AIMS_DLF A

left outer join

ER_AIM_PERIOD B

on A.L01=B.L01 and A.L03=B.L03 and A.A05=B.A05

14. The following illustrates how to calculate sum of periodic Achievement SLNs at the Aim Level:

L_ACH_SLN_p1 = sum of A_ACH_SLN_P1 for all aims of the learner.
L_ACH_SLN_p2 = sum of A_ACH_SLN_P2 for all aims of the learner.
L_ACH_SLN_p3 = sum of A_ACH_SLN_P3 for all aims of the learner.
L_ACH_SLN_p4 = sum of A_ACH_SLN_P4 for all aims of the learner.
L_ACH_SLN_p5 = sum of A_ACH_SLN_P5 for all aims of the learner.
L_ACH_SLN_p6 = sum of A_ACH_SLN_P6 for all aims of the learner.
L_ACH_SLN_p7 = sum of A_ACH_SLN_P7 for all aims of the learner.
L_ACH_SLN_p8 = sum of A_ACH_SLN_P8 for all aims of the learner.
L_ACH_SLN_p9 = sum of A_ACH_SLN_P9 for all aims of the learner.
L_ACH_SLN_p10 = sum of A_ACH_SLN_P10 for all aims of the learner.
L_ACH_SLN_p11 = sum of A_ACH_SLN_P11 for all aims of the learner.
L_ACH_SLN_p12 = sum of A_ACH_SLN_P12 for all aims of the learner.
L_ACH_SLN_Y2D = sum of A_ACH_SLN_Y2D for all aims of the learner.
L_ACH_SLN_EFY = sum of A_ACH_SLN_EFY for all aims of the learner.

15. The following SQL code illustrates how to calculate calculate sum of periodic Achievement SLNs at the Learner Level:


select --use of count() is to aggregate to learner level L01, L03, count(a05) as aims , sum(A_ACH_SLN_P1) as L_ACH_SLN_P1 , sum(A_ACH_SLN_P2) as L_ACH_SLN_P2 , sum(A_ACH_SLN_P3) as L_ACH_SLN_P3 , sum(A_ACH_SLN_P4) as L_ACH_SLN_P4 , sum(A_ACH_SLN_P5) as L_ACH_SLN_P5 , sum(A_ACH_SLN_P6) as L_ACH_SLN_P6 , sum(A_ACH_SLN_P7) as L_ACH_SLN_P7 , sum(A_ACH_SLN_P8) as L_ACH_SLN_P8 , sum(A_ACH_SLN_P9) as L_ACH_SLN_P9 , sum(A_ACH_SLN_P10) as L_ACH_SLN_P10 , sum(A_ACH_SLN_P11) as L_ACH_SLN_P11 , sum(A_ACH_SLN_P12) as L_ACH_SLN_P12 , sum(A_ACH_SLN_Y2D) as L_ACH_SLN_Y2D , sum(A_ACH_SLN_EFY) as L_ACH_SLN_EFY from ILR0910_E_AIMS_DLF group by L01, L03 order by L01, L03

Date last modified: 08nd December 2009