git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/bookings-detail.mql

   1 -- Group: bookings
   2 -- Name: detail
   3 -- Notes:
   4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6
   7 SELECT   coitem_id AS id, coitem_cohead_id AS altId,
   8          formatSoLineNumber(coitem_id) AS f_linenumber,
   9          getSoSchedDate(cohead_id) AS sort_scheddate,
  10          coitem.*, cohead.*,
  11          cust_number, cust_name,
  12          item_number, item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
  13          uom_name,
  14          warehous_code,
  15          qtyAtShipping(coitem_id) AS qtyatshipping,
  16 -- TODO - not needed, remove? (very slow)
  17 --         qtyAvailable(itemsite_id, coitem_scheddate) AS qtyavailable,
  18 --         (qtyAvailable(itemsite_id, coitem_scheddate) < 0.0) AS stockout,
  19 --         CASE WHEN (itemsite_useparams) THEN (qtyAvailable(itemsite_id, coitem_scheddate) <= itemsite_reorderlevel)
  20 --              ELSE (qtyAvailable(itemsite_id, coitem_scheddate) <= 0.0)
  21 --         END AS reorder,
  22          noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS qtybalance,
  23          currtobase(cohead_curr_id, coitem_price, cohead_orderdate) AS baseunitprice,
  24          round((coitem_qtyord * coitem_qty_invuomratio) *
  25                      (coitem_price / coitem_price_invuomratio), 2) AS extprice,
  26          round((coitem_qtyord * coitem_qty_invuomratio) *
  27                      (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) AS baseextprice,
  28          round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *
  29                      (coitem_price / coitem_price_invuomratio), 2) AS extpricebalance,
  30          round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *
  31                      (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) AS baseextpricebalance,
  32          round((coitem_qtyord * coitem_qty_invuomratio) *
  33                      (coitem_unitcost / coitem_price_invuomratio), 2) AS extcost,
  34          CASE WHEN (coitem_price = 0.0) THEN 0.0
  35               ELSE ROUND(coitem_qtyord * coitem_qty_invuomratio * (coitem_price - coitem_unitcost) / coitem_price_invuomratio,2)
  36          END AS margin,
  37          CASE WHEN (coitem_price = 0.0) THEN 0.0
  38               ELSE ((coitem_price - coitem_unitcost) / coitem_price)
  39          END AS marginpercent,
  40          curr_abbr AS currAbbr,
  41 -- TODO - not needed, remove? (very slow)
  42 --         CASE WHEN (qtyAvailable(itemsite_id, coitem_scheddate) < 0.0) THEN 'error'
  43 --              WHEN itemsite_useparams AND (qtyAvailable(itemsite_id, coitem_scheddate) <= itemsite_reorderlevel) THEN 'warning'
  44 --              WHEN NOT itemsite_useparams AND (qtyAvailable(itemsite_id, coitem_scheddate) <= 0.0) THEN 'warning'
  45 --         END AS qtyavailable_qtforegroundrole,
  46        'qty' AS coitem_qtyord_xtnumericrole,
  47        'qty' AS coitem_qtyshipped_xtnumericrole,
  48        'qty' AS coitem_qtyreturned_xtnumericrole,
  49        'qty' AS qtyatshipping_xtnumericrole,
  50        'qty' AS qtybalance_xtnumericrole,
  51        'qty' AS qtyavailable_xtnumericrole,
  52        'salesprice' AS coitem_price_xtnumericrole,
  53        'salesprice' AS baseunitprice_xtnumericrole,
  54        'cost' AS coitem_unitcost_xtnumericrole,
  55        'curr' AS extprice_xtnumericrole,
  56        'curr' AS extcost_xtnumericrole,
  57        'curr' AS margin_xtnumericrole,
  58        'percent' AS marginpercent_xtnumericrole,
  59        'curr' AS baseextprice_xtnumericrole,
  60        'curr' AS extpricebalance_xtnumericrole,
  61        'curr' AS baseextpricebalance_xtnumericrole,
  62        0 AS coitem_qtyord_xttotalrole,
  63        0 AS coitem_qtyshipped_xttotalrole,
  64        0 AS coitem_qtyreturned_xttotalrole,
  65        0 AS qtybalance_xttotalrole,
  66        0 AS baseextprice_xttotalrole,
  67        0 AS baseextpricebalance_xttotalrole
  68   FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
  69               JOIN custinfo ON (cust_id=cohead_cust_id)
  70               JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
  71               JOIN site() ON (warehous_id=itemsite_warehous_id)
  72               JOIN item ON (item_id=itemsite_item_id)
  73               JOIN uom ON (uom_id=coitem_qty_uom_id)
  74               JOIN curr_symbol ON (curr_id=cohead_curr_id)
  75 <? foreach("char_id_text_list") ?>
  76      LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?>
  77           ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='SO')
  78          AND  (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=cohead_id)
  79          AND  (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
  80      LEFT OUTER JOIN char char_alias<? literal("char_id_text_list") ?>
  81           ON (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=char_alias<? literal("char_id_text_list") ?>.char_id)
  82 <? endforeach ?>
  83 <? foreach("char_id_list_list") ?>
  84      LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?>
  85           ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='SO')
  86          AND  (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=cohead_id)
  87          AND  (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
  88      LEFT OUTER JOIN char char_alias<? literal("char_id_list_list") ?>
  89           ON (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=char_alias<? literal("char_id_list_list") ?>.char_id)
  90 <? endforeach ?>
  91 <? foreach("char_id_date_list") ?>
  92      LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?>
  93           ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='SO')
  94          AND  (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=cohead_id)
  95          AND  (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
  96      LEFT OUTER JOIN char char_alias<? literal("char_id_date_list") ?>
  97           ON (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=char_alias<? literal("char_id_date_list") ?>.char_id)
  98 <? endforeach ?>
  99   WHERE ( (coitem_status<>'X')
 100   <? if exists("cohead_id") ?>
 101     AND  (coitem_cohead_id=<? value("cohead_id") ?>)
 102   <? endif ?>
 103   <? if exists("openOnly") ?>
 104     AND  (coitem_status<>'C')
 105   <? endif ?>
 106   <? if exists("startDate") ?>
 107     AND  (cohead_orderdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
 108   <? endif ?>
 109
 110   <? if exists("salesrep_id") ?>
 111     AND  (cohead_salesrep_id=<? value("salesrep_id") ?>)
 112   <? endif ?>
 113   <? if exists("shipto_id") ?>
 114     AND  (cohead_shipto_id=<? value("shipto_id") ?>)
 115   <? endif ?>
 116   <? if exists("cust_id") ?>
 117     AND  (cohead_cust_id=<? value("cust_id") ?>)
 118   <? elseif exists("custtype_id") ?>
 119     AND  (cust_custtype_id=<? value("custtype_id") ?>)
 120   <? elseif exists("custtype_pattern") ?>
 121     AND  (cust_custtype_id IN (SELECT DISTINCT custtype_id
 122                                FROM custtype
 123                                WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
 124   <? elseif exists("custgrp") ?>
 125     AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 126                      FROM custgrpitem))
 127   <? elseif exists("custgrp_id") ?>
 128     AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 129                      FROM custgrpitem
 130                      WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
 131   <? elseif exists("custgrp_pattern") ?>
 132     AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 133                      FROM custgrp, custgrpitem
 134                      WHERE ( (custgrpitem_custgrp_id=custgrp_id)
 135                        AND   (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
 136   <? endif ?>
 137
 138   <? if exists("item_id") ?>
 139     AND  (itemsite_item_id=<? value("item_id") ?>)
 140   <? elseif exists("prodcat_id") ?>
 141     AND (item_prodcat_id=<? value("prodcat_id") ?>)
 142   <? elseif exists("prodcat_pattern") ?>
 143     AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
 144                              FROM prodcat
 145                              WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
 146   <? endif ?>
 147
 148   <? if exists("warehous_id") ?>
 149     AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 150   <? endif ?>
 151   <? literal("charClause") ?>
 152         )
 153
 154 <? if exists("orderByScheddate") ?>
 155   ORDER BY sort_scheddate, cohead_number, coitem_linenumber, coitem_subnumber
 156 <? elseif exists("orderByOrderdate") ?>
 157   ORDER BY cohead_orderdate, cohead_number, coitem_linenumber, coitem_subnumber
 158 <? else ?>
 159   ORDER BY cohead_number, coitem_linenumber, coitem_subnumber
 160 <? endif ?>
 161 ;