Dec 7, 2009

Get Allocated Inventory Items in Microsoft Great Plains

Below is the SQL query to get al the allocated inventory, copy and paste
the below code in sql browser on the GP company database. Modify accordingly
for the result.

PRINT 'This is the allocated quantity in the SOP10200 for Orders'
select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200
where ITEMNMBR = 'A' and ATYALLOC > 0 and SOPTYPE = 2

PRINT 'This is the allocated quantity in the SOP10200 for Invoices'
select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200
where ITEMNMBR = 'A' and ATYALLOC > 0 and SOPTYPE = 3

PRINT 'This is the allocated quantity in the IV10001 for a decrease Adjustment'
select IVDOCNBR,ITEMNMBR,TRXQTY from IV10001
where ITEMNMBR = 'A' and TRXQTY < 0 PRINT 'This is the allocated quantity in the IV10001 for Transfers' select IVDOCNBR,ITEMNMBR,TRXQTY from IV10001 where ITEMNMBR = 'A' and TRXQTY > 0 and IVDOCTYP = 3

PRINT 'This is the allocated quantity in the IVC10101 for saved Invoices'
select INVCNMBR,ITEMNMBR,QUANTITY from IVC10101
where ITEMNMBR = 'A' and DOCTYPE = 1

PRINT 'This is the allocated quantity in the BM10300 '
select TRX_ID,ITEMNMBR,ATYALLOC from BM10300
where Component_ID <> 0 and ITEMNMBR = 'A'
and ATYALLOC > 0

PRINT 'This is the allocated quantity in the POP10500 for POP Returns'
select POPRCTNM,QTYRESERVED from POP10500
where QTYRESERVED > 0 and Status = 0 and ITEMNMBR = 'A'

No comments: