Hi, in this post I will show how create and update records on list price at API.
DECLARE
l_return_status VARCHAR2 (20);
x_return_status VARCHAR2 (20);
l_error_data VARCHAR2 (20000);
l_error_message VARCHAR2 (20000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (20000);
l_err_count NUMBER;
i NUMBER;
l_price_list_rec qp_price_list_pub.price_list_rec_type;
l_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
l_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
l_price_list_line_val_tbl qp_price_list_pub.price_list_line_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_price_list_pub.pricing_attr_tbl_type;
l_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
x_price_list_rec qp_price_list_pub.price_list_rec_type;
x_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
x_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
x_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
x_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
x_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
x_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
x_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
BEGIN
l_price_list_line_tbl.DELETE;
l_pricing_attr_tbl.DELETE;
i := 1;
XXFR_PCK_VARIAVEIS_AMBIENTE.inicializar(p_application_short_name => 'ONT',
p_operation_unit_name => 'UO_FRISIA',
p_user_name => 'ALEX.PAGLIARINI');
-------CRIAR REGISTROS ----------
l_price_list_line_tbl(i).list_header_id := 206275;
l_price_list_line_tbl(i).inventory_item_id := 7208;
l_price_list_line_tbl(i).start_date_active := TRUNC(SYSDATE);
l_price_list_line_tbl(i).end_date_active := NULL;
l_price_list_line_tbl(i).operand := 10;
l_price_list_line_tbl(i).created_by := 1139;
l_price_list_line_tbl(i).creation_date := SYSDATE;
l_price_list_line_tbl(i).last_updated_by := 1139;
l_price_list_line_tbl(i).list_line_type_code := 'PLL';
l_price_list_line_tbl(i).arithmetic_operator := 'UNIT_PRICE';
l_price_list_line_tbl(i).last_update_date := SYSDATE;
l_price_list_line_tbl(i).operation := 'CREATE';
l_pricing_attr_tbl(i).list_header_id := 206275;
l_pricing_attr_tbl(i).product_attribute_context := 'ITEM';
l_pricing_attr_tbl(i).product_attribute := 'PRICING_ATTRIBUTE1';
l_pricing_attr_tbl(i).product_attr_value := 7208;
l_pricing_attr_tbl(i).created_by := 1139;
l_pricing_attr_tbl(i).creation_date := SYSDATE;
l_pricing_attr_tbl(i).last_updated_by := 1139;
l_pricing_attr_tbl(i).last_update_date := SYSDATE;
l_pricing_attr_tbl(i).excluder_flag := 'N';
l_pricing_attr_tbl(i).attribute_grouping_no := 1;
l_pricing_attr_tbl(i).price_list_line_index := 1;
l_pricing_attr_tbl(i).operation := 'CREATE';
l_pricing_attr_tbl(i).product_uom_code := 'L';
-------CRIAR REGISTROS -------------------
QP_PRICE_LIST_PUB.process_price_list ( 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 => l_msg_count,
x_msg_data => l_msg_data,
p_price_list_rec => l_price_list_rec,
p_price_list_val_rec => l_price_list_val_rec,
p_price_list_line_tbl => l_price_list_line_tbl,
p_price_list_line_val_tbl => l_price_list_line_val_tbl,
p_qualifiers_tbl => l_qualifiers_tbl,
p_qualifiers_val_tbl => l_qualifiers_val_tbl,
p_pricing_attr_tbl => l_pricing_attr_tbl,
p_pricing_attr_val_tbl => l_pricing_attr_val_tbl,
x_price_list_rec => x_price_list_rec,
x_price_list_val_rec => x_price_list_val_rec,
x_price_list_line_tbl => x_price_list_line_tbl,
x_price_list_line_val_tbl => x_price_list_line_val_tbl,
x_qualifiers_tbl => x_qualifiers_tbl,
x_qualifiers_val_tbl => x_qualifiers_val_tbl,
x_pricing_attr_tbl => x_pricing_attr_tbl,
x_pricing_attr_val_tbl => x_pricing_attr_val_tbl);
dbms_output.put_line ( 'Prepare_end_date_prc - c_return_status : ' || l_return_status);
x_return_status := l_return_status;
IF (l_return_status <> Fnd_Api.g_ret_sts_success) THEN
ROLLBACK;
Oe_Msg_Pub.Count_And_Get (p_count => l_err_count, p_data => l_error_Data);
l_error_data := NULL;
FOR i IN 1 .. l_err_count
LOOP
l_msg_Data :=
Oe_Msg_Pub.Get (p_msg_index => Oe_Msg_Pub.G_NEXT,
p_encoded => Fnd_Api.G_FALSE);
l_error_data := l_error_Data || l_msg_data;
END LOOP;
l_error_message := 'Error in Prepare_end_date_prc :' || l_error_data;
dbms_output.put_line(l_error_message);
ELSE
COMMIT;
END IF;
END;
After if I can update this value from 20 to 10 I run this code below…
DECLARE l_return_status VARCHAR2 (20); x_return_status VARCHAR2 (20); l_error_data VARCHAR2 (20000); l_error_message VARCHAR2 (20000); l_msg_count NUMBER; l_msg_data VARCHAR2 (20000); l_err_count NUMBER; i NUMBER; l_price_list_rec qp_price_list_pub.price_list_rec_type; l_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; l_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; l_price_list_line_val_tbl qp_price_list_pub.price_list_line_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_price_list_pub.pricing_attr_tbl_type; l_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; x_price_list_rec qp_price_list_pub.price_list_rec_type; x_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; x_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; x_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; x_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; x_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; x_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; x_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; BEGIN l_price_list_line_tbl.DELETE; l_pricing_attr_tbl.DELETE; i := 1; XXFR_PCK_VARIAVEIS_AMBIENTE.inicializar(p_application_short_name => 'ONT', p_operation_unit_name => 'UO_FRISIA', p_user_name => 'ALEX.PAGLIARINI'); -----ATUALIZAR REGISTROS---------------- l_price_list_line_tbl(i).list_header_id := 206275; l_price_list_line_tbl(i).list_line_id := 234110; l_price_list_line_tbl(i).end_date_active := TRUNC(SYSDATE); l_price_list_line_tbl(i).operand := 10; l_price_list_line_tbl(i).list_line_type_code := 'PLL'; l_price_list_line_tbl(i).arithmetic_operator := 'UNIT_PRICE'; l_price_list_line_tbl(i).last_updated_by := 1139; l_price_list_line_tbl(i).last_update_date := SYSDATE; l_price_list_line_tbl(i).operation := 'UPDATE'; -----ATUALIZAR REGISTROS---------------- QP_PRICE_LIST_PUB.process_price_list ( 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 => l_msg_count, x_msg_data => l_msg_data, p_price_list_rec => l_price_list_rec, p_price_list_val_rec => l_price_list_val_rec, p_price_list_line_tbl => l_price_list_line_tbl, p_price_list_line_val_tbl => l_price_list_line_val_tbl, p_qualifiers_tbl => l_qualifiers_tbl, p_qualifiers_val_tbl => l_qualifiers_val_tbl, p_pricing_attr_tbl => l_pricing_attr_tbl, p_pricing_attr_val_tbl => l_pricing_attr_val_tbl, x_price_list_rec => x_price_list_rec, x_price_list_val_rec => x_price_list_val_rec, x_price_list_line_tbl => x_price_list_line_tbl, x_price_list_line_val_tbl => x_price_list_line_val_tbl, x_qualifiers_tbl => x_qualifiers_tbl, x_qualifiers_val_tbl => x_qualifiers_val_tbl, x_pricing_attr_tbl => x_pricing_attr_tbl, x_pricing_attr_val_tbl => x_pricing_attr_val_tbl); dbms_output.put_line ( 'Prepare_end_date_prc - c_return_status : ' || l_return_status); x_return_status := l_return_status; IF (l_return_status <> Fnd_Api.g_ret_sts_success) THEN ROLLBACK; Oe_Msg_Pub.Count_And_Get (p_count => l_err_count, p_data => l_error_Data); l_error_data := NULL; FOR i IN 1 .. l_err_count LOOP l_msg_Data := Oe_Msg_Pub.Get (p_msg_index => Oe_Msg_Pub.G_NEXT, p_encoded => Fnd_Api.G_FALSE); l_error_data := l_error_Data || l_msg_data; END LOOP; l_error_message := 'Error in Prepare_end_date_prc :' || l_error_data; dbms_output.put_line(l_error_message); ELSE COMMIT; END IF; END;
Deixe uma resposta