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