Dynamics GP – Purchase Order Line status export and reconciliation – Script

Today received a request to provide an export of all purchase order line items which were open with data enabling the user to sum the outstanding amounts for each PO line item to match with the purchase orders remaining sub total amount.

Tables used:
POP10500 – Purchasing Receipt Line Quantities
POP10110 – Purchase Order Line
POP10100 – Purchase Order Work
GL00105 – Account Index Master
IV00101 – Item Master

The below script extracts the required data:

--drop table ##polines 
--drop table ##polines2 
SELECT dbo.pop10500.ponumber, 
       dbo.pop10500.vendorid, 
       dbo.pop10500.itemnmbr, 
       dbo.pop10500.uofm, 
       dbo.pop10500.curncyid, 
       dbo.pop10500.polnenum, 
       Sum(dbo.pop10500.qtyshppd)      AS Shipped, 
       Sum(dbo.pop10500.qtyinvcd)      AS Invoiced, 
       Sum(dbo.pop10500.qtyrej)        AS Rejected, 
       Sum(dbo.pop10500.qtymatch)      AS Matched, 
       Sum(CASE dbo.pop10500.poptype 
             WHEN 5 THEN dbo.pop10500.qtyreserved 
             ELSE 0 
           END)                        AS QTYReserved, 
       Sum(dbo.pop10500.qtyinvreserve) AS qtyinvreserve 
INTO   ##polines 
FROM   dbo.pop10500 
GROUP  BY ponumber, 
          vendorid, 
          itemnmbr, 
          uofm, 
          curncyid, 
          polnenum 

SELECT dbo.pop10110.itemnmbr, 
       dbo.pop10110.ponumber, 
       dbo.pop10110.vendorid, 
       CASE dbo.pop10100.potype 
         WHEN 1 THEN 'Standard' 
         WHEN 2 THEN 'Drop Ship' 
         WHEN 3 THEN 'Blanket' 
         WHEN 4 THEN 'Blanket Drop ship' 
       END 
       AS POTYPE, 
       dbo.pop10100.docdate, 
       Year(dbo.pop10100.docdate) 
       AS year, 
       CASE dbo.pop10100.postatus 
         WHEN 1 THEN 'New' 
         WHEN 2 THEN 'Released' 
         WHEN 3 THEN 'Change order' 
         WHEN 4 THEN 'Received' 
         WHEN 5 THEN 'Closed' 
         WHEN 6 THEN 'Cancelled' 
       END 
       AS [PO Status], 
       dbo.pop10110.unitcost, 
       ( dbo.pop10110.qtyorder - dbo.pop10110.qtycance - Isnull( 
         ##polines.shipped, 0) ) 
       * dbo.pop10110.unitcost 
       AS EXTDCOST, 
       dbo.pop10110.noninven, 
       dbo.pop10110.ord, 
       dbo.pop10110.reqdate, 
       dbo.pop10110.prmdate, 
       dbo.pop10110.prmshpdte, 
       dbo.pop10110.reqstdby, 
       dbo.pop10110.qtyorder 
       AS Ordered, 
       dbo.pop10110.qtycance 
       AS Cancelled, 
       Isnull(##polines.shipped, 0) 
       AS Shipped, 
       Isnull(##polines.invoiced, 0) 
       AS Invoiced, 
       Isnull(##polines.rejected, 0) 
       AS Rejected, 
       Isnull(##polines.matched, 0) 
       AS Matched, 
       dbo.pop10110.qtyorder - dbo.pop10110.qtycance - 
       Isnull(##polines.invoiced, 0) AS 
       RemainingQty, 
       ( dbo.pop10110.qtyorder - dbo.pop10110.qtycance - Isnull( 
         ##polines.invoiced, 0) 
       ) * dbo.pop10110.unitcost 
       AS RemainingExtd, 
       Isnull(##polines.qtyinvreserve, 0) 
       AS qtyinvreserve, 
       Isnull(##polines.qtyinvreserve, 0) * dbo.pop10110.unitcost 
       AS extinvreserve, 
       Isnull(##polines.qtyreserved, 0) 
       AS QTYReserved, 
       Isnull(##polines.qtyreserved, 0) * dbo.pop10110.unitcost 
       AS extQTYReserved, 
       dbo.gl00105.actnumst, 
       dbo.pop10100.remsubto, 
       dbo.pop10110.polnesta 
INTO   ##polines2 
FROM   dbo.gl00105 WITH (nolock) 
       RIGHT OUTER JOIN dbo.pop10100 WITH (nolock) 
                        INNER JOIN dbo.pop10110 WITH (nolock) 
                                ON dbo.pop10100.ponumber = dbo.pop10110.ponumber 
                        LEFT OUTER JOIN ##polines 
                                     ON dbo.pop10110.ponumber = 
                                        ##polines.ponumber 
                                        AND dbo.pop10110.ord = 
                                            ##polines.polnenum 
                     ON dbo.gl00105.actindx = dbo.pop10110.invindx 
WHERE  ( dbo.pop10110.linenumber <> 0 ) 
       AND ( dbo.pop10100.postatus IN ( 1, 2, 3 ) ) 
GROUP  BY dbo.pop10110.itemnmbr, 
          dbo.pop10110.qtyorder, 
          dbo.pop10110.qtycance, 
          dbo.pop10110.unitcost, 
          dbo.pop10110.extdcost, 
          dbo.pop10110.noninven, 
          dbo.pop10110.reqdate, 
          dbo.pop10110.prmdate, 
          dbo.pop10110.prmshpdte, 
          dbo.pop10110.reqstdby, 
          dbo.pop10100.postatus, 
          dbo.gl00105.actnumst, 
          dbo.pop10100.potype, 
          dbo.pop10100.docdate, 
          dbo.pop10110.ponumber, 
          dbo.pop10110.vendorid, 
          dbo.pop10110.itemnmbr, 
          dbo.pop10110.uofm, 
          dbo.pop10110.curncyid, 
          dbo.pop10110.ord, 
          ##polines.shipped, 
          ##polines.qtyreserved, 
          ##polines.qtyinvreserve, 
          ##polines.invoiced, 
          ##polines.rejected, 
          ##polines.matched, 
          ##polines.qtyreserved, 
          dbo.pop10100.remsubto, 
          dbo.pop10110.polnesta 

SELECT ##polines2.*, 
       Isnull(dbo.iv00101.itemdesc, '') AS [Item Description] 
FROM   ##polines2 
       LEFT OUTER JOIN dbo.iv00101 
                    ON ##polines2.itemnmbr = dbo.iv00101.itemnmbr 
--drop table ##polines 
--drop table ##polines2

The challenge was to demonstrate the sum of the PO line items outstanding were reconciled to the remaining subtotal of Purchase Order according to GP.  The reconciliation can be done by creating the pivot table with extracted data as follows:

PO Lines Report

PO Lines Report

 

Cheers!
Sanjay

Dynamics GP – Parent – Child Apply Script

Often requests are received to compile a list of payments from parent and details of how the payment is applied.  The below script extracts the parent payment  and child apply to information.

DECLARE @CUSTNUMBR AS VARCHAR(21) 
DECLARE @FROMDTE AS DATETIME 
DECLARE @TODTE AS DATETIME 

SELECT @CUSTNUMBR = 'CUSTOMERNUMBER' 
SELECT @FROMDTE = CONVERT(DATETIME, 'YYYY-MM-DD 00:00:00', 102) 
SELECT @TODTE = CONVERT(DATETIME, 'YYYY-MM-DD 00:00:00', 102) 

SELECT custnmbr, 
       cprcstnm, 
       trxsorce, 
       glpostdt, 
       posted, 
       aptodcnm, 
       aptodcdt, 
       aptodcty, 
       curncyid, 
       apptoamt, 
       apfrdcnm, 
       apfrdcty, 
       fromcurr 
FROM   rm20201 
WHERE  apfrdcnm IN (SELECT docnumbr 
                    FROM   rm20101 
                    WHERE  ( custnmbr = @CUSTNUMBR ) 
                           AND ( glpostdt >= @FROMDTE ) 
                           AND ( glpostdt <= @todate ))

 

Cheers!
Sanjay

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)))

SSRS – Report – Job Link

SELECT 

      c.Name AS ReportName

      , rs.ScheduleID AS JOB_NAME

      , s.[Description]

      , s.LastStatus

      , s.LastRunTime

FROM 

      ReportServer..[Catalog] c 

      JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID 

      JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID

      AND rs.SubscriptionID = s.SubscriptionID

Cheers!
Sanjay

TSQL script to retreive last two sale date for each item class with transposed columns.

Today received a request to set up a flash report as follows:

FlashReport

The report is a flash report, meaning it  should popup on demand when requested.  My initial approach was to group by item class and get the max date in the first run and then add a sub query to do same excluding the max date, which meant there would be two scans, obviously  with large number of records, it was very slow.   It was evident that the double group by was not the solution as there were two scans, the solution should be having the ability to select top two records for a virtual group.  The final script which did the task is as follows:

SELECT itmclscd, 
       [1] AS LastPurchaseDate, 
       [2] AS SecondLastPurchaseDate 
FROM   (SELECT docdate, 
               rownumber, 
               itmclscd 
        FROM   (SELECT Row_number() 
                         OVER ( 
                           partition BY custnmbr, itmclscd 
                           ORDER BY docdate DESC) AS rownumber 
                       itmclscd, 
                       custnmbr, 
                       docdate 
                FROM   (SELECT dbo.iv00101.itmclscd, 
                               dbo.sop30200.docdate, 
                               dbo.sop30200.custnmbr 
                        FROM   dbo.sop30300 WITH (nolock) 
                               INNER JOIN dbo.iv00101 WITH (nolock) 
                                       ON dbo.sop30300.itemnmbr = 
                                          dbo.iv00101.itemnmbr 
                               INNER JOIN dbo.sop30200 WITH (nolock) 
                                       ON dbo.sop30300.soptype = 
                                          dbo.sop30200.soptype 
                                          AND dbo.sop30300.sopnumbe = 
                                              dbo.sop30200.sopnumbe 
                        WHERE  dbo.sop30200.voidstts = 0 
                               AND dbo.sop30200.soptype = 3 
                               AND Year(docdate) > @Year
                               AND dbo.sop30200.custnmbr = @CUSTNMBR 
                        GROUP  BY dbo.iv00101.itmclscd, 
                                  dbo.sop30200.docdate, 
                                  dbo.sop30200.custnmbr) Level1) Level2 
        WHERE  rownumber <=2) p 
       PIVOT ( Max(docdate) 
             FOR rownumber IN ([1], 
                               [2]) ) AS level3 
ORDER  BY itmclscd

The key section of the code was:

1) Level 1 group by : Getting the data grouped by itmclscd,  docdate, custnmbr which creates a unique combination for the group by fields.

2) Level 2 (select) :
SELECT Row_number()
OVER (
partition BY custnmbr, itmclscd
ORDER BY docdate DESC
)   AS ‘rownumber’

This section of the TSQL assigns row numbers for each block for  custnmbr,itmclscd  which is ordered by docdate.

3)Level 3 where clause : where rownumber <=2 ensure that only top 2 records are selected

and finally setting up the pivot section to transpose the data at level 3.

It was fun setting up the query, which is much faster than the group by with sub query approach.

Cheers!
Sanjay

Un even alert scheduling

Today got an unusual request, “I want to know how many users are logged into Dynamics GP  at 10 A.M., 1 P.M. and 4 P.M. daily for next two week.  The problem with this request  was on how to schedule the alert.
Solution:

  1. Created an TSQL SQL Agent job to fire every one hour between 9:30 AM to 6:00 PM week days and set the start and end dates at SSMS agent

    TSQL Agent Job

    TSQL Agent Job

  2. Controlled the email delivery through this TSQL
    IF (SELECT DATEPART(HOUR,GETDATE())) in (10,13,16)
    BEGIN
    EXECUTE msdb.dbo.sp_send_dbmail
        @subject = 'Concurrent User count',
        @recipients = 'email1@domain.com;emai2@domain.com',
        @query = 'select cast(COUNT(*)as varchar) +
        '' users currently logged in at '' + 
        cast( getdate()  as varchar)  from dynamics..ACTIVITY'
    END

    Cheers!
    Sanjay

    PS: dbmail should be configured for SQL2008 R2 instance.  For SQL 2000 xp send mail can be used.

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