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          (round((coitem_qtyord * coitem_qty_invuomratio) *
  35                      (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
  36           round((coitem_qtyord * coitem_qty_invuomratio) *
  37                      (coitem_unitcost / coitem_price_invuomratio), 2)) AS margin,
  38          CASE WHEN (coitem_price > 0.0) THEN
  39            (round((coitem_qtyord * coitem_qty_invuomratio) *
  40                        (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
  41             round((coitem_qtyord * coitem_qty_invuomratio) *
  42                        (coitem_unitcost / coitem_price_invuomratio), 2)) /
  43             round((coitem_qtyord * coitem_qty_invuomratio) *
  44                        (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2)
  45               ELSE 0.0
  46          END AS marginpercent,
  47          curr_abbr AS currAbbr,
  48 -- TODO - not needed, remove? (very slow)
  49 --         CASE WHEN (qtyAvailable(itemsite_id, coitem_scheddate) < 0.0) THEN 'error'
  50 --              WHEN itemsite_useparams AND (qtyAvailable(itemsite_id, coitem_scheddate) <= itemsite_reorderlevel) THEN 'warning'
  51 --              WHEN NOT itemsite_useparams AND (qtyAvailable(itemsite_id, coitem_scheddate) <= 0.0) THEN 'warning'
  52 --         END AS qtyavailable_qtforegroundrole,
  53        'qty' AS coitem_qtyord_xtnumericrole,
  54        'qty' AS coitem_qtyshipped_xtnumericrole,
  55        'qty' AS coitem_qtyreturned_xtnumericrole,
  56        'qty' AS qtyatshipping_xtnumericrole,
  57        'qty' AS qtybalance_xtnumericrole,
  58        'qty' AS qtyavailable_xtnumericrole,
  59        'salesprice' AS coitem_price_xtnumericrole,
  60        'salesprice' AS baseunitprice_xtnumericrole,
  61        'cost' AS coitem_unitcost_xtnumericrole,
  62        'curr' AS extprice_xtnumericrole,
  63        'curr' AS extcost_xtnumericrole,
  64        'curr' AS margin_xtnumericrole,
  65        'percent' AS marginpercent_xtnumericrole,
  66        'curr' AS baseextprice_xtnumericrole,
  67        'curr' AS extpricebalance_xtnumericrole,
  68        'curr' AS baseextpricebalance_xtnumericrole,
  69        0 AS coitem_qtyord_xttotalrole,
  70        0 AS coitem_qtyshipped_xttotalrole,
  71        0 AS coitem_qtyreturned_xttotalrole,
  72        0 AS qtybalance_xttotalrole,
  73        0 AS baseextprice_xttotalrole,
  74        0 AS baseextpricebalance_xttotalrole
  75   FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
  76               JOIN custinfo ON (cust_id=cohead_cust_id)
  77               JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
  78               JOIN site() ON (warehous_id=itemsite_warehous_id)
  79               JOIN item ON (item_id=itemsite_item_id)
  80               JOIN uom ON (uom_id=coitem_qty_uom_id)
  81               JOIN curr_symbol ON (curr_id=cohead_curr_id)
  82 <? foreach("char_id_text_list") ?>
  83      LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?>
  84           ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='SO')
  85          AND  (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=cohead_id)
  86          AND  (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
  87      LEFT OUTER JOIN char char_alias<? literal("char_id_text_list") ?>
  88           ON (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=char_alias<? literal("char_id_text_list") ?>.char_id)
  89 <? endforeach ?>
  90 <? foreach("char_id_list_list") ?>
  91      LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?>
  92           ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='SO')
  93          AND  (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=cohead_id)
  94          AND  (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
  95      LEFT OUTER JOIN char char_alias<? literal("char_id_list_list") ?>
  96           ON (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=char_alias<? literal("char_id_list_list") ?>.char_id)
  97 <? endforeach ?>
  98 <? foreach("char_id_date_list") ?>
  99      LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?>
 100           ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='SO')
 101          AND  (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=cohead_id)
 102          AND  (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
 103      LEFT OUTER JOIN char char_alias<? literal("char_id_date_list") ?>
 104           ON (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=char_alias<? literal("char_id_date_list") ?>.char_id)
 105 <? endforeach ?>
 106   WHERE ( (coitem_status<>'X')
 107   <? if exists("cohead_id") ?>
 108     AND  (coitem_cohead_id=<? value("cohead_id") ?>)
 109   <? endif ?>
 110   <? if exists("openOnly") ?>
 111     AND  (coitem_status<>'C')
 112   <? endif ?>
 113   <? if exists("startDate") ?>
 114     AND  (cohead_orderdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
 115   <? endif ?>
 116
 117   <? if exists("salesrep_id") ?>
 118     AND  (cohead_salesrep_id=<? value("salesrep_id") ?>)
 119   <? endif ?>
 120   <? if exists("shipto_id") ?>
 121     AND  (cohead_shipto_id=<? value("shipto_id") ?>)
 122   <? endif ?>
 123   <? if exists("cust_id") ?>
 124     AND  (cohead_cust_id=<? value("cust_id") ?>)
 125   <? elseif exists("custtype_id") ?>
 126     AND  (cust_custtype_id=<? value("custtype_id") ?>)
 127   <? elseif exists("custtype_pattern") ?>
 128     AND  (cust_custtype_id IN (SELECT DISTINCT custtype_id
 129                                FROM custtype
 130                                WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
 131   <? elseif exists("custgrp") ?>
 132     AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 133                      FROM custgrpitem))
 134   <? elseif exists("custgrp_id") ?>
 135     AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 136                      FROM custgrpitem
 137                      WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
 138   <? elseif exists("custgrp_pattern") ?>
 139     AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 140                      FROM custgrp, custgrpitem
 141                      WHERE ( (custgrpitem_custgrp_id=custgrp_id)
 142                        AND   (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
 143   <? endif ?>
 144
 145   <? if exists("item_id") ?>
 146     AND  (itemsite_item_id=<? value("item_id") ?>)
 147   <? elseif exists("prodcat_id") ?>
 148     AND (item_prodcat_id=<? value("prodcat_id") ?>)
 149   <? elseif exists("prodcat_pattern") ?>
 150     AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
 151                              FROM prodcat
 152                              WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
 153   <? endif ?>
 154
 155   <? if exists("warehous_id") ?>
 156     AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 157   <? endif ?>
 158   <? literal("charClause") ?>
 159         )
 160
 161 <? if exists("orderByScheddate") ?>
 162   ORDER BY sort_scheddate, cohead_number, coitem_linenumber, coitem_subnumber
 163 <? elseif exists("orderByOrderdate") ?>
 164   ORDER BY cohead_orderdate, cohead_number, coitem_linenumber, coitem_subnumber
 165 <? else ?>
 166   ORDER BY cohead_number, coitem_linenumber, coitem_subnumber
 167 <? endif ?>
 168 ;