Create returns new API
- Anushi Dhaketa
We will make the below endpoint for creating returns.
API URL - https://wms.eshopbox.com/api/createReturn
Project - ESB-WMS-API
Step 1: We will get the below request body to create return in Eshopbox.
Sample request body -
{
"channelId": "TATA CLIQ VELOCY KAPAS KRAFT",
"customerOrderId": "OD119208447831346000",
"shipmentId":"",
"orderDate": "2020-02-29 15:39:11",
"isCOD": true,
"invoiceTotal": 4049.09,
"shippingMode":"",
"invoice": {
"number": "C00011323A000002",
"date": "2023-06-02T03:06:14+00:00"
},
"ewaybillNumber":"",
"balanceDue": 0,
"doorStepQcRequired":true,
"qcDetails":{ // check if we take this on items level or order level
"qc_color":"",
"qc_brand":"",
"qc_serial_no":"",
"qc_ean_barcode":"",
"qc_size":"",
"qc_product_name":"",
"qc_product_image":""
},
"shippingAddress": {
"locationCode": "",
"locationName": "",
"companyName": "",
"contactPerson": "",
"contactNumber": "",
"addressLine1": "",
"addressLine2": "",
"city": "",
"state": "",
"pincode": "",
"gstin": ""
},
"items": [
{
"itemID": "DB9U03FMGWZ",
"productTitle": "Pace Barnes",
"quantity": 1,
"itemTotal": 4049.09,
"hsn":"",
"mrp":"",
"discount":"",
"taxPercentage":"",
"returnReason": "",
"ean": "",
"length": "",
"breadth": "",
"height": "",
"weight": ""
}
],
"returnDimension": {
"length": "",
"breadth": "",
"height": "",
"weight": ""
},
"pickupLocation": {
"customerName": "John Doe",
"addressLine1": "Kapas Kraft Apparels Limited",
"addressLine2": "Banglore",
"city": "bengluru",
"state": "Karnataka",
"pincode": "560005",
"country": "India",
"contactPhone": "9998889998",
"email": "johndoe@gmail.com"
}
}Mapping of these keys to our Database
Keys in Request body | Place to be stored in DB | Mandatory |
|---|---|---|
|
| No |
|
| Yes |
| return_shipments.customerReturnNumber | Yes |
|
| No (Take current timestamp if ) |
|
| Yes |
|
| Yes |
|
| No |
|
| No |
|
|
|
|
|
|
|
| No |
|
| Conditionally yes on COD orders |
| return_shipments | Yes conditionally |
| return_shipments | if |
|
| if |
|
| if |
|
| if |
|
| if |
|
| if |
|
| if |
|
| if |
| Warehouse address | Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
|
| No |
mrp |
| No |
|
| No |
|
| No |
length |
| Yes |
|
| Yes |
|
| Yes |
|
| Yes |
returnReason |
| Yes |
| {} | Yes |
| return_shipments.dimension_length | Yes |
| return_shipments.dimension_width | Yes |
| return_shipments.dimension_height | Yes |
| return_shipments.weight | Yes |
| {} |
|
| pickup_customerName | No |
| return_shipments.pickup_addressLine1 | Yes |
| return_shipments.pickup_addressLine2 | No |
| return_shipments.pickup_city | Yes |
| return_shipments.pickup_state | Yes |
| return_shipments.pickup_postalCode | Yes |
| return_shipments.pickup_countryName | Yes |
| return_shipments.pickup_contactPhone | Yes |
| return_shipments.pickup_email | Yes |
Step 2: We will push this request body to a method checkOrderCreation().
In the method, We will check if the order is created in our system with the desired order_item.
We will use the below query to fetch the order from DB.
SELECT customerOrderNumber, itemID FROM orders
LEFT JOIN order_items ON order_items.order_id = orders.id
WHERE orders.channel_id = ? AND orders.vendorOrderNumber = {customerOrderId from requestBody}
AND order_items.itemID = {items.itemID from request body}
Case 1- If the order is already created in the system, proceed to step 3.
Case 2- If the order is not present in the system, follow the below steps - // add saurav functions
a) Make entry for order in the orders table by using the below query.
INSERT INTO orders(channel_id, customerOrderNumber, vendorOrderNumber, shipping_customerName, shipping_addressLine1, shipping_addressLine2, shipping_city, shipping_state, shipping_postalCode, shipping_countryCode, shipping_countryName,
shipping_contactPhone, shipping_email, shipMethod, billing_customerName, billing_addressLine1, billing_addressLine2, billing_city, billing_state, billing_postalCode, billing_countryCode, billing_countryName, billing_email, billing_contactPhone,
orderDate, isCOD, paymentType, subtotal, orderTotal, balanceDue, thirdPartyShipping, onHold)
VALUES ();
Key mapping -
Column in orders table | key |
|
|---|---|---|
channel_id | channelId from request body or channelId of manual app |
|
| requestBody. shipmentId |
|
| requestBody. shipmentId |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
|
|
|
|
|
|
| requestBody. |
|
|
|
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
| requestBody. |
|
|
|
|
|
|
|
|
|
|
| requestBody. |
|
|
|
|
|
|
|
| if isCOD = 1 , COD else Prepaid |
|
| requestBody.invoiceTotal |
|
| requestBody.invoiceTotal |
|
| requestBody.balanceDue |
|
| 0 |
|
| 0 |
|
We will make another method to fetch channelId of manual app if no channelId is fetched from the requestBody.
Method - getDefaultCustomAppChannelId() (Can be common for both order and return API) // add saurav’s function
We can cache this channelId. cacheKey - defaultCustomAppChannelId_{accountSlug}
b) Check if the location code is present in the DB. // saurav
Method - checkForLoation() (Can be common for both order and return API)
SELECT name, externalWarehouseID FROM warehouses
WHERE externalWarehouseID = ?;
If we get the result from the above query, proceed to next step of creating order_items.
If we do not get any result from the query, Call the below API to create the location.
curl --location -g --request POST 'https://{{accountSlug}}.auperator.co/api/v1/party' \
--header 'Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCIsImtpZCI6Ik1UaERRamxDUlRJelJVUTRRVU0wUVRJNU1FSkVOVGszUVVFeU5qVXdSa1JDTmpBeU16WTROQSJ9.eyJodHRwczovL2FwcERhdGEiOnt9LCJodHRwczovL3VzZXJEZXRhaWxzIjp7ImlkIjoxMDAzLCJ1c2VyVHlwZSI6ImRlZmF1bHQiLCJlbWFpbCI6ImFudXNoaS5kQGVzaG9wYm94LmNvbSJ9LCJodHRwczovL2FjY291bnRzIjpbInRjbnNnZ24iLCJ0Y25zYTQ4NCIsInRjbnN3OTk2IiwidGNuc2E0OTIiLCJ0Y25zZTAwMyIsInRjbnN3OTc1IiwidGNuc3c5NDAiLCJ0Y25zdzc0MCIsInRjbnN3NDA0IiwidGNuc3c0MTYiLCJ0Y25zdzUzMiIsInRjbnN3ODM4IiwidGNuc3c3MTUiLCJ0Y25zd2ExMDciLCJ0Y25zdzg2MCIsInRjbnNwYXRsaWJnZ24iLCJ0Y25zZXNiZm5mcnNodmlydHVhbG1vdmVtZW50IiwidGNuc2FnZ24iLCJ0Y25zIl0sImh0dHBzOi8vd2FyZWhvdXNlV29ya3NwYWNlcyI6WyJ0Y25zZ2duIiwidGNuc2E0ODQiLCJ0Y25zdzk5NiIsInRjbnNhNDkyIiwidGNuc2UwMDMiLCJ0Y25zdzk3NSIsInRjbnN3OTQwIiwidGNuc3c3NDAiLCJ0Y25zdzQwNCIsInRjbnN3NDE2IiwidGNuc3c1MzIiLCJ0Y25zdzgzOCIsInRjbnN3NzE1IiwidGNuc3dhMTA3IiwidGNuc3c4NjAiLCJ0Y25zcGF0bGliZ2duIiwidGNuc2VzYmZuZnJzaHZpcnR1YWxtb3ZlbWVudCIsInRjbnNhZ2duIl0sImh0dHBzOi8vd2FyZWhvdXNlcyI6W10sImh0dHBzOi8vcGFydG5lcnMiOlsiNDMxODk1IiwiMjg0MTY2IiwiODk4Njg1IiwiNjc4MTUxIiwiMTIyMDg1IiwiMjk1MzUyIiwiMTg2MTk2IiwiMTYzNTA5IiwiNTYzMzg1IiwiODIwMDYxIiwiOTI5MTMyIiwiOTI2NzY2IiwiMzExMTkxIiwiMTMwNTQxIiwiMTYzNjI0Il0sImlzcyI6Imh0dHBzOi8vZXNob3Bib3guYXV0aDAuY29tLyIsInN1YiI6ImVtYWlsfDYwODk2ZWVlYjYwODcxMDMyZTZjOGVjMyIsImF1ZCI6WyJodHRwczovL3dtcy5teWVzaG9wYm94LmNvbSIsImh0dHBzOi8vZXNob3Bib3guYXV0aDAuY29tL3VzZXJpbmZvIl0sImlhdCI6MTcxNTMyNDQ1NCwiZXhwIjoxNzE1NDEwODU0LCJzY29wZSI6Im9wZW5pZCBwcm9maWxlIGVtYWlsIiwiYXpwIjoiSUlOSjZrbjNFQkZLZDJlVEZ6TW9ZZ0tmaGw2NTQwMkwifQ.DM_FojYYcLRgxQHJ0XpGROWyfcQQIbGW_-iU_Di5gkRVVq1Ww32wke8LTmbyD_fi2tuFPjCRk67G6-iRQr9avEnCYHaX1hGoTcszUqb7z6uJG-fQi0asPDa-IvAN-Zx32cKzJhyuVYx1BsNWcVpja5OkhtSBAigrbeLLkNbGUAcK2f7CP_PqtAPDO5CoS8Vv5nmxSffqPze6mh6uY-YKu7u_VjUBi7Q8jhO7fSMiLy6_rUT8NOuIJwM0Sy_SM0UgIvULt-_BZf8XnEy_MhSEbOlFaenlgH8jAICU2IV6dyQwvRFyAkoj4EvYf1wn_7RAJki1cx5OPcnGHpX25vI6bQ' \
--header 'Content-Type: application/json' \
--data-raw '{
"partyName": "DELHI 001",
"refPartyId": "DEL001",
"flexStatus": "0",
"fcTraceability": "Sku level",
"addressLine1": "Gurgaon\nGurgaon",
"addressLine2": "Gurgaon\nGurgaon",
"city": "New Delhi",
"pincode": 110001,
"state": "Delhi",
"country": "India",
"gstin": "07AAFCD5862R007",
"primaryPhone": 9999999999,
"contactPerson": "Anushi Dhaketa",
"companyName": "Eshopbox"
}'
Key mapping -
Keys in the create location request body |
|
|---|---|
|
|
|
|
| '0' |
| '' |
|
|
| ? |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Response -
{
"id": "52046",
"accountId": 1653,
"partyId": "D000152046",
"refPartyId": "DEL001",
"partyName": "DELHI 001",
"type": "Supplier",
"gstin": "07AAFCD5862R007",
"addressLine1": "Gurgaon\nGurgaon",
"addressLine2": "Gurgaon\nGurgaon",
"city": "New Delhi",
"state": "Delhi",
"pincode": "110001",
"country": "India",
"primaryPhone": "9999999999",
"isDefault": "0",
"companyName": "Eshopbox",
"flexStatus": "1",
"contactPerson": "Anushi Dhaketa",
"fcTraceability": "Sku level"
}
To get the warehouseId from the partyID, use the below query -
SELECT warehouse_id FROM party WHERE id = ?
Key in query | Key in response |
|---|---|
id | response.id |
c) Create entry in the order_items table. // saurav
INSERT INTO order_items(order_id, warehouse_id, lineItemSequenceNumber, orderItemID,
itemID, sku, listing_id, quantity, productName, customerPrice,lineItemTotal,
invoiceTotal, lineItemOrderStatus, `status`, hsn, mrp, isVirtualKit)
VALUES ();
Column in the DB | Key |
|
|---|---|---|
order_id | orders table primary key |
|
warehouse_id | warehouse table primary key |
|
lineItemSequenceNumber |
|
|
orderItemID |
|
|
itemID | requestBody. |
|
sku | ESIN of itemID |
|
listing_id |
|
|
quantity | 1 |
|
productName | requestBody. |
|
customerPrice | requestBody.items. |
|
lineItemTotal | requestBody.items. |
|
invoiceTotal | requestBody.invoiceTotal |
|
lineItemOrderStatus | - |
|
status | CREATED |
|
hsn | requestBody.items.hsn |
|
mrp | requestBody.items.mrp |
|
isVirtualKit | ? |
|
discount | requestBody.items.discount |
|
taxRate | requestBody.items.taxPercentage |
|
d) Create entry in the shipments table. //saurav
Method - createShipment()
INSERT INTO shipments(order_id,warehouse_id,external_wms_channel_id,
externalShipmentID,label_url,invoice_url,package_type_id,dimension_length,
dimension_width,dimension_height,weight,courierName, cp_id,trackingID, invoiceNumber,
invoiceDate, expectedShipDate, latestStatus)
VALUES ();
Key mapping -
|
|
|---|---|
| orders table primary key |
| warehouse table primary key |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| requestBody.invoice.number |
| requestBody.invoice.date |
|
|
|
|
e) Make entry in shipment_status_logs with created, packed and dispatched statuses.
INSERT INTO shipment_status_logs(shipment_id,`status`)
VALUES ();
Key mapping -
|
|
|---|---|
| shipments table primary key |
status | created, packed, dispatched |
Step 3: Create a return on the order. Make an entry in the return_shipments table using the below query.
Method - createReturn()
What method does - creates return entry in return_shipment and return_shipment_status_logs
INSERT INTO return_shipments(order_id, warehouse_id, customerReturnNumber, `type`,
pickupType, pickup_customerName, dimension_length, dimension_width, dimwnsion_height,
weight, pickup_addressLine1, pickup_addressLine2, pickup_city, pickup_state,
pickup_postalCode, pickup_countryCode, pickup_countryName, pickup_contactPhone,
pickup_email, `status`, returnDate, expectedPickupDate)
VALUES ();
Key mapping for return_shipments table
column | Keys in request body | Mandatory |
|---|---|---|
| Fetch from DB orders table primary key | Yes |
| warehouse table primary key | Yes |
| shipmentId | Yes |
|
|
|
| reverse |
|
|
| Yes |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| created |
|
| orderDate else current date |
|
|
|
|
|
| Yes |
|
| if |
|
| if |
|
| if |
|
| if |
|
| if |
|
| if |
|
| if |
|
| if |
Update the shipment_id and return_shipment_id in the order_items table.
UPDATE order_items
SET shipment_id = ?, return_shipment_id = ?
WHERE order_id = ?
Create an entry in return_status_logs table with return status as created.
INSERT INTO return_shipment_status_logs(return_shipment_id, return_status)
VALUES(?, 'created');
Step 4 : Call the clickpost return manifest api
Method - assignCourier()
What method does - call the clickpost return manifest API to allocate courier to the return shipment and gives error or success response as received from clickpost as output.
Add the API here
Case 1- If the clickpost API gives error in the response, give the same error in the response of this /createReturn API.
Case 2 - If the clickpost API gives a success response, give the courier details in the response and make a entry in return_status_logs table for return approved.
Save the label in the DB and return it in the response.
INSERT INTO return_shipment_logs(return_shipment_id, return_status, remarks)
VALUES (?, 'approved', 'Return has been approved');
If we get duplicate request for a return, check if the return has approved status in return_shipment_logs,
if not retry to assign courier.
Note - Before creating order, check if the product is present in the system or not. If it is not present, we will push the product details in a task queue to create a draft product. This is a process that can run in the background.
Method - checkIfProductExist() (Can be common for order and return API)
curl --location 'https://esb-product-engine-staging.appspot.com/_ah/api/esb/v1/products' \
--header 'Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCIsImtpZCI6IlJVVXdSREZCUVRSRFFqQkdORFUxTVVZeE16ZEdPRFJHTnpORk5EaEJSVEU0TVVORk5qVTJOdyJ9.eyJodHRwczovL2FwcERhdGEiOnt9LCJodHRwczovL3VzZXJEZXRhaWxzIjp7ImlkIjozMjE1LCJ1c2VyVHlwZSI6ImRlZmF1bHQiLCJlbWFpbCI6ImhhcnNoaXRhLnNoaW5kZUBlc2hvcGJveC5jb20ifSwiaHR0cHM6Ly9hY2NvdW50cyI6WyJlbGVjdHJvbmljc2VsbDgyNTMxNCIsImVsZWN0cm9uaWNzZWxsNDIxMzAyIiwiZWxlY3Ryb25pY3NlbGwiXSwiaHR0cHM6Ly93YXJlaG91c2VXb3Jrc3BhY2VzIjpbImVsZWN0cm9uaWNzZWxsODI1MzE0IiwiZWxlY3Ryb25pY3NlbGw0MjEzMDIiXSwiaHR0cHM6Ly93YXJlaG91c2VzIjpbXSwiaHR0cHM6Ly9wYXJ0bmVycyI6W10sImlzcyI6Imh0dHBzOi8vZXNob3Bib3gtcGF5bWVudC1yZWNvLmF1dGgwLmNvbS8iLCJzdWIiOiJlbWFpbHw2NGE1MDE0ZTgxNzFiYmRmZTNhOTgzNDAiLCJhdWQiOlsiaHR0cHM6Ly9lc2hvcGJveC1wb3J0YWwtZGV2LmFwcHNwb3QuY29tIiwiaHR0cHM6Ly9lc2hvcGJveC1wYXltZW50LXJlY28uYXV0aDAuY29tL3VzZXJpbmZvIl0sImlhdCI6MTcxMjU2MzYwMywiZXhwIjoxNzEyNjUwMDAxLCJzY29wZSI6Im9wZW5pZCBwcm9maWxlIGVtYWlsIiwiYXpwIjoiVFNIMlRYeDdXdmZ4NmhwcElGZnpsNWNiMU1HcXY5VnAifQ.laEtyREGnjJ5hg_VnW_F6ID36RCqzzh2EQ1TTOwn3Ib5cYfV2ftu9Ft3nqHl8NOQImGj9713JVSQyxxhB9IS5MoRaAZaJHcVUHTXXVi7JsuFyxux0_0SbjnU0xEbcngQX7O5mdvOwRnnLkSGr-ZBjWpGmdWbPELFowt7Is2V6ulxnyEBCVr-LSQD1ss-MVT6FhMTW2CePB8Ar4gL80xkpOT_m8N2Q6OjK791DQQTCehl-PgpFYoW8TmioHn4evMkH0sUl5W63GgnO5TNjHQsKOC_DTcpCdO8hM_jvEvT_H0eKEUWscwhHAbf0h9tciMzCQKI6nxy-l_7l6-ARp5Q-w' \
--header 'proxyHost: trendy' \
--header 'Content-Type: application/json' \
--header 'Cookie: JSESSIONID=txcZG8pxy_boRavyTK6W-g' \
--data '{
"type": "BASE",
"sku": "kurtaset09",
"description": "hello",
"imageUrl": "https://i0.wp.com/mayurkarwa123.wpcomstaging.com/wp-content/uploads/2024/03/organicproducts1-1.jpg?fit=500%2C329&ssl=1",
"mrp": 123,
"unitPrice": 123,
"weight": 0.5,
"dimensionLength": 13,
"dimensionWidth": 12,
"dimensionHeight": 11,
"dimensionUnit": "cm",
"weightUnit":"g",
"batchTrackingEnabled" : false,
"taxCode" : "ESBHH1,ESBHH2",
"hsnCode": "574638"
}'
Key Mapping -
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Response -
{
"type": "BASE",
"sku": "kurtaset10",
"description": "hello",
"mrp": 123.0,
"unitPrice": 123.0,
"hsnCode": "574638",
"status": "DRAFT",
"createdAt": "2024-05-16T04:06:13.760Z",
"updatedAt": "2024-05-16T04:06:13.760Z",
"accountSlug": "trendy",
"esin": "10I0L3H3H7E",
"parentEsin": "10I0L3H3H7E",
"batchTrackingEnabled": false,
"isCreatedOnUc": "Zero",
"wmsSync": "Zero"
}
Map the ESIN in the response to the order_items.
UPDATE
Process that can run the background -
1. Creation of draft product and entry in order_items
Creation of order_items, shipment and entry in shipment_status_logs
Caching, methods to be reused, process that can be added in background