Solomon IV – Transaction Import Utility

iMIS provides support to meet export/import requirements for Solomon IV – VI Transaction Import utility. This support is in place in iMIS LAN version 3.5 or greater and iMIS version 3.31 or greater.

Make sure all GL accounts in iMIS match the accounts in Solomon’s charge of accounts (and sub-accounts, if used in Solomon). When you enter a GL account in iMIS, use the format xxxx-yyyy, where xxxx=the account number (i.e. 1200 for AR) and yyyy=sub-account number (defined in Solomon). The length of the account or sub-account can vary, and is defined in Solomon.

Note: iMIS currently supports two methods to export/import general ledger or financial data to Solomon IV. Financial transaction data can either be exported/imported using a Read Consolidation File option with Solomon IV, or through the Transaction Import Utility. You may wish to review the import steps for both options before deciding which one you would prefer to use. For those users who are licensed for the iMIS Accounts Payable Interface, AP vendor and transaction data can only be imported using the Transaction Import Utility. Refer to the Solomon System Manager manual for additional details on Transaction Import.

Note: Address line 3, which was added in iMIS 10.6, is not exported because the Transaction Import utility does not support it.

The following paragraphs provide some implementation setup tips for integrating iMIS with Solomon IV using the Transaction Import Utility.

The iMIS to Solomon IV (- TI) Interface works through the Solomon IV Transaction Import utility. The Transaction Import utility serves as the bridge to bring information from outside packages, such as iMIS, into the normal Solomon IV files. This utility provides the mechanism for bringing financial (accounts receivable/cash/income) data generated through iMIS into your Solomon IV General Ledger. If you are licensed for the iMIS AP Interface, it also provides the vehicle for imported vendor and accounts payable transaction data into Solomon IV for generating refund checks.

Note: The Solomon IV SQL-TI works the same as Solomon IV-TI except that the Ledger ID for the set of books that the transactions will be posted to must be specified in the export/import records.

The monthly (or more frequent) interface runs are quite straightforward.

On the Solomon IV side, the only setup required is to copy the control files supplied specifically for iMIS imports to the Solomon directory. The control files give the Solomon transaction import utility specific instructions as to how to import export data generated from iMIS. The control files for iMIS exports can be downloaded from the ASI Technical Support website. The following files should be copied into the main Solomon directory. Solomon will report an error that the file cannot be opened if the file is not in the expected directory. The control files are:

For the Financial Systems (GL) Interface:

  • IMIS.CTL – Control file to support the GL transaction import for Solomon IV-TI
  • IMISSQL.CTL – Control file to support the GL transaction import for Solomon IV SQL-TI

For the Accounts Payable Interface:

Note: Solomon only allows 10 characters for the vendor ID, which includes the letter “I” plus the iMIS member ID. Therefore, the member ID cannot exceed nine (9) characters in order to support the processing of refunds in Solomon.

  • IMISVEN.CTL – Control file to support the Vendor import
  • IMISAP.CTL – Control file to support the Voucher import

To set up iMIS for Solomon IV – Transaction Import Utility

The setup steps from iMIS include making appropriate choices on the AR/Cash General Ledger Interface window and a required step to establish a SOLOMON general/lookup validation table.

The iMIS setup steps are described in detail below:

Step 1 – Change the GL Interface Type

  1. From AR/Cash, select Set up module, and click G/L Interface to open the AR/Cash General Ledger Interface window.
  2. Click Edit.
  3. From the GL interface type drop-down list, select Solomon IV-TI or Solomon IV SQL-TI. If you are using Solomon VI, select a Solomon interface type.
    • Select Solomon IV-TI if Ledger IDs are NOT used in Solomon.
    • Select Solomon IV SQL-TI if multiple Ledger IDs are used in Solomon.
  4. Select an option from the Summarize by drop-down list.
  5. Enter the Output File Path/Name, which dictates how iMIS should name the export/import file and where it should be stored.

    6. For best results, set the extension of the file name to .DTA (i.e., I:\IMIS\GLINT.DTA).  The .DTA extension will make it easier to find the import file using the lookup window.

    • Use a # sign in the file name to instruct iMIS to create files for each iMIS organization code, with the ORG code inserted in the file name.
    • Use a ? mark in the file name to instruct iMIS to create sequentially numbered files with each subsequent file that is generated.

    For example, #glint?.dta will create ORG1glint1.dta and ORG2glint1.dat for the first export and ORG1glint2.dta and ORG2glint2.dta for the next export.

  6. Click Save.

Step 2 – Set up a special SOLOMON General Lookup/Validation Table

You must follow these steps if you process sales tax through iMIS. If not, you can skip them.

  1. From Customers, select Set up tables > General lookup/validation to open the General lookup/validation window.
  2. Select (New Table) from the list of table types. The New Table Type window opens.
  3. Enter a New Table named SOLOMON and click Save.
  4. Click New and insert a new record with the code of LEDGER in all caps in the Code field.
  5. In the Expansion field, fill in the correct codes for Solomon IV Ledger ID.
  6. Click Save.

To create the export file from iMIS (Solomon IV – Transaction Import Utility)

  1. Generate the general ledger transaction export files using AR/Cash > Process month-end procedures > Create GL Journal. If batch processing is enabled, only posted batches are included.

To import data into Solomon IV – Transaction Import Utility

After the new import files are created by iMIS, they can be imported into Solomon IV.

  1. Select Transaction Import from the Solomon IV Utility menu.

  2. Mark the first line as selected.
  3. For the data file name enter the output file name including the path name that you specified in iMIS in the second column or press F3 to open a search dialog window. The third column should default to ASCII.
  4. Select the screen in the fourth column by pressing F3. The following list of screens displays:
    • 0101000 (Journal Transactions) for GL Import
    • 0327000 (Vendor Maintenance) for Vendor Import
    • 0301000 (Voucher and Adjustment Entry) for Voucher Import
  5. Scroll over and enter the import control file into the fifth column.  The import control file describes the import file. Select the control file from the following options:
    • IMIS.CTL for GL Import without Ledger ID (Solomon IV -TI), or
      IMISSQL.CTL for GL Import with Ledger ID (Solomon IV SQL -TI)
    • IMISVEN.CTL for Vendor Import
    • IMISAP.CTL for Voucher Import
  6. In the sixth column, enter a file name for the creation of an error or log file to be created during the import.
  7. Enter the appropriate import control file name including the path name or press F3 to open a search dialog window. The seventh column should read Minimized and should not be changed.
  8. Select Begin after you have filled in all columns. If you named an error file, you can review the status of the import through Edit Errors.

This is an example of a normal error log output for a general ledger transaction import. The sample error log is configured to include “all import data”, and the Batch is out of balance message will always appear until the user enters a control and posts the batch. Make sure the number of errors near the end of the log file is zero.

'System Message 8000: Start Processing Wed Feb 23 04:07 P.M.
'System Message 8042: Transaction Import Processing Combine Edit and Update mode
'System Message 8040: Database Name SSTRIAL             
'System Message 8011: ========Begin Processing of a Batch Data Line========
'"Batch",,GJ,N,199607,R,-1,0,0
'System Message 8005: New Record Requested
'System Message 8012: ========Successful Processing of a Batch Data Line========
'System Message 8011: ========Begin Processing of a Detail Data Line========
'"Detail",1020,00-000-00-00-00-0,,03/03/2011,"110301-2: AR, TEST-AR -17",,1719.38
'System Message 8005: New Record Requested
'System Message 8012: ========Successful Processing of a Detail Data Line========
'System Message 8011: ========Begin Processing of a Detail Data Line========
'"Detail",1110,00-000-00-00-00-0,, 03/03/2011," 110301-2: PAY, DEMO-AR-PAY",1719.38,
'System Message 8005: New Record Requested
'System Message 8012: ========Successful Processing of a Detail Data Line========
'System Message 818: Batch is out of balance by 1719.38.  Do you want to edit?
'System Message 8030: The Number of Errors detected was 0

Note: Two import files (Vendor & Invoice) are required to support the AP Interface.  You can shortcut your steps by entering the import instructions for both files (Vendor First) on one Transaction Import window.

  1. When the import is complete, review the general ledger import batch through the General Ledger > Journal Transactions window.
  2. Select Accounts Payable > Voucher and Adjustment for the AP interface, to review the batch of transactions.  If you press F3 at the Batch Nbr field, the newly imported batch should be the first batch in the list.
  3. To process/post the batch, fill in the batch control amount with the amount shown as the debit or credit amount at the bottom of the window.

  4. Print the iMIS GL transaction batch using the Quick Print button at the lower left corner of the window.  (For the AP batch report, select Accounts Payable > Reports > AP Edit).
  5. When you are ready to post the batch, change the Batch Handling drop-down list to Release Now.
  6. Select General Ledger > Post Transactions to post the iMIS batch.

File Layout Examples

Financial Systems Interface – Solomon IV-TI

General Ledger Transaction Import/Export output file contains a batch header line and any number of detail lines:

"Batch",,GJ,N,200506,B,-1,0,0
"Detail",1100,0001,,06/13/2005,"PAY, DEMO-AR-PAY",100.00,
"Detail",1100,0001,,06/13/2005,"PAY, DEMO-MEETING-PAY",50.00,
"Detail",4000,0001,,06/13/2005,"DIST, DEMO-AR-LABELS",,100.00
"Detail",1250,0001,,06/13/2005, "DUE TO BLDG",,50.00
 
Layout definition:
Header record ("Batch")
# Field Name Sample Data Comments
1 Record type "Batch" 
2 Batch number blank 
3 Journal Type GJ see Solomon journal type codes
4 Batch Type N see Solomon batch type codes
5 Period Posted 200506 taken from Trans.FISCAL_PERIOD
6 Batch Handling B see Solomon batch handling codes
7 Auto Ref # -1 turns auto-ref numbers on
8 Auto Reverse 0 turns auto-reverse off
9 Ctrl total 0 sets control total to zero for the batch
 
Detail records ("Detail")
# Field Name Sample Data Comments
1 Account 1100 
2 Subaccount 0001 
3 Ref # blank
4 Trans date 06/13/2005 
5 Trans description "PAY, DEMO-AR-PAY"
6 Dr amount 100.00
7 Cr amount blank

Financial Systems Interface – Solomon IV SQL-TI

General Ledger Transaction Import/Export output file contains a batch header line and any number of detail lines:

"Batch",,GJ,N,200506,"ACTUAL",B,-1,0,0
"Detail",1100,0001,,06/13/2005,"PAY, DEMO-AR-PAY",100.00,
"Detail",1100,0001,,06/13/2005,"PAY, DEMO-MEETING-PAY",50.00,
"Detail",4000,0001,,06/13/2005,"DIST, DEMO-AR-LABELS",,100.00
"Detail",1250,0001,,06/13/2005, "DUE TO BLDG",,50.00
 
Layout definition:
Header record ("Batch")
# Field Name Sample Data Comments
1 Record type "Batch"
2 Batch number blank
3 Journal Type GJ see Solomon journal type codes
4 Batch Type N see Solomon batch type codes
5 Period Posted 200506 taken from Trans.FISCAL_PERIOD
6 Ledger ID "ACTUAL" from the iMIS validation table entry
7 Batch Handling B see Solomon batch handling codes
8 Auto Ref # -1 turns auto-ref numbers on
9 Auto Reverse 0 turns auto-reverse off
10 Ctrl total 0 sets control total to zero for the batch
 
Detail records ("Detail")
# Field Name Sample Data Comments
1 Account 1100
2 Subaccount 0001
3 Ref # blank
4 Trans date 06/13/2005
5 Trans description "PAY, DEMO-AR-PAY"
6 Dr amount 100.00
7 Cr amount blank

Accounts Payable Interface

Sample output for the Vendor Import/Export file:

Vendor,I136,"A","Sailing Dreams, Inc.",2100,0001,"U","Sailing Dreams, Inc.","Ms. Margaret K. Nelson",,"P.O. Box 8765","","Dallas","TX","75206-8765",,,,"Sailing Dreams, Inc.","Ms. Margaret K. Nelson",,"P.O. Box 8765","","Dallas","TX","75206-8765",,,,"N"
 
Layout definition:
Header record ("Batch")
# Field Name Sample Data Comments
1 Record type "Vendor" 
2 Vendor Code "I136" Always "I" plus the appropriate iMIS ID
3 Status "A" Active
4 Name "Sailing Dreams…" Stores as the Solomon Vendor name
5 AP Acct 2100
6 AP Sub-acct 0001
7 Pay date "U" Designates 'due date'
8 Name1 "Sailing Dreams…" 
9 Attn1 "Ms. Margaret…" From the iMIS name fields
10 Salutation blank 
11 Addr1 "P.O. Box…" Preferred Bill address fields
12 Addr2 
13 City "Dallas"
14 State "TX"
15 Zip "75206-8765"
16 Country blank
17 Phone blank 
18 Fax blank
19 Remit Name "Sailing Dreams…"
20 Remit Attn1 "Ms. Margaret…" From the iMIS name fields 
21 Remit Salutation blank
22 Remit Addr1 "P.O. Box…" Preferred Bill address fields
23 Remit Addr2 
24 Remit City "Dallas"
25 Remit State "TX"
26 Remit Zip "75206-8765"
27 Remit Country blank
28 Remit Phone blank 
29 Remit Fax blank
30 ReceiptAccept "N" N = 'accept quantity / no warning'

Sample output for the AP Transaction or Invoice Import/Export file, which includes a separate voucher and detail transaction line for each invoice in need of a refund:

Document,"","VO",06/24/2005,"A","",06/24/2005,"I136",,,06/24/2005,06/24/2005,45.00,45.00,2100,0001
Transaction,2500,0001,45.00,"Refund: R95 Books Are Good"
 
 
Layout definition:
Detail record ("Document")
# Field Name Sample Data Comments
1 Ref Nbr blank
2 Doc Type "VO" for voucher
3 Doc Date "06/24/2005"
4 Status "A" for Active
5 Invc Num blank
6 Invc Date "06/24/2005"
7 Vendor ID "I136" "I" plus the iMIS member ID
8 PO # blank
9 Terms blank
10 Due Date "06/24/2005" 
11 Pay Date "06/24/2005"
12 Orig Doc Amt 45.00
13 Disc Bal 45.00
14 AP Acct 2100
15 AP Sub-acct 0001
 
Detail record ("Transaction") - this is a detail line for the preceding Document record
# Field Name Sample Data Comments
1 Acct 2500
2 Sub-acct 0001
3 Trans amt 45.00
4 Trans Descr "Refund: R95 Books Are Good"

Note: Solomon only allows 10 characters for the vendor ID, which includes the letter “I” plus the iMIS member ID. Therefore, the member ID cannot exceed nine (9) characters in order to support the processing of refunds in Solomon.