Hi Funmilayo
You can check it using SQL by using this :
CREATE PROCEDURE CHECK_ITEM_ALLOCATE
@ITEMNMBR nvarchar(31)
AS
SET NOCOUNT ON;
select * from (
SELECT 'ITEM ADJUST OUT' AS SOURCEDOC, SUM(A.TRXQTY * A.QTYBSUOM) * -1 AS QTYALLOCATED, A.ITEMNMBR, A.TRXLOCTN
FROM IV10001 A WITH(NOLOCK)
WHERE A.TRXQTY < 0
AND A.IVDOCTYP = 1 AND A.ITEMNMBR = @ITEMNMBR
GROUP BY A.ITEMNMBR, A.TRXLOCTN
UNION ALL
SELECT 'ITEM TRANSFER' AS SOURCEDOC, SUM(A.TRXQTY * A.QTYBSUOM) AS QTYALLOCATED, A.ITEMNMBR, A.TRXLOCTN
FROM IV10001 A WITH(NOLOCK)
WHERE A.IVDOCTYP = 3 AND A.ITEMNMBR = @ITEMNMBR
GROUP BY A.ITEMNMBR, A.TRXLOCTN
UNION ALL
SELECT 'SOP' AS SOURCEDOC, SUM(A.ATYALLOC * A.QTYBSUOM) AS QTYALLOCATED, A.ITEMNMBR, A.LOCNCODE
FROM SOP10200 A WITH(NOLOCK)
WHERE A.SOPTYPE NOT IN (1,4) AND A.ITEMNMBR = @ITEMNMBR
GROUP BY A.ITEMNMBR, A.LOCNCODE
UNION ALL
SELECT 'POP RETURN' AS SOURCEDOC, SUM(A.QTYRESERVED * A.UMQTYINB) AS QTYALLOCATED, A.ITEMNMBR, A.TRXLOCTN
FROM POP10300 B WITH(NOLOCK) LEFT JOIN POP10500 A WITH(NOLOCK) ON A.POPRCTNM = B.POPRCTNM
WHERE A.ITEMNMBR IS NOT NULL AND A.ITEMNMBR = @ITEMNMBR AND B.POPTYPE IN (4,5,6,7,8)
GROUP BY A.ITEMNMBR, A.TRXLOCTN
UNION ALL
SELECT 'ASSEMBLY ENTRY' AS SOURCEDOC, SUM(A.ATYALLOC * A.QTYBSUOM) AS QTYALLOCATED, A.ITEMNMBR, A.LOCNCODE
FROM BM10200 B WITH(NOLOCK) LEFT JOIN BM10300 A WITH(NOLOCK) ON B.TRX_ID = A.TRX_ID
WHERE A.ITEMNMBR IS NOT NULL AND A.ITEMNMBR = @ITEMNMBR
GROUP BY A.ITEMNMBR, A.LOCNCODE
UNION ALL
SELECT 'IN-TRANSIT' AS SOURCEDOC, SUM(A.TRNSFQTY * A.QTYBSUOM) AS QTYALLOCATED, A.ITEMNMBR, A.TRNSFLOC
FROM SVC00701 A WITH(NOLOCK)
WHERE A.ITEMNMBR IS NOT NULL AND A.ITEMNMBR = @ITEMNMBR
GROUP BY A.ITEMNMBR, A.TRNSFLOC
) y
go
grant exec on CHECK_ITEM_ALLOCATE to DYNGRP
To check any item using this exec :
exec CHECK_ITEM_ALLOCATE '[Your Item]'
