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
Views:
Keywords: duplicate key error, Paid Transaction Removal