Oracle Pricing API

Example 1: Line level discount of 8% discount on all products File Path : $QP_TOP/patch/115/sql/QPXEXDS1.sql

/* Discount Creation - Get 8% discount on all products */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXDS1.sql 115.6 2006/08/24 04:51:05 nirmkuma ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+12;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'New HALLOWEAN 2000 Deal';
l_MODIFIER_LIST_rec.description := 'Latest New Description of HALLOWEAN 2000';
l_MODIFIER_LIST_rec.version_no := '9.4';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 'Get 8% discount' condition */
l_MODIFIERS_tbl(1).list_line_type_code := 'DIS';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).arithmetic_operator := '%';
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 2;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).product_precedence := 1;
l_MODIFIERS_tbl(1).operand := 8;
l_MODIFIERS_tbl(1).modifier_parent_index := 1;
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'ALL products' condition */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(1).product_attr_value:= 'ALL';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(2).pricing_attribute:= 'PRICING_ATTRIBUTE10';
l_PRICING_ATTR_tbl(1).pricing_attr_value_from:= 1;
l_PRICING_ATTR_tbl(1).pricing_attr_value_to := 9999999;
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).accumulate_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Call the Modifiers Public API to create the modifier header and a modifier line */
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
, p_MODIFIERS_tbl => l_MODIFIERS_tbl
--, p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
, p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
, x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
, x_MODIFIER_LIST_val_rec => l_MODIFIER_LIST_val_rec
, x_MODIFIERS_tbl => l_MODIFIERS_tbl
, x_MODIFIERS_val_tbl => l_MODIFIERS_val_tbl
, x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
, x_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl
, x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
, x_PRICING_ATTR_val_tbl => l_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
rollback;
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
rollback;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
rollback;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
COMMIT;
EXIT;

======================================================================
======================================================================

Example 2: Buy more than 5 quantities of item 62081 , Get 8% discount
File Path : $QP_TOP/patch/115/sql/QPXEXDS2.sql

/* Discount Creation - Buy 5 of item 62081, Get 8% discount */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXDS2.sql 115.3 2002/06/03 10:34:23 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'New HALLOWEAN 2000 Deal';
l_MODIFIER_LIST_rec.description := 'New Description of HALLOWEAN 2000';
l_MODIFIER_LIST_rec.version_no := '5.7';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 'Get 8% discount' condition */
l_MODIFIERS_tbl(1).list_line_type_code := 'DIS';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).operand := 8;
l_MODIFIERS_tbl(1).arithmetic_operator := '%';
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 3;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
l_MODIFIERS_tbl(1).product_precedence:= 220;
/* Create a Pricing Attribute record to specify the 'Buy 5 of item 62081' condition */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(1).product_attr_value:= '62081';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(1).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(1).pricing_attr_value_from:= '5';
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Call the Modifiers Public API to create the modifier, modifier line and a pricing attribute record */
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
--,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
COMMIT;
EXIT;

======================================================================
======================================================================

Example 3: For customer 1000, Buy more than 2 Units of item 62081, Get 10% discount
File Path : $QP_TOP/patch/115/sql/QPXEXDS3.sql

/* Discount Creation - For customer 1000, Buy 2 of item 62081, Get 10% discount */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXDS3.sql 115.3 2002/06/03 10:34:25 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'New Year 2001 Promotion';
l_MODIFIER_LIST_rec.description := 'New Year 2001 Promotion';
l_MODIFIER_LIST_rec.version_no := '6.7';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 'Get 10% discount' condition */
l_MODIFIERS_tbl(1).list_line_type_code := 'DIS';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).operand := 10;
l_MODIFIERS_tbl(1).arithmetic_operator := '%';
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 3;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 2 of item 62081' condition */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(1).product_attr_value:= '62081';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(1).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(1).pricing_attr_value_from:= '2';
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Qualifier Record to specify 'For Customer 1000' condition */
l_QUALIFIERS_tbl(1).excluder_flag := 'N';
l_QUALIFIERS_tbl(1).comparison_operator_code := '=';
l_QUALIFIERS_tbl(1).qualifier_context := 'CUSTOMER';
l_QUALIFIERS_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE2';
l_QUALIFIERS_tbl(1).qualifier_attr_value := '1000';
l_QUALIFIERS_tbl(1).qualifier_grouping_no := 5467;
l_QUALIFIERS_tbl(1).qualifier_precedence := 1;
l_QUALIFIERS_tbl(1).start_date_active := sysdate;
l_QUALIFIERS_tbl(1).end_date_active := sysdate+10;
l_QUALIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Call the Modifiers Public API to create the modifier header, modifier line, qualifier and a pricing attributes record */
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
COMMIT;
EXIT;

======================================================================
======================================================================

 Example 4: Charge 2% surcharge on all products File Path : /nfs/group/qpdev/qp/11.5/patch/115/sql/QPXEXSUR4.sql

/* Surcharge Creation - Charge 2% surcharge on all products */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXSUR.sql 115.4 2002/06/03 10:34:30 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'SLT' (Surcharge List) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'SLT';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'Surcharge';
l_MODIFIER_LIST_rec.description := '2% Surcharge';
l_MODIFIER_LIST_rec.version_no := '1.9';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 'Charge 2% Surcharge' condition */
l_MODIFIERS_tbl(1).list_line_type_code := 'SUR';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).operand := 2;
l_MODIFIERS_tbl(1).arithmetic_operator := '%';
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 2;
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Call the Modifiers Public API to create the modifier header and a modifier line */
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
--,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
--,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
COMMIT;
EXIT;

======================================================================
======================================================================

 Example 5: For customer 1000, Buy more than 2 units of item 62081, Get a Payment Term 2/10 NET 30
File Path : $QP_TOP/patch/115/sql/QPXEXTSN.sql

/* Terms Substitution Creation - For customer 1000, Buy 2 of item 62081,
Get a Payment Term 2/10 NET 30 */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXTSN.sql 115.3 2002/06/03 10:34:33 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'Terms Substitution';
l_MODIFIER_LIST_rec.description := 'New Year 2001 Promotion';
l_MODIFIER_LIST_rec.version_no := '1.7';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 'Get Payment Term 2/10 NET 30 ( Tems id=1000) ' condition */
l_MODIFIERS_tbl(1).list_line_type_code := 'TSN';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
--l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 3;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).substitution_context := 'TERMS';
l_MODIFIERS_tbl(1).substitution_attribute := 'QUALIFIER_ATTRIBUTE1';
l_MODIFIERS_tbl(1).substitution_value := '1000';
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 2 of item 62081' condition */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(1).product_attr_value:= '62081';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(1).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(1).pricing_attr_value_from:= '2';
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Qualifier Record to specify 'For Customer 1000' condition */
l_QUALIFIERS_tbl(1).excluder_flag := 'N';
l_QUALIFIERS_tbl(1).comparison_operator_code := '=';
l_QUALIFIERS_tbl(1).qualifier_context := 'CUSTOMER';
l_QUALIFIERS_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE2';
l_QUALIFIERS_tbl(1).qualifier_attr_value := '1000';
l_QUALIFIERS_tbl(1).qualifier_grouping_no := 5467;
l_QUALIFIERS_tbl(1).qualifier_precedence := 1;
l_QUALIFIERS_tbl(1).start_date_active := sysdate;
l_QUALIFIERS_tbl(1).end_date_active := sysdate+10;
l_QUALIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Call the Modifiers Public API to create the modifier header, modifier line, qualifier and a pricing attributes record */
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
COMMIT;
EXIT;

======================================================================
======================================================================

Example 6: Other Item Discount Creation - Buy 1 of item 62081, Get 1 free
File Path: $QP_TOP/patch/115/sql/QPXEXOID.sql

/* Other Item Discount Creation - Buy 1 of item 62081, Get 1 free */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXOID.sql 115.2 2002/06/03 10:34:26 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'latest 3.7 OID 2001 Promotion';
l_MODIFIER_LIST_rec.description := 'latest OID 2001 Promotion';
l_MODIFIER_LIST_rec.version_no := '3.7';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify Other Item Discount (OID) */
l_MODIFIERS_tbl(1).list_line_type_code := 'OID';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 3;
l_MODIFIERS_tbl(1).product_precedence := 12;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).modifier_parent_index := 1;
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify the free (100% discount) condition */
l_MODIFIERS_tbl(2).list_line_type_code := 'DIS';
l_MODIFIERS_tbl(2).automatic_flag:= 'Y';
l_MODIFIERS_tbl(2).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(2).accrual_flag := 'N';
l_MODIFIERS_tbl(2).start_date_active := sysdate;
l_MODIFIERS_tbl(2).end_date_active := sysdate+10;
l_MODIFIERS_tbl(2).operand := 100;
l_MODIFIERS_tbl(2).arithmetic_operator := '%';
l_MODIFIERS_tbl(2).pricing_group_sequence := 1;
l_MODIFIERS_tbl(2).pricing_phase_id := 3;
l_MODIFIERS_tbl(2).product_precedence := 12;
l_MODIFIERS_tbl(2).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(2).modifier_parent_index := 1;
l_MODIFIERS_tbl(2).rltd_modifier_grp_no := 12;
l_MODIFIERS_tbl(2).rltd_modifier_grp_type := 'BENEFIT';
l_MODIFIERS_tbl(2).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 1 of item 62081' condition. It is linked to the OID modifier record by specifying modifiers_index=1 */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(1).product_attr_value:= '62081';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(1).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(1).pricing_attr_value_from:= '1';
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Get 1 of item 62081' condition. It is linked to the DIS modifier record by specifying modifiers_index=2 */
l_PRICING_ATTR_tbl(2).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(2).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(2).product_attr_value:= '62081';
l_PRICING_ATTR_tbl(2).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(2).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(2).pricing_attr_value_from:= '1';
l_PRICING_ATTR_tbl(2).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(2).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(2).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(2).MODIFIERS_index:=2;
l_PRICING_ATTR_tbl(2).operation := QP_GLOBALS.G_OPR_CREATE;
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_x_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_x_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_x_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_x_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg 1 is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 5 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
commit;
exit;


======================================================================
======================================================================

 Example 7: Promotional Goods - Buy 1 of item 45 and 1 item 63, Get 1 item 62081 at 20% discount
File Path : $QP_TOP/patch/115/sql/QPXEXPRG.sql

/* Promotional Goods - Buy 1 of item 45 and 1 item 63, Get 1 item 62081 at 20% discount */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXPRG.sql 115.2 2002/06/03 10:34:29 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion)- linked to item 45 */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'latest 1.8 PRG 2001 Promotion';
l_MODIFIER_LIST_rec.description := 'latest PRG 2001 Promotion';
l_MODIFIER_LIST_rec.version_no := '1.8';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify Promotional Goods (PRG) - linked to item 45 */
l_MODIFIERS_tbl(1).list_line_type_code := 'PRG';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 3;
l_MODIFIERS_tbl(1).product_precedence := 12;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).modifier_parent_index := 1;
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify related item (RLTD) - linked to item 63 */
l_MODIFIERS_tbl(2).list_line_type_code := 'RLTD';
l_MODIFIERS_tbl(2).automatic_flag:= 'Y';
l_MODIFIERS_tbl(2).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(2).accrual_flag := 'N';
l_MODIFIERS_tbl(2).start_date_active := sysdate;
l_MODIFIERS_tbl(2).end_date_active := sysdate+10;
l_MODIFIERS_tbl(2).pricing_group_sequence := 1;
l_MODIFIERS_tbl(2).pricing_phase_id := 3;
l_MODIFIERS_tbl(2).product_precedence := 12;
l_MODIFIERS_tbl(2).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(2).modifier_parent_index := 1;
l_MODIFIERS_tbl(2).rltd_modifier_grp_no := 12;
l_MODIFIERS_tbl(2).rltd_modifier_grp_type := 'QUALIFIER';
l_MODIFIERS_tbl(2).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 20% discount on item 62081 */
l_MODIFIERS_tbl(3).list_line_type_code := 'DIS';
l_MODIFIERS_tbl(3).automatic_flag:= 'Y';
l_MODIFIERS_tbl(3).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(3).accrual_flag := 'N';
l_MODIFIERS_tbl(3).start_date_active := sysdate;
l_MODIFIERS_tbl(3).end_date_active := sysdate+10;
l_MODIFIERS_tbl(3).operand := 20;
l_MODIFIERS_tbl(3).arithmetic_operator := '%';
l_MODIFIERS_tbl(3).pricing_group_sequence := 1;
l_MODIFIERS_tbl(3).pricing_phase_id := 3;
l_MODIFIERS_tbl(3).product_precedence := 12;
l_MODIFIERS_tbl(3).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(3).modifier_parent_index := 1;
l_MODIFIERS_tbl(3).rltd_modifier_grp_no := 10;
l_MODIFIERS_tbl(3).rltd_modifier_grp_type := 'BENEFIT';
l_MODIFIERS_tbl(3).benefit_price_list_line_id := 41187;
l_MODIFIERS_tbl(3).benefit_qty := 1;
l_MODIFIERS_tbl(3).benefit_uom_code := 'Ea';
l_MODIFIERS_tbl(3).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 1 of item 45' condition. It is linked to the PRG modifier record by specifying modifiers_index=1 */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(1).product_attr_value:= '45';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(1).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(1).pricing_attr_value_from:= '1';
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 1 of item 63' condition. It is linked to the RLTD modifier record by specifying modifiers_index=2 */
l_PRICING_ATTR_tbl(2).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(2).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(2).product_attr_value:= '63';
l_PRICING_ATTR_tbl(2).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(2).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(2).pricing_attr_value_from:= '1';
l_PRICING_ATTR_tbl(2).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(2).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(2).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(2).MODIFIERS_index:=2;
l_PRICING_ATTR_tbl(2).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Get 1 of item 62081' condition. It is linked to the DIS modifier record by specifying modifiers_index=3 */
l_PRICING_ATTR_tbl(3).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(3).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(3).product_attr_value:= '62081';
l_PRICING_ATTR_tbl(3).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(3).pricing_attribute:= 'PRICING_ATTRIBUTE3';
l_PRICING_ATTR_tbl(3).pricing_attr_value_from:= '1';
l_PRICING_ATTR_tbl(3).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(3).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(3).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(3).MODIFIERS_index:=3;
l_PRICING_ATTR_tbl(3).operation := QP_GLOBALS.G_OPR_CREATE;
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_x_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_x_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_x_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_x_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg 1 is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 5 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
commit;
exit;

======================================================================
======================================================================

 Example 8: Coupon Issue - Buy 2 of item 45, Get Coupon for 20% discount
File Path : $QP_TOP/patch/115/sql/QPXEXCIE.sql

-- Coupon Issue - Buy 2 of item 45, Get Coupon for 20% discount
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXCIE.sql 115.2 2002/06/03 10:34:19 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'latest 2.1 CIE 2001 Promotion';
l_MODIFIER_LIST_rec.description := 'latest CIE 2001 Promotion';
l_MODIFIER_LIST_rec.version_no := '2.1';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a modifier of type Coupon Issue (CIE). The from_rltd_modifier_id is the list_line_id
of anathor modifier which specifies the 'Get 20% discount' condition of the Coupon. So, this
modifier needs to be created prior to creating this Coupun Issue */
l_MODIFIERS_tbl(1).list_line_type_code := 'CIE';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'Y';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 3;
l_MODIFIERS_tbl(1).product_precedence := 12;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).modifier_parent_index := 1;
l_MODIFIERS_tbl(1).to_rltd_modifier_id := 306693;
l_MODIFIERS_tbl(1).rltd_modifier_grp_no := 10;
l_MODIFIERS_tbl(1).rltd_modifier_grp_type := 'COUPON';
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 2 of item 45' condition. It is linked to the CIE modifier record by specifying modifiers_index=1 */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(1).product_attr_value:= '45';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(1).pricing_attribute:= 'PRICING_ATTRIBUTE10';
l_PRICING_ATTR_tbl(1).pricing_attr_value_from := 2;
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_x_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_x_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_x_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_x_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg 1 is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 5 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
commit;
exit;

======================================================================
======================================================================

 Example 9: Price Break - Buy 1-100 of item 45, Get 20% discount Buy 101-200 of item 45, Get 25% discount
File Path: $QP_TOP/patch/115/sql/QPXEXPBH.sql

/* Price Break - Buy 1-100 of item 45, Get 20% discount
Buy 101-200 of item 45, Get 25% discount */
REM FILETYPE : NOEXEC
REM Added for ARU db drv auto generation
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
--set serveroutput on
declare
/* $Header: QPXEXPBH.sql 115.2 2002/06/03 10:34:27 pkm ship $ */
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count number;
x_msg_data Varchar2(2000);
x_msg_index number;
l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
Begin
/* Create a Modifier header of type 'PRO' (Promotion) */
l_MODIFIER_LIST_rec.currency_code := 'USD';
l_MODIFIER_LIST_rec.list_type_code := 'PRO';
l_MODIFIER_LIST_rec.start_date_active := sysdate;
l_MODIFIER_LIST_rec.end_date_active := sysdate+10;
l_MODIFIER_LIST_rec.source_system_code := 'QP';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.name := 'latest 1.9 PBH 2001 Promotion';
l_MODIFIER_LIST_rec.description := 'latest PBH 2001 Promotion';
l_MODIFIER_LIST_rec.version_no := '1.9';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify Price Break (PBH) - linked to item 45 */
l_MODIFIERS_tbl(1).list_line_type_code := 'PBH';
l_MODIFIERS_tbl(1).automatic_flag:= 'Y';
l_MODIFIERS_tbl(1).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(1).accrual_flag := 'N';
l_MODIFIERS_tbl(1).start_date_active := sysdate;
l_MODIFIERS_tbl(1).end_date_active := sysdate+10;
l_MODIFIERS_tbl(1).pricing_group_sequence := 1;
l_MODIFIERS_tbl(1).pricing_phase_id := 3;
l_MODIFIERS_tbl(1).product_precedence := 12;
l_MODIFIERS_tbl(1).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(1).modifier_parent_index := 1;
l_MODIFIERS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 20% discount - linked to item 45 */
l_MODIFIERS_tbl(2).list_line_type_code := 'DIS';
l_MODIFIERS_tbl(2).automatic_flag:= 'Y';
l_MODIFIERS_tbl(2).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(2).accrual_flag := 'N';
l_MODIFIERS_tbl(2).start_date_active := sysdate;
l_MODIFIERS_tbl(2).end_date_active := sysdate+10;
l_MODIFIERS_tbl(2).operand := 20;
l_MODIFIERS_tbl(2).arithmetic_operator := '%';
l_MODIFIERS_tbl(2).pricing_group_sequence := 1;
l_MODIFIERS_tbl(2).pricing_phase_id := 3;
l_MODIFIERS_tbl(2).product_precedence := 12;
l_MODIFIERS_tbl(2).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(2).modifier_parent_index := 1;
l_MODIFIERS_tbl(2).rltd_modifier_grp_no := 10;
l_MODIFIERS_tbl(2).rltd_modifier_grp_type := 'PRICE BREAK';
l_MODIFIERS_tbl(2).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier line to specify 25% discount - linked to item 45 */
l_MODIFIERS_tbl(3).list_line_type_code := 'DIS';
l_MODIFIERS_tbl(3).automatic_flag:= 'Y';
l_MODIFIERS_tbl(3).modifier_level_code := 'LINE';
l_MODIFIERS_tbl(3).accrual_flag := 'N';
l_MODIFIERS_tbl(3).start_date_active := sysdate;
l_MODIFIERS_tbl(3).end_date_active := sysdate+10;
l_MODIFIERS_tbl(3).operand := 25;
l_MODIFIERS_tbl(3).arithmetic_operator := '%';
l_MODIFIERS_tbl(3).pricing_group_sequence := 1;
l_MODIFIERS_tbl(3).pricing_phase_id := 3;
l_MODIFIERS_tbl(3).product_precedence := 12;
l_MODIFIERS_tbl(3).price_break_type_code := 'POINT';
l_MODIFIERS_tbl(3).modifier_parent_index := 1;
l_MODIFIERS_tbl(3).rltd_modifier_grp_no := 10;
l_MODIFIERS_tbl(3).rltd_modifier_grp_type := 'PRICE BREAK';
l_MODIFIERS_tbl(3).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy item 45' condition. It is linked to the PBH modifier record by specifying modifiers_index=1 */
l_PRICING_ATTR_tbl(1).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(1).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(1).product_attr_value:= '45';
l_PRICING_ATTR_tbl(1).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(1).pricing_attribute:= 'PRICING_ATTRIBUTE10';
l_PRICING_ATTR_tbl(1).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(1).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(1).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(1).MODIFIERS_index:=1;
l_PRICING_ATTR_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 1-100 of item 45' condition. It is linked to the DIS modifier record by specifying modifiers_index=2 */
l_PRICING_ATTR_tbl(2).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(2).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(2).product_attr_value:= '45';
l_PRICING_ATTR_tbl(2).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(2).pricing_attribute:= 'PRICING_ATTRIBUTE10';
l_PRICING_ATTR_tbl(2).pricing_attr_value_from:= '1';
l_PRICING_ATTR_tbl(2).pricing_attr_value_to:= '100';
l_PRICING_ATTR_tbl(2).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(2).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(2).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(2).MODIFIERS_index:=2;
l_PRICING_ATTR_tbl(2).operation := QP_GLOBALS.G_OPR_CREATE;
/* Create a Pricing Attribute record to specify the 'Buy 101-200 of item 45' condition. It is linked to the DIS modifier record by specifying modifiers_index=3 */
l_PRICING_ATTR_tbl(3).product_attribute_context:= 'ITEM';
l_PRICING_ATTR_tbl(3).product_attribute:= 'PRICING_ATTRIBUTE1';
l_PRICING_ATTR_tbl(3).product_attr_value:= '45';
l_PRICING_ATTR_tbl(3).pricing_attribute_context:= 'VOLUME';
l_PRICING_ATTR_tbl(3).pricing_attribute:= 'PRICING_ATTRIBUTE10';
l_PRICING_ATTR_tbl(3).pricing_attr_value_from:= '101';
l_PRICING_ATTR_tbl(3).pricing_attr_value_to:= '200';
l_PRICING_ATTR_tbl(3).comparison_operator_code:= 'BETWEEN';
l_PRICING_ATTR_tbl(3).product_uom_code:= 'Ea';
l_PRICING_ATTR_tbl(3).excluder_flag:= 'N';
l_PRICING_ATTR_tbl(3).MODIFIERS_index:=3;
l_PRICING_ATTR_tbl(3).operation := QP_GLOBALS.G_OPR_CREATE;
QP_Modifiers_PUB.Process_Modifiers
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
,p_MODIFIERS_tbl => l_MODIFIERS_tbl
,p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
,x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec
,x_MODIFIER_LIST_val_rec => l_x_MODIFIER_LIST_val_rec
,x_MODIFIERS_tbl => l_x_MODIFIERS_tbl
,x_MODIFIERS_val_tbl => l_x_MODIFIERS_val_tbl
,x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
,x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
,x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
,x_PRICING_ATTR_val_tbl => l_x_PRICING_ATTR_val_tbl
);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
l_return_status := FND_API.G_RET_STS_ERROR;
-- Get message count and data
--dbms_output.put_line('err msg 1 is : ' || x_msg_data);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 2 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
WHEN OTHERS THEN
l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
--dbms_output.put_line(' msg count 5 is : ' || x_msg_count);
for k in 1 .. x_msg_count loop
x_msg_data := oe_msg_pub.get( p_msg_index => k,
p_encoded => 'F'
-- Get message count and data
--dbms_output.put_line('err msg ' || k ||'is: ' || x_msg_data);
end loop;
END;
/
commit;
exit;

======================================================================
======================================================================