Using QP_PRICE_LIST_PUB.Process_Price_List api,allows to import of large volumes of Price List data into QP tables
Takes two record types and six table types as input parameters. Use this API to insert, update, and delete price lists and to set up a price list for a given P_PRICE_LIST_REC record structure.
Usage :
Set up multiple price list lines by giving multiple price list line definitions in the P_ PRICE_LIST_LINE_TBL table structure.
Attach multiple qualifiers at the price list header level by giving multiple qualifiers in the P_QUALIFIERS_TBL table structure.
Attach multiple pricing attributes to price list lines by giving the pricing attributes in the P_PRICING_ATTR_TBL table structure.
Price_List_Rec_Type: Corresponds to the columns in the price list header tables QP_ LIST_HEADERS_B and QP_LIST_HEADERS_TL.
Price_List_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list header table QP_LIST_HEADERS_B, for example, Currency.
Price_List_Line_Rec_Type: Corresponds to columns in the price list line table and related modifiers tables QP_LIST_LINES and QP_RLTD_MODIFIERS.
Price_List_Line_Tbl_Type: Table of Price_List_Line_Rec_Type.
Price_List_Line_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list line table QP_LIST_LINES, for example, Price_By_Formula.
Price_List_Line_Val_Tbl_Type: Table of Price_List_Line_Val_Rec_Type.
Qualifiers_Rec_Type: Corresponds to the columns in the qualifier table QP_QUALIFIERS.
Qualifiers_Tbl_Type: Table of Qualifiers_Rec_Type.
Qualifiers_Val_Rec_Type: Made up of attributes that store the meaning of id or code columns in the qualifiers table QP_QUALIFIERS, for example, Qualifier_Rule.
Qualifiers_Val_Tbl_Type: Table of Qualifiers_Val_Rec_Type.
Pricing_Attr_Rec_Type: Corresponds to the columns in the pricing attributes table QP_ PRICING_ATTRIBUTES.
Pricing_Attr_Tbl_Type: Table of Pricing_Attr_Rec_Type.
Pricing_Attr_Val_Rec_Type: Attributes that store the meaning of id or code columns in the pricing attributes table QP_PRICING_ATTRIBUTES, for example, Accumulate.
Pricing_Attr_Val_Tbl_Type: Table of Pricing_Attr_Val_Rec_Type.
To Create a New Price List:
DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
--apps initilization variables
x_user_id NUMBER :=Fnd_Profile.VALUE('USER_ID');
x_resp_id NUMBER :=fnd_global.resp_id;
x_appl_id NUMBER :=fnd_global.resp_appl_id;
x_org_id NUMBER :=fnd_global.org_id;
x_login_id NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');
BEGIN
fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
DBMS_OUTPUT.PUT_LINE('API Execution Started');
FND_MSG_PUB.INITIALIZE;
--Header Rec
v_price_list_rec.list_header_id := qp_list_headers_b_s.nextval;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_create;
v_price_list_rec.name := 'Example1';
v_price_list_rec.description := 'Creating sample price list';
v_price_list_rec.currency_code :='USD';
--Line Record Values
v_price_list_line_tbl(1).list_header_id := qp_list_headers_b_s.currval;
v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 100;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).modifier_level_code :='LINE';
--Attribute Record Values
v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval;
v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '209955';
v_pricing_attr_tbl(1).product_uom_code := 'Ea';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;
dbms_output.put_line('Calling API to Enter Item Into Price List');
QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
( p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);
COMMIT;
IF (v_return_Status ='S') THEN
DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
DBMS_OUTPUT.PUT_LINE(v_return_status);
DBMS_OUTPUT.PUT_LINE(v_msg_count);
DBMS_OUTPUT.PUT_LINE(v_msg_data);
ELSE
IF v_msg_count > 0 THEN
FOR l_index in 1..v_msg_count LOOP
dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
END;
To update the existing price list with a new price list line:
DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
--apps initilization variables
x_user_id NUMBER :=Fnd_Profile.VALUE('USER_ID');
x_resp_id NUMBER :=fnd_global.resp_id;
x_appl_id NUMBER :=fnd_global.resp_appl_id;
x_org_id NUMBER :=fnd_global.org_id;
x_login_id NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');
BEGIN
fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
DBMS_OUTPUT.PUT_LINE('API Execution Started');
FND_MSG_PUB.INITIALIZE;
--Header Rec
v_price_list_rec.list_header_id := 230566;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_update;
--Line Record Values
v_price_list_line_tbl(1).list_header_id := 230566;
v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 100;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).modifier_level_code :='LINE';
--Attribute Record Values
v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval;
v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '209957';
v_pricing_attr_tbl(1).product_uom_code := 'Ea';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;
dbms_output.put_line('Calling API to Enter Item Into Price List');
QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
( p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);
COMMIT;
IF (v_return_Status ='S') THEN
DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
DBMS_OUTPUT.PUT_LINE(v_return_status);
ELSE
IF v_msg_count > 0 THEN
FOR l_index in 1..v_msg_count LOOP
dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
END;
Reference Links :
http://jyotioraapps.blogspot.com/2009/08/use-of-pricing-api-qppricelistpubproces.html
http://erpschools.com/scripts/
Takes two record types and six table types as input parameters. Use this API to insert, update, and delete price lists and to set up a price list for a given P_PRICE_LIST_REC record structure.
Usage :
Set up multiple price list lines by giving multiple price list line definitions in the P_ PRICE_LIST_LINE_TBL table structure.
Attach multiple qualifiers at the price list header level by giving multiple qualifiers in the P_QUALIFIERS_TBL table structure.
Attach multiple pricing attributes to price list lines by giving the pricing attributes in the P_PRICING_ATTR_TBL table structure.
Price_List_Rec_Type: Corresponds to the columns in the price list header tables QP_ LIST_HEADERS_B and QP_LIST_HEADERS_TL.
Price_List_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list header table QP_LIST_HEADERS_B, for example, Currency.
Price_List_Line_Rec_Type: Corresponds to columns in the price list line table and related modifiers tables QP_LIST_LINES and QP_RLTD_MODIFIERS.
Price_List_Line_Tbl_Type: Table of Price_List_Line_Rec_Type.
Price_List_Line_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list line table QP_LIST_LINES, for example, Price_By_Formula.
Price_List_Line_Val_Tbl_Type: Table of Price_List_Line_Val_Rec_Type.
Qualifiers_Rec_Type: Corresponds to the columns in the qualifier table QP_QUALIFIERS.
Qualifiers_Tbl_Type: Table of Qualifiers_Rec_Type.
Qualifiers_Val_Rec_Type: Made up of attributes that store the meaning of id or code columns in the qualifiers table QP_QUALIFIERS, for example, Qualifier_Rule.
Qualifiers_Val_Tbl_Type: Table of Qualifiers_Val_Rec_Type.
Pricing_Attr_Rec_Type: Corresponds to the columns in the pricing attributes table QP_ PRICING_ATTRIBUTES.
Pricing_Attr_Tbl_Type: Table of Pricing_Attr_Rec_Type.
Pricing_Attr_Val_Rec_Type: Attributes that store the meaning of id or code columns in the pricing attributes table QP_PRICING_ATTRIBUTES, for example, Accumulate.
Pricing_Attr_Val_Tbl_Type: Table of Pricing_Attr_Val_Rec_Type.
To Create a New Price List:
DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
--apps initilization variables
x_user_id NUMBER :=Fnd_Profile.VALUE('USER_ID');
x_resp_id NUMBER :=fnd_global.resp_id;
x_appl_id NUMBER :=fnd_global.resp_appl_id;
x_org_id NUMBER :=fnd_global.org_id;
x_login_id NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');
BEGIN
fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
DBMS_OUTPUT.PUT_LINE('API Execution Started');
FND_MSG_PUB.INITIALIZE;
--Header Rec
v_price_list_rec.list_header_id := qp_list_headers_b_s.nextval;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_create;
v_price_list_rec.name := 'Example1';
v_price_list_rec.description := 'Creating sample price list';
v_price_list_rec.currency_code :='USD';
--Line Record Values
v_price_list_line_tbl(1).list_header_id := qp_list_headers_b_s.currval;
v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 100;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).modifier_level_code :='LINE';
--Attribute Record Values
v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval;
v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '209955';
v_pricing_attr_tbl(1).product_uom_code := 'Ea';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;
dbms_output.put_line('Calling API to Enter Item Into Price List');
QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
( p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);
COMMIT;
IF (v_return_Status ='S') THEN
DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
DBMS_OUTPUT.PUT_LINE(v_return_status);
DBMS_OUTPUT.PUT_LINE(v_msg_count);
DBMS_OUTPUT.PUT_LINE(v_msg_data);
ELSE
IF v_msg_count > 0 THEN
FOR l_index in 1..v_msg_count LOOP
dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
END;
To update the existing price list with a new price list line:
DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
--apps initilization variables
x_user_id NUMBER :=Fnd_Profile.VALUE('USER_ID');
x_resp_id NUMBER :=fnd_global.resp_id;
x_appl_id NUMBER :=fnd_global.resp_appl_id;
x_org_id NUMBER :=fnd_global.org_id;
x_login_id NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');
BEGIN
fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
DBMS_OUTPUT.PUT_LINE('API Execution Started');
FND_MSG_PUB.INITIALIZE;
--Header Rec
v_price_list_rec.list_header_id := 230566;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_update;
--Line Record Values
v_price_list_line_tbl(1).list_header_id := 230566;
v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 100;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).modifier_level_code :='LINE';
--Attribute Record Values
v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval;
v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '209957';
v_pricing_attr_tbl(1).product_uom_code := 'Ea';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;
dbms_output.put_line('Calling API to Enter Item Into Price List');
QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
( p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);
COMMIT;
IF (v_return_Status ='S') THEN
DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
DBMS_OUTPUT.PUT_LINE(v_return_status);
ELSE
IF v_msg_count > 0 THEN
FOR l_index in 1..v_msg_count LOOP
dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
END;
Reference Links :
http://jyotioraapps.blogspot.com/2009/08/use-of-pricing-api-qppricelistpubproces.html
http://erpschools.com/scripts/
2 comments:
I am Getting Error "UOM is invalid" I tried different Name Can you please guide where should I look for this error.
Background: I am adding line in existing pricelist and that pricelist already have one item
HI thanks for your help in advance. issue is given below pls help
Calling API to Enter Item Into Price List
2) The item 66422 is invalid because this item does not exist or belongs to a different operating unit than the one defined in the profile "QP: Item Validation Organization."
2) The item 66422 is invalid because this item does not exist or belongs to a different operating unit than the one defined in the profile "QP: Item Validation Organization."
Post a Comment