Issue #24111:fix markup pricing and margins with uom conversion

diff --git a/foundation-database/public/functions/itemipsprice.sql b/foundation-database/public/functions/itemipsprice.sql
index f7ef3bc..93bfe05 100644 (file)
--- a/foundation-database/public/functions/itemipsprice.sql
+++ b/foundation-database/public/functions/itemipsprice.sql
@@ -18,7 +18,7 @@ DECLARE
   _item RECORD;
   _cust RECORD;
   _shipto RECORD;
-  _iteminvpricerat NUMERIC := 1.0;
+  _itempricepricerat NUMERIC := 1.0;
   _listprice NUMERIC := 0.0;
   _qty NUMERIC;
   _asof DATE;
@@ -38,7 +38,7 @@ BEGIN
   _asof := COALESCE(pAsOf, CURRENT_DATE);
 
 --  Cache Item, Customer and Shipto
-  SELECT item.*, itemCost(itemsite_id) AS invcost INTO _item
+  SELECT item.*, (itemCost(itemsite_id) / itemuomtouomratio(item_id, item_inv_uom_id, item_price_uom_id)) AS invcost INTO _item
   FROM item LEFT OUTER JOIN itemsite ON (itemsite_item_id=item_id AND itemsite_warehous_id=pSiteid)
   WHERE (item_id=pItemid);
 
@@ -52,7 +52,7 @@ BEGIN
 
 -- Get a value here so we do not have to call the function several times
   SELECT itemuomtouomratio(pItemid, pPriceUOM, _item.item_price_uom_id) AS ratio
-    INTO _iteminvpricerat;
+    INTO _itempricepricerat;
 
 -- First get a sales price if any so we when we find other prices
 -- we can determine if we want that price or this price.
@@ -64,15 +64,15 @@ BEGIN
          CASE WHEN (ipsitem_type = 'N') THEN
                (ipsitem_price * itemuomtouomratio(_item.item_id, pPriceUOM, ipsitem_price_uom_id))
               WHEN (ipsitem_type = 'D') THEN
-               noNeg(_item.item_listprice - (_item.item_listprice * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount) * _iteminvpricerat
+               noNeg(_item.item_listprice - (_item.item_listprice * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount) * _itempricepricerat
               WHEN ((ipsitem_type = 'M') AND _long30markups AND _wholesalepricecosting) THEN
-               (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+               (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
               WHEN ((ipsitem_type = 'M') AND _long30markups) THEN
-               (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+               (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
               WHEN (ipsitem_type = 'M' AND _wholesalepricecosting) THEN
-               (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+               (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
               WHEN (ipsitem_type = 'M') THEN
-               (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+               (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
               ELSE 0.00
          END AS ipsprice_price,
          CASE WHEN (ipsitem_item_id=_item.item_id) THEN itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak)
@@ -115,15 +115,15 @@ BEGIN
            CASE WHEN (ipsitem_type = 'N') THEN
                  (ipsitem_price * itemuomtouomratio(_item.item_id, pPriceUOM, ipsitem_price_uom_id))
                 WHEN (ipsitem_type = 'D') THEN
-                 noNeg(_item.item_listprice - (_item.item_listprice * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount) * _iteminvpricerat
+                 noNeg(_item.item_listprice - (_item.item_listprice * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount) * _itempricepricerat
                 WHEN ((ipsitem_type = 'M') AND _long30markups AND _wholesalepricecosting) THEN
-                 (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+                 (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                 WHEN ((ipsitem_type = 'M') AND _long30markups) THEN
-                 (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+                 (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                 WHEN (ipsitem_type = 'M' AND _wholesalepricecosting) THEN
-                 (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+                 (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                 WHEN (ipsitem_type = 'M') THEN
-                 (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
+                 (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                 ELSE 0.00
            END AS protoprice,
            CASE WHEN (ipsitem_item_id=_item.item_id) THEN itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak)

diff --git a/foundation-database/public/tables/metasql/itemPricingSchedule-detail.mql b/foundation-database/public/tables/metasql/itemPricingSchedule-detail.mql
index 377f256..0ed0c04 100644 (file)
--- a/foundation-database/public/tables/metasql/itemPricingSchedule-detail.mql
+++ b/foundation-database/public/tables/metasql/itemPricingSchedule-detail.mql
@@ -22,11 +22,14 @@ SELECT ipsitem_id AS id, 1 AS altid,
                   WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups') AND fetchMetricBool('WholesalePriceCosting')) THEN
                    (item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                   WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups')) THEN
-                   (itemCost(itemsite_id) / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
+                   (itemCost(itemsite_id) / itemuomtouomratio(item_id, ipsitem_qty_uom_id, ipsitem_price_uom_id) /
+                   (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                   WHEN (ipsitem_type = 'M' AND fetchMetricBool('WholesalePriceCosting')) THEN
                    (item_listcost + (item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                   WHEN (ipsitem_type = 'M') THEN
-                   (itemCost(itemsite_id) + (itemCost(itemsite_id) * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
+                   (itemCost(itemsite_id) / itemuomtouomratio(item_id, ipsitem_qty_uom_id, ipsitem_price_uom_id) +
+                    (itemCost(itemsite_id) / itemuomtouomratio(item_id, ipsitem_qty_uom_id, ipsitem_price_uom_id) * ipsitem_discntprcnt) +
+                    ipsitem_fixedamtdiscount)
                   ELSE 0.0
              END) AS netPrice,
        CASE WHEN (ipsitem_type='N') THEN <? value("nominal") ?>

diff --git a/foundation-database/public/tables/metasql/pricelist-detail.mql b/foundation-database/public/tables/metasql/pricelist-detail.mql
index 2859c48..b679d8e 100644 (file)
--- a/foundation-database/public/tables/metasql/pricelist-detail.mql
+++ b/foundation-database/public/tables/metasql/pricelist-detail.mql
@@ -51,8 +51,12 @@ FROM (
                  (ipsitem_price * itemuomtouomratio(<? value('item_id') ?>, NULL, ipsitem_price_uom_id)) * iteminvpricerat(ipsitem_item_id)
                 WHEN (ipsitem_type = 'D') THEN
                  noNeg(<? value('item_listprice') ?> - (<? value('item_listprice') ?> * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount)
+                WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups') AND fetchMetricBool('WholesalePriceCosting')) THEN
+                 (<? value('item_listcost') ?> / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                 WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups')) THEN
                  (<? value('item_unitcost') ?> / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
+                WHEN (ipsitem_type = 'M' AND fetchMetricBool('WholesalePriceCosting')) THEN
+                 (<? value('item_listcost') ?> + (<? value('item_listcost') ?> * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                 WHEN (ipsitem_type = 'M') THEN
                  (<? value('item_unitcost') ?> + (<? value('item_unitcost') ?> * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                 ELSE 0.00
@@ -101,11 +105,16 @@ FROM (
                 ELSE ipsitem_qtybreak
            END AS invqty,
            ipsitem_qtybreak AS qtybreak,
-           CASE WHEN ipsitem_type = 'N' THEN (ipsitem_price * itemuomtouomratio(<? value('item_id') ?>, NULL, ipsitem_price_uom_id)) *
-                                              iteminvpricerat(ipsitem_item_id)
-                WHEN ipsitem_type = 'D' THEN noNeg(<? value('item_listprice') ?> - (<? value('item_listprice') ?> * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount)
+           CASE WHEN ipsitem_type = 'N' THEN
+                 (ipsitem_price * itemuomtouomratio(<? value('item_id') ?>, NULL, ipsitem_price_uom_id)) * iteminvpricerat(ipsitem_item_id)
+                WHEN ipsitem_type = 'D' THEN
+                 noNeg(<? value('item_listprice') ?> - (<? value('item_listprice') ?> * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount)
+                WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups') AND fetchMetricBool('WholesalePriceCosting')) THEN
+                 (<? value('item_listcost') ?> / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                 WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups')) THEN
                  (<? value('item_unitcost') ?> / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
+                WHEN (ipsitem_type = 'M' AND fetchMetricBool('WholesalePriceCosting')) THEN
+                 (<? value('item_listcost') ?> + (<? value('item_listcost') ?> * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                 WHEN (ipsitem_type = 'M') THEN
                  (<? value('item_unitcost') ?> + (<? value('item_unitcost') ?> * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                 ELSE 0.00

diff --git a/foundation-database/public/tables/metasql/salesOrderItems-list.mql b/foundation-database/public/tables/metasql/salesOrderItems-list.mql
index 3f06697..abd4a32 100644 (file)
--- a/foundation-database/public/tables/metasql/salesOrderItems-list.mql
+++ b/foundation-database/public/tables/metasql/salesOrderItems-list.mql
@@ -101,8 +101,7 @@ SELECT coitem_id,
        END AS discountfromcust,
        coitem_unitcost,
        CASE WHEN (coitem_price = 0.0) THEN 0.0
-            ELSE ROUND((coitem_qtyord * coitem_qty_invuomratio) *
-                 ((coitem_price / coitem_price_invuomratio) - (coitem_unitcost / coitem_price_invuomratio)),2)
+            ELSE ROUND(coitem_qtyord * coitem_qty_invuomratio * (coitem_price - coitem_unitcost) / coitem_price_invuomratio,2)
        END AS margin,
        CASE WHEN (coitem_price = 0.0) THEN 0.0
             ELSE ((coitem_price - coitem_unitcost) / coitem_price)