Learner Payment Record Import Specification in PICSWeb

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

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
ADDED 8th December 2021

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:
APTB_PMR1
APTB_PMR2
APTB_PMR3

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
ADDED 8TH DECEMBER 2021

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:
Expected
Approved
Ready for Invoice
Invoiced
Paid
Cancelled
Withhold
Reconciled
Ready to Pay
Raised for Payment

New records are defaulted to EXPECTED

PAYMENT ITEM CODE
ADDED 8TH DECEMBER 2021

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.