IVTransferType Wrapper

This is how to call the eConnect IVTransferType from SQL. 

Related Articles

... and you 'll find more on the eConnect Menu

There are two pieces of code that we need to make this work. We first create three tables that hold the data. Integrations would populate these three tables (ignore the lot table if you don't need it), then call the stored procedure at the bottom. 

Note: The header table has a 'RowID' field, that field is the 'HeaderID' in the other two tables, this is the linking field. It is required. If you provide the IVDOCNBR we'll use it, otherwise we'll get it from Dynamics. This field is optional. 

If there are errors they will be in the errorText field in the header. 

If the transaction integrates successfully, the lines will delete; otherwise they'll remain so that you can edit and re-integrate

You don't need to populate all the fields in the three tables, leave them null if you're not going to use them

Line item sequence is only required if you use lots, it is a linking field

This is the code that we used to test:

declare @RowID int
 
insert into DDtaIVTransferHeaderInsert(BACHNUMB, DOCDATE)
    values ('TEST' ,'5/21/2020')
select @rowid = SCOPE_IDENTITY()
 
insert into DDtaIVTransferLineInsert(HeaderID,ITEMNMBR,TRXQTY,UOFM,TRXLOCTN,TRNSTLOC, LNSEQNBR)
    values (@RowID, 'myitem',1, 'EACH','001','004', 16384)
 
 
insert into DDtaIVTransferLotInsert(HeaderID, ITEMNMBR,LOTNUMBR,SERLTQTY,LOCNCODE,LNSEQNBR,TOLOCNCODE)
    VALUES (@RowID, 'myitem','N/A',1,'001',16384,'004')

Create the needed tables

if exists (select 1 from INFORMATION_SCHEMA.TABLES where table_name = 'DDtaIVTransferLotInsert' ) BEGIN
    drop table DDtaIVTransferLotInsert
end
  
GO
  
CREATE TABLE DDtaIVTransferLotInsert
(
RowID int NOT NULL IDENTITY (1, 1),
HeaderID int NOT NULL ,
IVDOCNBR char (17) ,
USERDATE datetime ,
ITEMNMBR char (30) ,
LOTNUMBR char (20) ,
SLTSQNUM int ,
SERLTQTY numeric (19,5) ,
LOCNCODE char (10) ,
EXPNDATE datetime ,
MFGDATE datetime ,
AUTOCREATELOT int ,
LNSEQNBR int ,
QTYTYPE tinyint ,
BIN char (15) ,
TOBIN char (15) ,
TOLOCNCODE char (10) ,
CreateBin tinyint ,
OverrideBin tinyint ,
RequesterTrx smallint ,
USRDEFND1 char (50) ,
USRDEFND2 char (50) ,
USRDEFND3 char (50) ,
USRDEFND4 varchar (8000) ,
USRDEFND5 varchar (8000)
)  ON [PRIMARY]
GO
ALTER TABLE DDtaIVTransferLotInsert ADD CONSTRAINT
    PK_taIVTransferLotInsert PRIMARY KEY CLUSTERED
    (
    RowID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
if exists (select 1 from INFORMATION_SCHEMA.TABLES where table_name = 'DDtaIVTransferLineInsert' ) BEGIN
    drop table DDtaIVTransferLineInsert
end
  
GO
  
CREATE TABLE DDtaIVTransferLineInsert
(
RowID int NOT NULL IDENTITY (1, 1),
HeaderID int NOT NULL ,
IVDOCNBR char (17) ,
ITEMNMBR char (30) ,
Reason_Code char (15) ,
LNSEQNBR numeric (19,5) ,
TRXQTY numeric (19,5) ,
UOFM char (8) ,
UNITCOST numeric (19,5) ,
TRXLOCTN char (10) ,
TRNSTLOC char (10) ,
TRFQTYTY smallint ,
TRTQTYTY smallint ,
IVIVINDX int ,
InventoryAccount varchar (75) ,
IVIVOFIX int ,
InventoryAccountOffSet varchar (75) ,
OverrideQty tinyint ,
RequesterTrx smallint ,
USRDEFND1 char (50) ,
USRDEFND2 char (50) ,
USRDEFND3 char (50) ,
USRDEFND4 varchar (8000) ,
USRDEFND5 varchar (8000)
)  ON [PRIMARY]
GO
ALTER TABLE DDtaIVTransferLineInsert ADD CONSTRAINT
    PK_taIVTransferLineInsert PRIMARY KEY CLUSTERED
    (
    RowID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
if exists (select 1 from INFORMATION_SCHEMA.TABLES where table_name = 'DDtaIVTransferHeaderInsert' ) BEGIN
    drop table DDtaIVTransferHeaderInsert
end
  
GO
  
CREATE TABLE DDtaIVTransferHeaderInsert
(
RowID int NOT NULL IDENTITY (1, 1),
errorText varchar(max),
BACHNUMB char (15) ,
IVDOCNBR char (17) ,
DOCDATE datetime ,
POSTTOGL tinyint ,
RequesterTrx smallint ,
USRDEFND1 char (50) ,
USRDEFND2 char (50) ,
USRDEFND3 char (50) ,
USRDEFND4 varchar (max) ,
USRDEFND5 varchar (max)
)  ON [PRIMARY]
GO
ALTER TABLE DDtaIVTransferHeaderInsert ADD CONSTRAINT
    PK_taIVTransferHeaderInsert PRIMARY KEY CLUSTERED
    (
    RowID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

 

Call the stored procedure

IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_IVInventoryTransferTypeWrapper') begin
DROP proc dd_IVInventoryTransferTypeWrapper
end
GO
 
CREATE proc dd_IVInventoryTransferTypeWrapper
--  dd_IVInventoryTransferTypeWrapper 2
 
@RowID int
 
as
set transaction isolation level read uncommitted
 
BEGIN TRY
    BEGIN TRAN
 
 
        DECLARE @RC int
        DECLARE @I_vBACHNUMB char(15)
        DECLARE @I_vIVDOCNBR char(17)
        DECLARE @I_vDOCDATE datetime
        DECLARE @I_vPOSTTOGL tinyint
        DECLARE @I_vRequesterTrx smallint
        DECLARE @I_vUSRDEFND1 char(50)
        DECLARE @I_vUSRDEFND2 char(50)
        DECLARE @I_vUSRDEFND3 char(50)
        DECLARE @I_vUSRDEFND4 char(8000)
        DECLARE @I_vUSRDEFND5 char(8000)
        DECLARE @O_iErrorState int
        DECLARE @oErrString varchar(255)
 
        DECLARE @I_vITEMNMBR char(30)
        DECLARE @I_vReason_Code char(15)
        DECLARE @I_vLNSEQNBR numeric(19,5)
        DECLARE @I_vTRXQTY numeric(19,5)
        DECLARE @I_vUOFM char(8)
        DECLARE @I_vUNITCOST numeric(19,5)
        DECLARE @I_vTRXLOCTN char(10)
        DECLARE @I_vTRNSTLOC char(10)
        DECLARE @I_vTRFQTYTY smallint
        DECLARE @I_vTRTQTYTY smallint
        DECLARE @I_vIVIVINDX int
        DECLARE @I_vInventoryAccount varchar(75)
        DECLARE @I_vIVIVOFIX int
        DECLARE @I_vInventoryAccountOffSet varchar(75)
        DECLARE @I_vOverrideQty tinyint
 
        DECLARE @I_vUSERDATE datetime
        DECLARE @I_vLOTNUMBR char(20)
        DECLARE @I_vSLTSQNUM int
        DECLARE @I_vSERLTQTY numeric(19,5)
        DECLARE @I_vLOCNCODE char(10)
        DECLARE @I_vEXPNDATE datetime
        DECLARE @I_vMFGDATE datetime
        DECLARE @I_vAUTOCREATELOT int
        DECLARE @I_vQTYTYPE tinyint
        DECLARE @I_vBIN char(15)
        DECLARE @I_vTOBIN char(15)
        DECLARE @I_vTOLOCNCODE char(10)
        DECLARE @I_vCreateBin tinyint
        DECLARE @I_vOverrideBin tinyint
 
 
        select @I_vIVDOCNBR = IVDOCNBR
            from DDtaIVTransferHeaderInsert h
            where h.rowid = @RowID
 
        if @I_vIVDOCNBR is null begin
            --1 is a Transaction, 3 is a Transfer
            exec taGetIvNumber 3, 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
 
        --===============================================================================================================================
 
        -- Lots
 
        --===============================================================================================================================
        DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
            select USERDATE,ITEMNMBR,LOTNUMBR,SLTSQNUM,SERLTQTY,LOCNCODE,EXPNDATE,MFGDATE,AUTOCREATELOT,LNSEQNBR,QTYTYPE,BIN,TOBIN,TOLOCNCODE,CreateBin,OverrideBin,RequesterTrx,USRDEFND1,USRDEFND2,USRDEFND3,USRDEFND4,USRDEFND5
                from DDtaIVTransferLotInsert lot
                where lot.HeaderID = @RowID
   
        OPEN curName
        WHILE 1=1
        BEGIN
            FETCH NEXT FROM curName INTO @I_vUSERDATE,@I_vITEMNMBR,@I_vLOTNUMBR,@I_vSLTSQNUM,@I_vSERLTQTY,@I_vLOCNCODE,@I_vEXPNDATE,@I_vMFGDATE,@I_vAUTOCREATELOT,@I_vLNSEQNBR,@I_vQTYTYPE,@I_vBIN,@I_vTOBIN,@I_vTOLOCNCODE,@I_vCreateBin,@I_vOverrideBin,@I_vRequesterTrx,@I_vUSRDEFND1,@I_vUSRDEFND2,@I_vUSRDEFND3,@I_vUSRDEFND4,@I_vUSRDEFND5
            if @@fetch_status <> 0 begin
                break
            end
   
            --comment/uncomment the fields that you intend to use
            EXECUTE @RC = [dbo].[taIVTransferLotInsert]
               @I_vIVDOCNBR = @I_vIVDOCNBR
              --,@I_vUSERDATE = @I_vUSERDATE
              ,@I_vITEMNMBR = @I_vITEMNMBR
              ,@I_vLOTNUMBR = @I_vLOTNUMBR
              --,@I_vSLTSQNUM
              ,@I_vSERLTQTY = @I_vSERLTQTY
              ,@I_vLOCNCODE = @I_vLOCNCODE
              --,@I_vEXPNDATE
              --,@I_vMFGDATE
              --,@I_vAUTOCREATELOT
              --,@I_vLNSEQNBR
              --,@I_vQTYTYPE
              --,@I_vBIN
              --,@I_vTOBIN
              ,@I_vTOLOCNCODE = @I_vTOLOCNCODE
              --,@I_vCreateBin
              --,@I_vOverrideBin
              --,@I_vRequesterTrx
              --,@I_vUSRDEFND1
              --,@I_vUSRDEFND2
              --,@I_vUSRDEFND3
              --,@I_vUSRDEFND4
              --,@I_vUSRDEFND5
              ,@O_iErrorState  = @O_iErrorState OUTPUT
              ,@oErrString  = @oErrString 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
        CLOSE curName
        DEALLOCATE curName                     
 
        --===============================================================================================================================
 
        -- Lines
 
        --===============================================================================================================================
        DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
            select ITEMNMBR,Reason_Code,LNSEQNBR,TRXQTY,UOFM,UNITCOST,TRXLOCTN,TRNSTLOC,TRFQTYTY,TRTQTYTY,IVIVINDX,InventoryAccount,IVIVOFIX,InventoryAccountOffSet,OverrideQty,RequesterTrx,USRDEFND1,USRDEFND2,USRDEFND3,USRDEFND4,USRDEFND5
                from DDtaIVTransferLineInsert l
                where l.HeaderID = @RowID
 
        OPEN curName
        WHILE 1=1
        BEGIN
            FETCH NEXT FROM curName INTO @I_vITEMNMBR,@I_vReason_Code,@I_vLNSEQNBR,@I_vTRXQTY,@I_vUOFM,@I_vUNITCOST,@I_vTRXLOCTN,@I_vTRNSTLOC,@I_vTRFQTYTY,@I_vTRTQTYTY,@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
 
 
 
            --comment/uncomment the fields that you intend to use
            EXECUTE @RC = [dbo].[taIVTransferLineInsert]
                @I_vIVDOCNBR              = @I_vIVDOCNBR 
                ,@I_vITEMNMBR                = @I_vITEMNMBR
                --,@I_vReason_Code           = @I_vReason_Code
                ,@I_vLNSEQNBR                = @I_vLNSEQNBR
                ,@I_vTRXQTY              = @I_vTRXQTY
                ,@I_vUOFM                    = @I_vUOFM
                --,@I_vUNITCOST              = @I_vUNITCOST
                ,@I_vTRXLOCTN                = @I_vTRXLOCTN
                ,@I_vTRNSTLOC                = @I_vTRNSTLOC
                --,@I_vTRFQTYTY              = @I_vTRFQTYTY
                --,@I_vTRTQTYTY              = @I_vTRTQTYTY
                --,@I_vIVIVINDX              = @I_vIVIVINDX
                --,@I_vInventoryAccount      = @I_vInventoryAccount
                --,@I_vIVIVOFIX              = @I_vIVIVOFIX
                --,@I_vInventoryAccountOffSet= @I_vInventoryAccountOffSet
                --,@I_vOverrideQty           = @I_vOverrideQty
                --,@I_vRequesterTrx          = @I_vRequesterTrx
                --,@I_vUSRDEFND1             = @I_vUSRDEFND1
                --,@I_vUSRDEFND2             = @I_vUSRDEFND2
                --,@I_vUSRDEFND3             = @I_vUSRDEFND3
                --,@I_vUSRDEFND4             = @I_vUSRDEFND4
                --,@I_vUSRDEFND5             = @I_vUSRDEFND5
                ,@O_iErrorState = @O_iErrorState OUTPUT
                ,@oErrString = @oErrString 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
        CLOSE curName
        DEALLOCATE curName       
         
        select
                @I_vBACHNUMB = BACHNUMB,
                @I_vDOCDATE = DOCDATE,
                @I_vPOSTTOGL = POSTTOGL,
                @I_vRequesterTrx = RequesterTrx,
                @I_vUSRDEFND1 = USRDEFND1,
                @I_vUSRDEFND2 = USRDEFND2,
                @I_vUSRDEFND3 = USRDEFND3,
                @I_vUSRDEFND4 = USRDEFND4,
                @I_vUSRDEFND5 = USRDEFND5
            from DDtaIVTransferHeaderInsert h
            where h.rowid = @RowID
                             
 
        --comment/uncomment the fields that you intend to use
        EXECUTE @RC = taIVTransferHeaderInsert
            @I_vBACHNUMB        = @I_vBACHNUMB
            ,@I_vIVDOCNBR       = @I_vIVDOCNBR
            ,@I_vDOCDATE        = @I_vDOCDATE
            --,@I_vPOSTTOGL     = @I_vPOSTTOGL
            --,@I_vRequesterTrx = @I_vRequesterTrx
            --,@I_vUSRDEFND1    = @I_vUSRDEFND1
            --,@I_vUSRDEFND2    = @I_vUSRDEFND2
            --,@I_vUSRDEFND3    = @I_vUSRDEFND3
            --,@I_vUSRDEFND4    = @I_vUSRDEFND4
            --,@I_vUSRDEFND5    = @I_vUSRDEFND5
            ,@O_iErrorState = @O_iErrorState OUTPUT
            ,@oErrString = @oErrString OUTPUT
 
        if @O_iErrorState > 0 begin
            select @oErrString = ec.ErrorDesc
                from dynamics..taErrorCode ec
                where ec.ErrorCode = @O_iErrorState
            raiserror(@oErrString, 16,2)
        end
 
         -- delete tables
         delete DDtaIVTransferHeaderInsert where rowid = @RowID
         delete DDtaIVTransferLineInsert where headerid = @rowid
         delete DDtaIVTransferLotInsert where headerid = @RowID
 
    COMMIT TRAN
END TRY
BEGIN CATCH
    --roll everything back
    ROLLBACK TRAN
 
    update DDtaIVTransferHeaderInsert set errorText = ERROR_MESSAGE() where rowid = @rowid
 
    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_IVInventoryTransferTypeWrapper TO PUBLIC

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences