Order Splitting Implementation Document
PRD: Order splitting PRD
Figma: WIP August 2023 [ NS+JW]
JIRA Epic: https://auperator.atlassian.net/browse/SHE-3536
Change Location Doc: Change Location Implementation Document
Project And API Details
GCP Project(Prod) - eshopbox-client-portal-prod
Service - default
Gitlab Repo - Inventory Engine - Client Portal
API type + Path (Staging) = POST https://{accountSLug}.auperator.net/api/v1/splitOrder
API type + Path (Prod) = POST https://{accountSLug}.myeshopbox.com/api/v1/splitOrder
Data Model
High Level Sequence Diagram
Flow Chart
Implementation
Request Body:
REQUEST BODY :-
{
"orderId" : 8474565,
"packages": {
"package_01": [ 30325340,30325341 ],//orderItemId_PK
"package_02": [ 30666311,30666312 ]
},
"status": {
"30325340": "Unfulfillable",
"30325341": "New",
"30325340": ""
}
}
Get account Id and actor from access token. It will be used while publishing to show activities.
Create a
compiled_orderItemId_list
with order item Ids in all the packagesFetch required details from DB
SELECT order_items.id, order_items.shipment_id, order_items.status, shipments.externalShipmentID, GROUP_CONCAT(shipment_status_logs.status SEPARATOR '##') AS statusList, SUBSTRING_INDEX(GROUP_CONCAT(shipment_status_logs.status ORDER BY shipment_status_logs.id DESC), ',', 1) AS latest_status FROM order_items LEFT JOIN shipments ON shipments.id = order_items.shipment_id LEFT JOIN shipment_status_logs ON shipment_status_logs.shipment_id = shipments.id WHERE order_items.id IN (30325339, 30325340, 30325341, 30325342, 30325343) GROUP BY order_items.id HAVING (order_items.shipment_id = 0 AND order_items.status != "CANCELLED") OR (latest_status IN ('created','accepted','picking','picked','failed_to_rts'));
If
compiled_orderItemId_list
size != query result size,Then throw exception “One of the Order item is already packed or cancelled”
Cause: At least one of the order item id is already either packed or cancelled.
Run a loop for each package recieved from front-end :
If all the orderItemIds in that list have same shipment Id. (i.e. if all the orderItems are in respective packages)
If true:
Return no-action is supposed to be taken
if false:
then continue below
Run a LOOP on above query result
If orderItems.status != “cancelled“ OR statusList != null AND (LatestStatus="packed" OR “cancelled“)
Then throw exception “One of the Order item is already packed or cancelled”
Compile a list of shipmentIds to be cancelled
Prepare a semi-colon separated insert query, for all shipmentIds:
INSERT INTO shipment_status_logs (shipment_id, STATUS, track_payload, remarks, external_updated_at) VALUES (:shipmentId, 'cancelled', '', 'SYSTEM INSERTED split order', :currentTime);
Prepare a Array-List of below update query for the
CombinationIdentifier
column in orderItemsId tableUPDATE order_items SET CombinationIdentifier = 'packageNumber', shipment_id = 0 WHERE id = :orderItemId;
Perform below query to fetch the remaining order Item Id’s in the shipment that will be cancelled
SELECT id from order_items where shipment_id in (:cancelShipmentIdsList) and not in (:requstedOrderItemIds);
Call WMS Shipment API to prepare event to publish to inventory team for the all the shipments to be cancelled.
UPDATE DATABASE
Run insert query created in step 7.c.
Run update queries in loop created in step 7.d
Run below query for remaining order item Ids fetched in step 8:
Update order_items set shipment_id = 0 where id in (:remainingOrderItemIds) AND `status` != "CANCELLED";
Publish the event in the pub-sub to re-stock the inventory
Details to be added after discussion from inventory team
Call WMS Order API to prepare the event to create new shipment
Run a loop on remaining order item id’s fetched in step 8
Prepare request body using response in step 12 and filtering the list only for those order item ids
Call real time inventory allocation API without any restrictions
Run a loop on all the packages received in request body from front end:
Create request body for items in this package
Call real time inventory allocation API with those items
Working of this API : https://auperator.atlassian.net/wiki/pages/createpage.action?spaceKey=ordershippingengine&title=Real%20Time%20Inventory%20Allocation%20%28Order-Splitting%29&linkCreation=true&fromPageId=4174610433
Create a
finalOrderItemIds
list with all the items incompiled_orderItemId_list
in step 2remaining order item id’s fetched in step 8
Call ALGOLIA task queue with above
finalOrderItemIds
Prepare the frontend response:
SELECT order_items.id AS orderItemId, order_items.orderItemId AS orderItemIdString, order_items.shipment_id AS shipmentId, order_items.onHold AS onHold, order_items.status AS status, orders.thirdPartyShipping AS thirdPartyShipping, orders.customerOrderNumber AS customerOrderNumber, shipments.externalShipmentID AS externalShipmentId FROM order_items LEFT JOIN orders ON orders.id=order_items.order_id LEFT JOIN shipments ON shipments.id = order_items.shipment_id WHERE order_items.id IN (:finalOrderItemIds)
Return the response