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