In Oracle Application, the standard function calls the following API to calculate Onhand Quantity: inv_quantity_tree_pub.query_quantities
(P_api_version_number = & gt; 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_recognition => recognition,
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
). Basically, you can guess the approximate meaning of the parameters. The following describes several special parameters: [1]
P_tree_modeThe standard form of R12 is explained in The background as follows: * 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_sourceUsed to determine which subs are supported ded in calculation of onhand qty has four optional values: 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; the values are set in Lookup code. lookup type: MTL_ONHAND_SOURCE [3] x_att return values. Available to transact [4] x_recognition return value. Available to reserve
MTL_ONHAND_QUANTITIES
The quantity of TRANSACTION_QUANTITY in is determined by organization, material, sub-database, space, batch... Method
Accumulate, and then subtract the material's reservation in MTL_RESERVATIONS. In many cases, I did not take the test.
Consider the number of materials in the inventory transaction processing interface table (MTL_MATERIAL_TRANSACTIONS_TEMP ).
The output quantity may be inaccurate. The following is a calculation of the number of items in the inventory transaction processing interface table.
. For more information, see.
/*--------------------------------------------------------------------------------
$ Header PTAC, SKip Siman He, 2008.03.25
* Procedure GET_ITEM_ATT_QTY
* Purpose:
Calculate the consumable amount of materials
----------------------------------------------------------------------------*/
FUNCTION get_item_att_qty (p_item_id NUMBER,
P_organization_id NUMBER,
P_subinventory_code VARCHAR2) RETURN NUMBER IS
Rochelle hand_qty NUMBER;
Rochelle resv_qty NUMBER;
Rochelle qoh NUMBER;
Rochelle rqoh NUMBER;
L_qr NUMBER;
L_qs NUMBER;
Rochelle att number;
Rochelle NUMBER;
Rochelle tree_mode NUMBER;
Rochelle msg_count VARCHAR2 (100 );
Rochelle msg_data VARCHAR2 (1000 );
Rochelle return_status VARCHAR2 (1 );
X_return VARCHAR2 (1 );
BEGIN
-- Transact mode
Rochelle 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,
Oracle ERP r12 Technical Development .doc
X_qs => l_qs,
X_att => l_att, -- available
X_recognition => l_recognition); -- retainable quantity
RETURN l_att;
END;
In the INV module, the maximum number of three columns for a user to view materials isCurrent usage,Reserved quantity,Processing capacity. The following procedure is provided by hand's technical consultant when the company went online.
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
Rochelle return_status VARCHAR2 (1 );
Rochelle msg_count NUMBER;
Rochelle msg_data VARCHAR2 (2000 );
Rochelle is_revision_control BOOLEAN: = TRUE;
Rochelle is_lot_control BOOLEAN: = TRUE;
Rochelle is_serial_control BOOLEAN: = FALSE;
P_revision VARCHAR2 (100); l_qoh NUMBER;
Rochelle rqoh NUMBER;
L_qr NUMBER;
L_qs NUMBER;
Rochelle att number;
Rochelle NUMBER;
P_lpn_id NUMBER; begin if p_lot_number IS NULL THEN
Rochelle is_lot_control: = FALSE;
End if; IF p_locator_id IS NULL THEN
Rochelle 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_recognition => l_recognition
, P_lpn_id => NULL); IF (l_return_status ='s ') THEN
X_onhand_qty: = l_qoh;
X_reservable_qty: = maid;
ELSE
Rochelle 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_recognition => l_recognition
, P_lpn_id => NULL); IF (l_return_status ='s ') THEN
X_onhand_qty: = l_qoh;
X_transactable_qty: = l_att;
ELSE
Rochelle 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
Rochelle hand_qty number;
Rochelle reservable_qty number;
Rochelle transactable_qty number;
Begin
-- P_sub can be empty. If it is empty, the number of retainable Databases
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;
