The timItemUnitOfMeas table is missing the _AppAudit trigger and therefore does not track any chages to the data in the tciMaintAuditLog table. This prevents inspection of when and whom is changing the data in this table. This data controls processes like showing the item (and UOM) on eCustomer/eBusiness Suite, the UPC that is used for barcoding (in the warehouse/labeling), UOMs available for Purchase and/or sale, etc.
by: Matthew A. | over a year ago | 5 - Integration
Comments
/****** Object: Trigger [dbo].[tR_timItemUnitOfMeas_AppAudit] Script Date: 11/04/2011 12:31:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tR_timItemUnitOfMeas_AppAudit] ON [dbo].[timItemUnitOfMeas]
FOR INSERT, UPDATE, DELETE
AS
/* Copyright (c) 1995-2008 Sage Software, Inc. */
BEGIN
-- for update
DECLARE
@AuditOption SMALLINT
,@Operation SMALLINT
,@TableName VARCHAR(18)
,@Userid VARCHAR(30)
,@InsRowStatus INT
,@DelRowStatus INT
,@CompanyID VARCHAR(3)
DECLARE
@NewItemKey INT
,@NewTargetUnitMeasKey INT
,@NewConversionFactor FLOAT
,@NewUnitVolume FLOAT
,@NewUnitWeight FLOAT
,@NewUPC VARCHAR(15)
,@NewUseForPurchases SMALLINT
,@NewUseForSales SMALLINT
,@NewUseStdConv SMALLINT
,@NewCompanyID VARCHAR(3)
DECLARE
@OldItemKey INT
,@OldTargetUnitMeasKey INT
,@OldConversionFactor FLOAT
,@OldUnitVolume FLOAT
,@OldUnitWeight FLOAT
,@OldUPC VARCHAR(15)
,@OldUseForPurchases SMALLINT
,@OldUseForSales SMALLINT
,@OldUseStdConv SMALLINT
,@OldCompanyID VARCHAR(3)
DECLARE
@ItemID VARCHAR(30)
,@IDValue VARCHAR(255)
,@OldValue VARCHAR(255)
,@NewValue VARCHAR(255)
SET @TableName = 'timItemUnitOfMeas'
SET @CompanyID = ''
-- get real user id
EXECUTE spGetloginName
@Userid OUTPUT
-- Insert cursor
DECLARE timItemUnitOfMeasInsertCursor CURSOR
FOR
SELECT
ItemKey
,TargetUnitMeasKey
,ConversionFactor
,UnitVolume
,UnitWeight
,UPC
,UseForPurchases
,UseForSales
,UseStdConv
FROM
inserted
OPEN timItemUnitOfMeasInsertCursor
FETCH NEXT FROM timItemUnitOfMeasInsertCursor INTO @NewItemKey,
@NewTargetUnitMeasKey, @NewConversionFactor, @NewUnitVolume,
@NewUnitWeight, @NewUPC, @NewUseForPurchases, @NewUseForSales,
@NewUseStdConv
SET @InsRowStatus = @@FETCH_STATUS
-- Delete cursor
DECLARE timItemUnitOfMeasDeleteCursor CURSOR
FOR
SELECT
ItemKey
,TargetUnitMeasKey
,ConversionFactor
,UnitVolume
,UnitWeight
,UPC
,UseForPurchases
,UseForSales
,UseStdConv
FROM
deleted
OPEN timItemUnitOfMeasDeleteCursor
FETCH NEXT FROM timItemUnitOfMeasDeleteCursor INTO @OldItemKey,
@OldTargetUnitMeasKey, @OldConversionFactor, @OldUnitVolume,
@OldUnitWeight, @OldUPC, @OldUseForPurchases, @OldUseForSales,
@OldUseStdConv
SET @DelRowStatus = @@FETCH_STATUS
WHILE @InsRowStatus = 0
OR @DelRowStatus = 0
BEGIN
IF @DelRowStatus <> 0
SET @Operation = 1 -- no rows in deleted so it must be insert
ELSE
BEGIN
IF @InsRowStatus <> 0
SET @Operation = 3 -- no rows in inserted so it must be delete
ELSE
SET @Operation = 2 -- else update
END
/* Get company IDs */
--New Company
SELECT
@CompanyID = CompanyID
,@NewCompanyID = CompanyID
FROM
timitem WITH ( NOLOCK )
WHERE
ItemKey = @NewItemKey
--Old company (should always be the same as new, but ....)
SELECT
@OldCompanyID = CompanyID
FROM
timitem WITH ( NOLOCK )
WHERE
ItemKey = @OldItemKey
-- minimize the fetch for options
IF @CompanyID IS NULL
OR @CompanyID <> COALESCE(@NewCompanyID, @OldCompanyID)
BEGIN
SELECT
@AuditOption = CASE @Operation
WHEN 1 THEN MaintAuditAdd
WHEN 2 THEN MaintAuditChange
ELSE MaintAuditDelete
END
FROM
timOptions
WHERE
CompanyID = COALESCE(@NewCompanyID, @OldCompanyID)
SET @CompanyID = COALESCE(@NewCompanyID, @OldCompanyID)
END
/* If we didn't get an option value log in detail */
SELECT
@AuditOption = COALESCE(@AuditOption, 2)
/* Build the ID Value */
SELECT
@ItemID = COALESCE(ItemID, '')
FROM
timItem WITH ( NOLOCK )
WHERE
itemkey = COALESCE(@NewItemKey, @OldItemKey)
/* Get the UOMID and concatenate */
SELECT
@IDValue = @ItemID + ' | ' + COALESCE(UnitMeasID, '')
FROM
tciUnitMeasure WITH ( NOLOCK )
WHERE
UnitMeasKey = COALESCE(@NewTargetUnitMeasKey,
@OldTargetUnitMeasKey)
IF @AuditOption = 2 -- detail audit
BEGIN
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewItemKey)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldItemKey)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'ItemKey'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewTargetUnitMeasKey)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldTargetUnitMeasKey)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'TargetUnitMeasKey'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewConversionFactor)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldConversionFactor)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'ConversionFactor'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewUnitVolume)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldUnitVolume)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'UnitVolume'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewUnitWeight)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldUnitWeight)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'UnitWeight'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewUPC)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldUPC)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'UPC'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewUseForPurchases)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldUseForPurchases)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'UseForPurchases'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewUseForSales)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldUseForSales)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'UseForSales'
,@OldValue
,@NewValue
,@Userid
SELECT
@NewValue = CONVERT(VARCHAR(255), @NewUseStdConv)
SELECT
@OldValue = CONVERT(VARCHAR(255), @OldUseStdConv)
IF ( COALESCE(@NewValue, '') <> COALESCE(@OldValue, '') )
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,'UseStdConv'
,@OldValue
,@NewValue
,@Userid
END
IF @AuditOption = 1 -- summary audit
EXECUTE spciMaintAuditLogInsert
@IDValue
,@CompanyID
,@TableName
,@Operation
,NULL
,NULL
,NULL
,@Userid
IF @InsRowStatus = 0
BEGIN
FETCH NEXT FROM timItemUnitOfMeasInsertCursor INTO @NewItemKey,
@NewTargetUnitMeasKey, @NewConversionFactor,
@NewUnitVolume, @NewUnitWeight, @NewUPC,
@NewUseForPurchases, @NewUseForSales,
@NewUseStdConv
SET @InsRowStatus = @@FETCH_STATUS
END
IF @DelRowStatus = 0
BEGIN
FETCH NEXT FROM timItemUnitOfMeasDeleteCursor INTO @OldItemKey,
@OldTargetUnitMeasKey, @OldConversionFactor,
@OldUnitVolume, @OldUnitWeight, @OldUPC,
@OldUseForPurchases, @OldUseForSales,
@OldUseStdConv
SET @DelRowStatus = @@FETCH_STATUS
END
END
CLOSE timItemUnitOfMeasInsertCursor
DEALLOCATE timItemUnitOfMeasInsertCursor
CLOSE timItemUnitOfMeasDeleteCursor
DEALLOCATE timItemUnitOfMeasDeleteCursor
END