The Balance function in the Inventory Period End program aims to ensure that the on hand quantities against the lots, bins and FIFO buckets are the same as the equivalent on hand quantities in the warehouse.
- Options and settings that affect balancing
- Processing of the balance function in SQL
- Processing of the balance function in C-ISAM
-
FIFO valuation is required.
-
Costing methods set to FIFO or LIFO and Actual costing by lot/serial is required.
-
Multiple bins in use.
-
Full Goods in Transit facility is in use.
-
Aged inventory valuation is required.
-
Balance lots from most recent bucket option is enabled (FIFO) regardless of whether the FIFO buckets are being adjusted for a traceable or a normal item.
When enabled, the values in the FIFO buckets are used to calculate the inventory value on the Warehouse Balance Report. Applies only when FIFO valuation is enabled at company or warehouse level.
When enabled, the warehouse cost is recalculated based on the costs/quantities in the FIFO buckets. Applies only when FIFO valuation is enabled and the Costing method is set to FIFO, or costing by warehouse is in use.
This option is not available when processing a Period end together with the Balance function (i.e. you do not enable the Ignore balance function option). In that case, it's set according to the FIFO buckets current cost recalculate: During period end balance function option (Inventory Setup). Access can also be controlled using the Inv Balance recalculate current cost (FIFO) operator security activity.
When enabled, the warehouse cost is recalculated based on the costs/quantities in the FIFO buckets. This applies only when Actual costing by lots or serials is in use.
This option is not available when processing a Period end together with the Balance function (i.e. you do not enable the Ignore balance function option). In that case, it is set according to the FIFO buckets current cost recalculate: During period end balance function option (Inventory Setup). Access can also be controlled using the Inv Balance recalculate current cost (FIFO) operator security activity.
This option is only available when processing a Balance function and Actual costing is enabled by lots or serials (Inventory Setup). It is disabled when processing a Period end function.
This option is available when running a Balance function. This option is automatically enabled and cannot be changed when processing a Period end function together with a Balance function (i.e. you don't choose the Ignore balance function option).
The processing sequence of the balance function in SQL described here is according to the flow of the program when running the Balance function in SQL from within the Inventory Period End program.
![[Note]](images/note.png)
|
|
|
The quantity to which everything is balanced depends on various options, which also have a bearing on the processing steps. Where differences occur, the on hand quantity used to correct the other quantities is determined as follows:
|
|
The balance functions are split across the following business objects:
| Object | Function and Description |
|---|---|
| INVTBL |
Inventory On Hand
Balance
This handles the main balancing of the on hand quantities. |
| INVTBG |
Inventory Goods in Transit
Balance
This balances the goods in transit information. |
| INVQVE |
Inventory Valuation Summary
Query
This calculates the warehouse values and quantities for the Warehouse Balance Report. |
Processing in this business object is split into three parts:
-
Pre-balance
-
Balancing routine
-
Post-balance
These functions are performed in preparation for the actual processing of the balance function itself and are processed entirely using SQL.
-
Purging of old FIFO buckets with zero quantities on hand.
Description Relevant settings FIFO valuation | LIFO costing Routine This takes place if the number of days to retain FIFO buckets is set to anything other than 9999. The purpose of this step is to reduce the number of FIFO rows to process in the balancing routine. -
FIFO bucket take-on
Description Relevant settings FIFO valuation | LIFO costing | Lot traceability | Actual costing. Routine This is performed if no FIFO buckets are found in the system. This occurs only when the balance function is called from the Inventory Setup program, when either FIFO valuation or LIFO costing is enabled and stock exists on the system.
-
If the item is not Lot traceable, then a FIFO row is created for each stock/warehouse combination that has an on hand quantity greater than zero for normal items.
-
If the item is either a floor stock item and in the floor stock warehouse or a returnable item in the returns warehouse(s) then a FIFO row is created for each stock/warehouse combination that has a non-zero quantity (i.e. it can be negative). The quantity and cost used is taken from the stock/warehouse table.
-
If the item is Lot traceable and Actual costing is not in use, then a FIFO row is created for each stock/warehouse combination where the total lot on hand quantity is greater than zero. The quantity is the total lot on hand and the cost is taken from the stock/warehouse table.
-
If Actual costing is in use either for serialized or lot traceable items, then the FIFO buckets for these items are created in the Actual Costing Conversion Review.
-
-
Correction of negative FIFO/LIFO buckets
Description Relevant settings FIFO valuation | LIFO costing. Routine Only items set as Floor stock in the floor stock warehouse, or Returnable items in the returns warehouse(s) are allowed to have negative buckets. Any negative buckets which do not match this criteria are therefore set to zero. In addition, they are deleted from the table if they are older than the FIFO purge date. -
Setting of the default bin against the warehouse
Description Relevant settings Multiple bins | Bins at warehouse level. Routine -
If the default bin on the stock/warehouse row is spaces and no multiple bin rows exist, then the default bin is set to equal the warehouse.
-
If no entry for the default bin exists, and no other multiple bin entries exist, then a new entry is created using the default bin.
-
If multiple bins are not in use in the warehouse, then a dummy bin row is created with the bin set to equal the warehouse code (if the dummy bin is not already defined).
-
-
Remove orphaned FIFO buckets
Description Relevant settings Lot traceability | Actual costing by lots. Routine If Actual costing is in use for lot traceable items and FIFO buckets exist that are not associated with a valid lot, then these are deleted as they cannot be accessed unless the lot is recreated. These rows cause the Inventory on hand balance query to show an out of balance result. -
Renumbering of FIFO/LIFO buckets
Description Relevant settings FIFO valuation | LIFO costing | Actual costing. Routine This routine renumbers the buckets starting from zero upwards for items that are not subject to Actual costing. If Actual costing is in use, then the buckets remain as they are because of the linkage between these and the lots/serials used. -
Balancing of multiple bins to lots.
Description Relevant settings Multiple bins | Lot traceable | ECC-controlled. Routine While this is really a balance function, this is performed before the balancing routine itself takes place as we can process this directly from the LotDetail table which has a 1-1 relationship with the multiple bin table (InvMultBin) and can be processed in a single SQL statement.
-
If the bin against a lot detail row does not exist, then it is created with the lot's quantity on hand.
-
If the bin does exist, but the quantity on hand against that bin does not equal that on the lot, then it is updated with the lot's quantity on hand.
-
This is the main part of the process and performs the actual balancing of the items' on hand quantities (with the exception of the multiple bins for lots).
-
Build a list of items with imbalances.
Description Relevant settings Multiple bins | Lot traceability | FIFO valuation or LIFO costing | Actual costing and Aged inventory valuation. Routine This builds up a temporary table with all stock/warehouse combinations that have an imbalance between the warehouse's on hand, lots' on hand (if lot traceable or ECC-controlled), multiple bins and FIFO/LIFO buckets and (if aged inventory valuation is required) any imbalance between the aged inventory figures and the current on hand. -
The program processes the on hand quantities in the following sequence: (Relevant settings, outputs and messages displayed in the balance report where applicable are included).
-
Warehouse on hand not equal to Lot on hand
Description Relevant settings Lot traceable | ECC-controlled. The warehouse on hand is updated to equal the total lot on hand for the warehouse.
Outputs Normal inventory journal and inventory movement for adjustment. Message Warehouse on hand adjusted to lot on hand. -
Warehouse on hand not equal to FIFO bucket on hand and buckets exist for stock/warehouse
Description Relevant settings FIFO/LIFO costing | not Lot traceable/ECC-controlled. The warehouse on hand is updated to equal the total FIFO on hand for the warehouse.
Outputs Inventory movement only, as FIFO is correct. Message Warehouse on hand adjusted to FIFO/LIFO on hand. -
Warehouse on hand negative and not equal to FIFO bucket on hand and no buckets exist for stock/warehouse.
Description Relevant settings FIFO/LIFO costing | not Lot traceable/ECC-controlled | not floor stock in floor stock warehouse or returnable in returns warehouse. Negative quantities are disallowed when FIFO is in place, so the warehouse quantity on hand is adjusted to zero.
Outputs Normal inventory journal and inventory movement for adjustment. Message Warehouse on hand adjusted to FIFO/LIFO on hand. -
Warehouse on hand not equal to FIFO bucket on hand
Description Relevant settings FIFO valuation only | not Lot traceable. The FIFO buckets are adjusted to the warehouse on hand.
Outputs FIFO journals for each adjusted FIFO bin. Message FIFO/LIFO on hand adjusted to warehouse on hand. -
Lot on hand not equal to FIFO bucket on hand
Description Relevant settings FIFO valuation only | Lot traceable | ECC-controlled | not Actual costing. The FIFO buckets are adjusted to the lot on hand.
Outputs FIFO journals for each adjusted FIFO bin. Message FIFO/LIFO on hand adjusted to lot on hand. -
Lot on hand not equal to FIFO bucket on hand
Description Relevant settings FIFO/LIFO costing | Lot traceable | ECC-controlled | not Actual costing. The FIFO buckets are adjusted to the lot on hand.
Outputs FIFO journals for each adjusted FIFO bin and a normal journal for the value of the adjustments made against the FIFO buckets (zero quantity). Message FIFO/LIFO on hand adjusted to lot on hand. -
Lot on hand not equal to FIFO bucket on hand
Description Relevant settings Actual costing by lots. The FIFO buckets are adjusted to the on hand quantity against each lot (FIFO buckets are associated to each lot).
Outputs FIFO journals for each adjusted FIFO bin and (if a normal journal was posted for the adjustment of warehouse on hand to lot on hand) then the journal is updated with this value. If no journal exists, then a normal journal for the value of the adjustments made against the FIFO buckets (zero quantity). Message FIFO/LIFO on hand adjusted to lot on hand. -
Warehouse on hand not equal to Multiple bins on hand
Description Relevant settings Not Lot traceable/ECC-controlled | Multiple bins. If multiple bins are not in use for the warehouse, then the dummy bin entry is updated with the warehouse quantity on hand. If multiple bins are in use, then an adjust bin is created (if it does not already exist) and then updated with the quantity on hand.
Outputs adjust bin in multiple bin table if multiple bins in use for the warehouse. Message adjust bin created (only output if bins are in use for the warehouse). -
Warehouse on hand less or greater than Aged inventory valuation total
Description Relevant settings Aged inventory valuation. The aged valuation buckets are adjusted to match the on hand quantity, commencing from the current year.
Outputs None Message None
-
Once the on hand quantities have been balanced, the following functions are processed if the relevant option was enabled:
-
If Actual costing is enabled by lots or serials (Inventory Setup) then the original receipt quantities on the FIFO buckets are optionally updated from the lot/serial receipt, transfer in transactions.
Message: None.
-
If FIFO valuation is enabled, then the current warehouse costs are recalculated if the Recalc current cost from FIFO buckets option is enabled against the Balance function.
Message: Warehouse cost updated by FIFO/LIFO.
-
If Actual costing is enabled, then the actual cost is recalculated if the Recalc actual cost from FIFO buckets option is enabled against the Balance function.
Message: Warehouse cost updated by actual costs.
This business object is called if the full goods in transit facility is in use and the option to balance goods in transit is enabled. The goods in transit information is processed in the following sequence:
-
Any locked GIT lines are released.
-
The sum of the values and quantities for the In and Out Transactions (GtrTransactions) are compared with the same figures against the equivalent detail rows (GtrDetail). If they differ, then the Detail row is updated with these figures.
Information on these changes is output to the Goods in Transit Balance Report.
-
The sum of the values for the In and Out Transactions (GtrTransactions) are compared to the same figures on the header row (GtrMaster). If they differ, then the header is updated with these figures.
Information on these changes is output to the Goods in Transit Balance Report.
-
The sum of the quantities and values outstanding are then calculated from the detail rows and these are compared to the quantity and value in transit on the target stock code warehouse entry (InvWarehouse). If they differ, then the warehouse is updated with the correct figures.
The following describes the phases in the Balance function when using C-ISAM. It is important to note that the option to run the Lot Balance report is mentioned here. This report is also run here when you are using SQL.
The total quantity of the aged valuation buckets is compared to the total on hand quantity for each warehouse then the Aged inventory valuation required option is enabled (Inventory Setup). If these figures do not balance, then the aged valuation buckets are adjusted to match the on hand quantity, commencing from the current year.
The on hand quantity and value for each warehouse is added to the totals printed at the end of the report.
Any bucket with a zero on hand quantity in the current and previous two periods is deleted when the No of days to retain buckets with zero quantity is exceeded. This does not apply when the item is subject to Actual Costing.
Any bucket with a negative on hand quantity is set to zero. This excludes floor stock items in the floor stock warehouse and returnable items in the returns warehouse(s). If you selected the FIFO valuation option, then the warehouse totals are updated by the value and quantity of the FIFO buckets, and not those of the warehouse.
The FIFO buckets are renumbered. This does not apply when the item is subject to Actual Costing. If the warehouse quantity is non-zero and no FIFO buckets are held on file, then a FIFO bucket is created with the following:
-
a bucket number of zero
-
the current on hand quantity and cost from the warehouse
-
the current system date as the last receipt date
If the total on hand quantity of the FIFO buckets for a warehouse does not agree with the on hand quantity stored against the warehouse, an error line is printed showing the warehouse on hand quantity; the total FIFO buckets on hand quantity; and the difference between the two. The warehouse on hand quantity is updated to match the total of the FIFO buckets.
If you selected to recalculate the current cost from FIFO buckets, then the total value and quantity of the FIFO buckets are accumulated and used to calculate a new unit cost for each inventory item.
Any bucket with a zero on hand quantity is deleted.
Any bucket with a negative on hand quantity, is set to zero. This excludes floor stock items in the floor stock warehouse and returnable items in the returns warehouse(s).
The warehouse totals are updated from the value and quantity of the LIFO buckets, and not those of the warehouse.
The LIFO buckets are renumbered.
If the warehouse quantity is non-zero and no LIFO buckets are held on file, then a LIFO bucket is created with the following:
-
a bucket number of zero
-
the current on hand quantity cost from the warehouse
-
the current system date as the last receipt date
If no bins exist for a stock code, a default bin is created. The entire quantity on hand for the stock code/warehouse is allocated to the default bin and the current system date is set to the last receipt date.
If multiple bins are installed in the warehouse and a bin location is defined against the stock code's warehouse, then this bin location is used to generate the default bin. Otherwise, the warehouse code becomes the bin location.
If the total on hand quantity of the bins for a warehouse does not match the on hand quantity stored against the warehouse, then the bins are adjusted to equal the on hand quantity stored against the warehouse.
If multiple bins are installed in the warehouse, then an adjust bin is created for the adjustment quantity. Otherwise, the adjustment is processed against the default bin.
If multiple bins are not in use in the warehouse, then the dummy bin is adjusted to the warehouse quantity on hand.
If no bins exist for a stock code then a default bin is created. The entire quantity on hand for the stock code/warehouse is allocated to the default bin and the current system date is set to the last receipt date.
If a bin location is defined against the stock code's warehouse, then this bin location is used to generate the default bin. Otherwise, the warehouse code becomes the bin location.
If the total on hand quantity of lots for a warehouse matches the on hand quantity of the bins, but does not match the total on hand quantity stored against the warehouse, then the total on hand quantity for the warehouse is updated and an adjustment entry is posted to the journal file and movement file.
If the total on hand quantity of lots for a warehouse does not match the total on hand quantity of either the bins or the total on hand quantity stored against the warehouse, then the quantity stored against the bins is set to zero and replaced by the lot quantity. The total on hand quantity for the warehouse is updated and an adjustment entry is posted to the journal file and movement file. You can run the Balance lots (report only) function to review lot quantity adjustments recommended by the system before updating your live system.
If Serial Number Tracking is installed and the Sales Order module is not installed, then the quantity allocated field (held against each warehouse) is set to zero and recalculated from any serial numbers in stock that are on loan, in for a service, or at the service depot.
If Engineering Change Control (ECC) is used and the stock control level is set to Revision or Revision/Release then an ECC item is treated exactly the same as a lot traceable item, regardless of whether Lot Traceability is installed. The LotDetail record is the only place that the stock level of a specific ECC revision/release is held. Therefore the report indicates an 'Out of balance lot adjustment', the adjustment is made from the LotDetail record when updating the warehouse(s) to the total of the individual revisions/releases.
If Actual costing for Lot traceable items is enabled (Inventory Setup) then the balancing of lots is done by balancing to multiple bins if they exist. Bins are updated to match the lot total. Each lot is balanced to its FIFO buckets and if there is a variance, the FIFO buckets are updated and a FIFO journal is created. Adjustments are processed to the first or last FIFO bucket according to the FIFO/LIFO options enabled (Inventory Setup).
For each stock code, the lot total is checked against the warehouse total and the warehouse total is adjusted to match the lot total if a variance exists. If there are FIFO adjustments, then a normal journal is created indicating the total value of the FIFO adjustments and the quantity of the warehouse adjustment.
Goods in Transit detail lines are set to 'complete' if the full quantity transferred has been received and the GIT line was not set to 'complete' at the time of receipt.
The 'In process' flag against GITs are cleared if the option: Balance goods in transit is selected.