Assign 1 inventory item to every inventory site using eConnect

OVERVIEW:

Microsoft Dynamics GP doesn't give you the ability to assign 1 inventory item to every site. But you can do it using a SQL Server stored procedure if you have installed the eConnect stored procedures. See What is eConnect?

CODE:

CREATE PROCEDURE [dbo].[spAssignItemToAllSites]

(@item nvarchar(20))

AS

BEGIN -- Stored Proc

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @itemnmbr char(30)

DECLARE @Locncode nvarchar(15)

-- Error Handling Variables

DECLARE @O_iErrorState int

DECLARE @oErrString varchar(255)

DECLARE @ErrorState int

DECLARE @ErrString varchar(1000)

-- Open A cursor

DECLARE Item Cursor FOR

SELECT @item, locncode from iv40700

OPEN Item

-- populate the cursor with the first record from the header

Fetch NEXT FROM Item

INTO

@itemnmbr, @locncode

While (@@FETCH_STATUS=0)

BEGIN -- While Loop

-- Set Default starting conditions

SET @ErrString = ''

SET @ErrorState = 0

SET @O_iErrorState = 0

SET @oErrstring = ''

--Begin a sql transaction so we can undo everything if it doesn't work

BEGIN TRANSACTION

if @O_iErrorState = 0

begin --Execute econnect stored proc

EXECUTE taItemSite

@I_vItemnmbr = @itemnmbr,

@I_VLocncode = @locncode,

@O_iErrorState = @O_iErrorstate OUTPUT,

@oErrString = @oErrString OUTPUT

end

IF @O_iErrorState <> 0

Begin

SET @ErrorState = @O_iErrorState

SET @ErrString = @oErrstring

End

--Processing is done. Let's begin the error handling.

if @ErrorState <> 0

begin

ROLLBACK TRANSACTION

--note: this is option. If an error occurs, the error message will be inserted into a custom

--SQL table. Obviously, you must create this table yourself.

Insert into EconnectErrorLog (Item, ErrorState, ErrString)

values (@itemnmbr, @errorstate, @errstring)

end

ELSE

COMMIT TRANSACTION

--Code is done - loop to the next record in the cursor

Fetch NEXT FROM Item

INTO

@itemnmbr, @locncode

END -- While Loop

CLOSE Item

DEALLOCATE Item

END -- Stored Proc