This script creates a modifier header, line and qualifier at header level in R12.
DECLARE
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_user_id NUMBER:=69575;
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_responsibility_name VARCHAR2(100) ;
i NUMBER :=1;
j NUMBER :=1;
k NUMBER :=1;
l_list_header_id NUMBER;
l_list_line_id NUMBER;
l_qualifier_id NUMBER;
e_problem EXCEPTION;
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;
BEGIN
l_responsibility_name := 'Order Management Super User';
holx_cyt_util_pkg.get_responsibility_id (l_responsibility_name,
l_responsibility_id,
l_application_id
);
fnd_global.apps_initialize (l_user_id,
l_responsibility_id,
l_application_id
);
OE_MSG_PUB.Initialize;
/* Create a Modifier header of type 'Discount List' */
l_MODIFIER_LIST_rec.list_type_code := 'DLT'; -- lookup_code in fnd_lookup_values where lookup_type = 'LIST_TYPE_CODE'
l_MODIFIER_LIST_rec.name := 'SingleInsert'; -- Modifier Name
l_MODIFIER_LIST_rec.comments := 'Description - Single Insert Testing'; -- Modifier Description
l_MODIFIER_LIST_rec.version_no := '1.0';
l_MODIFIER_LIST_rec.start_date_active := TRUNC(SYSDATE);
l_MODIFIER_LIST_rec.end_date_active := TRUNC(SYSDATE)+100;
l_MODIFIER_LIST_rec.currency_code := 'USD'; -- currency_code in fnd_currencies
l_MODIFIER_LIST_rec.description := 'Single Insert Testing';
l_MODIFIER_LIST_rec.active_flag := 'Y';
l_MODIFIER_LIST_rec.automatic_flag := 'Y';
l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
l_MODIFIER_LIST_rec.operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;
/* Create a Modifier Line to define a New Price for the inventory item id 2834301 */
l_MODIFIERS_tbl(i).modifier_level_code := 'LINE'; -- lookup_code in fnd_lookup_values where lookup_type = 'MODIFIER_LEVEL_CODE'
l_MODIFIERS_tbl(i).start_date_active := TRUNC(SYSDATE);
l_MODIFIERS_tbl(i).end_date_active := TRUNC(SYSDATE)+100;
l_MODIFIERS_tbl(i).list_line_type_code := 'DIS'; -- lookup_code in fnd_lookup_values where lookup_type = 'LIST_LINE_TYPE_CODE'
l_MODIFIERS_tbl(i).accrual_flag := 'N';
l_MODIFIERS_tbl(i).arithmetic_operator := 'NEWPRICE'; -- lookup_code in fnd_lookup_values where lookup_type = 'AMS_QP_ARITHMETIC_OPERATOR'
l_MODIFIERS_tbl(i).operand := 100; -- New price amount
l_MODIFIERS_tbl(i).product_precedence := 220;
l_MODIFIERS_tbl(i).price_break_type_code := 'POINT'; -- lookup_code in fnd_lookup_values where lookup_type = 'PRICE_BREAK_TYPE_CODE'
l_MODIFIERS_tbl(i).automatic_flag := 'Y';
l_MODIFIERS_tbl(i).override_flag := 'N';
l_MODIFIERS_tbl(i).pricing_phase_id := 2; -- pricing_phase_id in qp_pricing_phases
l_MODIFIERS_tbl(i).pricing_group_sequence := 1; -- Bucket
l_MODIFIERS_tbl(i).operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;
l_PRICING_ATTR_tbl(i).product_attribute_context := 'ITEM'; -- prc_context_code in qp_prc_contexts_b where prc_context_type = 'PRODUCT'
l_PRICING_ATTR_tbl(i).product_attribute := 'PRICING_ATTRIBUTE1'; -- segment_mapping_column in qp_segments_b
l_PRICING_ATTR_tbl(i).product_attr_value := '2834301'; -- inventory_item_id in mtl_system_items_b as product_attribute_context is ITEM
l_PRICING_ATTR_tbl(i).product_uom_code := 'EA'; -- uom_code in mtl_units_of_measure
l_PRICING_ATTR_tbl(i).comparison_operator_code := 'BETWEEN';
l_PRICING_ATTR_tbl(i).pricing_attribute_context := 'VOLUME'; -- prc_context_code in qp_prc_contexts_b
l_PRICING_ATTR_tbl(i).pricing_attribute := 'PRICING_ATTRIBUTE10'; -- segment_mapping_column in qp_segments_b
l_PRICING_ATTR_tbl(i).excluder_flag := 'N';
l_PRICING_ATTR_tbl(i).accumulate_flag := 'N';
l_PRICING_ATTR_tbl(i).MODIFIERS_index := 1;
l_PRICING_ATTR_tbl(i).operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;
/* Create a Qualifier Record */
l_QUALIFIERS_tbl(j).excluder_flag := 'N';
l_QUALIFIERS_tbl(j).comparison_operator_code := '=';
l_QUALIFIERS_tbl(j).qualifier_context := 'CUSTOMER'; -- prc_context_code in qp_prc_contexts_b where prc_context_type = 'QUALIFIER'
l_QUALIFIERS_tbl(j).qualifier_attribute := 'QUALIFIER_ATTRIBUTE2'; -- segment_mapping_column in qp_segments_b
l_QUALIFIERS_tbl(j).qualifier_attr_value := '769150'; -- cust_account_id in hz_cust_accounts_all as qualifier_context is CUSTOMER
l_QUALIFIERS_tbl(j).qualifier_grouping_no := 1;
l_QUALIFIERS_tbl(j).qualifier_precedence := 700;
l_QUALIFIERS_tbl(j).start_date_active := TRUNC(SYSDATE);
l_QUALIFIERS_tbl(j).end_date_active := TRUNC(SYSDATE)+100;
l_QUALIFIERS_tbl(j).operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;
/* Call the Modifiers Public API to create the modifier header, lines and Header Level Qualifiers */
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_MODIFIER_LIST_val_rec,
x_MODIFIERS_tbl => l_x_MODIFIERS_tbl,
x_MODIFIERS_val_tbl => l_MODIFIERS_val_tbl,
x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl,
x_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl,
x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl,
x_PRICING_ATTR_val_tbl => l_PRICING_ATTR_val_tbl);
IF l_return_status != 'S'
THEN
ROLLBACK;
RAISE e_problem;
END IF;
EXCEPTION
WHEN e_problem
THEN
IF (x_msg_count > 0)
THEN
FOR l_lcv IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get(p_msg_index => k, p_encoded => 'F');
END LOOP;
DBMS_OUTPUT.PUT_LINE('Error: '||x_msg_data);
END IF;
END;
/
