Hudson Pacific - Stocktake

Hudson Pacific do stocktakes twice a year. They complete their stocktake counts over 3 sessions. Each session focuses on a specific pick zone - chiller (C), dry (D) or freezer (F).

1. Get a full and current stock on hand listing for Hudson Pacific.

   Use the following query to extract ALL stock on hand values.

   Hudson Pacific use the BestBeforeDate field, however, additional fields have been included to cater for a value ending up in the wrong field.

   Copy

   Select
   Barcode,
   Status,
   BinCode,
   ItemCode,
   UnitQuantity,
   BatchNumber,
   BestBeforeDate,
   ExpiryDate,
   PackagingDate,
   ProductionDate,
   SerialNumber,
   SellByDate
    
    
    
   FROM ULD
   INNER JOIN ULD1 ON ULD1.ULDID = ULD.ULDID
   INNER JOIN ITM ON ITM.ItemID = ULD1.ItemID
   LEFT JOIN ULDB ON ULDB.ULDID = ULD.ULDID
   LEFT JOIN BIN ON BIN.BinID = ULDB.BinID
    
    
   WHERE ULD.InstanceID = 93
   AND BIN.InstanceID = 93
   AND ULD.Status IN ('Bulk', 'Picking')
   AND ULD1.UnitQuantity > 0

2. Open up the resulting file in Microsoft Excel keeping all fields as text.

   Keeping all fields as text means no numbers / values are changed by Microsoft Excel.

   When converting raw data using Text to columns, make sure you select all the columns first (Ctrl + Shift + click the last column), then convert them to text.

   

3. Sort the data by the BinCode column.

   The first letter of a bin code for Hudson Pacific reflect the pick zone - C = chiller, D = dry and F = freezer.

   If they are doing stocktake on their Dry store, you will need to remove all the bin locations starting with a C or F.

4. There are 3 locations for damaged stock - C-Damage, D-Damage and F-Damage.

   These damaged stock locations are not cleared during stocktake and must be left untouched. These should be deleted from the spreadsheet.

   As Hudson Pacific are completing a count of their Dry store, locate and remove the D-Damage bins.

   Also remove WH-OFFICE locations from the spreadsheet and notify Dash that WH-OFFICE locations have been excluded.

   You should have a spreadsheet similar to the one below. Empty columns have been deleted, such as SerialNumber and Packing Date.

   

5. The UnitQuantity / Current SOH column E must be converted to a negative value.

   You can do this in a number of ways. The simplest way is to multiple column E by -1.

   

6. Copy this data into the Stock Adjustment Macro File / Tool.

   While populating this file, you will to make sure that you take the negative values or you will end up increasing the stock on hand values.

   For the Comment field, include the date and area being counted. If the request has come from Jira, include the ticket number for future reference.

   The populated file looks like the screenshot below.

   If the BestBeforeDate is NULL in the raw data, leave it empty in the spreadsheet.

   

7. Check the Stock Adjustment file.

   To do this, select a ULD and check if the Items, Quantities and BestBeforeDates match the .wms Stock > ULD Management page.

8. Click Upload.

   Wait for the adjustments to be processed.

9. Review the results.

   The majority of the results in the Response column will display as successful.

   Filter to the failed adjustments.

   

   The failures will usually be due to a value in a field other than the Best Before Date.

   With this example, there is only one failure, which is due to a Production Date.

   

   As there is only one record, it is easy to complete this adjustment using the web UI and using the same comment as the import file.

   

10. Advice Hudson Pacific that this stock has been cleared.

    They will complete their stocktake and advise when they are ready to count the next zone.