Analytical Accounting Primary Key Problems

Personally I used to have allot of primary key issues when working with analytical accounting due to errors in the Analytical Accounting Index Master table in Dynamics database, and used to open this table manually and fill the correct indexes there.

Today @ Interesting Findings & Knowledge Sharing blog, “veeyeskay” posted an SQL script that updates this table Dynamically! Find the script below or visit the post for more information here.

USE [DYNAMICS]
GO
IF EXISTS ( SELECT *
FROM     sys.objects
WHERE   object_id =  OBJECT_ID(N'[dbo].[sp_FixAARecordCount]’)
AND type IN (  N’P’, N’PC’ ) )
DROP PROCEDURE [dbo].[sp_FixAARecordCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[sp_FixAARecordCount]
AS
DECLARE @INTERID CHAR(5)
DECLARE @CMPANYID INT
DECLARE @SQL CHAR(8000)
DECLARE cr_Companies CURSOR
FOR SELECT  INTERID,
CMPANYID
FROM    DYNAMICS.dbo.SY01500
ORDER BY CMPANYID
OPEN cr_Companies
FETCH NEXT  FROM cr_Companies INTO @INTERID, @CMPANYID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAcctClassID),0) FROM ‘
+ @INTERID
+ ‘..AAG00201) WHERE  aaTableID = 201 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDistrQueryID),0) FROM ‘
+ @INTERID
+ ‘..AAG00301) WHERE  aaTableID = 301 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaMLQueryID),0) FROM ‘
+ @INTERID
+ ‘..AAG00310) WHERE  aaTableID = 310 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimID),0) FROM ‘
+ @INTERID
+ ‘..AAG00400) WHERE  aaTableID = 400 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeID),0) FROM ‘
+ @INTERID
+ ‘..AAG00401) WHERE  aaTableID = 401 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeNumID),0) FROM ‘
+ @INTERID
+ ‘..AAG00402) WHERE  aaTableID = 402 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeBoolID),0) FROM  ‘
+ @INTERID
+ ‘..AAG00403) WHERE  aaTableID = 403 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeBoolID),0) FROM  ‘
+ @INTERID
+ ‘..AAG00404) WHERE  aaTableID = 404 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDateID),0) FROM ‘
+ @INTERID
+ ‘..AAG00500) WHERE  aaTableID = 500 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTreeID),0) FROM ‘
+ @INTERID
+ ‘..AAG00600) WHERE  aaTableID = 600 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaNodeID),0) FROM ‘
+ @INTERID
+ ‘..AAG00601) WHERE  aaTableID = 601 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaOption),0) FROM ‘
+ @INTERID
+ ‘..AAG00700) WHERE  aaTableID = 700 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAliasID),0) FROM ‘
+ @INTERID
+ ‘..AAG00800) WHERE  aaTableID = 800 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaBudgetTreeID),0) FROM ‘
+ @INTERID
+ ‘..AAG00900) WHERE  aaTableID = 900 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaUDFID),0) FROM ‘
+ @INTERID
+ ‘..AAG01000) WHERE  aaTableID = 1000 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLWorkHdrID),0) FROM ‘
+ @INTERID
+ ‘..AAG10000) WHERE  aaTableID = 10000 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaSubLedgerHdrID),0) FROM ‘
+ @INTERID
+ ‘..AAG20000) WHERE  aaTableID = 20000 AND CMPANYID = ‘
+ @CMPANYID + ”
)
EXEC
( ‘UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLHdrID),0) FROM ‘
+ @INTERID
+ ‘..AAG30000) WHERE  aaTableID = 30000 AND CMPANYID = ‘
+ @CMPANYID + ”
)
FETCH NEXT FROM cr_Companies INTO  @INTERID, @CMPANYID
END
CLOSE cr_Companies
DEALLOCATE cr_Companies
GO
GRANT EXECUTE ON sp_FixAARecordCount TO  DYNGRP

Regards,

Mohammad R. Daoud
MVP, MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com

Advertisements

About Mohammad R. Daoud
Mohammad R. Daoud has been working as a Microsoft Dynamics GP consultant since 2004. His career path started with version 7.5; he studied every single tip of the application technicalities and did allot of successful implementations that includes functional consultations, analysis, and custom development projects. He holds a graduate degree in Computer Science and currently he is pursuing MBA degree in Accounting. In January 2007 he was nominated to the Microsoft Most Valuable Professional (MVP) certificate and was certified in April 2008 due to his online contributions in the Dynamics Community (Dynamics GP Newsgroups, Forums, User Groups and his blog: http://mohdaoud.blogspot.com). Worth to mention that he was also listed as one of the Microsoft Dynamics Top 100 Most Influential People in 2009 by DynamicsWorld. And more... • Demonstrated Passion for MS Products through participating in beta testing for some product versions, an effective member of Microsoft Connect. • Talented in securing strong high quality relationships with premier customers, partners and sales teams passionately and profitably. • Dedicated to meeting the expectations and requirements of internal and/or external customers • Problem Solver – Uses systematic approach to uncover true problem(s) and practical solution and can marshal resources to solve problem! • Proven record of effective account management, including Account Planning, Opportunity Management, and Business Management Excellence and working as part of a larger virtual team • Successful in delivering high quality technical engagements in the enterprise Dynamics GP implementations area according to customer requirements. • Dedicated, Committed, Self Motivated, Creative and highly focused with excellent communication, Negotiation, presentation, Active Listening and Objection Handling skills. • Experienced in Profitable researches and studies including white papers and technical presentations.

One Response to Analytical Accounting Primary Key Problems

  1. Great Information. Thank you. Thank you for sharing. I found college information which provides Computer Support Specialist Program. This 2-semester certificate program provides students with an introduction to many areas of information technology including computer architecture, hardware, operating systems, network administration, and support. You Can Also Visit….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: