-- Group: salesHistory
-- 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 *,
       CASE WHEN (COALESCE(cohist_invcnumber, '-1') IN ('', '-1')) THEN  <? value("credit") ?>
            ELSE cohist_invcnumber
       END AS invoicenumber,
<? if exists("isReport") ?>
       formatDate(cohist_orderdate) AS f_orderdate,
       formatDate(cohist_invcdate) AS f_invcdate,
       formatQty(cohist_qtyshipped) AS f_qtyshipped,
       formatBoolYN(cohist_commissionpaid) AS f_commissionpaid,
       formatSalesPrice(baseunitprice) AS f_baseunitprice,
       formatMoney(extprice) AS f_extprice,
       formatMoney(baseextprice) AS f_baseextprice,
       formatMoney(extcost) AS f_extcost,
       formatMoney(margin) AS f_margin,
       formatPrcnt(marginpercent) AS f_marginpercent,
       formatMoney(basecommission) AS f_basecommission,
<? endif ?>
       <? value("return") ?> AS cohist_invcdate_xtnullrole,
       0 AS cohist_qtyshipped_xttotalrole,
       0 AS baseextprice_xttotalrole,
       0 AS extcost_xttotalrole,
       0 AS margin_xttotalrole,
       0 AS basecommission_xttotalrole
<? if exists("includeMisc") ?>
  FROM saleshistorymisc
<? else ?>
  FROM saleshistory
<? endif ?>
WHERE ( (true)
<? if exists("startDate") ?>
  AND  (cohist_invcdate >= <? value("startDate") ?>)
<? endif ?>
<? if exists("endDate") ?>
  AND  (cohist_invcdate <= <? value("endDate") ?>)
<? endif ?>
<? if exists("shipStartDate") ?>
  AND  (cohist_shipdate >= <? value("shipStartDate") ?>)
<? endif ?>
<? if exists("shipEndDate") ?>
  AND  (cohist_shipdate <= <? value("shipEndDate") ?>)
<? endif ?>
<? if exists("salesrep_id") ?>
  AND  (cohist_salesrep_id=<? value("salesrep_id") ?>)
<? endif ?>
<? if exists("shipto_id") ?>
  AND  (cohist_shipto_id=<? value("shipto_id") ?>)
<? endif ?>
<? if exists("billToName") ?>
  AND  (UPPER(cohist_billtoname) ~ UPPER(<? value("billToName") ?>))
<? endif ?>
<? if exists("cust_id") ?>
  AND  (cohist_cust_id=<? value("cust_id") ?>)
<? endif ?>
<? if exists("custtype_id") ?>
  AND  (custtype_id=<? value("custtype_id") ?>)
<? elseif exists("custtype_pattern") ?>
  AND  (custtype_id IN (SELECT DISTINCT custtype_id
                        FROM custtype
                        WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
<? endif ?>
<? if exists("by_custgrp") ?>
  AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
                   FROM custgrpitem))
<? endif ?>
<? if exists("custgrp_id") ?>
  AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
                   FROM custgrpitem
                   WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
<? endif ?>
<? if exists("custgrp_pattern") ?>
  AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
                   FROM custgrp, custgrpitem
                   WHERE ( (custgrpitem_custgrp_id=custgrp_id)
                     AND   (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
<? endif ?>

<? if exists("item_id") ?>
  AND  (item_id=<? value("item_id") ?>)
<? endif ?>
<? if exists("item_pattern") ?>
  AND  (itemnumber ~* <? value("item_pattern") ?>)
<? endif ?>
<? if exists("prodcat_id") ?>
  AND (prodcat_id=<? value("prodcat_id") ?>)
<? endif ?>
<? if exists("prodcat_pattern") ?>
  AND (prodcat_id IN (SELECT DISTINCT prodcat_id
                      FROM prodcat
                      WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
<? endif ?>
<? if exists("classcode_id") ?>
  AND (classcode_id=<? value("classcode_id") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
  AND  (warehous_id=<? value("warehous_id") ?>)
<? endif ?>
<? if exists("shipzone_id") ?>
  AND (cohist_shipzone_id=<? value("shipzone_id") ?>)
<? endif ?>
<? if exists("saletype_id") ?>
  AND (cohist_saletype_id=<? value("saletype_id") ?>)
<? endif ?>
<? if exists("cohead_id") ?>
  AND (cohead_id=<? value("cohead_id") ?>)
<? endif ?>
      )
ORDER BY cohist_invcdate, itemnumber;

