ER Balancing Payment Data Definition 2009/2010

A_BAL_PAYMENT_P1 to A_BAL_PAYMENT_P12, A_BAL_PAYMENT_Y2D, A_BAL_PAYMENT_EFY, A_BAL_SLN_P1 to A_BAL_SLN_P12, A_BAL_SLN_Y2D, A_BAL_SLN_EFY, L_BAL_PAYMENT_P1 to L_BAL_PAYMENT_P12, L_BAL_PAYMENT_Y2D, L_BAL_PAYMENT_EFY, L_BAL_SLN_P1 to L_BAL_SLN_P12, L_BAL_SLN_Y2D, L_BAL_SLN_EFY



PURPOSE

1. To enable balance 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.

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_BAL_CASH Employer Balancing Payment ER AIM_PERIOD
BAL_SLN_INST Balance SLN Instalment ER AIM_PERIOD
PERIOD PERIOD ER AIM_PERIOD


DERIVED VARIABLES AND DATASETS

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

Field Name Label Dataset
A_BAL_PAYMENT_P1 to A_BAL_PAYMENT_P12

A_BAL_PAYMENT_P1 Balance Payment in period 1 to A_BAL_PAYMENT_P12 Balance Payment in period 12.

 ER_AIMS_DLF
A_BAL_PAYMENT_Y2D A_BAL_PAYMENT_Y2D Balance Payment for year to date. ER_AIMS_DLF
A_BAL_PAYMENT_EFY A_BAL_PAYMENT_EFY Balance Payment Expected for Full Year. ER_AIMS_DLF

A_BAL_SLN_P1 to A_ BAL _SLN_P12

A_BAL_SLN_P1 Balance SLN in period 1 to A_BAL_SLN_P12 Balance SLN in period 12

 ER_AIMS_DLF
A_ BAL _SLN_Y2D A_BAL_SLN_Y2D Balance SLN for year to date. ER_AIMS_DLF
A_ BAL _SLN_EFY A_BAL_SLN_EFY Balance SLN Expected for Full Year. ER_AIMS_DLF
L_ BAL _PAYMENT_P1 to L_ BAL _PAYMENT_P12

L_BAL_PAYMENT_P1 Sum of Balance Payments in period 1 for all aims of the learner to L_BAL_PAYMENT_P12 Sum of Balance Payments in period 12 for all aims of the learner.

 ER_Learner_DLF
L_BAL_PAYMENT_Y2D L_BAL_PAYMENT_Y2D Sum of Balance Payment for year to date for all aims of the learner. ER_Learner_DLF
L_BAL_PAYMENT_EFY L_BAL_PAYMENT_EFY Sum of Balance Payment Expected for Full Year for all aims of the learner. ER_Learner_DLF

L_BAL_SLN_P1 to L_BAL_SLN_P12

L_BAL_SLN_P1 Sum of Balance SLN in period 1 for all aims of the learner to L_BAL_SLN_P12 Sum of Balance SLN in period 12 for all aims of the learner.

 ER_Learner_DLF
L_BAL_SLN_Y2D L_BAL_SLN_Y2D Sum of Balance SLN for year to date for all aims of the learner. ER_Learner_DLF
L_BAL_SLN_EFY L_BAL_SLN_EFY Sum of Balance SLN Expected for Full Year for all aims of the learner. ER_Learner_DLF

DETAILED DEFINITION

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 ER_BAL_CASH and BAL_SLN_INST values from the funding calculation’s Employer Responsive AIM_PERIOD data structure available to users. Learner level versions are also defined.

6. 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


7. The following illustrates how periodic Balance Payments are calculated at Aim Level:

A_BAL_PAYMENT_P1 = ER_BAL_CASH where PERIOD = 1.

A_BAL_PAYMENT_P2 = ER_BAL_CASH where PERIOD = 2.

A_BAL_PAYMENT_P3 = ER_BAL_CASH where PERIOD = 3.

A_BAL_PAYMENT_P4 = ER_BAL_CASH where PERIOD = 4.

A_BAL_PAYMENT_P5 = ER_BAL_CASH where PERIOD = 5.

A_BAL_PAYMENT_P6 = ER_BAL_CASH where PERIOD = 6.

A_BAL_PAYMENT_P7 = ER_BAL_CASH where PERIOD = 7.

A_BAL_PAYMENT_P8 = ER_BAL_CASH where PERIOD = 8.

A_BAL_PAYMENT_P9 = ER_BAL_CASH where PERIOD = 9.

A_BAL_PAYMENT_P10 = ER_BAL_CASH where PERIOD = 10.

A_BAL_PAYMENT_P11 = ER_BAL_CASH where PERIOD = 11.

A_BAL_PAYMENT_P12 = ER_BAL_CASH where PERIOD = 12.

A_BAL_PAYMENT_Y2D = sum of ER_BAL_CASH for PERIOD from 1 to current period.

A_BAL_PAYMENT_EFY = sum of ER_BAL_CASH for all PERIODs.


8. The following SQL code illustrates how periodic Balance Payments are calculated at Aim Level:

select

      L01, L03, A05
      , case when B.PERIOD = 1 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P1
      , case when B.PERIOD = 2 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P2
      , case when B.PERIOD = 3 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P3
      , case when B.PERIOD = 4 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P4
      , case when B.PERIOD = 5 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P5
      , case when B.PERIOD = 6 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P6
      , case when B.PERIOD = 7 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P7
      , case when B.PERIOD = 8 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P8
      , case when B.PERIOD = 9 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P9
      , case when B.PERIOD = 10 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P10
      , case when B.PERIOD = 11 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P11
      , case when B.PERIOD = 12 then ER_BAL_CASH else 0 end as A_BAL_PAYMENT_P12
      , case when B.PERIOD between 1 and A.PERIOD then sum(ER_BAL_CASH) else 0 end as A_BAL_PAYMENT_Y2D
      , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(ER_BAL_CASH) else 0 end as A_BAL_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



9. The following illustrates how periodic Balance Payments are calculated at Learner Level:

L_BAL_PAYMENT_P1 = sum of A_BAL_PAYMENT_P1 for all aims of the learner.

L_BAL_PAYMENT_P2 = sum of A_BAL_PAYMENT_P2 for all aims of the learner.

L_BAL_PAYMENT_P3 = sum of A_BAL_PAYMENT_P3 for all aims of the learner.

L_BAL_PAYMENT_P4 = sum of A_BAL_PAYMENT_P4 for all aims of the learner.

L_BAL_PAYMENT_P5 = sum of A_BAL_PAYMENT_P5 for all aims of the learner.

L_BAL_PAYMENT_P6 = sum of A_BAL_PAYMENT_P6 for all aims of the learner.

L_BAL_PAYMENT_P7 = sum of A_BAL_PAYMENT_P7 for all aims of the learner.

L_BAL_PAYMENT_P8 = sum of A_BAL_PAYMENT_P8 for all aims of the learner.

L_BAL_PAYMENT_P9 = sum of A_BAL_PAYMENT_P9 for all aims of the learner.

L_BAL_PAYMENT_P10 = sum of A_BAL_PAYMENT_P10 for all aims of the learner.

L_BAL_PAYMENT_P11 = sum of A_BAL_PAYMENT_P11 for all aims of the learner.

L_BAL_PAYMENT_P12 = sum of A_BAL_PAYMENT_P12 for all aims of the learner.

L_BAL_PAYMENT_Y2D = sum of A_BAL_PAYMENT_Y2D for all aims of the learner.

L_BAL_PAYMENT_EFY = sum of A_BAL_PAYMENT_EFY for all aims of the learner.


10. The following SQL code illustrates how periodic Balance Payments are calculated at Learner Level:

select

      L01, L03, count(a05) as aims
      , sum(A_BAL_PAYMENT_P1) as L_BAL_PAYMENT_P1
      , sum(A_BAL_PAYMENT_P2) as L_BAL_PAYMENT_P2
      , sum(A_BAL_PAYMENT_P3) as L_BAL_PAYMENT_P3
      , sum(A_BAL_PAYMENT_P4) as L_BAL_PAYMENT_P4
      , sum(A_BAL_PAYMENT_P5) as L_BAL_PAYMENT_P5
      , sum(A_BAL_PAYMENT_P6) as L_BAL_PAYMENT_P6
      , sum(A_BAL_PAYMENT_P7) as L_BAL_PAYMENT_P7
      , sum(A_BAL_PAYMENT_P8) as L_BAL_PAYMENT_P8
      , sum(A_BAL_PAYMENT_P9) as L_BAL_PAYMENT_P9
      , sum(A_BAL_PAYMENT_P10) as L_BAL_PAYMENT_P10
      , sum(A_BAL_PAYMENT_P11) as L_BAL_PAYMENT_P11
      , sum(A_BAL_PAYMENT_P12) as L_BAL_PAYMENT_P12
      , sum(A_BAL_PAYMENT_Y2D) as L_BAL_PAYMENT_Y2D
      , sum(A_BAL_PAYMENT_EFY) as L_BAL_PAYMENT_EFY

from

     ILR0910_E_AIMS_DLF

group by

      L01, L03

order by

      L01, L03


11. The following illustrates how periodic Balance SLNs are calculated at Aim Level: 

A_BAL_SLN_P1 = BAL_SLN_INST where PERIOD = 1.

A_BAL_SLN_P2 = BAL_SLN_INST where PERIOD = 2.

A_BAL_SLN_P3 = BAL_SLN_INST where PERIOD = 3.

A_BAL_SLN_P4 = BAL_SLN_INST where PERIOD = 4.

A_BAL_SLN_P5 = BAL_SLN_INST where PERIOD = 5.

A_BAL_SLN_P6 = BAL_SLN_INST where PERIOD = 6.

A_BAL_SLN_P7 = BAL_SLN_INST where PERIOD = 7.

A_BAL_SLN_P8 = BAL_SLN_INST where PERIOD = 8.

A_BAL_SLN_P9 = BAL_SLN_INST where PERIOD = 9.

A_BAL_SLN_P10 = BAL_SLN_INST where PERIOD = 10.

A_BAL_SLN_P11 = BAL_SLN_INST where PERIOD = 11.

A_BAL_SLN_P12 = BAL_SLN_INST where PERIOD = 12.

A_BAL_SLN_Y2D = sum of BAL_SLN_INST for PERIOD from 1 to current period. A_BAL_SLN_EFY = sum of BAL_SLN_INST for all PERIODs.


12. The following SQL code illustrates how periodic Balance SLNs are calculated at Aim Level: 

select

      L01, L03, A05
      , case when B.PERIOD = 1 then BAL_SLN_INST else 0 end as A_BAL_SLN_P1
      , case when B.PERIOD = 2 then BAL_SLN_INST else 0 end as A_BAL_SLN_P2
      , case when B.PERIOD = 3 then BAL_SLN_INST else 0 end as A_BAL_SLN_P3
      , case when B.PERIOD = 4 then BAL_SLN_INST else 0 end as A_BAL_SLN_P4
      , case when B.PERIOD = 5 then BAL_SLN_INST else 0 end as A_BAL_SLN_P5
      , case when B.PERIOD = 6 then BAL_SLN_INST else 0 end as A_BAL_SLN_P6
      , case when B.PERIOD = 7 then BAL_SLN_INST else 0 end as A_BAL_SLN_P7
      , case when B.PERIOD = 8 then BAL_SLN_INST else 0 end as A_BAL_SLN_P8
      , case when B.PERIOD = 9 then BAL_SLN_INST else 0 end as A_BAL_SLN_P9
      , case when B.PERIOD = 10 then BAL_SLN_INST else 0 end as A_BAL_SLN_P10
      , case when B.PERIOD = 11 then BAL_SLN_INST else 0 end as A_BAL_SLN_P11
      , case when B.PERIOD = 12 then BAL_SLN_INST else 0 end as A_BAL_SLN_P12
      , case when B.PERIOD between 1 and A.PERIOD then sum(BAL_SLN_INST) else 0 end as A_BAL_SLN_Y2D
      , case when B.PERIOD in (1,2,3,4,5,6,7,8,9,10,11,12) then sum(BAL_SLN_INST) else 0 end as A_BAL_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

 

13. The following illustrates how periodic Balance SLNs are calculated at Learner Level:

L_BAL_SLN_P1 = sum of A_BAL_SLN_P1 for all aims of the learner.

L_BAL_SLN_P2 = sum of A_BAL_SLN_P2 for all aims of the learner.

L_BAL_SLN_P3 = sum of A_BAL_SLN_P3 for all aims of the learner.

L_BAL_SLN_P4 = sum of A_BAL_SLN_P4 for all aims of the learner.

L_BAL_SLN_P5 = sum of A_BAL_SLN_P5 for all aims of the learner.

L_BAL_SLN_P6 = sum of A_BAL_SLN_P6 for all aims of the learner.

L_BAL_SLN_P7 = sum of A_BAL_SLN_P7 for all aims of the learner.

L_BAL_SLN_P8 = sum of A_BAL_SLN_P8 for all aims of the learner.

L_BAL_SLN_P9 = sum of A_BAL_SLN_P9 for all aims of the learner.

L_BAL_SLN_P10 = sum of A_BAL_SLN_P10 for all aims of the learner.

L_BAL_SLN_P11 = sum of A_BAL_SLN_P11 for all aims of the learner.

L_BAL_SLN_P12 = sum of A_BAL_SLN_P12 for all aims of the learner.

L_BAL_SLN_Y2D = sum of A_BAL_SLN_Y2D for all aims of the learner.

L_BAL_SLN_EFY = sum of A_BAL_SLN_EFY for all aims of the learner.


14. The following SQL code illustrates how periodic Balance SLNs are calculated at Learner Level:

select

      L01, L03, count(a05) as aims
      , sum(A_BAL_SLN_P1) as L_BAL_SLN_P1
      , sum(A_BAL_SLN_P2) as L_BAL_SLN_P2
      , sum(A_BAL_SLN_P3) as L_BAL_SLN_P3
      , sum(A_BAL_SLN_P4) as L_BAL_SLN_P4
      , sum(A_BAL_SLN_P5) as L_BAL_SLN_P5
      , sum(A_BAL_SLN_P6) as L_BAL_SLN_P6
      , sum(A_BAL_SLN_P7) as L_BAL_SLN_P7
      , sum(A_BAL_SLN_P8) as L_BAL_SLN_P8
      , sum(A_BAL_SLN_P9) as L_BAL_SLN_P9
      , sum(A_BAL_SLN_P10) as L_BAL_SLN_P10
      , sum(A_BAL_SLN_P11) as L_BAL_SLN_P11
      , sum(A_BAL_SLN_P12) as L_BAL_SLN_P12
      , sum(A_BAL_SLN_Y2D) as L_BAL_SLN_Y2D
      , sum(A_BAL_SLN_EFY) as L_BAL_SLN_EFY

from

     ILR0910_E_AIMS_DLF

group by

      L01, L03

order by

      L01, L03



Date Last Modified : 09th December 2009