Return Update Api

Problem statement:-

It has been noticed that there is one order having a single quantity SKU on which a return request is created and then because of some reason (whether Flipkart has cancelled the return or sending updated tracking with a return status update event), Again a new return request is made with a different tracking number and customer return number on the same order item but WMS return API ignored this request as the order item was already occupied with the old return.

Resolution approach:-

Create a return update API (update by customer return number), update the cancelled status in the return status logs (if we receive cancelled event else we will update the status which we receive from sales channel) against the old return and un-map/set it from the order item (only in case of cancelled event else we will not change anything), so that we can create a new return. When the second return request is made, we will create a new return.

Request URL:-

https://return-dot-eshopbox-client-portal-prod.el.r.appspot.com/_ah/api/esb/v2/return/{{customerReturnNumber}}

Request Body:-

{
  "trackingID":"123456789",
  "status": "cancelled",
  "returnDate" :"2022-07-12T02:05:46+05:30",
  "courierName": "TestCarrier",
  "remarks": "delievered"
}

Response Body:-

This API will return HTTP response code - 200

{
  "trackingID":"123456789",
  "status": "cancelled",
  "returnDate" :"2022-07-12T02:05:46+05:30",
  "customerReturnNumber" :"12102568821409968623",
  "courierName": "TestCarrier",
  "remarks": "delievered"
}

API handlings:-

Different scenarios that this API will handle.

Scanario 1:- If we receive the cancelled status event from channel then:-

We will be getting customerReturnNumber from our api url, based on that customerReturnNumber we will check whether the trackingID present in our system matches with the trackingID present in our request body.

1) If trackingId matches then we will simply log the status in return_shipment_status_logs as 'cancelled' and un-map it from its order items by updating return flag to “0“ and removing the return_shipment_id.

Also we will be updating remarks and courierName in reuturn_shipments and return_shipment_status_logs only if we are getting them in our request body.

Queries --

INSERT INTO return_shipment_status_logs (return_shipment_id,return_status,external_updated_at) VALUES (3234224,'cancelled','2022-07-12T02:05:46+05:30');

UPDATE order_items SET returnFlag = '0' AND return_shipment_id = 0;

UPDATE return_shipments SET courierName = '{{courier name from request body}}' WHERE customerReturnNumber = '{{customer return number}}';

UPDATE return_shipment_status_logs SET remarks = "{{remarks from request body}}" WHERE return_shipment_id = 2322424;

2) If trackingId doesn’t matches then we will first check the return shipment status logs against the return shipment, if the latest status is “received“ then we will not update the trackingID else if status is not “received” then we will update the trackingID in return_shipments table and then we will log cancel status in return_shipment_status_logs.

Also we will be updating remarks and courierName in reuturn_shipments and return_shipment_status_logs only if we are getting them in our request body.

Note: we will only execute 1st and 3rd query only if return status log in return_shipment_status_log is not 'received'.

Querie:

UPDATE return_shipments SET trackingID = '{{new tracking id from request body}}' WHERE customeReturnNumber = '{{customerReturnNumber}}';

INSERT INTO return_shipment_status_logs (return_shipment_id,return_status,external_updated_at) VALUES (3234224,'cancelled','2022-07-12T02:05:46+05:30');

UPDATE order_items SET returnFlag = '0' AND return_shipment_id = 0;

UPDATE return_shipments SET courierName = '{{courier name from request body}}' WHERE customerReturnNumber = '{{customer return number}}';

UPDATE return_shipment_status_logs SET remarks = "{{remarks from request body}}" WHERE return_shipment_id = 2322424;

Scanario 2:- Flipkart is sending updated tracking with a return status update event

Based on the customerReturnNumber from our api url, we will check whether the trackingID present in our system matches with the trackingID present in our request body.

1) If trackingId matches then we will simply log the event given by the Sales Channel in return_shipment_status_logs table and also we will be updating courierName and remarks as well (if we are getting them in request body).

Query --

INSERT INTO return_shipment_status_logs (return_shipment_id,return_status,external_updated_at) VALUES (3234224,'received','2022-07-12T02:05:46+05:30');

UPDATE return_shipments SET courierName = '{{courier name from request body}}' WHERE customerReturnNumber = '{{customer return number}}';

UPDATE return_shipment_status_logs SET remarks = "{{remarks from request body}}" WHERE return_shipment_id = 2322424;

2) If trackingId doesn’t matches then we will first check the return shipment status logs against the return shipment, if the latest status is “received“ then we will not update the trackingID else if status is not “received” then we will update the trackingID in return_shipments table and also we will log any update given by the Sales Channel using the below queries.

Also If we are getting remarks and courierName then we will update them as well.

UPDATE return_shipments SET trackingID = '{{new tracking id from request body}}' WHERE customerReturnNumber = '{{customerReturnNumber}}';

INSERT INTO return_shipment_status_logs (return_shipment_id,return_status,external_updated_at) VALUES (3234224,'received','2022-07-12T02:05:46+05:30');

UPDATE return_shipments SET courierName = '{{courier name from request body}}' WHERE customerReturnNumber = '{{customer return number}}';

UPDATE return_shipment_status_logs SET remarks = "{{remarks from request body}}" WHERE return_shipment_id = 2322424;` `

Apps and Automations

Return Update Api

Related content