Applicant Import Specification in PICSWeb
- Joe Robson (Unlicensed)
- Kirsty Taylor (Unlicensed)
- Tim Davies (Pellcomp) (Deactivated)
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 |
|---|---|---|---|---|
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 |
APP_FIRSTNAMES | Yes | string | Roberta | Used for matching existing Applicant records in PICS |
APP_PREFFIRSTNAME expected mid October 2023 |
| string | Ro | Preferred First Name |
TAG_IDS |
| string |
| INTERNAL USE ONLY |
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 |
| |
APP_NI_NO |
| string | JB010101A |
|
APP_BIRTHDATE |
| date | 30/10/2000 |
|
APP_SEX |
| string | F | 1: Male |
APP_ETHNICITY |
| numeric | 34 | |
APP_TITLE |
| numeric | 4 | |
APP_LSCDIFFIC |
| string | 2 | ILR Learner’s Self Assessed Disability status |
APP_ALL_LLDD |
| start separated list of codes | 12*13*96 | |
APP_PRIMARY_LLDD |
| numeric | 12 | ILR Primary LLDD |
CONT_METH |
| string | PETS | Allowed Contact Methods between Provider and Learner. E: Email P: Post T: Telephone S: SMS |
PREF_CONTACT_METH |
| string | E | Preferred Contact Method between Provider and Learner. |
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 |
APP_MENTOR |
| PICS Officer code | MENTOR01 | The Mentor Officer for the Applicant |
APP_RECRUITED_BY |
| PICS Officer code | RECRUI01 | The Recruitment Officer for the Applicant |
APP_QUALPLAN |
| 8 alphanumeric | QUALPL01 | Valid Qualification Plan code |
APP_PLANNED_HOURS |
| numeric |
| Planned Hours for ILR Programme |
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 7: Bank credit/debit card |
LRS_PRIVCYNOTICESEEN |
| numeric code |
| Learner Record Service - Privacy Notice 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) |
APP_GCSEENGGRADE |
|
|
| ILR English GCSE Grade |
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 |
APP_DAS_TOTALTRAININGHOURS |
| number |
| Apprenticeship Service Total off-the-job hours for the apprenticeship standard - used in the Apprenticeship Service Bulk Upload |
APP_DAS_DURATIONREDUCED |
| number |
| Apprenticeship Service Duration Reduced by Weeks - used in the Apprenticeship Service Bulk Upload |
APP_DAS_DURATIONREDUCED |
| number | 16 | Apprenticeship Service Duration Reduced by Weeks - used in the Apprenticeship Service Bulk Upload |
APP_DAS_PRICEREDUCED |
| number | 1250 | Apprenticeship Service Price Reduced by £s - used in the Apprenticeship Service Bulk Upload |
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) |
APP_SILR_RUI |
| star separated list of codes | 6*7* | ILR Restricted Use Indicator (RUI) |
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 |
APP_BKSB_USERNAME |
|
|
| Applicant's Username in BKSB |
APP_EMP_STATUS |
|
|
| Employment Status |
APP_EMP_SEI |
|
|
| Self Employment Indicator |
APP_EMP_EII |
|
|
| Employment Intensity Indicator |
APP_EMP_LOE |
|
|
| Length of Employment |
APP_EMP_LOU |
|
|
| Length of Unemployment |
APP_EMP_BSI |
|
|
| Benefit Status Indicator |
APP_EMP_PEI |
|
|
| Previous Education Indicator |
APP_EMP_OET1 |
|
|
| Other Employment monitoring Type |
APP_EHC |
|
|
| Education Health Care Plane |
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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
