Oracle erp销售订单订单行状态不对导致订单无法关闭之解决方案！

销售订单行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 $

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