Dynamics GP‎ > ‎

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
Comments