Cannot Cancel OM Lines Using oe_order_pub API (Doc ID 1369826.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Order Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
***Checked for relevance on 17-Jul-2013***

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 (as mentioned on metalink note) the API returns errors that line cannot be cancelled. The line can be canceled from the Order line form without any error/warning.

The purpose of OE_LINE_UTIL.Query_Row is to get entire line details in the PL/SQL record format and nothing more. 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 cancelation works fine. I can as well programatically provide the same detail without calling OE_LINE_UTIL.Query_Row but I don't know which particular column or sets of column is reqruierd by the API. So providing anything and everything by using 'OE_LINE_UTIL.Query_Row seem to work. But I'm interested to know what additional column information is needed to cancel the line without having to use OE_LINE_UTIL.Query_Row. I'd like to know the development team's feedback on this.

EXPECTED BEHAVIOR

Looking for documentation regarding 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 why we need to add the Loop now?  We don't see any documentation regarding a reason why it's now required to use the api to cancel.

---->>>>> 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. However where is this documented and why is it now required?

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms