/* Oracle Inventory On Hand . Available to Reserve Quantity , Available to Transact */
/* Formatted on 2/22/2012 11:26:05 AM (QP5 v5.114.809.3010) */
CREATE OR REPLACE 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;
-----------------------------------
DECLARE
v_lpn_onhand NUMBER;
v_reservable_quantity NUMBER;
v_transactable_quantity NUMBER;
v_sqoh NUMBER;
v_satt NUMBER;
v_satr NUMBER;
BEGIN
get_lot_item_qty(
p_lpn_id => NULL,
p_organization_id => 122, --HG5
p_source_type_id => 8, --Inventory
p_inventory_item_id => 85703, --HG_Sample_Item
p_revision => NULL,
p_locator_id => NULL,
p_subinventory_code => 'BULK',
p_lot_number => NULL ,
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;
No comments:
Post a Comment