Recently, worked on a task to cancel Order Lines which are aged (n days old). Intially the task seems to be simple with two steps
1. Retreive the Order Lines that are to be Cancelled.
2. Use oe_order_pub.Process_order API to cancel.
Used the following Order header and line records for Process order
Header Rec:
lr_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
Line Table:
lr_line_tbl(ln_count) := OE_ORDER_PUB.G_MISS_LINE_REC;
lr_line_tbl(ln_count).operation := OE_GLOBALS.G_OPR_UPDATE;
lr_line_tbl(ln_count).line_id := Order_rec.ola_line_id;
lr_line_tbl(ln_count).ordered_quantity := 0;
lr_line_tbl(ln_count).change_reason := p_vCancel_code;
lr_line_tbl(ln_count).change_comments := p_vCancel_comments;
Performed the steps as above and found to be working for a single Order. Then tested for a small number of order and the program was working fine.
Then started executing for the orders of one operating unit. There comes an issue
Many of the records errored as “User-Defined Exception in Package oe_line_util Procedure Update_Row”.
When checked there were no pattern of orders for this error. Hence picked an individual order which was errored and executed through the procedure individually. That could be successfully cancelled. Noticed the problem occurs only when executing for huge records.
For some requirement reasons, we didn’t opt for using order interface. Which could have been a right option for processing huge records.
After research, the following resolved the error.
Instead of using G_MISS_LINE_REC, to intialize the line record. Gather a lock on the order line as follows
OE_LINE_UTIL.Lock_Row
( p_line_id => Order_rec.ola_line_id,
p_x_line_rec => l_line_rec,
x_return_status => l_return_status
);
And used the Out parameter Line rec (l_line_rec - which came by locking the order line), to intialize the line record.
lr_line_tbl(ln_count) := l_line_rec;
lr_old_line_tbl(ln_count) := l_line_rec;
lr_line_tbl(ln_count).operation := OE_GLOBALS.G_OPR_UPDATE;
lr_line_tbl(ln_count).ordered_quantity := 0;
lr_line_tbl(ln_count).change_reason := p_vCancel_code;
lr_line_tbl(ln_count).change_comments := p_vCancel_comments;
This change resolved the user defined exception error.
It could now cancel huge number of orders using the API.
