Creating 3rd party order entry system

[Borvik]

Hello,

I'm working on an order entry system that does not require GP. This will be great, as we won't have to take up a precious seat just for an order entry person and someone else can use that seat.

I'm building off of my previous work on creating a web entry system (thread632-1195905). That web entry system had several problems that prevented its use - though upon revisiting the problems, I think I now know where the problems occurred (though it took several years).

(sorry I won't be posting any more code - though if what I find here fixes those other problems, I'm sure someone could fairly easily incorporate those fixes into the code provided in the earlier thread)

Before I go on with it though I'm hoping someone can give me some input on the procedure I _THINK_ I'm seeing. First though the errors that must be solved: 1 - duplicate master numbers (1 automated, 1 manual through GP), 2 - duplicate order numbers (1 automated, 1 historical). Both of these problems are explained in more detail in the aforementioned thread. From what I can tell - both of these problem might be solved within the first 20 lines of analyzed SQL statements which I will post here for reference.

EXEC TEST.dbo.zDP_SOP40200SS_1 2, 'STDORD' 
go
BEGIN DECLARE @stored_proc_name char(27) DECLARE @retstat int DECLARE @param1 int DECLARE @param2 int set nocount on SELECT @param1 = 0 SELECT @param2 = 0 SELECT @stored_proc_name = 'TEST.dbo.sopGetMasterNumber' EXEC @retstat = @stored_proc_name @param1 OUT, @param2 OUT SELECT @retstat, @param1, @param2 set nocount on END 
go
EXEC TEST.dbo.zDP_SOP40100SS_1 1 
go
BEGIN DECLARE @num int EXEC TEST.dbo.zDP_SOP40500SI 67025, @num OUT SELECT @num END 
go
insert into tempdb.dbo.DEX_LOCK values (619,727145,'TEST.dbo.SOP40500')
go
BEGIN DECLARE @stored_proc_name char(23) DECLARE @retstat int DECLARE @param4 char(21) DECLARE @param5 int set nocount on SELECT @param4 = '' SELECT @param5 = 0 SELECT @stored_proc_name = 'TEST.dbo.sopGetIDNumber' EXEC @retstat = @stored_proc_name 2, 'STDORD', 1, @param4 OUT, @param5 OUT SELECT @retstat, @param4, @param5 set nocount on END 
go
EXEC TEST.dbo.zDP_SOP30200SS_1 '0000079345', 2 
go
EXEC TEST.dbo.zDP_TX30000F_1 '0000079345',2,1,0.00000,-2147483648,'0000079345',2,1,0.00000,2147483647
go
BEGIN DECLARE @num int EXEC TEST.dbo.zDP_SOP10100SI 2, '0000079345', 0, '', 'STDORD', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', '1900.01.01', 0, 0, 0, 0, 0, '1900.01.01', '', '', '', '', 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0.00000, 0.00000, 0.00000, 0.00000, '', '', '', '', '', '', '', '', 0, 67025, '', '', 0.00000, 0.00000, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, '', 0.00000, 0.00000, 0, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0, '', '', '', '', 0, 0, '', 0.00000, 0.00000, 0, '', 0.00000, 0.00000, 0, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, '', '', '', 0, 0, 0, 0, 0.00000, '', 0, '', '', 0.0000000, 0.0000000, '1900.01.01', '00:00:00', 0, 0, '', 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, '', '', '1900.01.01', '', '', '1900.01.01', '1900.01.01', '1900.01.01', 0, 0.00000, 0, 0, 0, 0, '', 0, '1900.01.01', 0x00000000, 0, 0, 0.00000, 0.00000, '', 0, '', @num OUT SELECT @num END 
go
insert into tempdb.dbo.DEX_LOCK values (619,886733,'TEST.dbo.SOP10100')

In my first attempt I was not entering the master number into SOP40500. Which most likely error number 1 - the duplicate master numbers.

I believe the second error occurred because I omitted the check on line 13 ("EXEC TEST.dbo.zDP_SOP30200SS_1 '0000079345', 2").

From what I can tell - this is checking for the generated order number in history (which I never did in my previous attempt). From my example data though I can't tell what it might do if it finds a record in SOP30200 - generate a different order number, throw an error, etc...

I'm looking for any thoughts on these ideas - anything at all.

So any ideas?