How to implement a new Discount and Promotion Type
A discount and promotion type is an implementation of a rule for Discounts and Promotions. These rules define the logic to be applied to calculate the discount when the discount or promotion can be applied.
This how to targets developers wanting to implement these kind of rules. Users that need to configure existent ones should read this other document instead.
It is possible to define types that take care of a single line, such as X per cent discount in a single product, and types that look to the whole order or invoice to determine if the discount is applicable, for example buying product X and Y, Z is free.
The implementation of a Discount and Promotion Type is done within a module. This how to assumes there is already a module created.
This document explains how to create a "Buy X pay Y of same product" type. This rule applies when there are at least X units in a line, in this case for each group of X units, only Y are payed.
For example, if the rule can be applied to product A (which price is 10€), X is 4, and Y is 3. An order including 4 units of A, would have a discount of 10€. An order including 9 units of A, would have a discount of 20€.
The first thing to do is to extend the table that defines Discounts and Promotions (
M_Offer) in case the columns it has do not support the requirements for our Discount type. In this case we need
ALTER TABLE M_Offer ADD COLUMN em_obdisc_X numeric; ALTER TABLE M_Offer ADD COLUMN em_obdisc_Y numeric;
Note in this case our module's DBPrefix is
Now columns in Application Dictionary for
M_Offer table should be created: go to Tables and Columns window, look for
M_Offer table and click on Create Columns from DB button.
After that, their corresponding fields in Discounts and Promotions window: go to Windows, Tabs and Fields window, look for
Discounts and Promotions window,
Discounts and Promotions tab and click on Create Fields button.
Discount and Promotion Type definition
To make available the new Type, you just need to register it in Discounts and Promotions Types window. Create a new record there, select the module you are working in, and add a descriptive name.
The PL/SQL Implementor field indicates which is the PL function that implements this type. In this case we have named it
Once it is created, this Type will be available from Discounts and Promotions window when defining new rules.
Note it is a good practice, in order to keep this window available, to show the fields created in the section above just in case this type is selected. This can be accomplished by adding them a Display Logic which should look similar to
'E08EE3C23EBA49358A881EF06C139D63' is the UUID of the record that has just been created in Discounts and Promotions Types.
The code implementing the type is:
CREATE OR REPLACE FUNCTION obdisc_xy_same_product(p_type character varying, p_rule_id character varying, p_line_id character varying, p_priceprecision numeric, p_stdprecision numeric, p_user_id character varying, p_taxincluded character varying) RETURNS character varying AS $BODY$ DECLARE v_x NUMERIC; v_y NUMERIC; v_apply_next VARCHAR(1); v_mod NUMERIC; v_chunks NUMERIC; v_tax VARCHAR(32); v_qty NUMERIC; v_unitPrice NUMERIC; v_newUnitPrice NUMERIC; v_newGrossAmt NUMERIC; v_newNetAmt NUMERIC; v_newNetLine NUMERIC; v_priceactual NUMERIC; v_basePrice NUMERIC; v_origGrossAmt NUMERIC; v_origLineNetAmt NUMERIC; v_totalPromotion NUMERIC; BEGIN -- 1. Obtain information about how the rule is configured SELECT em_obdisc_x, em_obdisc_y, apply_next INTO v_x, v_y, v_apply_next FROM m_offer WHERE m_offer_id = p_rule_id; -- 2. Obtain information about the line the promotion can be -- applied to IF (p_type ='O') then -- Get info from Order SELECT gross_unit_price, c_tax_id, qtyordered, priceactual, line_gross_amount, linenetamt INTO v_unitprice, v_tax, v_qty, v_priceactual, v_origGrossAmt, v_origLineNetAmt FROM c_orderline WHERE c_orderline_id = p_line_id; else -- Get info from Invoice SELECT gross_unit_price, c_tax_id, qtyinvoiced, priceactual, line_gross_amount, linenetamt INTO v_unitprice, v_tax, v_qty, v_priceactual, v_origGrossAmt, v_origLineNetAmt FROM c_invoiceline WHERE c_invoiceline_id = p_line_id; end IF; -- 3. Decide whether the rule can be applied IF (v_qty < v_x) then RETURN 'Y'; -- rule not applied, apply next one if present end IF; -- 4. Calculate the discount v_mod := mod (v_qty, v_x); -- Units without discount v_chunks := floor(v_qty / v_x); -- How many times the discount is applied IF (p_taxIncluded = 'Y') then v_newGrossAmt := round(v_chunks * v_y * v_unitprice + v_unitprice * v_mod, p_stdprecision); v_newUnitPrice := round(v_newGrossAmt / v_qty, p_priceprecision); v_newNetLine := c_get_net_price_from_gross(v_tax, v_newGrossAmt, v_newGrossAmt, p_priceprecision, v_qty); v_newNetAmt := round(v_newNetLine * v_qty, p_stdprecision); v_basePrice := v_unitprice; v_totalPromotion := v_origGrossAmt - v_newGrossAmt; else v_newNetAmt := round(v_chunks * v_y * v_priceactual + v_priceactual * v_mod, p_stdprecision); v_newNetLine := round(v_newNetAmt / v_qty, p_priceprecision); v_basePrice := v_priceactual; v_totalPromotion := v_origLineNetAmt - v_newGrossAmt; end IF; PERFORM M_PROMOTION_ADD(p_type, p_line_id, p_rule_id, p_taxIncluded, v_newUnitPrice, v_newGrossAmt, v_newNetLine, v_newNetAmt, v_totalPromotion, v_totalPromotion, v_basePrice, p_user_id); -- 5. return whether other discounts can be applied to this same line RETURN v_apply_next; END ; $BODY$ LANGUAGE plpgsql VOLATILE
The following sections explain this code. Numbers in the title refer to numbers in comments above.
Any function implementing a Discount and Promotion Type, must receive the following parameters:
p_type: Possible values are O or I. O stands for Order and I for Invoice. Indicates whether the rule is being applied to an Order or an Invoice line.
p_rule_id: ID of the rule (M_Offer.M_Offer_ID) that is being checked.
p_line_id: ID of the line (Order line or Invoice line) the promotion is being applied to.
p_priceprecision: Based on document's currency, the precision to be used when rounding prices.
p_stdprecision: Based on document's currency, the precision to be used when rounding amounts.
p_user_id: ID of the user that has invoked the process, used when creating the actual discount for audit purposes.
p_taxincluded: Possible values are Y or N. Indicates whether the Price List that is applied to current document includes (Y) or not (N) taxes.
1. Get rule configuration
The Discount and Promotion that is currently checked usually has some instance configuration. In our case values for X and Y, and whether other Promotions and Discounts can be chained to this same line after applying this one.
2. Get line information
Line information must be retrieved. Note that the same function is invoked for Orders and Invoices, determined by
p_type, so both cases must be taken into account.
3. Decide whether the rule can be applied
This code is executed for all Promotions and Discounts that are candidates to be applied to each line (see next section). Depending on the rules the type defines, it is possible this candidate rule to be rejected.
In this case, if the quantity in the line is lower than the value for X, the rule is rejected. Note that as the rule is not applied Y is returned, this means other Discounts can always be applied.
4. Calculate the discount
At this point we know the Discount must be applied to this line. It is time to actually implement the amounts to be discounted.
Here it is important to take into account differences between Price Lists that include taxes and the ones not doing so, as well as correct rounding.
Finally, when everything is calculated the Discount is inserted by invoking
M_Promotion_Add function. It will create a new record in
These functions are expected to return a boolean value (Y or N). If Y is returned, the algorithm will continue looking for additional Discounts and Promotions to be applied to this line; whereas in case of N, the algorithm will stop after applying this one.
When this code is executed
The code that checks Discounts and Promotions (
M_Promotion_Calculate) is executed when Order and Invoices are completed or when Calculate Promotions button is clicked on any of these windows.
This code resets prices by removing all possible Discounts and Promotions previously applied and looks for Discount and Promotions candidates to be applied to each line of the document.
A Discount candidate is a discount that can be applied to the line based on the general filters this discount defines. Finally, the PL/SQL that implements the type is in charge to decide whether the discount is applied or not. For example, if X is 4 and the rule can be applied to A, any line with product A will have this rule as candidate, but only the ones with a quantity equals or greater than 4 should be applied with the discount.