Applicant Import Specification in PICSWeb

Owned by Joe Robson (Unlicensed)
Last updated: Sept 17, 2024 by Kirsty Taylor
4 min read

Instructions on using this specification to create an import file are available in Uploading a New Import File.

A CSV/XLSX file import can be used to bulk import and update Applicant data from other data sources. The table below shows what types of import are available for applicants.

Supported Options

 

CSV file format accepted

XLSX file format accepted

User Defined Fields supported

Record Handling

 

Single record in file

User redirected to the New Applicant window

Multiple records in file

Records added direct to database

For the import to work, the file must be in the following format. If no data is found for the mandatory columns, no records will be imported.

When multiple rows of data are provided the importer looks for matching a matching Applicant record in PICS using the APIDENT field. Any matches found in PICS will be updated with data from the CSV.

To prevent duplicate Applicant records from being imported data must be supplied in a combination of the the following columns:
OTHER_SYS_ID - if a value is supplied and a Live applicant with a matching surname is found the record is not imported.

or
APP_ULN - if a value is supplied and a Live applicant with a matching surname is found the record is not imported.

or
APP_BIRTHDATE / APP_NI_NO - if either or both value is supplied and a Live applicant with a matching surname is found the record is not imported.

Further information is also available on the following:

Column Name

Required?

Format

Example

Notes

Column Name

Required?

Format

Example

Notes

APIDENT

 

5 alphanumeric

0005Z

Used for matching existing Applicant records in PICS

APP_SURNAME

Yes

string

Smith

Used for matching existing Applicant records in PICS
Please note, if you provide the correct APP ident, but the name is not identical, it will update the Applicant's names to match the import data.

APP_FIRSTNAMES

Yes

string

Roberta

Used for matching existing Applicant records in PICS
Please note, if you provide the correct APP ident, but the name is not identical, it will update the Applicant's names to match the import data.

APP_PREFFIRSTNAME expected mid October 2023

 

string

Ro

Preferred First Name

TAG_IDS

 

string

 

INTERNAL USE ONLY
pcTagList RecGUID values * separated

TAG_NAMES

 

string

 April 2022*Engineers*

Names of the tags to create * separated

APP_ADDR1

 

string

Flat A

First line of Home Address

APP_ADDR2

 

string

123 Acacia Avenue

Second line of Home Address

APP_ADDR3

 

string

Suburb

Third line of Home Address

APP_ADDR4

 

string

Townville

Fourth line of Home Address

APP_POSTCODE

 

string

TO1 1WN

Home Postcode

APP_PHONE

 

numeric

01603492620

Home Phone number

APP_MOBPHONE

 

numeric

07799000001

Mobile Phone number

APP_EMAIL

 

string

bob@here.com

 

APP_NI_NO

 

string

JB010101A

 

APP_BIRTHDATE

 

date

30/10/2000

 

APP_SEX

 

string

F

1: Male
2: Female
M: Male
F: Female

APP_ETHNICITY

 

numeric

34

ILR Ethnicity Codes

APP_TITLE

 

numeric

4

Code Lists

APP_LSCDIFFIC

 

string

2

ILR Learner’s Self Assessed Disability status
ILR LLDD Status Codes

APP_ALL_LLDD

 

start separated list of codes

12*13*96

ILR LLDD Codes

APP_PRIMARY_LLDD

 

numeric

12

ILR Primary LLDD
One of the code specified in ALL_LLDD

CONT_METH

 

string

PETS

Allowed Contact Methods between Provider and Learner.
Any combination of the following codes:

E: Email

P: Post

T: Telephone

S: SMS

PREF_CONTACT_METH

 

string

E

Preferred Contact Method between Provider and Learner.
Must be one of the codes in Allowed Contact Methods.

APP_EMPCODE

 

6 alphanumeric

EMP001

Organisation record internal code

APP_CANSTART

 

date

01/01/2022

Date of probably the start of programme

APP_CURSTATUS

 

2 alphanumeric

01

Status code from Applicant Status Workflow configuration

APP_CURSUBSTATUS

 

5 alphanumeric

10000

Sub-status code from Applicant Status Workflow configuration

APP_CURSTATUS_NOTES

 

string

 

Note to be attached to the Status change record created from APP_CURSTATUS or the Default Status for New Applicants

APP_OFF_TRAIN

 

PICS Officer code

FREDDY01

The Main Officer for the Applicant

See Officers in PICSWeb Imports

APP_MENTOR

 

PICS Officer code

MENTOR01

The Mentor Officer for the Applicant

See Officers in PICSWeb Imports

APP_RECRUITED_BY

 

PICS Officer code

RECRUI01

The Recruitment Officer for the Applicant

See Officers in PICSWeb Imports

APP_QUALPLAN

 

8 alphanumeric

QUALPL01

Valid Qualification Plan code

APP_PLANNED_HOURS

 

numeric

 

Planned Hours for ILR Programme
(Off the job Hours for Apprenticeships)

ILR Planned Hours Specification

APP_ULN

 

numeric

 

Learner Record Service - Unique Learner Number

APP_LRS_VERIFTYPE

 

numeric code

 

Learner Record Service - Identity Verification Type

 

0: None

1: Relationship with School

2: Passport

3: Driving licence

4: ID card (national identification)

5: National Insurance card
6: Certificate of Entitlement to Funding

7: Bank credit/debit card

LRS_PRIVCYNOTICESEEN

 

numeric code

 

Learner Record Service - Privacy Notice Seen

0: Not seen
1: Seen

APP_NOK_NAME

 

string

 

Next of Kin Name

APP_NOK_REL

 

string

 

Next of Kin Relationship

APP_NOK_PHONE

 

string

 

Next of Kin Phone 1

APP_NOK_PHONE2

 

string

 

Next of Kin Phone 2

APP_NOK_EMAIL

 

string

 

Next of Kin Email

APP_HHS

 

star separated list of codes

1*2*

ILR House Hold Situation (HHS)

ILR FAM HHS Codes

APP_GCSEENGGRADE

 

 

 

ILR English GCSE Grade

See ILR Specification

APP_GCSEMATHGRADE

 

 

 

ILR Maths GCSE Grade

See ILR Guidance

APP_ACT

 

2 alphanumeric

1L

Apprenticeship Contract Type

See Code Lists

APP_TRG_TNP

 

numeric

 

Apprenticeship Total Negotiated Price - Training

APP_TRG_EMP

 

numeric

 

Apprenticeship Employer Training contribution

APP_ASM_TNP

 

numeric

 

Apprenticeship Total Negotiated Price - Assessment

APP_ASM_EMP

 

numeric

 

Apprenticeship Employer Assessment contribution

APP_ADD_EMP

 

numeric

 

Apprenticeship Additional Apprenticeship Price

APP_DAS_COHORT

 

 

 

Apprenticeship Service Cohort

APP_DAS_STATUS

 

 

 

Apprenticeship Service Status

APP_DAS_RPL_IDENTIFIED

 

string

Y

Apprenticeship Service Recognised Prior Learning - used in the Apprenticeship Service Bulk Upload

Apprenticeship service bulk upload specification
Y - Yes
N - No

APP_DAS_TOTALTRAININGHOURS

 

number

 

Apprenticeship Service Total off-the-job hours for the apprenticeship standard - used in the Apprenticeship Service Bulk Upload

Apprenticeship service bulk upload specification

APP_DAS_DURATIONREDUCED

 

number

 

Apprenticeship Service Duration Reduced by Weeks - used in the Apprenticeship Service Bulk Upload

Apprenticeship service bulk upload specification

APP_DAS_DURATIONREDUCED

 

number

16

Apprenticeship Service Duration Reduced by Weeks - used in the Apprenticeship Service Bulk Upload

Apprenticeship service bulk upload specification

APP_DAS_PRICEREDUCED

 

number

1250

Apprenticeship Service Price Reduced by £s - used in the Apprenticeship Service Bulk Upload

Apprenticeship service bulk upload specification

APP_PROVIDENT

 

 

 

Provider Assigned Identifier

APP_ULIN

 

12 alphanumeric

 

Learner Reference Number

APP_PREFCONT

 

star separated list of codes

4*5*6*

ILR Preferred Method Of Contact (PMC)

See ILR Specification

APP_SILR_RUI

 

star separated list of codes

6*7*

ILR Restricted Use Indicator (RUI)

See ILR Specification

APP_DELIVERY_LOCATION

 

 

 

PICS Organisation Code for Delivery Location

APP_SCHOOL

 

 

 

PICS Organisation Code for School

APP_COLLEGE

 

 

 

PICS Organisation Code for College

APP_RECRUIT_AGENCY

 

 

 

PICS Organisation Code for Recruitment Agency

OTHER_SYS_ID

 

 

 

Other System ID

APP_SITE

 

 

 

PICS Site

APP_PRIORATTAIN

 

 

 

ILR Prior Attainment Level

See ILR Specification

APP_BKSB_USERNAME

 

 

 

Applicant's Username in BKSB

APP_EMP_STATUS

 

 

 

Employment Status

See ILR Specification

APP_EMP_SEI

 

 

 

Self Employment Indicator

See ILR Specification

APP_EMP_EII

 

 

 

Employment Intensity Indicator

See ILR Specification

APP_EMP_LOE

 

 

 

Length of Employment

See ILR Specification

APP_EMP_LOU

 

 

 

Length of Unemployment

See ILR Specification

APP_EMP_BSI

 

 

 

Benefit Status Indicator

See ILR Specification

APP_EMP_PEI

 

 

 

Previous Education Indicator

See ILR Specification

APP_EMP_OET1

 

 

 

Other Employment monitoring Type

See ILR Specification

APP_EHC

 

 

 

Education Health Care Plane

See ILR Specification

Functional Skills Initial Assessments

From the PICS release for the 2023/24 ILR Year (due mid August 2023) imports of Functional Skills Initial Assessments will require either a Level or a Result to be processed. If a date is not specified the date the Applicant record was created will be used so that importing the same data multiple times does not create multiple results records.

Column Name

Required?

Format

Example

Notes

Column Name

Required?

Format

Example

Notes

APP_BAS_NUM

 

1 alphanumeric

 

Maths Functional Skills Initial Assessment Level

See Code Lists

APP_BAS_LIT

 

1 alphanumeric

 

English Functional Skills Initial Assessment Level

See Code Lists

APP_BAS_ICT

 

1 alphanumeric

 

ICT Functional Skills Initial Assessment Level

See Code Lists

APP_BAS_ESOL

coming Aug 2023

 

1 alphanumeric

 

ESOLFunctional Skills Initial Assessment Level

See Code Lists

APP_BAS_NUMRES

 

numeric

 

Maths Functional Skills Initial Assessment Result

APP_BAS_LITRES

 

numeric

 

English Functional Skills Initial Assessment Result

APP_BAS_ICTRES

 

numeric

 

ICT Functional Skills Initial Assessment Result

APP_BAS_ESOLRES

coming Aug 2023

 

numeric

 

ESOLFunctional Skills Initial Assessment Result

APP_BAS_NUMDATE

 

date

 

Maths Functional Skills Initial Assessment Date

APP_BAS_LITDATE

 

date

 

English Functional Skills Initial Assessment Date

APP_BAS_ICTDATE

 

date

 

ICT Functional Skills Initial Assessment Date

APP_BAS_ESOLDATE

coming Aug 2023

 

date

 

ESOL Functional Skills Initial Assessment Date

Prior Qualifications

Multiple prior qualifications can be included, but the last number of each column name below must be increased by 1 for each new qualification.

For example, APP_PRIORQUAL_TITLECODE_1 becomes APP_PRIORQUAL_TITLECODE_2 and then APP_PRIORQUAL_TITLECODE_3.

When updating an applicant if a matching APP_PRIORQUAL_UID_? value is found only the APP_PRIORQUAL_NOTES_? and APP_PRIORQUAL_ACHDATE_? fields will be updated.

Column Name

Required?

Format

Example

Notes

Column Name

Required?

Format

Example

Notes

APP_PRIORQUAL_UID_1

 

 

 

Internal field used to match existing records when updating

APP_PRIORQUAL_TITLECODE_1

Title or Code

5 alphanumeric

0005Z

PICS Code for the Title of the Prior Qualification

APP_PRIORQUAL_NAME_1

Title or Code

string

Maths GCSE

Title for the Prior Qualification

APP_PRIORQUAL_LEVEL_CODE_1

 

5 alphanumeric

GCSE01

PICS Code for the Level

APP_PRIORQUAL_LEVEL_1

 

string

GCSE

Level

APP_PRIORQUAL_GRADE_CODE_1

 

5 alphanumeric

GCSE1

PICS Code for the Grade

APP_PRIORQUAL_GRADE_1

 

string

1

Grade

APP_PRIORQUAL_ACHDATE_1

 

date

 

Achievement Date

APP_PRIORQUAL_NOTES_1

 

string

 

Notes

Example Data File

Â