API to get Lot On-hand, Available to Reserve and Transactable Quantites

Below is the sample test procedure and API to get the Lot On-hand, Available to Reserve and Transactable quantities. Same we can query from Inventory responsibility under
Navigation: On-hand, Availability->On-hand Quantity.

Test Procedure:
---------------------

DECLARE
v_lpn_onhand NUMBER;
v_reservable_quantity NUMBER;
v_transactable_quantity NUMBER;
v_sqoh NUMBER;
v_satt NUMBER;
v_satr NUMBER;

BEGIN
XXHG_ONT_AUTO_RESERVATION_PKG.get_lot_item_qty(
p_lpn_id => NULL,
p_organization_id => 104,
p_source_type_id => 8, --Inventory
p_inventory_item_id => 3001,
p_revision => NULL,
p_locator_id => NULL,
p_subinventory_code => NULL,
p_lot_number => 'XX.Lot.20001439.1',
p_is_revision_control => 'FALSE',
p_is_serial_control => 'FALSE',
p_is_lot_control => 'TRUE',
x_lpn_onhand => v_lpn_onhand,
x_reservable_quantity => v_reservable_quantity,
x_transactable_quantity => v_transactable_quantity,
p_grade_code => NULL,
x_sqoh => v_sqoh ,
x_satt => v_satt ,
x_satr => v_satr
-- NSRIVAST, INVCONV, END
);

dbms_output.put_line('v_lpn_onhand :'||v_lpn_onhand);
dbms_output.put_line('v_reservable_quantity :'||v_reservable_quantity);
dbms_output.put_line('v_transactable_quantity :'||v_transactable_quantity);

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error: '||SQLERRM);
END;

Output:
-------
v_lpn_onhand :80
v_reservable_quantity :60
v_transactable_quantity :60

Custom API:
----------------

CREATE OR REPLACE PACKAGE BODY APPS.XXHG_ONT_AUTO_RESERVATION_PKG
AS
PROCEDURE get_lot_item_qty(
p_lpn_id IN NUMBER,
p_organization_id IN NUMBER,
p_source_type_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_revision IN VARCHAR2,
p_locator_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_is_revision_control IN VARCHAR2,
p_is_serial_control IN VARCHAR2,
p_is_lot_control IN VARCHAR2,
x_lpn_onhand OUT NUMBER,
x_reservable_quantity OUT NUMBER,
x_transactable_quantity OUT NUMBER,
-- NSRIVAST, INVCONV , Start
p_grade_code IN VARCHAR2,
x_sqoh OUT NUMBER ,
x_satt OUT NUMBER ,
x_satr OUT NUMBER
-- NSRIVAST, INVCONV, END
) IS
l_msg_count VARCHAR2(100);
l_msg_data VARCHAR2(1000);
l_rqoh NUMBER;
l_qr NUMBER;
l_qs NUMBER;
l_atr NUMBER;
l_att NUMBER;
l_qoh NUMBER;
l_lpn_context NUMBER ;
l_return_status VARCHAR2(1);
x_return VARCHAR2(1);
l_is_revision_control BOOLEAN := FALSE;
l_is_serial_control BOOLEAN := FALSE;
l_is_lot_control BOOLEAN := FALSE ;
l_lpn_context NUMBER;
l_tree_mode NUMBER;

-- NSRIVAST, INVCONV, Start
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
-- NSRIVAST, INVCONV, END

QUANTITY_EXCEPTION EXCEPTION;
BEGIN

-- Clearing the quantity cache
inv_quantity_tree_pub.clear_quantity_cache;

IF Upper(p_is_revision_control) = 'TRUE' THEN
l_is_revision_control := TRUE;
ELSE
l_is_revision_control := FALSE;
END IF;

IF Upper(p_is_serial_control) = 'TRUE' THEN
l_is_serial_control := TRUE;
ELSE
l_is_serial_control := FALSE;
END IF ;

-- BUG NO 2768731
IF p_lot_number IS NULL THEN
l_is_lot_control := FALSE;
ELSE
l_is_lot_control := TRUE;
END IF;

IF (p_inventory_item_id IS NULL) THEN
RAISE QUANTITY_EXCEPTION;
END IF ;

-- Reserve mode
l_tree_mode := 1; --To get Available To Reserve Quantity

--Call public API
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 => l_tree_mode
, p_is_revision_control => l_is_revision_control
, p_is_lot_control => l_is_lot_control
, p_is_serial_control => l_is_serial_control
, p_demand_source_type_id => p_source_type_id
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_lot_expiration_date => NULL
, 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 => p_lpn_id
-- NSRIVAST, INVCONV, Start
, p_grade_code => p_grade_code
, x_sqoh => x_sqoh
, x_satt => x_satt
, x_satr => x_satr
-- , p_transaction_type => NULL
, x_srqoh => x_srqoh
, x_sqr => x_sqr
, x_sqs => x_sqs
, p_demand_source_header_id => -1
, p_demand_source_line_id => -1
, p_demand_source_name => -1
, p_transfer_subinventory_code => NULL
, p_cost_group_id => NULL
, p_transfer_locator_id => NULL
-- NSRIVAST, INVCONV, End
);

IF (l_return_status = 'S') THEN
x_lpn_onhand := l_qoh;
x_reservable_quantity := l_atr;
ELSE
x_return :='F';
RAISE QUANTITY_EXCEPTION;
END IF ;

-- Transact mode
l_tree_mode := 2; --To get Transactable Quantity

--Call public API
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 => l_tree_mode
, p_is_revision_control => l_is_revision_control
, p_is_lot_control => l_is_lot_control
, p_is_serial_control => l_is_serial_control
, p_demand_source_type_id => p_source_type_id
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_lot_expiration_date => NULL
, 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 => p_lpn_id
-- NSRIVAST, INVCONV, Start
, p_grade_code => p_grade_code
, x_sqoh => x_sqoh
, x_satt => x_satt
, x_satr => x_satr
-- , p_transaction_type => NULL
, x_srqoh => x_srqoh
, x_sqr => x_sqr
, x_sqs => x_sqs
, p_demand_source_header_id => -1
, p_demand_source_line_id => -1
, p_demand_source_name => -1
, p_transfer_subinventory_code => NULL
, p_cost_group_id => NULL
, p_transfer_locator_id => NULL
-- NSRIVAST, INVCONV, End
);

IF (l_return_status = 'S') THEN
x_lpn_onhand := l_qoh;
x_transactable_quantity := l_att;
ELSE
x_return :='F';
RAISE QUANTITY_EXCEPTION;
END IF ;

EXCEPTION
WHEN QUANTITY_EXCEPTION THEN
dbms_output.put_line('Quanity Exception Raised'||SQLERRM);

WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);

END get_lot_item_qty;

END XXHG_ONT_AUTO_RESERVATION_PKG;