Importing Sales Order in Booked Status

DECLARE
   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 ;
   l_return_status      VARCHAR2 ( 1000 ) ;
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2 ( 1000 ) ;
   p_api_version_number NUMBER          :=1.0;
   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;
   x_return_status      VARCHAR2 ( 1 ) ;
   x_msg_count          NUMBER;
   x_msg_data           VARCHAR2 ( 100 ) ;
   x_header_rec OE_ORDER_PUB.Header_Rec_Type                             := OE_ORDER_PUB.G_MISS_HEADER_REC;
   p_old_header_rec OE_ORDER_PUB.Header_Rec_Type                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
   p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type                     := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
   p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type                 := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
   p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
   p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type                 := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
   p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
   p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
   p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
   p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
   p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
   p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
   p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
   p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
   p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
   p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
   p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
   p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
   p_line_tbl OE_ORDER_PUB.Line_Tbl_Type                                 := OE_ORDER_PUB.G_MISS_LINE_TBL;
   p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type                             := OE_ORDER_PUB.G_MISS_LINE_TBL;
   p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type                         := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
   p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
   p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type                         := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
   p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
   p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
   p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
   p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
   p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
   p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
   p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
   p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
   p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
   p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
   p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
   p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
   p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type     := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
   p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
   p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
   p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
   p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
   p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type                    := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
   x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
   x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
   x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
   x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
   x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
   x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
   x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
   x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
   x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
   x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
   x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
   x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
   x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
   x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
   x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
   x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
   x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
   x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
   x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
   X_DEBUG_FILE     VARCHAR2 ( 100 ) ;
   l_line_tbl_index NUMBER;
   l_msg_index_out  NUMBER ( 10 ) ;
BEGIN
   dbms_output.enable ( 1000000 ) ;
   fnd_global.apps_initialize ( 1318, 21623, 660 ) ; -- pass in user_id, responsibility_id, and application_id
   MO_GLOBAL.INIT ( 'ONT' ) ;                           -- Required for R12
   mo_global.set_org_context ( 204, NULL, 'ONT' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   MO_GLOBAL.SET_POLICY_CONTEXT ( 'S', 204 ) ; -- Required for R12
   oe_msg_pub.initialize;
   --oe_debug_pub.initialize;
   --X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ( 'FILE' ) ;
   --oe_debug_pub.SetDebugLevel ( 5 ) ; -- Use 5 for the most debuging output, I warn you its a lot of data
   dbms_output.put_line ( 'START OF NEW DEBUG' ) ;
   --This is to CREATE an order header and an order line
   --Create Header record
   --Initialize header record to missing
   l_header_rec                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
   l_header_rec.TRANSACTIONAL_CURR_CODE := 'USD';
   l_header_rec.pricing_date            := SYSDATE;
   l_header_rec.cust_po_number          := 'BookTestAPI';
   l_header_rec.sold_to_org_id          := 5371;
   l_header_rec.price_list_id           := 1000;
   l_header_rec.ordered_date            := SYSDATE;
   l_header_rec.shipping_method_code    := 'DHL';
   l_header_rec.sold_from_org_id        := 204;
   l_header_rec.ship_from_org_id        := 204;
   l_header_rec.salesrep_id             := 1001;
   l_header_rec.order_type_id           := 1000;
   l_header_rec.operation               := OE_GLOBALS.G_OPR_CREATE;
   l_line_tbl_index                     :=1;
   -- FIRST LINE RECORD
   -- Initialize record to missing
   l_line_tbl ( l_line_tbl_index ) := OE_ORDER_PUB.G_MISS_LINE_REC;
   -- Line attributes
   l_line_tbl ( l_line_tbl_index ).inventory_item_id := 205952;
   l_line_tbl ( l_line_tbl_index ).ordered_quantity  := 10;
   l_line_tbl ( l_line_tbl_index ).ship_from_org_id  := 204;
   l_line_tbl ( l_line_tbl_index ).unit_selling_price  := 101;
   l_line_tbl ( l_line_tbl_index ).unit_list_price  := 101;
   --l_line_tbl ( l_line_tbl_index ) .subinventory      := 'FGI';
   l_line_tbl ( l_line_tbl_index ).calculate_price_flag  := 'Y';
   l_line_tbl ( l_line_tbl_index ).operation         := OE_GLOBALS.G_OPR_CREATE;
   l_line_tbl_index := 1;
   l_action_request_tbl(l_line_tbl_index).request_type := oe_globals.g_book_order;
   l_action_request_tbl(l_line_tbl_index).entity_code := oe_globals.g_entity_header;
  -- l_action_request_tbl(l_line_tbl_index).entity_id := 190383;
   -- CALL TO PROCESS ORDER Check the return status and then commit.
   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 => x_header_rec
                                , x_header_val_rec => x_header_val_rec
                                , x_Header_Adj_tbl => x_Header_Adj_tbl
                                , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
                                , x_Header_price_Att_tbl => x_Header_price_Att_tbl
                                , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
                                , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
                                , x_Header_Scredit_tbl => x_Header_Scredit_tbl
                                , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
                                , x_line_tbl => p_line_tbl
                                , x_line_val_tbl => x_line_val_tbl
                                , x_Line_Adj_tbl => x_Line_Adj_tbl
                                , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
                                , x_Line_price_Att_tbl => x_Line_price_Att_tbl
                                , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
                                , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
                                , x_Line_Scredit_tbl => x_Line_Scredit_tbl
                                , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
                                , x_Lot_Serial_tbl => x_Lot_Serial_tbl
                                , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
                                , x_action_request_tbl => x_action_request_tbl
                                ) ;
   --dbms_output.put_line ( 'OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE ) ;
   dbms_output.put_line ('Header_ID : '||x_header_rec.header_id);
   -- Retrieve messages
   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_out ) ;
      DBMS_OUTPUT.PUT_LINE ( 'message is: ' || l_msg_data ) ;
      DBMS_OUTPUT.PUT_LINE ( 'message index is: ' || l_msg_index_out ) ;
   END LOOP;
   -- Check the return status
   IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
      dbms_output.put_line ( 'Process Order Sucess' ) ;
   ELSE
      dbms_output.put_line ( 'Failed' ) ;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      dbms_output.put_line ( 'Error: '||SQLERRM ) ;  
END;

Create Sales Order Using API(OE_ORDER_PUB)

DECLARE
   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 ;
   l_return_status      VARCHAR2 ( 1000 ) ;
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2 ( 1000 ) ;
   p_api_version_number NUMBER          :=1.0;
   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;
   x_return_status      VARCHAR2 ( 1 ) ;
   x_msg_count          NUMBER;
   x_msg_data           VARCHAR2 ( 100 ) ;
   x_header_rec OE_ORDER_PUB.Header_Rec_Type                             := OE_ORDER_PUB.G_MISS_HEADER_REC;
   p_old_header_rec OE_ORDER_PUB.Header_Rec_Type                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
   p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type                     := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
   p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type                 := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
   p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
   p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type                 := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
   p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
   p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
   p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
   p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
   p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
   p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
   p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
   p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
   p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
   p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
   p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
   p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
   p_line_tbl OE_ORDER_PUB.Line_Tbl_Type                                 := OE_ORDER_PUB.G_MISS_LINE_TBL;
   p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type                             := OE_ORDER_PUB.G_MISS_LINE_TBL;
   p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type                         := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
   p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
   p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type                         := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
   p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
   p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
   p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
   p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
   p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
   p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
   p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
   p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
   p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
   p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
   p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
   p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
   p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type     := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
   p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
   p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
   p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
   p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
   p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type                    := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
   x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
   x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
   x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
   x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
   x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
   x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
   x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
   x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
   x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
   x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
   x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
   x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
   x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
   x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
   x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
   x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
   x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
   x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
   x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
   X_DEBUG_FILE     VARCHAR2 ( 100 ) ;
   l_line_tbl_index NUMBER;
   l_msg_index_out  NUMBER ( 10 ) ;
BEGIN
   dbms_output.enable ( 1000000 ) ;
   fnd_global.apps_initialize ( 1013936, 21623, 660 ) ; -- pass in user_id, responsibility_id, and application_id
   MO_GLOBAL.INIT ( 'ONT' ) ;                           -- Required for R12
   mo_global.set_org_context ( 996, NULL, 'ONT' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   MO_GLOBAL.SET_POLICY_CONTEXT ( 'S', 996 ) ; --(ORG_ID)--Required for R12
   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
   X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ( 'FILE' ) ;
   oe_debug_pub.SetDebugLevel ( 5 ) ; -- Use 5 for the most debuging output, I warn you its a lot of data
   dbms_output.put_line ( 'START OF NEW DEBUG' ) ;
   --This is to CREATE an order header and an order line
   --Create Header record
   --Initialize header record to missing
   l_header_rec                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
   l_header_rec.TRANSACTIONAL_CURR_CODE := 'USD';
   l_header_rec.pricing_date            := SYSDATE;
   l_header_rec.cust_po_number          := 'TestAPI1a';
   l_header_rec.sold_to_org_id          := 3874;
   l_header_rec.price_list_id           := 1696;
   l_header_rec.ordered_date            := SYSDATE;
   l_header_rec.shipping_method_code    := 'DHL';
   l_header_rec.sold_from_org_id        := 204;
   l_header_rec.salesrep_id             := 1001;
   l_header_rec.order_type_id           := 1437;
   l_header_rec.operation               := OE_GLOBALS.G_OPR_CREATE;
   l_line_tbl_index                     :=1;
   -- FIRST LINE RECORD
   -- Initialize record to missing
   l_line_tbl ( l_line_tbl_index ) := OE_ORDER_PUB.G_MISS_LINE_REC;
   -- Line attributes
   l_line_tbl ( l_line_tbl_index ) .inventory_item_id := 2242;
   l_line_tbl ( l_line_tbl_index ) .ordered_quantity  := 2;
   l_line_tbl ( l_line_tbl_index ) .ship_from_org_id  := 1381;
   --l_line_tbl ( l_line_tbl_index ) .subinventory      := 'FGI';
   l_line_tbl ( l_line_tbl_index ) .operation         := OE_GLOBALS.G_OPR_CREATE;
   -- CALL TO PROCESS ORDER Check the return status and then commit.
   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 => x_header_rec
                                , x_header_val_rec => x_header_val_rec
                                , x_Header_Adj_tbl => x_Header_Adj_tbl
                                , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
                                , x_Header_price_Att_tbl => x_Header_price_Att_tbl
                                , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
                                , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
                                , x_Header_Scredit_tbl => x_Header_Scredit_tbl
                                , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
                                , x_line_tbl => p_line_tbl
                                , x_line_val_tbl => x_line_val_tbl
                                , x_Line_Adj_tbl => x_Line_Adj_tbl
                                , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
                                , x_Line_price_Att_tbl => x_Line_price_Att_tbl
                                , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
                                , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
                                , x_Line_Scredit_tbl => x_Line_Scredit_tbl
                                , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
                                , x_Lot_Serial_tbl => x_Lot_Serial_tbl
                                , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
                                , x_action_request_tbl => l_action_request_tbl
                                ) ;
   dbms_output.put_line ( 'OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE ) ;
   dbms_output.put_line ('Header_ID : '||x_header_rec.header_id);
   -- Retrieve messages
   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_out ) ;
      DBMS_OUTPUT.PUT_LINE ( 'message is: ' || l_msg_data ) ;
      DBMS_OUTPUT.PUT_LINE ( 'message index is: ' || l_msg_index_out ) ;
   END LOOP;
   -- Check the return status
   IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
      dbms_output.put_line ( 'Process Order Sucess' ) ;
   ELSE
      dbms_output.put_line ( 'Failed' ) ;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      dbms_output.put_line ( 'Error: '||SQLERRM ) ;
END;
/
COMMIT; 

PL/SQL - Cursors

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc.

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:

• Implicit cursors
• Explicit cursors

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:

Attribute	Description
%FOUND	Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND	The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN	Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT	Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

Example:

We will be using the CUSTOMERS table we had created and used in the previous chapters.

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected:

DECLARE 
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF; 
END;
/

When the above code is executed at SQL prompt, it produces the following result:

6 customers selected

PL/SQL procedure successfully completed.

If you check the records in customers table, you will find that the rows have been updated:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |
|  2 | Khilan   |  25 | Delhi     |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2500.00 |
|  4 | Chaitali |  25 | Mumbai    |  7000.00 |
|  5 | Hardik   |  27 | Bhopal    |  9000.00 |
|  6 | Komal    |  22 | MP        |  5000.00 |
+----+----------+-----+-----------+----------+

Explicit Cursors

Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

• Declaring the cursor for initializing in the memory
• Opening the cursor for allocating memory
• Fetching the cursor for retrieving data
• Closing the cursor to release allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:

CURSOR c_customers IS
   SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:

OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:

CLOSE c_customers;

Example:

Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE
   c_id customers.id%type;
   c_name customers.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   OPEN c_customers;
   LOOP
      FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

1 Ramesh Ahmedabad 
2 Khilan Delhi 
3 kaushik Kota    
4 Chaitali Mumbai 
5 Hardik Bhopal  
6 Komal MP  

PL/SQL procedure successfully completed.

PL/SQL - Procedures

A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.

A subprogram can be created:

• At schema level
• Inside a package
• Inside a PL/SQL block

A schema level subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE or CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.

A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. We will discuss packages in the chapter 'PL/SQL - Packages'.

PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms:

• Functions: these subprograms return a single value, mainly used to compute and return a value.
• Procedures: these subprograms do not return a value directly, mainly used to perform an action.

This chapter is going to cover important aspects of a PL/SQL procedure and we will cover PL/SQL function in next chapter.

Parts of a PL/SQL Subprogram

Each PL/SQL subprogram has a name, and may have a parameter list. Like anonymous PL/SQL blocks and, the named blocks a subprograms will also have following three parts:

S.N.	Parts & Description
1	Declarative Part It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.
2	Executable Part This is a mandatory part and contains statements that perform the designated action.
3	Exception-handling This is again an optional part. It contains the code that handles run-time errors.

Creating a Procedure

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < procedure_body >
END procedure_name;

Where,

• procedure-name specifies the name of the procedure.
• [OR REPLACE] option allows modifying an existing procedure.
• The optional parameter list contains name, mode and types of the parameters. IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.
• procedure-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Example:

The following example creates a simple procedure that displays the string 'Hello World!' on the screen when executed.

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;
/

When above code is executed using SQL prompt, it will produce the following result:

Procedure created.

Executing a Standalone Procedure

A standalone procedure can be called in two ways:

• Using the EXECUTE keyword
• Calling the name of the procedure from a PL/SQL block

The above procedure named 'greetings' can be called with the EXECUTE keyword as:

EXECUTE greetings;

The above call would display:

Hello World

PL/SQL procedure successfully completed.

The procedure can also be called from another PL/SQL block:

BEGIN
   greetings;
END;
/

The above call would display:

Hello World

PL/SQL procedure successfully completed.

Deleting a Standalone Procedure

A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is:

DROP PROCEDURE procedure-name;

So you can drop greetings procedure by using the following statement:

DROP PROCEDURE greetings;

Parameter Modes in PL/SQL Subprograms

S.N.	Parameter Mode & Description
1	IN An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference.
2	OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.
2	IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value.

IN & OUT Mode Example 1

This program finds the minimum of two values, here procedure takes two numbers using IN mode and returns their minimum using OUT parameters.

DECLARE
   a number;
   b number;
   c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END; 

BEGIN
   a:= 23;
   b:= 45;
   findMin(a, b, c);
   dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

 Minimum of (23, 45) : 23

PL/SQL procedure successfully completed.

IN & OUT Mode Example 2

This procedure computes the square of value of a passed value. This example shows how we can use same parameter to accept a value and then return another result.

DECLARE
   a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
  x := x * x;
END; 
BEGIN
   a:= 23;
   squareNum(a);
   dbms_output.put_line(' Square of (23): ' || a);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Square of (23): 529

PL/SQL procedure successfully completed.

Methods for Passing Parameters

Actual parameters could be passed in three ways:

• Positional notation
• Named notation
• Mixed notation

POSITIONAL NOTATION

In positional notation, you can call the procedure as:

findMin(a, b, c, d);

In positional notation, the first actual parameter is substituted for the first formal parameter; the second actual parameter is substituted for the second formal parameter, and so on. So, a is substituted for x, b is substituted for y, c is substituted for z and d is substituted for m.

NAMED NOTATION

In named notation, the actual parameter is associated with the formal parameter using the arrow symbol ( => ). So the procedure call would look like:

findMin(x=>a, y=>b, z=>c, m=>d);

MIXED NOTATION

In mixed notation, you can mix both notations in procedure call; however, the positional notation should precede the named notation.

The following call is legal:

findMin(a, b, c, m=>d);

But this is not legal:

findMin(x=>a, b, c, d);