From e093b77a52e57b8e32edbccd1478edf458851b0e Mon Sep 17 00:00:00 2001 From: garyhgohoos Date: Tue, 29 Jul 2014 16:30:06 -0400 Subject: [PATCH] Issue #24224:normalize margin calculations --- .../public/functions/calcinvoiceamt.sql | 2 +- .../public/functions/calcsalesorderamt.sql | 2 +- .../public/tables/metasql/bookings-detail.mql | 19 +++++++ .../metasql/briefSalesHistory-detail.mql | 34 +++++++++---- .../public/tables/metasql/invoices-detail.mql | 5 ++ .../tables/metasql/opensalesorders-detail.mql | 7 ++- .../tables/metasql/salesHistory-detail.mql | 51 +++++-------------- .../public/views/saleshistory.sql | 51 ++++++++++--------- 8 files changed, 94 insertions(+), 77 deletions(-) diff --git a/foundation-database/public/functions/calcinvoiceamt.sql b/foundation-database/public/functions/calcinvoiceamt.sql index fb79c3f73..a60452dfd 100644 --- a/foundation-database/public/functions/calcinvoiceamt.sql +++ b/foundation-database/public/functions/calcinvoiceamt.sql @@ -37,7 +37,7 @@ BEGIN 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) diff --git a/foundation-database/public/functions/calcsalesorderamt.sql b/foundation-database/public/functions/calcsalesorderamt.sql index 0f4def933..cf11caba8 100644 --- a/foundation-database/public/functions/calcsalesorderamt.sql +++ b/foundation-database/public/functions/calcsalesorderamt.sql @@ -41,7 +41,7 @@ BEGIN 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) diff --git a/foundation-database/public/tables/metasql/bookings-detail.mql b/foundation-database/public/tables/metasql/bookings-detail.mql index 621440373..28a6f0840 100644 --- a/foundation-database/public/tables/metasql/bookings-detail.mql +++ b/foundation-database/public/tables/metasql/bookings-detail.mql @@ -29,6 +29,21 @@ SELECT coitem_id AS id, coitem_cohead_id AS altId, (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' @@ -43,7 +58,11 @@ SELECT coitem_id AS id, coitem_cohead_id AS altId, '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, diff --git a/foundation-database/public/tables/metasql/briefSalesHistory-detail.mql b/foundation-database/public/tables/metasql/briefSalesHistory-detail.mql index 8dc7a257b..b79e35321 100644 --- a/foundation-database/public/tables/metasql/briefSalesHistory-detail.mql +++ b/foundation-database/public/tables/metasql/briefSalesHistory-detail.mql @@ -7,15 +7,28 @@ 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 + 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 AND ) @@ -60,7 +73,8 @@ WHERE ((cohist_invcdate BETWEEN AND ) ) -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; diff --git a/foundation-database/public/tables/metasql/invoices-detail.mql b/foundation-database/public/tables/metasql/invoices-detail.mql index 67ee698f4..20b36482b 100644 --- a/foundation-database/public/tables/metasql/invoices-detail.mql +++ b/foundation-database/public/tables/metasql/invoices-detail.mql @@ -29,11 +29,16 @@ SELECT invchead_id, cust_id, invchead_invcnumber AS docnumber, findCustomerForm(cust_id, 'I') AS reportname, + 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 diff --git a/foundation-database/public/tables/metasql/opensalesorders-detail.mql b/foundation-database/public/tables/metasql/opensalesorders-detail.mql index 81385aa52..bb5808447 100644 --- a/foundation-database/public/tables/metasql/opensalesorders-detail.mql +++ b/foundation-database/public/tables/metasql/opensalesorders-detail.mql @@ -20,12 +20,17 @@ SELECT DISTINCT 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) diff --git a/foundation-database/public/tables/metasql/salesHistory-detail.mql b/foundation-database/public/tables/metasql/salesHistory-detail.mql index 50da06fb9..c427dea0e 100644 --- a/foundation-database/public/tables/metasql/salesHistory-detail.mql +++ b/foundation-database/public/tables/metasql/salesHistory-detail.mql @@ -4,58 +4,29 @@ -- 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 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, 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, - currConcat(cohist_curr_id) AS currAbbr, 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) - - 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) - - JOIN itemsite ON (itemsite_id=cohist_itemsite_id) - JOIN site() ON (warehous_id=itemsite_warehous_id) - JOIN item ON (item_id=itemsite_item_id) - + FROM saleshistory JOIN cohead ON (cohead_number=cohist_ordernumber) @@ -63,6 +34,8 @@ WHERE ( (true) AND (COALESCE(cohist_misc_type, '') <> 'F') AND (COALESCE(cohist_misc_type, '') <> 'T') + + AND (warehous_id IS NOT NULL) AND (cohist_invcdate >= ) diff --git a/foundation-database/public/views/saleshistory.sql b/foundation-database/public/views/saleshistory.sql index 7db4ed234..d648700eb 100644 --- a/foundation-database/public/views/saleshistory.sql +++ b/foundation-database/public/views/saleshistory.sql @@ -1,21 +1,18 @@ 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, @@ -27,22 +24,26 @@ SELECT cohist.*, '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; -- 2.39.2