John Lewis Orders Read FTP
The purpose of this document is to detail the read of orders from John Lewis and all accompanying functions to ensure seamless and sameness in the process of management on Marketplaces
The Edge will be exporting each order in a separate file that we have to read and store in Hemi. Each order will be in a txt file on the designated FTP folder and we should only read said order when we find the same file name with a “.DONE” extension at the end. We are not to read the .DONE file but use it only to acknowledge the original file is actually competed and can be processed.
Standard Order Filename Format: order-yyyymmddhhmmssnnnnnn.txt
Standard Folder structure: /live/incoming/ (for testing /uat structure will be used)
Ftp Path Label for the setting: OrderDownload
File format is slightly odd - there are two sets of headers: On line 1 there will always be the headers for the order main section. line 2 will be the values for the Order itself. Line 3 contains the headers for the order items section. From line 4 onwards we have the actual values for the ordered products. If an order has only 1 product that’s it, we have 4 lines. If there are 3 products in an order there is a total of 6 content lines (one for each of the SKUs ordered). At the end of the file there is always a checksum value noting how many lines there should’ve been in this file which we should use as a validation and if it doesn’t match the actual total of lines we read we should set the order on an Incomplete status with a corresponding error message: “There is a mismatch between expected lines and actual read lines within the order file”. The error should be stored in the new Order Error table. This error is with a high severity so the order should go to an “Incomplete” status if we face it
Example order file:
order-20220315100113192578.txt
Example Mapping: Please note there are two mapping sections below for better differentiation between the main order section and the ordered items section
Order:
| Integration Field | Integration Notes | Integration Format | Hemi Mapping | Hemi Notes |
|---|---|---|---|---|
| order_number | The unique purchase order reference (to be used when updating The Edge) | 1 alphabetic character followed by 9 numeric digits | Order > Marketplace Order ID | |
| order_date | Date and time at which the order was created | YYYY-MM-DD HH:MM:SS | Order > Order Created Time | |
| customer_ref | N/A | |||
| retailer_ref | JL internal reference (specific to a single delivery) | 23 digits, numeric | Order JL > Retailer Reference | |
| po_enduser | JL customer order number (to be quoted in all customer correspondence) | 9 digits, numeric | Order > Selling Manager SalesRecordNumber | |
| currency_code | N/A | Hardcode GBP | ||
| carrier | N/A | |||
| service | N/A | |||
| giftwrap | Contains 'n' to indicate not used | N/A | ||
| signature | Contains 'n' to indicate not used | N/A | ||
| expected_delivery_date | Set when the order is placed (order date plus lead-time) | YYYY-MM-DD HH:MM:SS | Order > Ship By Date | Might need to move it to a new field if it turns out it is for final delivery to Buyer |
| shipping_title | Customer title | Order > Shipping Buyer Title | NEW FIELD (open to suggestions) I think we’ve seen this enough times to finally expand it in a new field and start using it in new (and old when we get to that) integrations | |
| shipping_full_name | Customer forename(s) and surname | Order > Shipping Buyer Name | ||
| shipping_address_1 | Property | Order > Shipping Street 1 | ||
| shipping_address_2 | Street | Order > Shipping Street 2 | ||
| shipping_address_3 | Town / City | Order > Shipping City | ||
| shipping_address_4 | N/A | Every The Edge connection user can set the way they utilise their fields. John Lewis have decided that this is the way they want their address set, hence why these fields are not used by them at all | ||
| shipping_city | N/A | |||
| shipping_state | N/A | |||
| shipping_postcode | Postcode | Order > Shipping Postal Code | ||
| shipping_country | Country abbreviation | Order > Shipping Country Code | ||
| shipping_phone | Customer mobile number | Order > Shipping Phone | ||
| shipping_email | Customer email address | Order > Buyer mail | ||
| invoice_title | N/A | As we will not receive “Invoice” fields from JL we are to automatically fill in our Billing fields corresponding to the available Shipping ones | ||
| invoice_full_name | N/A | |||
| invoice_address_1 | N/A | |||
| invoice_address_2 | N/A | |||
| invoice_address_3 | N/A | |||
| invoice_address_4 | N/A | |||
| invoice_postcode | N/A | |||
| invoice_country | N/A | |||
| invoice_phone | N/A | |||
| invoice_email | N/A | |||
| comments | N/A | |||
| test_flag | 'Y' or 'N' | N/A | We don’t have use of this field at the moment |
Ordered Items:
| Integration Field | Integration Notes | Integration Format | Hemi Mapping | Hemi Notes |
|---|---|---|---|---|
| line_ref | Item sequence within this purchase order | Numeric | Product in Order > Item Order Line ID | |
| additional_ref | The consumer unit EAN code for this item | 13 digits, numeric | *Product in Order > SKU |
AND Product in Order > EAN (New field!) | We are to Map the SKU in the Product in Order via the passed EAN from JL. This is to be mapped by the Item Account > Marketplace EAN or the Item > EAN (in that order of relevance) to get the correct Item > SKU value to store in this field If we can’t map an EAN we are to store an error with the message “Product with EAN {{EAN}} could not be matched with any existing item” - where {{EAN}} should be the value from the file. As the order will not have a SKU in this case we are to set the order on an “Incomplete” status | | part_number | JL product code for this item | 8 digits, numeric | Product in Order > Channel Item ID | | | description | Product description | | Product in Order > Item Title | | | quantity | | | Product in Order > Quantity | | | cost | JL selling price for one unit of this item | | Product in Order > Item Price | | | shipping | | | N/A | | | subtotal | Selling price multiplied by ordered quantity | | N/A | | | tax | | | N/A | | | tax_rate | | | N/A | | | tax_code | | | N/A | | | total | | | N/A | | | promised_date | In practice this will be identical to the order-level expected delivery date at launch of Condor. | YYYY-MM-DD HH:MM:SS | N/A | This field is more important when we update information back to JL |
Additional Information:
- Workflow steps Besides reading the files there is no specific expectation of us to do anything with the files. Having in mind we will have also a “.DONE” file to take care of I believe the folder will fill up quite fast so it’d be better to move both such files to a sub /processed folder of the one from which the file was picked to keep things clean
From there on every order we receive we treat as a RFS, Paid order that needs to be stored in Hemi and go through all standard processes and validations (this includes but is not limited to taxes, debundling, country refinement, payment records creation, etc.)
We are not expecting to ever receive a subsequent order files for the same order which means that
- We are not expecting to get any subsequent cancellations and/or refunds that we need to store
- If we do receive such a file we want to map it correctly and store the right error: “Duplicated order file received from JL for this order”. This error should go in the new Order Error section. It is with a low severity and should not have any effect on the already previously stored order
- We need to calculate the Order Total & Subtotal as well - we are not going to be receiving any shipping cost associated so the subtotal will be equal to the total too
- If we face a completely broken file, such that we can’t begin or complete reading correctly we should move it into a sub “/error” folder of the one from which we’ve picked the order file
- Just a reminder - we are to process multiple files at a single run of the script otherwise we will get a huge backlog of files for processing 🙂