Shipping Exceptions LLD

What is the purpose of this article?

In this article, we will discuss implementation logic to identify various cases of pick-up failure and actions to be taken on them respectively.

Steps to identify and achieve pick-up failure cases(algolia sync)

(project id - esb - client order return, order-return service)

1. There are various cases of pick-up failure given as follows:
   create a new method handlingForPickUpFailure(orderItemIds) with given following parameters for case 1 and case 2
   CASE 1:

   1. Any order for which the Estimated handover date is breached and pick up is not attempted by the courier and the current order status on the workspace is ready to ship.

      1. Based on the handover date(shipments.dispatched_cpt) if it surpassed then the same would be added in the workspace tab “Need attention” remarks “pickup not attempted”

      2. Update query to fetch dispatchCpt

         SELECT shipments.id AS shipmentId, shipments.`externalShipmentID`, shipments.warehouse_id as warehouseId, shipments.`trackingID`, shipments.`invoiceNumber`, shipments.external_wms_channel_id as externalWMSChannelId, shipments.isMetro AS isMetro, shipments.isSpecialplace AS isSpecial, shipments.`order_id` as orderId, shipments.`courierName`, shipments.region, shipments.`invoiceDate`, shipments.invoice_url AS invoiceUrl, shipments.weight, shipments.`dimension_length` AS `length`, shipments.`dimension_width` AS width, shipments.`dimension_height` AS height, shipments.`courierName` AS forwardShippingProvider, shipments.`dispatch_cpt` AS dispatchCpt, GROUP_CONCAT( CONCAT( shipment_status_logs.status, '####', shipment_status_logs.`created_at`, '####', IF(LENGTH(shipment_status_logs.location)> 0, shipment_status_logs.location, ''), '####', IF(LENGTH(shipment_status_logs.remarks)> 0, shipment_status_logs.remarks, '')) ORDER BY CASE WHEN shipment_status_logs.external_updated_at = '0000-00-00 00:00:00' THEN shipment_status_logs.id + UNIX_TIMESTAMP(shipment_status_logs.created_at) ELSE shipment_status_logs.id + UNIX_TIMESTAMP(shipment_status_logs.external_updated_at) END ASC SEPARATOR '::::') AS statusLog, GROUP_CONCAT( CONCAT( shipment_status_logs.status, '####', IF(LENGTH(shipment_status_logs.remarks)> 0, shipment_status_logs.remarks, ' '), '####', IF(LENGTH(shipment_status_logs.track_payload)> 0, shipment_status_logs.track_payload, ' '), '####', IF (shipment_status_logs.`external_updated_at` = '0000-00-00 00:00:00', shipment_status_logs.`created_at`, shipment_status_logs.`external_updated_at`), '####', IF(shipment_status_logs.location IS NOT NULL AND LENGTH(shipment_status_logs.location)> 0, shipment_status_logs.location, ' '), '####', shipment_status_logs.`created_at`) ORDER BY CASE WHEN shipment_status_logs.external_updated_at = '0000-00-00 00:00:00' THEN shipment_status_logs.id + UNIX_TIMESTAMP(shipment_status_logs.created_at) ELSE shipment_status_logs.id + UNIX_TIMESTAMP(shipment_status_logs.external_updated_at) END ASC SEPARATOR '::::') AS trackingLogs, CASE WHEN fsp.priority = '1' THEN 'Priority' WHEN fsp.express = '1' THEN 'Express' WHEN fsp.standard = '1' THEN 'Standard' ELSE '' END AS forwardShippingMode FROM shipments LEFT JOIN courier_partners fsp ON fsp.id = shipments.`courier_partner_id` LEFT JOIN shipment_status_logs ON shipment_status_logs.shipment_id = shipments.`id` WHERE shipments.id IN (11207257) GROUP BY shipments.id
         if (shipments.dispatched_cpt.before(current_time) && currentStatus== ready_to_ship) { status = "pick_up_failure"; remarks = "pickup not attempted"; }

2. Add key in the filter will be added in the need attention dropdown “pickup failed”

   1. Actions can be taken

      1. The client can regenerate a new label

      2. Self-ship

   CASE 2:

   1. If the courier partner fails to pickup they will be informed by click post where events will be published to our system to recognise such cases where the courier partner fails to pick up the order with the reason

      1. Fetch the latest logs for shipment_status_logs (forward)

         else if (shipment_status_logs.contains(npr_status_code) && npr_status_code >=0) { status = "pick_up_failure"; remarks = "as_per_remarks_received_with_npr_code"; }

      2. There are various NPR Codes mapped all are as follows:

         NPR Status Code NPR Description 0 Exception 1 Customer unreachable 2 Cancelled by customer 3 QC failed 5 Quantity mismatch 6 Pickup rescheduled 7 Out of service area 8 Max attempts reached 9 Not attempted 11 Product not ready 13 Address issue 14 Customer not available

      3. Need to fetch NPR code description and remarks from trackPayLoad of latest status and update the same in pickUpFailedReason - NPR code description and pickUpFailedRemark - remarks respectively

      4. Automatically reattempt will be made by courier in case max attempts reached

         1. The client can regenerate a new label

         2. Self-ship

   CASE 3:

   1. create a new method handlingForHandoverDispute(orderItemIds) with given following parameters

   2. The case where the courier has picked up the item, but the status has not been updated in the workspace

      1. To identify such cases check if the shipments.latestStatus == “dispatched” AND shipments.latestStatusUpdatedAt >= 24 hours and that the current status is shipment_status_logs.status = dispatched then it will be handover dispute

      2. and that the current status is shipment_status_logs.status = dispatched then it will be handover dispute

      3. New key in Need attention to be updated as “Handover dispute” remarks “No scan received from courier”

      4. Actions can be taken

         1. Report a false pickup attempt

         2. Escalate to Eshopbox

         3. Regenerate new Label
            
            

The following actions to handle pick-up failure are explained under the heading (Steps to take action on pick-up failed orders)

Steps to take action on pick up failed orders (eshopbox shipping):

(project id - esb - client order return, shipping service)

1. create a new endpoint API /api/wms/regenerateLabel which will check if the self-ship || regenerate label is clicked on the workspace

2. API endpoint will check whether which of the above two request bodies is received for either case functionality and the request body will be prepared differently

3. Regenerate label:

   1. Prepare the request body accordingly and call WMS API to regenerate the label for the following shipment

b. If a successful response is received same is returned to the frontend with trackingID

c. Update db shipments table “UPDATE shipments SET trackingId = “new trackingID” AND courier_partner_id = “ new courier details “ where externalShipmentId = “dummy“;

4. Self-ship

   1. If self-ship is user updated request body would be:

b. Now update the details received from the above request body in the db

c. UPDATE query to be prepared for the above.

d. return a success response with “tracking”

The event to be published after updating the details for event body to be updated.

Steps to update the exports sheet on pick-up failed orders (eshopbox shipping):

TABLE FOR EXPORTS NEW COLUMN:

• Current order status in shipment_status_logs.status == “out_for_pickup”, remarks - either pickup failure npr_descriptions else pickup failed and track payload contains pickup failed we implement following:

• Add a new column as mentioned in the table above under the heading TABLE FOR EXPORTS NEW COLUMN.

Steps to trigger notifications on pick up failed orders (eshopbox shipping):