销售订单行booked后，挑库到待发库，突然决定不再发货，对订单行作backordered,但是订单行的状态没有变为cancle,仍然是picked,导致整个订单无法关闭！
具体操作步骤：
### Steps to Reproduce ###
1)om/order organizer /new sale order i create an sale order ,entry order line ordered item:C.BT.250-100G-2.8.J
02 qty: is 11
2) om/ship transaction/pick release reserved 11.and transer to satged subinventory 'DFK'.
3) later,i don't want to ship it to custmer, in ship transaction form, i do backordered,delivery is clos
ed,but order line status is
picked, in ship transaction form, query it ,line pick status is staged, reserved is not released.
Souliton:
Dwon the script from Metalink
REM $Header: single.sql 115.0 2000/06/15 01:55:37 ysinha noship $
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
set ver off
set feed off
set serveroutput on size 500000
Prompt
accept order_line_id number prompt 'Enter LINE_ID of the Line to Cancel : '
spool &order_line_id
col dtime format a25 heading 'Script run at Date/Time' ;
select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') dtime from dual;
Declare
l_line_id
number := &order_line_id;
l_user_id
number;
l_resp_id
number;
l_resp_appl_id number;
l_wf_exists varchar2(1) := 'Y';
Begin
update oe_order_lines_all
set
ordered_quantity
= 0
,
cancelled_quantity = nvl(cancelled_quantity, 0) + ordered_quantity
,
cancelled_flag
= 'Y'
,
open_flag
= 'N'
,
flow_status_code
= 'CANCELLED'
,
last_updated_by
= -2564702
,
last_update_date
= sysdate
where
line_id
= l_line_id;
Begin
select number_value
into
l_user_id
from
wf_item_attribute_values
where
item_type = 'OEOL'
and
item_key
= to_char(l_line_id)
and
name
= 'USER_ID';
select number_value
into
l_resp_id
from
wf_item_attribute_values
where
item_type = 'OEOL'
and
item_key
= to_char(l_line_id)
and
name
= 'RESPONSIBILITY_ID';
select number_value
into
l_resp_appl_id
from
wf_item_attribute_values
where
item_type = 'OEOL'
and
item_key
= to_char(l_line_id)
and
name
= 'APPLICATION_ID';
Exception
When No_Data_Found Then
l_wf_exists := 'N';
End;
If l_wf_exists = 'Y' Then
fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);
wf_engine.handleerror( OE_Globals.G_WFI_LIN
, to_char(l_line_id)
, 'CLOSE_LINE'
, 'RETRY'
, 'CANCEL'
);
End If;
update wsh_delivery_assignments
set
delivery_id
= null
,
parent_delivery_detail_id = null
,
last_updated_by
= -2564702
,
last_update_date
= sysdate
where
delivery_detail_id
in
(select wdd.delivery_detail_id
from
wsh_delivery_details wdd, oe_order_lines_all oel
where
wdd.source_line_id
= l_line_id
and
wdd.source_code
= 'OE'
and
oel.open_flag
= 'N'
and
oel.shipped_quantity is null
and
oel.ordered_quantity = 0
and
released_status
< 'D');
update wsh_delivery_details
set
released_status
= 'D'
,
src_requested_quantity = 0
,
requested_quantity
= 0
,
shipped_quantity
= 0
,
cycle_count_quantity
= 0
,
cancelled_quantity
= decode(requested_quantity,0,cancelled_quantity,requested_quantity)
,
subinventory
= null
,
locator_id
= null
,
lot_number
= null
,
serial_number
= null
,
revision
= null
,
ship_set_id
= null
,
inv_interfaced_flag
= 'X'
,
oe_interfaced_flag
= 'X'
,
last_updated_by
= -2564702
,
last_update_date
= sysdate
where
delivery_detail_id
in
(select wdd.delivery_detail_id
from
wsh_delivery_details wdd, oe_order_lines_all oel
where
wdd.source_line_id
= l_line_id
and
wdd.source_code
= 'OE'
and
oel.open_flag
= 'N'
and
oel.shipped_quantity is null
and
oel.ordered_quantity = 0
and
released_status
< 'D');
Exception
when others then
rollback;
dbms_output.put_line(substr(sqlerrm, 1, 240));
End;
/
Prompt
Prompt ===========================================================
Prompt You must enter COMMIT to Save changes or ROLLBACK to Revert
Prompt ===========================================================
spool off
……………………………………………………………………………………
$ sqlplus apps/apps @single.sql
SQL*Plus: Release 8.0.6.0.0 - Production on Sat Aug 13 11:11:04 2005
(c) Copyright 1999 Oracle Corporation.
All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Enter LINE_ID of the Line to Cancel : 858580
Script run at Date/Time
-------------------------
13-AUG-2005 11:33:00
===========================================================
You must enter COMMIT to Save changes or ROLLBACK to Revert
===========================================================
SQL commit;
SQL
<span lang="EN-US" style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-font-kerning: 0pt; mso-hansi-font-family: 'Times New Roman'; mso-bidi-f
