APIs and Open Interface--非工单领、发料（含调拨）

• 表名

1. MTL_TRANSACTIONS_INTERFACE
2. MTL_TRANSACTION_LOTS_INTERFACE

• 序列
  1. MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
• APIs

1. INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS

• 案例
  1. 杂发/杂收（代码）

       Declare

      v_user_id  number := fnd_global.user_id;

      v_login_id   number := fnd_global.login_id;

  v_request_id number := fnd_profile.value('CONC_REQUEST_ID');

Begin      

       --取transaction_interface_id

       v_transaction_interface_id := null;

        begin

          select mtl_material_transactions_s.nextval

          into v_transaction_interface_id

          from dual;

        exception

          when others then v_transaction_interface_id := null;

        end;

--处理lot no

 if v_lot_no is not null then

                begin

                 insert into mtl_transaction_lots_interface

                 (transaction_interface_id

                 ,lot_number

                 ,transaction_quantity

                 ,primary_quantity

                 ,last_update_date

                 ,last_updated_by

                 ,creation_date

                 ,created_by

                 ,last_update_login

                  )

                 values

                (v_transaction_interface_id  -- transaction_interface_id

                ,v_lot_no  -- lot_number

                ,v_transaction_quantity  -- transaction_quantity

                ,v_transaction_quantity  -- primary_quantity

                ,sysdate  -- last_update_date

                ,v_user_id  -- last_updated_by

                ,sysdate   -- creation_date

                ,v_user_id   -- created_by

                ,v_login_id -- last_update_login

                );

                exception

                  when others then

                    v_err_msg :=v_err_msg||' 新增LOT No交易时错误：'||SQLERRM;  

                end;

              end if;

    begin

                       

                insert into mtl_transactions_interface(

                      process_flag

                      ,organization_id

                      ,inventory_item_id

                      ,transaction_quantity

                      ,transaction_type_id

                      ,reason_id

                      ,transaction_reference

                      ,transfer_subinventory

                      ,transfer_locator

                      ,transfer_organization

                      ,subinventory_code

                      ,locator_id

                      ,transaction_date

                      ,transaction_uom

                      ,transaction_interface_id

                      ,source_code

                      ,source_header_id

                      ,source_line_id

                      ,transaction_header_id

                      ,validation_required

                      ,lock_flag

                      ,transaction_mode

                      ,last_update_date

                      ,last_updated_by

                      ,creation_date

                      ,created_by

                      ,last_update_login

                      ,request_id

                      ,distribution_account_id)

                values(

                      1    -- process_flag：'1' for ready, '2' for not ready，3：fails 

                      ,v_organization_id          -- organization_id

                      ,v_inventory_item_id        -- inventory_item_id

                      ,v_transaction_quantity        -- transaction_quantity

                      ,v_transaction_type_id      -- transaction_type_id 杂收

                      ,v_reason_id                -- reason_id

                      ,v_request_number           -- transaction_reference

                      ,NULL -- transfer_subinventory  v_from_subinventory_code

                      ,NULL  -- transfer_locator_id     \*目的储位 *\

                      ,v_organization_id          -- transfer_organization

                      ,v_from_subinventory_code     -- subinventory_code

                      ,v_from_locator_id            -- locator_id from

                      ,sysdate                    -- transaction_date

                      ,v_primary_uom_code         -- transaction_uom

                      ,v_transaction_interface_id -- transaction_interface_id

                      ,'SIVP325'                   -- source_code

                      ,v_header_id                -- source_header_id

                      ,v_line_id                  -- source_line_id

                      ,v_transaction_interface_id -- transaction_header_id

                      ,1                          -- validation_required

                      ,2        -- lock_flag ('1' for locked, '2' or NULL for not locked 

                      ,3        -- transaction_mode 3-background 2-immediate

                      ,sysdate                    -- last_update_date

                      ,v_user_id                  -- last_updated_by

                      ,sysdate                    -- creation_date

                      ,v_user_id                  -- created_by

                      ,v_login_id                 -- last_update_login

                      ,v_request_id

                      ,v_account_id);

              exception

                  when others then

            

                 v_err_msg :=v_err_msg||' 新增 ERP 杂项出库时发生错误:'||SQLERRM;

                   

            

            v_error_str :=v_error_str||v_err_msg;

                    

              end;

              if sql%found then

                COMMIT;

                do_import(v_transaction_interface_id,v_err_code,v_err_msg);

                if v_err_code = 'E' then -- import 失败

                  rollback;                    

                   v_err_msg :=v_err_msg||'  自动杂项出库import失败:'

                 

                      

                       v_error_str :=v_error_str||v_err_msg;

                else

                    Null;

--根据实际情况，处理实际业务

                end if;

              end if; --sql%found

     -- ELSE

     End;

PROCEDURE DO_IMPORT(P_TRANSACTION_INTERFACE_ID IN NUMBER

                    ,X_RET_CODE              OUT NOCOPY VARCHAR2

                    ,X_ERR_MSG               OUT VARCHAR2) IS

x_return_status  varchar2(1);

x_msg_count      number;

x_msg_data       varchar2(4000);

x_trans_count    number;

l_retcode        number;

--l_return_message varchar2(32767);

cursor c_mti is

  select mti.error_code||'-'||mti.error_explanation error_message

  from mtl_transactions_interface mti

  WHERE mti.transaction_header_id = P_TRANSACTION_INTERFACE_ID;

BEGIN

  l_retcode := inv_txn_manager_pub.process_transactions

(p_api_version      => 1.0,

 p_commit           => 'F',

 p_init_msg_list    => 'T',

 p_validation_level => 100,

 x_return_status    => x_return_status,

 x_msg_count        => x_msg_count,

 x_msg_data         => x_msg_data,

x_trans_count      => x_trans_count,

p_header_id        => P_TRANSACTION_INTERFACE_ID);

  if l_retcode = -1 or x_return_status <> 'S' then

    for r_mti in c_mti loop

      if X_ERR_MSG is null then

        X_ERR_MSG := r_mti.error_message;

      else

        X_ERR_MSG := X_ERR_MSG||'-'||r_mti.error_message;

      end if;

    end loop;

    rollback;

    x_ret_code := 'E';

  else

    x_ret_code := 'S';

  end if;

END DO_IMPORT;

1. 1. 详细说明
2. 步骤：先把资料导入到Interface，然后调用INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS
3. v_transaction_interface_id：根据序列进行获取mtl_material_transactions_s.nextval
4. v_transaction_quantity ：杂发数量为负数，杂收为正数。transaction_quantity/ primary_quantity是否一致，根据实际情况定义
5. v_transaction_type_id要存在MTL_TRANSACTION_TYPES中
6. v_reason_id 要存在MTL_TRANSACTION_REASONS中
7. v_request_number：记录单据号码
8.  source_code：记录源代码，
9. v_header_id：记录源表头代码
10. v_line_id：记录源表身代码
11. v_account_id：会计科目
    1. 出库部分，建议在Interface前先检查库存数量是否满足。（该程序资料已检查，所以在此部分未做处理）
    2. 调拨transfer_subinventory、transfer_locator_id需要进行赋值，该值为调拨至的值，v_transaction_quantity为正数。

• 手工转Interface功能与DO_IMPORT相同

1. You can use the following steps:
       a. (N) Inventory > Transactions > Transaction Open Interface
       b. Query the stuck records
       c. No record should get queried up.

• 常见错误
  1. Lot管理的料号，无MTL_TRANSACTION_LOTS_INTERFACE资料
  2. 出库类型库存不足
  3. 出库类型数量为正数
  4. 账期已经关账或没有开账
• 表说明