Item Import, Export, and Mass Maintenance Version

Contents

This topic discusses:

Links to related topics:

Search Online Doc
Current Release Notes

Overview

The Item Import and Mass Maintenance feature lets you import one -- or many -- item records into Supply Chain from a Microsoft Excel® worksheet. With the item import feature, you can download item records from Supply Chain to Excel, update them, and import them back to Supply Chain. Also, you can mass import new item records into Supply Chain and delete item records. Several types of item records are supported: item catalog records, item inventory records, item vendor records, and item vendor UOM records.

Important: The upload and import process has no “automatic undo” capability. If worksheets containing incorrect data are uploaded unintentionally, the item records will need to be corrected manually.

Notes and Advice About Item Maintenance

Importing new items and item updates into Supply Chain can be challenging because multiple detailed worksheets are required. Users who are fluent with Excel spreadsheet capabilities, and who have some familiarity with database tables are the best candidates for the item maintenance task.

The best advice we have for working with the maintenance feature is this:

Security

The Item Maintenance feature must be enabled before you can use either Item Imports or download the Excel Item Maintenance Add-In. (Both are available from the Materials Management main Contents.) To enable the Item Maintenance feature for your site, contact the Help Desk.

In Excel, if the Item Maintenance feature is disabled, you will not be able to upload or download any data, and will receive an error message: "The Item Maintenance feature is not enabled for your installation. Contact Supply Chain support to activate this feature."

Once the Item Maintenance feature is enabled for your site, the following security settings apply:

Important: You must change the default setting (None) to one of these values.

If your security settings are not correct, you receive the message “Your Security Level for 'Item' is not high enough to perform this action." when you try to use the Item Maintenance feature with Excel. Contact your Supply Chain system administrator to change your user profile.

How the Item Mass Maintenance Feature Works

To set up the feature, you download an Excel template file and add-in from Supply Chain to a local directory on your computer or on your network. The add-in must be installed and enabled on Excel. When you open Excel, use the Excel Add-In panel to locate the add-in on your network, and enable the add-in.

The template and add-in provide:

To import item data to Supply Chain, you enter item data in the rows of the worksheet templates. You can use Excel tools such as copy and paste to enter data. When you are finished making changes or entering new item records, you upload the worksheet(s) to Supply Chain, and Supply Chain imports the data.

On the worksheet template, the ActionCode column tells Supply Chain what to do with each uploaded item record. The ActionCodes are:

From the list of item import jobs (Materials Management Imports/Exports & Financials > Item Imports) , you can view the status of an import job, and any messages or errors.

Valid Vendor Type for imports:

You can only import items (using Item Import and Mass Maintenance) whose vendor is an MM vendor or an MM and AP vendor. The vendor or the buy-from location cannot have been deleted. However, items can be imported for a suspended vendor/buy-from location.

To download item data from Supply Chain to Excel, you first establish selection criteria for the item records to be downloaded. For example, you may wish to download items for a particular vendor or item class. Excel prompts you for the selection criteria when you request a download. You can create (and save) new selection criteria, or you can choose selection criteria that you or others defined earlier. (Pre-defined criteria can also be edited.)

Downloading and Installing the Excel Template and Add-In

Supply Chain lets you download item records to Excel, and accepts item records uploaded from Excel for import into your item catalog and item inventories. To simplify the creation and import process, a template and an Excel add-in are available as downloads from Materials Management. The template contains the correct item record formats for import. The Excel add-in lets Excel exchange data between   ERP and your local network.

To use the Excel Item Maintenance template with the add-in, you first must install both to a network location where you can access them from Excel. Then, you enable the add-in on Excel.

- The template and the upload/download add-in minimally require Windows 7, running Excel 2010.

- Item mass maintenance feature also runs on Windows 8.1, and Windows 10 using the Excel 32-bit version (Excel 2013 and 2016).

The current version of the template and add-in replace versions in formats earlier than Excel 2010. Before you install the new version, disable (by unselecting) the old version that you are currently using. The instructions below explain how to install, enable, and disable an installed Excel add-in.

To download and install the Excel item import template and add-in:

These instructions download and install the  Item Maintenance Template for Excel 2010 (and later versions: Excel 2013, 2016) and the Excel add-in (which lets you upload data to Materials Management).

Important:  members accessing the ERP Materials Management application through  must use the Excel 2010 template and add-in.

Users working with Excel versions prior to 2010 can continue with old templates that they have downloaded in the past, as long as they do not need to sign on through .

Caution: The download and install process overwrites any existing Excel worksheet with the same name. If you have an existing worksheet with the same name, you may wish to rename it so that its data are preserved.

  1. From the Materials Management main Contents, select Imports / Exports & Financials > Excel Item Maintenance Download.
    The Download panel appears (Figure 1).
  2. Select Click here to download this File.
    The File Download panel appears.

Figure 1 - Downloading the Excel Item Maintenance Add-in

  1. Click Run.
    Depending on your Internet Explorer Security Settings, a verification panel may appear.
  2. If the verification panel appears, click Run.
    The installation wizard panel appears (Figure 2).
  3. Click Next.
    Follow instructions on the install Wizard, clicking Next as you move through the panels.

Figure 2 - The Installation Wizard's Initial Panel

  1. The Destination Folder panel appears (Figure 3).
    The Destination Folder panel specifies where the template and the add-in will be installed. If you wish to install the add-in at another location, click Change and specify the location.

Figure 3 - Destination Location for Installed Excel Add-In

  1. Click Next.
    The Start Installation panel appears.
  2. Click Install.
  3. The system installs the add-in and template in the location specified. A message appears when installation is complete (Figure 4).

Figure 4 - Finish Template Installation Panel

  1. Click Finish. The add-in template is installed and available on your local system in the default location, or in the location you specified. Next, you need to enable the add-in.

Enabling/Disabling Excel Add-Ins

The steps for enabling an Excel add-in (once you have installed it) are the same as the steps to disable an existing Excel add-in (before you install a new version). The difference is that to enable the add-in, you select the box next to the add-in in the steps below. To disable the add-in, you unselect the box.

Note: You only need to perform the enable step one time.

To enable an add in
  1. On the Excel ribbon, click File. The Excel Menu appears (Figure 5).

Figure 5 - Opening the Excel Options Panel

  1. Click Options (Figure 5).
    The Options panel appears (Figure 6).

Figure 6 - The Excel Options Panel

  1. Click Add-Ins.
    The Add-Ins panel appears (Figure 7).

Figure 7 - Accessing the Excel Add-ins List

  1. At the bottom of the panel next to Manage: Excel Add-ins, click Go. (Figure 7).
    The list of Available Add-ins appears (Figure 8). (Note: Your list may contain more, or fewer add-ins.)

Figure 8 - List of Available Add Ins for a User

  1. Select the   Item Maintenance Add In.
    If this add-in does not appear on the list...
    • Click Browse (Figure 8, right side).
    • Open the location where you saved the download. Figure 9 is an example.

Figure 9 - Using the Browse Button to Find the Item Maintenance Add-In File

  1. Click OK. The system installs the add-in to Excel.
To disable an add in

Note: If you are removing an add-in completely (perhaps to replace it), you must not only delete/uninstall it from the appropriate folder, but also delete it from the add-ins list (Figure 8).

Sign On to Supply Chain for Item Mass Maintenance:  Users

You must sign on to  ERP to access data records.

When the add-in is activated on Excel, click the , Inc. tab on Excel. Several buttons appear in a toolbar at the top left (Figure 10).

Download Items
Upload Items
Create Template.

Figure 10 - Excel Links for Signing on and Uploading/Downloading

These buttons trigger item record upload/downloads, and let you create item record worksheet templates. When you click any of the buttons for the first time in an Excel Item Maintenance session, Sign On panels appear.

Sign On to ERP for Item Maintenance
  1. On the Excel worksheet (Figure 10), click Download Items, Upload Items, or Create Template.
    If this is your first time signing on using the Item Maintenance add-in, the Host Setup panel appears (Figure 11).

Figure 11 - The Sign On Panel for an Excel Add-In Feature

  1. Click Host Setup (Figure 11) to identify the host location (Premier/SSO or Aperek) that you are accessing.
    The Host Setup panel appears (Figure 12).

Figure 12- Host Setup Panel for Excel Add-Ins

  1. Select either Premier/SSO or Aperek.

- Premier/SSO is for users who access ERP/SCM via Premier Connect as single sign-on (SSO) users.
- Aperek is for users who access ERP/SCM directly via the ERP application URL for their site.
- The default is the last option selected.

When you select Premier/SSO, the panel changes to resemble Figure 13.

Figure 13 - Host Panel for SSO

  1. Click Save. The Sign on panel appears (Figure 14).

Figure 14 - The Sign On Panel for a  User

Signing on with the "Aperek" host option

Signing on with the "Premier/SSO" option

  1. Enter your email address/User Name (Figure 15).
  2. Click Sign On.

Figure 15 - Excel Add-In SSO User Sign On

Your site's network/application login panel appears. Figure 16 is an example.
Your site administrator can set you up with the correct URL and other parameters.

  1. Enter your normal login credentials, and sign on.

Figure 16 - Generic Sample: Network User Sign On Panel

For SSO, the sign on proceeds as follows:

- The Excel Add-In directs control to the Premier Connect page.
- The Premier Connect page looks at the email address and decides if you are an SSO user or not.
- In case of incorrect credentials, the user will get invalid login message.
- Otherwise, the Sign On panel disappears and you are signed on.

One of several panels appears (Create Upload, Create Template, or Item Selection) depending on which link you clicked to sign on (Figure 10).

Working with Item Records: Methods

To import item records from Excel to Supply Chain, you use one or more worksheets.
Four Excel worksheet templates are available: Items, ItemVendors, ItemInventories, and ItemVendorUOMs. The worksheet templates correspond to the various item record types. If you open two or more templates, each appears in the workbook as a separate worksheet, labeled for its item record type.

Important: Record Safety

The Item Mass Maintenance feature is a high-impact feature. In short, "you can shoot yourself in the foot" very easily if you are not clear on the use of fields in the item records, and how the worksheets upload.

Important: The upload and import process has no “automatic undo” capability.
If worksheets containing incorrect data are uploaded unintentionally, the item records will need to be corrected manually.

Tip: If you copy item information such as an item description, MIN, VIN, or other text from a .pdf or other file, it may contain invisible or invalid characters, such as a vendor/manufacturer registered trademark symbol. The item maintenance templates will not upload this information, and you will have errors. If there is any doubt about what your text copy contains, so you may wish to key in the value, rather than copying it.

Contact the Help Desk for other suggestions.

To add new item records, open blank worksheet template(s) on Excel, and enter data for the new items.
For each item record, enter A In the ActionCode column. Then, upload the worksheet(s) to Supply Chain.

Tip for adding records: Put a blank row between item records that you are adding. Finding data input errors will be easier.

To change or delete item records, you can use either method below:

Using Excel Item Record Templates

When you download item records from Supply Chain, Excel displays the records in one or more worksheets. You can open blank item record worksheets in Excel if you need to add item records, for example.

To open a blank Excel item record template:

  1. From an Excel worksheet (with the item import add-in activated) click Create Template (Figure 17).
    The Create Template panel appears (Figure 17).

Figure 17 - Opening a Template for Item Import

  1. Under Include sheets, select the type(s) of item records that you wish to create. In Figure 17, all four template types are selected.
  2. Click Create.

Figure 18 - Worksheet Templates for Items

Entering Data on the Worksheet Templates

In general, the columns on the worksheet templates match the editable fields in Supply Chain item records. For example, you will not see an "On Hand Quantity" field on a worksheet item template because that field in Supply Chain is not editable.

Key database columns on the worksheets have blue headers.

Important: When you are using a worksheet to change downloaded, existing item information, do not alter data in blue columns (except to specify the correct ActionCode). Unexpected consequences could result. You can enter data in these columns when you are creating new items, of course.

Comments Indicators

Worksheet comments display the type of data for each column. Normally, you will want the comment indicators enabled so that you can check the column data type and number of characters. If you do not see the comment indicators on a Supply Chain item template worksheet, they probably have been turned off.

  • To view a comment for a column, mouse over the indicator .

Mouse over

Values for the column appear

Columns with drop-down selection lists: Columns such as Item Status and Item Type provide a drop-down selection list when you click in the cell. Make a selection from the list. If you leave a cell in one of these columns empty, Excel will display an error message.

Text and Numeric Columns: Text columns contain a single character, or a "string" of characters. Numeric columns contain decimal or integer values.

"No value" entries in text and numeric columns: If you want an item record to have no entry for a particular field, use the following rules for entering "no value" in worksheet cells.

Yes/No Columns: Columns with a header ending in YN (for example, AllowSubstituteYN in Figure 20) are yes-no fields. Enter zero (0) for no or 1 for yes.
(The format type for these columns is called Boolean. You will see the word "Boolean" for the column type when you put the cursor on the column header.)

Deleting columns: You can delete columns that are not required and that you do not need, but you cannot delete columns with blue headers. If you do, you will get upload errors. You can also delete rows that you do not need. Deleting unneeded rows and columns can save time.

Changing column headers: If you change the header in any column, on import, Supply Chain ignores the column. This tactic is useful if you are updating values in a column such as ItemClass, but need to keep another column, such as ItemDesc, unchanged, but available for reference. Altering the ItemDesc header to, say, ItemDescXXX prevents the column from importing.

Renaming worksheets: You can also rename a worksheet. This technique is useful, for example, if you have made changes to a worksheet, but for some reason, may need to download the worksheet again, and do not want the download to overwrite your changes.

A table describing all the columns on the four templates is at the end of this topic.

Importing Item Records to Supply Chain from Excel

This section uses an example to illustrate the worksheet templates. The example starts with blank worksheets. Typically, for changing item record data, or deleting records, you would not begin with empty worksheets, but instead would download item data from Supply Chain, change the data, and then import it back to Supply Chain.

A general word about adding items using a worksheet template:

Adding items using the worksheet templates requires you to keep track of the values that you enter on the different templates. For example, if you enter the value 1 in the Item worksheet for the Item Status column, you must enter the same value for the same item in the Item Status column on the Item Inventories worksheet. This type of cross checking can be tedious when you are keying in unstructured data.

Caution: Using copy and paste to enter data on a worksheet template should only be done by experienced Excel users.

Copying and pasting data into Supply Chain Excel templates can cause problems, depending on what you copy and where you are copying from.

For example, if you copy a column from, say, a vendor's worksheet to a Supply Chain Excel template, the data format of the vendor's column may be different from the format in the Supply Chain Excel column. Also, data elements from an external source can contain hidden characters, including initial or trailing spaces. If you paste data to a Supply Chain template, exercise caution, and verify that the data you paste is compatible with what Supply Chain expects.

When you paste data to a Supply Chain Item Import template from another worksheet, use the Excel Paste Special feature. Paste only the values, not the other worksheet's cell formats.

Hint: Contents of the columns on each worksheet are documented in a subsequent section in detail. However, you may find it useful, if you are adding items, to first download item records for similar items from your item catalog. You can then study the worksheets for the downloaded items to get a better idea of what each column contains.

Default values

When an organization creates a new item, the values of several fields can be set as defaults and written automatically to item inventory records.

The fields are: CDM, Pass-Through Code, and Billable/Non-Billable Expense Code.

To use these fields as defaults for any inventory record -- when the item is assigned to an inventory -- an organization sets the flags Assign Patient Charge Number (CDM) from Catalog and Assign Expense Codes from Catalog on its organization record. The default values for CDM, Pass-Through Code, and Billable/Non-Billable Expense Code are then retained on the item catalog record, and used to automatically populate any future inventory assignments. If your organization flags are set, you can establish default values for these fields on the Items spreadsheet when you upload a new item or change an item. If you use the defaults, however, you cannot alter the values in the item inventory directly: entering values for these fields on the Item Inventories panel will have no effect.

Using Worksheets: An Example

Imagine that you have activated the Excel add-in for upload and download, and have opened four templates to enter and import some item records. You have several items (Figure 19) with no UNSPSC codes, and you wish to add the UNSPSC codes to the item records. Also, you wish to import a new item record for the same vendor.

Figure 19 - Items from the Item Catalog that Need UNSPSC Codes

The Items Template

Figure 20 is a sample Excel Items worksheet, with data being entered in its columns. (In Figure 20, spreadsheet comment indicators are turned off so that you can see the column headers.)

Figure 20 - Excel Items Template with Data Being Entered

Entering Data on the Items Template

Changing item records:

Adding item records:

Figure 21 - The Items Worksheet Template with New Items for Automatic Item Numbers

When the worksheet is imported, Supply Chain assigns the next two sequential item numbers to the two items.

- For new items to be automatically numbered, on the other worksheet templates, use the same code for each ItemNo that you used in the Items template. The goal is to have worksheet templates each use the same code for the same item. Figure 22 is an example of the ItemInventories, ItemVendors, and ItemVendorUOMs worksheets that would go with the Items worksheet (Figure 21) for the two new items.

Figure 22 - Worksheet Templates with the Corresponding Item Codes on the Items Template

Note: The next time that you add items, if you want Supply Chain to assign numbers, you can start again on a new worksheet with *ItemNo_1.

  • For ItemStatus and ItemType in Figure 22, the template provides a drop box for selecting a value. Click in a cell in either of these columns and the drop box appears.

    - In adding a new item to the item catalog, set the ItemStatus column to 1 (active). Then, when you import data from the ItemInventories, ItemVendors, and ItemVendorUOMs worksheets and the item data is complete, the item Status will be Active.

  • Single-sourced item :
    - On the Items worksheet, no entry is needed in the column VendorOverrideYN for a single-sourced item.
    However, on the ItemInventories worksheet, you will need to enter 0 in the column VendorOverrideYN.

  • Multi-sourced item:
    -
    On the Items worksheet, no entry is needed in the column VendorOverrideYN for a multi-sourced item.
    However, on the ItemInventories worksheet, you will need to enter 1 in the column VendorOverrideYN. (See "Multi-Sourcing an Item" in the online documentation.)

    - On the ItemInventories worksheet, be sure to enter the organization and asset location that multi-sources the item.

    - On the ItemVendors and ItemVendorUOMs worksheets, enter the vendor information for the asset location that multi-sources the item.

The ItemVendors Template

Figure 23 is a sample Item Vendors worksheet, with data for the new (single-sourced) item from (Figure 22) entered -- the ActionCode is A.
No changes are needed on this worksheet for the existing items, so their records are not included.

Figure 23 - Excel Item Vendors Template with Data

Entering Data on the ItemVendors Template

Changing item records:

Adding item records:

The ItemVendorUOMs Template

Figure 24 is the ItemVendorUOMs worksheet, with information entered for the new (single-sourced) item. Without item vendor UOM information, an item cannot be ordered, and the item record is Incomplete.

Figure 24 - Excel ItemVendorUOMs Template with Data

Entering Data on the ItemVendorUOMs Template

Changing item records:

Adding item records:

The ItemInventories Template

Figure 25 is a sample of part of an ItemInventories worksheet template for the new item record. (The comments on the worksheet are turned off so that you can see the column headers.) Use the ItemInventories worksheet to assign the item to an asset location.

Note: The item must be Complete -- i.e., the item must have all the information needed for purchasing -- before you can assign it to an inventory. Use the ItemVendorUOMs worksheet template to create units of measure for purchasing and issuing the item.

Figure 25 - Excel ItemInventories Template with Data

Entering Data on the ItemInventories Template

Changing item records:

Adding item records:

Worksheet Uploads

New Item Records:

You can upload all four record type worksheets at the same time. Behind the scenes, the system processes the worksheets in the following sequence, so that information for items is added or changed in the correct order:

1 - Items
2 - ItemVendors
3 - ItemVendorUOMs
4 - ItemInventories.

Note: When you import item records, Supply Chain updates the audit data for any changed field that is normally audited.

To upload item record worksheets to Supply Chain:

  1. Save the worksheets containing the item data by selecting File > Save As.
    Excel prompts you for a Save in folder and File name for the worksheet.
  2. Enter the folder and file name.
  3. Click Save.
  4. Click Upload Items (Figure 10, ).

The Upload Items panel appears (Figure 26)

Figure 26 - Excel Upload Items Panel

  1. On the Upload Items panel, select the worksheets that you wish to upload.

Viewing Item Import Jobs:

When you upload a worksheet from Excel, Supply Chain performs these steps:

  1. Validates the file
  2. Reads the file
  3. Processes the file to import item records based on the information in the uploaded worksheet(s).
    If Supply Chain cannot complete any step because of errors, it logs the errors. You can view the errors.

To view item import jobs

  1. From the Materials Management main Contents, select Imports / Exports & Financials > Item Imports . The Item Imports list appears.
  2. Locate the job corresponding to the worksheets that you uploaded.
  1. Review the entry for your job. The following fields provide status data:

To get detailed information about your uploaded worksheets

You can view import details for any of your uploaded worksheets from the Item Imports list.

  1. From the Materials Management main Contents, select Imports / Exports & Financials > Item Imports . The Item Imports list appears.
  2. On the Imports list, locate the job corresponding to the worksheets that you uploaded.
  3. In the row next to the Import ID, click Menu. A list of menu options appears (Figure 27).

Figure 27 - Selecting Item Import Details to View

  1. Select the menu option that corresponds to the type of item record that you uploaded.
    For example, in Figure 27, the item vendor UOM record details (from the ItemVendorUOMs worksheet) is being selected.

    Supply Chain displays the record(s) (Figure 28).

Figure 28 - Viewing Details for an Item Record Import

Downloading Item Records

The Excel add-in provides buttons that let you download item records from Supply Chain. The download process requires setting up a Selection Profile. A Selection Profile filters item records, identifying those to download. You tell Supply Chain which Selection Profile to use when you request a download.

You can create and save a Selection Profile, and use it over and over. On any download request, you can also create a new Selection Profile, or edit and save a pre-defined Selection Profile. When you click the Download button, Excel prompts you for a Selection Profile.

Hint: Downloading item records to Excel is a good way to make mass changes in the records. You can make changes as needed, and then upload the records back to Supply Chain. However, you can also download item records purely for analysis in Excel, and never upload them back to Supply Chain. Excel provides useful tools for analytical purposes.

Limits

Multiple worksheets: When you download multiple worksheets, the download limit on any single worksheet is 5,000 unique item numbers, which may, in fact, take up much more than 5,000 Excel rows. In other words, suppose you download the Items worksheet, the ItemVendors worksheet, and ItemVendorUOM worksheet. You will get records for no more than 5,000 actual items (if you have that many). However, since your ItemVendorUOM worksheet could easily have two or three Units of Measure (and prices) per item, for the limit of 5,000 unique items, you may have 10,000 or 15,000 rows in Excel.

One worksheet: When you download only one worksheet, the download limit is Excel's row limit: a maximum of 65, 636 rows.

To download item records:

  1. From an Excel worksheet (with the item import add-in activated) click Download Items (Figure 10, ).
    The Item Selection Download panel appears (Figure 29).

Figure 29 - Item Selection Panel for Downloading Item Records to Excel

  1. Specify the Selection Profile, the Include Sheets, and (if you wish), the worksheet Include Columns for the download.

    Selection Profile: On the left side of the Item Selection Download panel is the list of Selection Profiles.
    If no Selection Profiles have been created, the list box is blank, and you will need to create a Selection Profile. See Creating a Download Selection Profile.
  1. Click Download (on the upper right of the Item Selection Download panel).
    The system downloads the item record worksheets that you specified, according to the criteria in the Selection Profile.

Creating / Editing a Download Selection Profile

Selection Profiles contain criteria that Supply Chain uses to identify items for download to an Excel worksheet. Using the maintenance feature on the Item Selection Download panel, you can create new Selection Profiles, or edit and save existing ones.

To create or edit a Selection Profile:

  1. On the Item Selection Download panel (Figure 30), click the Maintain button.

Figure 30 - Item Selection Download Panel Showing Maintain Button

A panel appears with a list of existing Selection Profiles (Figure 31), or No Data if none have been created.

Figure 31 - List of Item Selection Profiles

  1. Open a Selection Profile panel for editing, or create a new profile.
  1. Enter criteria on the tabbed panels to identify the item records that you wish to download. See the detailed paragraphs about the panels below.

Selection Profile Fields - General

Data Entry Fields: The four Selection Profile panels contain blank data entry fields. You key data in to the fields, or choose a value from a list. Data entry fields are of two types: text and numeric.

In most cases, it's clear which sort of field is which. For example, the Item No, Description, Search words, Alias, and AssetLoc fields are text; Purchase Unit Price is numeric.

Check Boxes: For check box fields, no selection means "all." For example, the Item Type field has the options Stock, Non-stock. If neither value is selected, Supply Chain returns item records for both stock and non-stock items.

Selection Buttons: For selection buttons that contain the choices Yes, No, Both, the default value is Both.

Comparison Fields: Some fields, such as Create Date contain spaces for a comparison operator and a value. For the comparison operator, click the down arrow and select an operator from the list. The choices are:

EQ - "equal to"
NE - "not equal to"
GT - "greater than"
LT - "less than"
GE - "greater than or equal to"
LE - "less than or equal to"

UNSPSC code field:

The UNSPSC Code selection field supports partial and full UNSPSC code selections. The UNSPSC code is a character field on the Selection panel.

Examples:
Enter:    The system selects:
42    Any UNSPSC Code beginning with 42, such as 42000000, 42120000, 42121500, 42121501, etc.
42000000    Returns only 42000000
4216  Any UNSPSC Code beginning with 4216, such as 42161500, 42161501, 42161502, etc.
42121506   Returns only 42121506

 Notes on entering information in the panels' fields follow each figure in the next section.

Selection Profile Panels

This section reviews each selection profile panel, with tips about entering data in fields. Click Help for detailed information about each field.

Catalog Selections Panel

Figure 32 is the Catalog selections panel.

Figure 32 - Item Selections Panel for Downloading Item Catalog Records

Tips for the Catalog selections panel:
Inventory Selections Panel

Figure 33 is the Inventory selections panel.

Figure 33 - Item Selections Panel for Downloading Item Inventory Records

Tips for the Inventory selections panel:
Item Vendor Selections Panel

Figure 34 is the Item Vendor selections panel.

Figure 34 - Item Selections Panel for Downloading Item Vendor Records

Tips for the Item Vendor selections panel:
Item Vendor UOM Selections Panel

Figure 35 is the Item Vendor UOM selections panel.

Figure 35 - Item Selections Panel for Downloading Item Vendor UOM Records

Tips for the Item Vendor UOM Selections Panel

Setting Items as Multi-Sourced / Resetting Items to Single Source

To set items as multi-sourced using mass maintenance:

  1. Create the items as usual using the item mass maintenance spreadsheets.
  2. Download the item inventory record for the items that you wish to multi-source. This step captures the ItemVendorID that Supply Chain assigned the items when they were created in the application. See Download Item Records for details.
  3. On the ItemInventories spreadsheet, set the column VendorOverride to 1 for each item that you wish to multi-source.
  4. Make sure that the Actioncode column for the items that you wish to multi-source contains C for "change."
  5. Leave the Actioncode column blank for items that you are not changing so that their item records will be ignored when the spreadsheet is uploaded.
  6. Upload the ItemInventories spreadsheet. The process is described in Worksheet Uploads.

    If you wish, you can check the item records in Supply Chain to verify that they have been set to multi-source.

To reset multi-sourced items to single source:

  1. Follow steps 2, 3, 4, and 5 for multi-sourcing as explained previously; except: on the ItemInventories spreadsheet, set the column VendorOverride to zero (0).
  2. Upload the ItemInventories spreadsheet. See Worksheet Uploads for details.

You can set an item inventory item from multi-source back to single- source, even when inventory transactions exist for the item. When you set a multi-source item to single-source, the Multi-Source flag will be set to Single-Source for the following:

All of the multi-source data that did not exist in the single-source structure will be copied to the single-source structure so that the following transactions may process:

Multi-sourcing is explained in the online documentation topic: Multi-Sourcing an Item.

Columns on the Item Record Worksheets

An alphabetical list of columns on the four Item Record worksheet templates is in Table 1.

If you are using item record worksheets, you can download item records containing data in the following columns: VendorUnitPrice, FutureContractNo, FutureStartDate, ContractPrice4, ContractNo, and ContractUOM. However, for a contract item, if you make changes to these columns, on upload, Supply Chain ignores the changes.

Key to Table 1:

Column Name - The worksheet column heading.
Description - The function of the column/field in Supply Chain
Worksheet(s) - Excel worksheet templates that contain the column.

Value Required for New Item?

Y - In creating a new item, this column must contain a value
N - In creating a new item, the column can be left empty, and the system will use the default value.

Values - The type and length of values for the column.

Table 1 - Columns on Item Record Worksheets

A   B   C    D    E    F   G   H    I    J    K    L    M    N    O    P    Q    R    S    T    U    V    W    X    Y    Z

Column Name

Description

Worksheet(s)

Value Required?

Values

ABCClass A classification for an item based on a percentage of the annual expense for all items. Typical percentages are:
A = 20% (the 20% of the items that carry 80% of the expense)
B = 30%
C = 100% - Class B% - Class A%
Sites can define ABC classes based on annual item expenses. Any item can then be assigned the appropriate letter class according the percentage of the expense that it carries.
ItemInventories Y Drop down selection. Select the cell; a down arrow appears. Click the arrow for a selection box and select the appropriate value.
1 = A
2 = B
3 = C
4 = No class.
Action Code Tells Supply Chain what to do with each uploaded item record. All Y A - New item record: add the item record to Supply Chain
D - Current item record: delete the item record from Supply Chain
C - Current item record: update the item record with information from the worksheet.

blank (no code entered) - Supply Chain skips the row when you upload the worksheet. This option is useful if you have many records on a worksheet, but only need to update a few.

AllowSubstituteYN Yes (1) - Supply Chain looks up an out-of-stock item on a substitute table to issue a similar item in its place. Items
ItemInventories
N
N
Boolean. 1 (yes) or 0 (no)
Default is 0.
AlternateAisleLoc If the item also has a secondary location, the aisle identifier. ItemInventories N Text. Maximum length = 5 characters. Default is "blank."
AlternateBinLoc If the item also has a secondary location, the bin identifier. ItemInventories N Text. Maximum length = 5 characters. Default is "blank."
ApprovedSubYN Flags the item as an approved substitute for a catalog item with the same item number. Vendors for EDI 850 POs can automatically substitute the item, which will appear on the EDI 855. Boolean. 1 (yes) or 0 (no)
Default is 0.
AssetCode The asset code for the item, if assigned. Reports can be run against various Asset Codes. ItemInventories N Text. Maximum length = 15 characters. Default is "blank."
AssetLoc The asset location responsible for the item. ItemVendors
ItemInventories
ItemVendorUOMs
Y
Y
Y
Text. Maximum length = 15 characters.

ItemVendors
ItemVendorUOMs
If an item is single sourced, for the primary asset location enter the primary asset location.
If an item is multi-sourced, enter the asset location code for the multi-sourced inventory organization.

ItemInventories
Enter the asset location code for the inventory that you are assigning the item to. See the previous discussion.
AutoAdjMinStockYN Yes (1) - Supply Chain will recalculate the Minimum Stock Level as follows: If Days Between Delivery is specified, then:
Minimum Stock Level = Days Between Delivery * Average Daily Usage
otherwise:
 Minimum Stock Level = Pipeline Days * Average Daily Usage
If No (0), Supply Chain allows an authorized user to determine the Minimum Stock Quantity. Use Auto Adjust Minimum Stock level for stock items only.  
ItemInventories N Boolean. 1 (yes) or 0 (no)
Default is 0.
BuyFromLoc The primary vendor location that the item is usually purchased from. ItemVendors
ItemVendorUOMs
Y
Y
Text. Maximum length = 15 characters.
ChrgDeptOvrd The department charged for the item. This department overrides the
usual charge-to department.
ItemInventories N Text. Maximum length = 15 characters. Default is "blank."
CommodityCode A code that may be required and specified by local regulations to track certain types of commodities. It is a high-level classification of items into different groups for reporting. Examples: IV Solutions, Radioactive Materials. Commodity codes are user defined. Items N Text. Maximum length = 10 characters. Default is "blank."
ConsignmentYN Yes (1) - Indicates that the item is a consignment item. See Processing Consignment Items. Items
ItemInventories
N
N

Boolean. 1 (yes) or 0 (no)
Default is 0.

ContractNo The number of any current contract that governs the item.
With Sourcing and Contract Management, changes made to this column are ignored.
ItemVendors N Text. Maximum length = 15 characters. Default is "blank."
ContractPrice4 The price of the item according to the current contract.
With Sourcing and Contract Management, changes made to this column are ignored.
ItemVendors N Numeric: decimal value greater than zero with a maximum of four decimal places.
Default is "blank."
ContractUOM

The unit of measure for the Contract Price.

With Sourcing and Contract Management, changes made to this column are ignored.

ItemVendors N, unless Contract Price is
entered.
Text. Maximum length = 2 characters.
Must be entered if Contract Price4 is entered.
Default is "blank."
DatedMaterialYN Yes (1) - The item has a "use-by" date. Items N Boolean. 1 (yes) or 0 (no)
Default is 0.
DaysBetweenDelivery Specifies the number of days between deliveries by the vendor for this item. If a value is entered in this field, and Auto Adjust Minimum Stock Quantity is Yes (1), Days Between Delivery will be used in the calculation of Minimum Stock Quantity. ItemInventories N Numeric: Positive whole number between 0 and 9999999. Default is 0.
DftIssueUOMYN Yes (1) - The unit of measure is the default unit of measure in which the item is issued. ItemVendorUOMs Y Boolean. 1 (yes) or 0 (no)
DftPurchaseUOMYN Yes (1) - The unit of measure is the default unit of measure in which the item is purchased. ItemVendorUOMs Y Boolean. 1 (yes) or 0 (no)
DropPatReconciliationYN Yes (1) - the item should be dropped from the lost charge reconciliation report for every department.
Patient charges are not reconciled with issues of the item.
ItemInventories N Boolean. 1 (yes) or 0 (no)
Default is 0.
Export Control Code 1 thru 10 Any export control codes from the item inventory record. ItemInventories N  
FutureContractNo

The number of any future contract that governs the item.

With Sourcing and Contract Management, if the item is a contract item, on upload, Supply Chain ignores changes made to this column.

ItemVendors N Text. Maximum length = 10 characters. Default is "blank."
FutureStartDate The start date for any future contract that governs the item.

With Sourcing and Contract Management, if the item is a contract item, on upload, Supply Chain ignores changes made to this column.
ItemVendors N Date: m/d/yyyy
Default is "blank."
HazdsMaterialYN Yes (1) - The item is hazardous material. The item is reportable on a standard hazardous material sheet. Items N Boolean. 1 (yes) or 0 (no)
Default is 0.
InventoryGroup Site-defined high-level classification of goods within an asset location. Items
ItemInventories
N
N
Text. Maximum length = 10 characters. Default is "blank."
Implant Designates an implant item. Items
ItemInventories
N
N
 
IssueUOMYN Yes (1) - The item may be purchased in the unit of measure. ItemVendorUOMs Y Boolean. 1 (yes) or 0 (no)
ItemAlias An alternate description or name for the item.
- The Items worksheet contains the alias from the item catalog record.
- The ItemInventories worksheet contains the alias from the item inventory record.

The item catalog and item inventory aliases may be different, or one may not be specified.
Items
ItemInventories
N
N
Text. Maximum length = 100 characters.
Default is "blank."
ItemClass The item class. Item Classes are defined by a hospital in the Item Class Table. Items N Text. Maximum length = 10 characters. Default is "blank."
ItemDesc A description of the item. Items Y Text. Maximum length = 100 characters.
ItemNo The item number. This is your site's identifier for the item. All Y

Text. Maximum length = 15 characters.

Note: You cannot mix system-assigned and user-specified item numbers in the same template.
All the item numbers must either follow the pattern *ItemNo_1, *ItemNo_2, *ItemNo_3,
or must all be identifiers that you create; e.g., syringe20, 1356A.

ItemPicURL The Internet location (URL) of a picture of the item. If you enter the URL, Supply Chain builds a link from the item number in the item catalog and Item Inventory lists to the web page that contains the item picture. Users can click the link to view the picture. Items N >Text. Maximum length = 250 characters. Default is "blank."
ItemStatus Active - The item is currently issued, ordered, and used at your site.

Inactive - The item is not currently used. An inactive item can be changed to active.

Incomplete - Vendor or other information about the item is missing.
Items
ItemInventories
Y
Y

Drop down selection. Select the cell; a down arrow appears. Click the arrow for a selection box and select the appropriate value.

1 = Active
2 = Inactive
3 = Incomplete

ItemSubClass A sub classifier for an item, used for a finer distinction than Item Class. Items N Text. Maximum length = 10 characters. Default is "blank."
ItemTaxableYN Yes (1) - the item is taxable. ItemInventories N

Boolean. 1 (yes) or 0 (no)
Default is 0.

ItemType The item type: stock or non-stock.
Stock and non-stock items are listed in the item catalog. Stock items are inventoried. Non-stock items are not inventoried.

Items
ItemInventories
Y
Y
Drop down selection. Select the cell; a down arrow appears. Click the arrow for a selection box and select the appropriate value.1 = Stock; 2 = Non-stock

ItemVendorID An internal Supply Chain designator for the vendor. ItemVendors
ItemVendorUOMs
Y
Y

Adding a new item - enter zero in the field.
Changing an existing item - leave the value untouched.

LatexFreeYN Yes (1) - The item does not contain latex. Items N Boolean. 1 (yes) or 0 (no)
Default is 0.
LotTrackingYN Yes (1) - The item is lot tracked (serial number, lot number, or expiration date). ItemInventories N
Boolean. 1 (yes) or 0 (no)
Default = 0
LowestUOMYN Yes (1) - The unit of measure is the lowest unit of measure for the item.
This field is required for the item to be active.
ItemVendorUOMs Y Boolean. 1 (yes) or 0 (no)
MaximumQty The maximum quantity of an item that can be ordered at any one time. Backorders are not included in the MSQ. ItemInventories N Numeric: Positive whole number between 0 and 9999999. Default is 0.
MfrID The Supply Chain identifier for the manufacturer. Item Vendors
ItemVendorUOMs
Y
Y
Text. Maximum length = 15 characters.
MfrItemNo The manufacturer's identifier (item number) for the item. ItemVendors
ItemVendorUOMs
Y
Y
Text. Maximum length = 15 characters.
MinStockQty A calculated value equal to:
Days Between Delivery
* Average Daily Usage
if Days Between Delivery > 0. Otherwise, it equals Pipeline Days * Average Daily Usage. See the Suggested Order List.
ItemInventories N
Numeric: Positive whole number between 0 and 9999999.
Default is 0.
MSDSURL MSDS means Materials Safety Data Sheet. The MSDS URL is the Internet location of the sheet. If Hazardous Materials is checked, under the Defaults tab, then enter information in the MSDS URL field. Items N

Text. Maximum length = 250 characters. Default is "blank."

OptimumPct A site-determined optimum stock level percentage used to modify reorder quantity as additional safety stock. For example, if the Optimum Percentage Increase is set at 75%, then when the item comes up for reorder of 100, the optimum percentage increase would apply and make the order 175. ItemInventories N Numeric: Decimal value between 0 and 9999.999. Default is 0.
OrderFromVendorYN Yes (1) - the item is ordered from a vendor.
No (0) - the item is replenished via system transfer from the organization and asset location specified in ReplenishFromOrg and ReplenishFromAssetLoc. 
ItemInventories N

Boolean. 1 (yes) or 0 (no)
Default is 1.

Org The organization in the hospital that the asset location for the item belongs to. ItemVendors
ItemInventories
ItemVendorUOMs
Y
Y
Y

Text. Maximum length = 15 characters.

ItemVendors
ItemVendorUOMs
If an item is single sourced, enter the organization code for the primary organization.
If an item is multi-sourced, enter the organization code for the multi-sourced inventory organization.

ItemInventories
Enter the organization code for the inventory that you are assigning the item to. See the previous discussion.

PackagingInfo Notes or instructions about packaging for the item. ItemVendors N Text. Maximum length = 100 characters. Default is "blank."
PassThroughCode A code used for insurance billing purposes, such as a Medicare code.
If your organization is set up for automatic copying of default values to item inventory records:
- For additions or changes, the value in this field on the Items worksheet is copied to any new or existing item inventory records. See the discussion in the Organization topic: Assign Patient Charge Number (CDM) From Catalog.
- If you change this value on the Item Inventories worksheet, there is no effect on the item inventory record, there is no effect on the item inventory record because the item catalog default value takes precedence.
Items
ItemInventories
N
N
Text. Maximum length = 40 characters. Default is "blank."
PatBillableExpense An Expense Code for expenses that are directly billed to patients. This field is required if PatientChargeableYN is Yes (1).

The Expense Code corresponds to the materials expense segment of an account code in the Chart of Accounts. When the item is requisitioned or purchased, the cost of the item is entered in the account.

If your organization is set up for automatic copying of default values to item inventory records:
- For additions or changes, the value in this field on the Items worksheet is copied to any new or existing item inventory records. See the discussion in the Organization topic: Assign Expense Codes From Catalog.
- If you change this value on the Item Inventories worksheet, there is no effect on the item inventory record , there is no effect on the item inventory record because the item catalog default value takes precedence.

Items
ItemInventories
Y
Y

If Patient ChargeableYN
is 1.
Text. Maximum length = 15 characters.
Default is "blank." A value must be supplied in either PatBillableExpense or in PatNonBillableExpense that corresponds to the value in PatientChargeableYN.
PatChargeItemNo Also called the CDM.
The hospital-assigned Patient Charge Number passes to the patient billing system for patient bills and/or insurance compliance.
Patient chargeable items issued to a department print on the patient reconciliation report.
If your organization is set up for automatic copying of default values to item inventory records:
- For additions or changes, the value in this field on the Items worksheet is copied to any new or existing item inventory records. See the discussion in the Organization topic: Assign Patient Charge Number (CDM) From Catalog.
- If you change this value on the Item Inventories worksheet, there is no effect on the item inventory record because the item catalog default value takes precedence.
Items
ItemInventories
N
N
Text. Maximum length = 15 characters. Default is "blank."
PatientChargeableYN Yes (1) - The item is charged to patients. Items
ItemInventories
N
N
Boolean. 1 (yes) or 0 (no)
Default is 0.
PatNonBillableExpense An Expense Code for expenses that are not directly billed to patients.
This field is required if PatientChargeableYN is No (0). See PatBillableExpense and PatientChargeableYN.

If your organization is set up for automatic copying of default values to item inventory records:
- For additions or changes, the value in this field on the Items worksheet is copied to any new or existing item inventory records. See the discussion in the Organization topic: Assign Expense Codes From Catalog.
- If you change this value on the Item Inventories worksheet, there is no effect on the item inventory record, because the item catalog default value takes precedence.
Items
ItemInventories
Y
Except, can be left blank for a changed item.
Text. Maximum length = 15 characters. Default is "blank." A value must be supplied in either PatBillableExpense or in PatNonBillableExpense that corresponds to the value in PatientChargeableYN.
PrefOrderFactor The minimum divisor for an ordered quantity of an item on a purchase order.

If the PrefOrderFactor is specified, the PrefOrderUOM must be specified.
When values exist in these fields, the ordered quantity must be evenly divisible by the preferred order factor. If not evenly divisible, the quantity is rounded upwards to the next evenly divisible quantity.
ItemInventories N Integer
PrefOrderUOM The unit of measure for the PrefOrderFactor. ItemInventories N Text. Must be a valid unit of measure for the site.
PrimaryAisleLoc The storeroom aisle where the item is kept as the item's main location ItemInventories N Text. Maximum length = 5 characters. Default is "blank."
PrimaryBinLoc The storeroom bin where the item is kept as the item's main location. ItemInventories N Text. Maximum length = 5 characters. Default is "blank."
PrimarySuppYN If the item is supplied by multiple vendors, this column indicates that the vendor listed is the main supplier of the item. ItemVendors N
See Caution

Boolean. 1 (yes) or 0 (no)
Default is 0.
Caution: If you leave this column blank or delete the column (and thereby, accept the default of zero), you will not have defined a primary vendor, and the Item Vendor record will be Incomplete.

PrtPatientLabelsYN Yes (1) - Patient labels are printable for the item. ItemInventories N

Boolean. 1 (yes) or 0 (no)
Default is 0.

PurchaseUOMYN Yes (1) - The item may be purchased in the unit of measure. ItemVendorUOMs Y Boolean. 1 (yes) or 0 (no)
RecalcAllUOMPricesYN This field recalculates all UOM prices based on a new price for the lowest UOM.
ItemVendors N Boolean. 1 (yes) or 0 (no)
Default is 1.
ReplenishFromAssetLoc The asset location that orders or replenishes the item if OrderFrom VendorYN is zero.  Supply Chain defaults to the ReplenishFromOrg and ReplenishFromAssetLoc specified in Current Settings ItemInventories N Text. Maximum length = 15 characters. Default: Supply Chain uses the Asset Location in Current Settings.
ReplenishFromOrg The organization that replenishes or orders the item if OrderFrom VendorYN is zero.  ItemInventories N Text. Maximum length = 15 characters. Default: Supply Chain uses the Asset Location in Current Settings.
ReusableItemFee The charge for processing a reusable
item (e.g., sterilization) so that it can be issued.
ItemInventories N Numeric: Decimal value > 0, with a maximum of four decimal places. Default is 0.0.
ReusableYN Yes (1) - The item is reusable. Departments issued reusables are not charged for the purchase cost. Items
ItemInventories
N
N
Boolean. 1 (yes) or 0 (no)
Default is 0.
SafetyStockQty The minimum acceptable stock quantity for an item. The Safety Stock Quantity is site-defined. ItemInventories N Numeric: Positive whole number between 0 and 9999999. Default is 0.
SearchWords Words that users may associate with the item. For example, gauze pads might have the search words "gauze," "dressings," "pad," etc. Items N

Text. Maximum length = 250 characters. Default is "blank."

Supply Type A supply type defined in the site's supply type table. Items
ItemInventories
N
N
 
TrackExpDtYN Yes (1) -Expiration Date
tracking is in effect.
ItemInventories N Boolean. 1 (yes) or 0 (no)
Default = 1.
TrackLotNoYN Yes (1) - Lot Number tracking is in effect. ItemInventories N Boolean. 1 (yes) or 0 (no)
Default = 1.
TrackSerialNoYN Yes (1) - Serial Number tracking is in effect. ItemInventories N Boolean. 1 (yes) or 0 (no)
Default = 0.
UnitOfMeasure A unit of measure for the item. ItemVendorUOMs Y Text. Maximum length = 2 characters.
UNSPSCCode The UNSPSC®, United Nations Standard Products and Services Code®, associated with the item. The code has four levels of classification: segment, family, class, and commodity. Two digits are available for each level. See Using UNSPSC Codes. Items N Numeric: Positive whole number between 0 and 9999999. Default is "blank."
UOMConvFactor The multiple of the smallest Unit of Measure contained in each larger Unit of Measure. ItemVendorUOMs Y Numeric: Positive whole number between 0 and 9999999.
VendorItemNo The vendor's identifier (item number) for the item. ItemVendors
ItemVendorUOMs
Y
Y
Text. Maximum length = 40 characters.
VendorNo The vendor number. This number identifies the vendor to Supply Chain. ItemVendors ItemVendorUOMs Y
Y
Text. Maximum length = 15 characters.
VendorOverrideYN Yes (1) on the ItemInventories worksheet - The item is multi-sourced and has a different set of vendors, vendor units of measure, etc. for one or more asset locations.
On the Items worksheet, this column is ignored by Supply Chain.
ItemInventories
Items
N - ignored
N
Boolean. 1 (yes) or 0 (no)
Default is 0.
VendorUnitPrice The price of the item per unit of measure, including any vendor markup. (Zero is allowed.)
With Sourcing and Contract Management, changes made to this column are ignored.
ItemVendorUOMs Y Numeric: a decimal value greater than zero with a maximum of four decimal places.