SQL Scripting: Microsoft Great Plains Dynamics GP – Alba Spectrum
Alba Spectrum Group, http://www.albaspectrum.com help@albaspectrum.com 1-866-528-0577
This article is not intended to be distributed in open press, we at Alba Spectrum have copyrights and do not allow anyone to republish it outside of www.albaspectrum.com
If you are looking into GP data repair, we would like to introduce you to the level of comfort and complexity, we expect that you have reasonable level of Microsoft SQL Server DBA experience and certification.
First script will update item costs in IV10200 from purchase receipts:
update IV10200 set UNITCOST=d.UNITCOST, ADJUNITCOST=d.UNITCOST
from IV10200 b
join POP30310 a on
a.ITEMNMBR=b.ITEMNMBR and a.POPRCTNM=b.RCPTNMBR and a.LOCNCODE=b.TRXLOCTN
join POP30300 c on a.POPRCTNM=c.POPRCTNM and c.RECEIPTDATE=b.DATERECD
left join
(
select
a.POPRCTNM,
a.ITEMNMBR,
a.RCPTLNNM,
(a.SERLTQTY*a.UNITCOST+case when b.Total_Landed_Cost_Amount is null then 0 else b.Total_Landed_Cost_Amount end)/a.SERLTQTY as UNITCOST
from POP30330 a
left join POP30700 b on a.POPRCTNM=b.POPRCTNM and a.RCPTLNNM=b.RCPTLNNM --and b.LCLINENUMBER=16384
where a.SERLTQTY>0 --and a.ITEMNMBR='2781'
) d
on a.ITEMNMBR=d.ITEMNMBR and a.POPRCTNM=d.POPRCTNM
Second script will reconstruct UNITCOST and ADJUNITCOST for the items tracked by lot number in IV10200:
DECLARE @ITEMNMBR VARCHAR(35)
DECLARE @DEX_ROW_ID INTEGER
DECLARE ITEMCURSOR CURSOR FOR
select ITEMNMBR from IV00101 where ITEMNMBR in ('605146', '182P','521','2781','BD318')
OPEN ITEMCURSOR
FETCH NEXT FROM ITEMCURSOR INTO @ITEMNMBR
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DEX_ROW_IDCURSOR CURSOR FOR
select DEX_ROW_ID from IV10200 where ITEMNMBR=@ITEMNMBR and PCHSRCTY=5 order by DEX_ROW_ID asc
OPEN DEX_ROW_IDCURSOR
FETCH NEXT FROM DEX_ROW_IDCURSOR INTO @DEX_ROW_ID
WHILE @@FETCH_STATUS = 0
BEGIN
--------------------------------------------------------------
-------------------------------------------------------------
PRINT @ITEMNMBR
PRINT @DEX_ROW_ID
update IV10200 set
ADJUNITCOST=
--select a.ITEMNMBR, a.RCPTNMBR, w.QTYONHND,w.ADJUNITCOST, y.NEWQTYRECVD, y.NEWUNITCOST,
round(((w.QTYONHND*w.ADJUNITCOST)+(y.NEWQTYRECVD*y.NEWUNITCOST))/(w.QTYONHND+y.NEWQTYRECVD), 2)--, a.*
from IV10200 a
join
--Begin First Join
(
select
b.DEX_ROW_ID, b.ITEMNMBR, b.DATERECD, b.RCPTNMBR,
b.QTYONHND, b.ADJUNITCOST, b.QTYRECVD as NEWQTYRECVD, d.UNITCOST as NEWUNITCOST
from POP30310 a
join POP30300 c on a.POPRCTNM=c.POPRCTNM
join IV10200 b
on
a.ITEMNMBR=b.ITEMNMBR and a.POPRCTNM=b.RCPTNMBR and a.LOCNCODE=b.TRXLOCTN and c.RECEIPTDATE=b.DATERECD
join
(
select
a.POPRCTNM,
a.ITEMNMBR,
a.RCPTLNNM,
(a.SERLTQTY*a.UNITCOST+case when b.Total_Landed_Cost_Amount is null then 0 else b.Total_Landed_Cost_Amount end)/a.SERLTQTY as UNITCOST
from POP30330 a
left join POP30700 b on a.POPRCTNM=b.POPRCTNM and a.RCPTLNNM=b.RCPTLNNM --and b.LCLINENUMBER=16384
where a.SERLTQTY>0 --and a.ITEMNMBR='2781'
) d
on a.ITEMNMBR=d.ITEMNMBR and a.POPRCTNM=d.POPRCTNM and d.RCPTLNNM=a.RCPTLNNM
) y on a.DEX_ROW_ID=y.DEX_ROW_ID --on a.ITEMNMBR=y.ITEMNMBR and a.DATERECD=y.DATERECD and a.RCPTNMBR=y.RCPTNMBR and a.DEX_ROW_ID=y.DEX_ROW_ID
---End First Join
--Begin Second Join
join
(
select k.ITEMNMBR, k.RCPTNMBR, l.OLDRCPTNMBR, l.DEX_ROW_ID, k.NEW_DEX_ROW_ID from
(select
a.ITEMNMBR, a.RCPTNMBR, max(b.DEX_ROW_ID) as DEX_ROW_ID, a.DEX_ROW_ID as NEW_DEX_ROW_ID
from
IV10200 a
join IV10200 b on a.ITEMNMBR=b.ITEMNMBR
where
a.PCHSRCTY=5 and b.PCHSRCTY=5 /*and a.RCPTNMBR!=b.RCPTNMBR*/ and b.DEX_ROW_ID<a.DEX_ROW_ID
group by a.RCPTNMBR, a.ITEMNMBR, a.DEX_ROW_ID
)
k
join (select DEX_ROW_ID, RCPTNMBR as OLDRCPTNMBR from IV10200) l on k.DEX_ROW_ID=l.DEX_ROW_ID --and k.RCPTNMBR=l.OLDRCPTNMBR
)
z on y.ITEMNMBR=z.ITEMNMBR and y.RCPTNMBR=z.RCPTNMBR and a.DEX_ROW_ID=z.NEW_DEX_ROW_ID
--End Second Join
--Begin Third Join
join (
select
b.DEX_ROW_ID, b.ITEMNMBR, b.DATERECD, b.RCPTNMBR,
b.QTYONHND, b.ADJUNITCOST, b.QTYRECVD as NEWQTYRECVD, d.UNITCOST as NEWUNITCOST
from POP30310 a
join POP30300 c on a.POPRCTNM=c.POPRCTNM
join IV10200 b
on
a.ITEMNMBR=b.ITEMNMBR and a.POPRCTNM=b.RCPTNMBR and a.LOCNCODE=b.TRXLOCTN and c.RECEIPTDATE=b.DATERECD
left join
(
select
a.POPRCTNM,
a.ITEMNMBR,
sum(a.SERLTQTY*a.UNITCOST+b.Total_Landed_Cost_Amount)/sum(a.SERLTQTY) as UNITCOST
from POP30330 a
join POP30700 b on a.POPRCTNM=b.POPRCTNM and a.RCPTLNNM=b.RCPTLNNM and b.LCLINENUMBER=16384
where a.SERLTQTY>0
group by a.POPRCTNM,
a.ITEMNMBR
) d
on a.ITEMNMBR=d.ITEMNMBR and a.POPRCTNM=d.POPRCTNM
) w on z.DEX_ROW_ID=w.DEX_ROW_ID
where a.ITEMNMBR =@ITEMNMBR and a.DEX_ROW_ID=@DEX_ROW_ID--, a. --and a.DEX_ROW_ID=@DEX_ROW_ID--in ('182P','521','2781','BD318')
--------------------------------------------------------------
-------------------------------------------------------------
FETCH NEXT FROM DEX_ROW_IDCURSOR INTO @DEX_ROW_ID
END
CLOSE DEX_ROW_IDCURSOR
DEALLOCATE DEX_ROW_IDCURSOR
FETCH NEXT FROM ITEMCURSOR INTO @ITEMNMBR
END
CLOSE ITEMCURSOR
DEALLOCATE ITEMCURSOR
--select * from IV10200 where ITEMNMBR='605146' order by DEX_ROW_ID asc
The third script will update non Purchase Receipt related records in IV10200: inventory adjustments, transfers and Sales
DECLARE @ITEMNMBR VARCHAR(35)
DECLARE @DEX_ROW_IDFROM INTEGER
DECLARE @DEX_ROW_IDTO INTEGER
DECLARE @UNITCOST MONEY
DECLARE @RCPTNMBR VARCHAR(50)
DECLARE ITEMCURSOR CURSOR FOR
select ITEMNMBR from IV00101 where ITEMNMBR in ('605146', '182P','521','2781','BD318')
OPEN ITEMCURSOR
FETCH NEXT FROM ITEMCURSOR INTO @ITEMNMBR
WHILE @@FETCH_STATUS = 0
BEGIN
select @DEX_ROW_IDFROM=0;
select @DEX_ROW_IDTO=0;
DECLARE TWORCTLINESCURSOR CURSOR FOR
select k.DEX_ROW_IDFROM, k.DEX_ROW_IDTO from
(select
a.ITEMNMBR, a.RCPTNMBR, max(b.DEX_ROW_ID) as DEX_ROW_IDFROM, a.DEX_ROW_ID as DEX_ROW_IDTO
from
IV10200 a
join IV10200 b on a.ITEMNMBR=b.ITEMNMBR
where
a.PCHSRCTY=5 and b.PCHSRCTY=5 /*and a.RCPTNMBR!=b.RCPTNMBR*/ and b.DEX_ROW_ID<a.DEX_ROW_ID
group by a.RCPTNMBR, a.ITEMNMBR, a.DEX_ROW_ID
)
k
join (select DEX_ROW_ID, RCPTNMBR as OLDRCPTNMBR from IV10200) l on k.DEX_ROW_IDFROM=l.DEX_ROW_ID
where k.ITEMNMBR=@ITEMNMBR
order by k.DEX_ROW_IDFROM asc
OPEN TWORCTLINESCURSOR
FETCH NEXT FROM TWORCTLINESCURSOR INTO @DEX_ROW_IDFROM, @DEX_ROW_IDTO
WHILE @@FETCH_STATUS = 0
BEGIN
--------------------------------------------------------------
-------------------------------------------------------------
select @UNITCOST=0;
select @UNITCOST=ADJUNITCOST from IV10200 where DEX_ROW_ID=@DEX_ROW_IDFROM
select @RCPTNMBR=RCPTNMBR from IV10200 where DEX_ROW_ID=@DEX_ROW_IDFROM
PRINT @RCPTNMBR
PRINT CAST(@UNITCOST as CHAR(50))
PRINT CAST(@DEX_ROW_IDFROM as CHAR(20))
PRINT CAST(@DEX_ROW_IDTO as CHAR(20))
update IV10200 set UNITCOST=@UNITCOST, ADJUNITCOST=@UNITCOST where DEX_ROW_ID>@DEX_ROW_IDFROM and DEX_ROW_ID<@DEX_ROW_IDTO
and ITEMNMBR=@ITEMNMBR and PCHSRCTY!=5
--------------------------------------------------------------
-------------------------------------------------------------
FETCH NEXT FROM TWORCTLINESCURSOR INTO @DEX_ROW_IDFROM, @DEX_ROW_IDTO
END
---------------------------------------------------------------------------We have to update all new non-PR transactions, because PR is not there in the future yet
if @DEX_ROW_IDTO=0 return;
select @UNITCOST=ADJUNITCOST from IV10200 where DEX_ROW_ID=@DEX_ROW_IDTO
--PRINT @RCPTNMBR
PRINT CAST(@UNITCOST as CHAR(50))
--PRINT CAST(@DEX_ROW_IDFROM as CHAR(20))
PRINT CAST(@DEX_ROW_IDTO as CHAR(20))
update IV10200 set UNITCOST=@UNITCOST, ADJUNITCOST=@UNITCOST where DEX_ROW_ID>@DEX_ROW_IDTO
and ITEMNMBR=@ITEMNMBR and PCHSRCTY!=5
--------------------------------------------------------------------------
CLOSE TWORCTLINESCURSOR
DEALLOCATE TWORCTLINESCURSOR
FETCH NEXT FROM ITEMCURSOR INTO @ITEMNMBR
END
CLOSE ITEMCURSOR
DEALLOCATE ITEMCURSOR
Alba Spectrum Group:
http://www.albaspectrum.com
help@albaspectrum.com 1-866-528-0577,
1-630-961-5918, serving Microsoft Dynamics GP Great Plains customers in
USA/Canada nationwide. Local service is available in Dallas and Houston, TX:
Richmond, Sugar Land, Katy, Rosenberg, Missouri City, Pearland, Friendswood,
Meadows, Mission Bend, Jersey Village; Chicago, IL: Naperville, Aurora,
Bolingbrook, Romeoville, Joliet, Wheaton, Lisle, Downers Grove, Schaumburg, Elk
Grove, Lombard, Woodridge, Darien, Westmont, Batavia, St. Charles, Elgin,
Crystal Lake.
International: Brazil-English Brasil-Portuguese
We serve large scale Microsoft Dynamics GP Great Plains Customization, Data Conversion/Massage/Migration, version upgrade, custom software development and integration to GP, heterogeneous reporting for GP: SQL Server Reporting Services, Crystal Reports; Visual Studio.Net programming for GP, including eConnect, ADO to GP database; complex GP data repair (when somebody damaged GP data via inaccurate UPDATE SQL statement)