Netsuite Sync
Discover Netsuite synchronization reports provided by HotWax Commerce
Last updated
Discover Netsuite synchronization reports provided by HotWax Commerce
Last updated
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.
Field Header | Description | HC Entity |
---|---|---|
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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'.
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.
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.
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.
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 Header | Description | HC Entity |
---|---|---|
Field Header | Description | HC Entity |
---|---|---|
Field Header | Description | HC Entity |
---|---|---|
Field Header | Description | HC Entity |
---|---|---|
Field Header | Description | HC Entity |
---|---|---|
Field Header | Description | HC Entity |
---|---|---|
Field | Description |
---|---|
Field | Description |
---|---|
Order ID
It helps in distinguishing one order from another.
OrderHeader.ORDER_ID
SKU
SKU of the product
Product.INTERNAL_NAME
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
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
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
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
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
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
ORDER_NAME
The identifier for the order
ATTRIBUTE
The essential order attributes. For example, PRODUCT_VERIFIED
indicates whether the product has been verified
STATUS
The status if the order has essential attributes or not
ORDER_NAME
The identifier for the order
ATTRIBUTE
The essential order attribute. For example, PRODUCT_VERIFIED
indicates whether the product has been verified
NETSUITE_ORDER_EXPORTED
Status if the order has been exported to NetSuite or not
NETSUITE_CUSTOMER_ID
The customer identifier in NetSuite