Looking for a GP Dynamics eConnect method to create Item Warehouse

I am looking for an eConnect method that will add an Item Warehouse record,  I am currently doing this using GP Web Services call CreateItemWarehouse(), but we find this to be much slower than using eConnect.  Any help would be appreciated.

Thanks, Bob
bob_fellerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

Is taCreateInventorySite what you are looking for?

https://msdn.microsoft.com/en-us/library/ff623325.aspx?f=255&MSPPError=-2147217396


Thanks,

Steve Endow
Microsoft MVP - Dynamics GP
bob_fellerAuthor Commented:
That's not quite it. I am looking to add an item to each warehouse location. I found the following article excerpt saying what I want can't be done with eConnect, it must be done with SQL. I'm reluctant to use SQL stored procedures as I believe SQL updates isn't really supported.

article ==>

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?
[/b][/b]
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

Thanks, Bob
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi Bob,

Thanks for the clarification.  The eConnect taItemSite node should allow you to assign an Item to a warehouse/location code/site.

https://msdn.microsoft.com/en-us/library/jj193355.aspx

I don't believe there is an option in eConnect to assign an item to all sites--you would need to write the code to generate all of the item + site assignments, similar to how the stored procedure you provided is looping through the items and calling the taItemSite procedure.

If you are using eConnect, it looks like the taItemSite_Items object is an array, so you should be able to send in multiple item/site combinations in one eConnect submission per item.

Calling stored procs to perform the inserts isn't a supported approach, but as long as you are calling the underlying eConnect stored procedures to perform the inserts, it's generally safe, as the eConnect proc handles validation.  If you are already working with .NET to call eConnect, I would stick with that approach.  But if you are running into a limitation where a stored procedure call would be much simpler or more efficient, then you can consider calling the taItemSite procedure directly.

Let me know if that helps.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bob_fellerAuthor Commented:
Thanks for the help, That did the trick.  

Thanks, Bob
bob_fellerAuthor Commented:
Thanks for the quick reponses. I now have my code working.  Much appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.