Hi Richard,
You would just have to add a post proc like you mentioned for this. You could either do a check to see if the record exists and if not insert one for the customer using your variables, or you could use a script like this that just updates all customer ids in the company that are missing a PA00501 record in the post proc.
declare @CUSTNMBR char(30)
declare @PAcustalias char (30)
declare @PAcustaliasNEW char (30)
select @CUSTNMBR = '',
@PAcustalias = '',
@PAcustaliasNEW = ''
declare T_cursor CURSOR for select RM00101.CUSTNMBR, substring(RM00101.CUSTNMBR, 1, 5) as PAcustalias
from RM00101
left outer join PA00501 on RM00101.CUSTNMBR = PA00501.CUSTNMBR
where PA00501.CUSTNMBR is null
order by RM00101.CUSTNMBR, substring(RM00101.CUSTNMBR, 1, 5)
set nocount on
OPEN T_cursor
FETCH NEXT FROM T_cursor INTO @CUSTNMBR,@PAcustalias
WHILE (@@FETCH_STATUS <> -1)
begin
select @PAcustaliasNEW = @PAcustalias
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'1')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'2')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'3')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'4')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'5')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'6')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'7')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'8')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,4)+'9')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'10')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'11')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'12')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'13')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'14')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'15')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'16')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'17')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'18')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'19')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'20')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'21')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'22')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'23')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'24')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'25')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'26')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'27')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'28')
end
if exists (select top 1 * from PA00501 where PAcustalias = @PAcustaliasNEW)
begin select @PAcustaliasNEW = (substring(@PAcustalias,1,3)+'29')
end
insert into PA00501
select RM00101.CUSTNMBR, @PAcustaliasNEW, 0, 0, 'DEFAULT', '',
'', 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0
from RM00101
where RM00101.CUSTNMBR = @CUSTNMBR
FETCH NEXT FROM T_cursor INTO @CUSTNMBR,@PAcustalias
end
DEALLOCATE T_cursor
I hope this helps!
Isaac Olson
Microsoft Support
