/
Ajio unhold cron flow

Ajio unhold cron flow

Owned by Anushi Dhaketa
Last updated: Sept 16, 2024
3 min read

 

Step 1: Fetch all the Ajio connections with channel data with the below query.

SELECT connectionId, id AS channelId FROM channels WHERE integrationType = '16' AND `status` != 2;

 

Step 2: Fetch all the orders that are on hold for all the channel ids and are marked acknowledged on Ajio.

SELECT orders.channel_id, orders.customerOrderNumber, SUBSTRING_INDEX( GROUP_CONCAT( shipment_status_logs.status ORDER BY shipment_status_logs.id DESC ), ',', 1 ) AS latest_status, shipments.warehouse_id FROM orders LEFT JOIN shipments ON shipments.order_id = orders.id LEFT JOIN shipment_status_logs ON shipment_status_logs.shipment_id = shipments.id WHERE orders.channel_id IN (:channelIds) AND shipment_status_logs.`status` = 'accepted' AND shipments.poNumber == '' GROUP BY shipment_status_logs.shipment_id;

 

Step 3: Bifurgate the data based on channel id and push the data in the task queue.
Task queue name - on-hold-orders

Sample event pushed to task queue -

{ "channelId": "", "connectionId": "", {{warehouseId}}: [ "Order1", "Order2" ], {{warehouseId}}: [ "Order1", "Order2" ] }

 

Step 4: In the task queue listener, loop through the orders and call the get PO details API of Ajio.

Fetch the access token from the cache.
cache key -
If it's unavailable in the cache, use the below query to fetch the details from DB.

SELECT inputFields FROM ie_appinstall_connection WHERE id IN (:connectionIds);

 

Step 5: Push the order in the task queue and call the Ajio get PO details API

Ajio get PO details API -

curl --location --request GET 'https://api-seller.services.ajio.com/ds/eshopbox/orders?orderIds=S258469241' \ --header 'apiKey: eyJhbGciOiJSUzI1NiJ9.eyJ0ZW5hbnRzIjpbeyJpZCI6ImI4ZGE4MDI4LTBlN2YtNDljYi1hNmE2LTFiNDVjMzM2OTY1YyIsIm5hbWUiOiJBSklPIiwiZGVzY3JpcHRpb24iOiJBSklPIE1hcmtldHBsYWNlIn1dLCJmaXJzdE5hbWUiOm51bGwsImxhc3ROYW1lIjpudWxsLCJyb2xlcyI6W3sibmFtZSI6IlJPTEVfUE9CX1NFTExFUiJ9XSwiaWQiOiJjNzIxMjVlMC04MzNiLTQ4OWQtOTllZi0wZDllZDY5ZTBjM2IiLCJncmFudFR5cGUiOiJDUkVERU5USUFMX0dSQU5UIiwidHlwZSI6IkFjY2VzcyIsImV4cCI6MTcyNDE3MTQzNCwiaWF0IjoxNzI0MTY3ODM0LCJ1c2VybmFtZSI6IkRWMDAzMzQ0NDEifQ.xG6V4KEITaJmeUEMwHrnojK5-SSqPS2nlQW8YPDqHwwx9xb8-3HKmeCaq7Mhabk5n7CEkfI-H1WX1pjGWdtWKLM2-1iYtxQgmD2COPGEhp6lZOH3zE7A_pkDW6pOuLjYejVgmUxci1CaLoGxfxSP2yQPTBVD1t5VkVfq1qlc8CLQhx78bLLd8Kd0GxC7hPm9P7qdPha868kkV85i2vPz3jLdFK5PM7WPSLNr1s3CJ1OoJOc8YHgSuw8NTCj0cIxMKVKafVu0g5ijy5o9UeUuz36GbfGldNwaRa11lP7c86Z88SM8C0zZr76S8AUfc5DQr4Of6A4FLOyzN-5jUs5Zeg'


Sample response -

{ "orders": [ { "carrier": "string", "order_date": 1635828975, "order_id": "string", "payment_method": "string", "po_amount": 0, "total_tax": 0, "po_number": "string", "po_date": 1635828975, "po_pdf_url": "string", "status": "enum (CANCELLED, CONFIRMED, PACKED, DISPATCHED, DELIVERED, READY_TO_DISPATCH)", "order_lines": [ { "item_details": { "brand_name": "string", "color": "string", "ean": "string", "item_code": "string", "name": "string", "size": "string" }, "mrp": 0, "item_base_price": 0, "order_item_id": "string", "quantity": 0, "customer_cancelled_qty": 0, "seller_cancelled_qty": 0, "seller_short_picked_qty": 0, "hsn": "string", "tax_summary": { "cess_amount": 0, "cess_percentage": 0, "cgst_amount": 0, "cgst_percentage": 0, "igst_amount": 0, "igst_percentage": 0, "sgst_amount": 0, "sgst_percentage": 0, "tcs_amount": 0, "tcs_percentage": 0, "gst_amount": 0 }, "po_line_amount": 0, "total_tax": 0 } ], "recipient_address": { "address_line_1": "string", "address_line_2": "string", "address_line_3": "string", "city": "string", "country": "string", "first_name": "string", "last_name": "string", "mobile": "string", "state": "string", "state_code": 0, "zipcode": "string", "gstin": "string" } } ], "page": { "number": 0, "size": 0, "totalElements": 0, "totalPages": 0 } }

 

If we get an empty response from Ajio, throw an exception so the task queue can retry it. Add a delay of 60sec before retrying.
If we get a response from Ajio, follow the below steps.

Step 6. If the details for the Purchase Orders are fetched, we need to update certain fields corresponding to the order_id and channel_id of that order.

a) Update PO number in the shipments table.

SQL Query:

UPDATE shipments SET po_number= '{{orders[].po_number}}' WHERE shipments.order_id = {{wms order_id}}

 

b) Update the PO URL, GSTIN, and address in the orders table.

Update orders set shipping_customerName=:shipping_customerName, shipping_addressLine1=:shipping_addressLine1, shipping_addressLine2=:shipping_addressLine2 shipping_city=:shipping_city, shipping_state=:shipping_state, shipping_postalCode=:shipping_postalCode, gstin=:gstin billing_customerName=:shipping_customerName, billing_addressLine1=:shipping_addressLine1, billing_addressLine2=:shipping_addressLine2 billing_city=:shipping_city, billing_state=:shipping_state, billing_postalCode=:shipping_postalCode, orderAdditionalInfo=:orderAdditionalInfo WHERE customerOrderNumber=:customerOrderNumber AND channel_id =:channelId;

 

c) Update the taxes and hsn in the order_items table.

Update order_items set hsn=:hsn, centralGstPercentage=:centralGstPercentage, compensationCessPercentage=:compensationCessPercentage, integratedGstPercentage=:integratedGstPercentage, stateGstPercentage=:stateGstPercentage, cgst=:cgst, igst=:igst, sgst=:sgst where order_items.id IN (:orderItemIds);

 

 

d) Update prices in the order_items table.
Mapping -

Column name in DB

Key name in Ajio’s API response

Column name in DB

Key name in Ajio’s API response

invoiceTotal

order_lines.po_line_amount (order_lines.po_line_amount / quantity for multi item order)

lineItemTotal

order_lines.po_line_amount (order_lines.po_line_amount / quantity for multi item order)

customerPrice

same as lineItemTotal

taxAmount

order_lines.total_tax
(order_lines.total_tax / quantity for multi item order)


Step 7: If the details are successfully saved in DB, call the below API to unhold the order.


curl --location -g --request POST 'http://{{workspace}}.eshopbox.com/api/orders/unhold' \ --header 'Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCIsImtpZCI6Ik1UaERRamxDUlRJelJVUTRRVU0wUVRJNU1FSkVOVGszUVVFeU5qVXdSa1JDTmpBeU16WTROQSJ9.eyJodHRwczovL2FwcERhdGEiOnt9LCJodHRwczovL3VzZXJEZXRhaWxzIjp7ImlkIjoxMDAzLCJ1c2VyVHlwZSI6ImRlZmF1bHQiLCJlbWFpbCI6ImFudXNoaS5kQGVzaG9wYm94LmNvbSJ9LCJodHRwczovL2FjY291bnRzIjpbImhvdXNlLW9mLWtvYWxhIl0sImh0dHBzOi8vd2FyZWhvdXNlV29ya3NwYWNlcyI6W10sImh0dHBzOi8vd2FyZWhvdXNlcyI6W10sImh0dHBzOi8vcGFydG5lcnMiOlsiNDMxODk1IiwiMjg0MTY2IiwiODk4Njg1IiwiNjc4MTUxIiwiMTIyMDg1IiwiMjk1MzUyIiwiMTg2MTk2IiwiMTYzNTA5IiwiNTYzMzg1IiwiODIwMDYxIiwiOTI5MTMyIiwiOTI2NzY2IiwiMzExMTkxIiwiMTMwNTQxIiwiMTYzNjI0IiwiMTUwNTU5IiwiMjE2ODU3IiwiNjU3Njk2IiwiMTMwNzYwIiwiNDQ3MDc5Il0sImlzcyI6Imh0dHBzOi8vZXNob3Bib3guYXV0aDAuY29tLyIsInN1YiI6ImVtYWlsfDYwODk2ZWVlYjYwODcxMDMyZTZjOGVjMyIsImF1ZCI6WyJodHRwczovL3dtcy5teWVzaG9wYm94LmNvbSIsImh0dHBzOi8vZXNob3Bib3guYXV0aDAuY29tL3VzZXJpbmZvIl0sImlhdCI6MTcyMjUyMjQ2NCwiZXhwIjoxNzIyNjA4ODY0LCJzY29wZSI6Im9wZW5pZCBwcm9maWxlIGVtYWlsIiwiYXpwIjoiSUlOSjZrbjNFQkZLZDJlVEZ6TW9ZZ0tmaGw2NTQwMkwifQ.Qp0laLr91N_o3fVoiTrmcEY3gNXxtRqLatkLmzOuJytzXrwEsGwp5xM7i6f_WOEzYyb56IoZXi41EarPppqRRrnaTD1MDbZ9RjDJeB4oOj5v2cTKUMmP90ZuN9_ZyZPl9cCeiH6xHZoedbQ7Y5vNDg5Wd0BTrXBVfGvZJyzbWoO9NExksmoanrtahsguElR4ljHmLH2ZAZLEHTMvJxYlgmzO-QRpIqr511hWQHF47big5qO2Bmr3MtxBNFQ9lsB4enNBy0faOL9QS0Kyp0EJ3STBYkQEPHpY8zv3tRLhQwogukne9zJake7nwAvGbEPky5c6u2JoFWq6PQ-dYnqVcg' \ --header 'Content-Type: application/json' \ --data-raw '{ "externalChannelId": "CH2944", "externalShipmentId": "S255387483-2944-14", "customerOrderNumber": "S255387483", "items": [ { "itemId": "700005500004", "quantity": "1.0", "lineItemSequenceNumber": "1.0", "orderItemId": "S255387483-38644278" } ] }'

 

Note- If we get an empty response from Ajio for an order, skip that order.

Related content