What is the objective of this article?

In this article, we will discuss the flow In which we apply custom rules and conditions on customer returns

based on these rules and conditions we will charge refund fees for customer returns

What steps need to be addressed to achieve this?

to achieve this we will do the following steps:

1. Fetch All refund Id of the specified time intervals

2. Using these refunds Id fetch order items id

3. Using order item id fetch all the details of the order and customers required to created refund through Pipefy card

4. Filter out which refund need to be initiated based on account id and return type

5. To calculate refund fees use refund rule and refund conditions

6. Pass these details to create pipefy card

7. After successfully creating pipefy card, save status into refund status logs table, show activity on workspace and send notification to customer

Detailed steps to achieve above mentioned task:

1. Create GET API: _ah/api/esb/v1/new-refund-cron which will periodically check if refund need to be initiated or not, this API will get Hit periodically from cloud scheduler, inside this we will do following

   1. call another GET API: _ah/api/esb/v1/process-refund Throgh TaskQueue

2. Now This API: _ah/api/esb/v1/process-refund call its service layer and it service layer perform following tasks

   1. Fetch list of refund ids which is in created state using query given below

      1SELECT 2 refunds.id AS refundId 3FROM 4 refunds 5INNER JOIN 6 refund_status_logs rs ON refunds.id = rs.refund_id 7LEFT JOIN 8 refund_status_logs rs2 ON refunds.id = rs2.refund_id AND rs.created_at < rs2.created_at 9 10WHERE 11 rs2.id IS NULL -- This condition ensures that rs is the latest status log entry for each refund_id 12 AND rs.status = 'created' 13 AND refunds.created_at>='2024-01-01 00:00:00' 14GROUP BY 15 refunds.id;

   2. After getting the refund id we will fetch order item id and brandAccountId from order_items table using query given below:

      1SELECT order_items.id AS orderItemId, order_items.brandAccountId, order_items.refundId 2FROM order_items 3WHERE refundId IN (:refundIds) 4


   3. Create A Map between order item id and refund id after filtering order id based on brandAccountId

   4. Using list of order item id fetch customer details and order details required for card creation using query given below:

      1SELECT 2 order_items.refundId AS refundId, 3 `orders`.`customerOrderNumber` AS customerOrderNumber, 4 `order_items`.`return_shipment_id` AS returnShipmentId, 5 `order_items`.`shipment_id` AS shipmentId, 6 `order_items`.`warehouseReversePickupNum` AS warehouseReversePickupNum, 7 `order_items`.`itemID` AS itemID, 8 `order_items`.`sku` AS sku, 9 `return_shipments`.`pickup_customerName` AS pickupCustomerName, 10 `return_shipments`.`pickup_contactPhone` AS pickupContactPhone, 11 `refunds`.`refundAmount` AS refundAmount, 12 `refunds`.`mode` AS `mode`, 13 `refunds`.`beneficiaryName` AS beneficiaryName, 14 `refunds`.`beneficiaryAccountNo` AS beneficiaryAccountNo, 15 `refunds`.`beneficiaryIfscCode` AS beneficiaryIfscCode, 16 GROUP_CONCAT( 17 return_shipment_status_logs.return_status 18 ORDER BY 19 return_shipment_status_logs.id DESC 20 ) AS statuses, 21 return_shipments.customerReturnNumber AS customerReturnNumber, 22 order_items.productName AS productName, 23 order_items.lineItemTotal AS lineItemTotal, 24 orders.billing_customerName AS billingCustomerName, 25 `orders`.`isCOD` AS isCOD, 26 `orders`.`shipping_customerName` AS shippingCustomerName, 27 `orders`.`shipping_addressLine1` AS shippingAddressLine1, 28 `orders`.`shipping_addressLine2` AS shippingAddressLine2, 29 `orders`.`shipping_city` AS shippingCity, 30 `orders`.`shipping_state` AS shippingState, 31 `orders`.`shipping_postalCode` AS shippingPostalCode, 32 `order_items`.`productImageUrl` AS productImageUrl, 33 `order_items`.`productAdditionalInfo` AS productAdditionalInfo, 34 `order_items`.`shippingCharges` AS shippingCharges, 35 `order_items`.`reversePickupReason` AS reversePickupReason, 36 `order_items`.`warehouseReversePickupReason` AS warehouseReversePickupReason, 37 `order_items`.`warehouseInboundStatus` AS warehouseInboundStatus, 38 `channels`.`channelSlug` AS channelSlug, 39 accounts.account_slug AS accountSlug, 40 `orders`.`billing_email` AS email, 41 `order_items`.`productUrl` AS productUrl, 42 `refunds`.`beneficiaryBankCity` AS beneficiaryBankCity, 43 `refunds`.`beneficiaryBankName` AS beneficiaryBankName, 44 `orders`.`paymentType` AS paymentType, 45 `orders`.`channel_id` AS channelId, 46 accounts.id AS accountId, 47 GROUP_CONCAT( 48 CONCAT( 49 shipment_status_logs.status, 50 '@@@@', 51 IF(LENGTH(shipment_status_logs.remarks)> 0, shipment_status_logs.remarks, ' '), 52 '@@@@', 53 IF(LENGTH(shipment_status_logs.track_payload)> 0, shipment_status_logs.track_payload, ' '), 54 '@@@@', 55 IF(shipment_status_logs.external_updated_at = '0000-00-00 00:00:00', shipment_status_logs.created_at, shipment_status_logs.external_updated_at), 56 '@@@@', 57 shipment_status_logs.created_at 58 ) 59 ORDER BY 60 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) 61 ELSE shipment_status_logs.id + UNIX_TIMESTAMP(shipment_status_logs.external_updated_at) END ASC SEPARATOR '::::' 62 ) AS trackingLogs, 63 `order_items`.`returnReason` AS reasonForReturn, 64 return_shipments.created_at AS returnRequestedDate, 65 poe_channel_settings.returnResolutionPolicy AS returnResolutionPolicy, 66 poe_channel_settings.returnChargeCondition AS returnChargeCondition 67FROM 68 order_items 69LEFT JOIN 70 refunds ON order_items.refundId = refunds.id 71LEFT JOIN 72 `orders` ON `orders`.`id` = order_items.`order_id` 73LEFT JOIN 74 `return_shipments` ON `return_shipments`.id = `order_items`.`return_shipment_id` 75LEFT JOIN 76 `channels` ON `channels`.`id` = `orders`.`channel_id` 77LEFT JOIN 78 accounts ON accounts.id = channels.account_id 79LEFT JOIN 80 poe_channel_settings ON (order_items.brandAccountId = poe_channel_settings.accountId AND `orders`.`channel_id` = poe_channel_settings.channelId) 81 OR (order_items.brandAccountId = poe_channel_settings.accountId AND poe_channel_settings.channelId = 0) 82LEFT JOIN 83 return_shipment_status_logs ON return_shipment_status_logs.return_shipment_id = return_shipments.id 84LEFT JOIN 85 shipment_status_logs ON shipment_status_logs.shipment_id = `order_items`.`shipment_id` 86WHERE 87 order_items.id IN (:orderItemIds) 88GROUP BY 89 order_items.refundId; 90


   5. Using above details we filter out customer initiated return and create a separate list of customer initiated returns if

      1. order_items.return_shipment_id != 0 and

      2. order_items.returnFlag = '1'

   6. After getting the list of customer initiated returns we check if particular return qualifies for refund initiations and it does not qualifies we simply discards it

   7. Steps to check qualification of customer initiated returns for refund initiations

      1. get returnResolutionPolicy value

         1{ 2 "exchange": true, 3 "refund": true, 4 "isPickedUp": true, 5 "returnQCStatus": [ 6 "Quarantine" 7 ] 8}

      2. First we check if isPickedUp true

         1. Then we check if return_shipment_status logs have 'picked_up' status

            1. If true proceed

            2. else discard

      3. If isPickedUp is false, check returnQCStatus

         1. If warehouseInboundStatus is marked as “COMPLETE“ AND returnQCStatus matches with any of the values of reversePickupReason or warehouseReversePickupReason Then proceed else discard

   8. After Getting final list of customer initiated returns, apply the rule for refund fee deduction send it to create pipefy card

3.