Learner Responsive E2E Funding Data Definition 2009/2010

L_E2E_BONUS_SLN_P1 to L_E2E_BONUS_SLN_P12, L_E2E_BONUS_SLN_Y2D, L_E2E_BONUS_SLN_EFY, L_E2E_PROG_SLN_P1 to L_E2E_PROG_SLN_P12, L_E2E_PROG_SLN_Y2D, L_E2E_PROG_SLN_EFY, A_E2E_BONUS_SLN_P1 to A_E2E_BONUS_SLN_P12, A_E2E_BONUS_SLN_Y2D, A_E2E_BONUS_SLN_EFY, A_E2E_PROG_SLN_P1 to A_E2E_PROG_SLN_P12, A_E2E_PROG_SLN_Y2D, A_E2E_PROG_SLN_EFY


PURPOSE

1. To make Learner Responsive E2E programme and bonus funding information available 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.

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 ( LR )


SOURCE DATA

Field Name Label Dataset
E2E_PROGRAMME_SLN Learner Responsive E2E Programme SLN LR AIM_PERIOD
E2E_BONUS_SLN Learner Responsive E2E Bonus SLN LR AIM_PERIOD
PERIOD PERIOD LR AIM_PERIOD


DERIVED VARIABLES AND DATASETS

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

Field Name Label Dataset
A_E2E_BONUS_SLN_P1 to A_E2E_BONUS_SLN_P12

A_E2E_BONUS_SLN_P1 E2E Bonus SLN in period 1
to
A_E2E_BONUS_SLN_P12 E2E Bonus SLN in period 12.

 LR_AIMS_DLF
A_E2E_BONUS_SLN_Y2D A_E2E_BONUS_SLN_Y2D E2E Bonus SLN for year to date. LR_AIMS_DLF
A_E2E_BONUS_SLN_EFY A_E2E_BONUS_SLN_EFY E2E Bonus SLN Expected for Full Year. LR_AIMS_DLF

A_E2E_PROG_SLN_P1 to
A_E2E_PROG _SLN_P12

A_E2E_PROG_SLN_P1 E2E Programme SLN in period 1
to
A_E2E_PROG_SLN_P12 E2E Programme SLN in period 12

 LR_AIMS_DLF
A_E2E_PROG _SLN_Y2D A_E2E_PROG_SLN_Y2D E2E Programme SLN for year to date. LR_AIMS_DLF
A_E2E_PROG _SLN_EFY A_E2E_PROG_SLN_EFY E2E Programme SLN Expected for Full Year. LR_AIMS_DLF
L_E2E_PROG _PAYMENT_P1 to L_ E2E_PROG _PAYMENT_P12

L_E2E_BONUS_SLN_P1 Sum of E2E Bonus SLNs in period 1 for all aims of the learner
to
L_E2E_BONUS_SLN_P12 Sum of E2E Bonus SLNs in period 12 for all aims of the learner.

 LR_Learner_DLF
L_E2E_BONUS_SLN_Y2D L_E2E_BONUS_SLN_Y2D Sum of E2E Bonus SLN for year to date for all aims of the learner. LR_Learner_DLF
L_E2E_BONUS_SLN_EFY L_E2E_BONUS_SLN_EFY Sum of E2E Bonus SLN Expected for Full Year for all aims of the learner. LR_Learner_DLF

L_E2E_PROG_SLN_P1
to
L_E2E_PROG_SLN_P12

L_E2E_PROG_SLN_P1 Sum of E2E Programme SLN in period 1 for all aims of the learner
to
L_E2E_PROG_SLN_P12 Sum of E2E Programme SLN in period 12 for all aims of the learner.

 LR_Learner_DLF
L_E2E_PROG_SLN_Y2D L_E2E_PROG_SLN_Y2D Sum of E2E Programme SLN for year to date for all aims of the learner. LR_Learner_DLF
L_E2E_PROG_SLN_EFY L_E2E_PROG_SLN_EFY Sum of E2E Programme SLN Expected for Full Year for all aims of the learner. LR_Learner_DLF

DETAILED DEFINATION

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

5. These DVs make the E2E_PROGRAMME_SLN and E2E_BONUS_SLN values from the funding calculation’s Learner Responsive AIM_PERIOD data structure available to users. Learner level versions are also defined.

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

For LR:

Collection Current Period
F01 3
F02 6
F03 9
F04 12
F05 12


6. The following illustrates how periodic E2E Bonus SLNs are calculated at aim level:

A_E2E_BONUS_SLN_P1 = E2E_BONUS_SLN where PERIOD = 1.

A_E2E_BONUS_SLN_P2 = E2E_BONUS_SLN where PERIOD = 2.

A_E2E_BONUS_SLN_P3 = E2E_BONUS_SLN where PERIOD = 3.

A_E2E_BONUS_SLN_P4 = E2E_BONUS_SLN where PERIOD = 4.

A_E2E_BONUS_SLN_P5 = E2E_BONUS_SLN where PERIOD = 5.

A_E2E_BONUS_SLN_P6 = E2E_BONUS_SLN where PERIOD = 6.

A_E2E_BONUS_SLN_P7 = E2E_BONUS_SLN where PERIOD = 7.

A_E2E_BONUS_SLN_P8 = E2E_BONUS_SLN where PERIOD = 8.

A_E2E_BONUS_SLN_P9 = E2E_BONUS_SLN where PERIOD = 9.

A_E2E_BONUS_SLN_P10 = E2E_BONUS_SLN where PERIOD = 10.

A_E2E_BONUS_SLN_P11 = E2E_BONUS_SLN where PERIOD = 11.

A_E2E_BONUS_SLN_P12 = E2E_BONUS_SLN where PERIOD = 12.

A_E2E_BONUS_SLN_Y2D = sum of E2E_BONUS_SLN for PERIOD from 1 to current period.

A_E2E_BONUS_SLN_EFY = sum of E2E_BONUS_SLN for all PERIODs.


7. The following SQL code illustrates how periodic E2E Bonus SLNs are calculated at aim level:

select

      L01, L03, A05
      , case when B.PERIOD = 1 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P1
      , case when B.PERIOD = 2 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P2
      , case when B.PERIOD = 3 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P3
      , case when B.PERIOD = 4 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P4
      , case when B.PERIOD = 5 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P5
      , case when B.PERIOD = 6 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P6
      , case when B.PERIOD = 7 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P7
      , case when B.PERIOD = 8 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P8
      , case when B.PERIOD = 9 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P9
      , case when B.PERIOD = 10 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P10
      , case when B.PERIOD = 11 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P11
      , case when B.PERIOD = 12 then E2E_BONUS_SLN else 0 end as A_E2E_BONUS_SLN_P12
      , case when B.PERIOD between 1 and A.PERIOD then sum(E2E_BONUS_SLN) else 0 end as A_E2E_BONUS_SLN_Y2D
      , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(E2E_BONUS_SLN) else 0 end as A_E2E_BONUS_SLN_EFY

from

      ILR0910_LR_AIMS_DLF A

      left outer join

      LR_AIM_PERIOD B

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



8. The following illustrates how periodic E2E Bonus SLNs are calculated at learner level:

L_E2E_BONUS_SLN_P1 = sum of A_E2E_BONUS_SLN_P1 for all aims of the learner.

L_E2E_BONUS_SLN_P2 = sum of A_E2E_BONUS_SLN_P2 for all aims of the learner.

L_E2E_BONUS_SLN_P3 = sum of A_E2E_BONUS_SLN_P3 for all aims of the learner.

L_E2E_BONUS_SLN_P4 = sum of A_E2E_BONUS_SLN_P4 for all aims of the learner.

L_E2E_BONUS_SLN_P5 = sum of A_E2E_BONUS_SLN_P5 for all aims of the learner.

L_E2E_BONUS_SLN_P6 = sum of A_E2E_BONUS_SLN_P6 for all aims of the learner.

L_E2E_BONUS_SLN_P7 = sum of A_E2E_BONUS_SLN_P7 for all aims of the learner.

L_E2E_BONUS_SLN_P8 = sum of A_E2E_BONUS_SLN_P8 for all aims of the learner.

L_E2E_BONUS_SLN_P9 = sum of A_E2E_BONUS_SLN_P9 for all aims of the learner.

L_E2E_BONUS_SLN_P10 = sum of A_E2E_BONUS_SLN_P10 for all aims of the learner.

L_E2E_BONUS_SLN_P11 = sum of A_E2E_BONUS_SLN_P11 for all aims of the learner.

L_E2E_BONUS_SLN_P12 = sum of A_E2E_BONUS_SLN_P12 for all aims of the learner.

L_E2E_BONUS_SLN_Y2D = sum of A_E2E_BONUS_SLN_Y2D for all aims of the learner. L_E2E_BONUS_SLN_EFY = sum of A_E2E_BONUS_SLN_EFY for all aims of the learner.


9. The following SQL code illustrates how periodic E2E Bonus SLNs are calculated at learner level:

select

      L01, L03, count(a05) as aims
      , sum(A_E2E_BONUS_SLN_P1) as L_E2E_BONUS_SLN_P1
      , sum(A_E2E_BONUS_SLN_P2) as L_E2E_BONUS_SLN_P2
      , sum(A_E2E_BONUS_SLN_P3) as L_E2E_BONUS_SLN_P3
      , sum(A_E2E_BONUS_SLN_P4) as L_E2E_BONUS_SLN_P4
      , sum(A_E2E_BONUS_SLN_P5) as L_E2E_BONUS_SLN_P5
      , sum(A_E2E_BONUS_SLN_P6) as L_E2E_BONUS_SLN_P6
      , sum(A_E2E_BONUS_SLN_P7) as L_E2E_BONUS_SLN_P7
      , sum(A_E2E_BONUS_SLN_P8) as L_E2E_BONUS_SLN_P8
      , sum(A_E2E_BONUS_SLN_P9) as L_E2E_BONUS_SLN_P9
      , sum(A_E2E_BONUS_SLN_P10) as L_E2E_BONUS_SLN_P10
      , sum(A_E2E_BONUS_SLN_P11) as L_E2E_BONUS_SLN_P11
      , sum(A_E2E_BONUS_SLN_P12) as L_E2E_BONUS_SLN_P12
      , sum(A_E2E_BONUS_SLN_Y2D) as L_E2E_BONUS_SLN_Y2D
      , sum(A_E2E_BONUS_SLN_EFY) as L_E2E_BONUS_SLN_EFY

from

      ILR0910_LR_AIMS_DLF

group by

      L01, L03

order by

      L01, L03



10. The following illustrates how periodic E2E Programme SLNs are calculated at aim level:

A_E2E_PROG_SLN_P1 = E2E_PROGRAMME_SLN where PERIOD = 1.

A_E2E_PROG_SLN_P2 = E2E_PROGRAMME_SLN where PERIOD = 2.

A_E2E_PROG_SLN_P3 = E2E_PROGRAMME_SLN where PERIOD = 3.

A_E2E_PROG_SLN_P4 = E2E_PROGRAMME_SLN where PERIOD = 4.

A_E2E_PROG_SLN_P5 = E2E_PROGRAMME_SLN where PERIOD = 5.

A_E2E_PROG_SLN_P6 = E2E_PROGRAMME_SLN where PERIOD = 6.

A_E2E_PROG_SLN_P7 = E2E_PROGRAMME_SLN where PERIOD = 7.

A_E2E_PROG_SLN_P8 = E2E_PROGRAMME_SLN where PERIOD = 8.

A_E2E_PROG_SLN_P9 = E2E_PROGRAMME_SLN where PERIOD = 9.

A_E2E_PROG_SLN_P10 = E2E_PROGRAMME_SLN where PERIOD = 10.

A_E2E_PROG_SLN_P11 = E2E_PROGRAMME_SLN where PERIOD = 11.

A_E2E_PROG_SLN_P12 = E2E_PROGRAMME_SLN where PERIOD = 12.

A_E2E_PROG_SLN_Y2D = sum of E2E_PROGRAMME_SLN for PERIOD from 1 to current period.

A_E2E_PROG_SLN_EFY = sum of E2E_PROGRAMME_SLN for all PERIODs.


11. The following SQL code illustrates how periodic E2E Programme SLNs are calculated at aim level:

select

      L01, L03, A05
      , case when B.PERIOD = 1 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P1
      , case when B.PERIOD = 2 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P2
      , case when B.PERIOD = 3 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P3
      , case when B.PERIOD = 4 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P4
      , case when B.PERIOD = 5 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P5
      , case when B.PERIOD = 6 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P6
      , case when B.PERIOD = 7 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P7
      , case when B.PERIOD = 8 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P8
     , case when B.PERIOD = 9 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P9
     , case when B.PERIOD = 10 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P10
     , case when B.PERIOD = 11 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P11
     , case when B.PERIOD = 12 then E2E_PROGRAMME_SLN else 0 end as A_E2E_PROG_SLN_P12
     , case when B.PERIOD between 1 and A.PERIOD then sum(E2E_PROGRAMME_SLN) else 0 end as A_E2E_PROG_SLN_Y2D
     , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(E2E_PROGRAMME_SLN) else 0 end as A_E2E_PROG_SLN_EFY

from

      LR0910_LR_AIMS_DLF A

      left outer join

      LR_AIM_PERIOD B

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



12. The following illustrates how periodic E2E Programme SLNs are calculated at learner level:

L_E2E_PROG_SLN_P1 = sum of A_E2E_PROG_SLN_P1 for all aims of the learner.

L_E2E_PROG_SLN_P2 = sum of A_E2E_PROG_SLN_P2 for all aims of the learner.

L_E2E_PROG_SLN_P3 = sum of A_E2E_PROG_SLN_P3 for all aims of the learner.

L_E2E_PROG_SLN_P4 = sum of A_E2E_PROG_SLN_P4 for all aims of the learner.

L_E2E_PROG_SLN_P5 = sum of A_E2E_PROG_SLN_P5 for all aims of the learner.

L_E2E_PROG_SLN_P6 = sum of A_E2E_PROG_SLN_P6 for all aims of the learner.

L_E2E_PROG_SLN_P7 = sum of A_E2E_PROG_SLN_P7 for all aims of the learner.

L_E2E_PROG_SLN_P8 = sum of A_E2E_PROG_SLN_P8 for all aims of the learner.

L_E2E_PROG_SLN_P9 = sum of A_E2E_PROG_SLN_P9 for all aims of the learner.

L_E2E_PROG_SLN_P10 = sum of A_E2E_PROG_SLN_P10 for all aims of the learner.

L_E2E_PROG_SLN_P11 = sum of A_E2E_PROG_SLN_P11 for all aims of the learner.

L_E2E_PROG_SLN_P12 = sum of A_E2E_PROG_SLN_P12 for all aims of the learner.

L_E2E_PROG_SLN_Y2D = sum of A_E2E_PROG_SLN_Y2D for all aims of the learner.

L_E2E_PROG_SLN_EFY = sum of A_E2E_PROG_SLN_EFY for all aims of the learner.


13. The following SQL code illustrates how periodic E2E Programme SLNs are calculated at learner level:

select

      L01, L03, count(a05) as aims
      , sum(A_E2E_PROG_SLN_P1) as L_E2E_PROG_SLN_P1
      , sum(A_E2E_PROG_SLN_P2) as L_E2E_PROG_SLN_P2
      , sum(A_E2E_PROG_SLN_P3) as L_E2E_PROG_SLN_P3
      , sum(A_E2E_PROG_SLN_P4) as L_E2E_PROG_SLN_P4
      , sum(A_E2E_PROG_SLN_P5) as L_E2E_PROG_SLN_P5
      , sum(A_E2E_PROG_SLN_P6) as L_E2E_PROG_SLN_P6
      , sum(A_E2E_PROG_SLN_P7) as L_E2E_PROG_SLN_P7
      , sum(A_E2E_PROG_SLN_P8) as L_E2E_PROG_SLN_P8
      , sum(A_E2E_PROG_SLN_P9) as L_E2E_PROG_SLN_P9
      , sum(A_E2E_PROG_SLN_P10) as L_E2E_PROG_SLN_P10
      , sum(A_E2E_PROG_SLN_P11) as L_E2E_PROG_SLN_P11
      , sum(A_E2E_PROG_SLN_P12) as L_E2E_PROG_SLN_P12
      , sum(A_E2E_PROG_SLN_Y2D) as L_E2E_PROG_SLN_Y2D
      , sum(A_E2E_PROG_SLN_EFY) as L_E2E_PROG_SLN_EFY

from

      ILR0910_LR_AIMS_DLF

group by

      L01, L03

order by

      L01, L03




Date Last Modified : 09th December 2009