Wednesday, July 9, 2014

Oracle Order Creation With API

-------->>>====================================================================
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');
   
    l_header_rec                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
    l_header_rec.operation               := OE_GLOBALS.G_OPR_CREATE;
    -- l_header_rec.order_number            := TO_NUMBER(FND_DOC_SEQ_24_S.NEXTVAL);
    l_header_rec.pricing_date            := SYSDATE;
    l_header_rec.sold_to_org_id          := 1290;
    l_header_rec.price_list_id           := 1000;
    l_header_rec.ordered_date            := SYSDATE;
    l_header_rec.sold_from_org_id        := 204;
    l_header_rec.order_type_id           := 1437;
   
    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;
       
        -------->>>-------------------------------------------------------------                       
        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;
        l_line_tbl (1).inventory_item_id    := 2155;
        l_line_tbl (1).ordered_quantity     := 5;       
        l_line_tbl (1).ship_to_org_id       := 10480;
        l_line_tbl (1).line_type_id         := 1427;
        l_line_tbl (1).ship_from_org_id     := 207;
           
        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 ('Order Line is created');
            COMMIT;           
        ELSE
            DBMS_OUTPUT.PUT_LINE ('Order Line Creation Status Failure');
            RAISE API_ERROR;
            ROLLBACK;
        END IF;
    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 ;

-------->>>===================================================================

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect