Cross-linked or incorrect notes indexes in Microsoft Dynamics GP

Symptoms

You have notes that are duplicated or cross-linked in Microsoft Dynamics GP.

This article provides a script that can be used to determine the highest note index assigned in a given company. You can also use it to reset your next note index value to the next higher number. This will prevent further note indexes going forward from being cross-linked or incorrect.

This will not correct note indexes that are already incorrect.

Cause 

The next note index in the company master (SY01500) table has been set too low causing new records to receive note indexes that are the same as others.

Resolution


1. Make a backup of the DYNAMICS and all company databases.

2. Run the following statement against the company database that has the duplicated or cross-linked notes.

Note  If you are using Query Analyzer, run the statement in SQL Query Analyzer. To open Query Analyzer, click  Start , point to Programs , point to Microsoft SQL Server , and then click Query Analyzer . If you are using Microsoft SQL Server Management Studio, run the statement in Microsoft SQL Server Management Studio. To open Management Studio, click  Start , point to Programs , point to Microsoft SQL Server 2005/2008/2012 , and then click  SQL Server Management Studio . To run a script, click New Query .

---------------------------------------------
/*
** FindMaxNoteIndex.SQL
**
** Purpose:
**
** Find the max value of NOTEINDX from all tables including Project Accounting. 
**
** This script must be run against the company in which the notes are incorrect.
** it will automatically update your SY01500 for you to the correct next note index.
**
*/

if exists (select * from tempdb..sysobjects where name = '##GPSMaxNote')

drop table dbo.##GPSMaxNote

set nocount on

create table ##GPSMaxNote (MaxNoteIndex numeric(19,5) null)

go

-----------------------

declare @cStatement varchar(255) /* Value from the t_cursor */

declare @noteidx numeric(19,5)

declare @database as varchar(5)

set @database = cast(db_name() as varchar(5))


/* Get the tables that have a column name of NOTEINDX. */

declare T_cursor cursor for

select 'declare @NoteIndex numeric(19,5) select @NoteIndex = max(' +c.name+ ') from ' + o.name + ' insert ##GPSMaxNote values(@NoteIndex)'

from sysobjects o, syscolumns c

where o.id = c.id

and o.type = 'U'

and (c.name = 'NOTEINDX' or c.name like '%noteidx%' or c.name like '%niteidx%' or c.name ='NOTEINDX2')


/* Ok, we have the list of tables. Now get the max value of NOTEINDX from each table. */

open T_cursor

fetch next from T_cursor into @cStatement

while (@@fetch_status <> -1)

begin

exec (@cStatement)

fetch next from T_cursor into @cStatement

end

deallocate T_cursor


/* Display Maximum Note Index */

select 'Max Note Index:', max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null


/* Update Next Note Index */

use DYNAMICS

set @noteidx = (select max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null)

update SY01500 set NOTEINDX = (@noteidx + 1.0) where INTERID=@database

set nocount off

--------------------------------------------------------------------------


This will find the maximum note index for all companies and then update the SY01500 table to ensure the notes will not get cross-linked going forward.


3. Repeat Step 2 for each company database that has cross-linked note indexes. 

This article was TechKnowledge Document ID: 4837