用interface产生物料信息

1.首先创建 create table xyg_msib_temp
(segment1  VARCHAR2(200),
description VARCHAR2(240),
primary_uom_code VARCHAR2(50),
err_flag VARCHAR2(1)) ;

2将数据插入接口表中

INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
  (PROCESS_FLAG,
   TRANSACTION_TYPE,
   SEGMENT1,
   DESCRIPTION,
   ORGANIZATION_ID,
   TEMPLATE_ID,
   TEMPLATE_NAME,
   SET_PROCESS_ID,
   PRIMARY_UOM_CODE,
   ATTRIBUTE30,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_LOGIN)
  select 1, --PROCESS_FLAG
         'CREATE', -- TRANSACTION_TYPE,请求参数：创建或更新产品：1为创建，2为更新，3为同步
         segment1 ITEM_NUMBER, ---物料编码
         description DESCRIPTION, ---物料说明
         :p_organization_id ORG_ID, ---库存组织
         (select decode(:p_organization_id, 107, Template_ID ,null)
            from mtl_item_templates_vl
           where template_name = :p_template_name) Template_ID, --Need to be modified模板ID
         --01采购物料
         --01工装模板--16085
         decode(:p_organization_id, 107, :p_template_name, null) TEMPLATE_NAME,
         :p_set_process_id_1 SetProccessID,
         t.primary_uom_code,
         TO_CHAR(SYSDATE,'YYYYMMDD')||'.批量导入',   --:p_attribute30, --'20140709.批量导入', --mtl_system_items_b.ATTRIBUTE30
         SYSDATE, -- LAST_UPDATE_DATE,
         -1 X_USER_ID, -- LAST_UPDATED_BY,
         SYSDATE, -- CREATION_DATE,
         -1 X_USER_ID, -- CREATED_BY,
         -1 X_USER_ID -- LAST_UPDATE_LOGIN
    from xyg_msib_temp t
   where t.err_flag is null
     and not exists (select 1
            from mtl_system_items_b b
           where b.organization_id = 107
             and b.segment1 = t.segment1)
     and not exists
   (select 1
            from mtl_system_items_interface ii
           where ii.segment1 = t.segment1
             and ii.set_process_id = :p_set_process_id_1);

3.利用请求导入物料

declare
  l_count  number;
  l_req_id number,

begin

 fnd_global.apps_initialize(user_id      => 11236, --1533
                               resp_id      => 50606, --超白库存超级职责
                               resp_appl_id => 401 --库存应用ID
                               );
 
    l_req_id := fnd_request.submit_request(application => 'INV', --请求对应应用
                                           program     => 'INCOIN', --导入物料请求的简称
                                           description => null,
                                           argument1   => 107,
                                           argument2   => 1,
                                           argument3   => 1,
                                           argument4   => 1,
                                           argument5   => 1,
                                           argument6   => :p_set_process_id_2,
                                           argument7   => 1);
 
    dbms_output.put_line('l_req_id=' || l_req_id);
 
    commit;

 

end ;