EBS INV 计算现有量(标准API)、保留量、可用量 ---根据批次、库存组织、子库

现有量= 可用量+保留量；
1.现有量
 --批次
    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);