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.
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:
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.
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.
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.)
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.
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.
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.
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).
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.
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.
- 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.
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.
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).
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.
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:
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.
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.
|
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.
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.
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.
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.
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 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.)
Important: You cannot use both system-assigned item numbers and user-specified item numbers in the same spreadsheet. Your item numbers must all follow the *ItemNo_1, *ItemNo_2, *ItemNo_3 pattern, or must all be identifiers that you create; e.g., syringe20, 1356A. You cannot mix the two types.
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.
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.
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 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 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.
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.
Updates and deletions of item records: Worksheet templates for items being changed or deleted must contain the columns with the blue headers. If you are working on an ItemVendorUOM worksheet that you downloaded, leave the ItemVendorID column in place. If you are entering item data on a blank worksheet, set the value in the ItemVendorID column to zero.
Note: When you import item records, Supply Chain updates the audit data for any changed field that is normally audited.
The Upload Items panel appears (Figure 26)
When you upload a worksheet from Excel, Supply Chain performs these steps:
You can view import details for any of your uploaded worksheets from the Item Imports list.
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.
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.
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.
A panel appears with a list of existing Selection Profiles (Figure 31), or No Data if none have been created.
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.
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.
This section reviews each selection profile panel, with tips about entering data in fields. Click Help for detailed information about each field.
Figure 32 is the Catalog selections panel.
Figure 33 is the Inventory selections panel.
Figure 34 is the Item Vendor selections panel.
Figure 35 is the Item Vendor UOM selections panel.
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.
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.
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.
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. | ItemVendor | N | 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 |
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) |
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. |
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 |
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) |
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. |
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) |
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. ItemInventories |
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: |
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) |
PrtPatientLabelsYN | Yes (1) - Patient labels are printable for the item. | ItemInventories | N | Boolean. 1 (yes) or 0 (no) |
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. |
Copyright © 2023 by Premier Inc. All rights reserved.