Order Export
Order File Field Mapping 2022-05-23.docx
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 | 09.28.2022 | Bogomil Pavlov | First publish |
| 1.1 | 29.03.2023 | Bogomil Pavlov | Additional Amendments after UAT (marked in green) |
| 1.2 | 13.04.2023 | Bogomil Pavlov | SALES_TAX_RATE mapping updated, added description about shipping cost to be only on one Order Line |
| v.1.3 | 25/052023 | Hristiyan Georgiev | Additional logic for bundles export |
Orders will be downloaded directly in Hemi then we will have to export them in xml file on the FTP. Thus we need to be able to setup the FTP details in Hemi.
We will be exporting orders with (debundled, splitted shipping cost, seller id filled) Orders > Order Status = “Ready For Shipping“ only and Order > Exported = no.
(v1.3) GLTC will have bundle SKUs and we want to export only the Bundle SKU without the components. We need to include a filter and do not export any component SKUs from the orders.
Once we successfully export an order we will need to set Order > Exported = Yes. If we receive any error during export we need to store them in Order Error table.
Please note we are happy to go through the standard automatic export functionality if we can have custom export selected in xml format.
Sample xml structure:
<?xml version="1.0" encoding="utf-8"?>
<ORDERS>
<ORDER CHANNEL="CC" CUST_ID="370814550054" EXCHANGE_ORDER="N" ORDER_DATE="2021-11-16 14:29:24" O_ID="31616680">
<TENDER_LINE TL_ID="31616680" TYPE="Secure Trading STAPI">
<VALUE CURRENCY="GBP">191.75</VALUE>
<CARD_DETAILS>
<NUMBER>N/A</NUMBER>
<EXPIRY_DATE></EXPIRY_DATE>
<AUTH_CODE></AUTH_CODE>
<TYPE>PayPal</TYPE>
<FRAUD_SCORE></FRAUD_SCORE>
<TRANSACTION_REFERENCE>0000000000000000T</TRANSACTION_REFERENCE>
</CARD_DETAILS>
</TENDER_LINE>
<BILLING_DETAILS>
<NAME>
<TITLE></TITLE>
<FIRST_NAME>Stacey</FIRST_NAME>
<LAST_NAME>Flannery</LAST_NAME>
</NAME>
<ADDRESS>
<ADDRESS_LINE_1>Flat 14 Bordeston Court</ADDRESS_LINE_1>
<ADDRESS_LINE_2>The Ham</ADDRESS_LINE_2>
<TOWNCITY>Brentford</TOWNCITY>
<COUNTY></COUNTY>
<POSTCODE>TW8 8HW</POSTCODE>
<COUNTRY>GB</COUNTRY>
</ADDRESS>
<CONTACT_DETAILS>
<TELEPHONE TYPE="HOME">+44 7411 985122</TELEPHONE>
<TELEPHONE TYPE="MOBILE">+44 7411 985122</TELEPHONE>
<TELEPHONE TYPE="OFFICE"></TELEPHONE>
<EMAIL>stacey_f_1402@hotmail.com</EMAIL>
</CONTACT_DETAILS>
</BILLING_DETAILS>
<DELIVERY_DETAILS>
<NAME>
<TITLE></TITLE>
<FIRST_NAME>Stacey</FIRST_NAME>
<LAST_NAME>Flannery</LAST_NAME>
</NAME>
<ADDRESS>
<ADDRESS_LINE_1>Flat 14 Bordeston Court</ADDRESS_LINE_1>
<ADDRESS_LINE_2>The Ham</ADDRESS_LINE_2>
<TOWNCITY>Brentford</TOWNCITY>
<COUNTY></COUNTY>
<POSTCODE>TW8 8HW</POSTCODE>
<COUNTRY>GB</COUNTRY>
</ADDRESS>
<ORDER_LINE DELIVERY_LINE_ID="3549677486279" DESCRIPTION="Play & Store Dinosaur Playset - L5174 - Play & Store Dinosaur Playset" OL_ID="10792091386055" OL_SEQ="1" QUANTITY="1" SALES_TAX_RATE="20" SKU="L5174">
<UNIT_NET_PRICE>
<VALUE CURRENCY="GBP">32.67</VALUE>
</UNIT_NET_PRICE>
<TAX>
<VALUE CURRENCY="GBP">6.53</VALUE>
</TAX>
<DUTIES>
<VALUE CURRENCY="GBP">0.00</VALUE>
</DUTIES>
<LOCATION LOC_ID="" NAME="" QUANTITY="1" TYPE="DC" />
<LINE_NET_PRICE>
<VALUE CURRENCY="GBP">25.06</VALUE>
</LINE_NET_PRICE>
<PROMOTION PL_ID="1" TYPE="PercentageOff">
<VALUE CURRENCY="GBP">9.13</VALUE>
<PROMOTION_TRIGGER>
<VALUE_TRIGGER>
<VALUE CURRENCY="GBP">39.20</VALUE>
</VALUE_TRIGGER>
</PROMOTION_TRIGGER>
<PROMOTION_TRIGGER>
<CODE_TRIGGER>
<CODE>MUMSNETSS18</CODE>
</CODE_TRIGGER>
</PROMOTION_TRIGGER>
</PROMOTION>
</ORDER_LINE>
<ORDER_LINE DELIVERY_LINE_ID="3549677486279" DESCRIPTION="Marshmallow Play Kitchen - L4724 - Marshmallow Play Kitchen" OL_ID="10792091418823" OL_SEQ="2" QUANTITY="1" SALES_TAX_RATE="20" SKU="L4724">
<UNIT_NET_PRICE>
<VALUE CURRENCY="GBP">123.33</VALUE>
</UNIT_NET_PRICE>
<TAX>
<VALUE CURRENCY="GBP">24.67</VALUE>
</TAX>
<DUTIES>
<VALUE CURRENCY="GBP">0.00</VALUE>
</DUTIES>
<LOCATION LOC_ID="" NAME="" QUANTITY="1" TYPE="DC" />
<LINE_NET_PRICE>
<VALUE CURRENCY="GBP">94.61</VALUE>
</LINE_NET_PRICE>
<PROMOTION PL_ID="1" TYPE="PercentageOff">
<VALUE CURRENCY="GBP">34.47</VALUE>
<PROMOTION_TRIGGER>
<VALUE_TRIGGER>
<VALUE CURRENCY="GBP">148.00</VALUE>
</VALUE_TRIGGER>
</PROMOTION_TRIGGER>
<PROMOTION_TRIGGER>
<CODE_TRIGGER>
<CODE>MUMSNETSS18</CODE>
</CODE_TRIGGER>
</PROMOTION_TRIGGER>
</PROMOTION>
</ORDER_LINE>
<ORDER_LINE DELIVERY_LINE_ID="3549677486279" DESCRIPTION="Gingerbread Toy Baking Set - L5188 - Gingerbread Toy Baking Set" OL_ID="10792091451591" OL_SEQ="3" QUANTITY="1" SALES_TAX_RATE="20" SKU="L5188">
<UNIT_NET_PRICE>
<VALUE CURRENCY="GBP">24.0</VALUE>
</UNIT_NET_PRICE>
<TAX>
<VALUE CURRENCY="GBP">4.8</VALUE>
</TAX>
<DUTIES>
<VALUE CURRENCY="GBP">0.00</VALUE>
</DUTIES>
<LOCATION LOC_ID="" NAME="" QUANTITY="1" TYPE="DC" />
<LINE_NET_PRICE>
<VALUE CURRENCY="GBP">18.42</VALUE>
</LINE_NET_PRICE>
<PROMOTION PL_ID="1" TYPE="PercentageOff">
<VALUE CURRENCY="GBP">6.70</VALUE>
<PROMOTION_TRIGGER>
<VALUE_TRIGGER>
<VALUE CURRENCY="GBP">28.80</VALUE>
</VALUE_TRIGGER>
</PROMOTION_TRIGGER>
<PROMOTION_TRIGGER>
<CODE_TRIGGER>
<CODE>MUMSNETSS18</CODE>
</CODE_TRIGGER>
</PROMOTION_TRIGGER>
</PROMOTION>
</ORDER_LINE>
<ORDER_LINE DELIVERY_LINE_ID="3549677486279" DESCRIPTION="Time for Tea Wooden Tea Set - L5189 - Time for Tea Wooden Tea Set" OL_ID="10792091484359" OL_SEQ="4" QUANTITY="1" SALES_TAX_RATE="20" SKU="L5189">
<UNIT_NET_PRICE>
<VALUE CURRENCY="GBP">24.0</VALUE>
</UNIT_NET_PRICE>
<TAX>
<VALUE CURRENCY="GBP">4.8</VALUE>
</TAX>
<DUTIES>
<VALUE CURRENCY="GBP">0.00</VALUE>
</DUTIES>
<LOCATION LOC_ID="" NAME="" QUANTITY="1" TYPE="DC" />
<LINE_NET_PRICE>
<VALUE CURRENCY="GBP">18.42</VALUE>
</LINE_NET_PRICE>
<PROMOTION PL_ID="1" TYPE="PercentageOff">
<VALUE CURRENCY="GBP">6.70</VALUE>
<PROMOTION_TRIGGER>
<VALUE_TRIGGER>
<VALUE CURRENCY="GBP">28.80</VALUE>
</VALUE_TRIGGER>
</PROMOTION_TRIGGER>
<PROMOTION_TRIGGER>
<CODE_TRIGGER>
<CODE>MUMSNETSS18</CODE>
</CODE_TRIGGER>
</PROMOTION_TRIGGER>
</PROMOTION>
</ORDER_LINE>
<ORDER_LINE DESCRIPTION="Standard Delivery" OL_ID="3549677486279" OL_SEQ="5" QUANTITY="1" SALES_TAX_RATE="20" SKU="9999999993">
<UNIT_NET_PRICE>
<VALUE CURRENCY="GBP">3.29</VALUE>
</UNIT_NET_PRICE>
<WHOLESALE_UNIT_PRICE>
<VALUE CURRENCY="GBP">14.17</VALUE>
</WHOLESALE _UNIT_PRICE>
<TAX>
<VALUE CURRENCY="GBP">0.66</VALUE>
</TAX>
<DUTIES>
<VALUE CURRENCY="GBP">0.00</VALUE>
</DUTIES>
<LINE_NET_PRICE>
<VALUE CURRENCY="GBP">3.29</VALUE>
</LINE_NET_PRICE>
<LOCATION LOC_ID="" NAME="" QUANTITY="1" TYPE="DC" />
</ORDER_LINE>
</DELIVERY_DETAILS>
<GROSS_TOTAL>
<VALUE CURRENCY="GBP">191.75</VALUE>
</GROSS_TOTAL>
<TAX>
<VALUE CURRENCY="GBP">31.95</VALUE>
</TAX>
<POSTAGE>
<VALUE CURRENCY="GBP">3.95</VALUE>
</POSTAGE>
<SPECIAL_INSTRUCTIONS />
</ORDER>
</ORDERS>*Mapping:*Please note everything which is N/A we need to include it in the payload with empty value. If the field is not included in the mapping we do not want to include it in the payloadPlease note all amounts in the xml must be with 2 decimal place. Since in Hemi we are having 4 decimal places we need to handle this in the export.
| XML field | Atrributes | Attributes Desc | Required | Hemi Field | Comment | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ORDERS |
|||||||||||||
ORDER |
|||||||||||||
| O_ID | Order Number Numeric (8) | Yes | Orders > ID | We need to make sure there is no overlap with GLTC existing order numbers which begin with a 3. | |||||||||
| ORDER_DATE | Date order placed YYYY-MM-DD HH:MM:SS | Yes | Orders > Order Created Time | Date format - “2021-11-16 14:29:24” | |||||||||
| EXCHANGE_ORDER | Is this order a replacement of another order? Y/N | Yes | Hardcoded as “N“ | ||||||||||
| CUST_ID | Customer Number Numeric (12) | N/A | |||||||||||
| CHANNEL | Order Source PWS/CC | Yes | Hardcoded as “JLP“ | ||||||||||
| TENDER_LINE | |||||||||||||
| TYPE | Text | Yes | Hardcoded as "Secure Trading STAPI" | Currently static to simplify as Prolog don’t need the exact value | |||||||||
| Hardcoded as "Secure Trading STAPI" | |||||||||||||
| TL_ID | Numeric (8) | Yes | Orders > ID | ||||||||||
| VALUE | Orders > Order Total Amount | ||||||||||||
| CURRENCY | Orders > Order Currency | ||||||||||||
| CARD_DETAILS | |||||||||||||
| NUMBER | Hardcoded as “N/A“ | ||||||||||||
| EXPIRY_DATE | Hardcoded as ““ | ||||||||||||
| AUTH_CODE | Hardcoded as ““ | ||||||||||||
| TYPE | Hardcoded as “PayPal“ | ||||||||||||
| FRAUD_SCORE | Hardcoded as ““ | ||||||||||||
| TRANSACTION_REFERENCE | Hardcoded as “0000000000000000T“ | ||||||||||||
| BILLING_DETAILS | If billing address is missing we need to use the shipping address | ||||||||||||
| NAME | |||||||||||||
| TITLE | N/A | ||||||||||||
| FIRST_NAME | Orders > Billing Buyer Name |
OR Orders > Shipping Buyer Name | If the Billing info is missing we refer to the shipping details.
Need to get only the first name | | | | | | LAST_NAME | | | | | | | | Orders > Billing Buyer Name OR Orders > Shipping Buyer Name | If the Billing info is missing we refer to the shipping details.
Need to get only the last name | | | | | ADDRESS | | | | | | | | | | | | | | | | ADDRESS_LINE_1 | | | | | | | | Orders > Billing Street 1 OR Orders > Shipping Street 1 | If the Billing info is missing we refer to the shipping details.
Please note if the street 1 is missing we need to use street 2 | | | | | | ADDRESS_LINE_2 | | | | | | | | Orders > Billing Street 2 OR Orders > Shipping Street 2 | If the Billing info is missing we refer to the shipping details. | | | | | | TOWNCITY | | | | | | | | Orders > Billing City Name OR Orders > Shipping City | If the Billing info is missing we refer to the shipping details. | | | | | | COUNTY | | | | | | | | N/A | | | | | | | POSTCODE | | | | | | | | Orders > Billing Post Code OR Orders > Shipping Postal Code | If the Billing info is missing we refer to the shipping details. | | | | | | COUNTRY | | | | | | | | Orders > Billing Country Code OR Orders > Shipping Country Code | If the Billing info is missing we refer to the shipping details. | | | | | CONTACT_DETAILS | | | | | | | | | | | | | | | | TELEPHONE | | | | | | | Yes | Orders > Billing Phone OR Orders > Shipping Phone | If the Billing info is missing we refer to the shipping details.
Must be populated | | | | | | | | | | | TYPE | TELEPHONE TYPE="HOME" TELEPHONE TYPE="MOBILE" TELEPHONE TYPE="OFFICE" | Yes | Hardcode as “MOBILE“ | | | | | | | EMAIL | | | | | | | Yes | Orders > Buyer mail | Must be populated Please note if there is no email added in the order we need to generate it like: “dummy{{order.id}}@gltc.com” | | | | DELIVERY_DETAILS | | | | | | | | | | | | | | | | NAME | | | | | | | | | | | | | | | | TITLE | | | | | | | | N/A | | | | | | | FIRST_NAME | | | | | | | | Orders > Shipping Buyer Name | Need to get only the first name | | | | | | LAST_NAME | | | | | | | | Orders > Shipping Buyer Name | Need to get only the last name | | | | | ADDRESS | | | | | | | | | | | | | | | | ADDRESS_LINE_1 | | | | | | | | Orders > Shipping Street 1 | | | | | | | ADDRESS_LINE_2 | | | | | | | | Orders > Shipping Street 2 | | | | | | | TOWNCITY | | | | | | | | Orders > Shipping City | | | | | | | COUNTY | | | | | | | | N/A | | | | | | | POSTCODE | | | | | | | | Orders > Shipping Postal Code | | | | | | | COUNTRY | | | | | | | | Orders > Shipping Country Code | | | | | | ORDER_LINE | | | | | | | | | | Please note the shipping is also part of the order line. For example if we have order with delivery cost and 1 order items we will have two nodes for Order_Line.
Also if we have bundles we will need to include only the bundle components | | | | | | | | | | | DELIVERY_LINE_ID | | Yes | Order > IDOR Product In Order > Order Item Line ID | Required for every order line apart from the delivery line.
Contains the same value for each order line. On the delivery line, it has the same value as OL_ID attribute. So if we have shipping cost we need to include new ORDER_LINE node and use the Product In Order > Order Item Line ID if we have item in the ORDER_LINE node we use Orders > Id | | | | | | | | | | | DESCRIPTION | | | Product In Order > Title OR Hardcoded as “Standard Delivery“ | If there is a delivery on the order we need to hardcode the description and include a whole new line in Order_Line node. Please note we want to have only one Order Line for the shipping cost | | | | | | | | | | | OL_ID | | Yes | Product In Order > Order Item Line ID | Current system allocates an unique order line ID for every order line. | | | | | | | | | | | OL_SEQ | | | | E.g. if there’s 4 lines each field for this would be 1, 2, 3, 4 | | | | | | | | | | | QUANTITY | | | Product In Order > Quantity | | | | | | | | | | | | SALES_TAX_RATE | | | Product In Order > Sales Tax Percent OR Product in Order > Marketplace VAT Percent OR Product In Order > Vat Percent Product In Order> Sales Tax Percent OR Product In Order>Marketplace VAT Percent OR Product In Order>Vat Percent | Based on the Account > Country if we have selected “United States“ we are using the Sales Tax Group, if “United States“ is not selected or nothing is selected we check the Marketplace VAT Group and if empty the Internal VAT Group.
If the line is for the shipping we are using the shipping cost Tax/VAT percentage. If we have different percentage across items we get the highest | | | | | | | | | | | SKU | | Yes | Product In Order > SKU OR Hardcoded as “9999999993“ | Text, letters must be capitalised
If we have shipping row we want to hardcode the sku as “9999999993“ | | | | | | | UNIT_NET_PRICE | | | | | | | | Wholesale price? Need to establish how we will populate customs info for NI deliveries but at the same time capture the wholesale price for invoice. | | | | | | | | VALUE | | | | | | US: Product In Order > Item Price
REST: Product In Order > Item Price
Product in Order > Marketplace VAT Item Price
OR
Product In Order > Item Price
Product In Order > VAT Item Price Orders >Shipping Service Cost | Based on the Account > Country if we have selected “United States“ we are using the Sales Tax Group, if “United States“ is not selected or nothing is selected we check the Marketplace VAT Group and if empty the Internal VAT Group.
If the line is for shipping we need to specify the shipping cost | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | | | | WHOLESALE_UNIT_PRICE | | | | | | | | Wholesale price? Need to establish how we will populate customs info for NI deliveries but at the same time capture the wholesale price for invoice. | | | | | | | | VALUE | | | | | | Product Account > Original Price | | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | | | | TAX | | | | | | | | | | | | | | | | VALUE | | | | | | Product In Order > Item Sales Tax Price OR Product in Order > Marketplace VAT Item Price OR Product In Order > VAT Item Price Orders > Total Shipping Sales Tax OR Orders >Total Shipping Marketplace VAT OR Orders >Total Shipping VAT | Based on the Account > Country if we have selected “United States“ we are using the Sales Tax Group, if “United States“ is not selected or nothing is selected we check the Marketplace VAT Group and if empty the Internal VAT Group.
If the line is for shipping we need to specify the shipping cost tax/vat | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | | | | DUTIES | | | | | | | | | | | | | | | | VALUE | | | | | | Hardcoded as “0“ | | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | | | | LOCATION | | | | | | | | | | | | | | | | | | | LOC_ID | | | Hardcoded as ““ | | | | | | | | | | | | NAME | | | Hardcoded as ““ | | | | | | | | | | | | QUANTITY | | | Product In Order > Quantity | | | | | | | | | | | | TYPE | | | Hardcoded as “DC“ | | | | | | | | LINE_NET_PRICE | | | | | | | | | | | | | | | | VALUE | | | | | | Product in Order > Item Price Orders >Shipping Service Cost | If the line is for shipping we need to specify the shipping cost | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | GROSS_TOTAL | | | | | | | | | | | | | | | | VALUE | | | | | | | | | Order > Order Total Amount | | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | TAX | | | | | | | | | | | | | | | | VALUE | | | | | | | | | Order > Total Sales Tax OR Order > Total Marketplace VAT OR Order > Total VAT Orders > Total Shipping Sales Tax OR Orders >Total Shipping Marketplace VAT OR Orders >Total Shipping VAT | Based on the Account > Country if we have selected “United States“ we are using the Sales Tax Group, if “United States“ is not selected or nothing is selected we check the Marketplace VAT Group and if empty the Internal VAT Group.
If the line is for the shipping we need to specify the shipping tax/vat | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | POSTAGE | | | | | | | | | | | | | | | | VALUE | | | | | | | | | Order > Shipping Service Cost | | | | | | | | | | | | CURRENCY | | | Order > Order Currency | | | | | SPECIAL_INSTRUCTIONS | | | | | | | | | | N/A | |
Hemi Sales Tax, Marketplace VAT and Internal VAT overview and logic.
For the ease of use we will use the group names for reference instead mentioning all of the fields in the document. From now on these are the fields which will be used for any VAT/Sales Tax purposes.
| Sales Tax Group | Marketplace VAT Group | Internal VAT Group |
|---|---|---|
| Orders >Total Sales Tax | Orders > Total Marketplace VAT | Orders > Total VAT |
| Orders >Total Shipping Sales Tax | Orders > Total Shipping Marketplace VAT | Orders > Total Shipping VAT |
| Product In Order > Item Sales Tax Price | Product in Order > Marketplace VAT Percent | Product In Order > Vat Item Price |
| Product In Order > Sales Tax Percent | Product in Order > Marketplace VAT Item Price | Product In Order > Vat Percent |
| Product In Order > Item Shipping Cost Sales Tax | Product in Order > Marketplace VAT Item Shipping Cost | Product In Order > Vat Item Shipping Cost |
Please note the priority of the fields is Sales Tax, Marketplace VAT, Internal VAT. This is controlled based on the Account > Country field where if we have selected “United States“ we are using only the Sales Tax Group even if they are empty. If we have Account > Country != “United States“ or not selected we are using the Marketplace VAT Group or the Internal VAT Group. Based on the priority we first check if we have Marketplace VAT and use it if not we check the Internal VAT Group and even if there is nothing calculated here we send “0”. Please note even if some of the fields are “null” in the database we still need to send them as “0“
Please note this is required because not all integrations work in the same way.