Batch Inventory Overlay Upload
Purpose: Use the batch inventory overlay upload process to update the on-hand quantities for a batch of item locations.
A periodic function processes the contents of a pipe-delimited text file that is placed in the CWDIRECTCP_UPLOAD_DIRECTORY.
Note: You cannot use the file storage API or the Work with File Uploads option to import the file.
Processing: The batch overlay upload:
• Processes the inventory upload file if it is found in the directory defined in the CWDIRECTCP_UPLOAD_DIRECTORY property.
• For each row in the file, updates the Item Location record with the on-hand quantity, or creates the Item Location record if the Item Warehouse exists.
• Resets the on-hand quantity for the Item Warehouse record.
• Submits a record to the Evaluate Backorder queue, if needed.
• Writes any errors to an error file in the Errors subfolder of the CWDIRECTCP_UPLOAD_DIRECTORY, as described below.
• Deletes the file after processing is complete after writing any errors to the Errors subfolder.
• Logs the import, including the total number of records processed and in error, in the Job.log file if the new OVERLAY_DEBUG property is set to Y.
The periodic function does not:
• Reset the on-hand quantity to be lower than the reserved quantity.
• Write an Inventory Transaction History record.
Setup: Setup for the batch overlay process includes:
• Create the INVOVRL (Program Name PFINVOVR) periodic function and assign it to a periodic process.
• Working with Admin Properties (CPRP): Set the following admin properties:
- COMMIT_RATE: The number of records to process at a time. In most cases, should be set to 1000 for optimal processing.
- OVERLAY_DEBUG: Set to Y in order to create entries in the Job.log file. The log notes the number of records in the file, the number of records successfully processed, the number of errors, and the time to process, for example: File: INV_OVERLAY_005.TXT Rows: 4 Success: 1 Errors: 3 Start Time: Mon Mar 04 16:03:11 EST 2019 End Time: Mon Mar 04 16:03:11 EST 2019 Time In Seconds: 0.041420431 Time In Minutes: 6.903405166666667E-4
- CWDIRECTCP_UPLOAD_ DIRECTORY: Indicates the location where the periodic function looks for the batch overlay import file.
File name: The batch overlay upload file placed in the upload directory should be named INV_OVERLAY_SEQ.TXT, where SEQ is a unique, optional sequence number. If there are multiple files in the upload directory, the function processes them in order based on the sequence number.
File layout: A sample row in the batch overlay upload file is:
6|1000|RED 5|1|A010101|50
Where:
• 6 is the company.
• 1000 is the item code.
• RED 5 is the SKU, if any; otherwise, this field should be empty.
• 1 is the warehouse code.
• A010101 is the warehouse location.
• 50 is the new on-hand quantity to overlay the current on-hand quantity.
Errors: If the function finds any errors, it writes a copy of the file to the Errors subfolder in the CWDIRECTCP_UPLOAD_DIRECTORY, replacing the TXT suffix with ERROR, for example, INV_OVERLAY_123.ERROR. The function adds a description of the error to the row in the error file. Possible errors include:
• Location is not valid: Either the location or the company is invalid.
• No Item Warehouse row found: Possible explanations include:
- A matching Item Warehouse record does not exist for the specified Item Location.
- The item specified is invalid.
- No SKU is specified for a SKU’d item.
Note: Matching for alphanumeric data, including the item, SKU, and location code, is case-sensitive.
• Requested overlay brings on hand below Printed or Reserved: The overlay quantity is lower than the current printed or reserved quantity for the Item Location.
• Invalid number of entries: Possible explanations include:
- A row is empty.
- There is no company or quantity specified for the row.
• One or more entries are invalid: Possible explanations include:
- No item was specified.
- No warehouse was specified.
- No location was specified.
Note: To prevent potential errors, Oracle recommends not passing updates to the same Item Location in the same upload file.
