Create Order, Order Line and Book the Order through API Script in Oracle Applications

DECLARE

    l_count                     NUMBER;

    l_api_version_number        NUMBER := 1;

    l_return_status             VARCHAR2 (2000);

    l_msg_count                 NUMBER;

    l_msg_data                  VARCHAR2 (2000);

    l_msg_index                 NUMBER;

    API_ERROR                   EXCEPTION;

    X_DEBUG_FILE                VARCHAR2(100); 

    p_init_msg_list             VARCHAR2(10) := FND_API.G_FALSE; 

    p_return_values             VARCHAR2(10) := FND_API.G_FALSE;

    p_action_commit             VARCHAR2(10) := FND_API.G_FALSE;

    l_header_rec                OE_ORDER_PUB.Header_Rec_Type; 

    l_line_tbl                  OE_ORDER_PUB.Line_Tbl_Type;

    l_action_request_tbl        OE_ORDER_PUB.Request_Tbl_Type;

    l_header_adj_tbl            OE_ORDER_PUB.Header_Adj_Tbl_Type;

    l_line_adj_tbl              OE_ORDER_PUB.line_adj_tbl_Type;

    l_header_scr_tbl            OE_ORDER_PUB.Header_Scredit_Tbl_Type;

    l_line_scredit_tbl          OE_ORDER_PUB.Line_Scredit_Tbl_Type;

    l_request_rec               OE_ORDER_PUB.Request_Rec_Type ;

  /***OUT VARIABLES FOR PROCESS_ORDER API***************************/

   l_header_rec_out               oe_order_pub.header_rec_type;

   l_header_val_rec_out           oe_order_pub.header_val_rec_type;

   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;

   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;

   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;

   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;

   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;

   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;

   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;

   l_line_tbl_out                 oe_order_pub.line_tbl_type;

   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;

   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;

   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;

   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;

   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;

   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;

   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;

   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;

   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;

   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;

   l_action_request_tbl_out       oe_order_pub.request_tbl_type;

  

BEGIN

    DBMS_OUTPUT.ENABLE(1000000);

    MO_GLOBAL.INIT('ONT');

    MO_GLOBAL.SET_POLICY_CONTEXT('S', 204); -->Vision Operations

    FND_GLOBAL.APPS_INITIALIZE(1318,21623,660); -->User,Resp,Appl

    OE_MSG_PUB.INITIALIZE;

    OE_DEBUG_PUB.INITIALIZE;

    X_DEBUG_FILE := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');

    OE_DEBUG_PUB.SETDEBUGLEVEL(5);

    DBMS_OUTPUT.PUT_LINE('START OF NEW DEBUG');

   

    --> Header Records

    L_HEADER_REC                         := OE_ORDER_PUB.G_MISS_HEADER_REC;

    L_HEADER_REC.operation               := OE_GLOBALS.G_OPR_CREATE;

    L_HEADER_REC.pricing_date            := SYSDATE;

    L_HEADER_REC.sold_to_org_id          := 1006; -->Computer Service and Rentals(1006)

    L_HEADER_REC.price_list_id           := 1000; -->Corporate

    L_HEADER_REC.ordered_date            := SYSDATE;

    L_HEADER_REC.sold_from_org_id        := 207;

    L_HEADER_REC.order_type_id           := 1000; -->Corporate

    --> Line Records

    L_LINE_TBL (1)                      := OE_ORDER_PUB.G_MISS_LINE_REC;

    L_LINE_TBL (1).OPERATION            := OE_GLOBALS.G_OPR_CREATE;

    l_line_tbl (1).header_id            := l_header_rec_out.header_id;

    l_line_tbl (1).price_list_id        := 1000; -->Corporate

    l_line_tbl (1).inventory_item_id    := 149; -->AS54888

    l_line_tbl (1).ordered_quantity     := 3;      

    l_line_tbl (1).ship_to_org_id       := 1026; -->

    l_line_tbl (1).line_type_id         := 1427;

    l_line_tbl (1).ship_from_org_id     := 207;

    l_line_tbl (1).shipping_method_code := 'DHL';

    l_line_tbl (1).freight_carrier_code := 'DHL';

   

    -->INITIALIZE ACTION REQUEST RECORD

    l_action_request_tbl (1)                 := OE_ORDER_PUB.G_MISS_REQUEST_REC;

    l_action_request_tbl (1).request_type    := oe_globals.g_book_order;

    l_action_request_tbl (1).entity_code     := oe_globals.g_entity_header;   

    --> Calling the API

    OE_ORDER_PUB.PROCESS_ORDER

        (

        p_api_version_number       => 1.0,

        p_init_msg_list            => fnd_api.g_false,

        p_return_values            => fnd_api.g_false,

        p_action_commit            => fnd_api.g_false,

        x_return_status            => l_return_status,

        x_msg_count                => l_msg_count,

        x_msg_data                 => l_msg_data,

       

        p_header_rec               => l_header_rec,

        p_line_tbl                 => l_line_tbl,

        p_action_request_tbl       => l_action_request_tbl,

       

        -- OUT PARAMETERS

        x_header_rec               => l_header_rec_out,

        x_header_val_rec           => l_header_val_rec_out,

        x_header_adj_tbl           => l_header_adj_tbl_out,

        x_header_adj_val_tbl       => l_header_adj_val_tbl_out,

        x_header_price_att_tbl     => l_header_price_att_tbl_out,

        x_header_adj_att_tbl       => l_header_adj_att_tbl_out,

        x_header_adj_assoc_tbl     => l_header_adj_assoc_tbl_out,

        x_header_scredit_tbl       => l_header_scredit_tbl_out,

        x_header_scredit_val_tbl   => l_header_scredit_val_tbl_out,

        x_line_tbl                 => l_line_tbl_out,

        x_line_val_tbl             => l_line_val_tbl_out,

        x_line_adj_tbl             => l_line_adj_tbl_out,

        x_line_adj_val_tbl         => l_line_adj_val_tbl_out,

        x_line_price_att_tbl       => l_line_price_att_tbl_out,

        x_line_adj_att_tbl         => l_line_adj_att_tbl_out,

        x_line_adj_assoc_tbl       => l_line_adj_assoc_tbl_out,

        x_line_scredit_tbl         => l_line_scredit_tbl_out,

        x_line_scredit_val_tbl     => l_line_scredit_val_tbl_out,

        x_lot_serial_tbl           => l_lot_serial_tbl_out,

        x_lot_serial_val_tbl       => l_lot_serial_val_tbl_out,

        x_action_request_tbl       => l_action_request_tbl_out

       );

      

    IF l_return_status = FND_API.G_RET_STS_SUCCESS

    THEN

        DBMS_OUTPUT.PUT_LINE ('Oracle Order is Created, Order Header ID : '||l_header_rec_out.header_id ||', Order Number Is : '||l_header_rec_out.order_number );       

        COMMIT;    

    ELSE

        DBMS_OUTPUT.PUT_LINE ('Order Creation Status Failure');    

        RAISE API_ERROR;

        ROLLBACK;

    END IF;

--> ------------------------------------------------------

--> HANDLE EXCEPTIONS

EXCEPTION

   WHEN API_ERROR

   THEN

      FOR i IN 1 .. l_msg_count

      LOOP

         OE_MSG_PUB.GET (p_msg_index       => i,

                         p_encoded         => Fnd_Api.G_FALSE,

                         p_data            => l_msg_data,

                         p_msg_index_out   => l_msg_index

                        );

         DBMS_OUTPUT.PUT_LINE ('message is: ' || l_msg_data);

         DBMS_OUTPUT.PUT_LINE ('message index is: ' || l_msg_index);

      END LOOP;

     

   WHEN OTHERS

   THEN

      FOR i IN 1 .. l_msg_count

      LOOP

         OE_MSG_PUB.GET (p_msg_index       => i,

                         p_encoded         => Fnd_Api.G_FALSE,

                         p_data            => l_msg_data,

                         p_msg_index_out   => l_msg_index

                        );

         DBMS_OUTPUT.PUT_LINE ('message is: ' || l_msg_data);

         DBMS_OUTPUT.PUT_LINE ('message index is: ' || l_msg_index);

      END LOOP;

     

END ;