Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

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

5 comments:

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

    ReplyDelete
    Replies
    1. 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. 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. 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. 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