Wednesday, July 30, 2008

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
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


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:

@I_tInc_Dec tinyint = NULL,
@IO_vDocNumber varchar(21) = NULL output,
@O_iErrorState int = NULL output

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC


Anonymous said...

Nice post.

An example would be nice.

Mariano Gomez said...

Hi there,

Thanks for the feedback! I am not sure what type of sample you would like to see. The SQL code I provided is in itself a sample. It retrieves the next PO receipt number by calling the dbo.ivNumber_Inc_Dec provided by Microsoft under your company database.

You can decide to call your stored procedure from any project or development language you are comfortable with.

If I missed something please feel free to post with specifics on the sample you would like to see implemented around the dbo.ivNumber_Inc_Dec.

Best regards,

Mariano Gomez