Posted on September 6, 2011

SQL Script to find missing or deleted Journal No in Microsoft GP

Posted on April 8, 2011

Script
 
————————————————————————-
— Find Missing or deleted Journal Entry No in Microsoft GP
— Code by Venugopal G A
— mail me if you need query for other type of transactions or for any
— queries venuasg@gmail.com
————————————————————————-
If Object_Id(‘tempdb..#tempJV’) is Not Null
Drop table #tempJV

create table #tempJV
(
JVno int,
MJV int identity(1,1)
)

insert into #tempJV (JVno)
(select distinct JRNENTRY from GL20000 )

select wg.MJV as id_is_missing
from #tempJV wg
left join #tempJV ti on wg.MJV = ti.JVno
where ti.JVno is null
order by wg.MJV

SQL Script to Compare Bank Subledger balance to corresponding GL account balance

Posted on April 8, 2011

Script
 
—————————————————————————-
— Compare Bank Subledger balance to corresponding GL account balance in GP.
— Code by Venugopal G A
— mail me for any queries venuasg@gmail.com
—————————————————————————-
DECLARE @CHECKBOOKID VARCHAR(30)
SET @CHECKBOOKID = ‘FIRST BANK’ — CHANGE THIS TO YOUR CHECKBOOK ID
–Set Bank Type and get Bank Balance
Select ‘Bank’ as SubType, CURRBLNC as SubBal, 0 as GLBal
Into #SubtoGLCompare From CM00100 Where Chekbkid=@CHECKBOOKID
/*Get the GL total for this Account. Change the account below or optionally add additional
actnumbr parameters to tighten or broaden the account*/
INSERT #SubtoGLCompare (SubType,SubBal,GLBAL)
SELECT ‘Bank’ as SubType, 0 as SubBal, SUM(Perdblnc) AS GLBal
FROM GL11110 WHERE actnumbr_2 = ‘1100’ — Change this
Select SubType, sum(Subbal) as SubBal, Sum(GLBal) as GLBAL, sum(SubBal)-Sum(GLBAL) as Difference,
‘Difference is Subledger minus GL.’ as Instructions
from #SubtoGLCompare
Group by SubType
Drop Table #SubtoGLCompare

SQL Script to assign all items to all sites in Microsoft GP

Posted on April 8, 2011

Assign All Items to ALL Sites
 
——————————————————–
— Assign All Items to ALL Sites
— Code by Venugopal G A
— Below script will assign all the available inventory item
— to all available sites if they are not assigned already.
— mail me for any queries venuasg@gmail.com
——————————————————–
DECLARE @ITEM varchar(100)
DECLARE @LOCATION varchar(100)
DECLARE @ITEMAlreadyExist int

DECLARE ITEMMASTER CURSOR FOR
SELECT ITEMNMBR FROM IV00101
OPEN ITEMMASTER
FETCH NEXT FROM ITEMMASTER INTO @ITEM
WHILE (@@fetch_status -1)
BEGIN
DECLARE SITE CURSOR FOR
SELECT LOCNCODE FROM IV40700
OPEN SITE
FETCH NEXT FROM SITE INTO @LOCATION
WHILE (@@fetch_status -1)
BEGIN
SELECT @ITEMAlreadyExist = isnull(count(*), 0) FROM IV00102
where ITEMNMBR = @ITEM and LOCNCODE = @LOCATION
if @ITEMAlreadyExist = 0
BEGIN
INSERT INTO IV00102 VALUES(@ITEM,@LOCATION,”,2,”,0,0,0,0
,’01/01/1900′,”,’01/01/1900′,0,0,0,0,0,0,0,0,0,0,0,0
,’01/01/1900′,’01/01/1900′,’01/01/1900′,’01/01/1900′,0,”
,”,”,1,0,0,1,0,0,1,2,0,0,0,0,0,0,0,1,0,0,0,3,0,0,0,”
,”,”,”,”,”,”,”,1,1,”,1,1,0,1,1,1,0,0,0,0,0)
END
FETCH NEXT FROM SITE INTO @LOCATION
END
DEALLOCATE SITE
FETCH NEXT FROM ITEMMASTER INTO @ITEM
END
DEALLOCATE ITEMMASTER

SQL Script to assign all Items to a given Vendors

Posted on April 8, 2011

Assign All Items to a given Vendor
 ——————————————————–
— Assign All 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’

DECLARE ITEMMASTER CURSOR FOR
SELECT ITEMNMBR, ITEMDESC FROM IV00101
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
DEALLOCATE ITEMMASTER