ebs R12 杂收杂发 代码

DECLARE

  l_iface_rec        mtl_transactions_interface%ROWTYPE;
  l_iface_lot_rec    mtl_transaction_lots_interface%ROWTYPE;
 
  l_origanization_id NUMBER := 83; --Current Inv Organization
  l_user_id        NUMBER := 0; --User ID, Sysadmin here
  l_timeout        NUMBER;
  v_transaction_qty NUMBER;
  l_error_code     VARCHAR2(200);
  l_error_explanation VARCHAR2(2000);
  l_outcome        BOOLEAN;
BEGIN

  l_iface_rec.last_update_date  := SYSDATE;
  l_iface_rec.last_updated_by   := l_user_id;
  l_iface_rec.creation_date     := SYSDATE;
  l_iface_rec.created_by        := l_user_id;
  l_iface_rec.last_update_login := -1;

  SELECT mtl_material_transactions_s.NEXTVAL INTO l_iface_rec.transaction_interface_id FROM dual;
  l_iface_rec.transaction_header_id := l_iface_rec.transaction_interface_id;
  l_iface_rec.transaction_mode      := 3;  --2：并发 3:后台
  l_iface_rec.process_flag          := 1;  --1:是 2:否 3：错误
 
  l_iface_rec.transaction_type_id   := 200; --mtl_transaction_types
  l_iface_rec.transaction_source_type_id := 13;
  l_iface_rec.transaction_action_id := 1;
 

  -- 判断事务处理来源还是账户
  IF (l_iface_rec.TRANSACTION_SOURCE_TYPE_ID IN(13, 100) --来源类型:13--inventory, 100--Inventory transaction
      AND l_iface_rec.transaction_action_id != 2) THEN
      l_iface_rec.distribution_account_id := 8048;  --账户
  ELSIF l_iface_rec.TRANSACTION_SOURCE_TYPE_ID NOT IN (13, 100) THEN
      l_iface_rec.transaction_source_id := 4;   --账户别名
  END IF;
 
  v_transaction_qty := 4;
  IF l_iface_rec.transaction_action_id = 27 THEN
       l_iface_rec.transaction_quantity := v_transaction_qty;
  ELSIF l_iface_rec.transaction_action_id = 1 THEN
       l_iface_rec.transaction_quantity := -1*v_transaction_qty;
  ELSE
       NULL;    
  END IF;    
 
  l_iface_rec.organization_id       := l_origanization_id;
  l_iface_rec.inventory_item_id     := 58160;
  l_iface_rec.subinventory_code     := '1234567890';
  l_iface_rec.locator_id            := 5421;  --只有在货位控制下才有用
  l_iface_rec.transaction_uom       := 'PCS';
  l_iface_rec.transaction_date      := SYSDATE;
  l_iface_rec.source_code           := 'Test Only';
  l_iface_rec.source_header_id      := 987654321;
  l_iface_rec.source_line_id        := 987654321;

  INSERT INTO mtl_transactions_interface VALUES l_iface_rec;
 
  --批次控制
  l_iface_lot_rec.last_update_date  := SYSDATE;
  l_iface_lot_rec.last_updated_by   := l_user_id;
  l_iface_lot_rec.creation_date     := SYSDATE;
  l_iface_lot_rec.created_by        := l_user_id;
  l_iface_rec.last_update_login := -1; 
  l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;
  l_iface_lot_rec.lot_number               := 'SR-0003';
  l_iface_lot_rec.transaction_quantity     := -3;
  l_iface_lot_rec.source_code              := l_iface_rec.source_code;
  l_iface_lot_rec.source_line_id           := l_iface_rec.source_line_id;

  INSERT INTO mtl_transaction_lots_interface VALUES l_iface_lot_rec;
  l_iface_lot_rec.last_update_date  := SYSDATE;
  l_iface_lot_rec.last_updated_by   := l_user_id;
  l_iface_lot_rec.creation_date     := SYSDATE;
  l_iface_lot_rec.created_by        := l_user_id;  
  l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;
  l_iface_lot_rec.lot_number               := 'SR-0003';
  l_iface_lot_rec.transaction_quantity     := -1;
  l_iface_lot_rec.source_code              := l_iface_rec.source_code;
  l_iface_lot_rec.source_line_id           := l_iface_rec.source_line_id;

  INSERT INTO mtl_transaction_lots_interface VALUES l_iface_lot_rec;
 
  ---------------
  l_timeout := 100;
  -- Auto commit whenever success or failure!!
  l_outcome := mtl_online_transaction_pub.process_online(p_transaction_header_id => l_iface_rec.transaction_header_id,
                                                         p_timeout               => l_timeout,
                                                         p_error_code            => l_error_code,
                                                         p_error_explanation     => l_error_explanation);
  IF (l_outcome = FALSE) THEN
    dbms_output.put_line('Failed to process the transaction');
    dbms_output.put_line('Error code: ' || l_error_code);
    dbms_output.put_line('Error message: ' || l_error_explanation);
    DELETE inv.mtl_transactions_interface
     WHERE transaction_header_id = l_iface_rec.transaction_header_id;
    COMMIT;
  ELSE
    dbms_output.put_line('Transaction with header id ' ||
                         to_char(l_iface_rec.transaction_header_id) ||
                         ' has been processed successfully');
    COMMIT; --No need here
  END IF;

END;