A batch job is scheduled to run every week to extract member data from Vision for the purpose of updating the UK Member Lookup utility.
This extract contains the following member data:
- Scheme Code
- Member Reference Number
- Company Reference Number
- Surname
- Date of Birth
- Membership Group
- Membership Status
- Postcode
Note:
This is the scheduled batch job JU2BG_MLU\BJU2BGO0. (BATCHJOB128 MEMBER LOOK UP).
The file is a plain text comma separated value (CSV) file in the format as per the table below.
The file contains the header information as per the example below and is created with a .TXT extension, e.g. vision-to-ahc.txt.
With the exception of the first two fields, the date of birth and any empty fields, each value will be contained within double quotes.
For optional fields for which there is no value included in the file, the double quotes will not be included.
|
Field |
Description |
Format |
M/O |
Vision Value |
Note |
|
f.01 |
Link Number |
N |
O |
Blank |
No quotes |
|
f.02 |
Member Number |
N |
M |
Membership Reference Number |
In double quotes |
|
f.03 |
Staff Number |
AN |
M |
Company Reference Number |
In double quotes |
|
f.04 |
Status |
AN |
M |
Membership Status |
In double quotes |
|
f.05 |
Scheme |
AN |
M |
Scheme Code |
In double quotes |
|
f.06 |
Category |
AN |
O |
Contribution Membership Group |
In double quotes |
|
f.07 |
Surname |
AN |
M |
Surname |
Uppercase. In double quotes |
|
f.08 |
DOB |
|
M |
Date of Birth |
DD/MM/YYYY No quotes |
|
f.09 |
TRIS |
|
O |
Blank |
No quotes |
|
f.10 |
T64 |
|
O |
Blank |
No quotes |
|
f.11 |
Postcode |
|
|
Postal Code for the Client Address with the latest Effective Date linked to the Membership with an Address Type of POSTAL. If no Address with an Address Type of POSTAL is found read the Postal Address for the Address with a Type of RESIDENTIAL and with the latest Effective Date. |
In double quotes |
|
f.12 |
Date joined scheme |
|
M |
Membership Start Date |
In double quotes |
|
f.13 |
Pensionable salary |
|
M |
ACA to provide the specific one |
In double quotes |
|
f.14 |
Credit account value |
|
O |
Retrieve the Monetary balance on the Member’s INVESTMEMB accounts and the unit balance on the Member’s INVSTMEMUNIT accounts for the Income Types for which the value for Member Investment Choice is N as at the date of the extract. If the Type of Investment is UNITISED find the latest unit price and calculate the market value. If the Type of Investment is BONUS find the interest rate applicable as at the date of the extract and calculate interest from the date of the last interest allocation to the date of the extract. Sum the market values of the unitised portfolios, the monetary balances on the bonus portfolios and the interest calculated for the bonus portfolios. |
In double quotes |
|
f.15 |
Investment account value |
|
O |
Retrieve the Monetary balance on the Member’s INVESTMEMB accounts and the unit balance on the Member’s INVSTMEMUNIT accounts for the Income Types for which the value for Member Investment Choice is Y as at the date of the extract. If the Type of Investment is UNITISED find the latest unit price and calculate the market value. If the Type of Investment is BONUS find the interest rate applicable as at the date of the extract and calculate interest from the date of the last interest allocation to the date of the extract. Sum the market values of the unitised portfolios, the monetary balances on the bonus portfolios and the interest calculated for the bonus portfolios. |
In double quotes |
|
f.16 |
Date of values |
|
O |
Current Date |
In double quotes |
|
f.17 |
Participation in salary sacrifice |
|
O |
If the Member is linked to Contribution Membership Group C04, C27 or C31 set this value to YES. |
In double quotes |
|
f.18 |
Matched contributions made |
|
O |
Retrieve the monetary balances on the Member’s CONTRIBUTION account for the Income Types SMTE, SMTR, SSMT, RMTE and RMTR and sum the balances |
In double quotes |
|
f.19 |
AVC contributions made |
|
O |
Retrieve the monetary balances on the Member’s CONTRIBUTION account for the Income Types SAVS, SAVC, SSAV, TAVS, IAVS, RAVC and RAVS and sum the balances |
In double quotes |
|
f.20 |
Phasing |
|
O |
If the Member is linked to Contribution Membership Group C07, C26, C27, C28, C29, C30, C31, C32, C33, C35, C36 or C37 set this value to YES. |
In double quotes |
|
f.21 |
If phasing contribution, maximum level of contributions |
|
O |
|
In double quotes |
|
f.22 |
UK one off 1% contribution paid previously |
|
O |
Retrieve the monetary balances on the Member’s CONTRIBUTION account for the Income Type EOCR |
In double quotes |
|
f.23 |
Target retirement age |
|
O |
|
In double quotes |
|
F24 |
Title |
|
O |
|
In double quotes |
|
F25 |
Marital status |
|
O |
|
In double quotes |
This file will be placed in a directory from where it will be picked up and sent to Connect Direct.