Inventory
Discover Inventory reports provided by HotWax Commerce
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
Field Header | Description | HC Entity |
---|---|---|
SKU | SKU of the product | Product.INTERNAL_NAME |
Difference | Discrepancies in receiving | Difference of ShipmentReceipt.QUANTITY_ACCEPTED with ShipmentItem.QUANTITY |
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
Field Header | Description | HC Entity |
---|---|---|
Facility | Location of a physical store or warehouse | Facility.FACILITY_NAME |
Facility ID | An identifier used to distinguish one facility from another uniquely | Facility.FACILITY_ID |
SKU | Each product's unique code assigned for inventory management and tracking | GoodIdentification.ID_VALUE |
Adjustment date | The date on which a change or modification is made | InventoryItemVariance.CREATED_STAMP |
User | A person or system that interacts with the system | InventoryItemVariance.CHANGE_BY_USER_LOGIN_ID |
Quantity | The numerical amount or count of a product | InventoryItemVariance.QUANTITY_ON_HAND_VAR |
Description | A detailed explanation often used to provide information about products | VarianceReason.DESCRIPTION |
Enum ID | Short for "Enumeration Identifier," it refers to a unique identifier within an enumeration | Enumeration.ENUM_TYPE_ID |
Comments | Additional remarks or information provided to explain | InventoryItemVariance.COMMENTS |
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
Field Header | Description | HC Entity |
---|---|---|
External ID (WMS) | Identification code used in the WMS to track items externally | Shipment.EXTERNAL_ID |
External ID | An identification code used externally for tracking purposes | Shipment.SHIPMENT_ID |
Tracking code | Code used to monitor the orders | ShipmentRouteSegment.TRACKING_ID_NUMBER |
Facility | Location of a physical store or warehouse | Facility.FACILITY_NAME |
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
Field Header | Description | HC Entity |
---|---|---|
Original Facility ID | ID of the facility where the shipment originated | Shipment.ORIGIN_FACILITY_ID |
Destination Facility ID | ID of the facility where the shipment was delivered | Shipment.DESTINATION_FACILITY_ID |
Expected | Expected quantity of items | ShipmentItem.QUANTITY |
Shipment ID | Unique identifier for the shipment | Shipment.EXTERNAL_ID |
Transfer Order | Transfer order number associated with the shipment | ShipmentAttribute.ATTR_VALUE where the ShipmentAttribute.ATTR_NAME is set to EXTERNAL_ORDER_NAME |
SKU | SKU of the product | Product.INTERNAL_ID |
Received | Quantity of items received | ShipmentReceipt.QUANTITY_ACCEPTED |
Difference | Discrepancies in receiving | Difference of ShipmentReceipt.QUANTITY_ACCEPTED and ShipmentItem.QUANTITY |
Status | The status of the order based on the difference between expected and received quantities | Conditions applied to ShipmentItem.QUANTITY - ShipmentReceipt.QUANTITY_ACCEPTED (e.g., Completed, NotReceived, OverReceived, UnderReceived, Manually added item to transfer order) |
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.
Last updated