[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_SECONDARY_INVENTORIES msi
where mms.status_id = msi.status_id
and msi.SECONDARY_INVENTORY_NAME = p_sub_inv
and msi.organization_id = p_org_id;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_ITEM_LOCATIONS_KFV mil
where mms.status_id = mil.status_id
and mil.INVENTORY_LOCATION_ID = p_locator_id
and mil.organization_id = p_org_id;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_SERIAL_NUMBERS msn
where mms.status_id = msn.status_id
and msn.SERIAL_NUMBER = p_serial
and msn.current_organization_id = p_org_id
and msn.inventory_item_id = p_item_id;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_LOT_NUMBERS mln
where mms.status_id = mln.status_id
and mln.LOT_NUMBER = p_lot
and mln.organization_id = p_org_id
and mln.inventory_item_id = p_item_id;
select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
SELECT status_id
FROM MTL_LOT_NUMBERS
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND lot_number BETWEEN p_from_lot AND p_to_lot;
SELECT status_code
INTO x_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = lot_status_id;
SELECT status_id
FROM MTL_SERIAL_NUMBERS
WHERE current_organization_id = p_org_id
AND inventory_item_id = p_item_id
--AND current_status in (1, 3, 5)
AND current_status in (1, 3, 5, 7)
AND serial_number BETWEEN p_from_serial AND p_to_serial;
SELECT status_code
INTO x_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = serial_status_id;
PROCEDURE update_status(
p_update_method IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_sub_code IN VARCHAR2
, p_sub_status_id IN NUMBER
, p_sub_reason_id IN NUMBER
, p_locator_id IN NUMBER
, p_loc_status_id IN NUMBER
, p_loc_reason_id IN NUMBER
, p_from_lot_number IN VARCHAR2
, p_to_lot_number IN VARCHAR2
, p_lot_status_id IN NUMBER
, p_lot_reason_id IN NUMBER
, p_from_SN IN VARCHAR2
, p_to_SN IN VARCHAR2
, p_serial_status_id IN NUMBER
, p_serial_reason_id IN NUMBER
, x_Status OUT nocopy VARCHAR2
, x_Message OUT nocopy VARCHAR2
, p_update_from_mobile IN VARCHAR2 DEFAULT 'Y'
-- NSRIVAST, INVCONV , Start
, p_grade_code IN VARCHAR2 DEFAULT NULL
, p_primary_onhand IN NUMBER DEFAULT NULL
, p_secondary_onhand IN NUMBER DEFAULT NULL
, p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
-- NSRIVAST, INVCONV , End
)
IS
l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
SELECT lot_number
FROM MTL_LOT_NUMBERS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number between p_from_lot_number and p_to_lot_number;
select moqd.rowid FROM mtl_onhand_quantities_detail moqd
where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
and organization_id = p_organization_id
and subinventory_code = Nvl(p_sub_code, subinventory_code)
and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999) -- Bug 7012984, Modified the lpn_id condition
and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
and exists
(select 1 from mtl_system_items_b msi
where moqd.inventory_item_id = msi.inventory_item_id
AND moqd.organization_id = msi.organization_id
AND msi.serial_number_control_code in (1,6)
)
FOR UPDATE NOWAIT;
select msn.rowid FROM mtl_serial_numbers msn
where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
and current_organization_id = p_organization_id
and current_subinventory_code = Nvl(p_sub_code, current_subinventory_code)
and nvl(current_locator_id, -9999) = nvl(p_locator_id, Nvl(current_locator_id, -9999))
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999) -- Bug 7012984, Modified the lpn_id condition
and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
and current_status = 3
and exists
(select 1 from mtl_system_items_b msi
where msn.inventory_item_id = msi.inventory_item_id
AND msn.current_organization_id = msi.organization_id
AND nvl(msi.serial_status_enabled, 'N') = 'Y'
)
FOR UPDATE NOWAIT;
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_lpn_id
connect by parent_lpn_id = prior lpn_id
)
order by serial_summary_entry
FOR UPDATE NOWAIT;
IF p_update_from_mobile = 'Y' THEN
SAVEPOINT INV_UPDATE_STATUS;
mdebug('in update status');
l_status_rec.update_method := INV_MATERIAL_STATUS_PUB.g_update_method_manual;
update mtl_secondary_inventories
set status_id = p_sub_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where organization_id = p_organization_id
and secondary_inventory_name = p_sub_code;
l_status_rec.update_reason_id := p_sub_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
update mtl_item_locations
set status_id = p_loc_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where organization_id = p_organization_id
and inventory_location_id = p_locator_id;
l_status_rec.update_reason_id := p_loc_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
update mtl_lot_numbers
set status_id = p_lot_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and lot_number BETWEEN p_from_lot_number and p_to_lot_number ;
l_status_rec.update_reason_id := p_lot_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
update mtl_serial_numbers
set status_id = p_serial_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where current_organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and serial_number BETWEEN p_from_SN AND p_to_SN;
l_status_rec.update_reason_id := p_serial_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
SELECT serial_control into l_serial_status_control
from mtl_material_statuses
WHERE status_id = p_onhand_status_id;
SELECT outermost_lpn_id into l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id ;
mdebug('in mass update of lpn '||p_onhand_status_id);
FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
UPDATE mtl_onhand_quantities_detail
SET status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE inventory_item_id = l_wlc_cur.inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = Nvl(p_sub_code,'@@@@')
AND locator_id = Nvl(p_locator_id ,-9999)
AND Nvl(lot_number,'@@@@') = Nvl(l_wlc_cur.lot_number,'@@@@')
AND lpn_id = l_wlc_cur.parent_lpn_id;
UPDATE mtl_serial_numbers
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE inventory_item_id = l_wlc_cur.inventory_item_id
AND current_organization_id = p_organization_id
AND current_subinventory_code = Nvl(p_sub_code,'@@@@')
AND current_locator_id = Nvl(p_locator_id ,-9999)
AND lpn_id = l_wlc_cur.parent_lpn_id;
SELECT outermost_lpn_id into l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id ;
update mtl_serial_numbers
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where lpn_id = p_lpn_id
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number BETWEEN p_from_SN AND p_to_SN
AND Nvl(lot_number,'@@@@') = Nvl(p_from_lot_number,'@@@@');
SELECT outermost_lpn_id into l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
update mtl_onhand_quantities_detail
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list(j);
-- Modified the where clause in the above update as it is not required for the bug # 6633612
COMMIT;
mdebug(' update executed in sql: Onhand');
SELECT serial_control into l_serial_status_control
from mtl_material_statuses
WHERE status_id = p_onhand_status_id;
update mtl_serial_numbers
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list1(j);
mdebug(' update executed in sql: Onhand Serial');
l_status_rec.update_reason_id := p_onhand_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
FOR cc IN cur_lot_number LOOP -- To update all the lots in a given sub, locator combination..
l_status_rec.lot_number := cc.lot_number;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
end if; --added for full lpn update case
p_update_method
, p_organization_id
, p_inventory_item_id
, p_sub_code
, p_sub_status_id
, p_sub_reason_id
, p_locator_id
, p_loc_status_id
, p_loc_reason_id
, p_from_lot_number
, p_to_lot_number
, p_lot_status_id
, p_lot_reason_id
, p_from_SN
, p_to_SN
, p_serial_status_id
, p_serial_reason_id
, p_onhand_status_id -- Added for # 6633612
, p_onhand_reason_id -- Added for # 6633612
, p_lpn_id -- Added for # 6633612
, x_Status
, x_Message
);
IF p_update_from_mobile = 'Y' THEN
ROLLBACK TO INV_UPDATE_STATUS;
--ROLLBACK TO INV_UPDATE_STATUS;
IF p_update_from_mobile = 'Y' THEN
ROLLBACK TO INV_UPDATE_STATUS;
FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
END update_status;
p_update_method IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_sub_code IN VARCHAR2
, p_sub_status_id IN NUMBER
, p_sub_reason_id IN NUMBER
, p_locator_id IN NUMBER
, p_loc_status_id IN NUMBER
, p_loc_reason_id IN NUMBER
, p_from_lot_number IN VARCHAR2
, p_to_lot_number IN VARCHAR2
, p_lot_status_id IN NUMBER
, p_lot_reason_id IN NUMBER
, p_from_SN IN VARCHAR2
, p_to_SN IN VARCHAR2
, p_serial_status_id IN NUMBER
, p_serial_reason_id IN NUMBER
, p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, x_Status OUT nocopy VARCHAR2
, x_Message OUT nocopy VARCHAR2)
IS
l_workflow_name varchar2(250);
l_update_method varchar2(80);
l_calling_program_name := 'Update Status';
SELECT meaning
INTO l_update_method
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MTL_STATUS_UPDATE_METHOD'
AND LOOKUP_CODE = p_update_method;
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_sub_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_sub_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_sub_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_loc_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_loc_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_loc_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_lot_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_lot_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_lot_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_serial_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_serial_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_serial_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_onhand_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_onhand_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_onhand_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT tracking_quantity_ind INTO p_tracking_qty_ind
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_outermost_lpn_id
connect by parent_lpn_id = prior lpn_id
)
and wlc.parent_lpn_id not in
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_lpn_id
connect by parent_lpn_id = prior lpn_id
);
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_outermost_lpn_id
connect by parent_lpn_id = prior lpn_id
);
select status_id
FROM mtl_serial_numbers
where inventory_item_id = l_inventory_item_id
AND lpn_id = l_lpn_id;
select DISTINCT status_id INTO l_default_status_id
from mtl_onhand_quantities_detail
WHERE lpn_id = l_wlc_cur.parent_lpn_id
AND inventory_item_id = l_wlc_cur.inventory_item_id
AND NVL(lot_number,'@@@@') = NVL(l_wlc_cur.lot_number,'@@@@')
AND organization_id = p_organization_id;
select DISTINCT status_id into l_default_status_id
from mtl_onhand_quantities_detail
where lpn_id = l_wlc_item_cur.parent_lpn_id
AND inventory_item_id = l_wlc_item_cur.inventory_item_id
AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
AND organization_id = p_organization_id;
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_outermost_lpn_id
connect by parent_lpn_id = prior lpn_id
);
select status_id
FROM mtl_serial_numbers msn
where inventory_item_id = l_inventory_item_id
AND lpn_id = l_lpn_id
AND nvl(msn.lot_number , '@@@@') = NVL(p_lot_number,'@@@@')
AND msn.serial_number NOT IN (select serial_number
from mtl_serial_numbers
where serial_number between p_fm_sn AND p_to_sn);
select DISTINCT status_id into l_default_status_id
from mtl_onhand_quantities_detail
where lpn_id = l_wlc_item_cur.parent_lpn_id
AND inventory_item_id = l_wlc_item_cur.inventory_item_id
AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
AND organization_id = p_organization_id;