The following applies to companies with a make to stock strategy that generally do not work with requested delivery dates (e.g. has customers who prefer their orders delivered yesterday).

I bet my left spectacles (the ones I keep in my left-hand drawer) that every manager’s favorite report is “Shipped, not invoiced”. Who doesn’t want to know on a daily basis how much revenue is in the pipeline and just a posting run away.

I bet my other pair (from the right-hand side of my desk) that hardly any of them bother with “Sold, not shipped” and not just because there is no such report available.

We often assume that what hasn’t been shipped is simply logistic WIP, but is it? What if we have sufficient on hand inventory and orders are still not being shipped? Then we are dealing with deferred revenue and possibly other issues.

The value of available inventory sold but not shipped is what I like to call the shipping potential. There are various reasons (some valid) why orders are not being shipped, even though there is sufficient inventory on hand. What we need to beware of is having inflated shipping potential. Inflated shipping potential is a clear indicator that the elevator in the warehouse is not going to the top floor. Trouble in the aisles.

Determining the exact value of the shipping potential is actually a little tricky. The below SQL script does the job, but is not 100% accurate, because it specifically excludes scarce items (i.e. items which are available, but not sufficiently to fulfill all order requirements).

The script is however easily modified to produce order lines with scarce items or order lines that have 0-shipping potential. The latter might be an indicator for faulty master planning.

select SALESID
	, SALESSTATUS
	, sl.RECEIPTDATEREQUESTED
	, CUSTACCOUNT
	, REPLACE(DELIVERYNAME, char(10), ' ') as DLVNAME
	, LINENUM
	, ITEMID
	, REPLACE(REPLACE(NAME, char(13), ' '), char(10), ' ') as NAME
	, QTYORDERED
	, SALESUNIT
	, REMAINSALESPHYSICAL
	, COSTPRICE
	, SALESPRICE
	, PRICEUNIT
	, LINEPERCENT
	, LINEAMOUNT
	, ROUND(REMAINSALESPHYSICAL/PRICEUNIT * SALESPRICE * (100-LINEPERCENT)/100, 2) as SHIPVALUE
from SALESLINE sl
where sl.DATAAREAID = 'abcd'	--< REPLACE *****!****
and	  sl.REMAINSALESPHYSICAL > 0
and	  sl.RECEIPTDATEREQUESTED <= GETDATE()
and   sl.ITEMID in
(
	-- ITEMS WITHOUT PICKING LINES THAT HAVE FULL SHIPPING POTENTIAL
	select sl.ITEMID
	from	(
			-- OPEN SALES LINES GROUPED BY ITEM
			select SALESLINE.ITEMID, sum(REMAININVENTPHYSICAL) as REMAIN
			from SALESLINE
				left join WMSORDERTRANS ot on ot.INVENTTRANSID = SALESLINE.INVENTTRANSID and SALESLINE.DATAAREAID = ot.DATAAREAID and ot.EXPEDITIONSTATUS not in (10, 20)
			where SALESLINE.DATAAREAID = 'rsde' --< REPLACE *****!****
			and  SALESSTATUS = 1
			and ot.RECID is null
			group by SALESLINE.ITEMID
			) as sl
	join	(
			-- PHYSICAL INVENTORY GROUPED BY ITEM
			select ITEMID, sum(PHYSICALINVENT) as PHYSICAL
			from INVENTSUM
				join INVENTDIM id on id.INVENTDIMID = INVENTSUM.INVENTDIMID and id.DATAAREAID = 'abcd'	--< REPLACE *****!****
			where inventsum.DATAAREAID = 'abcd' --< REPLACE *****!****
			and id.INVENTLOCATIONID = 'WH01'    --< REPLACE *****!****
 			group by ITEMID
			) as isum on isum.ITEMID = sl.ITEMID
	where isum.PHYSICAL > 0
	and   sl.REMAIN > 0
	and	  sl.REMAIN < isum.PHYSICAL
) 
and sl.SALESSTATUS = 1
order by sl.ITEMID