Views:

I have a case where the user is receiving a duplicate key error when running Paid Transaction Removal. This is caused by a multi currency rounding distribution line being added to the distributions for rounding. This line has the same SEQNUMBR as another line. Which the RM10101 allows because the userid field is differnet but the RM30301 does not due to the primary key on the RM30301 only being the doc type, doc number and Sequence number. To correct the Sequence numbers in the RM10101 the following cursor can be run. It will fix all instances of the duplicates.

/*Make Backup as this cannot be reverted*/
--select * into RM30301BACK from RM30301

declare @RMDTyp smallint, 
@Docnum char(22),
@Seqnum int

declare @NewSeq int

declare T_cursor cursor for
select RMDTYPAL, DOCNUMBR, SEQNUMBR
from
(select RMDTYPAL, DOCNUMBR, SEQNUMBR
from RM10101
group by RMDTYPAL, DOCNUMBR, SEQNUMBR
having count(*) > 1) a
group by RMDTYPAL, DOCNUMBR, SEQNUMBR


open T_cursor
fetch next from T_cursor into @RMDTyp, @Docnum, @Seqnum
while (@@fetch_status <> -1)
begin
set @NewSeq = (select MAX(SEQNUMBR) from RM10101 where RMDTYPAL=@RMDTyp and DOCNUMBR=@Docnum)

update RM10101 set SEQNUMBR = @NewSeq, @NewSeq = @NewSeq + 16384 
where RMDTYPAL = @RMDTyp and DOCNUMBR = @Docnum and SEQNUMBR = @Seqnum


fetch next from T_cursor into @RMDTyp, @Docnum, @Seqnum 
end

close T_cursor
deallocate T_cursor