-- Group: unusedPurchasedItems
-- Name: detail
-- Notes: used by dspUnusedPurchasedItems.cpp
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

SELECT DISTINCT item_id, item_number,
                (item_descrip1 || ' ' || item_descrip2) AS itemdescrip, uom_name,
                SUM(itemsite_qtyonhand) AS qoh,
                MAX(itemsite_datelastcount) AS lastcount,
                MAX(itemsite_datelastused) AS lastused,
                'qty' AS qoh_xtnumericrole,
                CASE WHEN (COALESCE(MAX(itemsite_datelastcount), startOfTime()) = startOfTime()) THEN 'Never' END AS lastcount_qtdisplayrole,
                CASE WHEN (COALESCE(MAX(itemsite_datelastused), startOfTime()) = startOfTime()) THEN 'Never' END AS lastused_qtdisplayrole 
FROM item, itemsite, uom 
WHERE ((itemsite_item_id=item_id)
 AND (item_inv_uom_id=uom_id)
 AND (item_id NOT IN (SELECT DISTINCT bomitem_item_id FROM bomitem))
 AND (NOT item_sold)
 AND (item_type IN ('P', 'O')) 
<? if exists("classcode_id") ?>
 AND (item_classcode_id=<? value("classcode_id") ?>) 
<? elseif exists("classcode_pattern") ?>
 AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value("classcode_pattern") ?>))) 
<? endif ?>
<? if exists("includeUncontrolled") ?>
 AND (itemsite_controlmethod <> 'N')
<? endif ?>
) 
GROUP BY item_id, item_number, uom_name, item_descrip1, item_descrip2 
ORDER BY item_number;
