Overview
Fixing a stocktake that cannot be applied.
This only applies to stocktakes that show as in progress on the stocktake screen,
that gives the following error when you try to apply the stocktake.
This can be caused by several different issues that require different methods to fix them. Using the wrong method will make everything worse, so don’t apply a fix unless your 100% sure it’s the right fix for the situation.
Scenario 1.
Stocktake applied, but new products holding it open
The first scenario is the most common one for Redcape. It is caused by a bug when you add new products to the system while a stocktake is in progress, and the stocktake has been applied to some but not all stores. Backoffice will incorrectly add stkline records for the new products to the stores where the stocktakes have already been applied. The fix for this is still in the backlog. Backoffice should only add records for stores that have not been applied. This is to allow adding of products that didn’t exist when the stocktake was started that need to be counted in the stocktake.
To positively identify that this is the cause, check the stocktake entry screen for each of the stores that show In Progress. Create a view that includes the Entered, Applied, Qty Counted, and Qty onHand columns. At Redcape there is already a view called ‘Simon Test’. Select this view and turn on the ‘Sub Products & Inactive’ flag. Filter the view and un-check the Applied column. If you have any products where the Entered and Applied flags are both un-checked, it is probably this issue.
To be sure that it’s safe to fix using this first method, make sure that the total of the Qty Counted and Qty onHand columns are both zero, or at least equal. If it’s zero, then it’s definitely the above issue. If it’s any another value, so long as the two Qty totals are the same, this fix should also be safe to use.
The Fix.
Make sure that the stocktake has been fully applied for any other stores in this stocktake.
Using the above view and filter, make note of how many products are displayed for each of the stores. The total number of records will be used to make sure that no mistakes are made.
Make note of the Venue ID for the venue that the stocktake belongs to.
To get the StkHeadID of the stocktake, use this query. Replace the XX with the correct venue id. The StkHeadID of the first row will be the highest ID, which is the most recent stocktake.
select * from StkHead
where VenueID = XX
order by StkHeadID desc
Using this StkHeadID, this query will give you the list of products that are keeping the stocktake open. The number of rows returned should match the total number of products found when using the views and filters in step 2 on the stocktake entry screen. Replace XXXX with the StkHeadID from step 4.
select * from stkline
where StkHeadID = XXXX
and Applied = 0
If the above query returns the same number of products as the filtered views, then we can mark these counts as applied. If not, check everything in the previous steps.
We will be updating the records in a SQL transaction as a safety measure. The update should return the same number of rows as the query above. If it does, we commit the transaction. If not, we rollback. The transaction must be committed or rolled back quickly as the database table will be locked until it is.
Using the same StkHeadID from the above queries
Begin Transaction
Update stkline
Set Applied = 1
where StkHeadID = XXXX
and Applied = 0
If the number of rows affected matches the select query, then commit. Otherwise, rollback.
RollBack
Commit
Lastly, we need to go back to the stocktake screen to fully close the stocktake. When you select the venue, the stocktake will now show as completed. Do not skip this step.
To confirm that the stocktake has closed, check the stocktake screen a second time. The stocktake screen will show that the stocktake has not started. This confirms that the stocktake was closed properly.
Scenario 2.
Stocktake only partially applied
A less common issue for Redcape is where something happened while the stocktake was being applied, and some of the products weren’t applied. This can happen if Backoffice is closed while the stocktake is being applied.
To identify this scenario, we need to check the stocktake entry screens for each of the stores in the stocktake. Using a view that contains the Qty onHand and Start Qty fields, turn on the ‘Sub Products & Inactive’ flag. Filter the view, check the Entered column and un-check the Applied column. If you have any products where the Entered flag is checked and the Applied flag is un-checked, it is probably this issue. To be sure, the total of the Qty onHand and Start Qty Counted fields should be the same or very close. The total might be slightly different as they have a different number of decimal places.
The fix for this is very different from the previous one. The products that haven’t been applied must be applied in Backoffice for the stock to update correctly, but we must not re-apply any products that have already been applied. Backoffice doesn’t check that the starting quantity and quantity on hand are the same before applying them.
To fix this we need to move any products that have already been applied to a dummy stocktake, apply the stocktake in Backoffice for the remaining unapplied products, then move the products in the dummy stocktake back to the proper stocktake id.
The Fix.
Make sure that the stocktake has been fully applied for any other stores in this stocktake.
Using the above view, change the filter for the Applied column to checked and make note of how many products are displayed for each of the stores. The total number of records is the number of products that have already been applied. We will use this number to make sure that no mistakes are made.
Make note of the Venue ID for the venue that the stocktake belongs to.
To get the StkHeadID of the stocktake, use this query. Replace the XX with the correct Venue ID. The StkHeadID of the first row will be the highest ID, which is the most recent stocktake.
select * from StkHead
where VenueID = XX
order by StkHeadID desc
Using this StkHeadID, this query will give you the list of products that are keeping the stocktake open. This should be greater than zero.
select * from stkline
where StkHeadID = XXXX
and Applied = 0
Using the same StkHeadID, this query will give you the list of products that have already been applied. This is number of records that we will be moving to the dummy stocktake and should match the number from the filtered view above.
select * from stkline
where StkHeadID = XXXX
and Applied = 1
Before we move the already applied products to a dummy stocktake, make sure that there aren’t already products in the dummy stocktake. This should return zero records for dummy StkHeadID 9999.
select * from stkline
where StkHeadID = 9999
If the above returns zero records, we can move the applied products to the dummy StkHeadID.
Begin Transaction
Update stkline
Set StkHeadID = 9999
where StkHeadID = XXXX
and Applied = 1
Commit if the number matches the query above. Rollback if not.
RollBack
Commit
Now you can go into Backoffice and apply the stocktake for the remaining products as normal.
10. Finally, we need to move the products on the dummy stocktake back to their original StkHeadID.
Begin Transaction
Update stkline
Set StkHeadID = XXXX
where StkHeadID = 9999
Commit if the number matches the number that were moved originally. Rollback if not.
RollBack
Commit