-- Group: poItems
-- Name: detail
-- Notes: used by dspPoItemsByVendor, dspPoItemsByItem, dspPoItemsByDate
-- 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 pohead_id, poitem_id, pohead_number, poitem_status,
       CASE WHEN(poitem_status='C') THEN <? value("closed") ?>
            WHEN(poitem_status='U') THEN <? value("unposted") ?>
            WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) 
                                   AND (poitem_qty_ordered>(poitem_qty_received-poitem_qty_returned))) 
            THEN <? value("partial") ?>
            WHEN(poitem_status='O' AND ((poitem_qty_received-poitem_qty_returned) > 0) 
                                   AND (poitem_qty_ordered<=(poitem_qty_received-poitem_qty_returned))) 
            THEN <? value("received") ?>
            WHEN(poitem_status='O') THEN <? value("open") ?>
         ELSE poitem_status
       END AS f_poitem_status,
       vend_name,
       (SELECT warehous_code
          FROM whsinfo
         WHERE (COALESCE(itemsite_warehous_id, pohead_warehous_id)=warehous_id)) AS warehous_code,
       COALESCE(item_number, <? value("nonInv") ?>) AS itemnumber,
       poitem_vend_item_number, poitem_vend_uom,
       COALESCE(item_descrip1, firstLine(poitem_vend_item_descrip)) AS itemdescrip,
       COALESCE(uom_name, poitem_vend_uom) AS itemuom,
       poitem_duedate, poitem_qty_ordered, poitem_qty_received, poitem_qty_returned,
       CASE WHEN (poitem_duedate < CURRENT_DATE) THEN 'error' END AS poitem_duedate_qtforegroundrole,
       'qty' AS poitem_qty_ordered_xtnumericrole,
       'qty' AS poitem_qty_received_xtnumericrole,
       'qty' AS poitem_qty_returned_xtnumericrole 
  FROM pohead
  JOIN poitem   ON (pohead_id=poitem_pohead_id)
  JOIN vendinfo ON (pohead_vend_id=vend_id)
  LEFT OUTER JOIN (itemsite
                   JOIN item ON (itemsite_item_id=item_id)
                   JOIN uom ON (item_inv_uom_id=uom_id))
         ON (poitem_itemsite_id=itemsite_id)
WHERE (TRUE
<? if exists("byDate") ?>
   AND (poitem_duedate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
  AND (((itemsite_id IS NULL) AND
        (pohead_warehous_id=<? value("warehous_id") ?>) )
       OR
       ((itemsite_id IS NOT NULL) AND
        (itemsite_warehous_id=<? value("warehous_id") ?>) ) )
<? endif ?>
<? if exists("agentUsername") ?>
   AND (pohead_agent_username=<? value("agentUsername") ?>)
<? endif ?>
<? if exists("openItems") ?>
   AND (poitem_status='O')
<? endif ?>
<? if exists("closedItems") ?>
   AND (poitem_status='C')
<? endif ?>
<? if exists("poNumber") ?>
  AND (pohead_number=<? value("poNumber") ?>)
<? endif ?>
<? if exists("vend_id") ?>
  AND (pohead_vend_id=<? value("vend_id") ?>) 
<? elseif exists("vendtype_id") ?>
  AND (vend_vendtype_id=<? value("vendtype_id") ?>)
<? elseif exists("vendtype_pattern") ?>
  AND (vend_vendtype_id IN (SELECT vendtype_id
                              FROM vendtype
                             WHERE (vendtype_code ~ <? value("vendtype_pattern") ?>)))
<? endif ?>
<? if exists("item_id") ?>
  AND (itemsite_item_id=<? value("item_id") ?>) 
<? endif ?>
      )
ORDER BY poitem_duedate, pohead_number, poitem_linenumber
;
