John Lewis EDI Order read

Also added mapping for Marketplace VAT fields Also added mapping for Country and Country Code | | v1.5 | Bogomil Pavlov | 03.05.2023 | Added Validation for Product in Order > Item Order Line ID | | v1.6 | Danail Deltchev | 04.05.2023 | Order > Item Order Line ID validation reword | | v1.7 | Danail Deltchev | 10.05.2023 | Added specification for datetimeperiod when type parameter 102 (format YYYYMMDD) | | v1.8 | Danail Deltchev | 16.05.2023 | Adding “Additional Information” with specifically focusing on the order status when downloading | | v1.9 | Danail Deltchev | 22.05.2023 | Specifying how to connect Account to FTP for the EDI connection | | v1.10 | Danail Deltchev | 29.05.2023 | Addition to consume files with no extension and to populate ChannelItemId as part of the enrichment process | | v2.0 | Danail Deltchev | 08.06.2023 | Multiple Orders in the same file allowed | | v2.1 | Danail Deltchev | 13.06.2023 | Map Phone number from free text field |

The purpose of this page is to scope in detail the needs and ways of processing an EDI order for John Lewis. This is needed as an addition to our existing process (communicating with The Edge) as John Lewis have two operation models - Sellers and Suppliers. When our clients are Sellers (they are responsible for the sales on John Lewis website) they can fully go through The Edge platform integration. If our client is a Supplier for John Lewis (which means John Lewis are purchasing the goods from our client directly) they insist to provide the order via EDI message and receive an EDI Invoice message back

This document will detail the EDI Order message we need to read.

To achieve this we’ve made an agreement with an intermediary EDI provider OpenText who are supplying an SFTP for us to exchange the relevant messages.

As of late we’ve taken to use a library that converts EDI messages directly into JSON formats (mapping below will follow the JSON file)

What we will need to do is setup a standard SFTP connection through our FTP Credentials with type “John Lewis EDI” and establish the path in there for orders with label “OrderGet”. Then for this FTP to be used in the specific account it will need to be selected under “Account John Lewis” in a new filed - EDI FTP Credentials (v1.9)

The functionality will then need to check the selected folder for files and when it finds such to pick them up, transform them to JSON files and then import the orders into Hemi. All files should be picked, no matter of their extension (v1.9). A file can contain multiple orders (please see section “Multiple Orders” below). If we successfully read the file and store the orders in Hemi we should move the file to Processed folder in the location from where the file was picked. If there is a problem with even one order we should move the file in an Error folder in the same location again

Difference between which order download should work (The Edge or EDI) for the time being should be controlled only by switching the correct cron within the crontab.

Note: Besides the order download going through EDI all other functions should still work the same way - Acknowledgment, Shipment, etc. should be going through the standard The Edge functions (note is here for general flow understanding).

As this is essentially just an order import all standard functions as per the abstraction should work too - check for bundles, enrich taxes, calculate total and subtotal, create payment record, create order item lines, etc.

Example EDI and JSON Order file (please note I am not adding the file info below as well as it will become heavy for reading) - please note the examples are with a single order in a file:

ORDERS.json

edi sample.txt

Below a bit of general EDI explanation for better understanding when reading files and transformation and mapping needs:

Let me start the explanation with EDI is THE WORST! The EDI document consists of Segments, Sub-segments and Parameters in the sub-segments. The Segments are different parts of the message attached above. Basically every single row is a segment in the example. The segment itself is identified by the beginning letters. Segments can be duplicated with different sub-segments identified and/or different parameters showcasing different type of information - example: DTM. This is a segment for identifying Date and Time for the order. In the above example DTM+2 identifies the date and time for “Delivery date requested” where DTM+137 identifies the date and time for “Message creation time”. The “DTM” is the identifier for the segment beginning. With “+” we add the sub-segments and with “:” we segment the parameters within the sub-segment. In the case of DTM we have only 1 sub-segment with 3 added parameters. Those are type, timestamp and timestamp identification. If we take segment UNB for example (which is something like the envelope of the message) we have multiple sub segments, each of which has one or more parameters (+UNOA:3 - sub-segment for syntax and version, holds 2 parameters, first one for syntax, second one for version // +120702:0931 - sub-segment for date and time of message preparation, holds 2 parameters, first one for date (YYMMDD), second one for time (HHTT) and so on and so on). Segments are separated by an ending apostrophe ('). Most EDI files have breaks as well after reach segment end but this is not necessary, it can all be one long string on 1 row.

This is basically the whole structure idea and building and reading process of EDI. We are going to use the EANCOM standard with JohnLewis. More can be read in the following pages:

http://www.eancom.com/s3-eancom2002.htm - All docs

http://www.eancom.com/ean02s3/part2/orders/toc.htm - Orders section specifications

Now - Mapping - the library we use transforms the EDI file to a JSON one (provided example above) and we will map all fields coming from there. Mapping is broken down again per the different segments (parts of the JSON) to illustrate what is needed and what not

OR Orders > Order Created Time | There are two DTMs that we are interested in. We are to recognise them by the value in field datetimeperiodQualifier. IF we have value 2 in there it should go in Orders > Delivery By Date. IF we have value 137 it should go in Orders > Order Created Time Both are to be stored in unix timestamp in our DB. Format in which values are received can be picked from datetimeperiodFormatQualifier. If the value is 102 the date will be in format YYYYMMDD. As we don’t receive Time in this format we’d like to keep it as 00:00:00 (added via v1.7) If the value is 203 the date will be in format YYYYMMDDHHMI. IF we have any other value in datetimeperiodQualifier but 2 or 137 we are to skip this datetimeperiod segment | | SG2 | | | | | | | | | nameAndAddress | | | | | We are interested only in the segment with partyQualifier = DP. All other nameAndAddress segments can be skipped | | | | partyName | | | | | | | | | partyName | | Orders > Shipping Buyer name | As multiple parameters are possible in this EDI segment I believe the field in the JSON can be an Array or not. In case there are multiple values please concatenate them all with an “ “ between each of them (if there is nothing to concatenate or only one value there should be no empty or trailing spaces anywhere please) | | | | street | | | | | | | | | streetAndNumberpoBox | | Orders > Shipping Street 1 AND Orders > Shipping Street 2 | As multiple parameters are possible in this EDI segment I believe the field in the JSON can be an Array or not. In a case where we have a single value we are to simply put it in Shipping Street 1. If we have multiple values the first one is to go into Shipping Street 1 and concatenate all additional values with an “ “ between them in Street 2 (if there is nothing to concatenate or only one value there should be no empty or trailing spaces anywhere please) | | | | cityName | | | Orders > Shipping City | | | | | countrySubentityIdentification | | | Orders > Shipping State Province | As multiple parameters are possible in this EDI segment I believe the field in the JSON can be an Array or not. If it is an Array it is expected to hold multiple values separated by a comma. We want to take the last value for the State Province. If it is only one value we are to map it accordingly | | | | postcodeIdentification | | | Orders > Shipping postcode | | | | | | | | (v1.4) Orders > Shipping Country Code | (v1.4) As this information is mandatory for Hemi we want to use the Account > Country and map the correct country code | | | | | | | (v1.4) Orders > Shipping Country Name | (v1.4) As this information is mandatory for Hemi we want to use the Account > Country | | SG7 | | | | | | | | | currencies | | | | | | | | | currencyCoded | | | Orders > Currency | (v1.4) As Order > Currency is mandatory for Hemi if it is missing for the order we want to use the Account > Exchange Rate Calculator Currency | | SG25 | | | | | | Each of the objects within SG25 corresponds to a single Product in the order where we have to read the different sub elements to gather all needed information | | | lineItem | | | | | | | | | lineItemNumber | | | Product in Order > Item Order Line ID | (Mapping added with v1.3) (v1.6)Here we would like to include additional validation apart from what we have in our abstraction and make sure the Product in Order > Item Order Line ID will be always unique for the Order. Additionally in case this value is missing or not unique we’d like to store the order with the info we have and set the order as “Incomplete” and we will add an error message in Order Error. | | | | actionRequestnotificationCoded | | | Orders > Notes | As SG25 is a grouper of all products within the order and the actionRequestnotificationCoded is per product where our field is on Order I’d like us to concatenate any such notes in the following format: we state the SKU of the product, " - " and add the actual note. If we have notes for multiple products we just need to add them all to the field with using “ AND ” as a separator between them for human readability 🙂 | | | | itemNumberIdentification | | | | | | | | | itemNumber | | Product in Order > EAN | We are to use this value to also map the Product in Order > Item SKU (v1.4)(mapping via Product Account > Marketplace EAN or Product > EAN. With priority is Product Account > Marketplace EAN).

In case a mapping can’t be done we have an ordered product without SKU = Incomplete order | | | additionalProductId | | | | N/A | | | | itemDescription | | | | | We are interested only in such itemDescription segments that have itemDescriptionTypeCoded = F. Any other itemDescription segments are to be skipped | | | | itemDescription | | | | | | | | | itemDescription | | Product in Order > Item Title | | | | quantity | | | | | As multiple qty segments can be provided in an EDI file we want to pick only the one with quantityQualifier = 21. Any other are to be skipped | | | | quantityDetails | | | | | | | | | quantity | | Product in Order > Item Quantity | | | | freeText | | | | | This is a segment holding just pure free text for an order. It can hold any information decided to be provided by John Lewis or depending on their mapping it can be info coming directly from the buyer as well. As there can be multiple such segments I want to highlight 2 things:

1. We are only interested in lines with textSubjectQualifier = PUR
2. This segment can be repeated even with the same textSubjectQualifier. This means we can have multiple elements holding different values that need to go into the same field. More on how to concatenate such cases below
3. This segment can be an Array or not so we need to pick all information together | | | | textLiteral | | | | | | | | | freeText | | Product in Order John Lewis > EDI Additional Information AND Orders > Shipping Phone (v2.1) | NEW FIELD - this is to concatenate all free text information in this sub-segment. In case the field is an array it can be taken and added as is without any amendments or additional concatenations but we need all values to be in the field in Hemi. In case there are multiple free text segments with textSubjectQualifier = PUR we need to concatenate their values in the field using “ AND “ as a separator for human readability

The free text value for each product seems to be in the same format which holds confirmation of the address AND the tel number. Example: "textLiteral": { "freeText": [ "CUSTOMER ORDER NO 0028164280", "CONTACT TEL 07123 456789", "CONTACT TEL020 12345678", "JL DIRECT PO NUM 027689210", "MR JONES" ] } From this value we can parse that in one of the strings we have CONTACT TEL - this is from where we have to pick the phone. Everything after the tag CONTACT TEL is to be picked for the Shipping Phone value. Note - as this is per item we want to track this from all items and store the first phone we find, no need to parse the other items after this (if it is easier to store the last one this is also acceptable) (v2.1) | | | SG28 | | | | | This segment holds the pricing information | | | | priceDetails | | | | As multiple price segments can be provided in an EDI file we want to pick only the one with priceQualifier = AAA. Any other are to be skipped | | | | | priceInformation | | | | | | | | | price | Product in Order > Item Price | Please note this is NET value for the price. This means we need to pick up the VAT as well and calculate the final buyer value cost. For this purpose we are to use the TAX % that is provided in the next available SG34segment (as mapped below) with code “VAT” and calculate the full price before storing it or using it any further for Total and Subtotal calculations. Example - if we have a price of 200 and VAT of 20% we are to store in the Hemi Item Price field 240 and use this value for any additional calculations | | | SG34 | | | | | As multiple Tax segments can be provided in an EDI file we want to pick only the one with dutytaxfeeTypeCoded = VAT. Any other are to be skipped | | | | dutytaxfeeDetails | | | | | | | | | dutytaxfeeDetail | | | | | | | | | dutytaxfeeRate | Product in Order > Marketplace VAT Percent | This is the value to be used for calculating the Marketplace VAT Value and combining with the Net price from above arriving at the gross unit price to be stored in Item Price (v1.4) Using this VAT percentage we want to calculate also: Product in Order > Marketplace VAT Item Price Orders > Total Marketplace VAT (by multiplying the Product in Order > Marketplace VAT Item Price by the Product in Order > Item Quantity and summing it for all items in the order) | | sectionControl | | | | | | If we’ve reached this segment this means there are no more items to be added to the feed | | messageTrailer | | | | | | Reaching this segment should mean that we’ve successfully transformed the whole EDI file to a JSON so it can be used for validation |

Additional Information

• When downloading the orders we are to treat them always as RFS. This means that for us this is a paid and fully ready order when processing it through the abstraction and utilising the enrichments. The only two Hemi Order statuses expected to be used while storing the orders are RFS or Incomplete (in the cases where our RFS order is missing something crucial) (v1.8)
• As part of the enrichment we should make sure every product we store in Product in Order also has a ChannelItemID. As this is NOT mapped in the payload we should always pick it from its relevant Product Account. If there is no ChannelItemID we don’t expect any errors or warnings on this stage. simply to try and pick it and if it is there add it to the field on Product in Order if not, empty field and that’s it (v1.9)

Multiple Orders (v2.0)- due to unforeseen circumstances we need to factor in multiple orders in the same file. For the EDI file this would look like one message with opening and closing UNB and UNZ segments (the envelope) and then the order messages one after the other. Each order message starts with the UNH segment and ends with the UNT segment after which (if there are other orders) another UNH segment will start. The library we use is processing it correctly and presenting us with a JSON with the different orders one after the other which we need to process exactly as described above with their enrichments and so on. If we have any validations that are passing over the full file before we process each order those are to treat the whole file together - this means if we have an error with one of the orders in the edi file that permits us to store the order and for us to move the file to an Error folder we want this to trigger the same action for the whole file with all orders inside

Any edi files and their JSON counterparts should be kept in .temp no matter if we hit an issue or not. Once downloaded those files disappear from the target FTP and if we delete them on an error hit it is possible to lose them