Discounts and Promotions Product Guide

While the Discounts and Promotions package includes support for a number of standard use cases, it is also possible to create your own custom use case, which are known as usage restrictions.

Custom qualification rules are defined using plpgsql functions. These functions are stored in the enhancedpricing schema and are available to select when defining a discount rule. A custom qualification may be required when a discount cannot be satisfied by discount assignments that include sale type, customer number, and customer type. An order will be qualified for a discount when the usage restriction and assignment are satisfied.

An example of a custom usage restriction rule would be where one or more characteristic values are required, or sales history needs to meet a certain threshold. The logic for these rules would be written into a function that accepts a quote or sales order ID, a discount rule ID, and the order type of "QU" for quote and "SO" for sales order. The function would then return a boolean value of true or false. The discount rules engine would then call the function when evaluating discounts for a sales order.

Below is a sample of a function that checks for specific customer characteristic values:

-- Function: enhancedpricing.discountsalesperorder(integer, integer, text) 
-- DROP FUNCTION enhancedpricing.discountsalesperorder(integer, integer, text); 
CREATE OR REPLACE FUNCTION enhancedpricing.discountsalesperorder( 
  integer, 
  integer, 
  text) 
 RETURNS boolean AS 
$BODY$ 
-- determine rate to set Discount on customer 
DECLARE 
porderid ALIAS FOR $1; 
pdiscountid ALIAS FOR $2; 
pordertype ALIAS FOR $3; 
_custid integer; 

BEGIN 
-- get the customer ID from the order 
 IF pordertype = 'SO' THEN 
  select cohead_cust_id INTO _custid 
  FROM cohead 
  where cohead_id = porderid; 
 END IF; 

 IF pordertype = 'QU' THEN 
  select quhead_cust_id INTO _custid 
  from quhead 
  where quhead_id = porderid; 
 END IF; 

 IF _custid IS NULL THEN 
  RETURN FALSE; 
 END IF; 

-- get the customer ranking 
 PERFORM 1 
 from charass 
 where charass_char_id = getcharid('Customer Ranking','C') 
 AND charass_target_id = _custid 
 AND charass_target_type = 'C' 
 AND charass_value IN ('Client A','Client B','Client C','Client D','Client N'); 

 IF FOUND THEN 
  RETURN TRUE; 
 ELSE 
  RETURN FALSE; 
 END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql VOLATILE 
COST 100; 
ALTER FUNCTION enhancedpricing.discountsalesperorder(integer, integer, text) 
OWNER TO admin; 
GRANT EXECUTE ON FUNCTION enhancedpricing.discountsalesperorder(integer, integer, text) TO public; 
GRANT EXECUTE ON FUNCTION enhancedpricing.discountsalesperorder(integer, integer, text) TO admin; 

This custom function can be added to the enhancedpricing schema of the ERP database using PGAdmin. The custom usage restriction is further defined within the xTuple ERP client, as follows:

Discount Usage Restriction