Inventory audit trail

Post feature requests to this forum and a pre-configured poll will automatically be created for you.
Post Reply

How important is this enhancement to you?

It's a critical enhancement that I must have.
7
70%
It's an important enhancement but others are more critical.
2
20%
I'd like to have it but it's not that important.
0
No votes
I'd never use this feature.
1
10%
 
Total votes: 10

User avatar
compunerdy
Admiral (ADM)
Admiral (ADM)
Posts: 1283
Joined: Sun Nov 18, 2007 3:55 pm

Inventory audit trail

Post by compunerdy » Tue Nov 25, 2008 1:45 pm

I would like to see a inventory audit trail so we can find answers when inventory is off.

User avatar
nickc
Captain (CAPT)
Captain (CAPT)
Posts: 276
Joined: Thu Nov 29, 2007 3:48 pm

Re: Inventory audit trail

Post by nickc » Wed Nov 26, 2008 12:02 pm

Tim,

I worked around this problem by adding a table and a couple of triggers, so whenever Able code changes a stock count, a transaction is written. Won't tell you the source order, or admin user, but changing the datetime (I truncated time) would give you something to align with the events that alter stock counts.

Code: Select all

CREATE TABLE [dbo].[custom_ProductInventory](
	[ProductID] [int] NULL,
	[ProductVariantID] [int] NULL,
	[Sku] [nchar](40) NULL,
	[StockDate] [datetime] NULL CONSTRAINT [DF_custom_ProductInventory_StockDate]  DEFAULT (getdate()),
	[OriginalQuantity] [int] NULL,
	[NewQuantity] [int] NULL,
	[StockQuantity] [int] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[triggerProductStockHistory] 
   ON [dbo].[ac_Products] 
   FOR UPDATE
AS 
IF UPDATE(InStock)
BEGIN
	SET NOCOUNT ON;

 	INSERT INTO dbo.custom_ProductInventory
	SELECT
		INSERTED.ProductID,
		0,
		INSERTED.Sku,
		getdate(),
		DELETED.InStock,
		INSERTED.InStock,
		INSERTED.InStock - DELETED.InStock
	FROM
		INSERTED, DELETED
	WHERE
		(INSERTED.InStock - DELETED.InStock) <> 0

END
GO

CREATE TRIGGER [dbo].[triggerProductVariantsStockHistory] 
   ON [dbo].[ac_ProductVariants] 
   FOR UPDATE
AS 
IF UPDATE(InStock)
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.custom_ProductInventory
	SELECT
		INSERTED.ProductID,
		INSERTED.ProductVariantID,
		INSERTED.Sku,
		getdate(),
		DELETED.InStock,
		INSERTED.InStock,
		INSERTED.InStock - DELETED.InStock
	FROM
		INSERTED, DELETED
	WHERE
		(INSERTED.InStock - DELETED.InStock) <> 0

END
GO
Cheers,
Last edited by nickc on Tue Dec 02, 2008 10:51 am, edited 1 time in total.

User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Re: Inventory audit trail

Post by mazhar » Thu Nov 27, 2008 12:55 pm

Nice idea Tim!

User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

Re: Inventory audit trail

Post by jmestep » Fri Nov 28, 2008 8:05 am

A similar feature to show on the catalog/edit pages when it was last changed and by whom would be good. I did this in Able 5 and it saved a lot of finger-pointing as to who messed it up.
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx

User avatar
compunerdy
Admiral (ADM)
Admiral (ADM)
Posts: 1283
Joined: Sun Nov 18, 2007 3:55 pm

Re: Inventory audit trail

Post by compunerdy » Sat Nov 29, 2008 3:27 pm

nickc

Can you dumb down what I need to do in order implement this please.

kastnerd
Commodore (COMO)
Commodore (COMO)
Posts: 474
Joined: Wed Oct 22, 2008 9:17 am

Re: Inventory audit trail

Post by kastnerd » Mon Dec 01, 2008 2:05 pm

i would like this as well.
Not for sales, But edits to the inventory.

User avatar
nickc
Captain (CAPT)
Captain (CAPT)
Posts: 276
Joined: Thu Nov 29, 2007 3:48 pm

Re: Inventory audit trail

Post by nickc » Tue Dec 02, 2008 11:09 am

I've tweaked the script above to use a full datetime value; it just needs to be run as a query against your AbleCommerce database. Not sure how simple that is in your case - I always have an SSMS session running and just assume everyone else does. :) As always, I'd recommend applying to your testing enviroment first. Once the script has been run, anytime the value of a stock count changes (on either a product or a variant) a record will be written in the "custom_ProductInventory" audit table. At some point, I will get around to writing a view that combines transactions together with audit data for reporting/exception purposes.

User avatar
compunerdy
Admiral (ADM)
Admiral (ADM)
Posts: 1283
Joined: Sun Nov 18, 2007 3:55 pm

Re: Inventory audit trail

Post by compunerdy » Tue Dec 02, 2008 1:00 pm

So this records the changes in the database but the only way to view them with this setup is to go back into the database correct?

User avatar
nickc
Captain (CAPT)
Captain (CAPT)
Posts: 276
Joined: Thu Nov 29, 2007 3:48 pm

Re: Inventory audit trail

Post by nickc » Fri Dec 05, 2008 11:17 am

Correct.

User avatar
compunerdy
Admiral (ADM)
Admiral (ADM)
Posts: 1283
Joined: Sun Nov 18, 2007 3:55 pm

Re: Inventory audit trail

Post by compunerdy » Thu Mar 05, 2009 6:08 pm

Nickc

Did you get any further with this?

Robbie@FireFold
Commodore (COMO)
Commodore (COMO)
Posts: 433
Joined: Wed May 28, 2008 9:42 am
Location: Concord, NC
Contact:

Re: Inventory audit trail

Post by Robbie@FireFold » Tue Apr 14, 2009 9:35 am

Any updates here? This is a feature we -need-. Seriously...
Robbie Hodge
General Manager
Robbie@FireFold.com
http://www.FireFold.com

User avatar
nickc
Captain (CAPT)
Captain (CAPT)
Posts: 276
Joined: Thu Nov 29, 2007 3:48 pm

Re: Inventory audit trail

Post by nickc » Tue Apr 14, 2009 1:18 pm

Sorry all - the trigger above was a "just in case, make some extra data" effort during the early stages of our implementing AbleCommerce. There have been no requests made for a more extensive inventory audit made by operation staff here, so no additional work has ever been done. Some code could be added to the trigger to provide more clues about its source (and write more auditable fields to the custom table) - see http://vyaskn.tripod.com/tracking_sql_s ... iggers.htm.

Things move pretty fast around here, but without internal drivers, "at some point" could be a long way away :). I'd recommend continuing to petition Able for a more integrated solution. It's the right way to solve the problem, and would be faster and easier to implement than extending this trigger hack.

User avatar
compunerdy
Admiral (ADM)
Admiral (ADM)
Posts: 1283
Joined: Sun Nov 18, 2007 3:55 pm

Re: Inventory audit trail

Post by compunerdy » Wed Oct 06, 2010 8:38 am

My inventory is constantly getting off somehow. Without a audit system I cannot figure out what the issue is. Can we please get something we can use to figure this out?

Post Reply