Batch Accounts Payable File Import and Data Selection Page

Use this page to load batch accounts payable records from files stored on either your personal computer or network server.

Menu Path:  Fund Accounting > Entry & Processing > Payable Entry > Batch Accounts Payable > click Import on the Action Bar

Import Wizard

The Batch Accounts Payable page's Import item activates the Import Wizard, which takes 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 Batch Accounts Payable option, but not defined in the file.
• Saving the mapping that was created to be used for future file imports.
• Viewing a list of warnings and error messages generated during the data validation. These are the same messages users receive when manually adding batch accounts payable records.

File Requirements

The ability to import a file depends on the following:

• The data in the file's fields must comply with the field requirements in accounts payable records. For example, if a field requires numeric data and the field in the external file contains characters, the system cannot import the file.
• The file must be formatted as XML Spreadsheet 2003, Comma Separated (CSV), Tab-Delimited (TDF/TXT), or Pipe-Delimited ("|").
• The extension on the file must be *.xml, *.exml, *.csv, *.txt, or *.unl.
• You have the appropriate security resource to access the Import item.

Creating a File for Import

Users can create the file to be imported into batch accounts payable in any manner they choose. The easiest way is to use a spreadsheet program. The file can contain one or more batches of accounts payable. The database table used to store batch accounts payable is dpayable. 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.

• dpayable.batch (Control Number) - Required\
  Required, must be included in the file or entered in the Static Column Value Mapping page of the wizard.
  Note
  If importing multiple payables that should have different control numbers, the control numbers must be in the file. When the control number is set on the Static Column Value Mapping page, all payables will be linked to the same control number.
• dpayable.yr (Fiscal Year) - Required
  If the fiscal year is not in the file, the fiscal year will default on the Static Column Value Mapping page. The default is from the Year field on the Fund Accounting Profile. This field can be modified to the prior year if necessary. 
  If importing multiple payables spanning multiple years, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
• dpayable.period (Period) - Required
  If the period is not in the file, the period will default on the Static Column Value Mapping page. The default is from the Period field on the Fund Accounting Profile. This field can be modified to another period if necessary.
  If importing multiple payables spanning multiple periods, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
• dpayable.gl_cash (G/L Cash Account) - Required
  If the cash account is not in the file, the cash account will default on the Static Column Value Mapping page. The default is from the Cash field on the Fund Accounting Profile. This field can be modified to another cash account if necessary.
  If importing multiple payables that will be associated with different cash accounts, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
• dpayable.enc_no (*PO/Encumber* Number) - Required
  This field is used to determine whether a payable is a Purchase Order Payment or Non-Purchase Order Payment. To be a Non-Purchase Order Payment, this field should have a value of 0. For a Purchase Order Payment, enter a valid encumbered purchase order number.
  If importing multiple payables that may contain both Purchase order Payments and Non-Purchase Order Payments or if multiple purchase orders are being imported, then this field should be included in the file. Otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables
• dpayable.line_no (*PO/Encumber* Line Number) - Required
  This field is used to determine whether a payable is a Purchase Order Payment or Non-Purchase Order Payment. To be a Non-Purchase Order Payment, this field should have a value of 0. For a Purchase Order Payment, enter a valid encumbered line number associated with the purchase order number.
  If importing multiple payables that may contain both Purchase Order Payments and Non-purchase Order Payments or if multiple purchase orders with more than one line item are being imported, then this field should be included in the file. Otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
• dpayable.app_group (*Approval*)
  There are three (3) approval flags on the Fund Accounting Profile, Accounts Payable Tab, indicating whether the client is using approvals, as follows:
  
  • Approval for PO Payments
  • Approval for PO Payments Overpayment Only
  • Approval for Non-PO Payments
    If one or more of these flags are set on the profile, then dpayble.app_group is required and needs to be included in the file or entered in the Static Column Value Mapping page of the wizard.
    If none of these flags are set on the profile, then the dpayble.app_group is not required and will not be pulled into the Static Column Value Mapping page.
• dpayable.vend_no (Vendor Number) - Required
  If importing multiple payables that will be associated with different vendors, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
• dpayable.p_f (Partial/Final Payment Flag) - Required
  If the Partial/Final Payment Flag is not in the file, it will default on the Static Column Value Mapping page. The default is from the Default Partial/Final Flag field on the Fund Accounting Profile. This field can be modified if necessary. The values entered should be P or F.
  If importing multiple payables with different Partial/Final Payment Flags, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
  This field is only used for Purchase Order Payments. For Non-Purchase Order Payments, leave this field blank if it is in the file. If the field is not in the file and the value is entered on the Static Column Value Mapping page, during the import process, this field will be set to blank for all Non-Purchase Order Payments. This is determined for all records where the dpayable.enc_no and dpayable.line_no are set to 0.
• dpayable.due_date (Due Date) - Required
  If importing multiple payables with different due dates, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
• dpayable.single_ck (Single/Print Check Flag) - Required
  This field is used to indicate whether or not to print separate checks for payables entered to the same vendor based on different invoice numbers. It is the same indicator used for EFT vendors to indicate if an actual check should be issued to the vendor. If not in the file, defaults to N on the Static Column Value Mapping page.
• dpayable.key_orgn (*Budget Unit*) - Required
• dpayable.account (Account) - Required
• dpayable.amount (Payment Amount) - Required
• dpayable.project (*Project*)
• dpayable.proj_acct (Project Account)
• dpayable.c_1099 (1099 Type) - Required
  This field is used to indicate if the payable is tracked for 1099s. Valid values that can be entered are M, I, G or N.
  If importing multiple payables that have different 1099 types, then this field should be included in the file. Otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all payables.
• dpayable.description (Payment/Item Description)
• dpayable.invoice (Invoice Number)
• dpayable.disc_date (Invoice Date)
• dpayable.sales_tax (Sales Tax) - Required
  If not in the file, defaults to 0 on the Static Column Value Mapping page.
• dpayable.use_tax (Use Tax) - Required
  If not in the file, defaults to 0 on the Static Column Value Mapping page.
• dpayable.disc_amt (Discount Amount) - Required
  If not in the file, defaults to 0 on the Static Column Value Mapping page.
• dpayable.disc_per (Discount Percent)
  If not in the file, defaults to 0 on the Static Column Value Mapping page.
• dpayable.qty_paid (Quantity Paid)
• dpayable.qty_rec (Quantity Received)
• dpayable.alt_vend_no (Alternate Vendor Number)
• dpayable.voucher (Voucher 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 used to validate the Cash Account as it does for the other Batch Payables.

Following are the procedures for importing a file into Batch Accounts Payable. At any time in the Import Wizard, clicking Next will advance you to the next page. Clicking Previous will take you back to the previous page. Clicking Cancel takes you back to the Batch Accounts Payable page.

Importing a File into Batch Accounts Payable

1. Select Fund Accounting > Entry & Processing > Payable Entry > Batch Accounts Payable to display the Batch Accounts Payable Page.
2. Click Import to display the File Import and Data Selection page.
3. Click Browse... in the File to Import field to locate the file that you want to import from your personal computer or network server.
4. Once the file is located, select the file and click Open, to have the file appear in the File to Import field.
5. Click Load to load the data from the file into the Import File Data section of the File Import and Data Selection page.
6. 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, select the correct line and then click Set Column Titles again.
7. Once the column titles are 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.
   Click Select All to select all the lines of the file to be imported. Note that selecting blank rows will prevent the import process from completing. Remove blank rows that are selected by holding down the <ctrl> key on the keyboard and clicking on the blank row.
8. When all the lines have been selected, click Next to display the Define Column Mapping Page.
9. In the Define Column Mapping page, select a mapping from the drop down 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.
10. Once the columns in the file have been mapped to the fields in the database table, click Next.
    • If the data in the file does not meet the requirements of the database table, the Import Errors page displays. You will not be able to proceed until the file or mapping is corrected.
    • If there are no errors with the requirements between the data in the file and the database table, the Static Column Value Mapping page will display.
11. In the Static Column Value Mapping page, required fields from the database table that are not mapped on the Define Column Mapping page will display.
    In addition, non-required fields that are in the database table and not in the file can be selected using the Append Row button.
    When the dpayable.yr (Fiscal Year), dpayble.period (Period), dpayable.gl_cash (G/L Cash Account), dpayable.p_f (Partial/Final Payment Flag), dpayable.single_ck (Single/Print Check Flag), dpayable.disc_amt (Discount Amount), dpayable.disc_per (Discount Percent), dpayable.sales_tax (Sales Tax) and dpayable.use_tax (Use Tax) are mapped statically, the values will default.
    For details, refer to the Creating a File for Import section above. Values entered in the Static Column Value Mapping page will be used for every line number in the file being imported.
12. Once the Static Column Value Mapping is complete, click Next to display the Save Import Mapping page.

13. In the Save Import Mapping page, you can assign a unique name to the mapping that has just been defined.

    • If you choose to make the mapping Public, anyone who can import a file can use the mapping.
    • If you choose to make the mapping Restricted, users other than the owner of the mapping will not be able to update the saved mapping.

    Note
    The ability to add and update an import mapping on the Save Import Mapping page is dependent on security in addition to the Restricted field.

14. Once the selections on the Save Import Mapping page have been completed, click Finish. This will take you out of the Import Wizard.
15. If the system detects any errors or generates warning messages during data validation, the Import Status page will display.
    • The page lists all errors and warning messages. The Line # column lists the number of the lines in the source file that need to be corrected.
    • The page's Summary Information section will display the number of Errors, Warnings, and Successes. If there are no Successes, the Import item will not display.
    • You can choose to display Warnings Only, Errors Only, Warnings/Errors Only or Show All information by making the appropriate selection in the Display Results field. You can also print the listing of errors by clicking Print.
    • You can choose to import any successes by clicking the Import item or by correcting the file and running the import process again.
    • To exit without importing any records, click Back.
16. If there are no errors found during data validation or if the Import item is selected in the Import Status page, click Yes in the confirmation dialog to import the file. Another confirmation dialog displays when the process is complete.

The batch accounts will now display in the Batch Accounts Payable page. The payables will be set with the Hold Status of On Hold.

Fields

File Import and Data Selection Page

This page allows users to select the file and records to import into Batch Accounts Payable.

Define Column Mapping Page

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

Import Errors Page

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

Static Column Value 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 Value Mapping pages.

Import Status Page

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