Create or Update values on Price List at API

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;

 

Sem título

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;

Sem título

Deixe uma resposta

Powered by WordPress.com. Tema: Baskerville 2 por Anders Noren

Acima ↑

%d blogueiros gostam disto: