Wednesday, May 26, 2010
Price List Import via API (QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST) in R12
Oracle apps allow us to load the price list information via API named QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST.
Below script will help you understand the usage of the above API. This script was tested in R12.1.1 Instance
SET SERVEROUTPUT ON;
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;
BEGIN
v_price_list_rec.list_header_id := 708;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_update;
v_price_list_line_tbl(1).list_header_id := 708;
v_price_list_line_tbl(1).list_line_id := fnd_api.g_miss_num;
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 := 10;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).start_date_active := '30-APR-2011';
v_price_list_line_tbl(1).start_date_active := '29-MAY-2011';
v_price_list_line_tbl(1).organization_id := NULL;
v_pricing_attr_tbl(1).pricing_attribute_id := fnd_api.g_miss_num;
v_pricing_attr_tbl(1).list_line_id := fnd_api.g_miss_num;
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 := '2600';
v_pricing_attr_tbl(1).product_uom_code := 'KG';
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
);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull');
ELSE
DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
ROLLBACK;
FOR i IN 1 .. v_msg_count
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP ;
END IF;
END;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
2 Responses to “Price List Import via API (QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST) in R12”
August 30, 2011 at 3:14 AM
Hi here i'm sending my requirement can u send me code for this...
Should update ‘Pricelist ’ with standard cost.
• If item not in ‘Pricelist’ , program should automatically add new item, cost & other required information.
• Able to update column “Static Formula” with “Cost + 15 %”(Will change in future & need flexibility to change in program/definition of Quick code suggested).
• We need to provide a new lookup code to capture the variables Price list name and formula name.
• Mark-up % is built in the formula and hence it need not be in the lookup code.
• user cst_cost_api.get_item_cost(1,:inventory_item_id, organization_id,NULL,NULL) or getting the current cost of an item
• to check the request_id which is updating the above cst table
• as soon as we can get the list of item whose cost is getting updated we can update our price list
November 26, 2013 at 12:44 AM
Can you please Update the Script for creating the Price Breaks for the List Line crated using above API..????
Post a Comment