Learner Payment Record Import Specification in PICSWeb
- Tim Davies (Pellcomp) (Deactivated)
- Joe Robson (Unlicensed)
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 Learner Payment Record data from other data sources. The table below shows what types of import are available for Payment Records:
Supported Options |
|
|---|---|
CSV file format accepted | |
XLSX file format accepted | |
User Defined Fields supported | |
Record Handling |
|
Single record in file | Record added direct to database |
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. Any records that cannot be imported will be reported back to the user.
When adding new Payment records they will be added to the learner episode specified using the IDENT value or to the episode IDENT derived from the LEARNER REFERENCE NUMBER and EXPECTED PAYMENT DATE or ACTUAL PAYMENT DATE.
Records can only be updated if an INTERNAL RECORD ID is supplied. The easiest way to prepare data for updating is to use the Learner Payment Report in PICSWeb to output the records that you wish to update. The column names in the report and import are the same to make this process easier.
Column Name | Used | Required? | Format | Example | Notes |
|---|---|---|---|---|---|
INTERNAL RECORD ID | Update | For updates | 32 character unique id |
| If an IDENT is specified it will be cross checked with the record found |
IDENT | New | For new records | 5 character internal identifier | 00001 | PICS Learner Episode Identifier |
LEARNER REFERENCE NUMBER | New | For new records | As per ILR | PICS12345678 | Learner Reference Number as on the ILR. This is used in combination with the EPISODE START or EXPECTED PAYMENT DATE or ACTUAL PAYMENT DATE to derive a PICS Episode Ident. |
EPISODE START | New | Optional for new records | dd/mm/yyyy | 01/12/2020 | Used in conjunction with LEARNER REFERENCE NUMBER to identify a PICS Episode |
SYSTEM TYPE | New | For new records only | String | Blank or one of: | Type of Payment - can be left blank to be a “Generic Payment” |
EXPECTED PAYMENT DATE | New/Update | Required unless an ACTUAL PAYMENT DATE is provided | dd/mm/yyyy | 30/10/2020 | Date payment is expected |
EXPECTED PAYMENT AMOUNT | New/Update |
| Money | 12.00 | Expected amount |
ACTUAL PAYMENT DATE | New/Update | Required unless an EXPECTED PAYMENT DATE is provided | dd/mm/yyyy | 14/11/2020 | Date payment is made |
ACTUAL PAYMENT AMOUNT | New/Update |
| Money | 11.50 | Actual amount |
CREDIT/DEBIT | New | For new records only | Single Character | C or D | Automatically derived from SYSTEM TYPE for PMRs |
INVOICE NUMBER | New/Update |
| String |
|
|
INVOICE DATE | New/Update |
| dd/mm/yyyy |
|
|
ORGANISATION ID | New |
| PICS Organisation Identifier | EMP123 | If not specified and a SYS TYPE of APTB_PMR is provided then it will be taken from the latest Learner ILR Employment record |
ORGANISATION FROM EMPLOYER | New |
| Y or blank |
| If this is Y and no ORGANISATION ID value is specified then the Organisation ID will be taken from the latest Learner ILR Employment record |
PAYMENT STATUS | New/Update |
| String | One of: | New records are defaulted to EXPECTED |
PAYMENT ITEM CODE | New |
| String |
| Code value from the “Payment Items” list in Configuration > Lists and Types |
NOTES | New/Update |
| String | Any other notes |
Example Data File
The file below is an example of a valid Learner Payment import CSV.
