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.
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
XXAA_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.XXAA_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 XXAA_ONT_AUTO_RESERVATION_PKG;
No comments:
Post a Comment