git.roojs.org Git - xtuple/blob - foundation-database/public/views/saleshistory.sql

   1 SELECT dropIfExists('view', 'saleshistory');
   2 CREATE VIEW saleshistory AS
   3 SELECT *,
   4        COALESCE(item_number, cohist_misc_descrip) AS itemnumber,
   5        (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
   6        round((cohist_qtyshipped * cohist_unitprice), 2) AS extprice,
   7        round((cohist_qtyshipped * baseunitprice), 2) AS baseextprice,
   8        round((cohist_qtyshipped * custunitprice), 2) AS custextprice,
   9        round((cohist_qtyshipped * cohist_unitcost), 4) AS extcost,
  10        round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) AS margin,
  11        CASE WHEN (cohist_qtyshipped * baseunitprice > 0.0) THEN
  12             (round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) /
  13              round((cohist_qtyshipped * baseunitprice), 2))
  14             ELSE 0.0
  15        END AS marginpercent,
  16        currConcat(cohist_curr_id) AS currAbbr,
  17        'Return'::TEXT AS cohist_invcdate_xtnullrole,
  18        'qty'::TEXT AS cohist_qtyshipped_xtnumericrole,
  19        'salesprice'::TEXT AS cohist_unitprice_xtnumericrole,
  20        'salesprice'::TEXT AS baseunitprice_xtnumericrole,
  21        'curr'::TEXT AS custunitprice_xtnumericrole,
  22        'curr'::TEXT AS custextprice_xtnumericrole,
  23        'curr'::TEXT AS extprice_xtnumericrole,
  24        'curr'::TEXT AS baseextprice_xtnumericrole,
  25        'cost'::TEXT AS cohist_unitcost_xtnumericrole,
  26        'curr'::TEXT AS extcost_xtnumericrole,
  27        'curr'::TEXT AS margin_xtnumericrole,
  28        'percent'::TEXT AS marginpercent_xtnumericrole,
  29        'curr'::TEXT AS cohist_commission_xtnumericrole,
  30        'curr'::TEXT AS basecommission_xtnumericrole
  31 FROM (
  32 SELECT *,
  33        currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
  34        currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
  35        currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate) AS custunitprice
  36 FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
  37             JOIN custtype ON (custtype_id=cust_custtype_id)
  38             JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
  39             LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
  40             LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
  41             LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
  42             LEFT OUTER JOIN prodcat ON (prodcat_id=item_prodcat_id)
  43             LEFT OUTER JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
  44             LEFT OUTER JOIN shipzone ON (shipzone_id=shipto_shipzone_id)
  45             LEFT OUTER JOIN saletype ON (saletype_id=cohist_saletype_id)
  46      ) AS data;
  47
  48 REVOKE ALL ON TABLE saleshistory FROM PUBLIC;
  49 GRANT  ALL ON TABLE saleshistory TO GROUP xtrole;
  50
  51 COMMENT ON VIEW saleshistory IS 'Single point for sales history calculations.'
  52 ;