Thursday, December 6, 2012

Auto Assign Newly Added Analytical Accounting Code to All Users

I got a request from one of my customers, they had a need to auto assign newly created AA codes to all users and that how the idea started, I saw the scripts created by Sivakumar earlier to auto assign all codes to all users and used his cross join to create the below trigger.

The trigger is simply works on AAG00401 table upon record insert, it assigns the newly created codes to all GP users automatically, just run below script over your company database and enjoy.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

 

-- =============================================
-- Author : Mohammad R. Daoud
-- Create date: 09:56 AM ‎07/‎12/‎2012
-- Description: This trigger as created to auto assign
-- newly created analytical accounting codes to all users.
-- =============================================
CREATE TRIGGER DI_AutoAssignSecurity
ON AAG00401
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
DECLARE @aaTrxDimID INT
DECLARE @aaTrxDimCodeID INT

SELECT TOP 1 @aaTrxDimID = aaTrxDimID, @aaTrxDimCodeID = aaTrxDimCodeID FROM INSERTED
DELETE FROM AAG02000 WHERE aaTrxDimID = @aaTrxDimID AND AAG02000.aaTrxDimCodeID = @aaTrxDimCodeID
INSERT INTO AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust) (SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM AAG00401 A CROSS JOIN DYNAMICS..SY01400 B WHERE A.aaTrxDimID = @aaTrxDimID AND A.aaTrxDimCodeID = @aaTrxDimCodeID)
END
GO

1 comment:

Evan Marcus said...

Thanks for taking time for sharing this article, it was excellent and very informative. Its really very useful of all of users. I found a lot of informative stuff in your article. Keep it up.
Steering Rack

Related Posts:

Related Posts with Thumbnails