HotWax Commerce OMS offers out of the box pre-order reports to help monitor and track your pre-order operations. Putting all of these reports together, retailers can ensure that they're efficiently managing all their pre-orders.

Hourly ON Pre-Order and OFF Pre-Order Report

A merchandiser wants to determine which items should be available for Pre-Orders based on purchase orders and the queue of sales orders. They use a Pre-Order Management System to automatically list and delist pre-order products on eCommerce based on multiple rules.

Technical or operational challenges may prevent products from being available for pre-orders on eCommerce, or vice versa. So merchandisers want to spot-check on products that should be listed or delisted as pre-orders on eCommerce to avoid over-selling or underselling.

The Hourly ON and OFF Pre-Order Report helps merchandisers quickly identify products that are listed for accepting pre-orders and products that are recently delisted for taking Pre-Orders from eCommerce. With the help of these Shopify reports, merchandisers can quickly identify gaps and take action to fill the gaps.

User

Head of eCommerce, Merchandising Team

Glossary

Daily Pre-Order Summary Report

Merchants with successful pre-order strategy implemented with the help of a Pre-Order Management System can get up to 10% of their revenues from Pre-Order sales. To measure the performance of their Pre-Order initiative, merchandisers like to know how many pre-orders are received as compared to the overall revenue.

Using the Daily Pre-Order Summary Report, merchants can get the contribution of pre-order revenue to the overall revenue and the number of pre-order units sold.

User Head of eCommerce, Merchandising Team

Glossary

Daily Pre-Order Product Performance report

Merchandisers want to measure the best and least-performing Pre-Order products to guide their future strategies. For example, if a product does not perform well after its launch, merchandisers may want to discontinue the product. The Daily Pre-Order Product Performance report helps merchandisers analyze the performance of products on Pre-Order. The report also helps merchandisers make better decisions at the time of product assortment.

User

Head of eCommerce, Merchandising Team

Glossary

Daily Released Pre-Order report

When inventory is received at a warehouse, an effective Pre-Order Management System enables merchandisers to allocate inventory for accepted customer Pre-Orders both manually and automatically. When merchandisers manually allocate inventory for Pre-Orders, they already know how much inventory is consumed by existing pre-orders. However, when the system automatically allocates the inventory, merchandisers have no idea how much inventory is available to promise, which orders have been allocated the inventory, and which ones haven’t. Therefore, merchandisers prefer a daily summary of all pre-orders for which inventory is allocated automatically. The Daily Released Pre-Order report gives a daily summary of all the pre-orders for which the Pre-Order Management System has automatically allocated the inventory.

User

Head of eCommerce, Merchandising Team

Glossary

Daily Promise Date Changed Report

The customer expects their pre-orders to be shipped by/on the promised date. Due to supply-chain constraints or any other reasons, expected inventory may occasionally be delayed. For all pre-orders, OMS integrates with merchants' Marketing Automation Platform to notify the customers of updated promised dates.

Merchants accept pre-orders on multiple items every day. So, inventory delays can impact many pre-orders. Therefore, merchandisers prefer a daily summary of these date changes, to verify if the updated promise dates are communicated to customers or not.

The Daily Promise Date Changed Report shows all date changes made for previously accepted Pre-Orders.

User

Head of eCommerce, Merchandising Team

Glossary

This document details the Reorder-Limit feature, allowing clients to automatically generate reports for items requiring inventory replenishment.The feature utilizes a pre-configured Reorder Channel with customizable threshold rules. Clients can adjust these rules to define the minimum inventory level (reorder limit) at which they'd like to be notified for stock replenishment.

Setting Reorder Limits

Here's how to set the reorder limit for products by tag:

1. Navigate to Launchpad > ATP App > Threshold page.

2. Click the "+" symbol to create a new rule.

3. Enter a descriptive name for the rule, such as "Reorder Limit - [Tag Name]".

4. In the Threshold field, enter your desired reorder level (e.g., 5).

5. Under Channels, select the Configuration Facility.

6. Under Products by Tag, use the filter options to include or exclude products based on their tags.

7. Click Save to create the rule.

8. Schedule the rule to run daily at midnight. You can also click Run Now for immediate execution from the Job scheduler pinned on the top of the page.

Additional Steps (Optional)

1. Navigate to Launchpad > Job Manager App > Inventory page.

2. Locate the Import Product Facility job under "More jobs."

3. Schedule this job to run every 3 hours, or click Run Now for immediate execution. (This ensures data updates)

4. In the same app, navigate to the Miscellaneous page and schedule the Process Bulk Import Files job to run every 15 minutes. You can also click Run Now for immediate execution.

Setting Different Reorder Limits (Optional)

If you require different reorder limits for various product categories, create separate rules using the same process mentioned above. Utilize the Products by Tag section for filtering products within each rule.

Verifying Reorder Limits

After setting the reorder limits, verify the configuration by navigating to the product's inventory details within the OMS product page. You should see a new reorder channel with the corresponding reorder limit threshold. Products belonging to the designated tag and having an Available-To-Promise (ATP) level that falls below this reorder channel threshold, along with no upcoming purchase orders (POs), will be considered eligible for inclusion in the Reorder Eligible Products report.

Report Delivery

The scheduled report will be delivered to your inbox at a predetermined time slot, assuming there are products meeting the reorder criteria. The report frequency can be adjusted upon request.

Definition

In HotWax OMS, every process goes through certain stages in its lifecycle. Generally, there are four checkpoints in the lifecycle:

1. Creation: The initial stage where a process is created but still needs approval before it can move to the next stage. For example, an order that is created but cannot be brokered as it needs to be approved.

2. Authorization: Once approved, the process moves to this stage where it is ready and waiting to be processed. For instance, an order that is approved but not yet fulfilled.

3. Completion/Terminal: This stage means the process has finished, and no further action is needed. For example, when an order is either fulfilled (successful) or cancelled (failed).

4. Post-Completion: Sometimes even after a process is in the terminal stage, additional actions might be needed. For example, if a customer wants to return an order, the order enters this stage.

To better manage these stages, we assign a numeric value to each stage according to their chronology. These numbers will be the "age" of the process. Thus, the farther the process is into its lifecycle, the greater is its age. As a convention, the ages of the above four mentioned stages - Creation, Authorization, Completion and Post-Completion - are defined as:

Additionally, the Completion stage can have two outcomes:

There can also be events that occur between stages. For example, after a shipment is authorized, it might move to a "shipment packed" status. This is not the final stage because the shipment still needs to be sent out. Such an event would have an age value between 50 and 100, reflecting its position between Authorization and Completion.

Purpose

The StatusItem table is frequently used to identify records with a specific status or objective. For instance, if the goal is to find all shipments that are authorized but have not yet been shipped, the query would need to check for shipments in statuses such as SHIPMENT_APPROVED, SHIPMENT_PICKED, or SHIPMENT_PACKED. However, when there are multiple statuses to check, these searches can become slow.

To optimize this process, a numeric column called "age" has been added to the StatusItem table. This column indicates the stage of an item's lifecycle. Referring to the example above, instead of checking for multiple statuses, the search can now filter for shipments where the "age" is greater than or equal to 50 but less than 100. This approach simplifies the logic and improves search performance.

Different Types of Lifecycles and Their Status Ages

1) Inventory Count

2) Order

3) Order Item

4) Payment Preference

5) Pick Item

6) Picklist

7) Return

8) Service

9) Shipment

Rejected Order Items Report

It is common for fulfillment locations to reject online orders due to various factors such as inventory discrepancies or fulfillment issues. However, minimizing these rejections holds importance for retailers striving to uphold customer satisfaction and streamline operations. HotWax Commerce recognizes this need and offers a report on rejected orders from multi-fulfillment locations.

This report serves as a crucial tool for retailers in their efforts to reduce rejection rates. By providing insights into rejection occurrences—including timing, locations, SKUs, and order IDs—it empowers users to identify patterns and root causes behind rejections. Armed with this information, retailers can take measures to optimize inventory management, streamline fulfillment processes, and enhance staff training.

HotWax Commerce's rejection report enables users to make decisions aimed at improving overall operational efficiency. By understanding which items are rejected more frequently and in which locations, retailers can implement strategies to minimize rejections and ensure accurate, timely order fulfillment. Ultimately, this proactive approach not only enhances customer satisfaction but also strengthens the retailer's competitive edge in the market.

Glossary

Query Logic

Data Selection: The SQL query begins by selecting specific data from various tables relevant to generating a Rejected Order Items Report. These tables include information about orders, order items, order statuses, and product details.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins relevant tables together based on common fields such as order IDs and product IDs. By doing so, it gathers interconnected information about rejected order items, including order details, item descriptions, rejection reasons, and external IDs.

Selecting Necessary Information: From the joined tables, the query selects specific columns relevant to the report's objective. These columns typically include order IDs, rejection dates, SKUs (product identifiers), item descriptions, rejection reasons, and external IDs. This selection provides essential details required for analyzing and understanding rejected order items.

Grouping Data: Subsequently, the query groups the selected data based on certain criteria such as order ID, rejection date, SKU, item description, rejection reason, and external ID. Grouping the data helps in summarizing and organizing it for further analysis, facilitating insights into patterns and trends related to rejected order items.

Counting Distinct Orders: Within each group, the query counts the number of distinct order IDs. This count provides insights into the volume of unique orders that had items rejected, helping understand the overall impact of rejection on order fulfillment.

Calculating Date: Additionally, the query includes a calculation to determine the date of the rejected order items. It may derive the first day of the week for each rejection date, facilitating grouping of rejections by week and enabling a broader perspective on rejection trends over time.

Unreconciled Report

The Unreconciled Report helps retailers identify orders that have been brokered but not yet reconciled with their ERP or WMS systems. This report is crucial for maintaining operational efficiency and ensuring timely fulfillment of orders. By highlighting these unreconciled orders, retailers can take corrective actions to prevent data discrepancies.

The report provides detailed information, including order IDs, item descriptions, UPCs, brokered dates and times, and cut-off times, enabling retailers to address discrepancies promptly and maintain a smooth workflow. The focus is on orders brokered to facilities like distribution centers that have yet to be confirmed by the ERP or WMS, ensuring that all orders are accurately tracked and processed.

Glossary

Query Logic

Data Selection: The query begins by selecting specific data from various tables that contain information about orders, order items, facilities, and product details.

Defining Brokered Criteria: The primary focus is on brokered order items that have not been reconciled. Criteria are established to filter data and include only those records where the change_reason_enum_id indicates brokering and the brokering date is more recent than the specified cutoff date.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins several tables based on common fields such as ORDER_ID and ORDER_ITEM_SEQ_ID. This ensures all necessary information is captured.

Selecting Necessary Information: The query selects specific columns from the joined tables that are relevant to the report’s objective, such as internal order IDs, Shopify order IDs, facility locations, UPCs, item descriptions, brokering dates, and times, as well as cutoff statuses and product store IDs.

Filtering Data: The query includes filters to select records from a specific product store and only considers sales orders that have been approved. It also ensures that the brokering date is more recent than a specified date and that the facility type is a distribution center.

Store Rejections with Reasons

Rejection reason reports provide valuable insights into rejection rates and reasons across different facilities. These reports outline the number of rejections at each facility along with the underlying reasons behind them. This allows retailers to pinpoint facilities with higher rejection rates and take targeted actions to enhance their performance. For example, if a store frequently experiences rejections due to items being out of stock, conducting cycle counts can help reconcile discrepancies between the system's available inventory and the physical stock in the store. Moreover, by identifying facilities where damaged items are more prevalent, retailers can streamline operations to mitigate such occurrences. Ultimately, the aim of these reports is to pinpoint the location and frequency of rejections and implement measures to minimize them, thereby optimizing overall store performance.

Glossary

Query Logic

Data Selection: The SQL query is designed to gather information for generating a report on Store Rejections with Reasons. It selects specific data from various tables, including details about orders, order items, order statuses, and product information. These tables store interconnected data related to store orders and inventory.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins relevant tables together based on common fields such as order IDs, product IDs, and facility IDs. By joining these tables, it gathers detailed information about rejected order items, including order details, item descriptions, rejection reasons, and facility locations.

Selecting Necessary Information: From the joined tables, the query selects specific columns relevant to the report's objective. These columns typically include the location (store), rejection reasons, and the count of Shopify order IDs associated with each rejection reason. This selection provides essential details required for analyzing and understanding store rejections.

Grouping Data: Subsequently, the query groups the selected data based on certain criteria such as location (store) and rejection reason. Grouping the data allows for summarization and organization, facilitating insights into common rejection reasons across different store locations.

Counting Rejected Orders: Within each group, the query counts the number of distinct Shopify order IDs. This count provides insights into the volume of rejected orders associated with each rejection reason and location, aiding in understanding the frequency and impact of rejections.

Sorting and Limiting Results: Finally, the query sorts the results based on the count of Shopify order IDs in descending order. This arrangement helps prioritize the most significant rejection reasons. Additionally, it limits the output to a maximum of 5000 records, ensuring manageable data for analysis.

Shipment Tracking Report

Merchants use third-party services to ship orders to customers' addresses. Orders are picked, packed, and handed to third-party logistics (3PL) companies like FedEx and UPS. All shipments dispatched have their unique tracking ID. This tracking ID is provided by the 3PLs and helps customers keep track of their orders. Because multiple shipments are sent out daily, merchants require a centralized view of order shipment details. This Shopify report helps merchants quickly identify an order's tracking ID, shipping address, and dispatch location. The Shipment Tracking Report shows the daily shipment details for each order. Using this report, merchants can quickly resolve questions related to order tracking by finding the tracking ID and the shipping address for the order.

Glossary

Query Logic

Data Selection: The SQL query is structured to extract specific data for generating a Shipment Tracking Report. It selects information from various tables, and containing details about orders, order items, shipments, shipment statuses, and shipping addresses.

Defining Shipment Criteria: The primary objective of the report is to track shipments. Therefore, the query defines criteria to filter the data, including only shipments that have been marked as shipped (STATUS_ID = 'SHIPMENT_SHIPPED') and associated order items that are completed (STATUS_ID = 'ITEM_COMPLETED').

Joining Relevant Tables: To gather comprehensive shipment data, the SQL query joins several tables together based on common fields such as order IDs, order item sequence IDs, and shipment IDs. By doing so, it collects interconnected information about shipments, order items, shipment statuses, and shipping addresses.

Selecting Necessary Information: From the joined tables, the query selects specific columns relevant to the report's objective. These columns typically include the Shopify order ID, tracking number, shipping method, shipping location, ship-to address, and the number of units shipped. This selection provides essential details required for tracking and analyzing shipments.

Grouping Data: Subsequently, the query groups the selected data based on certain criteria such as the tracking number. Grouping the data allows for summarization and organization, facilitating insights into the status and progress of each shipment.

Counting Units Shipped: Within each group, the query counts the number of units shipped. This count provides insights into the volume of items included in each shipment, aiding in understanding shipment sizes and fulfillment metrics.

Sorting and Limiting Results: Finally, the query sorts the results based on the Shopify order ID and limits the output to a maximum of 10 records. This arrangement helps prioritize and present the most relevant shipment tracking information while ensuring a manageable dataset for review.

Shopify Fulfillment Status Report

The Shopify Fulfillment Status Report offers retailers valuable insights into their order processing. It highlights the number of orders fulfilled and those still pending, aiding in the assessment of operational efficiency. Retailers can leverage reports from Hotwax to access historical data and current statistics, enabling them to track performance over time and promptly address any issues. It serves as a progress report for order fulfillment, ensuring smooth operations.

Glossary

Query Logic

Data Selection: The SQL query is crafted to gather data for generating a Shopify Fulfillment Status Since Last Day Pie Chart. It selects specific information from various tables, including details about orders, order items, order fulfillment history, shipments, statuses, products, and facilities.

Defining Criteria: The primary objective of the report is to analyze the fulfillment status of Shopify orders within the last day. To achieve this, the query defines criteria to filter the data, including only orders of type "SALES_ORDER" and fulfillment history associated with shipments. Additionally, it focuses on orders where the Shopify fulfillment status is either 'pending' or has been updated within the last day.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins several tables together based on common fields such as order IDs, order item sequence IDs, shipment IDs, and status IDs. By doing so, it gathers interconnected information about order fulfillment, order items, shipments, statuses, products, and facilities.

Selecting Necessary Information: From the joined tables, the query selects specific columns relevant to the report's objective. These columns typically include the Shopify fulfillment status and the count of orders corresponding to each fulfillment status. This selection provides essential details required for visualizing the distribution of fulfillment statuses in the pie chart.

Grouping Data: Subsequently, the query groups the selected data based on the Shopify fulfillment status. Grouping the data allows for summarization and organization, facilitating insights into the distribution of pending and successfully fulfilled orders.

Sorting and Limiting Results: Finally, the query sorts the results based on the count of orders for each fulfillment status in descending order. This arrangement helps prioritize and present the most relevant fulfillment status information. Additionally, it limits the output to a maximum of 100 records, ensuring a manageable dataset for visualization in the pie chart.

Daily Store Shipment Performance Report

Merchants measure their stores’ omnichannel order fulfillment rates and want a clear, accurate view of their daily shipping performance. Therefore, merchants seek a detailed storewide report that displays every store’s daily shipping performance. The Daily Store Shipment Performance Report shows the number of daily shipped orders for each store, giving an overview of the store’s daily performance. This performance report also yields valuable insight into the most active and least active stores within a set timeframe. With this data, retailers can work with underperforming stores to understand the reason for low activity, identify the cause of underperformance, and resolve the issue. Similarly, the retailer wants to see product-level shipping performance. The report also includes product-level performance. This information helps retailers understand a newly launched product's performance in a given area or region. Retailers can plan their inventory accordingly after knowing which products perform best at a given store.

Glossary

Query Logic

Data Selection: The SQL query is intended to generate a Daily Store Shipment Performance Report. It begins by selecting specific data from various tables, including order items, order headers, order facility changes, order statuses, facilities, product features, products, and product associations.

Defining Criteria: The primary objective of the report is to analyze the shipment performance of retail stores on a daily basis. To achieve this, the query sets criteria to filter the data. It focuses on orders completed on the previous day (current_date() - INTERVAL 1 DAY), marked with a status ID of 'ITEM_COMPLETED', and involving a change reason enum ID of 'BROKERED'. Additionally, it specifically targets retail stores by filtering facility types.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins multiple tables together based on common fields such as order IDs, order item sequence IDs, facility IDs, product IDs, and status date. By doing so, it gathers interconnected information about order items, order headers, order facility changes, order statuses, facilities, product features, products, and product associations.

Selecting Necessary Information: From the joined tables, the query selects specific columns relevant to the report's objective. These columns typically include store IDs, SKUs (product identifiers), styles, colors, sizes, and the number of units shipped. This selection provides essential details required for assessing store shipment performance.

Grouping Data: Subsequently, the query groups the selected data based on store IDs, colors, sizes, and product IDs. Grouping the data allows for summarization and organization, facilitating insights into the distribution of shipped units across different products and attributes within each store.

Sorting and Limiting Results: Finally, the query limits the output to a maximum of 1000 records, ensuring a manageable dataset for analysis. This step ensures that the report focuses on the most relevant information while maintaining a reasonable size for practical use.

Store Daily Orders Fulfillment Report

The Store Daily Orders Fulfillment Report empowers retailers to monitor the efficiency and effectiveness of their store operations. By tracking the daily number of orders fulfilled per store, retailers can identify trends and patterns, facilitating individual store performance assessments. This insight aids in recognizing top-performing stores and pinpointing areas for improvement in underperforming ones. With Store Daily Orders Fulfillment Reports, retailers can optimize store operations, enhance customer satisfaction, and ultimately drive revenue growth.

Glossary

Query Logic

Data Selection: The SQL query is designed to generate a Store Daily Order Fulfillment Report. It begins by selecting specific data from various tables, including order facility changes, order statuses, facilities, order headers, and order items.

Defining Criteria: The primary objective of the report is to analyze the daily order fulfillment performance of retail stores. To achieve this, the query sets criteria to filter the data. It focuses on orders completed on the previous day, marked with a status ID of 'ITEM_COMPLETED', and involving a change reason enum ID of 'BROKERED'. Additionally, it specifically targets retail stores by filtering facility types and a particular product store ID.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins multiple tables together based on common fields such as order IDs, order item sequence IDs, facility IDs, and status dates. By doing so, it gathers interconnected information about order facility changes, order statuses, facilities, order headers, and order items.

Grouping and Summarizing Data: The query groups the selected data based on store IDs (facility IDs) and calculates the sum of quantities of order items fulfilled for each store. Grouping and summarizing the data in this manner allows for a clear overview of the total quantity of items fulfilled by each store.

Sorting and Limiting Results: Finally, the query sorts the results based on the sum of quantities in descending order and limits the output to a maximum of 100 records. This step ensures that the report focuses on the stores with the highest order fulfillment quantities, providing insights into their performance while maintaining a manageable dataset for analysis.

Daily Warehouse Fulfillment Report

In the era of eCommerce growth, retail warehousing has become increasingly crucial to modern businesses. A fully functional warehouse gives the best output because of its low inventory carrying cost. As a result, the warehouse is responsible for fulfilling the majority of orders placed.

As warehouse operations are incredibly massive, there is always a possibility that some brokered orders can get misplaced or left unattended during the process. For merchants, evaluating warehouse performance and order fulfillment rates becomes crucial to success.

The Daily Warehouse Fulfillment Report provides all the orders brokered at the warehouse but not fulfilled yet. The report shows all the daily shipped and pending orders at a warehouse.

After identifying all the pending orders within this report, retailers can work to clear the backlog. Ultimately, this report helps to analyze the warehouse's fill rate. And in the case of multiple warehouses, it helps to compare their performance and give retailers actionable insights to improve their omnichannel order fulfillment strategy.

User

Vice President of Retail, Warehouse Fulfillment team

Report Glossary

Store Dashboard

Omnichannel retailers use stores as their order fulfillment locations. Fulfillment centers help merchants meet customer expectations of faster delivery. Merchants often operate multiple stores in various locations within their operational area so they need a reliable way to measure their fulfillment performance.

Many reasons contribute to stores delaying and rejecting orders, including low operational strength and inventory shortages. Customer order cancellations are also common. Tracking store fulfillment performance enables retailers to identify and resolve these order fulfillment issues.

Likewise, merchants prefer to keep a company-wide order fulfillment goal for daily processed, shipped, rejected, and canceled orders. Any facility performing below the set goal raises a concern for merchants and puts their focus on improving the performance of these facilities.

The Store Dashboard shows the storewide data where the orders were brokered, rejected, canceled, and then fulfilled in a day.

The report shows the percentage of shipped, processed, and canceled orders of the stores. Retailers can compare the actual fulfillment performance against the set goal to assess the performance of each store.

User

Head of eCommerce, Head of Stores, Vice President of Retail

Glossary

Daily BOPIS Orders

Insider reports that 39% of consumers prefer using BOPIS when placing online orders because they get their orders faster. During their BOPIS journey, customers also expect prompt notifications from the brand on the status of their orders.

When customers can’t come to the store to pick up their orders, store managers need a detailed report on the unpicked BOPIS orders.

The Daily BOPIS Orders Report provides cumulative details of all the BOPIS orders of the last 30 days. This Shopify report also shows the status of these BOPIS orders if orders are picked up, canceled, or not picked up.

With this detailed report, store managers also look for long pending BOPIS orders. These orders keep the inventory blocked which can be used to fulfill other orders. With the knowledge of pending BOPIS orders, merchants can cancel all long-pending orders and use the inventory for new orders.

The report is essential to improve the BOPIS order fulfillment rate. Again, the fewer the unpicked orders, the better the order fill rate. Additionally, the problem diagnosis with the report helps retailers to clear the challenges in the omnichannel order fulfillment cycle.

User

Head of eCommerce, Head of Stores, Vice President of Retail

Report Glossary

Daily Unfulfilled Expedited Orders

According to the State of Shipping Report, 62% of consumers expect their orders to arrive in less than three business days. To help expedite shipping, many brands have started implementing same-day/next-day delivery. That sounds like an easy fix, right?

Well, sometimes omnichannel order fulfillment teams can't fulfill same-day/next-day orders due to inventory discrepancies or other reasons. Orders go unfulfilled, which leads to increased order cancellations, poor customer service, and broken promises.

To provide a better customer experience, merchants have dedicated Customer Success Teams. The Customer Success Team wants to be on top of unfulfilled expedited delivery orders and continuously work on decreasing the order cancellation rate.

To effectively manage unfulfilled expedited orders, Customer Success Teams need a Daily Unfulfilled Expedited Orders Report. This report provides a granular view of all the unfulfilled same-day/next-day delivery orders. It also helps identify the order’s last brokered location with brokering time.

Furthermore, the report will help Customer Success Teams to evaluate the efficiency of their expedited shipping fulfillment process. Due to the large volume of expedited orders handled by an OMS daily, merchants prefer to have a daily report of all unfulfilled orders.

User

Head of eCommerce, Vice President of Retail

Glossary

Daily Unfulfilled Orders

Merchants set benchmarks for fulfilling orders. Generally, merchants intend to complete order fulfillment within a seven-day maximum time frame. If any order’s fulfillment takes more than seven days, it draws a merchant’s attention.

To avoid order delays, merchants prefer to check all the pending orders at the facilities and alert fulfillment locations to speed up order fulfillment.

The Daily Unfulfilled Orders Report provides the data for all unfulfilled orders at different fulfillment locations. These can be standard, same-day/next-day delivery or store pick-up orders. The report also shows the last brokered facility and order brokered time which helps retailers analyze unfulfilled orders and mitigate challenges in the omnichannel order fulfillment pipeline.

User

Head of eCommerce, Vice President of Retail

Glossary

Receiving Discrepancies by Product

This discrepancy report helps retailers maintain inventory accuracy and uphold customer satisfaction. By highlighting inventory received that does not match the expected quantities, such as the example of shoes-M-38 with a discrepancy of 4 units, retailers can identify and rectify issues like shipment errors, theft, or miscounts. Timely detection of such discrepancies enables corrective actions like adjusting stock levels, investigating root causes, and improving receiving processes, ultimately ensuring that customers receive the products they desire when they expect them.

Glossary

SELECT `SKU` AS `SKU`,
       SUM(ABS(Difference)) AS `SUM(ABS(Difference))`
FROM
  (SELECT s.External_Id AS Shipment_Id,
          s.Shipment_Id as HotWax_Shipment_Id,
          sa.Attr_Value AS Transfer_Order,
          S.EXTERNAL_ID as EXTERNAL_ID,
          p.internal_name AS SKU,
          s.Origin_Facility_Id AS Origin_Facility_Id,
          f2.Facility_Name AS Origin_Facility,
          s.DESTINATION_FACILITY_ID AS Destination_Facility_Id,
          s.DESTINATION_FACILITY_ID AS FACILITY_ID,
          f.Facility_Name AS Destination_Facility,
          si.QUANTITY AS Expected,
          sr.QUANTITY_ACCEPTED AS Received,
          (IFNULL(sr.QUANTITY_ACCEPTED, 0) - si.QUANTITY) AS Difference,
          CASE
              WHEN (si.QUANTITY - sr.QUANTITY_ACCEPTED) = 0
                   AND s.external_id IS NOT NULL THEN 'Completed'
              WHEN sr.QUANTITY_ACCEPTED IS NULL
                   AND s.external_id IS NOT NULL THEN 'NotReceived'
              WHEN (si.QUANTITY - sr.QUANTITY_ACCEPTED) < 0
                   AND s.external_id IS NOT NULL
                   AND si.QUANTITY != 0 THEN 'OverReceived'
              WHEN (si.QUANTITY - sr.QUANTITY_ACCEPTED) > 0
                   AND s.external_id IS NOT NULL THEN 'UnderReceived'
              WHEN si.QUANTITY = 0
                   AND sr.QUANTITY_ACCEPTED IS NOT NULL THEN 'Manually added item to transfer order'
          END AS Status,
          ss.STATUS_DATE AS Received_Date
   FROM shipment s
   JOIN shipment_item si ON s.SHIPMENT_ID = si.SHIPMENT_ID
   LEFT JOIN shipment_receipt sr ON si.SHIPMENT_ID = sr.SHIPMENT_ID
   AND si.SHIPMENT_ITEM_SEQ_ID = sr.SHIPMENT_ITEM_SEQ_ID
   LEFT JOIN shipment_attribute sa ON sa.SHIPMENT_ID = si.SHIPMENT_ID
   LEFT JOIN product p ON p.PRODUCT_ID = si.PRODUCT_ID
   LEFT JOIN facility f ON f.FACILITY_ID = s.DESTINATION_FACILITY_ID
   LEFT JOIN facility f2 ON f2.FACILITY_ID = s.ORIGIN_FACILITY_ID
   LEFT JOIN shipment_status ss ON s.SHIPMENT_ID = ss.SHIPMENT_ID
   AND s.STATUS_ID = ss.STATUS_ID
   WHERE s.SHIPMENT_TYPE_ID = 'IN_TRANSFER'
     AND s.STATUS_ID = 'PURCH_SHIP_RECEIVED'
     AND date(ss.STATUS_DATE) > DATE(now() - INTERVAL 1 MONTH)
     AND sa.Attr_Name = 'EXTERNAL_ORDER_NAME') AS virtual_table
WHERE `Status` != 'Completed'
  AND `Received_Date` >= STR_TO_DATE('2024-05-01 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `Received_Date` < STR_TO_DATE('2024-05-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
GROUP BY `SKU`
ORDER BY `SUM(ABS(Difference))` DESC
LIMIT 10000;

Query Logic

Data Selection: Data selection involves gathering essential details from various tables. It selects shipment IDs, product SKUs, quantities, and facility information, providing insights into discrepancies in received shipments. This curated data forms the foundation for the report, enabling effective analysis and resolution of discrepancies.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query fetches information about shipments, products, facilities, and statuses. By joining these tables, the query gathers all the necessary information needed to understand discrepancies in received shipments.

Selecting Necessary Information: Once the tables are connected, the query selects specific details crucial for the report. It picks essential information like shipment IDs, product SKUs, facility IDs, expected and received quantities, and dates. These details provide insights into where the discrepancies occur, what products are involved, and when the shipments were received. This approach allows the report to focus on the key aspects of receiving discrepancies, enabling users to pinpoint issues and take corrective actions effectively.

Calculating Differences and Defining Statuses: Within the subquery, the query calculates the difference between expected and received quantities by subtracting the Quantity Accepted from the quantity. A status is assigned to each shipment item based on this difference, categorizing shipments into Completed, NotReceived, OverReceived, UnderReceived, and Manually added item to transfer order.

Grouping Data: The query groups the data by Destination_Facility_Id and SKU. This grouping helps to summarize and organize the discrepancies for each product at each facility.

Ordering: The query orders the results in descending order based on the total discrepancies, ensuring the product with more discrepancies appears first.

Recorded Variances Report

This report provides retailers with detailed insights into discrepancies in inventory levels across different products and facilities. By highlighting specific product SKUs that have experienced variances and outlining the reasons behind these discrepancies, the report allows retailers to pinpoint areas of concern swiftly and accurately. Understanding the root causes of inventory discrepancies enables retailers to take proactive measures to rectify issues, whether they stem from inaccuracies in recording, theft, damage, or other factors.

This report helps in optimizing inventory management by ensuring that stock levels align with demand and sales forecasts. By identifying variances promptly, retailers can prevent overstocking or stockouts, thus minimizing potential revenue losses.

Glossary

SELECT `Facility Id` AS `Facility Id`,
       `Facility` AS `Facility`,
       `SKU` AS `SKU`,
       DATE_FORMAT(`Adjustment Date`, "%Y-%m-%d %H:%i %p") AS `Adjustment date`,
       `User` AS `User`,
       `Quantity` AS `Quantity`,
       `Description` AS `Description`,
       `Enum ID` AS `Enum ID`,
       `Comments` AS `Comments`
FROM
  (SELECT fa.facility_id AS 'Facility Id',
          fa.facility_id AS 'FACILITY_ID',
          fa.facility_name AS 'Facility',
          gi.id_value AS 'SKU',
          iiv.quantity_on_hand_var AS 'Quantity',
          iiv.change_by_user_login_id AS 'User',
          iiv.CREATED_STAMP AS 'Adjustment Date',
          vr.description AS 'Description',
          iiv.comments AS 'Comments',
          e.enum_type_id 'Enum ID'
   FROM physical_inventory pi
   LEFT JOIN inventory_item_variance iiv ON pi.physical_inventory_id = iiv.physical_inventory_id
   LEFT JOIN inventory_item ii ON iiv.inventory_item_id = ii.inventory_item_id
   LEFT JOIN product pr ON ii.product_id = pr.product_id
   LEFT JOIN facility fa ON ii.facility_id = fa.facility_id
   LEFT JOIN good_identification gi ON pr.product_id = gi.product_id
   AND (GI.THRU_DATE > NOW()
        OR GI.THRU_DATE IS NULL)
   JOIN product_category_member pcm ON pr.product_id = pcm.product_id
   AND product_category_id ='PURCHASE_ALLOW'
   JOIN variance_reason vr ON vr.variance_reason_id = iiv.variance_reason_id
   JOIN enumeration e ON e.enum_id = vr.variance_reason_id
   WHERE gi.good_identification_type_id = "UPCA"
     AND (PCM.THRU_DATE IS NULL
          OR PCM.THRU_DATE > NOW())
     AND e.enum_type_id = "IID_REASON"
     AND iiv.variance_reason_id <> "POS_SALE"
     AND fa.facility_id <> "BDC") AS virtual_table
WHERE `Description` != 'Adjustment'
  AND `Adjustment Date` >= STR_TO_DATE('2024-05-01 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `Adjustment Date` < STR_TO_DATE('2024-05-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
LIMIT 50000;

Query Logic

Data Selection: The SQL query gathers data from multiple tables including physical_inventory, inventory_item_variance, product, facility, and others. These tables provide essential information such as facility IDs, SKUs, adjustment dates, quantities, users, descriptions, and comments necessary for analyzing inventory variances.

Filtering Criteria: To focus the report on relevant variances, the query applies several filtering criteria:

• It selects adjustments made for products identified by their UPC code (good_identification_type_id = "UPCA").

• Only adjustments related to products with the product category PURCHASE_ALLOW are included.

• Adjustments labeled as POS_SALE are excluded, as they typically do not represent inventory discrepancies.

• Adjustments associated with a specific facility ID ("BDC") are also excluded, based on business requirements.

• The query further refines the dataset by specifying a date range for the adjustment date, ensuring that only adjustments within the specified timeframe are included in the report.

Column Selection: Essential columns such as facility IDs, SKUs, adjustment dates, quantities, users, descriptions, and comments are selected to provide insights into inventory adjustments. This selection enables retailers to identify specific products, facilities, and reasons behind variances.

Grouping Data: The query groups the selected data based on certain criteria such as facility IDs, SKUs, adjustment dates, and descriptions. Grouping the data helps organize it logically, making it easier to analyze and identify patterns in inventory adjustments.

Open Inbound Shipment Report

This report facilitates the management of open inbound shipments for retailers by providing an overview of the transfer process, enhancing accuracy and operational efficiency.

For retailers, this report helps monitor the status of inbound shipments, tracks their journey to destination facilities, and verifies the receipt of goods. Key details such as destination facility, external ID (Netsuite ID), tracking code, and WMS (Warehouse Management System) ID are included, offering a detailed overview of each inbound shipment for streamlined identification and effective management.

Glossary

SELECT shipment_id AS shipment_id,
       hotwax_shipment_id AS hotwax_shipment_id,
       gobolt_order_name AS gobolt_order_name,
       origin_facility AS origin_facility,
       shipment_status AS shipment_status,
       DATE(imported_date) AS imported_date,
       destination_facility AS destination_facility,
       tracking_number AS tracking_number,
       sum(quantity) AS quantity
FROM
  (SELECT s.External_Id AS shipment_id,
          s.Shipment_Id AS hotwax_shipment_id,
          sa.Attr_Value AS gobolt_order_name,
          s.Origin_Facility_Id AS origin_facility_id,
          s.Origin_Facility_Id AS 'FACILITY_ID',
          f2.Facility_Name AS origin_facility,
          s.DESTINATION_FACILITY_ID AS destination_facility_id,
          f.Facility_Name AS destination_facility,
          'Receiving Pending' AS shipment_status,
          ss.STATUS_DATE AS imported_date,
          IFNULL(srs.TRACKING_ID_NUMBER, "Not Available") AS tracking_number,
          p.internal_name AS 'SKU',
          si.quantity AS quantity
   FROM shipment s
   LEFT JOIN shipment_status ss ON s.SHIPMENT_ID = ss.SHIPMENT_ID
   AND s.STATUS_ID = ss.STATUS_ID
   LEFT JOIN shipment_attribute sa ON s.SHIPMENT_ID = sa.SHIPMENT_ID
   AND sa.ATTR_NAME = 'EXTERNAL_ORDER_NAME'
   LEFT JOIN shipment_route_segment srs ON srs.SHIPMENT_ID = s.SHIPMENT_ID
   LEFT JOIN facility f ON s.DESTINATION_FACILITY_ID = f.FACILITY_ID
   LEFT JOIN facility f2 ON s.ORIGIN_FACILITY_ID = f2.FACILITY_ID
   JOIN shipment_item si ON si.SHIPMENT_ID = s.SHIPMENT_ID
   JOIN product p ON p.product_id = si.product_id
   WHERE S.SHIPMENT_TYPE_ID IN ('IN_TRANSFER')
     AND s.STATUS_ID IN ('PURCH_SHIP_SHIPPED')) AS virtual_table
WHERE imported_date >= STR_TO_DATE('2024-05-01 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND imported_date < STR_TO_DATE('2024-05-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
GROUP BY shipment_id,
         hotwax_shipment_id,
         gobolt_order_name,
         origin_facility,
         shipment_status,
         DATE(imported_date),
         destination_facility,
         tracking_number
ORDER BY quantity DESC
LIMIT 50000;

Query Logic

Data Selection: The SQL query selects specific details related to inbound shipments from the database. This includes information such as shipment ID, HotWax shipment ID, order name, origin and destination facilities, shipment status, import date, tracking number, and quantity.

Defining Criteria: The query filters the selected data to include only inbound shipments that are open. These are typically shipments that are in transit or awaiting receipt at the destination facility. The specific criteria for identifying open inbound shipments involves checking their status or other attributes.

Joining Relevant Tables: To gather comprehensive information, the query joins multiple tables such as shipment, shipment_status, shipment_attribute, facility, shipment_route_segment, shipment_item, and product. These tables are connected using common fields to provide a complete picture of each inbound shipment, including its status updates, attributes, route segments, facilities involved, items, and products.

Grouping Data: This query groups the selected data by certain attributes such as shipment IDs, origin facilities, or destination facilities. Grouping the data allows for a summarized view of inbound shipments, making it easier to analyze trends and identify patterns.

Receiving Report

By analyzing this report, retailers can gain insights into the incoming inventory flow, associated transfer orders, and the receiving method employed. This information enables them to track and manage inventory accurately, ensuring optimal stock levels and efficient warehouse operations.

It provides a comprehensive overview of received inventory, aiding retailers in managing their store or warehouse inventory effectively. It includes details such as received date, origin and destination facility IDs, expected quantities, shipment IDs, transfer orders, SKUs, received quantities, differences, and shipment statuses. Additionally, the receiving method (e.g., manual count, automated scanning) is tracked to understand the process employed for inventory reception and to identify any potential issues in the receiving workflow.

Glossary

SELECT DATE(`Received_Date`) AS `Received_Date`,
       `Origin_Facility_Id` AS `Origin_Facility_Id`,
       `Destination_Facility_Id` AS `Destination_Facility_Id`,
       `Expected` AS `Expected`,
       `Shipment_Id` AS `Shipment_Id`,
       `Transfer_Order` AS `Transfer_Order`,
       `SKU` AS `SKU`,
       `Received` AS `Received`,
       `Difference` AS `Difference`,
       `Status` AS `Status`
FROM
  (SELECT s.External_Id AS Shipment_Id,
          s.Shipment_Id AS HotWax_Shipment_Id,
          sa.Attr_Value AS Transfer_Order,
          s.EXTERNAL_ID AS EXTERNAL_ID,
          p.internal_name AS SKU,
          s.Origin_Facility_Id AS Origin_Facility_Id,
          f2.Facility_Name AS Origin_Facility,
          s.DESTINATION_FACILITY_ID AS Destination_Facility_Id,
          s.DESTINATION_FACILITY_ID AS FACILITY_ID,
          f.Facility_Name AS Destination_Facility,
          si.QUANTITY AS Expected,
          sr.QUANTITY_ACCEPTED AS Received,
          (IFNULL(sr.QUANTITY_ACCEPTED, 0) - si.QUANTITY) AS Difference,
          CASE
              WHEN (si.QUANTITY - sr.QUANTITY_ACCEPTED) = 0
                   AND s.external_id IS NOT NULL THEN 'Completed'
              WHEN sr.QUANTITY_ACCEPTED IS NULL
                   AND s.external_id IS NOT NULL THEN 'NotReceived'
              WHEN (si.QUANTITY - sr.QUANTITY_ACCEPTED) < 0
                   AND s.external_id IS NOT NULL
                   AND si.QUANTITY != 0 THEN 'OverReceived'
              WHEN (si.QUANTITY - sr.QUANTITY_ACCEPTED) > 0
                   AND s.external_id IS NOT NULL THEN 'UnderReceived'
              WHEN si.QUANTITY = 0
                   AND sr.QUANTITY_ACCEPTED IS NOT NULL THEN 'Manually added item to transfer order'
          END AS Status,
          ss.STATUS_DATE AS Received_Date
   FROM shipment s
   JOIN shipment_item si ON s.SHIPMENT_ID = si.SHIPMENT_ID
   LEFT JOIN shipment_receipt sr ON si.SHIPMENT_ID = sr.SHIPMENT_ID
   AND si.SHIPMENT_ITEM_SEQ_ID = sr.SHIPMENT_ITEM_SEQ_ID
   LEFT JOIN shipment_attribute sa ON sa.SHIPMENT_ID = si.SHIPMENT_ID
   LEFT JOIN product p ON p.PRODUCT_ID = si.PRODUCT_ID
   LEFT JOIN facility f ON f.FACILITY_ID = s.DESTINATION_FACILITY_ID
   LEFT JOIN facility f2 ON f2.FACILITY_ID = s.ORIGIN_FACILITY_ID
   LEFT JOIN shipment_status ss ON s.SHIPMENT_ID = ss.SHIPMENT_ID
   AND s.STATUS_ID = ss.STATUS_ID
   WHERE s.SHIPMENT_TYPE_ID = 'IN_TRANSFER'
     AND s.STATUS_ID = 'PURCH_SHIP_RECEIVED'
     AND DATE(ss.STATUS_DATE) > DATE(NOW() - INTERVAL 1 MONTH)
     AND sa.Attr_Name = 'EXTERNAL_ORDER_NAME') AS virtual_table
WHERE `Received_Date` >= STR_TO_DATE('2024-05-05 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `Received_Date` < STR_TO_DATE('2024-05-12 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
GROUP BY DATE(`Received_Date`),
         `Origin_Facility_Id`,
         `Destination_Facility_Id`,
         `Expected`,
         `Shipment_Id`,
         `Transfer_Order`,
         `SKU`,
         `Received`,
         `Difference`,
         `Status`
LIMIT 100;