What to do if you can't cancel a pick list from the Cancel Picks task

1 minute read time.

In Sage 500ERP, if the Pick List is not available in Cancel Picks, Reprint Picks, Confirm Picks or Generate Shipment, or you're seeing the error, "Pick List No xxx is invalid. Please re-enter." when entering pick list number in Cancel Picks, the following steps can help.

Note: This resolution requires knowledge of Microsoft T-SQL and ability to determine whether or not shipment has been generated for the pick.

The following T-SQL statements identify the picklistkey and shipment lines associated to the picklistkey. Inspect the value of the PickingComplete column (0=No, 1=Yes). The PickingComplete field must be set to 1 (Yes) before the pick list or the related shipment can be cancelled.

--To get the picklistkey, use picklist number and replace in the below query

SELECT PickListKey FROM tsopicklist WITH(NOLOCK) WHERE picklistno = 'xxxxxxxxxx'

--To get the shipment lines associated with the pick list, replace xxx below with PickListKey from above query

SELECT * FROM tsoShipLine WITH(NOLOCK) WHERE PickListKey = xxx

--To update the PickingCompleteFlag for the shipment lines associated with the pick list, replace xxx below with PickListKey from above query

UPDATE tsoShipLine SET PickingComplete =1 WHERE PickListKey = xxx

(You may optionally encapsulate the UPDATE statement between a T-SQL transaction. An example is shown below. The number of rows updated should match the number of rows returned from the second query above; SELECT * FROM tsoShipLine WITH(NOLOCK) WHERE PickListKey = xxx)


BEGIN TRANSACTION
UPDATE tsoShipLine SET PickingComplete =1 WHERE PickListKey = xxx
--If number of rows update looks incorrect or you want to undo your update execute ROLLBACK
ROLLBACK
--If number of rows update looks correct or you want to commit your changes COMMIT TRANSACTION
COMMIT TRANSACTION

Disclaimers


Backup Warning

Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.

Database Warning

These steps require knowledge of database engines and application databases (DBs) used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.). Customer Support is not responsible for assisting with these steps and cannot be responsible for errors resulting from changes to the database engine or DBs. Before making changes, backup all system and application DBs required for a full restore. Contact an authorized business partner or DB administrator for assistance.