John Lewis Cancellation FTP

Adding one more validation at the end of the document | | 1.2 | Danail Deltchev | | Tracking (Cancellation) Mapping change to reflect the actual file requirement Tracking change in need to send every line (only those that are requested for post Shipment Refund) + Validation to allow only full item refunds | | 1.3 | Aleksander Cvetkov | | Added path labels for pre-shipment and post-shipment cancellation. | | 1.4 | Danail Deltchev | 20.06.2022 | Adding Validation - neither flow can send a second update for the same order until the first one is completed (Successfully or not) Fixing folder structure for error files expectation | | 1.5 | Hristiyan Georgiev | 26/04/2023 | Changed hardcoded value of “CANCELLED POST-DISPATCH” |

The Purpose of this document is to specify the details around Pre and Post Shipment cancellation update of orders on John Lewis Marketplace

There are no refunds as such on John Lewis. The only such actions we are allowed to do are via Cancellations. There are two types of cancellations we can define -pre Shipment (Cancellation Acknowledgement) or post Shipment (Tracking update with status “Dispatched” (CANCELLED POST-DISPATCH).

The way we handle Refunds in Hemi is the same as for everywhere else with just a couple of validations we want to incorporate for JL. This means that through the UI the users will be able to post requests for refunds at any point in time with any value. Expectation is that there will be no Shipping cost with John Lewis (if you refer to the order read doc there is no mapping for it as well) meaning it should be easier for any calculations. We want to check for every requested refund that it totals to at least one full quantity and equal ONLY to devision of full quantities per SKU and process further only after this. We should also incorporate our standard validations for availability (if the product is available for the requested refund - number of products cancellation). For pre-shipment cancellation only “acknowledged” lines can be cancelled. For post-shipment only shipped lines can be cancelled. Based on these two we need to build one of two files to JL with the correct cancellation OR return a validation error in Hemi

Each of the below flows should also have a validation to not allow for a second cancellation (pre or post shipment) do be sent to the marketplace IF there is a non completed one before that. This means that if we have 2 “pending” refunds, we should pick only one and until it completes (successfully or not) not pick the second one. Same goes if we have a cancellation on a sent status - a second or third logged refunds will have to wait for the previous ones to be completed (New - added with v 1.4)

Below the standard image of example order update file setup

Note: sadly there is no example file at this point but based on the above picture and below mapping explanations I believe we can create the needed file easily

Cancellation Acknowledgement

Ftp Path Label for the setting: OrderCancel

Filename format: cancel-yyyymmddhhmmssnnnnnn.ack

Example Mapping: Please note there are two mapping sections below for better differentiation between the main order section and the ordered items section

Order:

Ordered Items:

OR Refund Row Line: Count quantity | Calculated available qty for cancellation based on the Refund Row > Amount vs Order Item > Item Trans Price comparison OR Pick the quantity count from the refund row lines directly. Just as a reminder we should process the refund further ONLY if it is equal to full quantity of item refunds. If there is anything outstanding that is not enough to cover the next quantity there should be an error Note: dev team to decide on the method and comment so I can tidy this up for future readings | | comments | | Text | 255 | N/A | |

Additional Information:

• We need to always spit out all items within the order. This means that if we are triggering a cancellation acknowledgement for 1 SKU in an order when there are 2 SKUs we need to send both SKUs again but have 0 for one of the products in the Quantity field
• Once the file is successfully uploaded we always need to upload a file with the same name with a “.DONE” extension. This file can be empty but this is the trigger for The Edge to pick the actual file and process it
• In case we don’t have an exact value match (there is more or less in the Refund Row > Amount than for a specific SKU to have “full” quantity) we are to block the cancellation with a validation error for it to be updated accordingly by whoever is requesting it

• The way JL treat their file connection system is slightly odd. If a file that we’ve generated fails to be read / uploaded by them they will rename the original file (in our case let’s say “cancel-yyyymmddhhmmssnnnnnn.ack”) to have a “.failed” extension - “cancel-yyyymmddhhmmssnnnnnn.ack.failed“.

  Additionally JL will create in the folder from which the file was picked a “.err” file (“cancel-yyyymmddhhmmssnnnnnn.ack.err“) that will contain the correct error. We need to incorporate this in our model so we can achieve the following flow:

  • Identify Cancellation Acknowledgments to be sent (pre-shipment cancellations)
  • Send Cancellation Acknowledgement update to JL. Update relevant Order Payment record to a “sent” status
  • Wait for file to be processed
    • If Success (which is indicated by our files completely disappearing from the FTP folder they were created in) update Order Payment in Hemi and update all relevant statuses on the order and items / lines (If the order is now fully cancelled the tool_status should be updated. If not proceed only to Product in order and Order Item Lines internal payment status updates, and filling in the refunded values on all relevant levels)
    • If error (which is indicated either by our files being renamed to “.failed” OR staying on the FTP without being picked for more than 24 hours since their creation) - store the correct error in Hemi and block further updates until changed. The correct error is:
      • If “.failed” - pick the error as seen in the corresponding “.err” file
      • If time validation - “The generated Cancellation Acknowledgement file hasn’t been processed for more than 24 hours”

Tracking update

Ftp Path Label for the setting: OrderRefund

Filename format: tracking-yyyymmddhhmmssnnnnnn.csv

The tracking update is the only slightly different order update file. It doesn’t have to contain only one order it can contain multiples. It looks like a much more standard csv document with only one header row and duplications going down if and where needed per items. For this purpose the example mapping below is only one table as well

Example Mapping:

e.g 2017-02-22 12:23:00 (Y-m-d H:M:S) | Date | 20 | N/A | | | carrier | Carrier name e.g “Green van” This will have been set on Dispatch of the order, but if provided this will replace the value defined on dispatch | Text | 100 | N/A | | | tracking_url | | Text | 2000 | N/A | | | dispatch_date | yyyy-mm-dd | Date | 10 | N/A | | | supplier_delivery_date | yyyy-mm-dd | Date | 10 | N/A | NOW() |

Additional Information:

• Due to the fact we are not passing Quantity for the “Tracking” update but just line_ref and Status we should add a validation to allow only for full item refunds to take place (a full product in this case should exclude items that are already Cancelled or still not shipped as lines). In case we are trying to under refund we should store an error in Hemi “Post Shipment we can refund only full line items”. If we are trying to over refund this usually means we are trying to refund non shipped items as well to which please refer for the validation and error message in the Note below
• Once the file is successfully uploaded we always need to upload a file with the same name with a “.DONE” extension. This file can be empty but this is the trigger for The Edge to pick the actual file and process it
• In case we don’t have an exact value match (there is more or less in the Refund Row > Amount than for a specific SKU to have “full” quantity) we are to block the cancellation with a validation error for it to be updated accordingly by whoever is requesting it

• The way JL treat their file connection system is slightly odd. If a file that we’ve generated fails to be read / uploaded by them they will rename the original file (in our case let’s say “tracking-yyyymmddhhmmssnnnnnn.csv”) to have a “.failed” extension - “tracking-yyyymmddhhmmssnnnnnn.csv.failed“.

  Additionally JL will create in the folder from which the file was picked a “.err” file (“tracking-yyyymmddhhmmssnnnnnn.csv.err“) that will contain the correct error.

  Note: the Tracking Update file contains multiple records and we haven’t seen the error response file. Expectation is that either the whole file will fail or we will have a line by line indicator to know what failed.

  We need to incorporate this in our model so we can achieve the following flow:

  • Identify Tracking updates to be sent (post-shipment cancellations)
  • Send Tracking update to JL. Update relevant Order Payment record to a “sent” status
  • Wait for file to be processed
    • If Success (which is indicated by our files completely disappearing from the FTP folder they were created in) update Order Payment in Hemi and update all relevant statuses on the order and items / lines (If the order is now fully cancelled the tool_status should be updated. If not proceed only to Product in order and Order Item Lines internal payment status updates, and filling in the refunded values on all relevant levels)
    • If error (which is indicated either by our files being renamed to “.failed” OR staying on the FTP without being picked for more than 24 hours since their creation) - store the correct error in Hemi and block further updates until changed. The correct error is:
      • If “.failed” - pick the error as seen in the corresponding “.err” file
      • If time validation - “The generated Tracking update file hasn’t been processed for more than 24 hours”

NOTE - General validation - In the very edge case we have the 2 types of communication both in the same Order Payment record in Hemi we should directly put it on error with the following message “Both pre Shipment and post Shipment cancellation attempts can’t be made in the same Order Refund record. Please create new refund requests and split the products that are shipped from these that are not”

NOTE - For reading the responses and updating after success we are using custom refund processor. After the refund processor from the Abstraction is finished, we will add it in this integration.