Cancel Sales Order By using Script -- Unable to Cancell SO Manually

Issue: While cancelling the sales order manually, we are getting below error.

Solution: Please user attached script to cancel the SO.

While running the script, Please pass SO Number SO Header ID as Parameters.

declare

  l_hdr_id        number := &hdr_id;
  l_hdr_key       varchar2(30) := to_char(l_hdr_id);

  l_ordered_qty   number;
  l_flow_exists   varchar2(1);
  l_all_canceled  varchar2(1);
  l_user_id       number;
  l_resp_id       number;
  l_resp_appl_id  number;
  l_heading       varchar2(1) := 'N';

  cursor line_info
  is
  select
    ol.line_id
  , to_char(ol.line_id) l_lin_key
  from  oe_order_lines_all ol
  where ol.header_id     = l_hdr_id
  and  (open_flag        = 'Y'
   or   cancelled_flag   = 'N'
   or   ordered_quantity > 0
   or   flow_status_code <> 'CANCELLED')
  for update nowait;

  cursor wsh_ifaced
  is
  select
    substr(wdd.source_line_number, 1, 15) line_num
  , substr(wdd.item_description, 1, 30) item_name
  , wdd.shipped_quantity
  , wdd.source_line_id line_id
  from  wsh_delivery_details wdd, oe_order_lines_all oel
  where wdd.inv_interfaced_flag     = 'Y'
  and   nvl(wdd.shipped_quantity,0) > 0
  and   oel.line_id                 = wdd.source_line_id
  and   oel.open_flag               = 'N'
  and   oel.ordered_quantity        = 0
  and   wdd.source_header_id        = l_hdr_id
  and   wdd.source_code             = 'OE'
  and   exists
      ( select 'x'
        from  mtl_material_transactions mmt
        where wdd.delivery_detail_id   = mmt.picking_line_id
        and   mmt.trx_source_line_id   = wdd.source_line_id
        and   mmt.transaction_source_type_id in ( 2,8 ));

begin

  dbms_output.put_line(chr(10)||'Updating Order Number: '||&ord_num);

  for line_rec in line_info loop

    dbms_output.put_line('Updating Line ID: '||line_rec.line_id);

    l_flow_exists := 'Y';

    update oe_order_lines_all
    set    flow_status_code   = 'CANCELLED'
    ,      open_flag          = 'N'
    ,      cancelled_flag     = 'Y'
    ,      ordered_quantity   = 0
    ,      cancelled_quantity = ordered_quantity + nvl(cancelled_quantity, 0)
    ,      last_updated_by    = -9999999
    ,      last_update_date   = sysdate
    where  line_id            = line_rec.line_id;

    Begin
      select number_value
      into   l_user_id
      from   wf_item_attribute_values
      where  item_type = 'OEOL'
      and    item_key  = line_rec.l_lin_key
      and    name      = 'USER_ID';

      select number_value
      into   l_resp_id
      from   wf_item_attribute_values
      where  item_type = 'OEOL'
      and    item_key  = line_rec.l_lin_key
      and    name      = 'RESPONSIBILITY_ID';

      select number_value
      into   l_resp_appl_id
      from   wf_item_attribute_values
      where  item_type = 'OEOL'
      and    item_key  = line_rec.l_lin_key
      and    name      = 'APPLICATION_ID';

      Exception
        When No_Data_Found Then
          l_flow_exists := 'N';
    End;

    if l_flow_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
                           , line_rec.l_lin_key
                           , 'CLOSE_LINE'
                           , 'RETRY'
                           , 'CANCEL'
                           );
    end if;

  end loop;

  select decode(count(*), 0, 'Y', 'N')
  into   l_all_canceled
  from   oe_order_lines_all
  where  header_id = l_hdr_id
  and    cancelled_flag = 'N';

  if l_all_canceled = 'Y' then

    l_flow_exists := 'Y';

    update oe_order_headers_all
    set    flow_status_code   = 'CANCELLED'
    ,      open_flag          = 'N'
    ,      cancelled_flag     = 'Y'
    ,      last_updated_by    = -9999999
    ,      last_update_date   = sysdate
    where  header_id          = l_hdr_id
    and   (open_flag         <> 'N'
      or   cancelled_flag    <> 'Y'
      or   flow_status_code  <> 'CANCELLED');

    Begin
      select number_value
      into   l_user_id
      from   wf_item_attribute_values
      where  item_type = 'OEOH'
      and    item_key  = l_hdr_key
      and    name      = 'USER_ID';

      select number_value
      into   l_resp_id
      from   wf_item_attribute_values
      where  item_type = 'OEOH'
      and    item_key  = l_hdr_key
      and    name      = 'RESPONSIBILITY_ID';
 
      select number_value
      into   l_resp_appl_id
      from   wf_item_attribute_values
      where  item_type = 'OEOH'
      and    item_key  = to_char(l_hdr_id)
      and    name      = 'APPLICATION_ID';
 
      Exception
        When No_Data_Found Then
          l_flow_exists := 'N';
    End;

    if l_flow_exists = 'Y' then

      fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);

      wf_engine.handleerror( OE_Globals.G_WFI_HDR
                           , l_hdr_key
                           , 'CLOSE_HEADER'
                           , 'RETRY'
                           , 'CANCEL'
                           );
    end if;

  end if;

  for wsh_ifaced_rec in wsh_ifaced loop
    if l_heading = 'N' then
      dbms_output.put_line(chr(10)||'Following Cancelled Lines have already been Interfaced to Inventory.');
      dbms_output.put_line('Onhand Qty must be manually adjusted for these Items and Quantities.'||chr(10));
      dbms_output.put_line('+---------------+------------------------------+---------------+---------------+');
      dbms_output.put_line('|Line No.       |Item Name                     |    Shipped Qty|        Line Id|');
      dbms_output.put_line('+---------------+------------------------------+---------------+---------------+');
      l_heading := 'Y';
    end if;
    dbms_output.put_line('|'||rpad(wsh_ifaced_rec.line_num, 15)||
                         '|'||rpad(wsh_ifaced_rec.item_name, 30)||
                         '|'||lpad(to_char(wsh_ifaced_rec.shipped_quantity), 15)||
                           '|'||lpad(to_char(wsh_ifaced_rec.line_id), 15)||'|');
  end loop;

  update wsh_delivery_assignments
  set    delivery_id               = null
  ,      parent_delivery_detail_id = null
  ,      last_updated_by           = -9999999
  ,      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   = oel.line_id
          and   wdd.source_header_id = l_hdr_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        = -9999999
  ,      last_update_date       = sysdate
  where source_header_id = l_hdr_id
  and   source_code      = 'OE'
  and   released_status  <> 'D'
  and   exists 
       (select 'x'
        from   oe_order_lines_all oel
        where  oel.header_id        = source_header_id
        and    source_line_id       = oel.line_id
        and    oel.open_flag        = 'N'
        and    oel.shipped_quantity is null
        and    oel.ordered_quantity = 0);

  Exception
    when others then
      rollback;
      dbms_output.put_line(substr(sqlerrm, 1, 240));
end;
/

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Oracle R12 Purchasing Introduction - 1

For Online Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639