-- Group: desktop
-- Name:  purchOpen
-- Notes: 
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

SELECT pohead_id, pohead_number, vend_number, vend_name, pohead_shipvia, 
  formatCntctName(pohead_vend_cntct_honorific, 
                  pohead_vend_cntct_first_name, 
                  pohead_vend_cntct_middle, 
                  pohead_vend_cntct_last_name, 
                  pohead_vend_cntct_suffix) AS vend_cntct, 
  pohead_vend_cntct_phone,
  formatCntctName(pohead_shipto_cntct_honorific, 
                  pohead_shipto_cntct_first_name, 
                  pohead_shipto_cntct_middle, 
                  pohead_shipto_cntct_last_name, 
                  pohead_shipto_cntct_suffix) AS shipto_cntct, 
  pohead_shipto_cntct_phone,
  min(poitem_duedate)  AS duedate, pohead_status AS status,
  CASE WHEN (pohead_status = 'O') THEN <? value("open") ?>
  ELSE <? value("unreleased") ?> END AS status_qtdisplayrole,
  SUM(round(noNeg(poitem_qty_ordered - poitem_qty_received + poitem_qty_returned) *            
        currToBase(pohead_curr_id, poitem_unitprice, pohead_orderdate), 2) ) AS amount,
  CASE WHEN (min(poitem_duedate) <= current_date) THEN 'error' 
  ELSE 'emphasis' END AS duedate_qtforegroundrole,
  'curr' AS amount_xtnumericrole
FROM pohead 
  JOIN vendinfo ON (pohead_vend_id=vend_id) 
  JOIN poitem ON (poitem_pohead_id=pohead_id) 
WHERE (poitem_status IN ('U','O'))
GROUP BY pohead_id, pohead_number, pohead_status, vend_number, vend_name, pohead_shipvia, pohead_vend_cntct_id, 
  pohead_shipto_cntct_id, pohead_vend_cntct_phone, pohead_shipto_cntct_phone, pohead_vend_cntct_honorific,   
  pohead_vend_cntct_first_name,  pohead_vend_cntct_middle, pohead_vend_cntct_last_name, pohead_vend_cntct_suffix,
  pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name, pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name, 
  pohead_shipto_cntct_suffix
ORDER BY pohead_number;	
