Netsuite Sync
Discover Netsuite synchronization reports provided by HotWax Commerce
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
Field Header | Description | HC Entity |
---|---|---|
Order ID | It helps in distinguishing one order from another. | OrderHeader.ORDER_ID |
SKU | SKU of the product | Product.INTERNAL_NAME |
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
Field Header | Description | HC Entity |
---|---|---|
Shopify Product ID | Identifies the product within Shopify | gi.GOOD_IDENTIFICATION_TYPE_ID |
HotWax Product ID | The internal product identifier within the HotWax Commerce system | gi.PRODUCT_ID |
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
Field Header | Description | HC Entity |
---|---|---|
HotWax product ID | The internal product identifier within the HotWax Commerce system | product.PRODUCT_ID |
SKU | SKU of the product | product.INTERNAL_NAME |
HotWax Discontinuation date | The date when the product was discontinued in the HotWax system, formatted as mm-dd-yyyy | product.SUPPORT_DISCONTINUATION_DATE |
comments | Any comments related to the product | product.COMMENTS |
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.
Field Header | Description | HC Entity |
---|---|---|
HotWax Customer ID | The unique identifier for the customer within the Hotwax Commerce system. | party.PARTY_ID |
First Name | The first name of the customer. | person.FIRST_NAME |
Last Name | The last name of the customer. | person.LAST_NAME |
Shopify Customer ID | The unique identifier for the customer within the Shopify platform. | party_identification.ID_VALUE (where PARTY_IDENTIFICATION_TYPE_ID = 'SHOPIFY_CUST_ID') |
Order Count | The total number of distinct orders placed by the customer, reflecting their purchase activity. | Derived from order_role.ORDER_ID |
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
Field Header | Description | HC Entity |
---|---|---|
Order ID | It helps in distinguishing one order from another. | OrderHeader.ORDER_ID |
Order Name | ||
Order Type ID | ||
Order Item Seq ID | ||
SKU | SKU of the product | Product.INTERNAL_NAME |
Status Date and Time | ||
Fulfillment Exported |
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
Field Header | Description | HC Entity |
---|---|---|
Order ID | It helps in distinguishing one order from another. | order_header.ORDER_ID |
Order Name | The name or identifier for the order. | order_header.ORDER_NAME |
Order Type ID | The type of order, identifying the category of the order. | order_header.ORDER_TYPE_ID |
Order Item Seq ID | The sequence identifier for items within an order. | order_item.ORDER_ITEM_SEQ_ID |
SKU | SKU of the product. | product.INTERNAL_NAME |
Status Date and Time | The date and time when the order item status was updated. | order_status.STATUS_DATETIME |
Fulfillment Exported | Indicates whether the fulfillment has been exported to NetSuite (Y or N). | Derived from order_fulfillment_history.FULFILLMENT_LOG_ID |
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
Field Header | Description | HC Entity |
---|---|---|
Return ID | Unique identifier for the return transaction. | return_header.RETURN_ID |
Entry Date | Date and time when the return transaction was recorded. | return_header.ENTRY_DATE |
Order Name | Name or identifier for the associated order. | order_header.ORDER_NAME |
Internal Name | Internal name or SKU of the returned product. | product.INTERNAL_NAME |
Facility ID | Identifier for the facility where the return was processed. | return_header.DESTINATION_FACILITY_ID |
Returned Quantity | Quantity of the product returned in the transaction. | return_item.RETURN_QUANTITY |
Restocked Quantity | Quantity of the product restocked back into inventory. | return_item.RECEIVED_QUANTITY |
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
Field | Description |
---|---|
ORDER_NAME | The identifier for the order |
ATTRIBUTE | The essential order attributes. For example, |
STATUS | The status if the order has essential attributes or not |
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.
Field | Description |
---|---|
ORDER_NAME | The identifier for the order |
ATTRIBUTE | The essential order attribute. For example, |
NETSUITE_ORDER_EXPORTED | Status if the order has been exported to NetSuite or not |
NETSUITE_CUSTOMER_ID | The customer identifier in NetSuite |
Last updated