Scalability Options for XML Publisher Reports - Data Template case

hi All,

The following post is actually a nice solution I found on the web for the issue we were having with several XML publisher reports: Data Template concurrent programs were failing with errors like even before the OPP run:

****Warning!!! Due to high volume of data, got out of memory exception…*** 
****Please retry with scalable option or modify the Data template to run in scalable mode…***

Any changes that were done to the scalability property of the Data Template:

<properties>
    <property name="scalable_mode" value="on"/>
</properties>

 had no effect what so ever...

I saw several solutions for this issue - the only one that did the trick on R12 is as follows:

From System Administrator/Application Developer navigate to Concurrent Program definitions and insert the following string into the Option field: -Xss2048k -Xmx512m

This command allocates additional memory to the Java heap used by Data Template Run.

Link to the original post

Previous solution works well even on 11i, but in both cases, you are restricted by an amount of physical memory you could spend and in case you are creating BIG XML file program still crashes.

Another neat trick that I came across (it was successfully implemented in 11i for VERY big XML) is adding a Scalable Flag as one of the concurrent programs' parameters.

Directions are as follows:

•  Locate relevant Data Template concurrent program. 

At System Administrator/Application Developer responsibility access to Concurrent>Program>Define

•  After the last parameter add a new one named P_SCALABLE_FLAG. 

Description: ScalableFlag  
Prompt: P_SCALABLE_FLAG
Value Set: XLA_SRS_NO_VALIDATION
Default Type: Constant
Default Value: Y

Token: ScalableFlag - do not change the name of the token.

If you have default PL/SQL package defined for Data Template, make sure to declare P_SCALABLE_FLAG as a global type in package specifications.

enjoy,

Felix

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