How to search for allocated Inventory Items in Microsoft Dynamics GP

TechKnowledge Content

Issue

How can I locate an allocation for an item in Microsoft Dynamics GP when the system is indicating that my item is allocated and you cannot find the allocation anywhere?

Resolution

This script checks the various modules and will return results of where the item is allocated. Before running the script to look for allocated quantities, be sure you have a backup made. Edit and then execute the below script in SQL Server Management Studio against the company database.

Note Be sure to replace the item number "A" in the second line with the correct item number that you are looking for.

if exists (select * from sysobjects where name ='SOP10200')Begin print 'Allocated Orders in Sales Order Processing'select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200 where ITEMNMBR = @ITEMNMBR and ATYALLOC > 0 and SOPTYPE = 2end

if exists (select * from sysobjects where name ='SOP10200')Begin print 'Allocated Invoices in Sales Order Processing'select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200 where ITEMNMBR = @ITEMNMBR and ATYALLOC > 0 and SOPTYPE = 3end

if exists (select * from sysobjects where name ='SOP10200')Begin print 'Allocated Fulfillment Orders in Sales Order Processing'select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200 where ITEMNMBR = @ITEMNMBR and ATYALLOC > 0 and SOPTYPE = 6end

if exists (select * from sysobjects where name ='POP10500 and POP10310')Beginselect A.POPRCTNM,A.ITEMNMBR, QTYRESERVED,* from POP10500 A join POP10310 Bon A.POPRCTNM=B.POPRCTNM AND A.RCPTLNNM=B.RCPTLNNM AND A.ITEMNMBR=B.ITEMNMBR where A.ITEMNMBR = @ITEMNMBR and A.QTYRESERVED > 0 end

select A.POPRCTNM,A.ITEMNMBR, QTYRESERVED,* from POP10500 A join POP10310 B on B.POPRCTNM=B.POPRCTNM AND A.RCPTLNNM=B.RCPTLNNM AND A.ITEMNMBR=B.ITEMNMBR where A.ITEMNMBR = @ITEMNMBR and A.QTYRESERVED > 0

if exists (select * from sysobjects where name ='SVC00203')Beginselect CALLNBR,ITEMNMBR,ATYALLOC,LOCNCODE from SVC00203 where ITEMNMBR=@ITEMNMBR and LINITMTYP='P' AND ATYALLOC <> 0endselect CALLNBR,ITEMNMBR,ATYALLOC,LOCNCODE from SVC00203 where ITEMNMBR=@ITEMNMBR and LINITMTYP='P' AND ATYALLOC <> 0

if exists (select * from sysobjects where name ='SVC05601')Beginselect RTV_Number,ITEMNMBR, QUANTITY, * from SVC05601 where ITEMNMBR=@ITEMNMBR and RTV_Status=2 and CUSTOWN=0 and Transfer_Reference=''end

select RTV_Number,ITEMNMBR, QUANTITY, * from SVC05601 where ITEMNMBR=@ITEMNMBR and RTV_Status=2 and CUSTOWN=0 and Transfer_Reference=''

if exists (select * from sysobjects where name ='PA10901')Beginselect PAIV_Document_No,ITEMNMBR,PABase_Qty,LOCNCODE from PA10901 where ITEMNMBR=@ITEMNMBR and PABase_Qty <> 0 and PAIV_Transfer_Type = 1end

print ''print 'This is the (general) allocated or pending issue quantity in the MOP1400 for this Manufacturing Order'print '==============================================================='print ''

if exists (select * from sysobjects where name ='MOP1400')Beginselect MANUFACTUREORDER_I, ITEMNMBR, ATYALLOC from MOP1400 where ATYALLOC > 0 and ITEMNMBR = @ITEMNMBRend

print 'This is the (bin) allocated quantity in the MOP1900 for this Manufacturing Order'print '==============================================================='print ''

if exists (select * from sysobjects where name ='MOP1200 and MOP1020 and MOP1025 and MOP1026')Beginselect * from MOP1020 where DOCNUMBR in (select PICKNUMBER from MOP1200 where POSTED=1)select * from MOP1025 where MOPDOCNUM in (select PICKNUMBER from MOP1200 where POSTED=1)select * from MOP1026 where MOPDOCNUM in (select PICKNUMBER from MOP1200 where POSTED=1)end