Import Batch Receipts

The Import item on the Batch Receipts page enables you to load batch receipts from files stored either on your personal computer or network server into Batch Receipts tables.

Menu Path

From the Fund Accounting menu, select Entry and Processing. In the Receipts menu, select Batch Receipts. From the action bar menu, select Import.

The Import item launches a wizard that will take you step by step through:

• Loading the file.

• Mapping the columns on the file to the fields in the database table.

• Mapping static columns that are required by the Import option, but not defined in the file.

• Saving the mapping that was created to be used for future file imports.

• Generating a list of warnings and/or error messages found during the data validation. These are the same messages a user will receive if manually adding a deduction record.

File Requirements

Your ability to import a file depends on the following:

• The data in the file's fields must comply with the field requirements in Batch Receipt records. For example, if a field requires numeric data and the file in the external contains characters, the system cannot import the file.

• The file must be formatted as XML Spreadsheet 2003, Comma Separated (CSV), Tab-Delimited (TXT), or Pipe-Delimited ("I").

• The extension on the file to be imported must be .xml, *.exml, *.csv,.txt, or *.unl.

• You have the appropriate security resource to access the Import item.

Creating Files for Import

Users can create the file to be imported into Batch Receipts in any manner they choose. The easiest way is to use a spreadsheet program. The database tables used to store these batch receipt records are dreceipt and recdeposit. The file created can contain any of the following fields in any order. Once the file is created, it needs to be saved with the extension of *.xml, *.exml, *.csv, *.txt, or *.unl.

Fields

Required fields must be included in the file or entered in the Static Column Value Mapping page of the wizard. Defaults will display on the Static Column Value Mapping page, but may be changed.

• dreceipt.account (Account) - Required

• dreceipt.batch (Control Number) - Required

• dreceipt.description (Description)

• dreceipt.enc_no (Receivable Number) - Required - default is 0
  If the Receivable Number is not 0, the software will default data into these dreceipt fields from the recledgr table when the user imports the batch receipt record:

  • GL Asset Account - recledger.gl_account (gl_recv - display only)

  • Budget Code - recledgr.key_orgn (key_orgn)

  • Account - recledgr.account (account)

  • Project - recledgr.proj (project)

  • Account - recledgr.proj_acct (proj_acct)

  • Description - recledgr.description (description)

  • Payer Number - recledgr.vend_no (vend_no)

• dreceipt.gl_cash (Cash Account)

• dreceipt.gl_cash_key_orgn (GL Cash Key Organization)

• dreceipt.gl_recv (GL Asset Account)

• dreceipt.hold_flg (Hold)

• dreceipt.invoice (Receipt Number) - Required

• dreceipt.key_orgn (Budget Unit) - Required

• dreceipt.period (Period) - Required

• dreceipt.proj_acct (Project Account)

• dreceipt.project (Project)

• dreceipt.vend_no (Payer Number)

• dreceipt.trans_amt (Receipt Amount) - Required

• dreceipt.yr (Year) - Required

• recdeposit.deposit_date (Deposit Date)

• recdeposit.deposit_num (Deposit Number)

The bank account field is required if the Print Checks by Bank Account option is selected in the Fund Accounting profile. The bank account will be a Table Field selected either during the Column Mapping or Static Value Mapping.

The bank account will be used to validate the Cash Account as it does for the other Batch Receipts.

If the Full Account Format option is enabled in the Fund Accounting Profile, then:

• The import file must include the account number as defined in the translation table. The account number can be mapped to the Account Number field. The label for the Account Number field is be pulled from the Fund Accounting profile. The Account Number replaces dreceipt.key_orgn and dreceipt.account.

• If the import file contains the Cash Account Number, it is mapped to Cash + "Account Number" full account title from the Fund Accounting profile. The Cash Account Number replaces dreceipt.gl_cash_key_orgn and dreceipt.gl_cash.

• The cash account number in the import file is converted to the gl_cash_key_orgn and account (gl_cash) based on the general ledger translation table. If the general ledger cash key organization and cash account are not found, Cash Account number not found on General Ledger error is reported.

• The error log displays the Account Number instead of the Budget Unit and Account code in the format defined in the expenditure, revenue, or general ledger translation table based on whether the account code falls into the expenditure, revenue, or general ledger (assets, liabilities, or equities) range on the Fund Accounting profile.

Import Files

Following is the procedure to import a file into Batch Receipts. At any time in the import wizard, click Next to advance to the next page. Click Previous to take you back to the previous page. Click Cancel to go back to the Batch Receipts page.

Importing a File Into Batch Receipts

1. Click Import to display the File to Import Data and Selection page.

2. Click Choose File or Browse in the File to Import field to locate the file you wish to import from your computer or network.

3. Select the desired file, and then click Open to have the file appear in the File to Import field.

4. Click Load to load the data from the file into the Import File Data section of the File to Import Data and Selection page.

5. In the Import File Data section, select the line that will be used for the Column Titles, and click Set Column Titles. The data selected will appear in the column headings of the Import File Data section. If the incorrect line was selected, click Reset Column Titles to put the line back into the list section and begin this step again.

6. With the column titles set, select the lines of the file to be imported. This can be done by clicking the first line, then holding the Shift key down and clicking the last line, or you can click each line individually by using the Ctrl key. You can also click Select All to select all lines.

7. When all the lines have been selected, click Next to display the Define Column Mapping page.

8. In the Define Column Mapping page, select a mapping from the dropdown selection in the Saved Import Mapping field or tab into the Table Field column for the first Import File Column to map the file column to the appropriate field in the database table. The Field Description column will default to a user friendly name and cannot be changed.

9. Once the columns in the file have been mapped to the fields in the database table, click Next. The Import Errors page will appear if the data in the file does not meet the requirements of the database table. The Static Column Value Mapping page will only display when there are no errors.

10. In the Static Column Value Mapping page, some required fields from the database table that are not mapped on the Define Column Mapping page will display with defaulted values. Other required fields, where the defaults are processed during the import, will not automatically display in this page. These can be mapped statically by using the Append Row button along with non-required fields that are in the database table and not in the file. Values entered in the Static Column Value Mapping page will be used for every line number in the file being imported.

11. Once the static mapping is complete, click Next to display the Save Import Mapping page.

12. In the Save Import Mapping page, you can give the mapping a unique identifying name. If you select to make the mapping Public, anyone who can import a file can use the mapping. If you select to make the mapping Restricted, only the owner of the mapping will be able to update the saved mapping.

13. Once the selections on the Save Import Mapping page have been completed, click Finish. This will take you out of the Import wizard.

14. The Import Status page will display if there are any errors or warning messages during data validation when the file is imported. In this page, all errors and warning messages will display. The Line # column represents the line number in the actual file to make it easier to find the data that must be corrected before the import. The Summary Information section will display the number of Errors, Warnings and Successes. If there are no Successes, the Import item will not appear. In the Display Results field, you can choose to display Warnings Only, Errors Only, Warnings/Errors Only and Show All information. You can also print the listing of errors by clicking on the (missing or bad snippet) item (default file name: importstatus.rpt). You can choose to import any successes by clicking the Import item or choose to correct the file and run through the import process again. To exit without importing any records, click Back.

15. If there are no errors with the data validation or if the Import item is selected in the Import Status page, the Confirmation will display.

16. Click Yes to import the file. This will generate the final Confirmation.
    The batch receipts records will now display in the Batch Receipts page.

Fields

File Import And Data Selection Page

This page allows users to select the file and records to import into Batch Receipts. This also initiates the Import wizard.

Define Column Mapping Page

This page allows users to map the columns in the file to be imported with the fields in the database table.

Import Errors Page

This page generates a list of errors when the data in the file does not match the requirements of the fields in the database table.

Static Column Mapping Page

This page allows users to statically map required fields in the database table to a value. This value is used for all lines of the file to be imported.

Save Import Mapping Page

This page allows users to save the mapping created in the Define Column Mapping and Static Column Mapping pages.

Import Status Page

This page allows users to see error and warning messages generated during the data validation of the Import into Batch Receipts.