Stop loss prevention

[AKPolarBear]

As we all know (or should), that Macola allows OE & PO numbers to be used, printed, voided and reused. This allows product to leave without a real trace. Proper shipping procedures aside, this action requires collusion between one or more employees to be effective.

The Macola audit report is a nightmare and creating a Crystal report has proven to be about the same.

I am assuming others may have had to deal with this problem so I am interesting any learning any possible solutions to prevent or better track this problem.

 

[kscheeler]

I would create a trigger on the order header table to capture key information; ord_no, status, cus_no, ord_dt, entered_dt po_no, etc.. along with a datetime stamp and the user who is logged in to your own table. You could do it on INSERT or UPDATE as well. This would be in addition to the Macola audit tables but there would be no function within Macola to purge it so only people who have delete priviledges in SQL Server would have the ability to update the table.

You could also then write reports off that table. I use this approach alot when I want to track changes to specific fields within a table rather than use the standard audit tables because they only have the fields I'm looking for.

Here's an example that tracks the price change on an order.


--------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udt_OELinePriceChange]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[udt_OELinePriceChange]
GO


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER udt_OELinePriceChange ON OEORDLIN_SQL
FOR UPDATE
AS

Declare @OrdNo Char(8)
Declare @LineNo Int
Declare @CusNo Char(12)
Declare @ItemNo Char(12)
Declare @ItemDesc Char(30)
Declare @Loc Char(3)
Declare @RequestDt Int
Declare @QtyOrdered varchar (20)
Declare @OldPrice varchar (30)
Declare @NewPrice varchar (30)

Select @OrdNo = I.ord_no, @LineNo = I.line_no, @CusNo = I.cus_no,
@ItemNo = I.Item_No, @ItemDesc = L.Item_Desc_1, @Loc = I.Loc, @RequestDt = I.request_dt,
@NewPrice = I.unit_price
From Inserted I

Select @OldPrice = unit_price from Deleted


if cast(@oldprice as decimal(13,6)) - cast(@newprice as decimal(13,6)) <> 0
Insert Into [DATA].[DBO].[TBLOELPRC_SQL]
Select '001', @OrdNo, @LineNo, @CusNo, @ItemNo, @ItemDesc, @Loc, @RequestDt, @QtyOrdered,
getdate(), cast(@oldprice as decimal(13,6)), cast(@newprice as decimal(13,6)), system_user


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------------------------------------

Kevin Scheeler

 

[AKPolarBear]

This is along the lines of what I am thinking is the best solution. Making this happen is another story as I do not have the skill to do this. A learning curve is ahead!

Another thought is to remove the user ablity to void orders and make it a supervisor/manager job.

 

[AKPolarBear]

Okay, here is a beginning stab at this:
********************************************
CREATE TRIGGER OENUMTRACK ON OEORDHDR_SQL
FOR INSERT, UPDATE, DELETE
AS

BEGIN

Declare @ord_no Char(8)
Declare @status Char(1)
Declare @cus_no Char(12)
Declare @ord_dt num (8)
Declare @entered_dt num (8)
Declare @bill_to_name Char(40)
Declare @slspsn_no Char(3)

begin
exec create_oenum_track @ord_no
end
END
********************************************

I know this is not correct. I don't think I can declare the INSERT, UPDATE, DELETE in one line. I'm not 100% sure the declare statements are correct either.
What I also don't have is the time of the action and the action itself. In the header audit table, under audit_action, there is A, B, C, D values. I am interested in finding OE's being deleted, especially after being printed.

Suggestions?

 

[stravis]

Here is breakdown on audit actions in the audit tables
A - Created
B - Record Before a change
C - Record After a change
D - Deleted (shows snapshot of record before delete)