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