SLN payment Data Definition 2009/2010

A_SLN_PAYMENT_P1 to A_SLN_PAYMENT_P12, A_SLN_PAYMENT_EFY, L_SLN_PAYMENT_P1 to L_SLN_PAYMENT_P12, L_SLN_PAYMENT_EFY

PURPOSE

1. To show if any SLN or payment has been made.

2. These variables were defined in the MI views specification in 2008/09.

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

SOURCE DATA

Field Name	Label	Dataset
A_TOTAL_SLN_P1 to A_TOTAL_SLN_P12	A_TOTAL_SLN_P1 Total SLN in period 1 to A_TOTAL_SLN_P12 Total SLN in period 12.	ER_AIMS_DLF and LR_AIMS_DLF
A_TOTAL_PAYMENT_P1 to A_TOTAL_PAYMENT_P12	A_TOTAL_PAYMENT_P1 Total payment in period 1 to A_TOTAL_PAYMENT_P12 Total payment in period 12.	ER_AIMS_DLF and LR_AIMS_DLF
A_TOTAL_SLN_Y2D	A_TOTAL_SLN_Y2D Total SLN for year to date.	ER_AIMS_DLF and LR_AIMS_DLF
A_TOTAL_PAYMENT_Y2D	A_TOTAL_PAYMENT_Y2D Total payment for year to date.	ER_AIMS_DLF and LR_AIMS_DLF
A_TOTAL_SLN_EFY	A_TOTAL_SLN_EFY Total SLN expected for full year.	ER_AIMS_DLF and LR_AIMS_DLF
A_TOTAL_PAYMENT_EFY	A_TOTAL_PAYMENT_EFY Total payment expected for full year.	ER_AIMS_DLF and LR_AIMS_DLF
L_TOTAL_SLN_P1 to L_TOTAL_SLN_P12	L_TOTAL_SLN_P1 Total SLN in period 1 for all aims of the learner, to L_TOTAL_SLN_P12 Total SLN in period 12 for all aims of the learner.	ER_AIMS_DLF and LR_AIMS_DLF
L_TOTAL_PAYMENT_P1 to L_TOTAL_PAYMENT_P12	L_TOTAL_PAYMENT_P1 Total payment in period 1 for all aims of the learner, to L_TOTAL_PAYMENT_P12 Total payment in period 12 for all aims of the learner.	ER_AIMS_DLF and LR_AIMS_DLF
L_TOTAL_SLN_Y2D	L_TOTAL_SLN_Y2D Total SLN for year to date for all aims of the learner.	ER_AIMS_DLF and LR_AIMS_DLF
L_TOTAL_PAYMENT_Y2D	L_TOTAL_PAYMENT_Y2D Total payment for year to date for all aims of the learner.	ER_AIMS_DLF and LR_AIMS_DLF
L_TOTAL_SLN_EFY	L_TOTAL_SLN_EFY Total SLN expected for full year for all aims of the learner.	ER_AIMS_DLF and LR_AIMS_DLF
L_TOTAL_PAYMENT_EFY	L_TOTAL_PAYMENT_EFY Total payment expected for full year for all aims of the learner.	ER_AIMS_DLF and LR_AIMS_DLF

DERIVED VARIABLES AND DATASETS

4. The definition produces the following derived variable(s)

Field Name	Label	Dataset
A_SLN_PAYMENT_EFY	A_SLN_PAYMENT_EFY Any SLN or payments expected for full year.	ER_AIMS_DLF and LR_AIMS_DLF
A_SLN_PAYMENT_P1 to A_SLN_PAYMENT_P12	A_SLN_PAYMENT_P1 Any SLN or payments in period 1 to A_SLN_PAYMENT_P12 Any SLN or payments in period 12.	ER_AIMS_DLF and LR_AIMS_DLF
L_SLN_PAYMENT_EFY	L_SLN_PAYMENT_EFY Any SLN or payments expected for full year for all aims of the learner.	ER_Learner_DLF and LR_Learner_DLF
L_SLN_PAYMENT_P1 to L_SLN_PAYMENT_P12	L_SLN_PAYMENT_P1 Any SLN or payments in period 1 for all aims of the learner, to L_SLN_PAYMENT_P12 Any SLN or payments in period 12 for all aims of the learner.	ER_Learner_DLF and LR_Learner_DLF

DETAILED DEFINITION

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 combine the total SLN and total payment DVs.

7. The following illustrates how A_SLN_PAYMENTP1-12 and A_SLN_PAYMENT_EFY are derived:

A_SLN_PAYMENT_P1 = Where (A_TOTAL_SLN_P1>0 or A_TOTAL_PAYMENT_P1>0) then 1, Else 0.
A_SLN_PAYMENT_P2 = Where (A_TOTAL_SLN_P2>0 or A_TOTAL_PAYMENT_P2>0) then 1, Else 0.
A_SLN_PAYMENT_P3 = Where (A_TOTAL_SLN_P3>0 or A_TOTAL_PAYMENT_P3>0) then 1, Else 0.
A_SLN_PAYMENT_P4 = Where (A_TOTAL_SLN_P4>0 or A_TOTAL_PAYMENT_P4>0) then 1, Else 0.
A_SLN_PAYMENT_P5 = Where (A_TOTAL_SLN_P5>0 or A_TOTAL_PAYMENT_P5>0) then 1, Else 0.
A_SLN_PAYMENT_P6 = Where (A_TOTAL_SLN_P6>0 or A_TOTAL_PAYMENT_P6>0) then 1, Else 0.
A_SLN_PAYMENT_P7 = Where (A_TOTAL_SLN_P7>0 or A_TOTAL_PAYMENT_P7>0) then 1, Else 0.
A_SLN_PAYMENT_P8 = Where (A_TOTAL_SLN_P8>0 or A_TOTAL_PAYMENT_P8>0) then 1, Else 0.
A_SLN_PAYMENT_P9 = Where (A_TOTAL_SLN_P9>0 or A_TOTAL_PAYMENT_P9>0) then 1, Else 0.
A_SLN_PAYMENT_P10 = Where (A_TOTAL_SLN_P10>0 or A_TOTAL_PAYMENT_P10>0) then 1, Else 0.
A_SLN_PAYMENT_P11 = Where (A_TOTAL_SLN_P11>0 or A_TOTAL_PAYMENT_P11>0) then 1, Else 0.
A_SLN_PAYMENT_P12 = Where (A_TOTAL_SLN_P12>0 or A_TOTAL_PAYMENT_P12>0) then 1, Else 0.

A_SLN_PAYMENT_EFY = Where (A_TOTAL_SLN_EFY>0 or A_TOTAL_PAYMENT_EFY>0) then 1, Else 0.

8. The following SQL code illustrates how A_SLN_PAYMENTP1-12 and A_SLN_PAYMENT_EFY are derived:

select

     L01, L03, A05
     , case when (A_TOTAL_PAYMENT_P1>0 or A_TOTAL_SLN_P1>0) then 1 else 0 end as A_SLN_PAYMENT_P1
     , case when (A_TOTAL_PAYMENT_P2>0 or A_TOTAL_SLN_P2>0) then 1 else 0 end as A_SLN_PAYMENT_P2
     , case when (A_TOTAL_PAYMENT_P3>0 or A_TOTAL_SLN_P3>0) then 1 else 0 end as A_SLN_PAYMENT_P3
     , case when (A_TOTAL_PAYMENT_P4>0 or A_TOTAL_SLN_P4>0) then 1 else 0 end as A_SLN_PAYMENT_P4
     , case when (A_TOTAL_PAYMENT_P5>0 or A_TOTAL_SLN_P5>0) then 1 else 0 end as A_SLN_PAYMENT_P5
     , case when (A_TOTAL_PAYMENT_P6>0 or A_TOTAL_SLN_P6>0) then 1 else 0 end as A_SLN_PAYMENT_P6
     , case when (A_TOTAL_PAYMENT_P7>0 or A_TOTAL_SLN_P7>0) then 1 else 0 end as A_SLN_PAYMENT_P7
     , case when (A_TOTAL_PAYMENT_P8>0 or A_TOTAL_SLN_P8>0) then 1 else 0 end as A_SLN_PAYMENT_P8
     , case when (A_TOTAL_PAYMENT_P9>0 or A_TOTAL_SLN_P9>0) then 1 else 0 end as A_SLN_PAYMENT_P9
     , case when (A_TOTAL_PAYMENT_P10>0 or A_TOTAL_SLN_P10>0) then 1 else 0 end as A_SLN_PAYMENT_P10
     , case when (A_TOTAL_PAYMENT_P11>0 or A_TOTAL_SLN_P11>0) then 1 else 0 end as A_SLN_PAYMENT_P11
     , case when (A_TOTAL_PAYMENT_P12>0 or A_TOTAL_SLN_P12>0) then 1 else 0 end as A_SLN_PAYMENT_P12
     , case when (A_TOTAL_PAYMENT_EFY>0 or A_TOTAL_SLN_EFY>0) then 1 else 0 end as A_SLN_PAYMENT_EFY

from

ILR0910_E_AIMS_DLF

9. The following illustrates how L_SLN_PAYMENTP1-12 and L_SLN_PAYMENT_EFY are derived:

L_SLN_PAYMENT_P1 = Where (L_TOTAL_SLN_P1>0 or L_TOTAL_PAYMENT_P1>0) then 1, Else 0.
L_SLN_PAYMENT_P2 = Where (L_TOTAL_SLN_P2>0 or L_TOTAL_PAYMENT_P2>0) then 1, Else 0.
L_SLN_PAYMENT_P3 = Where (L_TOTAL_SLN_P3>0 or L_TOTAL_PAYMENT_P3>0) then 1, Else 0.
L_SLN_PAYMENT_P4 = Where (L_TOTAL_SLN_P4>0 or L_TOTAL_PAYMENT_P4>0) then 1, Else 0.
L_SLN_PAYMENT_P5 = Where (L_TOTAL_SLN_P5>0 or L_TOTAL_PAYMENT_P5>0) then 1, Else 0.
L_SLN_PAYMENT_P6 = Where (L_TOTAL_SLN_P6>0 or L_TOTAL_PAYMENT_P6>0) then 1, Else 0.
L_SLN_PAYMENT_P7 = Where (L_TOTAL_SLN_P7>0 or L_TOTAL_PAYMENT_P7>0) then 1, Else 0.
L_SLN_PAYMENT_P8 = Where (L_TOTAL_SLN_P8>0 or L_TOTAL_PAYMENT_P8>0) then 1, Else 0.
L_SLN_PAYMENT_P9 = Where (L_TOTAL_SLN_P9>0 or L_TOTAL_PAYMENT_P9>0) then 1, Else 0.
L_SLN_PAYMENT_P10 = Where (L_TOTAL_SLN_P10>0 or L_TOTAL_PAYMENT_P10>0) then 1, Else 0.
L_SLN_PAYMENT_P11 = Where (L_TOTAL_SLN_P11>0 or L_TOTAL_PAYMENT_P11>0) then 1, Else 0.
L_SLN_PAYMENT_P12 = Where (L_TOTAL_SLN_P12>0 or L_TOTAL_PAYMENT_P12>0) then 1, Else 0.

L_SLN_PAYMENT_EFY = Where (L_TOTAL_SLN_EFY>0 or L_TOTAL_PAYMENT_EFY>0) then 1, Else 0.

10. The following SQL code illustrates how L_SLN_PAYMENTP1-12 and L_SLN_PAYMENT_EFY are derived:

select L01 , L03 , case when (max(L_TOTAL_PAYMENT_P1)>0 or max(L_TOTAL_SLN_P1)>0) then 1 else 0 end as L_SLN_PAYMENT_P1 , case when (max(L_TOTAL_PAYMENT_P2)>0 or max(L_TOTAL_SLN_P2)>0) then 1 else 0 end as L_SLN_PAYMENT_P2 , case when (max(L_TOTAL_PAYMENT_P3)>0 or max(L_TOTAL_SLN_P3)>0) then 1 else 0 end as L_SLN_PAYMENT_P3 , case when (max(L_TOTAL_PAYMENT_P4)>0 or max(L_TOTAL_SLN_P4)>0) then 1 else 0 end as L_SLN_PAYMENT_P4 , case when (max(L_TOTAL_PAYMENT_P5)>0 or max(L_TOTAL_SLN_P5)>0) then 1 else 0 end as L_SLN_PAYMENT_P5 , case when (max(L_TOTAL_PAYMENT_P6)>0 or max(L_TOTAL_SLN_P6)>0) then 1 else 0 end as L_SLN_PAYMENT_P6 , case when (max(L_TOTAL_PAYMENT_P7)>0 or max(L_TOTAL_SLN_P7)>0) then 1 else 0 end as L_SLN_PAYMENT_P7 , case when (max(L_TOTAL_PAYMENT_P8)>0 or max(L_TOTAL_SLN_P8)>0) then 1 else 0 end as L_SLN_PAYMENT_P8 , case when (max(L_TOTAL_PAYMENT_P9)>0 or max(L_TOTAL_SLN_P9)>0) then 1 else 0 end as L_SLN_PAYMENT_P9 , case when (max(L_TOTAL_PAYMENT_P10)>0 or max(L_TOTAL_SLN_P10)>0) then 1 else 0 end as L_SLN_PAYMENT_P10 , case when (max(L_TOTAL_PAYMENT_P11)>0 or max(L_TOTAL_SLN_P11)>0) then 1 else 0 end as L_SLN_PAYMENT_P11 , case when (max(L_TOTAL_PAYMENT_P12)>0 or max(L_TOTAL_SLN_P12)>0) then 1 else 0 end as L_SLN_PAYMENT_P12 , case when (max(L_TOTAL_PAYMENT_EFY)>0 or max(L_TOTAL_SLN_EFY)>0) then 1 else 0 end as L_SLN_PAYMENT_EFY from ILR0910_E_LEARNER_DLF group by L01 , L03 order by L01 , L03

select

     L01 , L03
      , case when (max(L_TOTAL_PAYMENT_P1)>0 or max(L_TOTAL_SLN_P1)>0) then 1 else 0 end as L_SLN_PAYMENT_P1
      , case when (max(L_TOTAL_PAYMENT_P2)>0 or max(L_TOTAL_SLN_P2)>0) then 1 else 0 end as L_SLN_PAYMENT_P2
      , case when (max(L_TOTAL_PAYMENT_P3)>0 or max(L_TOTAL_SLN_P3)>0) then 1 else 0 end as L_SLN_PAYMENT_P3
      , case when (max(L_TOTAL_PAYMENT_P4)>0 or max(L_TOTAL_SLN_P4)>0) then 1 else 0 end as L_SLN_PAYMENT_P4
      , case when (max(L_TOTAL_PAYMENT_P5)>0 or max(L_TOTAL_SLN_P5)>0) then 1 else 0 end as L_SLN_PAYMENT_P5
      , case when (max(L_TOTAL_PAYMENT_P6)>0 or max(L_TOTAL_SLN_P6)>0) then 1 else 0 end as L_SLN_PAYMENT_P6
      , case when (max(L_TOTAL_PAYMENT_P7)>0 or max(L_TOTAL_SLN_P7)>0) then 1 else 0 end as L_SLN_PAYMENT_P7
      , case when (max(L_TOTAL_PAYMENT_P8)>0 or max(L_TOTAL_SLN_P8)>0) then 1 else 0 end as L_SLN_PAYMENT_P8
      , case when (max(L_TOTAL_PAYMENT_P9)>0 or max(L_TOTAL_SLN_P9)>0) then 1 else 0 end as L_SLN_PAYMENT_P9
      , case when (max(L_TOTAL_PAYMENT_P10)>0 or max(L_TOTAL_SLN_P10)>0) then 1 else 0 end as L_SLN_PAYMENT_P10
      , case when (max(L_TOTAL_PAYMENT_P11)>0 or max(L_TOTAL_SLN_P11)>0) then 1 else 0 end as L_SLN_PAYMENT_P11
      , case when (max(L_TOTAL_PAYMENT_P12)>0 or max(L_TOTAL_SLN_P12)>0) then 1 else 0 end as L_SLN_PAYMENT_P12
      , case when (max(L_TOTAL_PAYMENT_EFY)>0 or max(L_TOTAL_SLN_EFY)>0) then 1 else 0 end as L_SLN_PAYMENT_EFY

from

ILR0910_E_LEARNER_DLF

group by

L01 , L03

order by

L01 , L03

Date Last Modified : 02nd December 2009