-- Group: costelem
-- Name:  unusedbyitem
-- Notes: list the cost elements that can be applied to an item that have
--        not already been applied to that item. takes an item_id and type.
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

<? if exists("item_id") ?>

SELECT costelem_id, costelem_type
FROM costelem
WHERE ((costelem_active)
   AND (NOT costelem_sys
        OR (CASE WHEN fetchMetricBool('Routings') AND
                     (<? value("itemtype") ?> IN ('M', 'F', 'B', 'C', 'T')) THEN
               costelem_type IN ('Direct Labor', 'Overhead', 'Machine Overhead')
                 WHEN (<? value("itemtype") ?> IN ('O', 'P')) THEN
                                                   costelem_type IN ('Material')
            END))
   AND (costelem_id NOT IN (SELECT itemcost_costelem_id
                            FROM itemcost
                            WHERE ((NOT itemcost_lowlevel)
                               AND (itemcost_item_id=<? value("item_id") ?>))))
      )
ORDER BY costelem_type;

<? elseif exists("bomitem_id") ?>

SELECT costelem_id, costelem_type
FROM costelem
WHERE ((costelem_active)
   AND (NOT costelem_sys
        OR (CASE WHEN fetchMetricBool('Routings') AND
                     (<? value("itemtype") ?> IN ('M', 'F', 'B', 'C', 'T')) THEN
               costelem_type IN ('Direct Labor', 'Overhead', 'Machine Overhead')
                 WHEN (<? value("itemtype") ?> IN ('O', 'P')) THEN
                                                   costelem_type IN ('Material')
            END))
   AND (costelem_id NOT IN (SELECT bomitemcost_costelem_id
                            FROM bomitemcost
                            WHERE ((NOT bomitemcost_lowlevel)
                               AND (bomitemcost_bomitem_id=<? value("bomitem_id") ?>))))
      )
ORDER BY costelem_type;


<? endif ?>
