在Oracle Application中,standard 功能是调用下面API来计算Onhand Quantity的:
-
inv_quantity_tree_pub.query_quantities
-
( p_api_version_number => 1.0,
-
p_init_msg_lst => 'F',
-
x_return_status => x_return_status,
-
x_msg_count => x_msg_count,
-
x_msg_data => x_msg_data,
-
p_organization_id => p_organization_id,
-
p_inventory_item_id => p_inventory_item_id,
-
p_tree_mode => tree_mode,
-
p_is_revision_control => is_revision_control,
-
p_is_lot_control => is_lot_control,
-
p_is_serial_control => FALSE,
-
p_revision => p_revision,
-
p_lot_number => p_lot_number,
-
p_lot_expiration_date => sysdate,
-
p_subinventory_code => p_subinventory_code,
-
p_locator_id => p_locator_id,
-
p_cost_group_id => cg_id,
-
p_onhand_source => 3,
-
x_qoh => qoh,
-
x_rqoh => rqoh,
-
x_qr => qr,
-
x_qs => qs,
-
x_att => att,
-
x_atr => atr,
-
p_grade_code => p_grade_code,
-
x_sqoh => sqoh,
-
x_satt => satt,
-
x_satr => satr ,
-
x_srqoh => x_srqoh,
-
x_sqr => x_sqr,
-
x_sqs => x_sqs,
-
p_demand_source_type_id => -1 ,
-
p_demand_source_header_id => -1 ,
-
p_demand_source_line_id => -1 ,
-
p_demand_source_name => NULL ,
-
p_transfer_subinventory_code => NULL ,
-
p_transfer_locator_id => NULL
-
);
参数基本上一看能猜出大概意思,对几个特殊的参数解释说明一下:
【1】p_tree_mode
R12的standard form后台是这样解释的:
* The parameter p_tree_mode determines which values to fetch:
* 2 => Fetch both packed and loose quantities
* 3 => Fetch only loose quantities
* To determine whether ONLY loose quantities are to be displayed:
* a) Subinventory, cost group for the current record (in QUANTITY_FOLDER block) are not NULL
* b) QUANTITY_FOLDER.PACKED is NOT NULL (for WMS org) and is equal to 0
* c) The current record does not have VMI or consigned stock
* d) For a lot controlled item, the QUANTITY_FOLDER.LOT_NUMBER is not null.
* When the above conditions are TRUE, then call the quantity tree with tree_mode = 3 and default
* the on-hand quantity to :QUANTITY_FOLDER.ON_HAND.
* If the current node has VMI or consigned stock, am showing the entire quantity(both packed and loose)
【2】p_onhand_source
Used to determine which subs are included in calculation of onhand qty
有4个可选值:
inv_quantity_tree_pvt.g_atpable_only CONSTANT NUMBER := 1;
inv_quantity_tree_pvt.g_nettable_only CONSTANT NUMBER := 2;
inv_quantity_tree_pvt.g_all_subs CONSTANT NUMBER := 3;
inv_quantity_tree_pvt.g_atpable_nettable_only CONSTANT NUMBER := 4;
这几个值是在Lookup code中设置的,
lookup type: MTL_ONHAND_SOURCE
【3】x_att
返回值。available to transact
【4】x_atr
返回值。available to reserve
在计算物料的可保留量的时候,我们通常的做法是MTL_ONHAND_QUANTITIES
中的TRANSACTION_QUANTITY的数量按照组织+物料+子库+货位+批次…的方式
进行累计,然后再减去物料在MTL_RESERVATIONS 中对应的保留。很多的时候没有去考
虑此时库存事务处理接口表(MTL_MATERIAL_TRANSACTIONS_TEMP)中物料数量,这样计算
出来的数量可能会不准确。以下是考虑了库存事务处理接口表的物料数量的计算方
式。大家不妨可以参考一下。
/*--------------------------------------------------------------------------------
$ Header PTAC , SKip Siman He , 2008.03.25
* Procedure GET_ITEM_ATT_QTY
* Purpose :
计算物料的可用量
---------------------------------------------------------------------------- */
-
FUNCTION get_item_att_qty(p_item_id NUMBER,
-
p_organization_id NUMBER,
-
p_subinventory_code VARCHAR2) RETURN NUMBER IS
-
l_onhand_qty NUMBER;
-
l_resv_qty NUMBER;
-
l_qoh NUMBER;
-
l_rqoh NUMBER;
-
l_qr NUMBER;
-
l_qs NUMBER;
-
l_att NUMBER;
-
l_atr NUMBER;
-
l_tree_mode NUMBER;
-
l_msg_count VARCHAR2(100);
-
l_msg_data VARCHAR2(1000);
-
l_return_status VARCHAR2(1);
-
x_return VARCHAR2(1);
-
BEGIN
-
-- Transact mode
-
l_tree_mode := 2;
-
inv_quantity_tree_pub.clear_quantity_cache;
-
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_item_id,
-
p_tree_mode => l_tree_mode,
-
p_is_revision_control => FALSE,
-
p_is_lot_control => FALSE,
-
p_is_serial_control => FALSE,
-
p_revision => NULL,
-
p_lot_number => NULL,
-
p_lot_expiration_date => NULL,
-
p_subinventory_code => p_subinventory_code,
-
p_locator_id => NULL,
-
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
-
x_qoh => l_qoh,
-
x_rqoh => l_rqoh,
-
x_qr => l_qr,
-
x_qs => l_qs,
-
x_att => l_att, --可用量
-
x_atr => l_atr); --可保留量
-
RETURN l_att;
-
END;
在INV模块,用户查看物料数量最多的三个栏位是现用量,可保留量,可处理量。下面的procedure是汉得技术顾问在公司上线时候提供的。
-
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;
-
-
转自http://www.cnblogs.com/benio/archive/2010/11/23/1880684.html
阅读(1434) | 评论(0) | 转发(0) |