-- Group: openpurchaseorders
-- 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 DISTINCT
       pohead_id AS id, COALESCE(pohead_cohead_id, -1) AS altid,
       pohead.*,
       calcPurchaseOrderDueDate(pohead_id) AS min_duedate,
       calcPurchaseOrderAmt(pohead_id) AS order_total,
       vend_name, vend_number, vendtype_code,
<? foreach("char_id_text_list") ?>
       charass_alias<? literal("char_id_text_list") ?>.charass_value AS char<? literal("char_id_text_list") ?>,
<? endforeach ?>
<? foreach("char_id_list_list") ?>
       charass_alias<? literal("char_id_list_list") ?>.charass_value AS char<? literal("char_id_list_list") ?>,
<? endforeach ?>
<? foreach("char_id_date_list") ?>
       charass_alias<? literal("char_id_date_list") ?>.charass_value::date AS char<? literal("char_id_date_list") ?>,
<? endforeach ?>
       CASE WHEN(pohead_status='C') THEN <? value("closed") ?>
            WHEN(pohead_status='U') THEN <? value("unposted") ?>
            WHEN(pohead_status='O') THEN <? value("open") ?>
            ELSE pohead_status
       END AS pohead_status_qtdisplayrole,
       'currency' AS order_total_xtnumericrole
FROM pohead 
     JOIN vendinfo ON (pohead_vend_id=vend_id)
     JOIN vendtype ON (vend_vendtype_id=vendtype_id)
<? if exists("pohead_agent_usr_id") ?>
     JOIN usr ON (pohead_agent_username=usr_username)
<? endif ?> 
<? if exists("warehous_id") ?>
     JOIN poitem ON (poitem_pohead_id=pohead_id) 
     JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
<? endif ?> 
<? foreach("char_id_text_list") ?>
     LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?> 
          ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='PO') 
         AND  (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=pohead_id)
         AND  (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
     LEFT OUTER JOIN char char_alias<? literal("char_id_text_list") ?> 
          ON (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=char_alias<? literal("char_id_text_list") ?>.char_id)
<? endforeach ?>
<? foreach("char_id_list_list") ?>
     LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?> 
          ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='PO') 
         AND  (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=pohead_id)
         AND  (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
     LEFT OUTER JOIN char char_alias<? literal("char_id_list_list") ?> 
          ON (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=char_alias<? literal("char_id_list_list") ?>.char_id)
<? endforeach ?>
<? foreach("char_id_date_list") ?>
     LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?> 
          ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='PO') 
         AND  (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=pohead_id)
         AND  (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
     LEFT OUTER JOIN char char_alias<? literal("char_id_date_list") ?> 
          ON (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=char_alias<? literal("char_id_date_list") ?>.char_id)
<? endforeach ?>
WHERE ( true
<? if exists("search_pattern") ?> 
  AND ((pohead_number::text ~* <? value("search_pattern") ?>) 
    OR (vend_number ~* <? value("search_pattern") ?>)
    OR (vend_name ~* <? value("search_pattern") ?>)
    OR (pohead_agent_username ~* <? value("search_pattern") ?>)
    OR (vendtype_code ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vend_cntct_first_name,'') || ' ' || COALESCE(pohead_vend_cntct_last_name,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vend_cntct_phone,'') ||' ' || COALESCE(pohead_vend_cntct_fax,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vend_cntct_email,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vendaddress1,'') || ' ' || COALESCE(pohead_vendaddress2,'') || ' ' || COALESCE(pohead_vendaddress3,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vendcity,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vendstate,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vendzipcode,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_vendcountry,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shipto_cntct_first_name,'') || ' ' || COALESCE(pohead_shipto_cntct_last_name,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shipto_cntct_phone,'') || ' ' || COALESCE(pohead_shipto_cntct_fax,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shipto_cntct_email,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shiptoaddress1,'') || ' ' || COALESCE(pohead_vendaddress2,'') || ' ' || COALESCE(pohead_vendaddress3,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shiptocity,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shiptostate,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shiptozipcode,'') ~* <? value("search_pattern") ?>)
    OR (COALESCE(pohead_shiptocountry,'') ~* <? value("search_pattern") ?>)
    )
<? endif ?> 
<? if exists("showUnreleased") ?> 
  AND (pohead_status ='U') 
<? endif ?> 
<? if exists("showOpen") ?>
  AND (pohead_status='O' )
<? endif ?> 
<? if exists("showBoth") ?> 
  AND (pohead_status IN ('U', 'O') ) 
<? endif ?> 
<? if exists("shownothing") ?> 
  AND (pohead_status NOT IN ('U', 'O', 'C')) 
<? endif ?> 
<? if exists("vend_id") ?>
  AND (vend_id=<? value("vend_id") ?>)
<? endif ?> 
<? if exists("vendtype_id") ?>
  AND (vend_vendtype_id=<? value("vendtype_id") ?>)
<? endif ?> 
<? if exists("vendtype_pattern") ?>
  AND (vendtype_code ~* <? value("vendtype_pattern") ?>)
<? endif ?> 
<? if exists("pohead_agent_usr_id") ?>
  AND (usr_id  = <? value("pohead_agent_usr_id") ?>)
<? endif ?> 
<? if exists("warehous_id") ?>
  AND (itemsite_warehous_id = <? value("warehous_id") ?>)
<? endif ?> 
<? literal("charClause") ?>
 ) 
ORDER BY pohead_number;
