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);