Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

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

No comments:

Post a Comment