Allocated Quantities

(excluding Depot management, Project Accounting, Service call & Manufacturing)

select  'Allocated Returns in Purchase Order Processing' as AllocType, A.POPRCTNM as document,A.ITEMNMBR, QTYRESERVED as allocatedqty
from POP10500 A join POP10310 B
on A.POPRCTNM=B.POPRCTNM AND
A.RCPTLNNM=B.RCPTLNNM AND
A.ITEMNMBR=B.ITEMNMBR 
where  A.QTYRESERVED > 0 

union all

select  'Allocated Returns in Purchase Order Processing' as AllocType,A.POPRCTNM as document,A.ITEMNMBR, QTYRESERVED as allocatedqty
 from POP10500 A join POP10310 B 
on B.POPRCTNM=B.POPRCTNM AND
A.RCPTLNNM=B.RCPTLNNM AND A.ITEMNMBR=B.ITEMNMBR 
where  A.QTYRESERVED > 0

union all

select 'Allocated Fulfillment Orders in Sales Order Processing' as AllocType, SOPNUMBE as document,ITEMNMBR,ATYALLOC as allocatedqty 
from SOP10200 
where  ATYALLOC > 0 and  SOPTYPE = 6

union all

select 'Allocated Invoices in Sales Order Processing' as AllocType, SOPNUMBE as document,ITEMNMBR,ATYALLOC as allocatedqty
from SOP10200 
where ATYALLOC > 0 and  SOPTYPE = 3

union all

select	'Allocated Orders in Sales Order Processing' as AllocType ,
		SOPNUMBE as document,
		ITEMNMBR,
		ATYALLOC as allocatedqty
from	SOP10200 
where	ATYALLOC > 0 and  SOPTYPE = 2

union all 

select 'Allocated documents in Inventory' as AllocType ,
IVDOCNBR as Document,
ITEMNMBR,
TRXQTY as AllocatedQTY
from IV10001 where TRXQTY < 0 

union all

select 'Allocated documents in Inventory' as AllocType ,
IVDOCNBR as Documnet,
ITEMNMBR,
TRXQTY as allocatedQTY
from IV10001 where  TRXQTY > 0 and IVDOCTYP = 3

union all 

select 'Allocated Invoices in Invoicing' as AllocType ,
INVCNMBR as Document,
ITEMNMBR,
QUANTITY as allocatedQTY 
from IVC10101 where  DOCTYPE = 1

union all 

select 'Allocated Assembly documents in Bill of Materials' as AllocType, 
TRX_ID as Document,
ITEMNMBR,
ATYALLOC as Allocated 
from BM10300 where Component_ID <> 0 
and  ATYALLOC > 0

Manufacturing Allocated or pending issue


select 'Allocated - general or pending issue' as AllocType,
        CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
            WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
            WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
            WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
            WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
            WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
            WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
            WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
        END AS MOstatus,
 *
from MOP1400 
inner join dbo.WO010032 
ON dbo.MOP1400 .MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
where ATYALLOC > 0 

select 'Allocated - bin ' as AllocType,
        CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
            WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
            WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
            WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
            WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
            WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
            WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
            WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
        END AS MOstatus,
		*
from MOP1900 
inner join dbo.WO010032 
ON dbo.MOP1900.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
where ATYALLOC > 0

select 'Allocated - lot or pending issue' as AllocType,
        CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
            WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
            WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
            WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
            WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
            WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
            WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
            WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
        END AS MOstatus,
        * 
from MOP1020 
inner join dbo.WO010032 
ON dbo.MOP1020.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I

Pending manufacturing transactions that can hold allocations


select	'MOP1025' as AllocType ,
		CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
			WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
			WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
			WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
			WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
			WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
			WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
			WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
		END AS MOstatus,
		* 
FROM         dbo.MOP1025 INNER JOIN
                      dbo.WO010032 ON dbo.MOP1025.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
WHERE     (dbo.MOP1025.MOPDOCNUM IN
                          (SELECT     PICKNUMBER
                            FROM          dbo.MOP1200
                            WHERE      (POSTED = 1)))

select 'MOP1020' as AllocType ,
		CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
			WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
			WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
			WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
			WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
			WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
			WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
			WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
		END AS MOstatus	,
		*	
FROM         dbo.MOP1020 INNER JOIN
                      dbo.WO010032 ON dbo.MOP1020.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
WHERE     (dbo.MOP1020.DOCNUMBR IN
                          (SELECT     PICKNUMBER
                            FROM          dbo.MOP1200
                            WHERE      (POSTED = 1)))
                           

SELECT     'MOP1020' AS AllocType, dbo.MOP1026.MANUFACTUREORDER_I,
			CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
				WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
				WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
				WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
				WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
				WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
				WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
				WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
			END AS MOstatus	,
			*
FROM         dbo.MOP1026 INNER JOIN
                      dbo.WO010032 ON dbo.MOP1026.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
WHERE     (dbo.MOP1026.MOPDOCNUM IN
                          (SELECT     PICKNUMBER
                            FROM          dbo.MOP1200
                            WHERE      (POSTED = 1)))

Dynamics GP inventory allocations to SOP orders script

Problem – User claims item is hung, allocated inventory not getting released.

Dynamics GP inventory allocations to SOP orders script.

declare @ITEMNMBR char(30)
select @ITEMNMBR = 'itemnumber'

print 'Allocated Documents in Sales Order Processing'
print '==============================================================='
print ''

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 = 2
end

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 = 3
end

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 = 6
end

Print ''
print 'Allocated Returns in Purchase Order Processing'
print '===============================================================' 
print ''

if exists (select * from sysobjects where name ='POP10500 and POP10310')
Begin
select A.POPRCTNM,A.ITEMNMBR, QTYRESERVED,* 
from POP10500 A join POP10310 B
on 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 

Print ''
print 'Allocated documents in Inventory'
print '==============================================================='
print ''

if exists (select * from sysobjects where name ='IV10001')
Begin
select IVDOCNBR,ITEMNMBR,TRXQTY 
from IV10001 where ITEMNMBR = @ITEMNMBR and TRXQTY < 0 
select IVDOCNBR,ITEMNMBR,TRXQTY 
from IV10001 where ITEMNMBR = @ITEMNMBR and TRXQTY > 0 and IVDOCTYP = 3
end

print ''
print 'Allocated Invoices in Invoicing'
print '==============================================================='
print ''

if exists (select * from sysobjects where name ='IVC10101')
Begin
select INVCNMBR,ITEMNMBR,QUANTITY 
from IVC10101 where ITEMNMBR = @ITEMNMBR and DOCTYPE = 1
end

print ''
print 'Allocated Assembly documents in Bill of Materials'
print '==============================================================='
print ''

if exists (select * from sysobjects where name ='BM10300')
Begin
select TRX_ID,ITEMNMBR,ATYALLOC 
from BM10300 where Component_ID <> 0 
and ITEMNMBR = @ITEMNMBR and ATYALLOC > 0 
end

Print ''
print 'Allocated Orders in Service Call Management'
print '==============================================================='
print ''

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

print 'Allocated Service Call Transfers'
print '==============================================================='
print ''

if exists (select * from sysobjects where name ='SVC00701')
Begin
select ORDDOCID,ITEMNMBR,TRNSFQTY from SVC00701 
where ITEMNMBR=@ITEMNMBR and TRNSFQTY <> 0
end

print 'Allocated Service Call Transfers (serial)'
print '==============================================================='
print ''

if exists (select * from sysobjects where name ='SVC00702')
Begin
select ORDDOCID,ITEMNMBR,SERLTQTY from SVC00702 
where ITEMNMBR=@ITEMNMBR and SERLTQTY <> 0
end

print 'Allocated RTV lines'
print '==============================================================='
print ''

if exists (select * from sysobjects where name ='SVC05601')
Begin
select 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=''

print 'Allocated Documents in Depot Management'
print '==============================================================='
print ''

if exists (select * from sysobjects where name ='SVC06100')
Begin
select WORKORDNUM,IBITEMNUM,QUANTITY,LOCNCODE 
from SVC06100 where IBITEMNUM=@ITEMNMBR 
AND WORECTYPE = 2 AND QUANTITY <> 0 
End

if exists (select * from sysobjects where name ='SVC06101')
Begin
select WORKORDNUM,ITEMNMBR,ATYALLOC,LOCNCODE 
from SVC06101 where ITEMNMBR=@ITEMNMBR 
AND WORECTYPE = 2 and ATYALLOC <> 0    
end

if exists (select * from sysobjects where name ='SVC06120')
Begin
select WORKORDNUM,ITEMNMBR,SERLTQTY from SVC06120 
where ITEMNMBR=@ITEMNMBR AND WORECTYPE = 2 AND SERLTQTY <> 0
End

print ''
print 'Allocated Documents in Project Accounting'
print '==============================================================='
print ''

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

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

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

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

if exists (select * from sysobjects where name ='MOP1900')
Begin
select MANUFACTUREORDER_I, ITEMNMBR, ATYALLOC, LOCNCODE, BIN 
from MOP1900 where ATYALLOC > 0 and ITEMNMBR = @ITEMNMBR
end

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

if exists (select * from sysobjects where name ='MOP1020')
Begin
select MANUFACTUREORDER_I, ITEMNMBR, SERLTNUM, 
FROM_SITE_I, DOCNUMBR from MOP1020 where ITEMNMBR =@ITEMNMBR
end

print 'Pending transactions that can hold allocations'
print '==============================================================='
print ''

if exists (select * from sysobjects 
where name ='MOP1200 and MOP1020 and MOP1025 and MOP1026')
Begin
select * 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

Source

Post script:  It turned out to be there were invoices at sop10100 which were allocated and no customers associated.

Cheers!

Sanjay