Issue #22824:add ability to update discount and markup schedules

diff --git a/foundation-database/public/tables/metasql/updatePrices-update.mql b/foundation-database/public/tables/metasql/updatePrices-update.mql
index 27e5f04..c0fa648 100644 (file)
--- a/foundation-database/public/tables/metasql/updatePrices-update.mql
+++ b/foundation-database/public/tables/metasql/updatePrices-update.mql
@@ -5,6 +5,7 @@
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
 -- See www.xtuple.com/CPAL for the full text of the software license.
 
+<? if exists("nominal") ?>
 UPDATE ipsiteminfo SET
   ipsitem_price=
 <? if exists("updateByValue") ?>
@@ -21,6 +22,7 @@ FROM selsched, item
 <? endif ?>
 WHERE ( ipsitem_item_id=item_id
   AND selsched_ipshead_id=ipsitem_ipshead_id
+  AND ipsitem_type='N'
 <? if exists("item_id") ?>
   AND item_id=<? value("item_id") ?>
 <? elseif exists("itemgrp_id") ?>
@@ -33,3 +35,68 @@ WHERE ( ipsitem_item_id=item_id
   AND prodcat_code ~ <? value("prodcat_pattern") ?>
 <? endif ?>
 );
+<? endif ?>
+
+<? if exists("discount") ?>
+UPDATE ipsiteminfo SET
+  ipsitem_discntprcnt=
+<? if exists("updateByValue") ?>
+  ipsitem_discntprcnt + (<? value("updateBy") ?> / 100.0)
+<? else ?>
+  roundSale(ipsitem_discntprcnt * (1.0 + (<? value("updateBy") ?> / 100.0)))
+<? endif ?>
+FROM selsched, item
+<? if reExists("itemgrp") ?>
+  JOIN itemgrpitem ON (itemgrpitem_item_id=item_id)
+  JOIN itemgrp ON (itemgrpitem_itemgrp_id=itemgrp_id)
+<? elseif reExists("prodcat") ?>
+  JOIN prodcat ON (prodcat_id=item_prodcat_id)
+<? endif ?>
+WHERE ( ipsitem_item_id=item_id
+  AND selsched_ipshead_id=ipsitem_ipshead_id
+  AND ipsitem_type='D'
+<? if exists("item_id") ?>
+  AND item_id=<? value("item_id") ?>
+<? elseif exists("itemgrp_id") ?>
+  AND itemgrp_id=<? value("itemgrp_id") ?>
+<? elseif exists("itemgrp_pattern") ?>
+  AND itemgrp_name ~ <? value("itemgrp_pattern") ?>
+<? elseif exists("prodcat_id") ?>
+  AND prodcat_id=<? value("prodcat_id") ?>
+<? elseif exists("prodcat_pattern") ?>
+  AND prodcat_code ~ <? value("prodcat_pattern") ?>
+<? endif ?>
+);
+<? endif ?>
+
+<? if exists("markup") ?>
+UPDATE ipsiteminfo SET
+  ipsitem_discntprcnt=
+<? if exists("updateByValue") ?>
+  ipsitem_discntprcnt + (<? value("updateBy") ?> / 100.0)
+<? else ?>
+  roundSale(ipsitem_discntprcnt * (1.0 + (<? value("updateBy") ?> / 100.0)))
+<? endif ?>
+FROM selsched, item
+<? if reExists("itemgrp") ?>
+  JOIN itemgrpitem ON (itemgrpitem_item_id=item_id)
+  JOIN itemgrp ON (itemgrpitem_itemgrp_id=itemgrp_id)
+<? elseif reExists("prodcat") ?>
+  JOIN prodcat ON (prodcat_id=item_prodcat_id)
+<? endif ?>
+WHERE ( ipsitem_item_id=item_id
+  AND selsched_ipshead_id=ipsitem_ipshead_id
+  AND ipsitem_type='M'
+<? if exists("item_id") ?>
+  AND item_id=<? value("item_id") ?>
+<? elseif exists("itemgrp_id") ?>
+  AND itemgrp_id=<? value("itemgrp_id") ?>
+<? elseif exists("itemgrp_pattern") ?>
+  AND itemgrp_name ~ <? value("itemgrp_pattern") ?>
+<? elseif exists("prodcat_id") ?>
+  AND prodcat_id=<? value("prodcat_id") ?>
+<? elseif exists("prodcat_pattern") ?>
+  AND prodcat_code ~ <? value("prodcat_pattern") ?>
+<? endif ?>
+);
+<? endif ?>