-- Group: bookings
-- 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   coitem_id AS id, coitem_cohead_id AS altId,
         formatSoLineNumber(coitem_id) AS f_linenumber,
         getSoSchedDate(cohead_id) AS sort_scheddate,
         coitem.*, cohead.*,
         cust_number, cust_name,
         item_number, item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
         uom_name,
         warehous_code,
         qtyAtShipping(coitem_id) AS qtyatshipping,
-- TODO - not needed, remove? (very slow)
--         qtyAvailable(itemsite_id, coitem_scheddate) AS qtyavailable,
--         (qtyAvailable(itemsite_id, coitem_scheddate) < 0.0) AS stockout,
--         CASE WHEN (itemsite_useparams) THEN (qtyAvailable(itemsite_id, coitem_scheddate) <= itemsite_reorderlevel)
--              ELSE (qtyAvailable(itemsite_id, coitem_scheddate) <= 0.0)
--         END AS reorder,
         noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS qtybalance,
         currtobase(cohead_curr_id, coitem_price, cohead_orderdate) AS baseunitprice,
         round((coitem_qtyord * coitem_qty_invuomratio) *
                     (coitem_price / coitem_price_invuomratio), 2) AS extprice,
         round((coitem_qtyord * coitem_qty_invuomratio) *
                     (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) AS baseextprice,
         round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *
                     (coitem_price / coitem_price_invuomratio), 2) AS extpricebalance,
         round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *
                     (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) AS baseextpricebalance,
         round((coitem_qtyord * coitem_qty_invuomratio) *
                     (coitem_unitcost / coitem_price_invuomratio), 2) AS extcost,
         CASE WHEN (coitem_price = 0.0) THEN 0.0
              ELSE ROUND(coitem_qtyord * coitem_qty_invuomratio * (coitem_price - coitem_unitcost) / coitem_price_invuomratio,2)
         END AS margin,
         CASE WHEN (coitem_price = 0.0) THEN 0.0
              ELSE ((coitem_price - coitem_unitcost) / coitem_price)
         END AS marginpercent,
         curr_abbr AS currAbbr,
<? 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 ?>
-- TODO - not needed, remove? (very slow)
--         CASE WHEN (qtyAvailable(itemsite_id, coitem_scheddate) < 0.0) THEN 'error'
--              WHEN itemsite_useparams AND (qtyAvailable(itemsite_id, coitem_scheddate) <= itemsite_reorderlevel) THEN 'warning'
--              WHEN NOT itemsite_useparams AND (qtyAvailable(itemsite_id, coitem_scheddate) <= 0.0) THEN 'warning'
--         END AS qtyavailable_qtforegroundrole,
       'qty' AS coitem_qtyord_xtnumericrole,
       'qty' AS coitem_qtyshipped_xtnumericrole,
       'qty' AS coitem_qtyreturned_xtnumericrole,
       'qty' AS qtyatshipping_xtnumericrole,
       'qty' AS qtybalance_xtnumericrole,
       'qty' AS qtyavailable_xtnumericrole,
       'salesprice' AS coitem_price_xtnumericrole,
       'salesprice' AS baseunitprice_xtnumericrole,
       'cost' AS coitem_unitcost_xtnumericrole,
       'curr' AS extprice_xtnumericrole,
       'curr' AS extcost_xtnumericrole,
       'curr' AS margin_xtnumericrole,
       'percent' AS marginpercent_xtnumericrole,
       'curr' AS baseextprice_xtnumericrole,
       'curr' AS extpricebalance_xtnumericrole,
       'curr' AS baseextpricebalance_xtnumericrole,
       0 AS coitem_qtyord_xttotalrole,
       0 AS coitem_qtyshipped_xttotalrole,
       0 AS coitem_qtyreturned_xttotalrole,
       0 AS qtybalance_xttotalrole,
       0 AS baseextprice_xttotalrole,
       0 AS baseextpricebalance_xttotalrole
  FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
              JOIN custinfo ON (cust_id=cohead_cust_id)
              JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
              JOIN site() ON (warehous_id=itemsite_warehous_id)
              JOIN item ON (item_id=itemsite_item_id)
              JOIN uom ON (uom_id=coitem_qty_uom_id)
              JOIN curr_symbol ON (curr_id=cohead_curr_id)
<? 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='SO') 
         AND  (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=cohead_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='SO') 
         AND  (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=cohead_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='SO') 
         AND  (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=cohead_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 ( (coitem_status<>'X')
  <? if exists("cohead_id") ?>
    AND  (coitem_cohead_id=<? value("cohead_id") ?>)
  <? endif ?>
  <? if exists("openOnly") ?>
    AND  (coitem_status<>'C')
  <? endif ?>
  <? if exists("startDate") ?>
    AND  (cohead_orderdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
  <? endif ?>

  <? if exists("salesrep_id") ?>
    AND  (cohead_salesrep_id=<? value("salesrep_id") ?>)
  <? endif ?>
  <? if exists("shipto_id") ?>
    AND  (cohead_shipto_id=<? value("shipto_id") ?>)
  <? endif ?>
  <? if exists("cust_id") ?>
    AND  (cohead_cust_id=<? value("cust_id") ?>)
  <? elseif exists("custtype_id") ?>
    AND  (cust_custtype_id=<? value("custtype_id") ?>)
  <? elseif exists("custtype_pattern") ?>
    AND  (cust_custtype_id IN (SELECT DISTINCT custtype_id
                               FROM custtype
                               WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
  <? elseif exists("custgrp") ?>
    AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
                     FROM custgrpitem))
  <? elseif exists("custgrp_id") ?>
    AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
                     FROM custgrpitem
                     WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
  <? elseif 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  (itemsite_item_id=<? value("item_id") ?>)
  <? elseif exists("prodcat_id") ?>
    AND (item_prodcat_id=<? value("prodcat_id") ?>)
  <? elseif exists("prodcat_pattern") ?>
    AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
                             FROM prodcat
                             WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
  <? endif ?>

  <? if exists("warehous_id") ?>
    AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
  <? endif ?>
  <? literal("charClause") ?>
        )

<? if exists("orderByScheddate") ?>
  ORDER BY sort_scheddate, cohead_number, coitem_linenumber, coitem_subnumber
<? elseif exists("orderByOrderdate") ?>
  ORDER BY cohead_orderdate, cohead_number, coitem_linenumber, coitem_subnumber
<? else ?>
  ORDER BY cohead_number, coitem_linenumber, coitem_subnumber
<? endif ?>
;
