Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Wednesday, December 2, 2015

Mass Assign Items to Vendors - SQL Script

The item-vendor assignment in Dynamics GP is a validation that shall not be bypassed by assigning all-to-all. The system does support assigning one item to one vendor while assigning a vendor item number which can be essential for reporting purposes. Although, it might be important "for testing purposes specifically" to have all items assigned to all vendors. In this essence, the setup would become a difficult task even through Macro.

 In this essence, I am providing an SQL script which assign all items to all vendors (for testing purposes only). Remember, it is not a recommendation to run the script on your Live environment.

The script can be downloaded from >>  Download Link


/*--------------------------------------------------------------------------------
Creation Date: 2, December, 2015
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to "Mass" assign all items to all vendors. It can be
used to assign a specific range of vendors to a specific range of items

The script has been tested on a limited sample data.

Revision History:
Revision No.            Revision Date    Description
1                       2/12/2015        Original Version
--------------------------------------------------------------------------------*/
INSERT  INTO dbo.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 ,
                2 ,
                A.ITEMNMBR ,
                0.00000 ,
                0.00000 ,
                0.00000 ,
                '1900.01.01' ,
                0.00000 ,
                0.00000 ,
                '1900.01.01' ,
                0.00000 ,
                0 ,
                0 ,
                0.00000 ,
                0.00000 ,
                0.00000 ,
                A.ITEMNMBR ,
                A.CURRCOST ,
                B.CURNCYID ,
                1 ,
                '' ,
                C.CURRNIDX ,
                0 ,
                1.00000 ,
                ''
        FROM    dbo.IV00101 AS A
                CROSS JOIN dbo.PM00200 AS B
                LEFT OUTER JOIN dbo.IV00105 AS C ON A.ITEMNMBR = C.ITEMNMBR
                                                    AND B.CURNCYID = C.CURNCYID
        WHERE   CONCAT(RTRIM(LTRIM(A.ITEMNMBR)), RTRIM(LTRIM(B.VENDORID))) NOT IN (
                SELECT  CONCAT(RTRIM(LTRIM(ITEMNMBR)), RTRIM(LTRIM(VENDORID)))
                FROM    dbo.IV00103 )


 
 
Best Regards, 
Mahmoud M. AlSaadi
Posted by at
Labels: , ,

7 comments:

  1. UnknownJuly 24, 2017 at 8:21 AM

    Mahmood,
    What would be the syntax to only update all items with one Vendor ID?
    Thanks!!

    ReplyDelete
    Replies
    1. Mahmoud M. AlSaadiJuly 25, 2017 at 1:57 AM

      INSERT INTO dbo.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 ,
      2 ,
      A.ITEMNMBR ,
      0.00000 ,
      0.00000 ,
      0.00000 ,
      '1900.01.01' ,
      0.00000 ,
      0.00000 ,
      '1900.01.01' ,
      0.00000 ,
      0 ,
      0 ,
      0.00000 ,
      0.00000 ,
      0.00000 ,
      A.ITEMNMBR ,
      A.CURRCOST ,
      B.CURNCYID ,
      1 ,
      '' ,
      C.CURRNIDX ,
      0 ,
      1.00000 ,
      ''
      FROM dbo.IV00101 AS A
      CROSS JOIN dbo.PM00200 AS B
      LEFT OUTER JOIN dbo.IV00105 AS C ON A.ITEMNMBR = C.ITEMNMBR
      AND B.CURNCYID = C.CURNCYID
      WHERE CONCAT(RTRIM(LTRIM(A.ITEMNMBR)), RTRIM(LTRIM(B.VENDORID))) NOT IN (
      SELECT CONCAT(RTRIM(LTRIM(ITEMNMBR)), RTRIM(LTRIM(VENDORID)))
      FROM dbo.IV00103 )
      AND B.VENDORID = 'XXXXXX'

      Delete
  2. UnknownAugust 24, 2017 at 6:37 AM

    I'm curious why you used the NOT IN clause when NOT EXISTs seems much faster.

    INSERT INTO dbo.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 ,
    2 ITMVNDTY,
    A.ITEMNMBR VNDITNUM,
    0.00000 QTYRQSTN,
    0.00000 QTYONORD,
    0.00000 QTY_Drop_Shipped,
    '1900.01.01' LSTORDDT,
    0.00000 LSORDQTY,
    0.00000 LRCPTQTY,
    '1900.01.01' LSRCPTDT,
    0.00000 LRCPTCST,
    0 AVRGLDTM,
    0 NORCTITM,
    0.00000 MINORQTY,
    0.00000 MAXORDQTY,
    0.00000 ECORDQTY,
    A.ITEMNMBR VNDITDSC,
    A.CURRCOST Last_Originating_Cost,
    B.CURNCYID Last_Currency_ID,
    1 FREEONBOARD,
    '' PRCHSUOM,
    ISNULL(C.CURRNIDX, 1007) CURRNIDX,
    0 PLANNINGLEADTIME,
    1.00000 ORDERMULTIPLE,
    '' MNFCTRITMNMBR
    FROM dbo.IV00101 AS A
    CROSS JOIN dbo.PM00200 AS B
    LEFT OUTER JOIN dbo.IV00105 AS C ON A.ITEMNMBR = C.ITEMNMBR
    AND B.CURNCYID = C.CURNCYID
    WHERE
    NOT EXISTS (SELECT * FROM dbo.IV00103 WHERE ITEMNMBR = a.ITEMNMBR AND VENDORID = b.VENDORID )
    ORDER BY ITEMNMBR, VENDORID

    ReplyDelete
  3. Jon MesserSeptember 19, 2017 at 2:49 PM

    Hi Mahmood,

    could you please possibly post a version of this that would run in SQL 2008 and only insert items with itemnmbr like 'xxxx%' and vendorid = 'xxxx'? that would be great! i could probably eventually figure out how to do it, but i know next to nothing about SQL. I have been learning a bit here and there, but im just a regular IT guy, not a DBA or GP person. thank you!

    ReplyDelete
    Replies
    1. Jon MesserSeptember 22, 2017 at 7:39 AM

      Got it! I used the script in the reply above and added a couple of AND statements at the end to define the itemnumbr and vendorID data i needed. thank you for the original script. this was a big help for me.

      Delete
  4. AnonymousMarch 21, 2018 at 8:29 AM

    Is there a version of this script that could be used as a trigger to create the Item Vendor record as you are selecting the item in the PO line - not sure if that is possible? Our environment would create 360 million item vendor records if we assigned them all up front. We would also still need to add these as new vendors are created if we mass assign them up front. THanks!

    ReplyDelete
  5. micheal panJune 21, 2021 at 6:21 PM

    BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete

Subscribe to: Post Comments (Atom)