First, we need the code to create the staging tables. You might not want that part, but it suits our needs. We integrate into the staging tables, then run the eConnect, sometimes in a separate process.
CREATE TABLE [dbo].[DDtaIVTransactionHeaderInsert](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[ErrorText] [varchar](max) NOT NULL,
[BACHNUMB] [char](15) NULL,
[IVDOCNBR] [char](17) NULL,
[IVDOCTYP] [smallint] NULL,
[DOCDATE] [datetime] NULL,
[RequesterTrx] [smallint] NULL,
[POSTTOGL] [smallint] NULL,
[MDFUSRID] [char](15) NULL,
[PTDUSRID] [char](15) NULL,
[POSTEDDT] [datetime] NULL,
[NOTETEXT] [varchar](8000) NULL,
[USRDEFND1] [char](50) NULL,
[USRDEFND2] [char](50) NULL,
[USRDEFND3] [char](50) NULL,
[USRDEFND4] [varchar](8000) NULL,
[USRDEFND5] [varchar](8000) NULL,
CONSTRAINT [PK_taIVTransactionHeaderInsert] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[DDtaIVTransactionLineInsert](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[HeaderID] [int] NOT NULL,
[IVDOCNBR] [char](17) NULL,
[IVDOCTYP] [smallint] NULL,
[ITEMNMBR] [char](30) NULL,
[Reason_Code] [char](15) NULL,
[LNSEQNBR] [numeric](19, 5) NULL,
[UOFM] [char](8) NULL,
[TRXQTY] [numeric](19, 5) NULL,
[UNITCOST] [numeric](19, 5) NULL,
[TRXLOCTN] [char](10) NULL,
[IVIVINDX] [int] NULL,
[InventoryAccount] [varchar](75) NULL,
[IVIVOFIX] [int] NULL,
[InventoryAccountOffSet] [varchar](75) NULL,
[OverrideQty] [tinyint] NULL,
[RequesterTrx] [smallint] NULL,
[USRDEFND1] [char](50) NULL,
[USRDEFND2] [char](50) NULL,
[USRDEFND3] [char](50) NULL,
[USRDEFND4] [varchar](8000) NULL,
[USRDEFND5] [varchar](8000) NULL,
CONSTRAINT [PK_taIVTransactionLineInsert] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DDtaIVTransactionLotInsert](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[HeaderID] [int] NOT NULL,
[IVDOCNBR] [char](17) NULL,
[IVDOCTYP] [smallint] NULL,
[USERDATE] [datetime] NULL,
[ITEMNMBR] [char](30) NULL,
[LOTNUMBR] [char](20) NULL,
[SERLTQTY] [numeric](19, 5) NULL,
[ADJTYPE] [smallint] NULL,
[BIN] [char](15) NULL,
[CreateBin] [tinyint] NULL,
[LNSEQNBR] [int] NULL,
[LOCNCODE] [char](10) NULL,
[EXPNDATE] [datetime] NULL,
[MFGDATE] [datetime] NULL,
[AUTOCREATELOT] [int] NULL,
[DATERECD] [datetime] NULL,
[RequesterTrx] [smallint] NULL,
[USRDEFND1] [char](50) NULL,
[USRDEFND2] [char](50) NULL,
[USRDEFND3] [char](50) NULL,
[USRDEFND4] [varchar](8000) NULL,
[USRDEFND5] [varchar](8000) NULL,
CONSTRAINT [PK_taIVTransactionLotInsert] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Then, call this proc, which wraps the eConnect stored procs.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dd_taItemTransactionTypeWrapper'
AND type = 'P')
DROP PROCEDURE dd_taItemTransactionTypeWrapper
GO
CREATE PROCEDURE dd_taItemTransactionTypeWrapper
-- dd_taItemTransactionTypeWrapper 5
@HeaderID int
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @RC int
DECLARE @I_vIVDOCNBR char(17)
DECLARE @I_vIVDOCTYP smallint
DECLARE @I_vITEMNMBR char(30)
DECLARE @I_vReason_Code char(15)
DECLARE @I_vLNSEQNBR numeric(19,5)
DECLARE @I_vUOFM char(8)
DECLARE @I_vTRXQTY numeric(19,5)
DECLARE @I_vUNITCOST numeric(19,5)
DECLARE @I_vTRXLOCTN char(10)
DECLARE @I_vIVIVINDX int
DECLARE @I_vInventoryAccount varchar(75)
DECLARE @I_vIVIVOFIX int
DECLARE @I_vInventoryAccountOffSet varchar(75)
DECLARE @I_vOverrideQty tinyint
DECLARE @I_vRequesterTrx smallint
DECLARE @I_vUSRDEFND1 char(50)
DECLARE @I_vUSRDEFND2 char(50)
DECLARE @I_vUSRDEFND3 char(50)
DECLARE @I_vUSRDEFND4 varchar(8000)
DECLARE @I_vUSRDEFND5 varchar(8000)
DECLARE @O_iErrorState int
DECLARE @oErrString varchar(255)
DECLARE @I_vBACHNUMB char(15)
DECLARE @I_vDOCDATE datetime
DECLARE @I_vPOSTTOGL smallint
DECLARE @I_vMDFUSRID char(15)
DECLARE @I_vPTDUSRID char(15)
DECLARE @I_vPOSTEDDT datetime
DECLARE @I_vNOTETEXT varchar(8000)
DECLARE @I_vUSERDATE datetime
DECLARE @I_vLOTNUMBR char(20)
DECLARE @I_vSERLTQTY numeric(19,5)
DECLARE @I_vADJTYPE smallint
DECLARE @I_vBIN char(15)
DECLARE @I_vCreateBin tinyint
DECLARE @I_vLOCNCODE char(10)
DECLARE @I_vEXPNDATE datetime
DECLARE @I_vMFGDATE datetime
DECLARE @I_vAUTOCREATELOT int
DECLARE @I_vDATERECD datetime
DECLARE @I_vAUTOCREATESERIAL int
DECLARE @I_vSERLNMBR char(20)
--note:
--the header table has a rowID, and the lines and lot table link to it via the HeaderID field.
--this script will grab one transaction (header, lines, serial) and eConnect it into GP
BEGIN TRY
BEGIN TRAN
--normally, we leave the doc number field null, this code will get the next one in sequence. Better than putting it in the staging table and run the
--risk of someone else using it
select @I_vIVDOCNBR = IVDOCNBR
from DDtaIVTransferHeaderInsert h
where h.rowid = @HeaderID
if @I_vIVDOCNBR is null begin
--1 is a Transaction, 3 is a Transfer
exec taGetIvNumber 1, 1,@I_vIVDOCNBR OUTPUT ,@O_iErrorState OUTPUT
if @O_iErrorState > 0 begin
select @oErrString = ec.ErrorDesc
from dynamics..taErrorCode ec
where ec.ErrorCode = @O_iErrorState
raiserror(@oErrString, 16,2)
end
end
--query the data for the serial
--we could have supplied default values in the table, but using IIF seemed clearer for maintenance later
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
select
lot.IVDOCTYP,
lot.ITEMNMBR,
lot.serlnmbr,
iif(lot.ADJTYPE is null,0,lot.adjtype),
iif(lot.BIN is null,'',lot.bin),
iif(lot.CreateBin is null,0,lot.CreateBin),
lot.LNSEQNBR,
lot.LOCNCODE,
lot.locncode,
iif(lot.AUTOCREATESERIAL is null,0,lot.AUTOCREATESERIAL),
iif(lot.RequesterTrx is null,0,lot.RequesterTrx),
iif(lot.USRDEFND1 is null,'',lot.usrdefnd1),
iif(lot.USRDEFND2 is null,'',lot.USRDEFND2),
iif(lot.USRDEFND3 is null,'',lot.USRDEFND3),
iif(lot.USRDEFND4 is null,'',lot.USRDEFND4),
iif(lot.USRDEFND5 is null,'',lot.USRDEFND5)
from DDtaIVTransactionSerialInsert lot
where headerid = @headerid
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @I_vIVDOCTYP,@I_vITEMNMBR,@I_vSERLNMBR,@I_vADJTYPE,@I_vBIN,@I_vCreateBin,@I_vLNSEQNBR,@I_vLOCNCODE,@I_vTRXLOCTN,@I_vAUTOCREATESERIAL ,@I_vRequesterTrx ,@I_vUSRDEFND1 ,@I_vUSRDEFND2 ,@I_vUSRDEFND3 ,@I_vUSRDEFND4 ,@I_vUSRDEFND5
if @@fetch_status <> 0 begin
break
end
--insert the serial
EXECUTE @RC = [dbo].[taIVTransactionSerialInsert]
@I_vIVDOCNBR
,@I_vIVDOCTYP
,@I_vITEMNMBR
,@I_vSERLNMBR
,@I_vADJTYPE
,@I_vBIN
,@I_vCreateBin
,@I_vLNSEQNBR
,@I_vLOCNCODE
,@I_vAUTOCREATESERIAL
,@I_vRequesterTrx
,@I_vUSRDEFND1
,@I_vUSRDEFND2
,@I_vUSRDEFND3
,@I_vUSRDEFND4
,@I_vUSRDEFND5
,@O_iErrorState OUTPUT
,@oErrString OUTPUT
if @O_iErrorState > 0 begin
print 'lot error'
select @oErrString = convert(varchar(20),@O_iErrorState) + '/' + ec.ErrorDesc
from dynamics..taErrorCode ec
where ec.ErrorCode = @O_iErrorState
raiserror(@oErrString, 16,2)
end
END
CLOSE curName
DEALLOCATE curName
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
select
l.IVDOCTYP ,
l.ITEMNMBR ,
IIF(l.Reason_Code is null,'', l.reason_code) ,
l.LNSEQNBR ,
l.UOFM ,
l.TRXQTY ,
l.UNITCOST ,
l.TRXLOCTN ,
iif(l.IVIVINDX is null,0,l.IVIVINDX) ,
iif(l.InventoryAccount is null,'',l.InventoryAccount) ,
iif(l.IVIVOFIX is null,0,l.IVIVOFIX) ,
iif(l.InventoryAccountOffSet is null,'',l.InventoryAccountOffSet),
iif(l.OverrideQty is null,1,l.OverrideQty) ,
iif(l.RequesterTrx is null,0,l.RequesterTrx),
iif(l.USRDEFND1 is null,'',l.USRDEFND1) ,
iif(l.USRDEFND2 is null,'',l.USRDEFND2) ,
iif(l.USRDEFND3 is null,'',l.USRDEFND3) ,
iif(l.USRDEFND4 is null,'',l.USRDEFND4) ,
iif(l.USRDEFND1 is null,'',l.USRDEFND5)
from DDtaIVTransactionLineInsert l
where headerid = @HeaderID
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @I_vIVDOCTYP,@I_vITEMNMBR,@I_vReason_Code,@I_vLNSEQNBR,@I_vUOFM,@I_vTRXQTY,@I_vUNITCOST,@I_vTRXLOCTN,@I_vIVIVINDX,@I_vInventoryAccount,@I_vIVIVOFIX,@I_vInventoryAccountOffSet,@I_vOverrideQty,@I_vRequesterTrx,@I_vUSRDEFND1,@I_vUSRDEFND2,@I_vUSRDEFND3,@I_vUSRDEFND4,@I_vUSRDEFND5
if @@fetch_status <> 0 begin
break
end
--insert the lines
EXECUTE @RC = [dbo].[taIVTransactionLineInsert]
@I_vIVDOCNBR
,@I_vIVDOCTYP
,@I_vITEMNMBR
,@I_vReason_Code
,@I_vLNSEQNBR
,@I_vUOFM
,@I_vTRXQTY
,@I_vUNITCOST
,@I_vTRXLOCTN
,@I_vIVIVINDX
,@I_vInventoryAccount
,@I_vIVIVOFIX
,@I_vInventoryAccountOffSet
,@I_vOverrideQty
,@I_vRequesterTrx
,@I_vUSRDEFND1
,@I_vUSRDEFND2
,@I_vUSRDEFND3
,@I_vUSRDEFND4
,@I_vUSRDEFND5
,@O_iErrorState OUTPUT
,@oErrString OUTPUT
if @O_iErrorState > 0 begin
print 'taIVTransactionLineInsert error'
select @oErrString = convert(varchar(20),@O_iErrorState) + '/' + ec.ErrorDesc
from dynamics..taErrorCode ec
where ec.ErrorCode = @O_iErrorState
raiserror(@oErrString, 16,2)
end
END
CLOSE curName
DEALLOCATE curName
select
@I_vBACHNUMB = h.BACHNUMB
,@I_vIVDOCTYP = h.IVDOCTYP
,@I_vDOCDATE = h.DOCDATE
,@I_vRequesterTrx= iif(h.RequesterTrx is null,0,h.RequesterTrx)
,@I_vPOSTTOGL = iif(h.POSTTOGL is null,1,h.posttogl)
,@I_vMDFUSRID = iif(h.MDFUSRID is null,'IMPORT',h.MDFUSRID)
,@I_vPTDUSRID = iif(h.PTDUSRID is null,'IMPORT',h.PTDUSRID)
,@I_vPOSTEDDT = iif(h.POSTEDDT is null,convert(date,getdate()),h.POSTEDDT)
,@I_vNOTETEXT = iif(h.NOTETEXT is null,'',h.NOTETEXT)
,@I_vUSRDEFND1 = iif(h.USRDEFND1 is null,'',h.USRDEFND1)
,@I_vUSRDEFND2 = iif(h.USRDEFND2 is null,'',h.USRDEFND2)
,@I_vUSRDEFND3 = iif(h.USRDEFND3 is null,'',h.USRDEFND3)
,@I_vUSRDEFND4 = iif(h.USRDEFND4 is null,'',h.USRDEFND4)
,@I_vUSRDEFND5 = iif(h.USRDEFND5 is null,'',h.USRDEFND5)
from DDtaIVTransactionHeaderInsert h
where rowid = @HeaderID
EXECUTE @RC = [dbo].[taIVTransactionHeaderInsert]
@I_vBACHNUMB
,@I_vIVDOCNBR
,@I_vIVDOCTYP
,@I_vDOCDATE
,@I_vRequesterTrx
,@I_vPOSTTOGL
,@I_vMDFUSRID
,@I_vPTDUSRID
,@I_vPOSTEDDT
,@I_vNOTETEXT
,@I_vUSRDEFND1
,@I_vUSRDEFND2
,@I_vUSRDEFND3
,@I_vUSRDEFND4
,@I_vUSRDEFND5
,@O_iErrorState OUTPUT
,@oErrString OUTPUT
if @O_iErrorState > 0 begin
select @oErrString = 'header' + convert(varchar(20),@O_iErrorState) + '/' + ec.ErrorDesc
from dynamics..taErrorCode ec
where ec.ErrorCode = @O_iErrorState
raiserror(@oErrString, 16,2)
end
--if all went well, delete the source data
delete DDtaIVTransactionHeaderInsert where rowid = @HeaderID
delete DDtaIVTransactionLineInsert where headerid = @headerid
delete DDtaIVTransactionSerialInsert where headerid = @headerid
COMMIT TRAN
END TRY
BEGIN CATCH
--roll everything back
ROLLBACK TRAN
update DDtaIVTransferHeaderInsert set errorText = ERROR_MESSAGE() where rowid = @HeaderID
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
grant exec on dd_taItemTransactionTypeWrapper to public