使用API取消销售订单Demo

DECLARE
  l_header_rec                 oe_order_pub.header_rec_type;
  l_line_tbl                   oe_order_pub.line_tbl_type;
  l_action_request_tbl         oe_order_pub.request_tbl_type;
  l_header_adj_tbl             oe_order_pub.header_adj_tbl_type;
  l_line_adj_tbl               oe_order_pub.line_adj_tbl_type;
  l_header_scr_tbl             oe_order_pub.header_scredit_tbl_type;
  l_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;
  l_request_rec                oe_order_pub.request_rec_type;
  l_return_status              VARCHAR2(1000);
  l_msg_count                  NUMBER;
  l_msg_data                   VARCHAR2(1000);
  p_api_version_number         NUMBER := 1.0;
  p_init_msg_list              VARCHAR2(10) := fnd_api.g_false;
  p_return_values              VARCHAR2(10) := fnd_api.g_false;
  p_action_commit              VARCHAR2(10) := fnd_api.g_false;
  x_return_status              VARCHAR2(1);
  x_msg_count                  NUMBER;
  x_msg_data                   VARCHAR2(100);
  p_header_rec                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
  p_old_header_rec             oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
  p_header_val_rec             oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
  p_old_header_val_rec         oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
  p_header_adj_tbl             oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
  p_old_header_adj_tbl         oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
  p_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
  p_old_header_adj_val_tbl     oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
  p_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
  p_old_header_price_att_tbl   oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
  p_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
  p_old_header_adj_att_tbl     oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
  p_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
  p_old_header_adj_assoc_tbl   oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
  p_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
  p_old_header_scredit_tbl     oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
  p_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
  p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
  p_line_tbl                   oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
  p_old_line_tbl               oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
  p_line_val_tbl               oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
  p_old_line_val_tbl           oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
  p_line_adj_tbl               oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
  p_old_line_adj_tbl           oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
  p_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
  p_old_line_adj_val_tbl       oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
  p_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
  p_old_line_price_att_tbl     oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
  p_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
  p_old_line_adj_att_tbl       oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
  p_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
  p_old_line_adj_assoc_tbl     oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
  p_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
  p_old_line_scredit_tbl       oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
  p_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
  p_old_line_scredit_val_tbl   oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
  p_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
  p_old_lot_serial_tbl         oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
  p_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
  p_old_lot_serial_val_tbl     oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
  p_action_request_tbl         oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;
  x_header_rec                 oe_order_pub.header_rec_type;
  x_line_tbl                   oe_order_pub.line_tbl_type;
  x_header_val_rec             oe_order_pub.header_val_rec_type;
  x_header_adj_tbl             oe_order_pub.header_adj_tbl_type;
  x_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type;
  x_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type;
  x_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type;
  x_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type;
  x_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type;
  x_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type;
  x_line_val_tbl               oe_order_pub.line_val_tbl_type;
  x_line_adj_tbl               oe_order_pub.line_adj_tbl_type;
  x_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type;
  x_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type;
  x_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type;
  x_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type;
  x_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;
  x_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type;
  x_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type;
  x_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type;
  x_action_request_tbl         oe_order_pub.request_tbl_type;
  x_debug_file                 VARCHAR2(100);
  l_line_tbl_index             NUMBER;
  l_msg_index_out              NUMBER(10);
  --
  lv_legacy_so_no      VARCHAR2(240) := &legacy_so_no;
  ln_header_id         NUMBER;
  lv_orig_sys_document VARCHAR2(50);
  ln_count             NUMBER := 0;
  ln_org_id            number := 0;
  -- WHO columns
  l_user_id        NUMBER := -1;
  l_resp_id        NUMBER := -1;
  l_application_id NUMBER := -1;
  l_row_cnt        NUMBER := 1;
  l_user_name      VARCHAR2(30) := 'SETUP_USER14';
  l_resp_name      VARCHAR2(30) := 'XXWE_MG_USER';
  --
  CURSOR order_line_cur(ln_id NUMBER) IS
    SELECT * FROM oe_order_lines_all ool WHERE ool.header_id = ln_id;
  -- 
BEGIN
  -- Get the user_id
  SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_user_name;

  -- Get the application_id and responsibility_id
  SELECT application_id,
         responsibility_id
    INTO l_application_id,
         l_resp_id
    FROM fnd_responsibility
   WHERE responsibility_key = l_resp_name;

  -- Initialize applications information      
  fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
  dbms_output.put_line('Initialized applications context: ' || l_user_id || ' ' || l_resp_id || ' ' || l_application_id);
  select fnd_global.org_id into ln_org_Id from dual;
  --
  dbms_output.enable(1000000);
  mo_global.init('ONT');
  mo_global.set_policy_context(p_access_mode => 'S', p_org_id => ln_org_id);
  oe_msg_pub.initialize;
  oe_debug_pub.initialize;
  x_debug_file := oe_debug_pub.set_debug_mode('FILE');
  oe_debug_pub.setdebuglevel(5); -- Use 5 for the most debuging output, I warn  you its a lot of data 
  dbms_output.put_line('START OF NEW DEBUG');
  --This is to UPDATE order header
  --header
  BEGIN
    SELECT ooh.header_id,
           ooh.orig_sys_document_ref
      INTO ln_header_id,
           lv_orig_sys_document
      FROM oe_order_headers_all ooh
     WHERE ooh.header_id = lv_legacy_so_no;
  EXCEPTION
    WHEN OTHERS THEN
      l_msg_data := 'Can not find SO need to update!';
      dbms_output.put_line(l_msg_data);
  END;
  l_header_rec                       := oe_order_pub.g_miss_header_rec;
  l_header_rec.header_id             := ln_header_id;
  l_header_rec.operation             := oe_globals.g_opr_update;
  --l_header_rec.orig_sys_document_ref := lv_orig_sys_document || '(disable)';
  --l_header_rec.cust_po_number        := NULL;
  l_header_rec.flow_status_code      := 'CANCELLED';
  l_header_rec.change_reason         := 'Not provided';
  --This is to UPDATE order line 
  FOR order_line_rec IN order_line_cur(ln_header_id) LOOP
    ln_count := ln_count + 1;
    l_line_tbl(ln_count) := oe_order_pub.g_miss_line_rec;
    l_line_tbl(ln_count).line_id := order_line_rec.line_id;
    l_line_tbl(ln_count).operation := oe_globals.g_opr_update;
    --l_line_tbl(ln_count).orig_sys_document_ref := order_line_rec.orig_sys_document_ref || '(disable)';
    --l_line_tbl(ln_count).orig_sys_line_ref := order_line_rec.orig_sys_line_ref || '(disable)';
    l_line_tbl(ln_count).change_reason := 'Not provided';
    l_line_tbl(ln_count).flow_status_code := 'CANCELLED';
    l_line_tbl(ln_count).ordered_quantity := 0;
  END LOOP;
  -- CALL TO PROCESS ORDER 
  oe_order_pub.process_order(p_api_version_number => 1.0,
                             p_init_msg_list      => fnd_api.g_true,
                             p_return_values      => fnd_api.g_true,
                             p_action_commit      => fnd_api.g_false,
                             x_return_status      => l_return_status,
                             x_msg_count          => l_msg_count,
                             x_msg_data           => l_msg_data,
                             p_header_rec         => l_header_rec,
                             p_line_tbl           => l_line_tbl,
                             p_org_id             => ln_org_id,
                             p_action_request_tbl => l_action_request_tbl,
                             -- OUT PARAMETERS 
                             x_header_rec             => x_header_rec,
                             x_header_val_rec         => x_header_val_rec,
                             x_header_adj_tbl         => x_header_adj_tbl,
                             x_header_adj_val_tbl     => x_header_adj_val_tbl,
                             x_header_price_att_tbl   => x_header_price_att_tbl,
                             x_header_adj_att_tbl     => x_header_adj_att_tbl,
                             x_header_adj_assoc_tbl   => x_header_adj_assoc_tbl,
                             x_header_scredit_tbl     => x_header_scredit_tbl,
                             x_header_scredit_val_tbl => x_header_scredit_val_tbl,
                             x_line_tbl               => x_line_tbl,
                             x_line_val_tbl           => x_line_val_tbl,
                             x_line_adj_tbl           => x_line_adj_tbl,
                             x_line_adj_val_tbl       => x_line_adj_val_tbl,
                             x_line_price_att_tbl     => x_line_price_att_tbl,
                             x_line_adj_att_tbl       => x_line_adj_att_tbl,
                             x_line_adj_assoc_tbl     => x_line_adj_assoc_tbl,
                             x_line_scredit_tbl       => x_line_scredit_tbl,
                             x_line_scredit_val_tbl   => x_line_scredit_val_tbl,
                             x_lot_serial_tbl         => x_lot_serial_tbl,
                             x_lot_serial_val_tbl     => x_lot_serial_val_tbl,
                             x_action_request_tbl     => x_action_request_tbl);

  dbms_output.put_line('OM Debug file: ' || oe_debug_pub.g_dir || '/' || oe_debug_pub.g_file);

  -- Retrieve messages 
  FOR i IN 1 .. l_msg_count LOOP
    oe_msg_pub.get(p_msg_index => i, p_encoded => fnd_api.g_false, p_data => l_msg_data, p_msg_index_out => l_msg_index_out);
    dbms_output.put_line('message is: ' || l_msg_data);
    dbms_output.put_line('message index is: ' || l_msg_index_out);
  END LOOP;
  -- Check the return status 
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    dbms_output.put_line('Line Quantity Update Sucessful');
  ELSE
    dbms_output.put_line('Line Quantity update Failed');
  END IF;
END;