H8.6.2.4a Import format for payments - LUPMISManual
Main menu:
8. LUPMIS Tools > 8.6 Revenue Tools > 8.6.2 Entry of revenue data
8.6.2.4a Import Format for Payments
Level of expertise required for this Chapter: Expert; specifically for LUPMIS @ TCPD
| Data | Essential ? | Format / Remark | Example | Excel column |
| UPN | ESSENTIAL | xxx-yyyy-aaaa format, as string | 678-0789-0123 | A |
| SubUPN | If exists: ESSENTIAL | xxx-yyyy-aaaaA format as string or blank or single letter | 678-0789-0123E | B |
| Street name | Deprecated, not used anymore, leave blank | Blank | C | |
| House/property number | Deprecated, not used anymore, leave blank | Blank | D | |
| Amount of payment | ESSENTIAL | as figure (value) in GHc | 15 | E |
| 'Property' or 'Business' | ESSENTIAL | word 'property' or 'business' | Property | F |
| Payee | as string | Kofi | G | |
| Date of payment | If payments: ESSENTIAL | dd-mm-yyyy format, as string (see remark below) | 31-01-2012 | H |
| Payment station | as string, max length 20 | Assembly | I | |
| Payment receipt | as string, max length 30 | ABC12 | J | |
| Receipt computer number | as string, max length 50 | K | ||
| Receipt date | dd-mm-yyyy format, as string (see below) , if different than payment date | 31-03-2012 | L | |
| Payment type | word 'payment', 'fee' or 'arrears', max length 30 | automatically by LUPMIS | M | |
| Number of Demand Notice | as string, max length 30 | 678-0789-0123-2012-05-01 | N | |
| Date of Demand Notice | dd-mm-yyyy format, as string (see below) | 01-05-2012 | O | |
| Date of submission of DN | dd-mm-yyyy format, as string (see below) | 05-05-2012 | P | |
| Arrears | Only for arrears | Word 'arrears' (leave blank, if empty) | arrears | Q |
| Previous year of arrears | Only for arrears | Year from when arrears dates (leave blank, if empty) | 2014 | R |
| 'payment' | ESSENTIAL | word 'payment' | payment | S |
| Comments | as string | no comments | T | |
| -99 | ESSENTIAL | control variable, as number | -99 | U |
In total, there should be 24 columns (last column in Excel: U).
File name should contain '_field_data_' (e.g. STMA_field_data_payments_area1.csv)
General requirements:
- Empty cells are allowed, except where indicated (UPN, collector id, amount).
- If SubUPN is entered, it has to be unique (UPN + 1 or 2 or 3 letters).
- If necessary, insert emtpy columns in your spreadsheet.
- Codes for pay status, business class, property use etc can be viewed at the Printing Tools > Classes
- Observe the number -99 at the right-most column as control variable.
- All date formats must be as strings. You might have to change the property of the Excel column to 'Text' or insert a single quotation at the first character in the Excel cell.
- No commas or and-signs(&) or quotations (" or ') are allowed anywhere in your Excel data, as they work as delimiters in the CSV file. Delete or replace them in Excel.
- Data from the CSV file will overwrite data in the LUPMIS Revenue Database. If the cell in the Excel/csv file is blank, this will NOT overwrite/delete the LUPMIS revenue data.
General preparations of CSV file in Excel:
- 1. If numbers are set up with comma as 1000-separator, delete it: Select area > Format cells > Number > Number > No tick on 'Use 1000 Separator' > OK .
- 2. If numbers are entered as text, convert them to numbers, e.g. insert in a column: +(cell of the text number)+0, copy this formula down to the whole range, then copy-paste all of them as values to a new column.
- 3. Once you are sure, there are no commas in the number columns, convert all commas to points (Control-F > Replace).
Special import option:
You can use this table also to import arrears. You have to fill following columns:
- UPN (and SubUPN if applicable)
- Amount of arrears (in box of payment, column: E)
- Word 'Property' or 'Business' (column: F)
- Word 'Arrears' (column: N)
- Year of arrears (previous year, from when arrears date back, column: O)
- Word 'Payment' (column: P)
- Number -99 (column: R)
This special import can be called by ticking the box at the selection screen.
Techical Note:
If you re-import, be aware that fees and arrears should be negative, payments positive (opposite to internal processing and display in LUPMIS). Specific arrears import are positive.
