/
What's the status of your inventory?

What's the status of your inventory?

Owned by Manas Singh (Unlicensed)
Last updated: Jun 21, 2022 by Muskan Choudhury (Unlicensed)
1 min read

API to be reviewed

/product-engine/api/v1/snapshotInventoryStatus

Request structure

curl --location -g --request GET 'https://{{baseDomain-workspace}}/product-engine/api/v1/salesChannelInventory?warehouseList=Bangalore_FC,Eshopbox_Mumbai,Gurgaon_FC,MJPAT_109,ESBMUM_112,ESBBLR_111&channelList=CH1908,CH1200,CH1192,CH1487,CH1238,CH1475,CH1955,CH1943,CH1918,AUP_STYLES_PROD,CH1450,CH1927' \ --header 'Authorization: Bearer {{workspace-authToken}}'

STEPS FOLLOWED :

STEP 1 : using accountSlug we will fetch externalChannelId and facilityId from facility table using this query :

SELECT warehouses.externalWarehouseID, facility.`id` FROM facility LEFT JOIN warehouses ON warehouses.`facility_id` = facility.`id` LEFT JOIN account_warehouse_mapping ON account_warehouse_mapping.`warehouse_id`= warehouses.`id` AND account_warehouse_mapping.`account_id` = 6 LEFT JOIN external_wms_accounts ON external_wms_accounts.`id` = facility.`external_wms_account_id` WHERE warehouses.`isDefault`= '1' AND (external_wms_accounts.`isDefault`= '1' OR external_wms_accounts.`account_id`= 6) AND (warehouses.status = '1' OR account_warehouse_mapping.id IS NOT NULL) AND account_warehouse_mapping.enrollmentStatus='ENROLLED' GROUP BY facility.`id`

 

RESULT :

STEP 2 : QUERY TO FETCH ESIN LIST FROM ALGOLIA : (accountSlug:'blackberrys') AND (status:'ACTIVE')

STEP 3 : ONCE WE RECEIVE ESIN LIST WE WILL SEND 50k data (by list partition) in one go and then will run a for LOOP

Under the for loop there is a query which will fetch info on the basis of esinList and warehouseList one by one

query is given below for reference:

 

SELECT SUM(IF(IFNULL(`GOOD_INVENTORY`,0)+IFNULL(`PICKLIST_ITEM_PUTAWAY_PENDING`,0)>0, IFNULL(`GOOD_INVENTORY`,0)+IFNULL(`PICKLIST_ITEM_PUTAWAY_PENDING`,0), 0)) AS abc, SUM(IF(IFNULL(`BAD_INVENTORY`,0)+IFNULL(`REJECTED`,0)+IFNULL(`QC_REJECTED`,0)+IFNULL(`QC_REJECTED_PUTAWAY_PENDING`,0)>0,IFNULL(`BAD_INVENTORY`,0)+IFNULL(`REJECTED`,0)+IFNULL(`QC_REJECTED`,0)+ IFNULL(`QC_REJECTED_PUTAWAY_PENDING`,0), 0)) AS abc2, SUM(IF(IFNULL(`CONSIGNMENT_RECEIVED`,0)+IFNULL(`QC_PENDING`,0)+IFNULL(`QC_ACCEPTED`,0)+IFNULL(`QC_ACCEPTED_PUTAWAY_PENDING`,0)>0, IFNULL(`CONSIGNMENT_RECEIVED`,0)+IFNULL(`QC_PENDING`,0)+IFNULL(`QC_ACCEPTED`,0)+IFNULL(`QC_ACCEPTED_PUTAWAY_PENDING`,0), 0)) AS abc3, SUM(IF(IFNULL(`RECEIVED_RETURN_PUTAWAY_PENDING`,0)+IFNULL(`REVERSE_PICKUP_PUTAWAY_PENDING`,0)+IFNULL(`COURIER_RETURNED_PUTAWAY_PENDING`,0)>0, IFNULL(`RECEIVED_RETURN_PUTAWAY_PENDING`,0)+IFNULL(`REVERSE_PICKUP_PUTAWAY_PENDING`,0)+IFNULL(`COURIER_RETURNED_PUTAWAY_PENDING`,0), 0)) AS abc4, SUM(IF(IFNULL(`COMMITTED`,0)>0, IFNULL(`COMMITTED`,0), 0)) AS abc5, SUM(IF(IFNULL(`RTV_GATEPASS_CREATED`,0)+IFNULL(`RTV_COMMITTED_INVENTORY`,0)>0, IFNULL(`RTV_GATEPASS_CREATED`,0)+IFNULL(`RTV_COMMITTED_INVENTORY`,0), 0)) AS abc6, SUM(IF(IFNULL(`RTV_GATEPASS_PACKED`,0)>0, IFNULL(`RTV_GATEPASS_PACKED`,0), 0)) AS abc7 FROM warehouse_inventories HERE sku IN (:skuList) AND warehouse IN (:warehouses)

Response structure

Add label

Related content