Dynamics GP - Fix Note indexes on imported records

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/02/04/fixing-missing-note-index-values/

This script will update all NOTEINDX fields with a value of 0 to the correct noteindx value for the @tablename defined


/*
** AssignNoteIndx.sql
**
** This script assigns note index values to all records
** in the given table that currently do not have one.
**
** Edit the table name below and execute in
** context of appropriate company database.
*/

/* -- parameters -- */

Declare @tablename sysname

/* ############## */

set @tablename = 'RM00101' -- put the table name here

/* ############## */


/* -- working variables -- */
set nocount on

declare @tloop int
declare @sCompanyID integer
declare @sSessionID integer
declare @DEX_ROW_ID integer
declare @NewNoteIndex numeric(19,5)
declare @sError integer
declare @Cnt integer

set @tloop = 1
select @sCompanyID = CMPANYID
 from DYNAMICS.dbo.SY01500
 where INTERID = db_name()
set @sSessionID = @@spid
set @Cnt = 0

/* @@@ MAINLINE @@@ */

while @tloop = 1 begin
set @tloop = 0

/* -- check table exists -- */
if object_id (@tablename) is null begin
print 'table : ' + @tablename
print 'does not exist in this database : ' + db_name()
break
end
if @sCompanyID is null begin
print 'No Entry in DYNAMICS system table for this company'
break
end

print 'Assigning Note Index for table : ' + @tablename

/* -- create a proc for repeated calls -- */

exec ('create procedure #nextdex as declare @DEXROWID integer select top 1 @DEXROWID = DEX_ROW_ID from ' + @tablename + ' where NOTEINDX = 0 return isnull(@DEXROWID,0) ' )
exec ('create procedure #assignnext (@NextIndx integer, @DEXROWID integer ) as update ' + @tablename + ' set NOTEINDX = @NextIndx where DEX_ROW_ID = @DEXROWID return @@ERROR ' )


exec @DEX_ROW_ID = #nextdex

while @DEX_ROW_ID > 0 begin

begin transaction
exec DYNAMICS.dbo.smGetNextNoteIndex @sCompanyID, @sSessionID, @NewNoteIndex OUTPUT, @sError OUTPUT
exec #assignnext @NewNoteIndex, @DEX_ROW_ID
commit transaction

set @Cnt = @Cnt + 1

exec @DEX_ROW_ID = #nextdex
end

print 'Number of records changed : ' + convert(varchar(8), @Cnt)


drop procedure #nextdex
drop procedure #assignnext

end

/* ###### end of script #### */