Shopify ERP Create Orders Per Seller
Summary of Changes: (The purpose of this table is to keep traceability and Product team to highlight the things that were changed into the scope, based on comments or discussions)
| Version | Date | Created / Updated | Notes |
|---|---|---|---|
| 1.0 | 12.12.2022 | Bogomil Pavlov | First publish |
| 1.1 | 16.12.2022 | Bogomil Pavlov | Added Mapping for Transactions Total Amount |
| 1.2 | 21.11.2023 | Bogomil Pavlov | Shopify Gateway Deprecation |
We have a new challenging requirement and we need to add additional logic to be able to split the orders per Shopify seller when exporting. This means for each order which we are splitting will have to make separate request to each connector.
The major change here will be we will need to add Connector ID on Product In Order level as well because one order with two items from two different sellers will need to be exported to two different Shopify systems. Also we will need to calculate the order totals in different way depends how many products we are exporting. All the changes in terms of the calculations is described in the mapping below. The way we are shipping orders will remain the same depends from which Shopify first we get the shipping update we will ship the whole order. There will be a change in the way we are getting refunds as well because they need to be get with the correct Shopify ID
For example the setup in Hemi will look like as follows: Each Account will have two Shopify Connectors for example SC1 and SC2
SC1 and SC2 will have two different Shopify Connectors set in Hemi and each Shopify Connector will export to different Shopify system.
Every Product In Order will have Seller ID obtained from the Account based on the internal Hemi script and we need to map the Product In Order > Seller with the Shopify Connector Seller > Seller (new slave table of the Shopify Connector table and new field which need to be a dropdown and display all sellers in Hemi) and export only Product In Orders with the same seller.
This way if we have order with SC1 and SC2 we will export the Product In Orders in two separate Shopify systems.
The whole new logic will be controlled by the Shopify Connector Seller table and each connector will be responsible for the seller.
Please note we can have more than two Account Shopify Connector.
If we have more than one connector added for more than one seller we will need to:
1 . Create Order
Start storing the Shopify Order IDs in each Product In Order > Connector ID and mark them as Product In Order > Shopify Exported = Yes once we successfully create an order in Shopify. The Orders > Connector ID will contain the first returned ID from Shopify.
Additional validation will be only If we do not have Product In Order > Seller ID we wont export the order. Same is the case if not all Seller IDs are populated across the order we want to stop the export for all items. Error should be returned in Orders > Connector Error and Order Error table. “Seller ID is missing from the product in order“
Also additional validation will be if we have Shopify Connector without added seller.Also we need to check if the seller is not associated with any other Shopify connector.
2. Create Order Mapping
The order export mapping will need to be changed as followsPlease note everything in red is the new changes in the mapping.
| Integration Field | Integration Notes | Hemi Mapping | Hemi Notes | |||
|---|---|---|---|---|---|---|
| order | ||||||
| checkout_id | Order Payment> Transaction ID | |||||
| Order >Marketplace Order ID | If the Order Payment> Transaction ID is missing we will be using Order >Marketplace Order ID | |||||
| source_identifier | Order >Marketplace Order ID | |||||
| created_at | Order > Order Created Time | date format - 2022-03-21T15:31:06+02:00 |
||||
| inventory_behaviour | Shopify Connector > Inventory Behaviour | |||||
| currency | Order > Order Currency | If Active Currency Conversion = Yes we use the Shopify Connector > Shopify Currency | ||||
send_fulfillment_receipt |
If Shopify Connector >Send Shipping Confirmation = true | |||||
send_receipt |
If Shopify Connector > Send Order Confirmation = true | |||||
| phone | N/A | Do not send in this node, just in ‘shipping’ and ‘billing’. | ||||
| Order > Order > Buyer mail | Trim leading white spaces if any | |||||
| note | Order > ID | |||||
| taxes_included | If Account Shopify Connector > Create Order Tax Excluded = No we push “true“ | |||||
| If Account Shopify Connector > Create Order Tax Excluded = Yes we push “false“ | ||||||
| financial_status | We need to map |
paid -Order Payment > Status = Completed
null - Order Payment > Status != Completed |
| | transactions | | | | | |
| | | kind | | | | Hardcoded as “capture“ |
| | | status | | | | Hardcoded as “success“ |
| | | currency | | | Orders > Оrder Currency | If Active Currency Conversion = Yes we use the currency from the Shopify Connector > Shopify Currency |
| | | gateway | | | N/A | <v1.2>We want to unmap this field and remove it from the payload</1.2> |
| | | amount | | | (Product In Order > Quantity Product In Order > Price) + Product In Order > Item Shipping Cost | Please note if we have more than one order item per seller we need to sum the tax as well for all order items. |
| | fulfillment_status | | | How far along an order is in terms line items fulfilled. Valid values: null, fulfilled, partial, and not_eligible | | We need to map
fulfilled - Orders> Order Status = Shipped
null - Orders> Order Status = Ready For Shipping |
| | total_tax | | | | (Product In Order > Quantity Product In Order > Vat Item Price) + Product In Order > Vat Item Shipping Cost
OR
(Product In Order > Quantity * Product In Order > Item Sales Tax Price) + Product In Order > Item Shipping Cost Sales Tax | If we have the Tax Amounts populated we push the all tax fields: Product In Order > Item Sales Tax Price Product In Order > Sales Tax Percent Product In Order > Item Shipping Cost Sales Tax
By default we use our VAT fields If Active Currency Conversion = Yes we convert according to the selected currency
| Please note if we have more than one order item per seller we need to sum the tax as well for all order items. | note_attributes | Orders > Marketplace Order ID Orders> Marketplace Fulfillment Channel Orders Amazon > Latest Ship Date Orders > Buyer User ID Orders > Selling Manager SalesRecordNumber Orders > Shipping Service | We include only the fields which are not empty so if we have Amazon orders we will have Fulfillment Channel and Latest Ship Date but if we have eBay we will have Sales Rec Num and Buyer User ID. For the name we will be using our field label and for the value we are pushing our values. | |||||
|---|---|---|---|---|---|---|---|---|
| name | We specify the field label | |||||||
| value | We specify the field value | |||||||
| line_items | Only for selected Seller | |||||||
| variant_id | Item Shopify > Variant ID | We need to use the Product In Order > SKU and map it with Item > SKU in order to get the correct Inventory Item ID | ||||||
| quantity | Product In Order > Item Quantity | |||||||
| price | Product In Order > Item Trans Price | If Active Currency Conversion = Yes we convert according to the selected currency | ||||||
| fulfillment_service | hardcode as “manual" | |||||||
| variant_inventory_management | hardcode as “shopify" | |||||||
| properties | Product In Order > Channel Item ID | For the name we will be using our field label and for the value we are pushing our values. | ||||||
| name | We specify the field label e.g “Channel Item ID“ | |||||||
| value | We specify the label value e.g “13123123“ | |||||||
| tax_lines | ||||||||
| price | Product In Order > Vat Item Price | |||||||
| Product In Order > Item Tax Price | If we have the Total Tax Amount populated we push the all tax fields: |
| Orders > Total Tax Amount Product In Order > Item Tax Price Product In Order > Tax Percent Orders > Shipping Sales Tax We need to multiply the tax by the item quantity when pushing to Shopify By default we use our VAT fields If Active Currency Conversion = Yes we convert according to the selected currency | rate | Product In Order > Vat Percent Product In Order > Tax Percent | we will need to divide our percentage amount by 100 and push it like “0.20“ If we have the Total Tax Amount populated we push the all tax fields: Orders > Total Tax Amount Product In Order > Item Tax Price Product In Order > Tax Percent Orders > Shipping Sales Tax By default we use our VAT fields If Product In Order > Tax Percent is missing we need to calculate it internally (Product In Order > Item Tax Price / Product In Order > Item Price) We need to round it at the 2nd decimal | title | hardcode as “Item VAT" | customer | first_name | Order > Shipping Buyer Name | We need only the first name and we get everything before the first space character Trim leading white spaces if any | last_name | Order > Shipping Buyer Name | We need only the last name and we get everything after the last space character Trim leading white spaces if any | Order > Buyer Email | Trim leading white spaces if any | billing_address | first_name | Order > Billing Name | We need only the first name and we get everything before the first space character Trim leading white spaces if any | address1 | Order > Billing street 1 | Trim leading white spaces if any | phone | Order > Billing Phone | Trim everything except for the numbers | city | Order > Billing City Name | Trim leading white spaces if any | zip | Order > Billing Postal Code | province | Order > Billing State Province | Trim leading white spaces if any | country | Order > Billing Country Name | Trim leading white spaces if any | company | Order > Company Name OR Account > Name | Trim leading white spaces if any If we have activated in Account Shopify Connector > Channel as company name we will sent Account > Name as company. | last_name | Order > Billing Name | We need only the last name and we get everything after the last space character Trim leading white spaces if any | address2 | Order > Billing street 2 | Trim leading white spaces if any | name | Order > Billing Name | Trim leading white spaces if any | country_code | Order > Billing Country Code | shipping_address | first_name | Order > Shipping Buyer Name | We need only the first name and we get everything before the first space character Trim leading white spaces if any | address1 | Order > Shipping street 1 | Trim leading white spaces if any | phone | Order > Shipping Phone | Trim everything except for the numbers | city | Order > Shipping City | Trim leading white spaces if any | zip | Order > Shipping Postal Code | province | Order > Shipping State Province | Trim leading white spaces if any | country | Order > Shipping Country Name | Trim leading white spaces if any | last_name | Order > Shipping Buyer Name | We need only the last name and we get everything after the last space character Trim leading white spaces if any | address2 | Order > Shipping street 2 | Concatenate ‘Orders Ebay > IOSS' and 'Orders > Shipping Street 2’. Trim leading white spaces if any | name | Order > Shipping Buyer Name | Trim leading white spaces if any | country_code | Order > Shipping Country Code | fulfillments | If the order is “shipped” and the shipments are with status “completed” or “warning“ and we have the shipping details we push them as follows; Also if we have Product In Orders with different locations we need to group the SKUs per location and push them as two separate fulfillment nodes. e.g. "order":{ "id": 4743616168183, "fulfillments":[ {"id": 4231381680375, "admin_graphql_api_id": "gid://shopify/Fulfillment/4231381680375", "created_at": "2022-04-18T11:02:19+03:00",…}, {"id": 4231381975287, "admin_graphql_api_id": "gid://shopify/Fulfillment/4231381975287", "created_at": "2022-04-18T11:03:10+03:00",…} ] } | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| tracking_company | Order > Courier | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| tracking_number | Order > Shipping Track Number | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| tracking_url | Order > Shipping Track URL | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| location_id | Shopify Connector Locations > External ID | We need to map the SKU Location Id | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| shipping_lines | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| code | Account > Marketplace + Order > Shipping Service | We need to concatenate Account > Marketplace plus the actual service from the order. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| If the Shipping Service is missing from the order we push only Account > Marketplace | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| title | Account > Marketplace + Order > Shipping Service | We need to concatenate Account > Marketplace plus the actual service from the order. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| If the Shipping Service is missing from the order we push only Account > Marketplace | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| price | Product in Order > Shipping cost | If Active Currency Conversion = Yes we convert according to the selected currency | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| This is the total Shipping cost so if there are more than one Item for the seller we need to sum the shipping cost | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| source | Hardcode as 'shopify' | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| tax_lines | Only for selected Seller | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| price | Product In Order >Vat Shipping Cost Value |
OR Product In Order > Item Shipping Cost Sales Tax | The total Shipping Cost VAT If we have the Total Tax Amount populated we push the all tax fields:
Product In Order > Item Tax Price Product In Order > Tax Percent
By default we use our VAT fields If Active Currency Conversion = Yes we convert according to the selected currency This is the total Shipping cost tax/vat so if there are more than one Item for the seller we need to sum the shipping cost tax/vat | | | | | rate | | Product In Order > Vat Percent Product In Order > Tax Percent | If we have different VAT rates we pick the highest rate from the Product In Orders. we will need to divide our percentage amount by 100 and push it like “0.20“ If we have the Total Tax Amount populated we push the all tax fields: Orders > Total Tax Amount Product In Order > Item Tax Price Product In Order > Tax Percent Orders > Shipping Sales Tax By default we use our VAT fields If Product In Order > Tax Percent is missing we need to calculate it internally (Product In Order > Item Tax Price / Product In Order > Item Price) We need to round it at the 2nd decimal | | | | | title | | | Hardcoded as “Shipping VAT“ | | | discount_codes | | | | | | | | | code | | | N/A | | | | | amount | | | N/A | | | | | type | | | N/A | |
3. Get Shipments
Mapping and logic remains the same but when we are getting the shipping updates from Shopify will need to map the orders, based on the Product In Order > Connector ID and despite for which Product In Order we will receive the shipping update we ship the whole order. Basically we get the shipments from both Shopify systems and whichever ship the order first we ship the whole order. However there are couple of validations we need to make.
- We are downloading shipping information only when we have orders with “Ready For Shipping” status and update shipping pending flag is equal to “No“.
- If we have an order with “Ready For Shipping” status and update shipping pending flag is equal to “No“ but the carrier and tracking details are different from what we have in Hemi we want to update them and set update shipping pending flag is equal to “Yes“.
- In all other cases we do not want to set update shipping pending flag is equal to “Yes“ or update the shipping information.
4. Get Refunds
Mapping and logic remains the same when we are getting refunds from Shopify we need to use the Product In Order > Connector Order ID and create the refund rows in Hemi.