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