/
New Refund Policy

New Refund Policy

Owned by Rohit Kumar
Last updated: May 01, 2024
3 min read

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

      SELECT refunds.id AS refundId FROM refunds INNER JOIN refund_status_logs rs ON refunds.id = rs.refund_id LEFT JOIN refund_status_logs rs2 ON refunds.id = rs2.refund_id AND rs.created_at < rs2.created_at WHERE rs2.id IS NULL -- This condition ensures that rs is the latest status log entry for each refund_id AND rs.status = 'created' AND refunds.created_at>='2024-01-01 00:00:00' GROUP BY refunds.id;

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

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

    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:

      SELECT order_items.refundId AS refundId, `orders`.`customerOrderNumber` AS customerOrderNumber, `order_items`.`return_shipment_id` AS returnShipmentId, `order_items`.`shipment_id` AS shipmentId, `order_items`.`warehouseReversePickupNum` AS warehouseReversePickupNum, `order_items`.`itemID` AS itemID, `order_items`.`sku` AS sku, `return_shipments`.`pickup_customerName` AS pickupCustomerName, `return_shipments`.`pickup_contactPhone` AS pickupContactPhone, `refunds`.`refundAmount` AS refundAmount, `refunds`.`mode` AS `mode`, `refunds`.`beneficiaryName` AS beneficiaryName, `refunds`.`beneficiaryAccountNo` AS beneficiaryAccountNo, `refunds`.`beneficiaryIfscCode` AS beneficiaryIfscCode, GROUP_CONCAT( return_shipment_status_logs.return_status ORDER BY return_shipment_status_logs.id DESC ) AS statuses, return_shipments.customerReturnNumber AS customerReturnNumber, order_items.productName AS productName, order_items.lineItemTotal AS lineItemTotal, orders.billing_customerName AS billingCustomerName, `orders`.`isCOD` AS isCOD, `orders`.`shipping_customerName` AS shippingCustomerName, `orders`.`shipping_addressLine1` AS shippingAddressLine1, `orders`.`shipping_addressLine2` AS shippingAddressLine2, `orders`.`shipping_city` AS shippingCity, `orders`.`shipping_state` AS shippingState, `orders`.`shipping_postalCode` AS shippingPostalCode, `order_items`.`productImageUrl` AS productImageUrl, `order_items`.`productAdditionalInfo` AS productAdditionalInfo, `order_items`.`shippingCharges` AS shippingCharges, `order_items`.`reversePickupReason` AS reversePickupReason, `order_items`.`warehouseReversePickupReason` AS warehouseReversePickupReason, `order_items`.`warehouseInboundStatus` AS warehouseInboundStatus, `channels`.`channelSlug` AS channelSlug, accounts.account_slug AS accountSlug, `orders`.`billing_email` AS email, `order_items`.`productUrl` AS productUrl, `refunds`.`beneficiaryBankCity` AS beneficiaryBankCity, `refunds`.`beneficiaryBankName` AS beneficiaryBankName, `orders`.`paymentType` AS paymentType, `orders`.`channel_id` AS channelId, accounts.id AS accountId, 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), '@@@@', 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, `order_items`.`returnReason` AS reasonForReturn, return_shipments.created_at AS returnRequestedDate, poe_channel_settings.returnResolutionPolicy AS returnResolutionPolicy, poe_channel_settings.returnChargeCondition AS returnChargeCondition FROM order_items LEFT JOIN refunds ON order_items.refundId = refunds.id LEFT JOIN `orders` ON `orders`.`id` = order_items.`order_id` LEFT JOIN `return_shipments` ON `return_shipments`.id = `order_items`.`return_shipment_id` LEFT JOIN `channels` ON `channels`.`id` = `orders`.`channel_id` LEFT JOIN accounts ON accounts.id = channels.account_id LEFT JOIN poe_channel_settings ON (order_items.brandAccountId = poe_channel_settings.accountId AND `orders`.`channel_id` = poe_channel_settings.channelId) OR (order_items.brandAccountId = poe_channel_settings.accountId AND poe_channel_settings.channelId = 0) LEFT JOIN return_shipment_status_logs ON return_shipment_status_logs.return_shipment_id = return_shipments.id LEFT JOIN shipment_status_logs ON shipment_status_logs.shipment_id = `order_items`.`shipment_id` WHERE order_items.id IN (:orderItemIds) GROUP BY order_items.refundId;

    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

        { "exchange": true, "refund": true, "isPickedUp": true, "returnQCStatus": [ "Quarantine" ] }

      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.  

 

Add label

Related content