Script to Create/Allocate/Transact Move Order

最新推荐文章于 2024-01-30 11:11:47 发布

cnvdkx2837

最新推荐文章于 2024-01-30 11:11:47 发布

阅读量136

点赞数

文章标签：数据库

Creating Move Order

DECLARE
  x_return_status VARCHAR2 (1);
  x_msg_data      VARCHAR2 (4000);
  x_msg_count     NUMBER;
  l_user_id       NUMBER;
  l_resp_id       NUMBER;
  l_appl_id       NUMBER;
  l_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec;
  x_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec;
  l_line_tbl inv_move_order_pub.trolin_tbl_type:= inv_move_order_pub.g_miss_trolin_tbl;
  x_line_tbl inv_move_order_pub.trolin_tbl_type:= inv_move_order_pub.g_miss_trolin_tbl;
  x_hdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
  x_line_val_tbl inv_move_order_pub.trolin_val_tbl_type;
  v_msg_index_out NUMBER;
  l_rsr_type inv_reservation_global.mtl_reservation_tbl_type;
  l_code_combination_id gl_code_combinations.code_combination_id%type;
  CURSOR c_itm_onhand
  IS
    SELECT a.organization_id,
      c.operating_unit org_id,
      a.inventory_item_id,
      b.concatenated_segments,
      a.subinventory_code,
      SUM (a.primary_transaction_quantity) total_onhand
    FROM mtl_onhand_quantities_detail a,
      mtl_system_items_kfv b,
      org_organization_definitions c
    WHERE a.inventory_item_id   = b.inventory_item_id
    AND a.organization_id       = b.organization_id
    AND a.organization_id       = c.organization_id
        AND b.concatenated_segments = \'AS54888\'
    AND c.organization_code     = \'M1\'
    GROUP BY a.organization_id,
      c.operating_unit,
      a.inventory_item_id,
      b.concatenated_segments,
      a.subinventory_code;

BEGIN
  SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = \'MFG\';

  SELECT fr.responsibility_id,
    fr.application_id
  INTO l_resp_id,
    l_appl_id
  FROM fnd_responsibility fr,
    fnd_responsibility_tl frt
  WHERE fr.responsibility_id  = frt.responsibility_id
  AND frt.responsibility_name = \'Manufacturing and Distribution Manager\'; --Responsibility
  fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);


  DBMS_OUTPUT.put_line (\'Creating MO\');

  FOR i IN c_itm_onhand
  LOOP
    mo_global.set_policy_context (\'S\', i.org_id);
    inv_globals.set_org_id (i.organization_id);
    mo_global.init (\'INV\');
    SELECT code_combination_id
    INTO l_code_combination_id
    FROM gl_code_combinations_kfv
    WHERE concatenated_segments = \'01-520-5250-0000-000\';
    l_line_tbl.DELETE;
    x_line_tbl.DELETE;
    l_hdr_rec.date_required               := SYSDATE;
    l_hdr_rec.header_status               := inv_globals.g_to_status_preapproved;
    l_hdr_rec.organization_id             := i.organization_id;
    l_hdr_rec.status_date                 := SYSDATE;
    l_hdr_rec.transaction_type_id         := inv_globals.g_type_transfer_order_issue; --Type:Move Order Issue
    l_hdr_rec.move_order_type             := inv_globals.g_move_order_requisition;
    l_hdr_rec.db_flag                     := fnd_api.g_true;
    l_hdr_rec.operation                   := inv_globals.g_opr_create;
    l_hdr_rec.description                 := \'PTIAN Move Order Test\';
    l_hdr_rec.to_account_id               := l_code_combination_id;
    l_hdr_rec.from_subinventory_code      := i.subinventory_code;
    l_line_tbl (1).date_required          := SYSDATE;
    l_line_tbl (1).inventory_item_id      := i.inventory_item_id;
    l_line_tbl (1).line_id                := fnd_api.g_miss_num;
    l_line_tbl (1).line_number            := 1;
    l_line_tbl (1).line_status            := inv_globals.g_to_status_preapproved;
    l_line_tbl (1).transaction_type_id    := inv_globals.g_type_transfer_order_issue;
    l_line_tbl (1).organization_id        := i.organization_id;
    l_line_tbl (1).quantity               := 11;
    l_line_tbl (1).status_date            := SYSDATE;
    l_line_tbl (1).uom_code               := \'Ea\';
    l_line_tbl (1).db_flag                := fnd_api.g_true;
    l_line_tbl (1).operation              := inv_globals.g_opr_create;
    l_line_tbl (1).from_subinventory_code := i.subinventory_code;
    l_line_tbl (1).to_account_id          := l_code_combination_id;
--  l_line_tbl (1).lot_number             := i.lot_number;
    DBMS_OUTPUT.put_line (\'===================================\');
    DBMS_OUTPUT.put_line (\'Calling INV_MOVE_ORDER_PUB to Create MO\');
    INV_MOVE_ORDER_PUB.PROCESS_MOVE_ORDER (p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_false, p_return_values => fnd_api.g_false, p_commit => fnd_api.g_false, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_trohdr_rec => l_hdr_rec, p_trolin_tbl => l_line_tbl, x_trohdr_rec => x_hdr_rec, x_trohdr_val_rec => x_hdr_val_rec, x_trolin_tbl => x_line_tbl, x_trolin_val_tbl => x_line_val_tbl);
    DBMS_OUTPUT.put_line (x_return_status);
    DBMS_OUTPUT.put_line (x_msg_count);
    IF x_return_status = \'S\' THEN
      COMMIT;
      DBMS_OUTPUT.put_line (\'Move Order Successfully Created\');
      DBMS_OUTPUT.put_line (\'Move Order Number is :=> \'||x_hdr_rec.request_number);
      DBMS_OUTPUT.put_line (\'===================================\');
    ELSE
      ROLLBACK;
      DBMS_OUTPUT.put_line (\'Move Order Creation Failed Due to Following Reasons\');
      DBMS_OUTPUT.put_line (\'===================================\');
    END IF;
    IF x_msg_count > 0 THEN
      FOR v_index IN 1 .. x_msg_count
      LOOP
        fnd_msg_pub.get (p_msg_index => v_index, p_encoded => \'F\', p_data => x_msg_data, p_msg_index_out => v_msg_index_out );
        x_msg_data := SUBSTR (x_msg_data, 1, 200);
        DBMS_OUTPUT.put_line (x_msg_data);
      END LOOP;
    END IF;
  END LOOP;
END;

Allocating Move Order

DECLARE
  l_api_version   NUMBER       := 1.0;
  l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
  l_return_values VARCHAR2 (2) := fnd_api.g_false;
  l_commit        VARCHAR2 (2) := fnd_api.g_false;
  x_return_status VARCHAR2 (2);
  x_msg_count     NUMBER := 0;
  x_msg_data      VARCHAR2 (255);
  l_user_id       NUMBER ;
  l_resp_id       NUMBER ;
  l_appl_id       NUMBER ;
  l_row_cnt       NUMBER := 1;
  l_trohdr_rec inv_move_order_pub.trohdr_rec_type;
  l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
  x_trohdr_rec inv_move_order_pub.trohdr_rec_type;
  x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
  l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes;
  l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
  l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
  x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
  x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
  x_number_of_rows       NUMBER ;
  x_transfer_to_location NUMBER ;
  x_expiration_date      DATE;
  x_transaction_temp_id  NUMBER ;
  CURSOR c_mo_details
  IS
    SELECT mtrh.header_id,
      mtrh.request_number,
      mtrh.move_order_type,
      mtrh.organization_id,
      mtrl.line_id,
      mtrl.line_number,
      mtrl.inventory_item_id,
      mtrl.lot_number,
      mtrl.quantity,
      revision,
      mtrl.from_locator_id,
      (SELECT DISTINCT operating_unit
      FROM org_organization_definitions
      WHERE organization_id = mtrh.organization_id
      ) org_id
  FROM mtl_txn_request_headers mtrh,
    mtl_txn_request_lines mtrl
  WHERE mtrh.header_id     = mtrl.header_id
  AND mtrh.request_number  = \'4076052\'
  AND mtrh.organization_id = 207;
BEGIN
  SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = \'MFG\';
  SELECT responsibility_id,
    application_id
  INTO l_resp_id,
    l_appl_id
  FROM fnd_responsibility_vl
  WHERE responsibility_name = \'Manufacturing and Distribution Manager\';

  fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
  FOR i IN c_mo_details
  LOOP
    mo_global.set_policy_context (\'S\', i.org_id);
    inv_globals.set_org_id (i.organization_id);
    mo_global.init (\'INV\');

    SELECT COUNT (*)
    INTO x_number_of_rows
    FROM mtl_txn_request_lines
    WHERE header_id = i.header_id;
    
    DBMS_OUTPUT.put_line (\'==========================================================\');
    DBMS_OUTPUT.put_line (\'Calling INV_REPLENISH_DETAIL_PUB to Allocate MO\');
    
    -- Allocate each line of the Move Order
    inv_replenish_detail_pub.line_details_pub
    (p_line_id => i.line_id,
    x_number_of_rows => x_number_of_rows,
    x_detailed_qty => i.quantity,
    x_return_status => x_return_status,
    x_msg_count => x_msg_count,
    x_msg_data => x_msg_data,
    x_revision => i.revision,
    x_locator_id => i.from_locator_id,
    x_transfer_to_location => x_transfer_to_location,
    x_lot_number => i.lot_number,
    x_expiration_date => x_expiration_date,
    x_transaction_temp_id => x_transaction_temp_id,
    p_transaction_header_id => NULL,
    p_transaction_mode => NULL,
    p_move_order_type => i.move_order_type,
    p_serial_flag => fnd_api.g_false,
    p_plan_tasks => FALSE,
    p_auto_pick_confirm => FALSE,
    p_commit => FALSE );

    DBMS_OUTPUT.put_line (\'return status:\'||x_return_status);
    DBMS_OUTPUT.put_line (x_msg_data);
    DBMS_OUTPUT.put_line (x_msg_count);
    IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
      DBMS_OUTPUT.put_line (x_msg_data);
    END IF;
    IF (x_return_status = fnd_api.g_ret_sts_success) THEN
      DBMS_OUTPUT.put_line (\'Trx temp ID: \'||x_transaction_temp_id);
    END IF;
    DBMS_OUTPUT.put_line (\'==========================================================\');
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.put_line (\'Exception Occured :\');
  DBMS_OUTPUT.put_line (\'SQLCODE :\'|| SQLERRM);
  DBMS_OUTPUT.put_line (\'=======================================================\');
END;

Transacting Move Order

DECLARE
  l_api_version      NUMBER       := 1.0;
  l_init_msg_list    VARCHAR2 (2) := fnd_api.g_true;
  l_commit           VARCHAR2 (2) := fnd_api.g_false;
  x_return_status    VARCHAR2 (2);
  x_msg_count        NUMBER := 0;
  x_msg_data         VARCHAR2 (255);
  l_move_order_type  NUMBER := 1;
  l_transaction_mode NUMBER := 1;
  l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
  l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
  x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
  x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
  l_transaction_date DATE := SYSDATE;
  l_user_id          NUMBER;
  l_resp_id          NUMBER;
  l_appl_id          NUMBER;
  CURSOR c_mo_details
  IS
    SELECT mtrh.header_id,
      mtrh.request_number,
      mtrh.move_order_type,
      mtrh.organization_id,
      mtrl.line_id,
      mtrl.line_number,
      mtrl.inventory_item_id,
      mtrl.lot_number,
      mtrl.quantity,
      revision,
      mtrl.from_locator_id,
      (SELECT DISTINCT operating_unit
      FROM org_organization_definitions
      WHERE organization_id = mtrh.organization_id
      ) org_id
  FROM mtl_txn_request_headers mtrh,
    mtl_txn_request_lines mtrl
  WHERE mtrh.header_id     = mtrl.header_id
  AND mtrh.request_number  = \'4076052\'
  AND mtrh.organization_id = 207;
BEGIN
  SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = \'MFG\';

  SELECT responsibility_id,
    application_id
  INTO l_resp_id,
    l_appl_id
  FROM fnd_responsibility_vl
  WHERE responsibility_name = \'Manufacturing and Distribution Manager\';

  fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
  
  FOR i IN c_mo_details
  LOOP
    mo_global.set_policy_context (\'S\', i.org_id);
    inv_globals.set_org_id (i.organization_id);
    mo_global.init (\'INV\');
    l_trolin_tbl (1).line_id := i.line_id;
    -- call API to create move order header
    DBMS_OUTPUT.put_line (\'=======================================================\');
    DBMS_OUTPUT.put_line (\'Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API\');
    inv_pick_wave_pick_confirm_pub.pick_confirm (p_api_version_number => l_api_version, p_init_msg_list => l_init_msg_list, p_commit => l_commit, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_move_order_type => i.move_order_type, p_transaction_mode => l_transaction_mode, p_trolin_tbl => l_trolin_tbl, p_mold_tbl => l_mold_tbl, x_mmtt_tbl => x_mmtt_tbl, x_trolin_tbl => x_trolin_tbl, p_transaction_date => l_transaction_date );
    DBMS_OUTPUT.put_line (\'=======================================================\');
    DBMS_OUTPUT.put_line (\'Return Status:\'||x_return_status);
    DBMS_OUTPUT.put_line (x_msg_data);
    DBMS_OUTPUT.put_line (x_msg_count);
    IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
      DBMS_OUTPUT.put_line (x_msg_data);
    END IF;
    DBMS_OUTPUT.put_line (\'=======================================================\');
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.put_line (\'Exception Occured :\');
  DBMS_OUTPUT.put_line (\'SQLCODE :\'|| SQLERRM);
  DBMS_OUTPUT.put_line (\'=======================================================\');
END;

Reference Note:
Note 729998.1:Oracle Inventory Management Application Program Interface (APIs)
Note 729265.1:How To Process Move Orders Using INV_MOVE_ORDER_PUB.
Note 729513.1 How to use APIs: This demonstration walks the user through the API specifics, creating the PL/SQL procedure for using an API and also debugging guidelines.
Note 729261.1 How To Create A Move Order Header Using INV_MOVE_ORDER_PUB.Create_Move_Order_Header API
Note 729263.1 How To Create Move Order Lines Using INV_MOVE_ORDER_PUB.Create_Move_Order_Lines API
Note 729265.1 How To Process Move Orders Using INV_MOVE_ORDER_PUB.Process_Move_Order API

转载于:http://blog.itpub.net/26687597/viewspace-1204356/

cnvdkx2837

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Script to Create/Allocate/Transact Move Order

Creating Move OrderDECLARE x_return_status VARCHAR2 (1); x_msg_data VARCHAR2 (4000); x_msg_count ...
复制链接

扫一扫