Section 11
Import Definitions Table

This table defines the names of any import files that you need to process from external sources, such as lockboxes, pay-by-phone systems, parking, tax collections, etc., and certain other characteristics about the files, such as their layout, file name, etc.

The external systems creates an export file containing specific information needed by the RASWIN application to generate transactions which will become part of the RASWIN transaction database.

RASWIN supports several different import formats. Three of these are pre-defined and cannot be changed, the last is mostly user definable.

1. 
   Format: CSV
   This is the simplest format: Four comma separated values in a specific order. These values are the TRANCODE, DESCRIPTION, PAYMENT METHOD, and FEE AMOUNT. An example of what such a file might look like is shown below:
   
   
   "45","Prop C Disc - Foothill Mitigat","Cash",37887.00
   "46","Prop C Disc - Foothill Mitigat","Cash",37.11
   "5000","TAP Card Sales","Cash",904.00
   "5001","Farebox Revenues","Cash",246.00
   "5005","EZ Pass Revenue","Cash",2519.68
   "5005","EZ Pass Revenue","Check",80.32
   "8603","Miscellaneous Revenue","Cash",24.00
   
   
   
   Records imported via this method create one receipt for EACH record in the input file.
2. 
   Format: CSV6
   Format: CSV8
   It is also possible to have additional fields in the CSV import records. RASWIN supports imports with 6 or 8 fields. These extra fields include data related to external system receipt or reference numbers, g/l account numbers, transaction dates, etc. In these formats, multiple items for the same host-system receipt number will be grouped into seperate receipts with the RASWIN database.
   
   If you are going to import this type of data Quadrant Support can provide details about the exact format you will need to create from your exporting system.
3. 
   Format: tab
   
   A tab separated record format that is customized specifically to data exported from Tidemark's Permit Plan System. If you are using this interface Quadrant will provide detailed information about the file requirements. Records imported via this method create one receipt for ALL the records in the input file. The user must provide the breakdown of payments tendered by method (e.g., CASH, CHECKS, CreditCard totals). Each unique payment method is added as a separate payment record attached to the single master receipt.
4. 
   
   User Definable FIXED LENGTH records. Only two values MUST be present in each record in the file (specifically, an Account NUMBER or REFERENCE NUMBER, and a TRANSACTION AMOUNT). Other fields may or may not be present in the import data, such as Payer Name, Transaction date, payment method, etc. In these cases, if the data is not present, the import process will provide reasonable default values. Records imported via this method create one receipt for EACH record in the input file. As noted below, each of the import fields, if present, is defined by its starting and ending position within the import record. If either of these values is set to 0, the program will provide a reasonable default value. This does not apply to the Amount field which has to be present in the file as we cannot guess as to the transaction amount. The working assumption in most cases is that the REFERENCE NUMBER field contains the CUSTOMER ACCOUNT (e.g., tax parcel number, utility account #, parking ticket number, etc.)

Each field in the table is listed, along with a short description, below:

1. SOURCE
   
   This value is used to group the various parameters associated with each type of transaction to be processed via the import module Each unique key is displayed in a drop down list on the import screen and this allows you to select one of the sets of values. The name you assign should be reflect of the source for the data .. for example, if you are importing tax payments from a lockbox system, you might assign LOCKBOX-TAX as the source.
2. TRANCODE
   
   The TRANCODE defines the transaction code to assign for each transaction in the batch of items. If the value is N/A, then the TRANCODE must be included in the import data. The TRANCODE, either the one included in the TRANCODE field, or in the import data itself, must be present in the RASWIN TRANCODES table in order for the data to be imported.
3. DATE_START
   
   This value defines the starting position of the ACCOUNTING_date within the transaction data record. If this value is 0, the program will use the current accounting date as the transaction date.
4. DATE_END
   
   This value defines the ending position of the accounting_date within the transaction import records. If the value is 0, the program will use the current accounting date as the transaction date.
5. TRANCODE_START
   
   Defines where the trancode, if present in the import date, starts within the import record. If this value is 0, you must have a valid trancode defined in the TRANCODE field (above)
6. TRANCODE_END
   
   Defines where the trancode, if present in the import date, ends within the import record. If this value is 0, you must have a valid trancode defined in the TRANCODE field (above)
7. AMNT_START
   
   Defines where the transaction amount starts within the import records. The import file MUST contain a transaction amount. The transaction amount field can include the numbers 0-9, commas, periods and the + and - signs. The sign, if present, can be leading or trailing.
8. AMNT_END
   
   Defines where the transaction amount ends in the import record.
9. METHOD_START
   
   Defines where the payment method, if present, starts within the import record. If not present in the import file, set this value to 0.
10. METHOD_END
    
    Defines where the payment method, if present, ends. If not present in the import data, set this value to 0.
11. ACCT_START
    
    Defines where the account number starts within the import record. If this value is set to 0, the program will determine the proper GL account number from the value associated with the trancode in the trancodes table.
12. ACCT_END
    
    Defines where the account number ends. If not present in the import data, set this value to 0.
13. REFNUM_START
    
    Defines where the Reference Number starts within the import record. Normally the reference number is the customer account number, whereas the ACCT NUMBER (above) is the financial system GL revenue number. At some sites you may have a different setup. If set to 0 the program will provide a reasonable default value.
14. REFNUM_END
    
    Defines where the Reference Number field ends within the import record.
15. PAYERNAME_START
    
    Defines where the payer name starts within the import record. If this value is set to 0, the program will use a default payer name.
16. PAYERNAME_END
    
    Defines where the payer name field ends within the import record.
17. COMMENT_START
    
    Defines where the comment field, if present in the import data, begins within the import record. If set to 0 the program will assign a default comment.
18. COMMENT_END
    
    Defines where the comment field ends. If set to 0 the program will assign a default comment.
19. FILE_FORMAT
    
    This field can be set to CSV, FIXED, or TAB. The TAB format is presently applicable for one Quadrant user with a custom import format. The CSV and FIXED formats can be used by any client if the file conforms to the specs defined in this section of the manual. The default value is CSV.
    
    
20. FILE_NAME
    
    This specifies the name of the import file to be associated with the import profile name. This is usually a complete path and file name and can include a specific disk drive letter, or a UNC file name...e.g.,
    N:\utility\exports\quad_import.dat
    or
    \\some_server_name\exports\quad_import.dat
    The import function uses the specified file name as the assumed file name when the import process is initiated. However, you can change the name by typing in a different name at the time of the import if your export process does not create the file using the pre-defined path and name. Changing the file name at import time does not cause the value, if any, stored in this table to be changed.
    
    
21. DECIMAL_HANDLING
    
    This setting tells the import process whether a decimal point is PRESENT or IMPLIED (default value). If the value is set to PRESENT the program will read the transaction amount value from the file and not adjust it in any way. So values like 40.00, 40., 40.0 and 40 would all be treated as $40.00. If set to IMPLIED the program will assume the amount value from the file is expressed without a decimal point ..e.g., 4000 will be treated as $40.00 (the amount is divided by 100 to determine the correct value. Note that each defined import definition and be set to either of the two options (PRESENT or IMPLIED), but you must ensure that in the actual data file the associated data must match the defined method of handling the decimal point.