-- Group: workOrderSchedule
-- Name:  detail
-- Notes: 
-- 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 wo_id,
       CASE WHEN (wo_ordid IS NULL) THEN -1
            ELSE wo_ordid
       END AS orderid,
       wo.*, warehous_code, uom_name,
       item_number, (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
       item_descrip1, item_descrip2, prj_number,
       CASE wo_ordtype WHEN ('S') THEN formatSoitemNumber(wo_ordid)
                       WHEN ('W') THEN formatWoNumber(wo_ordid)
       END AS parentorder,
       formatQty(wo_qtyord) AS ordered,
       formatQty(wo_qtyrcv) AS received,
       formatDate(wo_startdate) AS startdate,
       formatDate(wo_duedate) AS duedate,
       formatWONumber(wo_id) AS wonumber,
       'qty' AS wo_qtyord_xtnumericrole,
       'qty' AS wo_qtyrcv_xtnumericrole,
       CASE WHEN ((wo_startdate<=CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) THEN 'error'
       END AS wo_startdate_qtforegroundrole,
       CASE WHEN (wo_duedate<=CURRENT_DATE) THEN 'error'
       END AS wo_duedate_qtforegroundrole,
       CASE WHEN (wo_duedate<=CURRENT_DATE) THEN <? value("overdue") ?>
            ELSE <? value("ontime") ?>
       END AS condition,
       CASE WHEN (wo_duedate<=CURRENT_DATE) THEN 'error' END AS condition_qtforegroundrole,
       CASE wo_status WHEN ('O') THEN <? value("open") ?>
                      WHEN ('E') THEN <? value("exploded") ?>
                      WHEN ('R') THEN <? value("released") ?>
                      WHEN ('I') THEN <? value("inprocess") ?>
                      WHEN ('C') THEN <? value("closed") ?>
                      ELSE '?'
       END AS wo_status_qtdisplayrole,
       CASE wo_ordtype WHEN ('')  THEN ''
                       WHEN ('W') THEN <? value("wo") ?>
                       WHEN ('M') THEN <? value("planord") ?>
                       WHEN ('P') THEN <? value("mps") ?>
                       WHEN ('S') THEN <? value("so") ?>
                       ELSE '?'
       END AS wo_ordtype_qtdisplayrole
FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
        JOIN item ON (item_id=itemsite_item_id)
        JOIN uom ON (uom_id=item_inv_uom_id)
        JOIN whsinfo ON (warehous_id=itemsite_warehous_id)
        LEFT OUTER JOIN prj ON (prj_id=wo_prj_id)
<? if reExists("woSoStatus") ?>
        JOIN coitem ON ((coitem_order_id=wo_id) AND (coitem_order_type='W'))
<? endif ?>
WHERE ( (true)
<? if exists("search_pattern") ?>
  AND ( (wo_number::TEXT ~* <? value("search_pattern") ?>)
    OR  (item_number ~* <? value("search_pattern") ?>)
    OR  (item_descrip1 || item_descrip2 ~* <? value("search_pattern") ?>) )
<? endif ?>
<? if exists("woSoStatus") ?>
  AND (coitem_status <> 'X')
<? elseif exists("woSoStatusMismatch") ?>
  AND (coitem_status='C')
<? endif ?>
<? if exists("prj_id") ?>
  AND (wo_prj_id=<? value("prj_id") ?>)
<? endif ?>
<? if exists("startDate") ?>
  AND (wo_startdate >= <? value("startDate") ?>)
<? endif ?>
<? if exists("endDate") ?>
  AND (wo_startdate <= <? value("endDate") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
  AND (itemsite_warehous_id=<? value("warehous_id") ?>)
<? endif ?>
<? if exists("item_id") ?>
  AND (itemsite_item_id=<? value("item_id") ?>)
<? endif ?>
<? if exists("wo_id") ?>
  AND (wo_number IN (SELECT wo_number
                     FROM wo
                     WHERE (wo_id=<? value("wo_id") ?>)))
<? endif ?>
<? if exists("status_list") ?>
<? foreach("status_list") ?>
  <? if isfirst("status_list") ?>
    AND (wo_status=<? value("status_list") ?>
  <? else ?>
    OR wo_status=<? value("status_list") ?>
  <? endif ?>
  <? if islast("status_list") ?>
    )
  <? endif ?>
<? endforeach ?>
<? elseif exists("showOnlyRI") ?>
  AND (wo_status IN ('R','I'))
<? else ?>
  AND (wo_status<>'C')
<? endif ?>
<? if exists("showOnlyTopLevel") ?>
  AND (wo_ordtype<>'W')
<? endif ?>
<? if exists("classcode_id") ?>
  AND (item_classcode_id=<? value("classcode_id") ?>)
<? endif ?>
<? if exists("itemgrp_id") ?>
  AND (item_id IN (SELECT itemgrpitem_item_id
                   FROM itemgrpitem
                   WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
<? endif ?>
<? if exists("plancode_id") ?>
  AND (itemsite_plancode_id=<? value("plancode_id") ?>)
<? endif ?>
<? if exists("wrkcnt_id") ?>
  AND (wo_id IN (SELECT wooper_wo_id
                 FROM wooper
                 WHERE (wooper_wrkcnt_id=<? value("wrkcnt_id") ?>)))
<? endif ?>
<? if exists("classcode_pattern") ?>
  AND (item_classcode_id IN (SELECT classcode_id
                             FROM classcode
                             WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
<? endif ?>
<? if exists("itemgrp_pattern") ?>
  AND (item_id IN (SELECT itemgrpitem_item_id
                   FROM itemgrpitem, itemgrp
                   WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>) ) ))
<? endif ?>
<? if exists("plancode_pattern") ?>
  AND (itemsite_plancode_id IN (SELECT plancode_id
                                FROM plancode
                                WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
<? endif ?>
<? if exists("wrkcnt_pattern") ?>
  AND (wo_id IN (SELECT wooper_wo_id
                 FROM wooper, wrkcnt
                 WHERE ((wooper_wrkcnt_id=wrkcnt_id) AND (wrkcnt_code ~ <? value("wrkcnt_pattern") ?>))))
<? endif ?>
   )
ORDER BY 
<? if exists("sortByStartDate") ?>
  wo_startdate,
<? elseif exists("sortByDueDate") ?>
  wo_duedate,
<? elseif exists("sortByItemNumber") ?>
  item_number,
<? endif ?>
  wo_number, wo_subnumber;
