interface批量导入库存现有量

对于一些期初库存需要导入系统，或者盘点的时候需要对盘盈的库存进行导入等情况会批量导入库存，

与之前批量导出库存一样，用interface，不同点在于导出数量是负的，导入数量是正的

以下code只考虑到物料批次管控，有序列管控，版本管控，储位管控的以下代码需要修改方可使用：

create or replace procedure jw_import_onhand_proc as

  cursor c_item is
  select organization_id
         ,new_item
         ,sub_code
         ,lot_number
         ,quantity
         ,account1
         ,transaction_date
         ,description1
         ,flag
         ,rowid
    from JW_IMPORT_ITEM_20150611
   where flag is null;

   R_J EXCEPTION;
   v_num number;
   v_num1 number;
   v_num2 number;
   v_lot_flag varchar2(1);
   v_item_id number;

   v_mtl_transactions_inter    mtl_transactions_interface%rowtype;
   v_mtl_transaction_lot_inter mtl_transaction_lots_interface%rowtype;

begin
  null;
  --防呆
  for v_item in c_item loop
  begin
    select count(*)
      into v_num
      from mtl_parameters mp
     where mp.organization_id =  v_item.organization_id;
    if v_num = 0 then
      raise R_J;
    end if;

    select count(*)
      into v_num1
      from mtl_system_items_b msib
      where msib.organization_id = v_item.organization_id
        and msib.segment1 = TRIM(v_item.new_item)
        and msib.inventory_item_status_code <> 'Inactive';
    if v_num1 = 0 then
      raise R_J;
    end if;

    select count(*)
      into v_num2
      from mtl_secondary_inventories msi
      where msi.organization_id = v_item.organization_id
        and msi.secondary_inventory_name = TRIM(v_item.sub_code)
        and msi.disable_date is null;
    if v_num2 = 0 then
      raise R_J;
    end if;

    select msib.lot_control_code,msib.inventory_item_id
      into v_lot_flag,v_item_id
      from mtl_system_items_b msib
     where msib.organization_id = v_item.organization_id
       and msib.segment1 = TRIM(v_item.new_item)
       and msib.inventory_item_status_code <> 'Inactive';

    if (v_lot_flag = 2) and v_item.lot_number is null then
      raise R_J;
    end if;

    select fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
                                     key_flex_code          => 'GL#',
                                     structure_number       => '50348',
                                     validation_date        => to_char(SYSDATE,
                                                                       'YYYY-MM-DD'),
                                     concatenated_segments  => TRIM(v_item.account1))
                                     into v_mtl_transactions_inter.DISTRIBUTION_ACCOUNT_ID
                                     from dual;

    SELECT mtl_material_transactions_s.nextval
      INTO v_mtl_transactions_inter.transaction_interface_id
      FROM dual;

    v_mtl_transactions_inter.transaction_mode := '3'; --3
    v_mtl_transactions_inter.process_flag := '1'; --1
    v_mtl_transactions_inter.transaction_type_id := 42; --1   40
  --  v_mtl_transactions_inter.DISTRIBUTION_ACCOUNT_ID := , --1034       --zw:6381
    v_mtl_transactions_inter.organization_id := v_item.organization_id; -- 86 102           --85
    v_mtl_transactions_inter.inventory_item_id := v_item_id; --3501
    v_mtl_transactions_inter.subinventory_code := TRIM(v_item.sub_code); --QXCPLPC    22102
    --v_mtl_transactions_inter.LOC_SEGMENT1, --BA1R12
    v_mtl_transactions_inter.transaction_quantity := ABS(v_item.quantity); --入是正，出是负
    v_mtl_transactions_inter.transaction_uom := 'PCS'; --PCS
    v_mtl_transactions_inter.transaction_date := v_item.transaction_date; --DATE
    v_mtl_transactions_inter.source_code := '11'; --MANUAL_INSERT
    v_mtl_transactions_inter.source_header_id := '11'; --11
    v_mtl_transactions_inter.source_line_id := '11'; --11
    v_mtl_transactions_inter.transaction_reference := v_item.description1;
    v_mtl_transactions_inter.LAST_UPDATE_LOGIN := null;
    v_mtl_transactions_inter.LAST_UPDATE_DATE := sysdate;
    v_mtl_transactions_inter.LAST_UPDATED_BY := 1111;
    v_mtl_transactions_inter.CREATION_DATE := sysdate;
    v_mtl_transactions_inter.CREATED_BY := 1111;
    v_mtl_transactions_inter.LAST_UPDATE_LOGIN := null;

    insert into mtl_transactions_interface values v_mtl_transactions_inter;

    if v_lot_flag = 2 then
       v_mtl_transaction_lot_inter.transaction_interface_id := v_mtl_transactions_inter.transaction_interface_id;
       v_mtl_transaction_lot_inter.source_code := '11';
       v_mtl_transaction_lot_inter.source_line_id := 11;
       v_mtl_transaction_lot_inter.last_update_date := sysdate;
       v_mtl_transaction_lot_inter.last_updated_by := 1111;
       v_mtl_transaction_lot_inter.creation_date := sysdate;
       v_mtl_transaction_lot_inter.created_by := 1111;
       v_mtl_transaction_lot_inter.last_update_login := null;
       v_mtl_transaction_lot_inter.lot_number := TRIM(v_item.lot_number);
       v_mtl_transaction_lot_inter.transaction_quantity := ABS(v_item.quantity);
       v_mtl_transaction_lot_inter.primary_quantity := ABS(v_item.quantity);
       v_mtl_transaction_lot_inter.process_flag := '1';

       insert into mtl_transaction_lots_interface values v_mtl_transaction_lot_inter;
    end if;
    update JW_IMPORT_ITEM_20150611 j
       set j.flag = 'Y'
     where j.rowid = v_item.rowid;
    commit;
    NULL;
  exception
     when R_J then
       ROLLBACK;
       update JW_IMPORT_ITEM_20150611 j
          set j.flag = 'E'
        where j.rowid = v_item.rowid;
        COMMIT;
        null;
  end ;
  end loop;
end;