EBS INV 计算现有量(标准API)、保留量、可用量（一）

最新推荐文章于 2022-11-07 12:45:24 发布

rfb0204421

最新推荐文章于 2022-11-07 12:45:24 发布

阅读量4.3k

点赞数

分类专栏： OPM/WIP/INV 文章标签： api transactions tree null system numbers

OPM/WIP/INV 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了一种查询特定组织中物料的现有量和保留量的方法。通过遍历批次并使用Oracle EBS API来获取现有量，同时利用SQL查询计算保留量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

      现有量= 可用量+保留量；  
     
      1.现有量<BR> 
      --批次 
     
      CURSOR 
      c_lot_number(l_organization_id NUMBER) 
      IS
     
      SELECT 
      mln.lot_number, 
     
      mln.expiration_date 
     
      FROM 
      mtl_lot_numbers mln 
     
      WHERE 
      mln.inventory_item_id = p_inventory_item_id 
     
      -- AND mln.status_id = 1 --有效合格批次 
     
      AND 
      mln.organization_id = l_organization_id; 
      --需要添加合格字段  
     
      FOR 
      rec_loc 
      IN 
      c_lot_number(p_organization_id) 
     
      LOOP 
     
      --遍历批次。取合格批次。 
     
      inv_quantity_tree_pub.query_quantities(p_api_version_number  => 1.0, 
     
      p_init_msg_lst        => 
      NULL
      , 
     
      x_return_status       => l_return_status, 
     
      x_msg_count           => l_msg_count, 
     
      x_msg_data            => l_msg_data, 
     
      p_organization_id     => p_organization_id, 
      --仓库ID 
     
      p_inventory_item_id   => p_inventory_item_id, 
      --物料ID 
     
      p_tree_mode           => 3, 
     
      p_is_revision_control => 
      FALSE
      , 
     
      p_is_lot_control      => 
      TRUE
      , 
     
      p_is_serial_control   => 
      FALSE
      , 
     
      p_revision            => 
      NULL
      , 
     
      p_lot_number          => rec_loc.lot_number, 
     
      p_lot_expiration_date => rec_loc.expiration_date, 
     
      p_subinventory_code   => p_subinventory_code, 
      --子库code 
     
      p_locator_id          => 
      NULL
      , 
      --rec_loc.locator_id, 
     
      p_cost_group_id       => 
      NULL
      , 
     
      p_onhand_source       => inv_quantity_tree_pvt.g_all_subs, 
     
      x_qoh                 => l_qoh, 
      --现有量 
     
      x_rqoh                => l_atp_qty, 
     
      x_qr                  => l_qr, 
     
      x_qs                  => l_qs, 
     
      x_att                 => l_att, 
     
      x_atr                 => l_atr); 
     
      IF l_return_status = fnd_api.g_ret_sts_success 
      THEN
     
      l_onhand := l_onhand + l_qoh; 
     
      ELSE
     
      log(
      'Error:获取先有量出错'
      ); 
     
      END 
      IF; 
     
      END 
      LOOP; 
     
      2. 保留量

         SELECT 
         nvl(
         SUM
         (qty), 0) 
        
         INTO 
         l_reserv 
        
         FROM 
         (
         SELECT 
         mr.reservation_quantity qty, 
        
         msi.segment1 
        
         FROM 
         mtl_reservations   mr, 
        
         mtl_system_items_b msi 
        
         WHERE 
         1 = 1 
        
         AND 
         mr.demand_source_type_id <> 2 
         --排除SO  
        
         AND 
         mr.inventory_item_id = msi.inventory_item_id 
        
         AND 
         mr.organization_id = msi.organization_id 
        
         AND 
         mr.subinventory_code = p_subinventory_code 
        
         AND 
         msi.inventory_item_id = p_inventory_item_id 
        
         AND 
         msi.organization_id = p_organization_id 
        
         UNION
        
         SELECT 
         mmt.transaction_quantity qty, 
        
         msi.segment1 
        
         FROM 
         mtl_material_transactions_temp mmt, 
        
         mtl_system_items_b             msi 
        
         WHERE 
         1 = 1 
        
         AND 
         mmt.transaction_source_type_id <> 2 
         --排除SO  
        
         AND 
         mmt.subinventory_code = p_subinventory_code 
        
         AND 
         mmt.inventory_item_id = msi.inventory_item_id 
        
         AND 
         mmt.organization_id = msi.organization_id 
        
         AND 
         msi.inventory_item_id = p_inventory_item_id 
        
         AND 
         msi.organization_id = p_organization_id);