Create your own taGetNextNumber stored procedure

I have heard this one very often on developers and users newsgroups alike. In past releases of eConnect (8.0 and before) it was not possible to retrieve the next document number for a few existing transactions in the system via eConnect's COM interface, case in point the Purchasing Receipt Transaction. However, the problem is not limited to eConnect. Many developers have tried to retrieve transactions next document numbers via Integration Manager and have faced the same issue.

The following sample code segment creates a wrapper around the little known dbo.ivNumber_Inc_Dec stored procedure, present within the company database.

create procedure sptaGetNextNumber 
  @poprctnm char(17) output, 
  @errorstate int output
as
declare @nextrctnm char(17)

select @poprctnm = poprctnm, @nextrctnm = poprctnm from pop40100
-- call Inc_Dec procedure

exec @errorstate = dbo.ivNumber_Inc_Dec 1, @nextrctnm output, @errorstate output

if @errorstate = 0
  update pop40100 set poprctnm = @newrctnm

return 
go

grant execute on sp_taGetNextNumber to DYNGRP

The dbo.ivNumber_Inc_Dec stored procedure takes a document number string and increments or decrements the numeric portion based on a flag. The stored procedure uses the following parameters:

[dbo].[ivNumber_Inc_Dec]
  @I_tInc_Dec tinyint = NULL, 
  @IO_vDocNumber varchar(21) = NULL output, 
  @O_iErrorState int = NULL output

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/