This SQL will just find all the items from inventory, which are not used as a BOM components. The SQL is not tested and might have typos.
SELECT Inventor.Status,Inventor.Uniq_key,;
IIF(Inventor.Part_sourc="CONSG",CustPartNo,Part_No) AS Part_no,;
IIF(Inventor.Part_sourc="CONSG",CustRev,Revision) AS Revision,;
Part_class,Part_type,Descript,StdCost,Part_sourc,Buyer_type as
Buyer,;
Custno,U_of_Meas,SUM(InvtMfgr.Qty_oh) AS
TotQty_Oh,ROUND(SUM(InvtMfgr.Qty_oh * StdCost),2) AS Ext_cost ;
FROM Inventor, InvtMfgr ;
WHERE Part_sourc<>"MAKE" ;
AND InvtMfgr.Uniq_Key == Inventor.Uniq_Key ;
AND Inventor.Uniq_key NOT IN;
(SELECT Uniq_key FROM Bom_det ;
WHERE IIF(EMPTY(Eff_dt) AND
EMPTY(Term_dt),.T.,IIF(!EMPTY(Eff_dt) AND !EMPTY(Term_dt),Eff_dt<=Date() AND
Term_dt>Date(),IIF(EMPTY(Eff_dt),Term_dt>Date(),Eff_dt<=Date()))));
AND !Invtmfgr.Is_Deleted ;
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12;
ORDER BY Part_class,Part_type,3,4;
INTO CURSOR Zinvrep
|