Import Vendor Data Version

Contents

This topic discusses:

Links to related topics:

Vendor information: Import vendor catalogs:

Administrator topics:

Search Online Doc
Current Release Notes

Overview

The vendor data import feature provides a convenient way to add or change Materials Management and AP vendor information. The feature also allows you to synchronize vendor information in the system with an external (i.e., non-) AP application's vendor records. The data that you import are records that describe the vendor, one or more buy-from locations, and/or AP locations. Information might include; for example, address, discount terms, payment terms, default buy-from location, and so on. (If you wish to import vendor catalogs into the system, use the EDI 832 facility.)

You can add a new vendor record, change vendor information, logically delete a vendor, suspend vendors, reactivate vendors, and more. You can add, change, suspend, or delete AP locations and buy-from locations independently of the vendor that they belong to.

This topic explains how to add vendors to ERP using imports. You can also enter vendor data manually, which is covered in this topic: To add a vendor manually.

Note: A system setting, described below, lets you decide if you will enter vendor data manually, import vendor data, or do both. If your site is set up to import vendor data only, you will not be able to change or create vendor records manually.

Security

Display of vendor imports (Imports/Exports & Financials > Vendor Import) is controlled by the role object "Vendors." The minimal setting is View.

Users must have the proper authorization to update vendor information via an import file.
Authorization is set on the Users - Authorities tabbed panel, and through role objects "Vendors" and "VendorsMMandAP."
The flag on the Authorities panel is Allow Add/Update/Delete of Vendor Type "MM and AP." The flag is set "on" by default.

To access the Authorities panel:
- From the Work in Administration main Contents, select Administration > Users.
- The Users list appears. Locate the user of interest.
- Click Menu > Edit or click the edit icon .
- Click the Authorities tab. (It is the second to the last tab.)

Select the Method for Adding Vendors in System Values

Below are instructions on how to add vendors to ERP manually. You can also import vendor data, which is covered in this topic: Import Vendor Data.

Users want to add vendors to ERP with both the import method, or manually, as they choose. A feature field Vendor Maintenance Mode is available for this purpose (Figure 1).

Figure 1 - Vendor Maintenance Mode: Select Method of Adding New Vendors

Before you add a vendor to ERP, check this field and set it as you wish.

  1. From the main Materials Contents, select Administration > System Values. System values appear.
  2. Click Menu > Edit. A tabbed panel appears.
  3. Select the Other tab.
  4. Click the drop down arrow and select the desired option:
    - To always only enter new vendor records manually: select Manual Only.
    - To always use the vendor import only for new vendors: select Import Only.
    -To use either the import or manual entry, as you choose, at any time: select Manual and Import.
  5. Click Submit.

Steps for Importing Vendor Data

Importing vendor information has these steps:

When you upload a vendor file, the system performs these steps:

  1. Validates the file - the system checks the file for viruses, verifies that the file is a vendor update file, and determines if the file format is correct.

  2. Reads the file - the system reads the file to determine the action(s) needed; for example, adding or changing a vendor record. You set the actions in the import file's ACTIONCODE field for each vendor record. See "How ACTIONCODES work."

  3. Processes the file to update the system vendor information - the system updates its vendor records based on the information in the import file.

If the system cannot complete any step because of errors, it logs the errors. You can view the errors and correct them.

Note:
The field Help on the Authorities panel explains how the Allow Add/Update/Delete of Vendor... flag works, in concert with the relevant role objects.
- Click the Help button. Tabbed help information appears; click the Authorities tab.

Types of Vendor Imports

Several types of imports are available for vendors. The file format is the same for each type, except that data will be needed in different fields, depending on what you are importing. You can ...

Creating and editing a vendor import file

  1. Generate or create the import file.

Important Note: For editing the file, a text editor is preferable to a spreadsheet program. Spreadsheet programs (e.g., Excel) add commas for blank columns. The system cannot process trailing commas. Also, the spreadsheet column formats must match the formats expected by the system. Simply saving your file from the spreadsheet does not guarantee that the column formats will be correct. For this reason, editing and saving a .csv file in a text editor is better.

  1. Open the .csv file that you made in the previous step and edit it so that the fields and field values are consistent with the fields and values that the system expects. (See the Important Note above.)

  2. Add Rows 1 and 2.

Note: The data values that you enter in each column in the .csv file must match any parallel data values that you have defined in the system tables. For example, if your Discount Table contains the value: 2/10, you cannot enter 2%-10 as data in your .csv file. The import process will reject 2%-10.

  1. Reopen the file in MS Notepad (or another text editor) so that you can see the data as lines containing commas.
  2. Add the following end-of-file marker after the last line of comma-separated data, on a line by itself:

<EOF>

Important: Your file must contain <EOF>, by itself, on the last line.

  1. Save the file and close the text editor.

Import File Fields and Examples

Figure 2 displays the fields and their order on the comma-separated import file.

Figure 2 - Vendor Import File Fields - Extended Version

Table 2 provides details about fields that let you import different types of vendor data.

Difference between using the vendor edit panels on the application and importing vendor data with the template:

Several numeric fields that contain numeric data on the vendor edit panels automatically default to zero when you use those panels and leave the fields blank. This situation is not the same when you use the vendor import template. These fields, which you might normally leave blank (for "no") in the application panels must contain a value on the import template. Thus, the table below describes these fields as "required=Yes" when importing data.

Table 2 - Field Settings for Vendor Data Types - Extended Import

VENDOREXT CustomerID Time Stamp        
RecordType 1     Yes

1=Vendor, 2=AP Loc
3=Buy From, 4=Vendor/BFL, 5=Vendor/APL, 6=Vendor/BFL/APL
Note: Types 4,5,6 for Action Code 'A' only
7 - Asset Loc Hospital Account
8 - Department Hospital Account

All
ActionCode 1     Yes   All
VendorNumber 15     Yes    
ExtAPVendorNo 15     Yes   All
VendorType 1     No 1=MM, 2=AP, 3=MM/AP
Used for Vendor Adds only
1,4,5,6
VendorName 50     No   1,4,5,6
VendorID 15     No   1,4,5,6
BuyFromLoc 15     No   3,4,6,7,8
APLocation 15     No   2,5,6
AddressName 50     No   2,3,4,5,6
AddressLine1 50     No   2,3,4,5,6
AddressLine2 50     No   2,3,4,5,6
AddressLine3 50     No   2,3,4,5,6
City 30     No   2,3,4,5,6
StateCountry 2     No   2,3,4,5,6
Country 30     No   2,3,4,5,6
ZipCode 10     No   2,3,4,5,6
TaxID 15     No   1,4,5,6
Code1099 10     No   1,4,5,6
CustSvcContact 50     No   2,3,4,5,6
ContactTelNo 30     No   2,3,4,5,6
FaxNumber 30     No   2,3,4,5,6
ContactEmailAddress 30     No   3,4,6
DefaultBFLYN   1 0 Yes 1=Yes, 0=No
Sets BuyFromLoc as Vendor default (MM, MM/AP)
1,4,5,6,8
DftInvoicingExtAPVendor 15     No   1,4,5,6
DftInvoicingAPLoc 15     No   1,4,5,6
PaymentTerms 10     No   1,4,5,6
PaymentMethod 10 1   Yes 2,5,6,8
Bank 15     No   2,5,6
BankAccount 15     No   2,5,6
BankAccountRouting 15         2,5,6
DiscountTerms 10         1,4,5,6
DefaultAPLYN   1 0 Yes 1=Yes, 0=No
Sets AP Loc as Vendor default (AP only)
1,4,5,6,8
DeleteSuspend 1     No   1,4,5,6
VendorGroup 15     No   1,4,5,6
PaymentPriority 15     Yes Value may be 1 through 9 1,4,5,6,8
POTransmitType 15 1   Yes 3,4,6,8
MinOrderAmt   19 4 No   3,4,6
DftFreightType 15     No   3,4,6
VendorOrderCycleCode 10     No   3,4,6
SeparatePOByDeptYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
SepPOByItemTypeYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
AutoAuthEDIPOYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
AutoAuthFaxPOYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
AutoAuthEmailPOYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
FillKillYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
AutoProcessASNYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
Org 15     Yes   7,8
AssetLoc 15     Yes   7,8
Dept 15     Yes   8
DeliverToLoc 15     Yes   8
HospitalAcctNo 15     Yes   7,8
DftPayingAPLoc 15     No   2,5,6
DftPayingVendor 15     No   2,5,6
EmailAddress 30     No   2,5,6
TaxGroup 10     No   2,5,6
TaxType 10     Yes 2,5,6,8
DirDlvYN   1 0 Yes   3,4,6,8
FreightLimit   19 4 Yes Decimal value for the approximate amount allowed as a freight charge. The Freight Limit is transferred to a PO header when the PO is created, but can be changed on the PO. 3,4,6,8
RGARequiredYN   1 0 Yes 1=Yes, 0=No 3,4,6,8
TaxIDType 10     No   1,4,5,6
LegalNameFor1099 50     No   1,4,5,6
AddressLine1For1099 50     No   1,4,5,6
AddressLine2For1099 50     No   1,4,5,6
AddressLine3For1099 50     No   1,4,5,6
APLocationFor1099 30     No   1,4,5,6
CityFor1099 2     No   1,4,5,6
StateCountryFor1099 30     No   1,4,5,6
ZipCodeFor1099 10     No   1,4,5,6
CountryFor1099 30     No   1,4,5,6
AutoMatchEDIYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
DftDistProfile 15     No   1,4,5,6
FinRptFld1 10     No   1,4,5,6
FinRptFld2 10     No   1,4,5,6
FinRptFld3 10     No   1,4,5,6
ProjectCode 10     No   1,4,5,6
FundCode 10     No   1,4,5,6
GLAcctDftDistP1 15     No   1,4,5,6
GLAcctDftDistP2 10     No   1,4,5,6
GLAcctDftDistP3 10     No   1,4,5,6
SepPmtPerInvYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
InComplianceYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
SmallBusinessYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
FemaleOwnedYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
LocallyOwnedYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
MinCertifiedFromDate 10     No   1,4,5,6
MinCertifiedNo 15     No   1,4,5,6
MinCertifiedToDate 10     No   1,4,5,6
MinorityCertifiedYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
MinorityVendorYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
PreferredStatusYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
RestrictedVendorYN   1 0 Yes 1=Yes, 0=No 1,4,5,6,8
BTGLNCode   13 0 Yes   1,4,5,6,8
STGLNCode   13 0 Yes   1,4,5,6,8
AsianIndianYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8
AsianPacificYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8
BlackYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8
HispanicYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8
NativeAmericanYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8
VeteranYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8
VeteranServiceDisabledYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8
LGBTQYN   1 0 Yes 1=Yes, 0=No
Set to zero (0) if not entered
1,4,5,6,8

Table 3 explains field settings that implement different import types, and provides examples.

Table 3 - Using Fields for Different Types of Vendor Imports

To do this: Use Field Settings: Notes:
Add a vendor only (no buy-from location or AP location) Record Type 1, VendorType 3.

Example: Add a vendor

ActionCode field = A

Add an AP location only RecordType 2, VendorType blank, DefaultAPLYN 1 (if applicable).

Example: Add an AP location

ActionCode field = A

Add a buy-from location only RecordType 3, VendorType blank, DefaultBFLYN 1 (if applicable), DftInvoicingExtAPVendor, DftInvoicingAPLoc.

Example: Add a buy-from location

ActionCode field = A

Add an MM vendor and buy-from location at the same time

RecordType 4, VendorType 1

Example: Add an MM vendor and buy-from location

ActionCode field = A

Add an AP vendor and AP location at the same time RecordType 5, VendorType 2

Example: Add an AP vendor and AP Location

ActionCode field = A

Add an MM/AP vendor, AP location, and buy-from location at the same time RecordType 6, VendorType 3

Example: Add an MM/AP vendor, a buy-from location, and an AP location

ActionCode field = A

Change a vendor RecordType 1, VendorType blank. ActionCode field = C
Change buy-from location data RecordType 3, VendorType blank. ActionCode field = C
Change AP location data RecordType 2, VendorType blank. ActionCode field = C
Make a buy-from location the vendor default RecordType 3, VendorType blank, DefaultBFLYN 1.

Example: Change a buy-from location address and make it the buy-from location the default.

ActionCode field = C

Make an AP location the vendor default RecordType 2, VendorType blank, DefaultAPLYN 1. ActionCode field = C
Change the default invoicing location on a buy-from location RecordType 3, VendorType blank, DftInvoicingAPLoc, DftInvoicingExtAPVendor ActionCode field = C

Suspend a vendor
Unsuspend a vendor

RecordType 1, DeleteSuspend 3
RecordType 1, DeleteSuspend 4

ActionCode field = C

Suspend a buy-from location
Unsuspend a buy-from location

RecordType 3, DeleteSuspend 3
RecordType 3, DeleteSuspend 4

ActionCode field = C

Suspend an AP location
Unsuspend an AP location

RecordType 2, DeleteSuspend 3
RecordType 2, DeleteSuspend 4

ActionCode field = C

Delete a vendor
Undelete a vendor

RecordType 1, DeleteSuspend 1
RecordType 1, DeleteSuspend 2

ActionCode field = C
Delete a buy-from location
Undelete a buy-from location

RecordType 3, DeleteSuspend 1
RecordType 3, DeleteSuspend 2

ActionCode field = C
Delete an AP location
Undelete an AP location

RecordType 2, DeleteSuspend 1
RecordType 2, DeleteSuspend 2

ActionCode field = C

How ActionCodes work

The add (A) and delete (D) ActionCodes add and delete vendor records, including buy-from locations and AP locations. The C (change) code changes vendor information, suspends, and reactivates vendors. The C code works as follows:

When you enter C in the ACTIONCODE field, and a date in the SUSPENDEDDATE field, the system checks to see if the vendor is already suspended, and if not, suspends the vendor.

When you enter C in the ACTIONCODE field, and leave the SUSPENDEDDATE field empty, the system checks whether the vendor is already suspended, and if so, reactivates the vendor.

When you enter a C in the ACTIONCODE field, and the changes you wish to make in other appropriate fields, the system first checks to determine if you intend to suspend or reactivate a vendor. If there is no date in the SUSPENDEDDATE field, and if the system's record for the vendor shows that the vendor as active, the system assumes that you wish to make other changes.

Note about suspended vendors: When you "unsuspend" a vendor using an import, the system unsuspends all AP and Buy-From locations for the vendor. You can suspend and unsuspend AP locations and buy-from locations separately.

Note about adding vendors: When you add a new vendor, the system creates a new vendor number for the vendor, and associates the new vendor number internally with the ExtAPVendorNo value from the input file.

To import vendor data into the system

  1. From the Materials Management main Contents, select Imports/Exports & Financials > Vendor Import.
    Or, from the Accounts Payable Contents, select Imports/Exports > Vendor Import.
    The Import List for Import Type Vendor appears (Figure 3). The list contains vendor files that have been uploaded.

Figure 3 - List of Imported Vendor Files

  1. Click New. The Vendor Import panel appears.
  2. Enter the file that you wish to import to the system in the File to Upload field.
  1. At this step, you have a choice. You can work through the import process in two stages or in one stage. For new users of this feature, we recommend the two-stage approach.

- Select the box labeled Load and Verify, No Import.

- Click Submit. The system attempts to upload your file and validate it. If successful, the system sends you a message. If the system cannot upload your file and validate it, error messages appear. Read and close the error messages. Make changes as needed in your .csv file and try the upload again. Click here to read debugging information.

The Import List for Import Type Vendor appears.

- Click Refresh to see your file listed.

- Click Submit. The system attempts to upload your file and validate it. The Import List for Import Type Vendor appears.

- Click Refresh to see your file listed.

  1. Review the Import List... entry for your file. The following fields provide useful status data:

Import Status Desc - The successful or unsuccessful status of the upload.

Errors: The .csv file could not be read or validated.

Ready for Processing: If you have used a two-stage upload, the file is ready for the second (update) step.

Complete: Update of system data from the import file is complete and successful.

Incomplete: The file could not be brought into the system. (You may have the wrong file name or path.)

Processed - The number of data records processed out of the total uploaded. This number shows how many of the import file records successfully updated the system vendor records.

n of X: X is the number of data records uploaded and n is the number of records processed.

For a one-stage upload, this field indicates whether the system was able to update its vendor data using the imported file's records, and, if so, how many updates were successful. 0 of X means that the system was not able to update any of its vendor data using the imported file's records. Click Menu > View Import Details. The Vendor Import Details list appears, and you can review your data for errors.

For a two-stage upload, 0 of X is normal.

To complete processing of your import file:

If you are using the one-stage approach, the system has run the import step for you. You can review any errors, and begin fixing any problems.

If you are using the two-stage approach, you need to process the vendor file that you uploaded so that the the system records are updated. Do the following:

  1. From the main Contents, select Vendors > Vendor Import. The vendor Imports table appears.
  2. Locate the file that you wish to process.
  3. Click Menu > Reprocess. the system asks you to verify that you wish to reprocess the file.
  4. Click Submit.

To debug your import file:

You can encounter errors at any or all of the stages of vendor record import and update. Error messages provide information about problems with the .csv file. In most cases, you will wish to alter your .csv file. Sometimes, you may wish to change values in the system tables, such as establishing new Discount Terms to accommodate a particular vendor's data.

A. At the validation and reading stages:

  1. Check that the file name and path are correct.
  2. Make sure that the import file is a .csv file.
  3. Verify that your file has the header line.
  4. Check that you have included a line containing the correct column names, in the correct order, and that none are missing.
  5. Check that the correct number of fields are in the import file for each record.
  6. Review the ACTIONCODE values to make sure they are correct.
  7. Check that the file contains <EOF> on the last line.

B. At the update stage:

When you select Menu > Reprocess, the system attempts to update its vendor records using the imported records. The number of records successfully updated appears on the Import... list, and errors in processing each record are logged. To see the errors:

  1. Click Menu > View Import Details. The imported records appear (Figure 4).

Figure 4 - Vendor Import Details

  1. Review the Processed field for each record to determine which have errors. Yes indicates that the record successfully updated the system. If the field is empty, the record has a problem, and could not update the system.
  2. Click View Errors to get a list of the problems with the record. In most cases, you will want to go back to the .csv file, correct the data, and re-import the file.

Import Additional Buy-From and AP Locations

On a vendor import, you can add multiple buy-from and AP Locations for a vendor.

To add multiple buy-from or AP locations:

  1. On the import spreadsheet, create a record that adds a vendor with the initial buy-from location and AP location.
    The settings (from Table 3) are:

    To do this: Use Field Settings:
    Add an MM/AP vendor, AP location, and buy-from location at the same time RecordType 1, VendorType 3, DefaultBFLN 1(if applicable), DefaultAPLYN 1 (if applicable).

    Use the "A" action code (Figure 5).
  2. Create additional records for the same vendor that contain each buy-from and AP location being added (Figure 5).
    Use the settings for fields (below). These settings are outlined in Table 3.

    To do this: Use Field Settings:
    Add an AP location only RecordType 2, VendorType blank, DefaultAPLYN 1 (if applicable).
    Add a buy-from location only RecordType 3, VendorType blank, DefaultBFLYN 1 (if applicable), DftInvoicingExtAPVendor, DftInvoicingAPLoc.

  3. Upload the spreadsheet as normal. Figure 5 is an example. You can also view a sample file for adding a vendor and multiple buy-from and AP locations.

Figure 5- Adding Buy-From and AP Locations on a Vendor Import Spreadsheet