ALS payment data definition 2009/2010

A_ALS_PAYMENT_P1 to A_ALS_PAYMENT_P12, A_ALS_PAYMENT_Y2D, A_ALS_PAYMENT_EFY, L_ALS_PAYMENT_P1 to L_ALS_PAYMENT_P12, L_ALS_PAYMENT_Y2D, L_ALS_PAYMENT_EFY 



PURPOSE

1. To make ALS payment 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.

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

SOURCE DATA

4. Variables used as source data for the calculation of ALS Payment

Field Name Label Dataset
ER_ALS_PAYMENT Employer Responsive Additional Learning Support Payment ER AIM_PERIOD
LR_ALS_CASH Learner Responsive Additional Learning Support Payment LR AIM_PERIOD
PERIOD PERIOD

ER AIM_PERIOD and LR AIM_PERIOD


DERIVED VARIABLES AND DATASETS

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

Field Name Label Dataset
A_ALS_PAYMENT_P1 to A_ALS_PAYMENT_P12

A_ALS_PAYMENT_P1 Additional Learner Support payment in period 1; to A_ALS_PAYMENT_P12 Additional Learner Support payment in period 12.

ER_AIMS_DLF and LR_AIMS_DLF

A_ALS_PAYMENT_Y2D A_ALS_PAYMENT_Y2D Additional Learner Support payment for year to date.

ER_AIMS_DLF and LR_AIMS_DLF

A_ALS_PAYMENT_EFY A_ALS_PAYMENT_EFY Additional Learner Support Payment Expected for Full Year.

ER_AIMS_DLF and LR_AIMS_DLF

L_ALS_PAYMENT_P1 to L_ALS_PAYMENT_P12

L_ALS_PAYMENT_P1 Sum of Additional Learner Support payments in period 1 for all aims of the learner; to L_ALS_PAYMENT_P12 Sum of Additional Learner Support payments in period 12 for all aims of the learner.

ER_Learner_DLF and LR_Learner_DLF

L_ALS_PAYMENT_Y2D L_ALS_PAYMENT_Y2D Sum of Additional Learner Support payments in year to date for all aims of the learner.

ER_Learner_DLF and LR_Learner_DLF

L_ALS_PAYMENT_EFY L_ALS_PAYMENT_EFY Sum of Additional Learner Support Payments Expected for Full Year, for all aims of the learner.

ER_Learner_DLF and LR_Learner_DLF


DETAILED DEFINITION

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

These DVs make the ER_ALS_PAYMENT or LR_ALS_CASH values from the funding calculation’s AIM_PERIOD data structures available to users. Learner level versions are also defined.

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

For ER:

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

For LR:

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


7. The following illustrates how to calculate ALS Payment at the Aim Level:

A_ALS_PAYMENT_P1 = For ER: (ER_ALS_PAYMENT where PERIOD = 1) For LRLR_ALS_CASH where PERIOD = P1)

A_ALS_PAYMENT_P2 = For ER: (ER_ALS_PAYMENT where PERIOD = 2) For LRLR_ALS_CASH where PERIOD = P2)

A_ALS_PAYMENT_P3 = For ER: (ER_ALS_PAYMENT where PERIOD = 3) For LRLR_ALS_CASH where PERIOD = P3)

A_ALS_PAYMENT_P4 = For ER: (ER_ALS_PAYMENT where PERIOD = 4) For LRLR_ALS_CASH where PERIOD = P4)

A_ALS_PAYMENT_P5 = For ER: (ER_ALS_PAYMENT where PERIOD = 5) For LRLR_ALS_CASH where PERIOD = P5)

A_ALS_PAYMENT_P6 = For ER: (ER_ALS_PAYMENT where PERIOD = 6) For LRLR_ALS_CASH where PERIOD = P6)

A_ALS_PAYMENT_P7 = For ER: (ER_ALS_PAYMENT where PERIOD = 7) For LRLR_ALS_CASH where PERIOD = P7)

A_ALS_PAYMENT_P8 = For ER: (ER_ALS_PAYMENT where PERIOD = 8) For LRLR_ALS_CASH where PERIOD = P8)

A_ALS_PAYMENT_P9 = For ER: (ER_ALS_PAYMENT where PERIOD = 9) For LRLR_ALS_CASH where PERIOD = P9)

A_ALS_PAYMENT_P10 = For ER: (ER_ALS_PAYMENT where PERIOD = 10) For LRLR_ALS_CASH where PERIOD = P10)

A_ALS_PAYMENT_P11 = For ER: (ER_ALS_PAYMENT where PERIOD = 11) For LRLR_ALS_CASH where PERIOD = P11)

A_ALS_PAYMENT_P12 = For ER: (ER_ALS_PAYMENT where PERIOD = 12) For LR: (LR_ALS_CASH where PERIOD = P12)

A_ALS_PAYMENT_Y2D = For ER: (sum of ER_ALS_PAYMENT for PERIOD from 1 to current period) For LR: (sum of LR_ALS_CASH for PERIOD from 1 to current period).

A_ALS_PAYMENT_EFY = For ER: (sum of ER_ALS_PAYMENT for all PERIODs) For LR: (sum of LR_ALS_CASH for all PERIODs).



8. The following SQL code illustrates how to calculate periodic ALS Payments at Aim Level for ER:

-- First part for A_ALS_PAYMENT_P1-12

select

      L01, L03, A05
      , case when B.PERIOD = 1 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P1
      , case when B.PERIOD = 2 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P2
      , case when B.PERIOD = 3 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P3
      , case when B.PERIOD = 4 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P4
      , case when B.PERIOD = 5 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P5
      , case when B.PERIOD = 6 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P6
      , case when B.PERIOD = 7 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P7
      , case when B.PERIOD = 8 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P8
      , case when B.PERIOD = 9 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P9
      , case when B.PERIOD = 10 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P10
      , case when B.PERIOD = 11 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P11
      , case when B.PERIOD = 12 then ER_ALS_PAYMENT else 0 end as A_ALS_PAYMENT_P12

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

--2nd part for A_ALS_PAYMENT_Y2D and EFY

select

      L01, L03, A05
      , case when B.PERIOD between 1 and A.PERIOD then sum(ER_ALS_PAYMENT) else 0 end as A_ALS_PAYMENT_Y2D
      , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(ER_ALS_PAYMENT) else 0 end as A_ALS_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

group by

      L01, L03, A05, PERIOD




9. The following SQL code illustrates how to calculate periodic ALS Payments at Aim Level for LR:

-- First part for A_ALS_PAYMENT_P1-12

select

      L01, L03, A05
      , case when B.PERIOD = 1 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P1
      , case when B.PERIOD = 2 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P2
      , case when B.PERIOD = 3 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P3
      , case when B.PERIOD = 4 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P4
      , case when B.PERIOD = 5 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P5
      , case when B.PERIOD = 6 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P6
      , case when B.PERIOD = 7 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P7
      , case when B.PERIOD = 8 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P8
      , case when B.PERIOD = 9 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P9
      , case when B.PERIOD = 10 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P10
      , case when B.PERIOD = 11 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P11
      , case when B.PERIOD = 12 then LR_ALS_CASH else 0 end as A_ALS_PAYMENT_P12

from

      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

--2nd part for A_ALS_PAYMENT_Y2D and EFY

select

      L01, L03, A05
      , case when B.PERIOD between 1 and A.PERIOD then sum(LR_ALS_CASH) else 0 end as A_ALS_PAYMENT_Y2D
      , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(LR_ALS_CASH) else 0 end as A_ALS_PAYMENT_EFY

from

      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

group by

      L01, L03, A05, PERIOD



10. The following illustrates how to calculate ALS Payment at the Learner Level:

L_ALS_PAYMENT_P1 = sum of A_ALS_PAYMENT_P1 for all aims of the learner.

L_ALS_PAYMENT_P2 = sum of A_ALS_PAYMENT_P2 for all aims of the learner.

L_ALS_PAYMENT_P3 = sum of A_ALS_PAYMENT_P3 for all aims of the learner.

L_ALS_PAYMENT_P4 = sum of A_ALS_PAYMENT_P4 for all aims of the learner.

L_ALS_PAYMENT_P5 = sum of A_ALS_PAYMENT_P5 for all aims of the learner.

L_ALS_PAYMENT_P6 = sum of A_ALS_PAYMENT_P6 for all aims of the learner.

L_ALS_PAYMENT_P7 = sum of A_ALS_PAYMENT_P7 for all aims of the learner.

L_ALS_PAYMENT_P8 = sum of A_ALS_PAYMENT_P8 for all aims of the learner.

L_ALS_PAYMENT_P9 = sum of A_ALS_PAYMENT_P9 for all aims of the learner.

L_ALS_PAYMENT_P10 = sum of A_ALS_PAYMENT_P10 for all aims of the learner.

L_ALS_PAYMENT_P11 = sum of A_ALS_PAYMENT_P11 for all aims of the learner.

L_ALS_PAYMENT_P12 = sum of A_ALS_PAYMENT_P12 for all aims of the learner.

L_ALS_PAYMENT_Y2D = sum of A_ALS_PAYMENT_Y2D for all aims of the learner.

L_ALS_PAYMENT_EFY = sum of A_ALS_PAYMENT_EFY for all aims of the learner.


11. The following SQL code illustrates how to calculate ALS Payment at the Learner Level:

select

--use of count() to aggregate at learner level
     L01, L03, count(a05) as aims
     , sum(A_ALS_PAYMENT_P1) as L_ALS_PAYMENT_P1
     , sum(A_ALS_PAYMENT_P2) as L_ALS_PAYMENT_P2
     , sum(A_ALS_PAYMENT_P3) as L_ALS_PAYMENT_P3
     , sum(A_ALS_PAYMENT_P4) as L_ALS_PAYMENT_P4
     , sum(A_ALS_PAYMENT_P5) as L_ALS_PAYMENT_P5
     , sum(A_ALS_PAYMENT_P6) as L_ALS_PAYMENT_P6
     , sum(A_ALS_PAYMENT_P7) as L_ALS_PAYMENT_P7
     , sum(A_ALS_PAYMENT_P8) as L_ALS_PAYMENT_P8
     , sum(A_ALS_PAYMENT_P9) as L_ALS_PAYMENT_P9
     , sum(A_ALS_PAYMENT_P10) as L_ALS_PAYMENT_P10
     , sum(A_ALS_PAYMENT_P11) as L_ALS_PAYMENT_P11
     , sum(A_ALS_PAYMENT_P12) as L_ALS_PAYMENT_P12
     , sum(A_ALS_PAYMENT_Y2D) as L_ALS_PAYMENT_Y2D
     , sum(A_ALS_PAYMENT_EFY) as L_ALS_PAYMENT_EFY

from

    ILR0910_E_AIMS_DLF

group by

     L01, L03

order by

     L01, L03 



Date Last Modified : 08th December 2009