Inventory Check for Unfulfillable Order Events

Note: order_items.isVirtualKit = '1'

Orders having virtualKit flag 1 will be treated with the existing flow only and we do not have to check inventory for them.

Step 1. Once the following Query runs, we fetch the required unfulfillable orders from the orders_unfulfillable table.

SELECT
        order_items.shipment_id,
        orders_unfulfillable.order_id,
        channels.externalChannelID,
        orders.orderSiteID,
        orders.customerOrderNumber,
        orders.shipMethod,
        orders.orderDate,
        orders_unfulfillable.expectedShipDate,
        order_items.promiseDeliveryDate,
        orders.isCOD,
        orders.paymentType,
        orders.taxAmount,
        orders.shipChargeAmount,
        orders.subtotal,
        orders.orderTotal,
        orders.balanceDue,
        orders.thirdPartyShipping,
        orders.onHold,
        orders.vendorOrderNumber,
        orders.status,
        orders_unfulfillable.packageID,
        orders.dispatchAfterDate,
        orders.shipping_customerName,
        orders.shipping_addressLine1,
        orders.shipping_addressLine2,
        orders.shipping_city,
        orders.shipping_state,
        orders.shipping_postalCode,
        orders.shipping_countryCode,
        orders.shipping_countryName,
        orders.shipping_contactPhone,
        orders.shipping_email,
        orders.billing_customerName,
        orders.billing_addressLine1,
        orders.billing_addressLine2,
        orders.billing_city,
        orders.billing_state,
        orders.billing_postalCode,
        orders.billing_countryCode,
        orders.billing_countryCode,
        orders.billing_countryName,
        orders.billing_email,
        orders.billing_contactPhone,
        order_items.lineItemSequenceNumber,
        order_items.quantity,
        order_items.sku,
        order_items.itemID,
        order_items.productName,
        order_items.lineItemOrderStatus,
        order_items.mrp,
        order_items.lineItemTotal,
        order_items.shippingCharges,
        order_items.customerPrice,
        order_items.discount,
        order_items.productAdditionalInfo,
        order_items.orderItemID,
        orders_unfulfillable.externalWarehouseID,
        orders_unfulfillable.weight_unit,
        orders_unfulfillable.dimension_unit,
        orders_unfulfillable.courierName,
        channels.id,
        channels.integrationType,
        channels.account_id,
        orders_unfulfillable.account_slug      
    FROM
        orders_unfulfillable        
    LEFT JOIN
        orders               
            ON orders.id = orders_unfulfillable.order_id        
    LEFT JOIN
        order_items               
            ON order_items.order_id = orders_unfulfillable.order_id      
    LEFT JOIN
        channels               
            ON channels.id = orders.channel_id      
    WHERE
        orders_unfulfillable.shipmentStatus='PENDING'           
        AND orders_unfulfillable.order_id IS NOT NULL          
        AND orders_unfulfillable.account_slug IS NOT NULL        
        AND order_items.status!='CANCELLED'    
        AND DATE(orders_unfulfillable.created_at)>='2022-05-01'
        AND DATE(orders_unfulfillable.expectedShipDate)>='2022-08-01'
        ORDER BY orders_unfulfillable.expectedShipDate ASC

Step 1.1 As per the Query above, orders with null expectedShipDate will be fetched first, followed by the orders with expectedShipDate in the AESC sequence.

Step 1.2 Now, from Step 1.1, orders that don’t have expectedShipDate, sort those orders with orders.id in AESC sequence and then append at the end in the list where orders having expectedShipDate

Step 2. Now Prepare a list of Unique SKUs from the unfulfillable orders fetched from the above Query.

Step 2.1 Prepare another list of Unique externalChannelID for all the unfulfillable orders and fetch the Active FCs using the following Query:

SELECT DISTINCT 
      warehouses.externalWarehouseID FROM warehouses 
LEFT JOIN 
      external_wms_channels ON external_wms_channels.warehouse_id = warehouses.id
LEFT JOIN 
      channels ON channels.id = external_wms_channels.channel_id
LEFT JOIN 
     channel_account_mapping ON channel_account_mapping.external_wms_channel_id = external_wms_channels.id
LEFT JOIN 
     facility ON facility.id= warehouses.facility_id
WHERE 
     channels.externalChannelID IN  ('list of externalchannelIds') AND
     channel_account_mapping.enrollmentStatus = 'active''

Result:

externalWarehouseID

externalWarehouseID
Bangalore_FC
Eshopbox_Mumbai
Gurgaon_FC
Kolkata_FC

Step 3. Now we need to prepare a SKUInventory Map across all the active FCs where good_inventory>0. Use the following SQL Query:

SELECT `warehouse`,`sku`, `good_inventory` FROM warehouse_inventories WHERE `warehouse` 
IN (:warehouseList) AND `SKU` IN (:skuList) AND `good_inventory`> 0

Now once the records are fetched, Create the following kind of Map that can be used for both Marketplace orders as well as Website Orders to handle whether we need to publish the event or not.

Step 5. Prepare the following Map from Step 3

{
    "sku1": {
      "FC1": 20,
      "FC2": 15
    },
    "sku2": {
        "FC1": 20,
        "FC3": 15
    },
    "sku3": {
        "FC2": 20,
        "FC3": 15
    }
}

Sample SKUInventoryMap:

{
    "13TAZ3FNPJY": {
        "VLBOM_107": "2"
    },
    "KUVJH3FNPG6": {
        "Staging test FC": "43",
        "VLBOM_107": "15",
        "Delhi FC": "59",
        "DELKH004": "16"
    },
    "G18GN3FNPFA": {
        "VLBOM_107": "2"
    },
    "CTPR23FNPD7": {
        "VLBOM_107": "21"
    },
    "24QGK3H1ZV5": {
        "VLBOM_107": "6"
    },
    "55DMH3FNI9Q": {
        "Warehouse": "9",
        "BDGUR103": "7",
        "Staging test FC": "49",
        "VLBOM_107": "15",
        "Delhi FC": "91",
        "DELKH004": "19"
    },
    "000UY3FNPGJ": {
        "VLBOM_107": "2"
    },
    "20M983FNPGK": {
        "VLBOM_107": "25"
    },
    "BBSHOCL35755_4": {
        "Warehouse": "1"
    }
}

Step 6. Now Iterate over the orders, and check if the order_items.SKU present in the above map or not

Case a. if externalWarehouseId is present in the given order, check if this SKU is present and whether that externalWarehouseId(FC) is present in the map. If not present, then we can ignore the order.

Case b. If externalWarehouseId is not present, then check if the SKU is present in the map and check if any FC contains the inventory or not. If no FC is present for the given SKU, then we need to skip this item

and iterate start the 2nd iteration.

NOTE: remove the SKU from the SKUInventory map if it doesn't contain any FCs

Step 7. Code Level Implementation for SKUInventory map adjustment and whether we need to publish the unfulfillable order event.

first loop: order level (existing loop) 

flag = true; 
//flag indicated whether we need to do the inventory adjustment for the sku or not

second loop: item level

IF   (  FC is present in the order `&&` FC is not present in the SKUInventory map ){
            flag = false;
            //this means this order has to be skipped
            break;
    }
        else IF (SKU is not present in the SKUInventory Map){
            flag = false;
            break;
    }
        second loop closed
        if(flag == false){
            continue;
            //begin 2nd order iteration
    }
        else
    {
            minus the inventory for the sku in the SKUInventory Map
            if FC is given then subtract the inventory for that sku in the given FC
            else if FC is not given then subtract the inventory from any FC present for the SKU
            Note: if Fc's inventory becomes 0 then remve the FC from the SKUInventory Map and also if the SKU has empty FC map then we also need to remove the SKU key from the SKUInventoryMap
    }
        flag = true;