WHERE (invcitem_invchead_id=pInvcheadid);
IF (pType IN ('T', 'X')) THEN
- SELECT SUM(tax) INTO _tax
+ SELECT COALESCE(SUM(tax), 0.0) INTO _tax
FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
FROM tax
JOIN calculateTaxDetailSummary('I', pInvcheadid, 'T')ON (taxdetail_tax_id=tax_id)
AND (coitem_status != 'X');
IF (pType IN ('T', 'B', 'X')) THEN
- SELECT SUM(tax) INTO _tax
+ SELECT COALESCE(SUM(tax), 0.0) INTO _tax
FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
FROM tax
JOIN calculateTaxDetailSummary('S', pCoheadid, 'T')ON (taxdetail_tax_id=tax_id)
(coitem_price / coitem_price_invuomratio), 2) AS extpricebalance,
round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *
(currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) AS baseextpricebalance,
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (coitem_unitcost / coitem_price_invuomratio), 2) AS extcost,
+ (round((coitem_qtyord * coitem_qty_invuomratio) *
+ (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (coitem_unitcost / coitem_price_invuomratio), 2)) AS margin,
+ CASE WHEN (coitem_price > 0.0) THEN
+ (round((coitem_qtyord * coitem_qty_invuomratio) *
+ (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (coitem_unitcost / coitem_price_invuomratio), 2)) /
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2)
+ ELSE 0.0
+ END AS marginpercent,
curr_abbr AS currAbbr,
-- TODO - not needed, remove? (very slow)
-- CASE WHEN (qtyAvailable(itemsite_id, coitem_scheddate) < 0.0) THEN 'error'
'qty' AS qtyavailable_xtnumericrole,
'salesprice' AS coitem_price_xtnumericrole,
'salesprice' AS baseunitprice_xtnumericrole,
+ 'cost' AS coitem_unitcost_xtnumericrole,
'curr' AS extprice_xtnumericrole,
+ 'curr' AS extcost_xtnumericrole,
+ 'curr' AS margin_xtnumericrole,
+ 'percent' AS marginpercent_xtnumericrole,
'curr' AS baseextprice_xtnumericrole,
'curr' AS extpricebalance_xtnumericrole,
'curr' AS baseextpricebalance_xtnumericrole,
SELECT
COALESCE(cohead_id,-1) AS cohead_id, cust_id, cohist_ponumber,
cust_custtype_id, custtype_code,
- cohist_cust_id, SUM(extcost) AS extcost,
+ cohist_cust_id, cohist_ordernumber,
+ cust_number, cust_name, currAbbr,
+ CASE WHEN (cohist_invcnumber='-1') THEN <? value("credit") ?>
+ ELSE cohist_invcnumber
+ END AS invoicenumber,
+ cohist_orderdate, cohist_invcdate,
+ SUM(extprice) AS extprice,
+ SUM(baseextprice) AS baseextprice,
+ SUM(extcost) AS extcost,
+ SUM(margin) AS margin,
+ CASE WHEN (SUM(baseextprice) > 0.0) THEN
+ ROUND(SUM(margin) / SUM(baseextprice), 4)
+ ELSE 0.0
+ END AS marginpercent,
+ 'curr' AS extprice_xtnumericrole,
+ 'curr' AS baseextprice_xtnumericrole,
'curr' AS extcost_xtnumericrole,
+ 'curr' AS margin_xtnumericrole,
+ 'percent' AS marginpercent_xtnumericrole,
+ 0 AS baseextprice_xttotalrole,
0 AS extcost_xttotalrole,
- cohist_ordernumber,
- cust_number, cust_name, invoicenumber,
- cohist_orderdate, cohist_invcdate,
- SUM(baseextprice) AS extended,
- 'curr' AS extended_xtnumericrole,
- 0 AS extended_xttotalrole
+ 0 AS margin_xttotalrole
FROM saleshistory
LEFT OUTER JOIN cohead ON (cohead_number=cohist_ordernumber)
WHERE ((cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
<? endif ?>
)
-GROUP BY cohead_id, cust_id,cust_number,cust_custtype_id,cohist_cust_id,custtype_code,
- cust_name,cohist_ordernumber, cohist_ponumber,
- invoicenumber,cohist_orderdate, cohist_invcdate
+GROUP BY cohead_id, cust_id, cust_number, cust_custtype_id,
+ cohist_cust_id, custtype_code, currAbbr,
+ cust_name, cohist_ordernumber, cohist_ponumber,
+ cohist_invcnumber, cohist_orderdate, cohist_invcdate
ORDER BY cohist_invcdate, cohist_orderdate;
invchead_invcnumber AS docnumber,
findCustomerForm(cust_id, 'I') AS reportname,
<? endif ?>
+ CASE WHEN (calcInvoiceAmt(invchead_id,'S') != 0.0)
+ THEN calcInvoiceAmt(invchead_id,'M')
+ ELSE 0.0
+ END AS margin,
CASE WHEN (calcInvoiceAmt(invchead_id,'S') != 0.0)
THEN (calcInvoiceAmt(invchead_id,'M') / calcInvoiceAmt(invchead_id,'S'))
ELSE 1.0
END AS marginpercent,
'percent' AS marginpercent_xtnumericrole,
+ 'curr' AS margin_xtnumericrole,
'curr' AS extprice_xtnumericrole,
'curr' AS balance_xtnumericrole,
CASE WHEN (aropen_id IS NULL) THEN 'Unposted' END AS balance_qtdisplayrole
<? endforeach ?>
firstline(cohead_ordercomments) AS notes,
calcSalesOrderAmt(cohead_id) AS ordertotal,
+ CASE WHEN (calcSalesOrderAmt(cohead_id,'S') != 0.0)
+ THEN calcSalesOrderAmt(cohead_id,'M')
+ ELSE 0.0
+ END AS ordermargin,
CASE WHEN (calcSalesOrderAmt(cohead_id,'S') != 0.0)
THEN (calcSalesOrderAmt(cohead_id,'M') / calcSalesOrderAmt(cohead_id,'S'))
ELSE 1.0
END AS ordermarginpercent,
'percent' AS ordermarginpercent_xtnumericrole,
- 'extprice' AS ordertotal_xtnumericrole
+ 'extprice' AS ordertotal_xtnumericrole,
+ 'extprice' AS ordermargin_xtnumericrole
FROM cohead
JOIN custinfo ON (cohead_cust_id=cust_id)
JOIN custtype ON (cust_custtype_id=custtype_id)
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
-SELECT cohist.*,
+SELECT *,
CASE WHEN (cohist_invcnumber='-1') THEN <? value("credit") ?>
ELSE cohist_invcnumber
END AS invoicenumber,
- cust_number, cust_name, salesrep_name,
- saletype_code, shipzone_name,
- item_id, COALESCE(item_number, cohist_misc_descrip) AS item_number,
- item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
- warehous_code,
- currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
- round((cohist_qtyshipped * cohist_unitprice), 2) AS extprice,
- round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS baseextprice,
- round((cohist_qtyshipped * cohist_unitcost), 4) AS extcost,
- currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
<? if exists("isReport") ?>
formatDate(cohist_invcdate) AS f_invcdate,
formatQty(cohist_qtyshipped) AS f_qtyshipped,
formatBoolYN(cohist_commissionpaid) AS f_commissionpaid,
- formatSalesPrice(currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)) AS f_baseunitprice,
- formatMoney(round((cohist_qtyshipped * cohist_unitprice), 2)) AS f_extprice,
- formatMoney(round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2)) AS f_baseextprice,
- formatMoney(round((cohist_qtyshipped * cohist_unitcost), 4)) AS f_extcost,
- formatMoney(currtobase(cohist_curr_id, cohist_commission, cohist_invcdate)) AS f_basecommission,
+ formatSalesPrice(baseunitprice) AS f_baseunitprice,
+ formatMoney(extprice) AS f_extprice,
+ formatMoney(baseextprice) AS f_baseextprice,
+ formatMoney(extcost) AS f_extcost,
+ formatMoney(margin) AS f_margin,
+ formatPercent(marginpercent) AS f_marginpercent,
+ formatMoney(basecommission) AS f_basecommission,
<? endif ?>
- currConcat(cohist_curr_id) AS currAbbr,
<? value("return") ?> AS cohist_invcdate_xtnullrole,
- 'qty' AS cohist_qtyshipped_xtnumericrole,
- 'salesprice' AS cohist_unitprice_xtnumericrole,
- 'salesprice' AS baseunitprice_xtnumericrole,
- 'curr' AS extprice_xtnumericrole,
- 'curr' AS baseextprice_xtnumericrole,
- 'cost' AS cohist_unitcost_xtnumericrole,
- 'curr' AS extcost_xtnumericrole,
- 'curr' AS cohist_commission_xtnumericrole,
- 'curr' AS basecommission_xtnumericrole,
0 AS cohist_qtyshipped_xttotalrole,
0 AS baseextprice_xttotalrole,
0 AS extcost_xttotalrole,
+ 0 AS margin_xttotalrole,
0 AS basecommission_xttotalrole
- FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
- JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
- LEFT OUTER JOIN saletype ON (saletype_id=cohist_saletype_id)
- LEFT OUTER JOIN shipzone ON (shipzone_id=cohist_shipzone_id)
-<? if exists("includeMisc") ?>
- LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
- LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
- LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
-<? else ?>
- JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
- JOIN site() ON (warehous_id=itemsite_warehous_id)
- JOIN item ON (item_id=itemsite_item_id)
-<? endif ?>
+ FROM saleshistory
<? if exists("cohead_id") ?>
JOIN cohead ON (cohead_number=cohist_ordernumber)
<? endif ?>
<? if exists("includeMisc") ?>
AND (COALESCE(cohist_misc_type, '') <> 'F')
AND (COALESCE(cohist_misc_type, '') <> 'T')
+<? else ?>
+ AND (warehous_id IS NOT NULL)
<? endif ?>
<? if exists("startDate") ?>
AND (cohist_invcdate >= <? value("startDate") ?>)
SELECT dropIfExists('view', 'saleshistory');
CREATE VIEW saleshistory AS
-SELECT cohist.*,
- CASE WHEN (cohist_invcnumber='-1') THEN 'Credit'
- ELSE cohist_invcnumber
- END AS invoicenumber,
- cust_id, cust_number, cust_name, cust_curr_id, cust_custtype_id, custtype_code, custtype_descrip,
- salesrep_number, salesrep_name, shipzone_id, shipzone_name, shipzone_descrip,
- itemsite_warehous_id, itemsite_item_id,
- item_id, item_number, item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
- item_prodcat_id, warehous_code, warehous_descrip, prodcat_code,
- currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
- currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
- currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate) AS custunitprice,
+SELECT *,
+ COALESCE(item_number, cohist_misc_descrip) AS itemnumber,
+ (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
round((cohist_qtyshipped * cohist_unitprice), 2) AS extprice,
- round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS baseextprice,
- round((cohist_qtyshipped * currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS custextprice,
+ round((cohist_qtyshipped * baseunitprice), 2) AS baseextprice,
+ round((cohist_qtyshipped * custunitprice), 2) AS custextprice,
round((cohist_qtyshipped * cohist_unitcost), 4) AS extcost,
+ round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) AS margin,
+ CASE WHEN (cohist_qtyshipped * baseunitprice > 0.0) THEN
+ (round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) /
+ round((cohist_qtyshipped * baseunitprice), 2))
+ ELSE 0.0
+ END AS marginpercent,
currConcat(cohist_curr_id) AS currAbbr,
'Return'::TEXT AS cohist_invcdate_xtnullrole,
'qty'::TEXT AS cohist_qtyshipped_xtnumericrole,
'curr'::TEXT AS baseextprice_xtnumericrole,
'cost'::TEXT AS cohist_unitcost_xtnumericrole,
'curr'::TEXT AS extcost_xtnumericrole,
+ 'curr'::TEXT AS margin_xtnumericrole,
+ 'percent'::TEXT AS marginpercent_xtnumericrole,
'curr'::TEXT AS cohist_commission_xtnumericrole,
- 'curr'::TEXT AS basecommission_xtnumericrole,
- 0 AS cohist_qtyshipped_xttotalrole,
- 0 AS custextprice_xttotalrole,
- 0 AS baseextprice_xttotalrole,
- 0 AS extcost_xttotalrole,
- 0 AS basecommission_xttotalrole
+ 'curr'::TEXT AS basecommission_xtnumericrole
+FROM (
+SELECT *,
+ currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
+ currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
+ currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate) AS custunitprice
FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
JOIN custtype ON (custtype_id=cust_custtype_id)
JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
- JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
- JOIN site() ON (warehous_id=itemsite_warehous_id)
- JOIN item ON (item_id=itemsite_item_id)
- JOIN prodcat ON (prodcat_id=item_prodcat_id)
+ LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
+ LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
+ LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
+ LEFT OUTER JOIN prodcat ON (prodcat_id=item_prodcat_id)
LEFT OUTER JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
- LEFT OUTER JOIN shipzone ON (shipzone_id=shipto_shipzone_id);
+ LEFT OUTER JOIN shipzone ON (shipzone_id=shipto_shipzone_id)
+ LEFT OUTER JOIN saletype ON (saletype_id=cohist_saletype_id)
+ ) AS data;
REVOKE ALL ON TABLE saleshistory FROM PUBLIC;
GRANT ALL ON TABLE saleshistory TO GROUP xtrole;
projects / xtuple / commitdiff