二次上线之转料号

系统二次上线，全新的系统，要转入之前系统的料号和BOM资料
转料号代码如下
DECLARE
  l_iface_rec     mtl_system_items_interface%ROWTYPE;
  l_item_rec    inv_item_grp.item_rec_type; 
  x_item_rec    inv_item_grp.item_rec_type; 
  x_error_tbl   inv_item_grp.error_tbl_type;  
  l_template_id number;
  x_return_status VARCHAR2(1);  
     
  cursor c1 is
    select * 
     from mtl_system_items_interface msii
    where /*msii.segment1 IN ('4WXX-7B0090-00','Y5PG-710298-00','3WOP-761564-00')
      AND */MSII.ORGANIZATION_CODE = 'IMO';
    
  BEGIN
    -- x_error_code:='S';

     --l_iface_rec.Template_Id:=;
     --l_iface_rec.Template_Name:=;
     --l_iface_rec.attribute1:=;--默认投料站点 
     --l_iface_rec.attribute2:=;--默认损耗率
     --l_iface_rec.attribute3:=;--旧料号
     --l_iface_rec.attribute4:=;--旧料号描述
     --l_iface_rec.shelf_life_code:=   --是否批次管控 
     --l_iface_rec.Lot_Divisible_Flag:=  --是否批次拆分
     --l_iface_rec.lot_control_code:=  --是否批次过期管控
     --l_iface_rec.MATURITY_DAYS:=  --批次过期管控天数
     --l_iface_rec.SALES_ACCOUNT:=  --销售收入账户
     --l_iface_rec.COST_OF_SALES_ACCOUNT:=  --销售成本账户
     --l_iface_rec.EXPENSE_ACCOUNT:=  --费用账户

   for v1 in c1 loop
      select decode(v1.organization_code,'IMO',82,'ZW',83) INTO l_item_rec.ORGANIZATION_ID FROM DUAL;
    --  l_item_rec.ORGANIZATION_ID:=p_inf_rec.organization_id;
      l_item_rec.ITEM_NUMBER:=V1.segment1;
      l_item_rec.SEGMENT1:=V1.segment1;
      l_item_rec.DESCRIPTION:=V1.description;
      l_item_rec.PRIMARY_UOM_CODE:='PCS';
     l_template_id:=V1.template_id;
     /* l_item_rec.ATTRIBUTE1:=p_inf_rec.ATTRIBUTE1;
      l_item_rec.ATTRIBUTE2:=p_inf_rec.ATTRIBUTE2;
      l_item_rec.ATTRIBUTE3:=p_inf_rec.ATTRIBUTE3;
      l_item_rec.ATTRIBUTE4:=p_inf_rec.ATTRIBUTE4;
      l_item_rec.ATTRIBUTE5:=p_inf_rec.ATTRIBUTE5;--add by jam huang 20160118
      l_item_rec.ATTRIBUTE6:=p_inf_rec.ATTRIBUTE6;--add by jam huang 20160118
      l_item_rec.ATTRIBUTE7:=p_inf_rec.ATTRIBUTE7;--add by jam huang 20160118
      l_item_rec.ATTRIBUTE11:=p_inf_rec.ATTRIBUTE11;--add by jam huang 20160118
      l_item_rec.ATTRIBUTE12:=p_inf_rec.ATTRIBUTE12;--add by jam huang 20160118
      l_item_rec.ATTRIBUTE13:=p_inf_rec.ATTRIBUTE13;*/--add by jam huang 20160118
     /* l_item_rec.SHELF_LIFE_CODE:=p_inf_rec.SHELF_LIFE_CODE;
      l_item_rec.LOT_DIVISIBLE_FLAG:=p_inf_rec.LOT_DIVISIBLE_FLAG;
      l_item_rec.LOT_SPLIT_ENABLED:=p_inf_rec.LOT_DIVISIBLE_FLAG;
      l_item_rec.LOT_CONTROL_CODE:=p_inf_rec.LOT_CONTROL_CODE;
      l_item_rec.SHELF_LIFE_DAYS:=p_inf_rec.SHELF_LIFE_DAYS;
      l_item_rec.SALES_ACCOUNT:=p_inf_rec.SALES_ACCOUNT;
      l_item_rec.COST_OF_SALES_ACCOUNT:=p_inf_rec.COST_OF_SALES_ACCOUNT;
      l_item_rec.EXPENSE_ACCOUNT:=p_inf_rec.EXPENSE_ACCOUNT;*/
      
      select decode(substr(V1.segment1,1,1),'1',3086--成品
             ,'2',3086--成品
             ,'3',3086--成品
             ,'4',3087--虚拟件
             ,'5',3086,--成品
             decode(substr(V1.segment1,2,1),'9',3085,3084) )--JW客供料  else 原物料
        into l_template_id
        from dual;

     -- l_item_rec.SHELF_LIFE_CODE:=p_inf_rec.SHELF_LIFE_CODE;
      if substr(V1.segment1,1,1) in ('1','2','3','5') then
        l_item_rec.LOT_DIVISIBLE_FLAG:='Y';--可分批次  N Y  
     -- l_item_rec.LOT_SPLIT_ENABLED:=p_inf_rec.LOT_DIVISIBLE_FLAG;
        l_item_rec.LOT_CONTROL_CODE:=2;--批次管控   1不管控  2完全管控
      ELSE 
        l_item_rec.LOT_DIVISIBLE_FLAG:='N';--可分批次  N Y  
     -- l_item_rec.LOT_SPLIT_ENABLED:=p_inf_rec.LOT_DIVISIBLE_FLAG;
        l_item_rec.LOT_CONTROL_CODE:=1;--批次管控   1不管控  2完全管控
        SELECT DECODE(V1.ATTRIBUTE1,'SMT',3,'DIP',2,'PK',1,'ASS',2,decode(substr(V1.segment1,1,1),'4',0,2))
          INTO l_item_rec.postprocessing_lead_time  --
          FROM DUAL;
      end if;
     -- l_item_rec.SHELF_LIFE_DAYS:=p_inf_rec.SHELF_LIFE_DAYS;
      select mp.Sales_Account, mp.COST_OF_SALES_ACCOUNT, mp.EXPENSE_ACCOUNT
        into l_item_rec.SALES_ACCOUNT,
             l_item_rec.COST_OF_SALES_ACCOUNT,
             l_item_rec.EXPENSE_ACCOUNT
        from mtl_parameters mp
       where mp.organization_id = 83;
      l_item_rec.buyer_id:=V1.buyer_id;  --add by jam huang 20150302
      l_item_rec.FIXED_LOT_MULTIPLIER:=V1.FIXED_LOT_MULTIPLIER;--add by jam huang 20150302
      l_item_rec.FULL_LEAD_TIME:=V1.FULL_LEAD_TIME;--add by jam huang 20150302
      --l_item_rec.POSTPROCESSING_LEAD_TIME:= V1.postprocessing_lead_time;--add by jam huang20151201
      

      --标准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 => x_return_status, 
                               x_error_tbl     => x_error_tbl, 
                               p_template_id   => l_template_id);
      COMMIT;
      
      end loop;
      DBMS_OUTPUT.put_line(x_return_status);
end;