熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

銷售訂單單行狀態不對訂單不關閉的處理

2013-11-13 22:11:07  來源: Oracle 

  銷售訂單行booked後挑庫到待發庫突然決定不再發貨對訂單行作backordered但是訂單行的狀態沒有變為cancle仍然是picked導致整個訂單無法關閉!
  
  具體操作步驟
  
  ### Steps to Reproduce ###
  )om/order organizer /new sale order i create an sale order entry order line ordered item:CBTGJ
   qty: is
  ) om/ship transaction/pick release reserved and transer to satged subinventory DFK
  ) lateri dont want to ship it to custmer in ship transaction form i do backordereddelivery is clos
  edbut 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: singlesql // :: ysinha noship $
  
  WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
  
  set ver off
  
  set feed off
  
  set serveroutput on size
  
  Prompt
  
  accept order_line_id number prompt Enter LINE_ID of the Line to Cancel :
  
  spool &order_line_id
  
  col dtime format a heading Script run at Date/Time ;
  
  select to_char(sysdate DDMONYYYY 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 varchar() := Y;
  
  Begin
  
  update oe_order_lines_all
  
  set  ordered_quantity  =
  
     cancelled_quantity = nvl(cancelled_quantity ) + ordered_quantity
  
     cancelled_flag   = Y
  
     open_flag     = N
  
     flow_status_code  = CANCELLED
  
     last_updated_by  =
  
     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_globalapps_initialize(l_user_id l_resp_id l_resp_appl_id);
  
  wf_enginehandleerror( OE_GlobalsG_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      =
  
     last_update_date     = sysdate
  
  where delivery_detail_id    in
  
  (select wdddelivery_detail_id
  
  from  wsh_delivery_details wdd oe_order_lines_all oel
  
  where wddsource_line_id  = l_line_id
  
  and  wddsource_code   = OE
  
  and  oelopen_flag    = N
  
  and  oelshipped_quantity is null
  
  and  oelordered_quantity =
  
  and  released_status   <> D);
  
  update wsh_delivery_details
  
  set  released_status    = D
  
     src_requested_quantity =
  
     requested_quantity   =
  
     shipped_quantity    =
  
     cycle_count_quantity  =
  
     cancelled_quantity   = decode(requested_quantitycancelled_quantityrequested_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    =
  
     last_update_date    = sysdate
  
  where delivery_detail_id    in
  
  (select wdddelivery_detail_id
  
  from  wsh_delivery_details wdd oe_order_lines_all oel
  
  where wddsource_line_id  = l_line_id
  
  and  wddsource_code   = OE
  
  and  oelopen_flag    = N
  
  and  oelshipped_quantity is null
  
  and  oelordered_quantity =
  
  and  released_status   <> D);
  
  Exception
  
  when others then
  
  rollback;
  
  dbms_outputput_line(substr(sqlerrm ));
  
  End;
  
  /
  
  Prompt
  
  Prompt ===========================================================
  
  Prompt You must enter COMMIT to Save changes or ROLLBACK to Revert
  
  Prompt ===========================================================
  
  spool off
  
  ……………………………………………………………………………………
  
  $ sqlplus apps/apps @singlesql
  
  SQL*Plus: Release Production on Sat Aug ::
  
  (c) Copyright Oracle Corporation All rights reserved
  
  Connected to:
  
  Oraclei Enterprise Edition Release Production
  
  With the Partitioning option
  
  JServer Release Production
  
  Enter LINE_ID of the Line to Cancel :
  
  Script run at Date/Time
  
  
  
  AUG ::
  
  ===========================================================
  
  You must enter COMMIT to Save changes or ROLLBACK to Revert
  
  ===========================================================
  
  SQL> commit;
  
  SQL>
  
  <span lang=ENUS style=FONTSIZE: pt; FONTFAMILY: 宋體; msofontkerning: pt; msohansifontfamily: Times New Roman; msobidif
From:http://tw.wingwit.com/Article/program/Oracle/201311/18413.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.