Current inventory status visibility
This article is designed to show you how to achieve real-time visibility into inventory levels by analyzing electronic data interchange (EDI) transactions, which is important for any organization looking to optimize their inventory. By leveraging EDI 846 (Inventory inquiry/advice) and EDI 856 (Advance ship notice), this article provides a centralized view of inventory levels across multiple locations, enabling proactive decision-making and alignment with demand.
Key features of the procedures in this article include:
- Real-time inventory tracking: Monitor inventory levels using EDI 846 and EDI 856.
- Multi-location visibility: Aggregate inventory data across warehouses and distribution centers.
- Low-stock alerts: Trigger notifications when inventory falls below predefined thresholds.
- Integration with procurement systems: Automatically generate replenishment orders when needed.
Benefits of these procedures include:
- Reduced stock-outs: Proactive alerts prevent inventory shortages.
- Improved inventory accuracy: Real-time updates eliminate manual tracking errors.
- Lower holding costs: Optimize stock levels to avoid overstocking.
- Enhanced decision-making: Centralized visibility enables faster responses to demand changes.
Data required
- Primary data sources:
- EDI 846 (Inventory inquiry/advice) for real-time inventory levels.
- EDI 856 (Advance ship notice) for inbound/outbound shipment updates.
- Supporting data sources:
- EDI 850 (Purchase order) for demand signals.
Sample EDI events
EDI 850 (Purchase order)
EG*00*NE*PO12345*20231015 LIN*1*VP*SKU1001*25 (Ordered 25 units of SKU1001)
EDI 856 (ASN)
BSN*00*SHIP789*20231016 LIN*1*VP*SKU1001*25 (Shipped 25 units of SKU1001 to Warehouse A)
EDI 846 (Inventory update)
LIN*1*VP*SKU1001*20 (Warehouse A reports 20 units in stock) DTM*007*20231017
Step-by-step analysis instructions
This section provides a quick overview of the process you'll use to ingest EDI data, process it, and analyze inventory status in the Splunk platform.
- Set up EDI integration:
- Ingest EDI 846 and EDI 856 data into the Splunk platform.
- Map fields:
edi_item_code
,edi_location
,edi_quantity
,edi_transaction_type
.
- Calculate inventory changes: Use
eval
to adjust inventory levels based on inbound/outbound transactions. - Aggregate current stock: Use
stats
to calculate net inventory by item and location. - Set low-stock thresholds: Define thresholds (for example, 100 units) using
eval
. - Generate alerts: Configure automated alerts for items flagged as low stock.
- Visualize results:
- Build a dashboard with real-time inventory levels across locations.
- Use gauges to display stock levels and tables to list low stock items.
Base search
This base search combines EDI 856 ASN records with EDI 846 inventory update events. Next, you'll extract and process product IDs along with the associated quantities to be shipped from the EDI 856 records. Similarly, you'll retrieve the actual inventory updates, including product IDs and quantities, from the EDI 846 records.
These two events will then be merged into a single record using a common transaction ID. As a result, each record will provide a unified view of the transaction, including both the ASN shipment details and the corresponding inventory update status. This aggregated event will support various analyses, including shipment volumes, inventory statuses across products and warehouses, and the broader impact on customers.
index=supply_chain_edi sourcetype="edi:x12" edi_code IN (846 856) source=edi_quantumline_customer | rex field=_raw max_match=2 "HL.*[\r\n]+(?<edi_asn_item>LIN\|.*[\r\n]+.*)" | rex field=_raw max_match=2 "N1.*[\r\n]*(?<edi_inv_update>LIN\|.*[\r\n]+.*[\r\n]+.*[\r\n]+.*)" | rex field=edi_inv_update max_match=2 "(?<edi_inv_update>LIN\|.*[\r\n]+.*)" | stats list(edi_asn_item) AS edi_asn_item, list(edi_inv_update) AS edi_inv_update, values(edi_code) AS edi_code, latest(_time) AS _time BY edi_tr_id | table _time, edi_code, edi_tr_id, edi_asn_item, edi_inv_update | eval zip_item=mvzip(edi_asn_item,edi_inv_update) | mvexpand zip_item | table _time, edi_tr_id, zip_item | eval edi_asn_item=mvindex(split(zip_item,","),0), edi_inv_update=mvindex(split(zip_item,","),1) | table _time, edi_tr_id, edi_asn_item, edi_inv_update | rex field=edi_asn_item "LIN\|[^\|]*\|BP\|(?<asn_prod_name>\w+)" | rex field=edi_asn_item "QTY|SN1\|[^\|]*\|(?<asn_cnt>\w+)" | rex field=edi_inv_update "LIN\|[^\|]*\|BP\|(?<inv_prod_name>\w+)" | rex field=edi_inv_update "(QTY|SN1)\|[^\|]*\|(?<inv_cnt>\w+)"
Search explanation
Splunk search | Explanation |
---|---|
|
Filters EDI X12 data for inventory updates (EDI 846) and shipment records (EDI 856) from the specified data source. |
|
Extracts ASN (Advance Shipment Notice) line items from raw EDI data. |
|
Extracts inventory update records from raw EDI data. |
|
Refines extracted inventory update records by ensuring correct formatting. |
|
Groups shipment and inventory update events by transaction ID, preserving the latest timestamp for each transaction. |
|
Displays key fields for analysis, including timestamps, transaction IDs, and extracted shipment/inventory data. |
|
Merges ASN shipment and inventory update data into a single field for easier processing. |
|
Expands the merged field into multiple rows to ensure each shipment-inventory pair is treated separately. |
|
Displays the expanded dataset, focusing on transaction timestamps, IDs, and merged shipment-inventory data. |
|
Splits the merged data back into separate shipment (ASN) and inventory update fields. |
|
Organizes and presents the cleaned dataset with shipment and inventory details. |
|
Extracts product names from ASN shipment records. |
| rex field=edi_asn_item "QTY|SN1\|[^\|]*\|(?<asn_cnt>\w+)" |
Extracts the quantity of items in ASN. |
|
Extracts product names from inventory update records. |
| rex field=edi_inv_update "(QTY|SN1)\|[^\|]*\|(?<inv_cnt>\w+)" |
Extracts updated inventory count from ASN shipment records. |
Extending the base search
The following searches show a few examples of further inventory analysis that you can perform:
- Current inventory volumes by products
- Comprehensive warehouse activity summary
- Comprehensive warehouse current stock and activity summary
- Stock replenishment trends
- Stock shipment trends
- Status of warehouse inventory situations
Current inventory volumes by products
Here, the dedup
command is used to retrieve the most recent inventory status event. Next, we calculate the count reported in EDI 846 based on the inventory product name, as shown below.
... Base search omitted (Refer to the base search provided above and add here). | dedup inv_prod_name | stats sum(inv_cnt) as inv_cnt by inv_prod_name | sort - inv_cnt
The results quickly highlight the products with the highest stock in the warehouse, those that are out of stock, and the total quantity of products available in the warehouse.
Comprehensive warehouse activity summary
For this analysis, we want to assess the quantity of products stored in different warehouses. Since multiple SKUs are distributed across various warehouse locations globally, our goal is to analyze inventory levels across these sites.
The following search retrieves the current inventory status from the warehouse and then appends priority data, backfilled by manufacturing. Additionally, it incorporates the quantity of orders sent to the warehouse in the form of ASN (Advanced Shipping Notices), which indicate the amount of products that need to be deducted from the available stock.
In these examples, the appendcols
command is used to first retrieve replenishment quantities from the factory and then track the corresponding orders scheduled for shipment.
| inputlookup warehouse_inventory.csv | strcat edi_warehouse_loc ":" item_id combo | appendcols [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940 | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" | table _time, edi_code, edi_tr_id, edi_warehouse_loc, edi_warehouse_item | mvexpand edi_warehouse_item | rex field=edi_warehouse_item "W\d+\|(?<item_qty>\d*)\|[^\|]*\|[^\|]*\|(?<item_id>[^\|]*)~" | stats sum(item_qty) AS item_qty_add BY edi_warehouse_loc item_id | strcat edi_warehouse_loc ":" item_id combo | table combo item_qty_add ] | appendcols [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=856 source=edi_quantumline_customer_full | dedup edi_tr_id | rex field=_raw max_match=10 "(?<edi_ship_item>HL.*[\r\n]+LIN.*[\r\n]+SN.*)" | table _time, edi_code, edi_tr_id, edi_ship_item | mvexpand edi_ship_item | rex field=edi_ship_item max_match=10 "LIN\|\w*\|\w*\|(?<item_id>\w*)~[\r\n]+\w*\|\w*\|(?<item_qty_ship>\w*)" | appendcols [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940 earliest=-200d latest=+5d | dedup edi_tr_id | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" | table _time, edi_code, edi_tr_id, edi_warehouse_loc ] | stats sum(item_qty_ship) AS item_qty_ship BY edi_warehouse_loc item_id | strcat edi_warehouse_loc ":" item_id combo | table combo item_qty_ship ] | eval item_inv_tot=item_qty+item_qty_add-item_qty_ship | eval item_inv_diff=item_inv_tot-item_qty
Search explanation
Splunk Search | Explanation |
---|---|
|
Loads the warehouse inventory dataset from a CSV lookup file. |
|
Creates a unique key ( |
|
Retrieves EDI 940 (warehouse shipment order) transactions from the supply chain EDI index. |
|
Extracts the warehouse location ( |
|
Extracts multiple warehouse item details from the EDI 940 records. |
|
Displays key fields relevant to warehouse inventory tracking. |
|
Expands multi-value fields to process each item separately. |
|
Extracts item quantities and item IDs from the warehouse shipment details. |
|
Aggregates the total quantity of received items per warehouse location and item ID. |
|
Recreates the |
|
Organizes data for received inventory, showing item quantities added. |
|
Retrieves EDI 856 (shipment notifications) transactions. |
|
Removes duplicate transaction IDs to avoid redundancy. |
|
Extracts shipment item details from the EDI records. |
|
Displays relevant fields for shipment tracking. |
|
Expands multi-value fields for processing individual items. |
|
Extracts item quantities and IDs from shipment records. |
|
Searches historical EDI 940 transactions within a broad time range (-200 to +5 days). |
|
Removes duplicate transactions to ensure data integrity. |
|
Extracts warehouse location from past transactions. |
|
Extracts warehouse item details from historical shipment orders. |
|
Displays key fields relevant to past warehouse shipments. |
|
Aggregates the total quantity of shipped items per warehouse and item ID. |
|
Recreates the combo key for data alignment. |
|
Organizes shipment inventory data, showing item quantities shipped. |
|
Computes the updated total inventory by adding received stock and subtracting shipped stock. |
|
Calculates the inventory difference, highlighting discrepancies between expected and actual inventory levels. |
The results display a color-coded table showing the inventory status across warehouses for each individual SKU. This table can be further refined for in-depth analysis, allowing us to drill down into key details such as the total replenished quantities, SKUs lacking replenishment, current demand levels, and potential future demand for each SKU.
Comprehensive warehouse current stock and activity summary
Using the base search, let's dive deeper into a more detailed analysis of the examples we discussed, this time from the product SKU perspective.
... Base search omitted (Refer to the base search provided above and add here). | stats sum(item_qty_ship) AS item_qty_ship, sum(item_qty) AS item_qty, sum(item_qty_add) AS item_qty_add BY item_id | eval item_inv_tot=item_qty+item_qty_add-item_qty_ship | eval item_inv_diff=item_inv_tot-item_qty | table item_id item_qty item_qty_add item_qty_ship item_inv_diff item_inv_tot
The results provide a comprehensive, single-view snapshot of the entire inventory status. This includes the total units available in the warehouse, the quantity replenished, the number of units shipped, the difference between these values, and finally, the total inventory after replenishment and shipment.
Stock replenishment trends
Here is an example of looking at the amount of inventory being replenished, seeing it in trends. This analysis uses EDI code 940 “Warehouse ship instruction” EDI documents.
index=supply_chain_edi sourcetype="edi:x12" edi_code=940 | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" | table _time, edi_code, edi_tr_id, edi_warehouse_loc, edi_warehouse_item | mvexpand edi_warehouse_item | rex field=edi_warehouse_item "W\d+\|(?<item_qty>\d*)\|[^\|]*\|[^\|]*\|(?<item_id>[^\|]*)~" | timechart span=1d sum(item_qty) AS item_qty_add BY item_id
Stock shipment trends
The following is an example of analyzing shipping trends to fulfill orders using EDI 856 ASNs.
index=supply_chain_edi sourcetype="edi:x12" edi_code=856 source=edi_quantumline_customer_full | dedup edi_tr_id | rex field=_raw max_match=10 "(?<edi_ship_item>HL.*[\r\n]+LIN.*[\r\n]+SN.*)" | table _time, edi_code, edi_tr_id, edi_ship_item | mvexpand edi_ship_item | rex field=edi_ship_item max_match=10 "LIN\|\w*\|\w*\|(?<item_id>\w*)~[\r\n]+\w*\|\w*\|(?<item_qty_ship>\w*)" | timechart sum(item_qty_ship) AS item_qty_ship BY item_id
The analysis reveals a steady increase in order shipping trends day by day, indicating rising demand. This provides valuable insight for planning production to effectively meet customer needs.
Status of warehouse inventory situations
The following analysis focuses on warehouse inventory status KPIs. Here are two key examples:
- Product variety: Counting the number of different products stored in a warehouse.
- Total inventory: Measuring the overall quantity of all mixed product stocks.
| inputlookup warehouse_inventory.csv | strcat edi_warehouse_loc ":" item_id combo | appendcols [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940 | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" | table _time, edi_code, edi_tr_id, edi_warehouse_loc, edi_warehouse_item | mvexpand edi_warehouse_item | rex field=edi_warehouse_item "W\d+\|(?<item_qty>\d*)\|[^\|]*\|[^\|]*\|(?<item_id>[^\|]*)~" | stats sum(item_qty) AS item_qty_add BY edi_warehouse_loc item_id | strcat edi_warehouse_loc ":" item_id combo | table combo item_qty_add ] | eval item_qty_new_tot=item_qty+item_qty_add | stats sum(item_qty_new_tot) AS item_qty_tot, dc(item_id) AS item_count BY edi_warehouse_loc
The search results display the total quantity of combined product SKUs in a column chart, along with the count of different items per warehouse location. As shown in the results, there are 12 different SKUs available across these four warehouses. Any changes in these numbers can indicate potential stock shortages at a particular warehouse, impacting its ability to fulfill orders from an inventory status perspective.
Next steps
- Pilot implementation: Test with a subset of high-risk items and locations.
- Refine thresholds: Adjust low-stock thresholds based on item-specific demand.
- Scale up: Expand to all inventory items and warehouses.
- Integrate with procurement: Automate replenishment workflows using EDI 850 (Purchase order).