web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Dynamics GP Mass Vendo...
Microsoft Dynamics GP (Archived)

Dynamics GP Mass Vendor Item Assignement

(0) ShareShare
ReportReport
Posted on by Jody Wood 405

Dynamics GP 2010

I am working on setting up new items and need to mass-assign them to multiple vendors. I have a SQL script (pasted below) that a predecessor left around, but it doesn't seem to work. I've found a couple references to a utility under the name of the subject of this post, but I can't find a download for it. I'm sure I'm not the only one facing this task...what's your approach?

Utility in development: http://mohdaoud.blogspot.com/2012_02_01_archive.html

--Vendors

 

INSERTINTO IV00103(ITEMNMBR, VENDORID, ITMVNDTY, VNDITNUM, QTYRQSTN, QTYONORD, QTY_Drop_Shipped, LSTORDDT, LSORDQTY, LRCPTQTY, LSRCPTDT, LRCPTCST, AVRGLDTM, NORCTITM, MINORQTY, MAXORDQTY, ECORDQTY, VNDITDSC, Last_Originating_Cost, Last_Currency_ID, FREEONBOARD, PRCHSUOM, CURRNIDX, PLANNINGLEADTIME, ORDERMULTIPLE, MNFCTRITMNMBR)

 

 

 

 

SELECT A.ITEMNMBR, B.VENDORID, 1, A.ITEMNMBR, 0, 0, 0,'1/1/1900', 0, 0,'1/1/1900', 0, 0, 0, 0, 0, 0, ITEMDESC, 0,'Z-US$', 1,'', 9, 0, 1,''

 

 

 

 

FROM IV00101 A, PM00200 B

 

 

 

 

WHERENOTEXISTS(SELECT ITEMNMBR, VENDORID FROM IV00103 CWHERE C.ITEMNMBR= A.ITEMNMBRAND C.VENDORID= B.VENDORID)

 

 

 

 

and itemnmbrlike('INV - SALES/USE TAX CHARGED%')

 

 

 

 

AND ITMCLSCD='USE TAX'

--Sites

 

 

 

 

INSERTINTO IV00102(ITEMNMBR, LOCNCODE, BINNMBR, RCRDTYPE, PRIMVNDR, ITMFRFLG, BGNGQTY, LSORDQTY, LRCPTQTY, LSTORDDT, LSORDVND, LSRCPTDT, QTYRQSTN, QTYONORD, QTYBKORD, QTY_Drop_Shipped, QTYINUSE, QTYINSVC, QTYRTRND, QTYDMGED, QTYONHND, ATYALLOC, QTYCOMTD, QTYSOLD, NXTCNTDT, NXTCNTTM, LSTCNTDT, LSTCNTTM, STCKCNTINTRVL, Landed_Cost_Group_ID, BUYERID, PLANNERID, ORDERPOLICY, FXDORDRQTY, ORDRPNTQTY, NMBROFDYS, MNMMORDRQTY, MXMMORDRQTY, ORDERMULTIPLE, REPLENISHMENTMETHOD, SHRINKAGEFACTOR, PRCHSNGLDTM, MNFCTRNGFXDLDTM, MNFCTRNGVRBLLDTM, STAGINGLDTME, PLNNNGTMFNCDYS, DMNDTMFNCPRDS, INCLDDINPLNNNG, CALCULATEATP, AUTOCHKATP, PLNFNLPAB, FRCSTCNSMPTNPRD, ORDRUPTOLVL, SFTYSTCKQTY, REORDERVARIANCE, PORECEIPTBIN, PORETRNBIN, SOFULFILLMENTBIN, SORETURNBIN, BOMRCPTBIN, MATERIALISSUEBIN, MORECEIPTBIN, REPAIRISSUESBIN, ReplenishmentLevel, POPOrderMethod, MasterLocationCode, POPVendorSelection, POPPricingSelection, PurchasePrice, IncludeAllocations, IncludeBackorders, IncludeRequisitions, PICKTICKETITEMOPT, INCLDMRPMOVEIN, INCLDMRPMOVEOUT, INCLDMRPCANCEL)

 

 

 

 

SELECT ITEMNMBR, B.LOCNCODE,'', 2,'', 0, 0, 0, 0,'1/1/1900','','1/1/1900', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,'1/1/1900','1/1/1900','1/1/1900','1/1/1900', 0,'','','', 1, 0, 0, 1, 0, 0, 1, 2, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 3, 0, 0, 0,'','','','','','','','', 1, 1,'', 1, 1, 0, 1, 1, 1, 3, 1, 1, 1

 

 

 

 

FROM IV00101 A, IV40700 B

 

 

 

 

WHERENOTEXISTS(SELECT ITEMNMBR, LOCNCODE FROM IV00102 CWHERE RCRDTYPE= 2AND A.ITEMNMBR= C.ITEMNMBRAND B.LOCNCODE= C.LOCNCODE)

 

 

 

 

and itemnmbrlike('INV - SALES/USE TAX CHARGED%')

 

 

 

 

AND ITMCLSCD='USE TAX'

*This post is locked for comments

I have the same question (0)
  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at

    Do you own Integration Manager?   This script, and I am not a SQL guru, looks like it is creating a record but leaving all blanks.   Integration Manager could populate easier.  And safer.   If you don't own it, consider the Import Utility.  No integrity checks, but it can be a handy tool  in situations like this.

    Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com

  • Jody Wood Profile Picture
    Jody Wood 405 on at

    Thanks Dan,

    I'll investigate that approach.  I'm pretty sure we own Integration Manager, and I've used it a couple of times when setting up new companies.  Thanks for the tip.

    -Jody

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Jody,

    Actually the tool mentioned in my blog works to assign multiple items to a single vendor not the other way, however if this is what you want, please drop a mail and I will send you the tool.

  • Jody Wood Profile Picture
    Jody Wood 405 on at

    Mohammad,

    I don't need to assign multiple items to a vendor, rather multiple vendors to one item.  And then assign that item to multiple sites.  This is very tedious to do manually.

    -Jody

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hello Jody,

    Unfortunately nothing out of the box can do this for you, I can think of the following options:

    1. Record a macro to perform this operation and schedule this task to be done periodically!

    2. Use eConnect to create an application that links items to vendors based on information saved somewhere.

    3. Try to use Dynamics GP Excel Paste (http://di.jo/gpexcelpaste.aspx), it can do the job for you.

  • Community Member Profile Picture
    Community Member on at

    Does excel paste work with GL transactions with AA dimension distribution?

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    No it is complicated.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans