Generation of a LOT number for an Inventory item via API in R12

最新推荐文章于 2021-04-03 01:54:25 发布

最新推荐文章于 2021-04-03 01:54:25 发布 · 140 阅读

本文介绍了一种使用Oracle API自动生成库存批次号并将其与特定的商品和组织机构进行关联的方法。通过PL/SQL脚本实现，包括设置上下文、调用API生成批次号以及将批次号插入到指定的商品中。

使用脚本生成批次，并把这个批次关联到的Item和Org
1.使用API，INV_LOT_API_PUB.AUTO_GEN_LOT生成Next Lot Number
2.关联这个批次到一个Item和Org上

DECLARE

  v_object_id NUMBER;
  v_return_status             VARCHAR2(1)      := NULL;
  v_msg_count                 NUMBER           := 0;
  v_msg_data                  VARCHAR2 (2000);
  v_lot_number                VARCHAR2 (50);
  v_expire_date               DATE             := SYSDATE+180;
  v_organization_id           NUMBER           :=124;
  v_inventory_item_id         NUMBER           :=27839;
 
  FUNCTION set_context( i_user_name    IN  VARCHAR2
                       ,i_resp_name    IN  VARCHAR2
                       ,i_org_id       IN  NUMBER)
  RETURN VARCHAR2
  IS
    /* Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here */

  END set_context;


BEGIN
-- Setting the context ----
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
THEN
  DBMS_OUTPUT.PUT_LINE('Error while setting the context');       
END IF;

dbms_output.put_line ('Calling API to Create Lot Number');
v_lot_number := inv_lot_api_pub.auto_gen_lot (
                    p_org_id            => v_organization_id,
                    p_inventory_item_id => v_inventory_item_id,
                    p_parent_lot_number => NULL,
                    p_subinventory_code => NULL,
                    p_locator_id        => NULL,
                    p_api_version       => 1.0,
                    p_init_msg_list     => fnd_api.g_true,
                    p_commit            => fnd_api.g_false,
                    p_validation_level  => fnd_api.g_valid_level_full,
                    x_return_status     => v_return_status,
                    x_msg_count         => v_msg_count,
                    x_msg_data          => v_msg_data
                                           );
IF v_return_status = fnd_api.g_ret_sts_success THEN
   COMMIT;
   DBMS_OUTPUT.put_line ('The Auto generation of Lot Number is Sucessful: '||v_lot_number);
ELSE
   DBMS_OUTPUT.put_line ('The Auto generation of Lot Number Failed');
   ROLLBACK;
   FOR i IN 1 .. v_msg_count
   LOOP
      v_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| v_msg_data);
   END LOOP;
END IF;

IF v_lot_number IS NOT NULL THEN

dbms_output.put_line ('Calling API to Create/Insert a Lot Numbers');

inv_lot_api_pub.insertlot
                   (
                    p_api_version       => 1,
                    p_init_msg_list     => fnd_api.g_false,
                    p_commit            => fnd_api.g_false,
                    p_validation_level  => fnd_api.g_valid_level_full,
                    p_inventory_item_id => v_inventory_item_id,
                    p_organization_id   => v_organization_id,
                    p_lot_number        => v_lot_number,
                    p_expiration_date   => v_expire_date,
                    x_object_id         => v_object_id,
                    x_return_status     => v_return_status,
                    x_msg_count         => v_msg_count,
                    x_msg_data          => v_msg_data
                   );

IF v_return_status = fnd_api.g_ret_sts_success THEN
   COMMIT;
   DBMS_OUTPUT.put_line ('The Creation of Lot Number is Sucessful: '||v_object_id);
ELSE
   DBMS_OUTPUT.put_line ('The Creation of Lot Number Failed');
   ROLLBACK;
   FOR i IN 1 .. v_msg_count
   LOOP
      v_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| v_msg_data);
   END LOOP;
END IF;
END IF;
END;

source:http://www.shareoracleapps.com/2010/10/generation-of-lot-number-for-inventory.html