Shopify Refund Flow - Cron Based

Objective: Initiate Refund for Customer Initiated Return

Requirements: To Check Automation Steps Settings Configured by Brand depending upon which the Refund Issue Flow will initiate. Following are the option out of which 1 option will be configured:

OPTION 1: Courier partner picks up the return shipment from the customer (Recommended only if doorstep quality check is configured. Contact Eshopbox to know more about doorstep QC):
OPTION 2: Return shipment is delivered to Eshopbox FC and restocked as Sellable inventory:
1. Two cases can take place:
  1. Order is cancelled before dispatch(before courier assign) from the warehouse
  2. Order is dispatched but the customer didn’t accept, so RTO will take place

Note: For each Shopify App, we need to add this automation step in the “ie_app_automation_steps“ table

SQL QUERY 1. GET `IntegrationType` `7` connectionIDs using following SQL Query:

SELECT GROUP_CONCAT(channels.connectionId) FROM channels WHERE integrationType = '7' AND channels.status!='2'

The following Query will be used to check the automation step for `Issue Refund` is active or not:

Cache Key: “refundSetting_{{connectionId}}“

SELECT
        ie_appinstall_connection.accessToken AS accessToken,
        ie_app_automation_steps_config.isActive,
        ie_app_automation_steps_config.otherDetails AS otherDetails,
        ie_appinstall_connection.inputFields AS inputFields,
        ie_app_automation_steps_config.latestSyncTimeStamp AS latestSyncTimeStamp              
    FROM
        ie_appinstall_connection                     
    LEFT JOIN
        ie_app_installation 
            ON ie_app_installation.appInstallationId = ie_appinstall_connection.appInstallationId                    
    LEFT JOIN
        ie_app 
            ON ie_app.appId = ie_app_installation.appId                     
    LEFT JOIN
        ie_app_automation_steps 
            ON ie_app_automation_steps.appId = ie_app.appId                    
    LEFT JOIN
        ie_app_automation_steps_config 
            ON ie_app_automation_steps_config.connectionId = ie_appinstall_connection.id             
            AND ie_app_automation_steps_config.appAutomationStepId = ie_app_automation_steps.id                    
    WHERE
        ie_appinstall_connection.id ='{{connectionID}}'              
        AND   ie_app_automation_steps.stepTitle LIKE "%{{Initiate Refunds}}%"   
        AND ie_app_automation_steps_config.isActive = '1'

Result:

accessToken	isActive	otherFields	latestSyncTimeStamp	inputFields

accessToken	isActive	otherFields	latestSyncTimeStamp	inputFields
`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`	`1`	`{"refundChoice":"customerPickedUp"}` OR `{"refundChoice":"warehouseDelivered"}`	`2019-07-17 19:12:45`	`{"store_name":"eshopbox21"}`

OPTION 1: Get the required details to prepare Calculate Refund API Request, if the data is returned that means the item has been picked_up from the customer after QC is Passed:

  SELECT
        refunds.id,
        refunds.mode AS paymentMode,
        order_items.cancellationReason,
        refunds.refundType,
        order_items.returnReason,
        orders.customerOrderNumber,
        orders.channel_id,
        order_items.lineItemSequenceNumber,
        order_items.quantity,
        external_wms_channels.locationId,
        SUBSTRING_INDEX(GROUP_CONCAT(refund_status_logs.status 
    ORDER BY
        refund_status_logs.id DESC),
        ',',
        1) AS refundLatestStatus ,
        return_shipment_status_logs.return_status  
    FROM
        refunds   
    LEFT JOIN
        order_items 
            ON order_items.refundId = refunds.id   
    LEFT JOIN
        orders 
            ON orders.id = order_items.order_id  
    LEFT JOIN
        channels 
            ON channels.id = orders.channel_id  
    LEFT JOIN
        external_wms_channels 
            ON external_wms_channels.channel_id = channels.id  
    LEFT JOIN
        channel_account_mapping 
            ON channel_account_mapping.external_wms_channel_id = external_wms_channels.id  
    LEFT JOIN
        refund_status_logs 
            ON refund_status_logs.refund_id = refunds.id  
    LEFT JOIN
        return_shipment_status_logs 
            ON return_shipment_status_logs.return_shipment_id = order_items.return_shipment_id  
    WHERE
        channels.integrationType ='7' 
        AND  channels.connectionID ='347'
        AND channel_account_mapping.enrollmentStatus = 'active'  
        AND orders.isCOD = '0'  
        AND order_items.return_shipment_id>0 
        AND refunds.created_at >='2023-01-01 00:00:00' 
        AND order_items.refundId > 0  
        AND return_shipment_status_logs.return_status IN (
            'picked_up'
        )   
        AND refunds.mode = 'originalPaymentMode'  
    GROUP BY
        order_items.id 
    HAVING
        refundLatestStatus != 'initiated'

SQL QUERY TO FETCH ITEMS THAT NEEDS TO BE REFUNDED:

customerOrderNumber	refundId	paymentMode	refundType	connectionID
`BLCK100519`	`123`	`originalPaymentMode`	`cancellationRefund`	`347`
`BLCK100520`	`456`	`originalPaymentMode`	`returnRefund`	`348`

lineItemSequenceNumber	Quantity	locationId	refund_status_logs

lineItemSequenceNumber	Quantity	locationId	refund_status_logs
`10780660596930`	`2`	`64388038850`	`created`

Note: If the Automation step isActive = '1' and mode of payment is “originalPaymentMode“ then proceed with the flow as given below

OPTION 2.1:

NOTE: DELIVERED TO CUSTOMER AND CUSTOMER RETURNED AND ASKED FOR REFUND

SELECT
        refunds.id,
        refunds.mode AS paymentMode,
        order_items.cancellationReason,
        refunds.refundType,
        order_items.returnReason,
        orders.customerOrderNumber,
        orders.channel_id,
        order_items.lineItemSequenceNumber,
        order_items.quantity,
        external_wms_channels.locationId,
        SUBSTRING_INDEX(GROUP_CONCAT(refund_status_logs.status       
    ORDER BY
        refund_status_logs.id DESC),
        ',',
        1) AS refundLatestStatus   
    FROM
        refunds   
    LEFT JOIN
        order_items 
            ON order_items.refundId = refunds.id   
    LEFT JOIN
        orders 
            ON orders.id = order_items.order_id   
    LEFT JOIN
        channels 
            ON channels.id = orders.channel_id   
    LEFT JOIN
        external_wms_channels 
            ON external_wms_channels.channel_id = channels.id   
    LEFT JOIN
        channel_account_mapping 
            ON channel_account_mapping.external_wms_channel_id = external_wms_channels.id  
    LEFT JOIN
        return_shipment_status_logs 
            ON return_shipment_status_logs.return_shipment_id = order_items.return_shipment_id  
    LEFT JOIN
        refund_status_logs 
            ON refund_status_logs.refund_id = refunds.id  
    WHERE
        channels.integrationType ='7' 
        AND  channels.connectionID ='347'
        AND refunds.created_at >='2023-02-23 09:30:05' 
        AND channel_account_mapping.enrollmentStatus = 'active'  
        AND order_items.return_shipment_id>'0'  
        AND order_items.refundId > '0'  
        AND orders.isCOD = '0'  
        AND return_shipment_status_logs.return_status IN (
            'delivered'
        )   
        AND refunds.mode = 'originalPaymentMode'  
        AND order_items.warehouseInboundStatus = 'complete'  
        AND order_items.returnFlag = '1'  
        AND order_items.reversePickUpReason = 'good_inventory'  
    GROUP BY
        order_items.id 
    HAVING
        refundLatestStatus != 'initiated'

OPTION 2.2(Second task Queue Work: RTO cases only):

Order placed and immediately cancelled before any courier assigned:

SELECT
        refunds.id,
        refunds.mode AS paymentMode,
        order_items.cancellationReason,
        refunds.refundType,
        orders.customerOrderNumber,
        orders.channel_id,
        order_items.returnReason,
        order_items.lineItemSequenceNumber,
        order_items.quantity,
        external_wms_channels.locationId,
        SUBSTRING_INDEX(GROUP_CONCAT(refund_status_logs.status 
    ORDER BY
        refund_status_logs.id DESC),
        ',',
        1) AS refundLatestStatus ,
        shipment_status_logs.status AS shipmentLogsStatus  
    FROM
        refunds   
    LEFT JOIN
        order_items 
            ON order_items.refundId = refunds.id   
    LEFT JOIN
        orders 
            ON orders.id = order_items.order_id  
    LEFT JOIN
        channels 
            ON channels.id = orders.channel_id  
    LEFT JOIN
        external_wms_channels 
            ON external_wms_channels.channel_id = channels.id  
    LEFT JOIN
        channel_account_mapping 
            ON channel_account_mapping.external_wms_channel_id = external_wms_channels.id  
    LEFT JOIN
        refund_status_logs 
            ON refund_status_logs.refund_id = refunds.id  
    LEFT JOIN
        shipments 
            ON shipments.id = order_items.shipment_id  
    LEFT JOIN
        shipment_status_logs 
            ON shipment_status_logs.shipment_id = shipments.id  
    WHERE
        channels.integrationType ='7' 
        AND channel_account_mapping.enrollmentStatus = 'active'  
        AND channels.connectionID ='347'
        AND orders.isCOD = '0'  
        AND refunds.created_at >='2023-01-01 09:12:12'
        AND order_items.refundId > '0'  
        AND shipment_status_logs.status IN (
            'cancelled'
        )   
        AND refunds.mode = 'originalPaymentMode'  
    GROUP BY
        order_items.id 
    HAVING
        refundLatestStatus != 'initiated'

RTO Case:

SELECT
        refunds.id,
        refunds.mode AS paymentMode,
        order_items.cancellationReason,
        refunds.refundType,
        orders.customerOrderNumber,
        orders.channel_id,
        order_items.returnReason,
        order_items.lineItemSequenceNumber,
        order_items.quantity,
        external_wms_channels.locationId,
        SUBSTRING_INDEX(GROUP_CONCAT(refund_status_logs.status 
    ORDER BY
        refund_status_logs.id DESC),
        ',',
        1) AS refundLatestStatus ,
        shipment_status_logs.status AS shipmentLogsStatus  
    FROM
        refunds   
    LEFT JOIN
        order_items 
            ON order_items.refundId = refunds.id   
    LEFT JOIN
        orders 
            ON orders.id = order_items.order_id  
    LEFT JOIN
        channels 
            ON channels.id = orders.channel_id  
    LEFT JOIN
        external_wms_channels 
            ON external_wms_channels.channel_id = channels.id  
    LEFT JOIN
        channel_account_mapping 
            ON channel_account_mapping.external_wms_channel_id = external_wms_channels.id  
    LEFT JOIN
        refund_status_logs 
            ON refund_status_logs.refund_id = refunds.id  
    LEFT JOIN
        shipments 
            ON shipments.id = order_items.shipment_id  
    LEFT JOIN
        shipment_status_logs 
            ON shipment_status_logs.shipment_id = shipments.id  
    WHERE
        channels.integrationType ='7' 
        AND channel_account_mapping.enrollmentStatus = 'active'  
        AND channels.connectionID ='347'
        AND orders.isCOD = '0'  
        AND refunds.created_at >='2023-01-01 09:12:12'
        AND order_items.refundId > '0'  
        AND shipment_status_logs.status IN (
            'RTO'
        )   
        AND refunds.mode = 'originalPaymentMode'  
    GROUP BY
        order_items.id 
    HAVING
        refundLatestStatus != 'initiated'

Note: We need to issue a refund only for the items which are QC Passed which means items having “reversePickupReason“:”good_inventory”

Calculate Refund: To calculate refund for the items which are QC Passed, we need to make a POST API call with the following Sample Request Body:

POST API:

https://{{storeSlug}}.myshopify.com/admin/api/2021-10/orders/{{order_id}}/refunds/calculate.json

Sample Request Body:

{
    "refund": {
        "shipping": {
            "full_refund": true
        },
        "refund_line_items": [
            {
                "line_item_id": 10780660596930,
                "quantity": 1,
                "restock_type": "no_restock"
            }   
        ]
    }
}

Sample Response: 200 OK

{
    "refund": {
        "shipping": {
            "amount": "0.00",
            "tax": "0.00",
            "maximum_refundable": "0.00"
        },
        "duties": [],
        "total_duties_set": {
            "shop_money": {
                "amount": "0.00",
                "currency_code": "INR"
            },
            "presentment_money": {
                "amount": "0.00",
                "currency_code": "INR"
            }
        },
        "refund_line_items": [
            {
                "quantity": 1,
                "line_item_id": 10780660596930,
                "location_id": null,
                "restock_type": "no_restock",
                "price": "100.00",
                "subtotal": "100.00",
                "total_tax": "0.00",
                "discounted_price": "100.00",
                "discounted_total_price": "100.00",
                "total_cart_discount_amount": "0.00"
            }
        ],
        "transactions": [
            {
                "order_id": 4346827473090,
                "kind": "suggested_refund",
                "gateway": "manual",
                "parent_id": 5258867409090,
                "amount": "100.00",
                "currency": "INR",
                "maximum_refundable": "100.00"
            }
        ],
        "currency": "INR"
    }
}

Note: Once we have calculated the Refund by calling Shopify Calculate Refund API, we need to pick Transactions Map as shown above in the response to Call Shopify Create Refund API as shared below:

POST API:

https://{{storeSlug}}.myshopify.com/admin/api/2020-10/orders/4346818068674/refunds.json

Sample Request Body:

{
    "refund": {
        "currency": "INR",
        "notify": true,
        "note": "wrong size",
        "shipping": {
            "full_refund": true
        },
        "refund_line_items": [
            {
                "line_item_id": 10780650668226,
                "quantity": 1,
                "restock_type": "return",
                "location_id": 64388038850
            }
        ],
        "transactions": [
            {
                "parent_id": 10780650668226,
                "amount": 217,
                "kind": "refund",
                "gateway":"Pay on Delivery"
            }
        ]
    }
}

Sample Response: 201 CREATED

{
  "refund": {
    "id": 929361464,
    "order_id": 450789469,
    "created_at": "2022-01-06T16:25:04-05:00",
    "note": "wrong size",
    "user_id": null,
    "processed_at": "2022-01-06T16:25:04-05:00",
    "restock": false,
    "duties": [],
    "total_duties_set": {
      "shop_money": {
        "amount": "0.00",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "0.00",
        "currency_code": "USD"
      }
    },
    "additional_fees": [],
    "total_additional_fees_set": {
      "shop_money": {
        "amount": "0.00",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "0.00",
        "currency_code": "USD"
      }
    },
    "admin_graphql_api_id": "gid://shopify/Refund/929361464",
    "refund_line_items": [
      {
        "location_id": null,
        "restock_type": "no_restock",
        "quantity": 1,
        "id": 1058498311,
        "line_item_id": 518995019,
        "subtotal": 195.67,
        "total_tax": 3.98,
        "subtotal_set": {
          "shop_money": {
            "amount": "195.67",
            "currency_code": "USD"
          },
          "presentment_money": {
            "amount": "195.67",
            "currency_code": "USD"
          }
        },
        "total_tax_set": {
          "shop_money": {
            "amount": "3.98",
            "currency_code": "USD"
          },
          "presentment_money": {
            "amount": "3.98",
            "currency_code": "USD"
          }
        },
        "line_item": {
          "id": 518995019,
          "variant_id": 49148385,
          "title": "IPod Nano - 8gb",
          "quantity": 1,
          "sku": "IPOD2008RED",
          "variant_title": "red",
          "vendor": null,
          "fulfillment_service": "manual",
          "product_id": 632910392,
          "requires_shipping": true,
          "taxable": true,
          "gift_card": false,
          "name": "IPod Nano - 8gb - red",
          "variant_inventory_management": "shopify",
          "properties": [],
          "product_exists": true,
          "fulfillable_quantity": 1,
          "grams": 200,
          "price": "199.00",
          "total_discount": "0.00",
          "fulfillment_status": null,
          "price_set": {
            "shop_money": {
              "amount": "199.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "199.00",
              "currency_code": "USD"
            }
          },
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "USD"
            }
          },
          "discount_allocations": [
            {
              "amount": "3.33",
              "discount_application_index": 0,
              "amount_set": {
                "shop_money": {
                  "amount": "3.33",
                  "currency_code": "USD"
                },
                "presentment_money": {
                  "amount": "3.33",
                  "currency_code": "USD"
                }
              }
            }
          ],
          "duties": [],
          "admin_graphql_api_id": "gid://shopify/LineItem/518995019",
          "tax_lines": [
            {
              "title": "State Tax",
              "price": "3.98",
              "rate": 0.06,
              "channel_liable": null,
              "price_set": {
                "shop_money": {
                  "amount": "3.98",
                  "currency_code": "USD"
                },
                "presentment_money": {
                  "amount": "3.98",
                  "currency_code": "USD"
                }
              }
            }
          ]
        }
      }
    ],
    "transactions": [
      {
        "id": 1068278486,
        "order_id": 450789469,
        "kind": "refund",
        "gateway": "bogus",
        "status": "success",
        "message": "Bogus Gateway: Forced success",
        "created_at": "2022-01-06T16:25:04-05:00",
        "test": true,
        "authorization": null,
        "location_id": null,
        "user_id": null,
        "parent_id": 801038806,
        "processed_at": "2022-01-06T16:25:04-05:00",
        "device_id": null,
        "error_code": null,
        "source_name": "755357713",
        "receipt": {},
        "amount": "41.94",
        "currency": "USD",
        "admin_graphql_api_id": "gid://shopify/OrderTransaction/1068278486"
      }
    ],
    "order_adjustments": []
  }
}

Note: Once the Refund has been issued on Shopify Store successfully ( 201 Created HTTP Code), save `initiated` entry in `refund_status_logs` table using the refundId:

SAVE OR UPDATE INTO refund_status_logs(`status`) VALUES('initiated'') 
WHERE refund_status_logs.refundId = '{{refundId}}'

Apps and Automations

Shopify Refund Flow - Cron Based

Note: For each Shopify App, we need to add this automation step in the “ie_app_automation_steps“ table

SQL QUERY 1. GET `IntegrationType` `7` connectionIDs using following SQL Query:

The following Query will be used to check the automation step for `Issue Refund` is active or not:

Result:

OPTION 1: Get the required details to prepare Calculate Refund API Request, if the data is returned that means the item has been picked_up from the customer after QC is Passed:

OPTION 2.1:

OPTION 2.2(Second task Queue Work: RTO cases only):

Note: We need to issue a refund only for the items which are QC Passed which means items having “reversePickupReason“:”good_inventory”

Calculate Refund: To calculate refund for the items which are QC Passed, we need to make a POST API call with the following Sample Request Body:

Note: Once we have calculated the Refund by calling Shopify Calculate Refund API, we need to pick Transactions Map as shown above in the response to Call Shopify Create Refund API as shared below:

Note: Once the Refund has been issued on Shopify Store successfully ( 201 Created HTTP Code), save `initiated` entry in `refund_status_logs` table using the refundId:

Related content

Shopify Refund Flow - Cron Based

Note: For each Shopify App, we need to add this automation step in the “ie_app_automation_steps“ table

SQL QUERY 1. GET IntegrationType 7 connectionIDs using following SQL Query:

The following Query will be used to check the automation step for Issue Refund is active or not:

Result:

OPTION 1: Get the required details to prepare Calculate Refund API Request, if the data is returned that means the item has been picked_up from the customer after QC is Passed:

OPTION 2.1:

OPTION 2.2(Second task Queue Work: RTO cases only):

Note: We need to issue a refund only for the items which are QC Passed which means items having “reversePickupReason“:”good_inventory”

Calculate Refund: To calculate refund for the items which are QC Passed, we need to make a POST API call with the following Sample Request Body:

Note: Once we have calculated the Refund by calling Shopify Calculate Refund API, we need to pick Transactions Map as shown above in the response to Call Shopify Create Refund API as shared below:

Note: Once the Refund has been issued on Shopify Store successfully ( 201 Created HTTP Code), save initiated entry in refund_status_logs table using the refundId:

Related content

SQL QUERY 1. GET `IntegrationType` `7` connectionIDs using following SQL Query:

The following Query will be used to check the automation step for `Issue Refund` is active or not:

Note: Once the Refund has been issued on Shopify Store successfully ( 201 Created HTTP Code), save `initiated` entry in `refund_status_logs` table using the refundId: