[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_fromlpn_id
connect by parent_lpn_id = prior lpn_id
);
SELECT lpn_context
into l_xferlpn_context
from wms_license_plate_numbers where lpn_id = p_xfer_lpn_id
AND EXISTS(select 1 from mtl_onhand_quantities_detail moqd
where moqd.organization_id = p_xfer_org_id
AND moqd.lpn_id IN
(
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id =
(SELECT outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_xfer_lpn_id
)
));
SELECT subinventory_code,locator_id,organization_id
into l_xfer_sub_code,l_xfer_locator_id,l_xfer_org_id
from wms_license_plate_numbers where lpn_id = p_xfer_lpn_id;
select lpn_context into l_lpn_context
from wms_license_plate_numbers
where lpn_id = p_fromlpn_id;
SELECT 'Y'
INTO l_allow_status FROM DUAL
where l_return_status_id IN
(SELECT moqddst.status_id
FROM mtl_onhand_quantities_detail moqddst
WHERE moqddst.organization_id = p_organization_id
AND moqddst.lpn_id IN
(
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id =
(SELECT outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_fromlpn_id
)
));
SELECT moqdsrc.status_id
INTO temp_status_id
FROM mtl_onhand_quantities_detail moqdsrc
WHERE moqdsrc.organization_id = p_organization_id
AND moqdsrc.inventory_item_id = p_inventory_item_id
AND moqdsrc.subinventory_code = p_sub_code
AND moqdsrc.locator_id = p_locator_id
AND NVL(moqdsrc.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(moqdsrc.lpn_id, 0) = NVL(p_fromlpn_id, 0)
AND ROWNUM = 1;
SELECT 'Y'
INTO l_allow_status
FROM mtl_onhand_quantities_detail moqdsrc
WHERE moqdsrc.organization_id = p_organization_id
AND moqdsrc.inventory_item_id = p_inventory_item_id
AND moqdsrc.subinventory_code = p_sub_code
AND moqdsrc.locator_id = p_locator_id
AND NVL(moqdsrc.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(moqdsrc.lpn_id, 0) = NVL(p_fromlpn_id, 0)
AND ROWNUM = 1
AND moqdsrc.status_id IN
(
SELECT moqddst.status_id
FROM mtl_onhand_quantities_detail moqddst
WHERE moqddst.organization_id = l_xfer_org_id
AND moqddst.lpn_id IN
(
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id =
(SELECT outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_xfer_lpn_id
)
)
);
SELECT 'N'
INTO l_allow_status
FROM dual
WHERE EXISTS (
(SELECT DISTINCT moqdsrc.status_id
FROM mtl_onhand_quantities_detail moqdsrc
WHERE moqdsrc.organization_id = p_organization_id
AND moqdsrc.subinventory_code = p_sub_code
AND moqdsrc.locator_id = p_locator_id
-- AND NVL(moqdsrc.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND moqdsrc.lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
START WITH lpn_id = p_fromlpn_id CONNECT BY parent_lpn_id = prior lpn_id
AND plpn.organization_id = p_organization_id
)
)
MINUS
(
SELECT DISTINCT moqddst.status_id
FROM mtl_onhand_quantities_detail moqddst
WHERE moqddst.organization_id = l_xfer_org_id
AND moqddst.lpn_id IN
(
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id =
(SELECT outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_xfer_lpn_id
)
)
));
SELECT 'N'
INTO l_allow_transaction
FROM dual
WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail moqd,
mtl_status_transaction_control mtc
WHERE moqd.organization_id = p_organization_id
AND moqd.inventory_item_id = p_inventory_item_id
AND moqd.subinventory_code = p_sub_code
AND nvl(moqd.locator_id,-999) = nvl(p_locator_id,-999) --6974887
AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND Nvl(moqd.lpn_id,0) = Nvl(p_fromlpn_id,0)
AND moqd.status_id = mtc.status_id
AND mtc.transaction_type_id = p_trx_type_id
AND mtc.is_allowed = 2
--AND ROWNUM = 1
) ;
SELECT 'N'
INTO l_allow_transaction
FROM dual
WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail moqd,
mtl_status_transaction_control mtc
WHERE moqd.organization_id = l_xfer_org_id
AND moqd.inventory_item_id = p_inventory_item_id
AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND moqd.lpn_id = p_xfer_lpn_id
AND moqd.status_id = mtc.status_id
AND mtc.transaction_type_id = p_trx_type_id
AND mtc.is_allowed = 2
) ;
SELECT 'N'
INTO l_allow_transaction
FROM dual
WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail moqd,
mtl_status_transaction_control mtc
WHERE moqd.organization_id = l_xfer_org_id
AND moqd.inventory_item_id = p_inventory_item_id
AND moqd.subinventory_code = l_xfer_sub_code
AND nvl(moqd.locator_id,-999) = nvl(l_xfer_locator_id,-999) --6974887 --could be null for INV sub-inventories(doubt)
AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@') --only place where used xfer lot
AND moqd.status_id = mtc.status_id
AND mtc.transaction_type_id = p_trx_type_id
AND mtc.is_allowed = 2
-- AND ROWNUM = 1
) ;
SELECT 'N'
INTO l_allow_transaction
FROM dual
WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail moqd,
mtl_status_transaction_control mtc
WHERE moqd.organization_id = p_organization_id
AND moqd.subinventory_code = p_sub_code
AND moqd.locator_id = p_locator_id
-- AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND moqd.lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
START WITH lpn_id = p_fromlpn_id CONNECT BY parent_lpn_id = prior lpn_id
AND plpn.organization_id = p_organization_id
)
AND moqd.status_id = mtc.status_id
AND mtc.transaction_type_id = p_trx_type_id
AND mtc.is_allowed = 2
-- AND ROWNUM = 1
) ;
SELECT 'N'
INTO l_allow_transaction
FROM dual
WHERE EXISTS
(SELECT 1
FROM wms_lpn_contents wlc ,
mtl_serial_numbers msn,
mtl_status_transaction_control mtc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers START
WITH lpn_id = p_fromlpn_id CONNECT BY parent_lpn_id = PRIOR lpn_id
AND organization_id = p_organization_id
)
AND wlc.serial_summary_entry = 1
AND wlc.parent_lpn_id = msn.lpn_id
AND msn.status_id = mtc.status_id
AND mtc.transaction_type_id = p_trx_type_id
AND mtc.is_allowed = 2
);
SELECT 'N'
INTO l_allow_transaction
FROM dual
WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail moqd,
mtl_status_transaction_control mtc
WHERE moqd.organization_id = l_xfer_org_id
AND moqd.lpn_id = p_xfer_lpn_id
AND moqd.status_id = mtc.status_id
AND mtc.transaction_type_id = p_trx_type_id
AND mtc.is_allowed = 2
) ;
select is_allowed
into allowed
from mtl_status_transaction_control
where status_id = p_status_id
and transaction_type_id = p_transaction_type_id;
SELECT nvl(lot_status_enabled,'N'), Default_Lot_Status_ID,
nvl(serial_status_enabled,'N'), Default_serial_status_ID
INTO g_lot_status_enabled, g_default_lot_status_id,
g_serial_status_enabled, g_default_serial_status_id
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT NVL(msi.comms_nl_trackable_flag,'N')
INTO g_item_trackable
FROM mtl_system_items msi,
mtl_parameters mp
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.enabled_flag = 'Y'
AND nvl (msi.start_date_active, sysdate) <= sysdate
AND nvl (msi.end_date_active, sysdate+1) > sysdate
AND msi.organization_id = mp.master_organization_id
AND mp.organization_id = p_organization_id;
SELECT transaction_action_id,
transaction_source_type_id
INTO g_transaction_action_id,
g_transaction_source_type_id
FROM mtl_transaction_types mtt
WHERE mtt.transaction_type_id = p_trx_type_id;
SELECT nvl(freeze_flag, 'N')
INTO g_freeze_flag
FROM csi_install_parameters
WHERE rownum = 1;
select status_control_flag
into g_isa_trx_status_enabled
from mtl_transaction_types
where transaction_type_id = p_trx_type_id;
select status_id
into g_isa_sub_status_id
from mtl_secondary_inventories
where organization_id = p_organization_id
and secondary_inventory_name = p_sub_code;
select nvl(status_id, -1)
into g_isa_sub_status_id
from mtl_onhand_quantities_detail
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
and subinventory_code = p_sub_code
and lot_number is null
and locator_id is null
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
and rownum = 1 ;
select 1
into l_count
from mtl_onhand_quantities_detail moqd
where moqd.inventory_item_id = p_inventory_item_id
and moqd.organization_id = p_organization_id
and moqd.subinventory_code = p_sub_code
and INV_MATERIAL_STATUS_GRP.is_trx_allowed_wrap(
moqd.status_id
,p_trx_type_id) = 'Y'
and rownum = 1;
select count(distinct status_id)
into count_status_id
from mtl_onhand_quantities_detail
where organization_id = p_organization_id
and subinventory_code = p_sub_code
and lot_number is null
and locator_id is null
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999);
select status_id
into g_isa_sub_status_id
from mtl_onhand_quantities_detail
where organization_id = p_organization_id
and subinventory_code = p_sub_code
and lot_number is null
and locator_id is null
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
and rownum = 1 ;
select status_id
into g_isa_loc_status_id
from mtl_item_locations
where inventory_location_id = p_locator_id
and organization_id = p_organization_id;
select nvl(status_id, -1)
into g_isa_loc_status_id
from mtl_onhand_quantities_detail
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
and lot_number is null
and locator_id = p_locator_id
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
and rownum = 1 ;
select 1
into l_count
from mtl_onhand_quantities_detail moqd
where moqd.inventory_item_id = p_inventory_item_id
and moqd.organization_id = p_organization_id
and moqd.locator_id = p_locator_id
and INV_MATERIAL_STATUS_GRP.is_trx_allowed_wrap(
moqd.status_id
,p_trx_type_id) = 'Y'
and rownum = 1;
select count(distinct status_id)
into count_status_id
from mtl_onhand_quantities_detail
where organization_id = p_organization_id
and lot_number is null
and locator_id = p_locator_id
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999);
select status_id
into g_isa_loc_status_id
from mtl_onhand_quantities_detail
where organization_id = p_organization_id
and locator_id = p_locator_id
and lot_number is null
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
and rownum = 1 ;
select status_id
INTO g_isa_lot_number_status_id
from mtl_lot_numbers
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
and lot_number = p_lot_number;
select nvl(status_id, -1)
into g_isa_lot_number_status_id
from mtl_onhand_quantities_detail
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
and subinventory_code = p_sub_code
and nvl(locator_id, -9999) = nvl(l_locator_id, -9999)
and lot_number = p_lot_number
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
and rownum = 1 ;
select status_id
into l_status_id
from mtl_serial_numbers
where inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_id
and serial_number = p_serial_number;
PROCEDURE update_status
( p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_update_method IN NUMBER
, p_status_id IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER:=NULL
, p_sub_code IN VARCHAR2:=NULL
, p_locator_id IN NUMBER:=NULL
, p_lot_number IN VARCHAR2:=NULL
, p_serial_number IN VARCHAR2:=NULL
, p_to_serial_number IN VARCHAR2:=NULL
, p_object_type IN VARCHAR2
, p_update_reason_id IN NUMBER:=NULL
, p_lpn_id IN NUMBER:=NULL -- Onhand Material Status Support
, p_initial_status_flag IN VARCHAR2:='N' -- Onhand Material Status Support
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_status';
l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
l_status_rec.update_method := p_update_method;
l_status_rec.update_reason_id := p_update_reason_id;
INV_MATERIAL_STATUS_PUB.update_status(
p_api_version_number => p_api_version_number
, p_init_msg_lst => p_init_msg_lst
, x_return_status =>l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_object_type => p_object_type
, p_status_rec => l_status_rec );
END update_status;
SELECT restrict_locators_code
INTO l_restrict_loc_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
SELECT count(*)
INTO l_temp
FROM mtl_item_locations a, mtl_secondary_locators b
WHERE b.organization_id = p_org_id
AND b.inventory_item_id = p_inventory_item_id
AND b.subinventory_code = p_sub_code
AND a.inventory_location_id = b.secondary_locator
AND a.organization_id = b.organization_id
AND a.inventory_location_id = p_loc_id;
SELECT restrict_subinventories_code
INTO l_restrict_sub_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
SELECT count(*)
INTO l_temp
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = p_org_id
AND b.inventory_item_id = p_inventory_item_id
AND a.organization_id = b.organization_id
AND a.secondary_inventory_name = b.secondary_inventory
AND a.secondary_inventory_name = p_sub_code;
SELECT nvl(status_id, -1)
INTO l_default_status_id
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_sub_code
AND nvl( locator_id, -9999) =nvl( p_loc_id, -9999)
AND nvl(lot_number, '@@@@') = nvl(p_lot_number, '@@@@')
--AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999) /*LPN Status Project */
AND rownum = 1;
SELECT nvl(status_id, -1)
INTO l_default_status_id
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND nvl(lot_number, '@@@@') = nvl(p_lot_number, '@@@@')
AND lpn_id = p_lpn_id /*LPN Status Project */
AND rownum = 1;
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, p_src_status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.item_rec.default_lot_status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.item_rec.default_material_status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.loc_rec.status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.tosub_rec.default_loc_status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.tosub_rec.default_loc_status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.tosub_rec.status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.tosub_rec.status_id, p_lock_id);
insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
,p_lpn_id, inv_cache.org_rec.default_status_id, p_lock_id);
Procedure insert_status_history(p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_sub_code IN VARCHAR2,
p_loc_id IN NUMBER :=NULL,
p_lot_number IN VARCHAR2 :=NULL,
p_lpn_id IN NUMBER := NULL,
p_status_id IN NUMBER := NULL,
p_lock_id IN NUMBER := 0)
IS
c_api_name varchar2(30) := 'insert_status_history';
l_update_method NUMBER := 2;
inv_trx_util_pub.TRACE('Inside insert status history ', 'INV_MATERIAL_STATUS_GRP', 14);
update_status(l_api_version_number, l_init_msg_lst, l_return_status ,l_msg_count
,l_msg_data ,l_update_method ,p_status_id ,p_organization_id
,p_inventory_item_id ,p_sub_code ,p_loc_id ,p_lot_number
,NULL ,NULL ,'Q' ,NULL ,p_lpn_id, l_initial_status_flag);
inv_trx_util_pub.TRACE('Exception in insert_status_history, l_ret_status: '|| l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_count: '|| l_msg_count, 'INV_MATERIAL_STATUS_GRP', 14);
inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_data: '||l_msg_data, 'INV_MATERIAL_STATUS_GRP', 14);
END insert_status_history;
SELECT (decode(P.STOCK_LOCATOR_CONTROL_CODE,4,
decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE,S.LOCATOR_TYPE),
P.STOCK_LOCATOR_CONTROL_CODE))
INTO l_loc_control
FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = p_inventory_item_id
AND I.ORGANIZATION_ID = p_org_id
AND S.SECONDARY_INVENTORY_NAME = p_sub_code
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
AND P.ORGANIZATION_ID = I.ORGANIZATION_ID;
select transaction_action_id
into l_action_id
from mtl_transaction_types
where transaction_type_id = p_trx_type_id;
SELECT NVL( status_id ,-1 )
INTO l_onhand_status_id
FROM mtl_onhand_quantities_detail
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND subinventory_code = l_subinventory_code
AND NVL(locator_id, -9999) = NVL(l_locator_id,-9999)
AND NVL(lot_number,'@@@@') = NVL(l_lot_number,'@@@@')
AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
--AND ((l_lpn_id is NULL) OR (lpn_id = l_lpn_id ))
AND rownum = 1;
SELECT allow_different_status
INTO l_allow_different_status
FROM mtl_parameters
WHERE organization_id = l_transfer_org_id ;
SELECT status_id
INTO l_lot_status_id
FROM mtl_lot_numbers
WHERE organization_id = l_org_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
SELECT status_id, grade_code
INTO l_lot_status_id, l_grade_code
FROM mtl_lot_numbers
WHERE organization_id = l_transfer_org_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
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 wlc.serial_summary_entry DESC ;
SELECT mmtt.transaction_temp_id , mmtt.subinventory_code ,
mmtt.locator_id , mmtt.inventory_item_id ,
mmtt.lpn_id , mmtt.item_lot_control_code
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = p_lpn_id
AND NVL(mmtt.lpn_id,-99) <> p_lpn_id
AND NVL(mmtt.content_lpn_id,-99) <> p_lpn_id;
SELECT mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
where transaction_temp_id = l_transaction_temp_id;
SELECT msn.status_id
FROM mtl_serial_numbers msn
where msn.inventory_item_id = l_cur_inventory_item_id
AND msn.lpn_id = l_cur_lpn_id;
SELECT msn.status_id
FROM mtl_serial_numbers msn , mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_transaction_temp_id
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number;
SELECT wlpn.organization_id , wlpn.subinventory_code , wlpn.locator_id , wlpn.lpn_context into
l_lpn_org_id , l_lpn_sub ,l_lpn_loc ,l_lpn_context
FROM wms_license_plate_numbers wlpn
where lpn_id = p_lpn_id;
SELECT default_status_id
INTO l_def_org_status
FROM mtl_parameters
WHERE organization_id = l_lpn_org_id;
SELECT status_code
INTO l_return_status_code
FROM mtl_material_statuses
WHERE status_id =l_return_status_id ;
SELECT moqd.inventory_item_id inventory_item_id,moqd.lot_number lot_number,moqd.status_id status_id
FROM mtl_onhand_quantities_detail moqd, wms_lpn_contents wlc
WHERE moqd.organization_id = p_org_id
AND moqd.inventory_item_id = nvl(p_item_id,moqd.inventory_item_id)
AND moqd.subinventory_code = p_subinv_code
AND moqd.locator_id = p_locator_id
AND moqd.lpn_id = p_from_lpn_id
AND moqd.containerized_flag = 1
AND wlc.parent_lpn_id=moqd.lpn_id
AND wlc.inventory_item_id=nvl(p_item_id,wlc.inventory_item_id)
AND wlc.serial_summary_entry <> 1 -- To query only non serial controlled items.
GROUP BY moqd.inventory_item_id,moqd.lot_number,moqd.status_id;
SELECT Nvl(allow_different_status,1) INTO l_allow_diff_status
FROM mtl_parameters
WHERE organization_id =p_xfr_org_id ;
SELECT nvl(status_id,-9999) INTO l_source_status_id
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND (lot_number = p_lot_number
OR (lot_number is null and p_lot_number is NULL))
AND subinventory_code = p_subinv_code
AND locator_id = p_locator_id
AND lpn_id is NULL
AND containerized_flag = 2 -- (loose material)
AND ROWNUM=1;
SELECT 'Y' INTO l_comingle
FROM DUAL WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_xfr_org_id
AND inventory_item_id = p_item_id
AND (lot_number = p_lot_number
OR (lot_number is null and p_lot_number is null))
AND subinventory_code = p_tosubinv_code
AND locator_id = p_tolocator_id
AND Nvl(lpn_id,-9999)=Nvl(p_xfr_lpn_id,-9999)
AND l_source_status_id <> Nvl(status_id,-9999));
SELECT 'Y' INTO l_comingle
FROM DUAL WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_xfr_org_id
AND inventory_item_id =l_wlc_rec.inventory_item_id
AND Nvl(lot_number,'@@@@') = Nvl(l_wlc_rec.lot_number,'@@@@')
AND subinventory_code = p_tosubinv_code
AND locator_id = p_tolocator_id
AND Nvl(lpn_id,-9999) =Nvl(p_xfr_lpn_id,-9999)
AND nvl(status_id,-9999)<>nvl(l_wlc_rec.status_id,-9999));
SELECT *
FROM mtl_txn_request_lines mtrl
WHERE mtrl.lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_fromlpn_id
connect by parent_lpn_id = prior lpn_id
)
AND Nvl(l_cur_inventory_item_id,inventory_item_id)=inventory_item_id
AND organization_id=p_organization_id
AND line_status=7;
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_fromlpn_id
connect by parent_lpn_id = prior lpn_id
)
AND Nvl(l_cur_inventory_item_id,inventory_item_id)=inventory_item_id
ORDER BY wlc.serial_summary_entry DESC ;
SELECT msn.status_id
FROM mtl_serial_numbers msn
where msn.inventory_item_id = l_cur_inventory_item_id
AND msn.lpn_id = l_cur_lpn_id;
IS SELECT mmtt.transaction_temp_id , mmtt.subinventory_code,
mmtt.transaction_type_id,
mmtt.locator_id , mmtt.inventory_item_id ,
nvl(mmtt.lpn_id,mmtt.content_lpn_id) lpn_id , mmtt.item_lot_control_code
FROM mtl_material_transactions_temp mmtt
where mmtt.transfer_lpn_id = p_fromlpn_id
AND mmtt.transaction_source_type_id = 2
AND mmtt.transaction_type_id = 52
AND Nvl(l_cur_inventory_item_id,inventory_item_id)=inventory_item_id;
SELECT mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
where transaction_temp_id = l_transaction_temp_id;
SELECT msn.status_id
FROM mtl_serial_numbers msn , mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_transaction_temp_id
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number;
SELECT wlpn.organization_id , wlpn.subinventory_code , wlpn.locator_id , wlpn.lpn_context into
l_lpn_org_id , l_lpn_sub ,l_lpn_loc ,l_lpn_context
FROM wms_license_plate_numbers wlpn
where lpn_id = p_fromlpn_id;
SELECT 'N'
INTO l_allow_transaction
FROM dual
WHERE EXISTS
(SELECT 1
FROM mtl_onhand_quantities_detail moqd,
mtl_status_transaction_control mtc
WHERE moqd.organization_id = p_xfer_org_id
AND moqd.inventory_item_id = l_mmtt_cur.inventory_item_id
AND NVL(moqd.lot_number,'@@@') = NVL(l_mtlt_cur.lot_number,'@@@')
AND moqd.lpn_id = p_xfer_lpn_id
AND moqd.status_id = mtc.status_id
AND mtc.transaction_type_id = l_mmtt_cur.transaction_type_id
AND mtc.is_allowed = 2
) ;