Managing inventory effectively is crucial in any business, especially when it comes to ensuring that items are available for customers at the right time. One key aspect of this is setting the availability date for items, particularly when there are sales orders pending approval. Here’s a step-by-step guide to how this can be accomplished in NetSuite using a custom script:
1. Load Data from File
- Purpose: Start by loading necessary data from a file that contains item information.
- Process: The script first loads a specific file (identified by its internal ID) and parses its content into a JSON object for processing. If there’s an issue with loading the file, the script gracefully handles the error and continues by returning an empty array.
2. Iterate Over Each Item
- Purpose: For every item in the loaded file, perform an inventory search to gather critical details.
- Process: The script searches for inventory items in NetSuite, retrieving data such as available quantity, quantity on order, item type, and whether the item is lot-numbered.
3. Determine Item Type
- Purpose: Identify the type of item to handle it appropriately.
- Process:
- If it’s a regular inventory item, it’s classified as
INVENTORY_ITEM. - If it’s a lot-numbered inventory item, it’s classified as
LOT_NUMBERED_INVENTORY_ITEM. - If it’s a group item, it’s classified as
ITEM_GROUP.
4. Handle Group Items
- Purpose: Special handling is needed for group items, which consist of multiple member items.
- Process:
- For group items, the script retrieves the details of all member items within the group.
- It adjusts the available quantity by subtracting the quantity committed in sales orders with pending approval status.
- If any item in the group has an available quantity less than or equal to zero, and if there’s no quantity on order, the script marks the group as having zero final order quantity.
5. Handle Individual Items
- Purpose: For items that aren’t groups, adjust quantities directly.
- Process:
- The script calculates the total quantity of the item from sales orders with pending approval status and subtracts it from the available quantity.
- It then sets the final order quantity based on how much of the item is currently on order.
6. Set Availability Date Based on Calculated Quantities
- Purpose: The final step is to set the availability date based on the calculated quantities.
- Process:
- If the final available quantity is positive, the availability date is set to null, indicating that the item is available now.
- If the available quantity is zero or less, but there’s an order pending, the availability date is set to the earliest expected receipt date from open purchase orders.
- If there’s no available quantity and no orders pending, the availability date is set to six months from the current date.