[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(SUM(PRIMARY_TRANSACTION_QUANTITY)
,0)
INTO V_PRIMARY_SYS_QTY
FROM
MTL_ONHAND_QUANTITIES_DETAIL
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_ORG_ID
AND SUBINVENTORY_CODE = P_SUBINVENTORY
AND NVL(LOT_NUMBER
,'XX') = NVL(P_LOTNUMBER
,'XX')
AND NVL(REVISION
,'XXX') = NVL(P_REVISION
,'XXX')
AND NVL(LOCATOR_ID
,-2) = NVL(P_LOCATOR
,-2)
AND NVL(LPN_ID
,-3) = NVL(P_PARENT_LPN_ID
,-3);
SELECT
NVL(SUM(DECODE(MSN.CURRENT_STATUS
,3
,1
,0))
,0)
INTO V_PRIMARY_SYS_QTY
FROM
MTL_SERIAL_NUMBERS MSN
WHERE MSN.SERIAL_NUMBER = NVL(P_SERIAL_NUMBER
,MSN.SERIAL_NUMBER)
AND MSN.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND MSN.CURRENT_ORGANIZATION_ID = P_ORG_ID
AND MSN.CURRENT_SUBINVENTORY_CODE = P_SUBINVENTORY
AND NVL(MSN.LOT_NUMBER
,'XX') = NVL(P_LOTNUMBER
,'XX')
AND NVL(MSN.REVISION
,'XXX') = NVL(P_REVISION
,'XXX')
AND NVL(MSN.CURRENT_LOCATOR_ID
,-2) = NVL(P_LOCATOR
,-2)
AND NVL(MSN.LPN_ID
,-3) = NVL(P_PARENT_LPN_ID
,-3);
SELECT
DECODE(MSN.CURRENT_STATUS
,3
,'Y'
,'N')
INTO V_ISSERIALPRESENT
FROM
MTL_SERIAL_NUMBERS MSN
WHERE MSN.SERIAL_NUMBER = P_SERIAL_NUMBER
AND MSN.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND MSN.CURRENT_ORGANIZATION_ID = P_ORG_ID
AND ( MSN.CURRENT_STATUS = 6
OR ( MSN.CURRENT_SUBINVENTORY_CODE = P_SUBINVENTORY
AND NVL(MSN.LOT_NUMBER
,'XX') = NVL(P_LOTNUMBER
,'XX')
AND NVL(MSN.REVISION
,'XXX') = NVL(P_REVISION
,'XXX')
AND NVL(MSN.CURRENT_LOCATOR_ID
,-2) = NVL(P_LOCATOR
,-2) ) );
SELECT
COST_GROUP
INTO M_COST_GROUP
FROM
CST_COST_GROUPS
WHERE COST_GROUP_ID = P_COST_GROUP_ID;
SELECT
LICENSE_PLATE_NUMBER
INTO M_OUTERMOST_LPN
FROM
WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = P_OUTERMOST_LPN_ID;
SELECT
LICENSE_PLATE_NUMBER
INTO M_PARENT_LPN
FROM
WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = P_PARENT_LPN_ID;