-- Group: reorderExceptionsByPlannerCode
-- Name: detail
-- Notes: used by dspReorderExceptionsByPlannerCode
--        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 itemsite_id, itemtype, warehous_code, item_number,
       (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
       reorderdate, reorderlevel,
       (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, reorderdate) +
         qtyOrdered(itemsite_id, reorderdate)) AS projavail,
       'qty' AS reorderlevel_xtnumericrole,
       'qty' AS projavail_xtnumericrole
FROM (SELECT itemsite_id,
             CASE WHEN (item_type IN ('M', 'B', 'T')) THEN 1
                  WHEN (item_type IN ('P', 'O')) THEN 2
               ELSE 3
             END AS itemtype,
             warehous_code, item_number, item_descrip1,
             item_descrip2,
             reorderDate(itemsite_id,
                         <? value("lookAheadDays") ?>,
                         <? value("includePlannedOrders") ?>)
             AS reorderdate,
             reorderlevel
      FROM (SELECT itemsite_id, itemsite_item_id,
                   itemsite_warehous_id,
                   CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel
                     ELSE 0.0
                   END AS reorderlevel
            FROM itemsite
            WHERE ((true)
            <? if exists("warehous_id") ?>
               AND (itemsite_warehous_id=<? value("warehous_id") ?>)
            <? endif ?>
            <? if exists("plancode_id") ?>
               AND (itemsite_plancode_id=<? value("plancode_id") ?>)
            <? elseif exists("plancode_pattern") ?>
               AND (itemsite_plancode_id IN (SELECT plancode_id
                                             FROM plancode
                                             WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
            <? endif ?>
                  )
           ) AS itemsitedata, item, whsinfo
      WHERE ((itemsite_item_id=item_id)
         AND (itemsite_warehous_id=warehous_id))
     ) AS data
WHERE (reorderdate IS NOT NULL)
ORDER BY reorderdate, item_number;
