Oracle EBS OM RMA销售退货异常处理(Datafix)

declare

  l_line_id     number := 263115;

  l_ordered_qty number;

  cursor line_info is

    select line_id, ordered_quantity

      from oe_order_lines_all ool

     where ool.line_id = l_line_id

       and ool.flow_status_code = ‘AWAITING_RETURN‘

       andexists

     (select‘x‘

              from mtl_material_transactions mmt, rcv_transactions rcv

             where mmt.trx_source_line_id = ool.line_id

               and mmt.transaction_type_id = 15

               and rcv.oe_order_line_id = ool.line_id

               and mmt.rcv_transaction_id = rcv.transaction_id)

       forupdatenowait;

  l_user_id       number;

  l_resp_id       number;

  l_appl_id       number;

  x_return_status varchar2(10);

  x_msg_count     number;

  x_msg_data      varchar2(2000);

begin

  ifnvl(l_line_id, 0) > 0then

    open line_info;

    fetch line_info

      into l_line_id, l_ordered_qty;

    if line_info%notfoundthen

      close line_info;

      dbms_output.put_line(‘Error: Invalid Line Id, Re-enter.‘);

      return;

    endif;

    close line_info;

  else

    return;

  endif;

  Begin

    select number_value

      into l_user_id

      from wf_item_attribute_values

     where item_type = ‘OEOL‘

       and item_key = l_line_id

       andname = ‘USER_ID‘;

    select number_value

      into l_resp_id

      from wf_item_attribute_values

     where item_type = ‘OEOL‘

       and item_key = l_line_id

       andname = ‘RESPONSIBILITY_ID‘;

    select number_value

      into l_appl_id

      from wf_item_attribute_values

     where item_type = ‘OEOL‘

       and item_key = l_line_id

       andname = ‘APPLICATION_ID‘;

  Exception

    When No_Data_Found Then

      dbms_output.put_line(‘Error: Line flow does not exist.‘);

      return;

  End;

  fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);

  update oe_order_lines

     set fulfilled_quantity = null,

         shipped_quantity   = null,

         last_updated_by    = -99999999,

         last_update_date   = sysdate

   where line_id = l_line_id;

  begin

    oe_rma_receiving.push_receiving_info(l_line_id,

                                         l_ordered_qty,

                                         ‘NO PARENT‘,

                                         ‘RECEIVE‘,

                                         ‘N‘,

                                         x_return_status,

                                         x_msg_count,

                                         x_msg_data);

    if x_return_status = ‘S‘then

      oe_rma_receiving.push_receiving_info(l_line_id,

                                           l_ordered_qty,

                                           ‘RECEIVE‘,

                                           ‘DELIVER‘,

                                           ‘N‘,

                                           x_return_status,

                                           x_msg_count,

                                           x_msg_data);

    endif;

    oe_debug_pub.add(‘no. of OE messages :‘ || x_msg_count, 1);

    dbms_output.put_line(‘no. of OE messages :‘ || x_msg_count);

    for k in1 .. x_msg_count loop

      x_msg_data := oe_msg_pub.get(p_msg_index => k, p_encoded => ‘F‘);

      oe_debug_pub.add(substr(x_msg_data, 1, 255));

      oe_debug_pub.add(substr(x_msg_data, 255, length(x_msg_data)));

      dbms_output.put_line(‘Error msg: ‘ || substr(x_msg_data, 1, 200));

    endloop;

    fnd_msg_pub.count_and_get(p_encoded => ‘F‘,

                              p_count   => x_msg_count,

                              p_data    => x_msg_data);

    oe_debug_pub.add(‘no. of FND messages :‘ || x_msg_count, 1);

    dbms_output.put_line(‘no. of FND messages :‘ || x_msg_count);

    for k in1 .. x_msg_count loop

      x_msg_data := fnd_msg_pub.get(p_msg_index => k, p_encoded => ‘F‘);

      dbms_output.put_line(‘Error msg: ‘ || substr(x_msg_data, 1, 200));

      oe_debug_pub.add(substr(x_msg_data, 1, 255));

    endloop;

    if x_return_status <> ‘S‘then

      oe_debug_pub.add(‘Error occurred, rolling back changes.‘, 1);

      dbms_output.put_line(‘Error occurred, please fix the errors and retry.‘);

      rollback;

    else

      commit;

    endif;

  end;

  dbms_output.put_line(‘For details, see OM Debug File: ‘ ||

                       OE_DEBUG_PUB.G_DIR || ‘/‘ || OE_DEBUG_PUB.G_FILE);

end;