Split Sales Order lines by using OE_ORDER_PUB API

hi All,
Been "meddling" with Order Management APIs on R12 lately a lot. I was tasked to create split of SO line that already has reservations assigned to it:
Consider following example:
One SO line with Qty of 10 has one Delivery Detail line with Qty of 10. Next thing, I am making reservation (with API) of two different Lots 5 and 5 for that lines' Item Qty - so far so good.
Now is where it gets tricky: these Lots has different Grades:  "A" & "B"
Logically, SO line has to be divided in two lines per Lot  for re-pricing purposes ( grade B costs less then A).

Unfortunately, OE_ORDER_PUB.Process_order() we are using for split, destroys the Reservation done for that line...
the only solution I was able to come up with to this issue is redundant custom table that helps me out to re-create the Lots reservations once split is completed.

Well, this is not the topic of this post but OE_ORDER_PUB.
There are several examples on the web for API call - mine works :-)

 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_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_header_rec                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
     x_line_tbl                   oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_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;
     l_line_tbl_index             NUMBER;
  begin
     p_message := '';
     dbms_output.ENABLE(1000000);
     oe_msg_pub.initialize;
     oe_debug_pub.initialize;
     mo_global.init('ONT'); -- Required for R12
     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);

     --This is to UPDATE order line
     l_line_tbl_index := 1;
     -- Changed attributes
     l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
     l_header_rec.header_id := p_header_id; -- header_id of the order
     l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
     l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
     l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;
     l_line_tbl(l_line_tbl_index).split_by := fnd_global.USER_ID; -- user_id
     l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';
     l_line_tbl(l_line_tbl_index).header_id := p_header_id; -- header_id of the order
     l_line_tbl(l_line_tbl_index).line_id := p_order_line_id; -- line_id of the order line
     l_line_tbl(l_line_tbl_index).ordered_quantity := (p_so_line_qty - p_qty_to_split); -- new ordered quantity
     l_line_tbl(l_line_tbl_index).preferred_grade := p_lot_grade;
     l_line_tbl(l_line_tbl_index).change_reason := 'MISC'; -- change reason code
     l_line_tbl_index := 2;
     l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
     l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;
     l_line_tbl(l_line_tbl_index).split_by := fnd_global.USER_ID; -- user_id
     l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';
     l_line_tbl(l_line_tbl_index).header_id := p_header_id;
     l_line_tbl(l_line_tbl_index).split_from_line_id := p_order_line_id; -- line_id of  original line
     l_line_tbl(l_line_tbl_index).inventory_item_id := p_inventory_item_id; -- inventory item id
     l_line_tbl(l_line_tbl_index).ordered_quantity := p_qty_to_split; -- ordered quantity   
     fnd_file.PUT_LINE(fnd_file.log,'    Split_Qty_from_Line() for: '||p_order_line_id); 
     fnd_file.PUT_LINE(fnd_file.log,'    Calling the API to split: '||l_line_tbl(2).ordered_quantity); 
     FND_MSG_PUB.INITIALIZE; 
     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     ,
                                   p_action_commit        => fnd_api.g_true     ,
                                   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               => x_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
                                );
     fnd_file.PUT_LINE(fnd_file.log,'    done with status: '||l_return_status);    
     if l_return_status = fnd_api.G_RET_STS_SUCCESS then
        commit;
        fnd_file.PUT_LINE(fnd_file.log,'    Line Split successfull');
        --l_new_so_line_id := x_line_tbl(2).line_id;
     else
        if l_msg_count > 0 then
           for i in 1 .. l_msg_count loop
    --fnd_file.put_line (fnd_file.log,  fnd_msg_pub.get (i, 'f') );
              fnd_file.PUT_LINE(fnd_file.log,'    ERROR'||fnd_msg_pub.get (i, fnd_api.G_FALSE));
              p_message := p_message ||' ERROR'||fnd_msg_pub.get (i, fnd_api.G_FALSE);
           end loop;
        end if;
        return;
     end if;
     fnd_file.PUT_LINE(fnd_file.log,'    Original Line Id: ' ||x_line_tbl(1).line_id);
     fnd_file.PUT_LINE(fnd_file.log,'    Splitted Line Id: ' ||x_line_tbl(2).line_id);
 end;

Once API run completes two lines created - first the "original" line is going to hold "B" grade lot splited from total "A" qty. API created Delivery Detail Split as well, BUT: grades in Delivery Details Lines have to be updated manually by using WSH_DELIVERY_DETAILS_PUB.update_shipping_attributes():

 declare
     l_changed_attributes wsh_delivery_details_pub.changedattributetabtype;
     l_source_code                varchar2(30) := 'OE';
     l_return_status              VARCHAR2(1000);
     l_msg_count                  NUMBER;
     l_msg_data                   VARCHAR2(1000);
  begin
     dbms_output.ENABLE(1000000);
     oe_msg_pub.initialize;
     oe_debug_pub.initialize;
     mo_global.init('ONT'); -- Required for R12
     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);
     
     l_return_status  :='';
     l_msg_count      := 0;
     l_msg_data       := '';
     fnd_file.PUT_LINE(fnd_file.log,'         Calling WSH_DELIVERY_DETAILS_PUB API to Update Grade');
     l_changed_attributes (1).delivery_detail_id := p_delivery_detail_id;
     l_changed_attributes (1).preferred_grade := p_lot_grade;
     FND_MSG_PUB.INITIALIZE; 
     wsh_delivery_details_pub.update_shipping_attributes(
                        p_api_version_number => 1.0,
                        p_init_msg_list => fnd_api.g_true,
                        p_commit => p_action_commit,
                        x_return_status => l_return_status,
                        x_msg_count => l_msg_count,
                        x_msg_data => l_msg_data,
                        p_changed_attributes => l_changed_attributes,
                        p_source_code => l_source_code);     
     if (l_return_status <> wsh_util_core.g_ret_sts_success)   then
        fnd_file.PUT_LINE(fnd_file.log,'         Failed to Update WSH_DELIVERY_DETAIL for: '||p_delivery_detail_id);
        if l_msg_count > 0 then
           for i in 1 .. l_msg_count loop
              fnd_file.PUT_LINE(fnd_file.log,'         ERROR'||fnd_msg_pub.get (i, fnd_api.G_FALSE));
              p_message := p_message ||' ERROR '||fnd_msg_pub.get (i, fnd_api.G_FALSE);
           end loop;
        end if;
        return;
     else
        fnd_file.PUT_LINE(fnd_file.log,'         Successfully Updated Grade in WSH_DELIVERY_DETAIL for: '||p_delivery_detail_id);
        commit;

    end;

enjoy,


Felix