Oracle INV现用量，可保留量，可处理量

create or replace procedure get_inv_quantity(p_organization_id in number,
p_inventory_item_id in number,
p_lot_number in varchar2,
p_subinventory_code in varchar2,
p_locator_id in number,
x_onhand_qty out number,
x_reservable_qty out number,
x_transactable_qty out number) is

l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);
l_is_revision_control boolean := true;
l_is_lot_control boolean := true;
l_is_serial_control boolean := false;
p_revision varchar2(100);

l_qoh number;
l_rqoh number;
l_qr number;
l_qs number;
l_att number;
l_atr number;
p_lpn_id number;

begin

if p_lot_number is null then
l_is_lot_control := false;
end if;

if p_locator_id is null then
l_is_lot_control := false;
end if;

inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_tree_mode => 1,
p_is_revision_control => false, -- no revision control
p_is_lot_control => l_is_lot_control,
p_is_serial_control => l_is_serial_control,
p_demand_source_type_id => 2,
p_revision => null,
p_lot_number => p_lot_number,
p_lot_expiration_date => sysdate,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locator_id,
p_onhand_source => 3,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr,
p_lpn_id => null);

if (l_return_status = 'S') then
x_onhand_qty := l_qoh;
x_reservable_qty := l_atr;
else
l_return_status := 'F';
return;
end if;

inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_tree_mode => 2,
p_is_revision_control => false, -- no revision control
p_is_lot_control => l_is_lot_control,
p_is_serial_control => l_is_serial_control,
p_demand_source_type_id => 2,
p_revision => null,
p_lot_number => p_lot_number,
p_lot_expiration_date => sysdate,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locator_id,
p_onhand_source => 3,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr,
p_lpn_id => null);

if (l_return_status = 'S') then
x_onhand_qty := l_qoh;
x_transactable_qty := l_att;
else
l_return_status := 'F';
return;
end if;

end get_inv_quantity;

create or replace function get_reservable_qty(p_organization_id number,
p_inventory_item_id number,
p_sub varchar2)
return number is
l_onhand_qty number;
l_reservable_qty number;
l_transactable_qty number;
begin
--p_sub可以为空，为空时，所有库别的可保留数量
get_inv_quantity(p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_lot_number => null,
p_subinventory_code => p_sub,
p_locator_id => null,
x_onhand_qty => l_onhand_qty,
x_reservable_qty => l_reservable_qty,
x_transactable_qty => l_transactable_qty);
return l_reservable_qty;
end get_reservable_qty;