This appendix contains the following topics:
Introduction to Additional Inventory Control Utilities
Recalculate Inventory Quantities
Recalculate History Quantities
Recalculate Inventory Quantities
All PBS Utilities have the functions of export and restore. Some also have the functions of rebuild, extract and remake from an extract file. These functions are described in the PBS Administration documentation. The Inventory Control Utilities also include special functions related to controlling inventory data. These are described here and include:
6. Recalculate inventory quantities
7. Rebuild item keywords
8. Rebuild serial keywords
9. Recalculate history quantities
10. Create new inventory history
The Inventory Control utilities are available from the Windows Start menu (if those start menu selections are installed on your system). From the Windows Start menu:
Click
Programs->Passport Business Solutions->File Utilities->IC File Utilities
When accessing the utilities in UNIX, Linux or from a Windows command prompt, navigate to the top-level PBS folder and type in icutil.
This section contains information on the recalculating of inventory quantities.
The recalculate inventory quantities selection enables you to set several quantity-type fields (such as quantity on hand) in I/C to correct amounts. The I/C synchronization program updates four files/tables:
TRACKING (Lot and Serial information)
The status code flag of the serial number records is updated to show which serial numbers are committed. The quantity committed of the lot number records is updated.
ITEMS
All five quantity fields (on hand, committed, on back order, on order, and on work order) and average cost are updated.
LAYERS
(only updated if using LIFO or FIFO)
Records are deleted if the item number starts with an asterisk. This denotes a miscellaneous item.
WAREHOUSE STATUS
All warehouse quantity fields are updated.
Normally, the fields in theses files/tables are updated during posting in Order Entry, Purchase Order, Inventory Control and Point of Sale. If posting is interrupted by power failure or equipment failure, inaccurate quantities could result in any of the above data sets.
The following input files are used to bring I/C information into sync with the other modules:
Accounts Receivable. This module is processed if the Use PBS I/C flag is checked (set to Y) on A/R Control information. The invoice transaction header and line item files are used to update the item file and the status file.
Order Entry. This module is processed if the O/E Control information is present, and the Back order control flag is checked (set to Y) in I/C Control information. The transaction header and line item files are used to update Items and Statuses, and I/C tracking information. (see more on updating the tracking information below).
Purchase Order. This module is processed if the Interface to I/C flag is set on the PO Control information to ‘Y’. The transaction header records and line items are used to update to I/C Items and Statuses.
Point of Sale. This module is processed in the same manner as Order Entry.
I/C Tracking has six types of records:
U - Unsold serial numbers
S - Sold serial numbers
L - Lot header records
D - Lot detail records
K - Kit definition records
R - Kit resequence work records
For synchronization purposes, only record types U and L are updated. As a first step, these records are re-set to show that no items are committed.
Synchronizing the tracking file is only done for O/E and P/S records, not for A/R or P/O. Each O/E or P/S line item record has a tracking code. A value of "A" or "S" means we are tracking serial numbers. A value of "B" or "D" means we are tracking lots. Note: Actually, in the item file, only “S” for serial and “D” for lot is allowed as entries.
Updating Serial Number Records
For each line item record with tracking flag "A" or "S", the matching I/C tracking record is processed as follows:
| • | Locate the type "U" I/C tracking record with matching order number and line sequence number. The record contains a status code. A code of blank means the serial number is available. In this case we will update the record by moving a code of "C", which means that the serial number is committed. If the code was not blank, an error message is issued that the serial number is committed more than once. |
| • | Serial number records are not updated for credits nor for line item records with a zero quantity to ship. |
Updating Lot Number Records
For each line item record with tracking flag "B" or "D", the matching I/C tracking record is processed as follows:
| • | Locate the type "L" I/C tracking record with matching item, warehouse, and lot number. The record is re-written with quantity committed updated by adding quantity to ship from the line item record (or subtracting in the case of a credit). |
From the File recovery utilities menu:
Select
6. Recalculate inventory quantities
The following screen displays:
Only quantities that are updated are displayed as follows:
| 1. | QUANTITY COMMITTED and QUANTITY ON BACK ORDER display if Order Entry is in use and Back order Control was selected in the I/C Control information. |
| 2. | QUANTITY ON ORDER displays if Purchase Order is in use. |
| 3. | QUANTITY ON HAND displays only if LIFO or FIFO costing is being used. (QUANTITY ON HAND is set from the Layer File.) |
| 4. | QUANTITY ON WORK ORDER displays only if kits are used and there are incomplete work orders on file. |
You are then asked, Are you sure you want to do this ?.
Answer Y or N.
If you answer N, the file recovery utilities menu appears. If Y, the fields in I/C are updated based upon data in O/E, P/O, I/C, P/S and A/R, as appropriate. The screen displays the number of the item that is being processed.
If any quantities are corrected, the Inventory Recalculation Exception Report prints.
If you would like to understand when each file/table is checked and written too and in the order they are done this section is for you.
The program first opens the following files/tables:
Inventory Control information (ICCTLF)
Order Entry Control information (OECTLF) - to check if O/E is interfaced with I/C),
Point of Sale Control information (PSCTLF) - to check if P/S is interfaced with I/C),
Purchase Order Control information (POCTLF) - to check if P/O is interfaced with I/C), and
Accounts Receivable Control information (ARCTLF) - to check if A/R is interfaced with I/C.
Based on the control records found, the program opens the following files/tables:
The program opens the following: CNGHDR, ITMFIL, STAFIL, ICTRKF, LAYFIL (if using LIFO or FIFO), INVTRX, ORDHDR, LINITM, OETKXF, PIHDRF, PILINF, POHDRF, POLINF, RCVTRX, RECWRK, WKOCST, CMPFIL, POSHDR, POSLIN, PSTKXF.
Opened I-O: ITMFIL, ICTRKF
Before recalculating, the following checks are performed (anyone failing will exist the function):
| • | If using PO, checks to make sure there are no RCVTRX records. |
| • | If using PO, make sure there are no un-posted purchase orders on file by checking the CNGHDR file. |
| • | Make sure INVTRX is empty. |
| • | Make sure ICTRKF is empty. |
| • | Make sure the STAFIL is empty. |
| • | Make sure there is at least one non-miscellaneous item in the ITMFIL. |
Recalculating:
| • | Use a work file to create records based on STAFIL records. Creates 1 RECWRK record for each STAFIL record. |
| • | Un-commit unsold serial records (ICTRKF) by setting IC-TRK-SER-STAT from committed to available and adding 1 to the RECALC-TRACK-QTY-ON-HND for the matching RECWRK record. |
| • | Un-commit lot qtys. Using the ICTRKF, search for any lot header record, confirm the Item is a lot item, get the ICTRKF detail records for each ICTRKF lot header record and compute the total quantities on-hand, received, sold, credited, adjusted. If either total does not match the ICTRKF lot header record quantity, update the ICTRKF lot header quantity and set a flag to indicate an update was done. Add the ICTRKF lot header qty-on-hand to the RECALC-TRACK-QTY-ON-HND for the matching RECWRK record and if an update was done to the ICTRKF lot header record, add 1 to the RECALC-ADJ-TRACK-CNT for the matching RECWRK record. |
The below steps build up the recalculate work file (RECWRK).
| • | If LIFO or FIFO, the program updates based on LAYFIL records by warehouse or item. |
| • | If using kits, it updates based on qty-committed for kits components. |
| • | If using O/E, it updates based on qty-committed and qty-backordered based on OE LINITM records. |
| • | If using P/S, it updates based on qty-committed and qty-backordered based on PS POSLIN records. |
| • | If using A/R, it updates based on qty-committed and qty-backordered based on AR PILINF records. |
| • | If using P/O, it updates based on qty-committed and qty-backordered based on PO POLINF records. |
Other steps include:
| • | Close and open I-O the STAFIL and LAYFIL. |
Using the recalculate work file the program does the following:
| • | Prints the report and if any of the quantities changed, updates the STAFIL records accordingly. |
| • | Updates the ICTRKF file quantities for any lot records that changed creating correction records as needed and reports these changes. |
| • | Updates the ITMFIL record quantities accordingly reporting such changes. |
| • | Prints grand totals |
| • | It Initialize the recalculate work file and informs you the process is complete. |
This selection allows you to rebuild item keywords by reading the item description, sub-category, and vendor item number that are stored in I/C Items.
Select
7. Rebuild item keywords
The following screen displays:
This selection allows you to rebuild the item serial keyword file by reading the item serial numbers that are stored in the I/C item serial file.
Select
8. Rebuild serial keywords
The following screen displays:
This selection reads existing item and status information to recreate history quantities.
This selection sets the Item and Status quantity committed fields based what was entered in the Transaction Line Items from O/E, P/S or A/R.
Select
9. Recalculate history quantities
The program immediately recalculates history quantities.
This selection creates an inventory history by using existing item and status information, and offers the choice of using average or replacement costs.
Select
10. Create new inventory history
The following screen displays:
Enter A for average cost or R or replacement cost.