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