[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_locator(p_sub_code IN VARCHAR2, p_org_id IN NUMBER, p_locator_id IN NUMBER) IS
l_return_status VARCHAR2(10);
l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
DEBUG('In the update locator');
SELECT nvl(subinventory_type,1)
INTO l_sub_type
FROM mtl_secondary_inventories
WHERE organization_id = p_org_id
AND secondary_inventory_name = p_sub_code;
l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
l_status_rec.last_update_date := SYSDATE;
l_status_rec.last_update_login := fnd_global.user_id;
DEBUG('befire inserting status history');
inv_material_status_pkg.insert_status_history(l_status_rec);
DEBUG('Status history inserted');
SELECT default_locator_order_value
INTO l_picking_order
FROM mtl_parameters
WHERE organization_id = p_org_id;
UPDATE mtl_item_locations
SET subinventory_code = p_sub_code
, status_id = l_locator_status
, inventory_location_type = l_loc_type
, picking_order = l_picking_order
WHERE organization_id = p_org_id
AND inventory_location_id = p_locator_id;
END update_locator;
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, msub.lpn_controlled_flag
, nvl(msub.subinventory_type, 1)
, msub.reservable_type
, msub.enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND Nvl(subinventory_type,1) = Decode(p_putaway_code,
2, --Don't show any storage sub
-1,--if system suggested a RCV sub
1)
AND Nvl(lpn_controlled_flag,-1) = Decode(p_putaway_code,
3,--For SO xdock, sub must be
1,--LPN controlled
Decode(p_putaway_code,
4,--For WIP xdock, sub must
2,--NOT be LPN controlled
Nvl(lpn_controlled_flag,-1)))
AND reservable_type = Decode(p_putaway_code,
3,--For SO xdock, sub must be
1,--reservable
Decode(p_putaway_code,
4,--For WIP xdock, sub must
2,--not be reservable
reservable_type))
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_sub)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
UNION ALL
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type, 1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = Decode(p_putaway_code,
2,--Only show rcv sub if the
2,--system has suggested a rcv sub
Decode(p_putaway_code,
NULL,
2,
-1))
AND msub.secondary_inventory_name LIKE (p_sub)
AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
ORDER BY 1;
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type, 1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_sub)
AND Nvl(msub.subinventory_type,1) = Decode(p_putaway_code,
2, --Don't show any storage sub
-1,--if system suggested a RCV sub
1)
AND Nvl(msub.lpn_controlled_flag,-1) = Decode(p_putaway_code,
3,--For SO xdock, sub must be
1,--LPN controlled
Decode(p_putaway_code,
4,--For WIP xdock, sub must
2,--NOT be LPN controlled
Nvl(msub.lpn_controlled_flag,-1)))
AND msub.reservable_type = Decode(p_putaway_code,
3,--For SO xdock, sub must be
1,--reservable
Decode(p_putaway_code,
4,--For WIP xdock, sub must
2,--not be reservable
msub.reservable_type))
AND EXISTS( SELECT NULL
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
AND mis.inventory_item_id = p_item_id
AND mis.secondary_inventory = msub.secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
UNION ALL
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type, 1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND Nvl(msub.subinventory_type,1) = Decode(p_putaway_code,
2,--Only show rcv sub if the
2,--system has suggested a rcv sub
Decode(p_putaway_code,
NULL,
2,
-1))
AND msub.secondary_inventory_name LIKE (p_sub)
AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
ORDER BY 1;
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type, 1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_sub)
AND Nvl(msub.lpn_controlled_flag,-1) = Decode(p_lpn_context,
2,
Decode(p_putaway_code,
3,
1,
Decode(p_putaway_code,
4,
2,
Nvl(msub.lpn_controlled_flag,-1))
),
Nvl(msub.lpn_controlled_flag,-1)
)
AND msub.reservable_type = Decode(p_lpn_context,
2,
Decode(p_putaway_code,
3,
1,
Decode(p_putaway_code,
4,
2,
msub.reservable_type)
),
msub.reservable_type
)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
ORDER BY UPPER(msub.secondary_inventory_name);
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type, 1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_sub)
AND Nvl(msub.lpn_controlled_flag,-1) = Decode(p_lpn_context,
2,
Decode(p_putaway_code,
3,-- SO XDOCK
1,--Must be LPN controlled
Decode(p_putaway_code,
4,--WIP XDOCK
2,--Must be non LPN controlled
Nvl(msub.lpn_controlled_flag,-1))
),
Nvl(msub.lpn_controlled_flag,-1)
)
AND msub.reservable_type = Decode(p_lpn_context,
2,
Decode(p_putaway_code,
3,--SO XDOCK
1,--Must be reservable
Decode(p_putaway_code,
4,--WIP XDOCK
2,--Must be non reservable
msub.reservable_type)
),
msub.reservable_type
)
AND EXISTS( SELECT NULL
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
AND mis.inventory_item_id = p_item_id
AND mis.secondary_inventory = msub.secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
ORDER BY UPPER(msub.secondary_inventory_name);
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type, 1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE organization_id = p_organization_id
AND subinventory_type = 2
AND msub.secondary_inventory_name LIKE (p_sub)
AND location_id = p_location_id
AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
ORDER BY UPPER(msub.secondary_inventory_name);
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name IN (
SELECT from_subinventory_code
FROM mtl_txn_request_lines
WHERE header_id = p_moheader_id
)
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinv_code);
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name IN (SELECT to_subinventory_code
FROM mtl_txn_request_lines
WHERE header_id = p_moheader_id)
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinv_code);
SELECT a.inventory_location_id
--, a.concatenated_segments----Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, a.description
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY a.concatenated_segments;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY concatenated_segments;
SELECT a.inventory_location_id
--, a.concatenated_segments concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, a.description
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
AND a.project_id IS NULL
AND a.task_id IS NULL
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND NVL(a.physical_location_id, a.inventory_location_id) = a.inventory_location_id
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND concatenated_segments LIKE (p_concatenated_segments)
AND project_id IS NULL
AND task_id IS NULL
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND a.concatenated_segments LIKE (p_concatenated_segments )
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND concatenated_segments LIKE (p_concatenated_segments)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND concatenated_segments LIKE (p_concatenated_segments)
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
AND a.concatenated_segments LIKE (p_concatenated_segments )
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND concatenated_segments LIKE (p_concatenated_segments )
AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND concatenated_segments LIKE (p_concatenated_segments )
AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND a.alias = p_alias
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND alias = p_alias
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND alias = p_alias
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
AND a.alias = p_alias
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND alias = p_alias
AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
AND alias = p_alias
AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
ORDER BY 2;
l_status_rec inv_material_status_pub.mtl_status_update_rec_type; -- bug# 1695432
SELECT inventory_location_id
, description
INTO x_location_id
, x_description
FROM wms_item_locations_kfv
WHERE organization_id = p_org_id
AND subinventory_code = p_sub_code
AND concatenated_segments = p_concat_segs
AND ROWNUM < 2;
SELECT 'failed'
INTO l_validity_check
FROM DUAL
WHERE EXISTS( SELECT subinventory_code
FROM wms_item_locations_kfv
WHERE concatenated_segments = p_concat_segs
AND p_sub_code <> subinventory_code
AND organization_id = p_org_id);
SELECT NVL(default_loc_status_id, 1)
INTO l_sub_default_status
FROM mtl_secondary_inventories
WHERE organization_id = p_org_id
AND secondary_inventory_name = p_sub_code;
UPDATE mtl_item_locations
SET subinventory_code = p_sub_code
, status_id = l_sub_default_status
, inventory_location_type = l_loc_type
WHERE organization_id = p_org_id
AND inventory_location_id = x_location_id;
SELECT 'failed'
INTO l_validity_check
FROM DUAL
WHERE EXISTS( SELECT subinventory_code
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_concat_segs
AND p_sub_code <> subinventory_code
AND organization_id = p_org_id);
l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
l_status_rec.last_update_date := SYSDATE;
l_status_rec.last_update_login := fnd_global.user_id;
inv_material_status_pkg.insert_status_history(l_status_rec);
SELECT 1
INTO l_temp
FROM mtl_item_locations
WHERE organization_id = p_org_id
AND inventory_location_id = p_inventory_location_id;
SELECT restrict_locators_code
INTO l_restrict_locators_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_org;
SELECT mil.inventory_location_id
--, mil.concatenated_segments conseg--Bug4398337:Commented this line and added below line
, mil.locator_segments conseg
, mil.description
FROM wms_item_locations_kfv mil
WHERE mil.organization_id = p_organization_id
AND mil.inventory_location_id IN (SELECT from_locator_id
FROM mtl_txn_request_lines
WHERE header_id = p_moheader_id)
AND mil.concatenated_segments LIKE (p_concatenated_segments)
AND NVL(mil.project_id, -9999) = NVL(p_project_id, -9999)
AND NVL(mil.task_id, -9999) = NVL(p_task_id, -9999);
SELECT mil.inventory_location_id
--, mil.concatenated_segments conseg--Bug4398337:Commented this line and added below line
, mil.locator_segments conseg
, mil.description
FROM wms_item_locations_kfv mil
WHERE mil.organization_id = p_organization_id
AND mil.inventory_location_id IN (SELECT from_locator_id
FROM mtl_txn_request_lines
WHERE header_id = p_moheader_id)
AND mil.alias = p_alias
AND NVL(mil.project_id, -9999) = NVL(p_project_id, -9999)
AND NVL(mil.task_id, -9999) = NVL(p_task_id, -9999);
SELECT mil.inventory_location_id
--, mil.concatenated_segments consegs--Bug4398337:Commented this line and added below line
, mil.locator_segments consegs
, mil.description
FROM wms_item_locations_kfv mil
WHERE mil.organization_id = p_organization_id
AND inventory_location_id IN (SELECT to_locator_id
FROM mtl_txn_request_lines
WHERE header_id = p_moheader_id)
AND mil.concatenated_segments LIKE (p_concatenated_segments)
AND NVL(mil.project_id, -9999) = NVL(p_project_id, -9999)
AND NVL(mil.task_id, -9999) = NVL(p_task_id, -9999);
SELECT mil.inventory_location_id
--, mil.concatenated_segments--Bug4398337:Commented this line and added below line
, mil.locator_segments concatenated_segments
, mil.description
, mil.status_id
, mmsv.status_code
FROM wms_item_locations_kfv mil, mtl_material_statuses_vl mmsv
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory_code
AND mil.concatenated_segments LIKE (p_concatenated_segments)
AND mmsv.status_id = mil.status_id
AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
ORDER BY mil.concatenated_segments; -- PJM-WMS Integration
SELECT mil.inventory_location_id
--, mil.concatenated_segments--Bug4398337:Commented this line and added below line
, mil.locator_segments concatenated_segments
, mil.description
, mil.status_id
, mmsv.status_code
FROM wms_item_locations_kfv mil, mtl_material_statuses_vl mmsv
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory_code
AND mil.alias = p_alias
AND mmsv.status_id = mil.status_id
AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
ORDER BY mil.concatenated_segments; -- PJM-WMS Integration
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = p_organization_id
AND a.organization_id = b.organization_id
AND b.inventory_item_id = p_inventory_item_id
AND a.secondary_inventory_name = b.secondary_inventory
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = p_organization_id
AND a.organization_id = b.organization_id
AND a.secondary_inventory_name = b.secondary_inventory
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.inventory_item_id = p_inventory_item_id
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = p_organization_id
AND a.organization_id = b.organization_id
AND a.secondary_inventory_name = b.secondary_inventory
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.inventory_item_id = p_inventory_item_id
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = p_organization_id
AND a.organization_id = b.organization_id
AND a.secondary_inventory_name = b.secondary_inventory
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.inventory_item_id = p_inventory_item_id
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT restrict_subinventories_code
INTO l_restrict_subinventories_code
FROM mtl_system_items
WHERE organization_id = l_org
AND inventory_item_id = p_inventory_item_id;
SELECT inventory_asset_flag
INTO l_inventory_asset_flag
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_org;
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = l_org
AND a.organization_id = b.organization_id
AND a.secondary_inventory_name = b.secondary_inventory
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.inventory_item_id = p_inventory_item_id
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = l_org
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = l_org
AND a.organization_id = b.organization_id
-- and a.asset_inventory = 1
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND a.secondary_inventory_name = b.secondary_inventory
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = l_org
AND a.organization_id = b.organization_id
AND a.asset_inventory = 2
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND a.secondary_inventory_name = b.secondary_inventory
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT a.secondary_inventory_name
, NVL(a.locator_type, 1)
, a.description
, a.asset_inventory
, a.lpn_controlled_flag
, a.enable_locator_alias
FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
WHERE a.organization_id = l_org
AND a.organization_id = b.organization_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND a.secondary_inventory_name = b.secondary_inventory
AND b.inventory_item_id = p_inventory_item_id
AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = l_org
--and asset_inventory = 1
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = l_org
AND asset_inventory = 2
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = l_org
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = NVL(p_organization_id, organization_id)
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
ORDER BY secondary_inventory_name;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
ORDER BY secondary_inventory_name;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = NVL(p_organization_id, organization_id)
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
ORDER BY secondary_inventory_name;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
ORDER BY secondary_inventory_name;
SELECT 1
INTO loc_exists
FROM DUAL
WHERE exists (select 1
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code);
SELECT locator_type
INTO loc_control
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name = p_subinventory_code;
/* This procedure is for the status update find page, here the locatorType
in the second parameter represents if any locators exist in the current
subinventory but doesnt really mean the locator control type */
-- Obsolete
PROCEDURE get_sub_with_loc(
x_zones OUT NOCOPY t_genref
, p_organization_id IN NUMBER
, p_subinventory_code IN VARCHAR2
) IS
l_debug NUMBER;
SELECT secondary_inventory_name
, inv_ui_item_sub_loc_lovs.check_loc_existence(p_organization_id, secondary_inventory_name)
, msi.description
, asset_inventory
, mmsv.status_id
, status_code
, enable_locator_alias
FROM mtl_secondary_inventories msi, mtl_material_statuses_vl mmsv
WHERE organization_id = p_organization_id
AND mmsv.status_id = msi.status_id
AND NVL(msi.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code);
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, msub.lpn_controlled_flag
, msub.enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND EXISTS( SELECT wda.staging_lane_id
FROM wms_dock_appointments_b wda, mtl_item_locations milk, wsh_trip_stops pickup_stop
WHERE milk.inventory_location_id(+) = wda.staging_lane_id
AND milk.organization_id(+) = wda.organization_id
AND milk.organization_id = p_organization_id
AND milk.subinventory_code = msub.secondary_inventory_name
AND wda.dock_appointment_id = p_dock_appointment_id
AND wda.trip_stop = pickup_stop.stop_id(+))
AND msub.secondary_inventory_name LIKE (p_sub);
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND EXISTS( SELECT milk.inventory_location_id
FROM mtl_item_locations milk, wms_license_plate_numbers lpn
WHERE milk.inventory_location_id(+) = lpn.locator_id
AND milk.organization_id(+) = lpn.organization_id
AND milk.organization_id = p_organization_id
AND milk.subinventory_code = msub.secondary_inventory_name
AND (lpn.lpn_context = 1
OR lpn.lpn_context = 11
))
AND msub.secondary_inventory_name LIKE (p_sub);
SELECT secondary_inventory_name
, locator_type
, description
, asset_inventory
, lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
ORDER BY secondary_inventory_name;
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, msub.lpn_controlled_flag
, msub.enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
ORDER BY UPPER(msub.secondary_inventory_name);
SELECT UNIQUE msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, msub.lpn_controlled_flag
, msub.enable_locator_alias
FROM mtl_secondary_inventories msub, mtl_physical_subinventories mpsub
WHERE msub.organization_id = p_organization_id
AND mpsub.organization_id = p_organization_id
AND mpsub.subinventory = msub.secondary_inventory_name
AND mpsub.physical_inventory_id = p_physical_inventory_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
ORDER BY UPPER(msub.secondary_inventory_name);
SELECT UNIQUE mil.inventory_location_id
--, mil.concatenated_segments--Bug4398337:Commented this line and added below line
, mil.locator_segments concatenated_segments
, mil.description
FROM wms_item_locations_kfv mil, mtl_physical_inventory_tags mpit
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory_code
AND mil.concatenated_segments LIKE (p_concatenated_segments)
AND mil.inventory_location_id = mpit.locator_id
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.organization_id = p_organization_id
AND NVL(mpit.void_flag, 2) = 2
-- WMS PJM Integration: Restrict Locators based on the project and task
AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
--For bug number 4885951
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND mpit.adjustment_id IN (SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL);
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv mil
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND concatenated_segments LIKE (p_concatenated_segments)
-- WMS PJM Integration: Restrict Locators based on the project and task
AND NVL(project_id, -1) = NVL(p_project_id, -1)
AND NVL(task_id, -1) = NVL(p_task_id, -1)
--For bug number 4885951
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE);
SELECT UNIQUE mil.inventory_location_id
--, mil.concatenated_segments--Bug4398337:Commented this line and added below line
, mil.locator_segments concatenated_segments
, mil.description
FROM wms_item_locations_kfv mil, mtl_physical_inventory_tags mpit
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory_code
AND mil.alias = p_alias
AND mil.inventory_location_id = mpit.locator_id
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.organization_id = p_organization_id
AND NVL(mpit.void_flag, 2) = 2
-- WMS PJM Integration: Restrict Locators based on the project and task
AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
--For bug number 4885951
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND mpit.adjustment_id IN (SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL);
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv mil
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND alias = p_alias
-- WMS PJM Integration: Restrict Locators based on the project and task
AND NVL(project_id, -1) = NVL(p_project_id, -1)
AND NVL(task_id, -1) = NVL(p_task_id, -1)
--For bug number 4885951
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE);
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, msub.lpn_controlled_flag
, msub.enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = p_organization_id
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
-- Bug# 2770853
-- Check for material status at the subinventory level
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
msub.organization_id,
NULL,
msub.secondary_inventory_name,
NULL,
NULL,
NULL,
'Z') = 'Y')
ORDER BY UPPER(msub.secondary_inventory_name);
SELECT UNIQUE msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, msub.lpn_controlled_flag
, msub.enable_locator_alias
FROM mtl_secondary_inventories msub, mtl_cc_subinventories mccs
WHERE msub.organization_id = p_organization_id
AND mccs.cycle_count_header_id = p_cycle_count_header_id
AND mccs.subinventory = msub.secondary_inventory_name
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
-- Bug# 2770853
-- Check for material status at the subinventory level
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
msub.organization_id,
NULL,
msub.secondary_inventory_name,
NULL,
NULL,
NULL,
'Z') = 'Y')
ORDER BY UPPER(msub.secondary_inventory_name);
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT NVL(serial_discrepancy_option, 2), NVL(container_discrepancy_option, 2)
INTO l_serial_discrepancy_option, l_container_discrepancy_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id;
SELECT UNIQUE mil.inventory_location_id,
--mil.concatenated_segments concatenated_segments,--Bug4398337:Commented this line and added below line
mil.locator_segments concatenated_segments,
mil.description
FROM wms_item_locations_kfv mil, mtl_cycle_count_entries mcce
WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
AND mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory_code
AND NVL(mil.project_id,-1) = NVL(p_project_id,-1)
AND NVL(mil.task_id,-1) = NVL(p_task_id,-1)
AND mil.concatenated_segments LIKE (p_concatenated_segments)
AND mcce.organization_id = mil.organization_id
AND mcce.subinventory = mil.subinventory_code
AND mil.inventory_location_id = mcce.locator_id
AND mcce.entry_status_code IN (1,3)
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
-- Bug# 2770853
-- Check for material status at the locator level
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
mcce.organization_id,
mcce.inventory_item_id,
mcce.subinventory,
mcce.locator_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY concatenated_segments;
SELECT inventory_location_id,
-- concatenated_segments, --Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
AND project_id = p_project_id
AND task_id = p_task_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
organization_id,
inventory_item_id,
subinventory_code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments ,--Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND concatenated_segments LIKE (p_concatenated_segments )--inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
AND project_id = p_project_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
organization_id,
inventory_item_id,
subinventory_code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments, --Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
organization_id,
inventory_item_id,
subinventory_code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT NVL(serial_discrepancy_option, 2), NVL(container_discrepancy_option, 2)
INTO l_serial_discrepancy_option, l_container_discrepancy_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id;
SELECT UNIQUE mil.inventory_location_id,
--mil.concatenated_segments concatenated_segments,--Bug4398337:Commented this line and added below line
mil.locator_segments concatenated_segments,
mil.description
FROM wms_item_locations_kfv mil, mtl_cycle_count_entries mcce
WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
AND mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory_code
AND NVL(mil.project_id,-1) = NVL(p_project_id,-1)
AND NVL(mil.task_id,-1) = NVL(p_task_id,-1)
AND mil.alias = p_alias
AND mcce.organization_id = mil.organization_id
AND mcce.subinventory = mil.subinventory_code
AND mil.inventory_location_id = mcce.locator_id
AND mcce.entry_status_code IN (1,3)
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
-- Bug# 2770853
-- Check for material status at the locator level
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
mcce.organization_id,
mcce.inventory_item_id,
mcce.subinventory,
mcce.locator_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY concatenated_segments;
SELECT inventory_location_id,
-- concatenated_segments, --Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND alias = p_alias
AND project_id = p_project_id
AND task_id = p_task_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
organization_id,
inventory_item_id,
subinventory_code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments ,--Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND alias = p_alias
--inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
AND project_id = p_project_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
organization_id,
inventory_item_id,
subinventory_code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments, --Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND alias = p_alias
-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
NULL,
organization_id,
inventory_item_id,
subinventory_code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y')
ORDER BY 2;
SELECT DISTINCT a.license_plate_number
, a.outermost_lpn_id
, a.subinventory_code
, NVL(a.locator_id, 0)
, NVL(b.asset_inventory, '0')
, 0
, inv_project.get_locsegs(a.locator_id, p_organization_id)
, inv_project.get_project_id
, inv_project.get_project_number
, inv_project.get_task_id
, inv_project.get_task_number
FROM wms_license_plate_numbers a, mtl_secondary_inventories b
WHERE a.organization_id = p_organization_id
AND (a.lpn_context = 1 OR a.lpn_context = 11)
AND b.organization_id(+) = a.organization_id
AND b.secondary_inventory_name(+) = a.subinventory_code
AND NVL(b.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND a.license_plate_number LIKE (p_lpn_segments)
AND a.parent_lpn_id IS NULL
AND (p_owning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
,wms_license_plate_numbers wlpn
WHERE moqd.lpn_id in (wlpn.lpn_id)
AND wlpn.outermost_lpn_id = a.outermost_lpn_id
AND moqd.organization_id = a.organization_id
AND moqd.owning_organization_id = p_owning_org_id
AND moqd.owning_tp_type = p_owning_tp_type))
AND (p_planning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
,wms_license_plate_numbers wlpn
WHERE moqd.lpn_id in (wlpn.lpn_id)
AND wlpn.outermost_lpn_id = a.outermost_lpn_id
AND moqd.organization_id = a.organization_id
AND moqd.planning_organization_id = p_planning_org_id
AND moqd.planning_tp_type = p_planning_tp_type));
SELECT DISTINCT a.license_plate_number
, a.outermost_lpn_id
, a.subinventory_code
, NVL(a.locator_id, 0)
, NVL(b.asset_inventory, '0')
, 0
, inv_project.get_locsegs(a.locator_id, p_organization_id)
, inv_project.get_project_id
, inv_project.get_project_number
, inv_project.get_task_id
, inv_project.get_task_number
FROM wms_license_plate_numbers a, mtl_secondary_inventories b
WHERE a.organization_id = p_organization_id
AND (a.lpn_context = 1 OR a.lpn_context = 11)
AND b.organization_id(+) = a.organization_id
AND b.secondary_inventory_name(+) = a.subinventory_code
AND NVL(b.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND a.license_plate_number LIKE (p_lpn_segments)
AND a.parent_lpn_id IS NULL
AND vaildate_lpn_status(a.outermost_lpn_id
,a.organization_id
,p_to_organization_id
,p_wms_installed
,p_transaction_type_id) = 'Y'
AND (p_owning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
,wms_license_plate_numbers wlpn
WHERE moqd.lpn_id in (wlpn.lpn_id)
AND wlpn.outermost_lpn_id = a.outermost_lpn_id
AND moqd.organization_id = a.organization_id
AND moqd.owning_organization_id = p_owning_org_id
AND moqd.owning_tp_type = p_owning_tp_type))
AND (p_planning_org_id IS NULL
OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
,wms_license_plate_numbers wlpn
WHERE moqd.lpn_id in (wlpn.lpn_id)
AND wlpn.outermost_lpn_id = a.outermost_lpn_id
AND moqd.organization_id = a.organization_id
AND moqd.planning_organization_id = p_planning_org_id
AND moqd.planning_tp_type = p_planning_tp_type));
SELECT DISTINCT inventory_item_id
FROM wms_lpn_contents
WHERE parent_lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id)
AND inventory_item_id IS NOT NULL;
SELECT 1
INTO l_invalid_count
FROM dual
WHERE EXISTS (SELECT a.inventory_item_id
FROM mtl_system_items a
, mtl_system_items b
WHERE a.inventory_item_id = b.inventory_item_id
AND a.organization_id = p_orgid
AND b.organization_id = p_to_organization_id
AND a.inventory_item_id = l_item_id
AND ((a.serial_number_control_code IN (1,6) AND b.serial_number_control_code IN (2,5))
OR
(a.revision_qty_control_code = 1 AND b.revision_qty_control_code = 2)
OR
(a.lot_control_code = 1 AND b.lot_control_code = 2))
);
SELECT COUNT(*)
INTO l_count
FROM mtl_system_items
WHERE organization_id = p_to_organization_id
AND inventory_item_id = l_item_id;
SELECT COUNT(*)
INTO l_count
FROM mtl_system_items
WHERE organization_id = p_to_organization_id
AND inventory_item_id = l_item_id;
/*SELECT COUNT(*)
INTO l_lpn_rsvd
FROM mtl_reservations
WHERE lpn_id = p_lpn_id;*/
SELECT COUNT(*)
INTO l_lpn_rsvd
FROM mtl_reservations
WHERE lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id);
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = l_org
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND inv_ui_item_sub_loc_lovs.vaildate_to_lpn_sub(p_lpn_id, secondary_inventory_name, l_org, p_from_sub_asset_inventory, p_wms_installed, p_transaction_type_id) = 'Y'
AND secondary_inventory_name LIKE (p_secondary_inventory_name);
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = l_org
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND inv_ui_item_sub_loc_lovs.vaildate_to_lpn_sub(p_lpn_id, secondary_inventory_name, l_org, p_from_sub_asset_inventory, p_wms_installed, p_transaction_type_id) = 'Y'
AND secondary_inventory_name LIKE (p_secondary_inventory_name)
AND reservable_type = 1
AND lpn_controlled_flag = 1;
SELECT DISTINCT inventory_item_id
FROM wms_lpn_contents
WHERE parent_lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id)
AND inventory_item_id IS NOT NULL;
SELECT DISTINCT content_type INTO l_content_type
FROM wms_lpn_contents
WHERE outermost_lpn_id = p_lpn_id;
SELECT restrict_subinventories_code
, inventory_asset_flag
INTO l_restrict_subinventories_code
, l_inventory_asset_flag
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = p_orgid;
SELECT COUNT(*)
INTO l_count
FROM mtl_item_sub_inventories
WHERE inventory_item_id = l_item_id
AND organization_id = p_orgid
AND secondary_inventory = p_to_subinventory;
SELECT COUNT(*)
INTO l_count
FROM mtl_item_sub_inventories
WHERE inventory_item_id = l_item_id
AND organization_id = p_orgid
AND secondary_inventory = p_to_subinventory;
SELECT COUNT(*)
INTO l_count
FROM mtl_item_sub_exp_val_v
WHERE inventory_item_id = l_item_id
AND organization_id = p_orgid
AND secondary_inventory_name = p_to_subinventory;
SELECT COUNT(*)
INTO l_count
FROM mtl_item_sub_inventories
WHERE inventory_item_id = l_item_id
AND organization_id = p_orgid
AND secondary_inventory = p_to_subinventory;
SELECT DISTINCT inventory_item_id
FROM wms_lpn_contents
WHERE parent_lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id)
AND inventory_item_id IS NOT NULL;
SELECT restrict_locators_code
INTO l_restrict_locators_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = p_orgid;
SELECT COUNT(*)
INTO l_count
FROM mtl_secondary_locators
WHERE p_locator_id = secondary_locator
AND inventory_item_id = l_item_id
AND organization_id = p_orgid;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
SELECT 'Y'
INTO oth_val
FROM DUAL
WHERE EXISTS( SELECT wlpn.license_plate_number
FROM wms_license_plate_numbers wlpn
WHERE (wlpn.organization_id = p_org_id
AND wlpn.lpn_context = 5
AND license_plate_number = p_lpn
)
OR (wlpn.organization_id = p_org_id
AND (wlpn.lpn_context = 1
OR wlpn.lpn_context = 11
)
AND NVL(subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
AND NVL(locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(locator_id, '0'))
AND NOT lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
AND NVL(parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(parent_lpn_id, 0))
AND license_plate_number = p_lpn
));
SELECT DISTINCT wlc.serial_number
, wlc.lot_number
, wlc.inventory_item_id
, wlpn.subinventory_code
, wlpn.locator_id
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
WHERE wlc.organization_id = p_orgid
AND wlc.parent_lpn_id = wlpn.lpn_id
AND wlc.organization_id = wlpn.organization_id
AND wlc.parent_lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id)
AND wlc.inventory_item_id IS NOT NULL
UNION
SELECT DISTINCT serial_number
, lot_number
, inventory_item_id
, current_subinventory_code
, current_locator_id
FROM mtl_serial_numbers
WHERE current_organization_id = p_orgid
AND lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id);
PROCEDURE get_cgupdate_subs(
x_cgupdate_sub_lov OUT NOCOPY t_genref
, p_subinventory_code IN VARCHAR2
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2
) IS
l_debug NUMBER;
OPEN x_cgupdate_sub_lov FOR
SELECT DISTINCT moq.subinventory_code
, '0'
, msi.description
, '0'
, msi.lpn_controlled_flag
, msi.enable_locator_alias
FROM mtl_secondary_inventories msi, MTL_ONHAND_QUANTITIES_DETAIL moq
WHERE msi.secondary_inventory_name = moq.subinventory_code
AND msi.organization_id = moq.organization_id
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, moq.subinventory_code, NULL, NULL, NULL, 'Z') = 'Y'
AND moq.containerized_flag = 2
AND moq.subinventory_code LIKE (p_subinventory_code)
AND (moq.revision = p_revision
OR (moq.revision IS NULL
AND p_revision IS NULL
)
)
AND moq.inventory_item_id = p_inventory_item_id
AND moq.organization_id = p_organization_id
ORDER BY moq.subinventory_code;
END get_cgupdate_subs;
PROCEDURE get_cgupdate_locs(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
SELECT moq.locator_id
--, mil.concatenated_segments--Bug4398337:Commented this line and added below line
, mil.locator_segments concatenated_segments
, mil.description
FROM wms_item_locations_kfv mil, MTL_ONHAND_QUANTITIES_DETAIL moq
WHERE mil.concatenated_segments LIKE (p_concatenated_segments)
AND mil.inventory_location_id = moq.locator_id
AND mil.organization_id = p_organization_id
-- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
AND mil.project_id IS NULL
AND mil.task_id IS NULL
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, moq.locator_id, NULL, NULL, 'L') = 'Y'
AND moq.containerized_flag = 2
AND (moq.revision = p_revision
OR (moq.revision IS NULL
AND p_revision IS NULL
)
)
AND moq.inventory_item_id = p_inventory_item_id
AND moq.locator_id IS NOT NULL
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
GROUP BY moq.locator_id, mil.concatenated_segments, mil.description
ORDER BY 2;
END get_cgupdate_locs;
PROCEDURE get_cgupdate_locs(
x_locators OUT NOCOPY t_genref,
p_organization_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_concatenated_segments IN VARCHAR2,
p_inventory_item_id IN NUMBER,
p_revision IN VARCHAR2,
p_alias IN VARCHAR2
) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
get_cgupdate_locs(
x_locators => x_locators
,p_organization_id => p_organization_id
,p_subinventory_code => p_subinventory_code
,p_concatenated_segments => p_concatenated_segments
,p_inventory_item_id => p_inventory_item_id
,p_revision => p_revision
);
SELECT moq.locator_id
--, mil.concatenated_segments--Bug4398337:Commented this line and added below line
, mil.locator_segments concatenated_segments
, mil.description
FROM wms_item_locations_kfv mil, MTL_ONHAND_QUANTITIES_DETAIL moq
WHERE alias = p_alias
AND mil.inventory_location_id = moq.locator_id
AND mil.organization_id = p_organization_id
-- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
AND mil.project_id IS NULL
AND mil.task_id IS NULL
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, moq.locator_id, NULL, NULL, 'L') = 'Y'
AND moq.containerized_flag = 2
AND (moq.revision = p_revision
OR (moq.revision IS NULL
AND p_revision IS NULL
)
)
AND moq.inventory_item_id = p_inventory_item_id
AND moq.locator_id IS NOT NULL
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
GROUP BY moq.locator_id, mil.concatenated_segments, mil.description
ORDER BY 2;
END get_cgupdate_locs;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, 0 dummy
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = NVL(p_organization_id, organization_id)
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
UNION ALL
SELECT 'All Subinventories'
, 0
, ''
, 0
, 1 dummy
, 'N' enable_locator_alias
FROM DUAL
WHERE 'All Subinventories' LIKE (p_subinventory_code)
ORDER BY dummy DESC, secondary_inventory_name;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, 0 dummy
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
UNION ALL
SELECT 'All Subinventories'
, 0
, ''
, 0
, 1 dummy
, 'N' enable_locator_alias
FROM DUAL
WHERE 'All Subinventories' LIKE (p_subinventory_code)
ORDER BY dummy DESC, secondary_inventory_name;
SELECT milv.inventory_location_id
--, milv.concatenated_segments --Bug4398337:Commented this line and added below line
, milv.locator_segments concatenated_segments
, milv.description
, 0 dummy
, mmsv.status_code
FROM wms_item_locations_kfv milv, mtl_material_statuses_tl mmsv
WHERE milv.organization_id = p_organization_id
AND milv.subinventory_code = p_subinventory_code
AND milv.concatenated_segments LIKE (p_concatenated_segments)
AND (mmsv.status_id(+)/*Added outer join 2918529*/ = milv.status_id )
AND mmsv.language(+) = userenv('LANG')
UNION ALL
SELECT 0
, 'All Locators'
, ''
, 1 dummy
, ''
FROM DUAL
WHERE 'All Locators' LIKE (p_concatenated_segments)
ORDER BY dummy DESC, concatenated_segments;
SELECT milv.inventory_location_id
--, milv.concatenated_segments --Bug4398337:Commented this line and added below line
, milv.locator_segments concatenated_segments
, milv.description
, 0 dummy
, mmsv.status_code
FROM wms_item_locations_kfv milv, mtl_material_statuses_tl mmsv
WHERE milv.organization_id = p_organization_id
AND milv.subinventory_code = p_subinventory_code
AND milv.alias = p_alias
AND (mmsv.status_id(+)/*Added outer join 2918529*/ = milv.status_id )
AND mmsv.language(+) = userenv('LANG');
PROCEDURE update_dynamic_locator(
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_result OUT NOCOPY VARCHAR2,
x_exist_or_create OUT NOCOPY VARCHAR2,
p_locator_id IN NUMBER,
p_org_id IN NUMBER,
p_sub_code IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
SELECT subinventory_code
, project_id
, task_id
INTO l_sub_code
, l_project_id
, l_task_id
FROM mtl_item_locations
WHERE inventory_location_id = p_locator_id
AND organization_id = p_org_id;
update_locator(p_sub_code, p_org_id, p_locator_id);
DEBUG('After inserting the default values');
END update_dynamic_locator;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = NVL(p_organization_id, organization_id)
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND lpn_controlled_flag = 1
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
ORDER BY secondary_inventory_name;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND lpn_controlled_flag = 1
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
ORDER BY secondary_inventory_name;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT Nvl(subinventory_type,1)
INTO l_sub_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
select a.inventory_location_id,
--a.concatenated_segments,--Bug4398337:Commented this line and added below line
a.locator_segments concatenated_segments,
nvl( a.description, -1)
FROM wms_item_locations_kfv a,mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments)
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
a.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
select inventory_location_id,
--concatenated_segments,--Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_Organization_Id
AND subinventory_code = p_Subinventory_Code
AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND concatenated_segments LIKE (p_concatenated_segments )
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
select a.inventory_location_id,
--a.concatenated_segments,--Bug4398337:Commented this line and added below line
a.locator_segments concatenated_segments,
nvl( a.description, -1)
FROM wms_item_locations_kfv a,mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
AND a.concatenated_segments like (p_concatenated_segments )
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
a.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
select inventory_location_id,
--concatenated_segments,--Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_Organization_Id
AND subinventory_code = p_Subinventory_Code
AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
AND concatenated_segments LIKE (p_concatenated_segments )
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT Nvl(subinventory_type,1)
INTO l_sub_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
select a.inventory_location_id,
--a.concatenated_segments,--Bug4398337:Commented this line and added below line
a.locator_segments concatenated_segments,
nvl( a.description, -1)
FROM wms_item_locations_kfv a,mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
-- AND a.concatenated_segments LIKE (p_concatenated_segments)
AND a.alias = p_alias
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
a.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
select inventory_location_id,
--concatenated_segments,--Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_Organization_Id
AND subinventory_code = p_Subinventory_Code
AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
-- AND concatenated_segments LIKE (p_concatenated_segments )
AND alias = p_alias
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
select a.inventory_location_id,
--a.concatenated_segments,--Bug4398337:Commented this line and added below line
a.locator_segments concatenated_segments,
nvl( a.description, -1)
FROM wms_item_locations_kfv a,mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
-- AND a.concatenated_segments like (p_concatenated_segments )
AND a.alias = p_alias
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
a.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
select inventory_location_id,
--concatenated_segments,--Bug4398337:Commented this line and added below line
locator_segments concatenated_segments,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_Organization_Id
AND subinventory_code = p_Subinventory_Code
AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
-- AND concatenated_segments LIKE (p_concatenated_segments )
AND alias = p_alias
/* BUG#2810405: To show only common locators in the LOV */
AND inv_material_status_grp.is_status_applicable
( p_wms_installed,
NULL,
p_transaction_type_id,
NULL,
NULL,
p_Organization_Id,
p_Inventory_Item_Id,
p_Subinventory_Code,
inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY 2;
SELECT restrict_locators_code
INTO l_restrict_locators_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_org;
SELECT restrict_locators_code
INTO l_restrict_locators_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_org;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND alias = p_alias
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y'
UNION ALL
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 2
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
ORDER BY 1;
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 2
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
ORDER BY 1;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 1
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 1
AND lpn_controlled_flag = 1
AND reservable_type = 1
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 1
AND lpn_controlled_flag = 2
AND reservable_type = 2
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y';
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 1
AND lpn_controlled_flag = 1
AND reservable_type = 1
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y'
UNION ALL
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 2
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
ORDER BY 1;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 1
AND lpn_controlled_flag = 2
AND reservable_type = 2
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y'
UNION ALL
SELECT msub.secondary_inventory_name
, NVL(msub.locator_type, 1)
, msub.description
, msub.asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories msub
WHERE organization_id = p_organization_id
AND Nvl(subinventory_type,1) = 2
AND msub.secondary_inventory_name LIKE (p_subinventory_code)
AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
ORDER BY 1;
SELECT secondary_inventory_name
, NVL(locator_type, 1)
, description
, asset_inventory
, lpn_controlled_flag
, Nvl(subinventory_type,1)
, reservable_type
, enable_locator_alias
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y'
ORDER BY UPPER(secondary_inventory_name);
SELECT DISTINCT wlc.inventory_item_id
, msi.restrict_subinventories_code
FROM wms_lpn_contents wlc, mtl_system_items msi
WHERE wlc.parent_lpn_id IN(SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id)
AND wlc.inventory_item_id IS NOT NULL
AND wlc.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_organization_id;
SELECT inventory_item_id
, transaction_type_id
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT COUNT(*)
INTO l_count
FROM mtl_item_sub_inventories
WHERE organization_id = p_organization_id
AND inventory_item_id = l_item_id
AND secondary_inventory = p_subinventory_code;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, inventory_location_type
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND alias = p_alias
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND inv_ui_item_sub_loc_lovs.validate_lpn_loc(p_organization_id,
p_subinventory_code,
inventory_location_id,
p_lpn_id) = 'Y'
ORDER BY 2;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, inventory_location_type
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND inv_ui_item_sub_loc_lovs.validate_lpn_loc(p_organization_id,
p_subinventory_code,
inventory_location_id,
p_lpn_id) = 'Y'
ORDER BY 2;
SELECT DISTINCT wlc.inventory_item_id, msi.restrict_locators_code
FROM wms_lpn_contents wlc, mtl_system_items msi
WHERE wlc.parent_lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id)
AND wlc.inventory_item_id IS NOT NULL
AND wlc.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_organization_id;
SELECT inventory_item_id, transaction_type_id
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT nvl(subinventory_type,1)
INTO l_sub_type
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT COUNT(*)
INTO l_count
FROM mtl_secondary_locators
WHERE organization_id = p_organization_id
AND inventory_item_id = l_item_id
AND subinventory_code = p_subinventory_code
AND secondary_locator = p_locator_id;
/*Bug 2902336:Modfied the select clauses for performance enhancements*/
BEGIN
IF p_alias IS NULL THEN
get_pickload_loc_lov(
x_locators => x_locators
, p_organization_id => p_organization_id
, p_subinventory_code => p_subinventory_code
, p_restrict_locators_code => p_restrict_locators_code
, p_inventory_item_id => p_inventory_item_id
, p_concatenated_segments => p_concatenated_segments
, p_transaction_type_id => p_transaction_type_id
, p_wms_installed => p_wms_installed
, p_project_id => p_project_id
, p_task_id => p_task_id
);
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
/*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
AND a.alias = p_alias
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND alias = p_alias
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
AND a.alias = p_alias
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND alias = p_alias
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
--AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND a.alias = p_alias
AND a.project_id = p_project_id
AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND alias = p_alias
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
/*Bug 2902336:Modfied the select clauses for performance enhancements*/
BEGIN
BEGIN
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
/*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
--AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND a.project_id = p_project_id
AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND concatenated_segments LIKE (p_concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
/*Bug 2902336:Modfied the select clauses for performance enhancements*/
BEGIN
BEGIN
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id
, a.concatenated_segments -- Bug 4398336
--, a.locator_segments concatenated_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
/*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
, concatenated_segments -- Bug 4398336
--, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
, a.concatenated_segments -- Bug 4398336
--, a.locator_segments concatenated_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
-- AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
AND a.project_id is null
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
, concatenated_segments -- Bug 4398336
--, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
-- AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND project_id is null
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
, a.concatenated_segments -- Bug 4398336
--, a.locator_segments concatenated_segments
, NVL(a.description, -1)
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
--AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND a.project_id = p_project_id
AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
, concatenated_segments -- Bug 4398336
--, locator_segments concatenated_segments
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND concatenated_segments LIKE (p_concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
, a.subinventory_code
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
/*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, subinventory_code
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
, a.subinventory_code
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
/*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, subinventory_code
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
--, a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
, a.subinventory_code
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
--AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND a.project_id = p_project_id
AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, subinventory_code
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
--AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
AND concatenated_segments LIKE (p_concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
* All the locators for the given org are selected, not restricting on the subinventory
*/
-- Patchset J: Procedure used to get all the locs in the org
-- restricted by proj, task if passed and
-- NOT restricted by subinventory
-- Procedure Name: get_pickload_all_loc_lov
--
-- Input parameters:
-- p_organization_id - Organization Id
--
-- Output value:
-- x_locators Ref. cursor
--
PROCEDURE get_pickload_all_loc_lov(
x_locators OUT NOCOPY t_genref
, p_organization_id IN NUMBER
, p_restrict_locators_code IN NUMBER
, p_inventory_item_id IN NUMBER
, p_concatenated_segments IN VARCHAR2
, p_transaction_type_id IN NUMBER
, p_wms_installed IN VARCHAR2
, p_project_id IN NUMBER
, p_task_id IN NUMBER) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
/*Bug 2902336:Modfied the select clauses for performance enhancements*/
BEGIN
BEGIN
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id
-- , a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
, a.subinventory_code
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
/*AND b.subinventory_code = p_subinventory_code*/ /*Removed Subinventory restriction, displayes all locs in the org*/
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
--, concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, subinventory_code
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
/*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
-- , a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
,a.subinventory_code
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
/*AND b.subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
AND a.inventory_location_id = b.secondary_locator
AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, subinventory_code
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
/*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
AND concatenated_segments LIKE (p_concatenated_segments)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT a.inventory_location_id
-- , a.concatenated_segments--Bug4398337:Commented this line and added below line
, a.locator_segments concatenated_segments
, NVL(a.description, -1)
, a.subinventory_code
FROM wms_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
/*AND b.subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND a.project_id = p_project_id
AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
-- , concatenated_segments--Bug4398337:Commented this line and added below line
, locator_segments concatenated_segments
, description
, subinventory_code
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
/*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND concatenated_segments LIKE (p_concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id,
-- a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments )
-- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
-- AND alias = nvl(p_alias, alias)
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments )
-- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
/* BUG#28101405: To show only common locators in the LOV */
AND a.project_id = p_project_id
AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments )
-- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id,
-- a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.alias = p_alias
-- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.alias = p_alias
-- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
/* BUG#28101405: To show only common locators in the LOV */
AND a.project_id = p_project_id
AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.alias = p_alias
-- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
-- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments )
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments )
/* BUG#28101405: To show only common locators in the LOV */
AND a.project_id = p_project_id
AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments )
AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND concatenated_segments LIKE (p_concatenated_segments )
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.alias = p_alias
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.alias = p_alias
/* BUG#28101405: To show only common locators in the LOV */
AND a.project_id = p_project_id
AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
AND project_id = p_project_id
AND NVL(task_id, -1) = NVL(p_task_id, -1)
ORDER BY 2;
SELECT a.inventory_location_id,
--a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
a.locator_segments locsegs,
a.description
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_Organization_Id
AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND a.alias = p_alias
AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
/* BUG#28101405: To show only common locators in the LOV */
ORDER BY 2;
SELECT inventory_location_id,
--concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = Nvl(p_organization_id, organization_id)
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT inventory_location_id,
-- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
locator_segments locsegs,
description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND alias = p_alias
AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT subinventory_code
, CONCATENATED_SEGMENTS
, DESCRIPTION
, project_id
, task_id
INTO x_subinventory_code
, x_concatenated_segments
, x_description
, x_project_id
, x_task_id
FROM wms_item_locations_kfv
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = p_organization_id;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT inventory_location_id
, locator_segments locsegs
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = NVL(p_Subinventory_Code ,subinventory_code)
AND inventory_location_type = p_inventory_location_type
AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND concatenated_segments LIKE (p_concatenated_segments)
ORDER BY 2;
SELECT inventory_location_id
, locator_segments locsegs
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND inventory_location_type = p_inventory_location_type
AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND concatenated_segments LIKE (p_concatenated_segments )
AND inventory_location_id = NVL(physical_location_id,inventory_location_id)
ORDER BY 2;
SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
INTO l_ispjm_org
FROM pjm_org_parameters
WHERE organization_id=p_organization_id;
SELECT inventory_location_id
, locator_segments locsegs
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = NVL(p_Subinventory_Code ,subinventory_code)
AND inventory_location_type = p_inventory_location_type
AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND alias = p_alias
ORDER BY 2;
SELECT inventory_location_id
, locator_segments locsegs
, description
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
AND inventory_location_type = p_inventory_location_type
AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND inventory_location_id = NVL(physical_location_id,inventory_location_id)
AND alias = p_alias
ORDER BY 2;
SELECT locator_segments
INTO x_value
FROM wms_item_locations_kfv
WHERE alias = p_alias
AND organization_id = p_org_id
AND subinventory_code = p_sub_code
AND project_id IS NULL
AND task_id IS NULL;
SELECT locator_segments
INTO x_value
FROM wms_item_locations_kfv
WHERE alias = p_alias
AND organization_id = p_org_id
AND subinventory_code = p_sub_code
AND locator_segments = p_suggested
AND project_id IS NULL
AND task_id IS NULL;