销售订单行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 MetalinkREM $Header: single.sql 115.0 2000/06/15 01:55:37 ysinha noship $ WHENEVER SQLERROR EXIT FAILURE ROLLBACK;set ver offset feed offset serveroutput on size 500000Promptaccept order_line_id number prompt 'Enter LINE_ID of the Line to Cancel : 'spool &order_line_idcol 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;/ PromptPrompt ===========================================================Prompt You must enter COMMIT to Save changes or ROLLBACK to RevertPrompt =========================================================== spool off …………………………………………………………………………………………………………………………………………………………$ sqlplus apps/apps @single.sql SQL*Plus: Release 8.0.6.0.0 - Production on Sat Aug 13 2005 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to:Oracle8i Enterprise Edition Release 8.1.7.4.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - ProductionEnter LINE_ID of the Line to Cancel : 858580Script 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
