-- Group: workOrderHistory
-- Name: detail
-- Notes: used by dspWoHistoryByNumber, dspWoHistoryByItem, dspWoHistoryByClassCode
-- 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
<? if exists("woHistoryByClassCode") ?>
       wo_id, formatWoNumber(wo_id) AS wonumber, wo_status,
       wo_qtyord, wo_qtyrcv, wo_postedvalue,
       wo_startdate, wo_duedate,
<? elseif exists("woHistoryByItem") ?>
       wo_id, formatWoNumber(wo_id) AS wonumber, wo_status,
       wo_qtyord, wo_qtyrcv, wo_postedvalue,
       wo_startdate, wo_duedate,
<? else ?>
       wo.*, COALESCE((prj_number || ' ' || prj_name), '') AS project,
  <? if exists("revControl") ?>
       COALESCE(bom.rev_number, '') AS bom_rev_number,
       COALESCE(boo.rev_number, '') AS boo_rev_number,
  <? endif ?>
       'cost' AS wo_wipvalue_xtnumericrole,
<? endif ?>
<? if not exists("woHistoryByItem") ?>
       item_number, (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
       item_descrip1, item_descrip2, uom_name,
<? endif ?>
       warehous_code,
       formatDate(wo_startdate) AS f_wo_startdate,
       formatDate(wo_duedate) AS f_wo_duedate,
       formatQty(wo_qtyord) AS f_wo_qtyord,
       formatQty(wo_qtyrcv) AS f_wo_qtyrcv,
     <? if exists("showCosts") ?>
       formatCost(wo_postedvalue) AS f_wo_postedvalue,
     <? endif ?>
       'qty' AS wo_qtyord_xtnumericrole,
       'qty' AS wo_qtyrcv_xtnumericrole,
       'cost' AS wo_postedvalue_xtnumericrole
FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
        JOIN whsinfo ON (warehous_id=itemsite_warehous_id)
<? if not exists("woHistoryByItem") ?>
        JOIN item ON (item_id=itemsite_item_id)
        JOIN uom ON (uom_id=item_inv_uom_id)
<? endif ?>
<? if exists("revControl") ?>
        LEFT OUTER JOIN rev bom ON (bom.rev_id=wo_bom_rev_id)
        LEFT OUTER JOIN rev boo ON (boo.rev_id=wo_boo_rev_id)
<? endif ?>
        LEFT OUTER JOIN prj ON (prj_id=wo_prj_id)
WHERE ((true)
<? if exists("woHistoryByItem") ?>
   AND (itemsite_item_id=<? value("item_id") ?>)
   AND (wo_duedate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
   AND (itemsite_warehous_id=<? value("warehous_id") ?>)
<? endif ?>
<? if exists("wo_number") ?>
   AND (CAST(wo_number AS TEXT) ~ <? value("wo_number") ?>)
<? endif ?>
<? if exists("woHistoryByClassCode") ?>
   AND (itemsite_item_id=item_id)
   AND (wo_duedate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
  <? 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 ?>
<? endif ?>
<? if exists("showOnlyTopLevel") ?>
   AND ((wo_ordtype<>'W') OR (wo_ordtype IS NULL))
<? endif ?>
      )

<? if exists("woHistoryByItem") ?>
  ORDER BY wo_startdate DESC, wo_number, wo_subnumber
<? elseif exists("woHistoryByClassCode") ?>
  ORDER BY item_number
<? else ?>
  ORDER BY wo_number, wo_subnumber
<? endif ?>
;
