Sales order conversion

create or replace procedure XX_SO_CONV_PROC_SAN(ERRBUF VARCHAR2,RETCODE VARCHAR2) is

   cursor C1 is select * from XXOM_SALESO_TEMP_BIN;

   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
  
   fnd_global.apps_initialize ( FND_GLOBAL.USER_ID,FND_GLOBAL.RESP_ID,FND_GLOBAL.RESP_APPL_ID ) ; -- pass in user_id, responsibility_id, and application_id
   MO_GLOBAL.INIT ( 'ONT' ) ;                      
   mo_global.set_org_context ( fnd_global.org_id, NULL, 'ONT' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   MO_GLOBAL.SET_POLICY_CONTEXT ( 'S', fnd_global.org_id) ; -- Required for R12
   oe_msg_pub.initialize;
   
   fnd_file.put_line (fnd_file.log, 'START OF NEW DEBUG' ) ;
   
   --This is to CREATE an order header and an order line
   --Create Header record
   --Initialize header record to missing
    FOR i IN C1 LOOP

   l_header_rec                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
   l_header_rec.TRANSACTIONAL_CURR_CODE := i.TRANSACTIONAL_CURR_CODE;
   l_header_rec.pricing_date            := SYSDATE;
   l_header_rec.invoice_to_org_id        := i.invoice_to_org_id;
   l_header_rec.created_by                  :=FND_GLOBAL.user_id;
   l_header_rec.creation_date              :=sysdate;
   l_header_rec.last_updated_by          :=FND_GLOBAL.user_id;
   l_header_rec.last_update_date         :=sysdate;
   l_header_rec.last_update_login         :=FND_GLOBAL.user_id;
   l_header_rec.order_category_code    := i.order_category;
   l_header_rec.ordered_date               := i.ordered_date;
   l_header_rec.order_source_id           := i.order_source_id;
   l_header_rec.sold_to_org_id          := i.SOLD_TO_ORG_ID;
   l_header_rec.sold_from_org_id        :=i.sold_from_org_id;
   l_header_rec.price_list_id           := i.price_list_id;--1000;
   l_header_rec.ordered_date            := SYSDATE;
   l_header_rec.shipping_method_code    := 'DHL';
   l_header_rec.ship_from_org_id        := i.ship_from_org_id;
   l_header_rec.ship_to_org_id          := i.ship_to_org_id;
   l_header_rec.salesrep_id             := i.salesrep_id;
   l_header_rec.order_type_id           := i.order_type_id;
   l_header_rec.payment_term_id         :=i.payment_term_id;
   l_header_rec.operation               := OE_GLOBALS.G_OPR_CREATE;
   
   -- FIRST LINE RECORD
   -- Initialize record to missing

   l_line_tbl (1) := OE_ORDER_PUB.G_MISS_LINE_REC;

   -- Line attributes
   l_line_tbl(1).line_type_id := i.line_type_id; 
   l_line_tbl (1).inventory_item_id := i.inventory_item_id;
   l_line_tbl (1).ordered_quantity  := i.ordered_quantity;
   l_line_tbl (1).ship_from_org_id  := i.ship_from_org_id;
   l_line_tbl(1).ship_to_org_id := i.ship_to_org_id;
   l_line_tbl(1).sold_to_org_id := i.sold_to_org_id;
   l_line_tbl(1).sold_from_org_id := i.sold_from_org_id;
   l_line_tbl (1).unit_selling_price  := i.unit_selling_price;
   l_line_tbl (1).unit_list_price  := i.unit_list_price;
   l_line_tbl (1).order_source_id := i.order_source_id;
   l_line_tbl(1).orig_sys_document_ref := i.orig_sys_document_ref;
   l_line_tbl(1).orig_sys_line_ref := i.orig_sys_line_ref;   
   l_line_tbl (1).calculate_price_flag  := 'Y';
   l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
   l_line_tbl(1).created_by :=FND_GLOBAL.user_id;
   l_line_tbl(1).creation_date :=sysdate;
   l_line_tbl(1).last_updated_by :=FND_GLOBAL.user_id;
   l_line_tbl(1).last_update_date :=sysdate;
   l_line_tbl(1).last_update_login         :=FND_GLOBAL.user_id;
   l_line_tbl_index := 1;
   l_action_request_tbl(1).request_type := oe_globals.g_book_order;
   l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
  
   -- 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 
                                ) ;
  
   end loop;
   fnd_file.put_line (fnd_file.log,'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 ) ;
      fnd_file.put_line (fnd_file.log,'message is: ' || l_msg_data ) ;
      fnd_file.put_line (fnd_file.log,'message index is: ' || l_msg_index_out ) ;
   END LOOP;
   -- Check the return status
   IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
      fnd_file.put_line (fnd_file.log,'Process Order Sucess' ) ;
      COMMIT;
   ELSE
      fnd_file.put_line (fnd_file.log,'Failed' ) ;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.log, 'Error: '||SQLERRM ) ;   

END XX_SO_CONV_PROC_SAN;




--exec SO_CONV2;