Setup for Free Shipping Promotion Code

Desired Functionality:

To Setup a Promotion code, which will waive off the Freight and Shipping charges applied to the order

Following setups demonstrate the intended functionality:

Item has been added to the iStore shopping cart, shipping and handling charge have been applied to it

Apply the promotion code to the shopping cart

The shipping charges get waived (updated to 0)

To nullify the shipping charges, we apply another charge which is negative with a magnitude equal to the shipping charge applied on the iStore order line

(Note: the figures in ‘<>’ are negative)

Once the order is booked, the charges that have been applied can be queried from the Sales order form for the order line.
Following screen shot shows the negative free shipping charge that was applied

Setup for Free shipping promo code

Create a new charge type in the lookups
Path: Oracle Pricing Manager -> Setup -> Lookups
Type: FREIGHT_CHARGES_TYPE

Create a new formula that will return a negative value whose magnitude is equal to the Freight charges applied on the iStore order line
Path: Oracle Pricing Manager -> Pricing Formulas -> Formulas Setups

Create the Attribute mapping for Freight cost
Note: This step is required since there is no default mapping provided by Oracle for Freight cost for quoting/iStore application. Seeded mapping is available for Order management

The following function returns the value of shipping charge applied to the iStore order line

FUNCTION get_line_charge(p_quote_line_id NUMBER)
RETURN NUMBER
IS

v_line_charge NUMBER;

BEGIN

SELECT SUM(aql1.quantity * apa1.operand)
INTO v_line_charge
FROM apps.aso_quote_lines_all aql1,apps.aso_price_adjustments apa1
WHERE aql1.quote_header_id = apa1.quote_header_id
AND aql1.quote_line_id = apa1.quote_line_id
AND apa1.applied_flag = ‘Y’
AND apa1.modifier_line_type_code = ‘FREIGHT_CHARGE’
AND aql1.quote_line_id = p_quote_line_id
AND NVL(apa1.operand,0) > 0;

IF v_line_charge IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;

RETURN v_line_charge;

EXCEPTION WHEN OTHERS
THEN
dbms_output.put_line(‘Exception ‘|| sqlerrm);
END get_line_charge;

Compile the function (stand alone, or put the function in a package) and call the function in the ‘User Value String’ field as shown below

Setup Promotion Code:

Setup Modifier that will nullify the shipping charge if the promotion code has been used

Save and close all the modifier definitions