Warehouse Autom Release System
Purpose: The warehouse auto-release system will automatically generate the warehouse releases based on customer demand excel spreadsheet and available warehouse balance spreadheet. See details below:
1. The customer sends us the ‘Circuitronix PO Requirements Report’ in excel format (see attached file BTODL). This report is sent daily and will be uploaded to the db to generate the release. This report is sent in the same format every time. A unique ID should be created for this based on the date uploaded.
a. The data in the ‘Part’ column (column C) is the part number which is being requested.
b. The data in the ‘Needed Qty’ column (column H) is the quantity which the customer requires for that day.
c. The date is listed at the top of the page in column A (you can see the actual date in Column D ‘Run on: xx-xx-xxxx’).
2. In addition to the above report, the customer also sends a manual requirement report in excel format (see Release Sheet 3rd Party Warehouse attached). The difference between these two reports is that the ‘Circuitronix PO Requirements Report’ is generated by the customer’s internal system and the manual requirement is created manually by the customer. A unique ID can be created for this based on the date uploaded.
a. The ‘Part – Number’ column (column A) represents the items to be released.
b. The ‘Requested-QTY Pcs.’ Column (column C) represents the quantity which the customer is requesting.
i. Note that every now and then, the customer will not send a manual request.
3. We also need an option to manually add part numbers and quantities to be released even if there are no excel forms to be uploaded. This happens when the customer realizes that they forgot to include a part on the release or need to remove a part after they already sent the manual release request. This can be added to the manual release portal.
4. The current inventory stock will be uploaded in excel format into the auto-release system. This report will be generated from the inventory management database ‘active inventory’ with all QC statuses selected.
5. Once items 1-4 have been uploaded/added to the auto-release system, the system will then allow the option of generating the warehouse releases based on first in-first out (first to be received are the first to be released), QC status and availability.
6. For the release, ‘Goods parts’ status takes priority over ‘Pending QC’ followed by ‘Pending CTX Inspection’ followed by ‘To be Reworked’ followed by ‘Scrap/Bad Parts’
a. When ‘Pending QC’ items are selected, the line should be highlighted in orange
b. ‘Pending CTX Inspection’, ‘Scrap/Bad Parts’ and ‘To be Reworked’ lines should be highlighted in red and the system should ask the user if they are sure that they want to release those items.
c. ‘Good Parts’ should be left un-highlighted
d. If the same parts are available in both warehouses, select based on first in-first out and QC status. If the incoming date is the same for both warehouses, select from warehouse in alphabetical order
7. The release should be generated in the same format shown on attached file Release Template.
• i.e. if the customer is requesting 1,000pcs of P/N K3508-ICON, and the inventory shows the oldest line item contains 1 carton of 300pcs of ‘Good Parts’, the next line contains 1 carton of 1,200pcs of parts ‘Pending CTX Inspection’ and the third line shows 5 cartons of 1,000pcs of ‘Good Parts’, the auto release system will release the 1 carton of 300pcs and 1 carton of 1,000pcs.
a. In the above example, 2 lines would be created.
i. The first line would have the relevant data pertaining to the carton of 300pcs
ii. The second line would have the relevant data pertaining to carton of 1,000pcs
? Note that the actual release qty can be more than the requested qty to follow first in-first out (in this case, 1,300pcs are released even though the customer only requested 1,000pcs). The quantity released must at minimum be the customer’s requested qty or lowest qty above the request qty as dictated by first in-first out.
b. Cell H5 will be ‘”Warehouse Name” No:’.
a. The ‘Warehouse Name’ is based on the warehouse from which the parts are being released.
c. The P/L # (cell H5) should automatically be generated based on the previous release # from that warehouse.
iii. The user will be to have the option of changing this #.
1. I the user changes the P/L #, the following # which will be generated will be the modified #+1.
d. The ‘rev’ (revision level) field must be left blank unless the user adds data to it.
e. The user will select from a list of customer below the ‘Bill To’ field (B8); and the same applies to ‘Ship To’. The list will pull data from ‘Customer Information’ (see # 10).
f. The parts in column B are those which have inventory at that warehouse.
g. The release must be sorted by alphabetical order, then by P/O number, then by Qty/Ctn.
h. The unit weight of the part (column G Unit Wt (Kg.)) will be obtained from the ‘Part Source’ (see # 11).
i. ‘Total WT (Kg.)’ (column H) = ‘Total Qty’*’Unit Wt (Kg.)’ (F*G).
j. Row 23 is a Sum of the indicated data.
8. When the release P/L is created, the db will compare the P/O # listed on the release P/L to the file BTODL to verify that this P/O exists for the part #. If the P/O number is not on the BTODL file, it must be flagged for approval or else it will not allow the user to save the release.
9. When the release P/L is created, the db will compare the ‘Total Qty’ on the release for a given part to the total quantity available on the P/O according to the BTODL file. I the quantity on the release is more than the quantity on the BTODL file, the quantity on the release needs to be flagged for approval or else the system will not allow the user to save the file.
10. A ‘Customer Information’ spreadsheet will be uploaded to the db. This will have the ‘Bill To’ and ‘Ship To’ data used when generating the releases. The user will need the ability to manual update information which was previously uploaded as well as to manually add in a new customer with relevant data.
11. A ‘Part Source’ will be uploaded to the database. This will contain the data found on file Release Template – Part Source tab columns A-F (see attached).
12. When you look at the ‘Active Inventory’ report from the inventory database file, you will notice that some parts are separate by /.
• Example: P/Ns K5516, K5517V5 and K5519V2 come as a set from the factory. That is to say , in one box of 100pcs you will find 100pcs of K5516, 100pcs of K5517V5 and 100pcs of K5519V2. So when this is in the inventory, it is listed as K5516/K5517V5/K5519V2 Qty/Ctn=100 (this means 100 sets per box). However, on the customer’s release request file, each part is listed separately and can have different ‘Needed Qty’. The needed qty for K5516 might be 5,000pcs, the one for K5517V5 might be 7,500pcs and K5519V2 might be 5,000pcs.
a. Considering the above information, If this scenario were to occur, the system should release 7,500 sets of K5516/K5517V5/K5519V2. That is to say that the system is to release a minimum of the highest demand on a set based on the same logic previously explained on the note in point 7a. In this case, the highest demand was for 7,500pcs of K5517V5. Therefore, 7, 500pcs would be the minimum released if inventory is available.
13. There will be parts on the customer request form for which there are no parts at the warehouse. We refer to this as demand which cannot be met or unfulfilled demand. We will need to be able to run report for demand which could not be met. The unfulfilled demand report will contain the Date on which the part was requested, the P/N, the quantity which was not met and the last receipt date (found on file BTODL column I).
14. The system should be able to run a history report for unfulfilled demand similar to inventory database.
15. The system should be able to run a ‘Warehouse Release’ history report similar to the inventory database.
16. The system should be able to run a report based on the customer’s requirement report as well as based on the manual requirements, and both at the same time (option ‘All’).