Feature - Fields Added to User-Defined CSV to Support EDI 846
Release date: 9/10/2019
Version 8.5 & up
The primary purpose of this enhancement is to support EDI 846 (Inventory Inquiry/Advice). If you do business with e-retailers like Amazon, Target, Wayfair, Home Depot, Lowes, etc., they often request that you provide updated inventory information a few times a day so that when their prospective customers are searching on their website, they can have your up-to-date inventory quantities.
Define the User-Defined CSV Layout to Support EDI 846
To make this happen, you will go to I/M -> Reports -> Reorder Advice/User Def CSV -> Maintenance -> User Defined CSV Layout. Define a CSV export of the following columns:
A: [I] Item Number
B: [D1] Item Description 1
C: [D2] Item Description 2
D: [N1] UPC Code
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 then use Elliott's deferred processing function to export this layout to CSV files several times a day. The exported CSV file can then be picked up by Elliott SPS Service, interfaced to SPS commerce and transmitted to your trading partner as an EDI 846 (Inventory Inquiry/Advice) document.
There were several fields required on this CSV that we did not support in the past. This feature supports the new columns needed for EDI 846. In addition, two existing fields have been improved to add more options on 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, then you will need to set up their customer item number in Elliott COP -> Maintenance -> 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# "OSH951." That means this layout can only produce an Inventory Advice CSV for customer# OSH951. if you are not required to specify the customer 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
As for columns 1Q (1st Pending Receiving Qty) and 1D (1st Pending Receiving Date), they are already supported. The purpose in providing these two columns in EDI 846 is in situations where you do not have quantity on hand. Your trading partner may be interested in knowing when you are going to receive them and how many.
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 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, 1Q and 1D columns always look for the first RW (Scheduled Warehouse Receiving) or RR (Schedule Regular Receiving) ATP record to determine the First Pending Receiving Quantity and Date.
Since we need to assume that it is possible that you don't use the scheduled receiving function in Elliott, we may need to get the 1Q and 1D information from P* (Purchase) ATP records. Also, 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 now introduce a new flag in Global Setup -> Dist -> 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:
Also, we added support for two new fields in user-defined CSV layout:
Selling UOM and Ratio
Type SU - Selling Unit of Measure
Type SR - Selling to Stocking Ratio
If your selling UOM is different from your stocking UOM, chances are your trading partners are more interested in the selling UOM and quantity instead of the stocking.
Alternative Qty Avail
Column P & Q are optional. You can either skip the column (if there's no additional optional columns after) 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. It the above example, we get this value from attribute QTYAVAILBYCOMP attribute. In the SPS EDI interface, we can optionally use this value depends 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 can be in an EDI 846 (Inventory Advice) document is 10,000. You should make sure that you don't create user defined CSV file with more than 10,000 records with your selection criteria. 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, there's a risk with doing this. Some trading partners may consider your quantity stay the same from previous 846 if it is not provided in the current interface.
- Exclude Non-Stocked Items? - Only export stock 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: Base on the value in column "Purchase/Mfg/Kit/Feature", you can define the condition when the Alternative Qty Avail column should be used. Here is sample value: "M,MM" which means when the "Purchase/Mfg/Kit/Feature" value is = "M" or "MM", then use the qty in Alternate Qty Avail column.
After applying above filter parameters, if you still have over 10,000 items, you need to discuss with SPS in the testing/implementation stage to have them break down the 846 to smaller file so each 846 does not exceed 10,000 records.
Exported File Naming Convention
Keep in mind Elliott SPS Services (ESS) will only pickup file with extension EXP. So even though the file exported by this process has CSV format. But 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} during the testing period
or
...\Export\IB{NAME} after go live
Where .... is the FTP folder that ESS can access. IB is the hard coded two digits for ESS/SPS to recognize that this is 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