The following is an SQL statement that could be setup to check all items that have NOT been ordered in the past 60 days, create a list of these items and trigger an e-mail to be sent to someone within the company so they can review the list and determine when to go back to a customer and charge for excess inventory.
SELECT distinct Customer.custname, Inventor.part_no, Inventor.revision FROM manex!somain INNER JOIN manex!sodetail ON Somain.sono = Sodetail.sono INNER JOIN manex!inventor ON Sodetail.uniq_key = Inventor.uniq_key INNER JOIN manex!customer ON Somain.custno = Customer.custno WHERE sodetail.balance=0 AND Sodetail.status='Closed' AND Somain.Custno+Sodetail.Uniq_key NOT IN (SELECT S2.CustNO+SD2.Uniq_key FROM Somain S2,Sodetail SD2 WHERE S2.Ord_type<>'Cancel' AND SD2.Status<>'Closed' AND Sd2.Status<>'Cancel' AND Sd2.Balance<>0 AND DATE()- S2.orderdate<=60)
|