interface批量导出库存现有量

对于一些需要批量处理库存时，我们使用的方法有：

1、DATALOAD在界面杂项事务处理中处理

2、通过interface杂出库存

3、通过API

下面是用第二种方法，比方说我们要吧ZX仓库中的物料全部杂出，此程序只考虑到批次物料管控，如果有序列号管控，储位管控则以下代码不适用

逻辑：先把要杂出的信息存入临时表，再通过procedure处理插入mti表，运行请求处理物料事物处理接口程序

insert into JW_EXPORT_ITEM_TEMP
select moq.ORGANIZATION_ID,
        moq.INVENTORY_ITEM_ID item_id,
        msib.segment1 new_item,
        moq.SUBINVENTORY_CODE sub_code,
        moq.LOT_NUMBER,
        sum(moq.TRANSACTION_QUANTITY) quantity,
        decode(moq.ORGANIZATION_ID,
               85,
               '10.0000.9008.0000.000.0',
               86,
               '20.0000.9008.0000.000.0',
               '10.0000.9008.0000.000.0') account1,
        to_date('20150101','yyyymmdd') transaction_date,
        '盘点杂出' description1,
        'N' flag
   from mtl_onhand_quantities moq, mtl_system_items_b msib
  where moq.INVENTORY_ITEM_ID = msib.inventory_item_id
    and moq.ORGANIZATION_ID = msib.organization_id
    and moq.ORGANIZATION_ID = 86
    and moq.SUBINVENTORY_CODE = '22112'
  group by moq.ORGANIZATION_ID,
           moq.INVENTORY_ITEM_ID,
           moq.SUBINVENTORY_CODE,
           msib.segment1,
           moq.LOT_NUMBER;

create or replace procedure jw_export_onhand_proc as

  cursor c_item is
 select organization_id
         ,item_id
         ,new_item
         ,sub_code
         ,lot_number
         ,quantity
         ,account1
         ,transaction_date
         ,description1
         ,flag
         ,rowid
    from JW_EXPORT_ITEM_TEMP
   where flag = 'N';

   R_J EXCEPTION;
   v_lot_flag varchar2(1);


   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 msib.lot_control_code
      into v_lot_flag
      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';


    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 := 367; --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.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 := 0 - 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 := 1553;
    v_mtl_transactions_inter.CREATION_DATE := sysdate;
    v_mtl_transactions_inter.CREATED_BY := 1553;
    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 := 1553;
       v_mtl_transaction_lot_inter.creation_date := sysdate;
       v_mtl_transaction_lot_inter.created_by := 1553;
       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 := 0 - ABS(v_item.quantity);
       v_mtl_transaction_lot_inter.primary_quantity := 0 - 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_EXPORT_ITEM_TEMP j
       set j.flag = 'Y'
     where j.rowid = v_item.rowid;
    commit;
    NULL;
  exception
     when R_J then
       ROLLBACK;
       DBMS_OUTPUT.put_line(v_item.new_item || 'error');
       update JW_EXPORT_ITEM_TEMP j
       set j.flag = 'E'
     where j.rowid = v_item.rowid;
    commit;
        null;
  end ;
  end loop;
end;