SQL Script to Assign SPECIFIC Items to a given Vendor in Microsoft GP

--------------------------------------------------------  
-- Assign SPECIFIC Items to a given Vendor     
-- Code by Venugopal G A     
-- mail me for any queries venuasg@gmail.com
-- Change the vendor variable to your vendorid     
--------------------------------------------------------    
DECLARE @VENDORID VARCHAR(15)
DECLARE @ITEM VARCHAR(100)
DECLARE @ITEMDESC VARCHAR(100)
SET @VENDORID = 'ACETRAVE0001' -- CHANGE TO YOUR VENDOR ID

DECLARE ITEMMASTER CURSOR FOR 
SELECT ITEMNMBR, ITEMDESC  FROM IV00101 WHERE ITEMNMBR IN ('ITEM1','ITEM2','ITEM3') -- REPLACE HERE TO ADD ITEMS
OPEN ITEMMASTER
 FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
 WHILE (@@fetch_status <> -1) 
            BEGIN
  INSERT INTO [IV00103] ([ITEMNMBR],[VENDORID],[ITMVNDTY],[VNDITNUM],[VNDITDSC])
  VALUES(@ITEM,@VENDORID,1,@ITEM,@ITEMDESC)
            
  FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
            END
CLOSE ITEMMASTER    
DEALLOCATE ITEMMASTER

SQL Script to Assign ALL ITEMS IN THE ITEM CLASS to a given Vendor in Microsoft GP

-------------------------------------------------------------------------  
-- Assign ALL ITEMS IN THE ITEM CLASS to a given Vendor     
-- Code by Venugopal G A     
-- mail me for any queries venuasg@gmail.com
-- Change the vendor variable to your vendorid     
-------------------------------------------------------------------------    
DECLARE @VENDORID VARCHAR(15)
DECLARE @ITEM VARCHAR(100)
DECLARE @ITEMDESC VARCHAR(100)
SET @VENDORID = 'ACETRAVE0001' -- CHANGE TO YOUR VENDOR ID

DECLARE ITEMMASTER CURSOR FOR 
SELECT ITEMNMBR, ITEMDESC  FROM IV00101 
WHERE ITMCLSCD IN ('CLASS ID 1','CLASS ID 2') -- REPLACE HERE TO ADD ITEM CLASS ID
OPEN ITEMMASTER
FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
WHILE (@@fetch_status <> -1) 
     BEGIN
 INSERT INTO [IV00103] ([ITEMNMBR],[VENDORID],[ITMVNDTY],
                     [VNDITNUM],[VNDITDSC])
 VALUES(@ITEM,@VENDORID,1,@ITEM,@ITEMDESC)
            
 FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
      END
CLOSE ITEMMASTER    
DEALLOCATE ITEMMASTER

Missing Sub-ledger distribution in Microsoft GP

It is noted that when you drill-down the AP transactions sometimes you notice that the distribution is missing or is blank and you may also get issues while inquiring the document through Payable Transaction Inquiry.

Cause: Most of the time this happens when the posting process is interrupted for various reasons and the user has killed the session to reset GP, during which the tables are partially updated.

Resolution: There is no direct approach as GP Checklinks will not fix this issue and alternately SQL script will do by inserting the missing records. You can refer PM80600 table to re-insert the information through SQL script into PM30600 (PM Distribution History File) for historical distribution because it is easier.  And use the GL20000 for PM Open documents going into the PM10100 table (PM Distribution WORK OPEN) - most information is available in the GL table assuming that the posting set up is either Post To or through.

If you compare the content of these tables, most of the information is almost identical.  It is just a matter of rearranging the field to suit the tables. Any missing fields can then be mapped to a constant value just like when performing Integrations.

Start with a simple select statement from the source table and making sure that the result of the query reflects the expected information before going into the destination table.

Using the record from the sample screenshot in the original post, you can find the distribution detail in GL by running the select statement below:

SELECT * FROM GL20000 WHERE ORCTRNUM = 'Trx No' AND ORMSTRID = 'Customer/Vendor ID'