Microsoft,Great,Plains,SQL,Scr computer Microsoft Great Plains SQL Scripting: repairing lot numbers
Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc ----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a
Below we would like to give you brief excurse to lot number tracking and provide fixing scripts:Item Lot Number Master table, IV00300. This table gets record when lot is received at the specific date, please familiarize yourself with its primary key to get an idea. Now we should come to the complication of GP business logic, related to the fact, that lot number tracking is optional. This is why lot numbers refer to general table: Inventory Purchase Receipt Work this table participate in regular logic, when we dont use lot numberingInventory Purchase Receipt Work, IV10200. This table gets record when items arrive to GP via Purchase Receipts, Inventory Adjustments or Inventory transfers (in this case items are simply moved from one IV site to another)We provide you fixing scripts, however you should analyze and try them first in test environment, as your specific case is now known to us and you should be responsible for SQL data repair.Below please see script repairing missing IV00300 when IV10200 record is present:insert into IV00300(ITEMNMBR,LOCNCODE,DATERECD,DTSEQNUM ,LOTNUMBR,QTYRECVD ,QTYSOLD ,ATYALLOC,UNITCOST ,RCTSEQNM,VNDRNMBR,LTNUMSLD,QTYTYP ,BIN,MFGDATE,EXPNDATE)select a.ITEMNMBR,a.TRXLOCTN,a.DATERECD,1,e.SERLTNUM,a.QTYRECVD,0,0,a.UNITCOST,a.RCTSEQNM,'',--VNDRNMBR0,a.QTYTYPE,'','01/01/1900','01/01/1900'from IV10200 ajoin IV30200 b on b.DOCNUMBR=a.RCPTNMBR and DATERECD='MM/DD/YY' and join IV30400 e on b.DOCNUMBR=e.DOCNUMBRSecond script gives you opposite repair, when IV0200 record is missing:insert into IV10200(ITEMNMBR,TRXLOCTN,DATERECD,RCTSEQNM ,RCPTSOLD,QTYRECVD,QTYSOLD,QTYCOMTD,QTYRESERVED ,FLRPLNDT,PCHSRCTY,RCPTNMBR,VENDORID,PORDNMBR ,UNITCOST,QTYTYPE,Landed_Cost ,NEGQTYSOPINV,VCTNMTHD,ADJUNITCOST,QTYONHND )select a.ITEMNMBR,a.LOCNCODE,a.DATERECD,a.RCTSEQNM,0,a.QTYRECVD,a.QTYSOLD,0,0,'01/01/1900',1,'',--we do not know RCPTNMBR'INV TRF',-- as VENDORID'INV TRF', --as PORDNMBRa.UNITCOST,a.QTYTYPE,0, --as Landed Cost0, --as NEGQTYSOPINV3, --as VCTNMTHDa.UNITCOST, --as ADJUNITCOST1000000 -- as QTYONHAND - bogus - to try, in any case qty on hand is not important in this tablefrom IV00300 aleft join IV10200 b on a.ITEMNMBR=b.ITEMNMBR and b.TRXLOCTN=a.LOCNCODE and a.DATERECD=b.DATERECD and a.RCTSEQNM=b.RCTSEQNM and a.QTYTYPE=b.QTYTYPEwhere b.ITEMNMBR is null and a.LTNUMSLD=0
Microsoft,Great,Plains,SQL,Scr