ArticlesArticles Most Helpful ArticlesMost Helpful Articles Language SettingsLanguage Settings LoginLogin
RSS Feeds
DrillDown Icon Table of Contents Back
 . . . . . . . . . . . . .
DrillDown Icon MX
DrillDown Icon MANUALS and Other Info for SQL MANEX Client Server
DrillDown Icon Customer Relationship Management (CRM)
DrillDown Icon ManEx Minute
DrillDown Icon ManEx Component Exchange
DrillDown Icon ManEx Supplier Directory & Rankings
  Email This ArticlePrint PreviewPrint Current Article and All Sub-Articles
 
How to Setup a Trigger for a List of Assemblies not reordered within 60 Days
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)
 
 
 
 
 
 
Article ID: 3221