Returns reconcile cron

Step 1: Fetch all the active connectionIds which have the 'create return' automation step as on. We will be using the below query to fetch the results.

SELECT ie_app_automation_steps_config.connectionId, 
ie_app_automation_steps_config.latestSyncTimeStamp, ie_appinstall_connection.accessToken 
FROM ie_app_automation_steps_config 
LEFT JOIN ie_appinstall_connection ON ie_app_automation_steps_config.connectionId=ie_appinstall_connection.id 
WHERE ie_app_automation_steps_config.appAutomationStepId=? 
AND ie_app_automation_steps_config.isActive='1' 
AND ie_appinstall_connection.accessToken IS NOT NULL
AND ie_appinstall_connection.isActive = 'active'

Here we fetch the connection ids based on the automation step id of the app.

Database used: integration_engine_staging (For staging), integration_engine_prod(For prod).

Tables used: ie_appinstall_connection, ie_app_automation_steps_config.

Step 2: Make a list of all the connectionIds received from the above query and run the below query to fetch the orders which were marked as return completed in the last 24 hrs.

SELECT return_shipments.type, return_shipments.trackingID, order_items.* FROM order_items
LEFT JOIN orders ON order_items.order_id = orders.id
LEFT JOIN channels ON orders.channel_id = channels.id
LEFT JOIN return_shipments ON return_shipments.id = order_items.return_shipment_id
WHERE channels.connectionId IN (1359) 
AND warehouseInboundStatus = 'COMPLETE' 
AND DATE(order_items.created_at)>='2023-04-01'

Step 3: Call the get shipping package details API of UC to fetch the sale order code of the order.

Unicommerce getShippingPackageDetails API URL:

https://{{tenant}}.unicommerce.com/services/rest/v1/oms/shippingPackage/getShippingPackageDetails

Sample request body:

curl --location --request POST 'https://eshopbox.unicommerce.com/services/rest/v1/oms/shippingPackage/getShippingPackageDetails' \
--header 'Authorization: bearer 95fd1e1b-3cdf-40d8-8445-46737e3fff81' \
--header 'facility: Test' \
--header 'Content-Type: application/json' \
--data-raw '{
    "shippingPackageCode": "TEST00002"
}

We would receive a response as shown below in the sample response. Extract the shippingPackageDetailDTO.saleOrderCode from this response.

Sample response:

{
    "successful": true,
    "message": null,
    "errors": [],
    "warnings": null,
    "shippingPackageDetailDTO": {
        "code": "TEST00002",
        "channelShipmentCode": null,
        "saleOrderCode": "000000720-882-01",
        "statusCode": "DISPATCHED",
        "shippingManifestCode": "SM0003",
        "actualWeight": 200.000,
        "boxWidth": 210,
        "boxHeight": 10,
        "boxLength": 320,
        "collectableAmount": 959.00,
        "collectedAmount": null,
        "shippingProvider": "Delhivery",
        "trackingNumber": "2098310929434",
        "ewbNo": null,
        "ewbDate": null,
        "ewbValidTill": null,
        "saleOrderDetails": {
            "code": "000000720-882-01",
            "displayOrderCode": "718",
            "channel": "LINEN_CLUB_TEST_CHANNEL",
            "source": "ESHOPBOX",
            "displayOrderDateTime": 1629165112000,
            "status": "COMPLETE",
            "created": 1631271556000,
            "updated": 1631530272000,
            "fulfillmentTat": 1629358128000,
            "notificationEmail": "xyz@.com",
            "notificationMobile": "9999999999",
            "customerGSTIN": null,
            "cod": true,
            "priority": 0,
            "currencyCode": "INR",
            "customerCode": null,
            "billingAddress": {
                "id": "12046073",
                "name": "Venkat N",
                "addressLine1": "Test Address",
                "addressLine2": null,
                "city": "HYDERABAD",
                "district": null,
                "state": "TL",
                "country": "IN",
                "pincode": "500035",
                "phone": "9999999999",
                "email": null,
                "type": null
            },
            "addresses": [
                {
                    "id": "12046073",
                    "name": "Venkat N",
                    "addressLine1": "Test Address",
                    "addressLine2": null,
                    "city": "HYDERABAD",
                    "district": null,
                    "state": "TL",
                    "country": "IN",
                    "pincode": "500035",
                    "phone": "9999999999",
                    "email": null,
                    "type": null
                },
                {
                    "id": "12046072",
                    "name": "Venkat N",
                    "addressLine1": "Test Address",
                    "addressLine2": null,
                    "city": "HYDERABAD",
                    "district": null,
                    "state": "TL",
                    "country": "IN",
                    "pincode": "500035",
                    "phone": "9999999999",
                    "email": null,
                    "type": null
                }
            ],
            "customFieldValues": null
        },
        "saleOrderItems": [
            {
                "id": 10058342,
                "shippingPackageCode": "TEST00002",
                "shippingPackageStatus": "DISPATCHED",
                "facilityCode": "Test",
                "facilityName": "Test Warehouse",
                "alternateFacilityCode": null,
                "reversePickupCode": null,
                "shippingAddressId": 12046072,
                "packetNumber": 1,
                "combinationIdentifier": null,
                "combinationDescription": null,
                "type": "NORMAL",
                "item": null,
                "shippingMethodCode": "STD",
                "itemName": "CAMPH001PD00004-N5_S",
                "itemSku": "5SGHD3GSDDE",
                "sellerSkuCode": "5SGHD3GSDDE",
                "channelProductId": "OSGC600016-OSGC600016",
                "imageUrl": null,
                "statusCode": "DISPATCHED",
                "code": "000000720-20500385-1",
                "shelfCode": null,
                "totalPrice": 959.00,
                "sellingPrice": 959.00,
                "shippingCharges": 0.00,
                "shippingMethodCharges": 0.00,
                "cashOnDeliveryCharges": 0.00,
                "prepaidAmount": 0.00,
                "voucherCode": null,
                "voucherValue": 0.00,
                "storeCredit": 0.00,
                "discount": 0.00,
                "giftWrap": true,
                "giftWrapCharges": 0.00,
                "taxPercentage": null,
                "giftMessage": "",
                "cancellable": false,
                "editAddress": false,
                "reversePickable": true,
                "packetConfigurable": false,
                "created": 1631271556000,
                "updated": 1631530272000,
                "onHold": false,
                "saleOrderItemAlternateId": null,
                "cancellationReason": null,
                "cancelledBySeller": null,
                "pageUrl": null,
                "color": null,
                "brand": "Cavallo by Linen Club",
                "size": null,
                "replacementSaleOrderCode": null,
                "bundleSkuCode": null,
                "customFieldValues": null,
                "itemDetailFieldDTOList": [],
                "hsnCode": "62059090",
                "totalIntegratedGst": 0,
                "integratedGstPercentage": 0,
                "totalUnionTerritoryGst": 0,
                "unionTerritoryGstPercentage": 0,
                "totalStateGst": 0,
                "stateGstPercentage": 0,
                "totalCentralGst": 0,
                "centralGstPercentage": 0,
                "maxRetailPrice": 1599.00,
                "sellingPriceWithoutTaxesAndDiscount": 0,
                "batchDTO": null,
                "shippingChargeTaxPercentage": 0,
                "tcs": 0,
                "ucBatchCode": null,
                "channelMrp": null,
                "channelExpiryDate": null,
                "channelVendorBatchNumber": null,
                "channelMfd": null,
                "countryOfOrigin": null,
                "itemDetailFields": {},
                "channelSaleOrderItemCode": "000000720-20500385"
            }
        ]
    }
}

Check for return type in the data received from DB, if we get a null as return type, mark it as a FDR.

Step 4: If the type of return is customer-initiated return in the return_shipments.type and shippingPackageDetailDTO.saleOrderItems.reversePickupCodeis null. If it is null, Call the

https://documentation.unicommerce.com/docs/create-reversepickup.html API of Unicommerce to create a reverse pickup on Unicommerce. Else skip this step and go to step 5.

While preparing the request body for marking return on UC in case of CIR, compare and check for items with return_shipment_id and pass only those in the request.

Endpoint	`/services/rest/v1/oms/reversePickup/create`
Request type	POST
Scheme:	HTTPS
Header (Content-Type):	application/json
Header (Authorization):	bearer {access-token}, Eg.: bearer b30f3aea-7978-49bb-9ea7-33eddfc80afa
Header (facility):	facility in Unicommerce eg: ESHOPBOX

No.	Keys in create reverse pickup API	Data points to map

No.	Keys in create reverse pickup API	Data points to map
1.	saleOrderCode (Mandatory)	sale order code extracted from get shipping package details API `shippingPackageDetailDTO`.`saleOrderCode`
2.	`reversePickItems` (Mandatory)
2.1	`reversePickItems`. `reason`	''
2.2	`reversePickItems. saleOrderItemCode`	`shippingPackageDetailDTO.saleOrderItems`.`code` (loop through all the items received from the response of get shipping package details API and then compare it to the items received )
3	`actionCode`	“WAC“

Request:

curl --location --request POST 'https://{{tenantName}}.unicommerce.com/services/rest/v1/oms/reversePickup/create' \
--header 'Authorization: bearer bc147261-748b-4cd2-8a85-c59a4fc35e28' \
--header 'facility: ESHOPBOX' \
--header 'Content-Type: application/json' \
--data-raw '{
    "saleOrderCode": "4059236991161",
    "reversePickItems": [
        {
            "saleOrderItemCode": "10386132959417",
            "reason": "Not Good Item",
            "reversePickupAlternate": {
                "itemSku": "7000000008368",
                "totalPrice": 1572.69,
                "sellingPrice": 1572.69,
                "discount": 0,
                "shippingCharges": 0,
                "prepaidAmount": 0
            }
        }
    ],
    "shippingAddress": {
        "id": "5070",
        "name": "Sunny Tailor",
        "addressLine1": "9 Ferneley Rise",
        "addressLine2": "Thrussington",
        "city": "Leicester",
        "state": "England",
        "country": "GB",
        "pincode": "LE74UA",
        "phone": "07779 992983",
        "email": null
    },
    "pickupAddress": {
        "id": "5070",
        "name": "Sunny Tailor",
        "addressLine1": "9 Ferneley Rise",
        "addressLine2": "Thrussington",
        "city": "Leicester",
        "state": "England",
        "country": "GB",
        "pincode": "LE74UA",
        "phone": "07779 992983",
        "email": null
    },
    "actionCode": "WAC"
}'

Response:

{
    "successful": true,
    "message": null,
    "errors": [],
    "warnings": null,
    "reversePickupCode": "RP0165",
    "saleOrderItemCodes": [
        "10386132959417"
    ]
}

Step 5: Call the https://documentation.unicommerce.com/docs/saleorder_markreturned_with_inventory_type.html Unicommerce API to mark return on UC for the item which we have received from db only.

Endpoint	`/services/rest/v1/oms/returns/complete`
Request type	POST
Scheme:	HTTPS
Header (Content-Type):	application/json
Header (Authorization):	bearer {access-token}, Eg.: bearer b30f3aea-7978-49bb-9ea7-33eddfc80afa
Header (facility):	facility in Unicommerce eg: ESHOPBOX

if return type = 'CIR' : group the order items by return_shipment_id
if return_shipment_id = 0 : group the order itemsby shipment_id

curl --location --request POST 'https://{{tenantName}}.unicommerce.com/services/rest/v1/oms/returns/complete' \
--header 'Authorization: bearer 95fd1e1b-3cdf-40d8-8445-46737e3fff81' \
--header 'facility: Test' \
--header 'Content-Type: application/json' \
--data-raw '{
   "saleOrderCode": "000000720-882-01",
   "saleOrderItems": [
      {
         "code": "000000720-20500385-1",
         "status": "GOOD_INVENTORY",
         "shelfCode": "string", //not mandatory 
         "reason": "customer return"  // not mandatory
      }
   ]
   
}'

No.	Keys in mark order return API	Data points to map

No.	Keys in mark order return API	Data points to map
1.	saleOrderCode (Mandatory)	sale order code extracted from get shipping package details API `shippingPackageDetailDTO`.`saleOrderCode`
2.	saleOrderItems (Mandatory)
2.1	`saleOrderItems`. code(Mandatory)	customerOrderItemID from order_items table
2.2	`saleOrderItems.status`	reversePickupReason from order_items table

Response from Unicommerce: On UC item status changes to returned

{
    "successful": true,
    "message": null,
    "errors": null,
    "warnings": null
}

Apps and Automations

Returns reconcile cron

Related content