EBS R12 INV 接口整理

我很低调嘛

已于 2024-02-23 17:23:39 修改

阅读量234

点赞数

分类专栏： EBS 数据库文章标签：数据库

于 2023-03-29 16:37:38 首次发布

本文链接：https://blog.csdn.net/u012779110/article/details/129274547

版权

EBS 同时被 2 个专栏收录

10 篇文章 4 订阅

订阅专栏

数据库

6 篇文章 3 订阅

订阅专栏

物料创建

DECLARE
  l_user_name    VARCHAR2(240) := 'SYSADMIN';
  l_user_id      NUMBER;
  l_resp_name    VARCHAR2(240) := 'CUX_客户化开发超级职责';
  l_resp_id      NUMBER;
  l_resp_appl_id NUMBER;

  l_master_organization_id NUMBER := 89;

  l_template_id      NUMBER;
  l_item_rec         inv_item_grp.item_rec_type;
  x_item_rec         inv_item_grp.item_rec_type;
  l_error_tbl        inv_item_grp.error_tbl_type;
  l_assign_error_tbl error_handler.error_tbl_type;
  l_return_status    VARCHAR2(1);

  l_category_set_id NUMBER;
  l_old_category_id NUMBER;
  l_new_category_id NUMBER;

  l_errorcode NUMBER;
  l_msg_count NUMBER;
  l_msg_data  VARCHAR2(2000);

  CURSOR cur_assign IS
    SELECT mp.organization_id
      FROM mtl_parameters mp
     WHERE mp.master_organization_id = l_master_organization_id
       AND mp.organization_id <> mp.master_organization_id;

  ---------------------------------------------------
  -- 获取消息队列
  ---------------------------------------------------
  FUNCTION dump_error_stack RETURN VARCHAR2 IS
    l_msg_count     NUMBER;
    l_msg_data      VARCHAR2(2000);
    l_msg_index_out NUMBER;
    x_msg_data      VARCHAR2(4000);
  BEGIN
  
    x_msg_data := NULL;
  
    fnd_msg_pub.count_and_get(p_count => l_msg_count
                             ,p_data  => l_msg_data);
  
    FOR l_ind IN 1 .. l_msg_count LOOP
      fnd_msg_pub.get(p_msg_index     => l_ind
                     ,p_encoded       => fnd_api.g_false
                     ,p_data          => l_msg_data
                     ,p_msg_index_out => l_msg_index_out);
    
      x_msg_data := ltrim(x_msg_data || ' ' || l_msg_data);
    END LOOP;
  
    RETURN x_msg_data;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END dump_error_stack;
BEGIN
  SELECT fu.user_id
    INTO l_user_id
    FROM fnd_user fu
   WHERE fu.user_name = l_user_name;

  SELECT fr.responsibility_id
        ,fr.application_id
    INTO l_resp_id
        ,l_resp_appl_id
    FROM fnd_responsibility_vl fr
   WHERE fr.responsibility_name = l_resp_name;

  fnd_global.apps_initialize(user_id      => l_user_id
                            ,resp_id      => l_resp_id
                            ,resp_appl_id => l_resp_appl_id);

  l_error_tbl.delete;
  --Item
  l_item_rec.organization_id := l_master_organization_id; --主组织
  l_item_rec.segment1        := 'QYC2024022305'; --物料编码
  l_item_rec.description     := '测试物料';

  --物料有200+的属性，按需赋值，以下是根据不同页签下的属性，进行分配
  --主要
  l_item_rec.primary_uom_code           := 'EA'; --单位
  l_item_rec.inventory_item_status_code := 'Active'; --物料状态
  l_item_rec.item_type                  := 'LOW'; --用户物料类型

  --库存
  l_item_rec.inventory_item_flag           := 'Y'; --库存物料
  l_item_rec.stock_enabled_flag            := 'Y'; --可储存
  l_item_rec.mtl_transactions_enabled_flag := 'Y'; --可处理

  l_item_rec.lot_control_code   := 2; --批次控制  1:不控制，2:完全控制
  l_item_rec.lot_split_enabled  := 'Y'; --启用批次分解
  l_item_rec.lot_divisible_flag := 'Y'; --可分批次 

  l_item_rec.serial_number_control_code := 1; --序列生成 1:不控制

  --成本计算
  l_item_rec.costing_enabled_flag           := 'Y'; --启用成本计算
  l_item_rec.inventory_asset_flag           := 'Y'; --启用库存资产值
  l_item_rec.default_include_in_rollup_flag := 'Y'; --包括在累计中

  --采购
  l_item_rec.purchasing_item_flag        := 'Y'; --已采购
  l_item_rec.purchasing_enabled_flag     := 'Y'; --可采购
  l_item_rec.allow_item_desc_update_flag := 'Y'; --允许更新说明  

  --API  
  inv_item_grp.create_item(p_commit        => fnd_api.g_false
                          ,p_item_rec      => l_item_rec
                          ,x_item_rec      => x_item_rec
                          ,x_return_status => l_return_status
                          ,x_error_tbl     => l_error_tbl
                          ,p_template_id   => l_template_id);

  dbms_output.put_line('l_return_status:' || l_return_status);

  --Result
  IF l_return_status <> fnd_api.g_ret_sts_success THEN
    FOR i IN 1 .. l_error_tbl.count LOOP
      dbms_output.put_line('message_text:' || l_error_tbl(i).message_text);
      dbms_output.put_line('table_name:' || l_error_tbl(i).table_name);
      dbms_output.put_line('column_name:' || l_error_tbl(i).column_name);
      dbms_output.put_line('organization_id:' || l_error_tbl(i).organization_id);
    END LOOP;
  
    IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
      RAISE fnd_api.g_exc_unexpected_error;
    ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
      RAISE fnd_api.g_exc_error;
    END IF;
  
  ELSE
    dbms_output.put_line('创建成功:' || x_item_rec.item_number);
    dbms_output.put_line('inventory_item_id:' ||
                         x_item_rec.inventory_item_id);
  END IF;

  --更新物料类别
  /*
  如果有需要更新物料类别的，则继续更新
  */
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    --一般新建物料的时候，会默认一个类别
    SELECT mic.category_id
          ,mic.category_set_id
      INTO l_old_category_id
          ,l_category_set_id
      FROM mtl_item_categories mic
     WHERE 1 = 1
       AND mic.inventory_item_id = x_item_rec.inventory_item_id
       AND mic.organization_id = x_item_rec.organization_id;
  
    --获取你要更新的新类别
    SELECT mc.category_id
      INTO l_new_category_id
      FROM mtl_categories_b mc
     WHERE mc.segment1 = '17'
       AND mc.segment2 = '01'
       AND mc.segment3 IS NULL;
  
    --更新物料类别
    inv_item_category_pub.update_category_assignment(p_api_version       => '1.0'
                                                    ,p_init_msg_list     => fnd_api.g_true
                                                    ,p_commit            => fnd_api.g_false
                                                    ,p_category_id       => l_new_category_id
                                                    ,p_old_category_id   => l_old_category_id
                                                    ,p_category_set_id   => l_category_set_id
                                                    ,p_inventory_item_id => x_item_rec.inventory_item_id
                                                    ,p_organization_id   => x_item_rec.organization_id
                                                    ,x_return_status     => l_return_status
                                                    ,x_errorcode         => l_errorcode
                                                    ,x_msg_count         => l_msg_count
                                                    ,x_msg_data          => l_msg_data);
  
    IF l_return_status <> fnd_api.g_ret_sts_success THEN
      dbms_output.put_line('更新物料类别失败:' || dump_error_stack);
      IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
        RAISE fnd_api.g_exc_unexpected_error;
      ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
        RAISE fnd_api.g_exc_error;
      END IF;
    ELSE
      dbms_output.put_line('更新物料类别成功');
    END IF;
  
  END IF;

  --分配物料，以下是全部分配，也可以只分配某个库存组织
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    FOR rec_assign IN cur_assign LOOP
      ego_item_pub.assign_item_to_org(p_api_version       => 1.0
                                     ,p_init_msg_list     => fnd_api.g_true
                                     ,p_commit            => fnd_api.g_false
                                     ,p_inventory_item_id => x_item_rec.inventory_item_id
                                     ,p_item_number       => x_item_rec.item_number
                                     ,p_organization_id   => rec_assign.organization_id
                                     ,p_organization_code => NULL
                                     ,p_primary_uom_code  => NULL
                                     ,x_return_status     => l_return_status
                                     ,x_msg_count         => l_msg_count);
      IF l_return_status <> fnd_api.g_ret_sts_success THEN
        l_assign_error_tbl.delete;
        error_handler.get_message_list(l_assign_error_tbl);
        dbms_output.put_line('分配 ' || rec_assign.organization_id || ' 失败:');
        FOR i IN 1 .. l_assign_error_tbl.count LOOP
          dbms_output.put_line('message_text:' || l_assign_error_tbl(i).message_text);
        END LOOP;
        IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
          RAISE fnd_api.g_exc_unexpected_error;
        ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
          RAISE fnd_api.g_exc_error;
        END IF;
      ELSE
        dbms_output.put_line('分配 ' || rec_assign.organization_id || ' 成功');
      END IF;
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

物料更新

DECLARE
  l_user_name    VARCHAR2(240) := 'SYSADMIN';
  l_user_id      NUMBER;
  l_resp_name    VARCHAR2(240) := 'CUX_客户化开发超级职责';
  l_resp_id      NUMBER;
  l_resp_appl_id NUMBER;

  l_item_rec      inv_item_grp.item_rec_type;
  x_item_rec      inv_item_grp.item_rec_type;
  l_error_tbl     inv_item_grp.error_tbl_type;
  l_return_status VARCHAR2(1);

BEGIN
  SELECT fu.user_id
    INTO l_user_id
    FROM fnd_user fu
   WHERE fu.user_name = l_user_name;

  SELECT fr.responsibility_id
        ,fr.application_id
    INTO l_resp_id
        ,l_resp_appl_id
    FROM fnd_responsibility_vl fr
   WHERE fr.responsibility_name = l_resp_name;

  fnd_global.apps_initialize(user_id      => l_user_id
                            ,resp_id      => l_resp_id
                            ,resp_appl_id => l_resp_appl_id);

  --Item
  l_item_rec.organization_id := 90; --库存组织ID
  l_item_rec.segment1        := '测试物料'; --物料编码

  SELECT msib.inventory_item_id
    INTO l_item_rec.inventory_item_id
    FROM mtl_system_items_b msib
   WHERE msib.segment1 = l_item_rec.segment1
     AND msib.organization_id = l_item_rec.organization_id;

  l_item_rec.attribute1 := 'TEST';

  /*
   SELECT t.control_level
       ,t.user_attribute_name_gui
       ,t.attribute_name
       ,t.*
   FROM mtl_item_attributes_v t
  WHERE 1 = 1
    AND t.control_level = 1 --1：主层  2：组织层
    ;
    物料属性有主层和组织层控制，可根据以上sql查询，对应界面是 库存管理 - 设置 - 物料 - 属性控制
    属性是主层控制时，则只能在主组织下更新，api会自动将值同步到主组织下的其他组织物料，常见属性如 SERIAL_NUMBER_CONTROL_CODE, LOT_CONTROL_CODE；
       当组织层控制时，则各自更新对应组织下的物料属性，互不影响，常见属性如 WIP_SUPPLY_SUBINVENTORY，ATTRIBUTE字段等
  */

  --API
  inv_item_grp.update_item(p_commit        => fnd_api.g_false
                          ,p_item_rec      => l_item_rec
                          ,x_item_rec      => x_item_rec
                          ,x_return_status => l_return_status
                          ,x_error_tbl     => l_error_tbl);

  dbms_output.put_line('l_return_status:' || l_return_status);

  --Result
  IF l_return_status <> fnd_api.g_ret_sts_success THEN
    FOR i IN 1 .. l_error_tbl.count LOOP
      dbms_output.put_line('message_text:' || l_error_tbl(i).message_text);
      dbms_output.put_line('table_name:' || l_error_tbl(i).table_name);
      dbms_output.put_line('column_name:' || l_error_tbl(i).column_name);
      dbms_output.put_line('organization_id:' || l_error_tbl(i).organization_id);
    END LOOP;
  
    IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
      RAISE fnd_api.g_exc_unexpected_error;
    ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
      RAISE fnd_api.g_exc_error;
    END IF;
  
  ELSE
    /*
    另外对于物料描述更新，会根据当前session语言不同而有所差别
    查看当前语言：USERENV('LANG') 
    切换语言：dbms_session.set_nls('NLS_LANGUAGE', '''SIMPLIFIED CHINESE'''); --中文ZHS，有空格，需要引号括起来
              dbms_session.set_nls('NLS_LANGUAGE', 'AMERICAN');  --英语US
    结果如下：
    当前语言是 ZHS 时，仅 mtl_system_items_tl 中的 ZHS 更新
    当前语言是 US 时，仅 mtl_system_items_b 和 mtl_system_items_tl 中的 US 更新
    
    当然，有人会想问新增物料时，tl表会如何：
    无论当前语言是 ZHS 还是 US，tl表都会各自新增两行，只是 SOURCE_LANG 会根据当前语言赋值，简单来说，首先会新增当前语言的一条记录，然后
    系统安装的其他语言根据这原始记录新增
    
    就国内来说，一般不区分语言，想全都更新就手动update下，比如说：
    
    UPDATE mtl_system_items_b msib
       SET msib.description       = 'XXX'
          ,msib.last_update_date  = SYSDATE
          ,msib.last_updated_by   = fnd_global.user_id
          ,msib.last_update_login = fnd_global.login_id
     WHERE msib.organization_id = l_item_rec.organization_id
       AND msib.inventory_item_id = l_item_rec.inventory_item_id;
    
    --语言表
    UPDATE mtl_system_items_tl msit
       SET msit.description       = 'XXX'
          ,msit.last_update_date  = SYSDATE
          ,msit.last_updated_by   = fnd_global.user_id
          ,msit.last_update_login = fnd_global.login_id
     WHERE msit.organization_id = l_item_rec.organization_id
       AND msit.inventory_item_id = l_item_rec.inventory_item_id;   
       
     当然看你实际需要，之前清楚语言对结果的影响即可
    */
  
    dbms_output.put_line('更新成功:' || x_item_rec.item_number);
  END IF;
END;