WIP 离散工单导入

DECLARE
  l_iface_rec    wip.wip_job_schedule_interface%ROWTYPE; --工单任务接口表
  l_group_id     NUMBER;
  l_returnstatus VARCHAR2(40);
  l_errormsg     VARCHAR2(2000);
  l_error        VARCHAR2(2000);
BEGIN
  l_group_id := wip.wip_job_schedule_interface_s.NEXTVAL; --每次导入给定唯一的group_id，也可以不给

  fnd_global.APPS_INITIALIZE(user_id      => 0,
                             resp_id      => 20560,
                             resp_appl_id => 706);
  --
  l_iface_rec.last_update_date := to_date('2014-02-01', 'yyyy-mm-dd');
  l_iface_rec.last_updated_by  := fnd_global.user_id;
  l_iface_rec.creation_date    := to_date('2014-02-01', 'yyyy-mm-dd');
  l_iface_rec.created_by       := fnd_global.user_id;
  --
  l_iface_rec.group_id := l_group_id;
  /*================================================
   WIP_LOAD_TYPE        MFG_LOOKUPS
   ------------        --------------
       1              Create Standard Job
       2              Create Repetitive Schedule
       3              Update Discrete Job
       4              Create Non–standard Job
  ================================================*/
  l_iface_rec.load_type       := 1; --接口记录类型
  l_iface_rec.allow_explosion := 'Y'; -- Y自动用标准BOM和Routing展开  -N:需要自定义组件需求和工序，写入wip_job_dtls_interface
  l_iface_rec.process_phase   := '2'; --导入时，此字段为2
  /*Possible PROCESS_PHASE values include:
  2 Validation
  3 Explosion
  4 Complete
  5 Creation*/
  l_iface_rec.process_status := '1'; --导入时，此字段为1
  /*1 Pending
  2 Running
  3 Error
  4 Complete
  5 Warning*/
  l_iface_rec.status_type           := '1'; --工单状态 ：未发放
  l_iface_rec.job_name              := 'li_test001'; --工单名--唯一
  l_iface_rec.organization_id       := 683; --组织id
  l_iface_rec.class_code            := '200-OSA'; --WIP工单类型 分类名
  l_iface_rec.primary_item_id       := 51129; --装配件ID
  l_iface_rec.start_quantity        := 100;
  l_iface_rec.scheduling_method     := '1';
  l_iface_rec.first_unit_start_date := SYSDATE;
  l_iface_rec.source_code           := 'li test'; --来源代码，随便给，一般是来源系统名
  --l_iface_rec.source_line_id        := 987654321;

  INSERT INTO wip.wip_job_schedule_interface VALUES l_iface_rec;
  --API
  wip_massload_pub.massloadjobs(p_groupid         => l_group_id, -- Group ID
                                p_validationlevel => 2, -- Validation Level
                                p_commitflag      => 0, -- Commit 1 =Yes , 0 ='No'
                                x_returnstatus    => l_returnstatus,
                                x_errormsg        => l_errormsg);

  dbms_output.put_line('1' || l_returnstatus);
  dbms_output.put_line('2' || l_errormsg);
  IF (l_returnstatus <> fnd_api.g_ret_sts_success) THEN
  
    select error
      into l_error
      from wip_interface_errors
     where interface_id = (select interface_id
                             from wip_job_schedule_interface
                            where group_id = l_group_id);
    dbms_output.put_line('x_return_message: ' || l_error);
  
  END IF;

END;
/
select * from wip_interface_errors where last_update_date >= sysdate - 1;--错误接口表

select *
  from wip.wip_job_schedule_interface
 where last_update_date >= sysdate - 1;--接口表

转自： http://blog.csdn.net/chosen1brain/article/details/24377717