Feature - Fields Added to User-Defined CSV to Support the EDI 846
Release date: 9/10/2019
Version 8.5 & up
The primary purpose of this enhancement is to support the EDI 846 (Inventory Inquiry/Advice). E-retailers, like Amazon, Target, Wayfair, Home Depot, and Lowes, often request an updated inventory feed several times a day so that their website can provide your up-to-date inventory quantities.
Define the User-Defined CSV Layout to Support the EDI 846
Go to I/M, Reports, Reorder Advice/User Def CSV, Maintenance, and User Defined CSV Layout. Define a CSV export with the following columns:
A: [I] Item Number
B: [D1] Item Description 1
C: [D2] Item Description 2
D: [N1] UPC
E: [QH] Qty on Hand
F: [QA] Qty Allocated
G: [1Q] Qty on Order
H: [1D] Date Qty Available
I: [AY] Activity Code
J: [PC] Product Category
K: [IC] Inventory Class
L: [UC] Item User Defined Code
M: [UM] Item Stocking Unit of Measure
N: [SU] Item Selling Unit of Measure
O: [SR] Selling to Stocking Ratio
P: [PM] Purchase/Mfg/Kit/Feature (optional)
Q: [??] Alternative Qty Available (optional - used with column P)
R: [CI] Customer Item Number (optional - if required by trading partner)
S: [L] Location (optional - if you have multiple physical locations to report inventory)
See sample screen below:
You can use Elliott's Deferred Processing to export this layout to a CSV file several times a day. The exported CSV file can then be picked up by the Elliott SPS Service, interfaced to SPS Commerce and transmitted to your trading partner as an EDI 846 (Inventory Inquiry/Advice) document.
Several fields are required by the 846 that this CSV export did not support in the past. This feature supports the new columns needed for the EDI 846 and two existing fields have been improved to add more options to the ATP records included for calculation.
Customer Item Number
The last column "CI" (Customer Item Number) is optional. Your trading partner may or may not require you to provide their item number (Buyer's Item Number). If they do require it, you will need to enter their customer item number in Elliott COP, Maintenance, and Customer Item. When you add the "CI" column to the CSV layout, you will need to specify which customer. In the above example we use customer number OSH951. That means this layout can only produce an Inventory Advice CSV for customer OSH951. if you are not required to specify the customer item number, then you don't need to add the "CI" column and thus the CSV is generic and can be used for various trading partners.
1st Pending Receiving Qty & Date
Columns 1Q (1st Pending Receiving Qty) and 1D (1st Pending Receiving Date) are already supported. These two columns can provide a date and quantity of items that will be received for items that are out of stock. Your trading partner may require these fields.
If you are an importer, it is a common practice for you to enter the scheduled receiving transactions into Elliott warehouse receiving or a regular receiving module as soon as the items are on the vessel headed for you. These receiving transactions will normally be organized by a vessel ID (batch) with a scheduled receiving date. You wouldn't post the items on the vessel until the container actually arrives at your warehouse. These scheduled receiving transactions are reflected in the ATP and their dates are highly reliable because they are already at sea. In our previous implementation, the first RW (Scheduled Warehouse Receiving) or RR (Schedule Regular Receiving) ATP record was used to determine the First Pending Receiving Quantity and Date and populated into columns 1Q and 1D.
Since you may not use the scheduled receiving function in Elliott, we get the 1Q and 1D information from P* (Purchase) ATP records. If you are a manufacturer, you may want to look at the scheduled production, which are the B* (BOMP) ATP records.
For the above reasons, we introduced a new flag in Global Setup, Dist, and IM Global Control screen 4.
9. ATP Type for IM User Def CSV 1st Pending Recv?
The default value of this field is "R," which indicates that the value of the results will remain the same with only RR and RW ATP records included. If you don't use Elliott's scheduled receiving feature, then you should enter "P" and all P* (Purchase) and R* (Scheduled Receiving) records are included. If you are a manufacturer and wish to include your BOMP work order's scheduled production, you should enter "A," and all B* (BOMP), P*, and R* records are included. See sample screen below:
This version also adds support for two new fields in the user-defined CSV layout:
Selling UOM and Ratio
Type SU - Selling Unit of Measure
Type SR - Selling to Stocking Ratio
If your selling unit of measure is different from your stocking, chances are your trading partners are more interested in the selling UOM price and quantity instead of the stocking.
Alternative Qty Avail
Columns P and Q are optional. You can either skip the column (if there are no more columns after Q) or use a constant value of blank. Column Q is defined as Alternative Qty Available. You can get this quantity from anywhere in the item master or attribute. It can also be a constant or formula value. In the above example, we get this value from the QTYAVAILBYCOMP Attribute. In the SPS EDI interface, we can optionally use this value depending on the value in column P (Purchase/Mfg/Kit/Feature).
Maximum Number of Items in 846 - 10,000
Keep in mind that the maximum number of items in an EDI 846 (Inventory Advice) document is 10,000. You should make sure your selection criteria for the user defined CSV file limits the number of records to less than 10,000. Typically, you can use the following parameters in User Defined CSV Export to narrow down the number of items:
- Starting/Ending Prod Cat
- Activity Code - Only export active items
- User Defined Code
- Item Has Attribute - Add a certain attribute to those items that will be on 846.
- Show Item Qty OH > 0 - Only export items with Qty OH > 0. However, the risk with doing this is some trading partners may retain the quantity from the previous 846 or drop the item from the website if it is not provided in the current interface.
- Exclude Non-Stocked Items? - Only export stocked items.
- Material Cost Type - Export only finished goods (if your material cost type setup allows you to do so).
- Include Web Items Only? - You could use web item flag (in item and product category) to indicate whether an item should be on 846 interface. See the following KB article for details: https://support.elliott.com/knowledgebase/articles/1965536-feature-allow-web-item-flag-space-to-use-produ
- E846ExcludedInventoryClasses: Define a list of inventory classes to be excluded from 846 separated by a comma. For example, you could exclude inventory class “F”.
- E846ExcludedUserDefinedCode: Define a list of User Defined Code to be excluded from 846 separated by a comma.
- E846ExcludedProductCategory: Define a list of Product Category to be excluded from 846 separated by a comma. See sample below: “ALW,BBB,BOX,CGP,CHF,COP,COS,DIC,DIS,ECB,HDC,HOM,JCP,JOB,KBZ,KOH,LPI,LWS,MAC,MEN,MTM,OEM,,OSH,OST,PKG,PLU,PRI,PRO.PVC,SBX,SPC,SPT,SRV,STX,TGT”
- E846UseAltQtyAvailByPurchMfgFlag: Based on the value in column "Purchase/Mfg/Kit/Feature", you can define a condition when the Alternative Qty Avail column should be used. Here is a sample value: "M,MM" means when the "Purchase/Mfg/Kit/Feature" value is = "M" or "MM", then use the quantity in the Alternate Qty Avail column.
If you still have over 10,000 items after applying the above parameters, discuss with SPS in the testing/implementation stage to have them break the 846 into smaller files so each 846 does not exceed 10,000 records.
Exported File Naming Convention
Remember that the Elliott SPS Services (ESS) will only pickup files with an extension of EXP. Even though the export file is in a CSV format, the name must end with EXP in order to be picked up by SPS. The typical file name and path would be like:
....\TestOut\IB{NAME}.EXP during the testing period
or
...\Export\IB{NAME}.EXP after go live
Where .... is the FTP folder that ESS can access. IB is the hard coded two characters for ESS/SPS to recognize that this is an EDI 846. {NAME} is the partner ID (decided at the time of implementation) like AMAZON, HOMEDEPOT, LOWES, etc. The following is an example of the exported file:
M:\ELLIOTT7\EDI\EXPORT\IBAMAZON.EXP
Modified programs: IMCCLTYP.WS, IMCCLSTP, IMCCLMNT, IMSLSHCP
DLM