My Oracle Support Banner

Cannot Cancel Order Lines Using Oe_Order_Pub API (Doc ID 1369826.1)

Last updated on JULY 07, 2023

Applies to:

Oracle Order Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
Cancel Lines
oe_order_pub API


Symptoms

On :  11.5.10.2 version, Trx Changes & Cancellations

ACTUAL BEHAVIOR  

While trying to cancel line using OE_ORDER_PUB, the API returns errors that line cannot be cancelled.
The line can be canceled from the Sales Order form without any error/warning.

The purpose of the OE_LINE_UTIL.Query_Row is to get entire line details in the PL/SQL record format.
The API seems to need some additional value (other than Line_id and canceled-flag) to be provided to cancel a line.
The PL/SQL line record provided by OE_LINE_UTIL.Query_Row seems to have that value and so the cancellation is successful. 
The same detail can be provided without calling OE_LINE_UTIL.Query_Row, however it is not known which particular column or sets of column values is required by the API. 
What additional column information is needed to cancel the line without having to use OE_LINE_UTIL.Query_Row?


EXPECTED BEHAVIOR

What is required for the LOOP right before entering OE_LINE_UTIL.Query_Row for the API. 
Why is the API code requiring the 'OE_LINE_UTIL.Query_Row' parameter?
What is the code change or reason a loop needs to be added?

>>>>> NOT WORKING CODE BEGINS <<<<<

FOR recs IN (SELECT oel.LINE_ID,order_number
FROM CONV.XXCOH_OE_LINE_CONV L ,oe_order_lines_all oel
WHERE oel.line_id=l.line_id
and oel.open_flag='Y'
and ORDER_NUMBER = HDR_RECS.ORDER_NUMBER
--AND exists(select 1
-- from oe_order_lines_all ol
-- where substr(ol.orig_sys_line_ref,INSTR(ol.orig_sys_line_ref,':')+1)=l.line_id
-- and ol.org_id=l.to_org_id
-- and ol.order_source_id=l.order_source_id)
)
LOOP

--OE_LINE_UTIL.Query_Row( p_line_id => recs.line_id,
-- x_line_rec => l_line_rec);

l_line_tbl_index:=l_line_tbl_index+1;
show_msg('Line ID : '|| recs.line_id||'-'||recs.order_number||':'||l_line_tbl_index);

xx_line_tbl(l_line_tbl_index) := l_line_rec;
xx_line_tbl(l_line_tbl_index).header_id := hdr_recs.header_id; --98252; --Optional Parameter
xx_line_tbl(l_line_tbl_index).line_id := recs.line_id; --200869; --Mandatory parameter
xx_line_tbl(l_line_tbl_index).ordered_quantity := 0;
--xx_line_tbl(l_line_tbl_index).cancelled_flag := 'Y';
xx_line_tbl(l_line_tbl_index).change_reason := 'DE-BOOK: MGMT REQUEST';
xx_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;


END LOOP;

---------------------
>>>>> NOT WORKING CODE ENDS <<<<<




>>>>> WORKING CODE BEGINS <<<<<

FOR recs IN (SELECT oel.LINE_ID,order_number
FROM CONV.XXCOH_OE_LINE_CONV L ,oe_order_lines_all oel
WHERE oel.line_id=l.line_id
and oel.open_flag='Y'
and ORDER_NUMBER = HDR_RECS.ORDER_NUMBER
--AND exists(select 1
-- from oe_order_lines_all ol
-- where substr(ol.orig_sys_line_ref,INSTR(ol.orig_sys_line_ref,':')+1)=l.line_id
-- and ol.org_id=l.to_org_id
-- and ol.order_source_id=l.order_source_id)
)
LOOP

OE_LINE_UTIL.Query_Row( p_line_id => recs.line_id,
x_line_rec => l_line_rec);

l_line_tbl_index:=l_line_tbl_index+1;
show_msg('Line ID : '|| recs.line_id||'-'||recs.order_number||':'||l_line_tbl_index);

xx_line_tbl(l_line_tbl_index) := l_line_rec;
xx_line_tbl(l_line_tbl_index).header_id := hdr_recs.header_id; --98252; --Optional Parameter
xx_line_tbl(l_line_tbl_index).line_id := recs.line_id; --200869; --Mandatory parameter
xx_line_tbl(l_line_tbl_index).ordered_quantity := 0;
--xx_line_tbl(l_line_tbl_index).cancelled_flag := 'Y';
xx_line_tbl(l_line_tbl_index).change_reason := 'DE-BOOK: MGMT REQUEST';
xx_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;


END LOOP;

>>>>> WORKING CODE ENDS <<<<<



STEPS TO REPRODUCE

1. Responsibility: Order Management Super User
2. Navigate: Cancel a line using OE_ORDER_PUB api
3. Find the API returns errors that the line cannot be cancelled.
If a Loop is added right before entering OE_LINE_UTIL.Query_Row for the API then the API is able to cancel without error.

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.