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

   1 -- Group: salesHistory
   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 cohist.*,
   8        CASE WHEN (cohist_invcnumber='-1') THEN  <? value("credit") ?>
   9             ELSE cohist_invcnumber
  10        END AS invoicenumber,
  11        cust_number, cust_name, salesrep_name,
  12        saletype_code, shipzone_name,
  13        item_id, COALESCE(item_number, cohist_misc_descrip) AS item_number,
  14        item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
  15        warehous_code,
  16        currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
  17        round((cohist_qtyshipped * cohist_unitprice), 2) AS extprice,
  18        round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS baseextprice,
  19        round((cohist_qtyshipped * cohist_unitcost), 4) AS extcost,
  20        currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
  21 <? if exists("isReport") ?>
  22        formatDate(cohist_invcdate) AS f_invcdate,
  23        formatQty(cohist_qtyshipped) AS f_qtyshipped,
  24        formatBoolYN(cohist_commissionpaid) AS f_commissionpaid,
  25        formatSalesPrice(currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)) AS f_baseunitprice,
  26        formatMoney(round((cohist_qtyshipped * cohist_unitprice), 2)) AS f_extprice,
  27        formatMoney(round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2)) AS f_baseextprice,
  28        formatMoney(round((cohist_qtyshipped * cohist_unitcost), 4)) AS f_extcost,
  29        formatMoney(currtobase(cohist_curr_id, cohist_commission, cohist_invcdate)) AS f_basecommission,
  30 <? endif ?>
  31        currConcat(cohist_curr_id) AS currAbbr,
  32        <? value("return") ?> AS cohist_invcdate_xtnullrole,
  33        'qty' AS cohist_qtyshipped_xtnumericrole,
  34        'salesprice' AS cohist_unitprice_xtnumericrole,
  35        'salesprice' AS baseunitprice_xtnumericrole,
  36        'curr' AS extprice_xtnumericrole,
  37        'curr' AS baseextprice_xtnumericrole,
  38        'cost' AS cohist_unitcost_xtnumericrole,
  39        'curr' AS extcost_xtnumericrole,
  40        'curr' AS cohist_commission_xtnumericrole,
  41        'curr' AS basecommission_xtnumericrole,
  42        0 AS cohist_qtyshipped_xttotalrole,
  43        0 AS baseextprice_xttotalrole,
  44        0 AS extcost_xttotalrole,
  45        0 AS basecommission_xttotalrole
  46   FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
  47               JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
  48               LEFT OUTER JOIN saletype ON (saletype_id=cohist_saletype_id)
  49               LEFT OUTER JOIN shipzone ON (shipzone_id=cohist_shipzone_id)
  50 <? if exists("includeMisc") ?>
  51               LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
  52               LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
  53               LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
  54 <? else ?>
  55               JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
  56               JOIN site() ON (warehous_id=itemsite_warehous_id)
  57               JOIN item ON (item_id=itemsite_item_id)
  58 <? endif ?>
  59 <? if exists("cohead_id") ?>
  60               JOIN cohead ON (cohead_number=cohist_ordernumber)
  61 <? endif ?>
  62 WHERE ( (true)
  63 <? if exists("includeMisc") ?>
  64   AND  (COALESCE(cohist_misc_type, '') <> 'F')
  65   AND  (COALESCE(cohist_misc_type, '') <> 'T')
  66 <? endif ?>
  67 <? if exists("startDate") ?>
  68   AND  (cohist_invcdate >= <? value("startDate") ?>)
  69 <? endif ?>
  70 <? if exists("endDate") ?>
  71   AND  (cohist_invcdate <= <? value("endDate") ?>)
  72 <? endif ?>
  73 <? if exists("shipStartDate") ?>
  74   AND  (cohist_shipdate >= <? value("shipStartDate") ?>)
  75 <? endif ?>
  76 <? if exists("shipEndDate") ?>
  77   AND  (cohist_shipdate <= <? value("shipEndDate") ?>)
  78 <? endif ?>
  79 <? if exists("salesrep_id") ?>
  80   AND  (cohist_salesrep_id=<? value("salesrep_id") ?>)
  81 <? endif ?>
  82 <? if exists("shipto_id") ?>
  83   AND  (cohist_shipto_id=<? value("shipto_id") ?>)
  84 <? endif ?>
  85 <? if exists("billToName") ?>
  86   AND  (UPPER(cohist_billtoname) ~ UPPER(<? value("billToName") ?>))
  87 <? endif ?>
  88 <? if exists("cust_id") ?>
  89   AND  (cohist_cust_id=<? value("cust_id") ?>)
  90 <? endif ?>
  91 <? if exists("custtype_id") ?>
  92   AND  (cust_custtype_id=<? value("custtype_id") ?>)
  93 <? elseif exists("custtype_pattern") ?>
  94   AND  (cust_custtype_id IN (SELECT DISTINCT custtype_id
  95                              FROM custtype
  96                              WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
  97 <? endif ?>
  98 <? if exists("by_custgrp") ?>
  99   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 100                    FROM custgrpitem))
 101 <? endif ?>
 102 <? if exists("custgrp_id") ?>
 103   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 104                    FROM custgrpitem
 105                    WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
 106 <? endif ?>
 107 <? if exists("custgrp_pattern") ?>
 108   AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 109                    FROM custgrp, custgrpitem
 110                    WHERE ( (custgrpitem_custgrp_id=custgrp_id)
 111                      AND   (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
 112 <? endif ?>
 113
 114 <? if exists("item_id") ?>
 115   AND  (itemsite_item_id=<? value("item_id") ?>)
 116 <? endif ?>
 117 <? if exists("prodcat_id") ?>
 118   AND (item_prodcat_id=<? value("prodcat_id") ?>)
 119 <? endif ?>
 120 <? if exists("prodcat_pattern") ?>
 121   AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
 122                            FROM prodcat
 123                            WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
 124 <? endif ?>
 125
 126 <? if exists("warehous_id") ?>
 127   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 128 <? endif ?>
 129 <? if exists("shipzone_id") ?>
 130   AND (cohist_shipzone_id=<? value("shipzone_id") ?>)
 131 <? endif ?>
 132 <? if exists("saletype_id") ?>
 133   AND (cohist_saletype_id=<? value("saletype_id") ?>)
 134 <? endif ?>
 135 <? if exists("cohead_id") ?>
 136   AND (cohead_id=<? value("cohead_id") ?>)
 137 <? endif ?>
 138       )
 139 ORDER BY cohist_invcdate, item_number;
 140