Total Pageviews

Sunday, March 10, 2013


Process Order API

Process Order API

Process Order API is a standard API provided by Oracle to Create/Update/Cancel the Order.
Given below is a sample code which highlights the use of the API


l_msg_index_out            NUMBER;
l_excp_exit                EXCEPTION;
l_num_salesrep_id          NUMBER;
l_line_rec                 oe_order_pub.line_rec_type;
l_line_tbl                 oe_order_pub.line_tbl_type;
l_request_tbl              oe_order_pub.request_tbl_type;
l_header_rec               oe_order_pub.header_rec_type;
l_header_val_rec           oe_order_pub.header_val_rec_type;
l_header_adj_tbl           oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type;
l_line_val_tbl             oe_order_pub.line_val_tbl_type;
l_line_adj_tbl             oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type;
l_request_rec              oe_order_pub.request_rec_type;
l_action_request_tbl       oe_order_pub.request_tbl_type;
l_num_tbl_index            NUMBER;
l_chr_return_status        VARCHAR2 (20);           --POA return Status
l_num_msg_count            NUMBER;
l_chr_msg_data             VARCHAR2 (1000);
l_num_msgcntr              NUMBER;

l_num_order_number        NUMBER;
l_num_header_id           NUMBER;
l_num_line_count          NUMBER;
l_chr_error_message       VARCHAR2(1000);
l_num_temp                NUMBER;
l_excp_skip               EXCEPTION;
l_chr_error               VARCHAR2(1000);
BEGIN
   fnd_file.Put_line(fnd_file.LOG,'*********************************************** BOOK ORDER **************************');
   fnd_file.put_line(fnd_file.LOG,'Begining of the procedure Book Order');
   out_chr_errbuf := 'success';

       --Initialize header record
         l_header_rec                   := oe_order_pub.g_miss_header_rec;
        
         l_header_rec.cust_po_number := cust_po_number;
         l_header_rec.ordered_date := g_dte_sysdate;
         l_header_rec.salesrep_id            := -3;
         l_header_rec.order_type_id := 1085; 
         l_header_rec.operation := oe_globals.g_opr_create; --Specifies that Order is getting created
        
         l_header_rec.order_category_code := 'ORDER';
         l_num_tbl_index := 1;
         l_header_rec.booked_flag                    := 'Y';
         l_header_rec.sold_to_org_id                 := customer_id;
         l_header_rec.invoice_to_org_id              := invoice_to_org_id;
         l_header_rec.ship_to_org_id                 := ship_to_org_id;
         l_header_rec.price_list_id                  := price_list_id;
         l_line_tbl (l_num_tbl_index) := oe_order_pub.g_miss_line_rec;
         -- Line attributes
         l_line_tbl (l_num_tbl_index).inventory_item_id      := inventory_item_id;
         l_line_tbl (l_num_tbl_index).ordered_quantity       := quantity;
         l_line_tbl (l_num_tbl_index).line_category_code     := 'ORDER';
         l_line_tbl (l_num_tbl_index).line_type_id           := 1043; --transaction type id
         l_line_tbl (l_num_tbl_index).operation              :=     oe_globals.g_opr_create; -- Create Order Line
         l_action_request_tbl (l_num_tbl_index)              := oe_order_pub.g_miss_request_rec;
         l_action_request_tbl (l_num_tbl_index).request_type := oe_globals.g_book_order;
         l_action_request_tbl (l_num_tbl_index).entity_code  := oe_globals.g_entity_header;
         l_num_tbl_index := l_num_tbl_index + 1;

         oe_order_pub.process_order
                        (p_api_version_number          => 1.0,
                         p_init_msg_list               => fnd_api.g_true,
                         p_return_values               => fnd_api.g_false,
                         x_return_status               => l_chr_return_status,
                         x_msg_count                   => l_num_msg_count,
                         x_msg_data                    => l_chr_msg_data,
                         p_header_rec                  => l_header_rec,
                         p_line_tbl                    => l_line_tbl,
                         p_action_request_tbl          => l_action_request_tbl,
                         x_header_rec                  => l_header_rec,
                         x_header_val_rec              => l_header_val_rec,
                         x_header_adj_tbl              => l_header_adj_tbl,
                         x_header_adj_val_tbl          => l_header_adj_val_tbl,
                         x_header_price_att_tbl        => l_header_price_att_tbl,
                         x_header_adj_att_tbl          => l_header_adj_att_tbl,
                         x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl,
                        x_header_scredit_tbl          => l_header_scredit_tbl,
                         x_header_scredit_val_tbl      => l_header_scredit_val_tbl,
                         x_line_tbl                    => l_line_tbl,
                         x_line_val_tbl                => l_line_val_tbl,
                         x_line_adj_tbl                => l_line_adj_tbl,
                         x_line_adj_val_tbl            => l_line_adj_val_tbl,
                         x_line_price_att_tbl          => l_line_price_att_tbl,
                         x_line_adj_att_tbl            => l_line_adj_att_tbl,
                         x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl,
                         x_line_scredit_tbl            => l_line_scredit_tbl,
                         x_line_scredit_val_tbl        => l_line_scredit_val_tbl,
                         x_lot_serial_tbl              => l_lot_serial_tbl,
                         x_lot_serial_val_tbl          => l_lot_serial_val_tbl,
                         x_action_request_tbl          => l_request_tbl
                        );  
            fnd_file.put_line (fnd_file.LOG,
                                     'Return from the API'
                                  || 'return status'
                                  || l_chr_return_status
                                 );
        COMMIT;

        IF l_chr_return_status <> 'S'
        THEN
           print_api_error_messages (l_chr_error,
                                    'OE_ORDER_PUB.PROCESS_ORDER',
                                     l_chr_return_status,
                                     l_num_msg_count
                                     );
        -- fnd_file.put_line(fnd_file.LOG,'Return from api error Procedure' || l_chr_error);
           l_chr_error_message := l_chr_error;
           RAISE l_excp_skip;
        END IF;
END;


The API can be used to create or update an order. 
In order to create a sales order pass the parameter  l_header_rec.operation := oe_globals.g_opr_create
In order to Update an Order pass the parameter :  l_header_rec.operation := oe_globals.g_opr_update
In order to Cancel an Order Line pass the parameter: l_header_rec.operation := oe_globals.g_opr_update
 when passing the other parameter of the line record,update the Ordered Quantity as 0

When we are calling the Process Order API there are certain mandatory fields which need to be passed eg : order type,customer details and others. and there are other details are derived by the defaulting rules of the Order Management setup, in case any of the values being derived by the defaulting rules is already provided in the API it overrides the defaulting rules value.

No comments:

Post a Comment