TPA Details

TPA CSV Output Details

 

  • Created by running 40 and selecting a Template that has its output defined as CSV.

a) Reference – payemptph.reference if record exists, else pnlempmst.pension_id

b) Sex – pnlempmst.sex_id

c) NI Number – pnlempmst.ni_number

d) Surname – pnlempmst.surname

e) Initials – pnlempmst.initials

f) Previous Name – pnlempmst.prevname

g) Date of Birth – pnlempmst.date_birth

h) LEA Number – admcntrl.param_value (param_id = TPAAREA)

i) Estab – admcntrl.param_value (param_id = TPAREF)

j) Salary Scale – payemptpd.scale if record exists, else FO and O

k) F/P Time – Note B

l) Start Date – Note A

m) End Date – Note A

n) Withdrawal – payemptpd.withdrawn if record exists, else blank

o) Days Exclude – payemptpd.days_exc if record exists, else blank

p) Annual Salary – payemptpd.salary if record exists, else pnlempsh.sal_value

q) Inc Pt – 0

r) Part_Time – 0

s) SS – payemptpd.sal_safe if record exists, else blank

t) LA – payemptpd.london_all if record exists, else blank

u) SP – payemptpd.social_all if record exists, else zero

v) SC – payemptpd.special_all if record exists, else zero

w) Other Allowances – payemptpd.other_all if record exists, else zero

x) Record Type – 1

y) Serial Number – 1

z) Supplment – payemptpd.supplement if record exists, else zero

 

 

  • If a payemptph record exists, then a record with these differences is created:

b) Sex – Set to M not 1

i) Estab – Blank

j) Salary Scale – payemptph.add_cont

k) F/P Time – 0

l) Start Date – 0

m) End Date – 11 ??

o) Days Exclude – 2 ?

p) Annual Salary – admcntrl.param_value (param_id = TPAREF)

q) Inc Pt – Blank

r) Part Time – 2

s) to z) – Blank

 

 

TPA TXT Output Details

Created by running report 40 and selecting a Template with out defined as TXT.

Only 1 documented:

 

Header Record

Field Length Details
Record Type 4 UHL1
LA Number 3 admcntrl.param_value where param_id = ‘TPAAREA’
Filler (school) 4 admcntrl.param_value where param_id = ‘TPAREF’
Originators’ Title 16 admcntrl.param_value where param_id = ‘TPAORIG’
Created date 6 DDMMYY
Filler 63 Spaces

 

 

Trailer Record

Field Length Details
Record Type 4 UTL1
Total no of records 6 Not including UHL1 and UTL1
Total no of records of Type 1 6  
Total no of records of Type 2 6 zeroes
Total no of records of Type 3 6 zeroes
Total no of records of Type 4 6 zeroes
Filler 62 Spaces

 

 

 

Type 1 Record (Note: format has the NI Number missing)

Field Length Details
DFE Reference Number 7 payemptph.reference if record exists else pnlempmst.pension_id
Sex Digit 1 pnlempmst.sex_id
NI Number 9 pnlempmst.ni_number  (this is missed off the table, and in fact if the field exists the employee is omitted from the file
Surname 9 pnlempmst.surname
Initials 2 pnlempmst.initials
Previous Surname 9 pnlempmst.prevname
Date of Birth 6 (ddmmyy) pnlempmst.date_birth
LA  Number 3 as Header
School 4 as Header
Salary Scale 3 payemptpd.scale if record exists else god knows
Full/Part Time Indicator 1 Note B
Start date 6 (ddmmyy) Not e A
End date 6 (ddmmyy) Note A
Withdrawal 1 payemptpd.withdrawn if record exists else blank
Days Excluded 3 payemptpd.days_exc if record exists else zeroes
Annual Full Time Salary Rate 6 payemptpd.salary if record exists else pnlempsh.sal_value
Incremental Point 2 Zeroes
Part Time Salary Paid 6 payempptd.pt_salary if record exists else pnlempsh.sal_value if Full/Part Time indicator is P
Safeguarded salary 1 payemptpd.sal_safe if record exists else blank
London Allowance 1 payemptd.london_al if record exists else blank
Social Allowance 1 payemptd.social_all if record exists else 0
Special Class 1 payemptpd.special_all if record exists else 0
Other allowances 5 payemptpd.other_all if record exists else zeroes
Record Type Indicator 1 1
Record Serial Number 1 Start at 1 for each employee
Supplement 5 payemptpd.supplement if record exists else zeroes

 

 

 

TRA Report

 

a) Reference – payemptph.reference if record exists, else pnlempmst.pension_id

b) NI Number – pnlempmst.ni_number

c) Surname – pnlempmst.surname

d) Initials – pnlempmst.initials

e) Forename – pnlempmst.forename

f) Previous Name – pnlempmst.prename

g) Date of Birth – pnlempmst.date_birth

h) Basic Contributions – Note C

i) Additional Contributions – Note C

j) Contributory Salary – Note C

k) Start Date – Note A

l) End Date – Note A

m) F/P – Note B

n) Withdrawn – payemptpd.withdrawn if record exists, else blank

o) Full Time – payemptpd.salary if record exists, else pnlempsh_sal_value

p) Part Time – payemptpd.pt_salary if record exists, else pnlempsh.sal_value

 

 

 

TPA Grid

 

a) Employee Number – pnlempmst.emp_id

b) DFES_REF – payemptph.reference if record exists, else pnlempmst.pension_id

c) Sex_id – pnlempmst.sex_id

d) Ni_no – pnlempmst.ni_number

e) Surname – pnlempmst.surname

f) Forename – pnlempmst.forename

g) Knownas – pnlempmst.knownas

h) Initials – pnlempmst.initials

i) Title_id – pnlempmst.title_id

j) Prev_surname – pnlempmst.prevname

k) Date of Birth – pnlempmst.date_birth

l) Dept_id – pnlempmst.dept_id

m) Dept_desc – pnldept.description

n) Loc_id – pnlempmst.loc_id

o) Loc_desc – pnlloc_description

p) Date_start – pnlempmst.date_start

q) Date_left – pnlempmst.date_left

r) Status_id – pnlempmst.status_id

s) Age – pnlempmst.age

t) LEA-NO – admcntrl.param_value (param_id = TPAAREA)

u) Emp_No_type – admcntrl.param_value (param_id = TPAREF)

v) Sal_scale – payemptpd.scale if record exists, else FO and 000

w) Emp_type – Note B

x) Start_date – Note A

y) End_date – Note A

z) – Withdrawn – payemptpd.withdrawn if record exists, else blank

aa) Days_exc – payemptpd_exc if record exists, else zero

bb) Salary – payemptpd.salary if record exists, else pnlempsh.sal_value

cc) Scale – Pre-set to zero as not required

dd) Pt_Salary – payemptpd.salary if record exists, else pnlempsh_sal_value, if emp_type = P, else zero

ee) Sal_safe – payemptpd.sal_safe if record exists, else blank

ff) London_all – payemptpd.london_all if record exists, else blank

gg) Social_all – payemptpd.social_all if record exists, else zero

hh) Special_all – payemptpd.speical_all if records exists, else zero

ii) Other_all – payemptpd.other_all if record exists, else zero

jj) Type_id – 1

kk) Rec_serial_no – 1

ll) Supplement – payemptpd.supplement if record exists, else zero

mm) Basic_conts – Note C

nn) Add_conts – Note C

oo) Cont_sal – Note C

 

 

 

TPA Summary Screen

 

a) Year – payemptph.year_id

b) Reference – payemptph.reference

c) Contributory Salary – payemptph.cont_sal

d) Basic Contributions – payemptph.basic_conts

e) Additional Contributions – payemptph.add_cont

 

 

 

TPA Detail Screen

 

a) Year – payemptpd.year_id

b) Scale – payemptpd.scale

c) Full/Part Time – payemptpd.emp_type

d) Start Date – payemptpd.start_date

e) End Date – payemptpd.end_date

f) Annual Salary – payemptpd.salary

g) Part Time Salary Paid – payemptpd.pt_salary

h) Days Excluded – payemptpd.days_exc

i) Safeguarded Salary – payemptpd.sal_safe

j) London Allowance – payemptpd.london_all

k) Social Priority Allowance – payemptpd.social_all

l) Special Class Allowance – payemptpd.special_all

m) Other Allowances – payemptpd.other_all

n) Withdrawn – payemptpd.withdrawn

o) Supplement – payemptpd.supplement

 

 

 

Notes

 

  1. If a payemptpd record exists for the year, then the start and end date are taken from that record. Otherwise, one line is output for each pnlempsh record that was active at any time within those dates being processed by the report.
    The start date will be either the start date of the report or the start date of the pnlempsh record – whichever is later.
    The end date will be either the end date of the report, or the end date of the pnlempsh record – whichever is earlier.
  2. If a payemptpd record exists for the year, then the flag is taken from the field payemptpd.emp_type.
    Otherwise, unsure on what basis this is being set up.
  3. If a payemptph record exists, then Basic Contributionspayemptph.basic_conts. Otherwise, it is the sum of payaudpen.pen_ee for the reporting periods.
    Additional Contributionspayemptph.add_cont, otherwise it is the sum of payaudpen.pen_vol.
    Contributory Salary payemptph.cont_sal, otherwise it is the sum of payaudpen.pen_gross.
    If payemptph exists for the year then the payaudpen details are ignored.