Utility to find LIFO/FIFO items for which the stack quantity does not match quantity on hand in SL7.
Utility for LIFO stack and FIFO stack
Solution ID = KB-244
Goal : Utility to find LIFO/FIFO items for which the stack quantity does not match quantity on hand in SL7.
Version = Syteline7.XX.XX
Module : SLMFG
Fact : Support Utility
Symptom 1 : Stack quantity does not match quantity on hand
Symptom 2 : Incorrect LIFO/FIFO stacks
Cause
Fix
The SQL script below can be used to find all items for which the quantity in the LIFO/FIFO stack does not match the items's quantity on hand:
declare @item table (
item nvarchar(40)
, itemwhse decimal(25,10)
, itemlifo decimal(25,10)
)
insert into @item
select itemwhse.item, sum(qty_on_hand + qty_mrb)
, (select sum(qty) from itemlifo where itemlifo.item = itemwhse.item)
from itemwhse
inner join item on
item.item = itemwhse.item
and item.cost_method in ('L', 'F')
group by itemwhse.item
select * from @item
where itemwhse != isnull(itemlifo, 0)
If you currently have items with this problem, it will be difficult to determine when it developed and what if the application was the cause (as opposed to improperly imported data). Therefore, the intent is that you run the script, fix the stacks for all items listed in the output, and then run the utility again every day or so to see if any more appear. You should be able to fix most problems by adding, updating or deleting stack records using the Manual LIFO/FIFO Adjust utility.
If an item is found by a subsequent run of the program, we can then review what transactions have been done to the item since the previous running of the utility in the hopes of determining the cause of the problem. If an item appears when you run the utility after the initial cleanup, contact the support analyst with whom your are working and indicate when you had run the utility prior to the run that found the new problem items.
If there is no fifo stack for item quantity on hand you will be prevented from adding stack in manual lifo fifo adjustment utility by error messages. Workaround is to go to Miscellaneous Receipt form and receive 1 piece into stock creating the itemlifo record for that 1 piece. Then you can use the manual lifo/fifo adjustment utility to update the stack quantity to equal the original on hand plus the one you added. Then you can do a miscellaneous issue of 1 piece from stock to correct to the original on hand quantity with correct lifo\fifo stack.
