Single insert script to create a modifier and qualifier using API in R12

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;
/