Inventory audit trail
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Inventory audit trail
I would like to see a inventory audit trail so we can find answers when inventory is off.
Re: Inventory audit trail
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.
Cheers,
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
Last edited by nickc on Tue Dec 02, 2008 10:51 am, edited 1 time in total.
Nick Cole
http://www.ethofy.com
http://www.ethofy.com
Re: Inventory audit trail
Nice idea Tim!
- jmestep
- AbleCommerce Angel
- Posts: 8164
- Joined: Sun Feb 29, 2004 8:04 pm
- Location: Dayton, OH
- Contact:
Re: Inventory audit trail
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
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
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Re: Inventory audit trail
nickc
Can you dumb down what I need to do in order implement this please.
Can you dumb down what I need to do in order implement this please.
Re: Inventory audit trail
i would like this as well.
Not for sales, But edits to the inventory.
Not for sales, But edits to the inventory.
Re: Inventory audit trail
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.
Nick Cole
http://www.ethofy.com
http://www.ethofy.com
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Re: Inventory audit trail
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?
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Re: Inventory audit trail
Nickc
Did you get any further with this?
Did you get any further with this?
-
- Commodore (COMO)
- Posts: 433
- Joined: Wed May 28, 2008 9:42 am
- Location: Concord, NC
- Contact:
Re: Inventory audit trail
Any updates here? This is a feature we -need-. Seriously...
Re: Inventory audit trail
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.
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.
Nick Cole
http://www.ethofy.com
http://www.ethofy.com
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Re: Inventory audit trail
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?