HotWax has various BI Reports that clients use to monitor operations and make data-driven decisions.

Topics

These reports are essential for monitoring the brokering of orders to facilities.

Brokered Items Report

View this report to understand which items have been brokered to facilities for fulfillment. Compare this information with new orders received in the OMS to track allocation rates. Any discrepancies between new orders from eCommerce and entries in this report may indicate issues with orders not progressing to fulfillment.

In the NetSuite context, the Brokered Items Report is vital for a streamlined fulfillment process. By cross-referencing brokered items with new orders, retailers ensure alignment between NetSuite and eCommerce platforms. Inconsistencies may highlight integration issues, risking order fulfillment disruptions. A misalignment between new orders from eCommerce and entries in this report may indicate that orders are not progressing to fulfillment in NetSuite.

User

Operations team

Glossary

Brokered Count Report

View this report to monitor the brokered order count, providing a high-level overview to ensure all new orders are allocated. If the brokered count significantly lags behind the total new order count, investigate the unfillable reports for potential issues.

User

Operations team

Glossary

HotWax Order Count Report

The HotWax Order Count Report offers a daily snapshot of new orders in the Order Management System (OMS), providing a quick reference for monitoring overall order activity and facilitating proactive decision-making. In the Shopify context, this report ensures seamless synchronization by comparing daily order counts. It serves as a vital tool for merchants, quickly identifying and addressing any discrepancies in the synchronization process, ensuring accurate and efficient order fulfillment.

Glossary

SELECT count(`EXTERNAL_ID`) AS `COUNT(EXTERNAL_ID)`
FROM
  (SELECT oh.ORDER_ID ,
          oh.EXTERNAL_ID ,
          CASE
              WHEN oh.SALES_CHANNEL_ENUM_ID = 'POS_SALES_CHANNEL' THEN 'POS'
              ELSE 'Shopify'
          END AS SALES_CHANNEL ,
          oh.STATUS_ID ,
          oh.ORDER_DATE
   FROM order_header oh
   WHERE oh.ORDER_TYPE_ID = 'SALES_ORDER') AS virtual_table
WHERE (`STATUS_ID` NOT IN ('ORDER_CANCELLED'))
  AND `ORDER_DATE` >= STR_TO_DATE('2023-05-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `ORDER_DATE` < STR_TO_DATE('2024-05-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
LIMIT 100000;

Query Logic

Data Selection: The SQL query selects specific data fields required for the report. This typically includes information about orders, such as order IDs and other relevant details.

Filtering HotWax Orders: The primary objective of the report is to count orders processed by the HotWax system. Therefore, the query includes conditions to filter the data and include only orders processed by HotWax.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins tables containing order information. This typically involves joining the order_header table with other relevant tables based on common fields like ORDER_ID.

Selecting Necessary Information: From the joined tables, the query selects columns related to order details needed for analysis or reporting. This could include order IDs, order dates, and other relevant information.

Filtering and Grouping Data: Conditions are applied to filter the data and include only orders processed by HotWax. This might involve checking if certain attributes or flags indicate that the order was processed by HotWax. The query groups the selected data based on certain criteria, such as order IDs or dates. Grouping data helps summarize and organize it for analysis.

Counting HotWax Orders: Within each group, the query counts the number of orders processed by HotWax. This provides insights into the volume of orders processed by the system over a specified period.

Sales Order Count by Channel Report

Retailers receive orders through various channels, including eCommerce websites, social media platforms, online marketplaces, and point-of-sale (POS) systems. Retailers need visibility of their sales into channel performance. The Sales Order Count by Channel report offers a clear summary of order counts per channel, enabling retailers to identify high-performing channels and areas needing improvement. Retailers can easily discern the total count of orders per channel, whether it's from POS, Shopify, or any other channel enabling effective tracking and analysis.

Glossary

SELECT `SALES_CHANNEL` AS `SALES_CHANNEL`,
       count(`EXTERNAL_ID`) AS `COUNT(EXTERNAL_ID)`
FROM
  (SELECT oh.ORDER_ID ,
          oh.EXTERNAL_ID ,
          CASE
              WHEN oh.SALES_CHANNEL_ENUM_ID = 'POS_SALES_CHANNEL' THEN 'POS'
              ELSE 'Shopify'
          END AS SALES_CHANNEL ,
          oh.STATUS_ID ,
          oh.ORDER_DATE
   FROM order_header oh
   WHERE oh.ORDER_TYPE_ID = 'SALES_ORDER') AS virtual_table
WHERE (`STATUS_ID` NOT IN ('ORDER_CANCELLED'))
  AND `ORDER_DATE` >= STR_TO_DATE('2023-05-06 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `ORDER_DATE` < STR_TO_DATE('2024-05-06 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
GROUP BY `SALES_CHANNEL`
ORDER BY `COUNT(EXTERNAL_ID)` DESC
LIMIT 100;

Query Logic

Data Selection: The SQL query selects data from the order_header table, focusing on sales orders. It calculates the count of orders received through each sales channel, excluding cancelled orders, within a specific date range.

Sales Channel: This field extracts the sales channel information from the SALES_CHANNEL_ENUM_ID column in the order_header table. If the SALES_CHANNEL_ENUM_ID is POS_SALES_CHANNEL, it assigns "POS" to the SALES_CHANNEL column; otherwise, it assigns "Shopify".

Count of Orders: This field calculates the count of orders for each sales channel. It uses the count() function to count the occurrences of EXTERNAL_ID, representing each order in the dataset. The query aggregates the data by sales channel, counting the number of orders for each channel.

Filtering: Orders with a status of ORDER_CANCELLED are excluded from the analysis. Additionally, the query filters orders based on their order date, selecting only those within the specified time frame.

Ordering: The results are ordered based on the count of orders in descending order, providing insight into which sales channel has the highest volume of orders.

Limitation: The query limits the output to the top 100 sales channels by order count.

Missing Netsuite Order ID Report

Retailers often encounter synchronization issues between Shopify and Netsuite, leading to orders not being properly recorded. This can result in delays and disruptions in order processing and fulfillment, impacting customer satisfaction and revenue. This report is helpful for retailers to troubleshoot issues when orders, that have all the necessary attributes like SIGNIFYD_APPROVED, and NETSUITE_CUSTOMER_ID are not synchronized with Netsuite. It highlights orders in approved or completed status that lack Netsuite order IDs, indicating synchronization problems. Retailers can view this report to identify problematic orders and rectify errors to ensure all orders are synced with Netsuite. The synchronization process between HotWax and Netsuite may encounter delays. Therefore, this report exclusively displays orders without NetSuite order IDs for over 4 hours.

Glossary

SELECT `ORDER_NAME` AS `ORDER_NAME`,
       `HC_ORDER_ID` AS `HC_ORDER_ID`,
       `HC_ENTRY_DATE` AS `HC_ENTRY_DATE`,
       `TIME_SINCE_ENTRY` AS `TIME_SINCE_ENTRY`,
       `SALES_CHANNEL` AS `SALES_CHANNEL`
FROM
  (SELECT oh.ORDER_NAME ,
          oh.ORDER_ID AS HC_ORDER_ID ,
          DATE_FORMAT(oh.ENTRY_DATE, "%m-%d-%Y %h:%i %p") AS HC_ENTRY_DATE ,
          CASE
              WHEN (TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)) < 60 THEN CONCAT((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)), ' mins')
              ELSE CONCAT(FLOOR((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)) / 60), ' hrs ', ((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)) % 60), ' mins')
          END AS TIME_SINCE_ENTRY ,
          e.DESCRIPTION AS SALES_CHANNEL ,
          oh.ENTRY_DATE
   FROM order_header oh
   LEFT JOIN order_identification oid ON oh.ORDER_ID = oid.ORDER_ID
   AND oid.ORDER_IDENTIFICATION_TYPE_ID = 'NETSUITE_ORDER_ID'
   JOIN enumeration e ON oh.SALES_CHANNEL_ENUM_ID = e.ENUM_ID
   WHERE oh.STATUS_ID IN ('ORDER_COMPLETED',
                          'ORDER_APPROVED')
     AND oh.SALES_CHANNEL_ENUM_ID IN ('WEB_SALES_CHANNEL',
                                      'POS_SALES_CHANNEL')
     AND oid.ORDER_ID IS NULL) AS virtual_table
ORDER BY `ENTRY_DATE` ASC
LIMIT 1000;

Query Logic

Data Selection: The query starts by selecting specific data from a table named order_header. This table contains information about orders, including the order name, order ID, entry date, and sales channel.

Defining Criteria for Missing IDs: The report specifically targets orders without a Netsuite order ID (ORDER_IDENTIFICATION_TYPE_ID = 'NETSUITE_ORDER_ID') by filtering data where the Netsuite order ID is null or not present in the database.

Joining Relevant Tables: The query joins the order_header and order_identification tables based on common fields (e.g., ORDER_ID) to identify orders and associated identification information.

Selecting Necessary Information: Selected columns, such as ORDER_NAME, HC_ORDER_ID, and SALES_CHANNEL, provide context for the missing Netsuite order IDs. Including these details aids in understanding the orders affected by the missing IDs.

Ordering Results: The results are ordered by the ENTRY_DATE in ascending order, which arranges the orders from oldest to most recent. This organization aids in identifying patterns or trends over time.

POS Cash Sale Exp Failed Report

The POS Cash Sale Exp Failed Report is a tool for tracking synchronization failures related to Point of Sale (POS) cash sales. This report is instrumental in identifying instances where the synchronization process encountered issues, allowing for a proactive approach to address and resolve these failures. By leveraging this report, organizations can ensure the accuracy and completeness of their POS cash sale data, contributing to a more reliable and efficient sales reporting process.

Glossary

SELECT `ORDER_ID` AS `ORDER_ID`,
       `EXTERNAL_ID` AS `EXTERNAL_ID`,
       `SALES_CHANNEL` AS `SALES_CHANNEL`,
       `DATE` AS `DATE`,
       TIME_SINCE_ENTRY AS `TIME_SINCE_ENTRY`,
       `CUSTOMER` AS `CUSTOMER`,
       `SUBSIDIARY` AS `SUBSIDIARY`
FROM
  (SELECT OH.ORDER_NAME AS "ORDER_ID",
          OH.ORDER_ID AS "EXTERNAL_ID",
          E.DESCRIPTION AS "SALES_CHANNEL",
          F.EXTERNAL_ID AS "LOCATION",
          DATE_FORMAT(oh.ENTRY_DATE, "%m-%d-%Y %h:%i %p") AS "DATE",
          CASE
              WHEN (TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)) < 60 THEN CONCAT((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)), ' mins')
              ELSE CONCAT(FLOOR((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)) / 60), ' hrs ', ((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 5 HOUR)) % 60), ' mins')
          END AS TIME_SINCE_ENTRY,
          PI2.ID_VALUE AS "CUSTOMER",
          CASE
              WHEN F.EXTERNAL_ID = "376" THEN "5"
              ELSE "1"
          END AS "SUBSIDIARY",
          oh.ENTRY_DATE
   FROM ORDER_HEADER OH
   JOIN ENUMERATION E ON OH.SALES_CHANNEL_ENUM_ID = E.ENUM_ID
   JOIN ORDER_STATUS OS ON OH.ORDER_ID = OS.ORDER_ID
   AND OH.STATUS_ID = OS.STATUS_ID
   JOIN ORDER_ITEM_SHIP_GROUP OISG ON OH.ORDER_ID = OISG.ORDER_ID
   JOIN FACILITY AS F ON OISG.ORDER_FACILITY_ID = F.FACILITY_ID
   JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID
   AND ODR.ROLE_TYPE_ID = 'SHIP_TO_CUSTOMER'
   AND (ODR.THRU_DATE IS NULL
        OR ODR.THRU_DATE > NOW())
   JOIN PARTY_IDENTIFICATION PI2 ON ODR.PARTY_ID = PI2.PARTY_ID
   AND PI2.PARTY_IDENTIFICATION_TYPE_ID = 'NETSUITE_CUSTOMER_ID'
   LEFT JOIN ORDER_IDENTIFICATION OID ON OH.ORDER_ID = OID.ORDER_ID
   AND OID.ORDER_IDENTIFICATION_TYPE_ID = 'NETSUITE_ORDER_ID'
   WHERE OH.STATUS_ID = 'ORDER_COMPLETED'
     AND OH.SALES_CHANNEL_ENUM_ID = 'POS_SALES_CHANNEL'
     AND OISG.SHIPMENT_METHOD_TYPE_ID = 'POS_COMPLETED'
     AND OH.ORDER_TYPE_ID = 'SALES_ORDER'
     AND oid.ORDER_ID IS NULL) AS virtual_table
LIMIT 1000;

Query Logic

Data Selection: The query selects specific data from the ORDER_HEADER table, including order names, IDs, entry dates, sales channels, and customer information.

Data Filtering: Only completed orders (STATUS_ID = 'ORDER_COMPLETED') from the POS sales channel (SALES_CHANNEL_ENUM_ID = 'POS_SALES_CHANNEL') with no associated Netsuite order ID are included.

Defining Criteria for Failed Export: The report targets POS cash sales transactions with failed expense exports.

Joining Relevant Tables: Tables are joined based on common fields to identify transactions and associated details such as sales channels and error information.

Selecting Necessary Information: Selected columns provide context for identifying failed POS cash sales transactions.

Grouping Data: The query implicitly groups records based on filtering criteria to focus solely on transactions with failed expense exports.

Calculating Subsidiary: In this report, "subsidiary" is the division responsible for each transaction, based on the facility's external ID. Facility ID "376" maps to subsidiary "5," while all others map to subsidiary "1." This helps in financial tracking, operational management, and regulatory compliance. A conditional statement determines the subsidiary based on the facility's external ID, adding context to the order data.

Duplicate Order

Retailers often face the issue of duplicate orders, which can cause inventory discrepancies and financial losses. For instance, if an order is duplicated and subsequently brokered differently, it can result in ATP (Available to Promise) discrepancies and financial losses for the retailer. Furthermore, it may cause errors when reconciling orders with Netsuite.

The "Duplicate Order" report provides crucial insights into the frequency of duplicate orders by presenting the total count of duplicate orders associated with the same order name. By understanding the extent of duplicate orders, retailers can take necessary actions to ensure that duplicate orders are removed from the system.

Glossary

SELECT `ORDER_NAME` AS `ORDER_NAME`,
       `COUNT` AS `COUNT`,
       `HC_Order_Ids` AS `HC_Order_Ids`,
       `Netsuite_Order_Ids` AS `Netsuite_Order_Ids`
FROM
  (SELECT oh.ORDER_NAME,
          COUNT(oh.EXTERNAL_ID) AS COUNT,
          GROUP_CONCAT(oh.ORDER_ID ORDER BY oh.ENTRY_DATE SEPARATOR ', ') AS HC_Order_Ids,
          GROUP_CONCAT(oid.ID_VALUE ORDER BY oh.ENTRY_DATE SEPARATOR ', ') AS Netsuite_Order_Ids,
          MAX(oh.ENTRY_DATE) AS max_entry_date
   FROM order_header oh
   JOIN order_identification oid ON oh.ORDER_ID = oid.ORDER_ID
   AND oid.ORDER_IDENTIFICATION_TYPE_ID = 'NETSUITE_ORDER_ID'
   WHERE oh.STATUS_ID NOT IN ('ORDER_CANCELLED')
     AND oh.ORDER_NAME <> '#11100672002'
   GROUP BY oh.EXTERNAL_ID, oh.ORDER_NAME
   HAVING COUNT(oh.EXTERNAL_ID) > 1) AS virtual_table
ORDER BY max_entry_date DESC
LIMIT 1000;

Query Logic

Data Selection: The query selects four columns: ORDER_NAME, COUNT, HC_Order_Ids, and Netsuite_Order_Ids from the order_header and order_identification tables.

Counting Duplicate Orders: Duplicate orders are identified based on their ORDER_NAME and EXTERNAL_ID, and the COUNT() function calculates the number of occurrences of each unique EXTERNAL_ID.

Concatenation of Order IDs: For each duplicated order, the associated HC_Order_Ids (HotWax Commerce Order IDs) and Netsuite_Order_Ids (NetSuite Order IDs) are concatenated into comma-separated lists using the GROUP_CONCAT() function.

Filtering Criteria: Orders with a status of "ORDER_CANCELLED" and a specific ORDER_NAME are excluded from the analysis.

Grouping and Aggregation: Data is grouped by EXTERNAL_ID and ORDER_NAME to aggregate duplicate orders, and the HAVING clause filters out orders with a count of occurrences less than 1, ensuring that only duplicate orders are included.

Ordering by Entry Date: Results are ordered based on the maximum entry date (max_entry_date) of the duplicated orders in descending order, prioritizing the most recent occurrences of duplicate orders for resolution.

Shopify Order Import Error

When an order fails to transfer from Shopify to Hotwax, it doesn't show up in the OMS, and an error is logged in the EXIM Import record for Shopify orders. The Shopify Order Import Error report details these failed imports, providing the specific errors generated by the system. There are several potential causes for these errors, including data exported from Shopify not being compatible with the OMS, or issues with specific API endpoints, such as inadequate permissions or temporary unavailability. Retailers can use this report to identify problematic orders and troubleshoot accordingly.

Glossary

SELECT `Shopify Website` AS `Shopify Website`,
       `Error message` AS `Error message`,
       `Shopify Order Name` AS `Shopify Order Name`,
       `Order Created Date` AS `Order Created Date`
FROM
  (SELECT site_txt_en AS `Shopify Website`,
          docType,
          errorMessage_txt_en AS `Error message`,
          shopifyOrderName_txt_en AS `Shopify Order Name`,
          tags_txt_en,
          orderCreatedDate_dt AS `Order Created Date`
   FROM logInsights
   WHERE docType ='SHOPIFY_ORDER'
   ORDER BY orderCreatedDate_dt DESC) AS virtual_table
LIMIT 1000;

Query Logic

Data Selection: The query selects specific data from the logInsights table, likely containing information related to Shopify orders, including the Shopify website name, error messages, Shopify order names, tags, and order creation dates.

Defining Selection Criteria: Data is filtered to include only entries where the document type is SHOPIFY_ORDER, ensuring that only relevant Shopify order information is retrieved.

Identification of Shopify Order Errors: Within a subquery (virtual_table), records with a docType of SHOPIFY_ORDER are isolated, capturing entries specific to Shopify order imports. The errorMessage_txt_en field captures system-generated error messages, providing insights into the nature of import failures.

Canceled Order Report

Retailers often face challenges in managing and reducing order cancellations. Customers or CSRs may cancel orders due to reasons such as changes in preference, product unavailability, errors in orders, delivery delays, financial constraints, product defects, better offers elsewhere, and miscommunication. The Cancelled Order Report provides weekly insights into cancellation rates, enabling retailers to pinpoint peak cancellation weeks and address underlying causes. It outlines the total number of canceled orders within the OMS, helping retailers businesses enhance customer satisfaction and operational efficiency by addressing root issues effectively. A weekly report is generated for retailers, and the frequency of the report can be updated as per the requirements.

Glossary

SELECT DATE(DATE_SUB(`CANCELLED_DATE`, INTERVAL DAYOFWEEK(`CANCELLED_DATE`) - 1 DAY)) AS `CANCELLED_DATE`,
       count(DISTINCT `ORDER_ID`) AS `COUNT_DISTINCT(ORDER_ID)`
FROM
  (SELECT oid.ID_VALUE AS ORDER_ID,
          oi.ORDER_ID AS HC_ORDER_ID,
          os.STATUS_DATETIME AS CANCELLED_DATE,
          gi.ID_VALUE AS SKU,
          oi.ITEM_DESCRIPTION,
          oi.UNIT_PRICE AS Price,
          en.DESCRIPTION AS REASON,
          oi.EXTERNAL_ID AS External_Id
   FROM order_item oi
   JOIN order_status os ON oi.ORDER_ID = os.ORDER_ID
   AND oi.ORDER_ITEM_SEQ_ID = os.ORDER_ITEM_SEQ_ID
   AND os.STATUS_ID = 'ITEM_CANCELLED'
   JOIN good_identification gi ON oi.PRODUCT_ID = gi.PRODUCT_ID
   AND gi.GOOD_IDENTIFICATION_TYPE_ID = 'SHOPIFY_PROD_SKU'
   JOIN order_identification oid ON oi.ORDER_ID = oid.ORDER_ID
   AND oid.ORDER_IDENTIFICATION_TYPE_ID = 'SHOPIFY_ORD_NAME'
   LEFT JOIN enumeration en ON os.CHANGE_REASON = en.ENUM_ID
   JOIN order_header oh ON oi.ORDER_ID = oh.ORDER_ID
   AND oh.product_store_id = 'STORE'
   WHERE oi.STATUS_ID = 'ITEM_CANCELLED'
   GROUP BY ORDER_ID,
            HC_ORDER_ID,
            CANCELLED_DATE,
            SKU,
            ITEM_DESCRIPTION,
            Price,
            REASON,
            External_Id
   LIMIT 1000) AS virtual_table
GROUP BY DATE(DATE_SUB(`CANCELLED_DATE`, INTERVAL DAYOFWEEK(`CANCELLED_DATE`) - 1 DAY))
LIMIT 100000;

Query Logic

Data Selection:

• Order IDs and Statuses: The query selects oid.ID_VALUE (order ID) and os.STATUS_DATETIME (cancellation date) to track canceled orders.

• Order Details: Additional information like oi.ITEM_DESCRIPTION (item description), oi.UNIT_PRICE (price), and oi.EXTERNAL_ID (external ID) is also selected.

• Product Information: The query includes gi.ID_VALUE (SKU) to identify specific products.

• Cancellation Reasons: en.DESCRIPTION provides reasons for cancellations.

Defining Cancellation Criteria: The query filters the data to include only orders where the status indicates cancellation (e.g., STATUS_ID = 'ITEM_CANCELLED').

Joining Relevant Tables: The SQL query joins these tables together based on common fields (e.g., ORDER_ID, ORDER_ITEM_SEQ_ID, PRODUCT_ID) to compile a comprehensive dataset.

Selecting Necessary Information: Relevant columns are selected from the joined tables, such as order IDs, cancellation dates, SKUs (product identifiers), item descriptions, prices, cancellation reasons, and external IDs.

Grouping Data: The selected data is grouped based on certain criteria, such as order ID, canceled date, SKU, item description, price, cancellation reason, and external ID, to summarize and organize it for analysis.

Counting Distinct Orders: Within each group, the query counts the number of distinct order IDs, providing insights into how many unique orders were canceled on each date.

Calculating Date: The query calculates the date of the canceled orders by finding the first day of the week for each canceled date, facilitating grouping the cancellations by week.

Order Approval Duration Graph

Order approval delays can happen due to missing order attributes such as missing customer ID or municipio ID. Delays in order approval can lead to missed delivery deadlines and potential revenue loss.

The Order Approval Duration graph provides information on how long it takes the OMS to approve orders. It has two main attributes: order volume and average approval duration in minutes. This Graph helps in tracking the average approval duration over time. This helps in identifying any deviations from expected performance levels and taking corrective actions promptly.

Glossary

SELECT DATE(`ENTRY_DATE`) AS `ENTRY_DATE`,
       AVG(`duration in sec`) / 60 AS `Average approval duration in minutes`,
       count(DISTINCT `Order Id`) AS `Orders Volume`
FROM
  (select oh.ORDER_ID "Order Id",
          oh.ORDER_NAME "Shopify Order Id",
          TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, oh.ORDER_DATE, max(os.status_datetime))), "%H:%i:%s") "Duration",
          TIMESTAMPDIFF(SECOND, oh.ORDER_DATE, max(os.status_datetime)) "duration in sec",
          oh.ENTRY_DATE,
          oh.ORDER_DATE "Created At",
          max(os.status_datetime) "Approved At"
   from order_header oh
   join order_status os on os.ORDER_ID = oh.ORDER_ID
   and os.status_id = "ORDER_APPROVED"
   where os.status_datetime
   group by oh.ORDER_ID
   order by oh.ORDER_ID desc) AS virtual_table
WHERE `Created At` >= STR_TO_DATE('2024-04-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `Created At` < STR_TO_DATE('2024-05-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `ENTRY_DATE` >= STR_TO_DATE('2024-04-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `ENTRY_DATE` < STR_TO_DATE('2024-05-08 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
GROUP BY DATE(`ENTRY_DATE`)
ORDER BY `Average approval duration in minutes` DESC
LIMIT 10000;

Data Selection: The SQL query begins by selecting specific data from two tables: order_header and order_status. These tables likely contain information about orders and their statuses.

Defining Criteria: The query filters the data to include only orders with a specific status, in this case, orders that are approved. This is achieved by specifying the condition os.status_id = 'ORDER_APPROVED'.

Joining Relevant Tables: To combine relevant information from both tables, the query joins order_header and order_status using the common field ORDER_ID.

Selecting Necessary Information: From the joined tables, the query selects columns such as order ID, Shopify order ID, approval duration (calculated in seconds), creation date (ORDER_DATE), and approval date (status_datetime).

Grouping Data: The query groups the selected data by order ID. This means that all entries with the same order ID are grouped together, allowing for aggregation and analysis of approval durations for each order. The query groups the results by the date part of the creation date (ENTRY_DATE), allowing for a summary of average approval durations and order volumes for each day.

Calculating Average Approval Duration: Within each group, the query calculates the average approval duration in minutes (AVG(duration in sec) / 60).

Counting Distinct Orders: Additionally, the query counts the number of distinct order IDs to determine the volume of orders for each day.

Missing Order Attribute Report

The Missing Order Attribute Report is a vital report for tracking order synchronization. By monitoring the presence of essential attributes, it identifies orders lacking crucial information, ensuring a seamless synchronization process. This report enables proactive resolution of discrepancies, preventing any orders from failing to synchronize effectively. In the NetSuite context, the report ensures accurate order synchronization by verifying essential attributes. By highlighting orders lacking these attributes, it prevents synchronization issues, providing assurance that orders seamlessly integrate with NetSuite.

Order attributes are specific pieces of information or data associated with an order that are essential for its processing and synchronization. These attributes can include details such as whether an order has been approved by a fraud detection system (e.g., Signifyd), whether it has been exported to NetSuite, or customer identification numbers. Ensuring these attributes are correctly populated is crucial for smooth order management and integration across different systems.

Glossary

SELECT `ORDER_NAME` AS `ORDER_NAME`,
       `HC_ENTRY_DATE` AS `HC_ENTRY_DATE`,
       `TIME_SINCE_ENTRY` AS `TIME_SINCE_ENTRY`,
       `SALES_CHANNEL_ENUM_ID` AS `SALES_CHANNEL_ENUM_ID`,
       `SIGNIFYD_APPROVED` AS `SIGNIFYD_APPROVED`,
       `NETSUITE_ORDER_EXPORTED` AS `NETSUITE_ORDER_EXPORTED`,
       `NETSUITE_CUSTOMER_ID` AS `NETSUITE_CUSTOMER_ID`
FROM
  (SELECT oh.ORDER_NAME ,
          DATE_FORMAT(oh.ENTRY_DATE, "%m-%d-%Y %h:%i %p") AS HC_ENTRY_DATE ,
          CASE
              WHEN (TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 4 HOUR)) < 60 THEN CONCAT((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 4 HOUR)), ' mins')
              ELSE CONCAT(FLOOR((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 4 HOUR)) / 60), ' hrs ', ((TIMESTAMPDIFF(MINUTE, oh.ENTRY_DATE, NOW() - INTERVAL 4 HOUR)) % 60), ' mins')
          END AS TIME_SINCE_ENTRY ,
          oh.SALES_CHANNEL_ENUM_ID ,
          temp1.SIGNIFYD_APPROVED ,
          temp1.NETSUITE_ORDER_EXPORTED ,
          pi2.ID_VALUE AS NETSUITE_CUSTOMER_ID ,
          oh.ENTRY_DATE
   FROM
     (SELECT oa.ORDER_ID ,
             MAX(CASE
                     WHEN oa.ATTR_NAME = 'SIGNIFYD_APPROVED' THEN oa.ATTR_VALUE
                 END) AS SIGNIFYD_APPROVED ,
             MAX(CASE
                     WHEN oa.ATTR_NAME = 'NETSUITE_ORDER_EXPORTED' THEN oa.ATTR_VALUE
                 END) AS NETSUITE_ORDER_EXPORTED
      FROM order_attribute oa
      WHERE oa.ATTR_NAME IN ('SIGNIFYD_APPROVED',
                             'NETSUITE_ORDER_EXPORTED')
      GROUP BY oa.ORDER_ID) temp1
   JOIN order_header oh ON temp1.ORDER_ID = oh.ORDER_ID
   JOIN order_role or2 ON temp1.ORDER_ID = or2.ORDER_ID
   AND or2.ROLE_TYPE_ID = 'SHIP_TO_CUSTOMER'
   AND (or2.THRU_DATE IS NULL
        OR or2.THRU_DATE > NOW())
   LEFT JOIN party_identification pi2 ON or2.PARTY_ID = pi2.party_id
   AND pi2.PARTY_IDENTIFICATION_TYPE_ID = 'NETSUITE_CUSTOMER_ID'
   WHERE oh.STATUS_ID = 'ORDER_CREATED'
     AND oh.SALES_CHANNEL_ENUM_ID NOT IN ('LOOP_EXCH')
     AND (SIGNIFYD_APPROVED IS NULL
          OR NETSUITE_ORDER_EXPORTED IS NULL)
   ORDER BY oh.ENTRY_DATE) AS virtual_table
ORDER BY `ENTRY_DATE` ASC
LIMIT 1000;

Query Logic

Data Selection: The SQL query starts by selecting specific data fields relevant to the report. This includes information about orders and their attributes.

Defining Missing Attributes: The primary objective of the report is to identify orders with missing attributes. Therefore, criteria are established to filter the data and include only orders where certain attributes are absent or null.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins tables containing order information and attributes. This typically involves joining the order_header table with the order_attribute table based on a common field like ORDER_ID.

Selecting Necessary Information: From the joined tables, the query selects columns related to order attributes and any other relevant information needed for analysis or reporting.

Filtering Data: Conditions are applied to filter the data and include only orders with missing attributes. This involves checking if certain attribute values are null or absent in the dataset.

Grouping Data: The query groups the selected data based on certain criteria, such as order ID or attribute type. Grouping data helps summarize and organize it for analysis.

Counting Missing Attributes: Within each group, the query counts the number of orders with missing attributes. This provides insights into the frequency and distribution of missing attributes across orders.

Allocation Pending Report

The Allocation Pending Report displays orders assigned to facilities that currently lack sufficient inventory for immediate fulfillment. Monitoring the orders and items listed in this report is essential for initiating replenishment actions at the store. By doing so, retailers can ensure that inventory levels are maintained adequately, facilitating the successful completion of fulfillment for the identified orders.

Glossary

Custom Gift Card Report

Sometimes store associates place a custom gift card order that is not mapped to the parent product in Shopify and NetSuite. In such cases, retailers face synchronization issues between Shopify and NetSuite due to unmapped gift cards. The custom gift card report serves as a solution to address these synchronization issues. This report shows unmapped products, enabling retailers to manually map these products to NetSuite, inventory them, and associate them with NetSuite IDs.

Glossary

SELECT `ORDER_ID` AS `ORDER_ID`,
       `ORDER_NAME` AS `ORDER_NAME`,
       `PRODUCT_ID` AS `PRODUCT_ID`,
       `PRODUCT_TYPE_ID` AS `PRODUCT_TYPE_ID`,
       `CREATED_DATE` AS `CREATED_DATE`,
       `PRODUCT_NAME` AS `PRODUCT_NAME`
FROM
  (SELECT OH.ORDER_ID,
          OH.ORDER_NAME,
          P.PRODUCT_ID,
          P.PRODUCT_TYPE_ID,
          P.PRODUCT_NAME,
          P.CREATED_DATE
   FROM ORDER_HEADER OH
   JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID
   JOIN PRODUCT P ON OI.PRODUCT_ID = P.PRODUCT_ID
   WHERE P.PRODUCT_TYPE_ID = 'DIGITAL_GOOD'
     AND P.PRODUCT_ID NOT IN
       (SELECT pa.product_id_to
        FROM product_assoc pa
        WHERE pa.product_id = "53051")) AS virtual_table
WHERE `CREATED_DATE` >= STR_TO_DATE('2024-05-03 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `CREATED_DATE` < STR_TO_DATE('2024-05-10 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `PRODUCT_NAME` != 'Test'
LIMIT 1000;

Query Logic

Data Selection: The query selects data from the ORDER_HEADER, ORDER_ITEM, and PRODUCT tables. These tables contain information about orders, order items, and products, respectively. By joining these tables and selecting specific fields, the query retrieves necessary details related to custom gift card orders.

Defining Criteria: The query establishes specific criteria to identify custom gift card orders. It filters products based on their product type, ensuring only digital goods are considered. Then, it excludes products associated with a particular ID that is used for gift cards. This criterion ensures that the report focuses exclusively on digital goods matching the characteristics of custom gift cards which are not linked with the ID of the gift cards.

Selecting Necessary Information: Essential information such as order IDs, order names, product IDs, product type IDs, product names, and creation dates is retrieved. This selection provides comprehensive insights into custom gift card orders, enabling retailers to track and analyze their performance over time.

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;

Query Logic

Data Selection: The SQL query retrieves data from the shipment table along with relevant attributes and statuses. It selects fields such as received date, origin and destination facility IDs, expected quantity, shipment ID, transfer order, SKU, received quantity, difference, and status. These fields provide comprehensive information about the receiving process for inbound transfers.

Filtering Criteria: To generate the report for receiving, the query applies specific filtering criteria:

• It selects inbound transfer shipments with a status of PURCH_SHIP_RECEIVED, indicating that they have been received.

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

• Additionally, only shipments with an external order name attribute are considered, indicating that they are part of a transfer order.

Column Selection: Columns such as received date, origin and destination facility IDs, expected quantity, shipment ID, transfer order, SKU, received quantity, difference, and status are selected. These columns provide insights into the receiving process for inbound transfers, allowing users to track and manage received quantities effectively.

Calculating Difference and Status: The query calculates the difference between the expected and received quantities for each shipment item. Based on this difference and other conditions such as the presence of an external order name and the status of the shipment, it assigns a status to each item indicating whether it was received correctly, over-received, under-received, or not received at all.

Grouping Data: The query groups the selected data by various attributes such as received date, origin and destination facility IDs, expected quantity, shipment ID, transfer order, SKU, received quantity, difference, and status. Grouping the data helps organize it logically and facilitates analysis of the receiving process for inbound transfers.

Completed cycle report

This report helps retailers maintain accurate inventory records and ensure operational efficiency. By detailing the expected quantity of items, the quantity counted, and any variances discovered during the counting process, this report provides insights into the accuracy of inventory levels. Moreover, it records essential information such as who conducted the cycle count, the facility where it was performed, as well as the submission and completion dates.

This Report allows retailers to identify discrepancies between expected and actual inventory levels promptly, enabling timely investigation and resolution of any issues such as theft, damage, or discrepancies in record-keeping. This helps in maintaining inventory accuracy, which is important for optimizing stocking levels, preventing stockouts or overstock situations, and ultimately improving customer satisfaction.

The submission and completion dates listed in the report provide insights into the efficiency of the inventory management process. Retailers can track how quickly cycle counts are conducted and completed, allowing them to assess the effectiveness of their inventory management practices and identify areas for improvement.

User

Head of Store, Vice President of Retail

Report Glossary

Open Transfer order shipment

This report provides insights into the movement of inventory within the supply chain. It offers an overview of the transfer process by presenting shipment IDs, origin and destination facilities, tracking numbers, and pending quantities for reception.

it enables efficient inventory management by identifying precisely which products are in transit and yet to be received, allowing retailers to anticipate stock availability accurately.

User

Head of Store, Vice President of Retail

Glossary

POS orders vs POS variances

Retailers require this report to effectively manage their inventory and streamline operations. By tracking POS-completed orders and ensuring that the same inventory variance is logged into the ERP system the retailer gains insights into any possible inventory discrepancy.

This report provides information about the quantity variance between pos orders and pos variance. Retailers can identify the problematic orders with incorrect inventory variance and resolve the errors, if any.

User

Head of Store, Vice President of Retail

Glossary

Shipment by Tracking Number Report

The Shipment by Tracking Number Report is valuable for retailers as it shows information such as Shopify ID, Tracking Number, Shipping Method, Shipping Location, Destination Address, and Units Shipped.

Retailers require a report that can help them track and manage their shipments, ensuring transparency and accountability throughout the Shipment process. By having a clear overview of which orders have been shipped, where they are in transit, and their expected delivery destinations, retailers can optimize their supply chain.

User

Head of Store, Vice President of Retail

Glossary

Pending Cycle Count report

The Pending Cycle Count report presents products awaiting counting at a facility where a recent cycle count has occurred. Inactive products, characterized by a zero inventory that has remained unchanged for the last month, are excluded to ensure that only active products are included in the report. This selective approach aims to provide an accurate representation of products requiring attention for inventory verification and reconciliation.

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.

Missing Products from Order

This report identifies active orders and the products associated with it that are missing from NetSuite. It checks each order to see if it has a corresponding NetSuite Order ID and if the products within those orders have NetSuite Product IDs. Orders without a NetSuite Order ID and products without a NetSuite Product ID are highlighted. The query excludes canceled orders, joins relevant tables to gather necessary data, and groups it by order ID and product ID.

Glossary

SELECT order_id AS order_id,
       product_sku_not_in_netsuite AS product_sku_not_in_netsuite
FROM
  (SELECT oh.ORDER_NAME AS order_id ,
          p.INTERNAL_NAME AS product_sku_not_in_netsuite
   FROM order_header oh
   JOIN order_item oi ON oh.ORDER_ID = oi.ORDER_ID
   JOIN product p ON oi.PRODUCT_ID = p.PRODUCT_ID
   LEFT JOIN order_identification oi2 ON oh.ORDER_ID = oi2.ORDER_ID
   AND oi2.ORDER_IDENTIFICATION_TYPE_ID = 'NETSUITE_ORDER_ID'
   AND (oi2.THRU_DATE IS NULL
        OR oi2.THRU_DATE > NOW())
   LEFT JOIN good_identification gi ON oi.PRODUCT_ID = gi.PRODUCT_ID
   AND gi.GOOD_IDENTIFICATION_TYPE_ID = 'NETSUITE_PRODUCT_ID'
   AND (gi.THRU_DATE IS NULL
        OR gi.THRU_DATE > NOW())
   WHERE oh.STATUS_ID NOT IN ('ORDER_CANCELLED')
     AND oi2.ORDER_ID IS NULL
     AND gi.PRODUCT_ID IS NULL
   GROUP BY oh.ORDER_ID ,
            oi.PRODUCT_ID) AS virtual_table
LIMIT 1000;

Query Logic

Data Selection: The query starts by selecting data from various tables related to orders and products. These tables contain details about orders, order items, product information, and NetSuite identifiers.

Filtering Active Orders: The main focus of the report is to identify orders and products missing NetSuite IDs. Therefore, criteria are set to exclude canceled orders (e.g., STATUS_ID NOT IN ('ORDER_CANCELLED')) and only include active ones.

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins multiple tables: order headers, order items, and product details. Additionally, it joins to tables containing NetSuite order and product identifiers using common fields (e.g., ORDER_ID and PRODUCT_ID).

Selecting Necessary Information: The query selects specific columns from the joined tables that include order IDs and product SKUs that are not present in NetSuite.

Grouping Data: The selected data is grouped by order ID and product ID to ensure that each combination is considered individually. This helps in organizing the data for further analysis.

Identifying Missing NetSuite IDs: Within each group, the query checks if there are any corresponding NetSuite Order IDs or Product IDs. If both are missing, it highlights these records as the ones missing from NetSuite.

Products without Netsuite Order ID Report

This report identifies products listed on Shopify that are missing from NetSuite. It checks each product to see if it has a corresponding NetSuite Product ID. Products without a NetSuite Product ID are highlighted. The query joins relevant tables to gather necessary data, filters for active products, and groups it by product SKU, Shopify product ID, and Hotwax product ID.

Glossary

SELECT sku AS sku,
       shopify_product_id AS shopify_product_id,
       hotwax_product_id AS hotwax_product_id
FROM
  (SELECT p.INTERNAL_NAME AS sku ,
          gi.ID_VALUE AS shopify_product_id ,
          gi.PRODUCT_ID AS hotwax_product_id
   FROM good_identification gi
   LEFT JOIN good_identification gi2 ON gi.PRODUCT_ID = gi2.PRODUCT_ID
   AND gi2.GOOD_IDENTIFICATION_TYPE_ID = 'NETSUITE_PRODUCT_ID'
   AND (gi2.THRU_DATE IS NULL
        OR gi2.THRU_DATE > NOW())
   JOIN product p ON gi.PRODUCT_ID = p.PRODUCT_ID
   WHERE gi.GOOD_IDENTIFICATION_TYPE_ID = 'SHOPIFY_PROD_ID'
     AND (gi.THRU_DATE IS NULL
          OR gi.THRU_DATE > NOW())
     AND p.IS_VIRTUAL = 'N'
     AND p.IS_VARIANT = 'Y'
     AND gi2.PRODUCT_ID IS NULL
     AND gi.ID_VALUE <> p.INTERNAL_NAME) AS virtual_table
LIMIT 1000;

Query Logic

Data Selection: The query starts by selecting data from tables related to product identifications. These tables store information about product SKUs, Shopify product IDs, and internal Hotwax product IDs. The query selects specific columns that include the product SKU, Shopify product ID, and HotWax product ID.

Filtering Active Products: The main focus of the report is to identify Shopify products missing NetSuite IDs. Criteria are set to include only active product identifications (those with no end date or an end date in the future).

Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins multiple tables: good identifications (for product IDs) and products (for additional product details). It also joins the good identifications table to check for NetSuite Product IDs.

Grouping Data: The selected data is grouped by product ID and identification type to ensure that each product's information is uniquely considered.

Identifying Missing NetSuite IDs: Within each group, the query checks for missing NetSuite Product IDs. If a product does not have a corresponding NetSuite ID, it is highlighted.

Deleted Shopify Product Report

This SQL query generates a report listing products that have been deleted from Shopify. It selects key details such as the Hotwax product ID, product SKU, the date when the product was discontinued in Hotwax, and any comments related to the deletion. The query specifically looks for products with comments indicating they were "Deleted from Shopify." It then groups the data by product ID, SKU, discontinuation date, and comments to ensure each product's details are uniquely considered.

Glossary

SELECT hotwax_product_id,
       product_sku,
       hotwax_discontinuation_date,
       COMMENTS AS `My column`
FROM
  (SELECT p.PRODUCT_ID AS hotwax_product_id ,
          p.INTERNAL_NAME AS product_sku ,
          DATE_FORMAT(p.SUPPORT_DISCONTINUATION_DATE, "%m-%d%-%Y") AS hotwax_discontinuation_date ,
          p.COMMENTS
   FROM product p
   WHERE p.COMMENTS LIKE "%Deleted from Shopify:%") AS virtual_table
GROUP BY hotwax_product_id,
         product_sku,
         hotwax_discontinuation_date,
         COMMENTS
LIMIT 1000;

Query Logic

Data Selection: The query starts by selecting data from the product table, focusing on products deleted from Shopify. It retrieves information such as Hotwax product ID, product SKU, discontinuation date, and comments.

Filtering Criteria: To identify products deleted from Shopify, the query filters records where the comments field contains the phrase "Deleted from Shopify". This ensures that only relevant products are included in the report.

Grouping Data: The data is grouped by Hotwax product ID, product SKU, discontinuation date, and comments. Grouping helps organize the data and ensures each product's information is considered uniquely, avoiding duplicate entries in the report.

Customer Without Netsuite Id Report

This SQL query generates a report listing customers who have a Shopify customer ID but are missing a corresponding NetSuite customer ID. It fetches details including the Hotwax customer ID, first name, last name, Shopify customer ID, and the number of orders placed by each customer. By joining relevant tables and applying specific filters, the query identifies and provides a concise list of customers not present in the NetSuite system.

SELECT hotwax_customer_id AS hotwax_customer_id,
       `FIRST_NAME` AS `FIRST_NAME`,
       `LAST_NAME` AS `LAST_NAME`,
       shopify_customer_id AS shopify_customer_id,
       order_count AS order_count
FROM
  (SELECT p.PARTY_ID as hotwax_customer_id ,
          p2.FIRST_NAME ,
          p2.LAST_NAME ,
          pi3.ID_VALUE AS shopify_customer_id ,
          COUNT(DISTINCT or2.ORDER_ID) AS order_count
   FROM party p
   JOIN party_role pr ON p.PARTY_ID = pr.PARTY_ID
   JOIN party_identification pi3 ON p.PARTY_ID = pi3.PARTY_ID
   AND pi3.PARTY_IDENTIFICATION_TYPE_ID = 'SHOPIFY_CUST_ID'
   LEFT JOIN party_identification pi2 ON p.PARTY_ID = pi2.PARTY_ID
   AND pi2.PARTY_IDENTIFICATION_TYPE_ID = 'NETSUITE_CUSTOMER_ID'
   LEFT JOIN person p2 ON p.PARTY_ID = p2.PARTY_ID
   LEFT JOIN order_role or2 ON p.PARTY_ID = or2.PARTY_ID
   AND or2.ROLE_TYPE_ID = 'SHIP_TO_CUSTOMER'
   AND (or2.THRU_DATE IS NULL
        OR or2.THRU_DATE > NOW())
   WHERE p.PARTY_TYPE_ID = 'PERSON'
     AND pr.ROLE_TYPE_ID = 'CUSTOMER'
     AND pi2.PARTY_ID IS NULL
   GROUP BY p.PARTY_ID) AS virtual_table
GROUP BY hotwax_customer_id,
         `FIRST_NAME`,
         `LAST_NAME`,
         shopify_customer_id,
         order_count
LIMIT 1000;

Query Logic

Data Selection: The query starts by selecting data from the party table and related tables to gather customer details, including the Hotwax customer ID, first name, last name, Shopify customer ID, and the number of orders.

Filtering Criteria: To focus on customers without a NetSuite ID, the query filters records to include only those with a Shopify ID and excludes those with a NetSuite ID. It also ensures that only individuals (not organizations) and those with a customer role are included.

Joining Relevant Tables: The query joins several tables to collect all necessary information. It joins the party table with the party role table to determine customer roles, the party identification table to check for Shopify and NetSuite IDs, the person table to get names, and the order role table to count orders.

Grouping Data: The data is grouped by Hotwax customer ID, first name, last name, and Shopify customer ID to ensure that each customer is uniquely identified in the report.

Counting Orders: Within each group, the query counts the number of distinct orders for each customer. This count provides insights into the customer's activity.

Fulfilled Items Not Synced to NetSuite

This report identifies fulfilled order items that have not been synced to NetSuite. The report focuses on items marked as completed, joining relevant data from multiple tables to provide a comprehensive view. Items that have not yet been exported to NetSuite are highlighted, ensuring that all discrepancies are captured. The results are organized by fulfillment log ID and status datetime.

Glossary

SELECT `ORDER_ID` AS `ORDER_ID`,
       `ORDER_NAME` AS `ORDER_NAME`,
       `ORDER_TYPE_ID` AS `ORDER_TYPE_ID`,
       `ORDER_ITEM_SEQ_ID` AS `ORDER_ITEM_SEQ_ID`,
       `SKU` AS `SKU`,
       `STATUS_DATETIME` AS `STATUS_DATETIME`,
       `IS_FULFILLMENT_EXPORTED` AS `IS_FULFILLMENT_EXPORTED`
FROM
  (select oh.ORDER_ID ,
          oh.ORDER_TYPE_ID ,
          oh.ORDER_NAME ,
          oi.ORDER_ITEM_SEQ_ID ,
          p.INTERNAL_NAME SKU ,
          os.STATUS_DATETIME ,
          ofh.FULFILLMENT_LOG_ID ,
          ofh.EXTERNAL_FULFILLMENT_ID ,
          if(ofh.FULFILLMENT_LOG_ID is not null, "Y", "N") IS_FULFILLMENT_EXPORTED
   from order_header oh
   join order_item oi on oh.ORDER_ID = oi.ORDER_ID
   join product p on p.PRODUCT_ID = oi.PRODUCT_ID
   left join order_status os on os.ORDER_ID = oi.ORDER_ID
   and os.ORDER_ITEM_SEQ_ID = oi.ORDER_ITEM_SEQ_ID
   and os.STATUS_ID = "ITEM_COMPLETED"
   left join order_fulfillment_history ofh on ofh.ORDER_ID = oi.ORDER_ID
   and ofh.ORDER_ITEM_SEQ_ID = oi.ORDER_ITEM_SEQ_ID
   where oi.STATUS_ID = "ITEM_COMPLETED") AS virtual_table
WHERE `IS_FULFILLMENT_EXPORTED` IN ('N')
ORDER BY `FULFILLMENT_LOG_ID` ASC,
         `STATUS_DATETIME` DESC
LIMIT 1000;

Query Logic

Data Selection: The query starts by selecting data from various tables related to orders and order items. It retrieves key details including the order ID, order name, order type, order item sequence ID, SKU, status datetime, and a flag indicating whether the fulfillment items has been exported to NetSuite.

Filtering for Completed Items: The query focuses on order items that have been marked as completed. It joins to the order status table to ensure that only items with a status ID of "ITEM_COMPLETED" are included.

Identifying Unexported Fulfillment Items: The query joins to the order fulfillment history table to check if each fulfilled item has been exported to NetSuite. It sets a flag (IS_FULFILLMENT_EXPORTED) to "N" if the fulfillment log ID is null, indicating that the item has not been exported.

POS Orders vs POS Variance

This report compares the sales of POS (Point of Sale) orders with inventory variances, helping to identify discrepancies. It provides details such as order ID, order name, entry date, SKU, sales quantity, and quantity on hand (QOH) variance. The report focuses on POS orders completed after a particular date, and includes both sales data and inventory variance information. By joining relevant tables, it highlights orders where there is a variance between the recorded sales and the inventory quantity on hand.

Glossary

SELECT `ORDER_ID` AS `ORDER_ID`,
       `ORDER_NAME` AS `ORDER_NAME`,
       `ENTRY_DATE` AS `ENTRY_DATE`,
       `SKU` AS `SKU`,
       `SALES` AS `SALES`,
       IFNULL(`QOH`, 0) AS `QOH variance`
FROM
  (SELECT oh.ORDER_ID,
          oh.ORDER_NAME,
          oh.ENTRY_DATE,
          sale.PRODUCT_ID,
          p.INTERNAL_NAME SKU,
          sale.SALES,
          variance.QOH,
          variance.ATP,
          variance.INVENTORY_ID
   FROM order_header oh
   JOIN
     (SELECT oi.ORDER_ID,
             SUM(oi.QUANTITY) SALES,
             oi.PRODUCT_ID
      FROM order_item oi
      JOIN order_item_ship_group_assoc oisga ON oisga.ORDER_ID = oi.ORDER_ID
      AND oisga.ORDER_ITEM_SEQ_ID = oi.ORDER_ITEM_SEQ_ID
      JOIN order_item_ship_group oisg ON oisg.ORDER_ID = oisga.ORDER_ID
      AND oisg.SHIP_GROUP_SEQ_ID = oisga.SHIP_GROUP_SEQ_ID
      AND oisg.SHIPMENT_METHOD_TYPE_ID = 'POS_COMPLETED'
      GROUP BY oi.ORDER_ID,
               oi.PRODUCT_ID) sale ON sale.ORDER_ID = oh.ORDER_ID
   AND oh.ENTRY_DATE > '2024-03-24'
   AND oh.SALES_CHANNEL_ENUM_ID = 'POS_SALES_CHANNEL'
   LEFT JOIN
     (SELECT iiv.COMMENTS,
             ii.PRODUCT_ID,
             ii.INVENTORY_ITEM_ID INVENTORY_ID,
             SUM(iiv.quantity_on_hand_var) QOH,
             SUM(iiv.available_to_promise_var) ATP
      FROM inventory_item_variance iiv
      JOIN inventory_item ii ON ii.INVENTORY_ITEM_ID = iiv.INVENTORY_ITEM_ID
      AND iiv.VARIANCE_REASON_ID = 'POS_SALE'
      GROUP BY iiv.COMMENTS,
               ii.PRODUCT_ID) variance ON variance.COMMENTS LIKE CONCAT('%', sale.ORDER_ID, '%')
   AND variance.PRODUCT_ID = sale.PRODUCT_ID
   JOIN product p on sale.PRODUCT_ID = p.PRODUCT_ID) AS virtual_table
WHERE `SKU` != '9090909-999'
  AND (((IF(`QOH`, `QOH`, 0) + `SALES`) <> 0))
ORDER BY `ORDER_ID` DESC
LIMIT 1000;

Query Logic

Data Selection: The query selects data from multiple tables to gather information about POS orders, their sales quantities, and inventory variances. The key details retrieved include order ID, order name, entry date, SKU, sales quantity, and quantity on hand variance.

Filtering POS Orders: The report focuses on POS orders by filtering for orders completed after a particular date, and belonging to the POS sales channel. This ensures the report includes only relevant transactions.

Calculating Sales and Variance: The query calculates the total sales quantity for each order and product combination. It also sums the quantity on hand (QOH) and available to promise (ATP) variances from the inventory item variance table. The inventory variances are linked to the sales data based on comments containing the order ID.

Filtering Results: The results are filtered to exclude a specific SKU and to highlight records where the sum of QOH variance and sales is not zero, indicating a discrepancy.

Ordering and Limiting Results: The results are ordered by order ID in descending order to prioritize the most recent records. The output is limited to the first 1000 records.

POS Returns vs Restock

This report identifies POS (Point of Sale) return transactions and compares them against restocked quantities. It provides insights into how many items were returned versus how many were restocked back into inventory. The report includes details such as return ID, entry date, order ID, product name, facility ID, returned quantity, and restocked quantity. It focuses on returns directed to specific facilities and excludes those directed to 'BDC'.

Glossary

SELECT `RETURN_ID` AS `RETURN_ID`,
       `ENTRY_DATE` AS `ENTRY_DATE`,
       `ORDER_NAME` AS `ORDER_ID`,
       `INTERNAL_NAME` AS `INTERNAL_NAME`,
       `FACILITY_ID` AS `FACILITY_ID`,
       `RETURN_QUANTITY` AS `RETURNED_QUANTITY`,
       IFNULL(`RECEIVED_QUANTITY`, 0) AS `RESTOCKED_QUANTITY`
FROM
  (SELECT ri.RETURN_ID,
          ri.RETURN_ITEM_SEQ_ID,
          p.INTERNAL_NAME,
          ri.RETURN_QUANTITY,
          ri.RECEIVED_QUANTITY,
          ri.STATUS_ID,
          rh.DESTINATION_FACILITY_ID FACILITY_ID,
          rh.RETURN_CHANNEL_ENUM_ID,
          rh.ENTRY_DATE,
          oh.ORDER_NAME,
          oh.ORDER_ID
   FROM return_header rh
   JOIN return_item ri ON ri.return_id = rh.return_id
   JOIN product p ON p.product_id = ri.product_id
   JOIN order_header oh ON oh.order_id = ri.order_id
   WHERE rh.DESTINATION_FACILITY_ID <> "_NA_") AS virtual_table
WHERE `FACILITY_ID` != 'BDC'
LIMIT 1000;

Query Logic

Data Selection: The query begins by selecting data from various tables that hold information on return transactions, products, and orders. Key details include return ID, entry date, order name, internal product name (SKU), facility ID, returned quantity, and restocked quantity.

**Filtering Return Transactions:*8 The report specifically looks at returns that are not directed to the generic facility 'NA'. This helps in focusing on relevant return transactions.

Joining Relevant Tables: To gather comprehensive data, the SQL query joins multiple tables. The return header table (rh) contains general return information such as destination facility ID and entry date. The return item table (ri) provides details on individual returned items and their quantities. The product table (p) includes product details such as internal name (SKU), and the order header table (oh) links returns to their corresponding orders.

Calculating Return and Restock Quantities: The query selects the returned quantity from the return item table and checks for any restocked quantity, using IFNULL to handle cases where no restock occurred, setting it to zero if not restocked.

Excluding Specific Facilities: The results are filtered to exclude transactions directed to the 'BDC' facility, ensuring the report focuses on the relevant data.

Query Logic

Data Selection: The query starts by selecting relevant data from tables that store information on return transactions, products, and orders. It gathers details such as return ID, product name (SKU), returned quantity, restocked quantity, facility ID, and order name.

Filtering Return Transactions: The report focuses on returns directed to specific facilities, excluding those directed to the generic 'NA' facility.

Categorizing Return Status: The query categorizes each returned item as "Restocked" if the returned quantity matches the received quantity. If they do not match, the item is categorized as "Not Restocked". This is achieved using the IF and IFNULL functions.

Counting and Grouping Data: The data is grouped by the restocked status and counted, providing the number of items in each category ("Restocked" vs. "Not Restocked"). This count is crucial for generating the pie chart.

Missing Order Attribute Report

The Missing Order Attribute Report is a vital tool for tracking order synchronization. By monitoring the presence of essential attributes, it identifies orders lacking crucial information, ensuring a seamless synchronization process. This report enables proactive resolution of discrepancies, preventing any orders from failing to synchronize effectively.

Glossary

In the NetSuite context, the report ensures accurate order synchronization by verifying essential attributes. By highlighting orders lacking these attributes, it prevents synchronization issues, providing assurance that orders seamlessly integrate with NetSuite.

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

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

SELECT `Shopify Order ID` AS `Shopify Order ID`,
       `Location` AS `Location`,
       `HC Order Id` AS `HC Order Id`,
       `Style` AS `Style`,
       `SKU` AS `SKU`,
       `Product Name` AS `Product Name`,
       `ATP` AS `ATP`,
       `Safety Stock ` AS `Safety Stock `,
       `Rejected_Datetime` AS `Rejected_Datetime`,
       `Reason` AS `Reason`,
       `Comments` AS `Comments`
FROM
  (SELECT OFC.FROM_FACILITY_ID AS 'Location',
          OH.ORDER_NAME AS 'Shopify Order ID',
          OH.ORDER_ID AS 'HC Order Id',
          OI.ITEM_DESCRIPTION AS 'Style',
     (SELECT giupca.ID_VALUE
      FROM good_identification giupca
      WHERE oi.PRODUCT_ID = giupca.PRODUCT_ID
        AND giupca.GOOD_IDENTIFICATION_TYPE_ID = 'UPCA'
        AND (giupca.THRU_DATE > NOW() OR giupca.THRU_DATE IS NULL)
      LIMIT 1) AS SKU,
          P.PRODUCT_NAME AS 'Product Name',
          PF.LAST_INVENTORY_COUNT AS 'ATP',
          PF.MINIMUM_STOCK AS 'Safety Stock ',
          OFC.CHANGE_DATETIME AS Rejected_Datetime,
          OFC.CHANGE_REASON_ENUM_ID AS 'Reason',
          OFC.COMMENTS AS 'Comments',
                       F.FACILITY_ID AS FACILITY_ID,
                       OS.STATUS_DATETIME AS COMPLETED_DATETIME
   FROM ORDER_HEADER OH
   INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID
   INNER JOIN ORDER_FACILITY_CHANGE OFC ON OFC.ORDER_ID = OI.ORDER_ID
   AND OFC.ORDER_ITEM_SEQ_ID = OI.ORDER_ITEM_SEQ_ID
   AND OFC.CHANGE_REASON_ENUM_ID NOT IN ('BROKERED', 'UNFILLABLE', 'RELEASED')
   INNER JOIN PRODUCT P ON P.PRODUCT_ID = OI.PRODUCT_ID
   INNER JOIN PRODUCT_FACILITY PF ON PF.PRODUCT_ID = OI.PRODUCT_ID
   AND PF.FACILITY_ID = OFC.FROM_FACILITY_ID
   INNER JOIN FACILITY F ON PF.FACILITY_ID = F.FACILITY_ID
   AND F.FACILITY_TYPE_ID IN ('WAREHOUSE', 'RETAIL_STORE')
   LEFT JOIN ORDER_STATUS OS ON OS.ORDER_ID = OI.ORDER_ID
   AND OS.ORDER_ITEM_SEQ_ID = OI.ORDER_ITEM_SEQ_ID
   AND OS.STATUS_ID = "ITEM_COMPLETED"
   WHERE OH.PRODUCT_STORE_ID = 'STORE'
   ORDER BY OFC.CHANGE_DATETIME DESC) AS virtual_table
WHERE `Rejected_Datetime` >= STR_TO_DATE('2024-05-06 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `Rejected_Datetime` < STR_TO_DATE('2024-05-13 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
LIMIT 50000;

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

sql
SELECT `HC_ORDER_ID` AS `HC_ORDER_ID`,
       `SHOPIFY_ORDER_ID` AS `SHOPIFY_ORDER_ID`,
       `LOCATION` AS `LOCATION`,
       `UPC` AS `UPC`,
       `ITEM_DESCRIPTION` AS `ITEM_DESCRIPTION`,
       cast(BROKERED_DATE as date) AS `BROKERED_DATE`,
       date_format(BROKERED_DATE, "%H:%i %p") AS `BROKERED_TIME`,
       `WMI_SENT_DATE` AS `WMI_SENT_DATE`,
       `WMI_SENT_TIME` AS `WMI_SENT_TIME`,
       `CUT_OFF` AS `CUT_OFF`,
       `HC_PRODUCT_STORE_ID` AS `HC_PRODUCT_STORE_ID`
FROM
  (SELECT OH.ORDER_ID AS 'HC_ORDER_ID',
          IFNULL(
                   (SELECT OID.ID_VALUE
                    FROM ORDER_IDENTIFICATION OID
                    WHERE OID.ORDER_ID = OH.ORDER_ID
                      AND ORDER_IDENTIFICATION_TYPE_ID = 'SHOPIFY_ORD_NAME'
                      AND (OID.THRU_DATE > NOW()
                           OR OID.THRU_DATE IS NULL) ), '-') AS 'SHOPIFY_ORDER_ID',
          OISG.FACILITY_ID AS 'LOCATION',
          (SELECT DISTINCT(GI.ID_VALUE)
           FROM GOOD_IDENTIFICATION GI
           WHERE GI.GOOD_IDENTIFICATION_TYPE_ID = 'UPCA'
             AND GI.PRODUCT_ID = OI.PRODUCT_ID
             AND (GI.THRU_DATE IS NULL
                  OR GI.THRU_DATE > NOW())
           ORDER BY GI.FROM_DATE DESC
           LIMIT 1) AS 'UPC',
          CONCAT(OI.ITEM_DESCRIPTION, ' ',
                 (SELECT PF.DESCRIPTION
                  FROM PRODUCT_FEATURE PF, PRODUCT_FEATURE_APPL PFA
                  WHERE PFA.PRODUCT_ID = OI.PRODUCT_ID
                    AND PFA.PRODUCT_FEATURE_APPL_TYPE_ID = 'STANDARD_FEATURE'
                    AND PFA.PRODUCT_FEATURE_ID = PF.PRODUCT_FEATURE_ID
                    AND PF.PRODUCT_FEATURE_TYPE_ID = 'SIZE'
                    AND (PFA.THRU_DATE > NOW()
                         OR PFA.THRU_DATE IS NULL) )) AS 'ITEM_DESCRIPTION',
          CASE
              WHEN (OFC.CHANGE_DATETIME IS NULL
                    AND OISG.SHIPMENT_METHOD_TYPE_ID = 'STOREPICKUP') THEN OISG.CREATED_STAMP
              ELSE OFC.CHANGE_DATETIME
          END AS 'BROKERED_DATE',
          null AS 'WMI_SENT_DATE',
          null AS 'WMI_SENT_TIME',
          case
              WHEN (OFC.CHANGE_DATETIME IS NULL
                    AND OISG.SHIPMENT_METHOD_TYPE_ID = 'STOREPICKUP')
                   AND cast(OISG.CREATED_STAMP as date) < cast(NOW() as date) THEN 'BEFORE TODAY'
              when cast(OFC.CHANGE_DATETIME as date) < cast(NOW() as date) then 'BEFORE TODAY'
              WHEN (OFC.CHANGE_DATETIME IS NULL
                    AND OISG.SHIPMENT_METHOD_TYPE_ID = 'STOREPICKUP')
                   AND cast(OISG.CREATED_STAMP as date) = cast(NOW() as date)
                   and HOUR(OISG.CREATED_STAMP) <= 14 then 'BEFORE 2PM Local Time'
              when cast(OFC.CHANGE_DATETIME as date) = cast(NOW() as date)
                   and HOUR(OFC.CHANGE_DATETIME) <= 14 then 'BEFORE 2PM Local Time'
              else 'AFTER 2PM LOCAL TIME'
          end 'CUT_OFF',
              OH.PRODUCT_STORE_ID AS 'HC_PRODUCT_STORE_ID'
   FROM ORDER_HEADER OH
   INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID
   INNER JOIN ORDER_ITEM_SHIP_GROUP OISG ON OISG.ORDER_ID = OI.ORDER_ID
   and OISG.SHIP_GROUP_SEQ_ID = OI.SHIP_GROUP_SEQ_ID
   LEFT JOIN ORDER_FACILITY_CHANGE OFC ON OFC.ORDER_ID = OI.ORDER_ID
   AND OFC.ORDER_ITEM_SEQ_ID = OI.ORDER_ITEM_SEQ_ID
   AND OFC.SHIP_GROUP_SEQ_ID = OI.SHIP_GROUP_SEQ_ID
   AND OFC.CHANGE_DATETIME =
     (SELECT MAX(OFC1.CHANGE_DATETIME)
      FROM ORDER_FACILITY_CHANGE OFC1
      WHERE OFC1.ORDER_ID = OI.ORDER_ID
        AND OFC1.SHIP_GROUP_SEQ_ID = OI.SHIP_GROUP_SEQ_ID
        AND OFC1.ORDER_ITEM_SEQ_ID = OI.ORDER_ITEM_SEQ_ID )
   AND OFC.CHANGE_REASON_ENUM_ID = 'BROKERED'
   AND CAST(OFC.CHANGE_DATETIME AS DATE) > '${brokeredDate}'
   INNER JOIN FACILITY F ON F.FACILITY_ID = OISG.FACILITY_ID
   INNER JOIN FACILITY_TYPE FT ON FT.FACILITY_TYPE_ID = F.FACILITY_TYPE_ID
   AND FT.PARENT_TYPE_ID IN ('DISTRIBUTION_CENTER')
   WHERE OH.ORDER_TYPE_ID = 'SALES_ORDER'
     AND OH.PRODUCT_STORE_ID = 'STORE'
     AND OH.STATUS_ID IN ('ORDER_APPROVED')
     AND OI.STATUS_ID IN ('ITEM_APPROVED')) AS virtual_table
GROUP BY `HC_ORDER_ID`,
         `SHOPIFY_ORDER_ID`,
         `LOCATION`,
         `UPC`,
         `ITEM_DESCRIPTION`,
         cast(BROKERED_DATE as date),
         date_format(BROKERED_DATE, "%H:%i %p"),
         `WMI_SENT_DATE`,
         `WMI_SENT_TIME`,
         `CUT_OFF`,
         `HC_PRODUCT_STORE_ID`
LIMIT 1000;

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

SELECT `Location` AS `Location`,
       `Reason` AS `Reason`,
       count(`Shopify Order ID`) AS `COUNT(Shopify Order ID)`
FROM
  (SELECT OFC.FROM_FACILITY_ID AS 'Location',
          OH.ORDER_NAME AS 'Shopify Order ID',
          OH.ORDER_ID AS 'HC Order Id',
          OI.ITEM_DESCRIPTION AS 'Style',

     (select giupca.ID_VALUE
      from good_identification giupca
      where oi.PRODUCT_ID = giupca.PRODUCT_ID
        and giupca.GOOD_IDENTIFICATION_TYPE_ID = 'UPCA'
        and (giupca.THRU_DATE > NOW()
             OR giupca.THRU_DATE IS NULL)
      limit 1) as SKU,
          P.PRODUCT_NAME AS 'Product Name',
          PF.LAST_INVENTORY_COUNT AS 'ATP',
          PF.MINIMUM_STOCK AS 'Safety Stock ',
          OFC.CHANGE_DATETIME AS Rejected_Datetime,
          OFC.CHANGE_REASON_ENUM_ID AS 'Reason',
          OFC.COMMENTS AS 'Comments',
                       F.FACILITY_ID AS FACILITY_ID,
                       OS.STATUS_DATETIME AS COMPLETED_DATETIME
   FROM ORDER_HEADER OH
   INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID
   INNER JOIN ORDER_FACILITY_CHANGE OFC ON OFC.ORDER_ID=OI.ORDER_ID
   AND OFC.ORDER_ITEM_SEQ_ID=OI.ORDER_ITEM_SEQ_ID
   AND OFC.CHANGE_REASON_ENUM_ID NOT IN ('BROKERED',
                                         'UNFILLABLE',
                                         'RELEASED')
   INNER JOIN PRODUCT P ON P.PRODUCT_ID=OI.PRODUCT_ID
   INNER JOIN PRODUCT_FACILITY PF ON PF.PRODUCT_ID=OI.PRODUCT_ID
   AND PF.FACILITY_ID=OFC.FROM_FACILITY_ID
   INNER JOIN FACILITY F ON PF.FACILITY_ID=F.FACILITY_ID
   and F.FACILITY_TYPE_ID IN('WAREHOUSE',
                             'RETAIL_STORE')
   LEFT JOIN ORDER_STATUS OS ON OS.ORDER_ID = OI.ORDER_ID
   AND OS.ORDER_ITEM_SEQ_ID=OI.ORDER_ITEM_SEQ_ID
   AND OS.STATUS_ID = "ITEM_COMPLETED"
   WHERE OH.PRODUCT_STORE_ID = 'STORE'
   ORDER BY OFC.CHANGE_DATETIME DESC) AS virtual_table
WHERE `Rejected_Datetime` >= STR_TO_DATE('2024-05-06 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `Rejected_Datetime` < STR_TO_DATE('2024-05-13 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
GROUP BY `Location`,
         `Reason`
ORDER BY `COUNT(Shopify Order ID)` DESC
LIMIT 5000;

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

SELECT `Shopify Id` AS `Shopify Id`,
       `Tracking Number` AS `Tracking Number`,
       `Shipping Method` AS `Shipping Method`,
       `Shipping Location` AS `Shipping Location`,
       `Ship To Address` AS `Ship To Address`,
       `UNITS SHIPPED` AS `UNITS SHIPPED`
FROM
  (SELECT oh.ORDER_NAME AS 'Shopify Id',
          trck.TRACKING_CODE AS 'Tracking Number',
          oisg.SHIPMENT_METHOD_TYPE_ID AS 'Shipping Method',
          oisg.FACILITY_ID AS 'Shipping Location',
          concat(pa.ADDRESS1, ' ', pa.STATE_PROVINCE_GEO_ID, ' ', pa.POSTAL_CODE) AS 'Ship To Address',
          count(oi.ORDER_ITEM_SEQ_ID) AS 'UNITS SHIPPED',
          oh.PRODUCT_STORE_ID,
          oh.ORDER_TYPE_ID
   FROM order_item oi
   JOIN order_header oh ON oi.order_id = oh.order_id
   LEFT JOIN order_shipment osh ON oi.ORDER_ID = osh.ORDER_ID AND oi.ORDER_ITEM_SEQ_ID = osh.ORDER_ITEM_SEQ_ID
   LEFT JOIN shipment_status ss ON osh.SHIPMENT_ID = ss.SHIPMENT_ID
   LEFT JOIN shipment s ON osh.SHIPMENT_ID = s.SHIPMENT_ID
   JOIN order_item_ship_group oisg ON oi.ORDER_ID = oisg.ORDER_ID AND oi.SHIP_GROUP_SEQ_ID = oisg.SHIP_GROUP_SEQ_ID
   LEFT JOIN (SELECT oi.order_id,
                     oi.order_item_seq_id,
                     sprs.tracking_code AS 'TRACKING_CODE'
              FROM order_item oi
              INNER JOIN order_shipment os ON oi.ORDER_ID = os.ORDER_ID AND oi.ORDER_ITEM_SEQ_ID = os.ORDER_ITEM_SEQ_ID
              INNER JOIN shipment_package_route_seg sprs ON os.SHIPMENT_ID = sprs.SHIPMENT_ID AND sprs.TRACKING_CODE IS NOT NULL) trck ON oi.ORDER_ID = trck.ORDER_ID AND oi.ORDER_ITEM_SEQ_ID = trck.ORDER_ITEM_SEQ_ID
   LEFT JOIN order_contact_mech ocm ON oi.ORDER_ID = ocm.ORDER_ID
   INNER JOIN postal_address pa ON ocm.CONTACT_MECH_ID = pa.CONTACT_MECH_ID
   WHERE ss.STATUS_ID = 'SHIPMENT_SHIPPED'
     AND oi.STATUS_ID = 'ITEM_COMPLETED'
     AND oh.PRODUCT_STORE_ID = 'STORE'
     AND trck.TRACKING_CODE IS NOT NULL
   GROUP BY trck.TRACKING_CODE) AS virtual_table
WHERE `Shipped Date` >= STR_TO_DATE('2024-05-06 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `Shipped Date` < STR_TO_DATE('2024-05-13 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `ORDER_TYPE_ID` = 'SALES_ORDER'
GROUP BY `Shopify Id`,
         `Tracking Number`,
         `Shipping Method`,
         `Shipping Location`,
         `Ship To Address`,
         `UNITS SHIPPED`
LIMIT 10;

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

SELECT shopify_fulfillment_status AS shopify_fulfillment_status,
       COUNT(shopify_fulfillment_status) AS `COUNT(shopify_fulfillment_status)`
FROM
  (SELECT ofh.ORDER_ID AS hotwax_order_id ,
          oh.ORDER_NAME AS shopify_order_name ,
          p.INTERNAL_NAME AS sku ,
          f.FACILITY_NAME ,
          f.FACILITY_ID AS FACILITY_ID ,
          si.DESCRIPTION AS item_status ,
          si2.DESCRIPTION AS hotwax_shipment_status ,
          ss.STATUS_DATE AS hotwax_fulfillment_time ,
          ofh.CREATED_DATE AS created_time ,
          CASE
              WHEN ofh.EXTERNAL_FULFILLMENT_ID = '_NA_' THEN NULL
              ELSE ofh.EXTERNAL_FULFILLMENT_ID
          END AS shopify_fulfillment_id ,
          CASE
              WHEN ofh.EXTERNAL_FULFILLMENT_ID = '_NA_' THEN 'pending'
              ELSE 'success'
          END AS shopify_fulfillment_status ,
          CASE
              WHEN ofh.EXTERNAL_FULFILLMENT_ID = '_NA_' THEN NULL
              ELSE ofh.LAST_UPDATED_STAMP
          END AS shopify_fulfillment_entry_time
   FROM order_fulfillment_history ofh
   JOIN order_header oh ON ofh.ORDER_ID = oh.ORDER_ID
   AND oh.ORDER_TYPE_ID = "SALES_ORDER"
   JOIN order_item oi ON ofh.ORDER_ID = oi.ORDER_ID
   AND ofh.ORDER_ITEM_SEQ_ID = oi.ORDER_ITEM_SEQ_ID
   JOIN status_item si ON oi.STATUS_ID = si.STATUS_ID
   JOIN product p ON oi.PRODUCT_ID = p.PRODUCT_ID
   JOIN shipment s ON ofh.SHIPMENT_ID = s.SHIPMENT_ID
   JOIN status_item si2 ON s.STATUS_ID = si2.STATUS_ID
   JOIN facility f ON s.ORIGIN_FACILITY_ID = f.FACILITY_ID
   AND s.STATUS_ID = ss.STATUS_ID
   JOIN shipment_status ss ON ofh.SHIPMENT_ID = ss.SHIPMENT_ID
   WHERE ofh.SHIPMENT_ID IS NOT NULL
     AND oi.EXTERNAL_ID IS NOT NULL) AS virtual_table
WHERE ((shopify_fulfillment_status = 'pending'
        or cast(created_time as date) = cast((NOW() - Interval 1 day) as date)))
GROUP BY shopify_fulfillment_status
ORDER BY `COUNT(shopify_fulfillment_status)` DESC
LIMIT 100;

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

SELECT `STORE` AS `STORE`,
       `SKU` AS `SKU`,
       `STYLE` AS `STYLE`,
       `COLOR` AS `COLOR`,
       `UNITS` AS `UNITS`,
       `SIZE` AS `SIZE`
FROM
  (SELECT ofc.facility_id 'STORE',
                          ofc.facility_id 'FACILITY_ID',
                                          vp.PRODUCT_NAME 'STYLE',

     (select giupca.ID_VALUE
      from good_identification giupca
      where oi.PRODUCT_ID = giupca.PRODUCT_ID
        and giupca.GOOD_IDENTIFICATION_TYPE_ID = 'SHOPIFY_PROD_SKU') as SKU,
                                                          pf.color 'COLOR',
                                                                   pf.size 'SIZE',
                                                                           sum(oi.quantity) 'UNITS',
                                                                                            oh.PRODUCT_STORE_ID
   FROM order_item oi
   join order_header oh on oi.ORDER_ID = oh.ORDER_ID
   JOIN order_facility_change ofc ON oi.order_id = ofc.order_id
   AND oi.order_item_seq_id = ofc.order_item_seq_id
   JOIN order_status os ON oi.order_id = os.ORDER_ID
   AND oi.ORDER_ITEM_SEQ_ID = os.ORDER_ITEM_SEQ_ID
   INNER JOIN
     (SELECT ORDER_ID,
             ORDER_ITEM_SEQ_ID,
             max(CHANGE_DATETIME) 'MaxDate'
      FROM order_facility_change ofc2
      GROUP BY ORDER_ID,
               ORDER_ITEM_SEQ_ID) tm ON ofc.ORDER_ID = tm.ORDER_ID
   AND ofc.ORDER_ITEM_SEQ_ID = tm.ORDER_ITEM_SEQ_ID
   AND ofc.CHANGE_DATETIME = tm.MaxDate
   JOIN facility fac ON ofc.FACILITY_ID = fac.FACILITY_ID
   LEFT JOIN
     (SELECT PRODUCT_ID,
             min(CASE
                     WHEN pf.PRODUCT_FEATURE_TYPE_ID = 'COLOR' THEN pf.DESCRIPTION
                 END) AS 'color' ,
             min(CASE
                     WHEN pf.PRODUCT_FEATURE_TYPE_ID = 'SIZE' THEN pf.DESCRIPTION
                 END) AS 'size'
      FROM product_feature_appl pfa
      JOIN product_feature pf ON pf.PRODUCT_FEATURE_ID = pfa.PRODUCT_FEATURE_ID
      AND pf.PRODUCT_FEATURE_TYPE_ID IN ('SIZE',
                                         'COLOR')
      GROUP BY PRODUCT_ID) pf ON pf.PRODUCT_ID = oi.PRODUCT_ID
   JOIN product p ON oi.PRODUCT_ID = p.PRODUCT_ID
   JOIN product_assoc pa ON p.PRODUCT_ID = pa.PRODUCT_ID_TO
   JOIN product vp ON pa.PRODUCT_ID = vp.PRODUCT_ID
   WHERE CAST(os.STATUS_DATETIME AS date) = (current_date() - INTERVAL 1 DAY)
     AND os.STATUS_ID = 'ITEM_COMPLETED'
     AND ofc.change_reason_enum_id = 'BROKERED'
     AND fac.FACILITY_TYPE_ID = 'RETAIL_STORE'
   GROUP BY ofc.FACILITY_ID,
            pf.color,
            pf.size,
            p.product_id,
            oh.PRODUCT_STORE_ID) AS virtual_table
LIMIT 1000;

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

SELECT `STORE` AS `STORE`,
       sum(`sum(oi.quantity)`) AS `SUM(sum(oi.quantity))`
FROM
  (Select ofc.facility_id 'STORE',
                          ofc.facility_id 'FACILITY_ID',
                                          count(ofc.ORDER_ITEM_SEQ_ID),
                                          sum(oi.quantity)
   from order_facility_change ofc
   join order_status os on ofc.ORDER_ID = os.ORDER_ID
   and ofc.ORDER_ITEM_SEQ_ID = os.ORDER_ITEM_SEQ_ID
   inner join
     (select ORDER_ID,
             ORDER_ITEM_SEQ_ID,
             max(CHANGE_DATETIME) 'MaxDate'
      from order_facility_change ofc2
      group by ORDER_ID,
               ORDER_ITEM_SEQ_ID) tm on ofc.ORDER_ID = tm.ORDER_ID
   and ofc.ORDER_ITEM_SEQ_ID = tm.ORDER_ITEM_SEQ_ID
   and ofc.CHANGE_DATETIME = tm.MaxDate
   join facility fac on ofc.FACILITY_ID = fac.FACILITY_ID
   join order_header oh on ofc.ORDER_ID = oh.ORDER_ID
   join order_item oi on ofc.ORDER_ID = oi.ORDER_ID
   and ofc.ORDER_ITEM_SEQ_ID = oi.ORDER_ITEM_SEQ_ID
   where cast(os.STATUS_DATETIME as date) =date(now() - INTERVAL 1 DAY)
     and os.STATUS_ID='ITEM_COMPLETED'
     and ofc.change_reason_enum_id ='BROKERED'
     and fac.FACILITY_TYPE_ID='RETAIL_STORE'
     and oh.PRODUCT_STORE_ID='STORE'
   group by ofc.FACILITY_ID) AS virtual_table
GROUP BY `STORE`
ORDER BY `SUM(sum(oi.quantity))` DESC
LIMIT 100;

Query Logic