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 |
Dynamics GP >