Epicor’s Prophet 21 has several useful functions that are designed to help keep your database clean and running in top condition.  Unfortunately, many of these functions require you to execute via the user interface, meaning that a human has to get involved.  One of my coworkers jokingly describes this as: “the monkey has to be there to push the button”.

One of these functions is the purging of item/bin records that are no longer in use.  Prophet 21 keeps a record for each item and bin combination that has ever existed since your last purge action.  If you are moving a lot of inventory through your warehouse, the number of records is going to increase quickly.  This is further amplified if you are using random bin locations, or staging locations where there is a high propensity for transient inventory through a bin.

In the UI, you will find this feature under System Administration -> System -> Transaction Deletion -> Item Bin Records.

How Prophet 21 Bin Deletion works

The idea is that Prophet 21 will delete all of the bin records where the item’s on hand quantity for that bin is zero.  There are a few caveats (e.g. Primary Bin Location) that will prevent a record from being deleted, but for the most part, this feature does a good job of cleaning things up.

Keeping these records in check is important.  If for no other reason, it makes life much easier when looking through the Bin tab in Item Master Inquiry (F2 / IMI).  Also, it speeds up queries that depend on looking through item/bin records and generally helps keep the system running at a better level of performance where the inventory is concerned.

Automating this function via a SQL Agent Job is a fairly straightforward task, since Prophet 21 merely executes a stored procedure when you execute this task through the Prophet 21 UI.  Below, I am simply looping over the locations that are active.  (In our case we use lot/bin, so I added a filter for that as well).  For each active location, I am executing the stored procedure that deletes these item/bin records and then sending a message to the console each time a location is completed.

The Code

DECLARE @location INT
DECLARE @company VARCHAR(20)
DECLARE @msg VARCHAR(255)
DECLARE locCursor CURSOR FOR 
	SELECT 
		location_id, company_id
	FROM 
		location 
	WHERE 
		delete_flag = 'N'
		and lot_bin_integration = 'Y'
	ORDER BY
		company_id, location_id

OPEN locCursor
FETCH NEXT FROM locCursor INTO @location, @company
WHILE @@FETCH_STATUS = 0
BEGIN

	EXEC dbo.p21_inv_bin_deletion
		@as_CompanyID	= @company
		,@ai_LocationID = @location
		,@as_BeginItemID =''
		,@as_EndItemID = 'ZZZZZZZZZZZZZZZZZZZ'
		,@as_BeginBinID = ''
		,@as_EndBinID = 'ZZZZZZZZZZZ'
	SET @msg = 'Inv Bin Deletion Completed for Company ' 
                         + @company + ', Location ' + CAST(@location AS VARCHAR)

	RAISERROR(@msg,0,1) WITH NOWAIT

	FETCH NEXT FROM locCursor INTO @location, @company

END
CLOSE locCursor
DEALLOCATE locCursor

Disclaimer

Test in Play, Your Mileage May Vary, and I am not responsible for damage done to your system.  Any code from this site that you use should be considered to be used at your own risk.

Automating Prophet 21

I have this set up as a SQL job that runs weekly.  I have found it to be useful for keeping things cleaned up and making IMI’s  Bin tab much easier to read without all of the zero records in the way.  Hopefully, you find it useful as well.   You can find this and other scripts for Prophet 21 that I have published on my GitHub P21 project.

AP

Read More about Prophet 21.